数据库相关

                                                                                     视图

视图:数据库中表和视图都是其内部的对象,视图可以理解成一条虚拟的表,视图本质上就是取代了一段SQL查询语句

为什么使用视图:因为有些数据的查询需要书写大量的SQL语句,每次书写浪费时间,使用视图可以起到SQL语句的重用作用,可以隐藏敏感信息

create view 视图名 as 子查询

1.创建一个10号部门员工的视图

create view v_emp_10 as (select * from emp where deptno=10);

2.创建一个没有工资的员工表视图

create view v_emp_nosal as (select empno,ename,hiredate,comm,job from emp);

    视图分类

简单视图:创建视图的子查询不包含:去重,函数,分组,关联查询,简单视图可以对表中的数据进行,增加,修改,删除,查询(都是对原表中的数据进行操作)

复杂视图:和简单视图相反,只能进行查询

1.插入数据到视图

insert into v_emp_10(enpno,ename) values(10010,'Tom');

数据污染:往视图中插入一条视图中不可见,但是原表中存在的数据称为数据污染

通过with check option解决数据污染问题

create view v_emp_20 as(select * from emp where deptno=20 )with  check option;

测试:

insert into v_emp_20(enmp,ename) values(10012,'lucy');//报错

insert into v_emp_20(enmp,ename,deptno) values(10012,'lucy',20);

2.修改和删除只能对视图中存在的数据进行操作

3.修改视图

create or replace view 视图名 as (子查询);

create or replace view v_emp_20 as (select * from  emp where deptno=20 and sal>2000);

4.删除视图

drop view 视图名

drop view e_emp_10;

5.视图别名:如果创建视图的子查询中使用别名,则对视图进行增删改查时只能使用别名

create view v_emp as (select ename name from emp );

此时在视图中只能使用name

约束

约束:就是给表字段添加限制条件

非空约束:not null,创建表的时候在字段后面添加 not null

create table t1(id int,age int not null);

唯一约束 unique

create table t2(id int,age int unique);

主键约束 primary key 唯一且非空

默认约束 default 给字段设置默认值

create table t3( id int,age int default 20);

insert into t3 (id) values(1);

外键约束 用来建立关系的字段称为外键

外键约束:添加外键约束的字段,值可以为null,可以重复,但是不能是关联表中不存在的数据,外键指向的数据不能先删除,外键指向的表不能先删除

格式:,constraint 约束名称 foreign key (外键字段名) references 表名(主键名)

工作中除非特定场所一般不使用外键约束,因为添加约束后会影响测试效率,一般通过代码建立逻辑外键

                                                                                    索引

索引:索引是数据库中用来提高查询效率的技术,类似于目录

为什么使用索引:如果不使用索引,数据会零散的保存在磁盘块中,查询数据需要挨个遍历每一个磁盘块,直达找到数据位置,使用索引后会将磁盘块类似树状结构保存,查询数据时会大大降低访问磁盘块数量,从而提高查询效率

索引越多越好吗?

索引会占用磁盘空间,只对常用的查询字段创建索引

索引就一定好吗?

如果表中数据量很少,添加索引反而会影响查询效率

创建索引  create index 索引名 on 表名(字段(字段长度));

create index index_item_title on item2(title);

查看索引

show index from 表名

show index from item2;

只要是给表添加主键约束,则数据库会为此表自动创建主键字段的索引

删除索引

drop index 索引名 on 表名

drop index index_item_title on item2;

复合索引

通过多个字段创建的索引称为复合索引

create index 索引名 on 表名(字段1,字段2..);

create index index_item_title_price on item2(title,price);

事务

事务的ACID特性

Atomicity(原子性):最小不可拆分,全部成功或全部失败

Cobsistency(一致性):从一个一致状态到另一个一致状态

IsoIation(隔离性):多个事务之间互补影响

Durablity(持久性):事务完成后数据提交到数据库持久保存

      显示在一行

group_concat(字段1,字段2..);

查询每个人的科目和分数显示在一行

select name, group_concat( subject,':',score) from student group by name;

+------+----------------------------------+

| name | group_concat( subject,':',score) |

+------+----------------------------------+

| 李四 | 英语:78,语文:59,数学:88,体育:95  |

| 王五 | 英语:98,数学:54,语文:75,体育:88  |

| 张三 | 体育:77,英语:55,数学:77,语文:66  |

+------+----------------------------------+

查询每个人不及格的科目以及分数,不及格的科目数量

select name,group_concat(subject,'-',score),count(*) from student  where score<60 group by name;

+------+---------------------------------+----------+

| name | group_concat(subject,'-',score) | count(*) |

+------+---------------------------------+----------+

| 李四 | 语文-59                        |        1 |

| 王五 | 数学-54,体育-52                |        2 |

| 张三 | 英语-55                        |        1 |

+------+---------------------------------+----------+