第3.21课 上课 MySQL数据库进阶， 子查询的使用， 数据库视图

3_21

MySQL数据库进阶

 子查询的使用

  测试数据

 

CREATE TABLE e_user(

id int NOT NULL PRIMARY KEY AUTO_INCREMENT,

t_name varchar(10) DEFAULT NULL,

t_job varchar(50) DEFAULT NULL,

t_salary decimal(10, 2),

t_birthday date,

t_sex char(3) default '男'

)

 

insert into e_user values(null,'lis','Java软件工程师', 12000.0,'1980-09-22','男');

insert into e_user values(null,'admin','Android工程师', 10000.0,'1982-10-01','女');

insert into e_user values(null,'tom','DBA工程师', 8000.0,'1985-03-08','男');

insert into e_user values(null,' dave ','前端工程师', 9000.0,'1985-10-01','女');

insert into e_user values(null,'Amy','测试工程师', 7500.0,'1990-11-13','女');

insert into e_user values(null,'Kim','产品经理', 8500.0,'1991-12-05','男');

insert into e_user values(null,'Mary','项目经理', 9000.0,null,'女');

  单行子查询

1）只返回一行结果；

2）使用单行比较运算符；

 

 

 

 

 

 

 

 

-- 查询薪资大于平均的员工信息

 

select * from e_user where t_salary >(select avg(t_salary) from e_user);

 

 

 

 

 

 

 

  多行子查询

由于列子查询返回的结果集是 N 行一列，因此不能直接使用 = > < >= <= <> 这些比较标量结果的操作符。

 

在列子查询中可以使用 IN、ANY、SOME 和 ALL 操作符：

   IN：在指定项内，同IN(v1,v2,…)

   ANY:与比较操作符联合使用，表示与子查询返回的任何值比较是否为true,则返回true

 

 

-- 查询薪资小于 java软件工程师的 员工名和职位以及薪资

 

select u.id,u.t_name,u.t_job,u.t_salary from e_user u

where u.t_salary < ANY

(select t_salary from e_user where t_job ='Java软件工程师');

 

 

   ALL:与比较操作符联合使用，表示与子查询返回的所有值比较都为true ，则返回true

 

 

-- 查询薪资小于等于 前端工程师的 员工名和职位以及薪资,并排除前端工程师职位

 

select u.id,u.t_name,u.t_job,u.t_salary from e_user u

where u.t_salary <= ALL (select t_salary from e_user where t_job ='前端工程师')

AND t_job <> '前端工程师';

 

 

 数据库视图

使用视图的好处：

1）可以限制对数据的访问；

2）可以使复杂的查询变得简单；

3）提供了数据的独立性；

4）提供了对相同数据的不同显示；

 

  创建视图

视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

 

创建视图的语法：

CREATE VIEW 视图名 AS

SELECT column_name(s)

FROM table_name

WHERE condition

 

 

--创建视图

 

CREATE VIEW v_user_view AS SELECT t_name,t_job FROM e_user;

 

 

 

  使用视图查找数据

查询视图和查询表的语法是一样的。

 

SELECT * FROM v_user_view;

  修改视图

使用CREATE OR REPLACE VIEW...语句修改视图。

 

语法（可以创建要可以更新视图操作）

CREATE OR REPLACE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

 

 

-- 更新视图

CREATE OR REPLACE VIEW v_user_view AS select id,t_name,t_job from e_user;

  删除视图

--删除视图

DROP VIEW 视图名;

 

注意：删掉视图不会导致数据的丢失，因为视图是基于数据库的表之上的一个查询定义。