mysql

case when 条件 then 结果 else … end

含义：只要…就…

count()函数

• count(*)=count(‘字段值’)：都是统计记录数，有几条记录就统计几条记录；
• count(字段名称)：如果有null的记录，则不统计；

if语句

求奇数和偶数

mod(id,2)=1表示id为奇数
mod(id,2)=0表示id为偶数

count(distinct 字段)

lead函数

lead()表示从第几行之后开始取

datediff()函数

计算两个日期之间的天数

-- SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate
-- 1
-- SELECT DATEDIFF('2008-12-29','2008-12-30') AS DiffDate
-- -1

delete a …where…

表示删除a表中的记录，具体删除哪些记录要看where后面满足的条件。

not in+子查询（不在…里面）

select name as customers from customers where id not in (select customerid from orders );

in +子查询 (在…里面)

select department.name as department,employee.name as employee,employee.salary 
from department inner join employee 
on department.id=employee.departmentid 
where (employee.departmentid,employee.salary) in 
(select departmentid ,max(salary) as salary from employee group by departmentid);

连接查询

笛卡尔积（没有加筛选条件的连接）

将左边的所有记录与右表的第一行记录关联，重复此步骤。

select *from dept,emp;
或者
select *from dept join emp;
或者
select *from dept inner join emp;

左外连接

以左表为基准左表记录全部显示出来，右表与左表相等记录显示出现。

select *from dept left outer join emp on dept.depto=emp.deptno;

右外连接

以右边为基准右表全部显示出来，左表与右表相等记录显示出来

select *from dept right outer join emp on dept.depto=emp.deptno;

内连接（inner join或者join）

只显示两表相等的记录

select *from dept inner join emp on dept.depto=emp.deptno;
或者
select *from dept join emp on dept.depto=emp.deptno;
或者
select *from dept as a,emp as b where dept.depto=emp.deptno;

左表独有

select *from dept left outer join emp on dept.depto=emp.deptno where emp.name is null;


注意：最后使用where 过滤

右表独有

select * from t1 right join t2 on t1.id = t2.id where t1.id is  null;

注意：最后使用where过滤

全连接

两表关联，查询所有

select *from dept left outer join emp on dept.depto=emp.deptno 
    -> union 
    -> select *from dept right outer join emp on dept.depto=emp.deptno;

distinct的用法

distinct一般是用来去除查询结果中的重复记录的。

细节：当distinct应用到多个字段的时候，其应用的范围是其后面的所有字段，而不只是紧挨着它的一个字段，而且distinct只能放到所有字段的前面。

limit的使用

#在table_name表中从第2条数据开始取5条数据。即2，3，4，5，6
select *
from table_name
limit 1, 5;   


#在table_name表中从第6条数据开始取10条数据。即6，7，8，9......14，15
select *
from table_name
limit 5, 10;

sql的四大排名

• dense_rank()：分数一致排名一致，分数不一致排名+1；
• rank()：分数一致排名一致，分数不一致按照实际排名；
• row_number()：按照顺序依次递增排名，无重复排名；
• ntile(5)：分组排名，里面的数字是几最多排名就是几；

partition by的使用

求每个部门中（分组中）工资排名前三的员工

1.创建数据库
格式：create database 数据库名
on primary(
…)
log on(
…)

create database testdb01
on primary(
name=testdb01,
filename=‘E:\testdb\testdb01.mdf’,
size=5,
maxsize=100,
filegrowth=5 --最后一个属性不需要加逗号
)
log on(
name=testdb01_log,
filename=‘E:\testdb\testdb01_log.ldf’,
size=5,
maxsize=100,
filegrowth=5
)

–注意事项：这里需要自己先创建testdb文件夹，因为数据库不会自己给你创建的。

2.删除数据库
格式：drop database 数据库名

3.修改数据库的名字
格式：alter database 旧数据库名
modify name=新数据库名

alter database testdb02
modify name=testdb01

4.修改数据库文件的属性
格式：alter database 数据库名
modify file(

....)

