MySQL笔记_0
创建数据库
字符集选择: utf8mb4
Unicode编码，也称作统一码，万国码。如：utf8, utf16, utf32utf8mb4兼容utf8，并且可以表示更多的字符。具体来说:Unicode中的第1-126行是utf8的区域，utf8mb4同样兼容这一片区域，剩下126行以下的部分是utf8mb4扩充区域。- 一般不选择
utf8，因为其占用3B；而实际上很多内容，比如表情，就需要4B空间，所以一般使用utf8mb4
排序规则选择：
- 对于
utf8mb4而言，常用的排序规则有utf8mb4_unicode_ci，utf8mb4_general_ci，utf8mb4_bin
utf8mb4_unicode_ci |
utf8mb4_general_ci |
utf8mb4_bin |
|---|---|---|
| 基于标准的Unicode规则来排序和比较 | 没有实现Unicode排序规则 | 将字符串的每个字符用二进制数据编译存储 |
| 各语言精确排序 | 在某些特殊语言字符集，排序结果可能不一致 | |
| 不区分大小写 | 不区分大小写 | 区分大小写 |
| 为了能够处理特殊字符，实现了略微复杂的排序算法 | 比较和排序的时候更快 |
SQL分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，定义数据库、表、字段 |
| DML | Data Manipulation Language | 数据操作语言，对数据增 删 改 |
| DQL | Data Query Language | 数据查询语言，查 |
| DCL | Data Control Language | 数据控制语言，创建数据库用户，设置访问权限 |
DDL
查
-
查询所有数据库:
show databases; -
查询当前数据库:
select database(); -
查询当前数据库中所有的表:
show tables; -
查询表结构:
desc 表名; -
查询建表语句:
show create table 表名;
增
-
创建数据库:
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]; -
创建表:
create table 表名 (字段名 字段类型 [comment '字段注释'], 字段名 字段类型 [comment '字段注释'], ...) [comment '表注释']; -
添加表字段:
alter table 表名 add 字段名 类型 [comment '字段注释'];
删
-
删除数据库:
drop database [if exists] 数据库名; -
删除表:
drop table [if exists] 表名; -
删除并重新创建表:
truncate table 表名; -
删除字段:
alter table 表名 drop 字段名;
改
-
修改表名:
alter table 表名 rename to 新表名; -
修改字段数据类型:
alter table 表名 modify 字段名 新类型; -
修改字段名和数据类型:
alter table 表名 change 原字段名 新字段名 类型 [comment '字段注释'];
类型
| 分类 | 类型 | 大小 | 有符号范围 | 无符号范围 | 描述 |
|---|---|---|---|---|---|
| 数值 | TINYINT | 1B | (-128, 127) | (0, 255) | |
| SMALLINT | 2B | (-32768, 32767) | (0, 65535) | ||
| MIDIUMINT | 3B | (-8388608, 8388607) | (0, 16777215) | ||
| INT or INTEGER | 4B | (-2147483648, 2147483647) | (0, 4294967295) | ||
| BIGINT | 8B | (-2^63, 2^63 - 1) | (0, 2^64 - 1) | ||
| FLOAT | 4B | (-3.4028E+38, 3.4028E+38) | 0 and (1.17549E-38, 3.4028E+38) | ||
| DOUBLE | 8B | (-1.79769E+308, 1.79769E+308) | 0 and (2.22507E-308, 1.79769E+308) | ||
| DECIMAL | 由M(精度),D(标度)决定 | 由M(精度),D(标度)决定 | 精确定点数 |
| 分类 | 类型 | 大小 | 描述 |
|---|---|---|---|
| 字符串 | CHAR | (0, 255B) | 定长字符串 |
| VARCHAR | (0, 65535B) | 变长字符串 | |
| TINYBLOB | (0, 255B) | 不超过255个字符的二进制数据 | |
| TINYTEXT | (0, 255B) | 短文本 | |
| BLOB | (0, 65535B) | 二进制长文本 | |
| TEXT | (0, 65535B) | 长文本 | |
| MEDIUMBLOB | (0, 16777215B) | ||
| MEDIUMTEXT | (0, 16777251B) | ||
| LONGBLOB | (0, 4294967295B) | ||
| LONGTEXT | (0, 4294967295B) |
| 分类 | 类型 | 大小 | 范围 |
|---|---|---|---|
| 日期 | DATE | 3B | 1000-01-01至9999-12-31 |
| TIME | 3B | -838:59:59至838:59:59 | |
| YEAR | 1B | 1901至2155 | |
| DATETIME | 8B | 1000-01-01 00:00:00至9999-12-31 23:59:59 | |
| TIMESTAMP | 4B | 1970-01-01 00:00:01至2038-01-19 03:14:07 |
- 存储年龄，使用
tinyint unsigned，不能写unsigned tinyint - 存分数，一位小数，存储钱数，可以使用
decimal(4,1)，表示最多四位，小数在最后一位之前 char(10)定长为10，性能好，存储性别可以使用char(1)varchar(10)不定长，性能差一些timestamp最大时间到2038，所以一般不用- 记录生日使用
date
DML
增
-
给指定字段添加数据:
insert into 表名 (字段1, 字段2) values (值1, 值2); -
给所有字段添加数据:
insert into 表名 values (值1, 值2, ...); -
批量添加数据:
1
2insert into 表名 (字段1, 字段2) values (值1, 值2), (值3, 值4), ...
insert into 表名 values (值1, 值2, 值3, 值4, ...), ... -
插入数据为字符串或者日期类型，应使用引号，否则不会走索引
删
- 删除数据:
delete from 表名 [where 条件];
改
- 修改数据:
update 表名 set 字段1=值1, 字段2=值2,..., [where 条件];
DQL
-
查询语句:
select 字段列表 from 表名列表 [where 条件] [group by 分组字段列表] [having 分组后过滤条件列表] [order by 排序字段列表] [limit 分页参数]; -
查询时可以设置别名:
select 字段1 as 别名1, 字段2 as 别名2; -
查询去重:
select distinct 字段列表 from 表名; -
条件查询:
| 比较运算符 | 功能 |
|---|---|
<> or != |
不等于 |
BETWEEN min AND max |
在[min, max]范围内, 包含边界 |
IN(...) |
在列表中多选一 |
LIKE |
模糊匹配(_匹配单个字符, %匹配任意个字符) |
IS NULL |
为空 |
-
聚合函数
count, max, min, avg, sum:- 作用于某一列，所有
null不参与聚合函数计算 where是分组之前过滤，在聚合函数之前执行，因此不能使用聚合函数；having是分组之后过滤，聚合函数在having之前已经执行了，因此可以使用聚合函数- 分组查询一般查询分组之后的字段和聚合函数，其他没有意义
- 作用于某一列，所有
-
排序
order by 字段1 排序方式1, 字段2 排序方式2:- 升序
asc，降序desc，默认为升序
- 升序
-
分页查询
limit 起始索引, 查询记录数:- 起始索引从0开始，
起始索引 = (查询页码 - 1) * 每页记录个数 - 分页查询是数据库的方言，不同数据库关键字不同，
MySQL中使用limit，如果查询第一页的数据，则起始索引可以省略
- 起始索引从0开始，
DQL执行顺序:
from, where, group by, having, select, order by, limit- 首先执行
from找到表名，然后执行where判断条件，再group by分组和having，再select选择字段列表，最后order by排序和limit分页
DCL
查
-
查询用户:
use mysql; select * from user;用户信息都存放在
mysql系统数据库中 -
查询用户权限:
show grants for '用户名'@'主机名';
增
- 创建用户:
create user '用户名'@'主机名' identified by '密码';如果需要该用户可以在任意主机上访问该数据库，需要将主机名修改为
%即可
删
- 删除用户:
drop user '用户名'@'主机名';
改
-
修改密码:
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码'; -
授权:
grant 权限列表 on 数据库名.表名 to '用户名'@'表名'; -
撤销权限:
revoke 权限列表 on 数据库名.表名 from '用户名'@'表名';'用户名'@'表名'中间不能存在空格
| 权限 | 说明 |
|---|---|
| ALL, ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |