03、MySQL（DML、DDL和约束）

8、DML操作【重点】

8.1、新增(INSERT)

　　INSERT INTO 表名 (列1，列2，列3..... ) VALUES (值1，值2，值3….);

8.1.1、添加一条信息

#添加一条工作岗位信息
INSERT INTO t_jobs(`JOB_ID`,`JOB_TITLE`,`MIN_SALARY`,`MAX_SALARY`) 
VALUES( 'JAVA_Le','JAVA_Lecturer' ,250,9880);

#添加一条员工信息
INSERT INTO t_employees (EMPLOYEE_ID,`FIRST_NAME`, `LAST_NAME`,`EMAIL`,`PHONE_NUMBER`,`HIRE_DATE`,`JOB_ID`, `SALARY`, `COMMISSION_PCT`, `MANAGER_ID`,`DEPARTMENT_ID`)
VALUES ('194','Samuel','McCain','SMCAIN','650.501.3876','1998-87-01','SH.CLERK', '3200',NULL,'123','50');

注意: 表名后的列名 和 VALUES里的值要一 一对应（个数、顺序、类型)

8.2、修改(UPDATE)

UPDATE 表名 SET 列1 = 新值1, 列2 = 新值2,..WHERE 条件;

8.2.1、修改一条信息

#修改编号为100的员工的工资为25000
UPDATE t_employees SET SALARY = 25008 WHERE EMPLOYEE_ID = '100';
#修改编号为135的员工信息岗位编号为ST_MAN，工资为3508
UPDATE t_employees SET JOB_ID='ST_MAN',SALARY = 3500 WHERE EMPLOYEE_ID = '135';

注意: SET 后多个 列名=值，绝大多数情况下都要加 WHERE条件，指定修改，否则为整表更新

8.3、删除(DELETE)

DELETE FROM 表名 WHERE 条件;

8.3.1、删除—条信息

#册除编号为135的员工
DELETE FROM t_employees WHERE EMPLOYEE_ID='135';
#册除姓Peter,并且名为Hall 的员工
DELETE FROM t_employees WHERE FIRST_NAME = 'Peter' AND LAST_NAME='Hall';

注意:   删除时，如若不加 WHERE 条件，删除的是整张表的数据

8.4、清空整表数据(TRUNCATE)

　　TRUNCATE TABLE 表名;

8.4.1、清空整张表

#清空t_countries整张表
TRUNCATE TABLE t1;

注意: 与DELETE不加 WHERE删除整表数据不同，TRUNCATE是把表销毁，再按照原表的格式创建一张新表

9、数据表操作

9.1、数据类型

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

9.1.1、数值类型

类型	大小	范围(有符号)	范围(无符号)	用途
INT	4字节	(-2147 483 648，2147 483 647)	(0，4294 967295)	大整数值
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	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-3123:59:59	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-0100:00:00/2038 结束时间是第2147483647 秒北京时间2038-1-19 11:14:07，格林尼治时间2038年1月19日凌晨03:14:07	YYYYMMD HHMMSS	混合日期和时间值，时间戳

9.1.3、字符串类型

类型	大小	用途
CHAR	0-255字符	定长字符串char(10)10个字符
VARCHAR	0-65535字节	变长字符串varchar(10)10个字符
BLOB (binary large object)	0-65535字节	二进制形式的长文本数据
TEXT	0-65535字节	长文本数据

　　CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

　　BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型:TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

9.2、数据表的创建(CREATE)

CREATE TABLE 表名（

列名 数据类型 [约束],

列名 数据类型 [约束],

...

列名 数据类型 [约束]//最后一列的末尾不加逗号

)[charset=utf8] //可根据需要指定表的字符编码集

9.2.1、创建表

#依据上述表格创建数据表,并向表中插入3条测试语句
CREATE TABLE `subject`(
subjectId INT,
subjectName VARCHAR(20),
subjectHours INT
)CHARSET=utf8;

INSERT INTO `subject`(subjectId, subjectName , subjectHours) VALUES(1 , 'Java' ,40);
INSERT INTO `subject`(subjectId, subjectName , subjectHours) VALUES(2,'WYSQL',20);
INSERT INTO `subject`(subjectId, subjectName , subjectHours) VALUES(3 ,'JavaScript',30);

SELECT * FROM `subject`

9.3、数据表的修改(ALTER)

　　ALTER TABLE 表名操作;

9.3.1、向现有表中添加列

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

9.3.2、修改表中的列

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

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

9.3.3、删除表中的列

#删除课程表中gradeId 列
ALTER TABLE SUBJECT DROP gradeId;

注意: 删除列时，每次只能删除一列

9.3.4、修改列名

#修改课程表中subjectHours列为classHours
ALTER TABLE SUBJECT CHANGE subjectHours classHours INT ;

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

9.3.5、修改表名

#修改课程表的subject为sub
ALTER TABLE SUBJECT RENAME sub;

9.4、数据表的删除(DROP)

　　DROP TABLE 表名

9.4.1、删除学生表

#删除学生表
DROP TABLE sub;

10、约束

问题:

　　在往已创建表中新增数据时,可不可以新增两行相同列值的数据?

　　如果可行,会有什么弊端?

10.1、实体完整性约束

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

10.1.1、主键约束

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

#为表中适用主键的列添加主键约束
CREATE TABLE SUBJECT(
subjectId INT PRIMARY KEY,#课程编号标识每一个课程的编号唯一，且不能为 NULL
subjectName VARCHAR(20),
subjectHours INT
)CHARSET=utf8;