alter database testdb01
modify file(
name=testdb01, --这里不是修改名字，而是要找到修改的文件名字
size=11,
maxsize=100,
filegrowth=10
)

5.查看数据库属性
格式：exec sp_helpdb 数据库名

exec sp_helpdb testdb01

6.创建表
格式：create table 表名（
列名 数据类型 约束类型 是否为空,
列明 数据类型 是否为空）

create table stu(
stuID int primary key not null,
name varchar(10) not null,
age int null
)

注意事项：最后一行不能加逗号

7.删除表
格式：drop table 表名

drop table stu

8.--修改列类型
格式：alter table 表名
alter column 列名称 数据类型

alter table stu_infor
alter column name varchar(20)

9.修改列是否为空
格式：alter table 表名
alter column 字段名称 数据类型 是否为空（not null或者null）

alter table stu_infor
alter column age float not null

10.添加字段
格式：alter table 表名
add 字段名称 数据类型 是否为空（not null或者null）

alter table stu_infor
add grade varchar(10) not null

11.添加主键约束
格式：alter table 表名
add constraint 约束名 primary key (添加主键的字段名)

alter table stu_infor
add constraint KI primary key (stuID) --给字段stuID添加约束，约束类型是主键，约束名字是KI

12.添加外键约束
格式：alter table 表名
add constraint 外键名 foreign key(添加外键的字段名) references  依赖的表名（字段名）

–添加外键约束
alter table stu
add constraint fk foreign key (age) references stu_infor(ID)
在表stu的字段age中添加外键fk依赖于表stu_infor的字段上

13.修改字段名
格式：exec sp_rename '表名.字段名', '新的名字','column'

exec sp_rename ‘stu_infor.stuID’,‘ID’,‘column’
–将stu_infor表中的stu_ID字段名修改成新的字段名ID

14.between 查询在某一个范围内的记录

select *from stu_infor
where age between ‘20’ and ‘23’
–注意事项：between后面不管是什么数据类型，都要用单引号

select *from stu_infor
where name between '阿大’and ‘赵敏’

–查询表中不在65到75之间的记录
select *from stu_infor
where grade not between ‘65’ and ‘75’

–查询当前时间
select getdate()

15.delete记录

–删除表中第3行的记录
delete stu_infor
where ID=3

16.where精确限制

–where精确限制
update stu_infor
set name=‘阿大’
where ID=1

17.模糊查询like

select *from stu_infor --查询表中名字开头是啊的记录
where name like ‘阿%’

select *from stu_infor --查询表中名字结尾是四的记录
where name like ‘%四’

19.top查询表中前面几行数据
格式：select top 数据 字段 from 表名

–查询表中前几行的记录
select top 2 * from stu_infor

20.update修改表中的数据
格式：update 表名
set 字段=输入值
where 字段=值

–修改表中的字段数据
update stu_infor
set name=‘赵敏’
where ID=4

21.insert往表中插入记录
格式：insert into 表名（字段）
values(值)

–往表中插入一条记录
insert into stu_infor(ID,name,age,grade)
values(01,‘张三’,20,60)

–往表中插入多条记录
insert into stu_infor(ID,name,age,grade)
values(02,‘李四’,21,65),
(03,‘王麻子’,22,70),
(04,‘张无忌’,23,75)

–注意事项：这里插入多条记录时，插入的数据要与字段一一对应。

22.删除表中外键约束
格式：alter table 表名
drop constraint 约束名

–删除表中外键约束fk
alter table stu drop constraint fk

23.查询表中部分字段
格式：select 字段，字段，字段 from 表名 

–查询表中的部分字段
select ID,name from stu_infor

24.子查询in的用法，它是一个字段范围，在这个范围内查询
格式：select *from 表名 where 字段 in (值集)

–子查询in的用法,它是一个字段的范围，在这个范围内查询
select *from stu_infor
where ID in (1,2,3)
–查询整张表中ID是1，2，3的记录

–查询整张表忠姓名是阿大，李四和赵敏的记录
select * from stu_infor
where name in (‘阿大’,‘李四’,‘赵敏’)

