数据库基础点

数据库

数据库的三大设计范式

1.确保每列保持原子性：所有字段的值都是不可分解得原子值

2.确保表中的每列都和主键有关：一个表只能保存一种数据，不可以把多种数据保存在同一张数据库表中

3.确保每列都和主键列是直接相关而不是简介相关

ps：联接数据库指令：mysql -u用户名-p 输入密码 退出使用exit或者quit；

数据库的类型

1.关系型数据库

MySQl，Oracle，sqlite，db2，sql server

特征：具有表结构，数据与数据之间有关联和限制

2.非关系型数据库

redis,mongodb,memcache

特证：通常都是以k，v键值得形式存储数据，类似于字典

编码：一般都是utf8，utf8mb4：可以存表情

数据库增删改查操作

1.库操作：类似于文件夹

增：create database 库名 charset utf8； 添加库以及定义编码

删：drop database 库名； 删库;

改：alter database 库名 charset='gbk'； 修改编码

查：show databases; 查所有

选择数据库: use 库名

2.表操作：类似于文件

增：create table 表名(id int, name char(4)default 'god')

删: drop table 表名

改：alter table 表名 modify 字段名 char(32)

查：show tables; 查看当前库下所有的表;desc 表名 查单个表

3.记录操作

增： insert into 表名 values(1, 'god'); 插入单条数据

​ insert into 表名 values(1, 'god'), (2, 'godd'), (3, 'goddd'); 插入多条数据

​ insert into 表名(id) values(1);可以指定字段插入

删：delete from 表名 where id 1; 指定删除符合条件的数据；delete from 表名;将表中的数据全部删除

改：update 表名 set name = 'god' where id=1;修改数据的一个字段信息；update 表名 set name='godd', password=666 where id=1;修改数据的多个字段

查：select * from 表名; 查询所有的字段信息

​ select name from 表名; 查询指定字段信息

​ select id,name from 表名 where id=1 or name='god'；查询带有筛选条件的字段信息

清空表：

​ truncate table 表名; # 清空表

​ truncate 会重建表结构，清除主键；不能触发任何Delete触发器

存储引擎

mysql存储引擎

1.Innodb:5.5之后默认的存储引擎，两个文件

2.myisam:mysql5.5以前用的引擎，三个文件

3.memory：内存引擎（数据全部存在内存中），一个文件

4.blackhole：无论存什么 都立马消失（黑洞）， 一个文件

数据类型

1.数字

1.1整型：tinyinit int bigint

1..2小数：float：在位数比较短的情况下不精准；

double：在位数比较长的情况下不精准；

decimal：小数，精准内部原理是以字符串形式去存

unsigned：没有符号的整型，正整数

2.字符串

char(10):浪费空间，存取速度快

varchar：精准，节省空间，存取速度慢

ps：sql优化：创建表时，定长的类型往前方，变长的类型往后放；字符串类型一般255，超了只存路径或者url

3.时间类型

date:年月日

time:几时几分几秒

datetime:年月日时分秒

timestamp:时间戳

year:年份

最常用：datetime

4.枚举类型与集合类型

enum：单选 只能在给定的范围选一个值，比如性别选择

set：多选 在给定的范围内可以选择一个或者多个值（爱好1，爱好2，爱好3）

create table mmy (
    name varchar(40)
    size enum('x-small', 'small', 'medium', 'large', 'x-large')
);	# 在给定的范围内选择
insert into mmy(name, size) values('shirt', 'large'), ('t_shirt', 'medium');

CREATE TABLE myset(col SET('a', 'b', 'c', 'd'));	# 在给定的范围内选择
INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'),('a,d,d'),('d,a,d');

约束

1.not null 非空

2.default 给字段设置默认值

3.单列(id int unique)；联合唯一(unique(ip,port))

4.primary key主键：一般设置在id或编号，具有唯一且默认递增，与auto_increment配合使用

ps:innodb引擎中有且只有一个主键!

