MySQL心得9

1. 添加用户 可以使用CREATE USER语法添加一个或多个用户，并设置相应的密码。 语法式： CREATE USER user [identified BY [PASSWORD] 'password'] [, user [IDENTIFIED BY [PASSWORD] 'password']] ... 其中，user的式为： 'user_name'@ 'host name' 说明：

1. 添加用户

可以使用CREATE USER语法添加一个或多个用户，并设置相应的密码。 语法格式：

CREATE USER user [identified BY [PASSWORD] 'password']

[, user [IDENTIFIED BY [PASSWORD] 'password']] ...

其中，user的格式为： 'user_name'@ 'host name'

说明：

user_name为用户名，host_name为主机名，password为该用户的密码。在大多数SQL产品中，用户名和密码只由字母和数字组成。

使用自选的IDENTIFIED BY子句，可以为账户给定一个密码。特别是要在纯文本中指定密码，需忽略PASSWORD关键词。如果不想以明文发送密码，而且知道PASSWORD()函数返回给密码的混编值，则可以指定该混编值，但要加关键字PASSWORD。

CREATE USER用于创建新的MySQL账户。CREATE USER会在系统本身的mysql数据库的user表中添加一个新记录。要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限或INSERT权限。如果账户已经存在，则出现错误。

例： 添加两个新的用户，king的密码为queen，palo的密码为530415。

CREATE USER

'king'@'localhost'IDENTIFIED BY 'queen',

'palo'@'localhost'IDENTIFIED BY '530415';

说明：

在用户名的后面声明了关键字localhost。这个关键字指定了用户创建的使用MySQL的连接所来自的主机。如果一个用户名和主机名中包含特殊符号如“_”，或通配符如“%”，则需要用单引号将其括起。“%”表示一组主机，所有人都可以访问。

如果两个用户具有相同的用户名但主机不同，MySQL将其视为不同的用户，允许为这两个用户分配不同的权限集合。

如果没有输入密码，那么MySQL允许相关的用户不使用密码登录。但是从安全的角度并不推荐这种做法。

刚刚创建的用户还没有很多权限。它们可以登录到MySQL，但是它们不能使用USE语句来让用户已经创建的任何数据库成为当前数据库，因此，它们无法访问那些数据库的表，只允许进行不需要权限的操作，例如，用一条SHOW语句查询所有存储引擎和字符集的列表。

2. 删除用户

语法格式：DROP USER user [, user_name] ...

DROP USER语句用于删除一个或多个MySQL账户，并取消其权限。要使用DROPUSER，必须拥有mysql数据库的全局CREATE USER权限或DELETE权限。

例： 删除用户TOM ：DROP USER TOM@localhost;

如果删除的用户已经创建了表、索引或其他的数据库对象，它们将继续保留，因为MySQL并没有记录是谁创建了这些对象。

3. 修改用户

可以使用rename USER语句来修改一个已经存在的SQL用户的名字。 语法格式：RENAME USER old_user TO new_user,

[, old_user TO new_user] ...

说明：

old_user为已经存在的SQL用户。new_user为新的SQL用户。

RENAME USER语句用于对原有MySQL账户进行重命名。要使用RENAME USER，必须拥有全局CREATE USER权限或mysql数据库UPDATE权限。如果旧账户不存在或者新账户已存在，则会出现错误。

例: 将用户king1和king2的名字分别修改为ken1和ken2。

RENAME USER

'king1'@'localhost' TO' ken1'@'localhost',

'king2'@'localhost' TO' ken2'@'localhost';

4. 修改密码

要修改某个用户的登录密码，可以使用SET PASSWORD语句。

语法格式：SET PASSWORD [FOR user]= PASSWORD('newpassword')

说明：

如果不加FOR user，表示修改当前用户的密码。加了FORuser则是修改当前主机上的特定用户的密码，user为用户名。user的值必须以'user_name'@'host_name'的格式给定。

例： 将用户king的密码修改为queen1。

SET PASSWORD FOR 'king'@'localhost' =PASSWORD('queen1');

5. 授予权限

新的SQL用户不允许访问属于其他SQL用户的表，也不能立即创建自己的表，它必须被授权。可以授予的权限有以下几组。

（1）列权限：和表中的一个具体列相关。例如，使用UPDATE语句更新表XS学号列的值的权限。

（2）表权限：和一个具体表中的所有数据相关。例如，使用SELECT语句查询表XS的所有数据的权限。

