SQL-DDL(13)数据定义语言(1)

1.功能：

 实现库和表的管理
 一.库的管理
 创建，修改，删除
 二.表的管理
 创建，修改，删除
 
 创建：create
 修改：alter
 删除：drop

2.库管理的练习

案例1:创建库：Books

#[]:表示里面内容可有可无，推荐有！！！
mysql> create database [if not exists] books;
Query OK, 1 row affected (0.18 sec)

案例2:库的修改(更改库的字符集）

mysql> alter database books character set gbk;
Query OK, 1 row affected (0.37 sec)

案例3:库的删除

#[]:表示里面内容可有可无，推荐有！！！
mysql> drop database [if exists] books;
Query OK, 0 rows affected (0.41 sec)

3.表管理的练习

1.语法：
create table 表名(
       列名 列的类型【（长度） 约束】
       列名 列的类型【（长度） 约束】
       ... 
       列名 列的类型【（长度） 约束】

)

2.练习

（1）创建表

@1.创建表book

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

@2.创建表author

mysql> create table author(
    -> id int,
    -> au_name varchar(20),
    -> nation varchar(10)
    -> );
Query OK, 0 rows affected (0.17 sec)

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)

（2）表的修改

  语法：alter table 表名 add/drop/modify/change column 列名 【列类型 约束】;

@1.修改表名

mysql> alter table book change column publishdate pubdate datetime;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

@2.修改列的类型或约束

mysql> alter table book modify column pubdate timestamp;
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

@3.添加新列

mysql> alter table book add column annual double;
Query OK, 0 rows affected (0.43 sec)
Records: 0  Duplicates: 0  Warnings: 0

@4.删除列

mysql> alter table book drop column annual;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

@5.修改表名

mysql> alter table book rename to my_book;
Query OK, 0 rows affected (0.23 sec)

(3)表的删除

#[]:表示里面内容可有可无，推荐有！！！
mysql> drop table [if exists]author;
Query OK, 0 rows affected (0.43 sec)

注意：通常写法：

     drop database if exists 旧库名；
     create databse 新库名；
     
     drop table if exists 旧表名；
     create table 旧表名；

(4)表的复制

@1.仅仅复制表的结构

create table 新表名 like 旧表名;

mysql> create table boys_1 like boys;
Query OK, 0 rows affected (0.38 sec)

mysql> select * from boys_1;
Empty set (0.00 sec)

mysql> desc boys_1;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| id      | int(11)     | NO   | PRI | NULL    | auto_increment |
| boyName | varchar(20) | YES  |     | NULL    |                |
| userCP  | int(11)     | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

@2.复制表的结构+数据

create table 新表名 子查询（select * from 旧表名）;

sql> create table boys_2 select * from boys;
Query OK, 6 rows affected (0.16 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from boys_2;
+----+-----------+--------+
| id | boyName   | userCP |
+----+-----------+--------+
|  2 | 鹿晗      |    800 |
|  3 | 黄晓明    |     50 |
|  4 | 段誉      |    300 |
|  5 | 谢霆锋    |   1000 |
|  6 | 张一山    |    666 |
|  7 | 张翰      |    500 |
+----+-----------+--------+
6 rows in set (0.00 sec)

@3.只复制部分数据
create table 新表名 子查询 where 筛选条件

mysql> create table boys_3 select id,boyname from boys where usercp between 200 and 1000;
Query OK, 5 rows affected (0.18 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from boys_3;
+----+-----------+
| id | boyname   |
+----+-----------+
|  2 | 鹿晗      |
|  4 | 段誉      |
|  5 | 谢霆锋    |
|  6 | 张一山    |
|  7 | 张翰      |
+----+-----------+
5 rows in set (0.00 sec)

@4. 只复制某些字段

create table 新表名 子查询 where 0/(1=2）等等不成立的条件；
(没有数据，也并不像‘like’复制全部字段）

mysql> create table boys_4 select id , boyname from boys where 0;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from boys_4;
Empty set (0.00 sec)

mysql> desc boys_4;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   |     | 0       |       |
| boyname | varchar(20) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！