2、SQL 基础篇

该栏目讲叙 MySQL 相关的知识体系，包括数据库简介、SQL 简介、数据定义、数据操作、数据查询及数据优化等模块

---

---

简介

1、概述

• ：结构化查询语言，是专门用来与数据库通信的语言

2、特点

• SQL 不是某个特定数据库供应商专有的语言
• SQL 简单易学
• SQL 强大、灵活，可以进行非常复杂和高级的数据库

3、组成

• DDL：数据定义语言
• DML：数据操纵语言
• DQL：数据查询语言
• DCL：数据控制语言

---

基础语法

1、数据类型

• 字符串类型

描述	类型
固定长度的字符串	char(size)
可变长度的字符串	varchar(size)
存放最大长度为 65,535 个字符的字符串	text

• Number 类型

描述	类型
1个字节，取值范围-128 到 127	tinyint(size)
4个字节，取值范围-2147483648 到 2147483647	int(size)
8个字节，取值范围 -9223372036854775808 到 9223372036854775807	bigint(size)
带有浮动小数点的小数字，参数d规定小数点右侧的最大位数	float(size,d)
带有浮动小数点的小数字，参数d规定小数点右侧的最大位数	double(size,d)
作为字符串存储的double类型	decimal(size,d)

• Date 类型

描述	类型
日期，格式：YYYY-MM-DD	date
日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS	datetime
时间。格式：HH:MM:SS	time
时间戳	timestamp

2、常量

• 概述：在操作过程中不变的值，也称字面值或标量值
• 类型
  • 字符串常量
  • 数字常量
  • 布尔常量
  • 空值常量
  • 日期常量

3、变量

• 概述：在操作过程中存储变化的量
• 类型
  • 系统变量：由系统定义，包括全局变量、会话变量
  • 自定义变量：由用户定义，包括用户变量、局部变量
• 操作

### 系统变量

## 全局变量、会话变量
# 查询所有全局、会话变量，格式：show global[session] variables;
show global variables;

# 查询某些全局、会话变量，格式：show global[session] variables like '';
show global variables like '%char%';

# 查询单个全局、会话变量，格式：select @@global[session].变量名
select @@global.autocommit;

# 修改全局、会话变量的值，格式：set @@global[session].变量名 = 值
set @@global.autocommit = 1;

##########################################

### 自定义变量

## 用户变量
# 声明并初始化用户变量
set @value1 = 10;

# 赋值
select salary into @value1 from employees where employee_id = 123;

# 使用变量
select @value1; 

## 局部变量
create procedure localVar()
begin
    # 声明并初始化局部变量
	declare v1 int default 1;
	declare v2 int default 2;
	declare sum int;
	# 变量赋值
	set sum = v1 + v2;
	# w使用变量
	select sum;
end

4、运算符

• 算术运算符

描述	运算符
加号	=
减号	-
乘号	*
除号	/
取模	%

• 比较运算符

描述	运算符
等于	=
不等于	!=、<>
大于	>
大于等于	>=
小于	<
小于等于	<=

• 逻辑运算符

描述	运算符
取值范围	between...and...
字符模式匹配	like
等于多个可能的值	in
任一	any
所有	all
与空值比较	is、is not

---

数据定义（DDL）

1、数据库相关

• 查看所有数据库：show databases;
• 创建数据库：create database 数据库名;
• 选择数据库：use 数据库名;
• 修改数据库：alter database 数据库名 配置信息
• 删除数据库：drop database 数据库名;

2、约束

• 概述：更好地保证数据的完整性
• 类型
  • 空值约束（NOT NULL）：指定某列的值不能为空
  • 唯一性约束（UNIQUE）：指定某列的值不能重复
  • 检查约束（CHECK）：指定一个布尔表达式，用于指定对应列的值必须满足该表达式
  • 主键约束（PRIMARY KEY）：指定该列的值可以唯一地标识该条记录
  • 外键约束（FOREIGN KEY）：指定该行记录从属于主表中的一条记录，主要用于保证参照完整性

3、表结构相关

• 查看表结构：desc 表名;
• 创建表

create table 表名(列名1 数据类型,列名2 数据类型...);

• 更新表

# 添加一列
alter table 表名 add 列名 数据类型

# 删除一列
alter table 表名 drop column 列名

# 修改字段的数据类型
alter table 表名 alter column 列名 数据类型

# 添加非空约束
alter table 表名 alter column 列名 数据类型 not null

# 添加检查约束
alter table 表名 add constraint 约束名 check(检查规则)

# 添加唯一约束
alter table 表名 add constraint 约束名 unique(列名)

# 添加主键约束
alter table 表名 add constraint 主键名 primary key(列名)

# 添加外键约束
alter table 表名 add constraint 外键名 foreign key(外键列名) references 主表名(主键列名)

# 删除约束
alter table 表名 drop constraint 约束名

• 删除表

drop table 表名;

4、索引

