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：该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行;