MySQL（二）

聚合/分组函数：分组
count
max
min
avg
sum
select xx, xx from table_name group by field_name;
注意：select 后面接的内容：组函数，出现在group by中的字段

单表查询完整sql的结构:
select 
	字段、函数
from 
	表
where 
	条件
group by
	分组字段
having
	组函数条件
order by
	排序方式
limit 
	startRow, pageSize 分页

– 公司中的平均薪资 - 将整张表作为一组
select avg(sal) from emp;
– 查询每个部门的平均薪资，相同的部门号作为一组
select avg(sal), deptno from emp group by deptno;
select avg(sal), deptno, ename from emp group by deptno; – 错误用法

– 查询除了10部门以外其他部门的平均薪资
select avg(sal), deptno from emp where deptno<>10 group by deptno;
select avg(sal), deptno from emp group by deptno having deptno <> 10; – 不推荐使用，效率很低

– 查询平均薪资高于1600的部门
select avg(sal) a, deptno d from emp group by d having a > 1600;

/*
JOB GRADE
PRESIDENT A
MANAGER B
ANALYST C
SALESMAN D
CLERK E
*/
– oracle 中的 decode(field,if1,then1,if2,then2…,else) - if-else if…
select ename, job, decode(job,‘PRESIDENT’,‘A’,‘MANAGER’,‘B’,‘ANALYST’,‘C’,‘SALESMAN’,‘D’,‘E’) from emp;

– mysql 中的 decode 效果 case when then… else end
select ename, job, (case job
when ‘PRESIDENT’
then ‘A’
when ‘MANAGER’
then ‘B’
when ‘ANALYST’
then ‘C’
else ‘D’ end
) grade from emp;

– 分页查询 limit startRow, rowCount;
– startRow 从0开始，通过 page 和 rowCount 计算
– startRow = (page - 1) * rowCount
select * from emp;
select * from emp limit 0, 3; – 第一页
select * from emp limit 3, 3; – 第二页
select * from emp limit 6, 3; – 第三页
select * from emp limit 9, 3; – 第四页
select * from emp limit 12, 3; – 第五页，最后一页条数不够，会全部显示
– 排好序后分页
select * from emp order by sal limit 0,3;

/*
约束种类：
1.主键约束 - 特点：非空且唯一
主键：用来标识唯一
分类：代理主键(90%) 自然主键
代理主键：没有任何业务意义
自然主键：表中本身存在一个非空且唯一的有效字段
身份证号，学号，QQ号
主键：varchar - 用代码随机生成一套字符串
int - 自动增长/序列sequence
auto_increment
2.非空约束 - 特点：不能为空
3.唯一约束 - 特点：唯一，不能重复
4.外键约束
*/

desc user;

– 主键约束：在建表时创建
create table stu (
id int(6) primary key auto_increment,
name varchar(20) not null,
age int(3),
score int(5) comment ‘学分’
);
select * from stu;
insert into stu values(1, ‘张三’, 18, 23);
– Error Code: 1062. Duplicate entry ‘1’ for key ‘stu.PRIMARY’
– 因为设置了主键自动增长，所以可以将id设置null
insert into stu values(null, ‘李四’, 18, 23);
select last_insert_id(); – 查询最后一次添加进的id值

– 在已有的表中添加主键约束
alter table user modify id int(6) primary key;
alter table user drop primary key;

insert into user values(1, ‘张三’, 19, now());
delete from user where name = ‘lucy’;

– 非空约束
– 在已有表中添加非空约束
alter table stu modify name varchar(20) not null;

– 删除非空约束
alter table stu modify name varchar(20);

alter table stu add(sex char(1) not null);
– 添加非空约束并且设置默认值
alter table stu modify sex char(1) not null default ‘男’;

insert into stu(name, age, score) values(‘王五’, 20, 34);

– 唯一约束 unique
– 建表时添加唯一约束
alter table stu add(
snum int(11) unique
);
– 唯一约束，允许有多个 null 值
insert into stu(name, age) values(“鲁智深”, 23);
insert into stu(name, age) values(“宋江”, 25);
– 将唯一约束移除 index 索引
alter table stu drop index snum;

– 已有的表中添加唯一约束
alter table stu add unique index (snum, name);
alter table stu add unique index (snum);

– 外键约束 foreign key
– 创建表时添加外键约束
create table student (
id int(6) primary key auto_increment,
name varchar(20) not null,
age int(2),
cid int(6),
constraint fk_stu_cls foreign key (cid) references class(id)
);
create table class (
id int(6) primary key auto_increment,
name varchar(10) not null unique
);
insert into class values(2, ‘Java’);

insert into student values(null, ‘lucy’, 18, 2);

– 在已有的表中添加外键约束，先添加一列 cid
alter table stu add constraint fk_stu_cls1 foreign key (cid) references class(id);

/**
1NF:所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项
2NF:在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
3NF:在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
概念：
函数依赖：A --> B,假设通过A属性(属性组)，能确定唯一B属性的值
称B函数依赖于A
例：姓名 依赖于 学号， 学号 --> 姓名
(学号，课程名称) --> 分数
完全函数依赖：A --> B, 假设A是属性组，B的值需要依赖于A属性组中所有的值
称B完全依赖于A
例：(学号，课程名称) --> 分数
部分函数依赖：A --> B, 假设A是属性组，B的值需要依赖于A属性组中的一些、一个值
称B部分依赖于A
例：(学号，课程名称) --> 姓名
传递依赖：A --> B --> C, C依赖于B，B依赖于A，称C传递依赖于A
例：学号 --> 系名 --> 系主任
码：通过一个属性，或者一个属性组，能确定其他所有属性的值
称这个属性或者属性组为码
例：(学号，课程名称)
*/
– 多表查询 - 前提，存在外键关系
– 查询员工姓名以及他所在的部门名称
– ename - emp
– dname - dept

select * from emp;
select * from dept;
– 查询全部, 笛卡尔积 - 没有意义
select * from emp, dept;
– 隐式内连接：只能查出两张表中完全匹配的值
select * from emp e, dept d where e.deptno = d.deptno;
– 显式内连接 inner join
select * from emp e inner join dept d on e.deptno = d.deptno;
select * from emp e join dept d on e.deptno = d.deptno;

– 外连接：左外连接，右外连接，全外连接[oracle方言]
– 驱动表：如果你需要将一张表中所有的数据都显示出来，那么这张表就需要作为驱动表

– 查询员工姓名以及他所在的部门名称,没有部门的员工也要显示出来
select ename, dname
from emp e
left join dept d on e.deptno = d.deptno;

select ename, dname
from dept d
right join emp e on e.deptno = d.deptno;

select ename, dname
from dept d
left join emp e on e.deptno = d.deptno;