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; -- 删除视图