MySQL从删库到跑路： 入门篇

MySQL 入门

---

1. 查询语句

1.1 SQL的分类

---

# DDL: 数据定义语言  create \ alter \ drop \ rename \ truncate
# DML: 数据操作语言  insert \ delete \ update \ select
# DCL: 数据控制语言  commit \ rollback \ savepoint \ grant \ revoke

1.2 单表select语句

---

1. select...from...

select * from employees;
select employee_id, salary from employees;

2. 列的别名

select employee_id emp_id from employees;
select employe_id as emp_id from employees;		# as 可省略， 别名可用"" eg "部门id"

3. 去除重复行

select distinct department_id from employees;  # 字段前 + distinct
select salary, distinct department_id from employees;  # 错误
select distinct department_id, salary, from employees;  # 整体去重, 没实际意义

4. 空值参与运算

# null 不等同于0, '', 'null'
# 空值参与运算 结果一定为空
select 
	employee_id, salary "月工资", salary * (1 + commision_pct) * 12 "年工资" 
from 
	employees;		# commision_pct为null的 年工资全为null
	
select 
	employee_id, salary "月工资", salary * (1 + ifnull(commision_pct, 0)) * 12 "年工资" 
from 
	employees;		# 使用ifnull()

5. 着重号

select * from order;  # 错误 order为关键字
select * from `order`;

6. 查询常数

select "Mysql", employee_id, last_name from employees;	# 三列, 匹配常数Mysql

7. 显示表结构

describe employees;  # 显示表中相关字段的详细信息
desc employees;

8. 过滤数据

# 查询90号部门的员工信息
select * from employees where department_id = 90;

9. 运算符

# 算术运算符 +, -, *, /, div, %, mod
# 比较运算符 =, <=> 安全等于, <>(!=) 不等于, <, <=, >, >=

# is null, is not null, isnull
select last_name, salary, commision_pct from employees where commision_pct is null;

# least(), greatest()
select least('g', 'b', 'r'), greatest('g', 'T', 'c') from dual;

# between..and...	闭区间, between前可加not
select employee_id, last_name, salary from employees where salary between 6000 and 8000;

# in, not in
select last_name, salary, department_id from employees where department_id in (10, 20, 30);

# like, % 代表不确定个数的字符, _代表一个不确定字符
# 查询第二个字符是'_' 且 第三个字符为 'a'的员工信息  --> 使用转义字符
select last_name from employees where last_name like '_\_a%';

# regexp, rlike 正则表达式

# 逻辑运算符: or, ||, and, &&, not, !, XOR(逻辑异或, 一真一假)
select 
	last_name, salary, department_id 
from 
	employees 
where 
	department_id = 50 and salary >= 10000;
	
# 位运算符: &, |, ^, ~, >

10. 排序

select employee_id, last_name, salary from employees order by salary desc;  # desc asc

11. 分页

# limit 分页
select employee_id, last_name, salary from employees limit 0, 20;	# START_INDEX, PAGESIZE

1.3 多表select语句

---

1. 笛卡尔积

# 1 -> x, 每个员工都与每个部门匹配了一遍, 笛卡尔积(交叉连接), 缺少了多表的连接条件
select employee_id, department_name from employees, department;

2. 等值连接

select 
	e.employee_id, d.department_name 
from 
	employees e, departments d
where
	e.department_id = d.department_id;

3. 非等值连接

select
	e.last_name, e.salary, j.grade_level
from
	employees e, job_grades j
where
	e.salary between j.lowest_sal and j.highest_salary;

4. 自连接

select
	e.employee_id, e.last_name, m.employee_id, m.last_name
from
	employees e, employees m
where
	e.manage_id = m.employee_id;

5. 非自连接

# 以上几个case均为非自连接

5. 内连接

# 以上几个case均为内连接

6. 外连接

# 查询所有的员工的employee_id, department_name 信息
# SQL92语法实现外连接 使用 (+), MySQL 不支持
select
	e.employee_id, d.department_name
from
	employees e, departments d
where
	e.department_id = d.department_id(+);

# SQL99 语法使用join..on.. 实现多表查询
select
	e.employee_id, d.department_name
from
	employees e
left outer join
	departments d
on
	e.department_id = d.department_id;	# 左外连接
	
select
	e.employee_id, d.department_name
from
	departments d
right outer join
	employees e
on
	d.department_id = e.department_id;	# 右外连接
	
select
	e.employee_id, d.department_name
from
	departments d
full outer join
	employees e
on
	d.department_id