4DDL语言

最新推荐文章于 2023-11-09 09:44:50 发布

最佳第六六六人

最新推荐文章于 2023-11-09 09:44:50 发布

阅读量156

点赞数

分类专栏：数据库_01_MySQL 文章标签：数据库 mysql java

本文链接：https://blog.csdn.net/qq_43523503/article/details/108924686

版权

数据库_01_MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

DDL语言的学习

==DDL（Data Define Language）==数据定义语言

库和表的管理

创建 create
修改 alter
删除 drop

1库的管理

库存放路径C:\ProgramData\MySQL\MySQL Server 5.7\Data

#案例：1、创建库Books
CREATE DATABASE IF NOT EXISTS books;

#2、库的修改
RENAME DATABASE books TO 新库名;	# 一般不会修改库的信息

#更改库的字符集（改也就只可能改字符集）

ALTER DATABASE books CHARACTER SET gbk;

#3、库的删除
DROP DATABASE IF EXISTS books;

2表的管理

①表的创建

语法：
create table 表名(
列名列的类型【(长度) 约束】,
列名列的类型【(长度) 约束】,
列名列的类型【(长度) 约束】,
…
列名列的类型【(长度) 约束】

)

#案例：创建表Book
CREATE TABLE book (
	id INT, # 编号
	bName VARCHAR(20), # 书名
	price DOUBLE, # 价格
	authorID INT, # 作者编号
	publishDate DATETIME # 出版日期
);
DESC book;	# 查看表的描述

#案例：创建表author
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;

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

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

#④删除列
ALTER TABLE book DROP COLUMN annual;	

#⑤修改表名
ALTER TABLE author RENAME TO book_author;

DESC book_author;

# ⑥在某个字段位置添加列
# 语法：
	alter table 表名 add column 列名 类型 【first|after 字段名】
ALTER TABLE author ADD COLUMN birthday DATETIME FIRST ;

③表的删除

DROP TABLE IF EXISTS book_author;

SHOW TABLES;	# 查看该库下的表。

#通用的写法：
DROP DATABASE IF EXISTS 旧库名;
CREATE DATABASE 新库名;

DROP TABLE IF EXISTS 旧表名;
CREATE TABLE  表名();

④表的复制

# 在author表内插入数据
INSERT INTO author VALUES	
(1,'村上春树','日本'),
(2,'莫言','中国'),
(3,'冯唐','中国'),
(4,'金庸','中国');
# 查看表的信息
SELECT * FROM Author;
SELECT * FROM copy2;
#1.仅仅复制表的结构
CREATE TABLE copy LIKE author;

#2.复制表的结构+数据
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;

3数据类型介绍

数值型：
- 整数
- 小数：
  - 定点数
  - 浮点数
字符型：
- 较短的文本：char、varchar
- 较长的文本：text、blob（较长的二进制数据）
日期型
选择的原则：
- 所选择的类型越简单越好
- 能保存数值的类型越小越好

①整型

分类：tinyint，smallint，mediumint，int，bigint

1 2 3 4 8

特点：
1. 如果不设置无符号还是有符号，系统默认是有符号，如果想设置无符号，需要添加unsigned关键字
2. 如果插入的数值超出了整型的范围，会报out of range异常，并且插入临界值
3. 如果不设置长度，会有默认的长度，长度代表显示的最大宽度，如果不够会用0在左边填充，但必须搭配zerofill使用。

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

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

DESC tab_int;

INSERT INTO tab_int VALUES(-123456);
INSERT INTO tab_int VALUES(-123456,-123456);
INSERT INTO tab_int VALUES(2147483648,4294967296);

INSERT INTO tab_int VALUES(123,123);

SELECT * FROM tab_int;

②浮点型

浮点型
- float(M, D) 4字节
- double(M, D) 8字节
定点型
- DEC(M, D)

①M和D的含义：

M：整数部位+小数部位

D：小数部位

如果超出范围，则插入临界值