–查询整张表中ID不是1,2,3,4,5的记录
select * from stu_infor
where ID not in (1,2,3,4,5)

25.as给表/列起名字
格式：select *from 表名 as 名字

格式：select 字段 as 别名
字段 as 别名
字段 as 别名
from 表名

----给表起别名
select * from stu_infor as A

–给列起别名
select ID as 序号,
name as 姓名 ,
grade as 成绩
from stu_infor

–注意事项：如果要把一个查询结果作为另外一个查询的元，必须要起这个查询起别名
–如：
select *from (select *from stu_infor where age between 21 and 25) as A

26.exists子查询

27.索引index
索引目的：提高查询效率
索引分2类：
聚集索引：实际的物理存储顺序和逻辑顺序一致，一个表只能有一个聚集索引
非聚集索引：实际的物理存储顺序和逻辑顺序不一致，一个表可以有很多个非聚集索引
唯一索引、视图索引、全文索引、xml

如：张三排在某张表的第一位，如果按照年龄排序，张三也是在第一位，这就是聚集索引

增加索引后会增加额外的存储的空间，同时降低增、删、改、查的效率。

创建聚集索引
格式：create clustered index 索引名 on 表名（字段）

—给表中ID这个字段创建聚集索引
create clustered index ixhaha on stu_infor(ID)

删除索引
格式：drop index 表名.字段

–删除索引
drop index stu_infor.ixhaha

创建非聚集索引
格式：create nonclustered index 索引名 on 表名（字段）

–创建非聚集索引
create nonclustered index xixi on stu_infor(name)

MySQL中where、group by、having、order by关键字？

这三个MySQL关键字作用可以总结如下：

where筛选出满足条件的记录；
group by 对where筛选出的记录进行分组；
having 对分组记录再进行筛选；
order by对筛选出的记录进行分组；

where 和having的区别？

where是在分组之前进行筛选；having是在分组之后进行筛选。
where后面不能使用聚合函数；having后面可以使用聚合函数

where、group by、having、聚合函数、order by执行顺序和使用顺序

where、group by、having、聚合函数、order by

order by 语句用于根据指定的列对结果集进行排序。

2）MySQL事务有哪些特性？（重点掌握）

答：事务是单个逻辑工作单元执行的一系列操作，是一个不可分割的工作单位。满足如下的四大特性：

原子性（Atomicity）：事务作为一个整体被执行 ，要么全部执行，要么全部不执行；
一致性（Consistency）：保证数据库状态从一个一致状态转变为另一个一致状态；
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行；
持久性（Durability）：一个事务一旦提交，对数据库的修改应该永久保存 

面试官：“如果不做控制，多个事务并发操作数据库会产生哪些问题吗？

• 丢失更新：事务A和事务B对数据进行了修改，事务A先提交事务B后提交，先提交的事务所做的更新会被后提交的更新覆盖，导致数据更新丢失。
• 脏读：事务A读取了事务B未提交的数据，事务B出现了回滚，导致事务A数据不一致，事务A出现了脏读。
• 不可重复读：事务A在没有提交的情况下，同一个查询操作执行了多次得到的结果不一样，因为事务B在期间更新了该数据且进行了提交。
• 幻读：事务A在没有提交的情况下，第一次读取记录为n条，第二次读取记录条数为n+1条，因为事物B在期间增加了一条记录数且进行了提交。

注意：不可重复读是指读出的数值发生了改变。条数并没有发生改变；

面试官：“MySQL数据库事务的隔离级别有哪些？“

• 读未提交(Read Uncommitted)：所有事务都可以看到其他未提交事务的执行结果，本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少，允许脏读取。（没啥用）
  

• 读已提交(Read Committed)：所有事务都可以看到其他已提交事务所做的改变，允许不可重复读取。
  

• 可重复读(Repeatable Read)：这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不允许脏读和不可重复读，但是有时可能出现幻读，使用了mvcc机制。
  

• 序列化(Serializable)：最高的事务隔离级别，它通过强制事务排序，事务只能一个接着一个地执行，但不能并发执行
  

事务隔离级别如何实现的

