mysql相关

1.基本概念

1.1索引

索引有哪几种

• 单列索引（普通索引：允许空值和重复值 唯一索引：允许空值，不能有重复值 主键索引：不允许有空值和重复值）
• 组合索引：多个字段组合上创建的索引
• 全文索引：只能在char、varchar、text类型字段上使用，实际工作没用到过哈

建立索引的优劣

• 优势：
  类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本；
  通过索引对数据进行排序，降低数据排序的成本，降低CPU的消耗；
• 劣势：
  实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的；
  虽然索引提高了查询效率，同时也降低了表更新的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。

总结：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。

-- 添加唯一索引
alter table actor add unique index uniq_idx_firstname(first_name);

-- 添加普通索引
alter table actor add index idx_lastname(last_name);

1.2.视图

视图(VIEW)也被称作虚表，即虚拟的表，是一组数据的逻辑表示,其本质是对应于一条SELECT语句，结果集被赋予一个名字，即视图名字。视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化。

视图作用：
a.简单性。看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。
b.安全性。通过视图用户只能查询和修改他们所能见到的数据。但不能授权到数据库特定行和特定的列上。
通过视图，用户可以被限制在数据的不同子集上：使用权限可被限制在另一视图的一个子集上，或是一些视图和基表合并后的子集上。
c.逻辑数据独立性。视图可帮助用户屏蔽真实表结构变化带来的影响

create view actor_name_view as select first_name first_name_v,last_name last_name_v from actor

1.3数据库三范式

• 第一范式（1NF）无重复的列
• 第二范式（2NF）属性完全依赖于主键[消- 部分子函数依赖]
• 第三范式（3NF）属性不依赖于其它非主属性[消除传递依赖]

2.常用函数

1.1 时间函数

• to_date()
• to_char()

1.2 字符串函数

• substr
• concat

1.3 条件表达式

• case when
• nvl、ISNULL(score,0) --如果不为NULL，这直接返回该值；当学生成绩为空时，成绩记为0。

1.4 聚合函数

• sum
• avg
• max
• min

1.5 开窗函数

• 开窗排序函数：row_number()、rank()、dense_rank()
• 开窗聚合函数

