郑州网站建设seo优化,wordpress shortlink,搜索图片识别,iis怎么做网站空间主要内容#xff1a;
子查询#xff08;单行单列、多行单列、单行多列、多行多列#xff09;、分页查询limit、联合查询union、插入语句、修改语句、删除语句 一、子查询
子查询就是指的在一个完整的查询语句之中#xff0c;嵌套若干个不同功能的小查询#xff0c;从而一…主要内容
子查询单行单列、多行单列、单行多列、多行多列、分页查询limit、联合查询union、插入语句、修改语句、删除语句 一、子查询
子查询就是指的在一个完整的查询语句之中嵌套若干个不同功能的小查询从而一起完成复杂查询的一种编写形式
1子查询返回的数据分类
单行单列返回的是一个具体列的内容可以理解为一个单值数据单行多列返回一行数据中多个列的内容多行单列返回多行记录之中同一列的内容相当于给出了一个操作范围多行多列查询返回的结果是一张临时表
2子查询常出现的位置 - select之后仅支持单行单列 - from之后支持多行多列 - where或having之后支持单行单列、单行多列、多行单列 子查询应用实例
1单行单列
例如查询运维部所有员工信息
① 分析首先从departments部门表中查出运维部的编号
mysql select dept_id from departments where dept_name运维部;
---------
| dept_id |
---------
| 3 |
---------
1 row in set (0.00 sec)
② 组合再从employees员工表中查找该部门编号和运维部编号相同的员工
mysql select * from employees - where dept_id(select dept_id from departments where dept_name运维部);
-------------------------------------------------------------------------------------------
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
-------------------------------------------------------------------------------------------
| 14 | 廖娜 | 2012-05-20 | 1982-06-22 | liaonatarena.com | 15827928192 | 3 |
| 15 | 窦红梅 | 2018-03-16 | 1971-09-09 | douhongmeitedu.cn | 15004739483 | 3 |
| 16 | 聂想 | 2018-09-09 | 1999-06-05 | niexiangtedu.cn | 15501892446 | 3 |
| 17 | 陈阳 | 2004-09-16 | 1991-04-10 | chenyangtedu.cn | 15565662056 | 3 |
| 18 | 戴璐 | 2001-11-30 | 1975-05-16 | dailutedu.cn | 13465236095 | 3 |
| 19 | 陈斌 | 2019-07-04 | 2000-01-22 | chenbintarena.com | 13621656037 | 3 |
-------------------------------------------------------------------------------------------
6 rows in set (0.00 sec) 例如查询2018年12月所有比100号员工基本工资高的工资信息
① 分析首先查询2018年12月100号员工的基本工资
mysql select basic from salary- WHERE YEAR(date)2018 and MONTH(date)12 and employee_id100;
-------
| basic |
-------
| 14585 |
-------
1 row in set (0.00 sec)
② 组合再查询2018年12月所有比100号员工基本工资高的工资信息
mysql select employee_id,date,basic from salary- where YEAR(date)2018 and MONTH(date)12- and basic(select basic from salary WHERE YEAR(date)2018 and MONTH(date)12 and employee_id100);
--------------------------------
| employee_id | date | basic |
--------------------------------
| 1 | 2018-12-10 | 17016 |
| 2 | 2018-12-10 | 20662 |
| 4 | 2018-12-10 | 17016 |
| 127 | 2018-12-10 | 17016 |
--------------------------------
65 rows in set (0.00 sec)
③ 扩展通过内连接查询2018年12月所有比100号员工基本工资高的工资信息
mysql select s.employee_id,date,basic,name from salary s- inner join employees e- on s.employee_id e.employee_id- where year(date)2018 and month(date)12 and basic (- select basic from salary where year(date)2018 and month(date)12 and employee_id100);
-------------------------------------------
| employee_id | date | basic | name |
-------------------------------------------
| 1 | 2018-12-10 | 17016 | 梁伟 |
| 2 | 2018-12-10 | 20662 | 郭岩 |
| 4 | 2018-12-10 | 17016 | 张健 |
| 127 | 2018-12-10 | 17016 | 李瑞 |
-------------------------------------------
65 rows in set (0.00 sec) 例如查询部门员工人数比开发部人数少的部门
① 分析1首先从departments表中查询开发部的编号
mysql select dept_id from departments where dept_name开发部;
---------
| dept_id |
---------
| 4 |
---------
1 row in set (0.00 sec)
② 组合再从employees表中查询并统计开发部的员工人数
mysql select count(*) from employees where dept_id(- select dept_id from departments where dept_name开发部);
----------
| count(*) |
----------
| 55 |
----------
1 row in set (0.00 sec)
③ 分析2分组查询各部门人数
mysql select dept_id,count(*) from employees group by dept_id;
-------------------
| dept_id | count(*) |
-------------------
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 4 | 55 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
| 9 | 3 |
-------------------
9 rows in set (0.00 sec)
④ 组合查询部门员工人数比开发部人数少的部门
mysql select dept_id,COUNT(*) from employees group by dept_id- having count(*) (select count(*) from employees - WHERE dept_id(select dept_id from departments where dept_name开发部));
-------------------
| dept_id | COUNT(*) |
-------------------
| 1 | 8 |
| 2 | 5 |
| 3 | 6 |
| 5 | 12 |
| 6 | 9 |
| 7 | 35 |
| 8 | 3 |
-------------------
7 rows in set (0.00 sec) 例如查询每个部门的人数显示部门ID部门名字及人数总数别名amount
① 分析查询所有部门的信息
mysql select d.* from departments as d;
--------------------
| dept_id | dept_name |
--------------------
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
| 9 | NULL |
--------------------
9 rows in set (0.00 sec)
② 组合查询每个部门的人数
mysql select d.*, (- select count(*) from employees as e- where d.dept_ide.dept_id- ) as amount- from departments as d;
----------------------------
| dept_id | dept_name | amount |
----------------------------
| 1 | 人事部 | 8 |
| 2 | 财务部 | 5 |
| 3 | 运维部 | 6 |
| 4 | 开发部 | 55 |
| 5 | 测试部 | 12 |
| 6 | 市场部 | 9 |
| 7 | 销售部 | 35 |
| 8 | 法务部 | 3 |
| 9 | NULL | 0 |
----------------------------
9 rows in set (0.00 sec)
扩展通过内连接实现查询每个部门的人数结果相同
mysql select d.dept_id,d.dept_name,count(*) from employees e- inner join departments d- on e.dept_id d.dept_id- group by dept_id ; 2多行多列
例如查询人事部和财务部员工信息
方法1
① 分析查询人事部和财务部部门编号
mysql select dept_id from departments where dept_name人事部;
mysql select dept_id from departments where dept_name财务部;
② 组合查询部门编号是两个部门编号的员工信息
mysql select employee_id,name from employees- where dept_id(select dept_id from departments where dept_name人事部) or- dept_id(select dept_id from departments where dept_name财务部); - 方法2
① 分析查询人事部和财务部部门编号
mysql select dept_id from departments where dept_name in (人事部,财务部);
---------
| dept_id |
---------
| 1 |
| 2 |
---------
2 rows in set (0.00 sec)
② 组合查询部门编号是两个部门编号的员工信息
mysql select employee_id,name from employees- where dept_id in (select dept_id from departments where dept_name in (人事部,财务部));
------------------------
| employee_id | name |
------------------------
| 1 | 梁伟 |
| 2 | 郭岩 |
| 3 | 李玉英 |
| 4 | 张健 |
| 5 | 郑静 |
| 6 | 牛建军 |
| 7 | 刘斌 |
| 8 | 汪云 |
| 9 | 张建平 |
| 10 | 郭娟 |
| 11 | 郭兰英 |
| 12 | 王英 |
| 13 | 王楠 |
------------------------
13 rows in set (0.00 sec) 例如查询人事部2018年12月所有员工工资
① 分析查询人事部部门编号
mysql select dept_id from departments where dept_name人事部;
---------
| dept_id |
---------
| 1 |
---------
1 row in set (0.00 sec)
② 分析查询人事部人员的编号
mysql select employee_id from employees- where dept_id(select dept_id from departments where dept_name人事部);
-------------
| employee_id |
-------------
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
-------------
8 rows in set (0.00 sec)
③ 组合查询人事部所有员工2018年12月的工资
mysql select * from salary- where year(date)2018 and month(date)12 and employee_id in (- select employee_id from employees- where dept_id(select dept_id from departments where dept_name人事部)- );
---------------------------------------------
| id | date | employee_id | basic | bonus |
---------------------------------------------
| 6252 | 2018-12-10 | 1 | 17016 | 7000 |
| 6253 | 2018-12-10 | 2 | 20662 | 9000 |
| 6254 | 2018-12-10 | 3 | 9724 | 8000 |
| 6255 | 2018-12-10 | 4 | 17016 | 2000 |
| 6256 | 2018-12-10 | 5 | 17016 | 3000 |
| 6257 | 2018-12-10 | 6 | 17016 | 1000 |
| 6258 | 2018-12-10 | 7 | 23093 | 4000 |
| 6259 | 2018-12-10 | 8 | 23093 | 2000 |
---------------------------------------------
8 rows in set (0.00 sec)
扩展通过内连接查询人事部2018年12月所有员工工资
mysql select date,name,basic from salary as s- inner join employees as e- on s.employee_id e.employee_id- WHERE year(date)2018 AND MONTH(date)12 AND s.employee_id in(- select employee_id from employees where dept_id(select dept_id from departments where dept_name人事部)- );
------------------------------
| date | name | basic |
------------------------------
| 2018-12-10 | 梁伟 | 17016 |
| 2018-12-10 | 郭岩 | 20662 |
| 2018-12-10 | 李玉英 | 9724 |
| 2018-12-10 | 张健 | 17016 |
| 2018-12-10 | 郑静 | 17016 |
| 2018-12-10 | 牛建军 | 17016 |
| 2018-12-10 | 刘斌 | 23093 |
| 2018-12-10 | 汪云 | 23093 |
------------------------------
8 rows in set (0.00 sec) 3单行多列
例如查找2018年12月基本工资和奖金都是最高的工资信息
① 分析查询2018年12月最高的基本工资
mysql select max(basic) from salary- where year(date)2018 and month(date)12;
------------
| max(basic) |
------------
| 25524 |
------------
1 row in set (0.00 sec)
② 分析查询2018年12月最高的奖金
mysql select max(bonus) from salary- where year(date)2018 and month(date)12;
------------
| max(bonus) |
------------
| 11000 |
------------
1 row in set (0.00 sec)
③ 组合查找2018年12月基本工资和奖金都是最高的工资信息
mysql select * from salary- where year(date)2018 and month(date)12- and basic (select max(basic) from salary where year(date)2018 and month(date)12)- and bonus (select max(bonus) from salary where year(date)2018 and month(date)12);
---------------------------------------------
| id | date | employee_id | basic | bonus |
---------------------------------------------
| 6368 | 2018-12-10 | 117 | 25524 | 11000 |
---------------------------------------------
1 row in set (0.01 sec) 4多行多列
例如查询3号部门及其部门内员工的编号、名字和email
① 分析使用内连接查询3号部门和员工的所有信息
mysql select d.dept_name, e.* from departments as d- inner join employees as e- on d.dept_ide.dept_id;
② 分析将上述结果当成一张临时表必须为其起别名。再从该临时表中查询
mysql select dept_id, dept_name, employee_id, name, email- from (- select d.dept_name, e.* from departments as d- inner join employees as e- on d.dept_ide.dept_id ) as tmp_table- where dept_id3;
----------------------------------------------------------------
| dept_id | dept_name | employee_id | name | email |
----------------------------------------------------------------
| 3 | 运维部 | 14 | 廖娜 | liaonatarena.com |
| 3 | 运维部 | 15 | 窦红梅 | douhongmeitedu.cn |
| 3 | 运维部 | 16 | 聂想 | niexiangtedu.cn |
| 3 | 运维部 | 17 | 陈阳 | chenyangtedu.cn |
| 3 | 运维部 | 18 | 戴璐 | dailutedu.cn |
| 3 | 运维部 | 19 | 陈斌 | chenbintarena.com |
----------------------------------------------------------------
6 rows in set (0.00 sec)
扩展直接使用内连接增加where条件判断实现如上案例
mysql select employee_id,name,email,dept_name from employees e- inner join departments d- on e.dept_id d.dept_id- where e.dept_id3;
-------------------------------------------------------
| employee_id | name | email | dept_name |
-------------------------------------------------------
| 14 | 廖娜 | liaonatarena.com | 运维部 |
| 15 | 窦红梅 | douhongmeitedu.cn | 运维部 |
| 16 | 聂想 | niexiangtedu.cn | 运维部 |
| 17 | 陈阳 | chenyangtedu.cn | 运维部 |
| 18 | 戴璐 | dailutedu.cn | 运维部 |
| 19 | 陈斌 | chenbintarena.com | 运维部 |
-------------------------------------------------------
6 rows in set (0.00 sec) 二、分页查询
使用SELECT查询时当结果集数据量很大比如几万行数据放在一个页面显示的话数据量太大若通过分页显示每次显示100条可减缓阅读数据压力要实现分页功能实际上就是从结果集中显示第1至100条记录作为第1页显示第101至200条记录作为第2页以此类推
分页从结果集中“截取”出第M至N条记录。这个查询可以通过LIMIT , 子句实现起始索引从0开始每页显示内容速算LIMIT (PAGE-1)*SIZE, SIZE
例如按employee_id升序排序取出前5位员姓名
mysql select employee_id,name from employees- order by employee_id- LIMIT 0,5;
------------------------
| employee_id | name |
------------------------
| 1 | 梁伟 |
| 2 | 郭岩 |
| 3 | 李玉英 |
| 4 | 张健 |
| 5 | 郑静 |
------------------------
5 rows in set (0.00 sec)
例如按employee_id升序排序取出6号至8号员姓名
mysql select employee_id,name from employees- order by employee_id- LIMIT 5,3;
------------------------
| employee_id | name |
------------------------
| 6 | 牛建军 |
| 7 | 刘斌 |
| 8 | 汪云 |
------------------------
3 rows in set (0.00 sec) 三、联合查询UNION相当于全外连接
作用将多条select语句的结果合并到一起称之为联合操作。
语法( ) UNION ( ); 要求查询时多个select语句的检索到的字段数量必须一致 每一条记录的各字段类型和顺序最好是一致的 UNION关键字默认去重可以使用UNION ALL包含重复项 例如
mysql (select yes) union (select yes);
-----
| yes |
-----
| yes |
-----
1 row in set (0.00 sec)mysql (select yes) union all (select yes);
-----
| yes |
-----
| yes |
| yes |
-----
2 rows in set (0.00 sec)
例如查询1972年前或2000年出生的员工两种方法查询结果数量相同
- 普通方法过滤
mysql select name,birth_date from employees
- where year(birth_date)1972 or year(birth_date)2000;
- 联合查询方法拼接
mysql (select name,birth_date from employees where year(birth_date)1972) UNION
- (select name,birth_date from employees where year(birth_date)2000); 四、插入语句
1、不指定字段名的插入
格式INSERT INTO VALUES (值1, 值2,....);格式INSERT INTO VALUES (值1, 值2,....) , (值1, 值2,....) ...;支持多行VALUES插入
注意事项 ① 需要为所有的字段指定值 ② 值的顺序必须与表中字段的顺序一致 ③ 字符类型的字段要用引号 “ ” 括起来 例如给departments表中插入VALUES值dept_id,dept_name
mysql insert into departments values(10, 行政部);
Query OK, 1 row affected (0.01 sec)
常见报错表中已有dept_id为1号部门再插入则出错dept_id是主键不允许重复
mysql insert into departments values(1, 行政部);
ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY 例如给employees表中插入VALUES值employee_id,name,hire_date,birth_date,email,phone_number,dept_id
mysql insert into employees values- (134, 张三, 2019-5-10, 2000-10-12, zhangsantedu.cn, 15088772354, 9),- (135, 李四, 2020-8-20, 1999-6-23, lisitedu.cn, 13323458734, 9);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0 2、指定字段名的插入
格式INSERT INTO (字段1,字段2,...) VALUES (值1,值2,....);格式INSERT INTO (字段1,字段2,...) VALUES (值1,值2,....) , (值1,值2,....);若设置主键是自动增长的可以不指定主键的值支持子查询插入
注意事项 ① 字段和值的顺序要一致字段1,字段2--值1,值2 ② 字段名先后顺序不重要 ③ 没有赋值的字段使用默认值或自增长赋值 例如给departments表中插入VALUES值dept_id,dept_name字段先后顺序不重要
mysql insert into departments (dept_name, dept_id) values (售后部, 11);
Query OK, 1 row affected (0.00 sec)
例如主键由于是自动增长的【auto_increment】可以不指定主键的值
mysql desc departments;
------------------------------------------------------------
| Field | Type | Null | Key | Default | Extra |
------------------------------------------------------------
| dept_id | int(4) | NO | PRI | NULL | auto_increment |
| dept_name | varchar(10) | YES | | NULL | |
------------------------------------------------------------
2 rows in set (0.00 sec)mysql insert into departments (dept_name) values (咨询部);
Query OK, 1 row affected (0.00 sec)
例如支持子查询插入
mysql insert into employees- (name,hire_date,birth_date,email,phone_number,dept_id)- (- select name,hire_date,birth_date,email,phone_number,dept_id from employees- where name张三- );
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0 3、使用Set语句
格式INSERT INTO SET 字段名1字段值1, 字段名2字段值2, ...;
例如
mysql insert into departments set dept_name采购部; //dept_id自动递增
Query OK, 1 row affected (0.00 sec)
mysql select * from departments;
--------------------
| dept_id | dept_name |
--------------------
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
| 9 | Null |
| 10 | 行政部 |
| 11 | 售后部 |
| 12 | 咨询部 |
| 13 | 采购部 |
--------------------
13 rows in set (0.00 sec) 五、修改语句
1、修改单表记录
格式UPDATE SET 字段名1字段值1, 字段名2字段值2, ... WHERE 筛选条件;
注意事项 - 字段值要与字段类型相匹配 - 对于字符类型的字段值要用双引号“”括起来 - 若不使用where限定条件会更新所有记录字段值 - 限定条件时只更新匹配条件的记录的字段值 例如修改人事部的名称为人力资源部
mysql update departments set dept_name人力资源部- where dept_name人事部;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 2、修改多表连接记录
格式 UPDATE 表1 AS 表1别名 INNER | LEFT | RIGHT JOIN 表2 AS 表2别名 ON 连接条件 SET 字段名1字段值1, 字段名2字段值2, ... WHERE 连接条件; 例如修改李四所在部门为企划部
mysql update departments as d- inner join employees as e- on d.dept_ide.dept_id- set d.dept_name企划部- where e.name李四;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql select * from departments;
--------------------
| dept_id | dept_name |
--------------------
| 1 | 人事部 |
| 2 | 财务部 |
| 3 | 运维部 |
| 4 | 开发部 |
| 5 | 测试部 |
| 6 | 市场部 |
| 7 | 销售部 |
| 8 | 法务部 |
| 9 | 企划部 |
| 10 | 行政部 |
| 11 | 售后部 |
| 12 | 咨询部 |
| 13 | 采购部 |
--------------------
13 rows in set (0.00 sec) 六、删除记录
1、删除单表记录
格式DELETE FROM WHERE 筛选条件; 删除的是满足条件的整行记录而不是某个字段 注意事项不加条件删除表中的所有行
例如删除重复的员工张三只保留一个张三的信息
mysql select * from employees where name张三;
--------------------------------------------------------------------------------------
| employee_id | name | hire_date | birth_date | email | phone_number | dept_id |
--------------------------------------------------------------------------------------
| 134 | 张三 | 2019-05-10 | 2000-10-12 | zhangsantedu.cn | 15088772354 | 9 |
| 136 | 张三 | 2019-05-10 | 2000-10-12 | zhangsantedu.cn | 15088772354 | 9 |
--------------------------------------------------------------------------------------
2 rows in set (0.00 sec)
mysql delete from employees where employee_id136;
Query OK, 1 row affected (0.00 sec) 2、删除多表记录
格式 DELETE 表1别名, 表2别名 FROM 表1 AS 表1别名 INNER | LEFT | RIGHT JOIN 表2 AS 表2别名 ON 连接条件 WHERE 筛选条件; 例如删除9号部门中所有的员工
mysql delete e- from employees as e- inner join departments as d- on e.dept_id d.dept_id- where d.dept_id 9;
Query OK, 2 rows affected (0.00 sec) 3、清空表的表记录
格式TRUNCATE TABLE 表名; - TRUNCATE不支持WHERE条件 - 自增长列TRUNCATE后从1开始DELETE继续编号 - TRUNCATE不能回滚DELETE可以 - 效率略高于DELETE 例如清空wage_grade表
mysql truncate table wage_grade;
Query OK, 0 rows affected (0.01 sec) 补充drop,delete 与 truncate 的区别?
1.TRUNCATE 和 DELETE 只删除数据而 DROP 则删除整个表结构和数据。
2. DELETE 删除操作作为事务记录在日志中保存以便进行进行回滚操作。
3. TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
4. 表和索引所占空间。当表被 TRUNCATE 后这个表和索引所占用的空间会恢复到初始大小而 DELETE 操作不会减少表或索引所占用的空间。drop 语句将表所占用的空间全释放掉。 思维导图 小结
本篇章节为【第四阶段】RDBMS1-DAY3 的学习笔记这篇笔记可以初步了解到 子查询单行单列、多行单列、单行多列、多行多列、分页查询limit、联合查询union、插入语句、修改语句、删除语句。 Tip毕竟两个人的智慧大于一个人的智慧如果你不理解本章节的内容或需要相关笔记、视频可私信小安请不要害羞和回避可以向他人请教花点时间直到你真正的理解。
