数据库阶段总结（2）
二阶段总结
- 主键
- 自增
- 外键
- 表关系
- 修改表相关SQL的语句
- 关键字where
- 关键字group by
- 聚合函数
- 关键字having过滤
- 关键字dictinct
- 关键字order by
- 关键字limit
- 关键字regexp正则（了解）
- 关键字补充
- 多表查询理论
- 多表查询联表
- 多表查询子查询
- 可视化软件navicat
- python如何操作MySQL
- SQL注入
- 用户管理
- 事务
- 视图
- 触发器
- 存储过程
- 函数
- 索引
约束条件主键
1.单从约束层面上来讲 相当于not null + unique = 非空且唯一
primary key # 主键
语法：
create table 名 (名 类型 primary key);
eg：
create table ta1(id int primary key);
2.但是主键还是innoDB存储引擎组织数据表的依据
''' 1.innoDB中表必须由且只有一个主键 2.如果不指定主键，会采用采用隐藏字段作主键 3.当表中没有主题但是有非空且唯一的字段会自动成为主键 4.主键可以加快查询速度 '''
约束条件自增
当有大量数据要写入数据库时，id量会非常巨大难以手写输入，自增就可以解决这问题
''' 功能： 数据的值以自然数的自动增加 ''' 语法： create table 名( id int pimary key auto_increment, name char(16));
自增补充
'''' 自增不会因为delete删除操作而重置 ''' # 重置自增数据 truncate # 删除数据和重置主键
约束条件外键
''' 外键：记录表与表之间数据关系的字段 ''' 语法： # 用于管联表内 foreign key(员工表的部门id) renferences 部门表(id)
表关系的种类
- 一对多关系
- 多对多关系
- 一对一关系
"""判断表关系遵循 换位思考 的原则"""
外键字段的特性
1.再创建表的时候一定要先创建被关联表(自身没有外键字段的表)
2.在插入数据的时候也是先插入被关联表再插入关联表
3.关联表中只能输入被关联字段中出现的值
问题：
被关联表的数据无法自由删改
解决：
on update cascade # 级联更新，可以自由更新数据
on delete cascade # 级联删除，可以删除更新数据
一对多建表
一对多关系的使用规范：
- "一对多"表关系外键字段建在多的一方
- 先写基本字段类型与约束条件，后写外键
create table emp( id int primary key auto_increment, name varchar(16), age int, job_id int, foreign key(job_id) references dep(id));
多对多建表
# 针对多对多的表关系 外键字段需要建在第三张关系表中 eg: create table bk( id int primary key auto_increment, name varchar(16)); create table author( id int primary key auto_increment, name varchar(16) ); create table rel( id int primary key auto_increment, authorid int, bookid int, foreign key(authorid) references author(id) on update cascade on delete cascade, foreign key(bookid) references bk(id) on update cascade on delete cascade );
一对一建表
'''
在一对一关系中，外键建在任意一方都可以 但是推荐建在查询频率较高的表中
'''
eg:
create table hus(
id int primary key auto_increment,
name varchar(16),
wife_id int unique,
foreign key(wife_id) references wife(id)
on update cascade
on delete cascade
);
create table wife(
id int primary key auto_increment,
name varchar(16)
);
alter table 名 rename 新名；
2.增加字段
语法：
alter table 表名
add 字段名 数据类型 [完整性约束条件…],
add 字段名 数据类型 [完整性约束条件…];
3.删除字段
语法： alter table 表名 drop 字段名;
4.修改字段
# modify只改字段数据类型完整约束，不改字段名，但change可以 语法： alter table 表名 modify 字段名 数据类型 [完整性约束条件];
关键字where
功能：
对整个个表数据的进行一次初步筛选操作
语法：
select 字符名 from 名 where 筛选条件
模糊查询
关键字:like
关键符号：%：匹配任意个数的任意字符
_:一横表示匹配一个字符，多个横表示匹配多个字符
关键字group by分组
功能：
把所有个体通过某种条件组合为某种整体
语法：
select 字符名 from 表名 group by 条件字符名；
聚合函数
主要用于分组之后的数据处理
- max：最大值
- min：最小值
- avg：平均
- sum：求和
- count：计数
关键字having过滤
功能：
功能与where相同，通过条件筛选需要的数据内容
语法：
select 字符名 from 表名 group by 分组字符名 having 条件；
having与where的区别
where用于分组之前的筛选 having用于分组之后的过滤
关键字distinct
功能：
顾名思义此关键字适用于去重
语法：
select distinct post from emp； ''' 去重的前提是数据必须完全相同 '''
# 默认为升序 select * from emp order by salary;
# asc是升序关键字 不写则默认 select name,salary from emp order by salary asc;
# desc是降序的意思，在这里不是显示 select name,salary from emp order by salary desc;
关键字limit
功能：
用于对数据进行分页处理 控制展示信息节省资源
语法：
select * from emp limit 5； # 一个数字表示条数,显示5行
# 第一个数字表示起始位置 第二个数字表示显示个数 起始行不显示
select * from emp limit 3,3;
关键字regexp正则（了解）
功能：
使用特殊符号的组合，在表中来获取符合条件的数据内容
语法：
# 查询姓名是以字母j开头 n或者y结尾的数据 select name from emp where name regexp '^j.*(n|y)$';
关键字补充
group_concat()方法
功能
获取除分组以外其他字段数据，可以拼接字段名（用于分组之后）
例题1：
# 把各部门的员工和性别输出 select post,group_concat(name,':',gender) from emp group by post;
concat()方法
功能：
组合字符段和字符段或字符端与符号的组合（用于分组之前）
例题：
# 把部门的员工和性别单列输出 select concat(name,':',gender) from emp group by post;
concat_ws()方法
功能：
插入分隔符（用于分组之前）
例题：
select concat_ws('|',name,age,salary,gender) from emp where age>30;
as语法
功能:
多用于别名
例题1：
# 可以给查询出来的字段名起别名 select id as 'num',name as 'name' from emp;
多表查询理论
功能：
结合多个表的数据，进行查询
多表查询联表
联表理念
将多个表合并为一个表来使用
关键字：
inner join ：
内连接 将两表中通过相应的数据连结起来，只显示有对应关系的。运用较广
多表查询子查询
思路：
分布操作
方法：
将表的查询结果当作一条sql语句使用
使用Navicat的目的
在实际工作中效率非常重要，可视化软件可以大大提升工作效率 它可以充当很多数据库的客服端的软件
python如何操作MySQL
实现python代码操作MySQL需要借助第三方模块
该模块：也是由他人所写传到网上的
所以使用第三方软件需要网上下载
运程仓库
pip3下载模块时默认都是从国外的仓库下载，所以下载速度很慢，我们可以切换到国内的模块
下载
pip3 install 模块名 -i 仓库地址 --trusted-host 仓库地址
pip3下载模块报错
1.报错信息里面含有timeout
原因：网络不稳定
解决：检查网络或重复执行
2.报错信息里面含有warning警告版本过低
原因：pip版本过低
解决：拷贝提示信息里的更新命令运行既可
3.报错信息里面没有任何关键字就是一堆红色字体
原因：下载模块对计算机环境有要求
措施：下载之前预先准备好环境（百度搜一下）
SQL注入
通过特殊字符和特俗语法的组合来实现登陆成功 # 如何解决上述现象 在sql注册过程中涉及到关键数据时不要手动输入 交由固定方法拼接
用户管理
创建用户
creater user 用户名 identified by '密码'；
# 修改密码
set password for 用户名 =password('新密码')；
set password = password('新密码'); # 针对当前用户
重命名
rename user 新用户名 to 旧的名；
删除用户
drop user 用户名；
查看用户访问权限
show grants for 用户名;
# 新创建的用户没有任何权限
授予和撤销权限
# 授予用户对某数据库下所有表使用select权限
grant select on 库名.* to 用户名;
# 整个服务器 grant all/revoke all on ... # 整个数据库 on 库名.* # 特定表 on 库名.t1
# 撤销权限 revoke select on db1.* from 用户名;
事务
四大特性：ACID
A：原子性
每一个事物都是整体，不可分割的，事物中的多条代码同时执行同时失败
C：一致性
事物会将内部数据从一种一致的状态转换到另一种一致的状态
I：独立性
事物之间互相独立，互不干涉
D：永久性
事物内的数据改变、确定之后，将会是永久不变的
视图
什么是视图
通过查询得到的表，进行保存，可以重复使用该表
为什么要使用视图
如果要频繁使用一张虚拟表，可以不用重复查询
触发器
在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器
# 为何要用触发器
触发器专门针对我们对某一张表数据增insert(前后)、删delete(前后)、改update(前后)的行为
这类行为一旦执行就会触发触发器的执行，即自动运行另外一段sql代码
语法：
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
针对触发器的名字有一个小习惯
tri_before_insert_t1
触发器简写_之前或之后_操作_表名
# 删除触发器
drop 触发器的名字
存储过程
类似于python中的自定义函数
语法：
# 用‘$$’代替；
delimiter $$
# 创建
create procedure p1()
begin
select * from cmd;
end $$
delimiter ;
# 调用
call p1()
函数
相对于pythpn中的内置方法
"ps:可以通过help 函数名 查看帮助信息!"
索引
索引在MySQL中也叫做"键"，是存储引擎用于快速找到记录的一种数据结构（b树，b*树，b+树，红黑树）
主键 primary key
除了可以加快查询之外还有其他的功能
唯一键 unique
除了可以加快查询之外还有其他的功能
索引键 index key
除了可以加快查询之外没有其他的功能
外键 foreign key
跟索引半毛钱关系都没有 也不存在提升查询速度一说
索引带来的消极影响
在表中有大量数据的前提下,创建索引速度会很慢
在索引创建完毕后,对表的查询性能会大幅度提升,但是写的性能会降低
