数据库

优化

EXPLAIN

命令的输出结果包含以下列：

id：查询的标识符。
select_type：查询类型，例如 SIMPLE（简单查询）、PRIMARY（主查询）、SUBQUERY（子查询）等。
table：查询涉及的表名。
type：访问类型，反映了MySQL如何访问表中的数据。常见的访问类型有：ALL（全表扫描）、index（索引扫描）、range（范围扫描）、ref（使用非唯一索引扫描）、eq_ref（使用唯一索引扫描）等。通常，全表扫描和索引扫描的性能较差，而范围扫描和使用索引的查询性能较好。
possible_keys：可能使用的索引。
key：实际使用的索引。如果该列为空，表示没有使用索引。
key_len：实际使用的索引的长度。
ref：显示了哪些列或常量被用于查找索引列上的值。
rows：估计需要检查的行数。较小的行数通常意味着更高的查询性能。
filtered：表示返回结果的行数占需要检查行数的百分比。较高的百分比意味着更多的行符合查询条件。
Extra：提供了有关查询执行计划的其他信息，如“Using index”（使用索引）、“Using filesort”（使用文件排序）等。

all : 全表扫描（添加索引，提升查询效率）

ALTER TABLE user_table ADD index  idx_user_id (user_id)

慢日志查询

慢查询一般用于记录执行时间超过某个临界值的SQL语句的日志。

show variables  like '%slow_query_log%';

如何对慢查询进行优化？

分析语句的执行计划，查看SQL语句的索引是否命中
优化数据库的结构，将字段很多的表分解成多个表，或者考虑建立中间表。
优化LIMIT分页。

慢查询日志

# more  long_query.log 
# Time: 2023-04-05T08:47:45.868233-05:00
# User@Host: root[root] @ devstack [192.168.122.10]  Id:     9
# Query_time: 1.000723  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
use sakila;
SET timestamp=1680698865;
select sleep(1);

日志分析工具

mysqldumpslow long_query.log  # 进行了分类汇总，只显示汇总后摘要结果

Reading mysql slow query log from long_query.log
Count: 1  Time=10.00s (10s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select sleep(N) as a, N as b

Count: 1  Time=1.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@devstack
  select sleep(N)

https://www.51cto.com/article/709148.html

大表数据查询如何进行优化

索引优化
SQL语句优化
水平拆分
垂直拆分
建立中间表
使用缓存技术
固定长度的表访问起来更快
越小的列访问越快

数据量大如何优化

按需查询字段，减少网络IO消耗
避免使用select * ，减少MySQL优化器负担
查询的字段尽量保证索引覆盖
借助nosql缓存数据缓解MySQL数据库压力

偏移量越大优化方案

使用id限定

SELECT * FROM users LIMIT 100000, 100; # 扫描整个表，然后进行筛选和获取指定行数据。
# 优化
SELECT * FROM users where id > 100000 LIMIT 100;

如何优化WHERE子句

不要在where子句中使用!=和<>进行不等于判断，这样会导致放弃索引进行全表扫描。
不要在where子句中使用null或空值判断，尽量设置字段为not null。
尽量使用union all代替or
在where和order by涉及的列建立索引
尽量减少使用in或者not in，会进行全表扫描
在where子句中使用参数会导致全表扫描
避免在where子句中对字段及进行表达式或者函数操作会导致存储引擎放弃索引进而全表扫描

常用查询优化建议

优化查询的选择、连接和排序操作。
优化查询中使用的索引，包括创建新索引、删除无用索引、调整索引的顺序等。
优化查询中使用的表连接方式，包括内连接、外连接、自连接等。
优化查询中使用的子查询，包括对子查询进行优化、使用连接代替子查询等。
优化查询中使用的聚合函数，包括使用索引进行优化、使用分组连接代替聚合函数等。

联合索引的最左前缀原则

组合索引的最左前缀原则指的是，在创建组合索引时，应该将最常用于筛选数据的字段放在索引的最左侧，这样可以使索引更有效地帮助查询优化。

例如，如果有一张表中包含三个字段：A、B和C，并且频繁使用A和B这两个字段进行筛选数据，则应该将A和B作为组合索引的最左前缀，而不是C。

这样，在使用组合索引进行查询时，数据库系统就可以使用索引进行快速筛选，而不必扫描整张表。这有助于提高查询的效率。

SQL语句

select

子句执行顺序

FROM
JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT

select * from where uid>=2;

列是变量
变量可以计算
secelt shop_price-market_price from shop.
where是表达式值为真假。

select +[selet选项] + 字段列表 + from 数据源 + [where条件] + [1] + [2] + [3];

1:[group by ...]
2:[order by ...] desc ase
3:[limit ...] 2,8

[select选项]: all 默认
distinct 去重

字段别名
字段名 + as 别名

子查询是在索引上完成的，而普通的查询时在数据文件上完成的，通常来说，索引文件要比数据文件小得多，所以操作起来也会更有效率。

检索单个列：
select prod_name from products;
检索多个列：

检索不同的列：
distinct 放在列名前
select DISTINCT vend_id from products;
limit
select prod_name from products LIMIT 5,5; 从行5开始的5行，从0开始数
LIMIT 3,4 == LIMIT 4 offset 3

使用完全限定的表名：
select users.name from users;

子查询

SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM orders
WHERE orders.cust_id=customers.cust_id)
AS orders
FROM customers
ORDER BY cust_name;

联结表

vendors 只存储供应商信息
products 只存储产品信息，其中有供应商ID

供应商信息不重复，从而不浪费时间和空间。

等值联结(equl join)

SELECT vend_name, prod_name, prod_price 
FROM vendors, products 
WHERE vendors.vend_id= products.vend_id
ORDER BY vend_name, prod_name;

它会导致隐式连接，从而执行一个笛卡尔积，然后使用 WHERE 子句来过滤结果以匹配指定的连接条件。不建议使用

内部联结 inner join : 交集

内连接，也叫等值连接，inner join产生同时符合A和B的一组数据，默认的join

 SELECT vend_name, prod_name, prod_price
 FROM vendors INNER JOIN products
 ON vendors.vend_id = products.vend_id

联结多个表

  SELECT prod_name, vend_name, prod_price, quantity 
  FROM orderitems, products, vendors 
  WHERE products.vend_id = vendors.vend_id
  AND orderitems.prod_id = products.prod_id 
  AND order_num = 20005;

自联结

假如某物品(其ID为DTNTR)存在问题，因此想知道生产该物品的供应商其他物品是否也存在问题

SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
	AND p2.prod_id = 'DTNTR';

外部联结 (包含没有关联行的那些行)

left join:

（或left outer join:在Mysql中两者等价，推荐使用left join.）左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。

right join

列出所有用户及其订单

select customers.cust_id, orders.order_num  
from customers 
inner join orders 
on customers.cust_id = orders.cust_id;

包括没有订单的

SELECT customers.cust_id, orders.order_num  
FROM customers LEFT OUTER JOIN orders 
ON customers.cust_id = orders.cust_id;
使用带聚集函数的联结

