CS174
Chris Pollett
Mar 15, 2021
Models in the MVA pattern often interact with a database so we are going to have a whirlwind tour of databases to learn how to do this.
car(make VARCHAR(10), model VARCHAR(10), year DATE)
is an example table schema (specification). A possible row of car
is:
('Toyota', 'Corolla', '1970-01-01')
mysqladmin -uroot password what_password_is
mysql -uroot -pwhat_password_is
show databases;
use dbname;
show tables;
GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES on dbname.* TO username@127.0.0.1 IDENTIFIED BY 'password';
mysql -uusername -ppassword
Which of the following statements is true?
CREATE DATABASE my_nifty_db;
CREATE TABLE customer ( cust_id int(5) NOT NULL, name VARCHAR(50), title CHAR(3), PRIMARY KEY(cust_id) );
DROP TABLE customer;
DROP DATABASE my_nifty_db;
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;
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 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); }