MySQL知识整理（三）

MySQL子查询介绍和where后的标量子查询

子查询：出现在其他语句中的select语句，被包裹的select语句就是子查询或内查询。包裹子查询的外部的查询语句：称主查询语句
```
如：
select last_name from employees
where department_id in(
select department_id from departments
where location_id=1700
);
```
子查询分类：
通过位置来分:

select 后面:仅仅支持标量子查询

from 后面:支持表子查询

where 或having 后面:支持标量子查询(重要)\列子查询(重要)\行子查询(用的较少)

exists 后面(相关查询):支持表子查询

按结果集的行列数不同分类:

标量子查询(结果集只有一行一列)

列子查询(结果集只有一列但有多行)

行子查询(结果集只有一行但有多列)

表子查询(结果集多行多列)

子查询特点:

子查询放在小括号内

子查询一般放在条件的右侧

标量子查询,一般搭配着单行操作符来使用(> < >= <= <> =)

列子查询,一般搭配着多行操作符使用:in any/some all

子查询的执行顺序优先于主查询（select后的子查询存在例外）

1.where后面的标量子查询
案例:查询工资比Abel这个人的高的员工信息
select * from employees
where salary>(
select salary
from employees
where last_name='Abel'
);

2.查询job_id与141号员工相同,salary比143号员工多的员工姓名,job_id和工资
select last_name,job_id,salary
from employees
where job_id=(select job_id from employees
where employee_id=142 ) and salary>(select salary
from employees where employee_id=143);
(这个案例说明一个主查询里可以放很多个子查询)

3.子查询里用到分组函数:查询公司工资最少的员工的last_name,job_id和salary
select last_name,job_id,salary from employees
where salary=(select min(salary) from employees);

4.用到having的子查询:查询最低工资大于50号部门最低工资的部门id和其最低工资
select department_id , min(salary) from employees
group by department_id
having min(salary)>(select min(salary) from employees where department_id=50);

MySQL子查询之where后的列子查询和行子查询

where后面的列子查询(多行子查询)

首先来看一下多行操作符:
in/not in:等于列表中的任意一个
a in(10,20,30); 可以替换 a=10 or a=20 or a=30;
any/some:和子查询返回的某一个值比较
a > any(10,20,30); 可以替换 a > min(10,20,30);
all:和子查询返回的所有值比较
a > all(10,20,30); 可以替换 a > max(10,20,30);
a >10 and a>20 and a>30

案例
```
案例:返回location_id是14000或17000的部门中的所有员工的名字
select last_name from employees
where department_id in (select department_id from departments
where location_id in (14000,17000));
```
案例:查询其他工种中比job_id为'IT_PROG'的员工某一工资低的员工的员工号,姓名,job_id和salary

SELECT employee_id,last_name,job_id,salary FROM employees

WHERE salary < ANY(SELECT distinct salary FROM employees WHERE job_id='IT_PROG') and job_id<>'IT_PROG';

案例:查询其他工种中比job_id为'IT_PROG'的员工所有工资低的员工的员工号,姓名,job_id和salary

SELECT employee_id,last_name,job_id,salary FROM employees

WHERE salary < all(SELECT distinct salary FROM employees WHERE job_id='IT_PROG') and job_id<>'IT_PROG';

3.where后面的行子查询(一行多列)

案例:查询员工编号最小并且工资最高的员工信息

select * from employees

where

employee_id = (select min(employee_id) from employees)

and

salary = (select max(salary) from employees);

下面是行子查询的写法(用的很少,了解就可以):

select * from employees

where (employee_id,salary)=(

select min(employee_id) , max(salary) from employees

);

MySQL之select和from后面的子查询

案例:查询每个部门的部门信息和对应的员工个数(不用连接查询)
select d.*,(
select d.department_name count(*) from employees e
where d.department_id=e.department_id
) from departments d;


案例:查询员工号等于102的部门名(不用连接查询)
where d.department_id=e.department_id ) from employees e where employee_id = 102;

from后面的子查询:
案例:查询每个部门的平均工资等级
select avg(salary),department_id from employees group by department_id;

SELECT
avg_res.avgs,avg_res.`department_id`,g.`grade_level`
FROM
(
SELECT AVG(salary) avgs,department_id
FROM employees GROUP BY department_id
) avg_res
,`job_grades` g
WHERE
avg_res.avgs BETWEEN g.`lowest_sal` AND g.`highest_sal`;

MySQL之exists后面的子查询

exists的作用是：判断子查询有没有结果的存在

案例: select exists(select employee_id from employees); 返回的结果:1;

语法:
select exists(完整的子查询); 子查询有结果返回1,没有结果返回0;

案例:查询有员工的部门名
select department_name from departments d
where exists(select * from employees e where d.department_id=e.department_id );

注意:能用exists的绝对能用前面讲过的in来实现,所以exists很少使用
select department_name from departments d
where d.department_id in (
select department_id from employees
);

MySQL分页查询

