INSERT INTO t_dept(deptno, dname, loc)
VALUES(50, "技術(shù)部", "北京");

INSERT INTO t_dept(deptno, dname, loc)
VALUES(60, "后勤部", "北京"), (70, "保安部", "北京");

INSERT INTO t_emp
(empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES(8081, "劉娜", "SALESMAN", 8000, "1988-12-21", 2000, NULL, 
(SELECT deptno FROM t_dept  WHERE dname = "技術(shù)部"));

INSERT INTO t_emp 
SET empno=8002, ename="Jake", job="SALESMAN", mgr=8000,
hiredate="1985-12-21",sal=2500,comm=NULL, deptno=50;

INSERT INTO t_dept(deptno, dname, loc)
VALUES(60, "A", "北京"), (80, "B", "上海");
-- 1062 - Duplicate entry '60' for key 'PRIMARY', Time: 0.012000s

INSERT IGNORE INTO t_dept(deptno, dname, loc)
VALUES(60, "A", "北京"), (80, "B", "上海");
-- Affected rows: 1, Time: 0.036000s

UPDATE t_emp 
SET empno=empno+1, mgr=mgr+1 
ORDER BY empno DESC;

UPDATE t_emp 
SET sal = sal - 100
ORDER BY sal + IFNULL(comm,0) DESC
LIMIT 3;

UPDATE t_emp 
SET sal = sal + 200
WHERE deptno = 10 AND DATEDIFF(NOW(),hiredate) / 365 > 20;

UPDATE t_emp
SET deptno = (SELECT deptno FROM t_dept WHERE dname = 'RESEARCH'),
job = 'ANALYST'
WHERE ename = 'ALLEN';

UPDATE t_emp e 
JOIN t_dept d
SET e.deptno  = d.deptno, e.job = 'ANALYST', d.loc='北京'
WHERE e.ename = 'ALLEN' AND d.dname = 'RESEARCH';

UPDATE t_emp e1 
JOIN (SELECT AVG(sal) avg FROM t_emp) s
ON e1.sal < s.avg
SET e1.sal = e1.sal + 150;

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);

DELETE FROM t_emp
WHERE deptno = 10 AND DATEDIFF(NOW(),hiredate) / 365 >= 20;

DELETE FROM t_emp 
WHERE deptno = 20 
ORDER BY sal + IFNULL(comm,0) DESC 
LIMIT 1;

DELETE e, d FROM t_emp e JOIN t_dept d 
ON e.deptno = d.deptno
WHERE d.dname = 'SALES';

DELETE e FROM t_emp e
JOIN (SELECT deptno, AVG(sal) sal FROM t_emp GROUP BY deptno) g
ON e.deptno = g.deptno
WHERE e.sal < g.sal;

-- 表連接寫法1
DELETE e1, e2 FROM t_emp e1 JOIN t_emp e2
ON e1.ename = 'KING' AND e1.empno = e2.mgr;

--表連接寫法2
DELETE e1 FROM t_emp e1
JOIN (SELECT empno FROM t_emp WHERE ename = 'KING') e2
ON e1.mgr = e2.empno OR e1.ename = 'KING';

DELETE e FROM t_emp e 
LEFT JOIN t_dept d
ON e.deptno = d.deptno
WHERE d.dname = 'SALES' OR e.deptno IS NULL;

TRUNCATE TABLE t_emp;

SELECT ABS(-100); # 100
SELECT ROUND(4.6288 * 100) / 100; # 4.6300
SELECT FLOOR(9.9); # 9
SELECT CEIL(3.2); # 4
SELECT POWER(2, 3); # 8
SELECT LOG(7, 3); # 0.5645750340535797
SELECT LN(10); # 2.302585092994046

SELECT SQRT(9); # 3
SELECT PI(); # 3.141593
SELECT SIN(RADIANS(30)); # 0.49999999999999994
SELECT COS(RADIANS(45)); # 0.7071067811865476
SELECT TAN(RADIANS(35)); # 0.7002075382097097
SELECT COT(RADIANS(45)); # 1.0000000000000002
SELECT DEGREES(1); # 57.29577951308232

SELECT NOW(), CURDATE(), CURTIME();
2020-01-01 10:59:43  2020-01-01     10:59:43

-- 查詢明天你的生日是星期幾。
SELECT DATE_FORMAT('2020-04-05', '%w - %W') #0 - Sunday

