测试开发笔记三(SQL语法与数据库)

01 | MySQL数据库搭建


安装mysql(windows)

  • 下载地址:https://dev.mysql.com/downloads/mysql/

    image.png

    image.png

  • 选择历史版本(若想选择历史版本,可切换到Archives页签)


    image.png

    image.png
  • 解压后无需安装


    image.png
  • 新建data目录和my.ini文件


    image.png
  • 配置my.ini内容

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=D:\\Program Files\\mysql-8.0.19-winx64
# 设置mysql数据库的数据存储目录
datadir=D:\\Program Files\\mysql-8.0.19-winx64\\data
# 允许最大连接数
max_connections=200
# 允许连接失败的次数。为防止有人从该主机攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8
  • 配置环境变量
image.png

image.png
  • 执行初始化命令:mysqld --initialize --user=mysql --console;生成临时密码(记住此密码,第1次登录时会用到)

    image.png

    遇到问题:
    image.png

    解决方案:
    清空data目录中所有文件

  • 将mysql添加都服务:执行mysqld -install
    遇到问题:

    image.png

    解决方案:
    https://www.cnblogs.com/dhpong/p/10613915.html

  • 启动mysql服务:net start mysql
    遇到问题:
    无法启动mysql服务,系统错误1067
    解决方案:
    https://www.cnblogs.com/strawqqhat/p/10602228.html

  • 登录:
    1.执行mysql -u root -p
    2.密码是之前的临时密码

到此mysql安装完了,真是一步一个坑啊,还好当天都解决了,如果留到第2天,晚上会睡不着觉的

navicat连接数据库

  • 修改临时密码
use mysql;
alter user 'root'@'localhost' identified with mysql_native_password by '1234qwer';
flush privileges;
  • 新建数据库
    1.字符集:utf8mb4 -- UTF-8 Unicode
    2.排序规则:utf8mb4_general_ci

  • 命令行界面
    右击数据库-命令行界面

  • sql文件导入(导入表、初始数据)
    右击数据库 - 运行SQL文件 - 选择sql文件-开始


    image.png
  • sql文件导出
    右击数据库 或 右击表 - 转储SQL文件

02 | 数据库结构解析


创建表