窗口函数用法：
<窗口函数> OVER ( [PARTITION BY <列清单> ]
ORDER BY <排序用列清单> ）
*其中[ ]中的内容可以忽略

limit 2,1 从第三位开始，取一条记录
select *from employees order by hire_date desc limit 2,1
可能不止一个
所以应用窗口函数

select emp_no,birth_date,first_name,last_name,gender,hire_date
from(
select *,
row_number() over(order by hire_date desc )as n
from employees
) a
where n=3

附：RANK函数的使用
发表于 2020-05-11 22:14:35
这道题目考察的是SQL窗口函数（OLAP函数）中用于排序的专用窗口函数用法
但是由于关系数据库提供支持OLAP用途功能时间不长
还有一部分DBMS不支持这个新功能（比如MYSQL）

select 
    emp_no, salary,
    dense_rank() over (order by salary desc) as rank
from 
    salaries
where 
    to_date='9999-01-01'
order by 
    rank asc,emp_no asc;
    
    select grade,
sum(number) over(order by grade asc) as t_rank
from class_grade

下面介绍三种用于进行排序的专用窗口函数：

• a、RANK()
  在计算排序时，若存在相同位次，会跳过之后的位次。
  例如，有2条排在第1位时，排序为：1，1，3······
• b、DENSE_RANK()
  这就是题目中所用到的函数，在计算排序时，若存在相同位次，不会跳过之后的位次。
  例如，有2条排在第1位时，排序为：1，1，2······
• c、ROW_NUMBER()
  这个函数赋予唯一的连续位次。
  例如，有2条排在第1位时，排序为：1，2，3······

扩展：开窗函数和聚合函数有什么区别
a.开窗每组返回多行、聚合函数对于每组只返回一行
b.聚合函数=去重后的开窗函数

1.6 AB表开发

注意事项

a.新增字段，AB表都需要新增
b.切换前删索引，数据装载后加索引

1.7 拉链表

建立拉链表
新建临时表
找出新增记录，新纪录为8999-12-31
找出不一致记录，新纪录为8999-12-31

插入新增记录
更新不一致旧记录结束日期为当前日-1

1.8 批量更新表语句

1.9 表关联测试

mysql> select *from tmp1;
+------+------+
| a    | b    |
+------+------+
|    1 |   17 |
|    2 |   18 |
|    3 |    9 | 
+------+------+
3 rows in set (0.00 sec)

mysql> select *from tmp2;
+------+------+
| c    | d    |
+------+------+
|    1 |   15 |
|    1 |   19 |
|    2 |   11 |
+------+------+
3 rows in set (0.00 sec)

mysql> select *from tmp1 inner join tmp2 on tmp1.a=tmp2.c;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    1 |   17 |    1 |   15 |
|    1 |   17 |    1 |   19 |
|    2 |   18 |    2 |   11 |
+------+------+------+------+
3 rows in set (0.00 sec)

mysql> select *from tmp1 left join tmp2 on tmp1.a=tmp2.c;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    1 |   17 |    1 |   15 |
|    1 |   17 |    1 |   19 |
|    2 |   18 |    2 |   11 |
|    3 |    9 | NULL | NULL |
+------+------+------+------+
4 rows in set (0.00 sec)

mysql> select *from tmp1 right join tmp2 on tmp1.a=tmp2.c;
+------+------+------+------+
| a    | b    | c    | d    |
+------+------+------+------+
|    1 |   17 |    1 |   15 |
|    1 |   17 |    1 |   19 |
|    2 |   18 |    2 |   11 |
+------+------+------+------+
3 rows in set (0.00 sec)

2.sql优化

优化方法：
最大化利用索引、尽可能避免全表扫描、减少无效数据的查询
避免索引失效
方法如下
2.1 SQL优化(面试题)
　　因为现在面试经常需要问的需要SQL优化，问的具体操作步骤时候的常见做法，所以网上总结这些操作步骤：
SQL优化的具体操作：
1、在表中建立索引，优先考虑where、group by使用到的字段。

2、尽量避免使用select *，返回无用的字段会降低查询效率。如下：

SELECT * FROM t

优化方式：使用具体的字段代替*，只返回使用到的字段。

3、尽量避免使用in 和not in，会导致数据库引擎放弃索引进行全表扫描。如下：

SELECT * FROM t WHERE id IN (2,3)

SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)

优化方式：如果是连续数值，可以用between代替。如下：

SELECT * FROM t WHERE id BETWEEN 2 AND 3

如果是子查询，可以用exists代替。如下：

SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)

4、尽量避免使用or，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE id = 1 OR id = 3
优化方式：可以用union代替or。如下：
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3
（PS：如果or两边的字段是同一个，如例子中这样。貌似两种方式效率差不多，即使union扫描的是索引，or扫描的是全表）
5、尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE username LIKE ‘%li%’
优化方式：尽量在字段后面使用模糊查询。如下：
SELECT * FROM t WHERE username LIKE ‘li%’
6、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE score IS NULL
优化方式：可以给字段添加默认值0，对0值进行判断。如下：
SELECT * FROM t WHERE score = 0
7、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t2 WHERE score/10 = 9
SELECT * FROM t2 WHERE SUBSTR(username,1,2) = ‘li’
优化方式：可以将表达式、函数操作移动到等号右侧。如下：
SELECT * FROM t2 WHERE score = 109
SELECT * FROM t2 WHERE username LIKE ‘li%’
8、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE 1=1
优化方式：用代码拼装sql时进行判断，没where加where，有where加and。
2.2其他
mysql中常用的三种插入数据的语句:
insert into表示插入数据，数据库会检查主键，如果出现重复会报错；
replace into表示插入替换数据，需求表中有PrimaryKey，
或者unique索引，如果数据库已经存在数据，则用新数据替换，如果没有数据效果则和insert into一样；
insert ignore表示，如果中已经存在相同的记录，则忽略当前新数据；
insert ignore into actor values(“3”,“ED”,“CHASE”,“2006-02-15 12:34:33”);
select * from salariesforce INDEX(idx_emp_no) where emp_no=10005 --强制索引
update titles_test set to_date=NULL ,from_date=‘2001-01-01’ where to_date=‘9999-01-01’ --update多列用,分隔
update titles_test set emp_no=replace(emp_no,10001,10005) where id=5 --replace使用
alter table titles_test rename to titles_2017 --rename
alter table audit add constraint foreign key (emp_no) references employees_test(id) --外键
select number from grade group by number having count()>=3 --having
考点：group by 子句常见错误：
select 子句中只能存在以下三种元素：常数、聚合函数、group by子句指定列(聚合键)
create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end – 触发器

