目录
视图
视图是一个虚拟表,其内容由查询定义。跟真实的表一样,视图包含一系列带有名称的列和行数据。视图的数据变化会影响到基表,基表的数据变化也会影响到视图
创建视图?:
create view 视图名 as select 语句;
create view v_ename_dname as select ename,dname from RMP,DEPT
where EMP.eptono=DEPT,deptno
然后查看一下这个 v_ename_dname视图:
select * from v_ename_dname order by dname;
+--------+------------+
| ename | dname ?? |
+--------+------------+
| CLARK | ACCOUNTING |
| KING ? | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH | RESEARCH ? |
| JONES | RESEARCH ? |
| SCOTT | RESEARCH ? |
| ADAMS | RESEARCH ? |
| FORD ? | RESEARCH ? |
| ALLEN | SALES ?? |
| WARD ? | SALES ?? |
| MARTIN | SALES ? ?|
| BLAKE | SALES ? |
| TURNER | SALES ?? |
| JAMES | SALES ?? |
+--------+------------+
select emp.ename,dept.dname,dept.deptno from emp,dept where
emp.deptno=dept.deptno order by dname;
update v_ename_dname set ename='TEST' ?where ename='CLARK';
select * from EMP ?where ename='CLARK';
select * from EMP ?where ename='TEST';
mysql> update EMP set deptno=10 where ename='JAMES'; -- 修改基表
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from v_ename_dname where ename='JAMES';
+-------+----------+
| ename | dname ?|
+-------+----------+
| JAMES | RESEARCH | <== 视图中的数据也发生了变化
+-------+----------+
drop view 视图名;
?
用户管理
MySQL中的用户,都存储在系统数据库mysql的user表中
mysql> use mysql;
Database changed
mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host ?? | user ???? | authentication_string ?????????? |
+-----------+---------------+-------------------------------------------+
| localhost | root ???? | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys ? ?| *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
也可以:
select *from user \G
?
?字段解释:
创建用户?
?语法:
create user '用户名'@'登录主机/ip' identified by '密码';
?案例:
mysql> create user 'gch'@'localhost' identified by '12345678';
Query OK, 0 rows affected (0.06 sec)
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user ?? ??| host ? ?| authentication_string ????????? ?|
+---------------+-----------+-------------------------------------------+
| root ???? | % ???? | *A2F7C9D334175DE9AF4DB4F5473E0BD0F5FA9E75 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys ? ?| localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| gch ???? ?| localhost | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | --新增用户
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
?
删除用户?
?语法:
drop user '用户名'@'主机名'
示例:
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user ??? ?| host ? ?| authentication_string ?????????? |
+---------------+-----------+-------------------------------------------+
| root ???? | % ???? | *A2F7C9D334175DE9AF4DB4F5473E0BD0F5FA9E75 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys ? ?| localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| gch ???? ?| localhost | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 |
+---------------+-----------+-------------------------------------------+
4 rows in set (0.00 sec)
mysql> drop user gch; ???????--尝试删除
ERROR 1396 (HY000): Operation DROP USER failed for 'gch'@'%' -- <= 直接给个用户名,不能删除,它默认是%,表示所有地方可以登陆的用户
?最好带上主机名一块删
mysql> drop user 'whb'@'localhost'; --删除用户
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,authentication_string from user;
+---------------+-----------+-------------------------------------------+
| user ??? ?| host ? ?| authentication_string ????????? ?|
+---------------+-----------+-------------------------------------------+
| root ???? | % ?? ??| *A2F7C9D334175DE9AF4DB4F5473E0BD0F5FA9E75 |
| mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| mysql.sys ? ?| localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+---------------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)
set password=password('新的密码');
set password for '用户名'@'主机名'=password('新的密码');
数据库的权限?
?
刚创建的用户没有任何权限,需要给用户授权
grant 权限列表 on 库.对象名 to '用户名'@'登陆位置' [identified by '密码']
权限说明:
案例:
给gch赋予test数据库下文件的select权限:
//授权前
mysql> show databases;
+--------------------+
| Database ???? ?|
+--------------------+
| information_schema |
+--------------------+
grant select on test.* to 'gch'@'localhost';
//授权后
+--------------------+
| Database ???? ?|
+--------------------+
| information_schema |
| test ??????? | ?--赋完权之后,就能看到新的表
+--------------------+
2 rows in set (0.01 sec)
授权后要刷新一下:
flush privileges;
revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置';
?案例:
回收gch对test库的所有权限
revoke all on test.* from 'whb'@'localhost';
?