【冷启动#1】实用的MySQL基础

mysql · 浏览次数 : 0

小编点评

简单安装MySQL（Windows下，5.7.x版本）的步骤如下： 1. 下载安装包：访问MySQL官方网站（https://dev.mysql.com/downloads/mysql/5.7.html），选择Windows平台的安装包，并下载“mysql-5.7.19-winx64.zip”。 2. 配置环境变量：将解压后的MySQL bin目录添加到系统环境变量PATH中。例如，将D:\zpg\MySQL\mysql-5.7.19-winx64\bin添加到PATH。 3. 创建my.ini文件：在解压目录下创建一个名为my.ini的文件，文件内容如下： ``` [client] port=3306 default-character-set=utf8 [mysqld] basedir=D:\zpg\MySQL\mysql-5.7.19-winx64 datadir=D:\zpg\MySQL\mysql-5.7.19-winx64\dataport-3306 character_set_server=utf8 skip-grant-tables ``` 4. 安装MySQL：在cmd中使用管理员权限运行以下命令： ``` cd D:\zpg\MySQL\mysql-5.7.19-winx64\bin mysqld -install mysqld --initialize-insecure --user=mysql net start mysql ``` 5. 登录MySQL并修改密码：使用以下命令登录MySQL： ``` mysql -u root -p ``` 输入密码后，会提示输入密码。成功登录后，使用以下命令更新root密码： ``` update user set authentication_string=password('your_new_password') where user='root' and Host='localhost'; ``` 6. 修改MySQL配置文件：编辑MySQL的配置文件，将其bind-address参数设置为0.0.0.0以允许远程连接。在Ubuntu上，配置文件可能位于`/etc/mysql/mysql.conf.d/mysqld.cnf`或`/etc/mysql/my.cnf`。在Windows上，配置文件可能位于`C:\ProgramData\MySQL\MySQL Server X.X\my.ini`或`C:\Program Files\MySQL\MySQL Server X.X\my.ini`。 7. 修改用户权限：更新MySQL中的用户权限，允许root用户从任何IP地址登录。在MySQL shell中执行以下命令： ``` update user set host='%' where user='root' and host='localhost'; ``` 8. 创建数据库和表：使用以下命令创建一个名为game的数据库： ``` create database game; ``` 9. 导入和导出数据：使用MySQL命令行工具或MySQL Workbench等工具进行数据的导入和导出。完成上述步骤后，您应该已经成功安装并配置了MySQL 5.7.x版本。

正文

简单安装一下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

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 # 指定关联的字段（即两个表通过什么条件进行关联）

具体左右连接的区别，见详情

表连接的本质就是笛卡尔积+过滤条件，所以如果没有正确使用条件的话会导致数据异常（会有一些奇怪的组合）