01 | MySQL數(shù)據(jù)庫搭建

安裝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內(nèi)容

[mysqld]
# 設置3306端口
port=3306
# 設置mysql的安裝目錄
basedir=D:\\Program Files\\mysql-8.0.19-winx64
# 設置mysql數(shù)據(jù)庫的數(shù)據(jù)存儲目錄
datadir=D:\\Program Files\\mysql-8.0.19-winx64\\data
# 允許最大連接數(shù)
max_connections=200
# 允許連接失敗的次數(shù)。為防止有人從該主機攻擊數(shù)據(jù)庫系統(tǒng)
max_connect_errors=10
# 服務端使用的字符集默認為UTF8
character-set-server=utf8
# 創(chuàng)建新表時將使用的默認存儲引擎
default-storage-engine=INNODB
[mysql]
# 設置mysql客戶端默認字符集
default-character-set=utf8
[client]
# 設置mysql客戶端連接服務端時默認使用的端口
port=3306
default-character-set=utf8

• 配置環(huán)境變量

image.png

image.png

• 以管理員身份打開cmd，并cd到mysql的bin目錄
  
  image.png
  
  遇到問題：
  
  image.png
  
  解決方案：https://blog.csdn.net/weixin_42545675/article/details/104108216

• 執(zhí)行初始化命令：mysqld --initialize --user=mysql --console；生成臨時密碼（記住此密碼，第1次登錄時會用到）
  

  image.png
  
  遇到問題：
  
  image.png
  
  解決方案：
  清空data目錄中所有文件

• 將mysql添加都服務：執(zhí)行mysqld -install
  遇到問題：
  

  image.png
  
  解決方案：
  https://www.cnblogs.com/dhpong/p/10613915.html

• 啟動mysql服務：net start mysql
  遇到問題：
  無法啟動mysql服務，系統(tǒng)錯誤1067
  解決方案：
  https://www.cnblogs.com/strawqqhat/p/10602228.html

• 登錄：
  1.執(zhí)行mysql -u root -p
  2.密碼是之前的臨時密碼

到此mysql安裝完了，真是一步一個坑啊，還好當天都解決了，如果留到第2天，晚上會睡不著覺的

navicat連接數(shù)據(jù)庫

• 修改臨時密碼

use mysql;
alter user 'root'@'localhost' identified with mysql_native_password by '1234qwer';
flush privileges;

• 新建數(shù)據(jù)庫
  1.字符集：utf8mb4 -- UTF-8 Unicode
  2.排序規(guī)則：utf8mb4_general_ci

• 命令行界面
  右擊數(shù)據(jù)庫-命令行界面

• sql文件導入（導入表、初始數(shù)據(jù)）
  右擊數(shù)據(jù)庫 - 運行SQL文件 - 選擇sql文件-開始

  
  
  image.png

• sql文件導出
  右擊數(shù)據(jù)庫 或 右擊表 - 轉儲SQL文件

02 | 數(shù)據(jù)庫結構解析

創(chuàng)建表

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以及其變形的字段，這張表就是靜態(tài)表；靜態(tài)表每條記錄所占用的字節(jié)一樣，優(yōu)點是讀取快，缺點是浪費空間。
  2.dynamic：若一張表里面存在varchar、text以及其變形、blob以及其變形的字段，這張表就是動態(tài)表；動態(tài)表每條記錄所占用的字節(jié)是動態(tài)的，其優(yōu)點節(jié)省空間，缺點增加讀取的時間開銷。
  3.搜索查詢量大的表一般都以空間來換取時間，設計成靜態(tài)表。

03 | 修改表結構

• 格式
  ALTER TABLE <表名> [修改選項]

• 修改選項預發(fā)格式
  1.ADD COLUMN <列名> <類型> // 添加字段
  2.CHANGE COLUMN <舊列名> <新列名> <新列類型> //改變字段名
  3.MODIFY COLUMN <列名> <類型> //修改字段類型
  4.DROP COLUMN <列名> //刪除字段
  5.RENAME TO <新表名> //修改表名

04 | 表數(shù)據(jù)的增刪改查

語法

• 插入1條數(shù)據(jù)
  insert into 表名(字段名[,字段名2]) values(字段值1，字段值2)
