一周内容回顾(9.6-9.10）
day01
- 约束条件之主键
- 约束条件之自增
- 约束条件之外键
约束条件之主键
# 主键
primary key
1.从约束方面相当于not null unique # 非空且唯一
2.从存储方面主键是InnoDB组织数据表的依据
结论
在创建表的时候一般都需要有一个id字段(uid sid pid...)
且改字段应该设置为表的主键字段
联合主键
create table t12(
id int,
name char(16),
primary key(id,name) # 联合主键
);
约束条件之自增
# 自增
auto_increment
创建表、主键字段的固定写法：
id int primary key auto_increment
补充
1.delete from 表名; # 只删除数据
2.truncate 表名; # 既删除数据又重置主键
约束条件之外键
# 外键：
记录表与表之间数据关系的字段
表关系的种类
1.一对多关系
2.多对多关系
3.一对一关系
4.没有关系
如何判断表关系
一对多关系
一个可以，一个不可以，那么员工表与部门表就是"一对多"关系
"一对多"表关系外键字段建在多的一方
外键字段的特性
1.在创建表的时候一定要先创建被关联表
2.插入数据的时也是先插入被关联表再插入关联表
# 只能够填写被关联字段中出现的值
3.被关联表中的数据无法自由删除和修改
4.级联更新/级联删除
级联更新
on update cascade
级联删除
on delete cascade
多对多关系
两边都可以,那么表关系就是"多对多"关系
一对一关系
两边都不可以，那么表关系要么是没有关系要么是"一对一"关系
alter table 表名 rename 新表名;
2.增加字段
alter table 表名 add 字段名 数据类型 [完整性约束条件…],add 字段名 数据类型 [完整性约束条件…];
alter table 表名 add 字段名 数据类型 [完整性约束条件…] first;
alter table 表名 add 字段名 数据类型 [完整性约束条件…] after 字段名;
3.删除字段
alter table 表名 drop 字段名;
4.修改字段
# modify只能改字段数据类型完整约束，不能改字段名,但是change可以
alter table 表名 modify 字段名 数据类型 [完整性约束条件…];
alter table 表名 change 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
alter table 表名 change 旧字段名 新字段名 新数据类型 [完整性约束条件…];
day02
- where
- group by
- having
- distinct
- orderby
- limit
- regexp
数据准备
View Code查询关键字之where筛选
1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
select * from emp where salary in (20000,18000,17000);
模糊查询
关键字 like
关键符号
%:匹配任意个数的任意字符
_:匹配单个个数的任意字符
演示例子
3.查询员工姓名中包含o字母的员工姓名和薪资
select name,salary from emp where name like '%o%';
4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name)=4;
5.查询薪资不在20000，18000，17000范围的数据
select * from emp where salary not in (20000,18000,17000);
6.查询岗位描述为空的员工名与岗位名
# 针对null不能用等号，只能用is select name,post from emp where post_comment = NULL; # 查询为空 select name,post from emp where post_comment is NULL;
分组：按照指定的条件将单个单个的个体分成一个个整体
eg:
select post from emp group by post;
注意事项
分组之后默认只可以直接获取到分组的依据 无法再获取内部单个个体数据
如果想要获取 需要借助于一些方法：
set global sql_mode="strict_trans_tables,only_full_group_by";
聚合函数
# 主要用于分组之后的数据处理
max 最大值
min 最小值
avg 平均值
sum 求和
count 计数
对应SQL语句
1.获取每个部门的最高工资
select post,max(salary) from emp group by post;
2.获取每个部门的最低薪资
select post,min(salary) from emp group by post;
3.获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
4.获取每个部门的薪资总和
select post,sum(salary) from emp group by post;
5.获取每个部门的员工人数
select post,count(id) from emp group by post;
having用于分组之后的过滤
对应SQL语句
统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
可以分三步来想：
# 1.统计各部门平均工资
select post,avg(salary) from emp group by post;
# 2.在分组之前先对数据进行筛选
select post,avg(salary) from emp where age > 30 group by post;
# 3.分组之后还要对数据进行过滤
select post,avg(salary) from emp
where age > 30
group by post
having avg(salary) > 10000;
select distinct gender from emp;
select * from emp order by salary asc; # 升序 select * from emp order by salary desc; # 降序
多条字段排序
select * from emp order by age asc,salary desc;
对应SQL语句
统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp
where age > 10
group by post
having avg(salary) > 1000
order by avg(salary);
# 一个数字表示条数
select * from emp limit 5;
# 第一个数字表示起始位置，第二个数字表示条数
select * from emp limit 5,10;
对应SQL语句
查询工资最高的人的详细信息
# 1.先按照工资降序排序 # 2.然后使用limit限制取第一条
select * from emp order by salary desc limit 1;
# 正则:使用一些特殊符号的组合去字符串中筛选出符合条件的数据
对应SQL语言
查询姓名是以字母j开头 n或者y结尾的数据
select * from emp where name regexp '^j.*(n|y)$';
day03
- 补充知识
- 多表查询理论
- 可视化软件navicat
补充知识
group_concat()方法
作用：用于分组之后
# 获取除分组以外其他字段数据 本质可以理解为是拼接操作
concat()方法
用于分组之前
concat_ws()方法
用于分组之前 多个字段相同分隔符情况
as语法
# 可以给查询出来的字段名起别名
select id as '序号',name as '姓名' from emp;
多表查询理论
演示代码
View Code多表查询之联表
将多张表拼接成一张大表 然后在基于单表查询完成
拼接表关键字种类
inner join (内链接)
inner join
# 只链接两种表中都有对应的数据
left join (左链接)
left join
# 以关键字左表为基础展示左表所有的数据 没有对应的数据以null填充
right join (右链接)
right join
# 以关键字右表为基础展示右表所有的数据 没有对应的以null填充
union (全链接)
# 不管有没有对应数据，全部展示，其实就是把左链接和右链接用union连起来
select * from emp left join dep on emp.dep_id=dep.id union select * from emp right join dep on emp.dep_id=dep.id;
将一张表的查询结果括号括起来当成另外一条SQL语句的条件
Navicat是一款可以操作多种数据库的软件 内部其实就是封装了相应的SQL语句
如何破解
破解地址:https://defcon.cn/214.html
老版本破解:http://www.ddooo.com/softdown/59996.htm
功能之链接
点击左上角‘连接’，选择MySQL，输入密码，即可链接
功能之创建
选择连接，右键新建数据库，输入数据库名，选择‘utf8mb4’字符集
双击数据库，选择表，右键新建表，输入字段名和字段类型，设置主键
功能之外键
点击外键，设置完之后选择保存即可
表的连接关系
选择数据库，右键选择‘逆向数据库到模型’，可以看到表之间的连接关系。
建立SQL文件
选择一个数据库，右键选择‘转储SQL文件--结构和数据’，可以生成一个sql文件
使用sql文件
新建一个数据库，右键‘运行SQL文件’，选择文件导入，即可使用
day04
-
python代码操作MySQL
-
# python代码操作MySQL需要借助于第三方模块
# 第三方模块
本质就是网络上其他人写的模块
如果想要使用第三方模块需要基于网络先下载
python如何下载模块
# 步骤一： 在python解释器文件夹下找到scripts目录，接着复制该路径添加到环境变量中
# 步骤二 下载模块的基本语句
方式1:cmd终端
方式2:pycharm终端
方式3:pycharm快捷方式
远程仓库的切换
1.命令临时切换
pip3 install 模块名 -i 仓库地址
2.pycharm更改仓库地址
settings 下载模块界面下方点击manage...
3.永久更改
需要修改python解释器内置的配置文件
pip3下载模块报错
1.含有timeout关键字
原因:当前计算机网络不稳定
措施:多执行几次或者更换网络
2.含有warning警告版本过低
原因:pip3工具版本过低需要更新
措施:直接拷贝提示的更新命令更新即可
3.报错信息里面没有任何关键字就是一堆红色字体
原因:可能是即将下载的模块对计算机环境有要求
措施:下载之前需要先准备好环境，可百度搜索一下
day05
-
-
SQL注入
-
基于pymysql实现用户注册登录
-
事务
-
用户管理
-
索引
-
其他辅助知识补充
pymysql其他操作
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123', # 支持简写passwd
database='db6', # 支持简写db
charset='utf8',
autocommit=True # 自动确认
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 默认可以执行查询操作
# sql = 'select * from teacher'
不同取值
print(affect_rows) # 返回值表示的是执行sql语句之后影响的数据行数
print(cursor.fetchall()) # 从结果中获取所有的数据
print(cursor.fetchone()) # 从结果中获取一条数据
print(cursor.fetchmany(2)) # 从结构中指定获取几条数据
print(cursor.fetchone())
cursor.scroll(2, 'relative') # 相对于当前位置左右移动 正数往右 负数往左 cursor.scroll(2,'absolute') # 相对于起始位置左右移动 正数往右 负数往左
print(cursor.fetchone())
pymysql模块针对增删改
# 默认可以执行查询操作
sql = 'select * from teacher'
# 默认无法执行插入操作
sql = 'insert into userinfo(name,password) values("tony",222)'
# 默认无法执行修改操作
sql = 'update userinfo set name="benNB" where id=1'
# 默认无法执行删除操作
sql = 'delete from userinfo where id=4'
二次确认
affect_rows = cursor.execute(sql)
conn.commit() # 二次确认 提交
自动确认
autocommit=True
SQL注入
代码演示
View Code现象1
# 只需要用户名正确即可
select * from userinfo where name='jasonNB' -- ajsdkjaskldjklasd' and password=''
现象2
# 用户名密码都不需要也可以
select * from userinfo where name='xxx' or 1=1 -- jkasdjaksldklsajd' and password=''
如何解决
sql = "select * from userinfo where name=%s and password=%s" '''pymysql模块针对增删改 需要二次确认''' affect_rows = cursor.execute(sql, (username, password))
用户管理
创建用户
create user 用户名 identified by '密码';
"""修改密码"""
set password for 用户名 = Password('新密码');
set password = Password('新密码'); # 针对当前登录用户
重命名
rename user 新用户名 to 旧用户名;
删除用户
drop user 用户名;查看用户访问权限
查看用户访问权限
show grants for 用户名;
授予访问权限
grant select on db1.* to 用户名;
# 授予用户对db1数据库下所有表使用select权限
撤销权限
revoke select on db1.* from 用户名;
其他
整个服务器
grant all/revoke all
整个数据库
on db.*
特定的表
on db.t1
事务
四大特性:ACID
A:原子性
C:一致性
I:独立性
D:持久性
回退与确认
# 回滚到上一个状态
rollback;
# 确认事务没有问题将无法回退
commit;
视图
视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可
触发器
在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器
存储过程
# 相对于python中的自定义函数
delimiter $$
create procedure p1()
begin
select * from cmd;
end $$
delimiter ;
# 调用
call p1()
函数
基本内置方法
# 移除指定字符
Trim、LTrim、RTrim
# 大小写转换
Lower、Upper
# 获取左右起始指定个数字符
Left、Right
返回读音相似值
# 只对英文效果
Soundex
日期格式
View Code更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datediff 计算两个日期差值
...
流程控制
if条件语句
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
while循环语句
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
索引
索引就是一种数据结构，类似于书的目录
键的分类
主键 primary key
除了可以加快查询之外还有其他的功能
唯一键 unique
除了可以加快查询之外还有其他的功能
索引键 index key
除了可以加快查询之外没有其他的功能
外键 foreign key
跟索引没任何关系也不存在提升查询速度
索引的影响
1.在表中有大量数据的前提下,创建索引速度会很慢
2.在索引创建完毕后,对表的查询性能会大幅度提升,但是写的性能会降低
索引的类别
# 聚集索引(primary key)
# 辅助索引(unique,index)
# 覆盖索引
# 非覆盖索引
浙公网安备 33010602011771号