　　语法：select 查询字段 from 表名 where 筛选条件 limit offset（开始记录的索引，从0开始的，0可以不写）,size（要取出的记录条数）

案例： 查询前5条员工数据
select * from employees limit 0,5;
注意:如果从第一条开始,这个0可以省略:select * form employees limit 5;

案例:查询第11条到第25条
select * from employees 10,15;

案例:查询有奖金且工资最高的前10名的员工信息
select * from employees where commission_pct is not null order by salary desc limit 10;

　　分页查询的特点:

　　①limit语句是位置上是要放在比order by语句的还后面,其次中sql执行过程中,limit也是最后去执行的语句.

　　②通用的分页查询写法(page:页数,size:每页显示的记录条数)

　　select 查询列表

　　from 表名

　　limit (page-1)*size,size;

　　记住每页的起始记录索引值:(page-1)*size

　　比如:size=10那么, page 和 size 记录范围和起始记录索引值 limit

　　1 10 1-10条 0,10

　　2 10 11-20条 10,10

　　3 10 21_30条 20,10

MySQL之union联合查询

联合查询:关键字union,作用就是将多条查询语句的结果合并成一个结果集;

案例:查询部门编号大于100或名字中含有a字符的员工信息
select * from employees where department_id>100 or last_name like '%a%';

用联合查询演示:
select * from employees where department_id>100
union
select * from employees where last_name like '%a%';

联合查询的特点:

①联合查询的两个子查询的查询字段个数要一致

②要求联合查询的子查询的查询列表顺序要在内容上保持一致

③使用union联合默认会自动去重,如果不想去重可以将union改为union all

MySQL数据表记录增操作

对数据库的操作用的最最频繁的呢,总结起来就四个字:增删改查!

查是属于DQL(Data QueryLanguage ，数据查询语言)部分,而增、改、删属于DML（Data Manipulation Language, 数据操纵语言）
增：作用是往数据库的数据表里写入记录值

语法一：

insert into 表名(字段1,字段2,.....)values(对应字段1的值,对应字段2点值,........);

案例:增加一条学生信息到students表中

注意:

①insert记录值时候要保持插入的记录值的类型和表结构设计的类型一致,除了数字类型外,字符串型varchar和日期类型datetime等都应该用单引号包裹起来

②表结构设计中规定不可为空的字段必须插入记录值,可以为null的列在插入的时候可以不写

③表名后的字段列表的顺序必须和values后面的值列表没有严格是顺序限制但必须前后一一对应(内容上一致,还有数量上一致,数据类型一致)

④表名后面的字段列可以连带括号都可以省略,省略后意味着后面的values值列表必须和表结构中所有字段一一对应.(不建议这样用)

INSERT INTO `students`(`sno`,`sname`,`ssex`,`sbirthday`,`class`)

VALUES('11234','任波涛','男','1990-1-12','11601')

语法二:

insert into 表名
set 字段名1=值,字段名2=值,........;
```
案例:增加一条学生信息到students表中
INSERT INTO `students`
SET `sno`='114',`sname`='田兴伟',`ssex`='男',`sbirthday`='1990-12-12',`class`='11601';
```
比较两种语法:

①语法一可以一次性插入多条记录值,语法二不支持

INSERT INTO `students`(`sno`,`sname`,`ssex`,`sbirthday`,`class`)

VALUES('132','任波涛','男','1990-1-12','11601'),

('133','任波涛','男','1990-1-12','11601'),....;

②语法一可以支持子查询,语法二不支持

INSERT INTO `students`(`sno`,`sname`,`ssex`,`sbirthday`,`class`)

select concat(`sno`,'1'),`sname`,`ssex`,`sbirthday`,`class` from students

where sno='132';

总结:我们一般情况下用语法一的写法,语法二很少使用

MySQL数据表记录改操作

修改语句:作用修改记录里的部分值

1.修改单表记录

语法:

update 表名 set 字段名1=新的值,字段名2=新值,.......where 条件;

案例:修改学生表中姓王的同学的班级都改为11601
UPDATE students SET class='11601' WHERE `sname` LIKE '王%';

2.修改多表记录

语法:

update 表1 别名,表2 别名

set 字段1=值.......

where 连接条件 and 筛选条件;

update 表1 别名

[连接类型] join 表2 别名

on 连接条件

set 字段1=值.......

where 筛选条件;

案例:把students你的任波涛的年龄改成18
update students s
inner join ages a
on s.age_id=a.id
set a.age=18
where s.name='任波涛';

案例:修改没有年龄记录的同学的对应年龄的编号都为3
update students s
left join ages a
on s.age_id=a.id
set s.age_id=3
where a.age is null;

posted @ 2018-12-10 17:04 疾风亦&有归途阅读(233) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

风不会停息clearlove

MySQL知识整理（三）

MySQL子查询介绍和where后的标量子查询

MySQL子查询之where后的列子查询和行子查询

MySQL之select和from后面的子查询

MySQL之exists后面的子查询

MySQL分页查询

MySQL之union联合查询

MySQL数据表记录增操作

MySQL数据表记录改操作

公告