02-select查询

✨你好啊，我是“ 罗师傅”，是一名程序猿哦。
🌍主页链接：楚门的世界 - 一个热爱学习和运动的程序猿
☀️博文主更方向为：分享自己的快乐 briup-jp3-ing
❤️一个“不想让我曾没有做好的也成为你的遗憾”的博主。
💪很高兴与你相遇，一起加油！

前言

目标：Mysql数据库的使用及数据库分析及设计实践

SQL语法规范

书写规范

注释

单行注释: #注释文字(MySQL特有的方式)
单行注释: -- 注释文字(--后面必须包含一个空格)
多行注释: /* 注释文字 */

命名规则

• 字母，数字，下划线，@，#，$符号组成
• 首字母不能是数字和$
• 不允许是Mysql的关键字以及保留字
• 不允许出现空格和特殊字符
• 长度小于128位
• 必须保证所有的()、单引号、双引号是成对结束的，必须使用英文状态下的半角输入方式
• 字符串型和日期时间类型的数据可以使用单引号 ‘ ‘ 表示
• 列的别名，尽量使用双引号 “ “ ,而且不建议省略 as

基础查询

基本语法

select [distinct] *{col_name1,col_name2,..} from tb_name;
select * from s_dept;
select id,name,region_id from s_dept;
select id,last_name,salary from s_emp;

设置别名

select old_column [as] new_column_name from tb_name; # 建议不要省略as
select id,last_name,salary*12 as annual from s_emp;
select id,se.last_name from s_emp se; # 给员工表起别名

去除重复

select distinct col_name,col_name... from tb_name;

注意事项：distinct关键词只能放在select关键词后面

空值运算

select ifnull(expression,replacement) from tb_name;
select id,last_name,ifnull(commission_pct,0) c_pct from s_emp;

IFNULL() 函数的工作原理如下：

• 如果 expression 不为 NULL，则 IFNULL() 函数返回 expression 的值。
• 如果 expression 为 NULL，则 IFNULL() 函数返回 replacement 的值。

查询常数

select 100;
select 'john';
select 5*5-5;
select 'briup',id,last_name from s_emp;

条件查询

SELECT column1, column2, ...
FROM table
WHERE condition;

注意事项：where子句的优先级别最高

条件查询中常用的比较运算符有：

• 逻辑比较操作符：
  • = > < >= <= !=
• 不等于操作符，以下三个都表示不等于的意思，经常用的是 !=
  • != <> ^=

逻辑运算符用于组合多个条件，常用的逻辑运算符有：

• AND
• OR
• NOT

注意事项：and比or的优先级要高

select id,last_name,dept_id,title,salary
from s_emp
where salary > 1000
and (dept_id = 41 or dept_id = 44);

特殊条件

• between and 操作符，表示在俩个值之间
• in() ，表示值在一个指定的列表中
• like ，模糊查询，在值不精确的时候使用
  • % ，通配0到多个字符
  • _ ，通配一个字符，并且是一定要有一个字符
  • \ ，转义字符

select id,last_name,salary
from s_emp
where last_name like '_ _ _ n _%'; # 注意我是为了区分才加空格滴

注意事项：数据库中的字符串，需要使用单引号括起来

• is null ，判断值为null的时候使用，null值的判断不能使用等号

注意，上述几个关键字的取反操作的为：

not between and
not in (列表)
not like
is not null

SQL运算符

算数运算符

select 1+1;
select id,last_name,salary,salary*13 as sumsal from s_emp;

比较运算符

select id,last_name,salary from s_emp where salary <=> null; # 等于下一条语句
select id,last_name,salary from s_emp where salary is null;

注意事项：

• null参与运算结果都为null
• <=> 和 = 作用一样，唯一的区别 <=>可以用来对NULL进行判断‘

select id,last_name,salary from s_emp where id>=22;
select id,last_name,salary from s_emp where salary is not null;
select least(1,3,4,87,34);
select id,last_name,salary from s_emp  where salary between 850 and 1200;
select id,last_name,dept_id from s_emp where dept_id in(41,42,43,44);
select id,last_name from s_emp where last_name like '%a%';

