MySQL基础学习篇(4)——DDL

MySql基础学习

注明：文章笔记均来自哔哩哔哩视频学习

九、数据表操作

9.1 数据类型

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串（字符）类型。对于我们约束数据的类型有很大的帮助。

9.1.1 数值类型

类型	大小	范围（有符号）	范围（无符号）	用途
INT	4字节	(-2147483648,2147483647)	(0,4294967295)	大整数值
DOUBLE	8字节	(-1.797E+308,-2.22E-308)	0,(2.22E-308,1.797E+308)	双精度浮点数值
DOUBLE(M,D)	8字节，M表示长度，D表示小数位数	同上，受M和D的约束；如DOUBLE(5,2) -999.99~999.99	同上，受M和D的约束	双精度浮点数值
DECIMAL(M,D)	DECIMAL(M,D)	依赖于M和D的值，M最大值为65	依赖于M和D的值，M最大值为65	小数值

9.1.2 日期类型

类型	大小	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59’/‘838:59:59’	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DETETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

9.1.3 字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
BLOB（binary large object）	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据

• CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同，CHAR(N)会将多余的存储位以空格补足，而VARCHAR(N)则会根据实际存储长度进行“压缩”。在存储或检索过程中不进行大小写转换。
• BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

9.2 数据表的创建（CREATE）

CREATE TABLE 表名(
    列名 数据类型 [约束],
    列名 数据类型 [约束],
    ......
    列名 数据类型 [约束]#最后一行的末尾不加逗号
)[CHARSET=UTF8]#可根据需要指定表的字符编码集

括号内的内容为可选。

9.2.1 创建表

列名	数据类型	说明
subject_id	INT	课程编号
subject_name	VARCHAR(20)	课程时间
subject_hours	INT	课程课长

#根据上述表格创建数据库，并向表中插入3条测试语句
CREATE TABLE `t_subject` (
  `subject_id` int,
  `subject_name` varchar(20),
  `subject_hours` int(11)
)CHARSET=utf8;

INSERT INTO t_subject(subject_id,subject_name,subject_hours) 
VALUES(1,'JAVA',40);
INSERT INTO t_subject(subject_id,subject_name,subject_hours) 
VALUES(2,'MYSQL',30);
INSERT INTO t_subject(subject_id,subject_name,subject_hours) VALUES(3,'JAVASCRIPT',20)

9.3 数据表的修改（ALTER）

语法：ALTER TABLE 表名 操作;

9.3.1 向现有表中添加列

#在课程表基础上添加grade_id列
ALTER TABLE t_subject ADD grade_id INT;

9.3.2 修改表中的列

#修改课程表中的课程名称长度为10个字符
ALTER TABLE t_subject MODIFY subject_name VARCHAR(10);

• 注意：修改表中的某列时，也要写全列的名字，数据类型，约束。

9.3.3 删除表中的列

#删除课程表中grade_id列
ALTER TABLE t_subject DROP grade_id;

• 注意：删除列时，每次只能删一列。

9.3.4 修改列名

#修改课程表中subject_hours列为class_hours
ALTER TABLE t_subject CHANGE subject_hours class_hours INT;

• 注意：修改列名时，在给定列新名称时，要指定列的类型和约束。

9.3.5 修改表名

#修改课程表的t_subject为t_sub
ALTER TABLE t_subject RENAME t_sub;

9.4 数据表的删除（DROP）

语法：DROP TABLE 表名

9.4.1 删除课程表

#删除课程表
DROP TABLE t_sub;

十、约束

问题：在往已创建表中新增数据时，可不可以新增两行相同列的数据？如果可行，会有什么弊端？

这是可以的，但重复的数据不仅会浪费空间，而且对查询结果不利，也是多余的。在实际的存储中应尽量保证每条数据的唯一性。

10.1 实体完整性约束

表中的一行数据代表一个实体（entity），实体完整性的作用即是标识每一行数据不重复，实体唯一。

10.1.1 主键约束

关键词：PRIMARY KEY唯一，标识表中的一行数据，此列的值不可重复，且不能为NULL。

#为表中适用主键的列添加主键约束
CREATE TABLE t_subject(
#课程标号标识了每一个课程的编号唯一，且不能为NULL
subject_id INT PRIMARY KEY,
subject_name VARCHAR(20),
subject_hours INT
)CHARSET=utf8;
INSERT INTO t_subject(subject_id,subject_name,subject_hours) VALUES(1,'JAVA',40);
#报错，主键subject_id不可重复
INSERT INTO t_subject(subject_id,subject_name,subject_hours) VALUES(1,'JAVA',40);

