Mysql学习五 DDL和数据类型

DDL

/*
数据定义语言
库和表的管理
一、库的管理
创建、修改、删除
二、表的管理
创建、修改、删除

创建：create
修改：alter
删除：drop
*/

一、库的管理

库的创建

/*
语法：create database [if not exists] 库名；
*/

CREATE  DATABASE books;

库的修改（不安全）

###更改库的字符集

ALTER DATABASE books CHARACTER SET gbk;

库的删除

DROP DATABASE IF EXISTS books;

二、表的管理

表的创建

/*

create table 表名（
	列名 列的类型【（长度）约束】，
	列名 列的类型【（长度）约束】，
	列名 列的类型【（长度）约束】，
	……
	）；

*/
#创建book表

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

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

表的修改

/*
语法：

alter table 表名 add|drop|modify|change column 列名 （列类型 约束）

*/

修改列名

ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
DESC book;

修改列的类型或约束

ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP;

添加新列

ALTER TABLE author ADD	COLUMN annual	DOUBLE;
DESC author;#表的结构

删除列

ALTER TABLE author DROP COLUMN annual;

修改表名

ALTER TABLE author RENAME TO book_author;

表的删除

DROP TABLE IF EXISTS book_author;
SHOW TABLES;

表的复制

INSERT INTO author VALUES
(1,'村上春树','日本'),
(2,'冯唐','中国'),
(3,'莫言','中国'),
(4,'金庸','中国');

####仅仅复制表的结构

CREATE TABLE copy LIKE author;

####复制表的结构和数据

CREATE TABLE copy2
SELECT * FROM author; 

####只复制部分数据

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

####只复制部分结构

CREATE TABLE copy4
SELECT id,au_name
FROM 	author
WHERE	0;

常见的数据类型

/*
默认有符号
插入数值超出范围，默认插入临界值
如果不设置长度，会有默认的长度
数值型：
整型
小数
定点数
浮点数
字符型
较短的：char varchar
较长的 text blod(较长的二进制数据)
日期型
*/

整型

/*
分类：
tinyint 1、smallint 2、mediumint 3、int/integer 4、bigint 8
特点：
*/
#如何设置无符号和有符号

CREATE TABLE tab_int(
	 id  INT,
	 t2  INT UNSIGNED  
);

小数

/*
分类：
浮点型
float（M，D）
double(M,D)
定点型
dec（M,D）
decimal(M,D)
M:整数+小数总的位数
D:小数点后的位数
超过则插入临界值
如果是decimal，则M默认是10，D默认是0
如果是float和double，则会根据插入的数值的精度来决定精度
定点型的精确度较高
*/

CREATE TABLE tab_float(
	f1 FLOAT(5,2),
	f2 DOUBLE(5,2),
	f3 DECIMAL(5,2)
);

字符型

/*
较短的文本
char（长度默认为1）
varchar
enum
较长的文本
text
blob（较大的二进制）
*/

enum 插入只能从a,b,c中选，不分大小写

CREATE TABLE tab_char(
	c1 ENUM('a','b','c')
);

set 集合 可一次插入多个set集合中的

CREATE TABLE tab_set(
	s1 SET('a','b','c','d')
);

日期型

/*
*/

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 DATABASE students;
USE students;
CREATE TABLE stuinfo(
	id INT PRIMARY KEY,
	stuname VARCHAR(20) NOT NULL,
	gender CHAR(1) CHECK(gender='男'OR gender='女'),
	seat INT UNIQUE,
	age INT DEFAULT 18,
	majorId INT  REFERENCES major(id)
);
CREATE TABLE major(
	id INT PRIMARY KEY,
	majorName VARCHAR(20)
);

#添加表级约束
/*
语法： 在各个字段的最下面
【constraint 约束名】 约束类型（字段名）
*/

DROP TABLE IF EXISTS stuinfo;
CREATE TABLE 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 FOREIGN KEY(majorId) REFERENCES major(id)
);
SHOW INDEX FROM stuinfo;

修改表时添加约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

修改表时删除约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20);

#删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

#删除唯一

ALTER TABLE stuinfo DROP INDEX uq;

#删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk;

标识列（自增长列）

/*
一个表中至多有一个标识列
可以通过set auto_increment_increment设置步长
*/
###创建表时设置标识列

CREATE TABLE tab_identity(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO tab_identity VALUES(NULL,'john');
SET auto_increment_increment=1;