数据库基础-MySql8.0(第四篇)-多表查询和子查询

MySql基础篇

多表设计:

在存储不同的一类信息时
减少数据冗余

表与表之间如何关联

数据库设计范式:
1.列的原子性(不可再分)
2.要有主键(唯一的标识列),表中其他信息都依赖于主键
3.一张表存储一类信息,关联其他表,消除数据冗余

学生信息表:
	学号,姓名,性别,生日,手机号,年级ID,注册时间
	
年级表:
	年级ID,年级名称

-- 老师信息表
	工号,姓名,性别,年级ID

课程信息表

-- 创建年级表
CREATE TABLE grade(id INT PRIMARY KEY AUTO_INCREMENT,
                   NAME VARCHAR(10))
                   
-- 创建学生表
CREATE TABLE student(
               num INT PRIMARY KEY AUTO_INCREMENT,
               NAME VARCHAR(10) NOT NULL,
               sex CHAR(1),
               birthday DATE,
               phone CHAR(11),
               grade_id INT, -- 外键约束
               reg_time DATETIME
)                  
     
     
-- 弱关系,在表与表关联时,没有任何约束
-- 强关系,表与表之间存在约束关系         
                   
 -- 修改表,添加年级外键约束   
 -- 外键约束:外键与另一个表中的主键对应的
 ALTER TABLE student ADD CONSTRAINT grade_fk FOREIGN KEY(grade_id)  REFERENCES grade(id)  
 
 
 /*
 表与表之间的联系关系
	一对一:一个人有一个学号,一个学号对应一个人
	一对多:一个人属于一个年级
	多对一:多名学生属于一个年级
        多对多:一个人可以选多门课程,一个课程可以被多名学生选
 */
 
 CREATE TABLE course(id INT PRIMARY KEY AUTO_INCREMENT,
			NAME VARCHAR(10)	
			)
 -- 多对多设计一个关系表来存储学生与课程关系
 CREATE TABLE student_course(
	stu_num INT,
	course_id INT,
	CONSTRAINT stu_fk FOREIGN KEY(stu_num) REFERENCES student(num),
	CONSTRAINT course_fk FOREIGN KEY(course_id) REFERENCES course(id)
 )

子查询

-- 子查询:出现在其他语句(insert  update  delete  select) 中的select语句,成为子查询或内查询
-- 外部的查询语句,称为主查询或外查询

-- 在insert语句中使用子查询
INSERT INTO stu SELECT * FROM student 

-- 在update中使用子查询
UPDATE student SET sex='男' WHERE num IN (SELECT num FROM stu WHERE score>80)

-- 在delete语句中使用子查询
DELETE FROM student WHERE num IN(SELECT num FROM stu WHERE score>=90)



-- 查询语句中使用子查询
-- select后面:仅仅支持标量子查询(一行一列)
SELECT (
	SELECT ts.num
	FROM student ts
	WHERE ts.num = t.num
),t.name FROM student  t

-- 在where后面使用列子查询
SELECT * FROM student WHERE score IN (SELECT score FROM student WHERE score>60)

-- 在where后面使用行子查询  同时满足多个条件
SELECT * 
FROM student 
WHERE (num,score)=(
		SELECT MIN(num),MAX(score)
		FROM student
)

-- 在from后使用子查询  只支持表子查询(多行多列)
-- 把一个查询出来的结果,可以当一只那个临时表
SELECT *
FROM(
     SELECT COUNT(*)c ,sex
     FROM student
     GROUP BY sex) t
WHERE t.c >= 2