【知识详解】数据库（秋招总结）

数据库

目录

1.数据库概述

使用数据库的好处：

1.持久化数据到本地；
2.实现结构化查询，方便管理；

相关概念

  • DB:数据库，保存一组有组织的数据的容器；
  • DBMS:数据库管理系统，又叫数据库软件，用于管理DB中的数据；
  • SQL:结构化查询语言，用于和DBMS通信的语言；

数据库存储的特点

  • 数据放入表中，表再放入库中；
  • 表具有一些特性，定义了数据如何存储，类似于Java中的类；
  • 表由列(字段)组成，类似于Java中的属性；
  • 表中数据按行存储，类似于Java中的对象；

SQL语言分类

  • DQL(Data Query Language):数据查询语言； –> 查 select
  • DML(Data Manipulate Language):数据操作语言； –> 增删改； insert,update,delete;
  • DDL(Data Define Language):数据定义语言； –>create,drop,alter;
  • TCL(Transaction Control Language):事务控制语言； –>commit,rollback;

2.MySQL

2.1 DQL语言

1 基础查询：

SELECT firstname 姓 FROM students； #姓为别名；

2.条件查询：

SELECT firstname, score FROM students WHERE score<=90 AND score>=60;  
SELECT * FROM students WHERE score BRTWEEN 90 AND 60;  #模糊查询；
SELECT * FROM students WHERE firstname LIKE 'a%';  #like和通配符一起使用

3.排序查询：

#查询邮箱中含有e的员工信息，先按邮箱字节数降序，再按部门号升序；
SELECT *,LENGTH(email)  #如果是筛选时包括函数的注意要写在select后面；
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;

4.分组查询：

  • 1.和分组函数一同查询的字段必须是group by后面出现的字段；
  • 2.分组前查询：where：对原始表操作； 分组后查询：having：对分组后的虚拟结果表操作；
# 分组前筛选：常见关键字：每个**；
# 查询有奖金的每个领导手下员工的平均工资；   
SELECT AVG(salary),manager_id   #manager_id必须是groupby后面
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

# 分组后筛选：
# 每个工种有奖金的员工的最高工资>12000的工种编号和最高工资；
SELECT job_id,MAX(salary)
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id     #先查询有奖金的员工的最高工资；
HAVING MAX(salary)>12000   #分组完后再筛选；

5.连接查询

#传统模式：用where，其实就可以理解成在做一次筛选，完事后两个表合成了一个新表；是在做两个表的交集；
#查询有奖金的员工名、部门名；  
SELECT lastname,department_name,commission_pct
FROM employees e, department d    #一般都起别名；
WHERE e.department_id = d.department_id   #等值连接；
AND e.commission_pct IS NOT NULL;   

#99模式：	
    select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名 
	on 连接条件
#1.内连接：查询有奖金的员工名、部门名；
SELECT lastname,department_name
FROM employees e   
(INNER) JOIN department d
ON e.department_id=d.department;   #on后是连接条件，where后是筛选条件，实现分离；
WHERE e.commission_pct IS NOT NULL; 
#2.外连接：外连接的查询结果是主表中的所有记录=内连接结果+主表中有而从表中没有的，在连接条件上，如果从表上有匹配的，那就是匹配的值，如果没有匹配的，那就是null；常用在查询一个表中有，另一个表中没有的场景；      
#查询哪个部门没有员工  
SELECT d.*,e.employee_id
FROM department d    #这个最后其实会连接很多，拿着d里的每一行去匹配e表里的所有，所有可能会有1个部门很多员工的，
#所以在外连接里，并不一定查完之后表的大小和主表一模一样，只是说主表中的查完之后一定有；
LEFT OUTER JOIN employees e
ON d.department_id=e.department_id
WHERE e.employee_id IS NULL;
  • 左外连接：以左表为主表，可以查询左表存在但右表为null的记录。
  • 右外连接：以右表为记录，可以查询右表存在但左表为null的记录；
  • 内连接：查询左右表同时满足条件的记录，两边都不能为null；

