【无标题】考

• 一、SQL基础

• CHAR与VARCHAR的区别

char 和 varchar 类型的区别 ：

char(长度) 是定长字符串类型 ，括号内写多少长度，就实际占用多少

varchar(长度) 是不定长的字符串类型 ，占用空间由实际传入的值决定

• MySQL数值类型（TINYINT、INT、BIGINT）
• 
  

• BIGINT

  8 Bytes

  -2^63 ~ 2^63-1

  极大整数值

• TINYINT

  1 Bytes

  (-128，127)

  (0，255)

  小整数值

• FLOAT与DECIMAL的精度差异

• SELECT语句语法（WHERE、IN、BETWEEN）

• GROUP BY与WITH ROLLUP

-- group by语句 手动分组语句

-- group by 可以让我们自己去决定，把哪些数据分为一个组后，统计数据

-- group by 后面跟上要分组的字段，自动会将这个字段值相同的行，分为一个组

-- group by 使用特点 ：

-- 当普通字段 和 分组函数同时出现在select后面的时候，这个时候普通字段也需要出现在

-- group by后面

-- 分班级去统计学生的平均年龄，数量、最大年龄

SELECT sclass,avg(sage) ,count(1) , max(sage)

FROM student group by sclass;

-- 分班级去统计男生的平均年龄，数量、最大年龄

SELECT sclass,ssex,avg(sage) ,count(1) , max(sage)

FROM student

WHERE ssex = '男'

group by sclass,ssex;

-- 分班级分性别去统计学生的平均年龄，数量、最大年龄

SELECT sclass,ssex,avg(sage) ,count(1) , max(sage)

FROM student

group by sclass,ssex;

• JOIN类型（INNER JOIN、LEFT JOIN、RIGHT JOIN、CROSS JOIN）

内连接查询 inner join

-- 内连接可以通过设置条件将不符合条件的数据直接过滤，只返回符合条件的数据

-- 语法： select from 表1 inner join 表2 on 条件

-- inner join 表3 on 条件 ...

-- 左外连接 left join

-- 把左边的表设为基准表，不管数据是否关联，都会把左边基准表数据全部返回

-- 语法： select from 表1 left join 表2 on 条件

-- left join 表3 on 条件 ...

右外

-- 把右边的表设为基准表，不管数据是否关联，都会把右边基准表数据全部返回

-- 语法： select from 表1 right join 表2 on 条件

-- right join 表3 on 条件 ...

-- 全外连接 full join mysql中不支持

-- 把左外连接和右外连接用union 连接，就是全外连接

-- 交叉连接 cross join 返回结果和普通连接一样

• NULL值的存储和处理

• NULL与空字符串的区别

• NULL在聚合函数中的行为

• 子查询的嵌套位置和结果形式

-- 指的是在一个SQL语句中嵌套了另一个SQL语句 ，子查询可以出现在select 、FROM

-- where 、having等语句中，用于提供中间条件或者结果，让主查询可以得到需要的结果

-- 查询张三的英语分数

SELECT sid FROM student WHERE sname = '张三';

SELECT cid FROM course WHERE cname = '英语';

SELECT cmark FROM mark

where sid = (SELECT sid FROM student WHERE sname = '张三')

and cid = (SELECT cid FROM course WHERE cname = '英语');

-- 子查询用到的运算符

-- 1, 比较运算符 = > < >= <= != <>

-- 2, IN 和 NOT IN

-- 3, EXISTS 和 NOT EXISTS

-- 4, ANY 和 ALL

-- 子查询的分类

-- 1，按照执行的依赖关系 分为：非相关子查询 和 相关子查询

-- 非相关子查询(独立子查询)： 子查询可以独立运行，不依赖于外层的主查询

只执行一次查询，结果给主查询使用

SELECT * FROM mark WHERE sid = (SELECT sid FROM student where sname = '李四');

select * from mark where sid in (select sid from student where sclass = '1班');

• IN、EXISTS、ANY、ALL操作符

• UNION与UNION ALL的区别

二、SQL运行题

• LEFT JOIN查询结果分析

• COUNT聚合函数的使用

• EXISTS子查询的判断逻辑

• WHERE条件筛选（IS NULL）

