Mysql基础2

基础2

DDL（Data Definition Language）语言

DDL语言主要功能：库和表的管理。

库的管理

# 1. 创建库：
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>]

CREATE DATABASE IF NOT EXISTS books CHARACTER SET utf8;
# 2. 修改数据库字符集
ALTER DATABASE books CHARACTER SET gbk;
# 3. 删除数据库
DROP DATABASE IF EXISTS books;

表的管理

1. 创建表

创建表语法：
CREATE TABLE 表名(
	列名 列类型【(列长度) 约束】,
	列名 列类型【(列长度) 约束】,
	...
	列名 列类型【(列长度) 约束】
)

CREATE TABLE book(
	id INT,
	bname VARCHAR(20),
	price DOUBLE,
	authorId INT,
	publishDate DATETIME
);

CREATE TABLE author(
	id INT,
	au_name VARCHAR(20),
	nation VARCHAR(10)
);

2. 表修改

修改表语法：
ALTER TABLE 表名 CHANGE|MODIFY|ADD|DROP|RENAME 列名 【列类型 约束】;

# 1. 修改列名
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
# 2. 修改列的类型或约束
ALTER TABLE book MODIFY COLUMN pubDate DATE;
# 3. 添加新列
ALTER TABLE book ADD COLUMN updatetime DATE;
# 4. 删除列
ALTER TABLE book DROP COLUMN updatetime;
# 5. 修改表名
ALTER TABLE book RENAME TO books;

3. 表删除

# 表删除
DROP TABLE IF EXISTS books;

4. 表复制

# 仅复制表结构
CREATE TABLE copy_books LIKE books;
# 复制表结构+筛选数据
CREATE TABLE copy_author (SELECT * FROM author);
# 仅复制部分表结构
CREATE TABLE copy_author1 (SELECT id,au_name FROM author WHERE 0);

数据类型

数值型： 整型、小数（定点数、浮点数）
字符型： 较短的文本（char、varchar）、较长的文本（text）、二进制（blob）
日期型：date、time、datetime

整型

分类：tinyint、smallint、mediumint、int/integer、bigint

# 1. 有符号和无符号---UNSIGNED
CREATE TABLE tab_int(
	t1 INT,
	t2 INT UNSIGNED
);

t2是无符号数，插入负数会直接报错：
INSERT INTO tab_int VALUES(-1111, -11111);

插入超范围的数值也会报错：
INSERT INTO tab_int VALUES(2147483648, 4294967296);

# 2. int默认宽度是11，int unsigned宽度是10，可以在创建表时指定宽度。
CREATE TABLE tab_int(
	t1 INT(7) ZEROFILL,
	t2 INT(7) ZEROFILL
);
这里的宽度需要搭配zerofill使用，作用是如果不够7位前面补零，并且使用了zerofill默认就是无符号数。

小数

定点型精度相对较高，如果对数据要求精度较高则使用定点型，如：货币运算。

1. 浮点型
float(M,D)
double(M,D)

2. 定点型
dec(M,D)
decimal(M,D)

M：整数+小数 总共M位。
D：表示小数点后保留D位。

CREATE TABLE tab_int(
	t1 FLOAT(5,2),
	t2 DOUBLE(5,2),
	t3 DEC(5,2)
);

小数部分超出范围后会四舍五入保留D位小数。
INSERT INTO tab_int VALUES(214.235, 214.235, 214.235);
如下会报错超出范围，因为整数部分占了4位，小数部分需要占2位。
INSERT INTO tab_int VALUES(1214.2, 1214.2, 1214.2);

CREATE TABLE tab_int(
	t1 FLOAT,
	t2 DOUBLE,
	t3 DEC
);
如果不指定范围，默认float和double精度是根据插入的值决定，dec精度为(10,0)
插入如下数据，由于dec(10,0)会导致告警，但是能正常插入，只保留整数部分。
INSERT INTO tab_int VALUES(214.23, 214.23, 214.23);

字符型

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

enum类型：

枚举类型：
CREATE TABLE tab_char(
	c1 ENUM("a", "b")
);

INSERT INTO tab_char VALUES("a");
插入"c"会报失败
INSERT INTO tab_char VALUES("c");
插入大写"A"不会报错，直接转换为"a"保存
INSERT INTO tab_char VALUES("A");

binary和varbinary类型：

Set类型：

CREATE TABLE tab_set(
	c1 SET("a", "b", "c")
);
INSERT INTO tab_set VALUES("a");
INSERT INTO tab_set VALUES("a,b");

日期型

1. Timestamp占空间小支持的时间范围较小，取值范围：19700101080001——2038年的某个时间；
   Datetime的取值范围：1000-1-1 —— 9999—12-31
2. timestamp和实际时区有关，更能反映实际的日期，而datetime则只能反映出插入时的当地时区
3. timestamp的属性受Mysql版本和SQLMode的影响很大
   

CREATE TABLE tab_time(
	t1 DATETIME,
	t2 TIMESTAMP
);
插入当前时间
INSERT INTO tab_time VALUES(NOW(), NOW());
SELECT * FROM tab_time;

查询当前时区
SHOW VARIABLES LIKE 'time_zone';

修改时区后查询
SET time_zon