Mysql Pristine Series (pristine) _mysql

About MySQL collation of the need for memory and proficiency in the content

1./* View Operation * *---------------------------------------------------------------------------------------------------- ---1. /* View Operation * *

Show Processlist--shows which threads are running display
VARIABLES--View variables

2./* Database operation * *--------------------------------------------------------------------------------------------------- ---2. /* Database Operation * *

--View the current database
, select Database ();
--Displays the current time, user name, database version
select Now (), User (), version ();
--Copy table structure
CREATE table name like table name to copy-
-copy table structure and data
create table table name [as] SELECT * from the name of the table to copy

3./* Character Set code */--------------------------------------------------------------------------------------------------- ------3. /* Character Set code * *

Character encoding-
-MySQL, Database, table, field can be set code-
-Data encoding and client coding do not need to be consistent
show VARIABLES like ' character_set_% '--View all character Set encodings
Character_set_client the encoding that the client uses to send data to the server character_set_results the encoding that the
server side uses to return results to the client
character_set_ Connection connection layer encoding
Set Variable name = variable Value
set character_set_client = GBK;
Set character_set_results = GBK;
Set character_set_connection = GBK;
SET NAMES GBK; --equivalent to completing the above three settings

4./* Data type (column type) */----------------------------------------------------------------------------------------------- ----4. /* Data type (column type) * *

5./* Table Specification * *---------------------------------------------------------------------------------------------------- Specification for--------------5./* table

--Normal Format, NF
--Each table holds an entity information
-each with an ID field as primary key
-ID key + Atomic table-
1NF, the first normal form
field can not be divided, it satisfies the first normal form.
-2NF, the second normal form
satisfies the premise of the first normal form and cannot appear partial dependence.
eliminating the compliance of the primary key avoids partial dependency. Add a single-column keyword.
-3NF, the third normal form
satisfies the second normal form, cannot appear the transmission dependence.
A field depends on the primary key, and other fields depend on the field. This is transitive dependency. The
data for an entity information is implemented in a single table.

6./* SELECT query Statement * * *---------------------------------------------------------------------------------------------- ----------6./* SELECT query statement * *

1 A HAVING clause, the conditional clause is the
same as where function and usage, and the execution time is different.
where the test data is executed at the beginning, and the original data is filtered.
having the filtered results filtered again. The having field must be a
query and the where field must be a datasheet.
where the alias of the field can not be used, having can. Because the column value may not have been determined when the where code is executed.
where no aggregate functions can be used. Aggregate functions are generally required to use
the having SQL standard to require having to refer to a column in a GROUP BY clause or to a column in an aggregate function.

7./* Backup and Restore * * *--------------------------------------------------------------------------------------------------- ----------7./* Backup and restore * *

Backup to preserve the structure of the data and the data in the table.
Use the mysqldump instruction to complete.
--Export
1) export a table
mysqldump-u user name-p password Library name table name > filename (d:/a.sql)
2) export multiple tables
mysqldump-u user name-p password Library Name table 1 table 2 Table 3 > FileName (d:/a.sql)
3) Export all tables
mysqldump-u user name-p password Library name > filename (d:/a.sql)
4) export a library 
mysqldump-u username-　　p password-B library name > filename (d:/a.sql)
can carry the backup condition
---Import
1) in the case of MySQL login:
source backup file
2 without logging
in Mysql-u User name-p password Library name < backup text

8./* Lock Table * *------------------------------------------------------------------------------------------------------ ------------8./* Lock Form * *

Table locking is used only to prevent other clients from improperly reading and writing
MyISAM support table locks, InnoDB supports row locks-
locking lock
tables Tbl_name [as alias]-
Unlocking
UNLOCK TABLES

9./* User and Rights Management */-------------------------------------------------------------------------------------- ------------------9./* User and Rights Management */

User Information table: Mysql.user--Refresh Permissions FLUSH privileges--Increase user's CREATE user name identified by [PASSWORD] Password (string)-Must have global creat for MySQL database
E user permission, or have insert permission.
-Only users can be created and cannot be granted permissions. -User name, note quotes: such as ' user_name ' @ ' 192.168.1.1 '-passwords also need quotes, and pure numeric passwords also need quotes-to specify passwords in plain text, ignore password keywords. To specify the password as a mixed value returned by the PASSWORD () function, include the keyword PASSWORD-rename the user RENAME users old_user to New_user-set password Set PASSWORD = PASSWORD (' password ')  --Set password for current user set PASSWORD for username = PASSWORD (' password ')--set a password for a specified user----Delete user drop username--Assign rights/Add user GRANT rights list on table name to user name [Identified by [PASSWORD] ' PASSWORD ']-all privileges represents all permissions-*.* represents all the tables-Library names of all libraries. The table name represents a table below a library-view permissions show grants for Username--View current user rights show grants; or show grants for current_user;
or show grants for Current_User (); --Revoke Permissions REVOKE rights list on table name from username REVOKE all privileges, grant OPTION from username--Revoke all permissions--permission levels--to use GRANT or REVOKE, you must have
Grant option permission, and you must use the permissions that you are granting or revoking.
Global level: 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.* only grant and REVOKE database permissions.
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 only grant and revoke table permissions. Column level: Column permissions apply to a single column in a given table, MYSQL.COLUMNS_PRIV when using revoke, you must specify the same column as the authorized column.

The above is a small set to introduce the MySQL pristine series (pristine), I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!