SQL基础知识

SQL 结构化查询语言

分类

• DDL 数据定义语言
• DML 数据管理语言
• DCL 数据控制语言
• DQL 数据查询语言

DDL

• create
• alter
• drop
• rename
• truncate

DDL 没有事务性，不能回滚。所以在使用 truncate 的时候需要注意。

DML

• insert
• delete
• update
• select

使用频率较高，需要熟练掌握。

DCL

• commit
• rollback
• savepoint
• grant
• revoke

需要理解透彻事务。 transaction

DQL

select <字段名>
from <表名>
where <条件>

SQL 语句的规范

1. use interview;
2. 字句分行写，提高可读性。
3. 每条命令以 ; 结尾。
4. 关键字大写，表名，字段名小写。

SELECT

select 1;
select 1 + 1
from dual;
select *
from jobs;

别名

select job_id id
from jobs;
select job_id as id
from jobs;
select job_id "job id"
from jobs; -- 推荐
select job_id 'job id'
from jobs; -- 不推荐

distinct 去除重复行

如果要使用 distinct 的时候要放在 select 后面。

SELECT distinct studentid
from student_courses;

空值参与运算，结果也为空

null, 0 , '', 'null' 这四个值不同。

使用 IFNULL 函数来判断。

null 参与计算时结果为 null

select ifnull(age_, 0)
from students;

-- 无法获取数据，因为 null 参与计算的结果为 null。 如果要判断值是否为 null ，需要使用 xxx is null。或者使用 xx <=> null.
select *
from students
where name = null; 

着重号

用于转义关键字。

select *
from `order`;

显示表结构

describe students;
desc students;

过滤数据

select *
from students
where id = 1;

-- 会把 Persident 的记录查询出来。
-- 正常情况下是不应该查询出来的，但是 MySQL 比较特殊。
select *
from jobs
where job_title = 'president';

运算符 +

在 SQL 中， + 是没有连接的作用，仅表示加法运算。此时，字符串会隐式转换为数值，如果转换失败，当作 0。

select 100 + '1'
from dual; -- 101
select 100 + 'a'
from dual; -- 100

比较运算符

• =
• <=> 安全等于
• <>
• !=
• <
• <=
• >
• >=

-- true 返回 1 ，false 返回 0 
select 1 = 2,
       1 > 2,
       1 >= 2,
       1 != 2,
       1 <> 2,
       1 < 2,
       1 <= 2,
       'a' = 'a',
       1 = null,     -- null
       null = null,  -- null
       null <=> null -- 1
from dual;

• is null
• is not null
• least
• greatest
• between ... and ...
• isnull
• in
• not in
• like
• regexp
• rlike

select least(1, 2, 3)
from dual; -- 1 计算最小值
select greatest(1, 2, 3)
from dual; -- 3 计算最大值

逻辑运算符

and 优先级高于 or

• or
• ||
• and
• &&
• not
• !
• xor

排序 order by

select *
from students
order by id desc; -- 降序
select *
from students
order by id asc; -- 升序

分页 limit

-- 显示第 5,6 两条数据
select *
from students limit 4,2;
-- limit N offset M    -- N 从 0 开始
-- MySQL 8.0 新特性
select *
from students limit 2
offset 4;

-- 显示前 5 条数据
select *
from students limit 5;

where ... order by ... limit ...

笛卡尔积

select *
from tablea,
     tableb;

select *
from tablea
         cross join tableb;

多表查询

角度一

• 等值连接
• 非等值连接

-- 非等值连接
select emp.employee_id, emp.last_name, jg.grade_level
from employees emp,
     job_grades jg
where emp.salary between jg.lowest_sal and jg.highest_sal;

角度二

• 自连接
• 非自连接

-- 自连接
select emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name
from employees emp,
     employees mgr
where emp.manager_id = mgr.employee_id;

角度三

• 内连接 [inner] join ... on ....
• 外连接
  • 左外连接 left [outer] join ... on ...
  • 右外连接 right [outer] join ... on ...
  • 满外连接 MySQL 不支持 full [outer] join ... on ... 的写法，可以使用 union all 实现满外连接

-- SQL99
-- 内连接
select emp.employee_id, emp.last_name, mgr.employee_id, mgr.last_name
from employees emp
         inner join
     employees mgr
     on emp.manager_id = mgr.employee_id;
-- 左外连接
select emp.employee_id, emp.last_name, dept.department_id, dept.department_name
from employees emp
         left join departments dept
                   on emp.department_id = dept.department_id;
-- 右外连接
select emp.employee_id, emp.last_name, dept.department_id, dept.department_name
from employees emp
         right join departments dept
                    on emp.department_id = dept.department_id;
-- 满外连接, MySQL 不支持 full outer join 的写法
#
select emp.employee_id,
       emp.last_name,
       dept.department_id,
       dept.department_name
           #
