之前为了修改数据方便测试写了一个脚本，现在想系统整理一下相关知识
计划：
使用docker搭建一个mysql数据库，系统整理mysql基础，使用pymsql练习小Demo。
Demo需求分析
背景：测试人员也是一种测试资源，当测试部门独立时，测试人员作为一种测试资源在项目中合理配比。
需求 ：一个员工最多归属两个项目
设计：分析需求画ER图：（矩形是实体，圆形是属性，菱形是关系）
- 建表
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 | 此处应该拆分一个开始时间和结束时间，但是因为暂时没有逻辑，此处只是一个练习，但是如果有扩展需要时间查询，放在这里比较好扩展 |
- 建表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;
- 模拟一些操作：
新增项目sql
insert into item (item_name,item_describe)
values("测试项目3","一个测试项目"),("测试项目4","1111");
查询项目sql
SELECT * FROM HE.item;
新增几个人员
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= “小许”;
'''此时会报错：异常内容：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.
这是因为MySql运行在safe-updates模式下，该模式会导致非主键条件下无法执行update或者delete命令，执行命令SET SQL_SAFE_UPDATES = 0;修改下数据库模式
'''
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'
新增项目关系
#添加几个项目关系
#小班加入第一个项目 小许加入第一第二项目
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
#这里用不到，随便举个无业务逻辑的例子：
#取出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：新增人员后端返回的数据最好是降序排列，否则在添加之后客户看不到反馈。客户的反馈是考量产品的友好度的一个方面，应该在业务逻辑中考量产品的友好度
内连接
查询员工表和关系表 staff_id相同数据
#查询有在项目的项目的员工
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;
具体可以参考以下链接（图解内连接等连接）
https://blog.csdn.net/plg17/article/details/78758593
GROU BY
#对应id有多少项目
SELECT staff_id,count(*)from relation group by staff_id
嵌套右连接分组
#员工各有几个项目
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 ;
事务
#开始事务
begin;
#更新员工email字段
update staff set staff_email="example@xx.xyz" where staff_id= 1
#回滚
rollback;
#查询Email字段未变动
select * from staff;
下面是综合回顾：
| 相关知识点 |
|---|
| docker |
| mysql |
| pymysql |
docker：我们要了解什么是docker
https://www.zhihu.com/question/28300645
我们这里只是为了方便准备环境，方便练习。
准备环境：
请先安装docker
#搜索可用的mysql
docker search mysql
#我们拉一下最新的镜像
docker pull mysql:latest
#数据持久化将容器内db数据映射出来：
#新建目录
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：
环境：pycharm+python3.6+pymongo
使用pymysql需要装以下两个库：
我是在pycharm
pymysql
cryptography(此处要注意)
以一次建库操作体验一下pymysql
import pymysql
connect = pymysql.connect(
host= 'ip',
user='root',password='84529563',
database='RENTEST',
charset= 'utf8')
#创建游标
cursor = connect.cursor()
#sql语句
sql= "create DATABASE HE;"
effect_row = cursor.execute(sql)
#提交事务
connect.commit()
#关闭游标对象
cursor.close()
#关闭数据库链接
connect.close()
sql语法
此处还是要系统学习一下
https://segmentfault.com/a/1190000015189617
数据库分类：
SQL语言有五类
1、DDL Data Defintion Language即数据定义语言
包含操作：creat（建库）、drop（删库）alter（表的相关操作）
2、DML Data Manipulation Language即数据操作语言
包括操作：insert delete update select
3、DQL Data Query Language即数据查询语言select
4、TCL Transaction Control Language即事务控制：begin、roolback，commit
5、DCL Data Control Language即数据控制语言：用户权限相关
（未完待续）
下一篇计划利用flask框架写一个接口，练习pymysql
