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

• 以管理员身份打开cmd，并cd到mysql的bin目录
  
  image.png
  
  遇到问题：
  
  image.png
  
  解决方案：https://blog.csdn.net/weixin_42545675/article/details/104108216

• 执行初始化命令：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

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)

比较大小

修改删除文档

• 格式

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镜像

• 地址：https://hub.docker.com/_/mysql
• 安装命令

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

WorkBench

• 地址：https://dev.mysql.com/downloads/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分类

基本信息

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

增删改查

基本查询

• 练习
  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

• 练习
  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）
• 清理和还原被测系统数据， 一键恢复原来数据
• 测试数据的提取
• 写数据的校验