from employees emp
         full join departments dept #
on emp.department_id = dept.department_id;
-- union all 实现满外连接
select emp.employee_id, emp.last_name, dept.department_id, dept.department_name
from employees emp
         left join departments dept
                   on emp.department_id = dept.department_id
union all
select emp.employee_id, emp.last_name, dept.department_id, dept.department_name
from employees emp
         right join departments dept
                    on emp.department_id = dept.department_id and emp.employee_id is null;

union 、 union all 的使用

union 操作符返回两个查询结果集的并集，去除重复记录。
union all 操作符返回两个查询结果集的并集；不会去除重复记录。

能用 union all 就尽量使用 union all， union 效率比较低。

自然连接 natural join

自动查询两张表中所有相同的字段，然后进行等值连接。

-- nature join
select emp.employee_id, emp.last_name, dept.department_id, dept.department_name
from employees emp
         join departments dept
              on emp.department_id = dept.department_id and emp.manager_id = dept.manager_id;

select emp.employee_id, emp.last_name, dept.department_id, dept.department_name
from employees emp
         natural join departments dept;

using

简化等值连接

-- using
select emp.employee_id, emp.last_name, dept.department_id, dept.department_name
from employees emp
         join departments dept
              on emp.department_id = dept.department_id;

select emp.employee_id, emp.last_name, dept.department_id, dept.department_name
from employees emp
         join departments dept
              using (department_id);

系统内置函数

数值函数

• abs 绝对值
• sign 取符号
• pi
• ceil 天花板
• ceiling
• floor 地板
• least
• greatest
• mod
• rand
• rand(x)
• round(x)
• round(x,y)
• truncate(x,y) 数字x，截断为y位小数
• sqrt(x) 平方根

角度与弧度互换

• radians(x) 角度->弧度
• degrees(x) 弧度->角度

三角函数

• sin
• asin
• cos
• acos
• tan
• atan
• atan2
• cot

指数，对数

• pow(x,y),power(x,y)
• exp(x)
• ln(x),log(x)
• log10(x)
• log2(x)

进制转换

• bin(x) 2 进制
• hex(x) 16 进制
• oct(x) 8 进制
• conv(x,f1,f2)

字符串函数

MySQL 中下表是从 1 开始的

• ascii(s)
• char_length(s)
• length(s)
• concat(s1,s2,...)
• concat_ws(x,s1,s2,...)
• insert(str,idx,len,replacestr)
• replace(str,a,b)
• upper(s) ucase(s)
• lower(s) lcase(s)
• left(str,n)
• right(str,n)
• lpad(str,len,pad) 左对齐
• rpad(str,len,pad) 右对齐
• ltrim(s)
• rtrim(s)
• trim(s) 去掉首尾空格
• trim(s1 from s2)
• trim(trailing s1 from s2)
• repeat(str,n) 重复 n 次
• space(n) 返回 n 个空格
• strcmp(s1,s2) 比较 s1 s2 第 ascii 码
• substr(s,index,len) 从字符 index ，截取长度 len 的字符串
• locate(substr,str) 字符串出现的位置
• elt(m,s1,s2...) 返回指定标的字符串
• field(s,s1,s2...) 第一次出现的位置
• find_in_set(s1,s2) s1 在 s2 中的位置
• reverse(s)
• nullif(value1,value2) 比较两个字符串，相等返回null,否则返回 value1

select ascii('a'),        -- 第一个字符的 ascii 码
       char_length('abc'),-- 字节个数
       length('qwer'),
       length('我爱学习'),
       concat('hello', ' ', 'MySQL', '8.0'),
       concat_ws(' ', 'a', 'b', 'c'),
insert
(
'wangzhy'
,
2
,
3
,
'123'
)
,
-- 从第二个字符开始，替换三个字符
replace
(
'wangzhy'
,
'zhy'
,
'zhiyuan'
)
,
upper
(
'wangzhy'
)
,
ucase
(
'wangzhy'
)
,
lower
(
'WANG'
)
,
lcase
(
'WANG'
)
,
left
(
'wangzhy'
,
4
)
,
right
(
'wangzhy'
,
3
)
,
lpad
(
'aa'
,
7
,
'22'
)
,
-- 左对齐
rpad
(
'qq'
,
10
,
'*'
)
,
-- 右对齐
ltrim
(
' a b c '
)
,
rtrim
(
' a c  '
)
,
trim
(
' a '
)
,
repeat
(
'a'
,
20
)
,
space
(
20
)
,
strcmp
(
'a'
,
'1'
)
,
substr
(
'wangzhy'
,
1
,
2
)
,
locate
(
'a'
,
'wangzhy'
)
,
elt
(
'2'
,
'b'
,
'c'
,
'a'
)
,
field
(
'a'
,
'b'
,
'a'
)
,
find_in_set
(
'a'
,
'wangzhy'
)
,
reverse
(
'wangzhy'
)
,
nullif
(
'a'
,
'a'
)
,
nullif
(
'b'
,
'a'
)
,
nullif
(
'a'
,
'b'
)
from dual;