-- 查詢1981年上半年入職的員工有多少人
SELECT COUNT(*) FROM t_emp
WHERE DATE_FORMAT(hiredate, "%Y") = 1981
AND DATE_FORMAT(hiredate, "%m") <=6; # 5

SELECT 
NOW(),
DATE_ADD(NOW(),INTERVAL 15 DAY) d1,
DATE_ADD(NOW(),INTERVAL -300 MINUTE) d2,
DATE_ADD(DATE_ADD(NOW(), INTERVAL -2 MONTH), INTERVAL -3 DAY) d3;
# 2020-01-01 11:31:28   2020-01-16 11:31:28 2020-01-01 06:31:28 2019-10-29 11:31:28

SELECT DATEDIFF(NOW(),DATE_ADD(NOW(),INTERVAL -1 MONTH)); # 31

SELECT LOWER(ename), UPPER(ename), LENGTH(ename), 
CONCAT(sal, '$'), INSTR(ename,'A') FROM t_emp;
-- ...
-- allen    ALLEN   5   1600.00$    1

SELECT INSERT('你好', 1, 0, '先生'); # 先生你好
SELECT INSERT('你好', 1, 1, '先生'); # 先生好
SELECT REPLACE('你好先生', '先生', '女士'); # 你好女士

SELECT SUBSTR('你還世界',3, 4); # 世界
SELECT SUBSTRING('你好世界', 3, 2); # 世界
SELECT LPAD(SUBSTRING('12345678901',8,4),11,'*'); # *******8901
SELECT RPAD(SUBSTRING('李曉娜', 1, 1),LENGTH('李曉娜') / 3,'*'); # 李**
SELECT TRIM('    Hello World!  ') #Hello World!

SELECT e.ename, d.dname,  IF(d.dname = 'SALES', '禮品A', '禮品B')
FROM t_emp e 
JOIN t_dept d 
ON e.deptno = d.deptno;

SELECT 
e.ename, d.dname,
CASE d.dname
    WHEN 'SALES' THEN 'P1'
    WHEN 'ACCOUNTING' THEN 'P2'
    WHEN 'RESEARCH' THEN 'P3'
END AS place
FROM t_emp e
JOIN t_dept d
ON e.deptno = d.deptno

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) g
ON e.deptno = g.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 < g.avg THEN e.sal + 200
        WHEN e.deptno IS NULL THEN e.sal + 100
        ELSE e.sal
    END
);

START TRANSACTION;
SQL語句
[COMMIT | ROLLBACK];

START TRANSACTION;
DELETE FROM t_emp;
DELETE FROM t_dept;
SELECT COUNT(*) FROM t_emp; # 0
SELECT COUNT(*) FROM t_dept; # 0
COMMIT;
-- ROLLBACK;

-- 查詢面板1 
START TRANSACTION;
UPDATE t_emp SET sal = 1;

-- 查詢面板2
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 設(shè)置當(dāng)前會(huì)話事務(wù)隔離級(jí)別：允許讀取其他事務(wù)未提交數(shù)據(jù)福侈。
START TRANSACTION;
SELECT empno, ename, sal FROM t_emp;
COMMIT;
-- 7369 SMITH   1.00
-- 7499 ALLEN   1.00
-- 7521 WARD    1.00

-- 查詢面板1
START TRANSACTION;
UPDATE t_emp SET sal = 1;

-- 查詢面板2
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 設(shè)置當(dāng)前會(huì)話事務(wù)隔離級(jí)別：允許讀取其他事務(wù)已提交數(shù)據(jù)
START TRANSACTION;
SELECT empno, ename, sal FROM t_emp;
COMMIT;
-- 7369 SMITH   800.00
-- 7499 ALLEN   1600.00
-- 7521 WARD    1250.00

-- 查詢面板1
START TRANSACTION;
UPDATE t_emp SET sal = 1;
COMMIT;

--查詢面板2
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT empno, ename, sal FROM t_emp;
COMMIT;

-- 查詢面板1
START TRANSACTION;
UPDATE t_emp SET sal = 1;
COMMIT;

--查詢面板2
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT empno, ename, sal FROM t_emp;
COMMIT;

?  ~ mysqldump -uroot -p demo > /Users/nimengwei/downloads/demo.sql 
Enter password: 
?  ~ 