• 概述：索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的集合。或是指向表中物理标识这些值的数据页的逻辑指针清单
• 优点：提高查询效率
• 缺点：降低数据更新的效率
• 类型
  • 普通索引（INDEX）：提高查询速度
  • 唯一索引（UNIQUE）：字段数据是唯一的
  • 主键索引（PRIMARY）：字段数据唯一且不为 null
  • 全文索引（FULLTEXT）：提高文本字段的检索
• 操作

# 查看索引
show index;

# 创建普通索引
create index 索引名 on 表名(列名(长度) ASC|DESC);

# 创建唯一索引
create unique index 索引名 on 表名(列名(长度) ASC|DESC);

# 创建表结构时添加主键约束
create table 表名(列名1 数据类型,列名2 数据类型..., primary key(列名));

# 修改表结构时添加主键约束
alter table 表名 add constraint 主键名 primary key(列名)

# 删除索引
drop index 索引名 on 列名;

---

数据操纵（DML）

1、插入数据

insert into 表名[(列名1,列名2...)] values(值1,值2...)

# 示例：插入部门信息department_name = 'Public Relations'，manager_id = 1，location_id = 2
insert into departments(department_name,manager_id,location_id) values('Public Relations',1,2);

2、更新数据

update 表名 set 列名1=值1,列名2=值2... [where条件]

# 示例：更新员工id=13的员工的部门id为5
update employees set department_id = 13 where employee_id = 5;

3、删除数据

delete from 表名 [where条件]

# 示例：删除部门名称是Finance的部门信息
delete from departments where department_name = 'Finance';

---

数据查询（DML）

1、基本查询

# 查询指定字段
select 列名1,列名2.. from 表名

# 查询所有字段
select * from 表名

# 根据条件查询
select * from 表名 where 条件

# 查询后进行排序
select * from 表名 order by 列名1 desc|asc,...

# 分页查询
select * from 表名 limit (pageNo - 1) * pageSize,pageSize

### 示例 ###
# 查询所有部门（选择所有列）
select * from departments;

# 查询部门id和位置id（选择特定列）
select department_id,location_id from departments;

# 查询员工的员工名称 和 奖金倍率（使用别名）
select last_name as name, commission_pct comm from employees;

# 显示员工的表结构
desc employees;

# 查询 部门id为90的 所有员工的信息
select * from employees where department_id = 90

# 查询 工资小于等于3000的 员工信息
select * from employees where salary <= 3000;

# 查询 工资在2500到3500之间的 员工信息
select * from employees where salary between 2500 and 3500;

# 查询 领导的id是100，101，201之一的 员工信息
select * from employees where manager_id in(100,101,201);

# 查询 员工名称以S开关的 员工信息
select * from employees where name like 'S%'

# 查询 领导的id为null 的员工信息
select * from employees where manager_id is null;

# 查询 工资大于等于10000和职位包含MAN 的员工信息
select * from employees where salary >= 10000 and job_id like '%MAN%';

# 查询 工资大于等于10000或职位包含MAN 的员工信息
select * from employees where salary >= 10000 or job_id like '%MAN%';

# 查询 职位不包含job_id为1,2,3的员工信息
select * from employees where job_id not in(1,2,3);

# 查询所有员工信息 并根据生日降序排序
select * from employees order by hiredate

# 查询所有员工信息 并根据部门id升序排序、工资降序
select * from employees order by department_id asc,salary desc;

# 查询 职位包含REP 的员工的平均工资，最高工资，最小工资，工资总和
select avg(salary),max(salary),min(salary),sum(salary) from employees where job_id like '%REP%'

# 查询 部门id=50 的员工总数 
select count(*) from employees where department_id = 50;

# 查询员工的平均工资，按部门id划分
select department_id,avg(salary) from employees group by department_id;

# 查询员工的总工资，按部门id和职位id划分 
select department_id,job_id,sum(salary) from employees group by department_id,job_id;

# 查询 各部门最高工资且最高工资必须大于10000 的员工信息
select department_id,max(salary) from employees group by department_id having max(salary) > 10000;

2、常见函数

• 字符串函数
  • upper(value)：将字符串转换为大写字母
  • lower(value)：将字符串转换为小写字母
  • concat(value1,value2)：连接字符串
  • length(value)：返回字符串的长度
  • instr(value,sub)：返回子串在字符串中的第1个索引位置
  • replace(value,source,target)：将字符串中的source字符替换为target字符
  • substr(value,pos)：截取从pos开始的子字符串
  • reverse(value)：倒序字符串顺序

# 将员工的名字都以大写的形式输出
select upper(name) from employees;

# 将员工的名字都以小写的形式输出
select lower(name) from employees;

# 将员工的名字前面添加LN_形式输出
select concat('LN_',name) from employees;

# 获取员工名字首字符 
select substr(name,1,1) from employees;

# 获取员工名字的长度
select last_name,length(name) from employees;

