MYSQL(2)

case:

SELECT 字段名 case WHEN… THEN… END AS FROM 表名…

例：

查询员工年龄，并进行分段：
10-20yong20-30 midlife 30-40 old_man 40-50 old

代码：
select name,age,case when age >= 10 and age < 20 then ‘yong’
when age >= 20 and age < 30 then ‘midlife’
when age >= 30 and age <= 40 then ‘old’
end as agedusn
from emp;

结果：

查询学生姓名，总成绩，评价，评价规则：
180以下是评价差 180-200 以下是 评价中 200-250是良好 250-300是优秀。

代码：
select name,chinese+english+math as zong,case when chinese+english+math <180 then ‘差’
when chinese+english+math <200 then ‘中’
when chinese+english+math <250 then ‘良’
when chinese+english+math<= 300 then ‘优’
end as pingjia
from exam;

结果：

对员工表，员工收入进行评价：
0-3000 平民 3000-5000布衣 5000-10000土豪 10000 以上 富翁

代码：
select name,sal+comm as zong,case when sal+comm <= 3000 then ‘贫民’
when sal+comm <= 5000 then ‘布衣’
when sal+comm <= 10000 then ‘土豪’
when sal+comm > 10000 then ‘富豪’
end as pingjia
from emp;

结果：

分组查询：GROUP BY

SELECT 查询条件 FROM 表名 GROUP BY 字段名;

例：
统计男女人数，按照性别分组
SELECT COUNT(*) FROM stu GROUP BY sex;

统计各部门人数，按照部门分组
SELECT dep,COUNT(*) FROM emp GROUP BY dep;

查询 各个部门的工资总和，要求显示部门总和

查询各部门工资大于3000的工资总和和部门人数
Select dep,sum(sal+comm)zong from emp where sal+comm>3000 group by dep;

查询各部门工资大于3000的工资总和和部门人数，并按照总和升序排序

查询各部门工资大于3000的工资总和和部门人数并按照总和升序排序 并给部门评价总工资10000以下 穷部 10000-30000 平部30000以上 富部

HAVING:分组之后再过滤

HAVING 和 WHERE 的区别：
WHERE是过滤数据源，分组之前的过滤； HAVING分组之后的过滤
例：
查询 按照部门分组找出总工资大于10000
select dep,sum(sal+comm)as zong from emp group by dep having zong>10000;

查询工资总和大于9000的部门，即工资总和Select dep,sum(sal+comm)as z gongzi from emp GROUP BY dep having z gongzi>9000;

Limit:

Limit：限制结果数量
取一定数量的记录，一般和order by联用
例：
查询数学成绩 前五名
select name，math from exam order by math desc limit 5； (查从头开始五个人)
select name，math from exam order by math desc limit0， 5；(查从第一个元素开始的五个人)
select name，math from exam order by math desc limit 1，5；(查从第二个元素开始的五个人)

查询
select dep,avg(sal+comm)avg from emp group by dep order by avg desc limit 2;

补充函数：

left和right：左提取，右提取
left(字符串，提取位数)
year()：提取年份
month()：提取月份
day()：提取日
now()：获取当前时间

DCL：

（1）添加用户，使该用户可以访问mysql
（2）授权（一定要刷新权限）

数据完整性

作用： 保证用户输入的数据保存到数据库是正确的
怎么确保用户输入不会出问题呢：添加约束
完整性约束分类： 实体完整性、域完整性、引用完整性

实体完整性

实体：一行记录叫做一个实体，表中的一行。
实体完整性：标识每一行数据不重复
约束类型：主键约束(primary key)，唯一约束(unique)，自动增长列(auto_increment)
主键约束(primary key)：
特点：唯一，非空

唯一约束(unique)：
作用：让字段没有重复数据

自动增长(auto_increment)：作用:让数值类型的字段自动加一
需要注意，自动增长要和主键一起存在（主键才能增长)

域完整性：

用来限制单元格的数据输入正确
约束类型：数据类型，非空约束(not null)，默认值约束( default)
检查约束check
数据类型：约束输入的数据类型
非空约束：
作用：要求在插入时一定要给非空的字段插入值

默认约束：
作用：当插入数据时，不插入该约束字段会自动填入默认值

检查约束：
作用：检查用户输入的内容，如果与设置的不符就不允许输入
注意：在mysql中不支持该约束

引用完整性

外键引用：
学生表：id ；名字 ；性别
成绩表：id ；学生id ；成绩

表与表的关系：

为什么拆分表：
拆分表解决了数据的冗余问题，减少重复数据的存在；但是，查询更加复杂，速度效率降低。
优点：表结构很清晰

一对一：

例如：人和身份证号
主表：人；从表：身份证号

一对多：

最常见的对应关系：部门和部门员工，班级和班级学生，学生和成绩在多的一方，存储一的一方的外键

多表查询：

合并结果集: union、union all
连接查询：
内连接：inner , join on
外连接：[Outer] join on 有 左外连接: left [outer] join on、右外连接: right [outer] join on、全连接: full join
自然连接: natural join
子查询

合并结果集：

作用：将两个查询的结果合并到一起（纵向）
合并结果集有两个方式：
（1）union:去除重复记录合并，例如: select * from t1 union select * from t2;
（2）union all：不去除重复记录合并，例如: select * from t1 union all select * from t2;

两张表：
select * from ti union select * from t2;

注意：合并结果集不按照字段名称合并，要保证两个结果字段数量一致

连接查询(列连接)：

连接查询t1，t2，相当于t1*t2；
两张表：
select * from t1，t2；

连接查询会产生笛卡尔积表，假设集合A={a,b},集合B={0,1,2}，则两个集合的笛卡尔积表为{{a,1},{a,2},{a,0},{b,1},…}会出现2x3=6个元素。

为了不形成笛卡尔积表，那么就要去除重复，利用表与表的关联去去除重复，关联就是用来过滤重复的条件。

假设有emp表14条，dep表4条，如果连接查询会产生56条记录的表格，而我们的目的仅仅是将部门显示在员工表上，那么就需要使用主外键来去除无效的。
查询 显示 学生id 科目 成绩
select sid,kindname,score from kind,score where kind.id = source .kind_id order by;
查询 学生姓名 科目名称 成绩

内连接

上面的写法也是内连接，不过他不是mysql的标准写法
select * from stu inner join source on stu.sid=source.sid;
可以给表起别名 如果起别名 就不能用原表名
select * from stu a inner join source b on a.sid=b.sid;

inner可以省略
select * from stu a join source b on a.sid=b.sid;
内部连接的特点：查询过程必须满足条件，不满足条件的数据会丢掉

外连接

A left join B ： A主表
B left join A ：B主表
A right join B：B主表
特点：主表的数据全都要，然后去匹配从表的数据，匹配不上的数据就不要

面试题：左连接和右连接的区别

自然连接

2张表中有字段名和类型一样的 自动作为连接条件

子查询

一个select里面包含另一个select
子查询又叫嵌套查询，
子查询出现的位置： where后作为查询条件的一部分；from后边，做表
当子查询出现在where做条件时，还可以使用以下关键字：any：任意 ； all：所有
子查询结果集的形式：
单行单列：用于条件
单行多列：用于条件
多行单列：用于条件
多行多列：用于表