MySQL基础之查询(DQL)

#SQL的语言分类

• DQL（Data Query Language）：数据查询语言
  select
• DML(Data Manipulate Language):数据操作语言
  insert 、update、delete
• DDL（Data Define Languge）：数据定义语言
  create、drop、alter
• TCL（Transaction Control Language）：事务控制语言
  commit、rollback

一、MySQL服务的启动和停止

方式一：计算机——右击管理——服务

方式二：通过管理员身份运行

net start 服务名（启动服务）
net stop 服务名（停止服务）

二、MySQL服务的登录和退出

方式一：通过mysql自带的客户端

只限于root用户

方式二：通过windows自带的客户端

#登录：
mysql 【-h主机名 -P端口号 】-u用户名 -p密码

#退出：
exit或ctrl+C

三、MySQL的常见命令

1. 查看当前所有的数据库

show databases;

2. 打开指定的库

use 库名

3. 查看当前库的所有表

show tables;

4. 查看其它库的所有表

show tables from 库名;

5. 创建表

create table 表名(

	列名 列类型,
	列名 列类型，
	。。。
);

6. 查看表结构

desc 表名;

7. 查看服务器的版本

#方式一：登录到mysql服务端
select version();
#方式二：没有登录到mysql服务端
mysql --version
或
mysql --V

四、MySQL的语法规范

1. 不区分大小写,但建议关键字大写，表名、列名小写
2. 每条命令最好用分号结尾
3. 每条命令根据需要，可以进行缩进 或换行
4. 注释

• 单行注释：#注释文字
• 单行注释：-- 注释文字
• 多行注释：/* 注释文字 */

五、查询（DQL）

1.基础查询

SELECT 要查询的东西
【FROM 表名】;

类似于Java中 :System.out.println(要打印的东西);

特点：

①通过select查询完的结果 ，是一个虚拟的表格，不是真实存在

② 要查询的东西 可以是常量值、可以是表达式、可以是字段、可以是函数

2.条件查询

select 
	要查询的字段|表达式|常量值|函数
from 
	表
where 
	条件 ;

2.1 分类

2.1.1 条件表达式

示例：salary>10000

条件运算符：

< >= <= = != <>

2.1.2 逻辑表达式

示例：salary>10000 && salary<20000

逻辑运算符：

• and（&&）:两个条件如果同时成立，结果为true，否则为false
• or(||)：两个条件只要有一个成立，结果为true，否则为false
• not(!)：如果条件成立，则not后为false，否则为true

2.1.3 模糊查询

示例：last_name like ‘a%’

2.2 补充

2.2.1 安全等于 <=>

可以用于判断值和是否为null

#查询没有奖金的员工名和奖金率
SELECT
	last_name,
	commission_pct
FROM
	employees
WHERE
	commission_pct <=> NULL;

#查询工资为12000的员工信息
SELECT
	last_name,
	salary,
	commission_pct
FROM
	employees
WHERE
	salary <=> 12000;

2.2.2 经典面试题

问：select * from employee; 和 select * from employee where commission_pic like ‘%%’ and last_name like ‘%%’;结果是否一样？并说明原因。

可能不一样！如果判断的字段有null值，则该记录无法被匹配到。如果将and改成or并且加上所有字段作为匹配条件，则结果一致。

3.排序查询

select
	要查询的东西
from
	表
where 
	条件
order by 排序的字段|表达式|函数|别名 【asc|desc】

3.1 特点

1. asc代表升序，desc代表降序。不写默认升序
2. order by子句中可以支持单个字段、多个字段、表达式、函数、别名
3. order by子句一般是放在查询语句的最后面，limit子句除外

4.常见函数

4.1 单行函数

4.1.1 字符函数

concat拼接

substr截取子串(substring)

upper转换成大写

lower转换成小写

trim去前后指定的空格和字符

select trim('a' from 'aaaaa你aaaa好aaaa!aaaaa') as out_put;
#结果为:你aaaa好aaaa!

ltrim去左边空格

rtrim去右边空格

replace替换

lpad左填充

select lpad('林有有',2,'*') as out_put;
#结果为：林有

select lpad('林有有',5,'*') as out_put;
#结果为：**林有有

rpad右填充

instr返回子串第一次出现的索引,找不到返回0

length 获取字节个数

4.1.2 数学函数

round 四舍五入

