Python就业班——MySQL数据库
1、
修改密码(创建一个changePwd.txt,输入以下sql语句)
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
停掉mysql数据库
管理员方式启动powershell执行
mysqld --defaults-file="D:\ProgramFiles\DataBase\MySQL\mysql-8.0.17\my.ini" --init-file="x:/changePwd.txt" --console
启动mysql数据库
2、DML（添加，查询，修改，删除）、DCL（用户，权限，事务）、DDL（逻辑库，数据表，视图，索引）
3、创建逻辑库
CRATE DATABASE 逻辑库名称;
4、删除逻辑库
DROP DATABASE 逻辑库名称;
5、创建数据表
CREATE TABLE 数据表( 列名1 数据类型 [约束] [COMMENT 注释], .... 列名N 数据类型 [约束] [COMMENT 注释] ) [COMMENT 注释];
6、修改、删除表结构
ALTER TABLE student ADD address VARCHAR ( 200 ) NOT NULL, ADD home_tel CHAR ( 11 ) NOT NULL; ALTER TABLE student MODIFY home_tel VARCHAR ( 20 ) NOT NULL; DESC student; ALTER TABLE student CHANGE address home_addr VARCHAR ( 200 ) NOT NULL; Alter table student drop home_addr;
7、三大范式：原子性、唯一性、关联性
8、字段约束
主键约束：PRIMARY KEY
非空约束：NOT NULL
唯一约束：UNIQUE
外键约束：FOREIGN KEY
CREATE TABLE t_teacher ( id INT UNSIGNED PRIMARY KEY auto_increment, NAME varchar ( 20 ) NOT NULL, tel CHAR ( 11 ) NOT NULL UNIQUE, married boolean NOT NULL DEFAULT FALSE );
CREATE TABLE t_dept ( deptno INT UNSIGNED PRIMARY KEY, dname VARCHAR ( 20 ) NOT NULL UNIQUE, tel CHAR ( 4 ) NOT NULL UNIQUE ); CREATE TABLE t_emp ( empno INT UNSIGNED PRIMARY KEY, ename VARCHAR ( 20 ) NOT NULL, sex ENUM ( "男", "女" ) NOT NULL, deptno INT UNSIGNED NOT NULL, hiredate date NOT NULL, FOREIGN KEY ( deptno ) REFERENCES t_dept ( deptno ) );
9、索引
添加
CREATE TABLE t_message ( id INT UNSIGNED PRIMARY KEY, content VARCHAR ( 255 ) NOT NULL, type Enum ( "公告", "通报", "个人通知" ) NOT NULL, create_time TIMESTAMP NOT NULL, INDEX idx_type ( type ) );
CREATE INDEX 索引名称 ON 表名 (字段); ALTER TABLE 表名称 ADD INDEX [索引名称](字段);
查看
SHOW INDEX FROM 表名;
删除
DROP INDEX 索引名称 ON 表名;
10、分页
SELECT * FROM 表名 LIMIT 起始位置,偏移量; SELECT * FROM t_emp LIMIT 0,10;
11、结果集排序
SELECT * FROM t_emp ORDER BY empno ASC SELECT * FROM 表名 ORDER BY 列名 [ASC|DESC]
SELECT * FROM t_emp ORDER BY sal DESC LIMIT 0,5;
12、去重复数据
SELECT DISTINCT 字段 FROM 表名; SELECT DISTINCT job FROM t_emp
13、条件查询
SELECT * FROM t_emp WHERE deptno = 10 AND (sal + IFNULL(comm, 0)) * 12 >= 15000 AND DATEDIFF(NOW(), hiredate) / 365 >= 20;
IN：包含
SELECT * FROM t_emp WHERE deptno IN(10, 20) AND job != 'SALESMAN' AND hiredate < '1985-01-01';
BETWEEN AND：范围
LIKE：模糊查询
REGEXP：正则表达式
SELECT * FROM t_emp WHERE comm IS NOT NULL AND sal BETWEEN 1000 AND 3000 AND (ename LIKE "%D" OR ename LIKE "_LLE%" OR ename REGEXP "^[\\u4e00-\\u9fa5]{2,10}$");
P.S.：各子句执行顺序——FROM -> WHERE -> SELECT -> ORDER BY -> LIMIT
14、数据统计分析
AVG()、SUM()、MAX()、MIN()、COUNT()、ROUND()四舍五入取整、WITH ROLLUP对分组结果集再次做汇总计算、GROUP_CONCAT()把分组查询中的某个字段拼接成一个字符串
P.S.：各子句执行顺序——FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT
GROUP BY、GROUP BY HAVING
select deptno, count(*) From t_emp group by 1 having count(*) > 5
15、多表连接查询
内连接：多种语法形式
SELECT ... FROM 表1 JOIN 表2 ON 连接条件; SELECT ... FROM 表1 JOIN 表2 WHERE 连接条件; SELECT ... FROM 表1, 表2 WHERE 连接条件;
select e.*, d.dname from t_emp e join t_dept d on e.deptno = d.deptno order by d.dname
-- 查询每个员工的工号、姓名、部门名称、底薪、职位、工资等级 select e.empno, e.ename, d.dname, e.sal, e.job, s.grade from t_emp e, t_dept d, t_salgrade s where e.deptno = d.deptno and s.losal <= e.sal and e.sal <= s.hisal; select e.empno, e.ename, d.dname, e.sal, e.job, s.grade from t_emp e, t_dept d, t_salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal; select e.empno, e.ename, d.dname, e.sal, e.job, s.grade from t_emp e join t_dept d on e.deptno = d.deptno join t_salgrade s on e.sal between s.losal and s.hisal;
FLOOR()：舍位
CEIL()：进位
-- 查询底薪超过公司平均底薪的员工信息 SELECT e1.* FROM t_emp e1 JOIN ( SELECT AVG( sal ) avg FROM t_emp ) e2 ON e1.sal > e2.avg; -- 查询RESEARCH部门的人数、最高底薪、最低底薪、平均底薪、平均工龄 SELECT COUNT(*), MAX( e.sal ), MIN( e.sal ), AVG( e.sal ), FLOOR( AVG( DATEDIFF( now(), e.hiredate ) / 365 ) ) FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno WHERE d.dname = "RESEARCH"; -- 查询每种职业的最高工资，最低工资，平均工资，最高工资等级和最低工资等级 SELECT e.job, MAX( e.sal + IFNULL( e.comm, 0 )), MIN( e.sal + IFNULL( e.comm, 0 )), AVG( e.sal + IFNULL( e.comm, 0 )), MAX( s.grade ), MIN( s.grade ) FROM t_emp e JOIN t_salgrade s ON ( e.sal + IFNULL( e.comm, 0 )) BETWEEN s.losal AND s.hisal GROUP BY e.job; -- 查询每个底薪超过部门平均底薪的员工信息 SELECT e1.* FROM t_emp e1 JOIN ( SELECT deptno, AVG( sal ) AS avg FROM t_emp GROUP BY deptno ) e2 ON e1.deptno = e2.deptno WHERE e1.sal > e2.avg;
UNION将多个查询语句的结果集进行合并
-- 查询每个部门的名称和部门的人数 SELECT d.dname, COUNT( e.deptno ) FROM t_dept d LEFT JOIN t_emp e ON d.deptno = e.deptno GROUP BY d.deptno; -- 查询每个部门的名称和部门的人数，如果没有部门的员工，部门名称用NULL代替 ( SELECT d.dname, COUNT( e.deptno ) FROM t_dept d LEFT JOIN t_emp e ON d.deptno = e.deptno GROUP BY d.deptno ) UNION ( SELECT d.dname, COUNT( * ) FROM t_dept d RIGHT JOIN t_emp e ON d.deptno = e.deptno GROUP BY d.deptno );
16、外连接
-- 查询每名员工的编号，姓名，部门，月薪，工资等级，工龄，上司编号，上司姓名， 上司部门。 SELECT e1.deptno AS "员工编号", e1.ename AS "员工姓名", d1.dname AS "部门", e1.sal + IFNULL( e1.comm, 0 ) AS "月薪", s.grade AS "工资等级", FLOOR( DATEDIFF( NOW(), e1.hiredate )/ 365 ) AS "工龄/年", t.empno AS "上司编号", t.ename AS "上司姓名", t.dname AS "上司部门" FROM t_emp e1 LEFT JOIN t_dept d1 ON e1.deptno = d1.deptno LEFT JOIN t_salgrade s ON e1.sal + IFNULL( e1.comm, 0 ) BETWEEN s.losal AND s.hisal LEFT JOIN ( SELECT e1.empno, e1.ename, d1.dname FROM t_emp e1 JOIN t_dept d1 ON e1.deptno = d1.deptno ) t ON e1.mgr = t.empno;
P.S.：内连接只保留符合条件的记录，所以查询条件写在ON子句和WHERE子句中的效果是相同的。但是外连接里，条件写在WHERE子句里，不符合条件的记录是会被过滤掉的，而不是保留下来。
17、子查询（不推荐使用）
-- 用子查询查找FORD和MARTIN两个人的同事 SELECT e1.* FROM t_emp AS e1 JOIN ( SELECT deptno FROM t_emp WHERE ename IN ("MARTIN", "FORD") ) AS e2 ON e2.deptno = e1.deptno WHERE e1.ename NOT IN ("FORD", "MARTIN"); ORDER BY e1.deptno ASC;
18、INSERT
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2);
INSERT INTO 表名 (字段1, 字段2) VALUES (值1, 值2), (值1, 值2);
INSERT INTO t_dept(deptno, dname, loc) VALUES(60, "后勤部", "北京"), (70, "保安部", "北京");
-- 向技术部添加一条员工记录 INSERT INTO t_emp ( empno, ename, job, mgr, hiredate, sal, comm, deptno ) VALUES ( 8001, "张三", "SALESMAN", 8000, "1990-01-06", 2000, NULL, ( SELECT deptno FROM t_dept WHERE dname = "技术部" ) );
INSERT语句方言
INSERT INTO 表名 SET 字段1=值1, 字段2=值2;
INSERT [INTO] t_emp SET empno = 8002, ename = "JACK", job = "SALESMAN", mgr = 8000, hiredate = "1985-05-03", sal = 2500, comm = NULL, deptno = 50;
IGNORE
IGNORE关键字会让INSERT只插入数据库不存在的记录
INSERT [IGNORE] INTO 表名 (字段1, 字段2) VALUES (值1, 值2);
INSERT IGNORE INTO t_dept ( deptno, dname, loc ) VALUES ( 70, "a", "北京" ), ( 80, "B", "北京" ), ( 80, "C", "北京" );
19、UPDATE [IGNORE] 表名 SET 字段1=值1, 字段2=值2 [WHERE 条件1] [ORDER BY ...] [LIMIT ...];
-- 把每个员工的编号和上司的编号+1，用ORDER BY子句完成 UPDATE t_emp SET empno=empno+1, mgr=mgr+1 ORDER BY empno DESC; -- 把月收入前三名的员工底薪减100元，用LIMIT子句完成 UPDATE t_emp SET sal=sal-100 ORDER BY sal+IFNULL(comm, 0) DESC LIMIT 3; -- 把10部门中工龄超过20年的员工，底薪增加200元 UPDATE t_emp SET sal=sal+200 WHERE FLOOR(DATEDIFF(now(), hiredate)/365) > 20;
UPDATE表连接
（内连接）
UPDATE 表1 JOIN 表2 ON 条件 SET 字段1=值1, 字段2= 值2;
UPDATE 表1, 表2 SET 字段1=值1, 字段2= 值2 WHERE 连接条件;
-- 把ALLEN调往RESEARCH部门，职务调整为ANALYST UPDATE t_emp e JOIN t_dept d SET e.job="ANALYST", e.deptno=d.deptno, d.loc="北京" WHERE e.ename="ALLEN" AND d.dname="RESEARCH"; -- 把底薪低于公司平均底薪的员工，底薪增加150元 UPDATE t_emp e1 JOIN (SELECT AVG(sal) AS avg FROM t_emp) e2 ON e1.sal < e2.avg SET e1.sal=e1.sal+150
（外连接）
UPDATE 表1 [LEFT|RIGHT] JOIN 表2 ON 条件 SET 字段1=值1, 字段2=值2;
-- 把没有部门的员工，或者SALES部门低于2000元底薪的员工，都调往20部门 UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno = d.deptno SET e.deptno = 20 WHERE e.deptno IS NULL OR (d.dname="SALES" AND e.sal < 2000)
20、DELETE
DELETE [IGNORE] FROM 表名 [WHERE 条件1, 条件2] [ORDER BY] [LIMIT];
-- 删除10部门中工龄超过20年的员工记录 DELETE FROM t_emp WHERE deptno=10 AND FLOOR(DATEDIFF(NOW(), hiredate)/365) > 20; -- 删除20部门中工资最高的员工记录 DELETE FROM t_emp WHERE deptno=20 ORDER BY sal+IFNULL(comm, 0) DESC LIMIT 1;
DELETE表连接
DELETE 要删除的表1, 要删除的表2 FROM 表1 JOIN 表2 ON 条件 [WHERE 条件1, 条件2] [ORDER BY] [LIMIT];
-- 删除SALES部门和该部门的全部员工记录 DELETE e, d FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno WHERE d.dname = "SALES"; -- 删除每个低于部门平均底薪的员工记录 DELETE e1 FROM t_emp e1 JOIN ( SELECT deptno, AVG( sal ) AS avg_sal FROM t_emp GROUP BY deptno ) e2 ON e1.deptno = e2.deptno AND e1.sal < e2.avg_sal; -- 删除员工KING和他的直接下属的员工记录 DELETE e1 FROM t_emp e1 JOIN ( SELECT empno FROM t_emp WHERE ename = "KING" ) t ON e1.mgr = t.empno OR e1.empno = t.empno
DELETE 要删除的表1, 要删除的表2 FROM 表1 [LEFT | RIGHT] JOIN 表2 ON 条件;
-- 删除SALES部门的员工，以及没有部门的员工 DELETE e1 FROM t_emp e1 LEFT JOIN t_dept d1 ON d1.deptno = e1.deptno WHERE d1.dname="SALES" OR e1.deptno IS NULL;
快速删除数据表全部记录
DELETE语句是在事务机制下删除记录，删除记录之前，先把将要删除的记录保存到日志文件里，然后再删除记录。
TRUNCATE语句在事务机制之外删除记录，速度远超DELETE语句
TRUNCATE TABLE 表名;
21、数字函数
ABS()绝对值、ROUND()四舍五入、FLOOR()强制舍位到最近的整数、CEIL()强制进位到最近的整数、POWER()幂函数、LOG()对数函数、LN()对数函数
SQRT()开平方、PI()圆周率、SIN()正弦三角函数、COS()余弦三角函数、TAN()正切三角函数、COT()余切三角函数、RADIANS()角度转换弧度、DEGREES()弧度转换角度
22、字符函数
LOWER()转换小写字符、UPPER()转换大写字符、LENGTH()字符数量、CONCAT连接字符串、INSTR()字符串出现的位置、INSERT()插入/替换字符、REPLACE()替换字符
SUBSTR()截取字符串（步长）、SUBSTRING()截取字符串（偏移量）、LPAD()左侧填充符、RPAD()右侧填充符、TRIM()去除首尾空格
23、日期函数
NOW()获得系统日期和时间、CURDATE()获得系统当前日期、CURTIME()获得当前系统时间、DATA_FORMAT(日期, 表达式)、DATE_ADD(日期, INTERVAL 偏移量 时间单位)日期偏移计算、DATEDIFF(日期, 日期)计算两个日期之间相差的天数
24、条件函数
IFNULL(表达式,值)、IF(表达式,值1,值2)
-- SALES部门发放礼品A，其余部门发放礼品B，打印每名员工获得的礼品 SELECT e.empno, e.ename, d.dname, IF(d.dname="SALES", "礼品A", "礼品B") AS "礼品" FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno;
CASE
WHEN 表达式 THEM 值1
WHEN 表达式 THEM 值2
...
ELSE 值N
END
-- SALES部门去P1地点，部门去P2地点，RESEARCH部门去P3地点，查询每名员工的旅游地点 SELECT e.empno, d.dname, CASE WHEN d.dname = "SALES" THEN "P1" WHEN d.dname = "ACCOUNTING" THEN "P2" WHEN d.dname = "RESEARCH" THEN "P3" END AS "旅行地点" FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno
-- SALES部门中工龄超过20年，10% -- SALES部门中工龄不满20年，5% -- ACCOUNTING部门，300元 -- RESEARCH部门里低于部门平均底薪，200元 -- 没有部门的员工，100元 UPDATE t_emp e LEFT JOIN t_dept d ON e.deptno = d.deptno LEFT JOIN ( SELECT deptno, AVG( sal ) avg FROM t_emp GROUP BY deptno ) t ON e.deptno = t.deptno SET e.sal =( CASE WHEN d.dname = "SALES" AND DATEDIFF( NOW(), e.hiredate ) / 365 >= 20 THEN e.sal * 1.1 WHEN d.dname = "SALES" AND DATEDIFF( NOW(), e.hiredate ) / 365 < 20 THEN e.sal * 1.05 WHEN d.dname = "ACCOUNTING" THEN e.sal + 300 WHEN d.dname = "RESEARCH" AND e.sal < t.avg THEN e.sal + 200 WHEN e.deptno IS NULL THEN e.sal + 100 ELSE e.sal END );
25、redo日志和undo日志与事务有关
数据库——>拷贝数据——>undo日志——>记录修改——>redo日志——>同步数据——>数据库
事务机制：RDBMS = SQL语句 + 事务（ACID）；事务是一个或者多个SQL语句组成的整体，要么全部执行成功，要么全部执行失败。
管理事务：默认情况下，MYSQL执行每条SQL语句都会自动开启和提交事务；为了让多条SQL语句纳入到一个事务之下，可以手动管理事务
START TRANSACTION;
SQL语句
[COMMIT|ROLLBACK];
ACID：原子性、一致性、隔离性、持久性
事务四个隔离级别： read uncommitted读取未提交数据【买票】；read committed读取已提交数据【账户退款】；repeatable read重复读取【改价购物】{MYSQL默认级别}；serializable序列化【牺牲并行】。
SET SESSION TRANSACTION ISOLATION LEVER 隔离级别;
26、数据的导出导入
导出SQL文件：mysqldump -uroot -p [no-data] 逻辑库 > 路径
导入SQL文件：USE 数据库名;
SOURCE 要导入的SQL文件;
27、AES加/解密函数
AES_ENCRYPT(原始数据, 密钥字符串)
AES_DECRYPT(加密结果, 密钥字符串)
SELECT HEX(AES_ENCRYPT("你好世界", "ABC123456")); SELECT AES_DECRYPT(UNHEX("E85A104B6142A7375E53C0545CAD48EE"), "ABC123456");
28、MySQL-Connector模块
数据库连接池、预编译SQL、CRUD操作、事务管理、异常处理
#!/usr/bin/env python3 # coding=utf-8 # Version:python3.6.1 # Project:demo_mysql1 # File:example_1.py # Data:2020/9/10 16:04 # Author:LGSP_Harold # import mysql.connector # con = mysql.connector.connect( # host="localhost", port="3306", # user="root", password="root", # database="demo" # ) # con.close() # import pymysql # con = pymysql.connect( # host="localhost", # port=3306, # user="root", # password="root", # database="demo", # charset="utf8" # ) # # con.close() import pymysql config = { 'host': 'localhost', 'port': 3306, 'user': 'root', 'password': 'root', 'database': 'demo', 'charset': 'utf8' } con = pymysql.connect(**config) sql = 'SELECT * FROM t_emp;' cursor = con.cursor() cursor.execute(sql) for i in cursor: print(i[0], i[1], i[2]) con.close() if __name__ == '__main__': pass
#!/usr/bin/env python3 # coding=utf-8 # Version:python3.6.1 # Project:demo_mysql1 # File:example_2.py # Data:2020/9/10 21:29 # Author:LGSP_Harold # import pymysql # config = { # 'host': 'localhost', # 'port': 3306, # 'user': 'root', # 'password': 'root', # 'database': 'vega', # 'charset': 'utf8' # } # con = pymysql.connect(**config) # # sql = 'select * from t_user;' # username = '1 or 1 = 1' # password = '1 or 1 = 1' # sql = 'select count(*) from t_user where username = %s and aes_decrypt(unhex(password), "HelloWord") = %s'; # cursor = con.cursor() # cursor.execute(sql, (username, password,)) # print(cursor.fetchone()[0]) # # con.close() import pymysql config = { "host": "localhost", "port": 3306, "user": "root", "password": "root", "database": "vega", "charset": "utf8" } con = pymysql.connect(**config) cursor = con.cursor() username = "1 or 1 = 1" password = "1 or 1 = 1" sql = "select count(*) from t_user where username = %s and aes_decrypt(unhex(password), 'HelloWord') = %s" cursor.execute(sql, (username, password,)) print(cursor.fetchone()[0]) con.close() if __name__ == '__main__': pass
#!/usr/bin/env python3 # coding=utf-8 # Version:python3.6.1 # Project:demo_mysql1 # File:example_3.py # Data:2020/9/11 14:08 # Author:LGSP_Harold # MySql 8.0 以下版本 # import mysql.connector # Authentication plugin 'caching_sha2_password' is not supported # # try: # con = mysql.connector.connect( # host='localhost', # port=3306, # user='root', # passwd='root', # db='demo', # charset='utf8' # ) # con.start_transaction() # cursor = con.cursor() # sql = 'insert into t_emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (%s, %s, %s, %s, %s, %s, %s, %s);' # cursor.execute(sql, (9633, '赵欧娜', 'SALESMAN', None, '1985-12-02', 2500, None, 10)) # con.commit() # except Exception as e: # if 'con' in dir(): # con.rollback() # print(e) # finally: # if 'con' in dir(): # con.close() import pymysql try: con = pymysql.connect( host="localhost", port=3306, user="root", passwd="root", db="demo", charset="utf8" ) # 修改事务隔离级别 con.start_transaction([事务隔离级别]) # con.begin([事务隔离级别]) con.begin() cursor = con.cursor() sql = 'insert into t_emp (empno, ename, job, mgr, hiredate, sal, comm, deptno) values (%s, %s, %s, %s, %s, %s, %s, %s);' cursor.execute(sql, (9601, '赵娜', 'SALESMAN', None, '1985-12-02', 2500, None, 10)) con.commit() except Exception as e: if 'con' in dir(): # 这个怎么判断 con.rollback() print(e) finally: if 'con' in dir(): con.close() if __name__ == '__main__': pass
数据库连接池的语法
#!/usr/bin/env python3 # coding=utf-8 # Version:python3.6.1 # Project:demo_mysql1 # File:example_4.py # Data:2020/9/11 16:37 # Author:LGSP_Harold # MySql 8.0 以下版本 # import mysql.connector.pooling # # config = { # 'host': 'localhost', # 'port': 3306, # 'user': 'root', # 'passwd': 'root', # 'database': 'demo', # 'charset': 'utf8' # } # try: # pool = mysql.connector.pooling.MySQLConnectionPool(**config, pool_size=10) # con = pool.get_connection() # con.start_transaction() # cursor = con.cursor() # sql = 'update t_emp set sal = sal + %s where deptno = %s;' # cursor.execute(sql, (200, 20)) # con.commit() # except Exception as e: # if 'con' in dir(): # con.rollback() # print(e) import pymysql from DBUtils.PooledDB import PooledDB config = { 'host': 'localhost', 'port': 3306, 'user': 'root', 'passwd': 'root', 'database': 'demo', 'charset': 'utf8' } try: pool = PooledDB(pymysql, 10, **config) conn = pool.connection() cursor = conn.cursor() sql = 'update t_emp set sal = sal + %s where deptno = %s;' cursor.execute(sql, (200, 20)) conn.commit() except Exception as e: if 'conn' in dir(): conn.rollback() print(e) if __name__ == '__main__': pass
#!/usr/bin/env python3 # coding=utf-8 # Version:python3.6.1 # Project:demo_mysql1 # File:example_5.py # Data:2020/9/13 21:58 # Author:LGSP_Harold import pymysql from DBUtils.PooledDB import PooledDB config = { 'host': 'localhost', 'port': 3306, 'user': 'root', 'passwd': 'root', 'db': 'demo', 'charset': 'utf8' } try: pool = PooledDB(pymysql, 10, **config) con = pool.connection() con.begin() cursor = con.cursor() sql = "delete e, d from t_emp e join t_dept d on e.deptno = d.deptno where d.deptno = %s;" # sql = "truncate table t_emp" 在事务外执行 cursor.execute(sql, (20)) con.commit() except Exception as e: if 'con' in dir(): con.rollback() print(e) if __name__ == '__main__': pass
循环执行sql语句
#!/usr/bin/env python3 # coding=utf-8 # Version:python3.6.1 # Project:demo_mysql1 # File:example_6.py # Data:2020/9/13 22:40 # Author:LGSP_Harold import pymysql from DBUtils.PooledDB import PooledDB config = { 'host': 'localhost', 'port': 3306, 'user': 'root', 'passwd': 'root', 'db': 'demo', 'charset': 'utf8' } try: pool = PooledDB(pymysql, 10, **config) con = pool.connection() con.begin() cursor = con.cursor() sql = "insert into t_dept(deptno, dname, loc) VALUES (%s, %s, %s);" data = [[100, 'A部门', '北京'], [200, 'B部门', '北京'], [300, 'C部门', '北京']] cursor.executemany(sql, data) con.commit() except Exception as e: if 'con' in dir(): con.rollback() print(e) if __name__ == '__main__': pass
使用insert语句，把部门平均底薪超过公司平均底薪的部门里的员工信息导入到t_emp_new表里面，然后删掉t_emp表里面的这些员工，最后让t_emp_new表的这些员工隶属于sales部门。
#!/usr/bin/env python3 # coding=utf-8 # Version:python3.6.1 # Project:demo_mysql1 # File:example_7.py # Data:2020/9/13 23:01 # Author:LGSP_Harold # 使用insert语句，把部门平均底薪超过公司平均底薪的部门里的员工信息导入到t_emp_new表里面，然后删掉t_emp表里面的这些员工，最后让t_emp_new表的这些员工隶属于sales部门。 import pymysql from DBUtils.PooledDB import PooledDB
# 数据库连接配置 config = { 'host': 'localhost', 'port': 3306, 'user': 'root', 'passwd': 'root', 'db': 'demo', 'charset': 'utf8' } try:
# 配置连接池 pool = PooledDB(pymysql, 10, **config) con = pool.connection()
# 开始事务 con.begin()
# 创建游标 cursor = con.cursor()
# 先删除已有的t_emp_new表 sql = 'drop table t_emp_new;' cursor.execute(sql) # sql = 'create table t_emp_new as (select * from t_emp);' 表结构和数据一起复制
# 复制t_emp表结构来创建t_emp_new表 sql = 'create table t_emp_new LIKE t_emp;' # 复制表结构 cursor.execute(sql)
# 获取公司平均底薪 sql = 'select AVG(sal) as avg from t_emp;' cursor.execute(sql) temp = cursor.fetchone() avg = temp[0] # 公司的平均底薪
# 获取部门平均底薪高于公司平均底薪的部门 sql = 'select deptno from t_emp group by deptno having AVG(sal) >= %s;' cursor.execute(sql, avg) temp = cursor.fetchall() print(temp)
# 将部门平均底薪高于公司平均底薪的部门插入新表 sql = 'insert into t_emp_new select * from t_emp where deptno in (' for index in range(len(temp)): one = temp[index][0] if index < len(temp)-1: sql += str(one)+',' else: sql += str(one) sql += ');' print(sql) cursor.execute(sql)
# 删除旧表已移到新表的员工 sql = 'delete e from t_emp e where deptno in (' for index in range(len(temp)): one = temp[index][0] if index < len(temp)-1: sql += str(one)+',' else: sql += str(one) sql += ');' print(sql) cursor.execute(sql)
# 查询SALES的部门编号 sql = 'select deptno from t_dept where dname=%s;' cursor.execute(sql, ['SALES']) deptno = cursor.fetchone()[0]
# 更新新表员工的部门编号 sql = 'update t_emp_new set deptno=%s;' cursor.execute(sql, [deptno])
# 提交 con.commit() except Exception as e: if 'con' in dir():
# 回滚 con.rollback()
# 异常输出 print(e) if __name__ == '__main__': pass
编写一个insert语句向部门表插入两条记录，每条记录都在部门原有最大主键的基础上+10
#!/usr/bin/env python3 # coding=utf-8 # Version:python3.6.1 # Project:demo_mysql1 # File:example_8.py # Data:2020/9/16 2:07 # Author:LGSP_Harold # 编写一个insert语句向部门表插入两条记录，每条记录都在部门原有最大主键的基础上+10 import pymysql from DBUtils.PooledDB import PooledDB conf = { 'host': 'localhost', 'port': 3306, 'user': 'root', 'passwd': 'root', 'db': 'demo', 'charset': 'utf8' } global conn try: pool = PooledDB(pymysql, 10, **conf) conn = pool.connection() conn.begin() cursor = conn.cursor() # for i in range(2): # sql = 'select MAX(deptno) from t_dept' # cursor.execute(sql) # deptno = cursor.fetchone()[0] # print(deptno) # sql = 'insert into t_dept (deptno, dname, loc) VALUES (%s, %s, %s);' # cursor.execute(sql, [deptno+10, 'A部门', 'shanghai']) sql = 'insert into t_dept (select MAX(deptno)+10, %s, %s from t_dept union ' \ 'select MAX(deptno)+20, %s, %s from t_dept)' cursor.execute(sql, ['A部门', '北京', 'B部门', '上海', ]) conn.commit() except Exception as e: if 'conn' in dir(): conn.rollback() print(e) if __name__ == '__main__': pass
P.S.1：Pymysql及连接池：https://blog.csdn.net/weixin_40976261/article/details/89057633
P.S.2：pymysql官网文档链接：https://pymysql.readthedocs.io/en/latest/
略懂，略懂....
浙公网安备 33010602011771号