数据库操作语句5

#视图

#创建视图
CREATE VIEW emp_view AS SELECT empno,ename,job,deptno FROM emp
#查看视图
DESC emp_view
#查看创建视图的指令
SHOW CREATE VIEW emp_view
#更新视图,基表也会随之变化
ALTER VIEW emp_view AS SELECT empno,ename,job,deptno FROM emp
SELECT *FROM emp_view
UPDATE emp_view SET job='work' WHERE empno=7369
#删除
DROP VIEW emp_view
#视图中还可以使用视图
CREATE VIEW emp_view02 AS SELECT empno,ename FROM emp_view

#三张表生成视图
#使用三表联合查询得到结果
SELECT empno,ename,dname,grade FROM emp,dept,salgrade
		WHERE emp.deptno=dept.deptno AND (sal BETWEEN losal AND hisal)
#根据结果创建视图
CREATE VIEW emp_view03 AS
		SELECT empno,ename,dname,grade FROM emp,dept,salgrade
			WHERE emp.deptno=dept.deptno AND (sal BETWEEN losal AND hisal)

#Mysql用户管理
#开发中根据不同的开发人员赋给它相应的操作权限

#创建用户,localhost限制登录的IP
CREATE USER 'wzg_01'@'localhost' IDENTIFIED BY'123456'
SELECT *FROM mysql.user
#删除用户 用户名+IP
DROP USER 'wzg_01'@'localhost'
#修改自己的密码
SET PASSWORD =PASSWORD('wzg')
#修改其他用户的密码需要根据用户权限，高权限可以改低权限
SET PASSWORD FOR'root'@'localhost'=PASSWORD('wzg')

#使用root用户创建news表
CREATE TABLE news(
	id INT,
	content VARCHAR(32));
INSERT INTO news VALUES(100,'北京新闻')
#给wzg_01分配查看修改news表的权限
GRANT SELECT,INSERT ON wzg_db02.news TO 'wzg_01'@'localhost'
#增加权限
GRANT UPDATE ON wzg_db02.news TO 'wzg_01'@'localhost'
#收回权限
REVOKE ALL ON wzg_db02.news TO 'wzg_01'@'localhost'
#刷新权限
FLUSH PRIVILEGES