之前為了修改數(shù)據(jù)方便測試寫了一個(gè)腳本赡模，現(xiàn)在想系統(tǒng)整理一下相關(guān)知識
計(jì)劃：
使用docker搭建一個(gè)mysql數(shù)據(jù)庫田炭，系統(tǒng)整理mysql基礎(chǔ)，使用pymsql練習(xí)小Demo漓柑。
Demo需求分析
背景：測試人員也是一種測試資源教硫，當(dāng)測試部門獨(dú)立時(shí)厕怜，測試人員作為一種測試資源在項(xiàng)目中合理配比萨咕。
需求 ：一個(gè)員工最多歸屬兩個(gè)項(xiàng)目
設(shè)計(jì)：分析需求畫ER圖：（矩形是實(shí)體识虚，圓形是屬性宜雀，菱形是關(guān)系）
- 建表
staff
| 字段 | 類型 | 注解 |
|---|---|---|
| staff_id | TINYINT | PRIMARY KEY |
| staff_name | CHAR | |
| staff_age | TINYINT | |
| staff_sex | TINYINT | 1:男,2:女 |
| staff_dept | TINYINT | |
| staff_email | VARCHAR |
- 建表sql
use HE;
CREATE TABLE IF NOT EXISTS `staff`(
`staff_id` TINYINT AUTO_INCREMENT,
`staff_name` CHAR(40) NOT NULL,
`staff_sex` TINYINT NOT NULL,
`staff_dept` VARCHAR(40) NOT NULL,
`staff_age` TINYINT NOT NULL,
`staff_email` VARCHAR(100) NOT NULL,
PRIMARY KEY ( `staff_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
item
| 字段 | 類型 | 注解 |
|---|---|---|
| item_id | TINYINT | PRIMARY KEY |
| item_name | CHAR | |
| item_describe | VARCHAR |
- 建表sql:
CREATE TABLE IF NOT EXISTS `item`(
`item_id` TINYINT AUTO_INCREMENT,
`item_name` CHAR(40) NOT NULL,
`item_describe` VARCHAR(100) NOT NULL,
PRIMARY KEY ( `item_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
relation
| 字段 | 類型 | 注解 |
|---|---|---|
| relation_id | TINYINT | PRIMARY KEY |
| item_id | TINYINT | |
| staff_id | TINYINT | |
| item_time | DATE | 此處應(yīng)該拆分一個(gè)開始時(shí)間和結(jié)束時(shí)間搜吧，但是因?yàn)闀簳r(shí)沒有邏輯翁涤，此處只是一個(gè)練習(xí)打肝，但是如果有擴(kuò)展需要時(shí)間查詢坤邪，放在這里比較好擴(kuò)展 |
- 建表sql
use HE;
CREATE TABLE IF NOT EXISTS `relation`(
`relation_id` TINYINT AUTO_INCREMENT,
`item_id` TINYINT NOT NULL,
`staff_id` TINYINT NOT NULL ,
`item_time` DATE NOT NULL,
PRIMARY KEY ( `relation_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
- 模擬一些操作：
新增項(xiàng)目sql
insert into item (item_name,item_describe)
values("測試項(xiàng)目3","一個(gè)測試項(xiàng)目"),("測試項(xiàng)目4","1111");
查詢項(xiàng)目sql
SELECT * FROM HE.item;
新增幾個(gè)人員
insert into staff (staff_name,staff_dept,staff_age,staff_email,staff_sex)
values("小許","測試部",18,"example@xxx.com",2),("小班","測試部",19,"example1@xxx.com",2),("小李","測試部",19,"example1@xxx.com",2);
where子句
#多少員工等于18歲
select * from staff where staff_age = 18;
update
#修改員工郵箱
update staff set staff_email="example@xx.cn" where staff_name= “小許”;
'''此時(shí)會(huì)報(bào)錯(cuò)：異常內(nèi)容：Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Queries and reconnect.
這是因?yàn)镸ySql運(yùn)行在safe-updates模式下惭蹂，該模式會(huì)導(dǎo)致非主鍵條件下無法執(zhí)行update或者delete命令伞插，執(zhí)行命令SET SQL_SAFE_UPDATES = 0;修改下數(shù)據(jù)庫模式
'''
update staff set staff_email="example@xx.xyz" where staff_id= 1
delete
#小李離職
delete from staff where id =2;
like子句
#獲取staff表中
select * from staff where staff_email like '%xyz'
新增項(xiàng)目關(guān)系
#添加幾個(gè)項(xiàng)目關(guān)系
#小班加入第一個(gè)項(xiàng)目 小許加入第一第二項(xiàng)目
insert into relation (item_id,staff_id,item_time)
values(1,1,"2020-05-21"),(2,1,"2020-05-22"),(1,2,"2020-05-22");
union和union all
#這里用不到，隨便舉個(gè)無業(yè)務(wù)邏輯的例子：
#取出relution和item的item_id(相同的值只取一次)
select item_id from relation union select item_id from item;
#取出relution和item的全部item_id
select item_id from relation union allselect item_id from itemorder by item_id
排序
#降序排列staff
select *from staff order by staff_id desc
#升序排列
select *from staff order by staff_id asc
tips：新增人員后端返回的數(shù)據(jù)最好是降序排列剿干，否則在添加之后客戶看不到反饋蜂怎。客戶的反饋是考量產(chǎn)品的友好度的一個(gè)方面置尔，應(yīng)該在業(yè)務(wù)邏輯中考量產(chǎn)品的友好度
內(nèi)連接
查詢員工表和關(guān)系表 staff_id相同數(shù)據(jù)
#查詢有在項(xiàng)目的項(xiàng)目的員工
SELECT * FROM staff a inner join relation b on a.staff_id=b.staff_id;
左連接(左外連接)
SELECT * FROM staff a left join relation b on a.staff_id=b.staff_id;
右連接（右外連接）
SELECT * FROM staff a right join relation b on a.staff_id=b.staff_id;
具體可以參考以下鏈接（圖解內(nèi)連接等連接）
https://blog.csdn.net/plg17/article/details/78758593
GROU BY
#對應(yīng)id有多少項(xiàng)目
SELECT staff_id,count(*)from relation group by staff_id
嵌套右連接分組
#員工各有幾個(gè)項(xiàng)目
select staff_name,count(*) from (select a.* FROM staff a right join relation b on a.staff_id=b.staff_id ) as c group by staff_name ;
事務(wù)
#開始事務(wù)
begin;
#更新員工email字段
update staff set staff_email="example@xx.xyz" where staff_id= 1
#回滾
rollback;
#查詢Email字段未變動(dòng)
select * from staff;
下面是綜合回顧：
| 相關(guān)知識點(diǎn) |
|---|
| docker |
| mysql |
| pymysql |
docker：我們要了解什么是docker
https://www.zhihu.com/question/28300645
我們這里只是為了方便準(zhǔn)備環(huán)境杠步，方便練習(xí)。
準(zhǔn)備環(huán)境：
請先安裝docker
#搜索可用的mysql
docker search mysql
#我們拉一下最新的鏡像
docker pull mysql:latest
#數(shù)據(jù)持久化將容器內(nèi)db數(shù)據(jù)映射出來：
#新建目錄
mkdir -p /home/mySql/db
#跑起來容器
docker run -itd --name mysql-test -v /home/mySql/db:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=84529563 mysql
#連接試試
docker exec -it f61f90d6f52b mysql -h localhost -uroot -p
pymysql：
環(huán)境：pycharm+python3.6+pymongo
使用pymysql需要裝以下兩個(gè)庫：
我是在pycharm
pymysql
cryptography(此處要注意)
以一次建庫操作體驗(yàn)一下pymysql
import pymysql
connect = pymysql.connect(
host= 'ip',
user='root',password='84529563',
database='RENTEST',
charset= 'utf8')
#創(chuàng)建游標(biāo)
cursor = connect.cursor()
#sql語句
sql= "create DATABASE HE;"
effect_row = cursor.execute(sql)
#提交事務(wù)
connect.commit()
#關(guān)閉游標(biāo)對象
cursor.close()
#關(guān)閉數(shù)據(jù)庫鏈接
connect.close()
sql語法
此處還是要系統(tǒng)學(xué)習(xí)一下
https://segmentfault.com/a/1190000015189617
數(shù)據(jù)庫分類：
SQL語言有五類
1榜轿、DDL Data Defintion Language即數(shù)據(jù)定義語言
包含操作：creat（建庫）幽歼、drop（刪庫）alter（表的相關(guān)操作）
2、DML Data Manipulation Language即數(shù)據(jù)操作語言
包括操作：insert delete update select
3谬盐、DQL Data Query Language即數(shù)據(jù)查詢語言select
4甸私、TCL Transaction Control Language即事務(wù)控制：begin、roolback飞傀，commit
5皇型、DCL Data Control Language即數(shù)據(jù)控制語言：用戶權(quán)限相關(guān)
（未完待續(xù)）
下一篇計(jì)劃利用flask框架寫一個(gè)接口诬烹，練習(xí)pymysql
