- # 视图
- /*
- 含义: 虚拟表, 和普通表一样使用
- mysql5.1 版本出现的新特性, 是通过表动态生成的数据
- 比如: 舞蹈班和普通班级的对比
- 创建语法的关键字 是否实际占用物理空间 使用
- 视图 create view 只是保存了 sql 逻辑 增删改查, 只是一般不能增删改
- 表 create table 保存了数据 增删改查
- */
- # 案例: 查询姓张的学生名和专业名
- SELECT stuname,majorname
- FROM stuinfo s
- INNER JOIN major m ON s.`majorid`= m.`id`
- WHERE s.`stuname` LIKE '张 %';
- CREATE VIEW v1
- AS
- SELECT stuname,majorname
- FROM stuinfo s
- INNER JOIN major m ON s.`majorid`= m.`id`;
- SELECT * FROM v1 WHERE stuname LIKE '张 %';
- # 一, 创建视图
- /*
- 语法:
- create view 视图名
- as
- 查询语句;
- */
- USE myemployees;
- #1. 查询姓名中包含 a 字符的员工名, 部门名和工种信息
- #1创建
- 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 j.job_id = e.job_id;
- #2使用
- SELECT * FROM myv1 WHERE last_name LIKE '%a%';
- #2. 查询各部门的平均工资级别
- #1创建视图查看每个部门的平均工资
- CREATE VIEW myv2
- AS
- SELECT AVG(salary) ag,department_id
- FROM employees
- GROUP BY department_id;
- #2使用
- SELECT myv2.`ag`,g.grade_level
- FROM myv2
- JOIN job_grades g
- ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
- #3. 查询平均工资最低的部门信息
- SELECT * FROM myv2 ORDER BY ag LIMIT 1;
- #4. 查询平均工资最低的部门名和工资
- CREATE VIEW myv3
- AS
- SELECT * FROM myv2 ORDER BY ag LIMIT 1;
- SELECT d.*,m.ag
- FROM myv3 m
- JOIN departments d
- ON m.`department_id`=d.`department_id`;
- # 二, 视图的修改
- # 方式一:
- /*
- create or replace view 视图名
- as
- 查询语句;
- */
- SELECT * FROM myv3
- CREATE OR REPLACE VIEW myv3
- AS
- SELECT AVG(salary),job_id
- FROM employees
- GROUP BY job_id;
- # 方式二:
- /*
- 语法:
- alter view 视图名
- as
- 查询语句;
- */
- ALTER VIEW myv3
- AS
- SELECT * FROM employees;
- # 三, 删除视图
- /*
- 语法: drop view 视图名, 视图名,...;
- */
- DROP VIEW emp_v1,emp_v2,myv3;
- # 四, 查看视图
- DESC myv3;
- SHOW CREATE VIEW myv3;
- # 五, 视图的更新
- CREATE OR REPLACE VIEW myv1
- AS
- SELECT last_name,email,salary*12*(1+IFNULL(commission_pct,0)) "annual salary"
- FROM employees;
- CREATE OR REPLACE VIEW myv1
- AS
- SELECT last_name,email
- FROM employees;
- SELECT * FROM myv1;
- SELECT * FROM employees;
- #1. 插入
- INSERT INTO myv1 VALUES('张飞','[email protected]');
- #2. 修改
- UPDATE myv1 SET last_name = '张无忌' WHERE last_name='张飞';
- #3. 删除
- DELETE FROM myv1 WHERE last_name = '张无忌';
- # 具备以下特点的视图不允许更新
- #1包含以下关键字的 sql 语句: 分组函数, distinct,group by,having,union 或者 union all
- CREATE OR REPLACE VIEW myv1
- AS
- SELECT MAX(salary) m,department_id
- FROM employees
- GROUP BY department_id;
- SELECT * FROM myv1;
- # 更新
- UPDATE myv1 SET m=9000 WHERE department_id=10;
- #2常量视图
- CREATE OR REPLACE VIEW myv2
- AS
- SELECT 'john' NAME;
- SELECT * FROM myv2;
- # 更新
- UPDATE myv2 SET NAME='lucy';
- #3Select 中包含子查询
- CREATE OR REPLACE VIEW myv3
- AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
- FROM departments;
- # 更新
- SELECT * FROM myv3;
UPDATE myv3 SET 最高工资 = 100000;
- #4join
- CREATE OR REPLACE VIEW myv4
- AS
- SELECT last_name,department_name
- FROM employees e
- JOIN departments d
- ON e.department_id = d.department_id;
- # 更新
- SELECT * FROM myv4;
- UPDATE myv4 SET last_name = '张飞' WHERE last_name='Whalen';
- INSERT INTO myv4 VALUES('陈真','xxxx');
- #5from 一个不能更新的视图
- CREATE OR REPLACE VIEW myv5
- AS
- SELECT * FROM myv3;
- # 更新
- SELECT * FROM myv5;
UPDATE myv5 SET 最高工资 = 10000 WHERE department_id=60;
- #6where 子句的子查询引用了 from 子句中的表
- CREATE OR REPLACE VIEW myv6
- AS
- SELECT last_name,email,salary
- FROM employees
- WHERE employee_id IN(
- SELECT manager_id
- FROM employees
- WHERE manager_id IS NOT NULL
- );
- # 更新
- SELECT * FROM myv6;
- UPDATE myv6 SET salary=10000 WHERE last_name = 'k_ing';
- # 练习
- # 一, 创建视图 emp_v1, 要求查询电话号码以'011'开头的员工姓名和工资, 邮箱
- CREATE OR REPLACE VIEW emp_v1
- AS
- SELECT last_name,salary,email
- FROM employees
- WHERE phone_number LIKE '011%';
- # 二, 创建视图 emp_v2, 要求查询部门的最高工资高于 12000 的部门信息
- CREATE OR REPLACE VIEW emp_v2
- AS
- SELECT MAX(salary) mx_dep,department_id
- FROM employees
- GROUP BY department_id
- HAVING MAX(salary)>12000;
- SELECT d.*,m.mx_dep
- FROM departments d
- JOIN emp_v2 m
- ON m.department_id = d.`department_id`;
- # 标识列
- /*
- 又称为自增长列
- 含义: 可以不用手动的插入值, 系统提供默认的序列值
- 特点:
- 1, 标识列必须和主键搭配吗? 不一定, 但要求是一个 key
- 2, 一个表可以有几个标识列? 至多一个!
- 3, 标识列的类型只能是数值型
- 4, 标识列可以通过 SET auto_increment_increment=3; 设置步长
- 可以通过 手动插入值, 设置起始值
- */
- # 一, 创建表时设置标识列
- DROP TABLE IF EXISTS tab_identity;
- CREATE TABLE tab_identity(
- id INT ,
- NAME FLOAT UNIQUE AUTO_INCREMENT,
- seat INT
- );
- TRUNCATE TABLE tab_identity;
- INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john');
- INSERT INTO tab_identity(NAME) VALUES('lucy');
- SELECT * FROM tab_identity;
- SHOW VARIABLES LIKE '%auto_increment%';
- SET auto_increment_increment=3;
来源: http://www.bubuko.com/infodetail-3053066.html