(M, D)都可以省略

如果是DEC，则（M, D）默认是（10，0）

如果是float，double，则会根据插入的数据来决定精度

定点型的精度较高，如果要求插入数值的精度较高如货币运算则考虑使用。

#测试M和D
CREATE TABLE tab_float (
	f1 FLOAT(5, 3),
	f2 DOUBLE(5, 3),
	f3 DEC(5, 3)	
);
SELECT * FROM tab_float;
DESC tab_float;

INSERT INTO tab_float VALUES(123.4523,123.4523,123.4523);
INSERT INTO tab_float VALUES(123.45,123.45,123.45);
INSERT INTO tab_float VALUES(123.4,123.4,123.4);
INSERT INTO tab_float VALUES(129783.4,172983.4,127893.4);
#原则：
/*
所选择的类型越简单越好，能保存数值的类型越小越好

*/

③字符型

较短的文本

char(M) M表示最多的字符数（M可以省略，默认为1）固定长度的字符比较耗费空间高效率
varchar(M) M不可以省略可变长度的字符比较节省空间低效率
binary和varbinary（较短的二进制）

Enum类型（枚举型）每次只选择一个成员

# 枚举型实例
CREATE TABLE tab_char(
	c1 ENUM('a','b','c')		# 放一个列表
);
INSERT INTO tab_char VALUES('a');	# 成功a
INSERT INTO tab_char VALUES('b');	# 成功b
INSERT INTO tab_char VALUES('c');	# 成功c
INSERT INTO tab_char VALUES('m');	# 失败，空
INSERT INTO tab_char VALUES('A');	# 成功a

set类型，可以选择多个成员

CREATE TABLE tab_set(
	s1 SET('a','b','c','d')
);
INSERT INTO tab_set VALUES('a');
INSERT INTO tab_set VALUES('A,B');
INSERT INTO tab_set VALUES('a,c,d');

较长的文本
- text
- blob（较长的二进制）

④日期型

date 1000-01-01 只保存日期
datetime 1000-01-01 00:00:00 （★）保存日期＋时间
time -838:59:59 只保存时间
timestamp 19700101080000（★）保存日期+时间
year 1901 只保存年

datatime和timestamp区别：

字节范围时区等的影响

datetime 8 1000——9999 不受
timestamp 4 1970-2038 受

CREATE TABLE tab_date(
	t1 DATETIME,
	t2 TIMESTAMP
);

INSERT INTO tab_date VALUES(NOW(),NOW());

SELECT * FROM tab_date;

SHOW VARIABLES LIKE 'time_zone';

SET time_zone='+9:00';

4常见约束介绍

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

分类：六大约束

NOT NULL：非空，用于保证该字段的值b不能为空

比如姓名，学号等
DEFAULT：默认，用于保证该字段有默认值

比如性别
PRIMARY KEY：主键，用于保证该字段具有唯一性，并且非空

比如学号，员工编号等
UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空

比如年龄，性别，配偶
CHECK：检查约束【MYSQL不支持】

比如年龄、性别
FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自主表的关联列的值

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

添加约束的时机：

创建表时
修改表时

约束的添加分类：

列级约束
- 六大约束语法上都支持，但外键约束没有效果

表级约束

除了非空、默认，其他的都支持

create table 表名（
		字段名 字段类型 列级约束,
		字段名 字段类型,
		表级约束
）

主键和唯一的大对比：

	保证唯一性  是否允许为空    一个表中可以有多少个   是否允许组合
主键	√		×		至多有1个           √，但不推荐
唯一	√		√		可以有多个          √，但不推荐

外键：
1、要求在从表设置外键关系
2、从表的外键列的类型和主表的关联列的类型要求一致或兼容，名称无要求
3、主表的关联列必须是一个key（一般是主键或唯一）
4、插入数据时，先插入主表，再插入从表
删除数据时，先删除从表，再删除主表

①创建表时添加约束

