1000 Lines MySQL study notes (12)

/* User and Rights Management */-----------

User Information table: MySQL. User

--Refresh Permissions
FLUSH Privileges

--Increase user
CREATE user username identified by [PASSWORD] Password (string)
-You must have the global Create user permission for the MySQL database or have the INSERT permission.
-only users can be created and permissions cannot be granted.
-user name, note quotation marks: ' user_name' @' 192.168.1.1 '-the password also needs quotation marks, the pure numeric password also must enclose the quotation mark
- To specify a password in plain text, ignore the password keyword. To specify the password as a mixed value returned by the password () function, you need to include the keyword password

-- rename user
RENAME USER old_user to New_user

--Set password
Set PASSWORD = PASSWORD (' password ') --Sets the password for the current user
Set PASSWORD for username = PASSWORD (' password ') --Set the password for the specified user

--Delete user
DROP user username

--Assign permissions/Add Users
GRANT permission list on table name to user name [identified by [PASSWORD] ' PASSWORD ']
-All privileges means all permissions
-* * represents all tables for all libraries
-Library name. Table name represents a table below a library

--View Permissions
Show GRANTS for user name --View Current user permissions
Show GRANTS; or show GRANTS for current_user; or show GRANTS for current_user();

--Revoke permissions
REV OKE permissions list on table name from user name

REVOKE All privileges, GRANT OPTION from user name --Revoke all permissions

--Permission level

--To use grant or revoke, you must have grant option permission, and you must use the permissions that you are granting or revoking.

Global hierarchy: Global permissions apply to all databases in a given server, MySQL. User

GRANT All on * * and REVOKE all on * * ONLY GRANT and REVOKE global permissions.

Database hierarchy: Database permissions apply to all targets in a given database, mysql.db, mysql.host
GRANT All on db_name. * and revoke all on db_name. * Grant and REVOKE database permissions only.

Surface level: Table permissions apply to all columns in a given table, Mysql.talbes_priv
GRANT All on db_name. Tbl_name and revoke all on db_name. Tbl_name Grant and REVOKE table permissions only.

Column hierarchy: Column permissions apply to a single column in a given table, Mysql.columns_priv

When using revoke, you must specify the same columns as the authorized column.

--List of permissions
all [privileges] --Set all simple permissions except grant option

Alter --allows the use of alter TABLE
alter ROUTINE --Change or cancel a stored subroutine
Create --Allows the use of Create TABLE
Create ROUTINE --Creating a stored subroutine
Create temporary TABLES --Allow use of Create temporary TABLE

Create user -allows use of the create user, DROP user, RENAME user and revoke all privileges.
CREATE VIEW -Allows the CREATE view to be used
Delete --Allows the use of delete
Drop -Allows the drop TABLE to be used
EXECUTE --Allows the user to run stored sub-programs

FILE --Allows the use of select ... into outfile and load DATA INFILE
Index --allows use of CREATE INDEX and drop index
Insert --Allows the use of insert
Lock TABLES --Allows you to use lock TABLES for tables that you have SELECT permissions on
PROCESS -Allows the use of show full processlist

REFERENCES --not implemented
RELOAD --Allow flush to be used
REPLICATION Client --allows users to ask for the address of a subordinate server or primary server
REPLICATION SLAVE --For replicated slave servers (read binary log events from the primary server)
Select --Allows the use of select

Show DATABASES --Show all databases
Show View - allows the show CREATE view to be used
SHUTDOWN --Allows the use of mysqladmin SHUTDOWN
SUPER -Allows the use of change master, KILL, PURGE master Logs and set global statements, mysqladmin Debug commands, allowing you to connect (once) even if the max_ has been reached Connections
UPDATE - -Allow use of updateusage--"No permissions" synonyms
Grant OPTION - Allow permissions to be granted

/ * Table maintenance * /

--Analyze and store keyword distributions for tables
ANALYZE [LOCAL | No_write_to_binlog] table name ...

--check if one or more tables have errors
CHECK TABLE tbl_name [, Tbl_name] ... [option] ...
Option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

--Defragment the data files
OPTIMIZE [LOCAL | No_write_to_binlog] TABLE tbl_name [, Tbl_name] ...

/ * Miscellaneous */------------------
1. Use the back quotation mark (') to wrap the identifier (library name, table name, field name, Index, alias) to avoid duplicate keywords! Chinese can also be used as an identifier!

2. Each library directory has an option file db.opt that holds the current database.

3. Note: Single line comment # comment content Multiline Comment */comment content * /Single Comment --Comment content (standard SQL comment style, require double dash after a space (blank, TAB, line wrapping, etc.))

4. Mode wildcard: _ Any single character
% any number of characters, even including 0 character single quotes need to be escaped \ '

5. The statement terminator in the cmd command line can be ";", "\g", "\g", only affecting the display results. Other places are ended with semicolons. Delimiter can modify the statement terminator of the current conversation.
6. SQL is not case sensitive
7. Clear existing statements: \c

dramatization
    (Shocker Source: http://www.cnblogs.com/shockerli/p/1000-plus-line-mysql-notes.html)

1000 Lines MySQL study notes (12)