第二章数据库管理操作 ① 笔记

小丫头爱打盹

已于 2022-07-15 16:03:29 修改

阅读量362

点赞数 1

分类专栏： # MySQL 文章标签：数据库 java sql

于 2022-07-14 20:31:21 首次发布

本文链接：https://blog.csdn.net/Liu_wen_wen/article/details/125791183

版权

MySQL 专栏收录该内容

18 篇文章 1 订阅

订阅专栏

1. 内容回顾

1.1 课前测试
1.2 上节内容
在这里插入图片描述

2. 本章重点

2.1. 数据的操作
2.2. 数据完整性*
2.3. 数据定义语言

3. 具体内容

3.1 数据的操作

3.1.1 SQL简介
SQL (Structured Query Language:结构化查询语言) 是用于管理关系数据库管理系统（RDBMS）。 SQL 的范围包括数据插入、查询、更新和删除，数据库模式创建和修改，以及数据访问控制。
3.1.2 SQL语言分类
 数据查询语言（DQL:Data Query Language）： select
其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING。这些DQL保留字常与其他类型的SQL语句一起使用。
 数据操作语言（DML：Data Manipulation Language）：INSERT，UPDATE和DELETE其语句包括动词INSERT，UPDATE和DELETE。它们分别用于添加，修改和删除表中的行。也称为动作查询语言。
 事务处理语言（TPL）：
它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括BEGINTRANSACTION，COMMIT和ROLLBACK。
 数据控制语言（DCL）：
它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
 数据定义语言（DDL）：跟表的结构相关。create drop alter(更改表结构)
其语句包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROPTABLE）；为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
 指针控制语言（CCL）：
它的语句，像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

3.1.3 SQL常用操作语句

查询语句

select 列1,列2,...,列n
from 表名
where 条件
group by 列
having 条件
order by 列 asc/desc
limit 位置,记录数

插入语句

#按照字段插入
insert into 表名(列名1，列名2。。。) values(列值1，列值2。。。);

#插入一条
insert into 表名 values(id,name,age,address);

#同时插入多条语句
insert into 表名
(列1,列2,列3,列4)
values
(值1,值2,值3,值4),
(值1,值2,值3,值4),
(值1,值2,值3,值4);

#同时插入多条语句
insert into 表名
(列1,列2,列3,列4)
select 值1,值2,值3,值4 union
select 值1,值2,值3,值4

修改语句

update 表名
set 列名1 = '列值1',
	列名2 = '列值2',
	列名3 = '列值3'
where 条件

删除语句

delete from 表名 where 条件

3.2 数据完整性

3.2.1 数据完整性(Data Integrity)
数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。 它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。数据完整性分为四类：实体完整性（Entity Integrity）、域完整性（Domain Integrity）、参照完整性/引用完整性（Referential Integrity）、用户自定义完整性（Userdefined Integrity）。

3.2.2 数据完整性分类

 实体完整性(Entity Integrity)：保证数据记录的唯一性，主键,自增,唯一
 域完整性(Domain Integrity) : 非空,默认值,外键约束
 引用完整性(Referential Integrity) : 外键约束
 用户自定义完整性(User-defined Intergrity)

3.3.3 实体完整性(Entity Integrity)约束

实体完整性要确保实体的唯一性。：主键约束,自增约束,unique约束

3.3.3.1 主键约束（primary key）

特点: 加了主键约束的列要唯一且非空。并且主键列会自动生成索引。
主键约束是行数据的唯一标识，不能为null，不能重复，一个表中应该有主键，只能有一个主键，有主键（索引）的表，查询效率高。
主键数据库主键是指表中一个列，其值能唯一地标识表中的每一行。这样的一列称为表的主键，通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY约束来创建主键。一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY约束确保唯一数据，所以经常用来定义标识列。

#删除表
drop table emp;

# 主键约束: primary key
# 特点: 该列数据必须唯一，而且不能为空
create table emp
(
	empId int primary key,
	empName varchar(20),
	sex char(1),
	hireDate date,
	phone varchar(20)
)

#查看表数据
SELECT * from emp

insert into emp
(empId,empName,sex,hireDate,phone)
VALUES
(1001,'张三','男','2000-01-01','13112345678')

#问题1：插入重复数据会报错
insert into emp
(empId,empName,sex,hireDate,phone)
VALUES
(1001,'张三风','男','2000-01-01','13112345678')

#问题2:主键列不插入数据或者插入null值，也要报错
insert into emp
(empName,sex)
VALUES
('李四','女')

#添加主键
alter table 表名
add constraint 约束名 primary key (列)

#删除主键
alter table 表名
drop primary key