select round(1.55); #2
select round(1.45); #1
select round(-1.55); #-2
select round(-1.45); #-1
select round(1.556,2); #1.56

rand 随机数

floor向下取整，返回<=该参数的最大整数

ceil向上取整,返回>=该参数的最小整数

mod取余,结果和被除数符号一致，mod(a,b)=a-a/b*b

truncate截断

select truncate(1.6664,1) #1.6

4.1.3 日期函数

now当前系统日期+时间

curdate当前系统日期

curtime当前系统时间

str_to_date 将字符转换成日期

select str_to_date('1998-2-3','%Y-%c-%d') as out_put;

date_format将日期转换成字符

select date_format(now(),'%Y-%c-%d') as out_put;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Nb0xWW0e-1651736951076)(.\时间日期格式.png)]

4.1.4 流程控制函数

if 处理双分支

case语句 处理多分支

• 情况1：处理等值判断

case 要判断的字段活表达式
when 常量1 then 要显示的值1或语句1；
when 常量2 then 要显示的值2或语句2；
···
else 要显示的值n或语句n；
end

/*查询员工的工资，要求：
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/
select salary 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employee;

• 情况2：处理条件判断

case 
when 条件1 then 要显示的值1或语句1；
when 条件2 then 要显示的值2或语句2；
···
else 要显示的值n或语句n；
end

/*查询员工的工资，要求：
如果工资>20000，显示A级别
如果工资>15000，显示B级别
如果工资>10000，显示C级别
否则，显示D级别
*/
select salary 原始工资,
case 
when salary>20000 then 'A级别'
when salary>15000 then 'B级别'
when salary>10000 then 'C级别'
else 'D级别'
end as 工资级别
from employee;

4.1.5 其他函数

version版本

database当前库

user当前连接用户

4.2 分组函数（统计函数、聚合函数、组函数）

sum 求和

max 最大值

min 最小值

avg 平均值

count 计数

特点：

1. 以上五个分组函数都忽略null值，除了count(*)
2. sum和avg一般用于处理数值型
   max、min、count可以处理任何数据类型
3. 都可以搭配distinct使用，用于统计去重后的结果
4. count的参数可以支持：
   字段、、常量值，一般放1。建议使用 count()

效率：

MYISAM存储引擎下，count（*）的效率高

INNIDB存储引擎下，count（*）和count（1）的效率差不多，比count（字段）要高一些
5. 和分组函数一同查询的字段要求是group by后的字段

#案例：查询员工中最大入职时间和最小入职时间的相差天数（DIFFRENCE)
select datediff(max(hiredate),min(hiredate)) DIFFRENCE
from employees;

5.分组查询

select 查询的字段(要求出现在group by的后面)，分组函数
from 表
【where 筛选条件】
group by 分组的字段
【order by子句】
/*注意：
查询列表必须特殊，要求是分组函数和group by后出现的字段
*/

特点：

1. 可以按单个字段分组
2. 和分组函数一同查询的字段最好是分组后的字段
3. 分组筛选
   针对的表 位置 关键字
   分组前筛选： 原始表 group by的前面 where

分组后筛选： 分组后的结果集 group by的后面 having

分组函数做条件肯定是放在having子句中

能用分组前筛选的，就优先考虑使用分组前筛选
4. 可以按多个字段分组，字段之间用逗号隔开，没有顺序要求
5. 可以支持排序，排序放在整个分组查询的最后
6. having后可以支持别名

#查询每个工种的最高工资
select max(salary),job_id
from employees
group by job_id;

#查询每个位置上的部门个数
select count(*),location_id
from departments
group by location_id;

#查询员工个数>2的部门
select count(*),department_id
from employees
group by department_id
having count(*)>2;

6.多表连接查询

笛卡尔乘积：表1有m行，表2有n行，结果有m*n行。如果连接条件省略或无效则会出现
解决办法：添加上连接条件

6.1 分类

6.1.1 按年代

• sql92标准：仅支持内连接
• sql99标准【推荐】：支持内连接+外连接（左外和右外）+交叉连接

6.1.2 按功能

• 内连接:inner
  • 等值连接
  • 非等值连接
  • 自连接
• 外连接
  • 左外连接:left [outer]
  • 右外连接:right [outer]
  • 全外连接:full [outer]
• 交叉连接:cross

6.2 传统模式下的连接 ：等值连接——非等值连接