（3）数据库权限：和一个具体的数据库中的所有表相关。例如，在已有的XSCJ数据库中创建新表的权限。

（4）用户权限：和MySQL所有的数据库相关。例如，删除已有的数据库或者创建一个新的数据库的权限。

给某用户授予权限可以使用grant语句。使用showgrants语句可以查看当前账户拥有什么权限。

GRANT语法格式： GRANT priv_type [(column_list)] [, priv_type[(column_list)]] ...

ON [object_type] {表名 | * | *.* | 库名.*}

TO user [IDENTIFIED BY [PASSWORD]'password']

[, user [IDENTIFIED BY [PASSWORD] 'password']] ...

[WITH with_option [with_option] ...]

其中，object_type：

TABLE

| FUNCTION

| PROCEDURE

with_option ：

GRANT OPTION

| MAX_QUERIES_PER_HOUR count

| MAX_UPDATES_PER_HOUR count

| MAX_CONNECTIONS_PER_HOUR count

| MAX_USER_CONNECTIONS count

说明：priv_type为权限的名称，如SELECT、UPDATE等，给不同的对象授予权限priv_type的值也不相同。TO子句用来设定用户的密码。ON关键字后面给出的是要授予权限的数据库或表名，下面将一一介绍。

（1）授予表权限和列权限

授予表权限时，priv_type可以是以下值：

● SELECT：给予用户使用SELECT语句访问特定的表的权力。用户也可以在一个视图公式中包含表。然而，用户必须对视图公式中指定的每个表（或视图）都有SELECT权限。

● INSERT：给予用户使用INSERT语句向一个特定表中添加行权力.

● DELETE：给予用户使用DELETE语句向一个特定表中删除行权力。

● UPDATE：给予用户使用UPDATE语句修改特定表中值的权力。

● REFERENCES：给予用户创建一个外键来参照特定的表的权力。

● CREATE：给予用户使用特定的名字创建一个表的权力。

● ALTER：给予用户使用ALTERTABLE语句修改表的权力。

● INDEX：给予用户在表上定义索引的权力。

● DROP：给予用户删除表的权力。

● ALL或ALL PRIVILEGES：表示所有权限名。

在授予表权限时，ON关键字后面跟tb1_name，tb1_name为表名或视图名。

例： 授予用户king在XS表上的SELECT权限。

USE XSCJ;

GRANT SELECT ON XS TOking@use_name;

说明：

若在TO子句中给存在的用户指定密码，则新密码将原密码覆盖。如果权限授予了一个不存在的用户，MySQL会自动执行一条CREATE USER语句来创建这个用户，但必须为该用户指定密码。

例：用户liu和zhang不存在，授予它们在XS表上的SELECT和UPDATE权限。

GRANT SELECT,UPDATE ON XS

TO liu@localhost IDENTIFIED BY 'LPWD',

zhang@localhostIDENTIFIED BY 'ZPWD';

对于列权限，priv_type的值只能取SELECT、INSERT和UPDATE。权限的后面需要加上列名column_list。

例： 授予king在XS表上的学号列和姓名列的UPDATE权限。

GRANT UPDATE(姓名, 学号) ON XS TO king@localhost;

（2）授予数据库权限

表权限适用于一个特定的表。MySQL还支持针对整个数据库的权限。例如，在一个特定的数据库中创建表和视图的权限。

授予数据库权限时，priv_type可以是以下值：

● SELECT：给予用户使用SELECT语句访问特定数据库中所有表和视图的权力。

● INSERT：给予用户使用INSERT语句向特定数据库中所有表添加行的权力。

● DELETE：给予用户使用DELETE语句删除特定数据库中所有表的行的权力。

● UPDATE：给予用户使用UPDATE语句更新特定数据库中所有表的值的权力。

● REFERENCES：给予用户创建指向特定的数据库中的表外键的权力。

● CREATE：给予用户使用CREATETABLE语句在特定数据库中创建新表的权力。

● ALTER：给予用户使用ALTERTABLE语句修改特定数据库中所有表的权力。

● INDEX：给予用户在特定数据库中的所有表上定义和删除索引的权力。

● DROP：给予用户删除特定数据库中所有表和视图的权力。

● CREATE TEMPORARY TABLES：给予用户在特定数据库中创建临时表的权力。

● CREATE VIEW：给予用户在特定数据库中创建新的视图的权力。

● SHOW VIEW：给予用户查看特定数据库中已有视图的视图定义的权力。

