一、mysql内置方法
1）视图的概念和用法
1、什么是视图 视图就是通过查询得到一张虚拟表，然后保存下来，下次用的直接使用即可 2、为什么要用视图 如果要频繁使用一张虚拟表，可以不用重复查询 3、如何用视图 创建视图： create view teacher2course as select * from teacher inner join course on teacher.tid = course.teacher_id; 查看视图：create * from teacher2course; 删除视图：drop view teacher2course;
特别强调
1、在硬盘中，视图只有表结构文件，没有表数据文件 2、视图通常是用于插叙，尽量不要修改视图中的数据 3、开发人员尽量不要使用视图
2）触发器
1 触发器 在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器 2 为何要用触发器？ 触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行 就会触发触发器的执行，即自动运行另外一段sql代码
创建触发器语法
# 针对插入 create trigger tri_after_insert_t1 after insert on 表名 for each row begin sql代码。。。 end create trigger tri_after_insert_t2 before insert on 表名 for each row begin sql代码。。。 end # 针对删除 create trigger tri_after_delete_t1 after delete on 表名 for each row begin sql代码。。。 end create trigger tri_after_delete_t2 before delete on 表名 for each row begin sql代码。。。 end # 针对修改 create trigger tri_after_update_t1 after update on 表名 for each row begin sql代码。。。 end create trigger tri_after_update_t2 before update on 表名 for each row begin sql代码。。。 end
触发器案例
# 创建被检查的表 CREATE TABLE cmd ( id INT PRIMARY KEY auto_increment, 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, err_cmd CHAR (64), err_time datetime ); # 创建触发器，检查被检查的表，达到条件，储存到另一个表 delimiter $$ create trigger tri_after_insert_cmd after insert on cmd for each row begin if NEW.success = 'no' then insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time); end if; end $$ delimiter ; 删除触发器：drop trigger tri_after_insert_cmd;
特别强调
修改了结尾符，必须要修改回来
delimiter $$
delimiter ;
3）事务，常用于转账
开启一个事务可以包含一些sql语句，这些sql语句要么同时成功
要么一个都别想成功，称之为事务的原子性
常用用于转账操作
示例
create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('wsb',1000), ('egon',1000), ('ysb',1000); #原子操作 start transaction; # 开启事务 update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='ysb'; #卖家拿到90元 rollback; # 回滚，欢迎原有的数值 commit; # 执行之后，才真正的刷新到了硬盘上
python伪代码实现
create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('wsb',1000), ('egon',1000), ('ysb',1000); try: update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='ysb'; #卖家拿到90元 except 异常: rollback; else: commit;
二、mysql的存储过程
1）什么是存储过程
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql
2）三种开发模式
1、 应用程序：只需要开发应用程序的逻辑 mysql：编写好存储过程，以供应用程序调用 优点：开发效率，执行效率都高 缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差 2、 应用程序：除了开发应用程序的逻辑，还需要编写原生sql mysql： 优点：比方式1，扩展性高(非技术性的) 缺点： 1、开发效率，执行效率都不如方式1 2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题 3、 应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM mysql： 优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处 缺点：执行效率连方式2都比不过
3）在mysql中创建，调用存储过程
1.创建存储过程 delimiter $$ # in 只能用于接收值 # out 只能用于返回值 # inout 既能当接收值，又能当返回值 create procedure p1( in m int, in n int, out res int ) begin select tname from teacher where tid > m and tid < n; set res=0; end $$ delimiter ; 2.调用存储过程 set @res=10 call p1(2,4,10); #查看结果 select @res; # 如果res的值被改了，则数据被执行成功了
4）在python中调用存储过程
import pymysql conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', charset='utf8', database='db1' ) cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.callproc('p1',(2,4,10)) # @_p1_0=2,@_p1_1=4,@_p1_2=10 print(cursor.fetchall()) # 执行 p1 里面的sql 语句 cursor.execute('select @_p1_2;') # 执行该sql 语句，目的是为了 查看 @_p1_2 的值 print(cursor.fetchone()) # 查询 @_p1_2 的值，如果为 0 ， 则 存储执行成功 cursor.close() conn.close()
存储过程的案例（与事务结合）
5）创建案例表
create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('wsb',1000), ('egon',1000), ('ysb',1000);
mysql事务存储过程，存储失败案例，回滚
delimiter // create PROCEDURE p5( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; DECLARE exit handler for sqlwarning BEGIN -- WARNING set p_return_code = 2; rollback; END; START TRANSACTION; update user set balance=900 where id =1; update user123 set balance=1010 where id = 2; update user set balance=1090 where id =3; COMMIT; -- SUCCESS set p_return_code = 0; #0代表执行成功 END // delimiter ; # user123表 不存在，必然会报错
python事务存储过程，存储失败案例，回滚，基于上面内容
import pymysql # user表，在db2 库下 conn = pymysql.connect( host='127.0.0.1', port=3306, user='root', charset='utf8', database='db2' ) cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.callproc('p5',(100,)) # @_p5_0 = 100 cursor.execute('select @_p5_0') print(cursor.fetchone()) # {'@_p5_0':0} cursor.close() conn.close()
正确存储案例测试，下面语法正确即可
START TRANSACTION; update user set balance=900 where id =1; update user set balance=1010 where id = 2; update user set balance=1090 where id =3; COMMIT;
三、内置函数归纳
1）数字函数
ROUND(x,y)
返回参数x的四舍五入的有y位小数的值
RAND()
返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
2）聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col) 返回指定列的平均值
COUNT(col) 返回指定列中非NULL值的个数
MIN(col) 返回指定列的最小值
MAX(col) 返回指定列的最大值
SUM(col) 返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
3）字符串函数
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, 则返回结果不带有小数点，或不含小数部分。
方法归纳
INSERT(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); -> 'ratically' mysql> SELECT SUBSTRING('foobarbar' FROM 4); -> 'barbar' mysql> SELECT SUBSTRING('Quadratically',5,6); -> 'ratica' mysql> SELECT SUBSTRING('Sakila', -3); -> 'ila' mysql> SELECT SUBSTRING('Sakila', -5, 3); -> 'aki' mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2); -> 'ki'
4）日期和时间函数
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时间戳ts HOUR(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)
重点：format字符串格式
DATE_FORMAT(date,format) 根据format字符串格式化date值 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'
5）加密函数
MD5()
计算字符串str的MD5校验和
PASSWORD(str)
返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法
6）控制流函数
CASE WHEN[test1] THEN [result1]...ELSE [default] END 如果testN是真，则返回resultN，否则返回default CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等，则返回resultN，否则返回default IF(test,t,f) 如果test是真，返回t；否则返回f IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2 NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
7）控制函数小练习
#7.1、准备表 /* Navicat MySQL Data Transfer Source Server : localhost_3306 Source Server Version : 50720 Source Host : localhost:3306 Source Database : student Target Server Type : MYSQL Target Server Version : 50720 File Encoding : 65001 Date: 2018-01-02 12:05:30 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- 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`) ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8; -- ---------------------------- -- 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- 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`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of teacher -- ---------------------------- INSERT INTO `teacher` VALUES ('1', '大王'); INSERT INTO `teacher` VALUES ('2', 'alex'); INSERT INTO `teacher` VALUES ('3', 'egon'); 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_id=course.c_id GROUP BY score.c_id;
8）需要掌握的操作 date_format ，可根据年，月，日 分组统计
基本使用方法
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字段的值，按照格式后的结果即"年月"来分组 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)
四、python执行mysql语句（增删改查）
pip install pymysql
pymysql的执行的mysql语句
import pymysql conn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', database='db42', charset='utf8' ) cursor=conn.cursor(pymysql.cursors.DictCursor) sql="insert into user(username,password) value(%s,%s)" # 插入数据 rows=cursor.execute(sql,('wxx',123)) print(rows) print(cursor.lastrowid) # 查询当前记录到了多少条了。即查取 id 是几 rows=cursor.execute('update user set username="alexSB" where id=2') # 修改数据 print(rows) # 一次插入多行记录 sql='insert into user(username,password) values(%s,%s)' rows=cursor.executemany(sql,[('lwz','123'),('evia','455'),('lsd','333')]) print(rows) print(cursor.lastrowid) conn.commit() # 只有commit提交才会完成真正的修改 cursor.close() conn.close()
1）插入数据
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='db1',charset='utf8') # db是数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 为后面获取的mysql数据字典话
# sql_content = "insert into tb2 (user,password) values ('huang','agegg')" # 进入db1数据库的tb2表中插入数据。 容易被sql语句注入，不安全
effect_row = cursor.execute("insert into tb2 (user,password) values ('huang','agegg')") 用 cursor.execute() 来操作sql语句更安全
# cursor.execute(sql_content)
conn.commit()
print(cursor.lastrowid) # 因为是自增ID，可以用这个方法查看插上数据的ID cursor.close() conn.close()
字符串格式及加密插入数据
import pymysql
import hashlib
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='db1',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
user = input("请输入用户名:")
pwd = input("请输入密码:")
has = hashlib.md5(bytes('123afggegag',encoding='utf-8'))
has.update(bytes(pwd, encoding='utf-8'))
d = has.hexdigest()
sql_content = "insert into tb2(user,password) values ('%s', '%s')" %(user,d)
cursor.execute(sql_content)
conn.commit()
cursor.close()
conn.close()
2）查看数据
查询数据一
import pymysql conn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123456', database='db1', charset='utf8' ) # cursor = conn.cursor() # 执行这个返回元组形式 cursor=conn.cursor(pymysql.cursors.DictCursor) # 返回字典形式 rows=cursor.execute('select * from class;') # 查询结果，是影响到的行数 print(rows) # 4 输入影响的行数 print(cursor.fetchone()) # {'cid': 1, 'caption': '三年二班'} 返回字典的形式 print(cursor.fetchone()) print(cursor.fetchmany(2)) print(cursor.fetchone()) # None 因为只有4 条记录，再查询就为 None # fetchone() 一条一条的取 # fetchmany(2) 联系取2条 # fetchall() 取出剩下的所有 cursor.close() conn.close()
游标移动的，绝对移动，和相对移动
import pymysql conn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123456', database='db1', charset='utf8' ) cursor=conn.cursor(pymysql.cursors.DictCursor) rows=cursor.execute('select * from class;') print(cursor.fetchall()) cursor.scroll(1,'absolute') # 设置游标，绝对移动 print(cursor.fetchone()) cursor.scroll(1,'relative') # 设置游标，相对移动 print(cursor.fetchone()) cursor.close() conn.close()
3）利用数据库测试登录案例，会有sql注入问题
import pymysql conn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', database='db42', charset='utf8' ) cursor=conn.cursor(pymysql.cursors.DictCursor) inp_user=input('用户名>>：').strip() inp_pwd=input('密码>>：').strip() sql="select * from user where username='%s' and password='%s'" %(inp_user,inp_pwd) print(sql) rows=cursor.execute(sql) if rows: # 查询到语句，则返回1，为真 print('登录成功') else: # 没有查询到语句，则返回0，为假 print('登录失败') cursor.close() conn.close()
sql 语法的漏洞
4）解决sql注入问题。rows=cursor.execute(sql,(inp_user,inp_pwd))
import pymysql conn=pymysql.connect( host='127.0.0.1', port=3306, user='root', password='123', database='db42', charset='utf8' ) cursor=conn.cursor(pymysql.cursors.DictCursor) inp_user=input('用户名>>：').strip() inp_pwd=input('密码>>：').strip() sql="select * from user where username=%s and password=%s" print(sql) rows=cursor.execute(sql,(inp_user,inp_pwd)) if rows: print('登录成功') else: print('登录失败') cursor.close() conn.close()
查询数据二
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='db1',charset='utf8')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
sql_content = "select * from tb2"
cursor.execute(sql_content)
d = cursor.fetchall() # 获取所有的数据 fetchone 获取一个 fetchmany 获取多个
print(d)
for item in d:
print(item)
if item['user'] == 'zhan':
print("hello")
break
conn.commit()
cursor.close()
conn.close()