mySql 简单 多表 联查

mySql

引用：https://blog.csdn.net/Dream_Weave/article/details/85172796
引用：https://www.cnblogs.com/caoyajun33-blog/p/6814185.html
引用：https://blog.csdn.net/qq_34987215/article/details/83590167

建表方法  查询方法

// An highlighted block

   CREATE TABLE IF NOT EXISTS person(
    id INT PRIMARY KEY AUTO_INCREMENT,
    sname VARCHAR(10),
    sex CHAR(1),
    husband INT,
    wife INT 
);
 
 
INSERT INTO person VALUES(1,'小花','0',3,0);
INSERT INTO person VALUES(2,'小明','1',0,4);
INSERT INTO person VALUES(3,'张三','1',0,1);
INSERT INTO person VALUES(4,'小丽','0',2,0);
INSERT INTO person VALUES(5,'王五','1',0,0);

CREATE VIEW men AS (SELECT * FROM person WHERE sex='1');
CREATE VIEW women AS (SELECT * FROM person WHERE sex='0');

 SELECT men.sname AS husband,women.sname AS wife FROM men,women WHERE men.id = women.husband AND women.id = men.wife;```
 SELECT men.sname AS husband,women.sname AS wife FROM men INNER JOIN women ON men.id = women.husband AND women.id = men.wife;

// 一对多
// 建立人员表
CREATE TABLE human(
    id VARCHAR(12) PRIMARY KEY,
    sname VARCHAR(12),
    age INT,
    sex CHAR(1)
);
INSERT INTO human VALUES('H001','小王',27,'1');
INSERT INTO human VALUES('H002','小明',24,'1');
INSERT INTO human VALUES('H003','张慧',28,'0');
INSERT INTO human VALUES('H004','李小燕',35,'0');
INSERT INTO human VALUES('H005','王大拿',29,'1');
INSERT INTO human VALUES('H006','周强',36,'1');
 
// 建立车辆信息表
CREATE TABLE car(
    id VARCHAR(12) PRIMARY KEY,
    mark VARCHAR(24),
    price NUMERIC(6,2),
    hid VARCHAR(12),
    CONSTRAINT fk_human FOREIGN KEY(hid) REFERENCES human(id)
);
INSERT INTO car VALUES('C001','BMW',65.99,'H001');
INSERT INTO car VALUES('C002','BenZ',75.99,'H002');
INSERT INTO car VALUES('C003','Skoda',23.99,'H001');
INSERT INTO car VALUES('C004','Peugeot',20.99,'H003');
INSERT INTO car VALUES('C005','Porsche',295.99,'H004');
INSERT INTO car VALUES('C006','Honda',24.99,'H005');
INSERT INTO car VALUES('C007','Toyota',27.99,'H006');
INSERT INTO car VALUES('C008','Kia',18.99,'H002');
INSERT INTO car VALUES('C009','Bentley',309.99,'H005');


SELECT human.sname AS 车主,car.mark AS 车辆 FROM human,car WHERE human.id = car.hid;
SELECT human.sname AS 车主,car.mark AS 车辆 FROM human INNER JOIN car WHERE human.id = car.hid;


//多对多
// 建立学生表
CREATE TABLE student(
    id VARCHAR(10) PRIMARY KEY,
    sname VARCHAR(12),
    age INT,
    sex CHAR(1),
    class VARCHAR(6)
);
INSERT INTO student VALUES('S0001','王军',20,1,'c101');
INSERT INTO student VALUES('S0002','张宇',21,1,'c101');
INSERT INTO student VALUES('S0003','刘飞',22,1,'c102');
INSERT INTO student VALUES('S0004','赵燕',18,0,'c103');
INSERT INTO student VALUES('S0005','曾婷',19,0,'c103');
INSERT INTO student VALUES('S0006','周慧',21,0,'c104');
INSERT INTO student VALUES('S0007','小红',23,0,'c104');
INSERT INTO student VALUES('S0008','杨晓',18,0,'c104');
INSERT INTO student VALUES('S0009','李杰',20,1,'c105');
INSERT INTO student VALUES('S0010','张良',22,1,'c105');
 
// 建立课程表
CREATE TABLE course(
    id VARCHAR(10) PRIMARY KEY,
    sname VARCHAR(12),
    credit NUMERIC(2,1),
    teacher VARCHAR(12)
);
INSERT INTO course VALUES('C001','Java',3.5,'李老师');
INSERT INTO course VALUES('C002','高等数学',5.0,'赵老师');
INSERT INTO course VALUES('C003','JavaScript',3.5,'王老师');
INSERT INTO course VALUES('C004','离散数学',3.5,'卜老师');
INSERT INTO course VALUES('C005','数据库',3.5,'廖老师');
INSERT INTO course VALUES('C006','操作系统',3.5,'张老师');
 
// 建立选修表
CREATE TABLE sc(
    sid VARCHAR(10),
    cid VARCHAR(10)
);
 
ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY(sid,cid);
ALTER TABLE sc ADD CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(id);
ALTER TABLE sc ADD CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCES course(id);
 
INSERT INTO sc VALUES('S0001','C001');
INSERT INTO sc VALUES('S0001','C002');
INSERT INTO sc VALUES('S0001','C003');
INSERT INTO sc VALUES('S0002','C001');
INSERT INTO sc VALUES('S0002','C004');
INSERT INTO sc VALUES('S0003','C002');
INSERT INTO sc VALUES('S0003','C005');
INSERT INTO sc VALUES('S0004','C003');
INSERT INTO sc VALUES('S0005','C001');
INSERT INTO sc VALUES('S0006','C004');
INSERT INTO sc VALUES('S0007','C002');
INSERT INTO sc VALUES('S0008','C003');
INSERT INTO sc VALUES('S0009','C001');
INSERT INTO sc VALUES('S0009','C005');

//普通方式

SELECT * FROM student WHERE student.id NOT IN (SELECT sid FROM sc);
SELECT * FROM course WHERE course.id NOT IN (SELECT cid FROM sc);
//缺点：实际上是创建了两张表的笛卡尔积，所有可能的组合都会被创建出来。在笛卡尔连接中，
//在上面的例子中，如果有1000顾客和1000条销售记录，这个查询会先产生1000000个结果，然后通过正确的 ID过滤出1000条记录。 
//这是一种低效利用数据库资源，数据库多做100倍的工作。 在大型数据库中，笛卡尔连接是一个大问题，对两个大表的笛卡尔积会创建数10亿或万亿的记录。


//为了避免创建笛卡尔积，应该使用INNER JOIN ：


//关联方式

SELECT student.* FROM student LEFT JOIN sc ON student.id=sc.sid LEFT JOIN course ON course.id = sc.cid WHERE course.sname IS NULL;
SELECT course.* FROM course LEFT JOIN sc ON course.id=sc.cid LEFT JOIN student ON student.id = sc.sid WHERE student.sname IS NULL;

//（left join 左连接，左表所有数据 拼接 右表符合on条件的数据。)


//SQL约束(主键约束 primary key、外键约束 foreign key、唯一约束unique 、CHECK约束)


create table t_group (   
    id int not null,   
    name varchar(30),   
    primary key (id)   
);  
 
insert into t_group values (1, 'Group1');   
insert into t_group values (2, 'Group2');
 
create table t_user (   
    id int not null,   
    name varchar(30),   
    groupid int,   
    primary key (id),   
    foreign key (groupid) references t_group(id) on delete cascade on update cascade  
);  
 
insert into t_user values (1, 'qianxin', 1); --可以插入   
insert into t_user values (2, 'yiyu', 2);    --可以插入   
insert into t_user values (3, 'dai', 3);    --错误，无法插入，用户组3不存在，与参照完整性约束不符 
 
insert into t_user values (1, 'qianxin', 1);   
insert into t_user values (2, 'yiyu', 2);   
insert into t_user values (3, 'dai', 2);   
delete from t_group where id=2;              --导致t_user中的2、3记录级联删除   
update t_group set id=2 where id=1;          --导致t_user中的1记录的groupid级联修改为2