DDL：数据定义语言

DDL语言：数据定义语言

库的管理

库的创建

/*
    语法：
        create database [if not exists] 库名;
*/        
# 创建库：books
    CREATE DATABASE books;
    CREATE DATABASE IF NOT EXISTS books;

# utf-8
mysql> create database test_utf8 default charset utf8 collate utf8_general_ci;
Query OK, 1 row affected, 1 warning (0.08 sec)

#gbk
mysql> create database test_gbk default character set gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.06 sec)

库的修改

# 重名名
    RENAME DATABASE books TO 新库名;

# 更改库的字符集， 默认为utf8
    ALTER DATABASE books CHARACTER SET gbk;

库的删除

DROP DATABASE IF EXISTS books;

表的管理

表的创建

/*
格式： 
    CREATE TABLE [IF NOTE EXIST] table_name (
            column_name column_type [（长度） 约束],
            column_name column_type [（长度） 约束],
            ...
            column_name column_type [（长度） 约束],
     );
*/

# 创建表
create table student(
　　stu_id  INT  NOT NULL  AUTO_INCREMENT,
　　name CHAR(32) NOT NULL,
　　age INT NOT NULL,
　　register_data DATE,
　　PRIMARY KEY(stu_id)
);

# 创建表: book
CREATE TABLE book(
      id INT,
      bName VARCHAR(20),
      price DOUBLE,
      authorId INT,
      publishDate DATETIME
);

注：

如果字段不为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
nid int not null defalut 2：当插入数据时如果未主动设置，则自动添加默认值

表的修改

/*
用于修改数据表名或者修改数据表字段。
语法：
            alter table 表名 add|drop|modify|change column 列名 【列类型 约束】；

# 修改列名
    ALTER TABLE book CHANGE COLUMN publishdate pubDate DATATIME;

# 修改列的类型或约束
    ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

# 添加新列
    ALTER TABLE book ADD COLUMN annual DOUBLE;

# 删除列
    ALTER TABLE book DROP COLUMN annual;

# 修改表名
    ALTER TABLE book RENAME TO mybook;

修改列：
　　　　alter table 表名 modify column 列名 类型;            -- 类型
　　　　alter table 表名 change 原列名 新列名 类型;           -- 列名，类型
添加主键： 
　　　　alter table 表名 add primary key(列名);
删除主键： 
　　　　alter table 表名 drop primary key;
　　　　alter table 表名  modify  列名 int, drop primary key;
添加外键：  
　　　　alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
删除外键：  
　　　　alter table 表名 drop foreign key 外键名称
修改默认值：
　　　　ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
删除默认值：
　　　　ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

 ALTER设置默认值和指定not null
mysql> ALTER TABLE testalter_tbl MODIFY j BIGINT NOT NULL DEFAULT 100;
 CHANGE 子句： 在CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
 如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10); #修改字段c的类型为CHAR(10)


使用ALTER添加或删除主键
主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
 
你也可以使用 ALTER 命令删除主键：
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除指定时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
 

使用ALTER删除和添加索引
有四种方式来添加数据表的索引：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):       该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list):  添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
 
以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
 你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
 mysql> ALTER TABLE testalter_tbl DROP INDEX (c);

*/

表的删除

DROP TABLE IF EXISTS book;

清空表

# 清空表：参考表记录的删除
delete from 表名
truncate table 表名

表的复制

# 仅仅复制表的结构
    CREATE TABLE copy LIKE book;

# 复制表的结构+数据
    CREATE TABLE copy2 SELECT * FROM book;

# 只复制部分数据
    CREATE TABLE copy SELECT id,name FROM book WHERE id<100;

# 仅仅复制某些字段
    CREATE TBALE copy4 SELECT id,name FROM book WHERE 0;

常见数据类型

