3 SQL 语言
3.1 关系型数据库的常见组件
- 数据库：database
- 表：table，行：row 列：column
- 索引：index
- 视图：view
- 存储过程：procedure
- 存储函数：function
- 触发器：trigger
- 事件调度器：event scheduler，任务计划
- 用户：user
- 权限：privilege
3.2 SQL 语言的兴起与语法标准
目前，所有主要的关系数据库管理系统支持某些形式的 SQL，大部分数据库至少遵守 ANSI SQL89 标准，虽然有这一标准的存在，但大部分的 SQL 代码在不同的数据库系统中并不具有完全的跨平台性业内标准
微软和 Sybase 的 T-SQL，Oracle 的 PL/SQL
3.2.1 SQL 语言规范
在数据库系统中，SQL 语句不区分大小写，建议用大写
SQL 语句可单行或多行书写，默认以 " ; " 结尾
关键词不能跨多行或简写
用空格和 TAB 缩进来提高语句的可读性
子句通常位于独立行，便于编辑，提高可读性
注释：
- SQL 标准：
#单行注释，注意有空格
-- 注释内容
#多行注释
/*注释内容
注释内容
注释内容*/
- MySQL 注释：
# 注释内容
3.2.2 数据库对象和命名
数据库的组件(对象)：
- 数据库、表、索引、视图、用户、存储过程、函数、触发器、事件调度器等
命名规则：
- 必须以字母开头，后续可以包括字母,数字和三个特殊字符（# _ $）
- 不要使用 MySQL 的保留字
3.2.3 SQL 语句分类
- DDL: Data Defination Language 数据定义语言
- CREATE，DROP，ALTER
- DML: Data Manipulation Language 数据操纵语言
- INSERT，DELETE，UPDATE
- DQL：Data Query Language 数据查询语言
- SELECT
- DCL：Data Control Language 数据控制语言
- GRANT，REVOKE
- 软件开发：CRUD
3.2.4 SQL 语句构成
关健字 Keyword 组成子句 clause，多条 clause 组成语句
示例：
SELECT * #SELECT子句
FROM products #FROM子句
WHERE price>666 #WHERE子句
说明：一组 SQL 语句由三个子句构成，SELECT,FROM 和 WHERE 是关键字
获取 SQL 命令使用帮助：
官方帮助：https://dev.mysql.com/doc/refman/8.0/en/sql-statements.html
mysql> HELP KEYWORD
3.2.5 字符集和排序
早期 MySQL 版本默认为 latin1，从 MySQL8.0 开始默认字符集已经为 utf8mb4
查看支持所有字符集：
11:37:27(root@localhost) [(none)]> show character set;
#特别注意mysql中utf8并不是真正的uft8
正确的utf8最大应该占4个字节而mysql中utf8占3个字节，所以会导致一些生僻字显示不了
如要使用utf8应使用utf8mb4
utf8 | UTF-8 Unicode | utf8_general_ci | 3
查看支持所有排序规则：
11:37:42(root@localhost) [(none)]> show collation;
查看当前使用的排序规则
11:38:29(root@localhost) [(none)]> show variables like 'collation%';
设置服务器默认的字符集
[11:44:27 root@centos8 ~]#vim /etc/my.cnf
[mysqld]
character-set-server=utf8
设置客户端默认的字符集
#针对mysql客户端
[mysql]
default-character-set=utf8
#针对所有mysql客户端
[client]
default-character-set=utf8
范例：字符集和相关文件
11:47:48(root@localhost) [(none)]> show character set;
[11:48:20 root@centos8 ~]#ll /usr/share/mysql/charsets/
查看当前字符集的使用情况
11:49:01(root@localhost) [(none)]> show variables like 'character%';
3.3 管理数据库
3.3.1 创建数据库
CREATE DATABASE|SCHEMA [IF NOT EXISTS] 'DB_NAME'
CHARACTER SET 'character set name'
COLLATE 'collate name';
范例：
#创建数据库
11:49:20(root@localhost) [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)
#查看你创建的数据库
11:51:58(root@localhost) [(none)]> 11:51:58(root@localhost) [(none)]> show create database db1;
+----------+-------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#使用的字符集和排序规则存放位置
[root@centos8 ~]#cat /var/lib/mysql/db1/db.opt
default-character-set=latin1
default-collation=latin1_swedish_ci
11:55:31(root@localhost) [(none)]> create database db1;
ERROR 1007 (HY000): Can't create database 'db1'; database exists
11:55:41(root@localhost) [(none)]> create database IF NOT EXISTS db1;
Query OK, 1 row affected, 1 warning (0.00 sec)
11:56:22(root@localhost) [(none)]> show warnings;
+-------+------+----------------------------------------------+
| Level | Code | Message |
+-------+------+----------------------------------------------+
| Note | 1007 | Can't create database 'db1'; database exists |
+-------+------+----------------------------------------------+
1 row in set (0.00 sec)
范例：指定字符集创建新数据库
11:56:33(root@localhost) [(none)]> create database IF NOT EXISTS db2 CHARACTER SET 'utf8mb4';
Query OK, 1 row affected (0.00 sec)
11:58:17(root@localhost) [(none)]> 11:58:17(root@localhost) [(none)]> show create database db2;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| db2 | CREATE DATABASE `db2` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
范例: 创建数据库指定字符集，并且指定排序规则
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;
范例：
12:04:43(root@localhost) [(none)]> alter database db1 character set utf8mb4;
Query OK, 1 row affected (0.00 sec)
12:05:14(root@localhost) [(none)]> show create database db1;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| db1 | CREATE DATABASE `db1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
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)
范例：查看创建表命令
03:09:02(root@localhost) [db1]> show create table student;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` int unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`age` tinyint unsigned DEFAULT NULL,
`gender` enum('M','F') DEFAULT 'M',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
范例：查看表状态
03:09:06(root@localhost) [db1]> show table status like 'student'\G
*************************** 1. row ***************************
Name: student
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 2
Avg_row_length: 8192
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 12
Create_time: 2021-01-31 14:46:57
Update_time: 2021-01-31 14:49:35
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
查看库中所有表状态
SHOW TABLE STATUS FROM db_name
范例：
03:09:56(root@localhost) [db1]> show table status from db1\G
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
欢迎来到这里！
我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。
注册 关于