CS174
Chris Pollett
Oct 5, 2022
select col1, col2, col3 from table1, table2, table3 where condition_holds; // you may also have additional clauses for grouping and ordering results.
/* To Get First Names and Last Names of Employees with Salary's bigger than $10000 */ SELECT FNAME, LNAME FROM EMPLOYEE WHERE SALARY > 10000; /* To get triples (FNAME, LNAME, PNAME) of Employees who work on projects with names containing the string "SECRET", sorting result in descending alphabetical order, showing results 10 through 60. */ SELECT E.LNAME AS LNAME, E.FNAME AS FNAME, P.PROJECT_NAME AS PNAME FROM EMPLOYEE E, PROJECT P, WORKS_ON W WHERE E.EID=W.EID AND P.PID=W.PID AND P.PROJECT_NAME LIKE '%SECRET%' ORDER BY E.LNAME DESC, E.FNAME DESC, P.PROJECT_NAME DESC LIMIT 10,50; /* start at 10 plus next 50 */ /* Number of classes each student is taking */ SELECT STUDENT.NAME, COUNT(*) /* AVG, COUNT, SUM are aggregate functions*/ FROM STUDENT, COURSE WHERE STUDENT.ID=COURSE.SID GROUP BY STUDENT.NAME;
The above code is slightly older SQL syntax, one could have written the above as:
SELECT STUDENT.NAME, COUNT(*) /* AVG, COUNT, SUM are aggregate functions*/ FROM STUDENT JOIN COURSE ON STUDENT.ID=COURSE.SID //the ON clause is use to say how tables are connected // one can also have a where clause for condition not related two tables GROUP BY STUDENT.NAME;
SELECT T1.FIELD_NAME_1 AS NAME1, ..., T1.FIELD_NAME_N AS NAME_N, ..., TM.FIELD_NAME_N AS SOME_NAME FROM T1, T2, ..., TM // might use JOINs WHERE T1.FIELD_NAME_j = some value [OR|AND] ... T1.FIELD_NAME_k = TJ.FIELD_NAME_w [GROUP BY ...] [HAVING ...] [ORDER BY ...] [LIMIT ...]The above are called SELECT statements and consist of three main lines (plus optional ones). The first line lists the columns we want, the second line lists the tables to use, the last line lists the conditions that must be met by the returned row (join conditions).
INSERT INTO table_name VALUES ('col1_value', 'col2_value', 'col3_value'); or INSERT INTO table_name VALUES ('col1_value_1', 'col2_value_1', 'col3_value_1'), ('col1_value_2', 'col2_value_2', 'col3_value_2'), ... ('col1_value_n', 'col2_value_n', 'col3_value_n'); or INSERT INTO table_name SELECT col_1, ..., col_n FROM table_1, ..., table_m WHERE condition;
DELETE FROM table_name WHERE cond;
UPDATE table_name SET column='value' WHERE cond;
$db = mysqli_connect();
$db = mysqli_connect($host, $uname, $pword, $db_name);
mysqli_select_db($db, "vehicles");
$query ="SELECT * FROM CAR"; $result = mysqli_query($db, $query); $num_rows = mysqli_num_rows($result); $num_fields = mysqli_num_fields($result); echo $num_fields; for($j = 1; $j <= $num_rows; $j++) { $row = mysqli_fetch_array($result); print_r($row); }
mysqli_select_db($db, "vehicles");
$query ="SELECT * FROM CAR"; $result = mysqli_query($db, $query); $num_rows = mysqli_num_rows($result); $num_fields = mysqli_num_fields($result); echo $num_fields; for($j = 1; $j <= $num_rows; $j++) { $row = mysqli_fetch_array($result); print_r($row); }