3-SQL 语言 -DDL，DML

范例: 创建数据库指定字符集，并且指定排序规则

12:00:16(root@localhost) [(none)]> create database zabbix character set utf8 collate utf8_bin;

3.3.2 修改数据库

ALTER DATABASE DB_NAME character set utf8;

范例：

3.3.3 删除数据库
DROP DATABASE|SCHEMA [IF EXISTS] 'DB_NAME';

范例：
12:05:30(root@localhost) [(none)]> drop database db1;
Query OK, 0 rows affected (0.01 sec)

12:06:41(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.01 sec)
[12:07:11 root@centos8 ~]#tree /var/lib/mysql -d
/var/lib/mysql
├── db2
├── #innodb_temp
├── mysql
├── performance_schema
├── sys
└── zabbix

3.3.4 查看数据库列表
SHOW DATABASES;

范例：
12:07:58(root@localhost) [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| db2                |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zabbix             |
+--------------------+
6 rows in set (0.00 sec)

3.4 数据类型
数据类型：

数据长什么样
数据需要多少空间来存放

数据类型

系统内置数据类型
用户定义数据类型

MySQL 支持多种内置数据类型

数值类型
日期/时间类型
字符串(字符)类型

数据类型参考链接
MySQL :: MySQL 8.0 Reference Manual :: 11 Data Types

选择正确的数据类型对于获得高性能至关重要，三大原则：

更小的通常更好，尽量使用可正确存储数据的最小数据类型
简单就好，简单数据类型的操作通常需要更少的 CPU 周期
尽量避免 NULL，包含为 NULL 的列，对 MySQL 更难优化

3.4.1 整数型
tinyint(m) 1 个字节 范围(-128~127)
smallint(m) 2 个字节 范围(-32768~32767)
mediumint(m) 3 个字节 范围(-8388608~8388607)
int(m) 4 个字节 范围(-2147483648~2147483647)
bigint(m) 8 个字节 范围(+-9.22*10 的 18 次方)
上述数据类型，如果加修饰符 unsigned 后，则最大值翻倍
如：tinyint unsigned 的取值范围为(0~255)
int(m)里的 m 是表示 SELECT 查询结果集中的显示宽度，并不影响实际的取值范围，规定了 MySQL 的一些交互工具（例如 MySQL 命令行客户端）用来显示字符的个数。对于存储和计算来说，Int(1)和 Int(20)是相同的
BOOL，BOOLEAN：布尔型，是 TINYINT(1)的同义词。zero 值被视为假，非 zero 值视为真
3.4.2 浮点型(float 和 double)，近似值
float(m,d) 单精度浮点型 8 位精度(4 字节) m 总个数，d 小数位
double(m,d) 双精度浮点型 16 位精度(8 字节) m 总个数，d 小数位
设一个字段定义为 float(6,3)，如果插入一个数 123.45678,实际数据库里存的是 123.457，但总个数还以实际为准，即 6 位
3.4.3 定点数
在数据库中存放的是精确值,存为十进制
decimal(m,d) 参数 m<65 是总个数，d<30 且 d<m 是小数位
MySQL5.0 和更高版本将数字打包保存到一个二进制字符串中（每 4 个字节存 9 个数字）。
例如: decimal(18,9)小数点两边将各存储 9 个数字，一共使用 9 个字节：其中，小数点前的 9 个数字用 4 个字节，小数点后的 9 个数字用 4 个字节，小数点本身占 1 个字节
浮点类型在存储同样范围的值时，通常比 decimal 使用更少的空间。float 使用 4 个字节存储。double 占用 8 个字节
因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用 decimal，例如存储财务数据。但在数据量比较大的时候，可以考虑使用 bigint 代替 decimal
3.4.4 字符串(char,varchar,text)
char(n) 固定长度，最多 255 个字符,注意不是字节
varchar(n) 可变长度，最多 65535 个字符
tinytext 可变长度，最多 255 个字符
text 可变长度，最多 65535 个字符
mediumtext 可变长度，最多 2 的 24 次方-1 个字符
longtext 可变长度，最多 2 的 32 次方-1 个字符
BINARY(M) 固定长度，可存二进制或字符，长度为 0-M 字节
VARBINARY(M) 可变长度，可存二进制或字符，允许长度为 0-M 字节
内建类型：ENUM 枚举, SET 集合
char 和 varchar 的比较：
参考：https://dev.mysql.com/doc/refman/8.0/en/char.html


char(n) 若存入字符数小于 n，则以空格补于其后，查询之时再将空格去掉，所以 char 类型存储的字符串末尾不能有空格，varchar 不限于此
char(n) 固定长度，char(4)不管是存入几个字符，都将占用 4 个字节，varchar 是存入的实际字符数 +1 个字节（n< n>255)，所以 varchar(4),存入 3 个字符将占用 4 个字节
char 类型的字符串检索速度要比 varchar 类型的快

