【冷启动#1】实用的MySQL基础
简单安装一下MySQL
Windows下（5.7.x）
本体安装
1、首先先下载安装包，名字如下：
mysql-5.7.19-winx64.zip
2、配置环境变量，将解压之后的bin目录添加一下
3、在解压目录下创建my.ini文件，内容如下：
[ client ]
port=3306
default-character-set=utf8
[ mysqld ]
#设为自己MYSQL的安装目录
basedir=D:\zpg\MySQL\mysql-5.7.19-winx64
#设置为MYSQL的数据目录
datadir=D:\zpg\MySQL\mysql-5.7.19-winx64\data
port-3306
character_set_server=utf8
#跳过安全检查
#skip-grant-tables #如果不注释，进入MySQL时就不用输入密码
4、使用管理员权限打开cmd，安装mysql
切换到解压目录的bin目录下
cd D:\...\MySQL\mysql-5.7.19-winx64\bin
安装
mysqld -install
mysqld --initialize-insecure --user=mysql #创建data目录
启动MySQL服务
net start mysql
登录（此时没有密码，直接回车即可）
mysql -u root -p
5、修改MySQL密码
登录MySQL后会出现mysql的操作终端，输入以下指令
use mysql;
update user set authentication_string=password('ag') where user='root' and Host='localhost';
修改完成，此时如果在ini中注释了skip-grant-tables，那么下次登录就必须输入正确的密码
命令行连接
mysql -h 127.0.0.1 -P 3306 -u root -pag
Linux下（8.0）
5.79的安装教程：https://blog.csdn.net/qq_39724355/article/details/131332473
以下是8.0的（即Ubuntu20.04默认支持的版本）
apt install -y mysql-server-8.0
ps：
- 下载有问题记得换下源
- 不加y有可能会在安装过程中要求设置密码
新建一个数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
mysql> creat database game;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creat database game' at line 1
mysql> create database game;
Query OK, 1 row affected (0.00 sec)
mysql>
在Linux的终端下进入MySQL-shell然后操作，别打错单词
GUI安装
Navicat安装（收费）
教程：https://www.cnblogs.com/FRIM/p/16978145.html
SQLyog安装（免费）
滚吧mtfk
卸载MySQL
如果你不幸安装了错误版本的MySQL，请使用以下方法将其卸载（生产环境别这样干）（ref）
dpkg --list|grep mysql # 查看MySQL依赖
sudo apt-get remove mysql-common # 卸载
# 版本对应即可
sudo apt-get autoremove --purge mysql-server-8.0
# 清除残留数据
dpkg -l|grep ^rc|awk '{print$2}'|sudo xargs dpkg -P
dpkg --list|grep mysql # 这里一般就没有输出了，如果有执行下一步
# 继续删除剩余依赖项
sudo apt-get autoremove --purge mysql-apt-config
远程连接MySQL
使用Navicat等数据库管理工具连接的话，要进行以下设置
设置root密码
root账号默认没有密码（前面可知，回车即可进入mysql-shell）
将root账号密码设置为"root"，还是在user表中操作
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '102030';
注意，MySQL 8.0中不再使用
authentication_string字段，而是使用plugin和authentication_string字段进行身份验证。因此以下语句错误update user set authentication string =password('root') where user = 'root';
状态查看与配置文件位置
首先，你可以查看MySQL服务的状态
service mysql status
service mysql start / stop / restart
systemctl enable mysql 开机自启
要远程连接需要编辑MySQL的配置文件，可能位于以下常见位置
【Linux】
• /etc/my.cnf
• /etc/mysql/my.cnf
• /etc/mysql/debian.cnf【Ubuntu上看密码】
• /etc/mysql/mysql.conf.d/mysqld.cnf【Ubuntu上改IP配置】
【Windows】
• C:\ProgramData\MySQL\MySQL Server X.X\my.ini
• C:\Program Files\MySQL\MySQL Server X.X\my.ini
【Mac】
• /opt/homebrew/etc/my.cnf
以Ubuntu为例，找到mysqld.cnf文件后，修改其中的bind-addres参数为0.0.0.0（或者直接注释掉）
sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# 修改 bind-address
bind-address = 0.0.0.0
然后重启MySQL服务
修改登录限制
首先进入MySQL-Shell
mysql -u root -p
默认情况下没密码，回车就行
用show databases;查看当前存在的表，use mysql切换到"mysql表"进行后续操作
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| game |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql |
+------------------------------------------------------+
| columns_priv |
...
| user |
+------------------------------------------------------+
37 rows in set (0.01 sec)
mysql>
查找出表中用户名为"root"的内容
mysql> select host, user from user where user='root';
+-----------+------+
| host | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)
mysql>
要允许任何IP登录，需要将"host"的属性值更改成'%'
mysql> update user set host='%'where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
刷新一下flush privileges;
CURD快速实践
关键字说明
MySQL中的关键字分别负责不同的功能需求，可以分为以下几类（ref）：
创建一个数据库以及修改表结构
可以在sql-shell里面直接用SHOW DATABASES XX;来创建数据库，之后在Navicat中就可以查看得到（也可以在Navicat中直接创建）
这里创建了一个game数据库
选择数据库game（use game;），就可以在game中创建要使用的表了
创建一个玩家表player用来保存玩家信息
CREATE TABLE player(
id INT,
name VARCHAR(100), # 长度为100的可变字符串
leveal INT,
exp INT,
gold DECIMAL(10,2) # 长度为10并保留两位小数的十进制数
)
使用DESC player;来查看表结构（DESC是"描述"的缩写）
创建完表发现有问题怎么办？
使用ALTER关键字来修改表结构
例如，修改玩家名称的长度限制，使用ALTER修改（MODIFY）表player中name列（COLUMN）的数据类型
ALTER TABLE player MODIFY COLUMN name VARCHAR(200);
想修改列的名称也可以，比如把name改为nick_name
ALTER TABLE player RENAME COLUMN name TO nick_name;
或者添加新的列也可以，比如添加玩家最后的登录时间
ALTER TABLE player ADD COLUMN final_login DATETIME;
ALTER TABLE player DROP COLUMN final_login; # 删掉
删除整个表的话->DROP TABLE player;
表中数据的CURD
插入数据
用INSERT来插入数据，player后面可以不写列名，这样就会按照默认顺序插入
INSERT INTO player(id, name, leveal, exp, gold) VALUES (1, 'ag', 1, 1, 1);
# 插入多条
INSERT INTO player(id, name) VALUES (2, 'xixi'), (3, 'coco');
# 指定leveal字段的默认值为1
ALTER TABLE player MODIFY COLUMN leveal INT DEFAULT 1;
INSERT INTO player(id, name) VALUES (1, 'tutu');
插完用SELECT查询一下
SELECT * FROM player;
修改数据
用UPDATE配合SET修改已有列字段中的数据
UPDATE player SET exp = 20 WHERE name = 'ag';
# 当然，不加where条件就可以修改所有数据
UPDATE player SET leveal = 1, gold = 0, exp = 1;
实际中，在UPDATE或者DELETE时不加条件十分危险
删除数据
DELETE FROM player WHERE exp = 1;
数据的导入导出
导出
在终端中使用mysqldump来导出某个数据库
mysqldump -u root -p xxx game > game.sql
将game数据库导出到game.sql（是一条条的sql语句）
导入
假设你刚刚安装好MySQL
此时需要创建一个数据库game
create database game;
然后退出MySQL的命令行界面，在终端中执行如下命令来导入数据：
mysql -u root -p game < game.sql
数据源：https://github.com/geekhournet/mysql-course
常用的语句
IN:指定多个值
在使用查询关键字SELECT时，可以通过WHERE关键字限定查询范围，AND\OR\NOT可以处理一些逻辑条件，他们的优先级为：NOT>AND>OR
例如，查找等级大于1小于5或者经验值大于1小于5的玩家
SELECT * FROM player WHERE level > 1 AND level < 5 OR exp > 1 AND exp < 5;
(Ps:括号可以改变优先级顺序)
要查找多个不同等级的玩家，就可以使用IN实现
例如，查找等级为1、3、5的玩家
SELECT * FROM player WHERE level IN (1,3,5);
(通常与子查询连用)
BETWEEN...AND:范围查询
这个很好理解，例如，查询等级1到10的玩家
SELECT * FROM player WHERE level BETWEEN 1 AND 10;
加上NOT可以取反，例如等级不在1到10之间的玩家
SELECT * FROM player WHERE level NOT BETWEEN 1 AND 10;NOT可以加在任何条件语句之前
LIKE:模糊查询
实际上就是对于通配符和正则的一些使用
例如，查找名字里面有“王”的玩家
SELECT * FROM player WHERE name LIKE '王%'; # 姓王的玩家
SELECT * FROM player WHERE name LIKE '%王%'; # 包含王字的玩家
SELECT * FROM player WHERE name LIKE '王_'; # 姓王且名字只有两个字的玩家
在通配符中，‘%’表示匹配之后任意个字符，‘_’表示匹配一个字符【正则中没有这两个】
借助正则表达式进行查询
常用通配符：
'.' -- 任意一个字符；
'^' -- 开头； '$' -- 结尾； 'A|B' -- A或B；
'[abc]' -- 其中任意一个字符； '[a-z]' -- 范围内的任意一个字符（0-9也行）;
还是查找王姓的两个字的玩家，用正则表达式可以这么写：
SELECT * FROM player WHERE name REGEXP '^王.$'; # ^表示开始位置，$表示结束位置，.是匹配任意字符
SELECT * FROM player WHERE name REGEXP '王'; # 查询包含王字的玩家
如果要查询包含王或者张的玩家，可以使用'[]'，其会匹配中括号中的任意字符
SELECT * FROM player WHERE name REGEXP '[王张]'; # 查询包含王或张字的玩家
SELECT * FROM player WHERE name REGEXP '王|张';
练习
1、查找邮件地址以zhangsan开头的玩家
SELECT * FROM player WHERE email REGEXP 'zhangsan';
2、查找邮件地址以a\b\c开头的玩家
SELECT * FROM player WHERE email REGEXP '^[abc]';
SELECT * FROM player WHERE email REGEXP '^[a-c]';
3、查找邮件地址以net结尾的玩家
SELECT * FROM player WHERE email REGEXP 'net$';
SELECT * FROM player WHERE email LIKE '%net';
NULL:空值
在查找空值数据的时候有一个需要注意的地方，即不能使用'='作为判断条件
需要使用"IS NULL"来做，例如，查找没有填邮箱的玩家
SELECT * FROM player WHERE email IS NULL; # 邮箱为null值
SELECT * FROM player WHERE email IS NULL OR email = ''; # 邮箱为null或空值（空字符串）
ORDER BY:排序
为查询数据进行排序，不指定顺序默认升序
例如，等级从小到大排序
SELECT * FROM player ORDER BY level; # 升序
SELECT * FROM player ORDER BY 5 DESC; # 或者使用列的序号也可以
SELECT * FROM player ORDER BY level DESC; # 降序
对多个列排序
追加列名就行
SELECT * FROM player ORDER BY level DESC, exp; #等级降序， 经验升序
聚合函数
常用聚合函数：
AVG() -- 返回集合的平均值； COUNT() -- 返回集合中的项目数量；
MAX() -- 返回最大值； MIN() -- 返回最小值；SUM() -- 求和;
例如，求玩家总数
SELECT COUNT(*) FROM player;
SELECT AVG(level) FROM player;
分组查询
GROUP BY由于将查询后的结果进行分组，在关键字后面接上列名即可将指定列框定为一个“组”（group）
SELECT * FROM player GROUP BY level; # 按等级分组
与复合函数连用可以对分组后的数据进行计算处理
SELECT level, COUNT(level) FROM player GROUP BY level; # 统计每个等级的玩家数量
与HAVING配合
GROUP BY常与HAVING连用，可对分组后的数据进行过滤筛选
例如，想知道等级大于4的玩家
SELECT level, COUNT(level) FROM player GROUP BY level HAVING COUNT(level) > 4; #
与ORDER BY配合
GROUP BY与ORDER BY连用通常原来为结果排序
SELECT level, COUNT(level) FROM player GROUP BY level HAVING COUNT(level) > 4 ORDER BY COUNT(level) DESC; # 此处DESC表示降序
练习
统计每个姓氏玩家的数量，并将结果安装数量来降序排序，只显示数量大于等于5的姓氏
下意识会这么写：
SELECT name COUNT(name) FROM player GROUP BY name HAVING COUNT(name) > 5 ORDER BY COUNT(name) DESC;
但是不行，因为name是字符串，不能直接进行计数
并且我们需要统计的是姓氏，而不是整个名字出现的次数，显然需要对字符串进行截取
这里需要使用一个函数：SUBSTR（跟c++一样™的）用于截取字符串，思路如下：
SELECT SUBSTR(name, 1, 1), COUNT(SUBSTR(name, 1, 1)) FROM player # 首先截取出姓氏并统计出现次数
GROUP BY SUBSTR(name, 1, 1) # 对截取出来的姓氏列进行分组
HAVING COUNT(SUBSTR(name, 1, 1)) >= 6 # 筛选出出现次数大于等于5次的姓氏
ORDER BY COUNT(SUBSTR(name, 1, 1)) DESC # 降序排序
LIMIT 3 # 如果只想返回三条结果
LIMIT 3,3 # 第一个3是偏移量，表示从第四名开始，第二个是返回数量，也就是第四名后三个
# ↑即分页查询的原理
DISTINCT:查询结构去重
SELECT DISTINCT sex FROM player;
UNION:合并查询结果（并集）
查询所有等级为1-4以及经验为1-3的玩家
SELECT * FROM player WHERE level BETWEEN 1 AND 3
UNION # UNION会默认去重，UNION ALL不会去重
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;
tips：
- 这种合并方式是全外连接，也就是将表连接起来
- UNION与OR有点类似，但OR合并的是两个条件，而UNION合并的是两个查询结果
- 注意，连接的两个语句不要写分号不然会有问题（非要写可以在最后写一个）
INTERSECT:合并结果集（交集）
INTERSECT用于查找两个结果的交集
SELECT * FROM player WHERE level BETWEEN 1 AND 3
INTERSECT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3
EXCEPT:合并结果集（差集）
查找等级为1-3的但是经验不在1-3之间的玩家
SELECT * FROM player WHERE level BETWEEN 1 AND 3
EXCEPT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3
子查询
如果想使用一个查询的结果作为另一个查询的条件，可以通过子查询实现
例如，先使用AVG求出所有玩家的平均等级，然后再查出大于平均等级的玩家
SELECT AVG(level) FROM player;
SELECT * FROM player WHERE level > (SELECT AVG(level) FROM player);
又例如，想查询所有玩家等级与平均等级的差值
SELECT level, ROUND((SELECT AVG(level) FROM player)) FROM player; # ROUND可以将浮点数取整
SELECT level, ROUND((SELECT AVG(level) FROM player)), level - ROUND((SELECT AVG(level) FROM player)) FROM player; # 作差即可
但是这样写又臭又长，可以用AS给列起个别名看起来方便一些
SELECT level, ROUND((SELECT AVG(level) FROM player)) AS average,
level - ROUND((SELECT AVG(level) FROM player)) AS diff
FROM player;
还可以用子查询来创建新的表
又又例如，我们想讲等级小于5的玩家先查询出来然后单独拎到一个表中保存
SELECT * FROM player WHERE level < 5 # 先查询
CREATE TABLE new_player SELECT * FROM player WHERE level < 5 # 使用子查询建表
SELECT * FROM new_player # 查询新表
插入等级在6-10之间的玩家到新表中
SELECT * FROM player WHERE level BETWEEN 6 AND 10; # 还是先查询
#使用INSERT INTO按子查询结果插入数据到新表
INSERT INTO new_player SELECT * FROM player WHERE level BETWEEN 6 AND 10;
又又又例如，你想查询是否存在等级大于100的玩家，可以使用EXISTS配合子查询来实现
SELECT EXISTS(SELECT * FROM player WHERE level > 100)
表关联
表关联用于查询多个表中的数据，关联的表中需要含有相同字段
一般通过表的主键和外键来关联（概念理解详见）
以game数据库为例，里面除了玩家以外，还有装备数据。有时候我们希望将玩家与装备信息关联起来进行查询
DESC equip # 先查看一下equip表的结构
SELECT * FROM player # 查询player表中的所有项
INNER JOIN equip # 将player表和equip表关接起来
ON player.id = equip.player_id # 指定关联的字段（即两个表通过什么条件进行关联）
具体左右连接的区别，见详情
表连接的本质就是笛卡尔积+过滤条件，所以如果没有正确使用条件的话会导致数据异常（会有一些奇怪的组合）