6.子查询

一条查询语句中又嵌套了另一条完整的select语句

#查询谁的工资比Abel高？
SELECT salary
FROM employees
WHERE salary>(
    SELECT salary       #先查出Able的工资；
    FROM employees
    WHERE lastname = 'able'
);

7.分页查询

当要查询的数据，一页显示不全，需要分页时；

#查询第11条到第25条数据
SELECT * FROM employees LIMIT 10,15；  #10是起始，15是size；

2.2 DML语言

1.数据插入

INSERT INTO students(id, name, sex)
VALUES(12, '张三', '女')；   #可以不写字段，那就默认全部；

2.数据修改

#修改学生表中id为2的名称为李四
UPDATE students SET name = '李四'
WHERE id=2;

3.数据删除

#删除手机号为9的学生信息
DELETE FROM students WHERE phone LIKE '%9';
#删除学生表
TRUNCATE TABLE students;

2.3 DDL语言

1.库和表的管理

1.库的管理

#创建学生库
CREATE DATABASE IF NOT EXISTS students;
#更改学生库为老师库
ALTER DATABASE students TO teachers;
#删除学生库  
DROP DATABASE IF EXISTS students;

2.表的管理

#创建学生表
CREATE TABLE IF NOT EXISTS students(
    id INT,
    stuName VARCHAR(20),
    gender CHAR,
    born DATETIME
);            #字段之间加逗号，最后一个不用加，括号外面要分号；
DESC students;   #查看表；
#修改表名
ALTER TABLE students RENAME studentInfo;
#删除表
DROP TABLE IF EXISTS students;

#修改学生表  
#1.修改字段名
ALTER TABLE student CHANGE COLUMN gender sex CHAR;
#2.修改列类型或约束
ALTER TABLE student MODIFY COLUMN born DATA;  
#3.添加字段
ALTER TABLE student ADD COLUMN email VARCHAR(20);
#4.删除字段
ALTER TABLE student DROP COLUMN email;

库和表通用的创建和删除

DROP DATABASE/TABLE IF EXISTS       旧表名/旧库名
CREATE DATABASE/TABLE IF NOT EXISTS 新表名/新库名

2.数据类型

整型：INT
小数：DOUBLE
字符型：CHAR(M), VARCHAR(M) M表示最大字符串；
日期型：DATATIME;

3.常见约束

NOT NULL: 非空；保证字段值不为空；
DEFAULT: 该字段有默认值；
UNIQUE: 唯一，可以为空；比如座位号；
PRIMARY KEY: 主键，保证唯一且非空；比如编号；
FOREIGN KEY: 外键，用于限制两个表的关系；

CREATE TABLE student(
    id, INT PRIMARY KEY,
    student VARCHAR(20) NOT NULL UNIQUE, 
    seat INT UNIQUE,
    age INT DEFAULT 18,
    majorId INT
);

2.4 TCL语言

1.事务的概述

事务：一个或一组sql语句组成一个执行单元，要么全部执行，要么全部不执行；

问：事务的特性？

(ACID)
原子性：事务不可分割，要么都执行，要么都不执行；
一致性：保证事务的状态在操作前和操作后保持一致；(主要是对约束而言，比如余额的约束是大于0，比如90，要转出去100，显然不能成功，转完之后就不满足约束了，破坏了一致性；)
隔离性：一个事务的执行不受其他事务的干扰；
持久性：一个事务一旦提交，会永久改变数据库的数据；

步骤：

  1. 开启事务：set autocommit=0;
  2. 编写一组逻辑单元； 增删改查；
  3. 结束事务：commit(提交事务)； rollback(回滚事务)

2.事务的隔离级别

