#插入语句的一些补充
create table emp_copy4
AS
select * from emp where sal = null

#子查询插入多条数据
insert into emp_copy4
select * from emp where deptno = 20

insert into emp_copy5(empno,ename,sal)
select empno,ename,sal from emp where deptno = 20

#相关子查询

#删除高于自己部门平均工资的员工信息
delete from emp e
where sal >(select avg(sal) from emp where deptno=e.deptno)

#索引index 
#优点：加快查询速度；
#缺点：占内存，降低了增删改的速度（因为索引表需要同步）
create table test7(
id int(9) atuo_increment,
name varchar(20) default '' not null,
primary key(id),
index(name)
)

#经常需要作为条件的列最好建索引
create table emp_copy6
as
select * from emp


#视图view 命名的查询
create view emp_10
AS
select * from emp where deptno = 10

select * from emp_10
#视图是一个虚表，不存数据，存的是查询

create view emp_20
AS
select * from emp where deptno = 20
with check option #视图可以查询到的数据，才可以进行增删改