varchar 和 text：

varchar 可指定 n，text 不能指定，内部存储 varchar 是存入的实际字符数 +1 个字节（n< n>255)，text 是实际字符数 +2 个字节。
text 类型不能有默认值
varchar 可直接创建索引，text 创建索引要指定前多少个字符。varchar 查询速度快于 text

3.4.5 二进制数据 BLOB
BLOB 和 text 存储方式不同，TEXT 以文本方式存储，英文存储区分大小写，而 Blob 以二进制方式存储，不分大小写
BLOB 存储的数据只能整体读出
TEXT 可以指定字符集，BLOB 不用指定字符集
3.4.6 日期时间类型
date 日期 '2008-12-2'
time 时间 '12:25:36'
datetime 日期时间 '2008-12-2 22:06:44'
timestamp 自动存储记录修改时间
YEAR(2), YEAR(4)：年份
timestamp 字段里的时间数据会随其他字段修改的时候自动刷新，这个数据类型的字段可以存放这条记录最后被修改的时间
3.4.7 修饰符
NULL 数据列可包含 NULL 值，默认值
NOT NULL 数据列不允许包含 NULL 值，相当于网站注册表中的 * 为必填选项
DEFAULT 默认值
PRIMARY KEY 主键，所有记录中此字段的值不能重复，且不能为 NULL
UNIQUE KEY 唯一键，所有记录中此字段的值不能重复，但可以为 NULL
CHARACTER SET name 指定一个字符集
适用数值型的修饰符：
AUTO_INCREMENT 自动递增，适用于整数类型
UNSIGNED 无符号
范例：AUTO_INCREMENT
02:27:31(root@localhost) [(none)]> create database test;
Query OK, 1 row affected (0.01 sec)

02:27:46(root@localhost) [(none)]> use test
Database changed
02:27:54(root@localhost) [test]> create table t1(id int unsigned auto_increment primary key) auto_increment = 4294967294;
Query OK, 0 rows affected (0.02 sec)
02:31:43(root@localhost) [test]> show table status from test like "t1" \G
*************************** 1. row ***************************
Name: t1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 4294967294
Create_time: 2021-01-31 14:29:48
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

02:33:15(root@localhost) [test]> insert into t1 values(null);
Query OK, 1 row affected (0.00 sec)

02:33:19(root@localhost) [test]> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
| 4294967295 |
+------------+
2 rows in set (0.00 sec)

02:33:32(root@localhost) [test]> insert into t1 values(null);
ERROR 1062 (23000): Duplicate entry '4294967295' for key 't1.PRIMARY'
#上面表的数据类型无法存放所有数据,修改过数据类型实现自增长数据的增加
02:33:42(root@localhost) [test]> alter table t1 modify id bigint auto_increment;
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

02:35:02(root@localhost) [test]> desc t1;
+-------+--------+------+-----+---------+----------------+
| Field | Type   | Null | Key | Default | Extra          |
+-------+--------+------+-----+---------+----------------+
| id    | bigint | NO   | PRI | NULL    | auto_increment |
+-------+--------+------+-----+---------+----------------+
1 row in set (0.01 sec)

02:35:12(root@localhost) [test]> insert t1 values(null);
Query OK, 1 row affected (0.00 sec)

02:35:34(root@localhost) [test]> select * from t1;
+------------+
| id         |
+------------+
| 4294967294 |
| 4294967295 |
| 4294967296 |
+------------+
3 rows in set (0.00 sec)

3.5 DDL 语句
表：二维关系
设计表：遵循规范
定义：字段，索引

