MySQL学习(五)
MySQL学习(五)
上周回顾
查询关键字的使用
where筛选
select * from 表 where 条件;
# 对数据进行筛选
group by分组
select * from 表 where 条件 group by 字段名;
# 以字段名对筛选过后的数据进行分组，非主键字段名是，*号处的参数要和字段名保持一致
having过滤
select * from 表 where 条件 group by 字段名 having 条件;
# 进行分组后再次对数据进行一个过滤，where善后版
order by排序
select * from 表 order by 需要排序字段数据; # 默认是升序(从小到大)
select * from 表 order by 需要排序字段数据 asc; # 关键字asc 可以省略
select * from 表 order by 需要排序字段数据 desc; # 降序(从大到小)
distinct去重
select distinct 字段数据 from 表;
# 只能去重完全一样的数据，被封装的数据带上主键之后无法去重
limit分页
select * from 表 limit 条数;
# 限制展示数据的条数
select * from 表 limit 条数,页数;
# 分页效果
select * from 表 order by 字段名 desc limit 1;
# 数据量过大时，需要使用到limit功能控制展示的条数
regexp正则
select * from emp where name regexp '正则表达式';
多表查询
子查询
( SELECT 字段名 FROM 表 WHERE 条件 GROUP BY 字段名 HAVING 条件 )
# 先对一张表进行操作，拿到结果后，使用结果对另外一张表进行操作
SELECT 字段名 FROM 表 WHERE 条件 GROUP BY 字段名 HAVING 条件 ( SELECT 字段名 FROM 表 WHERE 条件 GROUP BY 字段名 HAVING 条件 )
连表操作
将多张表拼接到一起后，直接对达标进行数据操作
内链接 inner join
select * from 表1 inner join 表2 on 表1.外键字段=表2.主键;
# 只会链接两张表都拥有的数据
左链接 left join
select * from 表1 left join 表2 on 表1.外键字段=表2.主键;
# 左表为基准，右表中没有的数据以NULL填充
右链接 ringht join
select * from 表1 right join 表2 on 表1.外键字段=表2.主键;
# 右表为基准，左表中没有的数据以NULL填充
全链接 union
select * from 表1 left join 表2 on 表1.外键字段=表2.主键
union
select * from 表1 RIGHT join 表2 on 表1.外键字段=表2.主键;
# 左右两表数据全部展示 没有对应项则用NULL填充
今日学习内容
python操作MySQL
pymysql模块
下载:pip3 install pymysql
下载完直接导入模块
import pymysql
1.链接服务端
链接对象名 = pymysql.connect(
host='127.0.01', # MySQL服务端的IP地址
port=3306, # MySQL默认PORT地址(端口号)
user='', # 用户名
password='', # 密码 也可以简写 passwd
database='', # 库名称 也可以简写 db
charset='utf8' # 字符编码 utf-8会报错
)
2.产生获取命令的游标对象
光标功能名 = 链接对象名.cursor(
cursor = pymysql.cursors.DictCursor # 不填写默认返回元组数据，添加参数将数据转换为字典
)
3.编写SQL语句
# SQL语句会被高亮显示，因为pycharm默认是无法识别SQL语句的
sql_1 = 'show tables;'
sql_2 = 'select * from 表;'
4.执行SQL语句
用变量名接收 = cursor.execute(sql1);
print(affect_rows)
# 获取SQL语句之后受影响的行数
5.获取结果
res = cursor.fetchall()
print(res)
# fetchall读取所有内容，光标会移动到最后
获取SQL语句执行的结果 跟读取文件内容的read方法几乎一致(光标)
fetchone() # 读取结果为单个元组
fetchmany() # 读取结果为多个元组
fetchall() # 读取完所有内容
cursor.scroll(1, 'relative') # 相对于当前位置往后移动一个单位
cursor.scroll(1, 'absolute') # 相对于起始位置往后移动一个单位
SQL注入问题
注入问题主要是因为外部输入了SQL的注释语法或者逻辑运算符，外部输入的数据改变了原本的代码
解决方式：execute方法
cursor.execute(sql, (需要处理的数据1,需要处理的数据2))
批量插入数据:cursor.executemany(sql,[('1',11),('2',222),('3',333)])
二次确认
数据的增删改查的层级并不相等，查不会影响到数据，直接操作即可；而增删改会影响到真实数据需要二次确认
方式1：代码直接编写
链接对象名.commit() # 二次确认
方式2：配置固定参数
链接对象名 = pymysql.connect(
autocommit=True # 自动二次确认
)
修改表SQL语句补充
1.修改表的名字 rename
alter table 表 rename 重命名;
2.添加字段 add
alter table 表 add pwd int; '''默认是尾部追加字段'''
alter table 表 add tid int after name; '''指定追加位置'''
alter table 表 add nid int first; '''指定头部添加字段'''
3.修改字段 change(名字类型都可)/modify(只能改类型不能改名字)
alter table 表 change 字段名 重命名 tinyint(类型);
4.删除字段 drop
alter table 表 drop 字段名;
视图
概念：通过sql语句临时获得的一张虚拟表，保存下来之后就被称为'视图'
作用：如果频繁使用一张虚拟表，那么我们可以将他保存下来变成视图，降低操作量
视图的制作：
create view 视图名 as sql语句
注意事项：保存的视图会占据在库中的表格里，也会占用资源；视图如果过多会造成库下的表混乱；而且库不支持增删改。
触发器
概念：在对表数据进行增、删、改、的该表数据的操作的时候，就会自动触发的功能
作用：为表操作功能进行扩展，增添定制的功能
种类：表数据新增之前、新增之后；表数据修改之前、修改之后；表数据删除之前、删除之后
触发器创建：
create trigger 触发器名字 before/after insert/update/delete
on 表名 for each row
begin
SQL语句
end
触发器名字建议：
tri_after_insert_表
tri_before_update_表
tri_before_delete_表
补充:临时修改SQL语句的结束符:delimiter $$
临时修改的原因是因为触发器 存储过程等技术点 代码中也需要使用分号如果不修改 则无法书写出完成的代码
实际案例
1.先创建两张表
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment, # 主键int类型，自增特性
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, # 主键int类型，自增特性
err_cmd CHAR (64), # 创建字段名
err_time datetime # 获取时间
);
2.需求:cmd表插入数据的success如果值为no 则去errlog表中插入一条记录
delimiter $$ # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert 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 ; # 结束之后记得再改回来，不然后面结束符就都是$$了
3.仅仅往cmd表中插入数据
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)VALUES
4.触发器其他补充
查看当前库下所有的触发器信息
show triggers\G;
删除当前库下指定的触发器信息
drop trigger 触发器名称;
事物
概念：事物包含诸多SQL语句，并且这些SQL语句；只存在都成功和都失败两种情况
作用：现实生活中，比如说转账，就应该将扣款，和入账做一个事物关联，因为二者一成功则二者成，一失败则皆失败。
四大特性：ACID
A：原子性：一个事务是一个不可分割的整体 里面的操作要么都成立要么都不成立
C：一致性：事务必须使数据库从一个一致性状态变道另一个一致性状态
I：隔离性：并发编程中 多个事务之间是相互隔离的 不会彼此干扰
D：持久性：事物一旦提交，产生的结果应该是永久的
具体使用：
1.创建表及录入数据
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('eason',100000000000)；
2.事务操作
开启一个事务的操作
start transaction;
编写SQL语句(同属于一个事务)
update user set balance=100000001000 where name='jason';
update user set balance=0 where name='eason';
事务回滚(返回执行事务操作之前的数据库状态)
rollback; # 执行完回滚之后 事务自动结束
事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
commit; # 执行完确认提交之后 无法回滚 事务自动结束
存储过程
类似python中的自定义函数，一组为了完成特定功能的 SQL 语句集 , 存储在数据库中 , 经过第一次编译后再次调用不需要再次编译 .
定义：
delimiter $$
create procedure 名字()
begin # 开始
select * from cmd; # 功能
end $$ # 结束
delimiter;
调用：
call p1()
删除：
drop procedure 名字;
带参数存储过程
MySql 支持 IN (传递给存储过程)
OUT (从存储过程传出)
INOUT (对存储过程传入和传出) 类型的参数
存储过程的代码位于 BEGIN 和 END 语句内 , 它们是一系列 SQL 语句 , 用来检索值 , 然后保存到相应的变量 (通过指定INTO关键字)
delimiter $$
create procedure p1(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select tname from userinfo where id > m and id < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 针对res需要先提前定义
set @res=10; 定义
select @res; 查看
call p1(1,5,@res) 调用
select @res 查看
查看存储过程具体信息
show create procedure pro1;
查看所有存储过程
show procedure status;
函数
相当于内置函数
# 1.移除指定字符
Trim、LTrim、RTrim
# 2.大小写转换
Lower、Upper
# 3.获取左右起始指定个数字符
Left、Right
# 4.返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')
"""
# 5.日期格式:date_format
'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datediff 计算两个日期差值
...
流程控制
# MySQL if判断
if 条件 then
子代码
elseif 条件 then
子代码
else
子代码
end if;
# MySQL while循环
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT num ;
SET num = num + 1 ;
END WHILE ;
索引
索引在MySQL中也就是‘键’，除了加速查询的效果之外还有额外的约束条件；回忆我们学过的键：
primary key 主键 非空且唯一
unique key 唯一键 唯一
index key 索引键 无约束条件
索引的基本用法
创建完表后：
# 主外键约束（注:project表中的id字段约束了此表中的projectid字段）
FOREIGN KEY (projectid) REFERENCES project (id),
# 给字段创建了唯一索引(注:也可以在的创建字段时使用unique来创建唯一索引)
UNIQUE INDEX (字段),
# 给字段创建普通索引
INDEX (字段)
索引虽然方便了使用，但是会造成影响，因此不能大量的创建
影响：在表中有大量数据的前提下，创建索引速度会很慢；创建完索引后，对表的查询会更高效，而写的性能会降低
索引的底层数据结构是b+树（b树 红黑树 二叉树 b*树 b+树）
在每一个叶子节点做一个标记，把这些标记存起来，每次查的时候可以在查询根节点的时候从叶子节点也开始查
b树索引：计算机在读取数据的时候，都是按照块来读取的，大约4kb左右；而每次读取数据我们将其称为IO操作，如果计算机读取数据需要每次遍历所有块找数据，会造成资源极度浪费和时间浪费；而树索引极大的节约了计算机的工作时间和效率，这个工作就叫做索引。
聚集索引(primary key)，辅助索引(unique key,index key)
作业
import pymysql
# 1.链接服务端
conn_obj = pymysql.connect(
host='127.0.0.1', # MySQL服务端的IP地址
port=3306, # MySQL默认PORT地址(端口号)
user='root', # 用户名
password='', # 密码 也可以简写 passwd
database='name_data', # 库名称 也可以简写 db
charset='utf8', # 字符编码 千万不要加杠utf-8
autocommit=True
)
cursor = conn_obj.cursor(
cursor=pymysql.cursors.DictCursor
)
sql2 = 'insert into user_data(name,pwd) values(%s, %s);'
sql3 = 'select name from name_data.user_data;'
sql4 = 'select * from user_data;'
# sql1 = 'create table user_data( id int primary key auto_increment,name varchar(32),pwd int)'
# 创建表
def register():
name = input('请输入账号').strip()
cursor.execute(sql3)
res = cursor.fetchall()
for i in res:
if i['name'] == name:
print('您的账户已存在')
return
else:
pwd = input('请输入密码').strip()
affect_row = cursor.execute(sql2, (name, pwd))
if affect_row == 1:
print('注册成功')
#
def log_in():
name = input('输入您的账户').strip()
pwd = int(input('输入您的密码').strip())
cursor.execute(sql4)
res = cursor.fetchall()
for i in res:
if i['name'] == name and i['pwd'] == pwd:
print('登录成功')
return
else:
print('账户或密码错误')
def run():
number = input('输入1登录，输入2注册>>>:')
if number == '1':
log_in()
elif number == '2':
register()
else:
print('请输入正确的编号')
if __name__ == '__main__':
run()
小结
努力学吧，调整好状态
浙公网安备 33010602011771号