sql视图

#视图:虚拟表，和普通表一样使用

视图    CREATE VIEW    不占用物理空间，只保存SQL逻辑    增删改查，一般不用
表    CREATE TABLE     占用物理空间

#查询姓张的学生名和专业名
SELECT stuname ,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid` = m.`majorid`
WHERE s.`stuname` LIKE '张%';

CREATE VIEW v1
AS
SELECT stuname ,majorname
FROM stuinfo s
INNER JOIN major m ON s.`majorid` = m.`majorid`
;

SELECT * FROM v1 WHERE stuname LIKE '张%';

#一、创建视图:create view 视图名 as 查询语句
USE myemployees;

1.查询姓名包含a字符的员工名、部门名和工种信息

CREATE VIEW myv1
AS 
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
JOIN jobs j
ON e.job_id = j.job_id;

SELECT * FROM myv1 WHERE last_name LIKE '%a%';

2.查询各部门的平均工资级别