文章目录
1. 视图
1.1 创建，使用，删除视图
视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图的数据变化会影响到基表，基表的数据变化也会影响到视图。
下面是创建视图的语法
create view 视图名 as select 语句；
案例，使用emp表和dept表，表结构如下
mysql> desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno | int(6) unsigned zerofill | NO | | NULL | |
| ename | varchar(10) | YES | | NULL | |
| job | varchar(9) | YES | | NULL | |
| mgr | int(4) unsigned zerofill | YES | | NULL | |
| hiredate | datetime | YES | | NULL | |
| sal | decimal(7,2) | YES | | NULL | |
| comm | decimal(7,2) | YES | | NULL | |
| deptno | int(2) unsigned zerofill | YES | | NULL | |
+----------+--------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
mysql> desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO | | NULL | |
| dname | varchar(14) | YES | | NULL | |
| loc | varchar(13) | YES | | NULL | |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
现在想查emp表中的ename列和dept表中的dname列，可以使用内连接
mysql> select ename, dname from emp inner join dept on emp.deptno=dept.deptno;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.16 sec)
也可以创建视图，实际上是创建了一个表。
mysql> create view my_view as select ename, dname from emp inner join dept on emp.deptno=dept.deptno;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept |
| emp |
| exam |
| my_view |
| salgrade |
| stu |
+-----------------+
6 rows in set (0.00 sec)
mysql> select * from my_view;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
1.2 特点，视图和基表之间互相影响
修改视图，表会收到影响。
mysql> update my_view set ename='smith' where ename = 'SMITH';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select ename from emp;
+--------+
| ename |
+--------+
| smith |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+
14 rows in set (0.00 sec)
修改表，视图会收到影响
mysql> update dept set dname = 'sales' where deptno=30;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from my_view;
+--------+------------+
| ename | dname |
+--------+------------+
| smith | RESEARCH |
| ALLEN | sales |
| WARD | sales |
| JONES | RESEARCH |
| MARTIN | sales |
| BLAKE | sales |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | sales |
| ADAMS | RESEARCH |
| JAMES | sales |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
1.3 视图的规则和限制
- 与表一样，必须唯一命名（不能出现同名视图或表名）
- 创建视图数目无限制，但要考虑复杂查询创建为视图之后的性能影响
- 视图不能添加索引，也不能有关联的触发器或者默认值
- 视图可以提高安全性，必须具有足够的访问权限
- order by 可以用在视图中，但是如果从该视图检索数据 select 中也含有 order by ,那么该视图中的 order by 将被覆盖
- 视图可以和表一起使用（视图就是个表！）
2. 用户管理
2.1 查看，创建用户
用户信息存在mysql数据库中的user表中
mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | -- 一串字符串 |
| localhost | mysql.session | -- 一串字符串 |
| localhost | mysql.sys | -- 一串字符串 |
| % | root | -- 一串字符串 |
+-----------+---------------+-------------------------------------------+
一些字段的解释
- host： 表示这个用户可以从哪个主机登陆，如果是localhost，表示只能从本机登陆，host设置为%表示可以从公网登录。
- user： 用户名
- authentication_string： 用户密码通过password函数加密后的
下面是创建用户的语法。实际上就是向user表中插入数据(不推荐这样做)
create user '用户名'@localhost/ip IDENTIFIED by '密码';
下面创建一个用户并登录
mysql> create user 'lyf'@'localhost' identified by '123456Lyf';
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
-- 不行的话就刷新一下
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql> create user 'lyf'@'localhost' identified by '123456Lyf';
Query OK, 0 rows affected (0.01 sec)
mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | *C6C2DCDA2726E8007FDC18514A9C1E2410CCCE32 |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | root | *82C1867CE488BC7A5340B1032EF2911D983A9481 |
| localhost | lyf | *DF64AC5EF13FD5B877CB17C51721BBCFF6359DEE |
+-----------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql> select user,host from user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| lyf | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
5 rows in set (0.00 sec)
[lyf@hcss-ecs-3db9 ~]$ mysql -ulyf -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 128
Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
2.2 修改密码和删除用户
修改密码的语法，实际上就是对user表做更新操作
set password=password('新的密码');
-- root用户可以修改任意用户的密码
set password for '用户名'@'主机名'=password('新的密码');
修改密码后登录
mysql> set password = password('123Lyf');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> quit
Bye
[lyf@hcss-ecs-3db9 ~]$ mysql -ulyf -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 130
Server version: 5.7.44 MySQL Community Server (GPL)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show database;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'database' at line 1
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
删除用户，实际上就是对user表做删除操作
drop user '用户名'@'主机名'
-- 已经切换到了root用户
mysql> drop user 'lyf'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| root | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.01 sec)
3. 数据库的权限
| 权限分布 | 可能的设置的权限 |
|---|---|
| 表权限 | ‘Select’, ‘Insert’, ‘Update’, ‘Delete’, ‘Create’, ‘Drop’, ‘Grant’, ‘References’, ‘Index’, ‘Alter’ |
| 列权限 | ‘Select’, ‘Insert’, ‘Update’, ‘References’ |
| 过程权限 | ‘Execute’, ‘Alter Routine’, ‘Grant’ |
3.1 给用户增加权限
基础语法
grant 权限列表 on 库.对象名 to '用户名'@'登陆位置'
grant select on ...
grant select, delete, create on ....
grant all [privileges] on ... -- 表示赋予该用户在该对象上的所有权限
on后面加*.*表示赋予本系统中的所有数据库的所有对象（表，视图，存储过程等）权限on后面加库名.*代表赋予表示某个数据库中的所有数据对象(表，视图，存储过程等) 权限
下面有个例子
-- 这是root用户，在testdb数据库下建了一个表
mysql> create table can_not_see_me (id int, name varchar(20));
Query OK, 0 rows affected (0.01 sec)
-- 切到普通用户(lyf)，目前没有任何权限，没有找到该数据库。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
-- 切到root用户，给该用户添加权限
mysql> grant all on testdb.can_not_see_me to 'lyf'@'%';
Query OK, 0 rows affected (0.00 sec)
-- 切到普通用户
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| testdb |
+--------------------+
2 rows in set (0.00 sec)
mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> desc can_not_see_me;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into can_not_see_me values(1, '张三');
Query OK, 1 row affected (0.01 sec)
mysql> select * from can_not_see_me;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
+------+--------+
1 row in set (0.00 sec)
下面的命令可以查看某一用户目前的权限
mysql> show grants for 'lyf'@'%';
+----------------------------------------------------------------+
| Grants for lyf@% |
+----------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lyf'@'%' |
| GRANT ALL PRIVILEGES ON `testdb`.`can_not_see_me` TO 'lyf'@'%' |
+----------------------------------------------------------------+
2 rows in set (0.00 sec)
3.2 删除用户的权限
revoke 权限列表 on 库.对象名 from '用户名'@'登陆位置'；
下面是例子
-- root用户删除用户的增加权限
mysql> show grants for 'lyf'@'%';
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for lyf@% |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'lyf'@'%' |
| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE VIEW, SHOW VIEW, TRIGGER ON `testdb`.`can_not_see_me` TO 'lyf'@'%' |
+-------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
-- 切到普通用户(lyf)
mysql> select * from can_not_see_me;
+------+--------+
| id | name |
+------+--------+
| 1 | 张三 |
+------+--------+
1 row in set (0.00 sec)
mysql> insert into can_not_see_me values(2, '李四');
ERROR 1142 (42000): INSERT command denied to user 'lyf'@'localhost' for table 'can_not_see_me'
扫一扫
5227
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