时间、日期函数

• curdate() 年月日
• curtime() 时分秒
• sysdate() 年月日时分秒
• now() 年月日时分秒
• utc_date()
• utc_time()

select
    curdate(),
    current_date,
    curtime(),
    current_time(),
    now(),
    utc_date(),
    utc_time()
    from dual;

• unix_timestamp()
• unix_timestamp(date)
• from_unixtime(timestamp)

select unix_timestamp(),
       unix_timestamp(now()),
       unix_timestamp('2023-09-04 21:35:11'),
       from_unixtime(1693834511)
from dual;

• year(date)/month(date)/day(date)

• hour(time)/minute(time)/second(time)

• monthname(date)

• dayname

• weekname

• quarter

• wwek(date) weelofday(date)

• dayofyear

• dayofmonth

• dayofweek

• extract(type from date)

• time_to_sec(time)

• sec_to_time(sec)

• date_add(datetime,interval expr type) interval 是关键字，不省略。

• adddate(datetime,interval expr type)

• date_sub(datetime,interval expr type)

• subdate(datetime,interval expr type)

select date_add(now(), interval 1 year )
from dual

• addtime(time1,time2) time1+time2

• subtime(time1,time2) time1-time2

• datediff 时间间隔天数

• timediff

• from_days

• to_days

• last_day

• makedate

• maketime

• period_add

• date_format(date,fnt)

• time_format(time,fnt)

• get_format(date_type,format_type)

• str_to_date(str,fmt)

• %Y 年 2023

• %y 年 23

• %M 月份 January

• %m 月份 01

• %b 月份缩写 Jan,Feb

• %c 月份，数字 1，2，3

• %D 英文后缀，月中的天数 1st,2nd,3rd

• %d 两位数，月中天数 01，02

• %e 数字，月中天数 1，2

• %H 两位数，小时，24 小时制， 01，02...

• %h 两位数,小时，12 小时制 01,02...

• %k 数字，小时，24 小时制 1，2...

• %l 数字，小时，12 小时制 1，2...

• %i 两位数，分钟 00，01，02...

• %S 两位数，秒

• %s 两位数，秒

• %W 一周的星期名称 Sunday

• %a 一周的星期名称缩写 Sun,Mon,Tues...

• %w 数字，在一周的天数，0,1,2...

• %j 三位数，在年中的天数 001,002...

• %U

• $u

• %T 24 小时制

• %r 12 小时制

• %p AP/PM

select date_format(now(), '%Y-%m-%d %H:%i:%s')
from dual;

流程控制函数

• if
• ifnull
• case when exp1 then val1 when exp2 then val2 else val3 end
• case exp when constant1 then val1 when constant2 then val2 else value3 end

信息函数

• version()
• connection_id()
• database()
• schema()
• user()
• current_user()
• charset('')
• collation('')

聚合函数

• avg

• sum

• max

• min

• count count(*), count(1)

• group by

• having

select 的底层执行原理

SQL 的编写顺序

select...
    from...
    join...on 表的连接条件
where...不包括聚合函数的条件
group by...
    having...包含聚合函数的条件
order by...
    limit...

SQL 的执行顺序

from ...
join ... on ....
where ...
group by ....
having ....

select ....

order by ....
limit ...

from tab1 , tab2 -> on -> left/right join -> where -> group by -> having -> select -> distinct -> order by -> limit

根据 SQL 的执行顺序，可以得到

1. 别名只能在 order by 使用，不允许在 where 子句中使用。
2. 不包含聚合的函数应该写在 where 子句中，包含聚合函数的条件只能写在 having 子句中。

子查询

1. 子查询在主查询之前一次执行完成
2. 子查询的结果被主查询使用

注意

• 子查询要包含在括号内
• 将子查询放在比较条件的右侧
• 单行操作符对应单行子查询(>,=,>=,<,<=,!=)，多行操作符对应多行子查询(in,all,any,some)

子查询类别

• 单行子查询

• 多行子查询

• 相关子查询

  • 查询工资大于本部门平均工资的员工信息

• 不相子查询

  • 查询工资大于本公司平均工资的员工信息

多行子查询

in

在使用 not in 的时候需要注意，子查询的结果中，不允许包含 null，不然结果会有问题。

any

-- 返回其他 job_id 中比 job_id 为 'IT_PROG' 部门任一工资低的员工的员工号、姓名、JOB_ID、salary
select *
from employees
where job_id != 'IT_PROG'
  and salary < any (select salary from employees where job_id = 'IT_PROG');

all