• 刪除數(shù)據(jù)
  1.delete from 表名 where 字段名=字段值 //刪除1條數(shù)據(jù)
  2.delete from 表名 //刪除所有數(shù)據(jù)
• 查詢數(shù)據(jù)
  select * from 表名
• 更新數(shù)據(jù)
  1.update 表名 set 字段名1=字段值1 where 字段名2=字段值2
  2.update 表名 set 字段名1=字段值1 //修改表里所有數(shù)據(jù)

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內(nèi)存數(shù)據(jù)庫

簡介

• 開源高性能的key-value數(shù)據(jù)庫
• 支持數(shù)據(jù)的持久化，可將內(nèi)存中的數(shù)據(jù)保存在磁盤中
• 支持list，set，zset，hash等數(shù)據(jù)結構的存儲
• 支持master-slave模式的數(shù)據(jù)備份
• 性能極高，redis讀的速度是110000次/s，寫的速度是81000次/s
• 支持事務，要么成功執(zhí)行要么失敗完全不執(zhí)行
• 支持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

數(shù)據(jù)類型

基本使用

• 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數(shù)據(jù)庫

關系型數(shù)據(jù)庫和非關系型數(shù)據(jù)庫

• 關系型數(shù)據(jù)庫
  1.存儲在硬盤上，讀寫慢
  2.保持ACID（事物原則），擴展難

• 非關系型數(shù)據(jù)庫
  1.NoSql（Not Only SQL），不僅僅是SQL，更超越SQL
  2.key-value形式
  3.CAP->BASE
  一致性（Consistency）：分布式系統(tǒng)，所有節(jié)點在同一時間擁有相同的數(shù)據(jù)
  可用性（Availability）：保證每個請求不管成功還是失敗都有響應
  分離容忍（Partition tolerance）：系統(tǒng)中任意信息的丟失或失敗不會影響系統(tǒng)的繼續(xù)運作
  注：以上3個原則可以兩兩組合使用，但不能一起使用
  4.沒有標準化的語言
  5.有限的查詢功能

MongoDB

• 簡介
  1.非關系型數(shù)據(jù)庫
  2.文檔存儲：類json格式
  3.有機會對字段建立索引，可實現(xiàn)關系型數(shù)據(jù)庫

• 下載安裝啟動
  1.下載地址：https://www.mongodb.com/download-center/community
  2.安裝：一直下一步
  3.啟動：
  mongod -dbpath=/path/mongodb 或
  mongo

數(shù)據(jù)庫操作

• 查看當前數(shù)據(jù)庫：show dbs
• 切換數(shù)據(jù)庫（若沒有則自動創(chuàng)建）：use demo1
• 插入1條數(shù)據(jù)：db.demo1.insert({"name":"小紅"})
• 刪除數(shù)據(jù)庫：
  1.切換數(shù)據(jù)庫：use demo1
  2.刪除：db.dropDatabase()

集合操作

• 創(chuàng)建集合：db.createCollection('collection1')
• 查看已經(jīng)創(chuàng)建的集合：show collections
• 刪除集合：db.collection1.drop()
• 插入文檔
  1.文檔是mongodb中的基本單元
  2.相當于表中的1條記錄
  3.以json格式顯示
  4.插入:db.collection1.insert({"name":"hello"})
  5.查看插入內(nèi)容：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是替換的意思，整條數(shù)據(jù)都會被替換

• 修改單個文檔和多個文檔
  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個數(shù)字字段的某個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只能是數(shù)據(jù)類型，若field不存在，則自動插入1個數(shù)據(jù)類型
  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圖數(shù)據(jù)庫

image.png

定義

Neo4j是一個高性能的nosql圖形數(shù)據(jù)庫，它將結構化數(shù)據(jù)存儲在網(wǎng)絡上而不是表中

下載安裝

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

• 安裝使用（windows）
  1.解壓壓縮包，并配置環(huán)境變量
  2.命令行運行neo4j console（4.0.1版本不支持jdk8，需要升級到jdk11以上）
  3.瀏覽器輸入http://localhost:7474
  4.輸入默認的用戶名/密碼：neo4j

節(jié)點

• 創(chuàng)建節(jié)點
  1.創(chuàng)建emp節(jié)點，標簽為Employee，create(emp:Employee)
  2,.創(chuàng)建xiaohong節(jié)點，帶有屬性：create(xiaohong:People{no:1,age:10,gender:"M"})