主键的主要作用：
（1）保证实体的完整性；
（2）加快数据库的操作速度；
（3）在表中添加新记录时，数据库会自动检查新记录的主键值，不允许该值与其他记录的主键值重复；
（4) 数据库自动按主键值的顺序显示表中的记录。如果没有定义主键，则按输入记录的顺序显示表中的记录。
主键的特点:
（1）唯一性：一个表中只能有一个主键。如果在其他字段上建立主键，则原来的主键就会取消。联合主键！
（2）非空性：主键的值不可重复，也不可为空；
（3）一张表要有一个无意义的主键。主键主要是用于其他表的外键关联，以及本记录的修改与删除；

面试题: primary key 的特点 类似于 unique 和 not null 的组合。那么能不能使用uniq
ue 和not null 来代替 primary key 呢？

回答:
不能。因为主键会自动根据主键生成一个索引文件。增加查询效率。
不是说有了索引才会速度块，是因为索引算法。数字可以用b+tree,如果是汉字索引呢，hash
值。

3.3.3.2 自增约束（auto_increment）

特点: 系统自动生成，自动加1。要和主键约束一起使用。一个表只能由一个自增列

#自增约束: 用于整形列,要和主键约束一起使用,保证数据的自动增长
# 关键字auto_increment
drop table emp;

create table emp
(
	empId int primary key auto_increment,
	empName varchar(20),
	sex char(1),
	hireDate date,
	phone varchar(20)
)

#对于自增列: 不提供列值的时候，系统自动生成
insert into emp
(empName,sex)
VALUES
('李四','女')

select * from emp

#对于自增列:自己提供了列值，则插入自己的值
insert into emp
(empId,empName,sex)
VALUES
(1001,'李四','女')

#自增会在上一条记录的基础上累加
insert into emp
(empName,sex)
VALUES
('王五','女')

#添加自增
alter table 表名
modify column id int auto_increment

#删除自增
alter table 表名
modify column id int

注意: 自增值在某些场景下会出现断层

1.在mysql存储引擎是innodb的情况下，如果使用select union语法做批量插入，则会导致两
次插入的自增值断层。而使用 insert values语法不会导致这个问题。将表的存储引擎改为My
ISAM后这个问题可以解决

#查看表使用存储引擎
show create table emp

#创建表指定引擎
create table emp
(
	empId int primary key auto_increment,
	empName varchar(20),
	sex char(1),
	phone varchar(11) unique
)ENGINE = 'myisam'

2. 在innodb存储引擎下，在插入唯一约束数据重复，外键无效数据时，此时报错，会导致自增
断层。而数据长度过长导致的插入不成功不会导致自增断层。
3. 在有自增列的表中删除记录之后，再插入数据时，自增不会连续增长。

3.3.3.3 唯一约束（unique）

特点: 确保数据的唯一性。唯一约束允许null值。

#实体完整性:
#1. 主键约束(primary key) :
# 唯一且不能为空,可以用整数列也可以用于字符串列
# 举例:学号,员工编号
#2. 自增约束(auto_increment):
# 自动增长。用于整形列和主键一起用。一个表中只能有一个自增列
# 举例:整形学号,整形员工编号
#3. 唯一约束(unique)
# 唯一可以允许null。一个表中可以有多个。
# 举例: 手机号,身份证号

drop table emp;

create table emp
(
	empId int primary key auto_increment,
	empName varchar(20),
	sex char(1),
	hireDate date,
	phone varchar(20) unique # 唯一约束
)

select * from emp;

#表结构查询
desc emp

select * from emp

insert into emp
(empName,sex,hireDate,phone)
values
('张三','男','2002-01-01','13112345678')

insert into emp
(empName,sex,hireDate,phone)
values
('李四','男','2002-01-01','13112345679')

insert into emp
(empName,sex,hireDate)
values
('赵六','男','2002-01-01')

#添加唯一约束
alter table 表名
add constraint 唯一键约束名 UNIQUE(列)

#删除唯一索引约束
alter table 表名
drop index 唯一键约束名

3.3.4 引用完整性

引用完整性用于限定多表之间的关联关系。

3.3.4.1 外键约束(foreign key)

特点: 确保表与表之间的数据的一致性。
外键约束(foreign key )：主要是为了维护两个表之间的数据的一致性关系，是建立在主键的基础之上的。A表中的某列（外键）会引用B表中的主键；数据类型一致；体现表与表之间的关联关系；必须先有主键数据，才可以录入外键数据；外键要主键数据一致。
如果一张表的某个列指向另一个表的主键列，这个列就称之为外键列。这个表被称为从表，而其关联的目标表被称为主表。