?  ~ cd /Users/nimengwei/Downloads/sql_file 
?  sql_file mysql -uroot -p
Enter password: 
mysql> USE demo;
Database changed
mysql> SOURCE demo.sql;
Query OK, 0 rows affected (0.00 sec)
mysql> 

3.4 綜合案例：新聞管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)

1. 新聞管理系統(tǒng)成員

   
   
   image.png

2. 新聞?dòng)心男傩?/p>
   

   image.png
3. 數(shù)據(jù)庫ER圖設(shè)計(jì)
   
   image.png
4. 數(shù)據(jù)加密
   (1) 數(shù)據(jù)加密
   分類：對(duì)稱加密、非對(duì)稱加密
   (2) 對(duì)稱加密
   加密和解密使用同一個(gè)秘鑰惕它。
   分類：DES加密(已淘汰)怕午、AES加密
   (3) 非對(duì)稱加密
   加密和解密使用不同的秘鑰。
   公鑰可以解密私鑰加密的數(shù)據(jù)怠缸，私鑰可以解密公鑰加密的數(shù)據(jù)诗轻。
   分類：RSA加密、DSA加密揭北、ECC加密
5. AES加密函數(shù)
   MySQL提供了AES加密和解密的函數(shù)扳炬。
   AES_ENCRYPT(原始數(shù)據(jù), 秘鑰字符串);
AES解密要使用與加密相同的秘鑰，才能解密出原始數(shù)據(jù)搔体。
   AES_DECRYPT(加密結(jié)果, 秘鑰字符串);

SELECT AES_ENCRYPT('你好世界','ABC123456');
# èZ?KaB§7^SàT\-H? 亂碼了恨樟，可轉(zhuǎn)化為16進(jìn)制解決亂碼
SELECT HEX(AES_ENCRYPT('你好世界','ABC123456')); 
# hex為2二進(jìn)制轉(zhuǎn)16進(jìn)制
# E85A104B6142A7375E53C0545CAD48EE
SELECT AES_DECRYPT(UNHEX('E85A104B6142A7375E53C0545CAD48EE'), 'ABC123456') 
# unhex為16進(jìn)制轉(zhuǎn)2進(jìn)制
# 你好世界

6. 數(shù)據(jù)庫實(shí)現(xiàn)

(1) 創(chuàng)建數(shù)據(jù)庫

# 創(chuàng)建vega數(shù)據(jù)庫
CREATE DATABASE vega;
# 切換數(shù)據(jù)庫
USE vega;

(2) t_type文章類型表
數(shù)據(jù)表中數(shù)據(jù)量不大時(shí)，可以不添加索引疚俱。例如：t_type文章類型表劝术，t_role角色類型表。

# 創(chuàng)建文章類型表
CREATE TABLE t_type(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    type VARCHAR(20) NOT NULL UNIQUE
);

# 文章類型表插入數(shù)據(jù)
INSERT INTO t_type(type) 
VALUES("要聞"), ("體育"), ("科技"), ("娛樂"), ("歷史");

(3) t_role角色類型表

# 創(chuàng)建角色類型表
CREATE TABLE t_role(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    role VARCHAR(20) NOT NULL UNIQUE
);

# 角色類型表插入數(shù)據(jù)
INSERT INTO t_role(role)
VALUES("管理員"), ("新聞編輯");

(4) t_user用戶表
數(shù)據(jù)表中數(shù)據(jù)量很大呆奕，并且經(jīng)常使用某些字段查找數(shù)據(jù)养晋，則應(yīng)該對(duì)這些字段創(chuàng)建索引。例如：t_user表中的username梁钾。

# 創(chuàng)建用戶表
CREATE TABLE t_user(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20) NOT NULL UNIQUE,
    password VARCHAR(500) NOT NULL,
    email VARCHAR(100) NOT NULL,
    role_id INT UNSIGNED NOT NULL,
    INDEX(username)
);

# 用戶表插入數(shù)據(jù)
INSERT IGNORE INTO t_user(username, password, email, role_id) VALUES
('admin', HEX(AES_ENCRYPT('123456','HelloWord')), 'admin@163.com', 1),
('scott', HEX(AES_ENCRYPT('123456','HelloWord')), 'scott@163.com', 2);

