mysql 4核_Mysql核心（四）——DDL语言

DDL数据定义语言——库和表的管理

库的管理

创建、修改、删除

1、创建库

create database 【if not exists】 库名【 character set 字符集名】;

2、修改库

alter database 库名 character set 字符集名;

3、删除库

drop database 【if exists】 库名;

#案例：创建库BooksCREATE DATABASE IF NOT EXISTSbooks ;

#2、库的修改

RENAMEDATABASE books TO新库名;

#更改库的字符集ALTER DATABASE books CHARACTER SETgbk;

#3、库的删除DROP DATABASE IF EXISTS books;

表的管理

创建、修改、删除

创建： create

修改： alter

删除： drop

一、创建表 ★

create table 【if not exists】 表名(

字段名 字段类型 【约束】,

字段名 字段类型 【约束】,

。。。

字段名 字段类型 【约束】

)

1 #案例：创建表Book2 CREATE TABLEbook(3     id INT,#编号4     bName VARCHAR(20),#图书名5     price DOUBLE,#价格6     authorId INT,#作者编号7     publishDate DATETIME#出版日期8

9

10 );11

12 DESCbook;13 #案例：创建表author14 CREATE TABLE IF NOT EXISTSauthor(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 COLUMNannual;

#⑤修改表名ALTER TABLE author RENAME TObook_author;DESC book;

三、删除表

drop table【if exists】 表名;

#3.表的删除DROP TABLE IF EXISTSbook_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 * FROMAuthor;SELECT * FROMcopy2;

#1.仅仅复制表的结构CREATE TABLE copy LIKEauthor;

#2.复制表的结构+数据CREATE TABLEcopy2SELECT * FROMauthor;

#只复制部分数据CREATE TABLEcopy3SELECTid,au_nameFROMauthorWHERE nation='中国';

#仅仅复制某些字段CREATE TABLEcopy4SELECTid,au_nameFROMauthorWHERE 0;

【案例讲解】库和表的管理

1. 创建表dept1

NAME

NULL?

TYPE

id

YES

INT(7)

NAME

YES

VARCHAR(25)

USEtest;CREATE TABLEdept1(

idINT(7),

NAMEVARCHAR(25)

);

2. 将表departments中的数据插入新表dept2中

CREATE TABLEdept2SELECTdepartment_id,department_nameFROM myemployees.departments;

3. 创建表emp5

NAME

NULL?

TYPE

id

INT(7)

first_name

VARCHAR(25)

last_name

VARCHAR(25)

dept_id

INT(7)

CREATE TABLEemp5(

idINT(7),

first_nameVARCHAR(25),

last_nameVARCHAR(25),

dept_idINT(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

DESCemp5;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 EXISTStab_int;CREATE TABLEtab_int(

t1INT(7) ZEROFILL,

t2INT(7) ZEROFILL

);DESCtab_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和DDROP TABLEtab_float;CREATE TABLEtab_float(

f1FLOAT,

f2DOUBLE,

f3DECIMAL);SELECT * FROMtab_float;DESCtab_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 TABLEtab_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 * FROMtab_set;CREATE TABLEtab_set(

s1SET('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 TABLEtab_date(

t1DATETIME,

t2TIMESTAMP);INSERT INTO tab_date VALUES(NOW(),NOW());SELECT * FROMtab_date;

SHOW VARIABLESLIKE 'time_zone';SET time_zone='+9:00';

标识列

#标识列/*又称为自增长列

含义：可以不用手动的插入值，系统提供默认的序列值

特点：

1、标识列必须和主键搭配吗？不一定，但要求是一个key

2、一个表可以有几个标识列？至多一个！

3、标识列的类型只能是数值型

4、标识列可以通过 SET auto_increment_increment=3;设置步长

可以通过 手动插入值，设置起始值*/#一、创建表时设置标识列DROP TABLE IF EXISTStab_identity;CREATE TABLEtab_identity(

idINT,

NAMEFLOAT UNIQUEAUTO_INCREMENT,

seatINT);TRUNCATE TABLEtab_identity;INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');INSERT INTO tab_identity(NAME) VALUES('lucy');SELECT * FROMtab_identity;

SHOW VARIABLESLIKE '%auto_increment%';SET auto_increment_increment=3;

#二、修改表时设置标识列ALTER TABLE tab_identity MODIFY COLUMN id INT PRIMARY KEYAUTO_INCREMENT;

#三、修改表时删除标识列ALTER TABLE tab_identity MODIFY COLUMN id INT;