Day17 PythonWeb全栈课程课堂内容

1. 前提

1.1 创建表 students

/* 创建表 */
CREATE TABLE `students`(
	`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20),
	`age` TINYINT(3) UNSIGNED DEFAULT 18,
	`high` FLOAT(5,2) ,
	`gender` ENUM ('male','female','secret') DEFAULT "secret",
	`cls_id` INT,
	`id_del` INT
)ENGINE=INNODB DEFAULT CHARSET=utf8;

/* 插入数据 */
insert into students values(1,'Sam',28,1.81,'male',2,1);
insert into students values(2,'John',26,1.75,'male',3,0);
insert into students values(3,'Bob',27,1.87,'secret',1,0);
insert into students values(4,'Mike',29,1.80,'male',1,0);
insert into students values(5,'Lily',30,1.60,'female',1,0);
insert into students values(6,'Lisa',27,1.71,'female',2,1);
insert into students values(7,'Jame',25,1.78,'male',3,0);
insert into students values(8,'Coco',28,1.65,'female',3,0);

1.2 创建表 classes

/* 创建表 */
CREATE TABLE `classes`(
	`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

insert into classes values (1,'class_A');
insert into classes values (2,'class_B');
insert into classes values (3,'class_C');

2. 表连接

• 当查询结果的列来源于多张表格时，需要多张表连接成一个打的数据集，在选择合适的列返回MySQL。

2.1 内连接

• 仅选出两种表中互相匹配的记录。

select * from 表A inner join 表A on 表A.列=表A.列

• 直接将表students和表classes进行内连接(笛卡尔积)。

SELECT * FROM `students` AS s INNER JOIN `classes`;

---

• 练习1：只有 students.cls_id =classes.id 条件

SELECT * FROM `students` AS s INNER JOIN `classes` AS c on s.`cls_id` =c.`id`;

• 练习2：显示每位同学所属于的班级的名称

SELECT s.name, c.name FROM `students` AS s INNER JOIN `classes` AS c on s.`cls_id` =c.`id`;

• 练习3：显示学生的所有信息，但只显示班级名称

SELECT s.*, c.name FROM `students` AS s INNER JOIN `classes` AS c on s.`cls_id` =c.`id`;

• 练习4：将班级名称显示在第一列

SELECT c.name,s.* FROM `students` AS s INNER JOIN `classes` AS c on s.`cls_id` =c.`id`;

• 练习5：查询有能够对应班级的学生以及班级信息，按照班级进行排序

SELECT c.name,s.* FROM `students` AS s INNER JOIN `classes` AS c on s.`cls_id` =c.`id` order by s.cls_id;

• 练习6：查询有能够对应班级的学生以及班级信息，按照班级进行排序，当同一个班级时，按照学生的id进行从小到大排序。

SELECT c.name,s.* FROM `students` AS s INNER JOIN `classes` AS c on s.`cls_id` =c.`id` order by s.cls_id,s.id asc;

2.2 外连接

2.2.1 左连接

• 查询的结果为两个表匹配到的数据，左表持有的数据，对于右表中不存的数据使用null填充

select * from 表A left join 表B on 表A.列 = 表B.列;

• 练习1：students表左连接classes表

SELECT * FROM `students` as s LEFT JOIN classes as c ON s.`cls_id`=c.`id` WHERE c.`id`;

• 练习2：查询 s.is_del=1 并且 s.name=“Lisa” 的数据

SELECT * FROM `students` AS s LEFT JOIN `classes` AS c ON s.`cls_id`=c.`id` WHERE s.`id_del`=1 AND s.name='Lisa';

2.2.2 右连接

• 查询结果为两个表匹配到的数据，右表持有的数据，对于左表中不存在的数据使用null填充。

select * from 表A right join 表B on 表A.列 = 表B.列;

SELECT * FROM `students` AS s RIGHT JOIN `classes` AS c ON s.`cls_id`=c.`id`;

3. 子查询

• 某些情况下，当进行查询的时候，需要的条件是另外一个 select 语句的结果，这个时候，就要用到子查询。

/* 案例：查询最高身高的男生 */



-- 首先已知最高的男生身高为1.81;
select * from `students` where `high`=1.81;
/*
+----+------+------+------+--------+--------+--------+
| id | name | age  | high | gender | cls_id | id_del |
+----+------+------+------+--------+--------+--------+
|  1 | Sam  |   28 | 1.81 | male   |      2 |      1 |
+----+------+------+------+--------+--------+--------+
*/

select max(high) from `students` where `gender`=1;
/*
+-----------+
| max(high) |
+-----------+
|      1.81 |
+-----------+
*/

-- 显示最高男生身高以及对应的姓名 报错
SELECT NAME,MAX(high) FROM `students` WHERE `gender`=1;
/*
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'day17.students.name'; this is incompatible with sql_mode=only_full_group_by
*/


/* 使用子查询 */
-- 1.查询所有学生的身高
select s.name,s.high from `students` as s;

-- 2.指定条件查询最高的男生的升高
select s.name,s.high from `students` as s where s.`high`=(select max(high) from `students` where `gender`=1); 

• 剖析

1. 首先子语句(SELECT MAX(high) FROM students WHERE gender=1)= 1.81
2. 然后where s.high=1.81得出结果。

• 练习1：查询身高高于平均身高的学生

SELECT * FROM `students` WHERE `high`>(SELECT AVG(high) FROM `students`);

• 练习2：查询学生班级号cls_id能够对应的学生信息

SELECT * FROM `students` WHERE `cls_id` IN (SELECT id FROM `classes`);

• 查询最大年龄的女生的id

SELECT MAX(age) FROM `students` WHERE `gender`=2;

SELECT * FROM `students` WHERE `age`=(SELECT MAX(age) FROM `students` WHERE `gender`=2);

---

4. 自关联

• 简单的理解为自己与自己进行连接查询。

5. 外键

5.1 介绍

• MySQL的外键约束(FOREIGN KEY)是表的一个特殊字段。对于两个具有关联关系的表而言，相关联字段中的主键所在表就是主表(父表)，外键所在的表就是从表(子表)。

• 注意：主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。

5.2 语法

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

直接创建含有外键的student

CREATE TABLE `class`(
	`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `student`(
	`sid` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`sname` VARCHAR(30),
    `cid` INT(4) NOT NULL,
	CONSTRAINT fk_id FOREIGN KEY (cid) REFERENCES class (id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

案例：

• 创建表student和表class;其中class为主表，student为从表。

CREATE TABLE `class`(
	`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE `student`(
	`sid` INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	`sname` VARCHAR(30),
	`cid` INT(4) NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

• 在创建 student表时将其 cid 设置为外键。

• **注意：**从表的外键关联必须是主表的主键，并且主键与外键的类型必须保持一致。

5.3 在修改表时添加外键约束

-- alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名);

alter table student add constraint fid foreign key(cid) references class(id);

• **注意：**主表需存在时创建从表

5.4 删除外键约束

ALTER TABLE student DROP FOREIGN KEY fid;

5.5 删除顺序

• 在有外键约束的情况下，删除class；会报错，表示有外键约束。

drop table class;

• 总结删除表时，先删除从表，最后删除主表。

5.6 外键具有限制性

/* 案例 */

-- class表添加数据
insert into class values (1,'class-A'),(2,'class-B');

-- student表添加数据
insert into student values (1,'Sam',1),(2,'Bob',2);

-- 学生的cid=3能否插入
insert into student values (3,'John',3);

• 不可以插入。外键中的数据，要属于主表中的主键。

• 删除class当中的id=2的数据，此时也是不能删除的。因为student从中有外键约束。