#引用完整性:
#在多表之间关联的时候，一个表引用其他表的数据，必须保证引用的有效性
#1. 外键约束: foreign KEY

#创建班级表
create table classInfo
(
	classId int primary key auto_increment,#主键自增
	className varchar(20) not null
)

select * from classInfo;

insert into classInfo
(className)
values
('AAA01'),
('AAA02'),
('AAA03')

#创建学生信息表
create table student
(
	studentId int primary key auto_increment,
	name varchar(20) not null,
	sex char(1) not null,
	bjId int #班级编号
)
select * from classInfo;
select * from student;

insert into student
(name,sex,bjId)
select '张三','男',1 UNION
select '李四','女',2

#因为bjId没有加外键约束，所以录入了无效数据
insert into student
(name,sex,bjId)
select '王五','男',5
drop table student;

#创建学生表，加外键约束
create table student
(
	studentId int primary key auto_increment,
	name varchar(20) not null,
	sex char(1) not null,
	bjId int ,
	foreign key (bjId) #针对当前学生表中的bjId加外键约束
	REFERENCES classInfo(classId) #关联另外一个表的哪个列
)

#表结构
desc student

insert into student
(name,sex,bjId)
values
('张三','男',1)
select * from student;

#因为表中的bjId加了外键约束，所以插入该列的数据时
#如果数据不存在于关联的另外一个表的关联列中,则报错
insert into student
(name,sex,bjId)
values
('张三','男',5)

#添加外键约束
alter table 表名
add constraint 约束名
foreign key(当前表列名)
references 关联表(关联表列名)

#删除外键约束
alter table 表名
drop foreign key 外键约束名

在具有引用完整性的表之间，禁止用户进行下列操作
（1）当主表中没有关联的记录时，将记录添加到从表中。

#分类中的分类编号只有1,2,3但是商品要插入4的分类，此时报错
insert into goods
(goodsName,price,produceDate,address,categoryId)
values
('洗衣粉',2.5,'2020-01-01','广州',4)

报错:
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fa
ils (`qy149_0219`.`goods`, CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`categor
yId`) REFERENCES `category` (`categoryid`))

（2）更改主表表中的被关联值并导致从表中的记录孤立。

#商品表关联了分类表中，编号为1的记录，此时将分类表中的1改为10
#此时会报错外键错误
update category
set categoryId = 10
where categoryId = 1

报错:
[Err] 1451 - Cannot delete or update a parent row: a foreign key constrain
t fails (`qy149_0219`.`goods`, CONSTRAINT `goods_ibfk_1` FOREIGN KEY (`cat
egoryId`) REFERENCES `category` (`categoryid`))

(4) 主表被从表关联，此时删除主表结构，会报错，主表无法删除。
解决方法: 先删除从表，再删除主表

#分类表被商品表关联，此时删除分类表，则会报错
drop table category

错误：
[Err] 3730 - Cannot drop table 'category' referenced by a foreign key cons
traint 'goods_ibfk_1' on table 'goods'.

删除主表的解决方法：

#先删除从表(商品表),在删除主表
drop table goods;
drop table category;

注意：在实际项目中考虑到数据库的处理性能和表的维护，一般并不会实际加外键约束。而只是设计表时让从表和主表有对应数据列即可。这被称为逻辑约束。

3.3.5 域完整性(Domain Integrity)

域完整性，主要用于限定数据范围。

3.3.5.1 默认值约束：default，

当没有为列提供值得时候，用默认值填充列

/*
默认值约束: DEFAULT
# 加了默认值约束，则该列值没有提供的时候，用默认值填充
# 如果提供了列值，则用提供的值填充
*/

drop table emp;

create table emp
(
	empId int primary key auto_increment,
	empName varchar(20) not null,#非空约束
	sex char(1) not null, #非空约束
	hireDate date,
	phone varchar(20) unique, # 唯一约束
	province varchar(20) default '河南' #省份
)

select * from emp;

insert into emp
(empName,sex)
VALUES
('张三','男')

insert into emp
(empName,sex,province)
values
('李四','女','湖北')

alter table mystu
modify column province varchar(20)
default '河南'

3.3.5.2 非空约束：not null

确保某一列的值不能为空。

#删除数据库
drop database qy149_0218;
#删除表
drop table emp;

create table emp
(
	empId int primary key auto_increment,
	empName varchar(20) not null,#非空约束
	sex char(1) not null, #非空约束
	hireDate date,
	phone varchar(20) unique, # 唯一约束
	province varchar(20) #省份
)

select * from emp;

#非空约束
insert into emp
(empName,sex,hireDate,phone,province)
values
('张三','男','2020-01-01','13112345678','河南')

