欢迎来到飞鸟慕鱼博客,开始您的技术之旅!
当前位置: 首页知识笔记正文

mysql数据库sid,mysql数据库的完整步骤

终极管理员 知识笔记 36阅读

文章目录 MySQL数据库一、Python操作MySQLPython中的语法 二、SQL注入的问题三、视图什么是视图为什么要用视图如何使用视图 四、触发器触发器概念触发器作用触发器种类触发器语法结构触发器注意事项案例 五、事务什么是事务事务的产生事务的四大特性具体使用 六、存储过程七、函数八、流程控制九、索引

MySQL数据库 一、Python操作MySQL

我们想要在Python中操作MySQL数据库就需要用到一个第三方模块pymysql
安装模块两种方式

在cmd中输入pip install pymysql直接在pycharm中File | Settings | Project: Pycharm_Project_Test | Python Interpreter安装pymysql模块即可 Python中的语法
import pymysql  # 导入模块建立数据库连接conn  pymysql.connect(user  root,  # 数据库用户名password  123,  # 数据库密码(没有可以不填)host  127.0.0.1, # 数据库地址port  3306,  # 端口号database  day,  # 数据库的库名charset  utf8, # 字符编码 autocommit  True  # 自动二次提交(这个后面说可写可不写建议都写上))获取游标等待输入相当于在cmd中光标处于等待输入(用于操作数据库)cursor  conn.cursor()  # 括号内不填东西默认以元组的方式显示接收的内容# cursor  conn.cursor(cursorpymysql.cursors.DictCursor)  # 改变显示方式为列表内一个个字典写入SQL语句sql  select * from zero开始执行SQL语句这里是执行获取的是条数rows  cursor.execute(sql)也可以直接在这个执行SQL语句这里写入SQL语句直接把写入、执行合并为一步rows  cursor.execute(select * form zero)如果在建立数据库连接的时候没有autocommitTrue那么在这里就需要二次提交# conn.commit()获取命令的执行结果res  cursor.fetchall()  # 获取结果中的所有数据res  cursor.fetchone()  # 获取结果中的一条数据res  cursor.fetchmany() 获取结果中指定的几条数据(括号内写入的数字就是获取的条数当超出结果的条数也不会报错但是也只会显示应有的数据)print(res)cursor类似于光标 第一次获取完再次获取的消息就会丢失了基于当前位置往后移动关闭游标cursor.close()关闭连接conn.close()Pycharm里面py格式的写入的SQL语句会飘黄不用在意
二、SQL注入的问题

我们可以先写使用SQL编写的用户登录

import pymysql# 连接mysql库conn  pymysql.connect(userroot,host127.0.0.1,port3306,databaseday,charsetutf8)# 获取游标cursor  conn.cursor(cursor  pymysql.cursors.DictCursor)inp_user  input(username>>>:).strip()inp_pwd  input(password>>>:).strip()# 写SQL语句sql  select * from userinfo where username%s and password%s % (inp_user, inp_pwd)print(sql)# 需要执行二次提交conn.commit()# 执行sql语句rows  cursor.execute(sql)# 判断是否数据库中存在if rows: # 存在则走这里    print(login successful)else:  # 不存在走这里    print(username or password error)# 关闭游标cursor.close()# 关闭连接conn.close()


这样确实是登录匹配成功了但是当前这种SQL语句写法会有一个重大问题
如下图

所以SQL注入问题本质上就是利用SQL语法上的一些特殊符号的组合产生 了特殊的含义从而跳脱了正常的业务逻辑

措施就是针对用户输入的数据不能输入特殊符号什么的这也就是为什么那么多网站、软件账号都不允许输入特殊符号的原因所以我们可以让用户输入的数据不要自己处理交给专门的方法自动过滤

三、视图 什么是视图

视图就是通过执行SQL语句查询结果得到一张虚拟表然后保存下来后面可以基于该表做其他操作

为什么要用视图

如果要频繁使用一张虚拟表可以不用重复查询

如何使用视图
语法结构create view 视图名 as(注意as是不能省略也不是重命名的意思) SQL语句ex:create view teacher2course as select * from teacher inner join course on teacher.tidcourse.teacher_id;视图原理在硬盘中视图是有表结构文件没有表数据文件(不能做增删改)视图通常是用于查询select * from teacher2course;创建好了之后 验证它的存在navicat验证 cmd终端验证最后文件验证 得出下面的结论 视图只有表结构数据还是来源于之前的表删除视图delete from teacher2course where id1;


思考开发过程中会不会去使用视图

不会视图是mysql的功能如果你的项目里面大量的使用到了视图那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改意味着你需要先在mysql这边将视图先修改一下然后再去应用程序中修改对应的sql语句这就涉及到跨部门沟通的问题所以通常不会使用视图而是通过重新修改sql语句来扩展功能

四、触发器 触发器概念

触发器类似于我们python中魔法方法到达什么条件自动触发
在满足对某张表数据的增、删、改的情况下自动触发的功能称之为触发器