INSERT INTO SUBJECT(subjectId,subjectName , subjectHours)VALUES(1 , 'Java' ,40);
INSERT INTO SUBJECT(subjectId, subjectName , subjectHours)VALUES(1 , 'Java' ,40 ); #error 主键 1已存在

10.1.2、唯一约束

　　UNIQUE 唯一，标识表中的一行数据,不可重复，可以为NULL

#为表中列值不允许重复的列添加唯一约束
CREATE TABLE SUBJECT(
subjectId INT PRIMARY KEY,
subjectName VARCHAR(28) UNIQUE,#课程名称唯—!
subjectHours INT
)CHARSET=utf8;

INSERT INTO SUBJECT(subjectId , subjectName , subjectHours) VALUES(1 , 'Java' ,40);
INSERT INTO SUBJECT(subjectId, subjectName, subjectHours)VALUES(2, 'Java',40 ) ; #error课程名称已存在

10.1.3、自动增长列

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

#为表中主键列添加自动增长,避免忘记主键ID序号
CREATE TABLE SUBJECT(
subjectId INT PRIMARY KEY AUTO_INCREMENT,#课程编号主键且自动增长，会从1开始根据添加数据的顺序依次加1
subjectName VARCHAR(28)UNIQUE,
subjectHours INT
) CHARSET=utf8;
INSERT INTO SUBJECT(subjectName, subjectHours) VALUES( 'Java' ,40);#课程编号自动从1增长
INSERT INTO SUBJECT(subjectName , subjectHours) VALUES( 'JavaScript',30);#第二条编号为2

10.2、域完整性约束

　　限制列的单元格的数据正确性。

10.2.1、非空约束

　　NOT NULL 非空，此列必须有值。

#课程名称虽然添加了唯一约束，但是有 NULL值存在的可能,要避免课程名称为NULL
CREATE TABLE SUBJECT(
    subjectId INT PRIMARY KEY AUTO_INCREMENT,
    subjectName VARCHAR(20) UNIQUE NOT NULL,
    subjectHours INT
) CHARSET=utf8;

INSERT INTO SUBJECT(subjectName , subjectHours) VALUES(NULL,40 ) ; #error，课程名称约束了非空

10.2.2、默认值约束

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

#当存储课程信息时，若课程时长没有指定值,则以默认课时20填充
CREATE TABLE SUBJECT(
subjectId INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAR( 20) UNIQUE NOT NULL,
subjectHours INT DEFAULT 20
) CHARSET=utf8;

INSERT INTO SUBJECT(subjectName, subjectHours) VALUES( ' Java',DEFAULT) ;#课程时长以默认值20 填充

10.2.3、外键约束（引用完整性）

语法:

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

详解:

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

#创建专业表
CREATE TABLE Speciality(
id INT PRIMARY KEY AUTO_INCREMENT,
SpecialName VARCHAR(28) UNIQUE NOT NULL
) CHARSET=utf8;

#创建课程表(课程表的SpecialId引用专业表的id)
CREATE TABLE SUBJECT(
subjectId INT PRIMARY KEY AUTO_INCREMENT,
subjectName VARCHAR(28) UNIQUE NOT NULL,
subjectHours INT DEFAULT 20,
specialId INT NOT NULL,
CONSTRAINT fk_subject_specialId FOREIGN KEY(specialId) REFERENCES Speciality(id) #引用专业表里的id作为外键，新增课程信息时,约束课程所属的专业。
)CHARSET=utf8;

#专业表新增数据
INSERT INTO Speciality(SpecialName) VALUES( 'Java ');
INSERT INTO Speciality(SpecialName)VALUES('C#');#课程信息表添加数据

INSERT INTO SUBJECT(subjectName , subjectHours,`specialId`) VALUES( 'Java',30,1);#专业id 为1，引用的是专业表的Java
INSERT INTO SUBJECT(subjectName , subjectHours,`specialId`) VALUES('C#MVC',10,2);#专业 id 为2，引用的是专业表的C#

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

10.3、约束创建整合

　　创建带有约束的表。

10.3.1、创建表

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

 

CREATE TABLE Grade(
GradeId INT PRIMARY KEY AUTO_INCREMENT,
GradeName VARCHAR(20) UNIQUE NOT NULL
)CHARSET=UTF8;

列名	数据类型	约束	说明
student_id	VARCHAR(50)	主键	学号
Student_name	VARCHAR(50)	非空	姓名
sex	CHAR(2)	默认填充'男'	性别
borndate	DATE	非空	生日
phone	VARCHAR(11)	无	电话
Gradeld	INT	非空，外键约束:引用班级表的gradeid。	班级编号

CREATE TABLE student(
student_id VARCHAR(50) PRIMARY KEY,
student_name VARCHAR(50) NOT NULL,
sex CHAR(2)DEFAULT '男', 
borndate DATE NOT NULL,
phone VARCHAR(11),
gradeId INT NOT NULL,
CONSTRAINT fk_student_gradeId FOREIGN KEY(gradeId) REFERENCES Grade(GradeId) #引用Grade表的GradeId列的值作为外键,插入时约束学生的班级编号必须存在。
);

注意:

　　创建关系表时，一定要先创建主表,再创建从表

　　删除关系表时,先删除从表，再删除主表。