● CREATE ROUTINE：给予用户为特定的数据库创建存储过程和存储函数等权力。

● ALTER ROUTINE：给予用户更新和删除数据库中已有的存储过程和存储函数等权力。

● EXECUTE ROUTINE：给予用户调用特定数据库的存储过程和存储函数的权力。

● LOCK TABLES：给予用户锁定特定数据库的已有表的权力。

● ALL或ALL PRIVILEGES：表示以上所有权限名。

在GRANT语法格式中，授予数据库权限时ON关键字后面跟“*”和“db_name.*”。“*”表示当前数据库中的所有表；“db_name.*”表示某个数据库中的所有表；*.*表示所有库下所有表。

例： 授予king在XSCJ数据库中的所有表的SELECT权限。

GRANT SELECT ON XSCJ.* TO king@localhost;

说明：这个权限适用于所有已有的表，以及此后添加到XSCJ数据库中的任何表。

例： 授予king在XSCJ数据库中所有的数据库权限。

USE XSCJ;

GRANT ALL ON * TO king@localhost;

和表权限类似，授予一个数据库权限也不意味着拥有另一个权限。如果用户被授予可以创建新表和视图，但是还不能访问它们。要访问它们，它还需要单独被授予SELECT权限或更多权限。

（3）授予用户权限

最有效率的权限就是用户权限，对于需要授予数据库权限的所有语句，也可以定义在用户权限上。例如，在用户级别上授予某人CREATE权限，这个用户可以创建一个新的数据库，也可以在所有的数据库（而不是特定的数据库）中创建新表。

MySQL授予用户权限时priv_type还可以是以下值。

● CREATE USER：给予用户创建和删除新用户的权力。

● SHOW DATABASES：给予用户使用SHOW DATABASES语句查看所有已有的数据库的定义的权利。

在GRANT语法格式中，授予用户权限时ON子句中使用“*.*”，表示所有数据库的所有表。

例： 授予Peter对所有数据库中的所有表的CREATE、ALTERT和DROP权限。

GRANT CREATE ,ALTER,DROP

ON *.* TO Peter@localhost IDENTIFIED BY 'ppwd';

例： 授予Peter创建新用户的权力。

GRANT CREATE USER

ON *.* TO Peter@localhost;

6. 权限的转移和限制

GRANT语句的最后可以使用WITH子句。如果指定为WITH GRANT OPTION，则表示TO子句中指定的所有用户都有把自己所拥有的权限授予其他用户的权利，而不管其他用户是否拥有该权限。

例： 授予David在XS表上的SELECT权限，并允许其将该权限授予其他用户。

GRANT SELECT ON XSCJ.XS

TO David@localhost IDENTIFIED BY '123456'

WITH GRANT OPTION;

可以把这个权限传递给其他用户，这里假设用户Jim已经创建：

GRANT SELECT ON XSCJ.XS TO Jim@localhost;

说明：使用了WITH GRANT OPTION子句后，如果David在该表上还拥有其他权限，他可以将其他权限也授予Jim而不仅限于SELECT。

WITH子句也可以对一个用户授予使用限制，其中，MAX_queries_PER_HOURcount表示每小时可以查询数据库的次数；MAX_connections_PER_HOURcount表示每小时可以连接数据库的次数；MAX_UPDATES_PER_HOURcount表示每小时可以修改数据库的次数。count表示同时连接MySQL的最大用户数。count是一个数值，对于前三个指定，count如果为0则表示不起限制作用。

例： 授予Jim每小时只能处理一条SELECT语句的权限。

GRANT SELECT ON XS TO Jim@localhost

WITH MAX_QUERIES_PER_HOUR1;

7. 收回权限

要从一个用户回收权限，但不从USER表中删除该用户，可以使用REVOKE语句，这条语句和GRANT语句格式相似，但具有相反的效果。要使用REVOKE，用户必须拥有mysql数据库的全局CREATE USER权限或UPDATE权限。

语法格式： REVOKE priv_type[(column_list)] [, priv_type [(column_list)]] ...

ON {表名 | * |*.* | 库名.*}

FROM user [, user] ...

或者：

REVOKE ALL privileges, GRANT OPTION FROM user [, user] ...

说明：第一种格式用来回收某些特定的权限，第二种格式回收所有该用户的权限。

例： 回收用户David在XS表上的SELECT权限。

REVOKE SELECT ON XS FROM David@localhost;