select c.cust_name, c.cust_id, COUNT(o.order_num) AS num_ord 
FROM customers as c 
INNER JOIN orders as o 
ON c.cust_id = o.cust_id 
GROUP BY c.cust_id;

组合查询 UNION

需要价格小于等于5的所有物品的一个列表，而且需要供应商1001和1002生产的所有物品（不考虑价格）

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <=5 
   UNION 
SELECT vend_id,prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);
 -- 默认取消重复的行，不取消则使用UNIOIN ALL

SHOW

show columns from customeres; 显示表列
desc customers;

自动增量： auto_increment

show status；显示

show create database 和show create table; 显示创建特定的数据库或的的mysql语句。

show grants; 用来显示授予用户的安全权限

show errors; show warnings;

help show ;

排序检索数据

ORDER BY

子句(clause)
select name from users ORDER BY name;
按多个排序
	ORDER BY prod_price, prod_name;

指定排序方向：
	DESC  降序   ASC(ascending)

过滤数据

搜索条件 search criteria
过滤条件 filter condition

WHERE

相等测试： select name from users WHERE id=3;
数据也可以在应用层过滤
操作符：
=
<> 不等于
!= 不等于
<
<=
>
>=
BETWEEN

空值检查： NULL
WHERE price is NULL

数据过滤：

操作符： 
	AND
	OR  
		select name from products WHERE (id = 1002 OR id = 1003) AND price >= 10;
		AND在计算次序中优先级更高 
	IN  WHERE id IN (1002,1003) 完成与OR相同的功能，比OR更快
	NOT WHERE子句中用来否定后跟条件的关键字。

通配符进行过滤

LIKE操作符

通配符：
% : 任何字符出现任意次数。
WHERE name like 'jet%'
_ : 单个字符

正则表达式 REGEXP

WHERE name REGEXP '1000';    -- 与like的区别，like要name=1000, 而regexp是包含
	BINARY  以区分大小写 WHERE name REGEXP BINARY 'JetPack.000';
	   | 
	   []
	   \\-  
	   
   \\f  换页
   \\r  回车
   \\v  纵向制表 

   [:alnum:]   == [a-zA-Z0-9]
   [:alpha:]      [a-zA-Z]
   [:blank:]      [\\t]
   [:cntrl:]       ASCII控制字符(ASCII 0到31和127)
   [:digit:]      [0-9]
   [:lower:]      [a-z]
   [:print:]      可打印的字符 
   [:punct:]      既不在[:alnum:]又不在[:cntrl:]中的任意字符
   [:space:]       包括空格在内的任意字符 [\\f\\n\\r\\t\\v]
   [:upper:]       [A-Z]
   [:xdigit:]      任意十六进制数字同[a-fA-F0-9] 

定位符： 
	[[:<:]]    词的开始 
	[[:>:]]    词的结尾 

简单的正则表达式测试 
	select 'hello' REGEXP '[0-9]'

函数

拼接字段(concatenate)

Concat()函数
select concat(vend_name, '(', vend_country, ')') from vendors;

RTrim() 删除数据右侧多余的空格
LTrim()

使用别名(alias)  AS 
   concat(vend_name, '(', vend_country, ')') AS vend_title

测试计算:
select now();  可以返回时间  
select 3*3;

函数：
Left() 返回串左边的字符
Right() 返回串右边的字符
Length() 返回串的长度
Locate() 找出串的一个子串
Lower() 将串转换为小写

Soundex() 返回串的SOUNDEX值 (发音) Y.Lee Y.Lie
SubString() 返回子串的字符
Upper() 将串转换为大写

日期和时间处理函数:

AddDate() 增加一个日期(天、周等)
AddTime() 增加一个时间(时、分等)
CurDate() 返回当前日期
CurTime() 返回当前时间
Date() 返回日期时间的日期部分
DateDiff() 计算两个日期之差
Date_Add() 高度灵活的日期运算函数
Date_Format() 返回一个格式化的日期或时间串
Day() 返回一个日期的天数部分
DayOfWeek() 对于一个日期,返回对应的星期几
Hour() 返回一个时间的小时部分
Minute() 返回一个时间的分钟部分
Month() 返回一个日期的月份部分
Now() 返回当前日期和时间
Second() 返回一个时间的秒部分
Time() 返回一个日期时间的时间部分
Year() 返回一个日期的年份部分

数值处理函数

Abs() 返回一个数的绝对值
Cos() 返回一个角度的余弦
Exp() 返回一个数的指数值
Mod() 返回除操作的余数
Pi() 返回圆周率
Rand() 返回一个随机数
Sin() 返回一个角度的正弦
Sqrt() 返回一个数的平方根
Tan() 返回一个角度的正切

汇总数据

聚集函数(aggregate function)

AVG()    平均值
COUNT()  返回某列的行数 
MAX()	 
MIN()
SUM()

分组数据

GROUP BY

	ROLLUP:  GROUP BY vend_id WITH ROLLUP;  分组汇总

过滤分组HAVING

select cust_id, COUNT(*) AS orders from orders GROUP BY cust_id
			HAVING COUNT(*) >= 2;

全文本搜索

MyISAM支持，InnnoDB不支持

CREATE TABLE语句时接受FULLTEXT子句

CREATE TABLE productnotes(
  note_id int 		NOT NULL AUTO_INCREMENT,
  prod_id char(10)  NOT NULL,
  note_data datetime NOT NULL,
  note_text text    NULL,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
)ENGINE=MyISAM;


Match()  Against() 

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');

传递给Match()的值必须与FULLTEXT()定义中的相同，如果指定多个列，必须列出它们
不区分大小写

查询扩展： WITH QUERY EXPANSION

SELECT note_text FROM productnotes 
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

插入数据

INSERT INTO Customers VALUES(NULL,
	'Pep E. LaPew',
	'100 Main Street',
	'Los Angeles',
	'CA',
	'90046',
	'USA',
	NULL,
	NULL);

更安全:

INSERT INTO Customers(cust_name,
	cust_address,
	cust_city,
	cust_state,
	cust_zip,
	cust_country,
	cust_contact,
	cust_email)
VALUES(NULL,
	'Pep E. LaPew',
	'100 Main Street',
	'Los Angeles',
	'CA',
	'90046',
	'USA',
	NULL,
	NULL);

更新和删除数据

清空表:

truncate table TABLE_NAME;
truncate删除数据后是不可以rollback的
truncate删除数据后会重置Identity（标识列、自增字段）
truncate删除数据后不写服务器log，整体删除速度快
truncate删除数据后不激活trigger(触发器)

UPDATE customers 
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

DELETE FROM customers
WHERE cust_id = 10006;

视图

一个SQL查询组成的表。

创建

CREATE VIEW
SHOW CREATE VIEW viewname 

CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers,orders, orderitems
WHERE customers.cust_id = orders.cust_id
  AND orderitems.order_num = orders.order_num;

练习题

面试题

SQL题

--第十二题 查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select AVG(Degree) from Score where Cno like '3%' group by Cno having COUNT(Cno)>4
 --第十四题 查询所有学生的Sname、Cno和Degree列。