# 替换员工名字K的为W
select last_name,replace(name,'K','W') from employees;

# 查询员工名称中u的索引
select last_name,instr(name,'u') from employees;

# 将员工工资不够10位的左边用*填充
select lpad(salary,10,'*') from employees;

# 将员工工资不够10位的右边用*填充
select rpad(salary,10,'*') from employees;

# 获取HelloWorld中H后面的字符串，去除前后匹配的字符
select trim('H' from 'HelloWorld');

• 数学函数
  • abs(value)：求绝对值
  • mod(n,m)：求余数
  • round(value)：四舍五入

# 四舍五入保留员工工资两位小数
select round(salary,2) from employees;

# 保留员工工资的小数前两位
select truncate(salary,2) from employees;

# 求工资余3的余数
select mod(salary,3) from employees;

• 日期函数
  • now()：返回当前日期
  • str_to_date()：将字符串转换指定格式的日期
  • date_format()：将日期转换成字符串

# 获取当前日期
select now();

# 将日期格式字符串转换成指定格式的日期
select str_to_date('9-13-1999','%m-%d-%Y');

# 将日期转换成字符
select date_format('2018/5/5','%Y年%m月%d日');

3、聚合函数

• max()：最大值
• min()：最小值
• sum()：总和
• avg()：平均值
• count()：总数

4、分组查询

# 分组中的查询字段中只能出现分组列或聚合函数
select 列名,聚合函数 from 表名 group by 列名 

# 对分组后的结果再进行条件过滤
select 列名 from 表名 group by 列名 having 条件

5、子查询

• 概述：SELECT 语句出现在其他语句中
• 类型
  • 单行单列：返回的是一个具体列的内容，可以理解为一个单值数据
  • 单行多列：返回一行数据中多个列的内容
  • 多行单列：返回多行记录中同一列的内容，相当于给出一个操作范围
  • 多行多列：查询返回的结果是一张临时表

# 单行单列的情况
select * from 表名1 where 列名1 [=、!=、>、<] (返回单行单列的查询)

# 单行多列的情况 
select * from 表名 where (列名1,列名2)=(select 列名1,列名2 from 表名 where条件);

# 多行单列的情况，可以使用in、not in、any、all
select * from 表名1 where 列名1 in (返回多行单列的查询)

# 多行多列的情况
select * from 表名1 left join (返回多行多列的查询) 表别名 on 去除笛卡尔积条件

######################################################

### 示例 ###
## 单行子查询

# 谁的工资比 Abel 高
select last_name from employees where salary > (select salary from employees where name = 'Abel');

# 返回job_id 与141 号员工相同，salary 比143 号员工多的员工， 姓名，job_id（单行子查询） 
select name,job_id,salary from employees 
where job_id = (select job_id from employees where employee_id = 141) 
and 
salary > (select salary from employees where employee_id = 143);

# 返回公司工资最少的员工的last_name,job_id 和salary（在子查询中使用组函数）
select name,job_id,salary from employees 
where salary <= (select min(salary) from employees)

# 查询最低工资大于50 号部门最低工资的部门id（子查询中的having子句）
select department_id,min(salary) from employees group by department_id 
having min(salary) > (select min(salary) from employees where department_id = 50);

######################################################

## 多行子查询

# 返回location_id 是1400 或1700的部门中的所有员工姓名（in操作符）
select name from employees where department_id in (select department_id from departments 
where location_id in (1400,1700));

# 返回其它部门中比job_id 为‘IT_PROG’ 部门 任一 工资低的员工的员工号、姓名、job_id  以及salary（any）
select employee_id,name,job_id,salary from employees where salary < any (
	select salary from employees where job_id = 'IT_PROG'
) and job_id != 'IT_PROG';

# 返回其它部门中比job_id 为‘IT_PROG’ 部门 所有 工资低的员工的员工号、姓名、job_id  以及salary（all）
select employee_id,name,job_id,salary from employees where salary < all (
	select salary from employees where job_id = 'IT_PROG'
) and job_id != 'IT_PROG';

6、多表关联查询

# 全值查询
select * from 表1,表2 where 去除笛卡尔积条件

# 内连接
select * from 表1 inner join 表2 on 去除笛卡尔积条件

# 左外连接
select * from 表1 left join 表2 on 去除笛卡尔积条件

# 右外连接
select * from 表1 right join 表2 on 去除笛卡尔积条件

######################################################

### 示例 ###

# 查询女神及其男朋友信息（等值连接）
select b.id,b.`name`,bs.boyName from beauty b,boys bs where b.boyfriend_id = bs.id; 

# 查询女神及其男朋友信息（内连接）
select b.id,b.name,bs.boyName from beauty b inner join boys bs on b.boyfriend_id = bs.id;

# 查询女神及其男朋友信息（左外连接）
select b.id,b.name,bs.boyName from beauty b left join boys bs on b.boyfriend_id = bs.id;