(5) t_news新聞表
新聞內(nèi)容數(shù)據(jù)的特點(diǎn)為：數(shù)據(jù)量大绳泉、安全級(jí)別低，適合存儲(chǔ)在mongodb數(shù)據(jù)庫中姆泻。通過content_id查找零酪。

# 創(chuàng)建新聞表
CREATE TABLE t_news(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(40) NOT NULL,
    editor_id INT UNSIGNED NOT NULL,
    type_id INT UNSIGNED NOT NULL,
    content_id VARCHAR(24) NOT NULL,
    is_top TINYINT UNSIGNED NOT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    state ENUM('草稿', '待審批', '已審批', '隱藏') NOT NULL,
    INDEX(editor_id),
    INDEX(type_id),
    INDEX(state),
    INDEX(is_top),
    INDEX(create_time)
);

3.5 總結(jié)

1. 技能清單
   學(xué)習(xí)了事務(wù)機(jī)制的原理和特性冒嫡。
   掌握了事務(wù)的管理。
   掌握了數(shù)據(jù)導(dǎo)出與導(dǎo)入的操作四苇。
   完成了新聞管理系統(tǒng)數(shù)據(jù)表的設(shè)計(jì)孝凌。

4. MySQL與Python交互

4.1 介紹

1. MySQL Connector模塊
   數(shù)據(jù)庫連接池、預(yù)編譯SQL月腋、CRUD操作蟀架、事務(wù)管理、異常處理
2. 新聞管理系統(tǒng)
   新聞管理榆骚、用戶管理辜窑、系統(tǒng)登錄、數(shù)據(jù)分頁等

4.2 MySQL與Python的交互

1. MySQL Connector模塊
   MySQL Connector是MySQL官方的驅(qū)動(dòng)模塊寨躁，兼容性特別好穆碎。

?  ~ pip3 install mysql-connector
...
Successfully installed mysql-connector-2.2.9

2. 創(chuàng)建連接

(1) 語法一

import mysql.connector
con = mysql.connector.connect(
    host="localhost",
    port=3306,
    user="root",
    password="299***",
    database="demo"
)

con.close()

(2) 語法二

import mysql.connector

config = {
    "host":"localhost",
    "port": 3306,
    "user": "root",
    "password": "299***",
    "database": "demo"
}

con = mysql.connector.connect(**config)

con.close()

3. 游標(biāo)(Cursor)
   MySQL Connector里的游標(biāo)用來執(zhí)行SQL語句，查詢的結(jié)果集也會(huì)保存在游標(biāo)中职恳。

cursor = con.cursor()
cursor.execute(sql語句)

import mysql.connector

con = mysql.connector.connect(
    host="localhost",
    port=3306,
    user="root",
    password="299***",
    database="demo"
)

cursor = con.cursor()
sql = "SELECT empno, ename, hiredate FROM t_emp;"
cursor.execute(sql)
for item in cursor:
    print(item[0], item[1], item[2])
# 7369 SMITH 1980-12-17
# 7499 ALLEN 1981-02-20
# ...

con.close()

4. SQL注入攻擊

import mysql.connector

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "299***",
    "database": "vega"
}

# 連接數(shù)據(jù)庫
con = mysql.connector.connect(**config)

# 獲取游標(biāo)
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])  # 2
# 關(guān)閉數(shù)據(jù)庫連接
con.close()

注意：密碼必須是字符串格式所禀。

5. SQL注入攻擊的危害
   由于SQL語句是解釋型語言，所以在拼接SQL語句的時(shí)候放钦，容易被注入惡意的SQL語句色徘。

id = "1 OR 1=1"
sql = "DELETE FROM t_news WHERE id={}".format(id)

6. SQL預(yù)編譯機(jī)制
   (1) SQL預(yù)編譯機(jī)制
   預(yù)編譯SQL就是數(shù)據(jù)庫提前把SQL語句編譯成二進(jìn)制，這樣反復(fù)執(zhí)行同一條SQL語句的效率就會(huì)提升操禀。
   (2) 抵御注入攻擊
   SQL語句編譯的過程中褂策，關(guān)鍵字已經(jīng)被解析過了，所以向編譯后的SQL語句傳入?yún)?shù)颓屑，參數(shù)都被當(dāng)做字符串處理斤寂，數(shù)據(jù)庫不會(huì)解析其中注入的SQL語句。
   注釋：預(yù)編譯機(jī)制既可以提升SQL語句的執(zhí)行效率揪惦，又可以抵御注入攻擊遍搞。