作为主键的字段一般都是也应该是编号、学号、工号等字段。

10.1.2 唯一约束

关键字：UNIQUE唯一，标识表中的一行数据，不可重复，可以为NULL。

#为表中列值不允许重复的列添加唯一约束
ALTER TABLE t_subject MODIFY subject_name VARCHAR(20) UNIQUE;

INSERT INTO t_subject(subject_id,subject_name,subject_hours) VALUES(1,'JAVA',40);
#报错，课程名已存在
INSERT INTO t_subject(subject_id,subject_name,subject_hours) VALUES(2,'JAVA',40);

10.1.3 自动增长列

关键字：AUTO_INCREAMENT自动增长，给主键数值列添加自动增长。从1开始，每次加1。不能单独使用，和主键配合。

#为表中主键列添加自动增长，避免忘记主键ID序号
ALTER TABLE t_subject MODIFY subject_id INT AUTO_INCREMENT;
#课程ID自动增长为2
INSERT INTO t_subject ( subject_name, subject_hours ) VALUES( 'HTML5', 40 );

10.2 域完整性约束

该约束限制单元格的数据正确性。

10.2.1 非空约束

语法：NOT NULL,非空，此列必须有值

#课程名称虽然添加了唯一约束，但是有NULL值存在的可能，要避免课程名称为NULL。
ALTER TABLE t_subject MODIFY subject_name VARCHAR(20) NOT NULL;
#报错，课程名不能为空
INSERT INTO t_subject(subject_name,subject_hours) VALUES (NULL,40);

10.2.2 默认值约束

关键字：DEFAULT 值。为列赋予默认值，当新增数据不指定值时，书写DEFAULT，以指定的默认值进行填充。

#当存储课程信息时，若课程时长没有指定值，则以默认值进行填充
ALTER TABLE t_subject MODIFY subject_hours INT DEFAULT 40;
#不写默认课程时为40，也可以把值写成DEFAULT
INSERT INTO t_subject(subject_name) VALUES('JAVAWEB');

10.2.3 引用完整性约束

语法：CONSTRAINT 引用名 FOREIGN KEY (列名) REFERENCES 被引用表名(列名)

解释：FOREIGN KEY引用外部表某个列的值，新增数据时，约束此列的值必须是引用表中存在的值。

#创建专业表
CREATE TABLE t_speciality(
	special_id INT PRIMARY KEY AUTO_INCREMENT,
	special_name VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;
#添加一些专业信息
INSERT INTO t_speciality(special_name) VALUES('HTML5');
INSERT INTO t_speciality(special_name) VALUES('JAVA');
INSERT INTO t_speciality(special_name) VALUES('JAVAWEB');
INSERT INTO t_speciality(special_name) VALUES('数据结构');
#subject_id引用special_id
ALTER TABLE t_subject ADD 
CONSTRAINT fk_subject_spacial FOREIGN KEY(special_id) REFERENCES t_speciality(special_id);
#添加数据成功
INSERT INTO t_subject(subject_name,special_id) VALUES('数据结构',4);
#添加失败，主表中不存在5
INSERT INTO t_subject(subject_name,special_id) VALUES('操作系统',5);

• 注意：当两张表存在引用关系时，要执行删除操作，一定要先删除从表（引用表），再删除主表（被引用表）。

10.3 约束创建整合

创建带有约束的表。

列名	数据类型	约束	说明
grade_id	INT	主键、自动增长	班级编号
grade_name	VARCHAR(20)	唯一、非空	班级名称

CREATE TABLE t_grade(
	grade_id INT PRIMARY KEY AUTO_INCREMENT,
	grade_name VARCHAR(20) UNIQUE NOT NULL
)CHARSET=utf8;

列名	数据类型	约束	说明
student_id	VARCHAR(20)	主键	学号
student_name	VARCHAR(20)	非空	姓名
sex	CHAR(2)	默认填充“男”	性别
born_date	DATE	非空	生日
phone	VARCHAR(11)	无	电脑
grade_id	INT	非空、外键约束引用班级表的grade_id	班级编号

CREATE TABLE t_student(
	student_id VARCHAR(20) PRIMARY KEY,
	student_name VARCHAR(20) NOT NULL,
	sex CHAR(2) DEFAULT '男',
	born_date DATE NOT NULL,
	phone VARCHAR(11),
	grade_id INT NOT NULL,
	CONSTRAINT fk_student_grade FOREIGN KEY (grade_id) REFERENCES t_grade(grade_id)
)CHARSET=utf8;

• 注意：创建关系表时，一定要先创建主键，再创建从表。
• 删除关系表时，先删除从表，再删除主表。