-LIMIT 分页查询employees表
SELECT *
FROM employees
LIMIT 5,5
LIMIT 语句结构： LIMIT X,Y
Y ：返回几条记录
X：从第几条记录开始返回（第一条记录序号为0，默认为0）

在使用left join时,on and 和on where会有区别

1. on的条件是在连接生成临时表时使用的条件,以左表为基准 ,不管on中的条件真否,都会返回左表中的记录
   2.where条件是在临时表生成好后,再对临时表过滤。此时 和left join有区别(返回左表全部记录),条件不为真就全部过滤掉,on后的条件来生成左右表关联的临时表,where后的条件是生成临时表后对临时表过滤
   作者：大笨群
   链接：https://www.jianshu.com/p/7d953fe064ed
   来源：简书
   著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

在使用inner join时，不管是对左表还是右表进行筛选，on and和on where都会对生成的临时表进行过滤

3.存储过程

存储过程就是有业务逻辑和流程的集合

• 创建存储过程
  drop PROCEDURE if exists test2;
  create procedure test2()
  begin
  select * from employees limit 10;
  end;
  call test2()

• 创建带返回值的存储过程
  drop procedure if exists test3;
  create procedure test3(out pa int )
  begin
  select avg(emp_no) into pa from employees ;
  end
  ;
  call test3(@pa);
  select @pa;

• 创建带参数、返回值存储过程
  drop procedure if exists test3;
  create procedure test3(in number date,out pa decimal(8,2) )
  begin
  select avg(emp_no) into pa from employees where hire_date>number ;
  end
  ;
  call test3(‘1986-01-01’,@pa);
  select @pa;
  #4 6.什么时候用union和union all ?

我们经常会碰到这样的应用，两个表的数据按照一定的查询条件查询出来以后，需要将结果合并到一起显示出来，这个时候

就需要用到union和union all关键字来实现这样的功能，union和union all的主要区别是union all是把结果集直接合并在一起，而

union 是将union all后的结果镜像一次distinct，去除重复的记录后的结果。

5.对于distinct与group by的使用

q.当对系统的性能高并且数据量大时使用group by
q.当对系统的性能不高时或者使用数据量少时两者皆可
q.尽量使用group by

6.常用命令

• mysql -h 11.20.111.14 -u datawind -p --接着输入密码即可连接"select *from dag" >11.txt --远程执行命令
• mysql -h 20.201.26.200 -P3306 -uairflow -pAirFlow123! airflow -Ne “show tables”
• select count(*) from cbondagency
• mysql连接数满
  报错
  ERROR 1040 (HY000) :Too many connections

–查看最大连接数
show variables like ‘max_connections’;

–查看当前所有连接
show full processlist;

–查看连接数
show status like ‘Threads%’

truncate和delete区别

新建表命名规范
t_m_ 前端相关表
t_e_ ETL中间表
ID字段 _id结尾
日期字段 _dt结尾
时间戳字段 _tm结尾 2022-05-07-23.13.30.000000

8999-12-31 （预留值）
9999-12-31 +1数据会溢出

7.乐观锁和悲观锁