select Sname,Cno,Degree from student join Score on student.Sno=Score.Sno

--第十八题 假设使用如下命令建立了一个grade表：
create table grade(low int,upp int,rank char(1))
insert into grade values(90,100,'A')
...
select Sno,Cno,Degree,[RANK] from grade join Score on Score.Degree between low and upp

SELECT 
    name,
    CASE 
        WHEN score >= 90 THEN '优'
        WHEN score >= 80 THEN '良'
        WHEN score >= 60 THEN '合格'
        ELSE '不合格'
    END AS grade
FROM 
    student_scores;
    
-- 使用IF函数
SELECT 
    name,
    IF(score >= 90, '优', 
       IF(score >= 80, '良', 
          IF(score >= 60, '合格', '不合格'))) AS grade
FROM 
    student_scores;

面试题

范式(Normal Form)

第一范式（1NF）：确保每个数据表中的每个字段都是原子性的，不可再分。这意味着每个字段只能包含单一的数据，而不能包含多个值或重复的组合。比如，一个订单表中的产品字段应该是单一的产品编号，而不是一个包含多个产品编号的列表。

第二范式（2NF）：在满足第一范式的基础上，要求非主键字段完全依赖于候选键（即唯一标识一条记录的字段）。换句话说，每个非主键字段都必须完全依赖于候选键，而不是部分依赖。比如，如果一个表的候选键是订单号和产品编号的组合，那么订单表中的每个字段都必须与订单号和产品编号相关，而不是仅与订单号相关。

第三范式（3NF）：在满足第二范式的基础上，要求非主键字段之间不存在传递依赖关系。换句话说，非主键字段之间不能相互依赖，而是只依赖于主键或候选键。比如，如果一个表中的某个字段依赖于另一个非主键字段，而这个非主键字段又依赖于主键字段，那么就违反了第三范式。为了符合第三范式，需要将这种传递依赖关系拆分成独立的表（消除冗余）

SQL约束有哪些

主键约束：主键为在表中存在一列或者多列的组合，能唯一标识表中的每一行。一个表只有一个主键，并且主键约束的列不能为空。
外键约束：外键约束是指用于在两个表之间建立关系，需要指定引用主表的哪一列。只有主表的主键可以被从表用作外键，被约束的从表的列可以不是主键，所以创建外键约束需要先定义主表的主键，然后定义从表的外键。
唯一约束：确保表中的一列数据没有相同的值，一个表可以定义多个唯一约束。
默认约束：在插入新数据时，如果该行没有指定数据，系统将默认值赋给该行，如果没有设置没默认值，则为NULL。
Check约束：Check会通过逻辑表达式来判断数据的有效性，用来限制输入一列或者多列的值的范围。在列更新数据时，输入的内容必须满足Check约束的条件

脏读幻读

https://cloud.tencent.com/developer/article/1450773

脏读（读取未提交数据）

A事务读取B事务尚未提交的数据，此时如果B事务发生错误并执行回滚操作，那么A事务读取到的数据就是脏数据。就好像原本的数据比较干净、纯粹，此时由于B事务更改了它，这个数据变得不再纯粹。

不可重复读（前后多次读取，数据内容不一致）

事务A在执行读取操作，由整个事务A比较大，前后读取同一条数据需要经历很长的时间。而在事务A第一次读取数据，比如此时读取了小明的年龄为20岁，事务B执行更改操作，将小明的年龄更改为30岁，此时事务A第二次读取到小明的年龄时，发现其年龄是30岁，和之前的数据不一样了，也就是数据不重复了，系统不可以读取到重复的数据，成为不可重复读。

幻读（前后多次读取，数据总量不一致）

事务A在执行读取操作，需要两次统计数据的总量，前一次查询数据总量后，此时事务B执行了新增数据的操作并提交后，这个时候事务A读取的数据总量和之前统计的不一样，就像产生了幻觉一样，平白无故的多了几条数据，成为幻读。

(1) 不可重复读是读取了其他事务更改的数据，针对update操作

(2) 幻读是读取了其他事务新增的数据，针对insert和delete操作

隔离级别

大多数数据库的默认级别就是Read committed，比如Sql Server , Oracle。

mysql REPEATABLE-READ

Serializable 序列化（串行化）

SHOW VARIABLES LIKE 'tx_isolation'; -- 查看隔离级别

实现原理

	实现方式
RU	事务对当前被读取的数据不加锁；事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级共享锁，直到事务结束才释放。
RC	事务对当前被读取的数据加行级共享锁（当读到时才加锁），一旦读完该行，立即释放该行级共享锁；事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。
RR	事务在读取某数据的瞬间（就是开始读取的瞬间），必须先对其加行级共享锁，直到事务结束才释放；事务在更新某数据的瞬间（就是发生更新的瞬间），必须先对其加行级排他锁，直到事务结束才释放。
S	事务在读取数据时，必须先对其加表级共享锁，直到事务结束才释放；事务在更新数据时，必须先对其加表级排他锁，直到事务结束才释放。

MVCC（多版本并发控制）

英文全称为Multi-Version Concurrency Control，乐观锁为理论基础的MVCC（多版本并发控制），MVCC的实现没有固定的规范。每个数据库都会有不同的实现方式

mysql中，默认的事务隔离级别是可重复读（repeatable-read），为了解决不可重复读，innodb采用了MVCC（多版本并发控制）来解决这一问题。

MVCC是利用在每条数据后面加了隐藏的两列（创建版本号和删除版本号），每个事务在开始的时候都会有一个递增的版本号

例如：

使用 MVCC: T1 读取数据 A 的值时，会读取 T1 开始时的快照版本，T2 修改数据 A 的值时，会创建新的数据版本，但不会影响 T1 的快照版本，因此 T1 再次读取数据 A 的值时，仍然会读取 T1 开始时的快照版本，不会看到 T2 的修改。

优化查询效率

储存引擎选择：如果数据表需要事务处理，应该考虑使用 InnoDB，因为它完全符合 ACID 特性。
如果不需要事务处理，使用默认存储引擎 MyISAM 是比较明智的
分表分库，主从。
对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索
引
应尽量避免在 where 子句中对字段进行 null 值判断或参数、对字段进行表达式或函数操作，否则会导致权标扫描，否则将导致引擎放弃使用索引而进行全表扫描
应尽量避免在 where 子句中使45用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫
描
应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，
将导致引擎放弃使用索引而进行全表扫描
Update 语句，如果只更改 1、2 个字段，不要 Update 全部字段，否则频繁调用会引起明显的
性能消耗，同时带来大量日志
对于多张大数据量（这里几百条就算大了）的表 JOIN，要先分页再 JOIN，否则逻辑读会很高，
性能很差。
小表驱动大表

很多时候可考虑用 exists 代替 in

https://www.cnblogs.com/xuwc/p/14059032.html

in后面跟的是小表，exists后面跟的是大表。

