前言
本篇主要总结MySQL的数据库操作,表操作方面的常用DDL(Data Definition Language)语句。
一、DDL语句简述
DDL是数据定义语言的缩写,简单来说就是对数据库内部的对象进行创建、删除、修改等操作的语句。它和DML(Data Manipulation Language)语句最大的区别是DML只是对表内部的数据进行操作,而不涉及表的定义、结构的修改。
二、数据库操作
-- 创建数据库
create database dbname;
-- 查看数据库;
show databases;
-- 使用指定数据库
use dbname;
-- 删除数据库
drop database dbname;
三、表操作
1.创建表
-- 创建表
create table emp (
ename varchar(10),
hiredate date,
sal decimal(10,2),
deptno int(2)
);
-- 快速创建一个表结构相同的表
CREATE TABLE new_tablename LIKE old_tablename;
-- 创建表时增加索引
CREATE TABLE actor (
actor_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(45) NOT NULL,
last_name VARCHAR(45) NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (actor_id),
KEY idx_actor_last_name (last_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 创建表时增加外键
CREATE TABLE city (
city_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
city VARCHAR(50) NOT NULL,
country_id SMALLINT UNSIGNED NOT NULL,
last_update TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (city_id),
KEY idx_fk_country_id (country_id),
CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country (country_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
在创建索引时, 可以指定在删除、更新父表时,对子表进行的相应操作,包括 RESTRICT
、CASCADE
、SET NULL
和 NO ACTION
。
RESTRICT
和NO ACTION
是指限制在子表有关联记录的情况下, 父表不能更新,RESTRICT
为默认行为;CASCADE
表示父表在更新或者删除时,更新或者删除子表对应的记录;SET NULL
则表示父表在更新或者删除的时候,子表的对应字段被SET NULL
。
2.查看表
-- 查看表
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
mysql> show create table emp \G;
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`ename` varchar(10) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
-- 删除表
DROP TABLE tablename;
drop table emp;
3.修改表
-- 修改表名
ALTER TABLE tablename RENAME[TO] new_tablename;
-- 将表名emp改为emp1
ALTER TABLE emp rename emp1;
-- 修改字符集
ALTER TABLE tablename character set 字符集;
ALTER TABLE student character set utf8;
-- 修改表类型
ALTER TABLE tablename MODIFY[COLUMN] column_definition[FIRST|AFTER col_name];
-- 修改emp的ename字段定义,将varchar(10)改为varchar(20)
alter table emp modify ename varchar(20);
-- 增加表字段
ALTER TABLE tablename add[COLUMN]column_definition[FIRST|AFTER col_name];
-- 向emp中新增字段age,类型为int(3)
alter table emp add column age int(3);
-- 删除表字段
ALTER TABLE tablename DROP[COLUMN]col_name;
-- 将字段age删除
alter table emp drop age;
-- 字段改名
ALTER TABLE tablename CHANGE[COLUMN] old_col_name new_col_name column_definition;
-- 将age改为age1 同时类型由int(3) 改为int(4)
alter table emp change age age1 int(4);
-- 修改字段排列顺序
-- 将新增字段birth date 加在ename之后
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
mysql> alter table emp add birth date after ename;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
-- 将age移到ename后面
alter table emp modify age int(3) after ename ;
-- 修改外键
-- 或者修改
alter table city_innodb
drop foreign key fk_city_country;
alter table city_innodb
add CONSTRAINT `fk_city_country` foreign key (`country_id`)
references country_innodb (country_id) on delete restrict on update cascade;
CHANGE/FIRST|AFTER COLUMN这些关键字属于MySQL在标准sql中的扩展,在其他数据库中不一定适用
总结
笔者在实际开发中,经常要查询关于表操作的语句。最后决定整理一份属于自己的笔记。