1.pymysql模块操作数据库详细
import pymysql # user = 'chun'
# psw =
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',),('chun',),('hong',)])
print(r)
conn.commit()
cursor.close()
conn.close()
import pymysql # user = 'chun'
# psw =
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 = -- ' and password='%s'
cursor.execute(sql)
result = cursor.fetchone()
cursor.close()
conn.close()
if result:
print('登录成功')
else:
print('登录失败')
sql注入
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(,mode='relative') # 相对当前位置移动
# cursor.scroll(,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()
# 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>)
指定映射：
select id,name,,sum(x)/count()
条件：
case when id> then xx else xx end 三元运算： if(isnull(xx),,)
补充：
左右连表： 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
alex 权限
订单管理
用户劵
Bug管理
.... 用户类型&权限 程序：
用户登录 基于角色的权限管理 用户信息
id username pwd role_id
alex
eric 权限
订单管理
用户劵
Bug管理
.... 角色表：
IT部门员工
咨询员工
IT主管 角色权限管理 ===>
. 基于角色的权限管理
. 需求分析 今日内容：
. 视图
100个SQL：
: v1 select .. from v1
select asd from v1
某个查询语句设置别名，日后方便使用 - 创建
create view 视图名称 as SQL PS: 虚拟
- 修改
alter view 视图名称 as SQL - 删除
drop view 视图名称; . 触发器 当对某张表做：增删改操作时，可以使用触发器自定义关联行为 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('女',,'陈涛'),('女',,'张根'); -- NEW,代指新数据
-- OLD,代指老数据 . 函数
def f1(a1,a2):
return a1 + a2 f1()
bin() 内置函数：
执行函数 select CURDATE(); blog
id title ctime
asdf -
asdf -
asdf -
asdf - select ctime,count() from blog group ctime select DATE_FORMAT(ctime, "%Y-%m"),count() from blog group DATE_FORMAT(ctime, "%Y-%m")
-
- 自定义函数(有返回值)： delimiter \\
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int default ;
set num = i1 + i2;
return(num);
END \\
delimiter ; SELECT f1(,); . 存储过程
保存在MySQL上的一个别名 => 一坨SQL语句 别名() 用于替代程序员写SQL语句 方式一：
MySQL: 存储过程
程序：调用存储过程
方式二：
MySQL：。。
程序：SQL语句
方式三：
MySQL：。。
程序：类和对象（SQL语句） . 简单
create procedure p1()
BEGIN
select * from student;
INSERT into teacher(tname) values("ct");
END call p1()
cursor.callproc('p1')
. 传参数（in,out,inout）
delimiter //
create procedure p2(
in n1 int,
in n2 int
)
BEGIN select * from student where sid > n1;
END //
delimiter ; call p2(,)
cursor.callproc('p2',(,)) . 参数 out
delimiter //
create procedure p3(
in n1 int,
inout n2 int
)
BEGIN
set n2 = ;
select * from student where sid > n1;
END //
delimiter ; set @v1 = ;
call p2(,@v1)
select @v1; set @_p3_0 =
ser @_p3_1 =
call p3(@_p3_0,@_p3_1)
select @_p3_0,@_p3_1 cursor.callproc('p3',(,))
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',(,))
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 用于标识存储过程的执行结果 ,
)
BEGIN
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
insert into vv(..)
END //
delimiter ; . 事务 delimiter //
create procedure p4(
out status int
)
BEGIN
. 声明如果出现异常则执行{
set status = ;
rollback;
} 开始事务
-- 由秦兵账户减去100
-- 方少伟账户加90
-- 张根账户加10
commit;
结束 set status = ; END //
delimiter ; ===============================
delimiter \\
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = ;
rollback;
END; START TRANSACTION;
DELETE from tb1;
insert into tb2(name)values('seven');
COMMIT; -- SUCCESS
set p_return_code = ; END\\
delimiter ; . 游标 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 ; . 动态执行SQL（防SQL注入） delimiter //
create procedure p7(
in tpl varchar(),
in arg int
)
begin
. 预检测某个东西 SQL语句合法性
. SQL =格式化 tpl + arg
. 执行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 > ?",) ===> 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 ; 数据库相关操作：
. SQL语句 *****
- select xx() from xx ;
. 利用MySQL内部提供的功能
数据库基本知识
参考博客：
http://www.cnblogs.com/wupeiqi/articles/5713323.html
http://www.cnblogs.com/wupeiqi/articles/5716963.html . 数据库是什么
. MySQL安装
. 用户授权
.
数据库操作
-
数据表
- 数据类型
- 是否可以为空
- 自增
- 主键
- 外键
- 唯一索引 数据行
增
删
改
查
排序： 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
- 关闭游标
- 关闭连接 今日内容：
. 索引
作用：
- 约束
- 加速查找
索引：
- 主键索引：加速查找 + 不能为空 + 不能重复
- 普通索引：加速查找
- 唯一索引：加速查找 + 不能重复
- 联合索引（多列）：
- 联合主键索引
- 联合唯一索引
- 联合普通索引 加速查找：
快：
select * from tb where name='asdf'
select * from tb where id=
假设：
id name email
...
...
.. 无索引：从前到后依次查找
索引：
id 创建额外文件(某种格式存储)
name 创建额外文件(某种格式存储)
email 创建额外文件(某种格式存储) create index ix_name on userinfo3(email);
name email 创建额外文件(某种格式存储) 索引种类（某种格式存储）：
hash索引：
单值快
范围
btree索引： btree索引
二叉树 ========》 结果：快 《========
数据库基本知识