MySQL -SQL语言4-DDL-DML增删改
DDL 语句
表：二维关系
设计表：遵循规范
定义：字段，索引
- 字段：字段名，字段数据类型，修饰符
- 约束，索引：应该创建在经常用作查询条件的字段上
创建表
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是指表类型，也即在表创建时指明其使用的存储引擎
- 同一库中不同表可以使用不同的存储引擎
- 同一个库中表建议要使用同一种存储引擎类型
示例
CREATE TABLE student ( id int UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20) NOT NULL, age tinyint UNSIGNED, #height DECIMAL(5,2), gender ENUM('M','F') default 'M' )ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
通过查询现存表创建；新表会被直接插入查询而来的数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options]
[partition_options] select_statement
示例
MariaDB [db1]> create table user select user,host,password from mysql.user;
通过复制现存的表的表结构创建，但不复制数据
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name { LIKE old_tbl_name | (LIKE old_tbl_name) }
示例
MariaDB [db1]> desc student; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.001 sec) MariaDB [db1]> create table teacher like student; Query OK, 0 rows affected (0.006 sec) MariaDB [db1]> desc teacher; +--------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+---------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('M','F') | YES | | M | | +--------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.001 sec)
表查看
查看表：
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;
竖型排列 \G
SHOW TABLE STATUS LIKE 'student'\G
MariaDB [db1]> SHOW TABLE STATUS FROM db1\G *************************** 1. row *************************** Name: employee 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: NULL Create_time: 2020-02-17 11:43:21 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: Max_index_length: 0 Temporary: N *************************** 2. row *************************** Name: student 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: 10 Create_time: 2020-02-17 11:35:29 Update_time: NULL Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: Comment: Max_index_length: 0 Temporary: N 2 rows in set (0.001 sec) MariaDB [db1]>
修改和删除表
修改表
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'); alter table student modify is_del bool default false;
#修改字段名和类型 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 ;
#添加外键 ALTER TABLE students add foreign key(TeacherID) references teachers(tid);
#删除外键 SHOW CREATE TABLE students #查看外键名 ALTER TABLE students drop foreign key <外键名>;
DML 语句
DML: INSERT, DELETE, UPDATE
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,...)
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
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
