【MySQL】DDL数据定义语言的基本用法create、drop和alter(增删改)

DDL 的基础语法

简单复习一波 SQL必知必会

DDL 的英文全称是 Data Definition Language(数据定义语言),

它定义了数据库的结构和数据表的结构

在 DDL 中,我们常用的功能是增删改,分别对应的命令是 CREATE、DROP 和 ALTER。

对数据库进行定义

建数据库的基本SQL语法格式为:

CREATE DATABASE database_name;//创建一个名为 database_name 的数据库

“database_name”为要创建的数据库的名称,该名称不能与已经存在的数据库重名。

mysql> CREATE DATABASE database_name;
Query OK, 1 row affected (0.00 sec) mysql> CREATE DATABASE database_name;
ERROR 1007 (HY000): Can't create database 'database_name'; database exists

删除数据库是将已经存在的数据库从磁盘空间上清除,清除之后,数据库中的所有数据也将一同被删除。

删除数据库的基本SQL语法格式为:

DROP DATABASE database_name;//删除一个名为 database_name 的数据库

“database_name”为要删除的数据库的名称。若指定的数据库不存在,则删除出错。

mysql> DROP DATABASE database_name;
Query OK, 0 rows affected (0.00 sec)
mysql> DROP DATABASE database_name;
ERROR 1008 (HY000): Can't drop database 'database_name'; database doesn't exist

【MySQL】DDL数据定义语言的基本用法create、drop和alter(增删改)

mysql> CREATE DATABASE database_name;
Query OK, 1 row affected (0.00 sec) mysql> CREATE DATABASE database_name;
ERROR 1007 (HY000): Can't create database 'database_name'; database exists
mysql> SHOW CREATE DATABASE database_name;
+---------------+------------------------------------------------------------------------+
| Database | Create Database |
+---------------+------------------------------------------------------------------------+
| database_name | CREATE DATABASE `database_name` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+---------------+------------------------------------------------------------------------+
1 row in set (0.00 sec) mysql> DROP DATABASE database_name;
Query OK, 0 rows affected (0.00 sec) mysql> SHOW CREATE DATABASE database_name;
ERROR 1049 (42000): Unknown database 'database_name'
CREATE DATABASE mysqlcrashcourse;//	 创建一个名为 mysqlcrashcourse 的数据库
DROP DATABASE mysqlcrashcourse;// 删除一个名为 mysqlcrashcourse 的数据库

对数据表进行定义

创建表结构的语法是这样的:

CREATE TABLE table_name;创建一个名为 table_name的表

一般要写至少写一行 A table must have at least 1 column,后面介绍如何创建表结构

创建一个名为 table_name的表

mysql> CREATE DATABASE database_name;
Query OK, 1 row affected (0.00 sec) mysql> USE database_name;
Database changed mysql> CREATE TABLE table_name;
ERROR 1113 (42000): A table must have at least 1 column mysql> CREATE TABLE table_name( name VARCHAR(50) NOT NULL);
Query OK, 0 rows affected (0.01 sec) mysql> show tables;
+-------------------------+
| Tables_in_database_name |
+-------------------------+
| table_name |
+-------------------------+
1 row in set (0.00 sec) mysql> CREATE TABLE table_name( name VARCHAR(50) NOT NULL);
ERROR 1050 (42S01): Table 'table_name' already exists
mysql>

删除表的基本SQL语法格式为:

DROP TABLE table_name;
DROP TABLE [IF EXISTS] table_name;

DROP TABLE table_name;删除一个名为 table_name的表

DROP TABLE IF EXISTS table_name; 执行了这条语句如果存在table_name表就删除,不存在不会报错也是执行。

mysql> show tables;
+-------------------------+
| Tables_in_database_name |
+-------------------------+
| table_name |
+-------------------------+
1 row in set (0.00 sec) mysql> CREATE TABLE table_name( name VARCHAR(50) NOT NULL);
ERROR 1050 (42S01): Table 'table_name' already exists
mysql> DROP TABLE table_name;
Query OK, 0 rows affected (0.01 sec) mysql> show tables;
Empty set (0.00 sec) mysql> DROP TABLE table_name;
ERROR 1051 (42S02): Unknown table 'database_name.table_name'
mysql> CREATE TABLE table_name;
ERROR 1113 (42000): A table must have at least 1 column
mysql> CREATE TABLE table_name( name varchar(50) not null);
Query OK, 0 rows affected (0.01 sec) mysql> drop table if exists table_name;
Query OK, 0 rows affected (0.01 sec) mysql> drop table if exists table_name;
Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> drop table table_name;
ERROR 1051 (42S02): Unknown table 'database_name.table_name'
mysql> drop table if exists table_name;
Query OK, 0 rows affected, 1 warning (0.00 sec) mysql>

