巩固mysql基础-从一个需求开始（一）

作者: 铁甲依然在人间 | 来源:发表于2020-05-21 18:06 被阅读0次

巩固mysql基础-从一个需求开始（一）
MySQL Explain 执行计划
MySql那些事—基础架构
CorelDraw插件开发：从一个需求开始
从一个语言的基础开始
立一个目标，用半年去证明
04.DataFrame基础操作巩固-股票分析
FLAG_ACTIVITY_FORWARD_RESULT的使用
php基础巩固一
《MySQL实战》- 基础篇

之前为了修改数据方便测试写了一个脚本，现在想系统整理一下相关知识

计划：

使用docker搭建一个mysql数据库，系统整理mysql基础，使用pymsql练习小Demo。

Demo需求分析

背景：测试人员也是一种测试资源，当测试部门独立时，测试人员作为一种测试资源在项目中合理配比。
需求：一个员工最多归属两个项目
设计：分析需求画ER图：（矩形是实体，圆形是属性，菱形是关系）

image.png

建表

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