数据库总结(一):基本SQL
数据库总结(二):基本查询
数据库总结(三):分组,联结
数据库总结(四):表设计之关联关系
数据库总结(五):视图,约束,索引
视图
- 什么是视图: 数据库中表和视图都是其内部的对象,视图可以理解成是一张虚拟的表,视图本质就是取代了一段sql查询语句
- 为什么使用视图:因为有些数据的查询需要书写大量的sql语句,每次书写开发效率太低,使用视图可以重用sql语句,可以隐藏敏感信息
- 创建视图的格式:
create view 视图名 as (子查询);
-创建视图
create view v_emp_10 as (select * from emp where deptno=10);
-查询视图内的数据
select * from v_emp_10; - 练习: 创建30号部门工资大于2000的视图 v_emp_30
create view v_emp_30 as (select * from emp where deptno=30 and sal>2000); - 创建一个没有工资的员工表视图
create view v_emp_nosal as (select empno,ename,job,comm,hiredate,deptno from emp);
视图分类
- 简单视图:创建视图的子查询中不包含去重、分组查询、聚合函数、关联查询的视图称为简单视图,可以对视图中的数据进行增删改查操作
- 复杂视图:和简单视图相反,只能进行查询
- 创建显示每个部门工资总和,平均工资、最大工资、最小工资的复杂视图:
create view v_emp_info as (select deptno,sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno);
对简单视图进行增删改操作,操作方式和table一样
- 插入数据
insert into v_emp_10(empno,ename) values(10011,‘Tom’);
insert into v_emp_10(empno,ename,deptno) values(10012,‘Jerry’,20);
以上两行都为数据污染
- 如果插入一条数据在原表中显示但是在视图中不显示,则称为数据污染
- 数据污染可以通过 with check option 关键字解决
create view v_emp_20 as (select * from emp where deptno=20) with check option;
-插入数据
insert into v_emp_20 (empno,ename,deptno) values(10013,‘刘德华’,30); //插入数据失败
insert into v_emp_20 (empno,ename,deptno) values(10013,‘刘德华’,20); //插入数据成功!
-删除和修改数据 只能操作视图中存在的数据
delete from v_emp_20 where deptno=10; //没有数据被删除
update v_emp_20 set sal=666 where deptno=10;//没有数据被修改
修改视图
- 创建或替换,如果不存在则创建,如果存在则替换
create or replace view v_emp_20 as (select * from emp where deptno=20 and sal>2000);
删除视图
- 格式: drop view 视图名;
drop view v_emp_20;
视图别名
- 如果创建视图时的子查询使用了别名则视图操作时只能使用别名
• create view v_emp_20 as (select ename name,sal from emp where deptno=20);
select ename from v_emp_20;//报错不认识ename
select name from v_emp_20; //执行成功
视图总结:
- 视图是数据库中的对象,代表一段sql语句,可以理解成是一张虚拟的表
- 作用:重用sql、隐藏敏感信息
- 分类:简单视图(不包含,去重、聚合函数、分组、关联查询 可以对数据进行增删改操作)和复杂视图(和简单视图相反,只能查询)
- 插入数据时可能会出现数据污染问题,通过with check option解决
- 删除和修改只能操作视图中存在的数据
- 起了别名后只能用别名
约束
- 什么是约束:约束就是给表字段添加的限制条件
主键约束 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
- 给字段设置默认值,不赋值则为默认值,赋值则为所赋值(包括null)
- 格式: 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 t_dept(
id int primary key auto_increment,
name varchar(10));
create table t_emp(
id int primary key auto_increment,
name varchar(10),
dept_id int,
constraint fk_dept foreign key (dept_id) references t_dept(id));
- 格式介绍:constraint 约束名称 foreign key(外键字段) references 表名(字段名)
- 部门表插入数据
• insert into t_dept values(null,‘神仙’),(null,‘妖怪’);
insert into t_emp values(null,‘悟空’,1);//成功
insert into t_emp values(null,‘八戒’,1);//成功
insert into t_emp values(null,‘刘德华’,3);//失败
insert into t_emp values(null,‘刘德华’,null);//成功
delete from t_dept where id=1; //删除失败
drop table t_dept; //表删除失败
delete from t_emp where dept_id=1;//删除神仙部的员工
delete from t_dept 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 i_item_title on item2(title);
select * from item2 where title=‘100’; //耗时0.02
查看索引
- show index from 表名;
show index from item2; - 给表添加主键约束会自动根据主键字段创建索引
删除索引
- 格式:drop index 索引名 on 表名;
drop index i_item_title on item2;
select * from item2 where title=‘100’; //耗时1.14
复合索引
- 通过多个字段创建的索引称为复合索引
- 格式:create index 索引名 on 表名(字段1,字段2);
create index i_item_title_price on item2(title,price); - 查询数据时频繁使用多个字段作为查询条件时,使用复合索引
索引总结:
- 索引是数据库中用来提高查询效率的技术,类似于目录
- 因为索引会占用磁盘空间不是越多越好,只针对常用的查询字段创建索引
- 数据量小的表如果添加索引会降低查询效率,所以不是有索引就一定好
事务
- 事务是数据库中执行同一业务多条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;