注意事项：

• %表示0到任意字符
• _表示1个字符占位

select id,last_name,salary from s_emp where last_name like '$_%' escape '$'; # 注意事项：escape指定转义符
select id,last_name,salary from s_emp where last_name regexp '^s'; # regexp仍然解决不了大小写问题
select id,last_name,salary from s_emp where binary last_name like '^s'; # binary可以解决

逻辑运算符

select id,last_name,salary from s_emp where salary not between 850 and 1200;
select id,last_name,salary from s_emp where salary not (salary>=850 and salary<=1200);
select id,last_name,salary from s_emp where salary<850 or salary>1200;

注意：OR和And可以连用，但是and的优先级高于or

select id,last_name,dept_id from s_emp where dept_id not in(41,42,43,44);
# 员工在41或42号部门，但是薪水不能高于1000的员工信息，或员工不在41或42号部门，但是薪水高于1000的员工信息
# 也就是两个条件只能有一个为真
select id,last_name,salary,dept_id from s_emp where dept_id in(41,42) xor salary>1000;

注意事项：逻辑异或（XOR）运算符是当给定的值中任意一个值为NULL时，则返回NULL

位运算符

select 5&3;

运算优先级

排序分页

排序

查询数据的时候进行排序，就是根据某个字段的值，按照升序或者降序的情况将记录显示出来

select id,id2 from test order by id asc,id2 desc;

注意事项：

• order by语句，只对查询记录显示调整，并不改变查询结果，所以执行权最 低，最后执行
• 排序的默认值时asc：升序， desc：降序
• 如果有多个列进行排序，后面的列排序的前提时前面的列排好序以后有重复（相同）的值

分页

select 字段列表 from 表名 limit 起始索引, 查询记录数;
select id,last_name from s_emp limit 0,5;

注意事项：

• 起始索引从0开始，计算公式：起始索引=（查询页码-1）*每页显示记录数
• 分页查询时数据库的方言，不同的数据库有不同的实现，MySQL中的时LIMIT
• 如果查询的时第一页数据，其实索引可以省略直接简写为limit条数

多表查询

多表查询，又称表联合查询，即一条sql语句涉及到的表有多张，表中的数 据通过特定的连接，进行联合显示。

注意事项：阿里开发规范中要求多表连接必须给表起别名

笛卡儿积

等值连接

等值连接又称为内连接（自然连接），将两张具有关联关系的列的数据连接 起来，连接查询where子句中用来连接两个表的条件称为连接条件或者连接谓词，当连接运算为=的时候，称之为等值连接。

select col_name....
from table_name1,table_name2
where [table_name1].[col_name]=[table_name2].[col_name]
或
select col_name....
from table_name1 inner join table_name2
on [table_name1].[col_name]=[table_name2].[col_name]

不等值连接

两张没有关联关系（主外键）的表，通过某个特定场景的业务连接起来，为不等值连接

select e.id, e.last_name, e.title, e.salary, s.name
from s_emp e, salgrade s    # s_emp 和 salgrade没有关联关系
where e.salary between s.minsal and s.maxsal;

自连接

自连接就是一张表，自己和自己连接后进行查询

select s1.id,s1.last_name,s2.id,s2.last_name manager_name
from s_emp s1,s_emp s2
where s1.manager_id = s2.id;
# 可理解s1为员工表，s2为经理表

内连接

内连接查询：查询两表或多表中交集部分数据。

隐式内连接语法：

select 字段列表
from table_name1,table_name2
where 条件 ... ;

显式内连接语法：

select 字段列表
from table_name1 [inner] join table_name2
on 连接条件 ... ;

注意事项：一旦表起了别名，就不能再使用表名来指定对应的字段了，此时只能够使用别名再指定字段

外连接

左外连接

select col_name....
from table_name1 left [outer] join table_name2
on [table_name1].[col_name]=[table_name2].[col_name]

右外连接