当多个事务同时操作同一个数据库的相同数据时，就会产生并发问题；

  • 脏读：T1读取到了已经被T2更新但是还没有提交的数据，那这之后，如果T2回滚，那T1读的数据就是无效的；
  • 不可重复读：T1已经读取了一个数据，然后T2更新并且提交了该数据，之后T1再次读的时候值就不一样了；
  • 幻读：T1读取了一个表，之后T2在表里插入了新的行，进行了更新，之后T1再次读的时候，就会多出几行；
  • 不可重复读多指的是修改某条记录，而幻读重点指的增加或删除记录；

问：四种隔离级别？

  • READ UNCOMMITTED(读未提交)：允许事务读取还没有被其他事务提交的数据，脏读、不可重复读、幻读都会出现；
  • REAT COMMITTED(读已提交)：只允许事务读取已经被其他事务提交的变更，可以避免脏读，但不可重复读和幻读仍然会存在；
  • REPEATABLE READ(可重复读)：确保事务可以多次从一个字段中读取相同的值，在这个事务读取的过程中，禁止其他事务对这个字段进行更新，可以避免脏读和不可重复读，但是幻读仍然存在；
  • SERIALIZABLE(串行化)：确保一个事务可以从一个表中读取相同的行，在这个事务持续期间，禁止其他事务对该表的插入更新删除操作，并发问题都可以避免，但是效率很低；

3.数据库基础

问：drop、truncate、delete的区别？

  • delete是DML，也就是数据操作语言，主要是用来删除数据的，执行的时候，从表中删除满足条件的行，同时记录该操作，为了以后进行回滚；
  • truncate是DDL，数据定义语言，会直接隐式的提交，不能回滚，主要是用来操作数据表的；
  • drop也是DDL数据定义语言，会隐式提交，不能回滚，会删除表结构，并且把表所占用的空间释放；

在速度上，drop>truncate>delete; 在删除部分数据的时候用delete，一般都要带上where语句，if想删除所有数据保留表，那可以用truncate，if想把整个表删除，用drop；

问：关系型数据库和非关系型数据库？

  • 关系型数据库是采用了关系模型来组织数据的数据库，是一种二维表格；很容易理解，而且存储在磁盘上，掉电不丢失，缺点是当在高并发的时候，关系型数据库的IO操作是很大的消耗；比如常见的Oracle、mysql
  • 非关系型数据库是非关系的，分布式的，不严格遵守ACID的原则，结构并不固定，常见的以键值对来存储，比如redis；非关系型数据库存储在内存中，所以效率很高；

问：数据库三大范式？

  • 第一范式：表中的字段具有原子性，不可再分割；
  • 第二范式：表中的每列都和主键有关，不能只和主键的一部分有关；
  • 第三范数：表中每列都和主键直接相关，不是相互传递过来的相关。比如学号和学院号； 满足第三范式一定满足第二范式，满足第二范式一定满足第一范式；

问：Mysql中的锁机制？

锁的话就是计算机协调多个进程或者线程并发访问某一资源的机制。在数据库中，数据是需要很多用户共享的资源，所以要保证数据在并发访问的一致性，所以在mysql中需要用到锁；

  • 全局锁：也就是对整个数据库进行加锁，比如说需要对整个数据库备份的时候；
  • 表级锁：开销小，加锁比较快，不会出现死锁的问题，锁定的是整个表，粒度大，发生锁冲突的概率最高，并发度最低；比如MyISAM就是支持表级锁，在Mysql里有两种：一种是表锁，一种是元数据锁(meta data lock,MDL);
表锁：lock tables … read/write；

例如lock tables t1 read, t2 write; 命令，则其他线程写 t1、读写 t2   
的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2   
的操作。连写 t1 都不允许，自然也不能在unlock tables之前访问其他表。