可重复读（repeatable read）是mysql的默认隔离级别，它使用了多版本并发控制机制（mvcc），就是数据库表中一行记录可能有多个版本用字段row trx_id表示=tracsaction_id的值。

• 快照读：读历史版本
• 当前读：读最新版本
• 普通的select就是快照读，而update、insert、delete、select …for update、select
  …lock in share mode就是当前读

加锁实现
record locks(记录锁)：在索引记录上加锁
gap locks(间隙锁)：在索引记录之间加锁
next-key locks：在索引记录上加锁，并且在索引记录之间加锁

MySQL中的锁机制？（重点掌握）

答：MySQL数据库的锁分为表级锁和行级锁。从数据库的角度看，行级锁又可以分为独占锁和共享锁。

独占锁（排他锁），也称X锁（Exclusive Lock）

涉及到insert、delete、update的操作mysql会自动使用独占锁，select操作需要使用select…for update加入独占锁，多个事务操作数据，只能有一个事务能获取到独占锁。

面试官：“行级锁什么时候会锁住整个表？

行级锁是通过锁住索引实现的，如果数据表没有建索引，行级锁就会升级为表级锁。

共享锁，也叫S锁（Shared Lock）

可以通过select… lock in share mode加入共享锁，多个事务都可以获取共享锁，获取到共享锁的事务只能读取数据不能修改事务，独占锁和共享锁是互斥的，加入了独占锁就不能加入共享锁。

悲观锁与乐观锁：

悲观锁：当要对数据库中的一条数据进行操作时，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制实现，这点跟java中的synchronized很相似。

乐观锁：对数据库中的数据进行更新时不进行加锁，而是更新后，会比较该数据和数据表中数据是否一致，以此来决定是否要更新数据。利用CAS和版本号实现。

乐观锁的实现方式

• 版本号：给数据表加个version字段，数据表有更新version字段+1，查询的时候将version字段查出来更新的时候利用查询出的version字段进行验证。（ABA的一种解决办法）

• CAS：CAS 操作中包含三个操作数 —— 内存位置V、预期原值A和新值B。如果内存位置的值与预期原值相匹配，那么处理器会自动将该位置值更新为新值，否则不做任何操作。

面试官：“CAS导致的ABA问题有了解吗？如何解决？

两个事务A和B，它们都读取到某个数据为10，A将10更新为20，然后又更新为10，B认为这没发生变化继续操作，这就是ABA问题。

ABA可以使用版本号version解决，也可以使用时间戳机制解决。

有SQL优化或者MySQL故障排查经历吗？

优化：建立索引、覆盖索引、索引的最左前缀原则
故障排查：

• explain+sql语句 ，查看sql语句执行的参数，如：使用的索引、扫描了多少行数据等
• show create table 表名称；查看建表语句
• show index from 表名称； 查看索引的相关信息

MySQL建表的约束条件有哪些？

• primary key：唯一、非null、通常设置auto_increment(可以设置多个主键)

id int not null primary key auto_increment

• unique：字段的值不能重复，唯一，null/not null(可以设置多个唯一约束，可以进行更新)

name VARCHAR(50) NOT NULL UNIQUE

• check：限制字段值的范围

salary FLOAT CHECK(salary>0 AND salary<100)

• Default：给字段一个默认值

gender VARCHAR(10) DEFAULT '女'

• Foreign Key：建立两张表之间联系，主表的主键是从表的外键

foreign key (classno) references t_class(cno)

注意：建表语句顺序为
字段名称、类型、not null/null 约束 auto_increment

MySQL的日志模块binlog和redo log有了解吗？

mysql更新操作非常频繁，如果每次更新都根据条件找记录对记录更新然后写回磁盘，那么IO成本和查找记录成本都很大，因此使用日志模块，它能将更新先写到日志中进行保存，等空闲的时候写回到磁盘中。

redo log是innodb引擎特有的日志模块，它是物理日志，固定大小，记录数据页的变化，从头开始写写到结尾又从头开始写；

binlog它是server层的日志模块，它是逻辑日志，主要记录sql语句，在末尾进行追加的形式记录，不能修改，有三种格式：

