Tsai笔记：MySQL学习（2）—— DDL语言（数据库定义语句）

Tsai笔记：MySQL学习（2）—— DDL语言（数据库定义语句）

语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME

一、库的管理（create、alter、drop）

1、创建库（create）

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

#案例：创建表books

CREATE DATABASE IF NOT EXISTS books;

2、修改库（alter）

alter database 库名 character set 字符集名;

3、删除库（drop）

drop database 【if exists】 库名;

#案例：删除表books

DROP DATABASE IF EXISTS books;

二、表的管理

1、创建表（create）

create table 【if not exists】 表名(
    字段名 字段类型 【约束】,
    字段名 字段类型 【约束】,
    。。。
    字段名 字段类型 【约束】 
);

#案例：创建表Book

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

#案例：创建表author

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

2、修改表（alter）- （add、modify、change、drop、rename） 

1.添加列
  alter table 表名 add column 列名 类型 【first|after 字段名】;

#案例：添加列

ALTER TABLE author ADD COLUMN annual DOUBLE; 

2.修改列的类型或约束
   alter table 表名 modify column 列名 新类型 【新约束】;

#案例：修改列的类型或约束

ALTER TABLE author MODIFY COLUMN annual TIMESTAMP;

3.修改列名
   alter table 表名 change column 旧列名 新列名 类型;

#案例：修改列名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;

4 .删除列
   alter table 表名 drop column 列名;

#案例：删除列

ALTER TABLE book_author DROP COLUMN  annual;

5.修改表名
   alter table 表名 rename【to】 新表名;

#案例：修改表名

ALTER TABLE author RENAME TO book_author;

3、删除表（drop）

drop table【if exists】 表名;

#案例：删除表

DROP TABLE IF EXISTS book_author; 

#通用的写法：

DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;
DROP TABLE IF EXISTS 旧表名;
CREATE TABLE  表名();

4、复制表

1.复制表的结构
  create table 表名 like 旧表;

2.复制表的结构+数据
  create table 表名 
  select 查询列表 from 旧表【where 筛选】;

#案例：复制表的结构

CREATE TABLE copy LIKE author;

#案例：复制表的结构+数据

CREATE TABLE copy2 SELECT * FROM author;

#案例：只复制部分数据

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

三、数据类型

1、数值型

a.整型
  tinyint、smallint、mediumint、int/integer、bigint
       1           2                   3              4               8

特点：
①都可以设置无符号和有符号，默认有符号，通过unsigned设置无符号
②如果超出了范围，会报out or range异常，插入临界值
③长度可以不指定，默认会有一个长度
长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill，并且默认变为无符号整型

#1.如何设置无符号和有符号

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

b.小数
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M，D)
decimal(M,D)

#1.测试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);

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

c.字符型

1.较短的文本：
char、varchar
其他：binary和varbinary用于保存较短的二进制、enum用于保存枚举、set用于保存集合
2.较长的文本：
text、blob(较大的二进制)

d.日期型

分类：
1.date只保存日期
2.time 只保存时间
3.year只保存年
4.datetime保存日期+时间
5.timestamp保存日期+时间较短的文本：

四、常见的约束

NOT NULL：非空，该字段的值必填

DEFAULT：默认，该字段的值不用手动插入有默认值

UNIQUE：唯一，该字段的值不可重复

PRIMARY KEY：主键，该字段的值不可重复并且非空  unique+not null

FOREIGN KEY：外键，该字段的值引用了另外的表的字段

CHECK：检查，mysql不支持

1、创建表时添加约束

1.添加列级约束
  语法：直接在字段名和类型后面追加 约束类型即可。（只支持：默认、非空、主键、唯一）

#案例：添加列级约束

CREATE TABLE IF NOT EXISTS stuinfo(
    id INT PRIMARY KEY,#主键
    stuName VARCHAR(20) NOT NULL UNIQUE,#非空
    gender CHAR(1) CHECK(gender='男' OR gender ='女'),#检查
    seat INT UNIQUE,#唯一
    age INT DEFAULT  18,#默认约束
    majorId INT REFERENCES major(id)#外键
);
SHOW INDEX FROM stuinfo;

2.添加表级约束
  语法：在各个字段的最下面
 【constraint 约束名】 约束类型(字段名)#案例：添加表级约束

CREATE TABLE IF NOT EXISTS stuinfo(
    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 ='男' OR gender  = '女'),#检查
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
);
SHOW INDEX FROM stuinfo;

3.通用的写法

CREATE TABLE IF NOT EXISTS stuinfo(
    id INT PRIMARY KEY,
    stuname VARCHAR(20),
    sex CHAR(1),
    age INT DEFAULT 18,
    seat INT UNIQUE,
    majorid INT,
    CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)
);

2、修改表时添加约束

1.添加列级约束
  alter table 表名 modify column 字段名 字段类型 新约束;

#案例：添加列级约束

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE stuinfo(
    id INT,
    stuname VARCHAR(20),
    gender CHAR(1),
    seat INT,
    age INT,
    majorid INT
)
DESC stuinfo;

#1.添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname 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 UNIQUE(seat);

#5.添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id); 

#查看stuinfo中的所有索引，包括主键、外键、唯一

SHOW INDEX FROM stuinfo;

3、修改表时删除约束

#1.删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

#2.删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

#3.删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

#4.删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

#5.删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

#查看stuinfo中的所有索引，包括主键、外键、唯一

SHOW INDEX FROM stuinfo;