字段：字段名，字段数据类型，修饰符
约束，索引：应该创建在经常用作查询条件的字段上

3.5.1 创建表
创建表：
CREATE TABLE

获取帮助：
HELP CREATE TABLE

创建表的方法
CREATE TABLE [IF NOT EXISTS] 'tbl_name' (col1 type1 修饰符, col2 type2 修饰符,
...)
#字段信息
col type1
PRIMARY KEY(col1,...)
INDEX(col1, ...)
UNIQUE KEY(col1, ...)
#表选项：
ENGINE [=] engine_name
ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

注意：

Storage Engine 是指表类型，也即在表创建时指明其使用的存储引擎
同一库中不同表可以使用不同的存储引擎
同一个库中表建议要使用同一种存储引擎类型

范例：创建表
2:46:55(root@localhost) [db1]> create table student ( id int unsigned auto_increment primary key, name varchar(20) not null, age tinyint unsigned, gender enum('M','F') default 'M' )ENGINE=InnoDB auto_increment=10 default charset=utf8;
Query OK, 0 rows affected, 1 warning (0.02 sec)
#id字段以10初始值

02:46:57(root@localhost) [db1]> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | NO   |     | NULL    |                |
| age    | tinyint unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')    | YES  |     | M       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

02:48:08(root@localhost) [db1]> insert student (name,age) values('xiaoming',20);
Query OK, 1 row affected (0.00 sec)

02:48:20(root@localhost) [db1]> select * from student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
| 10 | xiaoming |   20 | M      |
+----+----------+------+--------+
1 row in set (0.01 sec)
02:48:35(root@localhost) [db1]> insert student (name,age,gender) values('xiaohong',18,'f');
Query OK, 1 row affected (0.00 sec)

02:49:35(root@localhost) [db1]> select *from student;
+----+----------+------+--------+
| id | name     | age  | gender |
+----+----------+------+--------+
| 10 | xiaoming |   20 | M      |
| 11 | xiaohong |   18 | F      |
+----+----------+------+--------+
2 rows in set (0.00 sec)

范例：auto_increment 属性
02:49:49(root@localhost) [db1]> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set (0.01 sec)
#修改默认起始数
02:50:47(root@localhost) [db1]> set @@auto_increment_increment=10;
Query OK, 0 rows affected (0.00 sec)
#修改默认步长
02:51:34(root@localhost) [db1]> set @@auto_increment_offset=3;
Query OK, 0 rows affected (0.00 sec)

02:51:46(root@localhost) [db1]> show variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 3     |
+--------------------------+-------+
2 rows in set (0.00 sec)

范例：时间类型
02:53:11(root@localhost) [db1]> create table testdate (id int auto_increment primary key,date timestamp default current_timestamp not null);
Query OK, 0 rows affected (0.02 sec)
02:55:42(root@localhost) [db1]> insert testdate () values();
Query OK, 1 row affected (0.00 sec)

02:55:49(root@localhost) [db1]> insert testdate () values();
Query OK, 1 row affected (0.00 sec)

02:55:50(root@localhost) [db1]> insert testdate () values();
Query OK, 1 row affected (0.00 sec)

02:55:51(root@localhost) [db1]> select * from testdate;
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2021-01-31 14:55:49 |
|  2 | 2021-01-31 14:55:50 |
|  3 | 2021-01-31 14:55:51 |
+----+---------------------+
3 rows in set (0.00 sec)

(2) 通过查询现存表创建；新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name   [(create_definition,...)]
[table_options]
[partition_options]   select_statement

范例：依据别的表创建新表并且把数据也插入，用于备份
02:58:57(root@localhost) [db1]> create table user select user,host from mysql.user;
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

03:01:53(root@localhost) [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student       |
| testdate      |
| user          |
+---------------+
3 rows in set (0.00 sec)

03:02:04(root@localhost) [db1]> desc user;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| user  | char(32)  | NO   |     |         |       |
| host  | char(255) | NO   |     |         |       |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

03:02:10(root@localhost) [db1]> select * from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

(3) 通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE
old_tbl_name) }

范例：只复制表结构
03:02:22(root@localhost) [db1]> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | NO   |     | NULL    |                |
| age    | tinyint unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')    | YES  |     | M       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