• UNION联合查询的结果

三、事务机制

• 事务四大特性（ACID）

事务的四大特性 ACID

原子性: 一个事务可以看成是一个原子单元，一个事务中的所有操作不可能拆分，要么一起提交，要么一起回滚

一致性：事务执行前后的数据保持一致， 比如 转账业务的前后总金额保持不变

隔离性：多个事务之间相互隔离，互不影响

持久性：指的是事务一旦提交或者回滚，数据就会持久化存储，不会改变，除非重新开启一个新事务去操作数据

• 事务隔离级别（READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ、SERIALIZABLE）

解决上面的问题，数据库中设置了 4 种隔离级别

READ UNCOMMITTED 读未提交 脏读 √ 不可重复读 √ 幻读 √

READ COMMITTED 读已提交 脏读 × 不可重复读 √ 幻读 √

REPEATABLE READ 可重复读 脏读 × 不可重复读 × 幻读 √

SERIALIZABLE 可串行化 脏读 × 不可重复读 × 幻读 ×

• 脏读、不可重复读、幻读的概念

将来多个事务同时操作，称为事务并发，事务并发就可能会出现一些问题 ，将来可以设置数据库中的隔离级别，控制问题的发生

脏读 ：一个事务读取到另一个事务未提交的数据 ，脏读在实际开发中，不允许出现

不可重复读 ：一个事务 A 在开始的时候，查询一条数据，另一个事务 B 修改了这条数据并提交，事务 A 查询数据前后不一致了 ， 实际开发中会根据需求确定是否可以出现不可重复读

幻读 ：一个事务 A 在做操作的时候，另一个事务 B 去添加了一条数据，但是事务 A 这个时候查询不到这条数据，事务 A 也去添加同一条数据，会报错 或者 事务 A 修改这条数据可以修改成功 ，就像产生幻觉一样，称为幻读 ，幻读也是允许出现的

• MySQL默认隔离级别

mysql 中默认的隔离级别是 REPEATABLE READ

oracle 中默认的隔离级别是 READ COMMITTED

• 事务的提交与回滚（COMMIT、ROLLBACK）

COMMIT; -- 提交

ROLLBACK; -- 回滚

四、存储引擎与高级特性

• InnoDB与MyISAM的区别

区别： innodb 支持 事务、行级锁、外键 、索引

myisam 支持表级锁 没有事务、外键

• 事务支持、外键支持、锁粒度

• 崩溃恢复能力

• 外键的定义和作用

外键约束 foreign key 用来约束两张表之间的关系，把两张表

作为主表和副表，主表数据必须存在，才能添加副表数据

-- 测试外键约束

-- 创建一个订单表，订单表中需要一个用户id，

-- 订单表中的用户id就是外键指向user表中的 uid

drop table `order`;

create table `order`(

oid int ,

uid int ,

pname varchar(100) ,

foreign key (uid) references `user`(uid) on delete cascade

);

-- 测试外键的添加用法 ，必须要保证主表有数据，才能往副表中加数据

-- 先往order表中添加数据，无法添加

insert into `user` values(101,'jack','123123','1999-1-1','扬州');

insert into `order` values(201,101,'手机');

• 外键的性能影响

• 视图的概念和作用

-- 视图 view

-- 视图是由查询定义一个虚拟表,可以像表一样被查询、做DML操作

-- 视图的操作语法和表完全相同

-- 创建视图语法 ： create [or replace] view 视图名 as select语句;

-- 子查询统计每个学生总分，筛选出总分高于全体学生平均总分的学生。

-- 创建一个每个人的总分视图

create view v_stuSumMark as

SELECT sid,sum(cmark) summk FROM mark GROUP BY sid;

-- 把视图当作表直接使用

SELECT sid FROM v_stuSumMark

WHERE summk > (SELECT avg(summk) FROM v_stuSumMark);

-- 创建一个视图，包含学生学号、姓名、课程名、分数

create view v_stuInfo as

SELECT s.sid , s.sname , c.cname , m.cmark

FROM student s join mark m on s.sid = m.sid

join course c on m.cid = c.cid;

-- 查询学生姓名和平均分

SELECT sname , avg(cmark) from v_stuInfo group by sid,sname;

