6 DDL

6.1 语法

数据库定义语言：用于库和表的管理
1）库的管理
创建、修改、删除

2）表的管理
创建、修改、删除

创建：create
修改：alter
删除：drop

6.2 库的管理

1）库的创建
语法：

create database books;

2）更改数据库的字符集

mysql> show create database books;
+----------+-------------------------------------------------------------------+
| Database | Create Database                                                   |
+----------+-------------------------------------------------------------------+
| books    | CREATE DATABASE `books` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
+----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter database books character set utf8;
Query OK, 1 row affected (0.00 sec)

3）库的删除

mysql> drop database  if exists books;
Query OK, 0 rows affected (0.02 sec)

6.3 表的管理

1）语法

# 表的创建
create table 表名(
    列名 列的类型[长度  约束],
    列名 列的类型[长度  约束],
  列名 列的类型[长度  约束],
  列名 列的类型[长度  约束]
);
# 表的修改
1）修改列名
2）修改列的类型或约束
3）添加新列
4）删除列
5）修改表名

2）创建表book

create table book(
    id int ,
  bName varchar(30),
  price double,
  authorId int,
    pubilshDate datetime
)
mysql> desc book;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| bName       | varchar(30) | YES  |     | NULL    |       |
| price       | double      | YES  |     | NULL    |       |
| authorId    | int(11)     | YES  |     | NULL    |       |
| pubilshDate | datetime    | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

3）创建author表

create table author(
id int,
au_name  varchar(20),
nation varchar(10)  
);
mysql> desc author;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| au_name | varchar(20) | YES  |     | NULL    |       |
| nation  | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4）修改列名

mysql> alter table book change pubilshDate pubDate datetime;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc book;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | YES  |     | NULL    |       |
| bName    | varchar(30) | YES  |     | NULL    |       |
| price    | double      | YES  |     | NULL    |       |
| authorId | int(11)     | YES  |     | NULL    |       |
| pubDate  | datetime    | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

5）修改列的类型或约束

mysql> alter table book modify column pubDate timestamp;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc book;
+----------+-------------+------+-----+-------------------+-----------------------------+
| Field    | Type        | Null | Key | Default           | Extra                       |
+----------+-------------+------+-----+-------------------+-----------------------------+
| id       | int(11)     | YES  |     | NULL              |                             |
| bName    | varchar(30) | YES  |     | NULL              |                             |
| price    | double      | YES  |     | NULL              |                             |
| authorId | int(11)     | YES  |     | NULL              |                             |
| pubDate  | timestamp   | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+----------+-------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)

6）增加新列

mysql> alter table author add column annual double;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc author;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| au_name | varchar(20) | YES  |     | NULL    |       |
| nation  | varchar(10) | YES  |     | NULL    |       |
| annual  | double      | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

7）删除列

mysql> alter table author drop column annual;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc author;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| au_name | varchar(20) | YES  |     | NULL    |       |
| nation  | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

8）修改表名

mysql> alter table author rename to book_author;
Query OK, 0 rows affected (0.00 sec)
mysql> desc book_author;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | YES  |     | NULL    |       |
| au_name | varchar(20) | YES  |     | NULL    |       |
| nation  | varchar(10) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

9）表的删除

mysql> drop table book_author;
Query OK, 0 rows affected (0.00 sec)

10）表的复制—只复制表的结构

