SQL
SQL分类
SQL 语句通常分为四大类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象(数据库，表，字段) |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户，控制数据库的访问权限 |
SQL通用语法
- SQL语句可以单行/多行书写，以分号结束。
- SQL语句中可以增加缩进/空格来增强可读性。
- SQL语句中的关键字不区分大小写。
- SQL语句注释：单行注释(-注释)，多行注释(
/*注释*/)
DDL数据库操作
查询
- 查询所有数据库：show databases;
- 查询当前数据库：select database();
使用
- 使用数据库：use 数据库名;
创建
- create database
[if not exists]数据库名;
删除
- 删除数据库：drop database
[if exists]数据库名;
注意事项
- 上述语法中的database,也可以替换成schema。如：create schema db01;
DDL图像化工具
- SQLyog
- Navicat
- DataGrip -(IDEA内置)
DDL表结构操作
创建
1 | create table 表名( |
约束
- 概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
- 目的：保证数据库中数据的正确性、有效性和完整性。
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制字段值不能为null | not null |
| 唯一约束 | 保证字段的所有数据都是唯一，不重复的 | unique |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | primary key(auto_increment自增) |
| 默认约束 | 保存数据时，如果未指定该字段值，则采用默认值 | default |
| 外键约束 | 让两张表的数据建立连接，保证数据的一致性和完整性 | foreign key |
数据类型
-
数据类型
- MySQL中的数据类型有很多，主要分为三类:数值类型、字符串类型、日期时间类型。
-
数值类型
| 类型 | 大小(byte) | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 | 备注 |
|---|---|---|---|---|---|
| tinyint | 1 | (-128, 127) | (0, 255) | 小整数值 | |
| smallint | 2 | (-32768, 32767) | (0, 65535) | 大整数值 | |
| mediumint | 3 | (-8388608, 8388607) | (0, 16777215) | 大整数值 | |
| int | 4 | (-2147483648, 2147483647) | (0, 4294967295) | 大整数值 | |
| bigint | 8 | (-2^63, 2^63-1) | (0, 2^64-1) | 极大整数值 | |
| float | 4 | (-3.402823466 E+38, 3.402823466 E+38) | 0 和 (1.175494351 E-38, 3.402823466 E+38) | 单精度浮点数值 | float(5,2): 5表示整数个数字长度，2 表示小数位个数 |
| double | 8 | (-1.7976931348623157 E+308, 1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308) | 双精度浮点数值 | double(5,2): 5表示整数个数字长度，2 表示小数位个数 |
| decimal | 小数值(精度更高) | decimal(5,2): 5表示整数个数字长度，2 表示小数位个数 |
- 字符串类型
| 类型 | 大小(byte) | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 | 备注 |
|---|---|---|---|---|---|
| tinyint | 1 | (-128, 127) | (0, 255) | 小整数值 | |
| smallint | 2 | (-32768, 32767) | (0, 65535) | 大整数值 | |
| mediumint | 3 | (-8388608, 8388607) | (0, 16777215) | 大整数值 | |
| int | 4 | (-2147483648, 2147483647) | (0, 4294967295) | 大整数值 | |
| bigint | 8 | (-2^63, 2^63-1) | (0, 2^64-1) | 极大整数值 | |
| float | 4 | (-3.402823466 E+38, 3.402823466 E+38) | 0 和 (1.175494351 E-38, 3.402823466 E+38) | 单精度浮点数值 | float(5,2): 5表示整数个数字长度，2 表示小数位个数 |
| double | 8 | (-1.7976931348623157 E+308, 1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308, 1.7976931348623157 E+308) | 双精度浮点数值 | double(5,2): 5表示整数个数字长度，2 表示小数位个数 |
| decimal | 小数值(精度更高) | decimal(5,2): 5表示整数个数字长度，2 表示小数位个数 |
- 日期类型
| 类型 | 大小(byte) | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| date | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
| time | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值(包含持续时间) |
| year | 1 | 1901 至 2155 | YYYY | 年份值 |
| datetime | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| timestamp | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，时间戳 |
查询
查询当前数据库所有表：show tables;
查询表结构：desc 表名;
查询建表语句：show create table 表名;
修改
- 添加字段：alter table 表名 add 字段名类型（长度）
[comment注释][约束]; - 修改字段类型：alter table 表名 modify 字段名 新数据类型（长度）;
- 修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型（长度）
[comment注释][约束]； - 删除字段：alter table 表名drop column 字段名；
- 修改表名：rename table 表名 to 新表名；
删除
- 删除表: drop table
[if exists]表名;
在删除表时，表中的全部数据也会被删除
DML
添加数据
insert语法
- 指定字段添加数据：insert into 表名 (字段名1，字段名2) values (值1，值2);
- 全部字段添加数据：insert into 表名 values (值1,值2,…);
- 批量添加数据（指定字段）：insert into 表名 (字段名1，字段名2) values(值1，值2)，(值1，值2)；
- 批量添加数据（全部字段）：insert into 表名 values (值1，值2，…)，(值1，值2，…)；
修改数据
update语法
- 修改数据：update 表名 set 字段名1=值1,字段名2=值2, …
[where条件];
删除数据
-
删除数据: delete from 表名
[where 条件]; -
注意事项
DELETE语句的条件可以有，也可以没有，如果没有条件，则会到除整张表的所有数据。
DELETE语句不能删除某一个字段的值(如果要操作，可以使用UPDATE,将该字段的值置为NULL)
DQL
select
查询多个字段：select 字段1，字段2，字段3 from 表名;
查询所有字段（通配符）: select * from 表名;
设置别名：select 字段1 [a5 别名1], 字段2 [as 别名2] from 表名;
去除重复记录：select distinct 字段列表 from 表名;
where
条件查询: select 字段列表 from 表名 where 条件列表;
| 比较运算符 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| != 或 <> | 不等于 |
| between … and … | 在某个范围内(含最小、最大值) |
| in(…) | 在指定的列表中的值，多选 |
| like | 模糊匹配(匹配单个字符，或匹配任意多个字符) |
| is null | 判断是否为null |
| 逻辑运算符 | 功能 |
|---|---|
| and 或 && | 并且(多个条件同时成立) |
| or 或 | |
| not 或 ! | 非，不是 |
聚合函数查询
聚合函数
- 介绍：将一列数据作为一个整体，进行纵向计算。
- 语法：select 聚合函数（字段列表）from 表名;
- 聚合函数不对Null进行运算
| 函数 | 功能 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
分组查询
分组查询: select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
使用group by返回有两类字段，一类是分组字段，一类是聚合函数
分组查询通常使用以下字段列表
- group by 分组字段列表
- having 分组后条件列表
where与having区别
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where.不能对聚合函数进行判断，而having可以。
注意事项
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
- 执行顺序：where>聚合函数>having。
排序查询
- order by 排序字段列表
- limit 分页参数
语法
条件查询：select 字段列表 from 表名 [where条件列表] [group by 分组字段] order by 字段1 排序方式1，字段2 排序方式2…;
当字段1排序相同时，字段2才生效
排序方式:
- ASC: 升序(默认值)
- DESC: 降序
分页查询
语法
- 分页查询：select 字段列表 from 表名 limit (起始索引，查询记录数)
条件表达式
三元判断
函数：
将会取第二个值
-
if(表达式,tvalue,fvalue):当表达式为true时，取值tvalue;当表达式为false时，取值fvalue
-
case expr when value1 then result1
[when value2 then value2...][else result]end
All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.