tinyint[(m)] [unsigned] [zerofill]	小整数	用于保存一些范围的整数数值范围：有符号： -128 ～ 127. 无符号：0～ 255。特别的： MySQL中无布尔值，使用tinyint(1)构造。
int[(m)][unsigned][zerofill]	整数	用于保存一些范围的整数数值范围：有符号：-2147483648 ～ 2147483647 无符号：0～ 4294967295 特点：　　　　如果不设置无符号还是有符号，默认是有符号，如果需要设置无符号，需要添加unsigned关键字；　　　　如果插入的数据超出了整数得范围，会报out of range异常，并且插入临界值；　　　　如果不设置长度，会有默认长度；长度代表了显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用。　　　　　　整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002
bigint[(m)][unsigned][zerofill	大整数	数据类型用于保存一些范围的整数数值范围。有符号：-9223372036854775808 ～ 9223372036854775807 无符号：0～ 18446744073709551615
FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]	单精度浮点数	非准确小数值，m是数字总个数，d是小数点后个数；超出了范围，则插入临界值无符号： -3.402823466E+38 to -1.175494351E-38, 1.175494351E-38 to 3.402823466E+38 有符号： 1.175494351E-38 to 3.402823466E+38 ** 数值越大，越不准确 **
DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]	双精度浮点数	非准确小数值，m是数字总个数，d是小数点后个数，超出了范围，则插入临界值无符号： -1.7976931348623157E+308 to -2.2250738585072014E-308 2.2250738585072014E-308 to 1.7976931348623157E+308 有符号：2.2250738585072014E-308 to 1.7976931348623157E+308 ** 数值越大，越不准确 **
decimal[(m[,d])] [unsigned] [zerofill]	准确的小数值	m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，默认是10；d最大值为30，默认为0 特别的：对于精确数值计算时需要用此类型。decaimal能够存储精确值的原因在于其内部按照字符串存储。
char (m)	字符	用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度，默认为1。PS: 即使数据小于m长度，也会占用m长度
varchar(m)		用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡。
text		用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。
mediumtext		A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.
longtext		A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.
enum	枚举类型	An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.) 插入的值必须是列表中的值，不区分大小写。
set	集合类型	A SET column can have a maximum of 64 distinct members. 和Enum类型类似，里面可以保存0~64个成员。但是SET类型一次可以选取多个成员，二Enum只能选取一个。不区分大小写
DATE	日期	YYYY-MM-DD（1000-01-01/9999-12-31），只保存日期
TIME	时间	HH:MM:SS（'-838:59:59'/'838:59:59'），只保存时间
YEAR	年	YYYY（1901/2155），保存年
DATETIME	时间	YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 ），保存日期+时间
TIMESTAMP	时间	YYYYMMDD HHMMSS（19700101080001/2038 年某时），保存日期和时间特点：　　　　timestamp和实际时区有关，更能反映实际的日期，datetime则只能反映出插入时的当地时区；　　　　timestamp的属性受Mysql版本和SQLMode的影响很大。
bit[(M)]		二进制位（101001），m表示二进制位的长度（1-64），默认m＝1

实例

CREATE TABLE shirts (
                    name VARCHAR(40),
                    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
                );

INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

约束

　　用于限制表中的数据，为了保证表中的数据的准确和可靠性。

六大约束

/*
1. NOT NULL
    非空，用于保证该字段值不能为空

2. DEFAULT
    默认值，用于保证该字段有默认值

3. PRIMARY KRY
    主键，用于保证该字段的具有唯一性，并且非空；一种特殊的唯一索引，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。
    
4. UNIQUE
    唯一，用于保证该字段的值具有唯一性，可以为空

5. CHECK
    检查约束【mysql中不支持，也不报错，没实际意义】
 
6. FOREIGN KEY
    外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值；
　　     一个特殊的索引，只能是指定内容。用于与另一张表的关联。是能确定另一张表记录的字段，用于保持数据的一致性。
*/

添加约束

创建表时
修改表时

约束添加分类

列级约束：六大约束语法上都支持，但是外键约束没有效果
表级约束：除了非空、默认，其他的都支持

创建表时添加约束

/*
添加列级约束， 外键不生效，也不报错
语法：
    直接在字段名 和类型后面追加 约束类型即可
    只支持： 默认，非空，主键，唯一
*/
CREATE TABLE student(
    id INT PRIMARY KEY,
    stuName VARCHAR(20) NOT NULL,
    gender  CHAR(1) CHECK(gender='M' OR gender='F'),
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorId INT REFERENCES major(id)   # 外键
);
CREATE TABLE major(
    id INT PRIMARY KEY,
    majorName VARCHAR(20)
);
create table tb1(
                nid int not null auto_increment primary key,
                num int null )

