数据库原理与应用

---

一、C/S解读及部分cmd命令

client 客户端：（如terminal，cmd，powershell等， 通过命令去访问/操作 数据库
server 服务端：比如mysql server
用客户端（cmd）去访问服务端（mysql server）

1. mysql -u root -p: 表示以用户名，密码的形式登录mysql
   -u表示接下来要输入用户名，root为权限最高的用户名，-p表示接下来要输入密码（可在后面不加空格直接写密码）。
2. cls 清屏
3. cd <路径> 表示到路经所在位置
4. mysqld --initialize-insecure --user=root: 表示在当前路径创建data文件，里面是一些数据库。
5. exit 表示退出cmd

二、数据模型

层次模型 和 网状模型 都有其局限性，前者结构相当于树，后者相当于图。

层次模型由于一个分支只能有一个双亲，所以当一个数据出现在多个分支里面时，在更改tom的年龄时要每个分支都改，改不全就会导致数据的不完整性。

网状模型中由于一个分支可以有多个双亲，所以不会出现数据的不完整性问题，但是缺乏特殊性和导航性，即如果给打游戏的100元，给学习的200元，这种情况统计起来会出现麻烦。

关系模型 可以由一个公共的数字如学号等，搜索不同的表，将不同的表进行相关联，各个表之间互不影响，各管理各的。关系模型可以通过增加关系表来完善各种功能。

三、mysql语法

在 创建 和 删除 数据库时，为了规范，名字可以加上反引号。
如果名字为关键字，则必须加反引号
加上反引号之后，前面的空格可以省略。

创建或删除多个数据库、表、字段时可以用逗号隔开。
exit，quit，\q 表示退出mysql

DDL data defination language 数据定义语言：
show create alter drop (针对于数据库和表)

alter 修改数据库时，只用于改名字
alter用于修改表时：add，drop，change和modify 可以用在后半句对字段进行添加，删除或者修改。

DML data manipulation language 数据操纵语言：
insert into表，delete from表…where字段…，update表…set字段…where字段…，select…from表…
DCL data control language 数据控制语言

1. 数据库的基本操作

1. show databases; 展示所有数据库
2. create database （if not exits） <数据库名字> charset=gbk; 创建一个数据库，可指定字符编码（gbk，utf8等）
3. show create database <数据库名字>; 展示某数据库是怎么创建的（可以看到字符编码）
4. alter database <数据库名字> charset=gbk; :更改某一个数据库的字符编码
5. drop database （if exits） <数据库名字>; 删除一个数据库

2. 表和字段的基本操作

一个数据库会有很多的 table，当进入某一个数据库时，将进行表的操作。

(直接) 1. use <数据库名字>;表示操作某个已存在的数据库

然后在此数据库(school)基础上：

2. show tables; 表示展示该数据库下的所有表

3. 创建表

create table if not exists teacher(
id int(10) auto_increment primary key comment '主键id' ,    #该字段下数据一定 not null ，
name varchar(30) not null comment '老师的名字',        #字段下的数据必须 not null
phone varchar(20) comment '电话号码',                  #字段下的数据可以为null
address varchar(100) default '暂时未知' comment '住址', #字段下的数据可以为null，默认值为'暂时未知'
age int(10) comment '年龄'
)engine=innodb;

解析：
如果设置为not null 那就表示该字段不能为空，后续必须添加数据，如果没有设置，则数据可加可不加。
default '暂时未知' 表示如果在该字段下 插入 default 或者 不插入数据（不是插入null），则该字段的数据默认为 创表时 default 后的内容。
comment '住址' 表示在该字段的注释，单引号内为注释内容
auto_increment 表示自动增长（0，1，2，…,n），只能为unsigned，且一定为主键（创表时后面一定要写上primary key），默认设定为非空。
primary key 表示为主键，靠它来区分学生表

3. show create table teacher; 表示展示某个表是怎么创建的。

（直接）4. desc <表名字>; 展示该表的结构

5. alter table teacher rename to teacher2; 修改已经存在的表的名字
6. ：drop table if exists <表名字>; 表示删除表

添加或删除字段：

7. alter table teacher add age int(11); 表示在表中添加字段
   (可在后面写after <字段名>，表示在已存在的某字段后面插入age字段)
   (可以在后面写first，表示该age字段插在第一个位置)
