MySQL详细文档地址

主：https://www.naste.top:1024/categories/MySQL/

次：http://mysql-doc.naste.top:2048/

数据库增删改查

新增

1
2
3
4
# CHARACTER：字符集【utf8mb4】
# COLLATE：排序规则【utf8mb4_general_ci】
# ENCRYPTION：是否创建加密空间【N：否  Y：是】
CREATE DATABASE [ IF NOT EXISTS ] `数据库名字` [ DEFAULT CHARACTER SET 字符集 COLLATE 排序规则 ] [DEFAULT ENCRYPTION='N/Y' ];
1
CREATE DATABASE  IF NOT EXISTS `test3` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

删除

1
DROP DATABASE [ IF EXISTS ] `数据库名`;
1
DROP DATABASE IF EXISTS `test3`;

修改

1
2
# 修改字符集和排序规则
ALTER DATABASE `test3` DEFAULT CHARACTER SET 字符集 COLLATE 排序规则;
1
ALTER DATABASE `test3` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 查询所有数据库
SHOW DATABASES;

# 查询当前正在使用的数据库
SELECT DATABASE();

# 查询指定库下面的所有表
SHOW TABLES FROM `数据库名`;

# 查看数据库的创建信息，可以查看创建数据库的sql
SHOW CREATE DATABASE `数据库名`;

# 使用指定数据库
USE `数据库名`;

表增删改查

新增

下面创建表的格式可能不全，推荐使用Navicat建表时使用SQL预览，或者使用SHOW CREATE TABLE 表名查看表的创建语句

image-20230219170701131

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
# 格式
CREATE TABLE [IF NOT EXISTS] 表名(
	字段1, 数据类型 [约束条件] [默认值] [注释],
	字段2, 数据类型 [约束条件] [默认值] [注释],
	字段3, 数据类型 [约束条件] [默认值] [注释],
	……
	[表约束条件]
);

