MySQL入门

发表于 更新于 分类于

安装MySQL

在Ubuntu中使用apt安装MySQL Server，如果是CentOS，将apt替换为yum。 

1
sudo apt install mysql-server

以前安装过程中需要设置MySQL root用户密码，现在Ubuntu 18.04和CentOS 8安装MySQL都不需要设置root的密码了。安装完MySQL后，运行mysql_secure_installation，设置root密码和修改一些安全配置。

1
sudo mysql_secure_installation

启动、重启、查看、登录MySQL

启动MySQL

1
systemctl start mysqld

重启MySQL

1
systemctl restart mysqld

查看MySQL状态

1
systemctl status mysql.service

登录MySQL

1
mysql -u root -p

MySQL基础

MySQL语句约定使用大写，其实它是大小写不敏感的，语句以分号;结尾。
查看MySQL版本

1
SELECT VERSION();
1
2
3
4
5
6
+-----------+
| VERSION() |
+-----------+
| 8.0.17    |
+-----------+
1 row in set (0.01 sec)

查看数据库

1
SHOW DATABASES;
1
2
3
4
5
6
7
8
9
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

SQL初步

新建数据库

1
CREATE DATABASE eg_db;

删除数据库

1
DROP DATABASE eg_db;

选定数据库
对表进行操作之前，需要选定数据库

1
USE eg_db;

创建表格
数据以表格形式保存，每一行是一个记录，每一列是一个属性。

1
2
3
4
5
CREATE TABLE person (
id int,
name varchar(50),
age int
);

查看表结构

1
DESCRIBE person;
1
2
3
4
5
6
7
8
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(50) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

删除表

1
DROP TABLE person;

重命名表

1
ALTER TABLE person RENAME people;

向表中添加一列

1
ALTER TABLE person ADD address varchar(100);

删除表中一列

1
ALTER TABLE person DROP COLUMN address;

修改一个列的数据类型

1
ALTER TABLE person MODIFY address varchar(50);

重命名一个列

1
ALTER TABLE person CHANGE COLUMN address addr varchar(50);

向表格中插入一条记录
INSERT INTO 表名称 VALUES (值1,值2,…)

1
INSERT INTO person VALUES (1, 'chenwen', 28, 'HNU');

或：INSERT INTO 表名称 (列1,列2,…) VALUES (值1,值2,…)

从表格中查询记录
SELECT 列名称1,列名称2… FROM 表名称;

1
SELECT name FROM person;

或：SELECT * FROM 表名称;

从表格中按条件查询一条记录
SELECT 列名称 FROM 表名 WHERE 列 运算符 值;

1
SELECT * FROM person WHERE name='chenwen';

从表格中删除一条记录
DELETE FROM 表名称 WHERE 列 运算符 值;

1
DELETE FROM person WHERE name='chenwen';

或：DELETE * FROM 表名称;

从表格中更新一条记录
UPDATE 表名称 SET 列名称=新值 WHERE 列=值;

1
UPDATE person SET age=31 WHERE name='chenwen';

返回结果删除重复项
SELECT DISTINCT 列名称 FROM 表名称;

1
SELECT DISTINCT name FROM person;

对查询结果按指定列进行排序
SELECT * FROM 表名称 ORDER BY 列名称;

1
SELECT * FROM person ORDER BY id;

或：SELECT * FROM 表名称 ORDER BY 列名称 DESC;

1
SELECT * FROM person ORDER BY id DESC;

以上命令很多，掌握它们的要领是按照增、删、改、查去运用它们，这也是我们实际上最常用的操作。操作数据库之前，记得使用USE选定这个数据库。

MySQL用户及权限管理

MySQL数据库默认只有一个root用户，MySQL将用户信息保存在mysql数据库user表中。
创建一个新用户
CREATE USER 用户名 IDENTIFIED BY ‘密码’;

1
CREATE USER chenwen IDENTIFIED BY 'password';

新用户不能创建新的数据库，因为没有设置权限。我们使用root账号创建新用户后，也顺便创建这个用户操作的数据库，并授予新用户权限，而不是用新用户去创建它的数据库。

删除用户
DROP USER 用户名;

