CS174
Chris Pollett
Oct 3, 2016
Last Day, we learned about the MVA design pattern. Models in this pattern often interact with a database so today 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?
Which of the following statements is true?
local
keyword.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;
/* 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 ...] [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');
DELETE FROM table_name WHERE cond;
UPDATE table_name SET column='value' WHERE cond;
$db = mysqli_connect($host, $uname, $pword, $db);
mysqli_select_db("cars");
$query ="SELECT * FROM Corvettes"; $result = mysqli_query($query); $num_rows = mysqli_num_rows($result); $num_fields = mysqli_num_fields($result); for($j = 1; $j <= $num_rows; $j++) { $row = mysqli_fetch_array($result); print $row[0].$row["some_attr"]. "<br />"; }
<?php $mysqli = new mysqli("localhost", "my_user", "my_password", "my_db"); // note: here we are using the OO interface to mysqli // we'll talk more about OO in PHP later today; for now, pretend its like Java/C++ $stmt = $mysqli->stmt_init(); if ($stmt->prepare("SELECT FNAME FROM EMPLOYEE WHERE LNAME=?")) { $lnames = ["Smith", "Jones", "Pollett"]; foreach($lnames as $lname) { $stmt->bind_param("s", $lname); //s == string, i == int, d==double $stmt->execute(); $stmt->bind_result($fname); $stmt->fetch(); print("The first person I found with last name $lname was $fname\n"); } $stmt->close(); } $mysqli->close();
SSN, PNUMBER--> HOURS SSN --> ENAME PNUMBER --> PNAME, PLOCATION.