8. alter table teacher drop <字段名>; 表示在表中删除某字段

修改字段

9. alter table teacher change <字段名> tel_phone int(11) 表示修改表中某字段的 名字和数据类型， <字段名>后面为修改的内容。 （alter table teacher change phone tel_phone int(11)）
10. alter table teacher modify <字段名> int(15); 该语句只能修改表中某字段的数据类型
    （ alter table teacher modify phone int(15);）

3. 数据操作 CURD

进入某一个数据库后：
（直接）

增 Create

插入一条数据

1. insert into teacher (id,name,phone,address,age) values (1,'tom','13846785749','Beijing',14); 表示按照所给字段顺序向teacher表中插入一条数据
2. insert into teacher values (null,'jack',null,null,null);
   插入数据的顺序为创建表时创建的字段顺序
   由于id自增，所以赋null后，会自动增长，name不可为null，后面三个都可为null
3. insert into teacher values (null,'jack',null,default,null); 当插入的数据为default或不写时，数据变为之前创表时address字段的默认值

插入多条数据

insert into teacher (id,name,phone,address,age) values (null,'unim','14568978856','heihe',12),(null,'retyn',null,default,null);

删 Delete

1. delete from teacher where id=5; 在teacher表中，删除id为5的一条数据（按照id删）
2. delete from teacher where name='jack'; 在teacher表中，删除名字为jack的数据（按照name删）
3. delete from teacher where age>20; 在teacher表中，删除age大于20的数据（按照age删）

不是删除表，是清空表中数据：

4. delete from teacher; 清空表（速度慢，重新插入数据时，有 id沿着之前的数字继续增长 的bug）×
   (推荐)truncate table teacher; 清空表（速度快，无bug，重新插入数据时，id重新从1开始）√

改 Update

1. update teacher set name='tom',age=18 where id=1; 将teacher表中id为1的同学的 name 和 age 进行对应的更改
2. update teacher set name='jjjj',age=20 where id=3 or id=4; 将teacher表中id为3 和 id为4的同学的 name和age 进行对应的更改。

查 Retrieve

1. select * from teacher; 表示查询teacher表中所有字段的数据
2. select id,address from teacher; 表示查询teacher表中 id和address字段的数据内容

四、数据类型

严格按照 项目需要 来选择数据类型

数值类型

无符号：int unsigned(5)、double unsigned(3,1)等等，后面加上 unsigned 即可。
如果设置的类型为 tinyint(5) ，设置值时宽度可以比5大，不会报错，也会照常显示，但是设置值时不可以超过 tinyint 的表示范围。

create table t_1(
number_1 float(3,1) #表示整数位数不会超过3-1，小数位数为1，如果设置的小数位数超过1，则四舍五入
number_2 double(5,2) #表示整数位数不会超过5-2，小数位数为2，不足补零，如果设置的小数位数超过2，则四舍五入
);

浮点数 float 和 double 会丢失精度，使数值不精确，一般用定点数decimal来表示小数，数值准确

对于浮点数和定点数类型，必须严格按照括号中的要求设置整数位数：
例如 double(m,n)
设置数时，整数位数一定不可以超过m-n位
显示数据时，整数位数的数为不会超过m-n，小数位数为n

字符串类型

varchar 是变长字符串，如果设置的字符个数小于括号中的值，多余的字符会自动回收。

布尔类型

create table test1(
number boolean
);

boolean 为 bool 类型，设置数据时true对应显示结果为1，false对应显示结果为0。

insert into test1 values(true)

枚举类型

create table test(
gender enum('man','woman','it','?')
);

对于枚举类型，向表中插入数据时，只能插入 设定好的内容 或者 对应整数，括号中的每个字符串分别是 smallint 类型的0,1,2,3
只占两个字节，速度快，节省空间。

insert into test values('woman');#insert into test values(2);

set集合类型

create table test2(
hobby set('math','chinese','english','IT','philosophy')
);

对于集合类型，向表中插入数据时，可以插入多个设置好的东西 或者 对应整数
当传入整数时，只能传一个数且必须传的是和
括号中的字符串分别是整数的 2⁰,2¹,2²,2³,2⁴

