MySQL单表多表查询，SQL优化，三表连接，范式与视图（二）

接上篇 MySQL介绍、数据库、表等基本操作，表约束表关系 （一）

一、单表查询

【注意：能具体写就不要用*查询，速度慢，影响用户体验】

1、查询所有数据
select * from student;

2、查询部分字段
select s_name from student;

3、取别名（临时）
select s.s_name from student as s;
select s.s_name as sn from student as s;

4、模糊查询
select * from student where s_name like 'x%';	# x开头
select s_name from student where s_name like '%O%'; # beidou、moran（中间有O的）

5、指定条件查询
select * from student where age > 18;
select * from student where age > 18 and sex = '男';

二、多表查询

1、内连接

1、inner join
select * from department inner join student;
select * from department, student;
select * from department cross join student;
select * from department join student;

2、内连接的条件查询
select * from department inner join student on department.d_id = student.dept_id;
加别名：
select * from department as d inner join student as s on d.d_id = s.depr_id;

3、字段有选择的查询
select d.d_id,d_names,s.s_name from department as d inner join student as s on d.d_id = s.dept_id;

2、外连接

1、左连接（小表放左边做主表 left join）
select d.d_id,d.d_name,s.s_id,s.s_name from department as d left join student as s on d.d_id=s.dept_id;

2、右连接（小表放右边放主表   right  join）
select  d.d_id,d.d_name,s.s_id,s.s_name  from  department  as  d  right  join  student  as  s  on  d.d_id=s.dept_id;

3、全连接（去重——UNION 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行）
select  d.d_id,d.d_name,s.s_id,s.s_name  from  department  as  d  left  join  student  as  s  on  d.d_id=s.dept_id union select  d.d_id,d.d_name,s.s_id,s.s_name  from  department  as  d  right  join  student  as  s  on  d.d_id=s.dept_id;

3、子表查询

# 子表当中最好不要用*
例1：select  *  from  department  left join ( 
    select  dept_id,s_name,s_age,s_sex  from  student right  join stu_detail on  student.s_id = stu_detail.stu_id) as e  on e.dept_id = department.d_id;

例2：select  *  from  department  where  d_id = (
    select  dept_id  from  students  inner  join  stu_detail  on  student.s_id = stu_detail.stu_id);

# 返回dept_id 传入 ( )
例3：select   *  from  students  where  dept_id  = (
    select  d_id  from  department  where  s_name = 'java');

4、排序 order by

asc：升序 （默认）	desc：降序 
1、select  *  from  mins  order  by  age  desc;
2、select  *  from  mins  order  by  age  asc;
3、select  *  from  mins  order  by  age  asc  limit  4;  #只要4行
4、select  *  from  mins  order  by  age  asc  limit  1, 4;  #从1开始

5、分组查询

1、select  d_id,d_name,count(*)  from  student  inner  join  department  on  student..dept_id  = department.d_id   group  by  d_name;

2、select  d_id,d_name,count(*)  from  student  inner  join  department  on  student..dept_id  = department.d_id   group  by  d_name  having  count(*) = 2;

6、处理空字典

select  s_name,ifnull(dept_id) from  student;

三、MySQL函数

abs、max、min、round、avg、sum

  select  min(age) from  student;

四、查询SQL优化

建议：
1.尽量避免整表扫描，如SELECT *
2.建立合适的索引
3.使用合适的存储引擎
4.在JOIN中，尽量用小表LEFT JOIN 大表（左连接）
5.除非十分必要，尽量不要使用ORDER BY,GROUP BY 和 DISTINCT(去重)，尽量用索引来代替

添加索引：
alter  table  student  add  index(age);
去重:
select  distinct (name)  from  student;

五、三表连接

select  d.d_id,d.d_name,s.s_name,s.s_id,stu.s_age,stu.s_sex 
from department  as  d  
left  join student  as  s  
on  d.d_id = s.dept_id 
left  join  stu_detail  as  stu  
on  stu.stu_id = s.s_id;

六、范式与视图

1、范式

1、第一范式（1NF）：符合1NF的关系中的每个属性都不可再分。1NF是所有关系型数据库的最基本要求

简单来说，就是不能出现多个表头

2、第二范式（2NF）：2NF在1NF的基础之上，消除了非主属性对于码的部分函数依赖

简单来说，只能描述一个对象（主键），其它列名（副键）与对象之间相互完全依赖

3、第三范式（3NF）：3NF在2NF的基础之上，消除了非主属性对于码的传递函数依赖

简单的说，所有的非主属性只在整个数据库里面出现一次，副键与副键之间，不能存在依赖关系

范式的作用是尽可能避免数据的冗余和插入/删除/更新的异常

2、事务

数据库事务(transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。

简单来说：几个操作是绑定在一起的，要么都做完，要么一个都不做

3、视图

1、视图的作用

三范式让表查询变得复杂，对于常用的数据查询，反复写复杂的查询语句十分不方便，因此可以创建一个虚拟的表（不存数据），这个虚拟表的数据来源于数据库中存在的其他表，虚拟表的数据来源就在定义时给定

2、创建视图 view

create view stu_det as
select s.s_name, d.d_name, d.d_id from department as d left join student as s on s.dept_id = d.d_id;

3、查看视图

select * from stu_det;
insert into stu_det values ('sss', 1, 'c++');  # 会报错，因为它只能看不能用

4、删除视图

DROP VIEW view_name