mysql基本语句

来源于B站视频，按照对应的章节显示了，如果有不明白的地方，可以直接跳转到B站观看
B站的game(database)中有一个player(TABLE), player中有7个属性id,name,sex,email,level,exp,gold
game中equip表的有id, name, player_id三个属性

0 预准备

0.1 navicat安装

先安装一下navicat，具体可以参考Navicat16安装和激活详细讲解（全网最简单且靠谱）。
更新的navicat版本navicat170_premium_cs_x64也可以使用
链接：https://pan.baidu.com/s/1D5Ehxa5SJIatMGc8O4Kw0A
提取码：yyds

0.2 mysql

按照B站视频安装mysql(windows)之后，需要将mysql中的bin添加到环境变量中，如果是默认安装在C盘下，那么需要添加在系统PATH中的为C:\Program Files\MySQL\MySQL Server 8.0\bin。如果不将其添加在环境变量中，那么在终端cmd调用类似于mysqldump -u root -p game > game.sql或者mysql -u root -p game < game.sql是无法成功的

0.3 进入mysql开发环境

下面有三种开发环境介绍，在上面的B站视频链接都有介绍

• 在将mysql添加到环境变量中后，可以直接在cmd终端输入mysql -u root -p，会提示输入密码，然后就进入了mysql开发环境中
• 可以使用mysql-shell，在cmd终端输入mysqlsh就进入mysql开发环境了，mysql-shell总共有JS, python, mysql三种语言方式，想要使用某种语言，可以直接输入\sql或者\py，也可以使用VScode扩展(详细使用参考B站06.MySQL-Shell的使用)
• 可以使用navicat开发工具(详细使用参考B站08.创建数据库)

1 创建数据库 创建表

1.1 创建数据库

SHOW DATABASES;  -- 展示数据库
CREATE DATABASE game;  -- 创建game数据库
DROP DATABASE game;  -- 删除game数据库

1.2 创建表

use game;  -- 选择数据库

-- 创建表
CREATE TABLE player(
  id INT,
  name VARCHAR(100),
  level INT,
  exp INT,
  gold DECIMAL(10, 2)
)

DESC player;  -- 查看表的结构
ALTER TABLE player MODIFY COLUMN name VARCHAR(200);  -- 修改表的结构, COLUMN表示修改的列
ALTER TABLE player RENAME COLUMN name to nick_name;  -- 将表中name修改为nick_name
ALTER TABLE player ADD COLUMN last_login DATETIME;  -- 在表中添加一个新的字段
ALTER TABLE player DROP COLUMN last_login;  -- 删除一个字段

DROP TABLE player;  -- 删除整个表

2 数据的增删改查 数据的导入导出

2.1 数据的增删改查

INSERT INTO player (id, name, level) VALUES (1, "张三", 1);  -- 插入数据
INSERT INTO player (id, name) VALUES (2, "李四"), (3, "王五");  -- 插入多条数据
ALTER TABLE player MODIFY COLUMN LEVEL INT DEFAULT 1;  -- 修改表的属性，将player表中的level都默认设置为1
UPDATE player SET level = 1 WHERE name = "李四";  -- 将“李四”的level修改为1
UPDATE player SET level = 1 WHERE name = "王五";  -- 将“王五”的level修改为1
UPDATE player SET exp=0, gold=0;  -- 将player中所有exp, gold都修改为0
DELETE FROM player where gold = 0;  -- 删除player中所有gold=0的数据

2.2 数据的导入导出

在终端cmd中操作，都需要先配置一下环境变量

mysqldump -u root -p game > game.sql; -- 导出数据库game中的所有数据，存放在game.sql
mysqldump -u root -p game player > game.sql;  -- 导出数据库game中表player的所有数据，存放在game.sql

mysql -u root -p game < game.sql;  -- 导入数据

也可以使用navicat图形化界面进行导入导出

3 常用语句

3.1 WHERE

SELECT * FROM player WHERE level > 1;  -- 查找所有level>1的玩家
SELECT * FROM player WHERE level > 1 AND level < 5;  -- 查找所有level>1并且level<5的玩家
SELECT * FROM player WHERE exp > 1 AND exp < 5;  -- 查找所有exp>1并且exp<5的玩家
-- 优先级顺序 NOT>AND>OR
SELECT * FROM player WHERE exp > 1 AND exp < 5 OR level > 1 AND level < 5; -- 查找所有level>1并且level<5或者exp>1并且exp<5的玩家
SELECT * FROM player WHERE exp > 1 AND (exp < 5 OR level > 1) AND level < 5; -- ()可以改变优先级