insert into test2 values('math,chinese');#insert into test2 values(3);

时间和日期类型

create table test_1(
TIME1 datetime
);

注意：以字符串形式插入，要符合格式

insert into test_1 values('2022-8-6 15:42:30');

五、列属性完整性

主键，复合主键，唯一键

概念 :
被挑选出来，作表的行的惟一标识的候选关键字(数据不可重复)。
一个表只有一个主键，主键可以由一个字段，也可以由多个字段组成，分别成为 单字段主键 或 多字段主键

作用：
1）保证实体的完整性;
2）加快数据库的操作速度
3）查看表时按主键值的顺序显示表中的记录。如果没有定义主键，则按输入记录的顺序显示表中的记录。

注意：
4）如果在 创建表时 或者 修改表时 已经定义好了单个字段或多个字段(复合主键)为主键，那么就不可以再继续添加该表中的其他字段为主键。
5）主键的值不可重复，也不可为空（NULL），除非是auto_increment。。
6）auto_increment 的字段必须设置为主键（后面跟上primary key）才可创建，且不可以删除该主键。

提示：
1）虽然并不总是需要主键，但是设计数据库时最好保证创建的每个表具有一个主键，以便以后的数据库的操作和管理。
2）尽量设定 不会更改数据的字段 作为主键。
3）如果在创建表时，没有说明哪一个是主键，可以在 创建表之后 再对主键进行说明：（不建议）

create table test(
id int(10),
name varchar(20),
);

将test表中的 id字段 设置为主键：

alter table test add primary key (id);

将test表中的 id字段 和 name字段 设置为主键：

alter table test add primary key (id,name);

消除 test表中的主键（如果创建复合主键，只能一次性全部删除）：

alter table test drop primary key;

关于主键和复合主键

1. 一个表中只能有一个主键，多字段主键为复合主键，也是一个主键。
2. 我们都知道主键字段的数据不能重复！但是对于多字段主键来说，只要保证这些个主键字段的数据不完全相同即可，有一个相同是没有问题的。例如有两个字段分别为 编号 和 昵称 ，可以添加数据 01,宝宝 和 01, 妈妈，是不会报错的。

关于主键和唯一键

主键和唯一键的共性是 数据不可重复！

1. 主键是唯一的且用来区分数据，但是唯一键 不用来区分数据
2. 主键不可为null，但是唯一键 可以为null
3. 主键只能有一个，但是 唯一键可以为多个
4. 主键可能与其他表有关联，但是唯一键不用来联系其他表，只在一个表中起作用

创建 唯一键：
创建唯一键的语法与创建主键相同，把primary key 改成 unique key 即可，与主键不同的是可以继续添加唯一键，因为性质3。
删除 唯一键：
(只能一个一个删除)

alter table test drop index name;

注释

代码注释

单行注释：#巴拉巴拉
多行注释：/ * 巴拉巴拉 * /

sql内注释

创建表时的comment语句，前面有写。

外键（不常用）

注意：

1. a表的主键可以当作b表的外键，主键和外键的数据类型必须一致
2. 通过desc 某张表，具有mul的为外键。
   通过show create table 某张表，constraint后面的是外键的名字，删除外键的时候会用到。
3. 删除表时，先删除从表，再删除主表

1. 外键的严格性操作

创建外键过程：

创建主表：

create table student(
stu_id int(10) primary key, #学号
name varchar(10) not null,  #姓名
age int(3)				    #年龄
); 

创建从表时设置外键：

最后一条语句的意思是将canteen表中的kit_stu_id设置为外键
设置为student表中的外键参考于stu_id。
结果：MUL 的意思是，该字段数据可以重复

create table canteen(
order_num int(10) primary key, #订单号
kit_stu_id int(10), #学号
price decimal(10,4), #价格
foreign key(kit_stu_id) references student(stu_id)
);

修改表时设置外键：（不建议）

alter table canteen add foreign key (kit_stu_id) references student(stu_id);

删除外键过程：

show create table canteen;

 alter table canteen drop foreign key canteen_ibfk_1; #key后面的单词不加括号

2. 置空操作 (多用来删除数据 )