CREATE TABLE `test_user` (
    `id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `name` VARCHAR(100) DEFAULT NULL COMMENT '名称',
    PRIMARY KEY (`id`) USING BTREE  # 索引
)ENGINE=INNODB AUTO_INCREMENT=10 DEFAULT CHARSET=UTF8 ROW_FORMAT=DYNAMIC COMMENT '测试用户表';
  • row_format
    1.fixed:若一张表不存在varchar、text以及其变形、blob以及其变形的字段,这张表就是静态表;静态表每条记录所占用的字节一样,优点是读取快,缺点是浪费空间。
    2.dynamic:若一张表里面存在varchar、text以及其变形、blob以及其变形的字段,这张表就是动态表;动态表每条记录所占用的字节是动态的,其优点节省空间,缺点增加读取的时间开销。
    3.搜索查询量大的表一般都以空间来换取时间,设计成静态表。

03 | 修改表结构


  • 格式
    ALTER TABLE <表名> [修改选项]

  • 修改选项预发格式
    1.ADD COLUMN <列名> <类型> // 添加字段
    2.CHANGE COLUMN <旧列名> <新列名> <新列类型> //改变字段名
    3.MODIFY COLUMN <列名> <类型> //修改字段类型
    4.DROP COLUMN <列名> //删除字段
    5.RENAME TO <新表名> //修改表名

04 | 表数据的增删改查


语法

  • 插入1条数据
    insert into 表名(字段名[,字段名2]) values(字段值1,字段值2)
  • 删除数据
    1.delete from 表名 where 字段名=字段值 //删除1条数据
    2.delete from 表名 //删除所有数据
  • 查询数据
    select * from 表名
  • 更新数据
    1.update 表名 set 字段名1=字段值1 where 字段名2=字段值2
    2.update 表名 set 字段名1=字段值1 //修改表里所有数据

05 | join


左连接查询 left join

  • 语句
    select * from a_table a left join b_table b on a.a_id = b.b_id;

  • 说明
    1.左表(a_table)记录都会显示
    2.右表(b_table)符合on后面条件的记录才会显示
    3.左表有,右表没有;则右表所有字段显示NULL

右连接查询 right join

  • 语句
    select * from a_table a right join b_table b on a.a_id = b.b_id;

  • 说明
    1.右表(b_table)记录都会显示
    2.左表(a_table)符合on后面条件的记录才会显示
    3.右表有,左表没有;则左表所有字段显示NULL

  • 语句
    select * from a_table a inner join b_table b on a.a_id = b.b_id;

  • 说明
    返回两个表的交集,左表和右表都有,且符合on后面条件的记录会显示

05 | redis内存数据库


简介

  • 开源高性能的key-value数据库
  • 支持数据的持久化,可将内存中的数据保存在磁盘中
  • 支持list,set,zset,hash等数据结构的存储
  • 支持master-slave模式的数据备份
  • 性能极高,redis读的速度是110000次/s,写的速度是81000次/s
  • 支持事务,要么成功执行要么失败完全不执行
  • 支持publish/subscribe,通知,key过期等特性

下载安装

  • 下载地址
    https://github.com/microsoftarchive/redis/releases

    image.png

  • 启动
    1.命令行->cd redis安装目录
    2.运行redis-server.exe redis.windows.conf

  • 连接
    1.原cmd窗口不要关闭,否则无法连接
    2.另开1个cmd窗口,进入redis的安装目录,运行redis-cli.exe -h localhost -p 6379

  • 设置密码
    1.编辑redis.windows.conf
    2.搜索requirepass,修改配置如下


    image.png

数据类型

类型 简介 特性 应用场景
String(字符串) - 二进制安全的;可存储任何数据(如jpg图片或序列化对象);1个键能存储512Mb数据 -
Hash(字典) 键值对集合 string类型的key-value的映射表,特别适合存储对象,每个hash可以存储40多个亿键值对 存储、读取、修改用户属性
List(列表) 简单的字符串列表,按照插入顺序排序,可添加1个元素到列表头部或尾部,1个列表可存储40多个亿的元素 增删快,提供了操作某一段元素的API 最新消息排行等功能(比如朋友圈);消息队列
Set(集合) String 类型的无序集合,元素不重复 通过哈希表实现;添加、删除、查找的复杂度都是0(1);每个集合可存储40多亿个成员 共同好友;利用唯一性统计访问网站的所有独立ip;好友推荐时,根据tag求交集,大于某个阈值就可以推荐
Sorted Set(有序集合) String 类型的有序集合,元素不重复 每个元素会关联1个double类型的分数;redis通过分数来为集合中的成员进行从小到大排序;有序集合的成员是唯一的,但分数(score)可重复;通过哈希表实现;添加、删除、查找的复杂度都是0(1);每个集合可存储40多亿个成员 排行榜;带权重的消息队列

基本使用

  • String
set key value
get key
  • Hash
hmset key field value [field value ...] 
hget key field
hgetall key
  • List
lpush  "key" value [value ...]
lrange "key" start stop
  • Set
sadd key member [member ...]
smembers key
  • Sorted Set
zadd key [NX|XX] [CH] [INCR] score member [score member ...]
zrange key start stop [WITHSCORES]
zrangebyscore key min max [WITHSCORES] [LIMIT offset count]

06 | MongoDB NoSQL数据库


关系型数据库和非关系型数据库

  • 关系型数据库
    1.存储在硬盘上,读写慢
    2.保持ACID(事物原则),扩展难

  • 非关系型数据库
    1.NoSql(Not Only SQL),不仅仅是SQL,更超越SQL
    2.key-value形式
    3.CAP->BASE
    一致性(Consistency):分布式系统,所有节点在同一时间拥有相同的数据
    可用性(Availability):保证每个请求不管成功还是失败都有响应
    分离容忍(Partition tolerance):系统中任意信息的丢失或失败不会影响系统的继续运作
    注:以上3个原则可以两两组合使用,但不能一起使用
    4.没有标准化的语言
    5.有限的查询功能

MongoDB

  • 简介
    1.非关系型数据库
    2.文档存储:类json格式
    3.有机会对字段建立索引,可实现关系型数据库

  • 下载安装启动
    1.下载地址:https://www.mongodb.com/download-center/community
    2.安装:一直下一步
    3.启动:
    mongod -dbpath=/path/mongodb
    mongo

数据库操作

  • 查看当前数据库:show dbs
  • 切换数据库(若没有则自动创建):use demo1
  • 插入1条数据:db.demo1.insert({"name":"小红"})
  • 删除数据库:
    1.切换数据库:use demo1
    2.删除:db.dropDatabase()

集合操作

  • 创建集合:db.createCollection('collection1')
  • 查看已经创建的集合:show collections
  • 删除集合:db.collection1.drop()
  • 插入文档
    1.文档是mongodb中的基本单元
    2.相当于表中的1条记录
    3.以json格式显示
    4.插入:db.collection1.insert({"name":"hello"})
    5.查看插入内容:db.collection1.find().pretty()
    6.插入复杂文档
# 声明变量
document=({
  title:'MongoDB',
  by:'RuotongYu',
  tags:['mongodb','database','NoSQL']
});
# 插入文档
db.collection1.insert(document)

比较大小

操作 格式 范例 类似语句
等于 {<key>:<value>} db.coll.find({"by":"2"}).pretty() where by = "2"
小于 {<key>:{$lt:<value>}} db.coll.find({"by":{$lt:50}}).pretty() where by < 50
小于等于 {<key>:{$lte:<value>}} db.coll.find({"by":{$lte:50}}).pretty() where by <= 50
大于 {<key>:{$gt:<value>}} db.coll.find({"by":{$gt:50}}).pretty() where by > 50
大于等于 {<key>:{$gte:<value>}} db.coll.find({"by":{$gte:50}}).pretty() where by >= 50
不等于 {<key>:{$ne:<value>}} db.coll.find({"by":{$ne:50}}).pretty() where by != 50

修改删除文档

  • 格式
db.collection.update(
  <query>,
  <update>,
  {
    upsert:<boolean>,  # 若不存在update的记录就插入,默认false
    multi:<boolean>,  # 更新所有按条件查出来的多条记录,默认false(只更新第1条)
    writeConcern:<document>  # 抛出异常级别
  }
)

注:update是替换的意思,整条数据都会被替换

  • 修改单个文档和多个文档
    1.db.col.update({"tittle":"MongoDB"},{"title":"MongoDB123"})
    2.db.col.update({"name":"hello"},{$set:{"name":"hello123"}},{multi:true})

  • 删除单个文档和多个文档
    1.db.col.remove({"name":"hello123"},1)
    2.db.col.remove({"name":"hello"})

修改操作符

  • $inc
    1.用法:{$inc{field:value}}
    2.作用:对1个数字字段的某个field增加value
    3.示例:将name为xiaoming学生的age增加5,db.students.update({name:"xiaoming"},{$inc:{age:5}})

  • $set
    1.用法:{$set:{field:value}}
    2.作用:把文档中某个字段field的值设为value
    3.示例:把xiaohong的年龄设为10,db.students.update({name:"xiaohong"},{$set:{age:10}})

  • $push
    1.用法:{$push:{field:value}}
    2.作用:把value追加到field里。注:field只能是数据类型,若field不存在,则自动插入1个数据类型
    3.示例:wangyaxiong添加别名“timothy”,db.students.update({name:"wangyaxiong"},{$push:{"ailas":"timothy"}})

  • $rename
    1.用法:{$rename:{old_field_name:new_field_name}}
    2.作用:对字段进行重命名
    3.示例:把xiaohong记录的name字段重命名为name2,db.students.update({name:"xiaohong"},{$rename:{"name":"name2"}})

07 | Neo4j图数据库


image.png

定义

Neo4j是一个高性能的nosql图形数据库,它将结构化数据存储在网络上而不是表中

下载安装

  • 下载地址:https://neo4j.com/download-center/#community

  • 安装使用(windows)
    1.解压压缩包,并配置环境变量
    2.命令行运行neo4j console(4.0.1版本不支持jdk8,需要升级到jdk11以上)
    3.浏览器输入http://localhost:7474
    4.输入默认的用户名/密码:neo4j

节点

  • 创建节点
    1.创建emp节点,标签为Employee,create(emp:Employee)
    2,.创建xiaohong节点,带有属性:create(xiaohong:People{no:1,age:10,gender:"M"})

  • 删除节点:match(e:people)delete e

  • 添加属性

# 筛选age为32的节点,并赋值给a
match(a{age:32})
set a.name="hello"
return a
  • 查询节点属性
match(a:peple)  # match(变量名:标签名)
return a  # 返回节点所有属性
return a.age  # 返回节点单个属性

match(a:{age:"32"})
return a 
  • 删除属性
match(book{title:"book1"})
remove book.price
return book

关系

  • 创建关系
create (p1:profile1{name:"xiaoming"})-[r1:friends]->(p2:profile2{name:"limei"}) 

match(a:people),(b:people)
where a.name="xiaohong" and b.name="wangwu"
create (a)-[r1:friends]-(b)
  • 删除关系
match(a:profile1)-[friends]->(b:profile2)
delete a,b,friends

delete和remove

  • delete删除节点和关系
  • remove删除标签和属性

其他

  • 排序
match(emp:employee)
return emp.empid,emp.name,emp,salary,emp.deptno
order by emp.name desc

08 | MYSQL环境部署


docker镜像

docker run \
--name mysql \
-v $PWD/mysql:/var/lib/mysql \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=hogwarts \
-d mysql:5.7

WorkBench

测试数据库

  • 项目地址:https://github.com/datacharmer/test_db
  • 导入sql
    1.方式1
    mysql -h localhost -u root -p < employees.sql
    2.方式2
    cat employees.sql | sed -e "s#employees;#employees_wangyaxiong;#g" | mysql -h sql.testing-studio.com -u root -p

09 | SQL语法和关键知识点


SQL分类

分类 说明 关键字
DDL(Data Definition Language) 数据定义语言,对数据库中的对象进行操作,不能操作表中的数据 create(创建)
alter(修改)
drop(删除)
rename(重命名)
truncate(截断)
comment(注释)
DML(Data Manipulation Language) 数据操作语言,用来操作表中的数据 select(查询)
insert(插入)
update(更新)
merge(合并)
call
explain plan
lock table
DCL(Data Control Language) 数据控制语言,控制的是用户的权限 grant(向用户赋予权限/角色)
revoke(撤销用户的权限/角色)
TCL(Transaction Control Language) 事物控制语言 commit
rollback(回滚)
savepoint(设置保存点)
set transaction

基本信息

  • show databases;
  • show tables;
  • help show;

增删改查

语句类型 示例
查询 select * from Customers;
增加 insert into Customeres values ('Cardinal','Stavanger','Norway');
insert into Customeres(CustomerName,City,Country) values ('Cardinal','Stavanger','Norway')
更新 update Customers set ContactName='Alfred Schmidt',City='Frankfurt',where CustomerID=1;
删除 delete from Customers where CustomerName='Alfreds';

基本查询

查询类型 示例
基本查询 select * from table_name
字段查询 select fileds from table
条件查询 select * from table where a = 1
排序 select * from table order by b desc
分页 select * from table order limit 10 offset 0
去重 select distinct gender from employees
  • 练习
    1.select birth_date 生日, gender 性别 from employees;
    2.select * from employees limit 10 offset 0;offset(偏移)=0,显示第1页(1-10条数据)
    3.select * from employees limit 10 offset 10;offset(偏移)=10,显示第2页(11-20条数据)

条件查询where

类型 示例
比较 =、>、<、<>
通配 where CustomerName like '%or%';
范围 where price between 10 and 20;
子集限定 where Country in ('Germany','France','UK')
逻辑关系 and or not
  • 练习
    1.SELECT * FROM departments WHERE dept_no BETWEEN 'd001' AND 'd003';;注 d001 和 d003 要加引号
    2.SELECT * FROM departments WHERE dept_no in (SELECT dept_no FROM departments WHERE dept_no BETWEEN 'd001' AND 'd003');;括号里可写子查询

聚合查询

  • 基本语法:group by 字段 having 条件
  • 常用函数:count,max,minx,sum,avg
  • 示例
    1.select count(gender),gender from employees group by gender;
    2.select count(gender),gender from employees group by gender having gender='F';
  • 练习
    1.统计employees表中数据:select count(*) from employees;
    2.查询employees表生日最小的记录:SELECT * FROM employees WHERE birth_date in (SELECT MAX(birth_date) FROM employees);
    3.查询employees表男女人数:SELECT gender 性别,COUNT(*) 人数 FROM employees GROUP BY gender;

进阶查询-多表join

  • 查询员工的生日、部门、工资:
SELECT
    first_name 名,
    last_name 姓,
    birth_date 生日,
    departments.dept_name 部门,
    salaries.salary 工资
FROM
    employees
JOIN dept_emp ON employees.emp_no = dept_emp.emp_no
JOIN departments ON dept_emp.dept_no = departments.dept_no
JOIN salaries ON employees.emp_no = salaries.emp_no
WHERE
    first_name = 'Nahum';
  • 查询各部门历任领导的记录并附上部门名字
SELECT
    first_name 名,
    last_name 姓,
    departments.dept_name 部门
FROM
    employees
JOIN dept_manager ON employees.emp_no = dept_manager.emp_no
JOIN departments ON dept_manager.dept_no = departments.dept_no
  • 计算各个部门的平均工资并排序,给出平均工资、部门名称
SELECT
    departments.dept_name ,
    AVG(salary)  salary
FROM
    salaries
LEFT JOIN dept_emp ON dept_emp.emp_no = salaries.emp_no
LEFT JOIN departments ON departments.dept_no = dept_emp.dept_no
GROUP BY
    departments.dept_no
ORDER BY
    salary;

10 | 实战


关键名字

  • 主键:表中记录的唯一标识符、非空、不重复
  • 外键:字段中的值来自于其他表的主键
  • 索引:加速表的查询

内置函数

官网地址:https://dev.mysql.com/doc/refman/8.0/en/

查看日志

  • general log:记录所有sql
SET GLOBAL general_log = 'ON';
SHOW VARIABLES LIKE "general_log%";  可查看log的位置
SELECT * FROM mysql.general_log;
  • slow log:记录慢查询
show VARIABLES LIKE "slow%";
show VARIABLES LIKE "long%";  可查看log的位置
SET GLOBAL slow_query_log = "ON";
SET GLOBAL long_query_time = 5;
  • 查看当前mysql的连接数
select count(*) from sys.`session`;

备份

  • 用工具(navicat),参考《01 | MYSQL数据库搭建》中sql导出导入
  • 用命令
mysqldump -h localhost -uroot -p1234qwer --databases employees

使用python连接mysql

  • 安装依赖:pip install mysql-connector-python
  • 示例
import datetime
import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="1234qwer",
  database="employees"
)
mycursor = mydb.cursor()

query = ("select first_name,last_name,hire_date from employees "
          "where hire_date between %s and %s")

hire_start = datetime.date(1999,1,1)
hire_end = datetime.date(1999,12,31)

mycursor.execute(query,(hire_start,hire_end))

for (first_name,last_name,hire_date) in mycursor:
  print("{},{} was hired on {:%d %b %Y}".format(
    last_name,first_name,hire_date))
 
mycursor.close()
mydb.close()

数据库面试题

  • 知识点结构
    1.数据定义:DDL
    2.sql基本查询知识:DML
    3.sql的知识进阶:DML
    4.数据库实操经验:自身机制与性能
  • 数据定义
    1.数据类型:常见数字类型、字符串类型(varchar)
    2.关系定义:主键、外键、索引
    3.表解雇修改:alter
  • 数据定义
    1.条件查询
    2.分页查询
    3.聚合查询
    4.更新符合条件数据
    5.删除符合条件数据
  • sql进阶
    1.链接(join)查询
    2.事物:定义与语法
    3.索引:价值与用途
    4.存储过程:如何对存储过程进行测试
  • 数据库使用经验
    1.常见数据库:mysql、oracle、mongodb、redis
    2.数据库的部署:docker、开放端口、连接方式
    3.数据库备份与恢复:mysqldump、mysql
    4.性能统计:连接数、show sql
    5.加锁机制:悲观锁、乐观锁、行锁、表锁

推荐书籍与学习建议

  • SQL必知必会(第4版)
  • 高性能MySQL(最新版)
  • MySQL官方文档
  • 熟悉MySQL,不要学习SQL Server等边缘数据库
  • 学号SQL,RDBMS与NoSQL产品多数也都支持SQL
  • NoSQL数据库多数应用与大数据,可以用于拔高了解
  • 使用flask编写小网站熟悉下SQL的应用场景

11 | 补充


docker的mysql容器导入sql文件

  • 若服务器在远端,执行以下命令
docker run -it -v /Users/seveniruby/projects/test_db:/data/ --rm mysql bash -c "cd /data; mysql -h sql.testing-studio.com -u root -p < /data/employees.sql"
  • 若在服务器上,执行以下命令
docker exec -i mysql mysql -h sql.testing-studio.com -uroot -phogwarts  < /tmp/demo.sql

测试工程师用sql做什么?

  • 测试数据构造(假设你搞不定mock)
  • 清理和还原被测系统数据, 一键恢复原来数据
  • 测试数据的提取
  • 写数据的校验

推荐阅读更多精彩内容

  • 2017/3/14 RDBMS:关系型数据库管理系统 关系模型独立于语言 SQL有几种不同类型的语言:数据定义语言...
    ancherl阅读 829评论 0 6
  • MSSQL 跨库查询(臭要饭的!黑夜) 榨干MS SQL最后一滴血 SQL语句参考及记录集对象详解 关于SQL S...
    碧海生曲阅读 2,821评论 0 0
  • 常用语句: sql/plus sqlplus 'amdocs/Amdocs.Jx.China.110#@ysdb1...
    好好学习的蜗牛阅读 351评论 0 0
  • 今天在杭州游览了西湖,去了宋城,观看了宋朝千古情表演,有马不停蹄的赶到乌镇,逛了一圈,紧接着飞奔上海,来到上海环球...
    轻描淡写的小情绪晴阅读 60评论 0 0
  • 没有起点的原点 没有下雨的昨天 你把信寄给了地平线 在海的那一端 城市的夜太绚烂 而你一个人在灯火阑珊 把酒无欢 ...
    山野木兮阅读 22评论 0 1