MySQL數據庫的那點小總結

CREATE DATABASE  ku1;
CREATE TABLE person(
   id INT PRIMARY KEY,
   username VARCHAR(20)
   ); 
   
CREATE TABLE car (
   id INT PRIMARY KEY,
   carname VARCHAR(20),
   pid INT,
   FOREIGN KEY(pid) REFERENCES person (id)   
)   


INSERT INTO car VALUES(1,'baoma',NULL)
INSERT INTO person VALUES(1,'zhangsan');

INSERT INTO car VALUES(2,'qq',1);

DELETE FROM person

/ 給張三買汽車 /
INSERT INTO person VALUES(1,'zhangsan');
INSERT INTO car VALUES (2,'hanma',1);
INSERT INTO car VALUES (3,'luhu',1);

/李四買汽車/
INSERT INTO person VALUES(2,'lisi');
INSERT INTO car  VALUES(4,'xiali',2);
INSERT INTO car  VALUES(5,'qq',2) 

/莉莉沒車/
INSERT INTO person VALUES(3,'lili');

/某人有某車/

SELECT person.username,car.carname FROM person,car; 

SELECT p.username,c.carname FROM person p,car c WHERE p.id = c.pid; 

SELECT p.username,c.carname FROM  person p INNER JOIN car c ON p.id = c.pid;




SELECT p.username,c.carname FROM  person p RIGHT JOIN car c ON p.id = c.pid;

 
 
 
/誰沒有車/ 
SELECT p.username,c.carname FROM  person p LEFT JOIN car c ON p.id = c.pid WHERE c.carname IS NULL; 
 
/某人有悍馬車/ 
SELECT FROM person WHERE id = 1  

SELECT pid FROM car WHERE carname='hanma'  

SELECT FROM person WHERE id = ( SELECT pid FROM car WHERE carname='hanma' );

 /**/
 
 CREATE TABLE idcard (
   id INT PRIMARY KEY,
   gov VARCHAR(30),
   FOREIGN KEY (id) REFERENCES person (id)
 )
 
INSERT INTO idcard VALUES(1,'北京發證');
INSERT INTO idcard VALUES(2,'上海發證');

/某人在某地發證/

SELECT username , gov FROM  person INNER JOIN  idcard ON person.id = idcard.id;

SELECT username ,gov FROM person LEFT JOIN idcard ON person.id = idcard.id;


SELECT username ,gov FROM person LEFT JOIN idcard ON person.id = idcard.id WHERE idcard.gov IS NULL; 

SELECT username ,gov FROM person RIGHT JOIN idcard ON person.id = idcard.id

/  / 

CREATE TABLE  student (

  id INT PRIMARY KEY,
  username VARCHAR(20) 

) 


CREATE TABLE course(

   id INT PRIMARY KEY,
   cname VARCHAR(20)
)

/聯合主鍵/
CREATE TABLE sc(

    sid INT,
    cid INT,
    PRIMARY KEY(sid,cid),
    FOREIGN KEY(sid) REFERENCES student (id),
    FOREIGN KEY(cid) REFERENCES course (id)
)



INSERT INTO student VALUES (1,'zhangsan');
INSERT INTO student VALUES (2,'lisi');



INSERT INTO course VALUES(1,'java');
INSERT INTO course VALUES(2,'php');
INSERT INTO course VALUES(3,'ios');

INSERT INTO sc VALUES (1,1);
INSERT INTO sc VALUES (1,2);

INSERT INTO sc VALUES (2,3);


/某人選擇了某課/

SELECT username,cname FROM student INNER JOIN sc ON student.id = sc.sid
                                   INNER JOIN course ON sc.cid = course.id


SELECT username,cname FROM student LEFT JOIN sc ON student.id = sc.sid
                                   INNER JOIN course ON sc.cid = course.id

SELECT username,cname FROM student LEFT JOIN sc ON student.id = sc.sid
                                   LEFT JOIN course ON sc.cid = course.id

SELECT username,cname FROM student RIGHT JOIN sc ON student.id = sc.sid
                                   RIGHT JOIN course ON sc.cid = course.id