一、数据类型
1.1 数值类型
分类 | 类型 | 大小 | 有符号(SIGNED) | 无符号(UNSIGNED)范围 | 描述 |
数值类型 | TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32768,32767) | (0,65535) | 大整数值 | |
MEDIUMINT | 3 bytes | (-8388608,8388607) | (0,1677215) | 大整数值 | |
INT或INTEGER | 4 bytes | (-2147483648,2147483647) | (0,4294967295) | 大整数值 | |
BIGINT | 8 bytes | (-2^63,2^63-1) | (0,2^64-1) | 极大整数值 | |
FLOAT | 4 bytes | (-3.402823466 E+38,3.402823466351 E+38) | 0和(1.175494351 E-38,3.402823466 E+38) | 单精度浮点数值 | |
DOUBLE | 8 bytes | (-1.7976931348623157 E+308,1.7976931348623157 E+308) | 0和(2.2250738585072014 E-308,1.7976931348623157 E+308) | 双精度浮点数值 | |
DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
其中,score DECIMAL(4,1)表示整数部分和小数部分的长度为4,保留1位小数。
1.2 字符串类型
分类 | 类型 | 大小 | 描述 |
字符串类型 | CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 | |
TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 | |
TINYTEXT | 0-255 bytes | 短文本字符串 | |
BLOB | 0-65535 bytes | 二进制形式的长文本数据 | |
TEXT | 0-65535 bytes | 长文本数据 | |
MEDIUMBLOB | 0-16777215 bytes | 二进制形式的中等长度文本数据 | |
MEDIUMTEXT | 0-16777215 bytes | 中等长度文本数据 | |
LONGBLOB | 0-4294967295 bytes | 二进制形式的极大文本数据 | |
LONGTEXT | 0-4294967295 bytes | 极大文本数据 |
1.3 日期类型
分类 | 类型 | 大小 | 范围 | 格式 | 描述 |
日期类型 | 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:00 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
根据需求创建表(设计合理的数据类型、长度)
设计一张员工信息表,要求如下:
1.编号(纯数字)
2.员工工号(字符串类型,长度不超过10位)
3. 员工姓名(字符串类型,长度不超过10位)
4.性别(男/女,存储一个汉字)
5.年龄(正常人年龄,不可能存储负数)
6. 身份证号(二代身份证号均为18位,身份证中有X这样的字符)
7.入职时间(取值年月日即可)
二、操作数据
2.1 通用语法及分类
分类 | 说明 |
DDL | 数据定义语言,用来定义数据库对象(数据库、表、字段) |
DML | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | 数据查询语言,用来查询数据库中表的记录 |
DCL | 数据控制语言,用来创建数据库用户、控制数据库的控制权限 |
2.2 DDL(数据定义语言)
2.2.1 数据库操作
SQL语句 | 说明 |
| 查询所有数据库 |
| 查询当前数据库 |
| 创建数据库 |
| 删除数据库 |
| 使用数据库 |
注意事项
-
UTF8字符集长度为3字节,有些符号占4字节,所以推荐用utf8mb4字符集
2.2.2 表操作
SQL语句 | 说明 |
| 查询当前数据库所有表 |
| 查询表结构 |
| 查询指定表的建表语句 |
| 创建表 |
| 添加字段 |
| 修改数据类型 |
| 修改字段名和字段类型 |
【例】 | |
| 删除字段 |
| 修改表名 |
| 删除表 |
| 删除表,并重新创建该表 |
2.3 DML(数据操作语言)
2.3.1 添加数据
SQL语句 | 说明 |
| 指定字段 |
| 全部字段 |
| 批量添加数据 |
注意事项
字符串和日期类型数据应该包含在引号中
插入的数据大小应该在字段的规定范围内
2.3.2 更新和删除数据
SQL语句 | 说明 |
| 修改数据 |
| 删除数据 |
2.4 DQL(数据查询语言)
SQL语法 | 说明 |
| 字段列表 |
| 表名字段 |
| 条件列表 |
| 分组字段列表 |
| 分组后的条件列表 |
| 排序字段列表 |
| 分页参数 |
2.4.1 基础查询
SQL语句 | 说明 |
| 查询多个字段 |
| 设置别名 |
| 去除重复记录 |
| 转义 |
2.4.2 条件查询
SQL语句:
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件:
比较运算符 | 功能 |
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN ... AND ... | 在某个范围内(含最小、最大值) |
IN(...) | 在in之后的列表中的值,多选一 |
LIKE 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
IS NULL | 是NULL |
逻辑运算符 | 功能 |
AND 或 && | 并且(多个条件同时成立) |
OR 或 || | 或者(多个条件任意一个成立) |
NOT 或 ! | 非,不是 |
2.4.3 聚合查询(聚合函数)
函数 | 功能 |
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
SQL语句
SELECT 聚合函数(字段列表) FROM 表名;
2.4.4 分组查询
SQL语句
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];
where 和 having 的区别:
-
执行时机不同:where是分组之前进行过滤,不满足where条件不参与分组;having是分组后对结果进行过滤。
-
判断条件不同:where不能对聚合函数进行判断,而having可以。
注意事项
-
执行顺序:where > 聚合函数 > having
-
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义
2.4.5 排序查询
SQL语句:
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式:
-
ASC: 升序(默认)
-
DESC: 降序
注意事项
如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
2.4.6 分页查询
SQL语句
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
注意事项
-
起始索引从0开始,起始索引 = (查询页码 - 1) * 每页显示记录数
-
分页查询是数据库的方言,不同数据库有不同实现,MySQL是LIMIT
-
如果查询的是第一页数据,起始索引可以省略,直接简写 LIMIT 10
DQL执行顺序
FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
2.5 DCL
SQL语句 | 说明 |
| 查询用户 |
| 创建用户 |
| 修改用户密码 |
| 删除用户 |
注意事项
-
主机名可以使用 % 通配
权限控制
常用权限:
权限 | 说明 |
ALL, ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
SQL语句 | 说明 |
| 查询权限 |
| 授予权限 |
| 撤销权限 |
注意事项
-
多个权限用逗号分隔
-
授权时,数据库名和表名可以用 * 进行通配,代表所有