注意！！！外键的引用列必须是键列！！！

CREATE DATABASE students;	# 创建库

添加列级约束
语法：

直接在字段名和类型后面追加约束类型即可。

只支持：默认、非空、主键、唯一

USE students;	# 使用库
DROP TABLE stuinfo;	# 删除表

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)
);	# 外键时候使用的major表

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

添加表级约束
语法：在各个字段的最下面
【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_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)#外键
	
);

SHOW INDEX FROM stuinfo;

通用的写法

只有外键使用表级约束，其他的使用列级约束。

CREATE TABLE IF NOT EXISTS stuinfo(
		id INT PRIMARY KEY,		#主键
    	stuname VARCHAR(20) NOT NULL,	# 非空
    	gender CHAR(1),		#
    	age INT DEFAULT 18,		# 默认约束
    	seat INT UNIQUE,	# 唯一
    	majorid INT，
    	CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id)	# 外键
);

②修改表时添加约束

添加列级约束：
- alter table 表名 modify column 字段名字段类型新约束；
添加表级约束：
- alter table 表名 add 【constraint 约束名】约束类型（字段名）【外键的引用】
修改表时添加约束：

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 stuinfo VARCHAR(20) NOT NULL;
#2.添加默认约束
ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;
#3.添加主键：
ALTER TABLE stuinfo MODIFY COLUMN id PRIMARY KEY;
#4.添加唯一约束
ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;
#5.添加外键
ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

修改表时删除约束：

#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;

SHOW INDEX FROM stuinfo;

5标识列

**概念：**标识列，又称为自增长列，即可以不用手动的插入值，系统提供默认的序列值

注意！标识列可以使用null，但是不加auto_increase的
特点：
- 标识列必须和主键搭配吗？不一定，但要求是一个key
- 一个表可以有几个标识列？至多一个！
- 标识列的类型只能是数值型
- 标识列可以通过 SET auto_increment_increment=3;设置步长
  可以通过手动插入值，设置起始值

①创建表时设置标识列

DROP TABLE IF EXISTS tab_identity;

CREATE TABLE tab_identity(
	id INT  ,
	NAME FLOAT UNIQUE AUTO_INCREMENT,
	seat INT 
);

TRUNCATE TABLE tab_identity;
INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');	
INSERT INTO tab_identity(NAME) VALUES('lucy');

SELECT * FROM tab_identity;

SHOW VARIABLES LIKE '%auto_increment%';

SET auto_increment_increment=3;

6级联

给外键添加级联。
- 带有级联删除的外键
- 带有级联置空的外键

首先在test库下，创建表stuinfo，和major

CREATE TABLE stuinfo (id INT unique auto_increment , stuname VARCHAR(20), gender CHAR(1), seat INT, age INT , majorid INT);	# id 自增长列
CREATE TABLE major (id INT PRIMARY KEY, majorName VARCHAR(20));	# 添加主键

# 查看键
SHOW INDEX FROM major;
SHOW INDEX FROM stuinfo;

给上述两个表添加外键，因为表已经创建，传统的修改表时添加外键方式。

alter table stuinfo add constraint fk_stu_major foreign key(majorid) references major(id);


# 删除外键
alter table stuinfo drop foreign key fk_stu_major;

插入数据

INSERT INTO major VALUES(1, 'java'),(2, 'h5'), (3, '大数据');

INSERT INTO stuinfo VALUES(NULL, 'java1', 'M', NULL, NULL, 1);
INSERT INTO stuinfo VALUES(NULL, 'java1', 'M', NULL, NULL, 3);
INSERT INTO stuinfo VALUES(NULL, 'java1', 'M', NULL, NULL, 1);
INSERT INTO stuinfo VALUES(NULL, 'java1', 'M', NULL, NULL, 3);
INSERT INTO stuinfo VALUES(NULL, 'java1', 'M', NULL, NULL, 2);
INSERT INTO stuinfo VALUES(NULL, 'java1', 'M', NULL, NULL, 2);
INSERT INTO stuinfo VALUES(NULL, 'java1', 'M', NULL, NULL, 2);
INSERT INTO stuinfo VALUES(NULL, 'java1', 'M', NULL, NULL, 1);
INSERT INTO stuinfo VALUES(NULL, 'java1', 'M', NULL, NULL, 1);
INSERT INTO stuinfo VALUES(NULL, 'java1', 'M', NULL, NULL, 1);

