一、where子句
select 字段 from 表名 where 子句; 在where子句中可以使用关系运算符、逻辑运算符，当子句的条件为真的数据才会显示对应的字段数据
where子句可以是
关系运算符
-
= != > < >= <=
注意：因为在SQL中无需、也不能定义变量，因此=运算符只能用来判断关系是否相等
逻辑运算符
-
and or not
-
&& || ！ 还可以继续使用
注意：在SQL中 not(!) 比关系运算符的优先级要低
特殊条件
-
is null\is not null
-
对于null状态只能通过is null、is not null来判断该字段是否为空状态，在SQL中null不是一个特定的值，而是一种状态，因此不能直接使用关系运算符比较
-
select * from 表名 where 字段 is (not) null;
-
-
-
between a and b
-
当字段的值出现在[a,b]之间时为真，如果使用关系、逻辑配合也可以达到同样效果
-
select * from 表名 where 字段 between a and b;
-
-
-
in(a,b,c...)
-
当字段的值出现在in后面的数值列表中，为真
-
select * from 表名 where 字段 in(a,b,c...);
-
-
-
like '%str'
-
进行模糊查询，'%'可以通配任意多个字符，'-'可以通配任意一个字符
注意 ：一般字符型字段比较适合模糊查询，但是数值型也可以，数据库会自动转换成字符串比较
-
-
子查询
-
把一条select语句的查询结果作为另一条select语句的数据源
-
min（字段） 查询出该字段的最小值
注意：min组函数不能直接用在where子句中
-
select first_name,salary
from s_emp
where salary > (select min(salary) from s_emp); -
二、排序
-
select 字段 from 表名 order by 排序字段[asc\desc]；
-
asc 升序（默认） desc 降序
-
MySQL排序中把null当最小值，Oracle中当作最大值
与where子句配合：
select 字段
from 表名
where 条件
order by 排序字段[asc\desc]；
-
三、多表查询（连接查询）
-
查询每个员工所属部门名
-
当需要的数据分布在不同的表中，就需要把多张表进行多表查询，如果无任何限制条件地进行多表查询，会产生“笛卡尔积”，有海量的无效数据，需要配合where子句进行多表查询，也称为交叉连接查询
select first_name,name
from s_emp,s_dept
where s_emp.dept_id = s_dept.id;
注意：多表中的字段名可能重名，需要 表名.字段名 进行区分
注意：交叉连接查询是先产生“笛卡尔积”，然后再从海量的结果中根据where条件筛选出合适的结果，如果“笛卡尔积”非常大时，交叉连接查询的效率就很低
根据连接方式和where子句，进行不同的连接查询：
-
内连接：
-
主要通过设置连接条件的方式，来移除查询结果中无效的数据行的交叉连接结果，就是消除了无效的“笛卡尔积”，也称为“等值连接”
select 字段
from 表1 inner jion 表2 on 连接条件;
select first_name,name
from s_emp inner join s_dept
on s_emp.dept_id = s_dept.id;
-
-
外连接：
-
左连接：left join
-
以表1为主表，表2为副表，会把表1的数据都查询出来，表2只查询符合连接条件的数据
-
把表1的数据只显示不符合连接条件的数据
-
-
右连接：right join
-
类似左连接
-
-
全连接：full outer join
-
注意：MySQL中不支持 full outer join 连接
但是可以用 union 替代全连接的效果
(左连接1) union (右连接1) = 全连接1
(左连接2) union (右连接2) = 全连接2
-
-
四、取别名
-
一次查询一张表无法查询出结果，有时需要同一张表查询多次，进行自连接查询
-
自连接的表名是相同的，所以需要取别名进行区分，或者表名太长也可以通过取别名来简化表名
-
如果两张表的字段数据有重复，需要通过排重筛选，以及需要通过内连接解决“笛卡尔积”问题
-
查询出所有领导的名字和id
#方法1
select distinct s2.id,s2.first_name
from s_emp as s1 inner join s_emp as s2
on s1.manager_id = s2.id;
#方法2
select id,first_name
from s_emp
where id in (select manager_id from s_emp);
-
五、分组查询
把表中的数据按照分组标准划分成不同的组
-
select 分组标准字段或组函数处理过的字段
from 表名
group by 字段;
#注意：在分组查询语句中，select后面字段要么是分组标准的字段、要么是经过合适的组函数处理后的字段group_concat（字段a） 把每个分组中的字段a的所有值显示
练习：分组查询出相同部门的员工姓名、部门id
-
select group_concat(first_name),dept_id
from s_emp
group by dept_id; -
常用组函数：
-
min(字段) 求该字段分组后的最小值
-
max(字段) 求该字段分组后的最大值
-
count(字段) 求该字段分组后的数据数量
-
sum(字段) 求该字段分组后数据的和
-
avg(字段) 求该字段分组后的平均值
-
group_concat(字段) 把该字段分组后每个组中的所有值显示
-
-
select group_concat(first_name),dept_id,count(first_name),max(salary)
from s_emp
group by dept_id;注意：组函数不能出现在where条件中
如何有条件地过滤分组后的数据
select 分组标准字段或组函数处理过的字段
from 表名
group by 字段
having 过滤条件;
练习：查询出平均工资高于1100的部门id以及平均薪资
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>1100;
where和having的差异
-
一般情况下，where用于过滤分组前数据行，having用于过滤分组后的数据
-
where子句中不能使用组函数，而having可以
六、复杂的select语句的顺序要求：
select [distinct]字段、分组标准字段、组函数
from 表名 [连接方式] [表名] [as 别名]
where/on 条件/连接标准
group by 分组标准字段
having 分组过滤条件[组函数]
order by 要排序的字段[desc/asc]
练习：查询各个部门工资高于1000的员工的部门的平均薪资，只降序显示平均薪资高于1100的部门id、部门员工名、平均薪资
select dept_id,group_concat(first_name),avg(salary)
from s_emp
where salary > 1000
group by dept_id
having avg(salary)>1100
order by avg(salary) desc;
七、表的三范式
1NF：
-
要保证每个字段具有原子性不可再拆分、没有重复字段
2NF
-
表中要有主键，且所有字段都不能与主键部份依赖，如果不满足可以通过拆表来满足
3NF
-
确保每个字段都与主键直接相关，而不是间接相关，如果是可以通过消除冗余项或者拆表来满足
在使用关系型数据库的过程中总结的三项建立表的准则，如果遵守能够最大效率地使用数据库
学生表：
学号 姓名 性别 身份证号 班级编号
create table student(
id int primary key,
name varchar(30) not null,
sex char default 'w',
id_cadr char(18) unique,
class_id int,
foreign key(class_id) references class(id)
);
成绩表：
学号 总分 语文 数学 英语
create table grade(
id int primary key,
total double check (total>=0&&total<=300),
c_grade double check (c_grade>=0&&c_grade<=300),
m_grade double check (m_grade>=0&&m_grade<=300),
e_grade double check (e_grade>=0&&e_grade<=300)
);
班级表：
班级编号 教室 班主任 语文老师 数学老师 英语老师
create table class(
id int primary key,
room_id int not null,
class_tch int,
c_tch int,
m_tch int,
e_tch int,
foreign key(class_tch) references teacher(id),
foreign key(c_tch) references teacher(id),
foreign key(m_tch) references teacher(id),
foreign key(e_tch) references teacher(id)
);
教师表：
工号 姓名 性别 身份证号 科目 职称
create table teacher(
id int primary key,
name varchar(30) not null,
sex char default 'w',
id_card char(18) unique,
subject char(20),
title char(20)
);
八、表的约束
约束是对表和表中数据的限制，可以提高表中数据的准确性和可靠性，一般在创建表、修改表时使用
约束的分类：
-
not null
-
非空约束 字段的数据不能为空，例如学生姓名
-
-
default
-
默认值约束 给字段设置默认值后，当插入数据不提供该字段的数据时，数据库会自动填充为默认值，例如学生性别
-
-
unique
-
唯一约束 该字段数据不能重复，但是可以为null，例如身份证号
-
-
primary key
-
主键约束 非空且唯一，只能有一个，例如学号
-
-
check
-
检查约束 设置一个条件，当插入数据不满足该条件时会失败，但是MySQL不支持\Oracle支持
-
-
foreign key
-
外键约束 用表A中的数据来确保表B中数据的准确性，例如表B中的某个字段的值必须在表A中的某个字段的值中出现过，否则无法插入，例如教师表中的教师工号必须在教师表中出现过
-
约束的设置方式：
-
创建表时设置约束：
-
craete table 表名(
字段名 类型 约束名,
...
); -
修改表时设置约束：只有not null\default 有效，可以增加、删除约束
-
alter table 表名 modify 原字段名 类型名 约束；
-
增加约束
-
-
alter table 表名 modify 原字段名 类型名；
-
删除约束
-
-
-
删除约束：只有unique、primary key有效
-
alter table 表名 drop constraint 约束名；
-
-
外键约束：
-
一张表（子表）的值必须引用自另一张表（父表），并且被引用的父表的字段必须具备唯一性，子表中被外界约束的字段值就必须来自父表或者为null
-
一般在创建表的外键约束时，需要先创建父表，再创建子表
create table 父表(
字段a 类型 primary key/unique;
);
creatr table 子表(
字段b 类型,
...
foreign key(字段b) references 父表名(字段a)
);也可以在后期添加外键，但是不建议，可能非法之已经存在
alter table 子表 add foreign key(子表字段) reference(父表字段)；
注意：应先插入父表数据，再插入子表数据
父表更新、删除数据：
-
先修改\删除子表，再删除父表
-
设置级联删除、级联更新
-
on delete cascade 级联删除
-
父表数据删除，子表对应数据也随之删除
-
-
on update cascade 级联更新
-
父表数据更新，子表对应数据也随之更新
-
create table 子表(
...
foreign key(字段b) reference 父表(字段a) on delete/update cascade;
); -
-