一周目，mysql八日

理论补充

pymysql操作补充

使用pymysql进行插入修改和删除操作时必须进行二次确认，即conn.commit()

也可以在链接操作时添加autocommit=True

import pymysql
conn = pymysql.connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    password='123',  # 支持简写passwd
    database='db6',  # 支持简写db
    charset='utf8',
    autocommit=True  # 自动确认

查询操作　　sql = 'select * from teacher'　　默认可执行

插入操作　　sql = 'insert into userinfo(name,password) values("tony",222)'　　默认不可执行

修改操作　　sql = 'update userinfo set name="jasonNB" where id=1'　　　　默认不可执行

删除操作　　sql = 'delete from userinfo where id=4'　　　　　　　　　　默认不可执行

affect_rows = cursor.execute(sql)　　　　　　　　affect_rows 是sql语句影响的行数

print(affect_rows)  
# 返回值表示的是执行sql语句之后影响的数据行数
print(cursor.fetchall()) 
# 从结果中获取所有的数据
print(cursor.fetchone())
# 从结果中获取一条数据
print(cursor.fetchmany(2))  
# 从结构中指定获取几条数据
cursor.scroll(2, 'relative')  
# 相对于当前位置左右移动 正数往右 负数往左
cursor.scroll(2,'absolute')  
# 相对于起始位置左右移动 正数往右 负数往左

SQL注入

import pymysql
# 1.先获取用户名和密码
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 2.链接MySQL
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)
# 3.拼接查询sql语句
sql = "select * from userinfo where name=%s and password=%s"
print(sql)
'''pymysql模块针对增删改 需要二次确认'''
# affect_rows = cursor.execute(sql, (username, password))
# 如果想一次性插入多条数据 可以使用下面的方法
affect_rows = cursor.executemany(sql, [('jason', 123), ('jason1', 123), ('jason2', 321), ('jason3', 222)])
res = cursor.fetchall()
if res:
    print(res, '登录成功')
else:
    print('用户名或密码错误')

如果是手动拼接，产生的现象

现象1
只需要用户名正确即可
select * from userinfo where name='jasonNB' -- ajsdkjaskldjklasd' and password=''　　#利用——注释了密码
现象2
用户名密码都不需要也可以
select * from userinfo where name='xxx' or 1=1 -- jkasdjaksldklsajd' and password=''　　#1=1恒成立，获取所有用户名以及密码

利用一些特殊符号和特殊语法的形式拼接出违背常理的语句
如何解决上述现象
在SQL注入中涉及到关键性的数据不要自己手动拼接
交由固定的方法拼接(方法会自动过滤掉特殊符号)

用户管理

1.创建用户
    create user 用户名 identified by '密码';
     """修改密码"""
      set password for 用户名 = Password('新密码');
    set password = Password('新密码');  # 针对当前登录用户
2.重命名
    rename user 新用户名 to 旧用户名; 
3.删除用户
    drop user 用户名;
4.查看用户访问权限
    show grants for 用户名;
5.授予访问权限
    grant select on db1.* to 用户名;
  # 授予用户对db1数据库下所有表使用select权限
6.撤销权限
    revoke select on db1.* from 用户名;
"""
整个服务器
    grant all/revoke all
整个数据库
    on db.*
特定的表
    on db.t1

事物

事物有四大特性：ACID，分别是

A:原子性
　　一个事务是一个完整的整体不能分割要么同时成功要么同时失败
C:一致性
　　一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态
I:独立性
　　事务与事务彼此不干扰相互独立
D:持久性
　　事务一旦被提交了，那么对数据库中的数据的改变就是永久性的

事物操作

# 修改数据之前先开启事务操作
start transaction;
# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tony'; #卖家拿到90元
# 回滚到上一个状态
rollback;
# 确认事务没有问题
commit;  # 确认之后就无法再回退

视图

# 1、什么是视图
    视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用即可
# 2、为什么要用视图
    如果要频繁使用一张虚拟表，可以不用重复查询
# 3、如何用视图
    create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;

触发器

定义

在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器

为何要用触发器

触发器专门针对我们对某一张表数据增insert(前后)、删delete(前后)、改update(前后)的行为，这类行为一旦执行就会触发触发器的执行，即自动运行另外一段sql代码

语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
    sql语句
end
针对触发器的名字有一个小习惯
    tri_before_insert_t1
        触发器简写_之前或之后_操作_表名

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 ;  # 结束之后记得再改回来，不然后面结束符就都是$$了

# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;

存储过程

# 相对于python中的自定义函数
delimiter $$
create procedure p1()
begin
    select * from cmd;
end $$
delimiter ;
# 调用
call p1()

sql中的函数

# 相对于pythpn中的内置方法
"ps:可以通过help 函数名    查看帮助信息!"
# 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 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 ;

不能直接运行需要放在存储过程中才能运行

索引

索引就是一种数据结构，类似于书的目录。
意味着以后在查数据应该先找目录再找数据，而不是用翻页的方式查询数据

索引在MySQL中也叫做"键"，是存储引擎用于快速找到记录的一种数据结构
主键 primary key
除了可以加快查询之外还有其他的功能
唯一键 unique
除了可以加快查询之外还有其他的功能
索引键 index key
除了可以加快查询之外没有其他的功能
外键 foreign key
跟索引半毛钱关系都没有也不存在提升查询速度一说

索引的影响
* 在表中有大量数据的前提下,创建索引速度会很慢
* 在索引创建完毕后,对表的查询性能会大幅度提升,但是写的性能会降低

'''
有一张表按照name字段查询数据 速度很慢 如何解决?
将name字段制作索引
小结论:可以简单的认为按照什么字段查询慢就把改字段制作成索引

是不是一遇到查询比较慢，就把对应的字段做成索引?
不对!!!应该改善sql语句
'''

聚集索引(primary key)
叶子结点放的一条条完整的记录
辅助索引(unique,index)
叶子结点存放的是辅助索引字段对应的那条记录的主键的值

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

posted @ 2021-09-12 18:45 aa_wang 阅读(36) 评论(0) 收藏举报

刷新页面返回顶部

aa_wang