数据库总结

数据库

类型

• 网状数据库

• 层次数据库
• 关系型数据库，硬盘（Oracle、DB2、SQL Server、MySQL）
• 非关系型数据库NOSQL ，存在内存（Memecached、MongoDB<Json字符串>、redis、HBase）

MySQL层次 ： 不同项目对应不同的数据库，不同数据库中有不同的表，每张表中存储的是数据

任何数据和NULL进行算术运算结果都为NULL

SQL语言

1. 数据查询语言（Data Query Language，DQL）：一条或多条数据。

   SELECT：查询 distinct(去重关键字)

   • SELECT <*表示全字段，可以用个别字段名替代> as（可省略） 别名（别名里面如果有特殊符号时候必须给别名带上单引号或者双引号，不可省略，其余情况可以省略） FROM <表名> ;

   • WHERE：筛选限制（分组前过滤），binary（区分大小写关键字）

     1. 加关系关系运算符（>、<、>=、<=、<>、!=）

     2. 加逻辑运算符

        • 与关系(&&、and 都是双向开区间、between 是双向闭区间)

        • 或关系（or、||、in），注意：and和or一起用，一般是先and后or

        • 模糊查询（like %任意多个字符、_任意一个字符）

        • NULL（is null ，is not null）

   • order by：排序（默认是asc升序，desc是降序）

   • group by：分组

   • having：分组后进行二次查询

   select语句总结：

   select column , group_function(column)

   from table

   [ where condition ]

   [ group by group_by_expression ]

   [ having group_condition ]

   [ order by column ] ;

   注意：顺序固定，不可改变顺序

   执行顺序：from -> where -> group by ->select -> having -> order by

2. 数据操作语言（Data Manipulation Language,DML）：针对数据

   • INSERT：增
     • INSERT INTO <表名> （所选字段） VALUES （插入的数据）;
   • DELETE：删
   • UPDATE：改
     • UPDATE <表名> SET <字段名 = xxx> （须加WHERE筛选等限制条件）;

3. 数据定义语言（Data Definition Language，DDL）：针对数据库对象（数据库、表、索引、视图、触发器、存储过程、函数）

   • CREATE：创建
     • CREATE TABLE <表名> （字段名 类型（显示长度），字段名 类型（显示长度），…) ;
   • ALTER：修改
     • ALTER TABLE <表名> (ADD增加 / DROP删除 / MODIFY修改列的类型，不改变列名字 / CHANGE修改列名和列的类型定义) <列名 数据类型> (FIRST首位 / AFTER <字段名>放在某个字段之后);
   • DROP：删除
     • DROP TABLE <表名> ;
   • DESC：查看表结构
     • DESC <表名> ;
   • SHOW：展示
     • SHOW CREATE TABLE <表名> ; 查看建表语句

4. 数据控制语言（Data Control Language，DCL）：回收或授予数据库权限

   • GRANT：授予用户权限
   • REVOKE：回收权限

5. 事务控制语言（Transation Control Language，TCL）：

   • START TRANSACTION：开启事务
   • COMMIT：提交
   • ROLLBACK：回滚
   • SET TRANSACTION：设置

快速添加：

• 复制（结构+数据）：create table <新创建的表名> as select * from <要复制的表名> ;

• 复制（仅结构）：create table <新创建的表名> as select * from <要复制的表名> where 1=2 ;

• 复制 (部分列部分数据)：create table <新创建的表名> as select <需要的复制表的字段> from <要复制的表名> where <筛选条件> ;

删除数据：

• delete from <要删除数据的表名> ;
• truncate table <要删除数据的表名> ;

delete和truncate区别：

1. DELETE为数据操作语言（DML），TRUNCATE为数据定义语言（DDL）
2. DELETE操作是将表中所有记录一条一条删除直到删完位置，TRUNCATE保留了表结构，重新构造了表，所有状态相当于新表，所以TRUNCATE操作效率更高
3. DELETE操作可以回滚，TRUNCATE操作会导致隐式提交，不能回滚
4. DELETE操作执行成功会返回已删除的行数，截断操作则不会，DELETE操作删除数据后再添加新数据的时候会继续使用之前的主键自增到的值，TRUNCATE操作则重新从1开始自增。