元数据锁：MDL 不需要显式使用，在访问一个表的时候会被自动加上，   
在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，   
加 MDL读锁；当要对表做结构变更操作的时候，加 MDL 写锁。
  • 行级锁：开销大，加锁比较慢，会出现死锁的问题，锁定的是单行，粒度小，发生锁冲突的概率最低，并发度也最高；只有InnoDB支持行级锁；
行级锁不是直接锁的数据，而是锁的索引，    
if是主键索引，则直接锁定这条主键索引，    
if是非主键索引，那就先锁定非主键索引，然后再锁住主键索引；
  • 页面锁：开销和加锁时间介于表锁和行锁之间，会出现死锁，锁定粒度在表锁和行锁之间，并发度一般；

问：数据库单表记录数过大时，优化措施？

  • 限定数据的范围：查找的时候要有范围：比如查最近1个月的订单；
  • 垂直分区：也就是拆分数据表，将数据列进行拆分，把一个表拆成多张表；
  • 水平分区：按行拆分；

问：一条SQL语句执行很慢的原因？

这个问题其实要看具体情况，比如可能有两种情况，一是大多数情况下是正常的，偶尔很慢，二是这条语句一直都很慢；

针对偶尔很慢的情况：

  • 数据库在刷新脏页，也就是将数据同步到磁盘中；
当往数据库中插入或者是更新数据的时候，会在内存中将数据更新，但是并不会马上同步到磁盘中   
而是把这些更新的记录写到redo log中去，等到空闲的时候，再通过redo log去把数据同步到磁盘中；    
if内存数据页和磁盘数据页不一样的时候，就把这个页称为：脏页；   
比如redo log一直在写，最后存满了，还没把更新同步到磁盘中，那这时候就只能先暂停了； 
先停下来，得等人家把数据同步完再说，所以语句可能就执行的比较慢；
  • 拿不到数据表的锁，比如要访问的数据表或者行别人也在用，并且加锁了，这条语句拿不到锁，就只能暂时等着别人释放锁；

针对一直很慢的情况：

  • 没有用上索引：比如要查询的字段在=或者>左边进行了运算或者函数，是不会用到索引的；如 select * from t where c+1 = 1000; 这时候没有用到索引，必须是 c=1000-1；

Redis

Redis是一种用C语言编写的key-value存储形式的数据库，是非关系型的；
这里面的value可以是多种类型，可以使用type进行查看，比如字符串、哈希、列表、集合、有序集合；Redis的数据是存在内存中的，所以是一种内存数据库，其读写速度很快，经常被用作缓存。

问：Redis的数据类型？

  • string
    概念：键是字符串，值可以是字符串（JSON，XML）、数字（整形、浮点数）、二进制（图片、音频、视频），最大不超过 512 MB。
    命令：set、get、setex、setnx、mset、mget、incr、decr。
    内部编码：① int（< 8B）。② embstr（不大于 39 字节）。③ raw（大于 39 字节）。
    应用场景：
    ① 缓存：Redis 作为缓存，MySQL 作为存储层，首先从 Redis 获取数据，如果失败就从 MySQL 获取并将结果写回 Redis 并添加过期时间。
    ② 计数：Redis可以实现快速计数功能，例如视频每播放一次就用 incr 把播放数加 1。
    ③ 共享 Session：一个分布式 Web 服务将用户的 Session 信息保存在各自服务器，但会造成一个问题，出于负载均衡的考虑，分布式服务会将用户的访问负载到不同服务器上，用户刷新一次可能会发现需要重新登陆。为解决该问题，可以使用 Redis 将用户的 Session进行集中管理，每次用户更新或查询登录信息都直接从 Redis 获取。

  • hash
    概念：键值本身又是一个键值对结构，哈希类型中的映射关系叫 field-value， value 是指 field 对应的值而不是键对应的值。
    命令：hset、hget、hdel、hlen、hexists。
    内部编码：① ziplist（field <= 512 且 value <= 64B）。② hashtable（field > 512 或 value > 64B）。

  • list
    概念：存储多个有序字符串，每个字符串称为元素，一个列表最多可以存储 232-1个元素。可以对列表两端插入和弹出，还可以获取指定范围的元素列表、获取指定索引的元素等。列表是一种比较灵活的数据结构，可以充当栈和队列，在实际开发中有很多应用场景。
    list 有两个特点：
    ①元素有序，可以通过索引获取某个元素或某个范围的元素。
    ② 元素可以重复。
    命令：lpush、rpop、lrange、lindex、llen。
    内部编码：① ziplist（key <= 512 且 value <= 64B）。② linkedlist（key > 512 或 value > 64B）。③ quicklist。
    应用场景：lpush + lpop = 栈、lpush + rpop = 队列、lpush + ltrim = 优先集合、lpush + brpop = 消息队列。

  • set