# 使用exists时会先进行外表查询，将查询到的每行数据带入到内表查询中看是否满足条件
SELECT * FROM table1 WHERE table1.col in (select table2.col from table2);
SELECT * FROM table1 WHERE EXISTS (
    SELECT 1 FROM table2 WHERE table1.column_name = table2.column_name);

# 当子查询返回的数据量较大时，EXISTS 比 IN 运算符效率更高，因为它只需要检查子查询是否返回任何行，而不需要将所有结果返回到主查询。

尽量使用数字型字段。
尽可能的使用 varchar/nvarchar 代替 char/nchar。
任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
尽量使用表变量来代替临时表。
避免频繁创建和删除临时表，以减少系统表资源的消耗。
尽量避免使用游标，因为游标的效率较差。
在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET
NOCOUNT OFF。
尽量避免大事务操作，提高系统并发能力。
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
使用视图加速查询
能够用 BETWEEN 的就不要用 IN
DISTINCT 的就不用 GROUP BY
能用 UNION ALL 就不要用 UNION (union会去重 )
用连接代替子查询
使用limit

数据库的优化

1.优化索引、SQL 语句、分析慢查询；
2.设计表的时候严格根据数据库的设计范式来设计数据库；
3.使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘 IO
4.优化硬件；采用 SSD，使用磁盘队列技术(RAID0,RAID1,RDID5)等
5.采用 MySQL 内部自带的表分区技术，把数据分层不同的文件，能够提高磁盘的读取效率；
6.垂直分表；把一些不经常读的数据放在一张表里，节约磁盘 I/O；
7.主从分离读写；采用主从复制把数据库的读操作和写入操作分离开来；flask-sqlalchemy-plus
8.分库分表分机器（数据量特别大），主要的的原理就是数据路由；
9.选择合适的表引擎，参数上的优化
10.进行架构级别的缓存，静态化和分布式；
11.不采用全文索引；
12.采用更快的存储方式，例如 NoSQL 存储经常访问的数据**。

聚簇索引与非聚簇索引

聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据

InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引

使用最多的数据库（Mysql，Mongodb，redis 等），对他们的理解？

MySQL 数据库：开源免费的关系型数据库，需要实现创建数据库、数据表和表的字段，表与表之
间可以进行关联（一对多、多对多），是持久化存储。
Mongodb 数据库：是非关系型数据库，数据库的三元素是，数据库、集合、文档，可以进行持久
化存储，也可作为内存数据库，存储数据不需要事先设定格式，数据以键值对的形式存储。
redis 数据库：非关系型数据库，使用前可以不用设置格式，以键值对的方式保存，文件格式相对自
由，主要用与缓存数据库，也可以进行持久化存储。

SQL 查询处理的步骤序号

(1) FROM <left_table>
(2) <join_type> JOIN <right_table>
(3) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(8) SELECT
(9) DISTINCT
(9) ORDER BY <order_by_list>
(10) <TOP_specification> <select_list>

以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用
者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在
查询中指定某一个子句，将跳过相应的步骤。

Mysql 集群的优缺点

优点：
99.999%的高可用性
快速的自动失效切换
灵活的分布式体系结构，没有单点故障
高吞吐量和低延迟
可扩展性强，支持在线扩容

缺点：
存在很多限制，比如：不支持外键
部署、管理、配置很复杂
占用磁盘空间大、内存大
备份和恢复不方便
重启的时候，数据节点将数据 load 到内存需要很长的时间

Mysql 各引擎之间有什么区别

主要 MyISAM 与 InnoDB 两个引擎，其主要区别如下：
InnoDB 支持事务，MyISAM 不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一
些列增删改中只要哪个出错还可以回滚还原，而 MyISAM 就不可以了；
MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用；
InnoDB 支持外键，MyISAM 不支持；
MyISAM 是默认引擎，InnoDB 需要指定；
InnoDB 不支持 FULLTEXT 类型的索引；
InnoDB 中不保存表的行数，如 select count() from table 时，InnoDB；需要扫描一遍整个表来
计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。注意的是，当 count()语句包含
where 条件时 MyISAM 也需要扫描整个表；

对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM 表中可以和其他字
段一起建立联合索引；清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建
表；
InnoDB 支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

Mysql 数据库如何分区、分表

分表可以通过三种方式：Mysql 集群、自定义规则和 merge 存储引擎。

分区有四类：

RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST 分区：类似于按 RANGE 分区，区别在于 LIST 分区是基于列值匹配一个离散值集合中的某个
值来进行选择。
HASH 分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的
这些行的列值进行计算。这个函数可以包含 MySQL 中有效的、产生非负整数值的任何表达式。
KEY 分区：类似于按 HASH 分区，区别在于 KEY 分区只支持计算一列或多列，且 MySQL 服务器
提供其自身的哈希函数。必须有一列或多列包含整数值。

MySQL中的update是行锁还是表锁

如果where条件包含索引列，并且只更新一条数据，行锁
不包含表锁
select * from t where id = 10 for update; 会增加一个Next-Key Lock来锁定id=10索引所在的区间
select * from user where id BETWEEN 1 AND 100 FOR UPDATE; 自动对索引加间隙锁来解决幻读问题

Next-Key Lock： 这是一个组合锁，它包含了索引值和间隙，即锁住了索引值本身和该索引值与下一个索引值之间的间隙。

Gap Lock： 只锁住索引值之间的间隙。

Redis

Redis 的并发竞争问题怎么解决?

方案一：可以使用独占锁的方式，类似操作系统的 mutex 机制，不过实现相对复杂，成本较高。

方案二：使用乐观锁的方式进行解决（成本较低，非阻塞，性能较高）

如何用乐观锁方式进行解决？
本质上是假设不会进行冲突，使用 redis 的命令 watch 进行构造条件

Redis 和 MongoDB 的优缺点

MongoDB 和 Redis 都是 NoSQL，采用结构型数据存储。二者在使用场景中，存在一定的区别，
这也主要由于二者在内存映射的处理过程，持久化的处理方法不同。MongoDB 建议集群部署，更多
的考虑到集群方案，Redis 更偏重于进程顺序写入，虽然支持集群，也仅限于主-从模式。
Redis 优点：
a) 读写性能优异
b) 支持数据持久化，支持 AOF 和 RDB 两种持久化方式
c) 支持主从复制，主机会自动将数据同步到从机，可以进行读写分离。
d) 数据结构丰富：数据结构丰富：支持 string、hash、set、sortedset、list 等数据结构。
缺点：
e) Redis 不具备自动容错和恢复功能，主机从机的宕机都会导致前端部分读写请求失败，需要
等待机器重启或者手动切换前端的 IP 才能恢复。
f)
主机宕机，宕机前有部分数据未能及时同步到从机，切换 IP 后还会引入数据不一致的问题，
降低了系统的可用性。

Redis 默认端口，默认过期时间，Value 最多可以容纳的数据长度

6379，

默认过期时间：可以说永不过期，一般情况下，当配置中开启了超出最大内存限制就写磁盘的
话，那么没有设置过期时间的 key 可能会被写到磁盘上。假如没设置，那么 REDIS 将使用
LRU 机制，将内存中的老数据删除，并写入新数据。