1. 等值连接的结果 = 多个表的交集
2. n表连接，至少需要n-1个连接条件
3. 多个表不分主次，没有顺序要求
4. 一般为表起别名，提高阅读性和性能
5. 注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

6.3 sql99语法：通过join关键字实现连接

语法：

select 字段，...
from 表1
【inner|left outer|right outer|cross】join 表2 on  连接条件
【inner|left outer|right outer|cross】join 表3 on  连接条件
【where 筛选条件】
【group by 分组字段】
【having 分组后的筛选条件】
【order by 排序的字段或表达式】

好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

6.4 自连接

案例：查询员工名和直接上级的名称

sql99

SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m ON e.`manager_id`=m.`employee_id`;

sql92

SELECT e.last_name,m.last_name
FROM employees e,employees m 
WHERE e.`manager_id`=m.`employee_id`;

6.5 外连接

应用场景：一个表中有，一个表中没有的记录

特点：

1. 外连接的查询结果为主表中的所有记录
   1. 如果从表中有和它匹配的，则显示匹配的值
   2. 如果从表中没有和它匹配的，则显示null
   3. 外连接查询结果=内连接结果+主表中有而从表中没有的记录
2. 左外连接，left join左边的是主表
3. 右外连接，right join右边的是主表
4. 左外和右外交换两个表的顺序，可以实现相同的效果
5. 全外连接=内连接结果+表1中有但表2中没有的+表2中有但表1中没有的记录

6.5.1 左外连接

select d.*,e.employee.id
from employees e
left outer join departments d
on d.department_id = e.department_id

6.5.2 右外连接

select d.*,e.employee.id
from employees e
right outer join departments d
on d.department_id = e.department_id;

6.5.3 全外连接

select b.*,bo.*
from beauty b
full outer join boys bo
on b.boyfriend_id = bo.id;

6.5.4 交叉连接

笛卡尔积

select b.*,bo.*
from beauty b
cross join boys bo;

7.子查询

7.1 含义

一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询在外面的查询语句，称为主查询或外查询

7.2 分类

1. 按子查询出现的位置：

• select后面：仅仅支持标量子查询
• from后面：支持表子查询
• where或having后面：支持标量子查询、列子查询、行子查询（较少）
• exists后面（相关子查询）：支持表子查询

2. 按结果集的行列数不同：

• 标量子查询（结果集只有一行一列）
• 列子查询（结果集只有一列多行）
• 行子查询（结果集有一行多列）
• 表子查询（结果集一般为多行多列）

7.3 where或having后面

特点：

1. 子查询放在小括号内
2. 子查询一般放在条件的右侧
3. 标量子查询，一般搭配着单行操作符使用： > < >= <= = <>
4. 列子查询，一般搭配着多行操作符使用：in any/some all
5. 子查询的执行优先与主查询执行，主查询的条件用到了子查询的结果

7.3.1 标量子查询（单行）

案例1：谁的工资比Abel高？

#step1:查询Abel的工资
select salary
from employees
where last_name = 'Abel'

#step2:查询员工的信息，满足salary>step1的结果
select *
from employees
where salary>(
	select salary
	from employees
	where last_name = 'Abel'
);

案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id和工资

#step1：查询141号员工的job_id
select job_id
from employees
where employee_id = 141

#step2:查询143号员工的salary
select salary
from employees
where employee_id = 143

#step3:查询员工姓名，job_id和工资，满足step1和step2结果
select name,job_id,salary
from employees
where job_id = (
	select job_id
	from employees
	where employee_id = 141
) and salary>(
	select salary
	from employees
	where employee_id = 143
)

案例3：返回公司工资最少的员工的last_name，job_id和salary

#step1:查询公司的最低工资
select min(salary)
from employees

#step2:查询员工的last_name,job_id和salary,满足salary=step1结果
select last_name，job_id,salary
from employees
where salary = (
	select min(salary)
	from employees
);

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资

#step1:查询50号部门的最低工资
select salary
from employees
where department_id = 50

#step2:查询每个部门最低工资
select min(salary),department_id
from employees
group by department_id

#step3:筛选step2，满足min（salary）>step1
select min(salary),department_id
from employees
group by department_id
having min(salary)>(
	select salary
	from employees
	where department_id = 50
)

7.3.2 列子查询（多行）

使用多行比较操作符：

in/not in : 等于列表中的任意一个（= any）

any|some : 和子查询返回的某一个值比较

all : 和子查询返回的所有值比较

