数据库同步工具
sqlserver,Mysql数据同步软件

day49数据库之六

购买事宜请联系QQ:1793040

昨天添加:pymysql模块添加,删除,更改和检查数据操作

### pymysql补充

“`python
#1.对于添加,删除和修改,pymysql需要第二次确认才能实际操作数据

导入pymysql

conn = pymysql.connect(
主机=” 127.0.0.1″,
端口= 3306,
用户=” root”,
passwd =” 123456″,
db =” day48″,
charset =” utf8″,
autocommit = True

cursor = conn.cursor(pymysql.cursors.DictCursor)

#增加
sql =”插入用户(名称,密码)值(%s,%s)”
#行= cursor.execute(sql,(” jackson”,123 ))#传递单个数据
rows = cursor.executemany(sql,[(” xxx”,123),(” ooo”,123),(” yyy”,123)])#传递多个数据
打印(行)
#conn.commit()#确认
#修改
#sql =”更新用户集名称=” jasonNB”其中id = 1″
#行= cursor.execute(sql)
#打印(行)
#conn.commit()#确认
#删除
行= cursor.execute(sql)
打印(行)
conn.commit()#确认
#检查
#sql =”从用户中选择* ”
#cursor.execute(sql)
#print(cursor.fetchall())

“””
另外,删除和修改检查
数据的删除,修改和添加修改的操作设计
方法一需要两次确认才能生效conn.commit ();方法二可以在连接数据库时,将自动提交添加为True,即autocommit = True
“””

#您还可以一次插入N条数据
rows = cursor.executemany(sql,[(” xxx”,123),(” ooo”,123)])
“ `

#今天的内容摘要

今天的内容基本上是为了理解知识,作为开发人员,您基本上不会在工作中使用它

*查看(了解)
*触发器(了解)
*事务(需要掌握)
*存储过程(了解)
*内置函数(了解)
*过程控制(理解)
*索引理论(理解以后再扩展)

#今天的内容很详细

###查看(了解)

*什么是视图

“`python
“””
视图是通过查询获取虚拟表,然后保存它,下次可以直接使用它
实际上,该视图也是表
“””
“`

*为什么要使用视图
“`python
“””
如果要频繁操作虚拟表(由拼写表组成),可以使视图遵循Direct操作
“””
“`

