“这是我参与8月更文挑战的第4天，活动详情查看：8月更文挑战”
MySQL 内置的功能
连接数据库
-u
-p
-S
-h
-P
-e
复制代码示例：
# mysql -S /tmp/mysql.sock
# mysql -uroot -p -hlocalhost -P3306
复制代码-e 参数的命令是不进行交互：mysql -uroot -e "show processlist";
# mysql -uroot -e "show processlist;"
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 10 | root | localhost | NULL | Query | 0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
复制代码< 用于恢复数据，将外部数据导入到 MySQL 中。
比如我们将 t_xz_invite 表的数据导入到 MySQL 中。
# mysql -uroot account < t_xz_invite.sq
复制代码内置命令
-
help —— 帮助命令
\c清除当前输入，结束上一个命令（control + c）\q退出 mysql（control + d）（exit）\G格式化输出结果source恢复数据（同 <）
1 SQL 介绍
- 结构化的查询语言
- 关系型数据库通用的命令
- 遵循 SQL92 标准（SQL_MODE）
2 SQL 常用种类
- DDL：数据定义语言
- DCL：数据控制语言
- DML：数据操作语言
- DQL：数据查询语言
3 SQL 引入——数据库的逻辑结构
-
库
- 库名
- 库属性：字符集、排序规则
-
表
- 表名
- 表属性：存储引擎、字符集、排序规则
- 列属性：数据类型、约束、其他属性
- 数据行
4 字符集（charset）
相当于 MySQL 的一个密码本（字符集编码）。
我们可以通过 show charset; 命令来查看 MySQL 支持的字符集。
mysql> show charset;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| ujis | EUC-JP Japanese | ujis_japanese_ci | 3 |
| sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| tis620 | TIS620 Thai | tis620_thai_ci | 1 |
| euckr | EUC-KR Korean | euckr_korean_ci | 2 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
| cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 |
| eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 |
| gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.02 sec)
复制代码MySQL5.7 默认的字符集是 latin1，当然，这是一个历史遗留的问题。
中文在下面几个字符集中占得字节：
- gbk —— 占 2 个字节
- utf8 —— 占 3 个字节
- utf8mb4 —— 占 4 个字节（建议使用这个）
注意：utf8mb4 支持 emoji。
5. 排序规则（collation）
我们使用 show collation; 命令查看排序规则。
注意：排序规则涉及 字符串大小写敏感 问题。
mysql> show collation;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
| dec8_swedish_ci | dec8 | 3 | Yes | Yes | 1 |
| dec8_bin | dec8 | 69 | | Yes | 1 |
| gb18030_unicode_520_ci | gb18030 | 250 | | Yes | 8 |
+--------------------------+----------+-----+---------+----------+---------+
222 rows in set (0.00 sec)
复制代码我们以 utf8mb4 为例：
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
| utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 |
| utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 |
| utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 |
| utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 |
复制代码以 _ci 结尾的排序规则是大小写不敏感的，比如 utf8mb4_general_ci，而后面的 Yes 代表这是默认的排序规则。
因此，当我们需要大小写敏感的时候，需要修改相应的排序规则。
6. 数据类型介绍
数据类型在 MySQL 中被规划为 4 种：
- 数字类型
- 字符串类型
- 时间类型
- 二进制类型
6.1 数字类型
- 整数（主要介绍）
- 浮点数
| 类 | 类型 | 说明 |
|---|---|---|
| 整数 | tinyint | 极小整数类型（0-255），有符号的为 -128 – 127 |
| 整数 | smallint | 较小整数数据类型（-2^15 到 2^15-1） |
| 整数 | mediumint | 中型整数数据类型 |
| 整数 | int | 常规（平均）大小的整数数据类型（-2^31 到 2^31-1） |
| 整数 | bigint | 较大整数数据类型（-2^63 到 2^63-1） |
| 浮点数 | float | 小型单精度（四个字节）浮点数 |
| 浮点数 | double | 常规双精度（八个字节）浮点数 |
| 定点数 | decimal | 包含整数部分、小数部分或同时包括二者的精确值数值 |
| BIT | bit | 位字段值 |
2^31=2147483648，因此 int 最多存储 10 位。
6.2 字符串类型
需要特别注意的是：
-
char：- 存储定长字符串类型，比如 char(100)，无论你存储 20 个还是 50 个，都是占用 100 个字符长度存储空间。
- 在分配的时候，不管字符串长度多长，都立即分配 100 个字符长度的存储空间，未占满的空间使用“空格”填充。
-
varchar：- 存储变长字符串类型，每次存储数据之前，都要先判断一下长度，按需分配磁盘空间。
- 会单独申请一个字符串长度的空间存储字符长度（少于 255，如果超过 255 以上，会占用两个存储空间）
-
enum：- 枚举数据类型
char 最多存储 255 个字符，varchar 最多存储 65535 个字符（其实是 65533 个，用两个来存储长度）
如何选择两种数据类型呢？
- 少于 255 个字符长度，定长的列值，选择 char
- 多于 255 个字符长度，变长的字符串，可以选择 varchar
6.3 时间类型
-
datetime：- 范围：1000-01-01 00:00:00 至 9999-12-31 23:59:59
-
timestamp：- 范围：1970-01-01 00:00:00 至 2038-12-31 23:59:59
- 可以根据时区自动变更时间
7. DDL 的应用
7.1 DDL 语句库的定义
- 创建数据库：
mysql> create database account charset utf8mb4 collate utf8mb4_bin;
Query OK, 1 row affected (0.00 sec)
复制代码- 查看数据库（不属于 DDL）：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| account |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
复制代码- 查看数据库创建语句（不属于 DDL）：
mysql> show create database account;
+----------+-----------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------------------------------+
| account | CREATE DATABASE `account` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */ |
+----------+-----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
复制代码- 删除数据库：
复制代码-
修改数据库：
- 修改数据库字符集（注意：一定是源字符集的超集，比如 utf8 -> utf8mb4）：
mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> show create database test;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database test charset utf8mb4;
Query OK, 1 row affected (0.00 sec)
mysql> show create database test;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+------------------------------------------------------------------+
1 row in set (0.00 sec)
复制代码* 修改排序类型同理。
复制代码7.2 关于库定义规范
- 库名使用小写字符
- 库名不能以数字开头
- 不能是数据库内部的关键字
- 必须设置字符集
7.3 DDL —— 表定义
- 建表
- 表名
- 列名
- 列属性
- PRIMARY KEY：主键约束，表中只能有一个，非空且唯一；
- NOT NULL：非空约束，不允许空值；
- UNIQUE KEY：唯一键约束，不允许重复值；
- DEFAULT：一般配合 NOT NULL 一起使用，默认值；
- UNSIGNED：无符号，一般配合数字列，非负数；
- COMMENT：注释；
- 表属性
create table student (
id INT primary key NOT NULL AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(255) NOT NULL COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'
) ENGINE InnoDB CHARSET utf8mb4;
复制代码-
建表规范：
- 表名小写字母，不能是数字开头；
- 不能是保留字符，使用和业务有关的表名；
- 选择合适的数据类型及长度；
- 每个列设置 NOT NULL + DEFAULT，对于数字用 0 填充，对于字符串使用有效字符串填充；
- 每个列设置注释；
- 表必须设置存储引擎和字符集；
- 主键列尽量是无关列数字列，最好是自增长；
- enum 类型不要保存数字，只能保存字符串类型；
-
查询建表信息：
mysql> show tables;
+-------------------+
| Tables_in_account |
+-------------------+
| student |
+-------------------+
1 row in set (0.00 sec)
mysql> show create table student;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
`sname` varchar(255) NOT NULL COMMENT '姓名',
`age` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
`gender` enum('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
`intime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
复制代码- 创建一个一模一样的表：
mysql> create table test like student;
Query OK, 0 rows affected (0.04 sec)
复制代码- 删除表；
mysql> drop table test;
Query OK, 0 rows affected (0.02 sec)
复制代码- 查看表字段：
mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(255) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | |
+--------+---------------------+------+-----+-------------------+----------------+
5 rows in set (0.00 sec)
复制代码- 添加列：
mysql> alter table student add qq varchar(20) NOT NULL COMMENT 'qq号';
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(255) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | |
| qq | varchar(20) | NO | | NULL | |
+--------+---------------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)
复制代码- 添加列（在某个列之后）：
mysql> alter table student add wechat varchar(60) NOT NULL UNIQUE COMMENT '微信号' after sname;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(255) | NO | | NULL | |
| wechat | varchar(60) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | |
| qq | varchar(20) | NO | | NULL | |
+--------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)
复制代码- 添加列（在某个列之前添加列）：
mysql> alter table student add num INT NOT NULL COMMENT '身份证' FIRST;
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| num | int(11) | NO | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(255) | NO | | NULL | |
| wechat | varchar(60) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | |
| qq | varchar(20) | NO | | NULL | |
+--------+---------------------+------+-----+-------------------+----------------+
8 rows in set (0.00 sec)
复制代码- 删除列：
mysql> alter table student drop num;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(255) | NO | | NULL | |
| wechat | varchar(60) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| gender | enum('m','f','n') | NO | | n | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | |
| qq | varchar(20) | NO | | NULL | |
+--------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.01 sec)
复制代码- 修改字段属性：
mysql> alter table student modify sname varchar(64) NOT NULL COMMENT '姓名';
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
复制代码- 修改字段类型：
mysql> alter table student change gender sex char(4) NOT NULL COMMENT '性别';
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+--------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| sname | varchar(64) | NO | | NULL | |
| wechat | varchar(60) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | NO | | 0 | |
| sex | char(4) | NO | | NULL | |
| intime | datetime | NO | | CURRENT_TIMESTAMP | |
| qq | varchar(20) | NO | | NULL | |
+--------+---------------------+------+-----+-------------------+----------------+
7 rows in set (0.00 sec)
复制代码7.4 DCL
- grant
- revoke
7.5 DML
- insert
-- 插入一行，不带字段
mysql> insert into student values(1,'关羽',20,'m',now());
Query OK, 1 row affected (0.00 sec)
-- 插入一行，带字段
mysql> insert into student(sname,age,sex) values ('刘备',21,'m');
Query OK, 1 row affected (0.01 sec)
-- 多行插入
mysql> insert into student(sname,age,sex) values ('赵云',22,'m'),('黄忠',23,'m');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from student;
+----+--------+-----+-----+---------------------+
| id | sname | age | sex | intime |
+----+--------+-----+-----+---------------------+
| 1 | 关羽 | 20 | m | 2021-01-24 20:21:50 |
| 2 | 刘备 | 21 | m | 2021-01-24 20:23:23 |
| 3 | 赵云 | 22 | m | 2021-01-24 20:24:38 |
| 4 | 黄忠 | 23 | m | 2021-01-24 20:24:38 |
+----+--------+-----+-----+---------------------+
4 rows in set (0.00 sec)
复制代码- update
- delete
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
![[桜井宁宁]COS和泉纱雾超可爱写真福利集-一一网](https://www.proyy.com/skycj/data/images/2020-12-13/4d3cf227a85d7e79f5d6b4efb6bde3e8.jpg)
![[桜井宁宁] 爆乳奶牛少女cos写真-一一网](https://www.proyy.com/skycj/data/images/2020-12-13/d40483e126fcf567894e89c65eaca655.jpg)