Value 最多可以容纳的数据长度是：512M。

Redis 有多少个库

Redis常见面试题

Redis

数据类型(5种)

类型常量	对象的名称
`REDIS_STRING`	字符串对象 set,get,decr,incr,mget
`REDIS_LIST`	列表对象 lpush,rpush,lpop,rpop,lrange
`REDIS_HASH`	哈希对象 hget,hset,hgetall
`REDIS_SET`	集合对象 sadd,spop,smembers,sunion
`REDIS_ZSET`	有序集合对象 zadd,zrange,zrem,zcard

底层数据结构共有八种，如下表所示：

编码常量	编码所对应的底层数据结构
REDIS_ENCODING_INT	long 类型的整数
REDIS_ENCODING_EMBSTR	embstr 编码的简单动态字符串
REDIS_ENCODING_RAW	简单动态字符串
REDIS_ENCODING_HT	字典
REDIS_ENCODING_LINKEDLIST	双端链表
REDIS_ENCODING_ZIPLIST	压缩列表
REDIS_ENCODING_INTSET	整数集合
REDIS_ENCODING_SKIPLIST	跳跃表和字典

列表对象

ziplist是一种压缩链表，它的好处是更能节省内存空间，因为它所存储的内容都是在连续的内存区域当中的。当列表对象元素不大，每个元素也不大的时候，就采用ziplist存储。但当数据量过大时就ziplist就不是那么好用了。因为为了保证他存储内容在内存中的连续性，插入的复杂度是O(N)，即每次插入都会重新进行realloc。如下图所示，对象结构中ptr所指向的就是一个ziplist。整个ziplist只需要malloc一次，它们在内存中是一块连续的区域。
————————————————

linkedlist是一种双向链表。它的结构比较简单，节点中存放pre和next两个指针，还有节点相关的信息。当每增加一个node的时候，就需要重新malloc一块内存。

哈希对象的底层实现可以是ziplist或者hashtable

查看所有的键：
keys *
del [key]
flushdb //清空当前缓存
flushall // 清空所有缓存

Redis 为什么快

Redis 内部做了非常多的性能优化，比较重要的有下面 3 点：

Redis 基于内存，内存的访问速度比磁盘快很多；
Redis 基于 Reactor 模式设计开发了一套高效的事件处理模型，主要是单线程事件循环和 IO 多路复用（Redis 线程模式后面会详细介绍到）；
Redis 内置了多种优化过后的数据类型/结构实现，性能非常高。
Redis 通信协议实现简单且解析高效。

Sentinel(哨兵)

主要功能列表

监控(Monitoring)

Sentinel 持续检查集群中的 master、slave 状态，判断是否存活

连接是否正常，是否响应命令、是否处于正常运行状态

故障通知(Notification)

在发现某个 Redis 实例死的情况下，Sentinel 能通过 API 通知系统管理员或其
他程序脚本。

故障转移(Automatic failover)

如果一个 master 挂掉后，sentinel 立马启动故障转移，把某个 slave 提
升为 master。其他的 slave 重新配置指向新 master。

配置管理(Configuration provider)

对于客户端来说 sentinel 通知是有效可信赖的。客户端会连接
sentinel 去请求当前 master 的地址，一旦发生故障 sentinel 会提供新地址给客户端。

哨兵的工作原理

哨兵集群：哨兵自身也是一个分布式系统，由多个哨兵实例组成，共同监控 Redis 实例。
主节点和从节点： Redis 集群通常包含一个主节点和多个从节点。
投票机制：当主节点出现故障时，哨兵会进行投票，只有获得多数票的哨兵才能将从节点提升为主节点。

哨兵的优势

高可用性：哨兵可以确保 Redis 集群即使在主节点故障时也能正常运行。
自动故障转移：哨兵可以自动将从节点提升为主节点，无需人工干预。
集群管理：哨兵可以管理 Redis 集群的配置，简化运维工作。

数据持久化

RDB(Redis DataBase)

在指定的时间间隔内将内存中的数据集快照写入磁盘。
save
bgsave
自动保存 redis.conf

save 900 1
dbfilename dump.rdb

优点:

全量备份，非常适合用于进行备份和灾难恢复；恢复速度快
对性能影响较小: RDB 持久化是通过子进程进行的，对主进程的影响相对较小。

缺点:一旦发生故障停机，你就可能会丢失好几分钟的数据，在数据集比较庞大时， fork() 可能会非常耗时

AOF(Append Only File)：命令追加到文件中

always: 同步持久化，每次发生数据变更会被同步
everysec：异步操作，每秒记录
no: 从不同步

appendonly yes
appendfsync always

优点：Redis 可以在 AOF 文件体积变得过大时，自动地在后台对 AOF 进行重写

缺点：

AOF 文件的体积通常要大于 RDB 文件的体积
恢复速度慢
对性能影响较大: AOF 持久化策略的选择会影响 Redis 性能，例如，每秒同步策略会降低 Redis 的吞吐量。

使用 Redis 附带的 redis-check-aof 程序，对原来的 AOF 文件进行修复。

事务

resdis事务

redis事务就是一次性、顺序性、排他性的执行一个队列中的一系列命令。不支持回滚

MULTI ：开启事务，redis会将后续的命令逐个放入队列中，然后使用EXEC命令来原子化执行这个命令系列。
EXEC：执行事务中的所有操作命令。
DISCARD：取消事务，放弃执行事务块中的所有命令。
WATCH：监视一个或多个key,如果事务在执行前，这个key(或多个key)被其他命令修改，则事务被中断，不会执行事务中的任何命令。
UNWATCH：取消WATCH对所有key的监视。

存在语法错误的情况下，所有命令都不会执行
存在运行错误的情况下，除执行中出现错误的命令外，其他命令都能正常执行

过期策略

TTL（Time To Live）：设置键的过期时间，到期后自动删除。
MAXIDLE：设置键的最大空闲时间，如果在指定时间内没有被访问，则自动删除。
MAXMEMORY：设置最大内存占用，当内存使用量超过限制时，会根据配置策略删除部分键。

应用场景

分布式锁：通过 Redis 来做分布式锁是一种比较常见的方式。通常情况下，我们都是基于 Redisson 来实现分布式锁。关于 Redis 实现分布式锁的详细介绍，可以看我写的这篇文章：分布式锁详解。
限流：一般是通过 Redis + Lua 脚本的方式来实现限流。如果不想自己写 Lua 脚本的话，也可以直接利用 Redisson 中的 RRateLimiter 来实现分布式限流，其底层实现就是基于 Lua 代码+令牌桶算法。
消息队列：Redis 自带的 List 数据结构可以作为一个简单的队列使用。Redis 5.0 中增加的 Stream 类型的数据结构更加适合用来做消息队列。它比较类似于 Kafka，有主题和消费组的概念，支持消息持久化以及 ACK 机制。
延时队列：Redisson 内置了延时队列（基于 Sorted Set 实现的）。
分布式 Session ：利用 String 或者 Hash 数据类型保存 Session 数据，所有的服务器都可以访问。
复杂业务场景：通过 Redis 以及 Redis 扩展（比如 Redisson）提供的数据结构，我们可以很方便地完成很多复杂的业务场景比如通过 Bitmap 统计活跃用户、通过 Sorted Set 维护排行榜。

