数据库之单表查询，多表查询-32 - 漫天飞雪世情难却

内容

查询语句
   1.完整查询语句
   2.一堆关键字
   关键字的执行顺序
   3.单表查询
     [where 条件   ******
   group by 字段名   ***
   having 条件     ***
   order by 字段名   *****
   limit 显示的条数] *******
   4.多表查询  *******

   5.子查询

1.一对一关系的补充

一对一 ******
生活中的一对一
   客户表，学员表
   通过分析
   一个客户只对应一个学员
   一个学员只对应一个客户
   所以确定关系为一对一

   在mysql中通过外键来建立一对一

   create table customer(id int primary key auto_increment,name char(10),phone char(11),sex char(1));
   create table student(id int primary key auto_increment,name char(10),class char(11),sex char(1),c_id int,
   foreign key(c_id) references customer(id) on update cascade on delete cascade
   );

2.拷贝表

拷贝表 ***

1.create table copy_table select *from customer ;
    拷贝结构与数据
    mysql> select * from copy_table;
+----+------+-------+------+
| id | name | phone | sex |
+----+------+-------+------+
|  1 | lmz | 110   | 女   |
+----+------+-------+------+

    mysql> desc copy_table;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | int(11)  | NO   |     | 0     |     |
| name | char(10) | YES |     | NULL |     |
| phone | char(11) | YES |     | NULL |     |
| sex   | char(1)  | YES |     | NULL |     |
+-------+----------+------+-----+---------+-------+

2.create table copy_table select *from customer where 0 > 1;
        仅拷贝结构

共同点：索引不能拷贝   描述不能拷贝（自增）

3.记录详细操作

以下语法中
[] 表示可选的
{}表示必选的
增
   insert [into] 表名[字段名] value|values(字段值....);
   into 可省略
   [字段名] 可选
   如果写了你后面的值必须与写的字段匹配
   不写  后面的值必须和表的结构完全匹配
   value 插入一条记录
   values 插入多条记录

改
   update 表名 set 字段名 = 新的值[,字段n = 新值n]  [where 条件]
   可以同时修改多个字段用逗号隔开   注意最后一个字段不能加逗号
   where 可选
   有就修改满足条件的记录
   没有就全部修改
删
   delete from 表名 [where 条件]
   where 可选
   有就删除满足条件的记录
   没有就全部删除
   如果你需要全部删除请使用truncate table 表名
   delete 是逐行比对  删除效率低
   delete删除的行号会保留

查询
   完整的查询语句
   select  [distinct] {* | 字段名 | 聚合函数 | 表达式} from 表名
   [where 条件
   group by 字段名
   having 条件
   order by 字段名
   limit 显示的条数]

   注意：关键字的顺序必须与上述语法一致

   简单查询 ******

   1.* 表示所有列都显示
   2.也可以手动指定要显示的列  可以是多个
   3.distinct 用于去除重复的记录
   只取出完全相同的记录
   当然你也可以手动指定要显示的列从而来去重
  select distinct name from student;#去除项名字相同的数据
   4.表达式  支持四则运算

   执行顺序
     def select()
   from() 打开文件
   where() 读取每一行并判断是否满足条件
   group() 对数据进行分组
   having() 再分组之后进行过滤   having不单独出现仅用于分组之后进行过滤
   distinct() 去重
   order() 用于对筛选后的数据进行排序
   limit() 限制显示的条数
   最后根据select后制定的字段来显示数据

准备数据：
create table stu(id int primary key auto_increment,name char(10),math float,english float); insert into stu values(null,"赵云",90,30); insert into stu values(null,"小乔",90,60); insert into stu values(null,"小乔",90,60); insert into stu values(null,"大乔",10,70); insert into stu values(null,"李清照",100,100); insert into stu values(null,"铁拐李",20,55); insert into stu values(null,"小李子",20,55);
   查询所有人的总成绩

select name,english+math 总分 from stu;
select name,english+10 英语 from stu;
需要在字段的数据前加上字段名:
name:赵云   english:90  math:30
需要使用字符串拼接函数
concat(字符串)

案列：
select

4.WHERE 关键字

where
   从硬盘上读取数据时的一个过滤条件

   where支持的运算符如下图

   where 的筛选过程
   在没有索引的情况下  挨个比较  效率低
   所以我们应该给表添加索引