insert into emp
(empName,sex,hireDate,phone,province)
values
('李四','女','2020-01-01','13112345679','河南')

#查看表结构
desc emp;

alter table mystu
modify column sex char(1)
not null

3.3.5.3 外键约束：foreign key

特点: 确保表与表之间的数据的一致性。

#添加外键约束
alter table 表名
add constraint 约束名
foreign key(当前表列名)
references 关联表(关联表列名)

#删除外键约束
alter table 表名
drop foreign key 外键约束名

*检查约束：check约束，MySql中没有实现check约束,SQLServer中有这个约束

3.3.6 用户自定义完整性

用户自定义完整性指针对某一具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。例如某个属性必须取唯一值，某个非主属性也不能取空值，某个属性的取值范围在0-100之间等
用户定义完整性可以涵盖实体完整性、域完整性、参照完整性等完整性类型。

3.3.7 建表时加约束

实例:

#创建班级表
create table class (
	id int(5) primary key auto_increment,
	classname varchar(30) unique not null
);

#创建学生表：
create table student(
	id int(5) primary key auto_increment,
	name varchar(50) not null,
	age it(5) not null,
	classid int(5),#外键列
	constraint 外键名classid_fk_id foreign key(引用id) references 被引用表名
(被引用id)

);

注意：
在从表(学生表)中插入数据时，其中的班级列数据必须是班级表中主键列的数据。
在删除主表(班级表)中的数据时，要确保删除的数据没有被从表中的外键数据关联。
以后的项目中大多都用逻辑外键 (列之间有对应的数据关系)，不用实际外键（表与表中有foreign key 关联的）。
逻辑外键：在逻辑关系上存在外键关联，但是在实际创建表结构时，并没有添加foreigh key 关联。
好处：如果有实际外键，当我们每一次去操作数据时都会进行外键的维护。效率低，占资源。
所以实际开发中经常使用逻辑外键关系。
小数据量基本体现不出快慢效果。快慢效果只有在庞大的数据基数下才可以体现。

3.3 数据定义语言(DDL)

动态添加/修改/删除-列/列数据类型/列名/列主键约束等。这样的话，数据类型建表错误的时候，就不用删表重建啦~
主要用来修改数据表结构用的，增加/减少/修改字段（属性）添加各种约束

//添加新的字段
alter table 表名 add 列名 <类型> ;
//添加新的字段 位置在第一列
alter table 表名 add 列名 <类型> first;
//将列名1添加到列名2的后边
alter table 表名 add 列名1 <类型> after 列名2;
//修改字段名和类型
alter table 表名 change COLUMN <旧列名> <新列名> <新列类型>;
//修改列名的类型
alter table 表名 modify <列名> <类型>;
//删除列
alter table 表名 drop <列名>;
//给表重命名
alter table 表名 rename <新表名>;
//设置字符集
alter table 表名 CHARACTER SET <字符集名>;
//移动列的位置 将列名a移动到列名b的后边
alter table 表名 modify <列名a>(列类型) after <列名b>;
//给id添加自增约束
alter table 表名 modify id int(5) auto_increment;
//修改表中的字段不为空/或者为空
alter table 表名 modify 字段名 类型 not null/null;
//添加表的字段自增主键 如果要修改只需将add 改成 modify
alter table 表名 add column 字段名 类型 auto_increment not null, add primary k
//添加唯一约束：
alter table 表名 add constraint uk_(字段名) unique(字段名);
//删除表的字段
alter table 表名 drop column 字段;
//删除自增主键约束
alter table 表名 modify id int;
//删除表的主键约束，要先删除自增约束才可以删除主键约束
alter table 表名 drop primary key;
//添加外键约束
//外键名 fk_classid_id student classid class id
alter table 表名 add constraint 外键名
foreign key(引用id) references 被引用表名 (被引用id)
//添加主键约束
alter table 表名 add constraint pk_id primary key(id(修改的主键列名));
//删除外键约束
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称
show create table 表名;
-- 获取名称之后就可以根据名称来删除外键约束
alter table 表名 drop foreign key 外键名;

4. 本章总结

 数据操作
 数据完整性
 数据定义语言

5. 课后作业

题目1、
a.创建系部表(department)、年级表(grade)、班级表(classes)、学生表(student)、
课程科目(subject)、成绩表(score)；（创建表，设置各种约束，录入数据）使用命
令练习
b.完成数据的录入，每表3条以上，使用命令录入。
c.完成数据库备份和恢复。
题目2、https://blog.csdn.net/weixin_39718665/article/details/78161013MySQL查询语句练习题45题版
题目3、https://zhuanlan.zhihu.com/p/52173860 MySQL50道题目练习