一、SQL基础CHAR与VARCHAR的区别char 和 varchar 类型的区别 char(长度) 是定长字符串类型 括号内写多少长度就实际占用多少varchar(长度) 是不定长的字符串类型 占用空间由实际传入的值决定MySQL数值类型TINYINT、INT、BIGINTBIGINT8 Bytes-2^63 ~ 2^63-1极大整数值TINYINT1 Bytes(-128127)(0255)小整数值INT或INTEGER4 Bytes-2^31 ~ 2^31-1(04 294 967 295)大整数值FLOAT与DECIMAL的精度差异FLOAT4 Bytes单精度浮点数值DOUBLE8 Bytes双精度浮点数值DECIMAL(长度,小数位数)依赖于M和D的值依赖于M和D的值小数值SELECT语句语法WHERE、IN、BETWEENGROUP 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 studentWHERE ssex 男group by sclass,ssex;-- 分班级分性别去统计学生的平均年龄数量、最大年龄SELECT sclass,ssex,avg(sage) ,count(1) , max(sage)FROM studentgroup 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 markwhere 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 NULLUNION联合查询的结果三、事务机制事务四大特性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 READoracle 中默认的隔离级别是 READ COMMITTED事务的提交与回滚COMMIT、ROLLBACKCOMMIT; -- 提交ROLLBACK; -- 回滚四、存储引擎与高级特性InnoDB与MyISAM的区别区别 innodb 支持 事务、行级锁、外键 、索引myisam 支持表级锁 没有事务、外键事务支持、外键支持、锁粒度MyISAMInnodb外键不支持支持事务不支持支持锁支持锁是避免资源争用的一个机制MySQL锁对用户几乎是透明的表级锁定行级锁定、表级锁定锁定力度小并发能力高崩溃恢复能力外键的定义和作用外键约束 foreign key 用来约束两张表之间的关系把两张表作为主表和副表主表数据必须存在才能添加副表数据-- 测试外键约束-- 创建一个订单表订单表中需要一个用户id-- 订单表中的用户id就是外键指向user表中的 uiddrop 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 asSELECT sid,sum(cmark) summk FROM mark GROUP BY sid;-- 把视图当作表直接使用SELECT sid FROM v_stuSumMarkWHERE summk (SELECT avg(summk) FROM v_stuSumMark);-- 创建一个视图包含学生学号、姓名、课程名、分数create view v_stuInfo asSELECT s.sid , s.sname , c.cname , m.cmarkFROM student s join mark m on s.sid m.sidjoin 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 的时候使用某些语法导致索引没有生效查询索引是否工作 explainexplain 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班; -- 索引失效4like 模糊查询使用通配符开头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命令的使用查询索引是否工作 explainexplain select ...... 查看 type 、key 等字段key 字段有值就表示使用索引 key 为 null索引失效全表扫描type 值如果是 all表示全表扫描EXPLAIN输出字段type、key_len、Extra、rows覆盖索引的概念索引选择性的概念查询优化策略EXISTS、LIMIT、避免函数索引类型B树、哈希、全文、复合MySQL 中默认采用的是 B树结构 Oracle 中默认采用的是 B 树B 树和 B树的区别1B 树中的非叶子、叶子节点存放的是完整的数据索引键一个节点中不能存放太多条数据B树中的非叶子节点 只存索引键子节点指针 不存数据一个节点能存更多的索引相比来说将来 B树的高度会更低IO 次数会更少2B 树可以支持等值查询、范围查询 节点内数据有序支持排序B树所有数据都存在叶子节点且形成链表所以更有利于范围查询慢查询日志的配置和使用六、代码运行题多表关联查询-- 多表查询指的是查询数据的时候如果结果中的数据来自于多张表-- 那么就需要将多张表关联后进行查询-- 这种多张表联合查询的方式就称为多表查询-- 比如 查询学生的姓名和他的平均分-- 笛卡尔积 多张表中的行数量在联合后的一个乘积-- 联合查询基本语法 -- select * from 表1,表2,表3... where 表1.列1 表2.列1 and 表2.列2 表3.列2-- 多表查询的规则 -- 要先找出几张表以及设置关联条件留下关联的正确数据后再去-- 查询需要的数据-- 查询学生的姓名和他的分数SELECT s.sname , m.cmarkFROM student s, mark mWHERE s.sid m.sid;-- 查询每个学生的姓名、课程名、分数SELECT s.sname,c.cname,m.cmarkFROM student s, mark m , course cWHERE s.sid m.sid and m.cid c.cid;-- 查询学生姓名和他的平均分返回平均分80分以上的学生SELECT s.sname,AVG(m.cmark)FROM student s , mark mWHERE s.sid m.sidGROUP BY s.sname , s.sidHAVING avg(m.cmark) 80;-- 查询每个班每门课的平均分返回班级名、课程名、平均分SELECT s.sclass ,c.cname , TRUNCATE(AVG(m.cmark),1) 平均分FROM student s , mark m , course cWHERE s.sid m.sid and m.cid c.cidGROUP BY s.sclass ,c.cname ;-- 如果学生考试及格则学分可得查询每个学的姓名和他的总学分SELECT s.sname , SUM(c.cval)FROM student s, mark m , course cWHERE s.sid m.sid AND m.cid c.cid AND m.cmark 60GROUP BY s.sname,s.sid;-- 查询返回平均分80分以上的老师姓名、课程名、平均分SELECT t.tname,c.cname,AVG(m.cmark)FROM teacher t, course c , mark mWHERE t.tid c.tid and c.cid m.cidGROUP BY t.tname,c.cnameHAVING AVG(m.cmark) 80;GROUP BY聚合统计HAVING条件过滤-- 分组函数的结果不能使用where去比较-- 把分数函数的结果去做条件比较要使用having-- 注意 having后面条件的写法格式和where后面条件写法格式基本相同-- having一般只用来过滤分组函数其他的条件都写where后面-- 哪个班级的平均年龄超过了20岁SELECT sclass,avg(sage)FROM studentgroup by sclasshaving avg(sage) 20 ;ORDER BY排序子查询的嵌套使用CASE WHEN条件判断/*语法1判断字段的值等于某个值就显示指定的内容CASE 字段WHEN 值1 THEN 内容1WHEN 值2 THEN 内容2...else 内容nEND语法2 判断某个字段值在某个范围内CASE WHEN 判断表达式1 THEN 结果1WHEN 表达式2 THEN 结果2...ELSE 其他结果END*/-- 查询员工表并返回员工部门信息10 研发部 20 市场部 30 财务部SELECT * , CASE deptnoWHEN 10 THEN 研发部WHEN 20 THEN 市场部WHEN 30 THEN 财务部ELSE 其他部门 END as 部门名称FROM emp;-- 查询返回每个学生的分数等级90分以上显示优秀80分以上显示中等-- 70分以上显示良好60 分以上显示及格 其他显示不及格SELECT *, CASEWHEN cmark 90 THEN 优秀WHEN cmark 80 THEN 中等WHEN cmark 70 THEN 良好WHEN cmark 60 THEN 及格ELSE 不及格 END as 等级FROM mark;-- 查询教师中40岁以上 和 40岁以下的老师分别有多少人SELECT CASEWHEN tage 40 THEN 40岁以上ELSE 40岁以下 END as age_group ,count(1)from teacher GROUP BY age_group;LEFT JOIN处理NULL值