5.主键自动递增 auto_increment ps：递增字段只能加载到主键字段

第一种插入方式:不选择id主键字段

第二种插入方式：用0占位

外键

foreign key(自己的id) references 别的表名(别的表名的字段)只是用来建表关系的

foreign key(author_id) references author(id)
表示当前表的author_id 与author表的id字段关联
on update cascade	连级更新
on delete cascade	连级删除
# 先建被关联的表
create table press(
		id int primary key auto_increment,    # 给id设置主键和自动增长
    	p_name varchar(10)
);
# 再建关联的表
create table library(
		id int primary key auto_increment,    # 给id设置主键和自动增长
    	l_name varchar(10),
    	l_price int,
    	press_id int，
    	foreign key(press_id)references press(id)   # 关联的字段
    	on update cascade   # 连级更新
		on delete cascade   # 连级删除
);
# 先插入被关联的表数据：
insert into press(address) values('清华出版社'),('上海出版社'),('北京出版社');
# 插入关联数据表：
insert into library(l_name,l_prince,press_id) values('天下无贼',128,1),

外键约束：

1.在创建表的时候必须先创建被关联的表

2.插入数据的时候，也必须先插入被关联的数据

关键字语法顺序

select,from,where,group by

关键字执行顺序

from, where, group by, select

其他的关键字

1.having

ps:where不能使用聚合函数而having可以使用

select post,avg(salary) from emp where age>=30 group by post having avg(salary) >10000;

2.distinct 去重复

单字段去重

select distinct post from emp;

组合去重

select distinct on (content, comment_name) * from comment_taobao1 where shopping_id='123456'

3.order by 排序

select * from emp order by salary asc; # 默认升序排

select * from emp order by salary desc; # 降序排

先按照age降序排，在年龄相同的情况下再按照薪资升序排列

select * from emp order by age desc, salary asc;

4.limit 限制显示几条

limit 5; 表示限制的个数

limit 0, 5; 第一个参数，表示起始位置，第二个参数表示个数；

5.regexp：正则查询

select * from emp where name regexp '^j.*(n|y)$';

6.like:模糊匹配

%:模糊匹配

_:匹配一个字符

char_length(name) = 4:计算字符长度

select * from teacher where username like '李%'	#	以李开头
select * from teacher where username like '%李'	#  以李结尾的所有
select * from teacher where username like '%李%'	#  匹配所有包含李的

7.inner join 联表查询

7.1、内连接：只取两张表有对应关系的记录

7.2、左连接: 在内连接的基础上保留左表没有对应关系的记录

7.3、右连接: 在内连接的基础上保留右表没有对应关系的记录

7.4、全连接：在内连接的基础上保留左、右面表没有对应关系的的记录

8.子查询

就是将一个查询语句的结果用括号括起来当作另外一个查询语句的条件去用

""" 先获取技术部和人力资源部的id号，再去员工表里面根据前面的id筛选出符合要求的员工信息 """

select * from emp where dep_id in (select id from dep where name='技术' or name = '人力资源');

""" 记住一个规律，表的查询结果可以作为其他表的查询条件，也可以通过其别名的方式把它作为一张虚拟表去跟其他表做关联查询 """

9.exist

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，
而是返回一个真假值，True或False。
当返回True时，外层查询语句将进行查询
当返回值为False时，外层查询语句不进行查询。
select * from emp
where exists
(select id from dep where id > 203);

数据库高级功能

1.视图

语法：create view 新建的视图名 as 连表

目的：当频繁使用多张表的连表结果，你可以事先生成视图后直接调用，避免反复写联表操作的sql语句

2.触发器

达到某种条件自动触发

增加前，增加后: before insert, after insert

删除前，删除后：before delete, after delete

修改前，修改后：before update, after update

针对插入

create trigger tri_after_insert_t1 after insert on 表名 for each row begin
sql语句
end
create trigger tri_before_insert_t2 before insert on 表名 for each row begin
sql语句
end