提高 Redis 的性能

选择合适的持久化策略：根据数据量和性能需求选择合适的持久化策略。
使用合适的缓存策略：选择合适的缓存策略，例如 LRU（Least Recently Used）或 FIFO（First In First Out）。
优化数据结构：选择最适合的数据结构，例如使用 Set 存储唯一元素，使用 List 存储有序元素。
避免慢查询：减少复杂查询，使用索引等方法优化查询速度。
使用合适的连接池：使用连接池可以减少连接建立和销毁的开销。

https://javaguide.cn/database/redis/redis-questions-01.html

Redis6.0 引入多线程主要是为了提高网络 IO 读写性能，因为这个算是 Redis 中的一个性能瓶颈（Redis 的瓶颈主要受限于内存和网络）。

虽然，Redis6.0 引入了多线程，但是 Redis 的多线程只是在网络数据的读写这类耗时操作上使用了，执行命令仍然是单线程顺序执行。因此，你也不需要担心线程安全问题。

Redis6.0 的多线程默认是禁用的，只使用主线程。如需开启需要设置 IO 线程数 > 1，需要修改 redis 配置文件 redis.conf：

io-threads 4

redis生产问题

缓存穿透

大量请求的 key 是不合理的，根本不存在于缓存中，也不存在于数据库中，这就导致这些请求直接到了数据库上，根本没有经过缓存这一层，对数据库造成了巨大的压力，可能直接就被这么多请求弄宕机了。

缓存击穿

请求的 key 对应的是 热点数据 ，该数据 存在于数据库中，但不存在于缓存中（通常是因为缓存中的那份数据已经过期） 。这就可能会导致瞬时大量的请求直接打到了数据库上，对数据库造成了巨大的压力，可能直接就被这么多请求弄宕机了。

提前预热（推荐）：针对热点数据提前预热，将其存入缓存中并设置合理的过期时间比如秒杀场景下的数据在秒杀结束之前不过期。

缓存雪崩

缓存在同一时间大面积的失效(如：数据库中的大量数据在同一时间过期)，导致大量的请求都直接落到了数据库上，对数据库造成了巨大的压力

python操作redis

pip install redis

# 这种连接是连接一次就断了，耗资源.端口默认6379，就不用写
r = redis.Redis(host='127.0.0.1',port=6379,password='tianxuroot')
r.set('name','root')
print(r.get('name').decode('utf8'))
'''
连接池：
当程序创建数据源实例时，系统会一次性创建多个数据库连接，并把这些数据库连接保存在连接池中，当程序需要进行数据库访问时，
无需重新新建数据库连接，而是从连接池中取出一个空闲的数据库连接
'''
pool = redis.ConnectionPool(host='127.0.0.1',password='helloworld')   #实现一个连接池
r = redis.Redis(connection_pool=pool)
r.set('foo','bar')
print(r.get('foo').decode('utf8'))

#String操作
set(name, value, ex=None, px=None, nx=False, xx=False)
get(name)

mset() #批量设置
mset(k1='v1', k2='v2')
mget({'k1': 'v1', 'k2': 'v2'})

# Hash
hset(name,key,value)Hash

# List
lpush(name, values) # lpush("oo", 11,22,33)
lpushx(name, value) # 左边添加
rpushx(name, value) # 右边添加

lindex(name,index)
lpop()

# set
sadd(name, values)
spop(name)

#有序集合
zadd(name)

管道

redis-py默认在执行每次请求都会创建（连接池申请连接）和断开（归还连接池）一次连接操作，如果想要在一次请求中指定多个命令，则可以使用pipline实现一次请求指定ii多个命令，并且默认情况下一次pipline 是原子性操作

pipe = r.pipeline(transaction=True)
pipe.set('name', 'root')
pipe.set('role', 'root')

pipe.execute()

发布-订阅

pool = redis.ConnectionPool(host='192.168.99.100', port=6739, db=0)
r=redis.Redis(connection_pool=pool)
r.publish('mychanel','hello everyone')

pool = redis.ConnectionPool(host='192.168.99.100', port=6739, db=0)
r=redis.Redis(connection_pool=pool)
p = r.pubsub() #打开订阅功能
p.subscribe(['mychanel']) # 订阅关注的频道，可以是多个
#p.parse_response() 阻塞的，只有当收到消息时才结束

for i in p.listen():
	print i.get('data')

MySQL

B树与B+树

平衡的多叉树

磁盘IO, 预读, 一页， 4k,8k

事务

原子性(Atomicity)：事务中的全部操作在数据库中是不可hh分割的，要么全部完成，要么均不执
行。
一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相
一致。
隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透
明的。
持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即
使数据库出现故障

1.原子性
begin;
...
commit;

2.隔离性：
同时修改: 加互斥锁, 只有第一个事件commit后，才会释放锁

幻读： 事务1提交了
脏读： 事务1回滚了

shell 执行sql语句

1.将SQL语句直接嵌入到shell脚本中

mysql -uroot -p123 -e "use test;"

2.命令行调用单独的SQL文件

mysql -uroot -p123 -e "source t.sql"

3.使用管道符调用SQL文件

mysql -uroot -p123 < t.sql

mysql -uroot -p123 << EOF
source t.sql;
select * from users;
EOF

mysql 导入导出sql文件

导出：

# 1.导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u dbuser -p dbname > dbname.sql

# 2.导出一个表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u dbuser -p dbname users> dbname_users.sql

# 3.导出一个数据库结构
mysqldump -u dbuser -p -d --add-drop-table dbname > dbname_db.sql
-d 没有数据 --add-drop-table 在每个create语句之前增加一个drop table

mysqldump -u用户名 -p密码 -h mysql主机  --default-character-set=指定编码  数据库名称  表名称  --where=" 查询条件 " > 导出文件名.sql

mysqldump -uroot -p123456 -h 192.168.1.123  --default-character-set=utf8 mydbname mytablename --where=" sid = 123456 and name = '小明同学' " > my_export_data.sql

导出数据

mysqldump -h 192.168.1.1 -u user -p'passwd'  dbname | gzip > dbname.sql.gz

导入：

mysql -uroot -p dp_db_bak < ~/dumpout.sql

source /home/abc/abc.sql;

登录远程服务器上的mysql

mysql -h 192.168.5.116 -P 3306 -u root -p123456

配置文件

/etc/mysql/mariadb.conf.d/50-server.cnf

#表名不区分大小写：
lower_case_table_names=1

设置密码

mysqladmin -u root password "newpass"

让linux普通用户登录root

select host,user,plugin from user;
| localhost | root | unix_socket |

update user set authentication_string=PASSWORD('123'), plugin='mysql_native_password' where user='root';

-- systemctl restart mariadb

