MySQL manual, mysql5.6 Chinese manual

MySQL manual, mysql5.6 Chinese manual
MySQL configuration file

The configuration file used by MySQL is my. ini, which is in the installation directory.

MySQL common configuration parameters:

1. default-character-set: default character set of the client.

2. character-set-server: Default character set on the server side.

3. port: port Number of the client and server.

4. default-storage-engine: MySQL default storage engine.

System database attached to MySQL

1. information_schema: Mainly stores information about database objects in the system, such as user table information, field information, permission information, character set information, and partition information.

2. performance_schema: Mainly stores database server performance parameters.

3. mysql: Mainly stores the user permission information of the system.

4. test: The test database automatically created by the MySQL database management system, which can be used by any user.

Structured Query Language (concept)

1. DML (data operation language): used to insert, modify, and delete table data, such as insert, update, and delete statements.

2. DDL (Data Definition Language): Creates or deletes database objects in a database, such as create, drop, alter, and other statements.

3. DQL (Data Query Language): Used to query data in a database, such as a select statement.

4. DCL (Data Control Language): used to control the access permission and access permissions of database components, such as grant and revoke.

Common MySQL Data Types

Common value types

Data Type	Bytes
Tinyint [(M)]	1 byte
Smallint [(M)]	2 bytes
Mediumint [(M)]	3 bytes
Int [(M)]	4 bytes
Float [(M, D)]	4 bytes
Double [(M, D)]	8 bytes
Decimal [(M, d)]	M + 2 bytes

 

 

 

 

 

 

 

 

 

String type

Data Type	Bytes	Description
Char [(M)]	MB bytes	Fixed-length string M is 0 ~ Integer of 255
Varchar [(M)]	Variable Length	Variable Length M is 0 ~ Integer of 65535
Tinytext	0 ~ 255	Micro text string
Text	0 ~ 65535	Text string

 

 

 

 

 

 

 

 

Date type

Data Type	Format	Value Range
Date	Yyyy-mm-dd	1000-01-01 ~ 9999-12-31
Datetime	Yy-mm-dd hh: mm: ss	1000-01-01 00:00:00 ~ 23:59:59, 9999-12-31
Time	Hh: mm: ss	-835: 59: 59 ~ 838: 59: 59
Timestamp	Yyyymmddhhmmss	The precision is 1 second between a time in January 1970 and a time in August 2038.
Year	Year in yyyy format	1901-2155

 

 

 

 

 

 

Common attribute constraints of MySQL Fields

Common attribute Constraints

Field attribute and constraint name	Keywords	Description
Non-empty Constraint	Not null	The field is not empty.
Default Constraint	Default	Set Default Value
Unique Constraint	Unique key	Set a unique field value. Only one null value is allowed.
Primary key constraint	Primary key	Set primary key
Foreign key constraint	Foreign key	Set Foreign keys
Automatic Growth	Auto_increment	Set the field to auto-increment. You can set the initial value and step size.

 

 

 

 

 

 

 

 

MySQL storage engine

MySQL supports nine storage engines, including InnoDB, MylSAM, Memory, MRG_MylSAM, Acrchive, Federated, CSV, and BLACKHOLE.

Show engines statement to view the engine types supported by the system.

The following are two common engines:

Comparison between InnoDB and MylSAM storage engines

Function	InnoDB	MylSAM
Transaction support	Supported	Not Supported
Full-text index supported	Not Supported	Supported
Foreign key constraint	Supported	Not Supported
Tablespace size	Large	Relatively small
Data row locking	Supported	Not Supported

 

 

 

 

 

 

　　

The InnoDB Storage engine is mainly advantageous in transaction processing. If frequent update and deletion operations are required and transaction integrity requirements are high, it is suitable to achieve concurrency control.

Use this engine.

The MylSAM storage engine provides fast access and is suitable for access-oriented applications.

View the current default storage engine

Show variables like 'Storage _ engine % ';

MySQL Data File

1. storage location

By default, it is in the data folder.

2. MylSAM-type table files

. Frm file: table structure definition file. It mainly Stores Table metadata, and contains table structure definition information. This file has nothing to do with the storage engine. Any storage type table will have this file.