-- 返回其他 job_id 中比 job_id 为 'IT_PROG' 部门所有工资低的员工的员工号、姓名、JOB_ID、salary
select *
from employees
where job_id != 'IT_PROG'
  and salary < all (select salary from employees where job_id = 'IT_PROG');

all 的使用方法

-- 查询平均工资最低的部门ID
-- 自己写的，这个有问题，最小值可能有多条数据
select department_id
from employees
group by department_id
order by avg(salary) asc limit 1;
-- 方式一 （三层子查询）
select department_id
from employees
group by department_id
having avg(salary) =
       (select min(avg_sal) from (select avg(salary) avg_sal from employees group by department_id) t);
-- 方式二（）
select department_id
from employees
group by department_id
having avg(salary) <= ALL (select avg(salary) avg_sal
                           from employees
                           group by department_id);

相关子查询

-- 查询大于本部门平均工资的员工
select *
from employees e1
where salary > (select avg(e2.salary) from employees e2 where e2.department_id = e1.department_id);

select *
from employees e1
         left join (select department_id, avg(salary) avg_sal from employees group by department_id) e2
                   on e1.department_id = e2.department_id
where e1.salary > e2.avg_sal;

order by 中使用子查询

-- 查询员工的 id,salary 根据 department_name 排序
select e.employee_id, e.salary, d.department_name
from employees e
         left join departments d on e.department_id = d.department_id
order by d.department_name;

select employee_id, salary
from employees
order by (select department_name from departments where employees.department_id = departments.department_id);

exists、not exists

-- 查询管理者的信息
select employee_id, job_id, department_id
from employees e1
where exists(select 1 from employees e2 where e2.manager_id = e1.employee_id);

-- 没有员工的部门
select department_id, department_name
from departments
where not exists(select 1 from employees where employees.department_id = departments.department_id);

存储过程

1. 一般用于更新操作。
2. 不可用户查询语句。
3. 可以有 0 个或多个返回值。
4. create procedure。
5. 功能更灵活，更强大。

存储函数

1. 一般用于查询操作。
2. 可以放在查询语句中使用。
3. 只有一个返回值。
4. create function。

存储过程和存储函数的优缺点

优点

1. 一次编译，多次使用，提高了 sql 的执行效率。
2. 可以减少开发工作量。
3. 安全性强。
4. 可以减少网络传输量。
5. 良好的封装性。

缺点

1. 可移植性差。
2. 调试困难。
3. 存储过程的版本难管理。
4. 不适合高并发场景。

小结

可以不用，但是要会。

变量

系统变量

用 @@ 标识

global variables

show
global variables;

session variables

show
session variables;

-- 1. 系统变量  
-- 1.1 全局 global variablesshow global variables ;  
-- 1.2 回话 session variablesshow session variables ;  
-- 查询 session system variablesshow variables ;  

show
global variables like 'admin%';  
show
variables like 'character%';  
show
global variables like 'character%';

select @@global.character_set_client;
select @@session.character_set_client;

-- Variable 'max_connections' is a GLOBAL variable  
-- select @@session.max_connections;  
select @@global.max_connections; -- 1000  
select @@session.pseudo_thread_id;
-- session id  

-- session 变量存在返回 session 变量的值，不存在就返回 global 变量的值。  
select @@character_set_client;
select @@max_connections; -- 1000  
select @@pseudo_thread_id;
-- session id  

-- 修改系统变量的值  
-- 1. 修改 my.cnf/my.ini 文件，需要重启 MySQL 服务。  
-- 2. 通过 set 命令修改系统变量。（重启 MySQL 服务之后，就会失效。）  
-- 2.1 set @@global.变量名=值  
-- 2.2 set global 变量名=值  
-- 2.3 set @session.变量名=值  
-- 2.4 set session 变量名=值

用户变量

用 @ 标识

• 会话用户变量
• 局部变量
  1. 只在 begin .... end 中有效，即只在存储过程和存储函数中使用。
  2. 声明在 begin 代码块中第一行，通过 declare 声明。
  3. declare 变量名 类型 [default 值] 如果没有声明默认值，则初始值为 null

-- 1. set @用户变量=值  
set
@emp_id=1
-- 2. set @用户变量:=值  
set @dept_id:=100
-- 3. select @用户变量 := 表达式 [from 等子句]  
select @count:= count(1)
from employees;
select @count;
-- 4. select 表达式 into @用户变量 [from 等子句]  
select avg(salary)
into @avg_sal
from employees;
select @avg_sal;

delimiter
$
create procedure test_var()
begin  
declare
a int default 0;  
declare
b int;  
declare
emp_name varchar(25);  
set
a = 1;  
set
b:=101;
select last_name
into @emp_name
from employees
where employee_id = 101;

select a, b, emp_name;
end $
delimiter ;

call test_var();