SELECT * FROM major;
SELECT * FROM stuinfo;

如果想删除3号专业，那么会发现是删除不了的！这是因为存在外键的限制。

那么解决的方法有两种：

1 是删掉3号专业和具有3号专业的学生信息；

2 是删除3号专业，并且让带有3号专业的学生的majorid这一栏置空；

①级联删除

# 级联删除（把带有3号专业的学生一起删掉）
# 删除原有的外键，重新添加外键（因为表已经创建，所以是修改表时添加外键），带有级联删除。
ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

②级联置空

# 级联置空（把带有2号专业的学生专业一栏置空）

ALTER TABLE stuinfo ADD FOREIGN KEY (majorid) REFERENCES major(id) ON DELETE SET NULL;

练习题

库和表的管理-案例讲解

#1.	创建表dept1
CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE dept1(
	id INT(7),
	NAME VARCHAR(25)
);

#2.	将表departments中的数据插入新表dept2中
CREATE TABLE dept2 SELECT department_id, department_name
FROM myemployees.`departments`;

#3.	创建表emp5
NAME	NULL?	TYPE
id		INT(7)
First_name	VARCHAR (25)
Last_name	VARCHAR(25)
Dept_id		INT(7)


CREATE TABLE emp5(
	id INT(7),
	first_name VARCHAR(25),
	last_name VARCHAR(25),
	dept_id INT(7)	
);

#4.	将列Last_name的长度增加到50
ALTER TABLE emp5 MODIFY COLUMN last_name VARCHAR(50);

#5.	根据表employees创建employees2
CREATE TABLE employees2 LIKE myemployees.`employees`;

#6.	删除表emp5
DROP TABLE IF EXISTS emp5;

#7.	将表employees2重命名为emp5
ALTER TABLE employees2 RENAME TO emp5;

#8.在表dept和emp5中添加新列test_column，并检查所作的操作
ALTER TABLE emp5 ADD COLUMN test_column INT(10);

#9.直接删除表emp5中的列 dept_id
ALTER TABLE emp5 DROP COLUMN test_column;

常见约束-案例讲解

#1.向表emp2的id列中添加PRIMARY KEY约束（my_emp_id_pk）
ALTER TABLE emp2 id INT PRIMARY KEY;

#2.	向表dept2的id列中添加PRIMARY KEY约束（my_dept_id_pk）
ALTER TABLE dept2 id INT PRIMARY KEY
#3.	向表emp2中添加列dept_id，并在其中定义FOREIGN KEY约束，与之相关联的列是dept2表中的id列。
ALTER TABLE emp2 ADD COLUMN dept_id;
ALTER TABLE ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY (dept_id) REFERENCES dept2(id);

		位置		支持的约束类型			是否可以起约束名
列级约束：	列的后面	语法都支持，但外键没有效果	不可以
表级约束：	所有列的下面	默认和非空不支持，其他支持	可以（主键没有效果）

最佳第六六六人

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
打赏
0
评论
4DDL语言

DDL语言的学习==DDL（Data Define Language）==数据定义语言库和表的管理创建 create修改 alter删除 drop1库的管理库存放路径C:\ProgramData\MySQL\MySQL Server 5.7\Data#案例：1、创建库BooksCREATE DATABASE IF NOT EXISTS books;#2、库的修改RENAME DATABASE books TO 新库名; # 一般不会修改库的信息#更改库的字符集（改也就只可能改
复制链接

扫一扫