MySQL 命令分类之 DDL

数据库

创建数据库

1
2
3
4
5
6
7
8
# 语法
create database [数据库名] character set [字符编码];

# 创建一个数据库，名字是study1  
create database study1;  

# 创建数据库study2，指定字符编码为gbk：  
create database study2 character set gbk;

查看数据库

1
2
3
4
5
# 显示当前所有的数据库  
show databases;

# 查看study2数据库的定义信息：  
show create database study2;

修改数据库

1
2
3
4
5
# 语法
alter database [数据库名] character set [字符编码];

# 将study2数据库的编码修改为utf-8；  
alter database study2 character set utf8;

删除数据库

1
2
# 删除study2数据库  
drop database study2;

查看当前使用的数据库

1
select database();

切换数据库

1
2
# 切换至study2数据库  
use study2;

数据库表

创建表

创建表语法 

1
create table 表名 字段1 字段类型,字段2 字段类型,...字段n 字段类型

常用的字段数据类型：
int：整型
double：小数类型，例如double5,2 表示最多5位，其中必须有2位小数，即最大值为999.99；
decimal $m,d$:小数类型，m是长度，d是小数的位数
char：固定长度字符串类型； char$ 10 $ 如果不足10位则会自动补足10位：’abc ‘
varchar：可变长度字符串类型；varchar$ 10 $ 如果不足10位不会补足：’abc’,性能不如char高
text：字符串类型;适用于大文本内容，可以存储文章评论等内容。
blob：字节类型，可以存储图片和视频等内容，会降低数据库性能，通常不用。
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 以世界协调时$ UTC $格式保存的, 在存储时会对当前时区进行转换, 在查询检索
时再把UTC时间转换为当前时区
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss 按实际输入的存储,与时区无关

1
create table employee(id int,name varchar(20),sex char(2),birthday date,job varchar(50),salary double(7,2));

查看数据库中所有的表

1
SHOW TABLES;

查看表的结构

1
2
3
4
5
# 语法
desc [表名];

# 查看表的字段信息  
DESC employee;

修改表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 在员工表的基础上增加一个address列  
ALTER TABLE employee ADD address varchar(100);  

# 修改job列，使其长度为30。  
ALTER TABLE employee MODIFY job varchar(30);  

# 删除address列,一次只能删一列。  
ALTER TABLE employee DROP address;  

# 表名改为user。  
RENAME TABLE employee TO user;

# 修改表的字符集为gbk  
ALTER TABLE user CHARACTER SET gbk;  

# 列名name修改为username  
ALTER TABLE user CHANGE name username varchar(20);

查看表格的创建细节

1
2
# 查看表格的创建细节  
SHOW CREATE TABLE user;

删除表

1
2
# 删除表  
DROP TABLE user ;

创建外键

1
2
3
4
5
6
7
8
9
10
create table student(
    stuId int(11) auto_increment primary key,
    stuName varchar(50),
    gradeId int(4),
    phone varchar(4),
    phone varchar(20),
    address varchar(2000),
    brithday date,
    constraint `s_g_key` foreign  key (gradeId) references grade (GradeId)
)

修改表增加外键(外键作用就是利用外部数据约束本表内容)

1
2
constraint  外键名 foreign key 本表字段 references 来源表(来源字段)
alter table 表名 add constraint 外键名 foreign key 本表字段 references 来源表(来源字段)

删除表的外键

1
2
alter table 表名 drop foreign key 外键名;
alter  table subject drop foreign key sub_g_key;

创建索引

在mysql中可以在创建表（CREATE TABLE）的同时创建索引；也可以在创建表后创建索引，使用CREATE INDEX语句或ALTER TABLE 语句。

1、使用CREATE INDEX语句

可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。 

1
CREATE INDEX [<索引名>] ON <表名> (<列名> [<长度>] [ ASC | DESC])

语法说明如下：

  • <索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
  • <表名>：指定要创建索引的表名。
  • <列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。
  • <长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
  • ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。

2、使用 CREATE TABLE 语句

索引可以在创建表（CREATE TABLE）的同时创建，语法格式：

1、创建主键索引

1
CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)

在使用 CREATE TABLE 语句定义列选项的时候，可以通过直接在某个列定义后面添加 PRIMARY KEY 的方式创建主键。而当主键是由多个列组成的多列索引时，则不能使用这种方法，只能用在语句的最后加上一个 PRIMARY KRY(<列名>，…) 子句的方式来实现。

2、创建一般索引

1
KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)

3、创建唯一性索引

1
UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

4、创建外键索引

1
FOREIGN KEY <索引名> <列名>

示例1：创建一个表 tb_stu_info，在该表的 height 字段创建一般索引。

1
2
3
4
5
6
7
8
9
mysql> CREATE TABLE tb_stu_info
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> INDEX(height)
    -> );

三、使用 ALTER TABLE 语句

在使用 ALTER TABLE 语句修改表的同时，可以向已有的表添加索引。具体的做法是在 ALTER TABLE 语句中添加以下语法成分的某一项或几项。

1、创建主键索引

1
ADD PRIMARY KEY [<索引类型>] (<列名>,…)

2、创建一般索引

1
ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

3、创建唯一性索引

1
ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

4、创建外键索引

1
ADD FOREIGN KEY [<索引名>] (<列名>,…)

示例1：创建一个表 tb_stu_info3后，在该表的 id 字段上使用 UNIQUE 关键字创建唯一索引。 

1
2
3
4
5
6
7
8
9
10
mysql> CREATE TABLE tb_stu_info3
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> dept_id INT DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> height INT DEFAULT NULL,
    -> );
Query OK，0 rows affected (0.40 sec)
mysql>ALTER TABLE tb_stu_info3 ADD UNIQUE (id) ;

显示索引信息

以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

示例： 

1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW CREATE TABLE tb_stu_info\G
*************************** 1. row ***************************
       Table: tb_stu_info
Create Table: CREATE TABLE `tb_stu_info` (
  `id` int(11) NOT NULL,
  `name` char(45) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `height` int(11) DEFAULT NULL,
  KEY `height` (`height`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.01 sec)
MySQL
#MySQL
MySQL 命令分类之 DDL
https://flepeng.github.io/041-MySQL-21-命令-MySQL-命令分类之-DDL/
作者
Lepeng
发布于
2021年3月6日
许可协议