day41
今日内容：
1.完整查询语句
2.多表查询
3.子查询
1.完整查询语句：
首先对于昨天的学习补充一个复制表
示例：首先我在一个库中创建了一个t1表（id 为int类型 设置为主键 并且设置了自增描述auto_increment，然后是一个name字段 类型char(5)）
然后我通过复制表的语句来实现了t2的创建：create table t2 select * from t1;
可以看出来我们复制的这个表没有复制到t1的描述和主键，只能赋值t1表的基本结构
接下来我开始总结今天所学的第一个知识点：
1.增：语句
（1）insert into 表名 字段名 字段名... value/values(字段值);
into 可省略,字段名（可选，如果写了字段名后面的值必须与写的字段名匹配起来，不写后面的值必须和表的结构完全匹配）
value 插入一条记录/values 插入多条记录
2.改：语句
update 表名 set 字段名 = 新值,字段名n =新值n where 条件
可以同时修改多个字段 用逗号隔开 （注意：最后一个字段不用加都好）
where 可选（有：满足修改条件的记录，无：全部修改）
3.删：语句
delete from 表名 where 条件
where 可选 （有：删除满足条件的记录，无：全部删除）
如果你想全部删除，建议使用“truncate table 表名” (因为delete关键字是通过逐行对比，然后进行删除，效率低)
4.查：语句
select distinct {看情况选择写：*|字段名|聚合函数|表达式} from 表名
where 条件 group by 字段名 having 条件 order by 字段名 limit 显示的条数
注：关键字的顺序必须与上述语法一致（并非所有都要用到，看情况选择用）
这里我先介绍一下简单查询：
1.*表示所有列都显示，也可以手动指定要显示的列，可以是多个
2.distinct：用于去除重复的记录，只取出完全相同的记录，当然你也可以手动指定要显示的列从而来去重
3.表达式 支持四则运算
然后我们需要记住其执行顺序：
select()
from()#相当于打开文件 ==》where #读取每一行并进行条件判断==》group#对数据进行分组==》having==》having#对分组后的数据再进行过滤==》distinct#去重==》order #排序==》limit #限制显示的记录数
示例：
select * from t1;#查看全部数据
select * from t1 where name = "xxx" or name = "yyy";#设置条件查看到了满足我们条件的数据
select id,distinct(name) from t1;#distintct:去重作用
select * from t1 group by name;#根据名字进行分组（虽然这个例子不太恰当 0.0）
select * from t1 group by name having id >3;#对分组后的数据进行过滤（注：特别注意 having 是用于过滤分组后的数据，也就是说没有gruop by 就没有 having）
select * from t1 group by name having id >3 order by id;#对于分组过滤后的数据再按照其id进行升序（注：降序就在后面加desc）
select * from t1 group by name having id >3 order by id limit 1,2;
select concat("id:",id) id,concat("name:",name) name from t1;
#这里有两个点需要学习
1.首先concat 这个关键字用来字符串拼接
2.concat("id:",id) id ：注意这句话我又加了一个id 这个的意思就是给这个字段名取别名，若不加这个显示的字段名会是concat("id:",id)
select (case when id<4 then concat(name,"<4") when id>=4 then concat(name,">=4") end) 了解即可 from t1;
了解即可，自行理解
where 关键字
介绍：从硬盘上读取数据时的一个过滤条件
where的筛选过程：在没有索引的情况下，逐个比较，效率低（应该给表加索引提高其效率）
group by关键字
介绍：用于数据分组
1.更加方便的管理数据
2.更加方便的统计
用一个更加贴切的例子来举例group by 的使用，并介绍一下聚合函数：
表格数据：
create table emp (id int,name char(10),sex char,dept char(10),job char(10),salary double);
insert into emp values (1,"刘备","男","市场","总监",5800),
(2,"张飞","男","市场","员工",3000),
(3,"关羽","男","市场","员工",4000),
(4,"孙权","男","行政","总监",6000),
(5,"周瑜","男","行政","员工",5000),
(6,"小乔","女","行政","员工",4000),
(7,"曹操","男","财务","总监",10000),
(8,"司马懿","男","财务","员工",6000);
select *from emp group by dept;按照部门给数据分组
其实这个语句有两种情况
1.sql_mode中 没有设置 ONLY_FULL_GROUP_BY 显示每个组的第一条记录 没有意义 所以新版中 自带ONLY_FULL_GROUP_BY
2.sql_mode中有设置 ONLY_FULL_GROUP_BY 直接报错
原因是： * 表示所有字段都要显示 但是 分组后 记录的细节被隐藏 只留下了
这意味着：只有出现在group by 后面的字段才能被显示
聚合函数：
将一堆数据经过计算，得到一个数据
sum()求和
avg()求平均数
max()/min() 最大值/最小值
count()计数
示例
select dept,count(*) from emp group by dept;
select dept,avg(salary) from emp group by dept;
select dept,avg(salary) avg_salary from emp group by dept having avg_salary>5000;
having 关键字
介绍：用于对分组后的数据进行过滤
having不会单独出现 都是和group by一起出现
与where比较：
相同点：都用于数据过滤
不同点：
1.where是最先执行的，用于读取硬盘数据，而having 要等到数据读取完后，才能进行过滤，比where晚执行
1.where中不能使用聚合函数，having可以
order by 关键字：
介绍：用于对记录进行排序
desc降序
asc升序
默认为升序
limit 关键字：
介绍：用于限制显示记录数
limit n,m#n指的是从第几行的后一行开始显示，m指的是显示几行
常用于分页显示
2.多表查询
多表查询：
介绍：在多个表中查询需要的数据
例如：有学生表和班级表
给你一个班级名称，让你查询所有学生的数据
首先我们应该先查班级表 得到一个班级id 再根据id去学生表查询对应的学生
多表查询的方式
1.笛卡尔积查询
什么是笛卡尔积,用坐标中的一条记录 去链接另一张表的所有记录
就像是把 两张表的数据做了一个乘法
这将导致 产生大量的无用重复数据
我们要的效果是：员工表中的部门id 与 部门表中的id相同 就拼接在一起
准备实例的数据：
insert emp values(1,"大黄","m",1);
insert emp values(2,"老王","m",2);
insert emp values(3,"老李","w",30);
insert dept values(1,"市场");
insert dept values(2,"财务");
insert dept values(3,"行政");
select * from emp,dept where emp.dept_id = dept.id;
on 关键字：作用于多表查询，为其进行条件限制,它只能用在专门多表查询语句中
inner join:内连接查询
select * from emp inner join dept on emp.dept_id = dept.id;
left join :左连接查询，左边表中的数据完全显示，右边表中的数据匹配上才显示
select * from emp left join dept on emp.dept_id = dept.id;
right join:有链接查询，右边表中的数据完全显示，左边表中的数据匹配上才显示
select * from emp right join dept on emp.dept_id = dept.id;
全外连接：
full join mysql不支持 oracle支持
通过 union 间接实现
union 表示合并查询 意思是把多个查询结果合并在一起显示，要求是被合并的表结构必须相同，默认去除重复
union all 合并但是不去除重复
select * from emp left join dept on emp.dept_id = dept.id
union
select * from emp right join dept on emp.dept_id = dept.id;
总结：多表链接 在书写时 按照填空来书写 如果左边要全部显示 用left join
右边全部显示 用right join
全部显示 把左链接的结果和右链接的结果 合并
当然 也可以更多表一起查 但是 没有意义 并且你要尽量避免 太多表 一起查
最多三张 在多对多的时候
3.子查询
1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 4：还可以包含比较运算符：= 、 !=、> 、<等
带IN关键字的子查询
查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
带比较运算符的子查询
比较运算符：=、!=、>、>=、<、<=、<> 查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from emp where age > (select avg(age) from emp); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec) 查询大于部门内平均年龄的员工名、年龄 select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。True或False，当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。
#department表中存在dept_id=203，Ture
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
#department表中存在dept_id=205，False
mysql> select * from employee
-> where exists
-> (select id from department where id=204);
Empty set (0.00 sec)
