(MY)SQL
1、(MY)SQL使用入门
支持 SQL 的关系数据库管理系统同样支持关系数据库三级模式结构。其中外模式包括若干视图和基本表,数据库模式包括若干基本表,内模式包括若干存储文件。
SQL 语句主要分为以下 3 个类别:
- DDL(Data Definition Languages)语句:数据定义语言,这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。常见的语句关键字有:create、drop、alter等。
- DML(Data Manipulation Languages)语句:数据操纵语句,用于添加、删除、更新和查询数据库记录,并检查数据完整性,常用的语句关键字有:insert、delete、update和select等。
- DCL(Data Contorl Languages)语句:数据控制语句,用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字有:grant、revoke等。
1.1、DDL 语句
是对数据库内部的对象进行创建、删除、修改的操作语句,它与 DML 语句的最大区别在于 DML 语句只是对表的内部数据的操作,而不涉及到表的定义、结构的修改。
1.1.1、创建数据库
CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>]
[[DEFAULT] COLLATE <校对规则名>]; #很少在服务器层面使用
mysql> create database test1;
Query OK, 1 row affected (0.00 sec)
各字段含义:
- Query OK 表示上面的命令执行成功;DML(不包含 SELECT)和 DDL 操作执行成功后都会显示 “Query OK”
- 1 row affected 表示此操作只影响了数据库中一行的记录
- 0.00 sec 表示记录了操作执行的时间
补充:字符集和校对顺序
数据库表用来存储和检索数据。不同的语言和字符集需要以不同的方式存储与检索。因此,MySQL 需要适应不同的字符集(不同的字母和字符),适应不同的排序和检索数据的方法。在讨论多种语言与字符集时,将会遇到以下重要术语:
- 字符集:为字母和符号的集合;
- 编码:为某个字符集成员的内部表示;
- 校对:为规定字符如何比较的指令。
使用何种字符集和校对的决定在服务器、数据库和表级进行。
MySQL 支持众多的字符集。为查看所支持的字符集完整列表,使用以下语句:
mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
...
#显示了所有可用的字符集以及每个字符集的描述和默认校对规则
为了查看所支持校对的安整列表,使用如下语句:
mysql> SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
...
#显示所有可用的校对,以及它们适用的字符集
通常系统管理在安装时定义了一个默认的字符集和校对。此外,也可以在创建数据库时,指定默认的字符集和校对。为了确定所用的字符集和校对,可以使用如下语句:
mysql> SHOW VARIABLES LIKE 'character#';
mysql> SHOW VARIABLES LIKE 'collation#';
实际上,字符集很少是服务器范围(甚至是数据库范围)的设置。不同的表,甚至不同的列都可能需要不同的字符集,而且两者都可以在创建时指定。为了给表指定字符集,可使用带子句的 CREATE TABLE 语句:
mysql> create table mytable(
-> column1 INT,
-> column2 VARCHAR(10)
-> )DEFAULT CHARACTER SET hebrew
-> COLLATE hebrew_general_ci;
Query OK, 0 rows affected (0.01 sec)
#创建了一个包含两列的表,并且指定了一个字符集和一个校对顺序
除了能指定字符集和校对的表范围外,MySQL 还允许对每个列设置它们,如下:
mysql> CREATE TABLE mytable2
-> (
-> column1 INT,
-> column2 VARCHAR(10),
-> column3 VARCHAR(10) CHARACTER SET latin1 collate latin1_general_ci
-> )DEFAULT CHARACTER SET hebrew
-> COLLATE hebrew_general_ci;
Query OK, 0 rows affected (0.00 sec)
校对在对用 ORDER BY 子句检索出来的数据排序时起了很大的作用。如果你需要用与创建表时不同的校对顺序排序特定的 SELECT 语句时,可以在 SELECT 语句自身中进行:
mysql> SELECT * FROM customers
-> ORDER BY lastname,firstname COLLATE latin1_general_cs;
如果想要知道系统中有哪些数据库,可以使用 show databases 命令查询:
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test1 |
+--------------------+
5 rows in set (0.00 sec)
除了刚刚建立的 test1 数据库外,还有另外 4 个数据库,其功能分别如下:
- information_schema 主要存储系统中的一些数据库对象信息,比如用户表信息、列信息、权限信息、字符集信息、分区信息等。每个用户都可以查看这个数据库,但根据权限的不同看到的内容也不相同
- performance_schema 用于存储系统性能相关的动态参数表
- sys 本身不记录系统数据,基于 information_schema 和 information_schema 之上,封装了一层更加易于调优和诊断的系统视图
- mysql 存储系统的用户权限信息
在查看了系统中存在的已有数据库后,可以使用下面的命令选择要操作的数据库:
USE 数据库名称;
mysql> use test1;
Database changed
查看数据库中的表:
mysql> show tables;
Empty set (0.00 sec)
由于 test1 是刚建立的数据库,还没有表,所以显示为空。命令行下面的 “Empty set” 表示操作的结果集为空。如果查看一下 mysql 数据库里面的表,则可以得到以下信息:
mysql> use mysql;
Database changed
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.00 sec)
1.1.2、删除数据库
DROP DATABASE 数据库名称;
mysql> drop database test1;
Query OK, 0 rows affected (0.01 sec)
删除成功后,后面却提示 “0 rows affected”,这个提示的含义是前一次 MySQL 操作所影响的记录行数,通常只对增删改的操作生效,drop 等 DDL 操作通常显示的是 “0 rows affected”。
注意:数据库删除后,下面的所有表的数据都会被删除。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
1.1.3、创建表
CREATE TABLE 表名(列名1 数据类型 列的约束条件 [DEFAULT 默认值]|[NOT NULL/NULL]|[AUTO_INCREMENT],
列名2 数据类型 列的约束条件 [DEFAULT 默认值]|[NOT NULL/NULL]|[AUTO_INCREMENT],
......
PRIMARY KEY(列名x[,...])
)[ENGINE=存储引擎类型];
#每个表只允许一个 AUTO_INCREMENT 列,而且它必须被索引
因为 MySQL 的表是以文件的形式保存在磁盘上的,所以表名的字符可以用任何文件名允许的字符。
例如,创建一个名称为 emp 的表。其中包括 3 个字段,ename(姓名),hiredate(雇用日期),sal(薪水),depno(部门编号),字段类型分别为 varchar(10),date,DECIMAL(10,2),int(2):
mysql> CREATE TABLE emp(ename VARCHAR(10),
-> hiredate date,
-> sal DECIMAL(10,2),
-> deptno INT(2));
Query OK, 0 rows affected (0.01 sec)
建表完成后,可使用 desc 命令查看建表的详细信息:
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
为了查看表的更加详细的信息,可以通过创建表的 SQL 语句来查看:
mysql> show create table emp \G
*************************** 1. row ***************************
Table: emp
Create Table: CREATE TABLE `emp` (
`ename` varchar(10) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
除了表的定义以外,还可以看到表的 engine(存储引擎)和 charset(字符集)等信息。“\G” 选项的含义是使得记录能够按照字段竖向排列,以便能够更好地显示内容较长的记录。
1.1.4、删除表
DROP TABLE 表名;
mysql> drop table emp;
Query OK, 0 rows affected (0.00 sec)
1.1.5、修改表
对于已经创建好的表,尤其是已经有大量数据的表,如果需要做一些结构上的改变,可以先将表删除(drop),然后按照新的表的定义重建表。但是这样会产生一些额外的工作,比如数据的重新加载。而且,如果有服务在访问表,也会对服务产生影响。
1.1.5.1、修改表的类型
ALTER TABLE 表名 MODIFY [COLUMN] 要修改的列名 列的定义 [FIRST|AFTER 列名x];
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(10) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
修改表 emp 的 ename 字段定义,改为 varchar(20):
mysql> alter table emp modify ename varchar(20);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1.1.5.2、增加表字段
ALTER TABLE 表名 ADD [COLUMN] 列名 列的定义 [FIRST|AFTER 列名x];
增加一个 age 字段,数据类型为 int(3):
mysql> alter table emp add column age int(3);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
1.1.5.3、删除表字段
ALTER TABLE 表名 DROP [COLUMN] 列名;
删除 age 字段:
mysql> alter table emp drop age;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptno | int(2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1.1.5.4、字段改名
ALTER TABLE 表名 CHANGE [COLUMN] 旧列名 新列名 新列名的定义 [FIRST|AFTER col_name];
将 deptno 改名为 deptnumber:
mysql> alter table emp change deptno deptnumber int(4);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptnumber | int(4) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
1.1.5.5、修改字段的排列顺序
在前面的语句中有一个选项:[FIRST|AFTER col_name],这个选项可以用来修改字段在表中的位置。ADD 增加的字段默认是加在表的最后位置,而 CHANGE/MODIFY 默认都不会改变字段的位置。
将新增的字段 dirth date 加在 ename 之后:
mysql> alter table emp add birth date after ename;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| ename | varchar(20) | YES | | NULL | |
| birth | date | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptnumber | int(4) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
修改字段 birth,将它放在最前面:
mysql> alter table emp change birth birthday date first;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc emp;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| birthday | date | YES | | NULL | |
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptnumber | int(4) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
注意:CHANGE/ FIRST|AFTER col_name 这些都属于 MySQL 在 SQL 上的拓展,在其他数据库上不一定适用。
1.1.5.6、修改表名
ALTER TABLE 旧表名 RENAME [TO] 新表名;
RENMAE TABLE 旧表名 TO 新表名;
把表 emp 改名为 emp_new:
mysql> RENAME TABLE emp TO emp_new;
Query OK, 0 rows affected (0.00 sec)
mysql> desc emp;
ERROR 1146 (42S02): Table 'li.emp' doesn't exist
mysql> desc emp_new;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| birthday | date | YES | | NULL | |
| ename | varchar(20) | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(10,2) | YES | | NULL | |
| deptnumber | int(4) | YES | | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
1.2、DML 语句
DML 语句是指对数据库中表的记录的操作,主要包括表记录的插入(insert)、更新(update)、删除(delete)和查询(select)。
1.2.1、插入记录
INSERT INTO 表名(列名1,列名2,...,列名n) VALUES(值1,值2,...,值n);
先把表名改回来并删除 birthday 字段:
mysql> alter table emp_new rename to emp;
Query OK, 0 rows affected (0.00 sec)
mysql> alter table emp drop birthday;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
向 emp 中插入以下记录,即 ename 为 zzxl,hiredate 为 2000-01-01,sal 为 2000,deptnumber 为 1:
mysql> insert into emp(ename,hiredate,sal,deptno) values('zzxl','2000-01-01','2000',1);
Query OK, 1 row affected (0.00 sec)
也可以不指定字段名称,但是 values 后面的顺序应该和字段的排列顺序一致:
mysql> insert into emp values('lisa','2003-02-01','3000',2);
Query OK, 1 row affected (0.00 sec)
含可空的字段、非空但是含有默认值的字段以及自增字段,可以不在 insert 后的字段列表里面出现,values 后面只写对应字段名称的值。这些没写的字段自动设置为 NULL、默认值、自增的下一个数字,这样在某些情况下可以大大缩短 SQL 语句的复杂性。
例如,只对表中的 ename 和 sal 字段显示插入:
mysql> insert into emp (ename,sal) values ('dony',1000);
Query OK, 1 row affected (0.00 sec)
来查看一下插入的内容:
mysql> select * from emp;
+-------+------------+---------+--------+
| ename | hiredate | sal | deptno |
+-------+------------+---------+--------+
| zzxl | 2000-01-01 | 2000.00 | 1 |
| lisa | 2003-02-01 | 3000.00 | 2 |
| dony | NULL | 1000.00 | NULL |
+-------+------------+---------+--------+
3 rows in set (0.00 sec)
可见,设置为可空的两个字段都显示为 NULL。
在 Mysql 中,insert 还可以一次性插入多条记录:
INSERT INTO 表名(列名1,列名2,...,列名n)
VALUES
(值1,值2,...,值n),
(值1,值2,...,值n),
...
(值1,值2,...,值n);
mysql> create table dept(deptno int(4),deptname varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept values(5,'dept5'),(6,'dept6');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 5 | dept5 |
| 6 | dept6 |
+--------+----------+
2 rows in set (0.00 sec)
mysql> insert into dept
-> values
-> (1,'tech'),
-> (2,'sale'),
-> (5,'fin');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from dept;
+--------+----------+
| deptno | deptname |
+--------+----------+
| 5 | dept5 |
| 6 | dept6 |
| 1 | tech |
| 2 | sale |
| 5 | fin |
+--------+----------+
5 rows in set (0.00 sec)
还可以插入检索出来的数据:
INSERT INTO 表名(列名1,列名2,...,列名n) SELECT语句;
1.2.2、更新数据
UPDATE [IGNORE] 表名
SET 列名1 = 值1,
列名2 = 值2,
...
列名n = 值n
[WHERE 过滤条件];
IGNORE 关键字:如果用 UPDATE 语句进行更新多行,并且在更新这些行中的一行或多行时出现一个错误,则整个 UPDATE 操作被取消。即使是发生错误,也继续进行更新,可使用 IGNORE 关键字。
为了更新表中的数据,可使用 UPDATE 语句。可采用两种方式:
- 更新表中特定行
- 更新表中所有行
基本的 UPDATE 语句由 3 部分组成,分别是:(1) 要更新的表;(2) 列名和他们的值;(3) 确定要更新行的过滤条件。
#客户 10005 现在有了电子邮件,因此需要更新数据
mysql> update customers
-> set cust_email = 'elmer@fudd.com'
-> where cust_id = 10005;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
为了删除某列的值,可以设置它为 NULL:
mysql> UPDATE customers
-> SET cust_email = NULL
-> WHERE cust_id = 10005;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
1.2.3、删除数据
DELETE FROM 表名
[WHERE 过滤条件];
为了删除表中的数据,可使用 DELETE 语句。可采用两种方式:
- 删除表中特定行
- 删除表中所有行
mysql> DELETE FROM customers
-> WHERE cust_id = 10006;
Query OK, 0 rows affected (0.00 sec)
DELETE 删除整行的值,如果想删除指定的列,使用 UPDATE 语句。但是,请注意的是,DELETE 删除的是表的内容,而不是表本身!!!想要从表中删除所有的行,不要使用 DELETE,可使用 TRUNCATE TABLE 语句。
1.2.4、查询数据
因 SELECT 语句用法太多,请见《 MySQL基础学习笔记之——SELECT查询.》。
1.3、DCL 语句
1.3.1、访问控制
MySQL 服务器的安全基础是:用户应该对他们需要的数据具有适当的访问权,既不能多也不能少。即你需要给用户提供他们所需的访问权,且仅提供他们所需的访问权。这就是所谓的访问控制,管理访问控制需要创建和管理用户账号。root 用户对整个 MySQL 具有完全的控制。
1.3.2、管理用户
MySQL 用户账号和信息存储在名为 mysql 的数据库中。mysql 数据库中有一个名为 user 的表,它包含所有用户账号。
mysql> use mysql;
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> SELECT user FROM user;
+---------------+
| user |
+---------------+
| root |
| mysql.session |
| mysql.sys |
| root |
+---------------+
4 rows in set (0.00 sec)
1.3.3、创建用户账号
为了创建一个新的用户账号,使用 CREATE USER 语句:
CREATE USER 用户名 [IDENTIFIED BY '密码'];
mysql> CREATE USER ben IDENTIFIED BY 'opfordream'; #使用了一个密码
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE USER li; #不使用密码
Query OK, 0 rows affected (0.00 sec)
为了重命名一个用户账号,使用 RENAME USER 语句:
RENAME USER 旧用户名 TO 新用户名;
mysql> RENAME USER ben TO bforta;
Query OK, 0 rows affected (0.00 sec)
1.3.4、删除用户账号
为了删除一个用户账号(以及相关权限),使用 DROP USER 语句:
mysql> DROP USER bforta;
Query OK, 0 rows affected (0.00 sec)
1.3.5、设置访问权限
在创建用户账号后,必须接着分配访问权限。新创建的用户账号没有访问权限。它们可以登陆 MySQL,但看不到数据,不能执行任何数据库的操作。
为了查看用户的权限,使用 SHOW GRANTS FOR:
mysql> SHOW GRANTS FOR li;
+--------------------------------+
| Grants for li@% |
+--------------------------------+
| GRANT USAGE ON *.* TO 'li'@'%' |
+--------------------------------+
1 row in set (0.00 sec)
输出结果显示用户 li 有一个权限 USAGE ON *.*。USAGE 表示根本没有权限。
此外,用户定义为 user@host:MySQL 的权限用用户名和主机名结合定义。如果不指定主机名,则使用默认的主机名%(授予用户访问权限而不管主机名)。
为设置权限,使用 GRANT,GRANT 要求至少给出以下信息:
- 要授予的权限;
- 被授予访问权限的数据库或表;
- 用户名。
GRANT 要授予的权限 ON 数据库或表 TO 用户名;
mysql> GRANT SELECT ON crashcource.* TO li;
Query OK, 0 rows affected (0.00 sec)
此 GRANT 允许用户在 crashcourse 数据库的所有表上使用 SELECT。SHOW GRANTS 反映这个更改:
mysql> SHOW GRANTS FOR li;
+---------------------------------------------+
| Grants for li@% |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'li'@'%' |
| GRANT SELECT ON `crashcource`.* TO 'li'@'%' |
+---------------------------------------------+
2 rows in set (0.00 sec)
GRANT 的反操作为 REVOKE,用它撤销特定的权限:
REVOKE 要授予的权限 ON 数据库或表 FROM 用户名;
mysql> REVOKE SELECT ON `crashcource`.* FROM li;
Query OK, 0 rows affected (0.00 sec)
GRANT 和 REVOKE 可在几个层次上控制访问权限:
- 整个服务器,使用 GRANT ALL 和 REVOKE ALL;
- 整个数据库,使用 ON database.*;
- 特定的表,使用 ON database.table;
- 特定的列;
- 特定的存储过程。
1.3.6、更改密码
为了更改用户密码,可使用 SET PASSWD 语句:
SET PASSWORD FOR 用户名 = Password('密码');
alter user '用户名'@'主机名' identified by '密码';
mysql> SET PASSWORD FOR li = Password('opfordream1996');
Query OK, 0 rows affected, 1 warning (0.00 sec)
2、查询元数据信息
在日常工作中,我们经常遇到类似下面的应用场景:
- 删除数据库 test1 下所有前缀为 tmp 的表;
- 将数据库 test1 下所有存储引擎为 myisam 的表改为 innodb。
一个自带的数据库 information_schema,用来记录 MySQL 中的元数据信息。元数据指的是数据的数据,比如表名、列名、列类型、索引名等表的各种属性名称。这个数据库比较特殊,它是一个虚拟数据库,物理上并不存在相关的目录和文件;库里 show tables 显示的各种 “表” 也并不是实际存在的物理表,而全部是视图。对于上面两个需求,可以简单地通过两个命令得到:
select concat('drop table test1.',table_name,';') from tables where table_schema='test1' and table_name like 'tmp%';
select concat('alter table test1.',table_name,'engine=innodb;') from tables where table_schema='test1' and engine='MyISAM';
下面列出一些比较常见的视图。
- SCHEMATA:该表提供了当前 MySQL 实例中所有数据库的信息, show databases 的结果取之于此;
- TABLES:该表提供了关于数据库中的表的信息(包括视图),详细表述了某个表属于哪个 schema、表类型、表引擎、创建时间等信息。show tables from schemaname 的结果取之于此;
- COLUMNS:该表提供了表中的列信息,详细表述了某张表的所有列以及每个列的信息。show columns from schemaname.tablename 的结果取之于此;
- STATISTICS:该表提供了关于表索引的信息。show index from schemaname.tablename 的结果取之于此。