触发器作用

专门针对表数据的操作定制个性化配套功能

触发器种类 表数据新增之前、新增之后表数据修改之前、修改之后表数据删除之前、删除之后 触发器语法结构
语法结构create trigger 触发器的名字 before/after insert/update/delete on 表名 for each rowbeginsql语句end触发器的名字一般情况下建议采用下列样式进行命名tri_after_insert_t1tri_before_update_t2tri_before_delete_t3

针对插入前后

插入前create trigger tri_after_insert_t1 after insert on 表名 for each rowbeginsql代码end插入后create trigger tri_before_insert_t1 before insert on 表名 for each rowbeginsql代码end

针对删除前后

删除前create trigger tri_after_delete_t1 after delete on 表名 for each rowbeginsql代码end删除后create trigger tri_before_delete_t1 before delete on 表名 for each rowbeginsql代码end

针对修改前后

修改前create trigger tri_after_update_t1 after update on 表名 for each rowbeginsql代码end修改后create trigger tri_before_update_t1 before update on 表名 for each rowbeginsql代码end
触发器注意事项

触发器内部的SQL语句需要用到分号但是分号又是SQL语句默认的结束符号
所以为了避免产生冲突完成完整的写出触发器代码我们需要临时修改SQL语句中默认的结束符
修改默认符号我们需要用到 delimiter $$ 需要修改的符号(注意cmd里面的临时修改 配置文件是永久修改)
注意修改完结束后记得在改回SQL语句的默认的结束符号 delimiter ;不然后面结束符就得用$$了