*如何操作
`python
#固定语法
创建视图表名称作为虚拟表的查询sql语句

#具体操作(在此处缝制教师和课程表以进行视图)
创建视图教师2课程,方法为
从*教师.tid = course.teacher_id上的教师INNER JOIN课程中选择*。
;
创建成功后,使用显示表;您可以查看新添加的表格

*注意

“`python
“””
1在硬盘上创建视图将仅具有表结构而不包含表数据(该数据仍来自上一个表)
2视图通常仅用于查询其中的数据,不要继续对其进行修改,可能会影响实际表
“””
`

*视图是否经常使用?
“””
不高
创建大量视图时,将导致表维护不善
“””
#总结
视图理解您基本上不能使用它! ! !

###触发(理解)

在添加,删除和修改表数据的情况下,自动触发功能
使用触发器可以帮助我们实现监视,记录,自动处理和其他功能…
触发您可以在六种情况下自动触发添加,添加,删除,删除,更改,更改,更改后更改

####触发器的基本语法结构
在插入/更新/删除表名之前/之后创建触发器的触发器名称
每行
开始
sql语句
结束

#触发器的名称的具体用法我们通常需要查看名称和理解
#对于增加(表的名称就是查看名称和理解,例如,以下是在添加t1表之前触发
>在插入t1之前为每个行创建触发器tri_before_insert_t1
每行
开始
sql语句
结束

#下面的句子是针对表t1增加之后的触发器。
在t1上插入后,为每一行创建触发器tri_after_insert_t1
开始
sql语句;
结束;

“””对于删除和修改,书写格式相同””””

ps:仅针对当前窗口修改MySQL的默认语句结束字符。
定界符$$将默认结束符号从;更改为$$
如果要改回它,请使用定界符;
如果要使用触发器,则必须使用此定界符,因为必须在sql语句和结尾之后添加;因此必须使用此函数。 dot \\ \\ \\ mid \\ \\ \\ \\ \\ \\ \\ \\ 00 \\ 00 \\ 00 00 00 dot 00 00 00 00 00 00 mid mid dot;;; \\ \\ \\ \\ \\ mid ; \\ middot; \ mi ddot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; ; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; ; \\ u00middot; \\ u00middot; \\ u00middot; \ mi ddot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot; \\ u00middot;
预先准备好的案例表:
首先创建一个新的库day49:创建数据库day49;
然后使用day49;
在此新创建的库下创建表
#Case
CREATE TABLE cmd(
id INT PRIMARY KEY auto_increment,
USER CHAR(32),
priv CHAR(10),
cmd CHAR(64),
sub_time datetime,#提交时间
成功枚举(” yes”,” no”)#0表示执行失败
);

创建表errlog(
id INT主键auto_increment,
err_cmd CHAR(64),
err_time datetime
);
“””
要求:如果cmd表中的记录成功字段为no,则触发执行触发器以将数据插入errlog表。
NEW引用数据对象
“””
分隔符$$
在cmd上插入后,为每一行创建触发器tri_after_insert_cmd
如果NEW.success =” no”,则开始

插入errlog(err_cmd,err_time)值(NEW.cmd,NEW.sub_time);
如果结束;
end $$

#将数据插入到cmd表中
INSERT INTO cmd(
USER,
priv,
cmd,
sub_time,
成功


(” jason”,” 0755″,” ls-l/etc”,NOW(),”是”),
(” jason”,” 0755″, ” cat/etc/passwd”,NOW(),” no”),
(” jason”,” 0755″,” useradd xxx”,NOW(),” no”),
(” jason”,” 0755″,” ps aux”,NOW(),”是”);

然后,查询cmd中将有数据。目前,我们没有将数据插入errlog表中,但是查询发现errlog中已经有数据。这是因为触发器起作用。

#删除触发器
删除触发器tri_after_insert_cmd;

###交易

*什么是交易

“””
启动事务可以包含多个sql语句。这些sql语句在同一时间成功完成
或不希望成功地将其称为事务的原子性
“””
例如:还款示例
egon用我的银行卡转账我的支付宝1000
1将egon银行卡帐户的数据减少1000元
2添加杰森支付宝帐户中的数据1000元
如果在转移过程中互联网断开连接,则Egon减少了1000元,但杰森没有钱。
当您处理多个数据时,某些操作可能不会出现成功情况
此时将使用事务,并且对多个数据进行操作的操作将被放入一个事务中
“`

*事务的四个特征:
“””
ACID
A:原子性
事务是不可分割的单元,事务中包含许多操作
同时成功或失败
C:一致性
事务必须将数据库从一种一致状态更改为另一种一致状态
一致性和原子性密切相关
I:隔离
事务的执行不能受到其他事务的干扰
(也就是说,事务中使用的操作和数据与并发的其他事务是隔离的,并发执行的事务也不会相互干扰)
/> D:持久性
也称为”永久性”
事务成功提交并执行后,则应修改数据库中的数据。永久性
其他操作或失败不应有任何影响对它的影响
“””

*如何使用交易
#与交易相关的关键字
#1开始交易
开始交易;
#2回滚(返回到执行事务之前的状态)。
#3确认(确认后无法回滚)。

“””模拟传递函数”””
创建表用户(名称char(16),
balance int
);
插入用户(名称,余额)值
(” jason”,1000),
(” egon”,1000),
(” tank”,1000) ;

#1首先开始交易
开始交易;
#2多个sql语句
更新用户集余额= 900,其中名称=” jason”;
更新用户设置余额= 1010,其中名称=” egon”;
更新用户设置余额= 1090,其中名称=”储罐”;
3.回滚(返回到执行事务之前的状态,在取消事务时使用)。
4.确认(确认后无法回滚)。

