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