添加用户

方法１：
CREATE USER username@host [IDENTIFIED BY 'PASSWORD'] 其中密码是可选项
CREATE USER 'john@192.168.189.71' IDENTIFIED BY "123";

方法２：通过GRANT命令创建用户
priv代表权限select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file等14个权限
grant all privileges on test.* to joe@192.168.10.1 identified by '123';

方法３：
insert into user (host,user,password) values ('%','john',password('123'));
flush privileges;

创建数据库

1.mysql> create database RUNOOB;
2.mysqladmin -u root -p create RUNOOB;

删除数据库

DROP DATABASE RUNOOB

删除具有外键的表

SET FOREIGN_KEY_CHECKS = 0
-- DROP语句
SET FOREIGN_KEY_CHECKS = 1;

复制表到另一个数据库

CREATE TABLE mytbl_new LIKE production.mytbl;
INSERT mytbl_new SELECT * FROM production.mytbl;

create table mytbl_new select * from production.mytbl;

将id设置主键，自动增长

alter table mytbl_new modify id int auto_increment primary key.

添加唯一性约束

alter table `user` add unique(`username`)

创建表

CREATE TABLE IF NOT EXISTS tasks (
    task_id INT(11) AUTO_INCREMENT,
    subject VARCHAR(45) DEFAULT NULL,
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    description VARCHAR(200) DEFAULT NULL,
    PRIMARY KEY (task_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据

INSERT INTO tasks(column1,column2...)
VALUES (value1,value2,...),
       (value1,value2,...),
...;

update df_goods_goodsinfo set gtype_id=4 where id=5;

修改表的字段顺序

ALTER TABLE 表名 CHANGE 字段名字段名 int not null default 1 AFTER 它前面的字段
alter table tb1 change title title varchar(100) null after age;

pymysql

import pymysql

db = pymysql.connect('127.0.0.1','leib','123','tiantian',charset='utf8')
                      host         'user' 'password'  'db'  'port'  ...
#db = pymysql.connect(**config)

cursor = db.cursor()
cursor.execute("select * from df_goods_goodsinfo")

data = cursor.fetchall()    # 返回值是tuple,
for d in data:
    print(d)
    
print("data more? ",cursor.fetchone())

cursor.close()

#插入操作
try: 
	cursor.execute(sql)
	db.commit()
except:
	db.rollback()

DBUtils

DBUtils 是一个通用的数据库连接池工具，它提供了一组用于管理数据库连接的类和方法，能够实现数据库连接的池化和复用，提高了数据库操作的效率和性能。
它并不是一个数据库驱动，而是建立在数据库驱动之上，通过对数据库连接进行管理来提供更好的性能和资源利用率。
DBUtils 可以与多种数据库驱动配合使用，比如 pymysql、psycopg2（用于 PostgreSQL）等。

#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# @Time    : 4/3/19 11:01 PM
# @Author  : Lbsx
# @File    : DBUtils_test.py
# @Software: PyCharm
# Copyright © 2019 Free Software Foundation,Inc.  
# License GPLv3+;
"""
模式一： 为每个线程创建连接
"""
import pymysql
from DBUtils.PersistentDB import  PersistentDB

POOL = PersistentDB(
    creator=pymysql,  # 使用链接数据库的模块
    maxusage=None,  # 一个链接最多被重复使用的次数，None表示无限制
    setsession=[],  # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服务端，检查是否服务可用。# 如：0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    closeable=False,
    # 如果为False时， conn.close() 实际上被忽略，供下次使用，再线程关闭时，才会自动关闭链接。如果为True时， conn.close()则关闭链接，那么再次调用pool.connection时就会报错，因为已经真的关闭了连接（pool.steady_connection()可以获取一个新的链接）
    threadlocal=None,  # 本线程独享值得对象，用于保存链接对象，如果链接对象被重置
    host='127.0.0.1',
    port=3306,
    user='leib',
    password='123',
    database='test',
    charset='utf8'
)

def func():
    conn = POOL.connection(shareable=False)
    cursor = conn.cursor()
    cursor.execute('select * from tb1')
    result = cursor.fetchall()
    print(result)
    cursor.close()
    conn.close()

if __name__ == "__main__":
    func()

视图

视图是一个虚拟表（就是一个查询集）
创建：CREATE VIEW v1 AS SELECT nid,name FROM a WHERE nid>4;
删除： DROP VIEW v1
修改： ALTER VIEW v1 AS SELECT A.nid,B.name FROM A LEFT JOIN B ON A.id=B.nid
使用： select * from v1;
查看视图：
select * from information_schema.VIEWS;

触发器

CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FROM EACH ROW
BEGIN
		...
END
	                                 AFTER
	                                 前后    DELETE
	                                        UPDATE

delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb7 FOR EACH ROW
BEGIN

IF NEW. user = 'qq' THEN
	INSERT INTO tb2 (NAME) VALUES ('aa');
END IF;
END //
delimiter ;

存储过程

是一个SQL语句集合

创建存储过程

delimiter //　　#修改结束符号，为//
create procedure p1()
BEGIN
	select * from t1;
END//
delimiter ;  
-- 执行存储过程
call p1()

有参数

参数： in  仅用于传入参数用
	  out 
	  inout  既可以传入又可以当作返回值

delimiter //
create procedure p1(
      in i1 int,
      in i2 int,
      inout i3 int,
      out r1 int
  )
BEGIN
  set r1=i1+i2+i3;
  set i3 = i1+i2;
end//
delimiter ;
	
-- 执行存储过程 
SET @t1=3;
call p1(1,2,@t1,@t2)
select @t1,@t1;	
	
-- 删除
drop procedure proc_name;

-- 查看存储过程
show procedure status;
-- 查看存储过程生成语句
show create procedure 存储过程名

批量插入数量

delimiter $$     //以delimiter来标记用$表示存储过程结束
create procedure pre()		//创建pre()存储方法
begin
declare i int;		//定义i变量
set i=6001;
while i<6021 do		//对i的值配置
  insert into assisted_material (id,name)
  values(i,'老师课本+'); 
set i=i+1;		//自增循环
end while;
end 
$$		//存储过程结束

call pre();		//调用pre()存储方法

删除

drop procedure if exists p1;

条件和循环

if 条件语句

if i = 1 THEN
    SELECT 1;
ELSEIF i = 2 THEN
    SELECT 2;
ELSE
    SELECT 7;
END IF;

case

select id,name,(case when  age>22 then 'old' when age<=22 then 'letter' end) age  from users;

while循环

WHILE num < 10 DO
	SELECT
	    num ;
	SET num = num + 1 ;
END WHILE ;

repeat循环

repeat
    select i;
    set i = i + 1;
    until i >= 5
end repeat;

函数

create function f1(
	i1 int,
	i2 int)
returns int
BEGIN
	declare num int;
	set num = i1+i2
	return(num);
END //

select f1(1,2) into @j;
select @j;

游标

1条sql对应N条结果集，取出资源的接口/句柄，就是游标

declare 游标名 cursor for select_statement;
open 游标名;
fetch 游标名 into var1,var2[,...]
close 游标名;

delimiter //
create procedure p12()
begin
	declare row_id int;
	declare row_name varchar(20);
	declare row_age int;
	declare getstu cursor for select * from students;
	open getstu;
	fetch getstu into row_id,row_name,row_age;
	select row_id,row_name,row_age;
	close getstu;
	
end//

数据类型

整数

unsigned无符号
tinyint
int
bigint

浮点数

float   4 
double  4

时间

YEAR 1
DATE 4
TIME 3
datetime 8
timestamp 4

类型

CHAR
VARCHAR
TEXT 大文本
ENUM
SET

二进制类型

BINARY(M) M为字节数

PostgreSQL

对象关系数据库管理系统(ORDBMS)

复杂SQL查询，SQL子选择，外键，触发器，视图，事务，多进程并发控制(MVCC)，流式复制(9.0)，热备(9.0))。

工具：
1.psql ,pgAdmin
2.phpPgAdmin
3.pgFouine 日志分析器。

安装 sudo apt install postgresql
登录 sudo -u postgres psql -p 5432 端口

修改密码 alter user postgres with password '123';

\du 查看用户 
\h select 
\l 查看 数据库
\d  列出当前数据库的所有表格
\d [table_name]  表结构
\x  对数据做展开操作

create database testdb;
grant all privileges on database testdb to leib;
create database testdb owner leib;

psql -d testdb ;

CREATE TABLE table_name(
	name CHAR(20),
	birth DATE
);

安装:
sudo apt install install postgresql postgresql-client
安装完毕后，系统会创建一个数据库超级用户 postgres，密码为空
sudo -i -u postgres

\help

数据类型

smallint 2 字节小范围整数 -32768 到 +32767
integer 4 字节常用的整数 -2147483648 到 +2147483647
bigint 8 字节大范围整数 -9223372036854775808 到 +9223372036854775807
decimal 可变长用户指定的精度，精确小数点前 131072 位；小数点后 16383 位
numeric 可变长用户指定的精度，精确小数点前 131072 位；小数点后 16383 位
real 4 字节可变精度，不精确 6 位十进制数字精度
double precision 8 字节可变精度，不精确 15 位十进制数字精度
smallserial 2 字节自增的小范围整数 1 到 32767
serial 4 字节自增整数 1 到 2147483647
bigserial 8 字节自增的大范围整数 1 到 9223372036854775807

创建数据库

CREATE DATABASE dbname;
查看: \l

SELECT CURRENT_TIMESTAMP;
		current_time
		current_date

avg() ： 返回一个表达式的平均值
sum() ： 返回指定字段的总和
count() ： 返回查询的记录总数

LIKE
	在 COMPANY 表中找出 NAME(名字) 字段中以 Pa 开头的的数据：
	runoobdb=# SELECT * FROM COMPANY WHERE NAME LIKE 'Pa%';   ！！只能单引号

sudo -u postgres psql

\password postgres  为postgres用户设置一个密码
CREATE USER dbuser WITH PASSWORD 'password';
CREATE DATABASE exampledb OWNER dbuser;
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;

---shell -----

sudo -u postgres createuser --superuser dbuser
sudo -u postgres createdb -O dbuser exampledb

psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432
psql exampledb

\h：查看SQL命令的解释，比如\h select。
?：查看psql命令列表。
\l：列出所有数据库。
\c [database_name]：连接其他数据库。
\d：列出当前数据库的所有表格。
\d [table_name]：列出某一张表格的结构。
\du：列出所有用户。
\e：打开文本编辑器。
\conninfo：列出当前数据库和连接的信息。

创建新表

CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);