1
DROP USER chenwen;

重命名用户
RENAME USER 原用户名 TO 新用户名;

1
RENAME USER chenwen TO cw;

修改当前用户密码
SET PASSWORD=PASSWORD(‘新密码’);

1
SET PASSWORD=PASSWORD('new_password');

修改指定用户密码
SET PASSWORD FOR 用户名 = PASSWORD(‘新密码’);

1
SET PASSWORD FOR chenwen = PASSWORD('new_password');

授予权限
授予一个用户权限：GRANT ALL PRIVILEGES ON 层级 TO 用户名@主机 WITH GRANT OPTION;
例如，授予chenwen用户针对eg_db数据库全部权限

1
GRANT ALL PRIVILEGES ON eg_db.* TO chenwen@'%' WITH GRANT OPTION;

如果你用的是老版本的MySQL，比如5.7，授权的命令不一样

1
GRANT ALL PRIVILEGES ON eg_db.* TO chenwen@'%' IDENTIFIED BY 'password';

可以使用以下方式指定权限范围

1
2
3
4
1. 所有主机：'%'
2. 精确的主机名或IP地址：www.biochen.com或者192.168.1.1
3. 使用"*"通配符：*.biochen.com
4. 指定一个网段:192.168.1.0/255.255.255.0

MySQL备份与恢复

备份数据库
mysqldump -u root -p 数据库名称 > 备份文件.sql

1
mysqldump -u root -p eg_db > eg_db.sql

mysqldump备份出来的是纯文本的SQL文件，可以修改后作为其他数据库数据使用。

从备份文件中恢复数据库
mysql -u root -p 数据库名称 < 备份文件.sql

1
mysqldump -u root -p eg_db > eg_db.sql

MySQL的字符编码

MySQL 8.0数据库的默认编码是utf8，老版本默认是latin，再也不用担心字符编码设置不对的乱码了。
以下命令可以查看MySQL当前使用的编码

1
SHOW VARIABLES LIKE 'character_set%';
1
2
3
4
5
6
7
8
9
10
11
12
13
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
1
SHOW VARIABLES LIKE 'collation%';
1
2
3
4
5
6
7
8
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_0900_ai_ci |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+
3 rows in set (0.00 sec)

创建数据库时指定编码

1
2
3
CREATE DATABASE eg_db
    DEFAULT CHARACTER SET utf8
    DEFAULT COLLATE stf8_general_ci;

修改一个数据库的编码

1
ALTER DATABASE eg_db CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL 数据类型

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
数值类型
MySQL支持所有标准SQL数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

类型 大小 范围（有符号） 范围（无符号） 用途
TINYINT 1 字节 (-128，127) (0，255) 小整数值
SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值
INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值
BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值
DOUBLE 8 字节 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值
DECIMAL DECIMAL(M,D) ，如果M>D，为M+2，否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值

日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。每个时间类型有一个有效值范围和一个”零”值，当指定不合法的MySQL不能表示的值时使用”零”值。TIMESTAMP类型有专有的自动更新特性。

类型 大小 (字节) 范围 格式 用途
DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值
TIME 3 ‘-838:59:59’/‘838:59:59’ HH:MM:SS 时间值或持续时间
YEAR 1 1901/2155 YYYY 年份值
DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
TIMESTAMP 8 1970-01-01 00:00:00/2037 年某时 YYYYMMDD HHMMSS 混合日期和时间值，时间戳

字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

类型 大小 用途
CHAR 0-255字节 定长字符串
VARCHAR 0-65535 字节 变长字符串
TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串
TINYTEXT 0-255字节 短文本字符串
BLOB 0-65 535字节 二进制形式的长文本数据
TEXT 0-65 535字节 长文本数据
MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据
MEDIUMTEXT 0-16 777 215字节 中等长度文本数据
LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据
LONGTEXT 0-4 294 967 295字节 极大文本数据

结束

我大部分时候，只是创建一个MySQL数据库给其他程序调用，比如WordPress。以上的内容，基本适合这种轻量级应用了。感谢你看到结尾，最后我们以一个命令结束。
退出MySQL

1
EXIT;