SQL 基础

1
2
3
4
5
6

CREATE DATABASE IF NOT EXISTS `sql_study`
    DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

-- 语句结构
CREATE DATABASE [IF NOT EXISTS] `${数据库名}`
    DEFAULT CHARACTER SET ${字符集} COLLATE ${排序};

1
2
3
4

DROP DATABASE IF EXISTS `sql_study`;

-- 语句结构
DROP DATABASE [IF EXISTS] `${数据库名}`;

1

SHOW DATABAS;

1
2
3
4

USE `sql_study`;

-- 语句结构
USE `${数据库名}`;

1
2
3
4

SHOW CREATE DATABASE `sql_study`;

-- 语句结构
SHOW CREATE DATABASE `${数据库名}`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

CREATE TABLE IF NOT EXISTS `user_info` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '序列号',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int DEFAULT '0' COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT '用户表';

-- 语句结构
CREATE TABLE [IF NOT EXISTS] `${表名}` (
  `${字段名}` ${字段类型} [${字段属性}] [COMMENT '${字段备注}'],
  -- ...
  `${字段名}` ${字段类型} [${字段属性}] [COMMENT '${字段备注}'],
  PRIMARY KEY (`${字段名}`)
) ${引擎} [${表属性}] [DEFAULT CHARSET=${字符集} COLLATE=${排序规则}] [COMMENT '${表备注}'];

1
2
3
4

DROP TABLE IF EXISTS `user`;

-- 语句结构
DROP TABLE [IF EXISTS] `${表名}`;

1

SHOW TABLES;

1
2
3
4

DESC `user`;

-- 语句结构
DESC `${表名}`;

1
2
3
4

SHOW CREATE TABLE `user`;

-- 语句结构
SHOW CREATE TABLE `${表名}`;

1
2
3
4

ALTER TABLE `user` RENAME AS `user1`;

-- 语句结构
ALTER TABLE `${旧表名}` RENAME AS `${新表名}`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 添加字段
ALTER TABLE `user` ADD `score` INT DEFAULT '0' COMMENT '成绩';
-- 修改字段类型
ALTER TABLE `user` MODIFY `score` FLOAT DEFAULT '0' COMMENT '成绩';
-- 重命名字段
ALTER TABLE `user` CHANGE `score` `math_score` FLOAT DEFAULT '0' COMMENT '成绩';

-- 语句结构
-- 添加字段
ALTER TABLE `${表名}` ADD `${字段名}` ${字段类型} [${字段属性}] COMMENT '${字段备注}';
-- 修改字段类型
ALTER TABLE `${表名}` MODIFY `${字段名}` ${新字段类型} [${新字段属性}] COMMENT '${新字段备注}';
-- 重命名字段
ALTER TABLE `${表名}` CHANGE `${旧字段名}` `${新字段名}` ${新字段类型} [${新字段属性}] COMMENT '${新字段备注}';

1
2
3
4

ALTER TABLE `user` DROP `score`;

-- 语句结构
ALTER TABLE `${表名}` DROP `${字段名}`;

 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

-- 创建年级表
CREATE TABLE IF NOT EXISTS `grade` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '序列号',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '年级名',
  `comment` varchar(40) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT '年级表表';

