MySQL复杂操作
============================================================================================================
*数据库约束
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
- DEFAULT - 规定没有给列赋值时的默认值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
- CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。
create table class(
id int primary key auto_increment,
name varchar(20)
);
create table student (
id int unique,
sn int primary key auto_increment,
name varchar(20) not null,
mail varchar(20) default 'qq',
class-id int,
foreign key (class_id) references class(id),
sex varchar(1) comment '性别', -- 字段说明
check (sex='男' or sex='女')
-- MySQL使用时不报错，但忽略该约束
);
*表的设计
- 三大范式：一对一、一对多、多对多
*新增(create)——插入查询结果
insert into table1_name (column 1,2,....) select (column 1,2,....) from table2_name ;
// 创建一张用户表，设计有name姓名、email邮箱、sex性别、mobile手机号字段——可以将学生表student中的已知数据-查询相同数据-再插入到创建的用户表中；
*查询（Retrieve)
- 查询语句中关键字的顺序： from > on> join > where > group by > with > having >select > distinct > order by > limit；
1.聚合查询
聚合函数count、sum、avg、max、min
①每个组函数接收一个参数；
②默认情况下，组函数忽略列值为null的行，不参与计算；
③有时，会使用关键字distinct剔除字段值重复的条数；
// 注意：
1）当使用组函数的select语句中没有group by子句时，中间结果集中的所有行自动形成一组，然后计算组函数；
2）组函数不允许嵌套，例如：count(max(…))；
3）组函数的参数可以是列或是函数表达式；
4）一个SELECT子句中可出现多个聚集函数。
应用示例：
//成绩
create table exam(
id int,
name varchar(20),
chinese decimal(3,1),
math decimal(3,1),
english decimal(3,1)
);
// 计算学生人数（数据行数）
select count(*) from exam;
// 计算这个9个人的数学总分和数学平均分
select sum(math) from exam;
select avg(math) from exam;
// 求语文最高分和最低分
select max(chinese),min(chinese) from exam;
GROUP BY子句 ——分组查询
- GROUP BY 子句可以对指定列进行分组查询;
- 条件：SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。
select column1, sum(column2), .. from table group by column1,column3;
// 应用示例：
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
role varchar(20) not null,
salary numeric(11, 2)
);
insert into emp(name, role, salary)
values ('老马哥', '服务员', 1000.20),
('化腾', '游戏陪玩', 2000.99),
('孙悟空', '游戏角色', 999.11),
('猪无能', '游戏角色', 333.5),
('沙和尚', '游戏角色', 700.33),
('老王', '董事长', 12000.66);
HAVING
- GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING；
// 最分组后的结果再进行筛选，得到平均工资不低于1500的角色、max(salary),min(salary),avg(salary)；
2. 联合查询
- 关联查询可以对关联表使用别名。
// 应用示例：
内连接
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件
例1： 得出李四同学的班级名称（学生表、班级表）
例2： 查询王五同学的各科成绩 （学生表、成绩表、课程表）
select sco.score,cou.name from student stu
join score scoon stu.id=sco.student_id
join course cou on sco.course_id=cou.id
and stu.name='王五';
例3：查询三个学生的总成绩（学生表、成绩表）
SELECT
stu.sn,
stu.name,
sum( sco.score )
FROM
student stu join score sco on stu.id=sco.student_id
GROUP BY
sco.student_id;
外连接
- 外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。
-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;
-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;
例： 查询所有同学的成绩，如果该同学没有成绩也需要显示出来
自连接
- 自连接：同一张表连接自身进行查询；
例1：查询某同学Java的成绩比计算机基础分数低的分数情况
SELECT
s1.*,s2.score
FROM
score s1
JOIN score s2 ON s1.student_id = s2.student_id
AND s1.score < s2.score
AND s1.course_id = 1
AND s2.course_id = 3;
例2：查询Java比计算机基础分数低的同学及分数
select stu.name,s1.score java,s2.score 计算机基础
from score s1
join score s2 on s1.student_id=s2.student_id
join student stu on stu.id=s1.student_id
join course c1 on c1.id=s1.course_id
join course c2 on c2.id=s2.course_id
and s1.score<s2.score and c1.name='java'
and c2.name='计算机基础';
子查询/嵌套查询
- 子查询：指一个查询语句包含其他查询语句；limit关键字不能再子查询中使用；
1）在where条件语句中使用子查询：
例1：查询和王五同班的同学；
例2：查询Java和C语言课程的成绩；
// 使用 in 关键字 就也可以使用not in 关键字查询
// exists关键字——将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或 FALSE）来决定主查询的数据结果是否得以保留。
// 带 ANY或 ALL的子查询语句：
- <ANY 子查询（匹配任一返回真） ：表示条件满足 小于 子查询中任何一个值 就会返回emp的一条记录，相当于筛选出小于子查询最大值的记录。
- <ALL 子查询（匹配所有返回真） ：表示条件满足 小于 子查询中所有的值 才会返回emp的一条记录，相当于筛选出子查询最小值的记录。其他操作符功能类似（=,<>,<,>,<=,>=）。
2）在from语句中使用子查询——把一个子查询当作一个临时表使用：
所有创建通过查询得出的结果表都是临时表，如：计算实验班学生的平均成绩
// 通过查询的临时表 从from中再次查询比临时表成绩高的成绩数据
合并查询
- 在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。
-
union：用于取两个结果集的并集：
//会自动去掉结果集中的重复行 -
union all：该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行;