DDL-定义语句

# 库的管理

CREATE DATABASE IF NOT EXISTS books;

ALTER DATABASE books CHARACTER SET gbk;

DROP DATABASE IF EXISTS books;

# 表的管理

CREATE TABLE book(

 id INT,

 bookname VARCHAR(20),

 price DOUBLE,

 authorId INT,

 publishDate DATETIME

);

# 修改列名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

# 修改列的类型或者约束

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

# 添加新列

ALTER TABLE author ADD COLUMN annual DOUBLE;

# 删除列

ALTER TABLE author DROP COLUMN annual;

# 修改表名

ALTER TABLE author RENAME TO book_author;

# 修改表的字符集

ALTER table job CONVERT TO CHARACTER SET utf8;

# 表的删除

DROP TABLE IF EXISTS book_author;

# 列出表

SHOW TABLES;

# 表的复制

# 1)仅仅复制表的结构

CREATE TABLE copy LIKE author;

# 2)复制表的结构 + 数据

CREATE TABLE copy2

SELECT * FROM author;

# 3)复制表的结构 + 部分数据

CREATE TABLE copy3

SELECT * FROM author

WHERE nation = '中国';

# 4)仅仅复制表的部分结构

CREATE TABLE copy4

SELECT id,author_name FROM author

WHERE 0;