Catalog

1, Building database and table

2, Use of views

1. explanation

2. format

3. pay attention to

4. Example demonstration

5. advantages

6. disadvantages

1, Building database and table

create database scort
use scort
create table emp
(
	empno int primary key,
	ename nvarchar(10),
	sal int,
	deptno int
)
insert into emp values (7369,'smith',1800,20);
insert into emp values (7499,'allen',1500,10);
insert into emp values (7521,'ward',1900,30);
insert into emp values (7566,'jones',2000,30);
insert into emp values (7654,'martin',1800,10);
insert into emp values (7698,'blake',1800,30);

2, Use of views

1. explanation

2. format

create view The name of the view
as
    --select You can't add begin
    select Sentence
    --select Can't add after end

3. pay attention to

The select statement for creating a view must specify aliases for all calculated columns

--error
create view via
as
    select AVG(sal) from emp

--ok
create view via
as
    select AVG(sal) "avg_sal" from emp

 

4. Example demonstration

Do not use view method

Find out the number of the Department with the highest average wage and the average wage of the Department
//Train of thought:
1.Find out the department number and average wage of all departments
select deptno,AVG(sal) "avg_sal"
    from emp
    group by deptno
2.Turn the department number and average wage of all the departments queried into a temporary table"T"
select * 
    from (
        select deptno,AVG(sal) "avg_sal"
        from emp
        group by deptno
    )"T"
3.Find out the average wage of the highest department and turn it into a temporary table"E"
select MAX("E"."avg_sal")
    from (	
        select deptno,AVG(sal) "avg_sal"
        from emp
        group by deptno	
)"E"
4.Give Way"T"The average wage of is equal to"E"	
select * 
    from (
        select deptno,AVG(sal) "avg_sal"
        from emp
        group by deptno
    )"T"
    where "T"."avg_sal"=(
        select MAX("E"."avg_sal")
            from (	
                select deptno,AVG(sal) "avg_sal"
                from emp
                group by deptno	
        )"E"
    )

Use view method

1.Create view
create view viemp
as
    select deptno,AVG(sal) "avg_sal"
        from emp
        group by deptno
2.query
select * from viemp
    where avg_sal=(select MAX(avg_sal) from viemp)

5. advantages

6. disadvantages