“””
摘要
如果要使多个SQL语句保持一致,则同时成功或失败
您应考虑使用事务
“””

###存储过程(理解)

存储过程类似于python中的自定义函数

它包含一系列可执行的SQL语句。存储过程存储在MySQL服务器中。您可以通过调用存储过程来直接触发内部SQL语句的执行

**基本用法**
创建过程存储过程的名称(形式参数1,形式参数2,…)
开始
sql代码
结束
#调用
调用存储过程的名称();

####三种开发模型
第一个开发模型
应用:程序员编写代码进行开发
MySQL:预先编写存储过程,提供按程序调用

优点:提高了开发效率。执行效率也得到提高。
缺点:考虑到人为因素和跨部门通信问题,后续存储过程的可伸缩性很差
“””

第二种类型
“””
应用程序:程序员在数据库开发之外编写代码,也可以自己动手编写
优点:高可伸缩性
开发效率降低
编写SQL语句太麻烦了,后续工作需要考虑SQL优化问题
“””

第三种
“””
应用程序:仅编写程序代码,不编写sql语句,基于其他人编写的操作,MySQL python框架可以直接调用操作ORM框架“””

基本上不使用第一个。通常是第三种,如果存在效率问题,请再次编写sql

*演示存储过程
定界符$$
创建过程p1(
in int,#只能进出m不能返回n在n中int,
out res int#此参数可以返回

开始
从老师那里选择tname,其中tid \ gt; m和tid \ lt; n;
设置res = 666; #将Res变量修改用于标识确实已执行了当前存储过程的代码
end $$
定界符;

#对于形式参数res不能直接传输的数据,应传递一个变量名
#定义变量
set @ ret = 10;
#查看与变量对应的值
select @ ret;
#调用和传递参数
调用p1(1,5,@ ret);
#再次检查
选择@ret;

如何在pymysql模块中调用存储过程?

导入pymysql

conn = pymysql.connect(
主机=” 127.0.0.1″,
端口= 3306,
用户=” root”,
passwd =” 123456″,
db =” day48″,
charset =” utf8″,
autocommit = True

cursor = conn.cursor(pymysql.cursors.DictCursor)
#调用存储过程
cursor.callproc(” p1″,(1,5,10))
“””
@ _p1_0 = 1
@ _p1_1 = 5
@_ p1_2 = 10
“””
#打印(cursor.fetchall())
cursor.execute(” select @ _p1_2;”)
打印(光标fetchall())

###函数

与存储过程有所不同。存储过程是自定义函数。功能类似于内置功能。
(”杰森”,” 0755″,” ls-l/等”,现在(),”是”)

创建表博客(
id INT PRIMARY KEY auto_increment,
NAME CHAR(32),
sub_time datetime
);

INSERT INTO博客(名称,子时间)

(”第1部分”,” 2015-03-01 11:31:21″),
(”第2部分”, ” 2015-03-11 16:31:21″),
(” Part 3″,” 2016-07-01 10:21:31″),
(” Part 4″,” 2016-07-22 09:23:21″),
(” Part 5″,” 2016-07-23 10:11:11″),
(” Part 6″,” 2016-07-25 11:21:31″),
(” Part 7″,” 2017-03-01 15:33:21″),
(” Part 8″,” 2017-03-01 17:32:21″),
(”第9章”,” 2017-03-01 18:31:21″);

选择date_format(sub_time,”%Y-%m”),按date_format(sub_time,”%Y-%m”)从博客组中计数(id);

###流程控制(理解)
#如果判断
定界符//
CREATE PROCEDURE proc_if()
开始
声明i int默认值0;
如果i = 1,则
选择1;
ELSEIF i = 2然后
SELECT 2;
ELSE
SELECT 7;
END IF;
END//
分隔符;
#while循环
分隔符//
CREATE PROCEDURE proc_while()
开始
DECLARE num INT;
SET num = 0;
num \ lt; 10 DO
SELECT
num;
SET num = num + 1;
结束时;

###索引

ps:数据存储在硬盘上,查询数据不可避免地需要IO操作

索引:是一种数据结构,类似于本书的目录。这意味着将来,如果要查询数据,则应先查找目录,然后再查找数据,而不要翻页。一,以提高查询速度并减少IO操作

索引也称为”索引”。密钥在MySQL中,它是存储引擎用来快速查找记录的数据结构

*主键
*唯一键
*索引键

请注意,外键不用于加快查询速度,这不在我们的研究范围之内

以上三个键,前两个键除了提高查询速度外还具有约束条件,最后一个索引键没有任何约束,仅用于帮助您快速查询数据

**必不可少**

通过不断缩小所需的数据范围,同时选择随机事件(逐页)来选择最终结果

成为顺序事件(首先查找目录,找到数据)

也就是说,借助索引机制,我们始终可以以固定的方式查找数据

一个表中可以有多个索引(多个目录)

尽管索引可以帮助您加快查询速度,但它也有缺点

“`python
“””
1如果表中有大量数据,则索引创建速度将非常慢
2之后的表查询性能
“””
不要随意创建索引!索引创建完成了,它会大大改善,但编写性能也会大大降低。 ! !
“`

### b +树

“`python
“””
仅叶子节点存储实际数据,其他节点仅存储虚拟数据用于指示。
树级别越高,您需要的步骤越多要查询数据(树中要查询数据的步骤很多)

一个磁盘块的存储空间有限
为什么建议您使用id字段作为索引
占用的空间更少。一个磁盘块可以存储更多数据。
这么长的时间降低了树的高度,减少了查询的数量。
“””
“`

###聚集索引(主键)

“`python
“””
聚集索引引用主键。
Innodb只有两个文件,并将主键直接存储在idb表中。
MyIsam三个文件分别将索引存储在文件中
“””
“`

###辅助索引(唯一索引)

查询数据时,不可能一直使用主键,还可以使用其他字段(例如名称,密码等)

然后,您目前无法使用聚簇索引。此时,您可以根据情况(也可以是b +树)为其他字段设置辅助索引

“`python
“””
叶子节点存储与数据相对应的主键值。
首先获取根据辅助索引的数据主键值。
需要转到主键的聚簇索引来查询数据
“””
“`

###封面索引

辅助索引中的叶节点已经获得了所需的数据

“`python
#设置名称的辅助索引
从用户那里选择名称name =” jason”;
#非覆盖索引
从用户那里选择年龄,其中name =” jason”;
“`

###用于测试索引是否有效的代码

如果您有兴趣,请尝试一下,如果您不感兴趣,请忽略它

“`python
**准备**

?“`mysql
#1.准备表
创建表s1(
id int,
名称varchar(20),
性别字符(6),
电子邮件varchar(50)
);

#2.创建存储过程以实现记录的批量插入
定界符$$#声明存储过程的结束符号为$$
创建过程auto_insert1()
BEGIN
声明i int默认值1;
while(i \\ 3000000)做
插入s1值(i,” jason”,” male”,concat(” jason”,i,” @oldboy”));
设置i = i + 1;
结束片刻;
END $$#$$结束
分隔符; #重新将分号声明为结束符号

#3.查看存储过程
show create procedure auto_insert1 \\\\ G

#4.调用存储过程
调用auto_insert1();
? “`

? “`mysql
#当表中没有索引时
选择* from s1其中id = 30000;
#避免由于打印导致的时间浪费
从s 1中选择count(id),其中id = 30000;
从s1中选择计数(id),其中id = 1;

#为id创建主键
alter table s1添加主键(id); #速度很慢

从id = 1的s1中选择count(id); #与未建立索引之前的速度相比,两者之间的差异是数量级
从s1那里选择count(id),其中name =” jason”#速度仍然很慢

“””
范围问题
“””
#没有索引,稍后根据此字段,查询
从s1中选择计数(id),其中id> 1; #速度远慢于id = 1
从s1中选择count(id),其中id \ gt; 1和id \ lt; 3;
从s1中选择count(id),其中id \\ u> 1和id \ lt; 10000;
从s1的id中选择count(id)! = 3;

alter table s1删除主键; #删除主键,并分别研究名称字段
从s1中选择count(id),其中name =” jason”; #再次变慢

在s1(名称)上创建索引idx_name; #为s1表的名称字段创建索引
从s1中选择count(id),其中name =” jason”#仍然很慢! ! !
“””
让我们看一下b +树的原理,数据需要区分得比较高,我们的表都是杰森,没有办法区分它
那棵树实际上是建在树上的。
“””
从s1中选择count(id),其中name =” xxx”;
#这会很快,我是棍子,第一个不是比赛直接不需要下来。
从名称为” xxx”的s1中选择count(id);
从s1中选择count(id),名称如” xxx%”;
从名称为”%xxx”的s1中选择count(id); #最慢的最左匹配功能

#辨别力低的字段不能被索引
drop index idx_name on s1;

#为id字段创建公共索引
在s1(id)上创建索引idx_id;
从s1中选择计数(id),其中id = 3; #快了
从s1中选择count(id),其中id * 12 = 3; #索引慢的字段不能参与计算s1上的丢弃索引idx_id;
从s1中选择count(id),其中name =” jason”,gender =” male”,id = 3,email =” xxx”;
#对于以上多次连续的操作,mysql将首先从左到右查找具有高度区分度的索引字段,首先降低总体范围,然后比较其他条件
在以下位置创建索引idx_name s1(名称);
从s1中选择count(id),其中名称=” jason”,性别=” male”,id = 3,电子邮件=” xxx”; #s1上没有加速度

下降索引idx_name;
#在名称和性别字段中添加不高度区分的索引以加快查询速度并不难。

在s1(id)上创建索引idx_id;
从s1中选择count(id),其中名称=” jason”,性别=” male”,id = 3,电子邮件=” xxx”; #赶快使用id讨论数据并将其快速锁定为s1中有一行
select count(id),其中name =” jason”和gender =” male”和id \\ ugt26; 3,电子邮件=” xxx”; #慢慢地,基于id检测的数据仍然很多,然后我必须比较其他字段

在s1上删除索引idx_id

在s1上创建索引idx_email(电子邮件) ;
从s1中选择count(id),其中name =” jason”,gender =” male”,id \\ u> 3,电子邮件=” xxx”; #快速在电子邮件字段中传递剑来密封喉咙
? “`

####联合索引

? “`mysql
从s1中选择count(id),其中name =” jason” and sex =” male” and id \ gt; 3并发送电子邮件=” xxx”;
#如果上述四个字段具有高度差异性,那么构建的任何人都可以加快查询速度。
#添加电子邮件,但不使用电子邮件字段。
从s1中选择count(id),其中名称=” jason”,性别=” male”,ID \\ u> 3;
#添加名称,但不使用名称字段。
从s1中选择count(id),其中性别=” male”和ID \\ u> 3;
#添加性别,但不使用性别字段
从s1中选择count(id),其中id \\ u> 3;

#问题是所有字段都已建立索引,但没有使用过,它还需要四次构建才能在s1上创建索引idx_all(电子邮件,姓名,性别,id); #最左边的匹配原则,将高度歧视的左边放在s1中,其中name =” jason”和gender =” male”和id \; 3,电子邮件=” xxx”; #速度变得更快
? “`

摘要:以上操作,如果您有兴趣,可以敲门,如果您不感兴趣,则不需要敲门,正确的观看快乐时。该理论已被掌握。

查询日志缓慢

设置时间来检测所有超过此时间的sql语句,然后有针对性地对其进行优化!

“`

未经允许不得转载:数据库同步软件|Mysql数据同步软件|sqlserver数据库同步工具|异构同步 » day49数据库之六

分享到:更多 ()

syncnavigator 8.6.2 企业版

联系我们联系我们