5.GROUP BY

group by
   作用用于给数据分组
   为什么要分组？  思考生活为什么要分组
   1.在生活中是为了方便管理
   2.在数据库中是为了方便统计

   准备数据
   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);

     按照部门给数据分组
     mysql> select * from emp group by dept;
+------+--------+------+--------+--------+--------+
| id   | name   | sex | dept   | job | salary |
+------+--------+------+--------+--------+--------+
|   1 | 刘备   | 男   | 市场   | 总监   |   5800 |
|   4 | 孙权   | 男   | 行政   | 总监   |   6000 |
|   7 | 曹操   | 男   | 财务   | 总监   |  10000 |
+------+--------+------+--------+--------+--------+
     有两种情况
     1.sql_mode中没有设置 ONLY_FULL_GROUP_BY 显示每个组的第一条记录没有意义所以新版中自带ONLY_FULL_GROUP_BY
     2.sql_mode中有设置 ONLY_FULL_GROUP_BY 直接报错
   原因是：  * 表示所有字段都要显示  但是分组后记录的细节被隐藏只留下了dept
   这意味着：只有出现在group by 后面的字段才能被显示
   分组是为了统计分组数据  如何统计？
   需要使用到聚合函数

聚合函数：
   将一堆数据经过计算，得到一个数据
   sum() 求和
   avg() 求平均数
   max()/min() 求最大值 / 最小值
   count() 个数

2.查询每个部门有几个人
mysql> select dept,count(*) from emp group by dept;
+--------+----------+
| dept   | count(*) |
+--------+----------+
| 市场   |   3 |
| 行政   |   3 |
| 财务   |   2 |
+--------+----------+

6.HAVING关键字

having
   用于对分组后的数据进行过滤
   having不会单独出现都是和group by 一起出现

   与where的区别
   相同点：都用于过滤数据
   不同点：
   1.where是最先执行   用于读取硬盘数据
   having 要等到数据读取完之后才能进过滤比where晚执行
   2.where中不能使用聚合函数
   having中可以

需求：
   查询平均⼯工资⼤大于5000的部⻔
   mysql> select dept,avg(salary) from emp group by dept having avg(salary)>5000;
    +--------+-------------+
    | dept   | avg(salary) |
    +--------+-------------+
    | 财务   |   8000 |
    +--------+-------------+

7.ORDER BY关键字

order by [desc,asc]
   用于对记录进行排序

   desc为降序
   asc为升序

   按照工资的从低到高顺序显示所有的员工
   mysql> select * from emp order by salary;
    +------+-----------+------+--------+--------+--------+
    | id   | name | sex | dept   | job | salary |
    +------+-----------+------+--------+--------+--------+
    |   2 | 张飞   | 男   | 市场   | 员工     |   3000 |
    |   3 | 关羽   | 男   | 市场   | 员工     |   4000 |
    |   6 | 小乔   | 女   | 行政   | 员工     |   4000 |
    |   5 | 周瑜   | 男   | 行政   | 员工     |   5000 |
    |   1 | 刘备   | 男   | 市场   | 总监     |   5800 |
    |   4 | 孙权   | 男   | 行政   | 总监     |   6000 |
    |   8 | 司马懿   | 男   | 财务   | 员工     |   6000 |
    |   7 | 曹操   | 男   | 财务   | 总监     |  10000 |
    +------+-----------+------+--------+--------+--------+
   默认为升序

   修改为降序
   mysql> select * from emp order by salary desc;
    +------+-----------+------+--------+--------+--------+
    | id   | name | sex | dept   | job | salary |
    +------+-----------+------+--------+--------+--------+
    |   7 | 曹操 | 男   | 财务   | 总监   |  10000 |
    |   4 | 孙权 | 男   | 财务   | 员工   |   6000 |
    |   1 | 刘备 | 男   | 市场   | 总监   |   5800 |
    |   5 | 周瑜 | 男   | 行政   | 员工   |   5000 |