7. 抵御注入攻擊

import mysql.connector

config = {
    "host": "localhost",
    "port": 3306,
    "user": "root",
    "password": "299***",
    "database": "vega"
}

# 連接數(shù)據(jù)庫
con = mysql.connector.connect(**config)

# 獲取游標(biāo)
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])  # 0
# 關(guān)閉數(shù)據(jù)庫連接
con.close()

注意：execute的第二個(gè)參數(shù)為元組或數(shù)組，例如: [a]器腋、(a,)溪猿。

8. 事務(wù)控制

con.start_transaction([事務(wù)隔離機(jī)制])
con.commit()
con.rollback()

9. 異常處理

(1) 語法

try:
    con = mysql.connector.connect(...)
    [ con.start_transaction() ]
    ...
    con.commit()
except Exception as e:
    [ con.rollback() ]
    print(e)
finally:
    if "con" in dir():
        con.close()

(2) 示例

import mysql.connector

try:
    con = mysql.connector.connect(
        host='localhost',
        port=3306,
        user='root',
        password='299***',
        database='demo'
    )
    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, (9600, '趙娜', 'SALESMAN', None,'1998-09-08', 2000, None, 10))
    con.commit()
except Exception as e:
    if 'con' in dir():
        con.rollback()
    print(e)
finally:
    if 'con' in dir():
        con.close()

10. 數(shù)據(jù)庫連接池技術(shù)
    數(shù)據(jù)庫連接是一種關(guān)鍵的、有限的纫塌、昂貴的資源诊县，在并發(fā)執(zhí)行的應(yīng)用程序中體現(xiàn)的尤為突出。TCP連接需要三次握手措左，四次揮手依痊，然后數(shù)據(jù)庫還要驗(yàn)證用戶信息。
    數(shù)據(jù)庫連接池(ConnectionPool)預(yù)先創(chuàng)建出一些數(shù)據(jù)庫連接媳荒，然后緩存起來抗悍，避免了程序語言反復(fù)創(chuàng)建和銷毀連接的昂貴代價(jià)。

import mysql.connector.pooling

config = {
    'host': 'localhost',
    'port': '3306',
    'user': 'root',
    'password': '299***',
    'database': 'demo'
}

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)
finally:
    if "con" in dir():
        con.close()

注意：使用數(shù)據(jù)庫連接池(ConnectionPool)也需要關(guān)閉(close)钳枕。

11. 刪除數(shù)據(jù)

(1) DELETE語句刪除記錄

import mysql.connector.pooling

config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '299***',
    'database': 'demo'
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    sql = "DELETE e, d FROM t_emp e JOIN t_dept d ON e.deptno=d.deptno " \
          "WHERE d.deptno=20;"
    cursor.execute(sql)
    con.commit()
except Exception as e:
    if 'con' in dir():
        con.rollback()
    print(e)
finally:
    if "con" in dir():
        con.close()

(2) TRUNCATE語句快速刪除記錄
TRUNCATE語句是在事務(wù)機(jī)制之外刪除記錄缴渊。

import mysql.connector.pooling

config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '299***',
    'database': 'demo'
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    cursor = con.cursor()
    sql = "TRUNCATE TABLE t_emp"
    cursor.execute(sql)
except Exception as e:
    print(e)

12. 循環(huán)執(zhí)行SQL語句

(1) for循環(huán)執(zhí)行execute()函數(shù)

import mysql.connector.pooling

config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '299***',
    'database': 'demo'
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    sql = "INSERT INTO t_dept(deptno, dname, loc) VALUES (%s, %s, %s);"
    data = [[50, '部門1', '北京'], [60, '部門2', '上海']]
    for item in data:
        cursor.execute(sql, item)
    con.commit()
except Exception as e:
    if 'con' in dir():
        con.rollback()
    print(e)
finally:
    if "con" in dir():
        con.close()

(2) 游標(biāo)(cursor)對(duì)象中的executemany()函數(shù)可以反復(fù)執(zhí)行一條SQL語句。

import mysql.connector.pooling

config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '299***',
    'database': 'demo'
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    sql = "INSERT INTO t_dept(deptno, dname, loc) VALUES (%s, %s, %s);"
    data = [[70, '部門1', '北京'], [80, '部門2', '上海']]
    cursor.executemany(sql, data)
    con.commit()