数据类型

1. 整数类型

MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度，显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示。

2. 浮点数类型

浮点数类型的宽度不会自动扩充，如：score double(4,1)，小数部分为1位，总宽度为4位，并且不会自动扩充。

3.字符串类型

字符串不区分单引号和双引号

4. 日期和时间

插入时间方式多样’2023-08-08’ “2023/08/08” “2023.08.08” ，插入当前时间用now()、sysdate()、CURRENT_DATE()

TIMESTEMP类型数据指定方式与DATETIME基本相同，两者的不同之处在于以下几点：

• 数据的取值范围不同，TIMESTEMP类型的取值范围更小
• 如果未对TIMESTAMP类型字段赋值或者赋值为NULL，MySQL自动将字段赋值为系统当前的日期与时间
• TIMESTAMP类型还可以使用CURRENT_TIMESTAMP来获取系统当前时间
• TIMESTAMP类型可以根据时区显示

DML使用时的一些注意事项：

• 关键字、表名、字段名不区分大小写
• 默认情况下，内容不区分大小写
• 删除操作from关键字不可少
• 修改，删除操作需加限制条件，不然影响数据库全部数据
• 字段名内容、数据内容等字符串不区分大小写

表的完整性约束：

保证数据库中数据的准确性和一致性

约束从作用上分为：

• 表级约束：约束表中任意一个或多个字段，与列定义相互独立，不包含在列定义中，与定义用’,'（逗号）分割，必须要指出约束列的名称

-- 创建表时的SQL：
create table t_student(
			sno int(6) auto_increment,
			sname varchar(5) not null,
			sex char(1) default '男',
			age int(3),
			enterdate date,
			classname varchar(15),
			email varchar(15),
			constraint pk_stu primary key(sno), -- pk_stu主键约束的名字
			constraint ck_stu_sex check(sex = '男' || sex = '女'),
			constraint ck_stu_age check(age >= 18 || age <= 50),
			constraint uq_stu_email unique(email)
);







-- 创建数据库

drop table if exists t_student;

create table t_student(
			sno int(6),
			sname varchar(5) not null,
			sex char(1) default '男',
			age int(3),
			enterdate date,
			classname varchar(10),
			email varchar(15)
);

-- 创建表后添加约束
alter table t_student add constraint pk_stu primary key (sno);
alter table t_student modify sno int(6) auto_increment;
alter table t_student add constraint ck_stu_sex check(sex = '男' || sex = '女');
alter table t_student add constraint ck_stu_age check(age >= 18 || age <= 50);
alter table t_student add constraint uq_stu_email unique(email);

• 列级约束：包含在列定义中，直接跟在该列的其他定义之后，用空格分隔，不必指定列名

如果插入的SQL报错，会浪费掉一个主键的值，之后的主键不连号继续递增

外键约束：指表中的某个字段的值依赖于另一张表中某个字段的值，被依赖的字段必须具有主键约束或者唯一约束，被依赖的叫父表或主表，设置外键约束的表称为子表或者从表。外键约束只有表级约束，没有列级约束。

-- 创建班级表
drop table if exists t_class;

create table t_class (
			cno int(4) primary key auto_increment,
			cname varchar(10) not null,
			room char(4)
);


insert into t_class values(null,'java101','10403');
insert into t_class values(null,'java101','10203');
insert into t_class values(null,'测试101','09403');
-- 一行插入多组数据
insert into t_class values(null,'测试101','09403'),(null,'java101','10203'),(null,'java101','10403');


drop table if exists t_student;
-- 创建表时添加外键
create table s_student(
			sno int(6) primary key auto_increment,
			sname varchar(5) not null,
			classno int(4),
			constraint fk_stu_classno foreign key (classno) references t_class(cno)
);

-- 创建表后添加外键
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class(cno);

外键策略：

• no action：修改从表中与主表关联的字段的内容（全部，也就是说从表中与主表没有关联信息），然后再操作主表对应字段的对应条件