8.LIMIT关键字*******

   用于限制显示的条数
   limit [start],count 从id=start+1开始取,取count个

   # 看看表里前三条数据
   mysql> select *from emp limit 3;=select * from emp limit 0,3;(从id=1开始取,取三个结束)
    +------+--------+------+--------+--------+--------+
    | id   | name   | sex | dept   | job | salary |
    +------+--------+------+--------+--------+--------+
    |   1 | 刘备   | 男   | 市场   | 总监   |   5800 |
    |   2 | 张飞   | 男   | 市场   | 员工   |   3000 |
    |   3 | 关羽   | 男   | 市场   | 员工   |   4000 |
    +------+--------+------+--------+--------+--------+
   # 看看表里的3-5条
   mysql> select * from emp limit 2,3;(从id=3开始取,取三个)
    +------+--------+------+--------+--------+--------+
    | id   | name   | sex | dept   | job | salary |
    +------+--------+------+--------+--------+--------+
    |   3 | 关羽   | 男   | 市场   | 员工   |   4000 |
    |   4 | 孙权   | 男   | 行政   | 总监   |   6000 |
    |   5 | 周瑜   | 男   | 行政   | 员工   |   5000 |
    +------+--------+------+--------+--------+--------+


   # 查看工资最高的那个人的信息
   mysql> select * from emp order by salary desc limit 1;
    +------+--------+------+--------+--------+--------+
    | id   | name   | sex | dept   | job | salary |
    +------+--------+------+--------+--------+--------+
    |   7 | 曹操   | 男   | 财务   | 总监   |  10000 |
    +------+--------+------+--------+--------+--------+

   limit 常用于数据的分页展示  比如腾讯新闻的上拉加载新的而一页
   select *from emp limit 0,10; 第一页     页数减1  乘以条数得到起始位置
   select *from emp limit 10,10; 第2页
   select *from emp limit 20,10; 第3页

9.多表查询

多表查询
   在多个表中查询需要的数据
   例如：有班级表和学生表
   给你一个班级名称  请查询所有的学员数据
   先查班级表得到一个班级的id 再根据id去学员表查询对应的学员

   准备数据：
   create table emp (id int,name char(10),sex char,dept_id int);
   insert emp values(1,"大黄","m",1);
   insert emp values(2,"老王","m",2);
   insert emp values(3,"老李","w",30);

   create table dept (id int,name char(10));
   insert dept values(1,"市场");
   insert dept values(2,"财务");
   insert dept values(3,"行政");

   多表查询的方式
   1.笛卡尔积查询
   什么是笛卡尔积,用坐标中的一条记录去链接另一张表的所有记录
   就像是把两张表的数据做了一个乘法
   这将导致产生大量的无用重复数据
   我们要的效果是：员工表中的部门id 与部门表中的id相同就拼接在一起
   用 where 筛选出正确的数据
   mysql> select dept.id,dept.name,emp.name from emp,dept
      where dept.id=emp.dept_id;
    +------+--------+--------+
    | id   | name   | name   |
    +------+--------+--------+
    |   1 | 市场   | 大黄   |
    |   2 | 财务   | 老王   |
    +------+--------+--------+

   on关键字
   作用用于多表查询是进行条件限制
   mysql> select * from emp,dept on emp.dept_id=dept.id;
        ERROR 1064 (42000): You have an error in your SQL syntax;
        check the manual that corresponds to your MySQL server version
        for the right syntax to use near 'on emp.dept_id=dept.id' at line 1
        这是错误的语法,因为on它只能用在专门多表查询语句中

   2.内连接查询
   inner join
   mysql> select * from emp inner join dept on(where) emp.dept_id=dept.id;
        +------+--------+------+---------+------+--------+
        | id   | name   | sex | dept_id | id   | name   |
        +------+--------+------+---------+------+--------+
        |   1 | 大黄   | m |     1 |   1 | 市场   |
        |   2 | 老王   | m |     2 |   2 | 财务   |
        +------+--------+------+---------+------+--------+

     # 查询所有的员工以及他们所属的部门信息
   3.左外连接
   left join
     左边表中的数据完全显示   右边表中的数据匹配上才显示
     mysql> select * from emp left join dept on emp.dept_id=dept.id;
        +------+--------+------+---------+------+--------+
        | id   | name   | sex | dept_id | id   | name   |
  +------+--------+------+---------+------+--------+
        |   1 | 大黄   |

10.多对多,三表联查

