目录
DDL(Data Definition Language)数据定义语言
DML(Data Manipulation Language)数据操作语言
DQL(Data Query Language) 数据查询语言
DCL(Data Control Language)数据控制语言
TCL(Transaction Control Language) 事务控制语言
概述
什么是SQL
结构化查询语言（Structured Query Language）
通过SQL语句完成数据库对象操作。主要是对数据库对象进行增删改查（CRUD）
SQL书写规范
1. sql语句可以单行或多行书写，最后以分号结尾
2. sql语句（在windows平台下）不区分大小写，建议关键字大写
SELECT * FROM student;
3. 注释
单行
-- 所有数据库厂商支持
# 仅mysql厂商支持（方言）
多行
/* 注释内容 */
SQL分类
1. DDL(Data Definition Language)数据定义语言
用于操作数据库对象：数据库、表、列等 关键字：create drop alter 等
2. DML(Data Manipulation Language)数据操作语言
用于对数据库表中数据进行增删改。关键字：insert delete update 等
3. DQL(Data Query Language) 数据查询语言
用于对数据库表中数据进行查询。 关键字：select from where 等
4. DCL(Data Control Language)数据控制语言
用于对数据库访问权限、安全级别等操作 关键字：grant revoke 等
5. TCL(Transaction Control Language) 事务控制语言
用于数据库事务控制 关键字：commit rollback 等
SQL基础操作
DDL(Data Definition Language)数据定义语言
操作数据库
C：创建
1. 直接创建数据库
语法：create database 数据库名称;
2. 创建数据库并指定字符
语法：create database 数据库名 charset 字符集;
R：查询
1. 查看所有数据库
语法：show databases;
2. 查看建库语句
语法：show create database 数据库名;
U：修改
1. 修改数据库字符集
语法：alter database 数据库名 charset 新字符集;
D：删除
1. 直接删除数据库
语法： drop database 数据库名;
使用数据库
1. 进入/切换某一个具体的数据库
语法：use 数据库名;
2. 查看当前所在哪个数据库中
语法：select database();
操作表
C：创建
1. 创建表
语法：
create table 表名(
列名1 数据库类型,
列名1 数据库类型,
列名1 数据库类型,
...
);
2. 常用数据类型
int：整型
float：浮点型
double：浮点型
decimal：浮点型（保留精准度） 钱
decimal(m,n) 指定范围
m 总长度
n 小数长度
varchar：字符型
varchar(n) 指定容纳多少个字符 1~65535
包含字母，符号，汉字
text：文本型
date：日期类型
datetime：日期时间类型
3. 克隆表
创建表时，可以快速指定另一张表的结构
语法：
create table 新表名 like 旧表名;
R：查询
1. 查看所有表
语法：
show tables;
2. 查看建表语句
语法：
show create table 表名;
3. 查看表结构
语法：
desc 表名;
U：修改
1. 添加一列
语法：
alter table 表名 add 列表 数据类型;
2. 修改列类型
语法：
alter table 表名 modify 列表 新类型;
3. 修改列名和类型
语法：
alter table 表名 change 旧列表 新列名 新类型;
4. 删除指定列
语法：
alter table 表名 drop 列名;
5. 修改表字符集
语法：
alter table 表名 charset 字符集;
6. 修改表名
语法：
rename table 旧表名 to 新表名;
D：删除
1. 直接删除表
语法：
drop table 表名;
DML(Data Manipulation Language)数据操作语言
添加
1. 语法：
insert into 表名(列名1,列名2 ...) values(值1,值2 ...);
如果插入数据时，设置所有列对应的数据，表名后面的列名，可以省略
insert into 表名 values(值1,值2 ...);
要求：值与列能够一一对应起来即可
批量查询语法：
insert into 表名(列名1,列名2 ...) values(值1,值2 ...),(值1,值2 ...),(值1,值2 ...);
2. 注意：
列名和值的数据类型要对应
字符串类型可以使用单双引号，推荐单引号
字符串可以插入一切（任意）类型，MySQL底层实现了隐式转换
个别列名字段名如果跟关键字冲突了，我们可以使用反引号包裹起来
蠕虫复制
1.克隆表
CREATE TABLE 新表 LIKE 旧表;
2.数据迁移
INSERT INTO 新表 SELECT * FROM 旧表;
修改
1. 语法：
update 表名 set 列表1=值1,列表2=值2 ... [where条件]
解释说明：笔记中如果在SQL语句中，使用到中括号，括起来的内容可以省略
删除
1. 语法：
delete from 表名 [where条件]
DQL(Data Query Language) 数据查询语言
DQL简单查询
1. 格式：
select * from 表名;
select 列名1, 列名2 ... from 表名;
2. 去重关键字 distinct:去除重复的关键字
select distinct 列名 from 表名;
3. 查询结果进行运算，不会影响原表中的记录
注意：查询结果只用于展示，不会操作原始数据
4. null参与数学运算结果还是null
ifnull()函数
ifnull(列名,默认值) 如果该列不为null，直接返回，如果为null，返回默认值
5. 设置查询别名
select 列名 [as] 别名, ... from 表名 [as] 表别名
DQL条件查询
1. 格式
select * from 表名 where 条件;
select 列名1, 列名2 ... from 表名 where 条件;
2. 关系（比较）运算符
> < >= <= != =
3. 逻辑运算符
&& and 条件同时满足
|| or 条件满足其一即可
! not 条件取反
4. in关键字（某一个列，查询多个值）
select 列名1, 列名2 ... from 表名 where 列名 in（值1,值2 ...);
5. between关键字（范围查询） 包括取值范围两端的值
select 列名1, 列名2 ... from 表名 where 列名 between 较小值 and 较大值;
6. null值处理
is null 为空条件
is not null 不为空条件
7. like关键字（模糊匹配）
通配符：
% 匹配任意多个字符
_ 下划线，匹配任意一个字符
DQL高级查询
排序
1. 语法：
select ... from 表名 order by 排序列1 [asc | desc],排序列2 [asc | desc] ...
asc:升序 默认值，可以省略
desc:降序
2. 注意：
多列排序时，后面的排序结果是在前面的排序结果基础上再排序的
聚合函数
语法：
count(列名)：统计一列的个数
max(列名):求一列最大值
min(列名):求一列最小值
sum(列名):对一列求和
avg(列名):求一列数据的平均值
分组
1. 语法：
select 分列组 from 表名 group by 分组列 having 分组后的过滤条件
2. where和having区别
where是分组前设置的过滤条件，不能使用聚合函数
having是分组后设置的过滤条件，可以使用聚合函数
分页
1. 语法：
select ... from 表名 limit 分页起始索引,每页记录数;
limit分页关键字
2. 索引特点：
分页起索引：默认是从0开始查询，0是默认值，可以省略
3. 分页索引公式：
起始索引=(当前页-1)*每页记录数
sql语句执行顺序：select ... from 表名 where 条件 group by 分组 having 分组后条件 order by 排序条件 limit 分页条件；
数据库约束
1. primary key:主键约束【掌握】 要求表中有一个字段 唯一 且 非空，通常我们使用id作为主键
特点：唯一（不会重复）且非空，一张表只能有一个主键约束
2. unique：唯一约束（对于null无效）
该列不允许重复
3. not null：非空约束
该列不允许为空（null）
4. default：默认值
为列指定默认值
5. foreign key：外键约束
约定表与表之间的关联关系
表关系
一对多 多对多 一对一
外键约束
1. 在从表中添加外键约束
1）创建表
create table 表名(
id int primary key auto_increment,
列名 数据类型,
...
[constraint] [约束名] foreign key(外键列) references 主表(主键列)
);
2）已有表
alter table 表名 add [constraint] [约束名] foreign key(外键列) references 主表(主键列);
2. 外键约束特点
1. 不能直接删除主表中还被从表关联的数据
2. 从表不能添加主表中不存在的关联数据
3. 添加数据时，先添加主表数据，再添加从表数据
4. 删除数据时，先删除从表数据，再删除主表数据
5. 外键约束允许为空，但是不能引用错误的数据
3. 删除外键约束
alter table 表名 drop foreign key 约束名;
多表查询
笛卡尔积：多张表的记录进行组合，这种现象称为笛卡尔积(交叉连接)
select ... from 左表,右表
内连接
* 功能
拿左表的数据去匹配右表的数据，如果符合条件则查询显示（两种表交集的部分）
* 语法
1）隐式内连接
select ... from 左表,右表 where 关联条件
2）显示内连接【推荐】
select ... from 左表 [inner] join 右表 on 关联条件;
外连接
1. 左外连接
功能
展示左表全部数据，再去匹配右表数据，如果条件符合则展示。条件不符合则显示null
语法
select ... from 左表 left [outer] join 右表 on 关联条件;
1. 右外连接
功能
展示右表全部数据，再去匹配左表数据，如果条件符合则展示。条件不符合则显示null
语法
select ... from 左表 right [outer] join 右表 on 关联条件;
子查询（嵌套）
* 功能
一条查询语句（select）的执行结果，作为另一条查询语句（select）的一部分。
* 语法 一条select语句执行结果有如下三种情况
1）查询结果单列单值
SELECT MAX(salary) FROM emp;
2）查询结果单列多值
SELECT salary FROM emp;
3）查询结果多列多值
SELECT * FROM emp;
* 规律
子查询结果为单列，肯定作为条件在where后面使用
select ... from 表名 where 列名 in (子查询);
子查询结果为多列，一般作为虚拟表(临时表）在from后面使用
select ... from (子查询) as 表别名;
DCL(Data Control Language)数据控制语言
1. 创建用户
语法：
create user '用户名'@'主机名' identified by '密码';
注意：
主机名：限定客户端登录ip
指定ip：127.0.0.1 （localhost）
任意ip：%
2. 授权用户
语法：
grant 权限1,权限2... on 数据库名.表名 to '用户名'@'主机名';
注意：
权限：
select、insert、delete、update、create、drop、alter...
all 所有权限
数据库名.* 指定库下面所有的表
3. 查看权限
语法：
show grants for '用户名'@'主机名';
4. 撤销授权
语法：
revoke 权限1,权限2... on 数据库名.表名 from '用户名'@'主机名';
注意：
权限：
select、insert、delete、update、create...
all 所有权限
数据库名.* 指定库下面所有的表
5. 删除用户
语法：
drop user '用户名'@'主机名';
6. 密码管理
1.超级管理员
set password for '用户名'@'主机名'=password('新密码');
2.普通用户
set password=password('新密码');
MySQL函数
字符串函数
1. 函数：CONCAT(s1,s2...sn)
描述：字符串 s1,s2 等多个字符串合并为一个字符串
2. 函数：CHAR_LENGTH(str)
描述：返回字符串 str 的字符数
3. 函数：LENGTH(str)
描述：返回字符串 s 的字节数
编码：UTF8（一个中文字符占3个字节）
4. 函数：UCASE(s) | UPPER(s)
描述：将字符串转换为大写
5. 函数：LCASE(s) | LOWER(s)
描述：将字符串转换为小写
6. 函数：LOCATE(s1,s)
描述：从字符串 s 中获取 s1 的开始位置
注意：从1开始
7. 函数：TRIM(str) | LTRIM(str) | RTRIM(str)
描述：字符串去空格
8. 函数：REPLACE(s,s1,s2)
描述：将字符串 s2 替代字符串 s 中的字符串 s1
9. 函数：SUBSTR(s, start, length)
描述：从字符串 s 的 start 位置截取长度为 length 的子字符串
注意：从1开始
10. 函数：STRCMP(str1,str2)
描述：比较字符串大小,左大于右时返回1，左等于右时返回0，，左小于于右时返回-1，
日期函数
1. 函数：NOW() | CURDATE() | CURTIME()
描述：获取系统当前日期时间、日期、时间
2. 函数：YEAR(DATE) | MONTH(DATE) | DAY(DATE)
描述：从日期中选择出年、月、日
3. 函数：LAST_DAY(DATE)
描述：返回月份的最后一天
4. 函数：ADDDATE(DATE,n) | SUBDATE(DATE,n)
描述：计算起始日期 DATE 加（减） n 天的日期
5. 函数：QUARTER(DATE)
描述：返回日期 DATE 是第几季节，返回 1 到 4
6. 函数：DATEDIFF(d1,d2)
描述：计算日期 d1->d2 之间相隔的天数
7. 函数：DATE_FORMAT(d,f)
描述：按表达式 f的要求显示日期 d
数字函数
1. 函数：ABS(x)
描述：返回 x 的绝对值
2. 函数：CEIL(x) | FLOOR(x)
描述：向上（下）取整
3. 函数：MOD(x,y)
描述：返回x mod y的结果，取余
4. 函数：RAND()
描述：返回 0 到 1 的随机数
5. 函数：ROUND(x)
描述：四舍五入
6. 函数：TRUNCATE(x,y)
描述：返回数值 x 保留到小数点后 y 位的值
高级函数
CASE表达式：相当于java中swtich语句
select
case 列名
when 值1 then 希望得到的值1
when 值2 then 希望得到的值2
...
else 前面的条件都无法满足的情况获取到的值
end
from 表名
IF表达式：相当于java中三元运算符
select if(1>0,'真','假') from 表名
TCL(Transaction Control Language) 事务控制语言
手动提交事务
1. 开启事务
begin 或 start transaction
2. 提交事务
commit
3. 回滚事务 将数据恢复执行SQL前的状态
rollback
自动提交事务
* 默认情况下，在MySQL中每一条DML（增删改）语句，就是一个独立的事务
* 查看MySQL是否开启自动提交
show variables like 'autocommit';
* 临时关闭自动提交（手动）
set autocommit=off;
保存（回滚）点
1. 设置保存点
savepoint 保存点名;
2. 回滚到保存点
rollback to 保存点名;
事务特性 ACID
1. 原子性：A atomicity
如果一个包含多个步骤的业务操作，被事务管理，那么这组操作要么同时成功，要么同时失败
2. 一致性：C consistency
事务执行前后，数据的一致性
3. 隔离性：I isolation【重点】
多个事务之间，相互独立，互不影响
脏读【必须要避免的....】
在一个事务中，读到了另一个事务没有提交的数据。
不可重复读
在一个事务中，两次读取的内容不一致，另一个事务在做update（更新操作）
幻读
在一个事务中，两次读取的数量不一致，另外一个事务执行了insert（插入数据操作）
4. 持久性：D durability
事务一旦提交成功，持久化到磁盘中，不可逆
| 级别 | 名字 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 数据库默认隔离级别 |
|---|---|---|---|---|---|---|
| 1 | 读未提交 | read uncommitted | 是 | 是 | 是 | |
| 2 | 读已提交 | read committed | 否 | 是 | 是 | Oracle和SQL Server |
| 3 | 可重复读 | repeatable read | 否 | 否 | 是 | MySQL |
| 4 | 串行化 | serializable | 否 | 否 | 否 |
MySQL优化
硬件优化：提升数据库服务器性能
软件优化：SQL优化、创建索引、数据库读写分离配置等
分析-执行次数比较多的语句
增删改查操作，我们使用查询频次较高，8:2以上比例（查询对比增删改操作）
适合使用创建索引的方式提升数据查询效率
增删改查操作，增删改操作频繁时，不适合使用创建索引方式提升查询效率。可以使用专业的搜索引擎技术完成搜索功能
elasticsearch、solr ...
-- 查询累计插入和返回数据条数 查询累计执行的CRUD操作
show global status like 'Innodb_rows%';
查看SQL语句的执行情况
-- 查看慢查询日志开启情况
show variables like '%slow_query_log%';
-- 查看慢查询时间配置
show variables like '%long_query_time%';
查看慢查询日志更全参数：show variables like '%query%';
开启慢查询日志：set global slow_query_log = on;
设置慢查询sql的时间阈值：-- 全局配置（下次生效...）set global long_query_time=3;
-- 临时（会话）配置（本次会话窗口生效）set session long_query_time=3;
MySQL索引分类
* 主键（约束）索引
主键约束（非空且唯一） + 提升数据查询效率
* 唯一（约束）索引 unique
唯一约束 + 提升数据查询效率
* 普通索引
提升数据查询效率
* 组合（联合）索引
通过多列组成索引
* 全文索引
solr 、elasticsearch ...全文搜索技术
* hash索引
基于key-value 提升查询效率
索引的优缺点
* 优点
减少磁盘IO，大大提升查询效率
* 缺点
索引会占用磁盘空间
我们在进行增删改操作时，需要重新维护索引，增加数据维护成本。消耗数据库服务器资源。例如：cpu、内存等
索引创建原则
1. 字段内容可识别度不能低于70%
字段内容不要存在大量相同的内容 例如：性别列
2. 经常使用where条件搜索的字段 简单点说：经常作为查询条件的字段
3. 经常使用表连接的字段（内连接、外连接）
4. 经常排序的字段 order by
* 注意：索引本身会占用磁盘空间，不是所有的字段都适合增加索引....
常见索引失效情况
使用like模糊查询，通配符在最左侧时，索引失效 '%jack..'
尽量避免在or查询条件中使用索引。条件中如果有任意一个条件没有索引，索引失效
在索引库进行计算时，索引失效
在使用 !=、 not in 、in、 is null 、is not null等，索引失效
mysql索引数据结构：B+Tree
数据库的存储引擎
MyISAM （非聚集索引） 不支持事务、不支持外键约束 MySQL5.5版本之前默认存储引擎
InnoDB（聚集索引）支持事务管理、支持外键约束 MySQL5.5版本之后默认存储引擎
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