概念：保存多个字符串元素，和 list 不同的是集合不允许有重复元素，并且集合中的元素是无序的，不能通过索引下标获取元素。一个集合最多可以存储 232-1 个元素。Redis 除了支持集合内的增删改查，还支持多个集合取交集、并集、差集。
命令：sadd、sremove、scard、sismember、spop。
内部编码包括：① intset（key <= 512 且 element 是整数）。② hashtable（key > 512 或 element 不是整数）。
应用场景：sadd = 标签、spop = 生成随机数，比如抽奖、sinter = 社交需求。

  • zet

概念：有序集合保留了集合不能有重复成员的特性，不同的是可以排序。但是它和 list 使用索引下标作为排序依据不同的是，他给每个元素设置一个分数（score）作为排序的依据。有序集合提供了获取指定分数和元素查询范围、计算成员排名等功能。
命令：zadd、zremove、zscore、zrank、zcount。
内部编码：① ziplist（key <= 128 且 member <= 64B）。② skiplist（key > 128 或 member > 64B）。
应用场景：有序集合的典型使用场景就是排行榜系统，例如用户上传了一个视频并获得了赞，可以使用 zadd 和zincrby。如果需要将用户从榜单删除，可以使用zrem。如果要展示获取赞数最多的十个用户，可以使用 zrange。
具体5种类型使用可查看此博客

问：为什么要用Redis和缓存？

使用缓存的主要目的是为了提升用户体验，能够应对更多用户；主要从高性能和高并发两个方向进行考虑：

  • 高性能：if用户每次都是从磁盘中读数据的话，这个过程比较慢，所以可以将经常访问的高频数据且不容易发生改变的存在缓存里，这样就可以直接从缓存中操作，速度更快；需要注意的点就是要保证数据库和缓存中的数据一致性；
  • 高并发：直接操作缓存能够承受的数据库请求数量远远大于直接访问数据库的；每秒的查询次数，也就是QPS能够大幅提高；

问：Redis的持久化？

redis是支持持久化的，提供了两种持久化的方法，分别是RDB(Redis DataBase)快照持久化和AOF(Append Only File)追加文件持久化;

  • RDB意思就是说在不同的时间点，将redis存储的数据生成快照并存储在磁盘等介质中；if需要大规模数据的恢复，并且对数据完整性要求不高，RDB方式更加高效；但是if 对数据完整性非常敏感，就不太合适了，比如5分钟持久化一次，ifredis故障了，仍然会有5分钟的数据丢失；
  • AOF就是把redis执行过的所有指令记录下来，在下次redis重新启动时把这些指令从前到后重复执行一遍，然后实现数据恢复；

4.常见sql题

查询每门课都大于80分的学生姓名

select name
from student
group by name
having min(score)>80

删除除了自动编号外不同，其他都相同的学生冗余信息

//先拿除了自动编号外的字段进行分组，
//然后获得里面的最小编号，然后再进行子查询
//也就是将不在这些编号里的数据删除
delete student 
where 自动编号 not in
(select min(自动编号) from student group by学号、姓名)
Tags:

VirMach 便宜 VPS

QNews

QNews