03:04:00(root@localhost) [db1]> create table teacher like student;
Query OK, 0 rows affected (0.02 sec)

03:04:21(root@localhost) [db1]> desc teacher;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20)      | NO   |     | NULL    |                |
| age    | tinyint unsigned | YES  |     | NULL    |                |
| gender | enum('M','F')    | YES  |     | M       |                |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

3.5.2 表查看
查看表：
SHOW TABLES [FROM db_name]

查看表创建命令：
SHOW CREATE TABLE tbl_name

查看表结构：
DESC [db_name.]tb_name
SHOW COLUMNS FROM [db_name.]tb_name

查看表状态：
SHOW TABLE STATUS LIKE 'tbl_name'

查看支持的 engine 类型
SHOW ENGINES;

范例：查看表当前数据库表列表
03:04:28(root@localhost) [db1]> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| student       |
| teacher       |
| testdate      |
| user          |
+---------------+
4 rows in set (0.00 sec)

范例：查看表结构
03:07:32(root@localhost) [db1]> desc user;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| user  | char(32)  | NO   |     |         |       |
| host  | char(255) | NO   |     |         |       |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)
03:07:43(root@localhost) [db1]> show columns from user;
+-------+-----------+------+-----+---------+-------+
| Field | Type      | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------+-------+
| user  | char(32)  | NO   |     |         |       |
| host  | char(255) | NO   |     |         |       |
+-------+-----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

范例：查看创建表命令
3.5.3 修改和删除表
修改表
ALTER TABLE 'tbl_name'
#字段：
#添加字段：add
ADD col1 data_type [FIRST|AFTER col_name]
#删除字段：drop
#修改字段：
alter（默认值）, change（字段名）, modify（字段属性）

查看修改表帮助
Help ALTER TABLE

删除表
DROP TABLE [IF EXISTS] 'tbl_name';

修改表范例
ALTER TABLE students RENAME s1;
ALTER TABLE s1 ADD phone varchar(11) AFTER name;
ALTER TABLE s1 MODIFY phone int;
ALTER TABLE s1 CHANGE COLUMN phone mobile char(11);
ALTER TABLE s1 DROP COLUMN mobile;
ALTER TABLE s1 character set utf8;
ALTER TABLE s1 change name name varchar(20) character set utf8;
ALTER TABLE students ADD gender ENUM('m','f');
ALETR TABLE students CHANGE id sid int UNSIGNED NOT NULL PRIMARY KEY;
ALTER TABLE students DROP age;
DESC students;

#新建表无主键，添加和删除主键
CREATE TABLE t1 SELECT * FROM students;
ALTER TABLE t1 add primary key (stuid);
ALTER TABLE t1 drop primary key ;

3.6 DML 语句
DML: INSERT, DELETE, UPDATE
3.6.1 INSERT 语句
功能：一次插入一行或多行数据
语法
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
{VALUES | VALUE} ({expr | DEFAULT},...),(...),...
[ ON DUPLICATE KEY UPDATE #如果重复更新之
col_name=expr
[, col_name=expr] ... ]
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name
SET col_name={expr | DEFAULT}, ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]
INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
[INTO] tbl_name [(col_name,...)]
SELECT ...
[ ON DUPLICATE KEY UPDATE
col_name=expr
[, col_name=expr] ... ]

简化写法：
INSERT tbl_name [(col1,...)] VALUES (val1,...), (val21,...)

3.6.2 UPDATE 语句
语法：
UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

注意：一定要有限制条件，否则将修改所有行的指定字段
可利用 mysql 选项避免此错误：
mysql -U | --safe-updates| --i-am-a-dummy
[root@centos8 ~]#vim /etc/my.cnf
[mysql]
safe-updates

4.6.3 DELETE 语句
删除表中数据，但不会自动缩减数据文件的大小。
语法:
DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
#可先排序再指定删除的行数

注意：一定要有限制条件，否则将清空表中的所有数据
如果想清空表，保留表结构，也可以使用下面语句，此语句会自动缩减数据文件的大小。
TRUNCATE TABLE tbl_name;

缩减表大小
OPTIMIZE TABLE tb_name