mysql> create table jobs_bak like  jobs;
Query OK, 0 rows affected (0.00 sec)
mysql> show create table jobs_bak;
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                                               |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| jobs_bak | CREATE TABLE `jobs_bak` (
  `job_id` varchar(10) NOT NULL,
  `job_title` varchar(35) DEFAULT NULL,
  `min_salary` int(6) DEFAULT NULL,
  `max_salary` int(6) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> select * from jobs_bak;
Empty set (0.00 sec)

11）复制表的结构和数据

mysql> create table jobs_bak select * from jobs;
Query OK, 19 rows affected (0.00 sec)
Records: 19  Duplicates: 0  Warnings: 0
mysql> select * from jobs_bak;
+------------+---------------------------------+------------+------------+
| job_id     | job_title                       | min_salary | max_salary |
+------------+---------------------------------+------------+------------+
| AC_ACCOUNT | Public Accountant               |       4200 |       9000 |
| AC_MGR     | Accounting Manager              |       8200 |      16000 |
| AD_ASST    | Administration Assistant        |       3000 |       6000 |
| AD_PRES    | President                       |      20000 |      40000 |
| AD_VP      | Administration Vice President   |      15000 |      30000 |
| FI_ACCOUNT | Accountant                      |       4200 |       9000 |
| FI_MGR     | Finance Manager                 |       8200 |      16000 |
| HR_REP     | Human Resources Representative  |       4000 |       9000 |
| IT_PROG    | Programmer                      |       4000 |      10000 |
| MK_MAN     | Marketing Manager               |       9000 |      15000 |
| MK_REP     | Marketing Representative        |       4000 |       9000 |
| PR_REP     | Public Relations Representative |       4500 |      10500 |
| PU_CLERK   | Purchasing Clerk                |       2500 |       5500 |
| PU_MAN     | Purchasing Manager              |       8000 |      15000 |
| SA_MAN     | Sales Manager                   |      10000 |      20000 |
| SA_REP     | Sales Representative            |       6000 |      12000 |
| SH_CLERK   | Shipping Clerk                  |       2500 |       5500 |
| ST_CLERK   | Stock Clerk                     |       2000 |       5000 |
| ST_MAN     | Stock Manager                   |       5500 |       8500 |
+------------+---------------------------------+------------+------------+
19 rows in set (0.00 sec)

13）只复制部分数据

mysql> create table jobs_bak select * from jobs where job_id = 'AD_ASST';
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
mysql> 
mysql> select * from jobs_bak;
+---------+--------------------------+------------+------------+
| job_id  | job_title                | min_salary | max_salary |
+---------+--------------------------+------------+------------+
| AD_ASST | Administration Assistant |       3000 |       6000 |
+---------+--------------------------+------------+------------+
1 row in set (0.00 sec)

14）只复制部分结构

mysql> create table jobs_bak select job_id, job_title from jobs where 1=2;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc jobs_bak;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| job_id    | varchar(10) | NO   |     | NULL    |       |
| job_title | varchar(35) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> select * from jobs_bak;
Empty set (0.00 sec)

6.4 案例

1）创建表dept1
name type
id int(7)
name varchar(25)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> use test
Database changed
mysql> create table dept1(id int(7),name varchar(25));
Query OK, 0 rows affected (0.00 sec)
mysql> desc dept1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(7)      | YES  |     | NULL    |       |
| name  | varchar(25) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2）将表departments中的数据插入到新表的dept1中

mysql> insert into test.dept1 select department_id,department_name from myemployees.departments;
Query OK, 27 rows affected (0.00 sec)
Records: 27  Duplicates: 0  Warnings: 0
mysql> select * from dept1;
+------+------+
| id   | name |
+------+------+
|   10 | Adm  |
+------+------+

3）创建表emp5
name type
id int(7)
first_name varchar(25)
last_name varchar(25)
dept_id int(7)

mysql> create table emp5 (id int(7),first_name varchar(25), last_name varchar(25),dept_id int(7));
Query OK, 0 rows affected (0.00 sec)
mysql> desc emp5;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(7)      | YES  |     | NULL    |       |
| first_name | varchar(25) | YES  |     | NULL    |       |
| last_name  | varchar(25) | YES  |     | NULL    |       |
| dept_id    | int(7)      | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

4）把last_name的长度增加到50

mysql> alter table emp5 modify last_name varchar(50);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc emp5;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id         | int(7)      | YES  |     | NULL    |       |
| first_name | varchar(25) | YES  |     | NULL    |       |
| last_name  | varchar(50) | YES  |     | NULL    |       |
| dept_id    | int(7)      | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

5）根据employees 创建employees_bak

mysql> create table employees_bak like employees;
Query OK, 0 rows affected (0.00 sec)
mysql> desc employees_bak;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |
| first_name     | varchar(20)  | YES  |     | NULL    |                |
| last_name      | varchar(25)  | YES  |     | NULL    |                |
| email          | varchar(25)  | YES  |     | NULL    |                |
| phone_number   | varchar(20)  | YES  |     | NULL    |                |
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |
| salary         | double(10,2) | YES  |     | NULL    |                |
| commission_pct | double(4,2)  | YES  |     | NULL    |                |
| manager_id     | int(6)       | YES  |     | NULL    |                |
| department_id  | int(4)       | YES  | MUL | NULL    |                |
| hiredate       | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)
mysql> select * from employees_bak;
Empty set (0.00 sec)

6）删除表emp5

mysql> drop table emp5;
Query OK, 0 rows affected (0.00 sec)

7）将employees_bak重命名为emp5

mysql> alter table employees_bak rename to emp5;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------------+
| Tables_in_myemployees |
+-----------------------+
| departments           |
| emp5                  |
| employees             |
| job_grades            |
| jobs                  |
| jobs_bak              |
| locations             |
+-----------------------+
7 rows in set (0.00 sec)

8）在表emp5中添加新列test_column

mysql> alter table emp5 add column test_column varchar(20);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc emp5;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |
| first_name     | varchar(20)  | YES  |     | NULL    |                |
| last_name      | varchar(25)  | YES  |     | NULL    |                |
| email          | varchar(25)  | YES  |     | NULL    |                |
| phone_number   | varchar(20)  | YES  |     | NULL    |                |
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |
| salary         | double(10,2) | YES  |     | NULL    |                |
| commission_pct | double(4,2)  | YES  |     | NULL    |                |
| manager_id     | int(6)       | YES  |     | NULL    |                |
| department_id  | int(4)       | YES  | MUL | NULL    |                |
| hiredate       | datetime     | YES  |     | NULL    |                |
| test_column    | varchar(20)  | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

9.删除emp5中的列test_column

mysql> alter table emp5 drop column test_column;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> desc emp5;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| employee_id    | int(6)       | NO   | PRI | NULL    | auto_increment |
| first_name     | varchar(20)  | YES  |     | NULL    |                |
| last_name      | varchar(25)  | YES  |     | NULL    |                |
| email          | varchar(25)  | YES  |     | NULL    |                |
| phone_number   | varchar(20)  | YES  |     | NULL    |                |
| job_id         | varchar(10)  | YES  | MUL | NULL    |                |
| salary         | double(10,2) | YES  |     | NULL    |                |
| commission_pct | double(4,2)  | YES  |     | NULL    |                |
| manager_id     | int(6)       | YES  |     | NULL    |                |
| department_id  | int(4)       | YES  | MUL | NULL    |                |
| hiredate       | datetime     | YES  |     | NULL    |                |
+----------------+--------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)