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
