课程回顾

day01

1. 数据库相关sql
2. 查询所有 show databases;
3. 创建 create database db1 character set utf8/gbk;
4. 查询详情 show create database db1;
5. 删除 drop database db1;
6. 使用 use db1;
7. 表相关
8. 创建表 create table t1(name varchar(10),age int);
9. 查询所有 show tables;
10. 查询表详情 show create table t1;
11. 表字段 desc t1;
12. 删除表 drop table t1;
13. 修改表名 rename table t1 to t2;
14. 添加字段 alter table t1 add age int first/after xxx;
15. 修改引擎和字符集 alter table t1 engine=myisam/innodb charset=utf8/gbk;
16. 删除字段 alter table t1 drop age;
17. 修改字段名和类型 alter table t1 change 原名 新名 新类型;
18. 修改类型和位置 alter table t1 modify 字段名 新类型 位置;
19. 数据相关：
20. 插入数据 insert into t1 (字段1,字段2) values(值1,值2),(值1,值2);
21. 查询数据 select * from 表名;
22. 修改 update t1 set age=10,name='xxx' where id=5;
23. 删除 delete from t1 where age<20;

day02

1. 主键约束+自增： primary key auto_increment
2. 注释 comment
3. `:用于修饰字段名和表名可以省略 '用于修饰字符串
4. 冗余： 表设计不合理导致的大量重复数据，通过拆分表解决
5. 事务：数据库中执行同一业务多条sql语句的工作单元，可以保证多条sql全部执行成功或全部执行失败
6. 开启事务： begin
7. 回滚：rollback
8. 提交：commit
9. 保存回滚点： savepoint xxx;
10. 回滚到某个回滚点： rollback to xxx;
11. sql分类：
12. DDL数据定义语言： 包含create drop truncate alter 不支持事务
13. DML数据操作语言： 包含insert update delete select(DQL) 支持事务
14. DQL数据查询语言： 只包含select
15. TCL事务控制语言
16. DCL数据控制语言： 用于分配用户 权限相关的sql
17. 数据类型
18. 整数：int(m) bigint(m) m代表显示长度 结合zerofill使用
19. 浮点数：double(m,d) m代表总长度 d代表小数长度 decimal(m,d)
20. 字符串:char(m) 不可变长度 最大255 varchar(m)可变长度最大65535 超过255建议使用 text
21. 日期：date 年月日 time 时分秒 datetime 最大9999-12-31 默认值null， timestamp 最大2038-1-19 默认值为当前系统时间
22. 其它

day03

1. is null 和 is not null
2. 别名
3. 去重 distinct
4. 比较运算符 > < >= <= = !=和<>
5. and和or
6. in 和 not in
7. between x and y 和 not between x and y
8. like 模糊查询 _代表单个未知字符 %代表0或多个未知字符
9. 排序 order by 字段名 asc/desc,字段名;
10. 分页 limit 跳过的条数,每页的条数
11. 数值计算 + - * / 7%2 mod(7,2)
12. 日期相关： now() curdate() curtime() date(now()) time(now()) extract(year/month/day/hour/minute/second from now()); dateformat(时间,格式) %YymcdHhis strto_date(字符串时间,格式)
13. ifnull(x,y) 如果x的值为null 则取y 不为null则取x
14. 聚合函数 平均值avg 最大值max 最小 min 求和sum 计数count
15. 字符串相关 concat() char_length() instr(str,substr) locate(substr,str); upper() lower() left() right() substring() trim() repeat() replace() reverse()

day04

1. 数学相关函数: floor() round() truncate() rand()
2. 分组查询 group by 字段名,字段名
3. having后面写聚合函数的条件需要结合group by使用 where后面写普通字段的条件
4. 子查询： 嵌套在sql语句中的 sql查询语句称为子查询，可以嵌套n层
5. 可写在的位置：
   1. where和having后面当做查询条件的值
   2. 写在创建表的时候 把结果保存到新表中
   3. 写from后面当成虚拟表 必须有别名
6. 关联查询
7. 查询方式有三种：
   1. 等值连接 select * from A,B where A.x=B.x and A.age=18;
   2. 内连接 select * from A join B on A.x=B.x where A.age=18;
   3. 外链接 select * from A left/right join B on A.x=B.x where A.age=18;
8. 如果查询两张表的交集数据使用，等值连接和内连接 建议使用内联
9. 如果查询两张表中一张表的全部数据和另外一张表的交集数据则使用外连接

day05

1. 关联关系
2. 一对一： 在从表中添加外键指向主表的主键
3. 一对多： 在多的表中添加外键指向主表的主键
4. 多对多： 创建单独的关系表保存两个表的主键
5. 权限管理案例： 三张主表：用户表 角色表 权限表 两张关系表：用户角色关系表 角色权限关系表

面试题

交易号 交易时间 交易金额 交易类型 姓名 性别 关系

流水表：交易号 交易时间 交易金额 交易类型 person_id

人物表：id 姓名 性别 关系 1. 创建表： create database db6 character set utf8; use db6; create table trade (id int primary key autoincrement,time datetime,money int,type varchar(10),personid int); create table person(id int primary key autoincrement,name varchar(10),gender varchar(5),rel varchar(10)); - 往人物表插入以下数据： 1 刘德华 男 亲戚 2 杨幂 女 亲戚 3 马云 男 同事 4 特朗普 男 朋友 5 貂蝉 女 朋友 insert into person values(null,'刘德华','男','亲戚'),(null,'杨幂','女','亲戚'),(null,'马云','男','同事'),(null,'特朗普','男','朋友'),(null,'貂蝉','女','朋友'); - 往流水表插入以下数据： 刘德华 微信 收1000 2018-03-20 杨幂 现金 收500 发50 2018-04-14 马云 支付宝 收20000 发5 2018-3-11 特朗普 微信 收2000 2018-5-18 貂蝉 微信 发20000 2018-7-22 insert into trade values(null,'2018-03-20',1000,'微信',1),(null,'2018-04-14',500,'现金',2), (null,'2018-04-14',-50,'现金',2),(null,'2018-3-11',20000,'支付宝',3), (null,'2018-3-11',-5,'支付宝',3), (null,'2018-5-18',2000,'微信',4), (null,'2018-03-20',-20000,'微信',5); 1. 统计2018年2月15号到现在的所有红包收益 select sum(money) from trade where time>=strtodate('2018年2月15号','%Y年%c月%d号'); 2. 查询2018年2月15号到现在金额大于100所有女性亲戚的名字和金额 select p.name,t.money from trade t join person p on t.personid=p.id where t.time>=strtodate('2018年2月15号','%Y年%c月%d号') and t.money not between -100 and 100 and p.gender='女' and p.rel='亲戚'; 3. 查询每个平台分别收入的红包金额 select type,sum(money) from trade where money>0 group by type;

视图

• 什么是视图： 数据库中表和视图都是其内部的对象，视图可以理解成是一张虚拟的表，视图本质就是取代了一段sql查询语句

• 为什么使用视图：因为有些数据的查询需要书写大量的sql语句，每次书写开发效率太低，使用视图可以重用sql语句，可以隐藏敏感信息

• 创建视图的格式： create view 视图名 as (子查询); -创建视图 create view vemp10 as (select * from emp where deptno=10); -查询视图内的数据 select * from vemp10;

• 练习： 创建30号部门工资大于2000的视图 vemp30 create view vemp30 as (select * from emp where deptno=30 and sal>2000);

• 创建一个没有工资的员工表视图 create view vempnosal as (select empno,ename,job,comm,hiredate,deptno from emp);

  select * from vemp10

视图分类

1. 简单视图：创建视图的子查询中不包含去重、分组查询、聚合函数、关联查询的视图称为简单视图,可以对视图中的数据进行增删改查操作
2. 复杂视图：和简单视图相反，只能进行查询
3. 创建显示每个部门工资总和，平均工资、最大工资、最小工资的复杂视图： create view vempinfo as (select deptno,sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno);

对简单视图进行增删改操作，操作方式和table一样

1. 插入数据 insert into vemp10(empno,ename) values(10011,'Tom'); insert into vemp10(empno,ename,deptno) values(10012,'Jerry',20);
   • 以上两行都为数据污染
2. 如果插入一条数据在原表中显示但是在视图中不显示，则称为数据污染
3. 数据污染可以通过 with check option 关键字解决 create view vemp20 as (select * from emp where deptno=20) with check option; -插入数据 insert into vemp20 (empno,ename,deptno) values(10013,'刘德华',30); //插入数据失败 insert into vemp20 (empno,ename,deptno) values(10013,'刘德华',20); //插入数据成功！ -删除和修改数据 只能操作视图中存在的数据 delete from vemp20 where deptno=10; //没有数据被删除 update vemp20 set sal=666 where deptno=10;//没有数据被修改

修改视图

• 创建或替换，如果不存在则创建，如果存在则替换 create or replace view vemp20 as (select * from emp where deptno=20 and sal>2000);

删除视图

• 格式： drop view 视图名; drop view vemp20;

视图别名

• 如果创建视图时的子查询使用了别名则视图操作时只能使用别名 create view vemp20 as (select ename name,sal from emp where deptno=20); select ename from vemp20;//报错不认识ename select name from vemp20; //执行成功

视图总结：

1. 视图是数据库中的对象，代表一段sql语句，可以理解成是一张虚拟的表
2. 作用：重用sql、隐藏敏感信息
3. 分类：简单视图（不包含，去重、聚合函数、分组、关联查询 可以对数据进行增删改操作）和复杂视图（和简单视图相反，只能查询）
4. 插入数据时可能会出现数据污染问题，通过with check option解决
5. 删除和修改只能操作视图中存在的数据
6. 起了别名后只能用别名

约束

• 什么是约束：约束就是给表字段添加的限制条件

主键约束 primary key

• 唯一且非空

非空约束 not null

• 字段的值不能为null
• 格式： create table t1(id int,age int not null); -测试： insert into t1 values(1,20);//成功 insert into t1 values(2,null);//失败

唯一约束 unique

• 字段的值不能重复
• 格式： create table t2(id int,age int unique); -测试： insert into t2 values(1,20);//成功 insert into t2 values(2,20);//报错

默认约束 default

• 给字段设置默认值
• 格式： create table t3(id int,age int default 25); -测试： insert into t3 (id) values(1); //触发默认值生效 insert into t3 values(2,null);//age值为null insert into t3 values(3,100); //age=100

外键约束

• 外键：用来建立关系的字段称为外键
• 外键约束：保证数据的完整性，外键字段的值可以null，可以重复，但是不能是关系表中不存在的数据，被依赖的数据不能先删除，被依赖的表也不能被先删除
• 格式： 先创建被依赖的部门表 create table tdept(id int primary key autoincrement,name varchar(10)); create table temp(id int primary key autoincrement,name varchar(10),deptid int,constraint fkdept foreign key(deptid) references tdept(id)); -格式介绍：constraint 约束名称 foreign key(外键字段) references 表名(字段名)
  • 部门表插入数据 insert into tdept values(null,'神仙'),(null,'妖怪'); insert into temp values(null,'悟空',1);//成功 insert into temp values(null,'八戒',1);//成功 insert into temp values(null,'刘德华',3);//失败 insert into temp values(null,'刘德华',null);//成功 delete from tdept where id=1; //删除失败 drop table tdept; //表删除失败 delete from temp where deptid=1;//删除神仙部的员工 delete from tdept where id=1;//再次删除神仙部 删除成功

索引

• 什么是索引：索引是数据库中提高查询效率的技术，类似于字典的目录
• 为什么使用索引：如果不使用索引数据会零散的保存在每一个磁盘块中，要想找到数据需要挨个遍历每一个磁盘块，是用索引后，磁盘块会以树桩结构保存，查询数据时能够大大降低磁盘块的访问量从而提高查询效率
• 有索引就一定好吗？ 不是，因为索引会占存储空间，如果数据量小的话添加索引反而会降低查询效率

• 索引是越多越好吗？ 不是，因为索引会占存储空间，只针对常用的查询字段创建索引，否则就浪费存储空间

• 导入数据：

• window系统 把文件解压出来后保存到 d盘根目录 source d:/item_backup.sql;

• linux系统 把文件保存到 桌面 source /home/soft01/桌面/item_backup.sql;

• 导入完成后测试： show tables; 查看是否有item2; select count(*) from item2; 看是否有172万条数据

  select * from item2 where title='100'; //耗时1.16

创建索引

• 格式： create index 索引名 on 表名(字段名(字符长度)); create index iitemtitle on item2(title);

  select * from item2 where title='100'; //耗时0.02

查看索引

• show index from 表名; show index from item2;
• 给表添加主键约束会自动根据主键字段创建索引

删除索引

• 格式：drop index 索引名 on 表名; drop index iitemtitle on item2;

  select * from item2 where title='100'; //耗时1.14

复合索引

• 通过多个字段创建的索引称为复合索引
• 格式：create index 索引名 on 表名(字段1,字段2); create index iitemtitle_price on item2(title,price);
• 查询数据时频繁使用多个字段作为查询条件时，使用复合索引

索引总结：

1. 索引是数据库中用来提高查询效率的技术，类似于目录
2. 因为索引会占用磁盘空间不是越多越好，只针对常用的查询字段创建索引
3. 数据量小的表如果添加索引会降低查询效率，所以不是有索引就一定好

事务

• 事务是数据库中执行同一业务多条sql语句的工作单元，可以保证全部执行成功或全部执行失败
• 事务的ACID特性 ：是保证事务正确执行的四大基本要素
• 原子性：Atomicity，最小不可拆分，保证全部成功或全部失败
• 一致性：Consistency，从一个一致状态到另一个一致状态
• 隔离性：Isolation， 多个事务之间互不影响
• 持久性：Durability，事务提交后数据保存到数据库文件中持久生效
• 事务相关指令：
• 开启事务 begin
• 回滚事务 rollback
• 提交事务 commit
• 设置回滚点 savepoint xxx

• 回滚到某个回滚点 rollback to xxx;

• 查看自动提交状态： show variables like '%autocommit%';

• 开启事务的第二种方式： 关闭自动提交 改成手动提交 作用和begin类似 set autocommit=0/1;

group_concat() 组连接

• 凡是在需求中提到 显示到一行(条) 时 都使用此关键字

• 案例1：查询每个部门的员工姓名，部门的员工姓名显示到一条数据中 select deptno,group_concat(ename) from emp group by deptno;

• 案例2：查询每个部门的员工姓名和对应的工资，要求每个部门的信息显示到一条数据内 select deptno,group_concat(ename,'-',sal) from emp group by deptno;

面试题

• 创建学生成绩表student,id主键 name姓名 subject学科 score分数 create table student(id int primary key auto_increment,name varchar(10),subject varchar(5),score int);
• 保存以下12条数据： 张三 语文 66，张三 数学 77，张三 英语 55，张三 体育 77， 李四 语文 59，李四 数学 88，李四 英语 78，李四 体育 95， 王五 语文 75，王五 数学 54，王五 英语 98，王五 体育 88 insert into student (name,subject,score) values ('张三','语文',66),('张三','数学',66), ('张三','英语',66),('张三','体育',66), ('李四','语文',59),('李四','数学',88), ('李四','英语',78),('李四','体育',95), ('王五','语文',75),('王五','数学',54), ('王五','英语',98),('王五','体育',88);
• 查询每个人的平均分 从大到小 select name,avg(score) a from student group by name order by a desc;
• 查询每个人的姓名以及 学科和成绩的对应信息 每个人信息显示一行 select name,group_concat(subject,'-',score) from student group by name;
• 查询每个人的最高分和最低分 select name,max(score),min(score) from student group by name;
• 查询每个人不及格的科目以及分数，不及格的科目数量 select name,group_concat(subject,'-',score),count(*) from student where score<60 group by name;