3.2 IN: 指定多个值

SELECT * FROM player WHERE level IN (1, 3, 5);  -- 查找level为1, 3, 5的玩家

3.3 BETWEEN … AND …

SELECT * FROM player WHERE level BETWEEN 1 AND 10;  -- 查找level在1-10之间的玩家

3.4 NOT: 取反

SELECT * FROM player WHERE level NOT BETWEEN 1 AND 10;  -- 查找level不在1-10之间的玩家

3.5 LIKE: 模糊查询

LIKE 通配符
% 任意个字符
_ 任意一个字符

SELECT * FROM player WHERE name LIKE '王%';  -- 查找姓王的玩家 name中第一个字是王, 后面可以是任意个字符
SELECT * FROM player WHERE name LIKE "%王%";  -- 查找name中包含王的玩家
SELECT * FROM player WHERE name LIKE "王_";  -- 查找name中姓王且只有两个字的玩家
SELECT * FROM player WHERE name LIKE "王__";  -- 查找name中姓王且有三个字的玩家

3.6 REGEXP: 正则表达式

常用通配符
.  任意一个字符
^  开头  
$  结尾
[abc]  其中任意一个字符
[a-z]  范围内的任意一个字符
A|B  A或者B

SELECT * FROM player WHERE name REGEXP "^王.$";  -- 查找name中姓王且只有两个字的玩家
SELECT * FROM player WHERE name REGEXP "王";  -- 查找名字中包含王的玩家  不能加%或者_, 正则表达式中没有这两个通配符
SELECT * FROM player WHERE name REGEXP "[王张]";  -- 查找name中包含王或者张任意一个字符的玩家
SELECT * FROM player WHERE name REGEXP "王|张";  -- 查找name中包含王或者张任意一个字符的玩家

-- 查找email以zhangsan开头的玩家
SELECT * FROM player WHERE email like "zhangsan%";
SELECT * FROM player WHERE email REGEXP "^zhangsan";

-- 查找email以a/b/c开头的玩家
SELECT * FROM player WHERE email REGEXP "^[abc]";
SELECT * FROM player WHERE email REGEXP "^[a-c]";

-- 查找email以net结尾的玩家
SELECT * FROM player WHERE email like "%net";
SELECT * FROM player WHERE email REGEXP "net$";

3.7 NULL: 空值

NULL表示没有填写值，''表示填写了一个空的字符串，二者是不对等的

-- 查找email为NULL的玩家 (查找没有填写email的玩家)
SELECT * FROM player WHERE email IS NULL;  
SELECT * FROM player WHERE email <=> NULL;
-- 查找email为'' 或者 NULL的玩家
SELECT * FROM player WHERE email = '' or email IS NULL;
-- 查找email不为Null的玩家
SELECT * FROM player WHERE email IS NOT NULL;

-- 不要使用=查找NULL
SELECT * FROM player WHERE email = NULL;  -- 不要使用

3.8 ORDER BY: 排序

不指定默认升序排序

SELECT * FROM player ORDER BY level;  -- 查找按照level升序排序
SELECT * FROM player ORDER BY level DESC;  -- 查找按照level降序排序
SELECT * FROM player ORDER BY level DESC, exp;  -- 查找level降序，exp升序的玩家
SELECT * FROM player ORDER BY level DESC, exp ASC;  -- 查找level降序，exp升序的玩家
SELECT * FROM player ORDER BY 5 DESC; -- 查找第5列(level)降序排列的玩家

3.9 聚合函数

常用聚合函数
AVG(): 返回集合的平均值
COUNT(): 返回集合中的项目数
MAX(): 返回最大值
MIN(): 返回最小值
SUM(): 求和

SELECT COUNT(*) FROM player;    -- COUNT: 返回集合中的项目数
SELECT AVG(level) FROM player;  -- AVG: 返回level的平均值  
SELECT MAX(level) FROM player; 	-- MAX: 返回level最大值 
SELECT MIN(level) FROM player;  -- MIN: 返回level最小值
SELECT SUM(level) FROM player;  -- SUM: level求和

