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)
注：这是本人的学习笔记及练习，如果有错误的地方望指出一起讨论，谢谢！
网友评论