针对删除

create trigger tri_after_delete_t1 after delete on 表名 for each row begin
sql语句
end
create trigger tri_before_delete_t2 before delete on 表名 for each row begin
sql语句
end

针对修改

create trigger tri_after_update_t1 after update on 表名 for each row begin
sql语句
end
create trigger tri_before_update_t2 before update on 表名 for each row begin
sql语句
end

案例展示

create table cmd(
	id int primary key auto_increment,
    user char(32),
    priv char(10),
    cmd char(64),
    sub_time datetime,	# 提交时间
    success enum('yes', 'no')	# 0代表执行失败
);

create table errlog(
    id int primary key auto_increment,
    err_cmd char(64),
    err_time datetime
);

delimiter $$	# 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after on cmd for each row begin
	if NEW.success = 'no' then # 新的记录都会被mysql封装成NEW对象
    	insert into errlog(err_cmd,err_time) values(NEW.cmd, NEW.sub_time);
    end if;	# 这里必须是;
end $$
delimiter ; # 结束以后要改回来，不然后面的结束符都会是$$

事务

事务包含了一大堆sql语句，这些sql语句要么同时成功，要么一个也别成功

作用

保证了数据操作的安全性

案例理解

当一个银行给另一个银行转账时，这个银行里的钱扣了，然而这个银行的atm机突然故障了，另一个银行消息还未收到，那么钱扣除了却没有到账

四大特性（ACID）

A：原子性；C：一致性；I：隔离性；D：持久性

A：一个事务必须是一个不可分割的工作单位，事务中操作要么都做，要么都不做

C：总量不变，从一个一致性变到另一个一致性

I：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离，并发执行的各个事务之间不能互相干扰

D：一个事务一旦提交，他对数据库中数据的改变应该是永久性的，接下来的操作或故障都不应该对其有影响

如何开启事务

start transaction

# 修改数据之前先开启事务操作
start transaction
# 修改操作
sql语句
# 回滚到上一个状态，未保存，数据在内存中
rollback
# 开始事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit	# 相当于保存，数据刷新到硬盘

# rollback 在没有永久性更改以前，都可以进行回滚。就跟没有板寸之前的撤回一样
# commit 永久性更改，保存数据

索引与慢查询优化

索引在mysql中也叫作"键"，是存储引擎用于快速找到记录的一种数据结构

primary key 聚集索引（主键）

unique key 辅助索引 (主键之外)

index key

primary key，unique key 除了有加速的效果外还有额外的约束条件，而index key没有任何约束功能，只有加速查询

本质

通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们总是可以用同一种查找方式来锁定数据

影响

在表中有大量数据的前提下，创建索引速度会很慢

在索引创建完毕后，对表的查询性能大幅提升，但是写的性能会降低

原理

b+树

只有叶子结点存放真实的数据，根和树枝节点存的仅仅是虚拟数据

查询次数由树的层级决定，层级越低则次数越少

案例

select name from user where name="jsn";
# 此语句为覆盖索引，只在辅助索引的叶子节点上就已经找到了我们想要的数据
select age from user where name="jsn";
# 此语句为非覆盖索引，虽然在查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键去查找

详细实例

单索引

#1. 准备表
create table student(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);

#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into student values(i,'jon','male',concat('jon',i,'@qq.com'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明 分号为结束符号

#3. 查看存储过程
show create procedure auto_insert\G 

#4. 调用存储过程
call auto_insert();

# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;

# 给id做一个主键
alter table s1 add primary key(id);  # 速度很慢

select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason'  # 速度仍然很慢


"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;

alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason';  # 又慢了

create index idx_name on s1(name);  # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason'  # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';  
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性

# 区分度低的字段不能建索引
drop index idx_name on s1;

# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3;  # 快了
select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算

drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速

drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度

create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段

drop index idx_id on s1

create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉 

联合索引

select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3; 
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3; 
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3; 

# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快