3.10 GROUP BY: 分组

SELECT sex, COUNT(*) FROM player GROUP BY sex;  -- 按照sex分组后，计算每个组的数量
SELECT level, COUNT(level) FROM player GROUP BY level;  -- 查找每一个level的玩家有多少名

3.11 HAVING: 筛选分组后的数据

SELECT level, COUNT(level) FROM player GROUP BY level HAVING COUNT(level) > 4;  -- 只保留COUNT(level)>4的玩家分组 

-- 在分组后，只保留COUNT(level)>4的分组基础上，进行降序排列
SELECT level, COUNT(level) FROM player GROUP BY level HAVING COUNT(level) > 4 ORDER BY COUNT(level) DESC; 

3.12 SUBSTR: 截取字符串

SUBSTR(name, a, b) -- name: 截取的字符串, a: 截取的开始位置, b: 截取的长度

-- 注意这只有一个分号，是一个命令
SELECT SUBSTR(name, 1, 1), COUNT(SUBSTR(name, 1, 1)) FROM player  -- 截取姓氏，并计数
GROUP BY SUBSTR(name, 1, 1)  -- 按照姓氏分组
HAVING COUNT(SUBSTR(name, 1, 1)) >= 5 -- 选择数量大于5的姓氏
ORDER BY COUNT(SUBSTR(name, 1, 1)) DESC;  -- 按照降序排列

3.13 LIMIT: 限制数量

LIMIT a, b -- a:是选择的数量，b是偏移量

SELECT SUBSTR(name, 1, 1), COUNT(SUBSTR(name, 1, 1)) FROM player  -- 截取姓氏，并计数
GROUP BY SUBSTR(name, 1, 1)  -- 按照姓氏分组
ORDER BY COUNT(SUBSTR(name, 1, 1)) DESC  -- 按照降序排列
LIMIT 3;  -- 返回3个，偏移量为0，返回1、2、3名

SELECT SUBSTR(name, 1, 1), COUNT(SUBSTR(name, 1, 1)) FROM player  -- 截取姓氏，并计数
GROUP BY SUBSTR(name, 1, 1)  -- 按照姓氏分组
ORDER BY COUNT(SUBSTR(name, 1, 1)) DESC  -- 按照降序排列
LIMIT 3, 3;  -- 返回3个，偏移量为3，返回4、5、6名

3.14 DISTINCT: 去重

SELECT DISTINCT sex FROM player;  -- 去除重复的， 得到所有玩家的性别，

3.15 UNION: 合并查询结果(并集)

-- 将所有等级为1-3的玩家和所有经验为1-3的玩家合并起来(默认会自动去除重复的记录)
SELECT * FROM player WHERE level BETWEEN 1 AND 3  -- 所有等级为1-3的玩家
UNION  
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;  -- 所有经验为1-3的玩家

-- 将所有等级为1-3的玩家和所有经验为1-3的玩家合并起来(默认不会自动去除重复的记录)
SELECT * FROM player WHERE level BETWEEN 1 AND 3  -- 所有等级为1-3的玩家
UNION ALL 
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;  -- 所有经验为1-3的玩家

3.16 INTERSECT: 合并结果集(交集)

-- 保留所有等级为1-3的玩家和所有经验为1-3的玩家之间的交集
SELECT * FROM player WHERE level BETWEEN 1 AND 3  -- 所有等级为1-3的玩家
INTERSECT  
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;  -- 所有经验为1-3的玩家

3.17 EXCEPT: 合并结果集(差集)

-- 查找等级为1-3的玩家，但是经验不在1-3之间的玩家
SELECT * FROM player WHERE level BETWEEN 1 AND 3  -- 所有等级为1-3的玩家
EXCEPT  
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;  -- 所有经验为1-3的玩家

4 子查询 表关联 索引 视图

4.1 子查询

WHERE中使用

SELECT * FROM player WHERE level > (SELECT AVG(level) FROM player);  -- 查询level > AVG(level)的玩家

SELECT中使用