案例1：返回location_id是1400或1700的部门的所有员工姓名

#step1:查询location_id是1400或1700的部门编号
select distinct department_id
from employees
where location_id in (1400,1700)

#step2:查询员工姓名，满足部门id=step1的结果中的一个
select name 
from employee
where department_id in(
	select distinct department_id
	from employees
	where location_id in (1400,1700)
);

案例2：返回其他工种中比job_id为’IT_PROG’部门任一工资低的员工的员工号、姓名、job_id、salary

#step1:查询job_id为'IT_PROG'部门任一工资
select distinct salary
from employees
where job_id = 'IT_PROG'

#step2:查询员工的员工号、姓名、job_id、salary，满足salary比step1结果中任一低
select employee_id,name,job_id,salary
from employees
where salary < any(
	select distinct salary
	from employees
	where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG';
#小于任一个，等价于小于最大值

案例2：返回其他工种中比job_id为’IT_PROG’部门所有工资低的员工的员工号、姓名、job_id、salary

#step1:查询job_id为'IT_PROG'部门任一工资
select distinct salary
from employees
where job_id = 'IT_PROG'

#step2:查询员工的员工号、姓名、job_id、salary，满足salary比step1结果中所有低
select employee_id,name,job_id,salary
from employees
where salary < all(
	select distinct salary
	from employees
	where job_id = 'IT_PROG'
) and job_id <> 'IT_PROG';
#小于所有，等价于小于最小值

7.3.3 行子查询（多列多行）

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

select *
from employees
where (employee_id,salary)=(
	select min(employee_id),max(salary)
	from employees
);

7.4 select后面

案例1：查询每个部门的员工个数

select d.*,(
	select count(*)
	from employees e
	where e.department_id = d.'department_id'
) 个数
from departments d;

案例2：查询员工号=102的部门名

select (
	select department_name
	from departments d
	inner join employees e
	on d.department_id = e.department_id
	where e.employee_id=102
) 部门名;

7.5 from后面

将子查询结果充当一张表，要求必须起别名

案例1：查询每个部门的平均工资的工资等级

#sep1:查询每个部门的平均工资
select avg(salary),department_id
from employees
group by department_id

#step2:连接step1的结果集和job_grades表，筛选条件平均工资between lowest_sal and highest_sal
select ag_dep.*,g.'grade_level'
from (
	select avg(salary) ag,department_id
	from employees
	group by department_id
) ag_dep
inner join job_grades g
on ag_dep.ag between lowest_sal and highest_sal

7.6 exists后面（相关子查询）

语法：exists（完整的查询语句）

结果：1或0

案例1：查询有员工的部门名

select department_name
from departments
where exists(
	select * 
	from employees e
	where d.'department_id' = e.'department_id'
);

案例2：查询没有女朋友的男神信息

#in方式
select bo.* 
from boys bo
where bo.id not in(
	select boyfriend_id
	from beauty
);

#exists方式
select bo.*
from boys bo
where not exists(
	select boyfriend_id
	from beauty b
	where bo.'id'=b.'boyfriend_id'
);

8.分页查询

8.1 应用场景

实际的web项目中需要根据用户的需求提交对应的分页查询的sql语句

8.2 语法

select 字段|表达式,...
from 表
【where 条件】
【group by 分组字段】
【having 条件】
【order by 排序的字段】
limit 【起始的条目索引，】条目数;

8.3 特点

1. 起始条目索引从0开始
2. limit子句放在查询语句的最后
3. 公式：select * from 表 limit （page-1）*sizePerPage,sizePerPage
   假如:
   每页显示条目数sizePerPage
   要显示的页数 page

select last_name,salary
from employee
where salary=(
select min(salary)
from employees
)

9.联合查询

union 联合、合并：将多条查询语句的结果合并成一个结果

9.1 特点

1. 多条查询语句的查询的列数必须是一致的
2. 多条查询语句的查询的列的类型和顺序最好一致
3. union代表去重，union all代表不去重

9.2 应用场景

要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时

9.3 案例

案例1：查询部门编号>90或邮箱包含a的员工信息

select * from employees where email like '%a%' or department_id>90;

#联合查询
select * from employees where email like '%a%'
union
select * from employees where department_id>90;

案例2：查询中国用户中男性的信息以及外国用户中男性的用户信息

select id,cname,csex from t_ca where csex='男'
union
select t_id,tName,tGender from t_ua where tGender='male';