建表:
CREATE TABLE COMPANY(
    ID INT PRIMARY KEY     NOT NULL,
    NAME           TEXT    NOT NULL,
    AGE            INT     NOT NULL,
    ADDRESS        CHAR(50),
    SALARY         REAL
);

表信息: \d tablename

插入数据

INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');

选择记录

SELECT * FROM user_tbl;

更新数据

UPDATE user_tbl set name = '李四' WHERE name = '张三';

删除记录

DELETE FROM user_tbl WHERE name = '李四' ;

添加栏位

ALTER TABLE user_tbl ADD email VARCHAR(40);

更新结构

ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;

更名栏位

ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;

删除栏位

ALTER TABLE user_tbl DROP COLUMN email;

表格更名

ALTER TABLE user_tbl RENAME TO backup_tbl;

删除表格

DROP TABLE IF EXISTS backup_tbl;

导入导出sql文件

pg_dump --format=t -d db_name -U user_name -h 127.0.0.1 -O -W  > dump.sql
psql -h 127.0.0.1 -U user_name db_name < dump.sql

MongoDB

下载 https://www.mongodb.com/download-center#community
.TGZ

./mongod --dbpath=... // 默认/data/db

后台管理shell:
./mongo // js shell

显示所有数据库
show dbs

切换数据库
use local

sqlite

表头与列对齐显示

.header on #启用表头
.mode column #使用列模式

posted @ 2025-06-09 20:21 少侠来也阅读(30) 评论(0) 收藏举报

刷新页面返回顶部

千里不留行

数据库

优化

EXPLAIN

慢日志查询

相关参数

如何对慢查询进行优化？

日志分析工具

大表数据查询如何进行优化

数据量大如何优化

偏移量越大优化方案

如何优化WHERE子句

常用查询优化建议

联合索引的最左前缀原则

SQL语句

select

子句执行顺序

子查询

联结表

等值联结(equl join)

内部联结 inner join : 交集

联结多个表

自联结

外部联结 (包含没有关联行的那些行)

left join:

right join

组合查询 UNION

SHOW

排序检索数据

ORDER BY

过滤数据

WHERE

通配符进行过滤

LIKE操作符

正则表达式 REGEXP

函数

拼接字段(concatenate)

日期和时间处理函数:

数值处理函数

汇总数据

分组数据

过滤分组HAVING

全文本搜索

插入数据

更新和删除数据

视图

创建

练习题

面试题

范式(Normal Form)

SQL约束有哪些

脏读幻读

隔离级别

实现原理

MVCC（多版本并发控制）

优化查询效率

数据库的优化

聚簇索引与非聚簇索引

使用最多的数据库（Mysql，Mongodb，redis 等），对他们的理解？

SQL 查询处理的步骤序号

Mysql 集群的优缺点

Mysql 各引擎之间有什么区别

Mysql 数据库如何分区、分表

分区有四类：

MySQL中的update是行锁还是表锁

Redis

Redis 的并发竞争问题怎么解决?

Redis 和 MongoDB 的优缺点

Redis 默认端口，默认过期时间，Value 最多可以容纳的数据 长度

Redis 有多少个库

Redis常见面试题

Redis

数据类型(5种)

列表对象

Redis 为什么快

Sentinel(哨兵)

主要功能列表

监控(Monitoring)

故障通知(Notification)

故障转移(Automatic failover)

Redis 默认端口，默认过期时间，Value 最多可以容纳的数据长度