create table tb1(
                nid int not null,
                num int not null,
                primary key(nid,num) 
/*
添加表级约束
语法：
    在各个字段的最下面，追加
    [CONSTRAINT 约束名] 约束类型（字段名）
*/
DROP TABLE IF EXISTS student;
CREATE TABLE student(
    id INT,
    stuName VARCHAR(20),
    gender  CHAR(1),
    seat INT,
    age INT,
    majorId INT,   

    CONSTRAINT pk PRIMARY KEY (id),
    CONSTRAINT uq UNIQUE(seat),
    CONSTRAINT ck CHECK(gender='M' OR gender='F'),
    CONSTRAINT fk_student_major FOREIGN KEY(majorid) REFERENCES major(id)
);

DESC student;
SHOW INDEX FROM student;

# 通用写法
CREATE TABLE student(
    id INT PRIMARY KEY,
    stuName VARCHAR(20) NOT NULL,
    gender  CHAR(1) CHECK(gender='M' OR gender='F'),
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorId INT，   
    CONSTRAINT fk_student_major FOREIGN KEY(majorid) REFERENCES major(id)
);

修改表时添加约束

/*
添加列级约束
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 新约束

添加表级约束
ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型（字段名） [外键的引用] 

*/


# 添加非空约束
ALTER TABLE student MODIFY COLUMN stuName VARCHAR(20) NOT NULL;

# 添加默认约束
ALTER TABLE student MODIFY COLUMN age INT DEFAULT 18;

# 添加主键
ALTER TABLE student MODIFY COLUMN  id INT PRIMARY KEY;
ALTER TABLE student ADD PRIMARY KEY(id);

# 添加唯一键
ALTER TABLE student MODIFY COLUMN seat INT UNIQUE;
ALTER TABLE student ADD UNIQUE(seat);

# 添加外键
ALTER TABLE student ADD [CONSTRAINT fk_student_major] FOREIGN KEY(majorid) REFERENCES major(id);

修改表时删除约束

/*
删除非空约束
ALTER TABLE student MODIFY COLUMN stuName VARCHAR(20) NULL;

删除默认约束
ALTER TABLE student MODIFY COLUMN age INT;

删除主键
ALTER TABLE student DROP PRIMARY KEY;

删除唯一
ALTER TABLE student DROP INDEX seat;

删除外键
ALTER TABLE student DROP FROEING KEY majorid;

*/

主键和唯一

	唯一性	是否允许为空	一个表中可以有多少个	是否允许组合
主键	是	否	最多有一个	可以，但不推荐
唯一	是	是	可以有多个	可以，但不推荐

外键

在从表设置外键关系
从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
主表的关联列必须是一个key（一般是主键或唯一）
插入数据时，先插入主表，在插入从表
删除数据是，先删除从表，再删除主表
两个表必须是InnoDB表，MyISAM表暂时不支持外键
外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引
外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；
可以使得两张表关联，保证数据的一致性和实现一些级联操作；

/*
[CONSTRAINT symbol] FOREIGN KEY [id] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]
    [ON UPDATE {RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT}]

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用，如果不指定CONSTRAINT symbol，MYSQL会自动生成一个名字。
　　ON DELETE、ON UPDATE表示事件触发限制，可设参数：
　　RESTRICT （限制外表中的外键改动）
　　CASCADE （跟随外键改动）
　　SET NULL （设空值）
　　SET DEFAULT （设默认值）
　　NO ACTION （无动作，默认的）
*/

实例

creat table color(
                nid int not null primary key,
                name char(16) not null
            )

 create table fruit(
                nid int not null primary key,
                smt char(32) null ,
                color_id int not null,
                constraint fk_cc foreign key (color_id) references color(nid)
            )
/*
注意：
1、对于自增列，必须是索引（含主键）。
2、对于自增可以设置步长和起始值
        show session variables like 'auto_inc%';
        set session auto_increment_increment=2;
        set session auto_increment_offset=10;
        show global  variables like 'auto_inc%';
        set global auto_increment_increment=2;
        set global auto_increment_offset=10;
*/
mysql> create table stu( id int not null auto_increment primary key, name char not null);
Query OK, 0 rows affected (0.67 sec)

mysql> create table class( class_num int not null auto_increment primary key, teacher char not null)ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected, 1 warning (0.17 sec)

标识列

/*
标识列又称自增长列
    可以不用手动的插入值，系统提供默认的序列值
特点：
    1. 必须和一个KEY搭配；主键或者唯一
    2. 一个表最多只能有一个自增列
    3. 标识列必须是数值型
    4. 可以通过SET auto_increment_increment=3;设置步长
    5. 可以通过手动插入值设置起始值

*/
# 创建表时设置标识列
CREATE TABLE major(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20),
)

INSERT INTO major VALUES(NULL, 'jhon');
INSERT INTO major(name) VALUES('jhon');  

SHOW VARIABLES LIKE '%auto_increment%';

# 修改表时设置标识列
ALTER TABLE major MODIFY COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

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


# 设置自增步长, 默认是1
SET auto_increment_increment=3;

删除含外键的记录

# 方式一： 级联删除： 删除主表记录，从表记录对应的记录也会被删除
ALTER TABLE student ADD CONSTRAINT fk_student_major FROEIGN KEY(major_id) REFERENCES major(id) ON DELETE CASCADE;

# 方式二：级联置空： 删除主表记录，从表关联的字段设置为NULL
ALTER TABLE student ADD CONSTRAINT fk_student_major FROEIGN KEY(major_id) REFERENCES major(id) ON DELETE SET NULL;

参考

外键参考：http://www.cnblogs.com/programmer-tlh/p/5782451.html

https://blog.csdn.net/duck_arrow/article/details/8264686

https://www.cnblogs.com/laumians-notes/p/9069498.html

http://www.cnblogs.com/alex3714/articles/5950372.html

https://www.cnblogs.com/programmer-tlh/p/5782418.html

mysql练习题： http://www.cnblogs.com/wupeiqi/articles/5729934.html 　　

mysql知识： http://www.cnblogs.com/wupeiqi/articles/5713323.html