数据库总结（一）：基本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;
2413
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
