pymysql模块的数据操作
数据的简单查询操作:
1 import pymysql
2 username=input("请输入用户名:")
3 pwd=input("请输入密码:")
4
5 conn=pymysql.connect(host="localhost",user="kevin",password='12121',database="around") #创建连接
6 cursor=conn.cursor() #创建一个游标对象
7 sql="select * from userinfo where user=%s AND psd=%s"
8 r=cursor.execute(sql,[username,pwd]) #受影响的行数
9 print(r)
10 ret=cursor.fetchone() #查询到的结果
11 print(ret)
12
13 cursor.close() #关闭游标
14 conn.close()
添加数据操作:
1 import pymysql
2 conn=pymysql.connect(host="localhost",user="kevin",password="12121",database='around')
3 cursor=conn.cursor()
4 sql="insert into userinfo(user,psd) VALUES (%s,%s)"
5 r=cursor.executemany(sql,[('jack','1234'),('lisa','12138'),('time','11594')]) #插入多行记录时必须用executemany
6 conn.commit()
7 print(r)
8
9 cursor.close()
10 conn.close()
创建视图:(不推荐使用)
1 CREATE VIEW cont AS
2 SELECT s.uid 编号,s.uname 用户名,s.psd 密码,s.powid 权限号,power.cate 权限 FROM
3 (SELECT * FROM userinfo INNER JOIN control ON userinfo.uid=control.userid)AS s INNER JOIN power
4 ON power.pid=s.powid
触发器的创建:
1 USE around;
2 delimiter // -- 触发器
3 CREATE TRIGGER t1 BEFORE INSERT ON userinfo FOR EACH ROW -- 在执行userinfo插入一条数据后会被执行
4 BEGIN
5 INSERT INTO student(sname)VALUES(NEW.user);
6 END //
7 delimiter ;
8
9 INSERT INTO userinfo (user,psd)VALUES('abc','admin');
>>>>>>>>>>>>>>>>>>>>>>>>>>>>因为SQL语句默认识别分号为结束标志,所以触发器在创建时必须修改默认标志,创建完成之后再改回原样
存储过程的实现:
MySQL端代码块:
1 delimiter //
2 CREATE PROCEDURE p1 (IN t1 INT,OUT t2 INT )
3 BEGIN
4 SET t2=12121;
5 SELECT * FROM student WHERE sid > t1;
6 INSERT INTO teacher (tname)VALUES('小芬');
7 END //
8 delimiter ;
9
10 SET @v=0;
11 call p1(10,@v);
12 SELECT @v;
pycharm端代码块:
1 import pymysql
2
3 conn=pymysql.connect(host='localhost',user='kevin',password='12121',database='around',charset='utf8')
4 cursor=conn.cursor()
5 cursor.callproc('p1',(10,123))
6 conn.commit()
7 ret=cursor.fetchall()
8 print(ret)
9
10 cursor.execute('select @_p1_0,@_p1_1')
11 r=cursor.fetchall()
12 print(r)
13
14 cursor.close()
15 conn.close()
用存储过程实现把A表的数据id和num求和插入B表:
创建的A表和B表:
1 CREATE TABLE A(id INT PRIMARY KEY auto_increment,
2 number INT);
3
4 CREATE TABLE B(bid INT PRIMARY KEY auto_increment,
5 num INT);
MySQL代码块:
1 delimiter //
2 CREATE PROCEDURE pre()
3 BEGIN
4 DECLARE row_id INT; -- 自定义变量
5 DECLARE row_num INT; -- 自定义变量
6 DECLARE done INT DEFAULT FALSE; -- done 默认为false
7 DECLARE temp int;
8
9 DECLARE my_cursor CURSOR FOR SELECT id,number FROM A; -- 定义一个游标去取值
10 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; -- 如果为true 则游标数据为空
11
12 OPEN my_cursor;
13 xxx: LOOP
14 FETCH my_cursor INTO row_id,row_num; -- 获取的值传给变量
15 IF done THEN
16 LEAVE xxx;
17 END IF;
18 SET temp=row_id+row_num;
19 INSERT INTO B (num)VALUES(temp);
20 END LOOP xxx;
21 END //
22 delimiter ;
>>>>>>>>>>>>>>>>>>>>>>>>>>>
数据库 动态防SQL注入:
1 delimiter //
2 CREATE PROCEDURE proce( IN nid INT)
3 BEGIN
4 SET @nid=nid;
5 PREPARE prod FROM 'select * FROM student WHERE sid > ?';
6 EXECUTE prod USING @nid;
7 DEALLOCATE PREPARE prod;
8 END //
9 delimiter ;
创建索引:
1 -- 索引的创建
2 CREATE INDEX ix_name ON student(sname);
3 -- 索引的删除
4 DROP INDEX ix_name ON student;
5 索引种类(某种格式存储):hash索引: 单值快
6 btree索引: btree索引二叉树 适合范围查询
索引的命中:
1 - like '%xx'
2 select * from tb1 where email like '%cn';
3
4
5 - 使用函数
6 select * from tb1 where reverse(email) = 'wupeiqi';
7
8
9 - or
10 select * from tb1 where nid = 1 or name = 'seven@live.com';
11
12
13 特别的:当or条件中有未建立索引的列才失效,以下会走索引
14 select * from tb1 where nid = 1 or name = 'seven';
15 select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'
16
17
18 - 类型不一致
19 如果列是字符串类型,传入条件是必须用引号引起来,不然...
20 select * from tb1 where email = 999;
21
22
23 - !=
24 select * from tb1 where email != 'alex'
25
26 特别的:如果是主键,则还是会走索引
27 select * from tb1 where nid != 123
28 - >
29 select * from tb1 where email > 'alex'
30
31
32 特别的:如果是主键或索引是整数类型,则还是会走索引
33 select * from tb1 where nid > 123
34 select * from tb1 where num > 123
35
36
37 - order by
38 select name from tb1 order by email desc;
39
40 当根据索引排序时候,选择的映射如果不是索引,则不走索引
41 特别的:如果对主键排序,则还是走索引:
42 select * from tb1 order by nid desc;
43
44 - 组合索引最左前缀
45 如果组合索引为:(name,email)
46 name and email -- 使用索引
47 name -- 使用索引
48 email -- 不使用索引
注意事项:
1 避免使用select * 来直接查询
2.用count(1)或者count(列)代替count(*)
3.表的固定长度字段优先
4.创建表时尽量使用char代替varchar
5.组合索引替代多个单列索引
6.尽量使用短索引
7.使用join连接来代替子查询
8.连表是注意条件类型一致
9.重复或者少值散列的列不适合建立索引 比如性别
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>执行计划:让mysql预估执行操作(一般正确) explain
2 执行效率:慢--->快 all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const 3 id,email 4 5 慢: 6 select * from userinfo3 where name='alex' 7 8 explain select * from userinfo3 where name='alex' 9 type: ALL(全表扫描) 10 select * from userinfo3 limit 1; 11 快: 12 explain select * from userinfo3 where email='alex' 13 type: const(走索引)
分页操作:
1 ******分页*******
2
3 a. select * from userinfo3 limit 20,10;
4 b.
5 - 不让看
6 - 索引表中扫:
7 select * from userinfo3 where id in(select id from userinfo3 limit 200000,10)
8 - 方案:
9 记录当前页最大或最小ID
10 1. 页面只有上一页,下一页
11 # max_id
12 # min_id
13 下一页:
14 select * from userinfo3 where id > max_id limit 10;
15 上一页:
16 select * from userinfo3 where id < min_id order by id desc limit 10;
17 2. 上一页 192 193 [196] 197 198 199 下一页
18
19 select * from userinfo3 where id in (
20 select id from (select id from userinfo3 where id > max_id limit 30) as N order by N.id desc limit 10
21 )