except Exception as e:
    if 'con' in dir():
        con.rollback()
    print(e)
finally:
    if "con" in dir():
        con.close()

13. 使用INSERT語句鱼炒，把部門平均底薪超過公司平均底薪這樣的部門里的員工信息導(dǎo)入到t_emp_new表里衔沼，并且讓這些員工隸屬于sales部門。
    編程語言中昔瞧，數(shù)據(jù)庫查詢結(jié)果可以通過變量保存指蚁，比直接通過SQL語句操作數(shù)據(jù)庫的寫法簡(jiǎn)單。

import mysql.connector.pooling

config = {
    'host': 'localhost',
    'port': '3306',
    'user': 'root',
    'password': '299***',
    'database': 'demo'
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    # 創(chuàng)建數(shù)據(jù)表
    sql = "CREATE TABLE t_emp_new LIKE t_emp;"
    cursor.execute(sql)
    # 公司平均底薪
    sql = "SELECT AVG(sal) as avg FROM t_emp;"
    cursor.execute(sql)
    avg = cursor.fetchone()[0]
    # print(avg) # 2073.214286
    # 部門底薪超過公司平均底薪的部門
    sql = "SELECT deptno FROM t_emp GROUP BY deptno HAVING AVG(sal) >= %s;"
    cursor.execute(sql, (avg,))
    deptnos = cursor.fetchall()
    # print(deptnos) # [(10,), (20,)]
    # 符合條件的部門員工導(dǎo)入到新表
    deptnos_str = '('
    for index in range(len(deptnos)):
        if index == len(deptnos) - 1:
            deptnos_str += (str(deptnos[index][0]))
        else:
            deptnos_str += (str(deptnos[index][0]) + ',')
    deptnos_str += ')'
    sql = "INSERT INTO t_emp_new (SELECT * FROM t_emp WHERE deptno IN {});".format(deptnos_str)
    cursor.execute(sql)
    sql = "DELETE FROM t_emp WHERE deptno IN {};".format(deptnos_str)
    cursor.execute(sql)
    # 將t_emp_new表的員工部門修改為sales部門
    sql = "SELECT deptno FROM t_dept WHERE dname=%s;"
    cursor.execute(sql, ['SALES'])
    new_emptno = cursor.fetchone()[0]
    print(new_emptno)
    sql = "UPDATE t_emp_new SET deptno=%s"
    cursor.execute(sql, [new_emptno])
    con.commit()
except Exception as e:
    if 'con' in dir():
        con.rollback()
    print(e)
finally:
    if "con" in dir():
        con.close()

注意：① 'CREATE TABLE t_emp_new LIKE t_emp;'表示使用t_emp表的結(jié)構(gòu)創(chuàng)建t_emp_new表自晰。② CREATE TABLE語句為DDL語句凝化，不受事務(wù)控制。③ 由于不知道符合條件的部門個(gè)數(shù)酬荞，因此該案例只能使用字符串拼接搓劫，不能使用字符串格式化。 ④ INSERT INTO t_emp_new 查詢出的表結(jié)果;語法可以將查詢出的數(shù)據(jù)表集合插入到t_emp_new表中枪向。

14. 編寫一個(gè)INSERT語句秘蛔，向部門表插入兩條記錄，每條記錄都在部門原有最大主鍵值的基礎(chǔ)上加10辨液。

(1) 不支持邊插入，邊查詢本表數(shù)據(jù)
例如：INSERT INTO 表名(字段1,字段2, ...) VALUES(值1,值2,...);

INSERT INTO t_dept(deptno, dname, loc) 
VALUES((SELECT MAX(deotno) FROM t_dept) + 10, "A部門", "南京");
# 報(bào)錯(cuò) Unknown column 'deotno' in 'field list', Time: 0.014000s

(2) 可以先查詢出數(shù)據(jù)表燎悍，再寫入
例如：INSERT INTO 表名 數(shù)據(jù)集;

INSERT INTO t_dept (SELECT MAX(deptno) + 10, "A部門", "南京" FROM t_dept);

INSERT INTO t_dept (SELECT MAX(deptno) + 10, "A部門", "南京" FROM t_dept) UNION 
(SELECT MAX(deptno) + 20, "B部門", "上海" FROM t_dept);

(3) Python語言實(shí)現(xiàn)

import mysql.connector.pooling

config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '299***',
    'database': 'demo'
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **config,
        pool_size=10
    )
    con = pool.get_connection()
    con.start_transaction()
    cursor = con.cursor()
    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部門', '上海'))
    con.commit()