select col_name....
from table_name1 right [outer] join table_name2
on [table_name1].[col_name]=[table_name2].[col_name]

全连接

# UNION 并集 ->全连接
# 使用左连接和右连接的并集代替全连接
SELECT se.dept_id, last_name, `name`
FROM s_emp se 
LEFT JOIN s_dept sd
on se.dept_id = sd.id
UNION
SELECT se.dept_id, last_name, `name`
FROM s_emp se 
RIGHT JOIN s_dept sd
on se.dept_id = sd.id;

单行函数

单行函数接受参数返回一个结果，只对一行进行变换，每行返回一个结果，可以嵌套，参数可以时一列或一个值

数值函数

select abs(-20);
select round(32.56,-2);
select bin(10),oct(10),hex(10);

字符串函数

select upper('hello'),lower('GOOD');
select concat('hello','world');
select trim(' hello world ');
select substr('helloworld',3);

时间日期

select curdate(),current_date(); 
select curtime(),current_time();
select now(),sysdate();
select utc_date(),utc_time();
select unix_timestamp();
select unix_timestamp(now());
select from_unixtime(1661789223);
select year(now());
select extract(year_month from now());

extract中type：

select time_to_sec(now());
select adddate(now(),interval 1 day);
select last_day(now());
select date_format(now(),'%Y-%m-%d');

fm格式：

select str_to_date('22/02/2022','%d/%m/%Y');
select period_add('2203',3); # 返回202203的后3个月 2022年03月的后三个月 即6月

注意：第一个参数格式yyyyMM或yyMM，第二个参数可以是负值

流程控制

select id,last_name,salary,if(salary>2000,'蓝领','白领') from s_emp;
select id,last_name,salary,ifnull(salary,0)*13 sum_sal from s_emp;

# 相当于Java的if else 
select id,last_name,salary,
case when salary>2500 then '金领'
when salary<=2500 and salary>=1500 then '蓝领'
else '白领' end
as "rank"
from s_emp;

# 相当于Java的switch case
select id,last_name,salary,
case id%3 when 1 then '1group'
when 2 then '2group'
else '3group' end
as "group"
from s_emp;

聚合函数

聚合函数，一般会操作多组数据，并对每一组中的某个列，执行计算并返回 单一的值。

聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用，所以也把其它称之 为分组函数。

select 组函数(field1|*)
from table_name
where condition
group by field1,field2,...
[having condition];

聚合函数能够出现的位置：

• select后面
• having后面
• order by后面

注意事项：where后面一定不能出现组函数！原因时where的执行顺序在组函数之前！

基础聚合函数

• avg ，求平均值
• count ，计算有多少条数据
• max ，求最大值
• min ，求最小值
• sum ，求和

select dept_id,count(*),sum(salary)
from s_emp
group by dept_id
order by dept_id;

group by子句

注意：分组查询语句中，如果select、having、order by语句后面出现组函数，那么 select、having、order by后面没有被组函数修饰的列，就必须出现在group by 后面！

select dept_id,avg(salary) from s_emp group by dept_id;
select dept_id,max(salary) from s_emp group by dept_id;
select dept_id,sum(salary) from s_emp group by dept_id;
select dept_id,count(*) from s_emp group by dept_id;
select dept_id,count(*),min(salary),max(salary),sum(salary),avg(salary) from s_emp group by dept_id;

思考，如果group by后跟了俩个字段，表示什么意思？(这两个字段看作是一个整体)

having关键字

select dept_id,avg(salary) from s_emp group by dept_id having avg(salary)>=1400;

select执行顺序

一个完整的select查询语句，由以下几部分组成：(下面的不是执行顺序哈~)

select 字段1,字段2
from 表
where 条件
group by 分组条件
having 分组筛选条件
order by 排序条件
limit (n-1)*size,size;

group by 和 having的关系：

• group by 可以单独存在，后面可以不出现having语句
• having不能单独存在，如果需要出现，那么就必须出现在group by后面

order by语句：

• 如果sql语句中需要排序，那么就一定要协作sql语句的最后面（limit才是最后的哈~）
• order by后也可以出现组函数

