[Essence series] Mysql essence, mysql

[Essence series] Mysql essence, mysql

What Mysql requires to be remembered and mastered

 

1./* View operation */Actions 1./* view operation */

Show processlist -- display which threads are running show variables -- View VARIABLES



2./* Database Operations */actions 2./* Database Operations */

-- 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 name of the TABLE to be copied -- Copy TABLE structure and data create table name [AS] SELECT * from table name to be copied

3./* character set encoding */encoding 3./* character set encoding */

Character encoding -- MySQL, database, table, and field can be set encoding -- data encoding and client encoding do not need to be consistent show variables like 'character _ set _ % '-- View All character set encoding items from the character_set_client Client the character_set_results encoding used by the server to send data. The server returns the result to the client using the 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 preceding three settings


4./* Data Type (column type) */bytes 4./* Data Type (column type )*/

1) Value Type

Int 4-byte bigint 8-byte

Int (M) M indicates the total number of digits-the default symbol bit exists, and the unsigned attribute is modified-display width. If a number is not enough to define the number of digits, fill in 0 in the front, zerofill Attribute Modification example: int (5) inserts a number '000000', which is set to '000000' after completion. The smaller the value, the better. -1 indicates that the bool value is true, and 0 indicates that the bool value is false. MySQL does not have a boolean type, expressed by integer 0 and 1. Tinyint (1) is commonly used to indicate boolean type.

2) string type --. char, varchar ---------- char fixed length string, fast, but a waste of space varchar Variable Length string, slow, but space saving M indicates the maximum length that can be stored, this length is the number of characters, the number of non-bytes. Different codes occupy different spaces. Char, which can contain a maximum of 255 characters and is irrelevant to the encoding. Varchar, a maximum of 65535 characters, related to encoding. A valid record cannot exceed 65535 bytes. Utf8 can contain a maximum of 21844 characters, gbk can contain a maximum of 32766 characters, and latin1 can contain a maximum of 65532 characters. varchar can be extended. You need to use the storage space to save the length of varchar, if the data size is smaller than 255 bytes, one byte is used to save the length. Otherwise, two bytes are required to save the data. The maximum valid length of a varchar is determined by the maximum row size and the character set used. The maximum valid length is 65532 bytes. Because the first byte is null when the varchar stores the string, there is no data, and two bytes are required to store the length of the string, therefore, the valid length is 64432-1-2 = 65532 bytes. For example, if a TABLE is defined as create table tb (c1 int, c2 char (30), c3 varchar (N) charset = utf8; what is the maximum value of N? A: 65535-1-2-4-30*3)/3

5. /* Table creation specification */lifecycle 5. /* Table creation specification */-- Normal Format, NF-each table stores an object information-each table has an ID field as the primary key-ID Primary Key + atomic table -- 1NF, if the first paradigm field cannot be further divided, it satisfies the first paradigm. -- 2NF: Partial dependence cannot appear when the second paradigm satisfies the first paradigm. If the primary key is eliminated, partial dependencies can be avoided. Add a single column keyword. -- 3NF: when the third paradigm satisfies the second paradigm, transmission dependencies cannot occur. A field depends on the primary key, and other fields depend on this field. This is the transfer dependency. Put the data of an object information in a table.

6./* select query statement */limit 6./* select query statement */

1) having clause. The Condition Clause has the same function and usage as where, and the execution time is different. Where performs detection data at the beginning to filter the original data. Having filters the results again. The having field must be queried, And the where field must exist in the data table. Where cannot use the field alias, having can. This is because the column value may not be determined when executing the WHERE code. Where cannot use aggregate functions. The having SQL standard requires HAVING to reference columns in the GROUP BY clause or columns in the aggregate function.

7./* Backup and Restore */1_7./* Backup and Restore */backup to save the data structure and table data. Use the mysqldump command. -- Export 1) export a table mysqldump-u username-p Password Database Name Table Name> file name (D:/. SQL) 2) Export multiple tables mysqldump-u username-p Password Database Name Table 1 Table 2 Table 3> file name (D:/. SQL) 3) export all tables mysqldump-u username-p Password Database Name> file name (D:/. SQL) 4) export a library mysqldump-u username-p password-B Database Name> file name (D:/. SQL)-w can carry backup conditions-import 1) when logging on to mysql: source backup file 2) mysql-u user name-p password database name without logon <backup file

8. /* Lock table */lifecycle 8. /* Lock table */table lock is only used to prevent other clients from reading data incorrectly and writing data to MyISAM. Table lock is supported, innoDB supports row LOCK-lock tables tbl_name [AS alias]-UNLOCK TABLES

 

9./* user and permission management */9./* user and permission management */

User information table: mysql. user -- refresh permission flush privileges -- add user create user username identified by [PASSWORD] PASSWORD (string)-you must have the global CREATE user permission for mysql database or the INSERT permission. -Only users can be created, and permissions cannot be granted. -Username. Note the quotation marks: for example, 'user _ name' @ '192. 168.1.1 '-the PASSWORD must also be enclosed in quotation marks, and a pure digital PASSWORD must also be enclosed in quotation marks. to specify a PASSWORD in plain text, ignore the keyword "PASSWORD. TO specify the PASSWORD as the mixed value returned by the PASSWORD () function, you need TO include the keyword PASSWORD -- RENAME the user rename user old_user TO new_user -- SET the password set password = PASSWORD ('Password ') -- set password for the current user = PASSWORD ('Password ') -- set PASSWORD for specified USER -- delete user drop user Username -- assign permission/Add user grant permission list ON table name TO username [identified by [password] 'Password']-all privileges all permissions -*. * Indicates the name of all tables in all databases. table name indicates a table under a database -- View permission show grants for username -- View Current user permission show grants; or SHOW GRANTS CURRENT_USER; or show grants for CURRENT_USER (); -- REVOKE the REVOKE permission list ON table name FROM username revoke all privileges, grant option from username -- REVOKE all permissions -- permission level -- to use GRANT or REVOKE, you must have the grant option permission and use it for the permissions you are granting or revoking. Global level: Global permissions apply to ALL databases ON a given server. mysql. user grant all on *. * and revoke all on *. * only GRANT and REVOKE Global permissions. Database level: the database Permission applies 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. Table 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: the column Permission applies to a single column in a given table. When mysql. columns_priv uses REVOKE, you must specify the same column as the authorized column.