1.pymysql模块操作数据库详细
import pymysql # user = 'chun' # psw = 123 conn = pymysql.connect(host='localhost',user='root',password='shang123',database='shang') cursor = conn.cursor() sql = 'insert into userinfo(username,password) values(%s,%s)' r = cursor.executemany(sql,[('shang',1),('chun',1),('hong',1)]) print(r) conn.commit() cursor.close() conn.close()
import pymysql # user = 'chun' # psw = 123 conn = pymysql.connect(host='localhost',user='root',password='shang123',database='shang') cursor = conn.cursor() sql = 'select * from userinfo limit 3' r = cursor.execute(sql) # result = cursor.fetchone() # print(result) # result = cursor.fetchone() # print(result) # result = cursor.fetchone() # print(result) result = cursor.fetchall() print(result) cursor.close() conn.close()
import pymysql user = input("username:") pwd = input("password:") conn = pymysql.connect(host="localhost",user='root',password='',database="db666") cursor = conn.cursor() sql = "select * from userinfo where username='%s' and password='%s'" %(user,pwd,) # select * from userinfo where username='uu' or 1=1 -- ' and password='%s' cursor.execute(sql) result = cursor.fetchone() cursor.close() conn.close() if result: print('登录成功') else: print('登录失败')
import pymysql # 增加，删，该 # conn = pymysql.connect(host="localhost",user='root',password='',database="db666") # cursor = conn.cursor() # sql = "insert into userinfo(username,password) values('root','123123')" # 受影响的行数 # r = cursor.execute(sql) # # ****** # conn.commit() # cursor.close() # conn.close() # conn = pymysql.connect(host="localhost",user='root',password='',database="db666") # cursor = conn.cursor() # # sql = "insert into userinfo(username,password) values(%s,%s)" # # cursor.execute(sql,(user,pwd,)) # # sql = "insert into userinfo(username,password) values(%s,%s)" # # 受影响的行数 # r = cursor.executemany(sql,[('egon','sb'),('laoyao','BS')]) # # ****** # conn.commit() # cursor.close() # conn.close() # 查 # conn = pymysql.connect(host="localhost",user='root',password='',database="db666") # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # sql = "select * from userinfo" # cursor.execute(sql) # cursor.scroll(1,mode='relative') # 相对当前位置移动 # cursor.scroll(2,mode='absolute') # 相对绝对位置移动 # result = cursor.fetchone() # print(result) # result = cursor.fetchone() # print(result) # result = cursor.fetchone() # print(result) # result = cursor.fetchall() # print(result) # result = cursor.fetchmany(4) # print(result) # cursor.close() # conn.close() # 新插入数据的自增ID: cursor.lastrowid # import pymysql # # conn = pymysql.connect(host="localhost",user='root',password='',database="db666") # cursor = conn.cursor() # sql = "insert into userinfo(username,password) values('asdfasdf','123123')" # cursor.execute(sql) # conn.commit() # print(cursor.lastrowid) # cursor.close() # conn.close()
1. MySQL：文件管理的软件
2. 三部分：
- 服务端
- SQL语句
- 客户端
3. 客户端：
- mysql
- navicat
4. 授权操作
- 用户操作
- 授权操作
5. SQL语句
- 数据库操作
- create database xx default charset utf8;
- drop database xx;
- 数据表
- 列
- 数字
整数
小数
- 字符串
- 时间
- 二进制
- 其他：引擎，字符编码，起始值
- 主键索引
- 唯一索引
- 外键
- 一对多
- 一对一
- 多对多
- 数据行
- 增
- 删
- 改
- 查
- in not in
- between and
- limit
- group by having
- order by
- like "%a"
- left join xx on 关系
- 临时表
select * from (select * from tb where id< 10) as B;
-
select
id,
name,
1,
(select count(1) from tb)
from tb2
SELECT
student_id,
(select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
(select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
(select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
from score as s1;
- 条件
select
course_id,
max(num),
min(num),
min(num)+1,
case when min(num) <10 THEN 0 ELSE min(num) END as c
from score GROUP BY course_id
select course_id,avg(num),sum(case when num <60 THEN 0 ELSE 1 END),sum(1),sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl from score GROUP BY course_id order by AVG(num) asc,jgl desc;
PS: 数据放在硬盘上
思想：
- 操作
- 设计
今日内容：
1. 练习题
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
-- select score.student_id,student.sname from score
--
-- left join student on score.student_id=student.sid
--
-- where course_id =1 or course_id =2 GROUP BY student_id HAVING count(course_id) > 1
8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
-- select student_id from score where course_id in (
-- select cid from course left JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师"
-- ) GROUP BY student_id having count(course_id) = (select count(cid) from course left JOIN teacher on course.teacher_id = teacher.tid where teacher.tname = "李平老师")
--
--
10、查询有课程成绩小于60分的同学的学号、姓名；
-- select student_id from score where num < 60 GROUP BY student_id
-- select DISTINCT student_id from score where num < 60
-- 查询没有学全所有课的同学的学号、姓名；
11、查询没有学全所有课的同学的学号、姓名；
-- select student_id,count(1) from score GROUP BY student_id HAVING count(1) < (select count(cid) from course);
--
-- 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
-- select course_id from score where student_id = 1;
-- select student_id from score where student_id != 1 and course_id in (select course_id from score where student_id = 1) GROUP BY student_id
-- 13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
-- select course_id from score where student_id = 1;
-- select student_id,count(1) from score where student_id != 1 and course_id in (select course_id from score where student_id = 1) GROUP BY student_id HAVING count(1) = (select count(course_id) from score where student_id = 1)
-- 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
-- 获取和方少伟选课个数相同的通许
-- select count(1) from score where student_id = 1;
--
-- select student_id from score where student_id in (
-- select student_id from score where student_id !=1 GROUP BY student_id HAVING count(1) = (select count(1) from score where student_id = 1)
-- ) and course_id in (select course_id from score where student_id = 1) GROUP BY student_id HAVING count(1) = (select count(1) from score where student_id = 1)
--
--
-- insert into tb(student_id,course_id,num)
--
-- select student_id,2,(SELECT AVG(num) from score where course_id = 2) from score where course_id != 2
-- 17、按平均成绩从低到高 显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
-- 1 90 80 99
-- 2 90 80 99
-- SELECT
-- student_id,
-- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 1) as 语文,
-- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 2) as 数学,
-- (select num from score as s2 where s2.student_id=s1.student_id and course_id = 3) as 英语
-- from score as s1;
--
-- 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
-- select course_id,max(num),min(num),min(num)+1,case when min(num) <10 THEN 0 ELSE min(num) END as c from score GROUP BY course_id
-- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select course_id,avg(num),sum(case when num <60 THEN 0 ELSE 1 END),sum(1),sum(case when num <60 THEN 0 ELSE 1 END)/sum(1) as jgl from score GROUP BY course_id order by AVG(num) asc,jgl desc;
pymysql模块：
pip3 install pymysql -i https://pypi.douban.com/simple
Python模块：对数据库进行操作（SQL语句）
1. Python实现用户登录
2. MySQL保存数据
- 连接、关闭（游标）
- execute() -- SQL注入
- 增删改： conn.commit()
- fetchone fetchall
- 获取插入数据自增ID
SQL语句： 数据行： 临时表：(select * from tb where id>10) 指定映射： select id,name,1,sum(x)/count() 条件： case when id>8 then xx else xx end 三元运算： if(isnull(xx),0,1) 补充： 左右连表： join 上下连表： union # 自动去重 select id,name from tb1 union select num,sname from tb2 # 不去重 select sid,sname from student UNION ALL select sid,sname from student 基于用户权限管理 参考表结构： 用户信息 id username pwd 1 alex 123123 权限 1 订单管理 2 用户劵 3 Bug管理 .... 用户类型&权限 1 1 1 2 2 1 3 1 程序： 用户登录 基于角色的权限管理 用户信息 id username pwd role_id 1 alex 123123 1 2 eric 123123 1 权限 1 订单管理 2 用户劵 3 Bug管理 .... 角色表： 1 IT部门员工 2 咨询员工 3 IT主管 角色权限管理 1 1 1 2 3 1 3 2 3 3 ===> 1. 基于角色的权限管理 2. 需求分析 今日内容： 1. 视图 100个SQL： 88: v1 select .. from v1 select asd from v1 某个查询语句设置别名，日后方便使用 - 创建 create view 视图名称 as SQL PS: 虚拟 - 修改 alter view 视图名称 as SQL - 删除 drop view 视图名称; 2. 触发器 当对某张表做：增删改操作时，可以使用触发器自定义关联行为 insert into tb (....) -- delimiter // -- create trigger t1 BEFORE INSERT on student for EACH ROW -- BEGIN -- INSERT into teacher(tname) values(NEW.sname); -- INSERT into teacher(tname) values(NEW.sname); -- INSERT into teacher(tname) values(NEW.sname); -- INSERT into teacher(tname) values(NEW.sname); -- END // -- delimiter ; -- -- insert into student(gender,class_id,sname) values('女',1,'陈涛'),('女',1,'张根'); -- NEW,代指新数据 -- OLD,代指老数据 3. 函数 def f1(a1,a2): return a1 + a2 f1() bin() 内置函数： 执行函数 select CURDATE(); blog id title ctime 1 asdf 2019-11 2 asdf 2019-11 3 asdf 2019-10 4 asdf 2019-10 select ctime,count(1) from blog group ctime select DATE_FORMAT(ctime, "%Y-%m"),count(1) from blog group DATE_FORMAT(ctime, "%Y-%m") 2019-11 2 2019-10 2 自定义函数(有返回值)： delimiter \\ create function f1( i1 int, i2 int) returns int BEGIN declare num int default 0; set num = i1 + i2; return(num); END \\ delimiter ; SELECT f1(1,100); 4. 存储过程 保存在MySQL上的一个别名 => 一坨SQL语句 别名() 用于替代程序员写SQL语句 方式一： MySQL: 存储过程 程序：调用存储过程 方式二： MySQL：。。 程序：SQL语句 方式三： MySQL：。。 程序：类和对象（SQL语句） 1. 简单 create procedure p1() BEGIN select * from student; INSERT into teacher(tname) values("ct"); END call p1() cursor.callproc('p1') 2. 传参数（in,out,inout） delimiter // create procedure p2( in n1 int, in n2 int ) BEGIN select * from student where sid > n1; END // delimiter ; call p2(12,2) cursor.callproc('p2',(12,2)) 3. 参数 out delimiter // create procedure p3( in n1 int, inout n2 int ) BEGIN set n2 = 123123; select * from student where sid > n1; END // delimiter ; set @v1 = 10; call p2(12,@v1) select @v1; set @_p3_0 = 12 ser @_p3_1 = 2 call p3(@_p3_0,@_p3_1) select @_p3_0,@_p3_1 cursor.callproc('p3',(12,2)) r1 = cursor.fetchall() print(r1) cursor.execute('select @_p3_0,@_p3_1') r2 = cursor.fetchall() print(r2) =======> 特殊 a. 可传参： in out inout b. pymysql cursor.callproc('p3',(12,2)) r1 = cursor.fetchall() print(r1) cursor.execute('select @_p3_0,@_p3_1') r2 = cursor.fetchall() print(r2) 为什么有结果集又有out伪造的返回值？ delimiter // create procedure p3( in n1 int, out n2 int 用于标识存储过程的执行结果 1,2 ) BEGIN insert into vv(..) insert into vv(..) insert into vv(..) insert into vv(..) insert into vv(..) insert into vv(..) END // delimiter ; 4. 事务 delimiter // create procedure p4( out status int ) BEGIN 1. 声明如果出现异常则执行{ set status = 1; rollback; } 开始事务 -- 由秦兵账户减去100 -- 方少伟账户加90 -- 张根账户加10 commit; 结束 set status = 2; END // delimiter ; =============================== delimiter \\ create PROCEDURE p5( OUT p_return_code tinyint ) BEGIN DECLARE exit handler for sqlexception BEGIN -- ERROR set p_return_code = 1; rollback; END; START TRANSACTION; DELETE from tb1; insert into tb2(name)values('seven'); COMMIT; -- SUCCESS set p_return_code = 2; END\\ delimiter ; 5. 游标 delimiter // create procedure p6() begin declare row_id int; -- 自定义变量1 declare row_num int; -- 自定义变量2 declare done INT DEFAULT FALSE; declare temp int; declare my_cursor CURSOR FOR select id,num from A; declare CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; open my_cursor; xxoo: LOOP fetch my_cursor into row_id,row_num; if done then leave xxoo; END IF; set temp = row_id + row_num; insert into B(number) values(temp); end loop xxoo; close my_cursor; end // delimter ; 6. 动态执行SQL（防SQL注入） delimiter // create procedure p7( in tpl varchar(255), in arg int ) begin 1. 预检测某个东西 SQL语句合法性 2. SQL =格式化 tpl + arg 3. 执行SQL语句 set @xo = arg; PREPARE xxx FROM 'select * from student where sid > ?'; EXECUTE xxx USING @xo; DEALLOCATE prepare prod; end // delimter ; call p7("select * from tb where id > ?",9) ===> delimiter \\ CREATE PROCEDURE p8 ( in nid int ) BEGIN set @nid = nid; PREPARE prod FROM 'select * from student where sid > ?'; EXECUTE prod USING @nid; DEALLOCATE prepare prod; END\\ delimiter ; 数据库相关操作： 1. SQL语句 ***** - select xx() from xx ; 2. 利用MySQL内部提供的功能
参考博客： http://www.cnblogs.com/wupeiqi/articles/5713323.html http://www.cnblogs.com/wupeiqi/articles/5716963.html 1. 数据库是什么 2. MySQL安装 3. 用户授权 4. 数据库操作 - 数据表 - 数据类型 - 是否可以为空 - 自增 - 主键 - 外键 - 唯一索引 数据行 增 删 改 查 排序： order by desc/asc 分组：group by 条件：where 连表： left join right join inner join 临时表： 通配符 分页：limit 组合： union 视图（虚拟） 触发器 函数 select xx(f) 存储过程 - 游标 - 事务 - 结果集+ “返回值” pymysql - 连接 connect(...) - 操作（游标） - 增删改 -> commit - 查 -> fetchone,fetchall - SQL注入 - 调用存储过程： callproc('p1',参数) select @_存储过程名称_0 - 关闭游标 - 关闭连接 今日内容： 1. 索引 作用： - 约束 - 加速查找 索引： - 主键索引：加速查找 + 不能为空 + 不能重复 - 普通索引：加速查找 - 唯一索引：加速查找 + 不能重复 - 联合索引（多列）： - 联合主键索引 - 联合唯一索引 - 联合普通索引 加速查找： 快： select * from tb where name='asdf' select * from tb where id=999 假设： id name email ... ... .. 无索引：从前到后依次查找 索引： id 创建额外文件(某种格式存储) name 创建额外文件(某种格式存储) email 创建额外文件(某种格式存储) create index ix_name on userinfo3(email); name email 创建额外文件(某种格式存储) 索引种类（某种格式存储）： hash索引： 单值快 范围 btree索引： btree索引 二叉树 ========》 结果：快 《========