• cascade（级联操作）：删除之前的外键约束，再重新添加新的外键约束，会导致直接把从表中和主表关联的数据删除

  • alter table t_student drop foreign key fk_stu_classon;
    -- 删除之前的外键约束
    
    alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;
    -- 添加新的外键约束
    

• set bull 置空操作：删除之前的外键约束，再重新添加新的外键约束，会导致直接把从表中和主表关联的数据置为NULL

  • alter table t_student drop foreign key fk_stu_classon;
    -- 删除之前的外键约束
    
    alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;
    -- 添加新的外键约束
    

• cascade 和 set null 混合使用

函数

函数是对特定功能进行的封装，函数只是把真实数据进行了加工然后展现出来，并没有改变数据内容

单行函数： 对每一条输入值进行计算得到相对应的计算结果

多行函数： 对一组数据进行运算，只返回一个结果

除了多行函数都是单行函数，多行函数仅有（max、min、count、sum、avg）

select abs(-5) 绝对值,cell(5.3) 向上取整,floor(5.9) 向下取整, round(3,14) 四舍五入 from dual ;
-- 如果没有where条件可以不用加from dual（伪表）

-- 时间函数
select curdate() 年月日,curtime() 时分秒;
select now() 当前时间,sysdate() 函数执行的的日期时间,sleep(3),now() ,sysdate() from dual;

-- 流程函数
-- if相关
select sno , if(sno>=2600,'高工资','低工资') as '薪资等级' from s_student ;-- if-else双分支
select sno , ifnull(sno,0) from s_student ; -- 如果sno是null，则取值为0，否则取本身的值
select nullif(1,2) , nullif(1,1) from dual; -- 如果两值相等，返回null，不过不相等，返回第一个值
-- case 相关
-- case 等值判断
select empno,ename,job,
case job
when 'CLERK' then '店员'
when 'SALESMAN' then '销售'
when 'MANNAGER' then '经理'
else '其他'
end '岗位',
sal from emp ;

-- 区间判断
select empno,ename,sal,
case 
when sal<=1000 then 'A'
when sal<=2000 then 'B'
when sal<=3000 then 'C'
else 'D'
end '工资等级',
sal from emp ;

-- 其他函数
select database(),user(),version() from dual ;

多表查询

99语法：

• **交叉连接：**笛卡尔积查询，通过where筛选数据

select  (*)  from  <表1>  cross  join  <表2> ;（cross在MySQL中可以省略）

• 自然连接：优点也是缺点：自动匹配所有同名列

select *
from  <表1>
natural join <表2> ;  

-- 优化查询效率，指定字段查询
select 表1.字段名A , 表1.字段名B , 表2.字段名A , ....
from  <表1>
natural join <表2> ;

-- 优化字段名多，起别名
select 1.字段名A , 1.字段名B , 2.字段名A , ....
from  <表1> 1
natural join <表2> 2;

• **内连接：**显示不同表中不同名或相同名字段都有数据的匹配信息（其中一条无数据则直接不显示）

-- 相对自然连接的多个同名列，使用using子句
select *
from 表1 
inner join 表2  -- inner可以不写
using(指定表1和表2的 同名 列字段);

-- 优化不同表中不同名的字段，on子句
select *
from 表1 
inner join 表2  -- inner可以不写
on 表1.字段名 = 表2.字段名;

• **外连接（outer可省略）：**相对内连接的只显示都有数据时候匹配到的信息之外，其中有个表中无数据也可以进行匹配显示

-- 左外连接  left outer join 左边表的信息哪怕不匹配也可以显示匹配信息
select *
from 表1
left outer join 表2
on 表1.字段名 = 表2.字段名;

-- 右外连接  right outer join 右边表的信息哪怕不匹配也可以显示匹配信息
select *
from 表1
right outer join 表2
on 表1.字段名 = 表2.字段名;

-- 全外连接  在MySQL中不支持，但是在oracle中支持   （展示左右表全部不匹配信息）
select *
from 表1
full outer join 表2
on 表1.字段名 = 表2.字段名;

