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 语句的规范
- use interview;
- 字句分行写,提高可读性。
- 每条命令以
;
结尾。 - 关键字大写,表名,字段名小写。
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);