目录
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版本之后默认存储引擎