一、SQL分类
SQL是结构化查询语言(Structured Query Language)的缩写。它是一种用于管理和操作关系型数据库系统的标准化语言。SQL分类如下:
![](https://img-blog.csdnimg.cn/18214c037ca44c84b189f66d3a40c681.png)
- DDL: 数据定义语言,用来定义数据库对象(数据库、表、字段)
- DML: 数据操作语言,用来对数据库表中的数据进行增删改
- DQL: 数据查询语言,用来查询数据库中表的记录
- DCL: 数据控制语言,用来创建数据库用户、控制数据库的控制权限
二、DDL-数据定义语言
2.1 DDL-数据库操作
![](https://img-blog.csdnimg.cn/5faef5ddb71e41bc892b1b116b47d279.png)
查询所有数据库:
SHOW DATABASES;
查询当前数据库:
SELECT DATABASE();
创建数据库:
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];
删除数据库:
DROP DATABASE [ IF EXISTS ] 数据库名;
使用数据库:
USE 数据库名;
注意事项
- UTF8字符集长度为3字节,有些符号占4字节,所以推荐用utf8mb4字符集
2.2 DDL-表操作
![](https://img-blog.csdnimg.cn/8e788da60ab943fba1939d2516ceb1d9.png)
查询当前数据库所有表:
SHOW TABLES;
查询表结构:
DESC 表名;
查询指定表的建表语句:
SHOW CREATE TABLE 表名;
![](https://img-blog.csdnimg.cn/924b39586990469a9d48d9b0404ded25.png)
创建表:
CREATE TABLE 表名 (
字段1 数据类型 约束,
字段2 数据类型 约束,
字段3 数据类型 约束,
...
);
修改表名:
ALTER TABLE 表名 RENAME TO 新表名
删除表:
DROP TABLE [IF EXISTS] 表名;
删除表,并重新创建该表:
TRUNCATE TABLE 表名;
2.3 DDL-字段操作
添加字段:
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
例:ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';
修改字段数据类型:
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型:
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
例:将emp表的nickname字段修改为username,类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';
删除字段:
ALTER TABLE 表名 DROP 字段名;
三、补充:数据类型
3.1 整型
类型名称 |
取值范围 |
大小 |
TINYINT |
-128〜127 |
1个字节 |
SMALLINT |
-32768〜32767 |
2个字节 |
MEDIUMINT |
-8388608〜8388607 |
3个字节 |
INT |
-2147483648〜2147483647 |
4个字节 |
BIGINT |
-9223372036854775808〜9223372036854775807 |
8个字节 |
无符号在数据类型后加 unsigned 关键字。
3.2 浮点型
类型名称 |
说明 |
存储需求 |
FLOAT |
单精度浮点数 |
4 个字节 |
DOUBLE |
双精度浮点数 |
8 个字节 |
DECIMAL (M, D),DEC |
压缩的“严格”定点数 |
M+2 个字节 |
3.3 日期和时间
类型名称 |
日期格式 |
日期范围 |
存储需求 |
YEAR |
YYYY |
1901 ~ 2155 |
1 个字节 |
TIME |
HH:MM:SS |
-838:59:59 ~ 838:59:59 |
3 个字节 |
DATE |
YYYY-MM-DD |
1000-01-01 ~ 9999-12-3 |
3 个字节 |
DATETIME |
YYYY-MM-DD HH:MM:SS |
1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 |
8 个字节 |
TIMESTAMP |
YYYY-MM-DD HH:MM:SS |
1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC |
4 个字节 |
3.4 字符串
类型名称 |
说明 |
存储需求 |
CHAR(M) |
固定长度非二进制字符串(性能好)
|
M 字节,1<=M<=255 |
VARCHAR(M) |
变长非二进制字符串 (性能较差)
|
L+1字节,在此,L< = M和 1<=M<=255 |
TINYTEXT |
非常小的非二进制字符串 |
L+1字节,在此,L<2^8 |
TEXT |
小的非二进制字符串 |
L+2字节,在此,L<2^16 |
MEDIUMTEXT |
中等大小的非二进制字符串 |
L+3字节,在此,L<2^24 |
LONGTEXT |
大的非二进制字符串 |
L+4字节,在此,L<2^32 |
ENUM |
枚举类型,只能有一个枚举字符串值 |
1或2个字节,取决于枚举值的数目 (最大值为65535) |
SET |
一个设置,字符串对象可以有零个或 多个SET成员 |
1、2、3、4或8个字节,取决于集合 成员的数量(最多64个成员) |
3.5 二进制类型
类型名称 |
说明 |
存储需求 |
BIT(M) |
位字段类型 |
大约 (M+7)/8 字节 |
BINARY(M) |
固定长度二进制字符串 |
M 字节 |
VARBINARY (M) |
可变长度二进制字符串 |
M+1 字节 |
TINYBLOB (M) |
非常小的BLOB |
L+1 字节,在此,L<2^8 |
BLOB (M) |
小 BLOB |
L+2 字节,在此,L<2^16 |
MEDIUMBLOB (M) |
中等大小的BLOB |
L+3 字节,在此,L<2^24 |
LONGBLOB (M) |
非常大的BLOB |
L+4 字节,在此,L<2^32 |
四、DML- 数据操作语言
4.1 添加数据
![](https://img-blog.csdnimg.cn/7b683a9dee8842e18c52764cbf5bf120.png)
指定字段:
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
全部字段:
INSERT INTO 表名 VALUES (值1, 值2, ...);
批量添加数据:
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
注意事项
- 字符串和日期类型数据应该包含在引号中
- 插入的数据大小应该在字段的规定范围内
4.2 更新和删除数据
![](https://img-blog.csdnimg.cn/c0af25f6e6aa474ea4486ba302beaefa.png)
修改数据:
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];
例:
UPDATE emp SET name = 'Jack' WHERE id = 1;
删除数据:
DELETE FROM 表名 [ WHERE 条件 ];
五、DQL- 数据查询语言
![](https://img-blog.csdnimg.cn/9b580aad4d644b658189a6b47c055cdc.png)
5.1 基础查询
![](https://img-blog.csdnimg.cn/ba0080dbbb194f919c0703b70b596e3b.png)
查询多个字段:
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;
设置别名:
SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;
SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;
去除重复记录:
SELECT DISTINCT 字段列表 FROM 表名;
5.2 条件查询
![](https://img-blog.csdnimg.cn/d24bff669e4243b8b998d50cd19bad70.png)
条件查询:
SELECT 字段列表 FROM 表名 WHERE 条件列表;
例子:
-- 年龄等于30
select * from employee where age = 30;
-- 年龄小于30
select * from employee where age < 30;
-- 小于等于
select * from employee where age <= 30;
-- 没有身份证
select * from employee where idcard is null or idcard = '';
-- 有身份证
select * from employee where idcard is not null;
-- 不等于
select * from employee where age != 30;
-- 年龄在20到30之间
select * from employee where age between 20 and 30;
select * from employee where age >= 20 and age <= 30;
-- 下面语句不报错,但查不到任何信息
select * from employee where age between 30 and 20;
-- 性别为女且年龄小于30
select * from employee where age < 30 and gender = '女';
-- 年龄等于25或30或35
select * from employee where age = 25 or age = 30 or age = 35;
select * from employee where age in (25, 30, 35);
-- 姓名为两个字
select * from employee where name like '__'; (此处为两个 _ )
-- 身份证最后为X
select * from employee where idcard like '%X';
5.3 聚合函数
语法:
SELECT 聚合函数(字段列表) FROM 表名;
例:
SELECT count(id) from employee where workaddress = "广东省";
5.4 分组查询
![](https://img-blog.csdnimg.cn/eb8e54472cf14460a330e8514c4e5c57.png)
语法:
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];
where 和 having 的区别:
- 执行时机不同:where是分组之前进行过滤,不满足where条件不参与分组;having是分组后对结果进行过滤。
- 判断条件不同:where不能对聚合函数进行判断,而having可以。
例子:
-- 根据性别分组,统计男性和女性数量(只显示分组数量,不显示哪个是男哪个是女)
select count(*) from employee group by gender;
-- 根据性别分组,统计男性和女性数量
select gender, count(*) from employee group by gender;
-- 根据性别分组,统计男性和女性的平均年龄
select gender, avg(age) from employee group by gender;
-- 年龄小于45,并根据工作地址分组
select workaddress, count(*) from employee where age < 45 group by workaddress;
-- 年龄小于45,并根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress, count(*) address_count from employee where age < 45 group by workaddress having address_count >= 3;
注意事项
- 执行顺序:where > 聚合函数 > having
- 分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
5.5 排序查询
![](https://img-blog.csdnimg.cn/38b56b60afd24035922c5f5fe639c23e.png)
语法:
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式:
例子:
-- 根据年龄升序排序
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age;
-- 两字段排序,根据年龄升序排序,入职时间降序排序
SELECT * FROM employee ORDER BY age ASC, entrydate DESC;
注意事项:
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
5.6 分页查询
![](https://img-blog.csdnimg.cn/be61e02450344403954befee33e9d994.png)
语法:
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
例子:
-- 查询第一页数据,展示10条
SELECT * FROM employee LIMIT 0, 10;
-- 查询第二页
SELECT * FROM employee LIMIT 10, 10;
注意事项
- 起始索引从0开始,起始索引 = (查询页码 - 1) * 每页显示记录数
- 分页查询是数据库的方言,不同数据库有不同实现,MySQL是LIMIT
- 如果查询的是第一页数据,起始索引可以省略,直接简写 LIMIT 10
六、DQL-执行顺序
![](https://img-blog.csdnimg.cn/02bff7a54e9043b28bd5630150eb9a2a.png)
DQL执行顺序
FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
七、DCL-数据控制语言 (开发人员操作较少)
7.1 管理用户
![](https://img-blog.csdnimg.cn/558f82aba3f64e35b5ab4b4e06e4f416.png)
查询用户:
USE mysql;
SELECT * FROM user;
创建用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码:
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户:
DROP USER '用户名'@'主机名';
例子:
-- 创建用户test,只能在当前主机localhost访问
create user 'test'@'localhost' identified by '123456';
-- 创建用户test,能在任意主机访问
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密码
alter user 'test'@'localhost' identified with mysql_native_password by '1234';
-- 删除用户
drop user 'test'@'localhost';
注意事项
7.2 权限控制
常用权限:
权限 |
说明 |
ALL, ALL PRIVILEGES |
所有权限 |
SELECT |
查询数据 |
INSERT |
插入数据 |
UPDATE |
修改数据 |
DELETE |
删除数据 |
ALTER |
修改表 |
DROP |
删除数据库/表/视图 |
CREATE |
创建数据库/表 |
![](https://img-blog.csdnimg.cn/0e9a0da1656e4f749eac8e9cbe972753.png)
查询权限:
SHOW GRANTS FOR '用户名'@'主机名';
授予权限:
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限:
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
注意事项
- 多个权限用逗号分隔
- 授权时,数据库名和表名可以用 * 进行通配,代表所有