SQL复习（二）-- 数据查询DQL & 约束 & 多表间的关系 & 数据库设计及三大范式

1 DQL（数据查询语言）

1.1 语法

select
    查询的列, 多列使用逗号分隔
[from
    表名
where
    过滤条件
group by
    分组查询
having
    分组后的过滤
order by
    排序
limit
    分页];

1.2 简单的条件查询 

① 查询所有数据

select * from tb_student;
select id, gender, age, birthday, score, regtime, name from student;

② 带条件的查询，可以使用的条件有: >, >=, <, <=, =, !=, <>, between...and, and, or, not, is null, like, in

-- 查询编号为1004的学生
select name,birthday from tb_student where id = 1004;

-- 查询年龄大于18的学生
select * from tb_student where age>18;

-- 查询姓名为张三的成绩
select name ,age from tb_student where name ="张三";

-- 查询18-20岁的学生  between...and...
select * from tb_student where age>=18 and age<=20;
select * from tb_student where age between 18 and 20;

-- 查询除编号1003以外的学生 
select * from tb_student where id != 1003;
select * from tb_student where id <> 1003;

-- 查询编号为1005 或者年龄为18的学生
select * from student where id=1005 or age=18;

-- 查询编号（不）为1003、1005、1007的信息    in
select * from tb_student where id in (1003,1005,1007);
select * from tb_student where id not in (1003,1005,1007);

-- 模糊查询 like ，通配符%（任意个任意字符）  _（任意一个字符）
-- 查询所有名字含亚的学生信息
select * from tb_student where name like ‘张%’;

-- 查询所有第二个字为季的学生信息
select * from tb_student where name like ‘_季%’;

-- 查询参加考试的学生
select * from tb_student where score is not null;
select * from tb_student where not score is null;

③ select子句

select子句的作用是选择要查询的列. 还有以下的功能:

• 可以在select子句中进行运算;
• 可以使用distinct关键字去除重复行数据;
• 可以给列起别名;

-- 运算  
-- null不参与运算, 结果都是null  
select name, score, score+10 from student; 
 
-- 可以给列定义别名, 使用as关键字, 可以省略  
-- 别名最好不要出现特殊字符, 中文也不建议使用  
select name, score, score+10 as new_score from student;  
select name, score, score+10 new_score from student;  
select name, score, score+10 '成 绩' from student;  

-- ifnull函数, 类似于java中的三目运算符, 用于处理空值null, 使用给定的值进行替换null  
select name, score, IFNULL(score,0)+10 new_score from student;  

-- 查询所有学生的年龄  
select distinct age from student; 

④ 排序

使用order by 子句可以进行结果的排序，需要配合关键字使用。升序（默认）：ASC（ascending）；降序：DESC（descending）

-- 查询所有的学生信息，按照id进行升序排序
select * from tb_student order by id asc;

-- 查询年龄为20的学生信息，按照id降序排序
select * from tb_student where age=20 order by id desc;

-- 按照别名排序
-- 查询所有的学生的新成绩，按照新成绩排名
select name,score+10 new_score from tb_student order by new_score desc;

-- 根据第几列排序，不常用
select * from tb_student order by 6;

-- 查询所有学生的信息，按照年龄降序排序，如果年龄相同，再按照id降序排序
select * from tb_student order by age desc,id desc;

-- rand()函数，生成随机数，范围[0,1)
-- 配合rand函数可以进行数据随机排序，打乱数据
select * from tb_student order by rand();

⑤ 分组函数

分组函数用于对查询结果进行统计, 返回结果是一个值, 一共有5个:

• count(): 用于计数
• sum(): 用于求和
• max(): 求最大值
• min(): 求最小值
• avg(): 求平均数

-- 使用分组函数时，null不参与统计
-- 统计所有学生的人数
select count(*) from tb_student;
select count(id) from tb_student;
select count(score) from tb_student;

-- 查询总分,最低分数和最高分数以及平均分数  
select sum(score), min(score), max(score), avg(score) from tb_student; 

⑥ 分组查询

分组查询可以使用group by子句实现, 分组查询后, 需要注意以下几点:

• select子句只能查询分组字段和分组函数;
• 可以使用where子句在分组前进行条件过滤;
• 可以使用having子句在分组后进行过滤.

-- 按照性别进行分组，分别统计男女同学的人数和平均成绩
select gender,count(*) cnt,avg(score) avg_score from tb_student group by gender;

-- 按照性别进行分组，分别统计男女同学的人数和平均成绩，排除成绩低于70的学生信息
select gender,count(*) cnt,avg(score) avg_score from tb_student where score>70 group by gender;

-- where和having的区别在 having中可以使用分组函数，而where不能
-- 按照年龄进行分组，分别统计不同年龄的同学的人数和平均成绩，排除人数小于2的分组信息
select age,count(*) cnt,avg(score) avg_score from tb_student group by age having count(*)>=2;
select age,count(*) cnt,avg(score) avg_score from tb_student group by age having cnt>=2;

⑦ 分页查询

在MySQL中，可以通过limit关键字，实现分页查询！！！limit必须出现在SQL的末尾

两个参数 1）起始索引（从0开始）2）查询的记录数   

-- 每页显示两条学生信息，完成显示
-- page：第几页
-- size：每页显示几条数据
-- select * from tb_student order by id limit (page-1)*size,size;
-- 第一页
select * from tb_student order by id limit 0,2;
-- 第二页
select * from tb_student order by id limit 2,2;
-- 第三页
select * from tb_student order by id limit 4,2;

2 约束

Constraints, 约束, 用来对数据库表格的数据进行约束, 保证数据的完整性, 一致性和正确性. 对约束的操作属于DDL的范畴, 但其实约束的操作针对的是表格. 我们可以在创建表格的同时添加约束, 也可以在表格创建成功后再去操作约束. 在MySQL中, 约束一共有四种