. MYI file: index file. Stores the index information of a MylSAM-type table. Each MylSAM-type table has a. MYI file, which is stored in the same location as the. frm file.

. MYD file: data file. The file that stores the table data.

3. InnoDB table files

. Frm file: table structure definition file. It mainly Stores Table metadata, and contains table structure definition information. This file has nothing to do with the storage engine. Any storage type table will have this file.

Ibd file: data file. Save the data of all InnoDB tables. The storage location of this file can be queried or modified using the parameters in the my. ini file. Example:

Innodb_data_home_dir: "file storage path ";

Log on to the MySQL database using the command line

Mysql-u root-proot

Or

Mysql-u root-p

Enter password: root

MySQL database operation syntax

1. Create a database

Create database [if not exists] database name;

2. delete a database

Drop database [if exists] database name;

3. Switch Database

Use Database Name;

4. view all databases

Show databases;

MySQL operation table syntax

1. Create a table

Create table [if not exists] table Name (

Field 1 Data Type [field attribute | constraint] [Index] [comment],

Field 2 Data Type [field attribute | constraint] [Index] [comment],

......

Field n data type [field attribute | constraint] [Index] [comment]

) [Table type] [Table character set] [comment];

2. delete a table

Drop table [if exists] table Name;

3. view all tables in the current database

Show tables;

4. View table Definitions

Describe table name;

Or

Desc table name;

5. Set MySQL Default character set encoding in the dos window

Set names gbk;

Or

Set character_set_client = gbk;

Set character_set_results = gbk;

Set character_set_connection = gbk;

6. Modify the table name

Alter table old table name rename [to] new table name;

7. Add Fields

Alter table name add field name data type [attribute];

8. Modify Fields

Alter table Name change Original Field name new field name data type [attribute];

9. delete a field

Alter table Name drop field name;

10. Add a primary key

Alter table name add constraint primary key name primary key table name (primary key field );

If you add a table when creating a table, you only need the primary key.

11. Add a foreign key

Alter table name add constraint foreign key name foreign key (foreign key field) references join table name (associated field );

If you add a table when creating a table, you only need to associate the table name (associated field) with references)

MySQL Data Operations

1. Add data

Like SQL, you can add multiple records in one statement. Insert into table name values (),...;

2. Add the query results to the new table.

Insert into new table (field) select field from original table; (Note: the new table must be created in advance based on the type, sequence, and number of inserted fields)

Or

Create table new table name (select field from original table); (Note: New table does not need to be created in advance)

3. delete data

Delete from table name; (Note: auto-incrementing column information will not be deleted)

Or

Truncate table name. (Note: auto-incrementing column information will be deleted, and the execution speed is higher than that of delete blocks)

4. Paging Query

Select * from table name limit start row, number of rows queried; (Note: The start row starts from 0)

Or

Select * from table name limit to query the number of rows;

MySQL common functions

　　

Common Aggregate functions

Function Name	Function
AVG ([DISTINCT]Expr)	ReturnExpr. The DISTINCT option can be used to returnExprThe average value of different values. If no matching row is found, AVG () returns NULL.
COUNT (Expr)	Returns the number of non-NULL values in the row retrieved by the SELECT statement. If no matching row is found, COUNT () returns 0.
MIN ([DISTINCT]Expr), MAX ([DISTINCT]Expr)	ReturnExpr. Values of MIN () and MAX () can be a string parameter; in these cases, they return the minimum or maximum string value. DISTINCT keywords can be used for searchingExprThe minimum or maximum value of different values. However, the result is the same as that of the omitted DISTINCT. If no matching row is found, MIN () and MAX () return NULL.
SUM ([DISTINCT]Expr)	ReturnExpr. If no row exists in the returned set, SUM () returns NULL. The DISTINCT keyword can be used in MySQL 5.1 to obtainExprThe sum of different values. If no matching row is found, SUM () returns NULL.

　　　　　　

 

 

 

 

 

 

 

 

 

 

 

Common string functions