create table tec(id int,name char(10));
insert into tec value(1,"egon");
insert into tec value(2,"yyh");

create table stu(id int,name char(10));
insert into stu value(1,"大傻");
insert into stu value(2,"中傻");
insert into stu value(3,"小傻");
create table s_t(s_id int,t_id int);
insert into s_t value(1,2);
insert into s_t value(2,2);
insert into s_t value(3,1);

需求  找出 yyh 这个老师教过的学生信息
思路：
   第一步到关系表中去查询哪些老师教过哪些学生（学生的id）  形成了一个临时表
   第二步  将上一步得到临时表与学生表进行连接
   第三步加上额外的筛选条件   老师的name 是  yyh

mysql> select tec.name teacher,stu.name student from
   -> tec inner join s_t on tec.id=s_t.t_id
   -> inner join stu on s_t.s_id=stu.id
   -> where tec.name='yyh';
+---------+---------+
| teacher | student |
+---------+---------+
| yyh   | 大傻   |
| yyh   | 中傻   |
+---------+---------+

11.子查询

--------------------------------子查询------------------------------ -----------------

什么是子查询：将上一次查询的结果作为本次查询的原始数据（或是查询条件）

   需求查询出工资最高的人的信息
   先查询出财务部最高工资是多少
   拿着最高工资去表中看谁的工资和最高工资匹配
   select *from emp where salary = (select max(salary) from emp);

1.子查询 ****
什么子查询
当一个查询是另一个查询的条件时,这个查询称之为子查询(内层查询)
又称为内查询

什么时候使用?
当查询需求比较复杂,一次性查询无法得到结果,需要多次查询时,

子查询能干的事多表联查也能干

2.如何使用?
首先明确子查询就是一个普通的查询,当一个查询需要作为子查询使用时,用括号包裹即可
案列:
准备数据:
create table emp (id int,name char(10),sex char,age int,dept_id int,job char(10),salary double);
insert into emp values
(1,"刘备","男",26,1,"总监",5800),
(2,"张飞","男",24,1,"员工",3000),
(3,"关羽","男",30,1,"员工",4000),
(4,"孙权","男",25,2,"总监",6000),
(10,"刘备2","男",26,2,"总监",5800),
(5,"周瑜","男",22,2,"员工",5000),
(6,"小乔","女",31,2,"员工",4000),
(7,"曹操","男",19,3,"总监",10000),
(8,"司马懿","男",24,3,"员工",6000);

create table dept(id int primary key,name char(10));
insert into dept values(1,"市场"),(2,"行政"),(3,"财务");

需求:财务部有哪些人:
方式1：
数据在两张表中可以使用链接查询:
select emp.name from emp inner join dept on emp.dept_id = dept.id where dept.name = "财务";
方式2：
数据在两张表中,先查询那张?
emp? 不行不知道部门名查dept
第一步需要知道财务部的id
select id from dept where name = "财务";
第二步用查询的到的id作为判断条件查询emp
select name from emp where dept_id = 3;
第三步不能写死是上一个查询的结果所以直接写在后面加上括号就变成了子查询
select name from emp where dept_id = (select id from dept where name = "财务");

查询平均年龄大于25的部门名称
方式1：
多表查询方式:
先把数据拼接到一起在加以筛选
select dept.name from emp inner join dept
on emp.dept_id = dept.id
group by dept.name
having avg(age) >25;
方式2：
in 关键字子查询
1.求每个部门的平均年龄
select avg(age) from emp group by dept_id;
2.筛选结果
select dept_id,avg(age) as a from emp group by dept_id having a > 25;
select name from dept where id in (select dept_id from emp group by dept_id having avg(age) > 25);
查询每个部门工资最高的员工信息

先查询每个部门的最高工资
select dept_id,max(salary) from emp group by dept_id;
select *fromemp inner join (select dept_id,max(salary) maxs from emp group by dept_id) as t2 on emp.dept_id = t2.dept_id where emp.salary = t2.maxs;

exists关键字子查询
exists 后跟子查询子查询有结果是为True 没有结果时为False
为true时外层执行为false外层不执行
select *from emp where exists (select *from emp where salary > 1000);
select (exists (select *from emp where salary > 10000));

漫天飞雪

公告