-- 创建学生表
CREATE TABLE IF NOT EXISTS `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '序列号',
  `age` int DEFAULT '0' COMMENT '年龄',
  `name` varchar(20) NOT NULL DEFAULT '' COMMENT '姓名',
  `grade_id` int DEFAULT NULL COMMENT '年级id',
  PRIMARY KEY (`id`),
  -- 定义外键 Key 并将添加引用字段
  KEY `FK_grade_id` (`grade_id`),
  -- 创建外键约束，指定被引用表和字段名
  CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT '年级表表';

-- 语句结构
  -- 定义外键 Key 并将添加引用字段
  KEY `${FK_外键名}` (`${从字段名}`),
  -- 创建外键约束，指定被引用表和字段名
  CONSTRAINT `${FK_外键名}` FOREIGN KEY (`${从字段名}`) REFERENCES `${主表名}`(`${主字段名}`)

1
2
3
4

ALTER TABLE `student` ADD CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`id`);

-- 语句结构
ALTER TABLE `${从表名}` ADD CONSTRAINT `${FK_外键名}` FOREIGN KEY (`${从字段名}`) REFERENCES `${主表名}` (`${主字段名}`);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

-- 插入单条数据
INSERT INTO `grade` (`name`,`comment`) VALUE ('大一','第一年');

-- 插入多条数据
INSERT INTO `grade` (`name`,`comment`) VALUE ('大二','第二年'),('大三','第三年');

-- 语句结构
INSERT INTO `${表名}`
  (`${字段}`,`${字段}`)
  VALUE
  ('${值}','${值}'),
  ('${值}','${值}');

1
2
3
4
5
6
7
8
9

UPDATE `student` SET `name`='李四',`age`='22' WHERE `name`='李4';

-- 语句结构
UPDATE `${表名}`
  SET
  `${字段}`='${值}',
  ...
  `${字段}`='${值}'
  [WHERE ${条件}];

1
2
3
4

DELETE FROM `student` WHERE `id`='5';

-- 语句结构
DELETE FROM `${表名}` [WHERE ${条件}];

1
2
3
4
5

-- 清空数据表，结构与外键约束不变
TRUNCATE `test`;

-- 语句结构
TRUNCATE `${表名}`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

-- 查询所有字段和记录
SELECT * FROM `student`;
-- 查询指定字段和记录
SELECT `name`,`grade_id` FROM `student` WHERE `id`='2';
-- 使用字段别名
SELECT `name` AS '学生姓名',`grade_id` AS '年级id' FROM `student`;

-- 语句结构
SELECT
  `${字段名}` [AS '${字段别名}'],
  ...
  `${字段名}` [AS '${字段别名}']
  FROM `${表名}` [AS '${表别名}']
  [WHERE ${条件}];

1
2
3
4
5
6
7
8

-- 去重结果
SELECT DISTINCT `name` FROM `student`;
-- 查询函数结果
SELECT VERSION();
-- 查询计算结果
SELECT 10*2+5 AS '计算结果';
-- 查询系统变量
SELECT @@auto_increment_increment;

 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

SELECT ABS(-1);        -- 绝对值
SELECT CEILING(9.3);   -- 向上取整
SELECT FLOOR(9.3);     -- 向下取整
SELECT RAND();         -- 生成随机数
SELECT SIGN(1);        -- 判断符号

SELECT LENGTH('hello,word!');                 -- 计算字符串长度
SELECT INSERT('hello,word!',7,4,'neko');      -- 删除插入字符串
SELECT REPLACE('hello,word!','word','neko');  -- 替换字符串

SELECT UPPER('hello,word!');  -- 转为大写
SELECT LOWER('INSERT');       -- 转为小写

SELECT CURDATE();   -- 当前日期
SELECT NOW();       -- 当前时间
SELECT LOCALTIME(); -- 本地时间
SELECT SYSDATE();   -- 系统日期

SELECT USER();          -- 当前用户
SELECT VERSION();       -- 当前版本

-- 拼接字段
SELECT CONCAT('姓名：',`name`,'；年级id：',`grade_id`) AS 学生信息 FROM `student`;
-- 数据加密
INSERT INTO `testmd5`(`id`,`user`,`pwd`) VALUES(1,`neko`,MD5('123456'))

-- 函数语法
${函数名}(${参数})

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT COUNT(*) FROM `student`;       -- 统计所有的字段，不会忽略 BULL 值，慢
SELECT COUNT(1) FROM `student`;       -- 忽略所有的字段，用 1 代替，不会忽略 NULL 值，最快
SELECT COUNT(`name`) FROM `student`;  -- 值统计指定的字段，会忽略 NULL 值，快

SELECT SUM(age) AS '年龄总和' FROM `student`;  -- 求和
SELECT AVG(age) AS '年龄总和' FROM `student`;  -- 平均
SELECT MAX(age) AS '年龄总和' FROM `student`;  -- 最高
SELECT MIN(age) AS '年龄总和' FROM `student`;  -- 最低

-- 函数语法
${函数名}(${参数})

1
2
3
4
5
6

-- % 表示任意个字符
-- _ 表示一个字符
SELECT `name` FROM `student` WHERE `name` LIKE '王%';

-- 语句结构
WHERE `${字段名}` LIKE ${匹配字符}

1
2
3
4
5

-- IN 后面的合集应为一个具体的值
SELECT `name` FROM `student` WHERE `id` IN (1,3);

-- 语句结构
WHERE `${字段名}` IN ${集合}

1
2
3
4
5

-- 应考虑 NULL 和空字符串两种情况
SELECT `id`,`name` FROM `student` WHERE `name`='' OR `name` IS NULL;

-- 语句结构
WHERE `${字段名}`='' OR `${字段名}` IS NULL

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- 查询多个表的情况，应先从两个表的连接写起
SELECT s.`id` AS '学生id',s.`name` AS '学生姓名',g.`name` AS '年级'
FROM `student` AS s
INNER JOIN `grade` AS g
ON s.grade_id = g.id;

-- 语句结构
SELECT [${表名/表别名}.]`${字段名}` [AS '${字段别名}'],
  ...,
  [${表名/表别名}.]`${字段名}` [AS '${字段别名}']
FROM `${表名}` [[AS] ${表别名}]
${联表方式} JOIN `${表名}` [[AS] ${表别名}]
  ON [${表名/表别名}.]`${字段名}` = [${表名/表别名}.]`${字段名}`;
...
${联表方式} JOIN `${表名}` [[AS] ${表别名}]
  ON [${表名/表别名}.]`${字段名}` = [${表名/表别名}.]`${字段名}`;
[WHERE ${条件}]

1
2
3
4
5
6

create table if not exists `category`(
    `category_id` int auto_increment,
    `pid` int,
    `name` varchar(40),
    primary key (`category_id`)
);

1
2
3

SELECT p.`name` AS '父目录',s.`name` AS '子目录'
FROM `category` AS p,`category` AS s
WHERE p.`category_id`=s.`pid`;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT `id` AS '学生id',`name` AS '学生姓名'
FROM `student`
WHERE `grade_id` IN (
  SELECT `id` from `grade` WHERE `name` IN ('大二','大三')
  );

-- 语句结构
WHERE ${字段名} ${运算} (
  ${子查询}
)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT g.`name` AS '年级', avg(s.`age`) AS '平均年龄' FROM `student` AS s
INNER JOIN grade AS g
ON s.grade_id = g.id
GROUP BY s.grade_id
HAVING 平均年龄 >= 20;

-- 语句结构
SELECT ${聚合函数} FROM `${表名}`
GROUP BY ${字段名}
HAVING ${筛选聚合条件};

1
2
3
4
5
6
7
8

SELECT s.`id`,s.`name`,g.`name`
FROM `student` AS s
INNER JOIN `grade` AS g
ON s.grade_id=g.id
ORDER BY s.`id` DESC;

-- 语句结构
ORDER BY [${表名/表别名}.]`${字段名}` [${ASC/DESC}]

1
2
3
4
5
6
7
8
9

SELECT s.`id`,s.`name`,g.`name`
FROM `student` AS s
INNER JOIN `grade` AS g
ON s.grade_id=g.id
ORDER BY s.`id` DESC;
LiMIT 2,5

-- 语句结构
LiMIT ${起始位置},${查询条数}

1
2
3
4
5
6
7
8

SELECT [DISTINCT] [${表名/表别名}.]`${字段名}` [[AS] ${字段别名}][,...]
FROM `${表名}` [[AS] ${表别名}][,...]
[${联表方式} JOIN `${表名}` [[AS] ${表别名}] ON [${表名/表别名}.]`${字段名}`=[${表名/表别名}.]`${字段名}`]
[WHERE ${条件语句}]
[GROUP BY [${表名/表别名}.]`${字段名}` [HAVING ${筛选聚合条件}]]
[ORDER BY [${表名/表别名}.]`${字段名}` [ASC/SESC]]
[LiMIT ${起始记录数},${读取记录数}]
[UNION ${查询}]

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

CREATE USER 'neko' IDENTIFIED BY '123456';
SET PASSWORD FOR 'neko' = PASSWORD('123456');
RENAME USER 'neko' TO 'neko1';
DROP USER 'neko';

-- 语句结构
CREATE USER '${用户名}' IDENTIFIED BY '${密码}';
SET PASSWORD [FOR '${用户名}'] = PASSWORD('${密码}');
RENAME USER '${旧用户名}' TO '${新用户名}';
DROP USER '${用户名}';

1
2
3
4
5
6
7
8

GRANT ALL PRIVILEGES ON *.* TO 'neko';     -- 授予权限
SHOW GRANTS FOR 'neko';                    -- 查看权限
REVOKE ALL PRIVILEGES FROM *.* TO 'neko';  -- 撤销权限

-- 语句结构
GRANT ${权限列表} ON ${数据库名}.${表名} TO '${用户名}'[@'${主机}'];
SHOW GRANTS FOR ${用户名}[@'${主机}'];
REVOKE ${权限列表} ON ${数据库名}.${表名} FROM '${用户名}'[@'${主机}'];

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

-- mysql 默认开启事务并自动提交
SET autocommit = 0;  -- 关闭自动提交

START TRANSACTION;  -- 事务开始
[执行语句]
SAVEPOINT ${检查点名};  -- 生成检查点
[执行语句]
ROLLBACK TO SAVEPOINT ${检查点名};  -- 回滚到检查点
[执行语句]
RELEASE SAVEPOINT ${检查点名};  -- 释放检查点
[执行语句]

-- 事务只能以回滚或提交结束
COMMIT;    -- 提交，完成事务
ROLLBACK;  -- 回滚，撤销事务

SET autocommit = 1;  -- 打开自动提交