主表中，包含主键字段（stu_id）的 一条 数据被删除时：如果为置空操作，则从表中外键字段（kit_stu_id）与主键对应的 一个 数据会被设置为 null

3. 级联操作 (多用来更新数据)

主表中，包含主键字段（stu_id）的 一条 数据被更新时：如果为级联操作，则从表中外键字段（kit_stu_id）与主键对应的 一个 数据也会被更新

置空操作，级联操作的代码演示

创建主表：

create table student(
stu_id int(10) primary key, #学号
name varchar(10) not null, #姓名
age int(3)); #年龄

在创建从表规定外键时，就要设置 置空操作 和 级联操作

create table canteen(
order_num int(10) primary key, #订单号
kit_stu_id int(10), #学号
price decimal(10,4), #价格
foreign key(kit_stu_id) references student(stu_id) on delete set null on update cascade #删除时置空，更新时级联
);

主表和从表的结构：

主表插入数据：

 insert into student values(1,'John',18)，(2,'Tom',19)，(3,'Bob',20);

查询主表：

从表插入数据：

insert into canteen values(001,2,15.6),(002,3,14.78),(003,1,25.6),(004,2,29),(005,2,40.5);

查询从表：

主表更新数据：

update student set stu_id=99 where stu_id=2;

查询主表（成功update）：

查询从表：（成功级联）

主表删除数据：

delete from student where stu_id=99;

查询主表（成功delete）：

查询从表（成功置空）：

六、数据库设计思维

对于表来说：
每一行就是一个实体（一条数据），每一列就是一个属性（一个字段）

关系型数据库实体和实体之间的关系：
主表->从表：一对一，一对多，多对一，多对多的关系

codd：
X->Y，则说明属性 字段x 确定 字段y ，则y函数依赖于x。
第一范式：保证每个字段的原子性。
第二范式：每个表干自己的事，别瞎扯淡。例如学生表就放学生的基本信息，不要放无关的东西，消除部分依赖。
第三范式：消除传递依赖和部分依赖，若满足第三范式则一定满足第二范式。

七、 单表查询

select...from...where...group by...having...order...by...

select

select表示查哪些字段的内容

三种形式如下：

1. select表示查询，可当计算器使用，select后面的式子为字段名，结果为数据
   select...as... ：前省略号为数据，后省略号为自己起的 字段别名
   也可以像最后一种直接查

2. select 14*15 as result from dual;

from dual表示从伪表查询，可以省略。

from

from表示从哪个表查

select * from t1,t2; 当 from 后面是两个表时，查询时，会显示笛卡尔积的组合形式。
注意：t1和t2之间用 and 或者 or 都会报错。

筛选条件语句

where

where 表示对原有表中的数据进行筛选

符号：

1. =，!=，>，<，>=，<= 用在字段和数据之间
2. and，or，not 并 或 非，用在几个等式之间
3. between...and... 用在同一个字段的数据之间
4. in用在同一个字段的数据之间

例子：

1）如：
select * from student where age > 18 and address = '上海';
select * from student where not id >=2;
2）如：
select * from teacher where id = 1 or id = 2;
select * from teacher where id in (1,2);
select * from teacher where id between 1 and 2;
上述三个语句一个意思，in的相反语句为not in，betwee…and…的反语句为not between …and…
3）如：
select * from teacher where score is null; 查询空数据不能用 =null，反语句为not null

like 模糊查询

select * from student where name like '张%'; 查询student表中name字段的数据为张姓的几条数据
select * from student where name like '张_'; 查询student表中name字段的数据为张姓并且名只有一个字的几条数据。

having

having表示对经过 聚合函数 或者 分组 之后的虚拟表的数据进行筛选
多与聚合函数和group by搭配使用

like 模糊查询

where和having的区别

1. 对于执行顺序，where 早于 group by 早于 having，where子句在聚合前先筛选记录，也就是说作用在group by 子句和having子句前，而 having子句 在聚合后对组记录进行筛选.
2. 代码举例：

筛选出甲地、乙地、丙地这三个地区和他们各自的学校数量

select region,count(sch_number) 
from school 
where region in ('甲' , '乙' , '丙') 
group by region；

筛选出甲地、乙地、丙地的学校数量 超过5所 的地区和各区的学校数量。

