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                     )