由于David用户对XS表的SELECT权限被回收了，那么包括直接或间接地依赖于它的所有权限也回收了，在这个例子中，Jim也失去了对XS表的SELECT权限。但以上语句执行之后WITH GRANT OPTION还保留，当再次授予David对于同一个表的表权限时，它会立刻把这个权限传递给Jim。

以下非重点，属于了解内容：（表维护语句）

1．analyze table语句

在一个定义了索引的列上，该列上不同值的数目被称为该索引列的可压缩性，可以使用SHOWINDEX FROM tb_name语句来显示它。

一个索引列的可压缩性不是自动更新的。就是说，用户在某列创建了一个索引，而该列的可压缩性是不会立即计算出来的。这时需要使用ANALYZE TABLE语句来更新它。

语法格式：ANALYZE [LOCAL |NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

在MySQL上执行的所有更新都将写入到一个二进制日志文件中。这里如果直接使用ANALYZE TABLE语句，结果数据也会写入日志文件中。如果指定了NO_eriteTO_binlog选项，则关闭这个功能（LOCAL是NO_ERITE_TO_BINLOG的同义词），这样ANALYZETABLE语句也将会更快完成。

例： 更新表XS的索引的可压缩性，并随后显示。

ANALYZE TABLE XS;

SHOW INDEX FROM XS;

2. check table 语句

这条语句用来检查一个或多个表是否有错误，只对MyISAM和InnoDB表起作用。 语法格式：

CHECK TABLE tbl_name [, tbl_name] ...[option] ...

其中，option为：

QUICK | FAST | MEDIUM | EXTENDED |CHANGED

说明：

使用该语句有多个选项。

● quick：不扫描行，不检查错误的链接，这是最快的方法。

● fast：检查表是否已经正确关闭。

● changed：检查上次检查后被更改的表，以及没有被正确关闭的表。

● medium：扫描行，以验证被删除的链接是有效的。也可以计算各行的关键字校验和，并使用计算出的校验和验证这一点。

● extended：对每行的所有关键字进行全面的关键字查找。这可以确保表是100％一致的，但是花的时间较长。

例： 检查XS表是否正确。

CHECK TABLE XS;

3. checksum table 语句

对于数据库中的每一个表，都可以使用CHECKSUM TABLE语句获得一个校验和。 语法格式：

CHECKSUM TABLE tbl_name [, tbl_name]... [ QUICK | EXTENDED ]

说明：如果表是MyISAM表，如果指定了QUICK，则报告表校验和，否则报告NULL。指定EXTENDED则表示无论表是否是MyISAM表，都只计算检验和。

4. optimize table 语句

如果用户不断地使用DELETE、INSERT和UPDATE语句更新一个表，那么表的内部结构就会出现很多碎片和未利用的空间。这时可以使用OPTIMIZE TABLE语句来重新利用未使用的空间，并整理数据文件的碎片。OPTIMIZE TABLE语句只对MyISAM、BDB和InnoDB表起作用。

语法格式： OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name[, tbl_name] ...

例： 优化XS表。

5. repair table 语句

如果一个表或索引已经损坏，可以使用REPAIR TABLE语句尝试修复它。REPAIR TABLE只对MyISAM和ARCHIVE表起作用。

REPAIR [LOCAL | NO_WRITE_TO_BINLOG] TABLE

tbl_name [, tbl_name]... [QUICK] [EXTENDED] [USE_FRM]

说明：

REPAIR TABLE语句支持以下选项：

● QUICK：如果指定了该选项，则REPAIR TABLE会尝试只修复索引树。

● EXTENDED：使用该选项，则MySQL会一行一行地创建索引行，代替使用分类一次创建一个索引。

● USE_FRM：如果MYI索引文件缺失或标题被破坏，则必须使用此选项。

另外，还有两个表维护语句：backup TABLE和restore TABLE语句。

使用BACKUP TABLE语句可以对一个或多个MyISAM表备份。

语法格式为：

BACKUP TABLE tbl_name [, tbl_name] ... TO'/path/to/backup/directory'

使用RESTORE TABLE语句可以获取BACKUPTABLE创建的一个或多个表的备份，将数据读取到数据库中。

语法格式为：

RESTORE TABLE tbl_name [, tbl_name] ... FROM'/path/to/backup/directory'

但是这两条语句不是很理想，已经不推荐使用了，这里只是大概了解一下。OPTIMIZE TABLEKC;