select region,count(sch_number) 
from school 
where region in ('甲' , '乙' , '丙') 
group by region 
having count(sch_number) > 5;

在having语句中也可以使用。

limit

limit m,n：表示从第m+1条数据开始(包含此条数据)，查n条数据
limit n：表示默认从第一条数据开始(包含此条数据)，查n条数据

select * from info limit 0,2;

select * from info limit 1,3;

select * from info limit 3;

升序或降序排列

select * from info order by age desc; 按照age降序排列

select * from info order by age asc; 按照age升序排列（默认）

聚合函数

函数名+(字段名)

如：
select sum(chinese) from score; 求所有人chinese的总和
select avg(chinese) from score; 求chinese的平均值
select max(chinese) from score; 求chinese的最高分
select min(chinese) from score; 求chinese的最高分
select count(chinese) from score; 统计拥有chinese字段的数据条数

分组查询

group by

group by 要和聚合函数搭配使用

需求：按照性别分组，查询男性和女性各自的平均年龄。

语句：select avg(age) as '平均年龄',gender as '性别' from info group by gender;

group by...后面的语句很重要，表示按谁分组，as是将前面的字段名起个别名

group_concat

group_concat (字段) ：表示将该字段的数据合在一起，group by 表示按照谁合在一起。

select group_concat(id),gender from info group by gender;

去重

select all gender from info; all可以省略

select distinct from info;

八、多表查询

内连接 inner join

用法：
内连接的两个表要有公共逻辑的字段，且两个表中的该字段(id/stu_id)的数据必须一致才可以显示出来。
分析：
此例子因为两个表 student.stu_id = scoree.id 的公共数据部分为1，2，4，所以查询结果只显示三条数据。
例子：

查询语句：

 select name,score 
 from student inner join scoree 
 on student.stu_id = scoree.id;

inner可以省略，select student.name,scoree.score 更规范

查询结果：

其他：
如果要查询两个表中的全部内容，语句如下：

select *
from student inner join scoree 
on student.stu_id=scoree.id;

左外连接 left join，右外连接 right join

注意：
from student left join scoree
from student right join scoree
所谓的左和右，是根据上述语句的左右来判断的

左外连接（left join）：以公共字段为基础，左表中要查的字段的数据全部列出来，右表中要查的字段的数据没有的直接置为null

 select name,score 
 from student left join scoree 
 on student.stu_id = scoree.id;

（此结果就是以左表中的name字段为基础，把数据全列出来，右表的score有数据就写，没有数据就为null）

右外连接（right join）：以公共字段为基础，右表中要查的字段数据全部列出来，左表中要查的字段的数据没有的直接置为null

略

自然连接 natural join（不常用）

定义：
要用自然连接，就不用写on了，因为它会默认以两个表中字段名字相同的那一个字段为基础，查询相应的字段数据。

注意：
所以要使用自然连接，必须保证两个个表中 有且仅有一个 相同名字的字段，如果没有相同名字字段，则返回笛卡尔积形式，如果多于一个，返回空表。

自然连接：natural join 类似没有on的内连接
自然左连接：natural left join 类似没有on的左外连接
自然右连接：natural right join 类似没有on的右外连接

交叉连接 cross join（不常用）

交叉连接(cross join)：又称笛卡尔连接(cartersian join)或叉乘(product)。

查询语句：

select * from student cross join scoree;

后面不用on。

查询结果：

联合查询 union

用法：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时，多用联合查询

查询语句：

select id,name from teacher
union 
select id,name from student;

在union后面加上distinct或者all，表示去重或不去重。

注意：

1. 多条查询语句的 查询字段数 必须一致
2. 多条查询语句的查询的每一个字段的类型和顺序最好一致
3. union关键字默认去重，如果使用union all 可以包含重复项

九、子查询

分数表：

学生表：

in和not in

需求：
根据两张表，查询大于85分的学生的学号和姓名信息。

查询语句：
以公共的学号字段为基础

 select stu_id,name 
 from student where 
 stu_id in(select id from score where point > 85 ); # not in 表示相反

查询结果：

exists 和 not exists

需求：
只要score表中有point大于85的数据，那么就查询student 表中的所有数据。

查询语句：