-- MySQL 中不支持全外连接的解决方案：union 并集
select *
from 表1
left outer join 表2
on 表1.字段名 = 表2.字段名;
union -- 并集（union去重，union all不去重），将左外连接结果和右外连接结果取并集
select *
from 表1
right outer join 表2
on 表1.字段名 = 表2.字段名;

• **自连接：**自己的某个字段和自己的另一个字段有关联

92语法

• **相当于99语法的交叉连接：**笛卡尔积查询，通过where筛选数据

select * -- 可用字段替代
from 表1 , 表2 ;

• 相当于99语法的自然连接

select * -- 可用字段替代
from 表1 , 表2 
where 表1.字段 = 表2.字段 and 筛选条件;
-- where后面是连接条件，and后面是筛选条件

子查询

一条SQL语句中含有多个select，先执行子查询，在执行外查询。

• 单行子查询：查询结果只有一行
• 多行子查询：查询结果有多行（条件筛选可以使用in、any、=）
• 不相关子查询：子查询可以单独执行
• 相关子查询：子查询不能单独执行

事务

用来维护数据库的完整性，保证一系列的MySQL操作，要么全部执行，要么全不执行。

特性：

• **原子性（Atomicity）：**不可拆分
• **一致性（Consistency）：**状态一致（提交或回滚）
• **隔离性（Isolation）：**各个事务互不影响
• **持久性（Durability）：**数据持久化，不丢失

默认一个DML语句是一个事务

-- 手动开启事务
start  transaction ;
-- 事务的语句

-- 手动回滚
rollback ;

-- 手动提交
commit ;

注意： 在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据

并发问题：

• **脏读（Dirty read）：**事务A读取到了事务B（可能回滚）未提交的数据。
• **幻读（Phantom read）锁表：**多次查询数据不一致（增加或删除数据）
• **不可重复读（Unrepeatable read）锁行：**多次查询数据不一致（修改数据）

事务隔离级别：

-- 查看默认事务级别
select  @@tansaction_isolation ;

-- 设置事务隔离级别 （当前会话）
set  session  transaction  isolation  level  read  uncommitted ;
set  session  transaction  isolation  level  read  committed ;
set  session  transaction  isolation  level  read  repeatable ;
set  session  transaction  isolation  level  read  serializable ;

视图（View）

概念：从单张或者多张基础数据表或其他视图中构建出来的虚拟表，只是一个查询语句。

-- 创建视图
create view 视图名称
as  -- 关联表关键字
select 表中字段或者*
from 表1
join 表2
on 连接条件
where 筛选条案件 ;  -- 可选项

-- 查看视图
select * from 视图名称

-- 操作视图  会影响到数据库数据
insert into 视图名称 .... ;
....
...

-- 
create or replace(替换，试图里有就替换，无就创建) view 视图名称
as  -- 关联表关键字
select 表中字段或者*
from 表1
join 表2
on 连接条件
with check option(校验) ; --建议加上

注意： 视图可以基于视图创建。

存储过程（Stored Procedure）

类似方法的使用对数据库的查询进行了效率化，通过调用存储过程简化操作

• 无返回值的存储过程:

-- 定义一个无返回值的 存储过程
create procedure 存储过程名(in 参数名 参数类型)
begin  -- 存储过程开始
	if 参数 is null or 参数 = "" then -- 分支判断条件
		select * from 表名;  -- 满足条件执行的语句
	else  -- 另一条分支 
		select * from 表名 where 筛选条件 like concat('%',参数,'%');   -- 满足条件执行的语句
	end if;  -- if判断结束
end;  -- 存储过程结束


-- 举例：
create procedure mypro1(in name varchar(10))
begin
	if name is null or name = "" then
		select * from emp;
	else
		select * from emp where ename like concat('%',name,'%');
	end if;
end;


-- 删除存储过程
drop procedure 存储过程名 ;

-- 调用存储过程
call 存储过程名(参数);

• 有返回值的存储过程：

-- 定义一个有返回值的 存储过程
create procedure 存储过程名(in 参数名 参数类型，out 返回值名 返回值类型)
begin
	if 参数 is null or 参数 = "" then
		select * from 表名 ;
	else
		select * from 表名 where 筛选条件 like concat('%',参数,'%') ;
	end if;
	select found_rows() into 返回值名;  -- 返回值执行语句
