测试开发笔记三（SQL语法与数据库）

2020-04-10  本文已影响0人  提摩太_e9ec

01 | MySQL数据库搭建

安装mysql（windows）

[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

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

navicat连接数据库

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

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 '测试用户表';

03 | 修改表结构

04 | 表数据的增删改查

语法

05 | join

左连接查询 left join

右连接查询 right join

05 | redis内存数据库

简介

下载安装

数据类型

类型 简介 特性 应用场景
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多亿个成员 排行榜；带权重的消息队列

基本使用

set key value
get key

hmset key field value [field value ...] 
hget key field
hgetall key

lpush  "key" value [value ...]
lrange "key" start stop

sadd key member [member ...]
smembers key

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数据库

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

MongoDB

数据库操作

集合操作

# 声明变量
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是替换的意思，整条数据都会被替换

修改操作符

07 | Neo4j图数据库

image.png

定义

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

下载安装

节点

# 筛选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

其他

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

测试数据库

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

基本信息

增删改查

语句类型 示例
查询 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

条件查询where

类型 示例
比较 =、>、<、<>
通配 where CustomerName like '%or%';
范围 where price between 10 and 20;
子集限定 where Country in ('Germany','France','UK')
逻辑关系 and or not

聚合查询

进阶查询-多表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/

查看日志

SET GLOBAL general_log = 'ON';
SHOW VARIABLES LIKE "general_log%";  可查看log的位置
SELECT * FROM mysql.general_log;

show VARIABLES LIKE "slow%";
show VARIABLES LIKE "long%";  可查看log的位置
SET GLOBAL slow_query_log = "ON";
SET GLOBAL long_query_time = 5;

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

备份

mysqldump -h localhost -uroot -p1234qwer --databases employees

使用python连接mysql

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()

数据库面试题

推荐书籍与学习建议

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做什么？

上一篇 下一篇

猜你喜欢

热点阅读