2.1 非空约束（NOT NULL）

     表示一个字段的数据不能为null值 当前字段为必填项

-- 创建表格时设置非空值，并设置默认值
drop table if exists stu;
create table stu(
    id int(10),
    name varchar(10) not null default 'demo'
);

-- 建表后添加非空约束
drop table stu;  
create table stu (  
    id int,  
    name varchar(20)  
);  
alter table stu modify name varchar(20) not null; 

2.2  唯一约束

表示一个表格中某行数据的某个字段唯一, 与其他行不重复. 在MySQL中, null不做唯一限制.

-- unique 唯一约束
drop table if exists stu;
create table stu(
    id int(5),
    name varchar(20) not null,
    email varchar(20) unique
);

-- 在表格创建成功后添加唯一约束
drop table stu;
create table stu(
    id int,
    name varchar(20) not null,
    email varchar(20)
);

alter table stu modify email varchar(50) unique;

--删除唯一约束
drop index email on stu;

2.3  主键约束  primary key

非空并唯一, 用于唯一标识当前行数据. 某个字段既不能为null值, 也不允许和其他行的该字段重复.

-- 一个表只能有一个主键
-- 多个字段做主键称为联合主键

create table stu (  
    id int primary key,  
    name varchar(20) not null,  
    email varchar(50)  
);  
  
-- 修改表的阶段添加主键  
drop table stu;  
create table stu (  
    id int,  
    name varchar(20) not null,  
    email varchar(50)  
);  
alter table stu modify id int primary key; 
 
-- 删除主键  
alter table stu drop primary key;  
  
-- 主键的自增, 使用关键字auto_increment  
-- 该字段必须是数字      
-- 表示该字段会从1开始自动递增, 添加的时候无需赋值  
drop table stu;  
    create table stu (  
    id int primary key auto_increment,  
    name varchar(20) not null,  
    email varchar(50)  
);  

-- 如果创建时忘记添加主键自增
-- alter table 表名 modify 字段 类型 auto_increment primary key
  
insert into stu values (null, 'demo', 'wuyw2020@163.com');  
insert into stu values (default, 'demo', 'wuyw2020@163.com'); 

2.4 外键约束（foreign key）

 约束表和表之间的关系

-- 创建班级表
create table cls(
    id  int  primary key auto_increment,
    name varchar(10) not null,
    room varchar(5)
);

-- 录入班级数据
-- 多行输入：shift + Alt + 鼠标左键 选中多行，同时输入（Navicat、idea中可用）
insert into cls values
    (default,'软件工程1601','1-101'),
    (default,'软件工程1602','1-102'),
    (default,'软件工程1603','1-103');

-- 主表（父表） 被依赖的表，班级表
-- 从表（子表） 依赖主表的表，学生表
-- 外键添加到从表中，需要依赖主表的唯一字段，通常会使用主键
create table stu (
	id int primary key auto_increment,
	name varchar ( 20 ) not null,
	email varchar ( 50 ) unique,
	cid int,
	-- ① foreign key(cid) references cls(id) 
    -- ② constraint fk_cid foreign key( cid ) references cls ( id ) 
);

-- 在修改表的时候添加外键
alter table stu add constraint fk_cid foreign key(cid) references cls(id);

删除外键 

-- alter table 表名 drop foreign key 外键名;
-- 查看建表语句，可以找到外键名
-- ①的方式外键名默认，可以查询建表语句查看外键名
-- ②的方式创建外键可以自定义外键名
show create table stu;

-- 查询结果
CREATE TABLE `stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) NOT NULL,
  `email` varchar(50) DEFAULT NULL,
  `cid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`),
  KEY `cid` (`cid`),
  CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `cls` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

alter table stu drop foreign key stu_ibfk_1;

外键的级联操作

默认情况下，添加外键后，被依赖的字段不能修改和删除

    如果需要删除，可以先删除从表相关的数据，然后再删除主表数据

    或者不删除从表中的数据，先删除外键约束，再删除主表数据

 mysql中的级联操作，包括修改和删除，已删除为例介绍

级联删除：当删除主表数据时，自动删除从表数据

CREATE TABLE stu (
	id INT PRIMARY KEY auto_increment,
	NAME VARCHAR ( 20 ) NOT NULL,
	email VARCHAR ( 50 ) UNIQUE,
	cid INT,
	constraint fk_cid foreign key(cid) references cls(id) on delete cascade
);

级联设空：删除主表数据时，会将从表的外键字段设置为null

CREATE TABLE stu (
	id INT PRIMARY KEY auto_increment,
	NAME VARCHAR ( 20 ) NOT NULL,
	email VARCHAR ( 50 ) UNIQUE,
	cid INT,
	constraint fk_cid foreign key(cid) references cls(id) on delete set null
);

3 表和表之间的关系 

 一对一、一对多、 多对多

4 数据库设计及三大范式

范式, 英文单词对应 Normal Form, 用于规范数据库的设计. 实际工作中, 表格的设计只要达到第三范式就已经可以满足生产需要了. 范式一定要灵活使用, 不要死板教条.

4.1 第一范式

所谓第一范式（1NF）是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。MySQL中自动遵守。

一句话总结: 第一范式要求列保证原子性.通俗理解，一张表中字段名不重复

4.1 第二范式

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

一句话总结: 第二范式用于限制多对多关系. 使用中间表配合联合主键.

4.1 第三范式

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）.另外，根据情况适当增加重复字段，出现反第三范式的情况。

一话总结: 第三范式用于限制一对多(多对一)关系. 使用外键.