end;

-- 调用存储过程
call 存储过程名(参数, @返回值名) ; -- 注意这个只是存储过程，返回值另外查询
select @返回值名 ;

-- 删除存储过程
drop procedure 存储过程名 ;

索引

作用： 用来加快数据的访问速度

MySQL数据存储在磁盘，查询数据比较慢，一般卡在IO上面

提高效率：

• 减少IO次数
• 减少IO量（数据量）

局部性原理： 数据和程序都有聚集成群的倾向，同时之前被访问过的数据可能被再次查询，时间局部性，空间局部性。

磁盘预读： 内存跟磁盘在发生数据交换的时候，一般情况下有一个最小的逻辑单元，称之为页（datapage），页一般由操作系统决定多大，一般是4k或8k，而我们在进行数据交互的时候，可以取页的整数倍来进行读取。innodb存储引擎，每次读取数据，读取16k。

索引存储在磁盘，查询数据时，索引优先加载到内存

索引：

• Key：实际数据行中存储的值
• 文件地址
• offset：偏移量

OLAP： 联机分析处理（对海量历史数据进行分析，产生决策性的影响）-> 数据仓库（Hive）

OLTP： 联机事务处理（要求在很短时间内返回对应的结果）-> 关系型数据库（mysql、oracle、db2）

存储引擎： 不同数据在磁盘的不同组织形式

• Innodb（frm（表结构），idb（数据+索引））： 主要是B+树，支持自适应hash
• myisam（frm，MYD（Data），MYI（Index））： B+树
• memory： 使用hash索引

聚簇索引： 数据和索引放在一起（innodb只能有一个聚簇索引，但是可以有很多非聚簇索引，索引Key可以是主键，没有主键就是唯一键，没有唯一键，自动生成一个6字节的rowId，用户不可见）

非聚簇索引： 数据和索引不在一起（myisam）

回表： 从非聚簇索引跳转到聚簇索引中查找数据的过程。

索引覆盖： 当非聚簇索引的叶子节点中包含了查询需要的所有字段时，不需要回表。

索引下推： 把要检索的数据从Server层推到了存储引擎层。

name和age是一个组合索引

![外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传](https://img-home.csdnimg.cn/images/20230724024159.png?origin_url=C%3A%5CUsers%5C%E8%A5%BF%E8%A5%BF%5CDesktop%5Cimage-

主键： 一般是一个列

联合主键： 可以有多个列

索引： 一般是单个索引列
联合索引/组合索引： 可能有多个索引列

最左匹配： 最左前缀原则，又称最左匹配原则，是指在使用联合索引（复合索引）进行查询时，查询条件需要遵循索引中列的顺序，从左到右进行匹配。

假设有一张表，有id、name、age、gender四个字段，name、age是组合索引列，组合索引使用的时候必须先匹配name，然后匹配age

select  *  from  table  where  name=？  and  age=？ -- 走索引

select  *  from  table  where  name=？ -- 走索引

select  *  from  table  where  age=？  -- 不走索引

select  *  from  table  where  and  age=？  name=？  -- 走索引
-- mysql 内部有优化器，会调整对应的顺序

存储引擎： innodb、myisam、memory

– 走索引

select * from table where name=？ – 走索引

select * from table where age=？ – 不走索引

select * from table where and age=？ name=？ – 走索引
– mysql 内部有优化器，会调整对应的顺序

**存储引擎：**innodb、myisam、memory

| 存储引擎 | innodb                                 | myisam                 |
| -------- | -------------------------------------- | ---------------------- |
| 事务     | 支持                                   | 不支持                 |
| 外键     | 支持                                   | 不支持                 |
| 锁类型   | 支持表锁和行锁                         | 支持表锁               |
| 索引     | 5.6之后支持全文索引                    | 一直支持               |
| 索引类型 | 索引的叶子节点直接存放数据（聚簇索引） | 存放地址（非聚簇索引） |