黄山网站建设费用,企业网络管理软件,企业网站建设网站模板,wordpress uazoh7一. 视图 视图是一个虚拟表#xff08;非真实存在#xff09;#xff0c;是跑到内存中的表#xff0c;真实表是硬盘上的表#xff0c;怎么就得到了虚拟表#xff0c;就是你查询的结果#xff0c;只不过之前我们查询出来的虚拟表#xff0c;从内存中取出来显示在屏幕上…一. 视图 视图是一个虚拟表非真实存在是跑到内存中的表真实表是硬盘上的表怎么就得到了虚拟表就是你查询的结果只不过之前我们查询出来的虚拟表从内存中取出来显示在屏幕上内存中就没有了这些表的数据但是下次我要是想用这个虚拟表呢没办法只能重新查一次每次都要重新查。其本质是【根据SQL语句获取动态的数据集并为其命名】用户使用时只需使用【名称】即可获取结果集可以将该结果集当做表来使用。如果我们想查询一些有关联的表比如我们前面的老师学生班级什么的表我可能需要几个表联合查询的结果但是这几张表在硬盘上是单独存的所以我们需要通过查询的手段将这些表在内存中拼成一个虚拟表然后是不是我们再基于虚拟表在进行进一步的查询然后我们如果以后想重新再查一下这些关系数据还需要对硬盘上这些表进行再次的重新加载到内容联合成虚拟表然后再筛选等等的操作意味着咱们每次都在写重复的sql语句那有没有好的方法啊其实很简单我们把重复用的这些sql逻辑封装起来然后下次使用的时候直接调用这个封装好的操作就可以了这个封装起来的操作就类似我们下面要说的视图 为什么要用视图使用视图我们可以把查询过程中的临时表摘出来保存下来用视图去实现这样以后再想操作该临时表的数据时就无需重写复杂的sql了直接去视图中查找即可但视图有明显地效率问题并且视图是存放在数据库中的如果我们程序中使用的sql过分依赖数据库中的视图即强耦合那就意味着扩展sql极为不便因此并不推荐使用 临时表应用举例 #两张有关系的表
mysql select * from course;
-------------------------
| cid | cname | teacher_id |
-------------------------
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
-------------------------
rows in set (0.00 sec)mysql select * from teacher;
----------------------
| tid | tname |
----------------------
| 1 | 张磊老师 |
| 2 | 李平老师 |
| 3 | 刘海燕老师 |
| 4 | 朱云海老师 |
| 5 | 李杰老师 |
----------------------
rows in set (0.00 sec)#查询李平老师教授的课程名
mysql select cname from course where teacher_id (select tid from teacher where tname李平老师); #子查询的方式--------
| cname |
--------
| 物理 |
| 美术 |
--------
rows in set (0.00 sec)#子查询出临时表作为teacher_id等判断依据
select tid from teacher where tname李平老师 一. 创建视图 #语法CREATE VIEW 视图名称 AS SQL语句
create view teacher_view as select tid from teacher where tname李平老师;mysql show tables;---------------| Tables_in_crm |---------------| class || course || score || student || teacher || teacher_view | #看这里---------------mysql desc teacher_view; #有表结构-------------------------------------------| Field | Type | Null | Key | Default | Extra |-------------------------------------------| tid | int(11) | NO | | 0 | |-------------------------------------------
row in set (0.04 sec)mysql select * from teacher_view; #有表数据-----| tid |-----| 2 |-----
row in set (0.00 sec)
注意但是在硬盘上你找到自己的mysql安装目录里面的data文件夹里面的对应的那个库的文件夹这个文件夹里面存着咱们的表信息打开之后你会发现这个视图表只有表结构的teacher_view.frm文件没有那个.idb存放数据的文件
其实他并没有真实的数据也没有必要再存一份数据因为它的数据来源于其他两个表所以他本质在后台对应的就是一个sql语句而已所以记住了视图只有表结构没有表数据
视图的好处是以后我们如果再需要查询或者使用上面的虚拟表就可以直接使用这个视图了sql的代码量也会省很多。但是弊端也很致命看下面注意的内容。#于是查询李平老师教授的课程名的sql可以改写为
mysql select cname from course where teacher_id (select tid from teacher_view);
--------
| cname |
--------
| 物理 |
| 美术 |
--------
rows in set (0.00 sec)#注意注意注意
#1. 使用视图以后就无需每次都重写子查询的sql开发的时候是方便了很多但是这么效率并不高还不如我们写子查询的效率高#2. 而且有一个致命的问题视图是存放到数据库里的如果我们程序中的sql过分依赖于数据库中存放的视图那么意味着一旦sql需要修改且涉及到视图的部分则必须去数据库中进行修改然后再到自己的应用程序里面将那个sql语句改一改需要很多的修改工作并而对视图的更改通常在一般中型及以上公司中数据库有专门的DBA负责你要想完成修改必须付出大量的沟通成本DBA可能才会帮你完成修改极其地不方便
这么多的弊端为什么mysql还要提供这个东西呢有一点是因为mysql想把所有数据处理的工作全部接手过来但其实还有其他的原因等我们讲完存储过程在和大家说吧。
#3 并且注意视图一般都是用于查询尽量不要修改(插入、删除等)视图中的数据虽然有时候可以修改成功但是尽量不要这样做因为这个视图可能是多个表联合起来生成的一个结果如果你修改它可能会造成很多表里面的数据都跟着被修改了 二 . 使用视图 复制代码
#修改视图原始表也跟着改
mysql select * from course;
-------------------------
| cid | cname | teacher_id |
-------------------------
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
-------------------------
4 rows in set (0.00 sec)mysql create view course_view as select * from course; #创建表course的视图
Query OK, 0 rows affected (0.52 sec)mysql select * from course_view;
-------------------------
| cid | cname | teacher_id |
-------------------------
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
-------------------------
4 rows in set (0.00 sec)mysql update course_view set cnamexxx; #更新视图中的数据
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4 Changed: 4 Warnings: 0mysql insert into course_view values(5,yyy,2); #往视图中插入数据
Query OK, 1 row affected (0.03 sec)mysql select * from course; #发现原始表的记录也跟着修改了
------------------------
| cid | cname | teacher_id |
------------------------
| 1 | xxx | 1 |
| 2 | xxx | 2 |
| 3 | xxx | 3 |
| 4 | xxx | 2 |
| 5 | yyy | 2 |
------------------------
5 rows in set (0.00 sec) 我们不应该修改视图中的记录而且在涉及多个表的情况下是根本无法修改视图中的记录的如下图 三 修改视图 语法ALTER VIEW 视图名称 AS SQL语句这基本就和删掉视图重新创建一个视图的过程是一样的修改视图没什么好讲的这里就简单提一下就不讲啦~~还不如我们直接删掉再重新创建呢
mysql alter view teacher_view as select * from course where cid3;
Query OK, 0 rows affected (0.04 sec)mysql select * from teacher_view;
------------------------
| cid | cname | teacher_id |
------------------------
| 4 | xxx | 2 |
| 5 | yyy | 2 |
------------------------
rows in set (0.00 sec) 四 删除视图 语法DROP VIEW 视图名称DROP VIEW teacher_view 二. 触发器 使用触发器可以定制用户对某一张表的数据进行【增、删、改】操作时前后的行为注意没有查询在进行增删改操作的时候触发的某个操作称为触发器也就是增删改的行为触发另外的一种行为触发的行为无非就是sql语句的事情及自动运行另外一段sql语句。来看一下触发器怎么来创建 一 创建触发器 # 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN #begin和end里面写触发器要做的sql事情注意里面的代码缩进并且给触发器起名字的时候名字的格式最好这样写有表示意义一看名字就知道要做什么是给哪个表设置的触发器...
END# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN...
END# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN...
END# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN...
END# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN...
END# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN...
END 插入后触发触发器 #准备表
CREATE TABLE cmd ( #这是一张指令信息表你在系统里面执行的任何的系统命令都在表里面写一条记录id INT PRIMARY KEY auto_increment, #idUSER CHAR (32), #用户priv CHAR (10), #权限 cmd CHAR (64), #指令sub_time datetime, #提交时间 success enum (yes, no) #是否执行成功0代表执行失败
);CREATE TABLE errlog ( #指令执行错误的信息统计表专门提取上面cmd表的错误记录id INT PRIMARY KEY auto_increment, #iderr_cmd CHAR (64), #错误指令err_time datetime #错误命令的提交时间
);
#现在的需求是不管正确或者错误的cmd都需要往cmd表里面插入然后如果是错误的记录还需要往errlog表里面插入一条记录
#若果没有触发器我们会怎么实现我们完全可以通过咱们的应用程序来做根据cmd表里面的success这个字段是哪个值yes成功no表示失败在给cmd插入记录的时候判断一下这个值是yes或者no来判断一下成功或者失败如果失败了直接给errlog来插入一条记录
#但是mysql说你的应用程序可以省事儿了你只需要往cmd表里面插入数据就行了没必要你自己来判断了可以使用触发器来实现可以判断你插入的这条记录的success这个字段对应的值然后自动来触发触发器进行errlog表的数据插入#创建触发器
delimiter // 或者写$$其他符号也行但是不要写mysql不能认识的知道一下就行了delimiter 是告诉mysql遇到这句话的时候就将sql语句的结束符分号改成delimiter后面的//
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW #在你cmd表插入一条记录之后触发的。
BEGIN #每次给cmd插入一条记录的时候都会被mysql封装成一个对象叫做NEW里面的字段都是这个NEW的属性IF NEW.success no THEN #mysql里面是可以写这种判断的等值判断只有一个等号然后写thenINSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号并且注意我们必须用delimiter来包裹不然mysql一看到分号就认为你的sql结束了所以会报错END IF ; #然后写end if必须加分号
END// #只有遇到//这个完成的sql才算结束
delimiter ; #然后将mysql的结束符改回为分号#往表cmd中插入记录触发触发器根据IF的条件决定是否插入错误日志
INSERT INTO cmd (USER,priv,cmd,sub_time,success
)
VALUES(chao,0755,ls -l /etc,NOW(),yes),(chao,0755,cat /etc/passwd,NOW(),no),(chao,0755,useradd xxx,NOW(),no),(chao,0755,ps aux,NOW(),yes);#查询错误日志发现有两条
mysql select * from errlog;
------------------------------------------
| id | err_cmd | err_time |
------------------------------------------
| 1 | cat /etc/passwd | 2017-09-14 22:18:48 |
| 2 | useradd xxx | 2017-09-14 22:18:48 |
------------------------------------------
2 rows in set (0.00 sec)
复制代码 特别的NEW表示即将插入的数据行OLD表示即将删除的数据行。 二 使用触发器 触发器无法由用户直接调用而由对表的【增/删/改】操作被动引发的。 三 删除触发器 drop trigger tri_after_insert_cmd; 三. 事务 事务用于将某些操作的多个SQL作为原子性操作也就是这些sql语句要么同时成功要么都不成功事务的其他特性在我第一篇博客关于事务的介绍里面有这里就不多做介绍啦一旦有某一个出现错误即可回滚到原来的状态从而保证数据库数据完整性。 简单来说我给一个姑娘转账姑娘那儿收到了200你的账户上扣了200这两个操作是不是两个sql语句这两个sql语句是你的应用程序发给mysql服务端的并且这两个sql语句都要一起执行不然数据就错了你想想是不是。并且如果你通过应用程序发送这两条sql的时候由于网络问题你只发送了一个sql过来那只有一个账户改了数据另外一个没改那数据是不是就出错了啊。这就是事务要完成的事情。 create table user(
id int primary key auto_increment,
name char(32),
balance int
);insert into user(name,balance)
values
(wsb,1000),
(chao,1000),
(ysb,1000);#原子操作
start transaction;
update user set balance900 where namewsb; #买支付100元
update user set balance1010 where namechao; #中介拿走10元
update user set balance1090 where nameysb; #卖家拿到90元
commit; #只要不进行commit操作就没有保存下来没有刷到硬盘上#出现异常回滚到初始状态
start transaction;
update user set balance900 where namewsb; #买支付100元
update user set balance1010 where namechao; #中介拿走10元
uppdate user set balance1090 where nameysb; #卖家拿到90元,出现异常没有拿到
rollback; #如果上面三个sql语句出现了异常就直接rollback数据就直接回到原来的状态了。但是执行了commit之后rollback这个操作就没法回滚了
#我们要做的是检测这几个sql语句是否异常没有异常直接commit有异常就rollback但是现在单纯的只是开启了事务但是还没有说如何检测异常我们先来一个存储过程来捕获异常等我们学了存储过程再细说存储过程。
commit;#通过存储过程来捕获异常(shit写存储过程的是注意每一行都不要缩进按照下面的缩进来写居然让我翻车了我记住你了~~~)我的代码直接黏贴就能用。
delimiter //
create PROCEDURE p5()
BEGIN
DECLARE exit handler for sqlexception
BEGIN
rollback;
END;START TRANSACTION;
update user set balance900 where namewsb; #买支付100元
update user set balance1010 where namechao; #中介拿走10元
#update user2 set balance1090 where nameysb; #卖家拿到90元
update user set balance1090 where nameysb; #卖家拿到90元
COMMIT;END //
delimiter ;mysql select * from user;
-------------------
| id | name | balance |
-------------------
| 1 | wsb | 1000 |
| 2 | chao | 1000 |
| 3 | ysb | 1000 |
-------------------
3 rows in set (0.00 sec) 四 存储过程 一 介绍 存储过程包含了一系列可执行的sql语句存储过程存放于MySQL中通过调用它的名字可以执行其内部的一堆sql。到目前为止我们上面学的视图、触发器、事务等为我们简化了应用程序级别写sql语句的复杂程度让我们在应用程序里面写sql更简单方便了但是我们在应用程序上还是需要自己写sql的而我们下面要学的存储过程它是想让我们的应用程序不需要再写sql语句了所有的sql语句全部放到mysql里面被mysql封装成存储过程说白了它就是一个功能这个功能对应着一大堆的sql语句这些语句里面可以包括我们前面学的视图啊、触发器啊、事务啊、等等的内容也就是说存储过程其实是什么是一堆sql的集合体可以直接用mysql里面提供的一堆功能有了存储过程以后它的好处是我项目逻辑中需要的各种查询都可以让DBA或者你自己封装到存储过程里面以后使用的时候直接调用存储过程名就可以了在开发应用的时候就简单了就不要应用程序员进行sql语句的开发了但是你想如果你真的这么做了确实很有好处简单很多应用程序的开发和数据库sql语句的开发完全的解耦了这样专门的人做专门的事情专门招一个应用开发的人开发应用程序招一个开发型DBA会sql的开发他把sql写完之后封装成一个个的存储过程给应用程序员用就行了对不对这个DBA就不单纯的是管理数据库系统了还需要会写sql语句那这样你的应用程序开发的效率就高了运行效率也提高了你开发应用程序的时候如果写了一堆的sql语句这些语句是不是要通过网络传输传输到mysql服务端来执行然后将结果返回给你的应用程序那么在传输的时候你说好多的sql语句和简单的一个存储过程的名字哪个传输的速度快哪个发送给服务端的速度快当然是单纯的一个存储过程的名字更快。 所以摆在你面前有两种开发模式 第一种是招一个会开发应用程序的并且这个人还要会sql开发这样的人既写应用程序还写sql语句这种情况你可以招两个人一个是前面说的还有一个是数据库管理人员单纯只会管理数据库的而不会sql开发的人这样好招人工资也不高。应用程序员--只需要开发应用程序的逻辑 。 sql开发人员--编写存储过程 第二种招一个应用程序开发的只需要会应用程序级别的开发再招一个会sql开发的DBA。应用程序员--开发应用写原生sql 。 数据库人员负责维护数据库的正常运行 我们比较一下这两种的开发模式第二种解耦和开发效率高运行效率也高所以以后最好采用第一种开发模式哈哈是不是神反转原因是什么呢钱只是一个方面主要还是因为以后如果你想扩展那就很不方便了为什么呢因为通常sql开发人员不如你的应用程序员更懂你的业务逻辑一旦你要扩展一个功能还需要跨部门沟通导致这种工作方式受限的不只是技术层面了这种方式在技术层面肯定是效率高的但是要考虑人为因素还有成本方面的考虑所以通常咱们以后做开发不要想着会有人给你写sql需要你自己写的很熟练。这样你一个部门就能搞定这两件事情。 第二种方式其实也比较麻烦你开发程序员自己需要写sql并且写出来的sql还存在效率问题那么有没有一种方式可以不让开发程序员自己再写sql了搞一个封装程度更高的东西让你来调用有没有这种方式呢有就是第三种方式 第三种应用程序除了开发应用程序的逻辑不需要编写原生sql只需要使用别人写好的框架基于框架来处理数据框架提供的功能是ORM对象关系映射和对象有关系就是在应用程序里面只需要定义一堆的类每个类对应数据库里面的一张表这个类一实例化也就是一个类对象对应表里面的一条记录得到对象以后这个对象除了有数据之外还有处理该条信息的方法增删改查都有了全都封装成了对象的一个一个的方法了意味着你以后再想进行查询就没必要写原生sql了直接基于面向对象的思想来处理类与对象就行了但是这种方式本质上还是使用了原生sql只不过对于应用程序员来说你不用直接写sql了别人写好的ORM框架就帮你处理这件事儿了帮你把你调用的那些接口方法和你传入的参数等等帮你转换为了原生sql然后再往mysql里面提交。所以这种方式和第二种方式有些类似但是比第二种方式要好(前提是第二种方式应用程序员的sql水平比较low的情况下一般会比较low) 这种方式的优点应用程序员不需要再写原生的sql了这意味着开发效率比第二种要高同时还兼顾了第二种方式扩展性高的好处因为本质上还是原生sql 缺点是执行效率还不如第二种方式高因为你现在再想运行需要做什么事 情首先你想你程序里面用的是别人写好的ORM框架或者模块你的sql要想执行你需要做什么事儿你的ORM框架需要把类或者类对象先翻译成原生的sql再沿着网络发到mysql服务端中间对了一个转换的过程所以执行效率其实连方式二都比不上。 总结其实单单从技术层面上看第一种方式肯定是最好的开发和执行效率是最高的扩展性单纯技术层面来看也比较高所以单单从技术层面来考虑这种方式肯定是优选的。但是就目前的现状而言多数还是需要你应用程序员既做应用逻辑的开发还要会原生sql的开发所以应该尽可能的不让mysql来做了所有关于数据的增删改查都交给应用程序级别来做在应用程序中写原生的sql这也是第二种方式和第三种方式的一个共性所有事情基本都交给应用程序级别来做mysql级别基本不做sql的开发这样扩展性也好一些因为所有的事情都交给你应用程序的开发部门来做了自己部门内部进行扩展还是扩展性不错的所以咱们一般从后面两种方式来选择那么后面两种选哪一种呢第二种执行效率比第三种高因为比第三种少了一步类对象转换为sql的过程。第三种开发效率高不要应用程序员再写原生的sql了。所以具体选哪一种看你们自己公司的情况需要快速开发就找第三种如果自己程序员的sql写的很溜(又快又优)那么找第二种一般大公司采用的第一种多一些部门分工非常的明确等你们大家学到Django框架你们就会接触ORM啦~~~学完MySQL之后我们在学框架之前带大家咱们自己开发一个简单的ORM框架能够在你自己的应用程序中使用的ORM写这个ORM框架很重要对你以后的框架和项目的学习很有指导意义说哆啦我们继续说存储过程 使用存储过程的优点 #1. 用于替代程序写的SQL语句实现程序与sql解耦#2. 基于网络传输传别名的数据量小而直接传sql数据量大 使用存储过程的缺点 #1. 程序员扩展功能不方便 上面一大堆话的总结程序与数据库结合使用的三种方式 #方式一MySQL存储过程程序调用存储过程#方式二MySQL程序纯SQL语句#方式三MySQL:程序类和对象即ORM本质还是纯SQL语句 二 创建简单存储过程无参 delimiter //
create procedure p1()
BEGINselect * from blog;INSERT into blog(name,sub_time) values(xxx,now());
END //
delimiter ;#在mysql中调用
call p1(); #类似于MySQL的函数但不是函数昂别搞混了MySQL的函数(count()\max()\min()等等)都是放在sql语句里面用的不能单独的使用存储过程是可以直接调用的 call 名字括号;
#MySQL的视图啊触发器啊if判断啊等等都能在存储过程里面写这是一大堆的sql的集合体都可以综合到这里面
#在python中基于pymysql调用
cursor.callproc(p1)
print(cursor.fetchall()) 另外存储过程是可以传参数的看下面的内容 三 创建存储过程有参 对于存储过程可以接收参数其参数有三类#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值 in:传入参数 delimiter //
create procedure p2(in n1 int, #n1参数是需要传入的也就是接收外部数据的并且这个数据必须是int类型in n2 int
)
BEGINselect * from blog where id n1; #直接应用变量
END //
delimiter ;#调用存储过程的两种方式或者说是两个地方吧#在mysql中调用call p2(3,2)#在python中基于pymysql调用cursor.callproc(p2,(3,2))print(cursor.fetchall()) #通过存储过程的传参来看也能体现出我们学习的Python的灵活性传参不需要指定类型也不需要声明这个参数是传入的还是返回出来的参数既可以传入这个参数也可以直接通过return返回。 out返回值 #查看存储过程的一些信息show create procedure p3; #查看视图啊、触发器啊都这么看还可以用\Gshow create procedure p3\G;\G的意思是你直接查看表结构可能横向上显示不完\G是让表给你竖向显示一row是一行的字段
delimiter //
create procedure p3(in n1 int,out res int
)
BEGINselect * from blog where id n1; set res 1; #我在这里设置一个res1如果上面的所有sql语句全部正常执行了那么这一句肯定也就执行了那么此时res1如果我最开始传入的时候给res的值设置的是0
#那么你想最后我接收到的返回值如果是0那么说明你中间肯定有一些sql语句执行失败了
#注意写法out的那个参数可以用set来设置set设置之后表示这个res可以作为返回值并且不需要像python一样写一个return你直接set之后的值就是这个存储过程的返回值
END //
delimiter ;#在mysql中调用
set res0; #这是MySQL中定义变量名的固定写法(set 变量名值)可以自己规定好0代表假执行失败1代表真执行成功如果这个被改为1了说明存储过程中的sql语句执行成功了
call p3(3,res);#注意不要这样写call p331这样out的参数值你写死了没法确定后面这个1是不是成功了也就是说随后这个out的值可能改成0了也就是失败了但是这样你就判断不了了你后面查看的这个res就成1了所以这个参数应该是一个变量名昂定义变量名就是上一句,如果你直接传一个常量数字会报错的写法不对。
select res; #看一下这个结果就知道这些sql语句是不是执行成功了大家明白了吗~~~#在python中基于pymysql调用在python中只需要知道存储过程的名字就行了
cursor.callproc(p3,(3,0)) #0相当于set res0为什么这里这个out参数可以写常数0啊因为你用的pymysql,人家会帮你搞定pymysql其实会帮你写成这样第一个参数变量名_p3_03第二个_p3_10也就是pymysql会自动帮你对应上一个变量名pymysql只是想让你写的时候更方便
#沿着网络将存储过程名和参数发给了mysql服务端比咱们发一堆的sql语句肯定要快对了mysql帮你调用存储过程
print(cursor.fetchall()) #查询select的查询结果cursor.execute(select _p3_0,_p3_1;) #_p3_0代表第一个参数_p3_1代表第二个参数即返回值
print(cursor.fetchall())
#别忘了关掉
cursor.close()
conn.close()
#注意昂存储过程在哪个库里面建的就只能在哪个库里面用 inout既可传入又可以返回值: delimiter //
create procedure p4(inout n1 int
)
BEGINselect * from blog where id n1;set n1 1;
END //
delimiter ;#在mysql中调用
set x3;
call p4(x);
select x;#在python中基于pymysql调用
cursor.callproc(p4,(3,))
print(cursor.fetchall()) #查询select的查询结果cursor.execute(select _p4_0;)
print(cursor.fetchall()) 存储过程结合事务来写 delimiter //create procedure p4(out status int)BEGIN1. 声明如果出现异常则执行{set status 1;rollback;}开始事务-- 由秦兵账户减去100-- 方少伟账户加90-- 张根账户加10commit;结束set status 2;END //delimiter ;#实现
delimiter //
create PROCEDURE p5(OUT p_return_code tinyint
)
BEGIN DECLARE exit handler for sqlexception #声明如果一旦出现异常则执行下面的这个begin和end里面的操作BEGIN -- ERROR #--是什么啊忘了吧是注释的意思就告诉你后面是对错误的处理set p_return_code 1; #将out返回值改为1了这是你自己规定的1表示出错了rollback; #回滚事务END; DECLARE exit handler for sqlwarning #声明了出现警告信息之后你的操作行为BEGIN -- WARNING set p_return_code 2; rollback; END; START TRANSACTION; #其实咱们这个存储过程里面就是执行这个事务并且一直检测着这个事务一旦出错或者出现警告就rollbackDELETE from tb1; #事务里面的任何一条sql执行失败或者执行出现警告都会执行上面我们声明的那些对应的操作如果没有任何的异常就会自动执行下面的commit并执行后面成功的sqlinsert into blog(name,sub_time) values(yyy,now()); #拿我的代码进行测试的时候别忘了改成你自己库里的表还有表里面对应的字段名要有的自己测试的时候可以自己写一个错误的sql来试试看COMMIT; -- SUCCESS set p_return_code 0; #0代表执行成功END //
delimiter ;#在mysql中调用存储过程
set res123;
call p5(res);
select res;#在python中基于pymysql调用存储过程
cursor.callproc(p5,(123,)) #注意后面这个参数是个元祖别忘了逗号按照我们上面规定的上面有三个值0120成功、1失败、2警告也是失败。所以我们传给这个out参数的值只要不是这三个值就行了这里给的是100
print(cursor.fetchall()) #查询select的查询结果cursor.execute(select _p5_0;)
print(cursor.fetchall())
#执行成功以后查看一下结果就能看到执行后的值了 四 执行存储过程 在MySQL中执行存储过程 -- 无参数
call proc_name()-- 有参数全in
call proc_name(1,2)-- 有参数有inoutinout
set t10;
set t23;
call proc_name(1,2,t1,t2)执行存储过程 在python中基于pymysql来执行存储过程 import pymysqlconn pymysql.connect(host127.0.0.1, port3306, userroot, passwd123, dbt1)
cursor conn.cursor(cursorpymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc(p1, args(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute(select _p1_0,_p1_1,_p1_2,_p1_3)
result cursor.fetchall()#conn.commit()
cursor.close()
conn.close()print(result) 五 删除存储过程 drop procedure proc_name; 五. 函数 MySQL中提供了许多内置函数但是注意这些函数只能在sql语句中使用不能单独调用昂例如其实下面的有些函数我们都已经用过了其他的如果你们用到了咱们再过来查吧好不 一、数学函数ROUND(x,y)返回参数x的四舍五入的有y位小数的值RAND()返回到内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。二、聚合函数(常用于GROUP BY从句的SELECT查询中)AVG(col)返回指定列的平均值COUNT(col)返回指定列中非NULL值的个数MIN(col)返回指定列的最小值MAX(col)返回指定列的最大值SUM(col)返回指定列的所有值之和GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果 三、字符串函数CHAR_LENGTH(str)返回值为字符串str 的长度长度的单位为字符。一个多字节字符算作一个单字符。CONCAT(str1,str2,...)字符串拼接如有任何一个参数为NULL 则返回值为 NULL。CONCAT_WS(separator,str1,str2,...)字符串拼接自定义连接符CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL。CONV(N,from_base,to_base)进制转换例如SELECT CONV(a,16,2); 表示将 a 由16进制转换为2进制字符串表示FORMAT(X,D)将数字X 的格式写为#,###,###.##,以四舍五入的方式保留小数点后 D 位 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点或不含小数部分。例如SELECT FORMAT(12332.1,4); 结果为 12,332.1000INSERT(str,pos,len,newstr)在str的指定位置插入字符串pos要替换位置其实位置len替换的长度newstr新字符串特别的如果pos超过原字符串长度则返回原字符串如果len超过原字符串长度则由新字符串完全替换INSTR(str,substr)返回字符串 str 中子字符串的第一个出现位置。LEFT(str,len)返回字符串str 从开始的len位置的子序列字符。LOWER(str)变小写UPPER(str)变大写REVERSE(str)返回字符串 str 顺序和字符顺序相反。SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)不带有len 参数的格式从字符串str返回一个子字符串起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样则子字符串的位置起始于字符串结尾的pos 字符而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。mysql SELECT SUBSTRING(Quadratically,5);- raticallymysql SELECT SUBSTRING(foobarbar FROM 4);- barbarmysql SELECT SUBSTRING(Quadratically,5,6);- raticamysql SELECT SUBSTRING(Sakila, -3);- ilamysql SELECT SUBSTRING(Sakila, -5, 3);- akimysql SELECT SUBSTRING(Sakila FROM -4 FOR 2);- ki四、日期和时间函数CURDATE()或CURRENT_DATE() 返回当前的日期CURTIME()或CURRENT_TIME() 返回当前的时间DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)DAYOFMONTH(date) 返回date是一个月的第几天(1~31)DAYOFYEAR(date) 返回date是一年的第几天(1~366)DAYNAME(date) 返回date的星期名如SELECT DAYNAME(CURRENT_DATE);FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式格式化UNIX时间戳tsHOUR(time) 返回time的小时值(0~23)MINUTE(time) 返回time的分钟值(0~59)MONTH(date) 返回date的月份值(1~12)MONTHNAME(date) 返回date的月份名如SELECT MONTHNAME(CURRENT_DATE);NOW() 返回当前的日期和时间QUARTER(date) 返回date在一年中的季度(1~4)如SELECT QUARTER(CURRENT_DATE);WEEK(date) 返回日期date为一年中第几周(0~53)YEAR(date) 返回日期date的年份(1000~9999)重点:DATE_FORMAT(date,format) 根据format字符串格式化date值mysql SELECT DATE_FORMAT(2009-10-04 22:23:00, %W %M %Y);- Sunday October 2009mysql SELECT DATE_FORMAT(2007-10-04 22:23:00, %H:%i:%s);- 22:23:00mysql SELECT DATE_FORMAT(1900-10-04 22:23:00,- %D %y %a %d %m %b %j);- 4th 00 Thu 04 10 Oct 277mysql SELECT DATE_FORMAT(1997-10-04 22:23:00,- %H %k %I %r %T %S %w);- 22 22 10 10:23:00 PM 22:23:00 00 6mysql SELECT DATE_FORMAT(1999-01-01, %X %V);- 1998 52mysql SELECT DATE_FORMAT(2006-06-00, %d);- 00五、加密函数MD5() 计算字符串str的MD5校验和PASSWORD(str) 返回字符串str的加密版本这个加密过程是不可逆转的和UNIX密码加密过程使用不同的算法。六、控制流函数 CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真则返回resultN否则返回defaultCASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等则返回resultN否则返回defaultIF(test,t,f) 如果test是真返回t否则返回fIFNULL(arg1,arg2) 如果arg1不是空返回arg1否则返回arg2NULLIF(arg1,arg2) 如果arg1arg2返回NULL否则返回arg1 七、控制流函数小练习
#7.1、准备表将下面这些内容保存为一个.txt文件或者.sql然后通过navicat的运行sql文件的功能导入到数据库中还记得吗
/*
Navicat MySQL Data TransferSource Server : localhost_3306
Source Server Version : 50720
Source Host : localhost:3306
Source Database : studentTarget Server Type : MYSQL
Target Server Version : 50720
File Encoding : 65001Date: 2018-01-02 12:05:30
*/SET FOREIGN_KEY_CHECKS0;-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS course;
CREATE TABLE course (c_id int(11) NOT NULL,c_name varchar(255) DEFAULT NULL,t_id int(11) DEFAULT NULL,PRIMARY KEY (c_id),KEY t_id (t_id)
) ENGINEInnoDB DEFAULT CHARSETutf8;-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO course VALUES (1, python, 1);
INSERT INTO course VALUES (2, java, 2);
INSERT INTO course VALUES (3, linux, 3);
INSERT INTO course VALUES (4, web, 2);-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS score;
CREATE TABLE score (id int(11) NOT NULL AUTO_INCREMENT,s_id int(10) DEFAULT NULL,c_id int(11) DEFAULT NULL,num double DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT12 DEFAULT CHARSETutf8;-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO score VALUES (1, 1, 1, 79);
INSERT INTO score VALUES (2, 1, 2, 78);
INSERT INTO score VALUES (3, 1, 3, 35);
INSERT INTO score VALUES (4, 2, 2, 32);
INSERT INTO score VALUES (5, 3, 1, 66);
INSERT INTO score VALUES (6, 4, 2, 77);
INSERT INTO score VALUES (7, 4, 1, 68);
INSERT INTO score VALUES (8, 5, 1, 66);
INSERT INTO score VALUES (9, 2, 1, 69);
INSERT INTO score VALUES (10, 4, 4, 75);
INSERT INTO score VALUES (11, 5, 4, 66.7);-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS student;
CREATE TABLE student (s_id varchar(20) NOT NULL,s_name varchar(255) DEFAULT NULL,s_age int(10) DEFAULT NULL,s_sex char(1) DEFAULT NULL,PRIMARY KEY (s_id)
) ENGINEInnoDB DEFAULT CHARSETutf8;-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO student VALUES (1, 鲁班, 12, 男);
INSERT INTO student VALUES (2, 貂蝉, 20, 女);
INSERT INTO student VALUES (3, 刘备, 35, 男);
INSERT INTO student VALUES (4, 关羽, 34, 男);
INSERT INTO student VALUES (5, 张飞, 33, 女);-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS teacher;
CREATE TABLE teacher (t_id int(10) NOT NULL,t_name varchar(50) DEFAULT NULL,PRIMARY KEY (t_id)
) ENGINEInnoDB DEFAULT CHARSETutf8;-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO teacher VALUES (1, 大王);
INSERT INTO teacher VALUES (2, alex);
INSERT INTO teacher VALUES (3, chao);
INSERT INTO teacher VALUES (4, peiqi);#7.2、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ 60]select score.c_id,course.c_name, sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as [100-85],sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as [85-70],sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as [70-60],sum(CASE WHEN num 60 THEN 1 ELSE 0 END) as [ 60]
from score,course where score.c_idcourse.c_id GROUP BY score.c_id; 需要掌握的函数date_format 这个我们要讲一讲将来你可能会用的到的我们前面没有讲过的一个东西。 #1 基本使用
mysql SELECT DATE_FORMAT(2009-10-04 22:23:00, %W %M %Y);- Sunday October 2009
mysql SELECT DATE_FORMAT(2007-10-04 22:23:00, %H:%i:%s);- 22:23:00
mysql SELECT DATE_FORMAT(1900-10-04 22:23:00,- %D %y %a %d %m %b %j);- 4th 00 Thu 04 10 Oct 277
mysql SELECT DATE_FORMAT(1997-10-04 22:23:00,- %H %k %I %r %T %S %w);- 22 22 10 10:23:00 PM 22:23:00 00 6
mysql SELECT DATE_FORMAT(1999-01-01, %X %V);- 1998 52
mysql SELECT DATE_FORMAT(2006-06-00, %d);- 00#2 准备表和记录
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);#3. 提取sub_time字段的值按照格式后的结果即年月来分组统计一下每年每月的博客数量怎么写呢按照sub_time分组但是我们的sub_time是年月日加时间我想看每年每月直接按照sub_time来分组是不行的每篇博客的发表时间基本都是不同的所以我们需要通过这个date_format来搞了
SELECT DATE_FORMAT(sub_time,%Y-%m),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,%Y-%m);#结果
-----------------------------------------
| DATE_FORMAT(sub_time,%Y-%m) | COUNT(1) |
-----------------------------------------
| 2015-03 | 2 |
| 2016-07 | 4 |
| 2017-03 | 3 |
-----------------------------------------
3 rows in set (0.00 sec) 一 自定义函数自己简单看看吧 #注意
#函数中不要写sql语句否则会报错函数仅仅只是一个功能是一个在sql中被应用的功能
#若要想在begin...end...中写sql请用存储过程 delimiter //
create function f1(i1 int,i2 int)
returns int
BEGINdeclare num int;set num i1 i2;return(num);
END //
delimiter ; delimiter //
create function f5(i int
)
returns int
begindeclare res int default 0;if i 10 thenset res100;elseif i 20 thenset res200;elseif i 30 thenset res300;elseset res400;end if;return res;
end //
delimiter ; 二 删除函数 drop function func_name; 三 执行函数 # 获取返回值
select UPPER(chao) into res;
SELECT res;# 在查询中使用
select f1(11,nid) ,name from tb2; 六. 流程控制 一 条件语句 if条件语句 delimiter //
CREATE PROCEDURE proc_if ()
BEGINdeclare i int default 0;if i 1 THENSELECT 1;ELSEIF i 2 THENSELECT 2;ELSESELECT 7;END IF;END //
delimiter ; 二 循环语句 while循环#后面讲索引的时候咱们会用到while循环注意语法 delimiter //
CREATE PROCEDURE proc_while ()
BEGINDECLARE num INT ;SET num 0 ;WHILE num 10 DOSELECTnum ;SET num num 1 ;END WHILE ;END //
delimiter ; 到这里你会发现其实sql也是一个开发语言基本数据类型啊函数啊流程控制啊(if、while等)它都有。下面这两个我们简单看一下用法就行啦~~~ repeat循环 delimiter //
CREATE PROCEDURE proc_repeat ()
BEGINDECLARE i INT ;SET i 0 ;repeatselect i;set i i 1;until i 5end repeat;END //
delimiter ; loop BEGINdeclare i int default 0;loop_label: loopset ii1;if i8 theniterate loop_label;end if;if i10 thenleave loop_label;end if;select i;end loop loop_label;END https://www.processon.com/view/link/5c10b974e4b0fd4819953063 转载于:https://www.cnblogs.com/tjp40922/p/10105993.html