• 刪除節(jié)點：match(e:people)delete e

• 添加屬性

# 篩選age為32的節(jié)點，并賦值給a
match(a{age:32})
set a.name="hello"
return a

• 查詢節(jié)點屬性

match(a:peple)  # match(變量名：標簽名)
return a  # 返回節(jié)點所有屬性
return a.age  # 返回節(jié)點單個屬性

match(a:{age:"32"})
return a 

• 刪除屬性

match(book{title:"book1"})
remove book.price
return book

關系

• 創(chuàng)建關系

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刪除節(jié)點和關系
• remove刪除標簽和屬性

其他

• 排序

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

08 | MYSQL環(huán)境部署

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/

測試數(shù)據(jù)庫

• 項目地址：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條數(shù)據(jù)）
  3.select * from employees limit 10 offset 10；offset（偏移）=10，顯示第2頁（11-20條數(shù)據(jù)）

條件查詢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 條件
• 常用函數(shù)：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.統(tǒng)計employees表中數(shù)據(jù)：select count(*) from employees;
  2.查詢employees表生日最小的記錄：SELECT * FROM employees WHERE birth_date in (SELECT MAX(birth_date) FROM employees);
  3.查詢employees表男女人數(shù)：SELECT gender 性別,COUNT(*) 人數(shù) 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 | 實戰(zhàn)

關鍵名字

• 主鍵：表中記錄的唯一標識符、非空、不重復
• 外鍵：字段中的值來自于其他表的主鍵
• 索引：加速表的查詢

內(nèi)置函數(shù)

官網(wǎng)地址：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的連接數(shù)

select count(*) from sys.`session`;

備份

• 用工具（navicat），參考《01 | MYSQL數(shù)據(jù)庫搭建》中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()

數(shù)據(jù)庫面試題

• 知識點結構
  1.數(shù)據(jù)定義：DDL
  2.sql基本查詢知識：DML
  3.sql的知識進階：DML
  4.數(shù)據(jù)庫實操經(jīng)驗：自身機制與性能
• 數(shù)據(jù)定義
  1.數(shù)據(jù)類型：常見數(shù)字類型、字符串類型（varchar）
  2.關系定義：主鍵、外鍵、索引
  3.表解雇修改：alter
• 數(shù)據(jù)定義
  1.條件查詢
  2.分頁查詢
  3.聚合查詢
  4.更新符合條件數(shù)據(jù)
  5.刪除符合條件數(shù)據(jù)
• sql進階
  1.鏈接（join）查詢
  2.事物：定義與語法
  3.索引：價值與用途
  4.存儲過程：如何對存儲過程進行測試
• 數(shù)據(jù)庫使用經(jīng)驗
  1.常見數(shù)據(jù)庫：mysql、oracle、mongodb、redis
  2.數(shù)據(jù)庫的部署：docker、開放端口、連接方式
  3.數(shù)據(jù)庫備份與恢復：mysqldump、mysql
  4.性能統(tǒng)計：連接數(shù)、show sql
  5.加鎖機制：悲觀鎖、樂觀鎖、行鎖、表鎖

推薦書籍與學習建議

• SQL必知必會（第4版）
• 高性能MySQL（最新版）
• MySQL官方文檔
• 熟悉MySQL，不要學習SQL Server等邊緣數(shù)據(jù)庫
• 學號SQL，RDBMS與NoSQL產(chǎn)品多數(shù)也都支持SQL
• NoSQL數(shù)據(jù)庫多數(shù)應用與大數(shù)據(jù)，可以用于拔高了解
• 使用flask編寫小網(wǎng)站熟悉下SQL的應用場景

11 | 補充

docker的mysql容器導入sql文件

• 若服務器在遠端，執(zhí)行以下命令

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"

• 若在服務器上，執(zhí)行以下命令

docker exec -i mysql mysql -h sql.testing-studio.com -uroot -phogwarts  < /tmp/demo.sql

測試工程師用sql做什么？

• 測試數(shù)據(jù)構造（假設你搞不定mock）
• 清理和還原被測系統(tǒng)數(shù)據(jù)， 一鍵恢復原來數(shù)據(jù)
• 測試數(shù)據(jù)的提取
• 寫數(shù)據(jù)的校驗