子查询

标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查 询称为标量子查询。

常用的操作符： = <> > >= < <=

-- 1.查询Smith所在部门id
select dept_id
from s_emp
where last_name='Smith';
#查询结果：41
-- 2.查询Smith所在部门的最高工资
select max(salary)
from s_emp
where dept_id=41;
#查询结果：1450.00
-- 3.查询比Smith所在部门最高工资高的员工的信息
select id,last_name,salary
from s_emp
where salary >1450;
-- 合并以上三条SQL语句
select id,last_name,salary
from s_emp
where salary > (
    select max(salary)
    from s_emp
    where dept_id=(
        select dept_id
        from s_emp
        where last_name='Smith'
    )
);

列子查询

子查询返回的结果是**一列(可以是多行)**，这种子查询称为列子查询。

常用的操作符：

-- 1.查询41号部门的平均工资
select avg(salary)
from s_emp
where dept_id=41;
#查询结果：1247.5
-- 2.查询平均工资比41号部门的平均工资高的部门编号
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5;
#查询结果： 31 32 35 50 33 10
-- 3.查询平均工资比 41号部门的平均工资 高 的部门中员工的信息
select last_name,salary,dept_id
from s_emp
where dept_id in(10,31,32,33,35,50);
-- 合并以上三条SQL语句
select last_name,salary,dept_id
from s_emp
where dept_id in(
    select dept_id
    from s_emp
    group by dept_id
    having avg(salary)>(
        select avg(salary)
        from s_emp
        where dept_id=41
    )
);

行子查询

子查询返回的结果是**一行(可以是多列)**，这种子查询称为行子查询。

常用的操作符：= 、<> 、IN 、NOT IN

-- 查询"Ngao"的入职日期 及 职位
select start_date,dept_id
from s_emp
where last_name = 'Ngao';
#查询结果：1990-03-08 41
-- 查询与"Ngao"的入职日期及职位相同的员工信息
select last_name,id,salary,dept_id
from s_emp
where (start_date,dept_id) = ('1990-03-08',41);
-- 合并以上两条SQL语句
select last_name,id,salary,dept_id
from s_emp
where (start_date,dept_id) = (
    select start_date,dept_id
    from s_emp
    where last_name = 'Ngao');

表子查询

子查询返回的结果是多行多列，可将其作为临时表，进一步进行查询，这种子查 询称为表子查询。

-- 1.查询41号部门的平均工资
select avg(salary)
from s_emp
where dept_id=41;
#查询结果：1247.5
-- 2.查询平均工资比 41号部门的平均工资 高 的部门id
select dept_id
from s_emp
group by dept_id
having avg(salary) > 1247.5;
#查询结果：10,31,32,33,35,50
-- 3.查询平均工资比 41号部门的平均工资 高 的部门中员工的信息
select se.last_name,se.dept_id
from s_emp se
where se.dept_id in (10,31,32,33,35,50);
-- 4.准备部门平均工资表
select dept_id,avg(salary) avgs
from s_emp
group by dept_id;
-- 5.查询平均工资比 41号部门的平均工资 高 的部门中员工的信息，并且显示
出当前部门的平均工资
select se.last_name,se.dept_id,sa.avgs
from s_emp se,(
    select dept_id,avg(salary) avgs
    from s_emp
    group by dept_id
) sa
where se.dept_id in (10,31,32,33,35,50)
and sa.dept_id = se.dept_id;
-- 合并以上五条SQL语句
select se.last_name,se.dept_id,sa.avgs
from s_emp se,(
    select dept_id,avg(salary) avgs
    from s_emp
    group by dept_id
) sa
where se.dept_id in (
    select dept_id
    from s_emp
    group by dept_id
    having avg(salary) > (
        select avg(salary)
        from s_emp
        where dept_id=41
    )
)
and sa.dept_id = se.dept_id;

❤️❤️❤️忙碌的敲代码也不要忘了浪漫鸭！

纸上得来终觉浅，绝知此事要躬行。💪