select * 
from student where 
exists (select id from score where point > 85 ); # not exists 表示相反

查询结果：

一、视图 view

作用：
增加查询的效率，限定用户查看的表内容

语句：

create view view_1 as
select name from student;

结果：

注意：

1. 在终端里面，通过show tables;是可以同时看到表和视图的，所以创建视图时，最好加上vw_前缀加以区分.
2. 修改视图：alter view view_1 as select stu_id from student;
3. 删除视图：drop view view_1;
4. 如果视图要显示的表格是子查询得来的，那么创建视图的语句应改为

create algorithm=temptable view view_1 as
<子查询>

(默认算法为undefined，此举将算法改为temptable，除此之外，算法还有merge)

二、事务 transaction

事务的特性 ACID

Atomicity 原子性：
事务是数据库的逻辑工作单位，事务中包括的诸多操作，要么都做，要么都不做。

Consistency 一致性：
要么都做，要么都不做。

Isolation 隔离性：
一个事务的执行不能被其他事务干扰。

Durability 持续性：
也称永久性(Permanence)事务一旦提交，对数据库数据的改变就应该是永久性的。

注意：
与钱有关的，最好都要用事务

基本用法

wallet 表：

需求：
以事物的方式，更新id为1和id为2的人的钱数
过程：

执行到这一步，表的数据还没有更改！

输入 commit; 后，前面的操作才算数，数据才进行更新。
如果后悔了，就输入 rollback;取消操作，但是一旦commit;之后，就无法再rollback;了。

注意：
只有在创建表时，engine=innodb 才可以使用事务，默认建表的引擎都是innodb

设置回滚点 rollback to

wallet表：

过程：

savepoint... 表示设置回滚点
rollback to... 表示保存该回滚点之前的操作内容

操作结果：

结果只插入了一条数据。

三、索引 index（了解）

要经常频繁的去查询某一列数据，那么就可以为其设置索引
缺点是会浪费性能、时间。

student为表名，Sno为字段名
建立索引：create [unique] index sno_index on student(Sno)
修改表时添加索引：
alter table student add [unique] index sno_index on student(Sno);
修改索引名： alter index sno_index rename to snno_index;
删除索引：drop index sno_index;

四、存储过程（了解）

在sql语句中，;往往表示为分隔符，还代表着执行了一条sql语句。

所以在创建存储过程前，执行下条操作：
delimiter // :分隔执行符变为//
这样在存储过程中，可以用;将不同sql语句分隔开，但是不执行，最后输入//执行上述的存储过程。

delimiter ; :恢复以前的分隔执行符

案例

wallet 表：


先改分隔符为//

然后创建存储过程（类似于函数），要有begin 和 end
存储过程中的//代表一个存储过程创建完毕。

要想执行存储过程的操作，需要 call proc(); 进行调用
由于在存储过程里嵌套了事务，所以要执行commit;才可以执行存储过程里面的操作。

五、企业规范约束

库表字段约束规范

1. 表示 是或者否 的 字段时，如：
   名：is_vip
   类型：unsigned 的 tinyint
   长度 ：1
2. bool类型不能为is开头
3. 字段名字 必须为小写字母开头，不能为大写字母 或 数字开头。
4. 主键字段名一般为：pk_xxxxx
   唯一键字段名：uk_xxxx
   唯一键索引：idx_xxxxx
5. 表名不能为复数，不能为关键字
6. 字段的类型凡是小数的，不准用double或float，必须用decimal类型
7. 字段的类型是字符类型时，要用char类型，不用varchar
8. 一个表中，必须要有的三个字段:
   id：一定是unsigned的bigint类型、该表与其他表没有绑定时设置为自增
   create_time：datetime类型
   update_time：datetime类型
9. 定义年龄字段时，必须为unsigned 的 tinyint 类型

索引规范

sql开发约束

1. count(xx,xxx,xxxx)不能替代count( * ) ，count( * ) 可查到null的个数。
2. 数据is null 不是 = null
3. 绝对不要使用外键和级联
4. 实际开发过程中，开发人员禁止使用存储过程
5. 数据删除和更新的时候，要先select，看看有没有，然后再操作
6. in的操作，能避免就避免。
7. utf_8

其他约束