• statement：记录完整的sql语句，优点就是文件小执行效率高，缺点就是记录数据不准确会有丢失；
• row：记录数据表中行的变化，优点就是记录数据准确缺点就是文件大执行效率低；
• mixed：两者的混合；

面试官：为什么MySQL会突然变慢一下？

redo log日志写满了，需要flush脏页，将内存数据写到磁盘中，导致mysql执行速度变慢；

MySQL的索引有了解吗？（重点掌握）

对数据表中的一列或者多列进行排序的结构，可以增加检索的速度，提高查询的速度，类似于书的目录一样，innodb引擎的索引结构时B+树。

B+数的结构

• m阶B+树每个节点最多有m个分支；
• 分支数=节点关键字个数；
• 非叶根节点至少有2个分支，其他节点至少有⌈m/2⌉个分支；
• 所有叶节点在都在同一层，并且包含全部关键字的信息和指向记录的指针，关键字按照从小到大的顺序递增排列，相邻节点用链表链接在一起，有一个指针指向关键字最小的叶子结点；

B树的结构

• 所有节点中含有分支数最多的就是B树的阶数，m表示阶数；
• m阶B树每个最多有m个分支，最多有m-1个关键字；
• 根节点最少有两个分支
• 非根节点最少有⌈m/2⌉分支，至少含有⌈m/2⌉-1个关键字；
• 所有叶节点在同一层，并且不包含任何信息；

B+树与B树的区别

B+	B
m个分支有m个关键字	m个分支有m-1个关键字
非叶节点只起到索引的作用因此非叶节点可以存储更多的索引信息，树的高度得到降低，一次性读入内存需要查找的关键字就越多，减少IO次数，数据的检索速度变快	每个结点中存放有k和value，叶节点并不包含任何关键字的信息，有时候在内部节点中就找到要查询的数据，有时候查找数据的范围还需要回旋
叶子结点包含全部关键字的信息，节点中关键字按照从小到大的顺序进行排列，相邻的结点链接在一次，方便进行顺序查找	–
每次都是从根结点到叶子结点查询，所以查询更加稳定
更加适合基于范围查找，数据库中基于范围的查找还是很频繁的，不需要进行回找	基于范围的查找需要进行反复的回找，查找效率很低

面试官：“聚簇索引和非聚簇索引有了解吗？”

• 聚餐索引：又叫主键索引，索引和数据放在一起存储，主键索引树的叶子节点上包含数据表行记录；
• 非聚簇索引：又叫普通索引，索引和数据分开存储，普通索引树中叶子节点上包含主键值

MySQL回表

先查询普通索引树，得到主键值，再去查主键索引树，得到数据表的行数据；

覆盖索引

直接在普通索引树上查询数据表的记录，不需要进行回表操作，提高检索速度，mysql的性能优化方法之一；

索引的最左前缀原则

在联合索引中，不需要索引的全部信息，只需要满足索引的最左前缀，就可以提高检索速度，也是mysql的性能优化方法之一；

MySQL常见的存储引擎有哪些（重点掌握）？

innodb	MYISAM
支持事务	不支持事务
支持表级锁，支持行级锁	只支持表级锁
支持主键和外键	只支持主键
	count(*)操作更加有优势保存了行数
适合巨大数据量操作
不支持全文索引	支持全文索引

mysql的innodb中为什么使用B+树做结构

mysql索引的设计原则

• 经常作为条件查询的列适合作为索引而且该列要和其他列有一定的区分度如：使用name作为索引效果就会很差
• 尽量使用短索引，短索引涉及到的磁盘IO次数也会减少，检索速度也会加快
• 尽量使用最左前缀
• 不要过度索引，建立索引需要花费空间，维护索引也要花费时间，不是索引越多越好。

索引的使用场景有哪些？

• 中大型表使用索引效果很好，小型表直接查询更快
• 如果表的insert/delete/update非常多，select很少则没必要建立索引，因为建立索引既要空间维护也要时间
• 一般不作为条件查询的列没必要建立索引
• 多个列经常作为条件查询可以考虑联合索引