-- 视图的使用语法和表基本一样，视图也可以执行增删改，DML执行的最终还是表

-- 如果视图中出现了：分组函数、distinct、group by 等会将表数据分组的结果不能使用增删改

-- 大部分情况下不会在视图中 执行DML

-- 视图特点 ：

-- 1，视图是虚拟表，不占用磁盘物理空间

-- 2，视图可以将一些表中敏感数据隐藏，留下基本数据使用

-- 3，视图一般用来将复杂的sql语句做包装，可以减少复复杂SQL语句的重复执行

五、索引与性能优化

• 索引设计原则

默认情况下，表中如果没有索引，都是执行全表扫描

概念 ： 索引是一种用来加快查询的数据结构 ，可以看作是书籍的目录 ，通过目录可以快速的检索到想要查找的数据

• 索引列的选择（区分度）

• 复合索引的最左前缀原则

4，复合索引遵循最左前缀匹配原则

复合索引( a , b ,c ) 给 a b c 三个字段创建索引

索引生效: where a= ? , where a = ? and b = ? ,where a = ? and b= ? and c= ?

索引失效： where b= ? , where b = ? and c= ?

• 索引失效场景

索引失效指的是创建了索引，但是由于编写 SQL 的时候使用某些语法，导致索引没有生效

查询索引是否工作： explain

explain select ...... 查看 type 、key 等字段

key 字段有值，就表示使用索引 ，key 为 null，索引失效，全表扫描

type 值如果是 all，表示全表扫描

失效情况

1，索引列使用了函数

EXPLAIN select * from student where UPPER(sname) = '张三'; -- 无效

EXPLAIN select * from student where sname = '张三'; -- 有效

2，索引列存在隐式转换(字符串和数字混用)

字段类型是数字，写一个字符串作为条件，或者 字段是一个字符串，写一个数字作为条件，都会导致索引失效

3，使用 or 连接无索引的字段

or 的两侧任意列没有索引，整个索引都会失效

EXPLAIN select * from student where sname = '张三' or sid = 10004; -- 索引生效

EXPLAIN select * from student where sname = '张三' or sclass = '1班'; -- 索引失效

4，like 模糊查询使用通配符开头

EXPLAIN SELECT * from student where sname like '%小%';

EXPLAIN SELECT * from student where sname like '徐%';

5，复合索引不遵循最左前缀原则

create index index_name_smajor on student(sname,snativeplace,smajor)

EXPLAIN SELECT * from student where sname = '张三' and snativeplace = '江苏' ; -- 有效

EXPLAIN SELECT * from student where snativeplace = '江苏' and smajor = '计算机' ; -- 无效

6，字段数据分布极度不均匀

使用低选择性的字段加了索引

7，查询结果集占表数据量较大

筛选后的数据超过了表总量的 30%，mysql 认为回表开销较大，会放弃索引

8，索引列参与了数学计算

explain select * from student where sid + 1 = 10002; -- 无效

9，使用了 != /not / not in / <>几个符号的也会导致索引失效

• EXPLAIN命令的使用

查询索引是否工作： explain

explain select ...... 查看 type 、key 等字段

key 字段有值，就表示使用索引 ，key 为 null，索引失效，全表扫描

type 值如果是 all，表示全表扫描

• EXPLAIN输出字段（type、key_len、Extra、rows）

• 覆盖索引的概念

• 索引选择性的概念

• 查询优化策略（EXISTS、LIMIT、避免函数）

• 索引类型（B+树、哈希、全文、复合）

MySQL 中默认采用的是 B+树结构 ，Oracle 中默认采用的是 B 树

B 树和 B+树的区别：

1，B 树中的非叶子、叶子节点，存放的是完整的数据+索引键，一个节点中不能存放太多条数据

B+树中的非叶子节点 只存索引键+子节点指针， 不存数据，一个节点能存更多的索引

相比来说，将来 B+树的高度会更低，IO 次数会更少

2，B 树可以支持等值查询、范围查询 ，节点内数据有序支持排序

B+树所有数据都存在叶子节点，且形成链表，所以更有利于范围查询

• 慢查询日志的配置和使用

六、代码运行题

• 多表关联查询