Function Name	Function
CONCAT (Str1,Str2,...)	Returns the string generated by the connection parameter. If any parameter is NULL, the return value is NULL. There may be one or more parameters. If all parameters are non-binary strings, the result is a non-binary string. If the independent variable contains any binary string, the result is a binary string. A numeric parameter is converted to an equivalent binary string format. To avoid this, you can use an explicit cast, for example: select concat (CAST (int_col as char ), char_col)
INSERT (Str,Pos,Len,Newstr)	Returns a string.Str, Its substring starts fromPosLocation and long-standing stringNewstrReplacedLenCharacter. IfPosIf the string length is exceeded, the return value is the original string. Assume thatLenIf the length is greater than the length of other stringsPosStart replacement. If any parameter is null, the return value is NULL.
LOWER (Str)	Returns a string.StrAnd all characters mapped to lowercase letters based on the latest character set ing table (default: cp1252 Latin1 ).
UPPER (Str)	Returns a string.Str,And Characters mapped to uppercase letters based on the latest character set (default: cp1252 Latin1 ).
SUBSTRING (Str,Pos), SUBSTRING (StrFROMPos) SUBSTRING (Str,Pos,Len), SUBSTRING (StrFROMPosFORLen)	Not includedLenParameter format from stringStrReturns a substring starting from positionPos. WithLenParameter format from stringStrReturns the same lengthLenSubstring with the same character, starting from positionPos. Use the FROM format as the standard SQL syntax. OrPosUse a negative value. In this case, the position of the substring startsPosCharacter rather than the starting position of the string. In the following format, you canPosUse a negative value.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Common date functions

Function Name	Function
CURDATE ()	Returns the value of the current date in 'yyyy-MM-DD 'or YYYYMMDD format, depending on the format of the function used in a string or numeric context.
CURTIME ()	Return the current time in the format of 'hh: MM: ss' or HHMMSS. The specific format is determined by the function used in the string or digit context.
NOW ()	Returns the current date and time value in the format of 'yyyy-MM-DD HH: MM: ss' or YYYYMMDDHHMMSS, depending on whether the function is used in a string or a numeric context.
WEEK (Date[,Mode])	This function returnsDateThe number of weeks. The dual-parameter form of WEEK () allows you to specify whether the WEEK starts on Sunday or Monday, and whether the returned value range is from 0 to 53 or from 1 to 53. IfModeIf the parameter is omitted, the value of the default_week_format system independent variable is used.
YEAR (Date)	ReturnDateThe year in the range of 1000 to 9999.
HOUR (Time)	ReturnTimeThe corresponding hours. The return value range of the daily value is from 0 to 23.
MINUTE (Time)	ReturnTimeThe number of minutes, ranging from 0 to 59.
DATEDIFF (Expr,Expr2)	DATEDIFF () returns the start timeExprAnd end timeExpr2The number of days.ExprAndExpr2It is a date or date-and-time expression. Only the date portion of these values is used in the calculation.
ADDDATE (Expr,Days)	Calculate date + days

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Common mathematical functions

Function Name	Function
Ceil (x)	Returns the smallest integer greater than or equal to x.
Floor (x)	Returns the largest integer less than or equal to x.
Rand ()	Returns 0 ~ Random number between 1

 

 

 

 

 

MySQL transactions, views, indexes, backup, and recovery

Transactions

Begin; (start)

Commit; (submit)

Rollback; (rollback)

Set autocommit = 0; (disable automatic submission)

Set autocommit = 1; (enable automatic submission)

View

Create

Create view name

As

Select * from table

Delete

Drop view name

Note: operations on the data in the view directly reference the data in the table. However, operations on data from multiple tables are not allowed in the view.

Index

Create

Create index name on table name (field)

Delete

Drop index table name. index name

View

Show index from Table Name

Back up database

Mysqldump-u root-proot Database Name> Save path

Or

Mysqldump-u root-p Database Name> Save path

Enter password root

Restore database

Mysql-u root-p database name <path

Or

Source filename (MySQL Server login required)

Export table data

Select * from table into outfile 'path' (Note: This operation path must be consistent with the secure-file-priv path in the my. ini configuration file)

Import table data

Load data infile path into table Name