创建表结构(数据表)

创建数据表,指的是在已经创建好的数据库中建立新表。创建数据表的过程是规定数据列的属性的过程,同时也是实施数据完整性(包括实体完整性、引用完整性和域完整性等)约束的过程。

比如我们想创建一个用户表,表名为 user,里面有两个字段,一个是 user_id,它是 int 类型,另一个 user_name 字段是varchar(255)类型。这两个字段都不为空,且 user_id 是递增的。

那么创建的时候就可以写为:

CREATE TABLE  user(
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(255) NOT NULL
);

使用CREATE TABLE创建表时,必须指定以下信息:

(1)要创建的表的名称,不区分大小写,不能使用SQL语言中的关键字,如DROP、ALTER、INSERT等。

(2)数据表中每一列(字段)的名称和数据类型,如果创建多列,就要用逗号隔开。

CREATE TABLE  user(
user_id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(255) NOT NULL,
primary key(user_id)
);
mysql> CREATE TABLE  user(
-> user_id int(11) NOT NULL AUTO_INCREMENT,
-> user_name varchar(255) NOT NULL
-> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key mysql> CREATE TABLE user(
-> user_id int(11) NOT NULL AUTO_INCREMENT,
-> user_name varchar(255) NOT NULL,
-> primary key(user_id)
-> );
Query OK, 0 rows affected (0.01 sec)

需要注意的是,语句最后以分号(;)作为结束符,最后一个字段的定义结束后没有逗号

数据类型中 int(11) 代表整数类型,显示长度为 11 位,括号中的参数 11 代表的是最大有效显示长度,与类型包含的数值范围大小无关。varchar(255)代表的是最大长度为 255 的可变字符串类型。NOT NULL表明整个字段不能是空值,是一种数据约束。AUTO_INCREMENT代表主键自动增长。

设计工具

我们可以使用一些可视化工具来创建和操作数据库和数据表。

在这里使用 Navicat,https://www.navicat.com.cn/

它是一个数据库管理和设计工具,跨平台,支持很多种数据库管理软件,比如 MySQL、Oracle、MariaDB 等。

假如想创建一个nba的数据库,在nba数据库中创建player表,我们设计以下的字段:

字段 含义 类型

player_id 球员ID int整型类型,最大显示长度11

team_id 球队ID int整数类型,最大显示长度为11

player_name 球员姓名 varchar字符串类型,最大长度255

height 身高 float浮点类型,一共存储3个有效数字,其中小数点长度为2

其中 player_id 是数据表 player 的主键,且自动增长,也就是 player_id 会从 1 开始,然后每次加 1。player_id、team_id、player_name 这三个字段均不为空,height 字段可以为空。

如何实现呢?

按照上面的设计需求,可以使用 Navicat 软件进行设计,如下所示:

【MySQL】DDL数据定义语言的基本用法create、drop和alter(增删改)

然后,还可以对 player_name 字段进行索引,索引可以使查询速度快了点,索引类型为Unique。使用 Navicat 设置如下:

【MySQL】DDL数据定义语言的基本用法create、drop和alter(增删改)

btree

这样一张 player 表就通过可视化工具设计好了。我们可以把这张表导出来,可以看看这张表对应的 SQL 语句是怎样的。方法是在 Navicat 左侧用右键选中 player 这张表。

【MySQL】DDL数据定义语言的基本用法create、drop和alter(增删改)

这样就可以看到导出的 SQL 文件了,代码如下:

/*
Navicat Premium Data Transfer Source Server : 本地测试
Source Server Type : MySQL
Source Server Version : 50728
Source Host : localhost:3306
Source Schema : nba Target Server Type : MySQL
Target Server Version : 50728
File Encoding : 65001 Date: 19/03/2020 22:01:28
*/ SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0; -- ----------------------------
-- Table structure for player
-- ----------------------------
DROP TABLE IF EXISTS `player`;
CREATE TABLE `player` (
`player_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '球员id',
`player_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '球员名字',
`team_id` int(11) NOT NULL COMMENT '球队id',
`height` float(3, 2) NULL DEFAULT NULL COMMENT '身高',
PRIMARY KEY (`player_id`) USING BTREE,
UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; SET FOREIGN_KEY_CHECKS = 1;

我们可以看到整个 SQL 文件中的 DDL 处理,首先先删除 player 表(如果数据库中存在该表的话),然后再创建 player 表,里面的数据表和字段都使用了反引号``,

这是为了避免它们的名称与 MySQL 保留字段相同,对数据表和字段名称都加上了反引号。

其中 player_name 字段的字符集是 utf8mb4,排序规则是utf8_general_ci,代表对大小写不敏感,如果设置为utf8_bin,代表对大小写敏感,还有许多其他排序规则这里不进行介绍。

因为 player_id 设置为了主键,因此在 DDL 中使用PRIMARY KEY进行规定。

同时索引方法采用 BTREE,因为我们对 player_name 字段进行索引,在设置字段索引时,我们可以设置为UNIQUE INDEX(唯一索引),也可以设置为其他索引方式,比如NORMAL INDEX(普通索引),这里我们采用UNIQUE INDEX。唯一索引和普通索引的区别在于它对字段进行了唯一性的约束。在索引方式上,你可以选择BTREE或者HASH,这里采用了BTREE方法进行索引。

整个数据表的存储规则采用 InnoDB,它是 MySQL5.5 版本之后默认的存储引擎。同时,我们将字符集设置为 utf8mb4,排序规则为utf8_general_ci,行格式为Dynamic,就可以定义数据表的最后约定了:

ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic

能看出可视化工具还是非常方便的,它能直接帮我们将数据库的结构定义转化成 SQL 语言,方便数据库和数据表结构的导出和导入。不过在使用可视化工具前,我们首先需要了解 DDL 的基础语法,至少能清晰地看出来不同字段的定义规则、索引方法,以及主键和外键的定义。

修改表结构

在创建表结构之后,我们还可以对表结构进行修改,那如何使用 DDL 命令来完成表结构的修改。

\1. 添加字段,比如在数据表中添加一个 age 字段,类型为int(11)

ALTER TABLE player ADD (age int(11));

\2. 修改字段名,将 age 字段改成player_age

ALTER TABLE player RENAME COLUMN age to player_age

MySQL:rename column 没有用。。。

下面的可以

ALTER TABLE player change age player_age int(11);

ALTER TABLE player change COLUMN age player_age int(11);

\3. 修改字段的数据类型,将player_age的数据类型设置为float(3,1)

ALTER TABLE player MODIFY (player_age float(3,1));

modify

MySQL:ALTER TABLE player MODIFY player_age float(3,1);

\4. 删除字段, 删除刚才添加的player_age字段

ALTER TABLE player DROP COLUMN player_age;

drop

mysql> use nba;
Database changed
mysql> show tables;
+---------------+
| Tables_in_nba |
+---------------+
| player |
+---------------+
1 row in set (0.00 sec) mysql> show player;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'player' at line 1
mysql> desc player;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| player_id | int(11) | NO | PRI | NULL | auto_increment |
| player_name | varchar(255) | NO | UNI | NULL | |
| team_id | int(11) | NO | | NULL | |
| height | float(3,2) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec) mysql> alter table player add(age int(11));
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> desc player;
+-------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+----------------+
| player_id | int(11) | NO | PRI | NULL | auto_increment |
| player_name | varchar(255) | NO | UNI | NULL | |
| team_id | int(11) | NO | | NULL | |
| height | float(3,2) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec) mysql> ALTER ATBLE player RENAME COLUMN age to player_name; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ATBLE player RENAME COLUMN age to player_name' at line 1
mysql> ALTER TABLE player RENAME COLUMN age to player_age
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLUMN age to player_age' at line 1
mysql> use player;
ERROR 1049 (42000): Unknown database 'player' mysql> ALTER TABLE player change COLUMN age to player_age;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'to player_age' at line 1
mysql> ALTER TABLE player change COLUMN age player_age;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> ALTER TABLE player change COLUMN age player_age int(11);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE player MODIFY (player_age float(3,1));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(player_age float(3,1))' at line 1
mysql> ALTER TABLE player MODIFY player_age float(3,1);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE player DROP COLUMN player_age;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

小结

稍微系统的复习了下DDL的基础语法,了解下对数据库和数据表进行定义。

DDL

基础语法

DDL Data Definition Language 数据定义语言 定义了数据库的结构和数据表的结构

对数据库进行定义:

create database,drop database

对数据表进行定义

创建: create table [table_name] ( 字段名 数据类型,…)

修改: alter table …

可视化管理和设计工具:Navicat

参考资料

1、MySQL 8从入门到精通:视频教学版/王英英著.—北京:清华大学出版社,2019

2、MySQL必知必会 - [英]Ben Forta

3、SQL必知必会,从入门到数据实战 - 陈旸 这篇笔记主要就是记录下极客时间的SQL必知必会

上一篇:[WPF]解决ListView在没有Items时,水平滚动条不出现的问题


下一篇:创建表空间、新增用户、给用户赋予DBA权限 、删除用户下的上有数据表