-- 多表查询指的是，查询数据的时候，如果结果中的数据来自于多张表

-- 那么就需要将多张表关联后进行查询

-- 这种多张表联合查询的方式就称为：多表查询

-- 比如 ：查询学生的姓名和他的平均分

-- 笛卡尔积 ： 多张表中的行数量在联合后的一个乘积

-- 联合查询基本语法 ：

-- select * from 表1,表2,表3... where 表1.列1 = 表2.列1 and 表2.列2 = 表3.列2

-- 多表查询的规则 ：

-- 要先找出几张表以及设置关联条件，留下关联的正确数据后再去

-- 查询需要的数据

-- 查询学生的姓名和他的分数

SELECT s.sname , m.cmark

FROM student s, mark m

WHERE s.sid = m.sid;

-- 查询每个学生的姓名、课程名、分数

SELECT s.sname,c.cname,m.cmark

FROM student s, mark m , course c

WHERE s.sid = m.sid and m.cid = c.cid;

-- 查询学生姓名和他的平均分，返回平均分80分以上的学生

SELECT s.sname,AVG(m.cmark)

FROM student s , mark m

WHERE s.sid = m.sid

GROUP BY s.sname , s.sid

HAVING avg(m.cmark) >= 80;

-- 查询每个班，每门课的平均分，返回班级名、课程名、平均分

SELECT s.sclass ,c.cname , TRUNCATE(AVG(m.cmark),1) 平均分

FROM student s , mark m , course c

WHERE s.sid = m.sid and m.cid = c.cid

GROUP BY s.sclass ,c.cname ;

-- 如果学生考试及格，则学分可得，查询每个学的姓名和他的总学分

SELECT s.sname , SUM(c.cval)

FROM student s, mark m , course c

WHERE s.sid = m.sid AND m.cid = c.cid AND m.cmark >= 60

GROUP BY s.sname,s.sid;

-- 查询返回平均分80分以上的老师姓名、课程名、平均分

SELECT t.tname,c.cname,AVG(m.cmark)

FROM teacher t, course c , mark m

WHERE t.tid = c.tid and c.cid = m.cid

GROUP BY t.tname,c.cname

HAVING AVG(m.cmark) > 80;

• GROUP BY聚合统计

• HAVING条件过滤

-- 分组函数的结果，不能使用where去比较

-- 把分数函数的结果去做条件比较要使用having

-- 注意： having后面条件的写法格式和where后面条件写法格式基本相同

-- having一般只用来过滤分组函数，其他的条件都写where后面

-- 哪个班级的平均年龄超过了20岁

SELECT sclass,avg(sage)

FROM student

group by sclass

having avg(sage) > 20 ;

• ORDER BY排序

• 子查询的嵌套使用

• CASE WHEN条件判断

/*

语法1：判断字段的值等于某个值，就显示指定的内容

CASE 字段

WHEN 值1 THEN 内容1

WHEN 值2 THEN 内容2

...

else 内容n

END

语法2： 判断某个字段值在某个范围内

CASE WHEN 判断表达式1 THEN 结果1

WHEN 表达式2 THEN 结果2

...

ELSE 其他结果

END

*/

-- 查询员工表，并返回员工部门信息，10 研发部 20 市场部 30 财务部

SELECT * , CASE deptno

WHEN 10 THEN '研发部'

WHEN 20 THEN '市场部'

WHEN 30 THEN '财务部'

ELSE '其他部门' END as 部门名称

FROM emp;

-- 查询返回每个学生的分数等级，90分以上显示优秀，80分以上显示中等

-- 70分以上显示良好，60 分以上显示及格 ，其他显示不及格

SELECT *, CASE

WHEN cmark >= 90 THEN '优秀'

WHEN cmark >= 80 THEN '中等'

WHEN cmark >= 70 THEN '良好'

WHEN cmark >= 60 THEN '及格'

ELSE '不及格' END as 等级

FROM mark;

-- 查询教师中，40岁以上 和 40岁以下的老师分别有多少人

SELECT CASE

WHEN tage >= 40 THEN '40岁以上'

ELSE '40岁以下' END as age_group ,

count(1)

from teacher GROUP BY age_group;

• LEFT JOIN处理NULL值