07DDL-数据定义语言

库的管理

库的创建

语法：

create database  [if not exists]库名;

if not exists 如果已经存在不会报错，只会报警告。不存在就创建

#显示所有的数据库
show databases;

#使用某个数据库，使其作为当前数据库
use employees;

案例：创建库Books

CREATE DATABASE IF NOT EXISTS books ;

命名规则
• 数据库名不得超过30个字符，变量名限制为29个
• 必须只能包含 A–Z, a–z, 0–9, _共63个字符
• 不能在对象名的字符间留空格
• 必须不能和用户定义的其他对象重名
• 必须保证你的字段没有和保留字、数据库系统或常用方法冲突
• 保持字段名和类型的一致性,在命名字段并为其指定数据类型的时候一定要保证一致性

库的修改

备注：库一般不修改 ，容易导致数据出现问题，尤其是库中已经有数据的时候

RENAME DATABASE books TO 新库名; 

以上语法现在已经不能用了，不安全，导致数据丢失。如果想修改需要停止服务，然后找到数据对应的文件夹，修改文件夹的名字，但是同样存在不稳定的问题。

更改库的字符集

ALTER DATABASE books CHARACTER SET gbk;

3、库的删除

DROP DATABASE IF EXISTS books;

表的管理

表的创建

前提：具备创建表的权限以及拥有足够的存储空间
语法：

create table 表名(
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	列名 列的类型【(长度) 约束】,
	...
	列名 列的类型【(长度) 约束】
)

案例：创建表Book

CREATE TABLE book(
	id INT,#编号
	bName VARCHAR(20),#图书名字符个数
	price DOUBLE,#价格
	authorId  INT,#作者编号
	publishDate DATETIME#出版日期
);

案例：创建表author

CREATE TABLE IF NOT EXISTS author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
)；

表的修改

语法

alter table 表名 add|drop|modify|change column 列名 【列类型 约束】;

①修改列名

ALTER TABLE book CHANGE [COLUMN] publishdate pubDate DATETIME; #必须加类型

建议加上column。

②修改列的类型或约束（对于约束的修改只适用于之后插入的数据）

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

③添加新列

ALTER TABLE author ADD COLUMN annual DOUBLE; 

④删除列

ALTER TABLE book_author DROP COLUMN  annual;

⑤修改表名
执行rename语句可以改变表、视图的名称，前提是必须是对象拥有者。

ALTER TABLE author RENAME TO book_author;

表的删除

• 数据和结构都被删除
• 所有正在运行的相关事务被提交
• 所有相关索引被删除
• DROP TABLE 语句不能回滚

DROP TABLE IF EXISTS book_author;

# 显示当前数据库下所有的表
SHOW TABLES;

通用的写法：

DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;


DROP TABLE IF EXISTS 旧表名;
CREATE TABLE  表名();

表的复制

1.仅仅复制表的结构

CREATE TABLE copy LIKE author;

2.复制表的结构+数据

CREATE TABLE copy2 
as
SELECT * FROM author;

只复制部分数据

CREATE TABLE copy3
as
SELECT id,au_name
FROM author 
WHERE nation='中国';

仅仅复制某些字段，不要数据

CREATE TABLE copy4 
as
SELECT id,au_name
FROM author
WHERE 0;

清空表

truncate table 表名;
删除表中所有的数据
释放表的存储空间

案例

1. 创建表dept1

CREATE TABLE dept1(
	id INT(7),
	NAME VARCHAR(25)
);

2. 将表departments中的数据插入新表dept2中（跨库复制表结构）

CREATE TABLE dept2
SELECT department_id,department_name
FROM myemployees.departments;  
#加上作用域，没有就不行

3. 创建表emp5

CREATE TABLE emp5(
	id 			INT(7),
	first_name 	VARCHAR(25),
	last_name 	VARCHAR(25),
	dept_id 	INT(7)
);

4. 将列Last_name的长度增加到50

ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);

5. 根据表employees创建employees2

CREATE TABLE employees2 LIKE myemployees.employees;

6. 删除表emp5

DROP TABLE IF EXISTS emp5;

7. 将表employees2重命名为emp5

ALTER TABLE employees2 RENAME TO emp5;