except Exception as e:
    if 'con' in dir():
        con.rollback()
    print(e)
finally:
    if "con" in dir():
        con.close()

4.3 總結(jié)

1. 技能清單
   掌握了數(shù)據(jù)庫連接池技術(shù)
   掌握了Python程序的CRUD操作
   掌握了用預(yù)編譯SQL抵御SQL注入攻擊

5. 開發(fā)新聞管理系統(tǒng)

1. 第三方模塊
   (1) 升級(jí)pip
? ~ python3 -m pip install --upgrade pip
   (2) 安裝colorama
? ~ pip3 install colorama
   向控制臺(tái)輸出彩色文字

from colorama import Back, Fore, Style

print(Fore.LIGHTBLUE_EX, 'HelloWord')  # 設(shè)置字體顏色
print('HelloWord')
print(Back.LIGHTRED_EX, 'HelloWord')  # 設(shè)置背景色
print('HelloWord')
print(Style.RESET_ALL, 'HelloWord')  # 重置樣式

2. 項(xiàng)目介紹

(1) 新建項(xiàng)目

(2) 項(xiàng)目結(jié)構(gòu)
vega - db奏路、service斜脂、app.py
(3) 管理員身份操作流程圖

3. 代碼地址
   https://github.com/nmwei/database-vega
4. 代碼示例(部分)
   (1) mysql_db提供數(shù)據(jù)庫連接池

import mysql.connector.pooling

__config = {
    'host': 'localhost',
    'port': 3306,
    'user': 'root',
    'password': '299792',
    'database': 'vega'
}

try:
    pool = mysql.connector.pooling.MySQLConnectionPool(
        **__config,
        pool_size=10
    )
except Exception as e:
    print(e)

(2) dao文件用來操作數(shù)據(jù)庫，以user_dao.py為例片任。

from db.mysql_db import pool

class UserDao:
    # 驗(yàn)證用戶登錄
    def login(self, username, password):
        try:
            con = pool.get_connection()
            cursor = con.cursor()
            sql = "SELECT COUNT(*) FROM t_user WHERE username=%s AND " \
                "AES_DECRYPT(UNHEX(password), 'HelloWord')=%s;"
            cursor.execute(sql, (username, password))
            count = cursor.fetchone()[0]
            # Python中的三元運(yùn)算符
            return True if count == 1 else False
        except Exception as e:
            print(e)
        finally:
            if "con" in dir():
                con.close()
...

(3) service文件用來調(diào)用dao函數(shù)氛濒，并處理業(yè)務(wù)邏輯涝动。以user_service.py為例醋粟。

from db.user_dao import UserDao


class UserService:
    __user_dao = UserDao()

    # 驗(yàn)證用戶登錄
    def login(self, username, password):
        return self.__user_dao.login(username, password)
...

(4) app.py程序入口

from colorama import Fore, Style
from getpass import getpass
from service.user_service import UserService
from service.news_service import NewsService
from service.role_service import RoleService
import os
import sys
import time

__user_service = UserService()
__news_service = NewsService()
__role_service = RoleService()

while True:  # 輪詢操作
    os.system('clear')  # 如果是window系統(tǒng)鼻吮，使用cls
    print(Fore.LIGHTBLUE_EX, '\n\t=================')
    print(Fore.LIGHTBLUE_EX, '\n\t歡迎使用新聞管理系統(tǒng)')
    print(Fore.LIGHTBLUE_EX, '\n\t=================')
    print(Fore.LIGHTGREEN_EX, '\n\t1. 登錄系統(tǒng)')
    print(Fore.LIGHTGREEN_EX, '\n\t2. 退出系統(tǒng)')
    print(Style.RESET_ALL)  # 重置樣式
    opt = input("\n\t請(qǐng)輸入操作編號(hào): ")
    if opt == '1':
        username = input('\n\t用戶名: ')
        password = getpass('\n\t密碼: ')  # 使輸密碼過程不可見
        result = __user_service.login(username, password)
        # 登錄成功
...

注意：①Python中的三元運(yùn)算符：True if count == 1 else False违柏。②數(shù)據(jù)庫中管理員用戶名密碼為：admin 123456畜伐。