# 案例
CREATE TABLE IF NOT EXISTS `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `字段名` 字段类型 [ CHARACTER SET 字符集 COLLATE 排序规则 ] [DEFAULT 默认之] [COMMENT '字段注释'],
  PRIMARY KEY (`id`)
) [ENGINE=InnoDB] [DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci] [COMMENT='表注释'];

# 数据库导出SQL
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `username` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户名',
  `password` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '用户密码',
  `BELONG_ORG_CODE` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '111' COMMENT '所属组织机构',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1318 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户信息';

删除

1
DROP TABLE [ IF EXISTS ] `表1` [, `表2`, …, `表3`];
1
2
3
4
5
# 删除一张表
DROP TABLE IF EXISTS `user1`;

# 同时删除多张表
DROP TABLE IF EXISTS `user1`, `user2`;

修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 新增列
ALTER TABLE [`数据库名`.]`表名` ADD COLUMN `新增字段名` 字段类型 [(NOT NULL/NULL) [DEFAULT '默认值'] [COMMENT '字段注释'] [AFTER `指定新增在哪个字段后面`];
                                                   
ALTER TABLE `user` ADD COLUMN `test2` VARCHAR(25) NOT NULL DEFAULT '11' COMMENT '测试字段' AFTER `test`;
   
# 删除字段
ALTER TABLE [`数据库名`.]`表名` DROP COLUMN `字段名`;

ALTER TABLE `user` DROP COLUMN `test3`;      
                                                   
#修改列
ALTER TABLE [`数据库名`.]`表名` CHANGE COLUMN `旧字段名` `新字段名` 字段类型 [(NOT NULL/NULL) [DEFAULT '默认值'] [COMMENT '字段注释'] [AFTER `指定新增在哪个字段后面`];
                                                            
ALTER TABLE `test`.`user` CHANGE COLUMN `test2` `test3` int NOT NULL DEFAULT 11 COMMENT '测试字段' AFTER `test`;

查询

1
2
3
4
5
6
# 查看建表SQL
SHOW CREATE TABLE `表名`;

# 查看表字段信息
DESC `表名`;
DESCRIBE `表名`;

清空表

  • TRUNCATE TABLE比DELETE FROM tableName速度快，且使用的系统和事务日志资源少，但TRUNCATE无事务且不触发TRIGGER，有可能造成事故，故不建议在开发代码中使用此语句。

  • TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同

1
2
3
4
5
# 使用 TRUNCATE 清空表（不能触发事务，开发可以使用，生产环境不建议使用）
TRUNCATE TABLE `表名`;

# 使用 DELETE 清空表（可以触发事务，开发和生产环境都推荐使用）
DELETE FROM `表名`;

数据增删改查

新增

使用value / values

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 指定插入列，使用value插入一条数据
INSERT INTO `表名` ( `column1`, `column2`, ...... , `columnn` ) 
VALUE ( value1, value2 , ...... , valuen );

# 不指定插入列，使用value插入一条数据，如果不指定列，则value值的顺序就是数据库字段的顺序
INSERT INTO `表名`
VALUE ( value1, value2 , ...... , valuen );

# 插入指定列，使用values插入多条数据
INSERT INTO `表名` ( `column1`, `column2`, ...... , `columnn` ) 
VALUE 
	( value1, value2 , ...... , valuen ),
	( value1, value2 , ...... , valuen );

# 不指定插入列，使用values插入多条数据，如果不指定列，则values值的顺序就是数据库字段的顺序
INSERT INTO `表名`
VALUE 
	( value1, value2 , ...... , valuen ),
	( value1, value2 , ...... , valuen );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
# 指定插入列，使用value插入一条数据
INSERT INTO `user` ( `id`, `username`, `password`, `BELONG_ORG_CODE` ) 
VALUE ( 1, 'Dorothy Walker', 'nioMES9G08', 'WHl8WjeQFT' );

# 不指定插入列，使用value插入一条数据，如果不指定列，则value值的顺序就是数据库字段的顺序
INSERT INTO `user` 
VALUE ( 2, 'Dorothy Walker', 'nioMES9G08', 'WHl8WjeQFT' );

# 插入指定列，使用values插入多条数据
INSERT INTO `user` ( `id`, `username`, `password`, `BELONG_ORG_CODE` )
VALUES
	( 1, 'Dorothy Walker', 'nioMES9G08', 'WHl8WjeQFT' ),
	( 2, 'Xie Yunxi', 'aaNxpPdFuA', 'Qkkn10oQnY' );

# 不指定插入列，使用values插入多条数据，如果不指定列，则values值的顺序就是数据库字段的顺序
INSERT INTO `user1`
VALUES
	( 31, 'Dorothy Walker', 'nioMES9G08', 'WHl8WjeQFT' ),
	( 32, 'Xie Yunxi', 'aaNxpPdFuA', 'Qkkn10oQnY' );

将查询结果插入列中（具体查询使用看后面的查询SQL）

1
2
3
4
5
6
INSERT INTO 目标表名
(tar_column1 [, tar_column2, …, tar_columnn])
SELECT
(src_column1 [, src_column2, …, src_columnn])
FROM 源表名
[WHERE condition]
1
2
INSERT INTO `user1` ( `id`, `username`, `password`, `BELONG_ORG_CODE` ) 
SELECT `id`, `username`, `password`, `BELONG_ORG_CODE` FROM `user` WHERE `id` = 1

删除

1
DELETE FROM `表名` [WHERE <condition>];
1
DELETE FROM `user` WHERE `id` = 31 AND `username` = 'Dorothy Walker';

修改

1
UPDATE `表名` SET column1 = value1, column2 = value2, ...... , column = valuen [WHERE condition]
1
UPDATE `user1` SET `username` = '新用户名' , `password` = '新密码' WHERE `id` = 1 AND `username` = '1';

查询

MySQL函数

单行函数：

聚合函数：

单行函数：https://new-wangz.github.io/2022/04/11/%E7%AC%AC07%E7%AB%A0-%E5%8D%95%E8%A1%8C%E5%87%BD%E6%95%B0/

聚合函数：https://new-wangz.github.io/2022/04/12/%E7%AC%AC08%E7%AB%A0-%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0/

MySQL查询格式

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
# 方式1（SQL92）：
SELECT ...... , ...... , ......
FROM ...... , ...... , ......
WHERE
	多表的连接条件 
	AND 不包含组函数的过滤条件 
GROUP BY ...... , ...... , ......
HAVING
	包含组函数的过滤条件 
ORDER BY ...... ASC / DESC 
	LIMIT ...... , ......
	
# 方式2（SQL99）：
SELECT ...... , ...... , ......
FROM ......
JOIN ...... ON 多表的连接条件
JOIN ...... ON 多表的连接条件
WHERE
	不包含组函数的过滤条件 
	AND / OR 不包含组函数的过滤条件 
GROUP BY ...... , ...... , ......
HAVING
	包含组函数的过滤条件 
ORDER BY ...... ASC / DESC 
	LIMIT ...... , ......

#其中：
#（1）from：从哪些表中筛选
#（2）on：关联多表查询时，去除笛卡尔积
#（3）where：从表中筛选的条件
#（4）group by：分组依据
#（5）having：在统计结果中再次筛选
#（6）order by：排序
#（7）limit：分页

GROUP BY 分组查询

MySQL SELECT 执行顺序

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 关键字的顺序
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...

# SELECT 语句的执行顺序 （在 MySQL 和 Oracle 中，SELECT 执行顺序基本相同）
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

# 案例
SELECT DISTINCT
	player_id, player_name, count(*) AS num # 顺序 5
FROM
	player 
JOIN team ON player.team_id = team.team_id # 顺序 1
WHERE height > 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num > 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7