8. 在表emp5中添加新列test_column

ALTER TABLE emp5 ADD COLUMN test_column INT;

9. 删除表emp5中的列 dept_id

ALTER TABLE emp5 DROP COLUMN test_column;

常见的数据类型

整型

分类：

类型	字节
tinyint	1
smallint	2
mediumint	3
int/integer	4
bigint	8

特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度，长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用！一加上zerofill就表示是无符号的数了！
④范围由类型决定，小括号里的数表示显示结果的样子，很少用

如何设置无符号和有符号

DROP TABLE IF EXISTS tab_int;
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT(7) UNSIGNED
);

小数

分类：

浮点型

float(M,D)	四字节
double(M,D)	八字节

定点型

dec(M，D)
decimal(M,D)
以上两个一回事儿，最大取值范围与double相同，但是保存的精度要高一些。所以如果需要使用精度较高的小数就应该使用dec。

特点

1. M：整数位数+小数位数，D：小数位数，即整数的位数是M-D。如果超过范围，则插入临界值

2. 如果是decimal类型M和D都可以省略，则M默认为10，D默认为0，如果是float和double，则会根据插入的数值的精度来决定精度

3. 定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用，若没有其他要求可以直接使用float，这样可以节省空间

选择原则

所选择的类型越简单越好，能保存数值的类型越小越好

测试M和D
DROP TABLE tab_float;
CREATE TABLE tab_float(
	f1 FLOAT(5,2),
	f2 DOUBLE(5,2),
	f3 DECIMAL(5,2)
);
SELECT * FROM tab_float;
DESC tab_float;

INSERT INTO tab_float VALUES(123.45,123.45,123.45);
INSERT INTO tab_float VALUES(123.456,123.456,123.456);
如果超过精度，那么四舍五入

INSERT INTO tab_float VALUES(123.4,123.4,123.4);
如果精度不够，后面会补0

INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);
out of range,并没有填入最大值999.99，

字符型

较短的文本：char（M）、varchar（M），M是最多字符数
char固定长度，varchar是可变长度的字符序列

binary和varbinary用于保存较短的二进制
比较

		 写法		M的意思								特点				空间的耗费	效率
char	char(M)		最大的字符数，可以省略，默认为1		   固定长度的字符		比较耗费		高

varchar varchar(M)	最大的字符数，不可以省略			   可变长度的字符		比较节省		低

如何选择：如果确定长度，我们就使用char，否则使用varchar

enum用于保存枚举，不区分大小写，适用于少量、有限的元素类型

CREATE TABLE tab_char(
	c1 ENUM('a','b','c')
);

INSERT INTO tab_char VALUES('a');
INSERT INTO tab_char VALUES('b');
INSERT INTO tab_char VALUES('c');
INSERT INTO tab_char VALUES('m');
插入失败
INSERT INTO tab_char VALUES('A');
插入a

set用于保存集合，不区分大小写，大写会转换到小写，set和ENUM类型类似，里面可以保存0-64个成员，和ENUM类型最大的区别是：SET类型一次可以选取多个成员，而ENUM只能选一个，根据成员个数不同，存储所占的字节也不同

CREATE TABLE tab_set(
	s1 SET('a','b','c','d')
);

INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B'); # 不要有空格啊。。。。
INSERT INTO tab_set VALUES('a,c,d');

较长的文本：
text
blob(较大的二进制)

日期型（必须用单引号引起来）

分类：

date	只保存日期
time	只保存时间
year	只保存年
datetime	保存日期+时间
timestamp	保存日期+时间

datetime和timestamp的差别

1. timestamp支持的时间范围较小
2. timestamp和实际时区有关，更能反映实际的日期，而datetime则只能反映出插入时当地的时区
3. timestamp的属性受MySQL版本和SQLMode的影响很大

总结起来一句话，timestamp更加能够准确反映插入时的时间，并且占用空间较小

测试datetime和timestamp
CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP
);

INSERT INTO tab_date VALUES(NOW(),NOW());

SELECT * FROM tab_date;

显示当前的时区
SHOW VARIABLES LIKE 'time_zone';
修改时区
SET time_zone='+9:00';