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
欢迎来到这里！
我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。
注册 关于