-- 查询的数据中, 第一列是level, 第二列是AVG(level), 第三列是 level - AVG(level)
-- 注意必须使用()包含SELECT AVG(level) FROM player, 不能不要这个()
SELECT level, (SELECT AVG(level) FROM player), level - (SELECT AVG(level) FROM player) FROM player;  

-- AVG(level)得到的是浮点数, ROUND()是将浮点数转换为整数
SELECT level, ROUND((SELECT AVG(level) FROM player)), level - ROUND((SELECT AVG(level) FROM player)) FROM player;

-- 为了查询结果有更好的阅读性,给第2列起别名average, 第3列起别名diff
SELECT level, ROUND((SELECT AVG(level) FROM player)) AS average, 
level - ROUND((SELECT AVG(level) FROM player)) AS diff 
FROM player;

还可以在CREATE, DELETE, UPDATE, INSERT等语句中使用

CREATE中使用

-- 将表player中所有level<5的数据添加到新创建的表new_player
CREATE TABLE new_player SELECT * FROM player WHERE level < 5;  

INSERT中使用

-- 将表player中所有6<=level<=10的数据添加到表new_player
INSERT INTO new_player SELECT * FROM player WHERE level BETWEEN 6 AND 10;  

EXISTS: 判断一个查询是否有结果，反馈值只有0或1

SELECT EXISTS(SELECT * FROM player WHERE level > 100);  -- 是否有level>100的玩家，反馈值为0
SELECT EXISTS(SELECT * FROM player WHERE level > 10);  -- 是否有level>10的玩家，反馈值为1

4.2 表关联

INNER JOIN  -- 内连接  只返回两个表中都有的数据
LEFT JOIN  -- 左连接  返回左表中所有的数据和右表中匹配的数据, 右表中没有的数据用NULL填充
RIGHT JOIN  -- 右连接  返回右表中所有的数据和左表中匹配的数据, 左表中没有的数据用NULL填充

使用简介，不理解可以直接看B站视频

-- 内连接
SELECT * FROM player
INNER JOIN equip
ON player.id = equip.player_id;
-- 内连接可以使用WHERE达到相同的效果
SELECT * FROM player, equip
WHERE player.id = equip.player_id;
-- 也可以用别名代替表名
SELECT * FROM player AS p, equip AS e
WHERE p.id = e.player_id;

-- 左连接
SELECT * FROM player
LEFT JOIN equip
ON player.id = equip.player_id;

-- 右连接
SELECT * FROM player
RIGHT JOIN equip
ON player.id = equip.player_id;

4.3 索引

-- CREATE INDEX 是创建索引的关键字  INDEX 前面加上可选的索引类型
-- UNIQUE 唯一索引  FULLTEXT 全文索引  SPATIAL 空间索引
-- index_name 索引的名称
-- tbl_name 表的名称
-- (index_col_name, ...) 一个或多个字段名, 对这些字段创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name  
  ON tbl_name (index_col_name, ...)

CREATE INDEX email_index ON player (email);  -- 创建email_index索引
SHOW INDEX FROM player;  -- 展示player中的索引
SELECT * FROM player WHERE email LIKE "a%" ORDER BY id;
DROP INDEX email_index ON player;  -- 删除索引

除了使用CREATE INDEX创建索引之外，还可以在修改表的结构的时候创建索引

ALTER TABLE player ADD INDEX name_index (name);

4.4 视图

4.4.1 CREATE VIEW: 创建视图

CREATE VIEW top10
AS
SELECT * FROM player ORDER BY level DESC LIMIT 10;  -- 创建top10为player中level降序前10名的视图

视图的使用和表是一样的，可以使用正常的语句查询视图，当表中的数据发生变化的时候，视图中的数据也会随着发生变化。

SELECT * FROM top10;
-- 视图中的数据是动态的, 会随着表的数据变化而变化
UPDATE player SET level = 10 WHERE id = 57;  -- 修改player中的level(id=57是top10中的第一名)
-- player中level修改之后, 在top10中id=57就不会出现了, 因为已经不是player中的前10了

4.4.2 ALTER VIEW: 修改视图

ALTER VIEW top10
AS
SELECT * FROM player ORDER BY level LIMIT 10;  -- 修改top10为player中level升序前10名的视图

4.4.3 DROP VIEW: 删除视图

DROP VIEW top10;  -- 删除视图