跳到主要内容

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();
标签：
最后于 更新