sql外连接与视图练习

外连接

#mysql外连接
SELECT deptname,ename,job FROM emp,dept WHERE emp.depton=dept.deptnum
ORDER BY deptname
SELECT*FROM dept
#
CREATE TABLE stu(
id INT,NAME VARCHAR(32))
INSERT INTO stu VALUES(1,'Tom'),(2,'jack'),(3,'liu'),(4,'hong')

CREATE TABLE exam(id INT,grade INT);
INSERT INTO exam VALUES(1,99),(2,88),(11,60);
SELECT*FROM exam
#左连接（显示所有人的成绩，如果没成绩也显示该学生的姓名，ID，成绩为空）
#select? from 表1 left join 表2 on 条件
SELECT stu.id,NAME,grade FROM stu LEFT JOIN exam ON stu.id=exam.id

#右连接
SELECT stu.id,NAME,grade FROM stu RIGHT JOIN exam ON stu.id=exam.id

视图

CREATE TABLE work01
(
id INT,
NAME VARCHAR(32),
depid INT
)
INSERT INTO work01 VALUES(1,'刘德华',1),(2,'孙策',12),(6,'孙华',24)
SELECT*FROM work01
CREATE TABLE dept(
depid INT,
home VARCHAR(32),
avgsal INT
)
SELECT*FROM dept
INSERT INTO dept VALUES(12,'保安部',100),(24,'销售部',2000)
#视图
CREATE VIEW view_work01(id,NAME,depid) AS SELECT id,NAME,depid FROM work01
SELECT*FROM view_work01
#创建多表视图
CREATE VIEW view02(id,NAME,home)AS  SELECT work01.id,work01.name,dept.home  FROM work01,dept
WHERE work01.depid=dept.depid
SELECT*FROM view02
#修改视图、
ALTER VIEW view_work01(id,NAME) AS SELECT id,NAME FROM work01
CREATE OR REPLACE VIEW view_work01(id,NAME,depid) AS SELECT id,NAME,depid FROM work01
#查看视图结构
DESC view02

SHOW CREATE  VIEW view02
#更新视图

UPDATE view_work01 SET NAME='zhangxueyou' WHERE id=1
#设置权限只能更新depid=1的
CREATE VIEW view03(id,NAME,depid) AS SELECT id,NAME,depid FROM work01 
WHERE depid=1 WITH LOCAL CHECK OPTION

SELECT*FROM view03
 insert into view03 values(7,'gcy',2)
#删除
drop view view03