MySQL | DDL SQL语句 | 数据定义语言 | Data Defination Language
DDL SQL 语句
DDL是指 Data Defination Language，数据定义语言，包括创建、删除、修改数据库表、列等的SQL语句。
列出数据库模式
数据库模式(Schema)。表建立在不同的数据库模式中。通常一个应用程序对应一个数据库模式。数据库模式保证多个应用程序可以使用同一个MySQL服务器，但是却互不干扰。
使用SHOW DATABASE列出所有的数据库模式
show databases;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
4 rows in set (0.00 sec)
切换数据库模式
使用use命令切换到指定的数据库模式
use information_schema;
mysql> use information_schema;
Database changed
切换后使用select database()查看当前使用的哪个数据库模式
select database();
mysql> select database();
+--------------------+
| database() |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)d
也可以在登录的时候直接声明数据库模式名称
-uroot -padmin information_schema
列出数据表
切换到某一数据库模式后使用SHOW TABLES列出该模式所有的数据表
show tables;
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| COLUMN_PRIVILEGES |
| ENGINES |
| EVENTS |
| FILES |
| GLOBAL_STATUS |
| GLOBAL_VARIABLES |
| KEY_COLUMN_USAGE |
| PARAMETERS |
| PARTITIONS |
| PLUGINS |
| PROCESSLIST |
| PROFILING |
| REFERENTIAL_CONSTRAINTS |
| ROUTINES |
| SCHEMATA |
| SCHEMA_PRIVILEGES |
| SESSION_STATUS |
| SESSION_VARIABLES |
| STATISTICS |
| TABLES |
| TABLESPACES |
| TABLE_CONSTRAINTS |
| TABLE_PRIVILEGES |
| TRIGGERS |
| USER_PRIVILEGES |
| VIEWS |
| INNODB_BUFFER_PAGE |
| INNODB_TRX |
| INNODB_BUFFER_POOL_STATS |
| INNODB_LOCK_WAITS |
| INNODB_CMPMEM |
| INNODB_CMP |
| INNODB_LOCKS |
| INNODB_CMPMEM_RESET |
| INNODB_CMP_RESET |
| INNODB_BUFFER_PAGE_LRU |
+---------------------------------------+
40 rows in set (0.00 sec)
查看表结构
使用DESC或者DESCRIBE命令查看指定表的结构
desc COLUMNS;
mysql> desc COLUMNS;
+--------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| COLUMN_NAME | varchar(64) | NO | | | |
| ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | |
| COLUMN_DEFAULT | longtext | YES | | NULL | |
| IS_NULLABLE | varchar(3) | NO | | | |
| DATA_TYPE | varchar(64) | NO | | | |
| CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | |
| NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | |
| CHARACTER_SET_NAME | varchar(32) | YES | | NULL | |
| COLLATION_NAME | varchar(32) | YES | | NULL | |
| COLUMN_TYPE | longtext | NO | | NULL | |
| COLUMN_KEY | varchar(3) | NO | | | |
| EXTRA | varchar(27) | NO | | | |
| PRIVILEGES | varchar(80) | NO | | | |
| COLUMN_COMMENT | varchar(1024) | NO | | | |
+--------------------------+---------------------+------+-----+---------+-------+
19 rows in set (0.02 sec)
该命令将列出表的所有列名、类型，是否可以为空，是否主键，默认值以及描述等属性。
创建数据库模式
使用CREAT DATABASE创建数据库，并在自定义数据库里创建表以及写数据等
create database crane;
mysql> create database crane;
Query OK, 1 row affected (0.00 sec)
MySQL默认的编码方式为latin1，显示中文字符时可能会乱码。可以在创建数据库的时候指明数据库编码
CREATE DATABASE crane CHARCTER SET utf8
删除数据库模式
删除数据库使用DROP DATABASE命令
drop database crane;
mysql> drop database crane;
Query OK, 0 rows affected (0.00 sec)
修改数据库模式
修改数据库使用ALTER DATABASE命令。使用此命令可能需要相应的权限
ALTER DATABASE crane CHARACTER SET utf8;
mysql> alter database crane character set utf8;
Query OK, 1 row affected (0.00 sec)
创建表
使用CREATE TABLE创建表。表必须创建在某个数据库模式下面
完整格式为：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name (table_column)。方括号[]表示该参数是可选的
CREATE TABLE IF NOT EXISTS tb_CRANE --如果表tb_CRANE不存在则创建
(id int primary key auto_increment, --创建int类型主键，且自增长
name varchar(200) not null, --创建字符类型的列，最大长度为200字节
salary float(11, 2), --创建小数类型的列，小数点后2位
birthday date, --创建日期类型的列 只保存日期不保存时间 如 2019-05-17
sleep time, --创建时间类型的列 只保存时间不保存日期 如 22:22:22
ts timestamp, --创建时间戳类型的列，保存日期与时间 如 2019-05-17 22:22:22
description text, --创建大文本类型数据的列，保存大文本，长度不受限
picture blob --创建二进制类型的列，保存二进制数据
);
'–'后面为MySQL的行注释。也可以使用/* */引起的多行注释。
SQL中列出了常用类型的列的创建方式，如int(integer)、varchar、float、date、time、timestamp、text、blob等。varchar有最大长度限制，一般为255个字符。而text要相对长一些，适用于存储较大的文本。
用primary key注明主键，不能为空，不会重复，每行对应一个主键值。
mysql> CREATE TABLE IF NOT EXISTS tb_CRANE
-> (id int primary key auto_increment,
-> name varchar(200) not null,
-> salary float(11, 2),
-> birthday date,
-> sleep time,
-> ts timestamp,
-> description text,
-> picture blob);
Query OK, 0 rows affected (0.42 sec)
使用TEMPORARY可以创建临时表。临时表是指创建后只在当前数据库内有效，断开数据库的连接后表就自动删除了
CREATE TEMPORARY TABLE tb_temp
( id integer,
name varchar(100),
primary key(id));
mysql> CREATE TEMPORARY TABLE tb_temp
-> ( id integer,
-> name varchar(100),
-> primary key(id));
Query OK, 0 rows affected (0.36 sec)
临时表常用来转移数据，或者存储中间计算结果。
删除表
使用DROP TABLE命令。如果表中有数据也会一并删除
drop table tb_CRANE;
mysql> drop table tb_CRANE;
Query OK, 0 rows affected (0.38 sec)
修改表
修改表结构使用ALTER TABLE。常用的操作有删除列，添加列，更改列，改表名等
删除列的格式为ALTER TABLE table_name DROP column_name
ALTER TABLE tb_CRANE DROP name;
mysql> ALTER TABLE tb_CRANE DROP name;
Query OK, 0 rows affected (0.61 sec)
Records: 0 Duplicates: 0 Warnings: 0
添加列的格式为ALTER TABLE table_name ADD column_name
ALTER TABLE tb_CRANE ADD name varchar(200) not null default 'CRANE';
--非空的varchar类型列，有默认值
mysql> ALTER TABLE tb_CRANE ADD name varchar(200) not null default 'CRANE';
Query OK, 0 rows affected (0.59 sec)
Records: 0 Duplicates: 0 Warnings: 0
修改列的格式为ALTER TABLE table_name CHANGE column_name
ALTER TABLE tb_CRANE CHANGE name c_name integer;
mysql> ALTER TABLE tb_CRANE CHANGE name c_name integer;
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0
--如果原name列有非数字字符，强制类型转换位integer列时会出错