#DDL

/*

数据定义语言

库和表的管理

一、库的管理

	创建、修改、删除

二、表的管理

	创建、修改、删除

创建 create

修改 alter

删除 drop

*/

#一、库的管理

#1、库的创建

/*

CREATE DATABASE 库名;

*/

CREATE DATABASE books;

SHOW DATABASES;

#如果没有再创建

CREATE DATABASE IF NOT EXISTS books;

#2、库的修改

/*

一般地，不对库做修改

*/

#更改库的字符集

ALTER DATABASE books CHARACTER SET gbk;

#3、库的删除

DROP DATABASE IF EXISTS books;

#二、表的管理

#1、表的创建

/*

CREATE TABLE 表名(

	列名 列的类型 【长度，约束】,

    列名 列的类型 【长度，约束】,

    ...

    列名 列的类型 【长度，约束】

)

*/

USE books;

#案例1:创建book表

CREATE TABLE book(

	id int,

    bname varchar(50),

    price double,

    author_id int,

    publish_date datetime

);

CREATE TABLE author(

	id int,

    au_name varchar(20),

    nation varchar(20)

);

#2、表的修改

/*

ALTER TABLE 表名 change|modify|add|drop|rename COLUMN 列名 【列类型 约束】

*/

#修改列名

ALTER TABLE book CHANGE COLUMN publish_date pub_date DATETIME;

#修改列的类型或约束

ALTER TABLE book MODIFY COLUMN pub_date TIMESTAMP;

#添加新列

ALTER TABLE author ADD COLUMN annual DOUBLE;

#删除列

ALTER TABLE author DROP COLUMN annual;

#修改表名

ALTER TABLE author RENAME TO book_author;

DESC book;

#3、表的删除

DROP TABLE IF EXISTS book_author;

#4、表的复制

INSERT INTO book_author

VALUES(1, '莫言', '中国'), (2, '村上春树', '日本');

#仅仅复制表的结构

CREATE TABLE copy LIKE book_author;

#复制表的结构+数据

CREATE TABLE copy2

SELECT * FROM book_author;

#只复制部分数据

CREATE TABLE copy3

SELECT id, au_name

FROM book_author

WHERE nation='中国';

#只复制部分字段

#设置一个不等条件

CREATE TABLE copy4

SELECT id, au_name

FROM book_author

WHERE 0;

#常见数据类型

/*

数值型：

	整型

	小数：定点数、浮点数

字符型：

	较短的文本：char, varchar

    较长的文本：text, blob(较长的二进制数据)

日期型

*/

#1、整型

#tinyint, smallint, mediumint, int/integer, bigint

#默认有符号；无符号需追加unsigned

#插入数值超过范围会报warning，并且插入临界值

#长度代表显示的宽度，不设置长度会有默认值（zerofill，不够宽度用0填充）

DROP TABLE IF EXISTS tab_int;

CREATE TABLE tab_int(

	t1 int,

    t2 int unsigned

);

INSERT INTO tab_int(t1)

VALUES(-123);   #能够插入，说明int默认有符号

#2、浮点型

#float(M, D) 最常用, double(M, D)

#3、定点型

#decimal(M, D)  精度较高

/*

M:整数部位+小数部位

D:小数部位

如果超过范围，插入临界值

*/

#4、字符型

#较短文本：

#固定长度的字符：var(M)，效率高；可变长度的字符：varchar(M)，节省空间，效率低；M：最多的字符数

#binary/varbinary 保存较短的二进制；enum 保存枚举；set 保存集合

#较长文本：

#text, blob（图片等较大的二进制）

#5、日期型

#日期值必须用单引号引起来

#date, datetime, timestamp, time, year

#timestamp的属性和实际时区有关，更能反应实际的日期

CREATE TABLE tab_date(

	t1 datetime,

    t2 timestamp

);

INSERT INTO tab_date

VALUES(NOW(), NOW());

SELECT * FROM tab_date;

#常见约束

/*

含义：一种限制，用于限制表中的数据

六大约束：

	NOT NULL: 非空，用于保证该字段的值不能为空。比如姓名、学号等

    DEFAULT: 默认，用于保证该字段有默认值。比如性别，

	PRIMARY KEY: 主键，用于保证该字段的值具有唯一性，且非空。比如学号、编号等

    UNIQUE: 唯一性，可以为空

    CHECK: 检查约束【mysql中不支持】

    FOREIGN KEY: 外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列

    比如学生表的专业编号，员工表的部门编号，员工表的工种编号

一般在设计表的时候添加约束

CREATE TABLE 表名(

	字段名 字段类型 列级约束1 列级约束2,

    字段名 字段类型 列级约束,

    ...,

    表级约束

);

*/

CREATE DATABASE students;

USE students;

#添加列级约束

CREATE TABLE stuinfo(

	id int primary key,

    stu_name varchar(20) not null,

    gender char(1) check(gender='男' or gender='女'),

    seat int unique,

    age int default 18,

    major_id int

);

CREATE TABLE major(

	id int primary key,

    major_name varchar(20)

);

DESC stuinfo;

#添加表级约束

#【constraint 约束名】 约束类型(字段名)

DROP TABLE IF EXISTS stuinfo;

CREATE TABLE IF NOT EXISTS stuinfo(

	id int,

    stu_name varchar(20) not null,

    gender char(1),

    seat int,

    age int default 18,

    major_id int,

    constraint pk primary key(id),

    constraint uq unique(seat),

    constraint ck check(gender='男' or gender='女'),

    constraint fk_stuinfo_major foreign key(major_id) references major(id)

);

SHOW INDEX FROM stuinfo;

/*

主键和唯一的对比：

	都具有唯一性；

	主键不允许为空、唯一允许为空；

    一个表中最多只能有一个主键；

    都允许组合，但都不推荐

外键：

	1、要求在从表设置外键关系

    2、从表的外键列的类型和主表的关联列的类型要一致或兼容

    3、主表的关联列必须是一个key（一般是主键或唯一）

    4、插入数据时，先插入主表，再插入从表；删除数据时，先删除从表，再删除主表

*/

#修改表时添加约束

DROP TABLE IF EXISTS stuinfo;

CREATE TABLE IF NOT EXISTS stuinfo(

	id int,

    stu_name varchar(20),

    gender char(1) check(gender='男' or gender='女'),

    seat int unique,

    age int,

    major_id int

);

#1、添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stu_name varchar(20) not null;

#2、添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

#3、添加主键

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

# ALTER TABLE stuinfo ADD PRIMARY KEY(id);

#4、添加唯一

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

# ALTER TABLE stuinfo ADD UNIQUEY(id);

#5、添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_major_id

FOREIGN KEY(major_id) REFERENCES major(id);

#修改表时删除约束

#和添加约束语法一致，把后面的约束去掉再执行一次就行了

#删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

#删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

#删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_major_id;