案例
模拟一个cmd 如果执行失败则写入errlog里面1.CREATE TABLE cmd(id INT PRIMARY KEY auto_increment,USER CHAR(32),prive CHAR(10),cmd CHAR(64),  # cmd命令sub_time datetime,  # 提交时间success enum(yes,no)  # no代表执行失败 );2.CREATE TABLE errlog(id INT PRIMARY KEY auto_increment,err_cmd CHAR(64),# 报错的cmd命令err_time datetime# 报错时间);3.delimiter $$  # 将mysql默认的结束符由;转换成$$4.create trigger tri_after_insert_cmd after insert on cmd for each rowbeginif NEW.success  no then  # 新记录都会被MySQL封装成NEW对象insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);end if;end $$5.delimiter ; # 结束之后记得再改回来不然后面结束符都是$$了往表cmd中插入记录触发触发器根据IF的条件决定是否插入错误日志6.INSERT INTO cmd(USER,priv,cmd,sub_time,success)VALUES(egon,0755,ls -l /etc,NOW(),yes),    (egon,0755,cat /etc/passwd,NOW(),no),    (egon,0755,useradd xxx,NOW(),no),    (egon,0755,ps aux,NOW(),yes);7.select * from errlog; # 查询errlog表记录8.drop trigger tri_after_insert_cmd; # 删除触发器
五、事务 什么是事务

事务是用户定义的一系列数据库操作这些操作可以视为一个完成的逻辑处理工作单元
要么全部执行要么全部不执行是不可分割的工作单元

通俗的说

开启一个事务可以包含一些SQL语句这些SQL语句要么同时成功要么一个都别想成功称之为事务的原子性

事务的产生
数据库中的数据是共享资源因此数据库系统通常要支持多个用户的或不同应用程序的访问并且各个访问进程都是独立执行的这样就有可能出现并发存取数据的现象为了避免数据库的不一致性这种处理机制称之为并发控制其中事务就是为了保证数据的一致性而产生的一种概念和手段(事务不是唯一手段)
事务的四大特性

为了保证数据库的正确性与一致性实物具有四个特征这四个特征通常称为ACID特性。

原子性(Atomicity)事务的原子性保证事务中包含的一组更新操作是原子的不可分割的不可分割是事务最小的工作单位所包含的操作被视为一个整体执行过程中遵循要么都不执行要不全部执行不存在一半执行一般不执行的情况 一致性(Consistency)事务的一致性要求事务必须满足数据库的完整性约束且事务执行完毕后会将数据库一个一致性的状态变为另一个一致性的状态 事务的一致性与原子性是密不可分的 隔离性(Isolation)事务的隔离性要求事务之间是彼此独立的 隔离的 及一个事务的执行不可以被其他事务干扰 持久性(Durability)事物的持续性也称持久性 是指一个事务一旦提交 它对数据库的改变将是永久性的因为数据刷进了物理磁盘了 其他操作将不会对它产生任何影响 具体使用
 1.创建表及录入数据     create table user(      id int primary key auto_increment,      name char(32),      balance int     );       insert into user(name,balance)values     (jason,1000),     (kevin,1000),     (tank,1000);2.事务操作开启一个事务的操作start transaction;编写SQL语句(同属于一个事务)update user set balance900 where namejason;  # 买家支付100元update user set balance1010 where namekevin; # 中介拿走10元update user set balance1090 where nametank;  # 卖家拿到90元事务回滚(返回执行事务操作之前的数据库状态)rollback;  # 执行完回滚,撤回到上一个状态(上一步)开启事务之后只要没有执行commit操作数据其实都没有真正刷新到硬盘事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)commit;  # 开启事务检测操作是否完整不完整主动回滚到上一个状态如果完整就应该执行commit操作开启事务检测操作是否完整不完整主动回滚到上一个状态如果完整就应该执行commit操作
六、存储过程

存储过程包含了一些可执行的SQL语句存储过程存放于MySQL中通过调用它的名字可以执行其内部的一对SQL类似于Python中的自定义函数

类似于Python中的自定义函数语法结构delimiter $$ # 涉及到begin语法需要修改临时结束符create procedure 名字(参数,参数)beginsql语句;end 临时结束符delimiter ;# 用完再修改回去# 相当于调用函数call 名字()
类似于有参函数:delimiter $$create procedure p1(    in m int,  # in表示这个参数必须只能是传入不能被返回出去    in n int,      out res int)  # out表示这个参数可以被返回出去还有一个inout表示即可以传入也可以被返回出去begin    select name from teacher where tid > m and tid < n;# name 表示用户设置的数字    set res0;  # 用来标志存储过程是否执行end $$delimiter ;# 用完修改成默认结束符号# 针对res需要先提前定义set res10;  定义# 相当于python中设置一个变量名值为10select res;  查看call p1(1,5,res)  调用select res  查看查看存储过程具体信息show create procedure 名称;查看所有存储过程show procedure status 名称;删除存储过程drop procedure 名称;
七、函数

注意与存储过程的区别MySQL内置的函数只能在SQL语句中使用

ps:可以通过help 函数名    查看帮助信息!# 1.移除指定字符Trim、LTrim、RTrim # 2.大小写转换Lower、Upper # 3.获取左右起始指定个数字符Left、Right # 4.返回读音相似值(对英文效果)Soundexeg:客户表中有一个顾客登记的用户名为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-012.where Year(sub_time)2016 AND Month(sub_time)07;# 更多日期处理相关函数     adddate    增加一个日期     addtime    增加一个时间    datediff    计算两个日期差值  ...
八、流程控制
if条件语句delimiter //# 设置结束符号CREATE PROCEDURE proc_if ()# 创建一个存储过程BEGIN    declare i int default 0;# 声明变量    if i  1 THEN# 如果i 等于1 那么 THEN(那么)        SELECT 1;# 选择1    ELSEIF i  2 THEN# elif i 等于2 那么 THEN(那么)        SELECT 2;# 选择2    ELSE        SELECT 7;#  否则 选择7    END IF;# 结束if判断END //# 结束语句delimiter ;# 修改回结束符号while循环delimiter //CREATE PROCEDURE proc_while ()# 设置循环BEGIN    DECLARE num INT ;# 声明变量    SET num  0 ;# 设置变量    WHILE num < 10 DO# 当num小于10 做        SELECT            num ;        SET num  num  1 ;# num  1    END WHILE ;# 结束循环END //delimiter ;
九、索引
# 索引就是一种数据结构类似于书的目录。意味着以后再查数据应该先找目录再找数据而不是用翻页的方式查询数据  索引在MySQL中也叫做“键”是存储引擎用于快速找到记录的一种数据结构  primary key     主键  unique    key   唯一键  index     key   索引键上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一unique key:唯一)而index key没有任何约束功能只会帮你加速查询# ps:foreign key不是用来加速查询用的,不在我们研究范围之内 # 索引的基本用法id    name    pwd        post_comment  addr  age     基于id查找数据很快 但是基于addr查找数据就很慢       解决的措施可以是给addr添加索引索引虽然好用 但是不能无限制的创建!!!**索引的影响:**    * 在表中有大量数据的前提下创建索引速度会很慢    * 在索引创建完毕后对表的查询性能会大幅度提升但是写的性能会降低 索引的底层数据结构是b树    b树 红黑树 二叉树 b*树 b树      上述结构都是为了更好的基于树查找到相应的数据 只有叶子结点存放真实数据根和树枝节点存的仅仅是虚拟数据查询次数由树的层级决定层级越低次数越少一个磁盘块儿的大小是一定的那也就意味着能存的数据量是一定的。如何保证树的层级最低呢一个磁盘块儿存放占用空间比较小的数据项思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段 聚集索引(primary key)辅助索引(unique key,index key)    查询数据的时候不可能都是用id作为筛选条件也可能会用namepassword等字段信息那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引这些索引就叫辅助索引叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})数据查找 如果一开始使用的是辅助索引 那么还需要使用聚焦索引才可以获取到真实数据覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据    select name from user where namejason;非覆盖索引:虽然查询的时候命中了索引字段name但是要查的是age字段所以还需要利用主键才去查找    select age from user where namejason;

标签:
声明:无特别说明,转载请标明本文来源!