Mysql核心（四）——DDL语言
DDL数据定义语言——库和表的管理
库的管理
创建、修改、删除
1、创建库
create database 【if not exists】 库名【 character set 字符集名】;
2、修改库
alter database 库名 character set 字符集名;
3、删除库
drop database 【if exists】 库名;
#案例：创建库Books CREATE DATABASE IF NOT EXISTS books ; #2、库的修改 RENAME DATABASE books TO 新库名; #更改库的字符集 ALTER DATABASE books CHARACTER SET gbk; #3、库的删除 DROP DATABASE IF EXISTS books;
表的管理
创建、修改、删除
创建： create
修改： alter
删除： drop
一、创建表 ★
create table 【if not exists】 表名(
字段名 字段类型 【约束】,
字段名 字段类型 【约束】,
。。。
字段名 字段类型 【约束】
)
1 #案例：创建表Book 2 CREATE TABLE book( 3 id INT,#编号 4 bName VARCHAR(20),#图书名 5 price DOUBLE,#价格 6 authorId INT,#作者编号 7 publishDate DATETIME#出版日期 8 9 10 ); 11 12 DESC book; 13 #案例：创建表author 14 CREATE TABLE IF NOT EXISTS author( 15 id INT, 16 au_name VARCHAR(20), 17 nation VARCHAR(10) 18 ) 19 DESC author;
二、修改表
1.添加列
alter table 表名 add column 列名 类型 【first|after 字段名】;
2.修改列的类型或约束
alter table 表名 modify column 列名 新类型 【新约束】;
3.修改列名
alter table 表名 change column 旧列名 新列名 类型;
4 .删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 【to】 新表名;
/* 语法 alter table 表名 add|drop|modify|change column 列名 【列类型 约束】; */ #①修改列名 ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME; #②修改列的类型或约束 ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP; #③添加新列 ALTER TABLE author ADD COLUMN annual DOUBLE; #④删除列 ALTER TABLE book_author DROP COLUMN annual; #⑤修改表名 ALTER TABLE author RENAME TO book_author; DESC book;
三、删除表
drop table【if exists】 表名;
#3.表的删除 DROP TABLE IF EXISTS book_author; SHOW TABLES; #通用的写法： DROP DATABASE IF EXISTS 旧库名; CREATE DATABASE 新库名; DROP TABLE IF EXISTS 旧表名; CREATE TABLE 表名();
四、复制表
1、复制表的结构
create table 表名 like 旧表;
2、复制表的结构+数据
create table 表名
select 查询列表 from 旧表【where 筛选】;
#4.表的复制 INSERT INTO author VALUES (1,'村上春树','日本'), (2,'莫言','中国'), (3,'冯唐','中国'), (4,'金庸','中国'); SELECT * FROM Author; SELECT * FROM copy2; #1.仅仅复制表的结构 CREATE TABLE copy LIKE author; #2.复制表的结构+数据 CREATE TABLE copy2 SELECT * FROM author; #只复制部分数据 CREATE TABLE copy3 SELECT id,au_name FROM author WHERE nation='中国'; #仅仅复制某些字段 CREATE TABLE copy4 SELECT id,au_name FROM author WHERE 0;
【案例讲解】库和表的管理
1. 创建表dept1
| NAME | NULL? | TYPE |
| id | YES | INT(7) |
| NAME | YES | VARCHAR(25) |
USE test; 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
| NAME | NULL? | TYPE |
| id | INT(7) | |
| first_name | VARCHAR(25) | |
| last_name | VARCHAR(25) | |
| dept_id | INT(7) |
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.在表dept和emp5中添加新列test_column，并检查所作的操作
ALTER TABLE emp5 ADD COLUMN test_column INT;
9.直接删除表emp5中的列 dept_id
DESC emp5; ALTER TABLE emp5 DROP COLUMN test_column;
数据类型
数值型：
整型
小数：定点数、浮点数
字符型：
较短的文本：char、varchar
较长的文本：text、blob（较长的二进制数据）
日期型
一、整型
分类：
|
整数类型 |
字节 |
范围 |
|
tinyint |
1 |
有符号：-128～127 无符号：0～255 |
|
smallint |
2 |
有符号：-32768～32767 无符号：0～65535 |
|
mediumint |
3 |
有符号：-8388608～8388607 无符号：0～1677215 |
|
Int/Integer |
4 |
有符号：-2147483648～2147483647 无符号：0～4294967295 |
|
bigint |
8 |
很大，不用记 |
特点：
① 如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
② 如果插入的数值超出了整型的范围,会报out of range异常，并且插入临界值
③ 如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用，并且默认变为无符号整型！
#1.如何设置无符号和有符号 DROP TABLE IF EXISTS tab_int; CREATE TABLE tab_int( t1 INT(7) ZEROFILL, t2 INT(7) ZEROFILL ); DESC tab_int; INSERT INTO tab_int VALUES(-123456); INSERT INTO tab_int VALUES(-123456,-123456); INSERT INTO tab_int VALUES(2147483648,4294967296); INSERT INTO tab_int VALUES(123,123); SELECT * FROM tab_int;
二、小数
分类：
|
浮点数类型 |
字节 |
范围 |
|
float(M,D) |
4 |
|
|
double(M,D) |
8 |
|
|
定点数类型 |
字节 |
范围 |
|
dec(M，D) |
M+2 |
最大取值范围与double相同，给定decimal的有效取值范围由M和D决定 |
|
decimal(M,D) |
M+2 |
|
特点：
①
M：整数部位+小数部位 / 小数点前+小数点后的位数加起来是M位
D：小数部位
如果超过范围，则插入临界值
②
M和D都可以省略
如果是decimal，则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
③定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
#测试M和D DROP TABLE tab_float; CREATE TABLE tab_float( f1 FLOAT, f2 DOUBLE, f3 DECIMAL ); SELECT * FROM tab_float; DESC tab_float; INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523); INSERT INTO tab_float VALUES(123.456,123.456,123.456); INSERT INTO tab_float VALUES(123.4,123.4,123.4); INSERT INTO tab_float VALUES(1523.4,1523.4,1523.4);
原则上是：所选择的类型越简单越好，能保存数值的类型越小越好
三、字符型
较短的文本：
char
varchar
其他：
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
较长的文本：
text
blob(较大的二进制)
特点：
|
|
写法 |
M的意思 |
特点 |
空间的耗费 |
效率 |
|
char |
char(M) |
最大的字符数，可以省略，默认为1 |
固定长度的字符 |
比较耗费 |
高 |
|
varchar |
varchar(M) |
最大的字符数，不可以省略 |
可变长度的字符 |
比较节省 |
低 |
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'); SELECT * FROM tab_set; 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');
四、日期型
|
日期和时间类型 |
字节 |
最小值 |
最大值 |
|
date |
4 |
1000-01-01 |
9999-12-31 |
|
datetime |
8 |
1000-01-01 00:00:00 |
9999-12-31 23:59:59 |
|
timestamp |
4 |
19700101080001 |
2038年的某个时刻 |
|
time |
3 |
-838:59:59 |
838:59:59 |
|
year |
1 |
1901 |
2155 |
分类：
date只保存日期
time 只保存时间
year只保存年
datetime保存日期+时间
timestamp保存日期+时间 比较容易受时区、语法模式、版本的影响，更能反映当前时区的真实时间
特点：
|
类型 |
字节 |
范围 |
时区等的影响 |
|
datetime |
8 |
1000——9999 |
不受 |
|
timestamp |
4 |
1970-2038 |
受 |
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';
标识列
#标识列 /* 又称为自增长列 含义：可以不用手动的插入值，系统提供默认的序列值 特点： 1、标识列必须和主键搭配吗？不一定，但要求是一个key 2、一个表可以有几个标识列？至多一个！ 3、标识列的类型只能是数值型 4、标识列可以通过 SET auto_increment_increment=3;设置步长 可以通过 手动插入值，设置起始值 */ #一、创建表时设置标识列 DROP TABLE IF EXISTS tab_identity; CREATE TABLE tab_identity( id INT , NAME FLOAT UNIQUE AUTO_INCREMENT, seat INT ); TRUNCATE TABLE tab_identity; INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john'); INSERT INTO tab_identity(NAME) VALUES('lucy'); SELECT * FROM tab_identity; SHOW VARIABLES LIKE '%auto_increment%'; SET auto_increment_increment=3; #二、修改表时设置标识列 ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT; #三、修改表时删除标识列 ALTER TABLE tab_identity MODIFY COLUMN id INT;
