MySQL very full and complete summary

(1) Data type

Type	Note
Tinyint/smallint/mediumint/int/bigint	1b/2b/3b/4b/8b
Float/double	Single-precision/double-precision floating-point
Decimal	Single-precision/double-precision floating-point with no loss of precision
Date	Date type
Time	Time Type
datetime/TimeStamp	Date Time Type/TimeStamp ( login time, self-populated )
Year	Year Type
Char	Fixed-length string type
varchar	Variable long String type
Tinyblob/blob/mediumblob/longblob	255b/64k/16m/4g size picture/Music II data
Tinytext/text/mediumtext/longtext	255b/64k/16m/4g Size of text data

(2) MySQL Trivia

① Login

MySQL > Mysql-u root-p Enter

Enter

MySQL > Exit Enter

② Creating/using/viewing/deleting a database

Create database name; (end with semicolon), SQL command uppercase and lowercase irrelevant

show databases;

Use database name;

③ Change/backup/RESTORE Database data

backup :c:\> mysqldump-u root-p mydb2 > D:\mydb2.sql return (without semicolon end) password carriage return

Note: The SQL command is unique to MySQL and must be running outside of the MySQL environment, i.e. running in a WINDOWXP environment (exiting the MySQL platform)

recovery:mysql:\> source d:\mydb2.sql; carriage return (semicolon-end required)

Note: The SQL command is unique to MySQL and must be running in a MySQL environment.

④mysql Support data Types Simple Introduction

(1) Date/datetime/timestamp, delimiters use "or", but some databases may not support "", priority recommendation ' as delimiter, for date type, MySQL database has a segment-function

(2) varchar (variable length)/char (fixed length)

(3) Text (data greater than 65536 characters)/blob (storing binary multimedia data, such as MP3, etc.), the two type has four subtypes, depending on the size of the storage content to choose

(4) int is signed and unsigned, int (5) indicates that int defaults to 5 bits, assuming the insertion ID value, less than 5 bits, left fill space, assuming the Insert ID value, greater than 5 bits, according to the insertion value, but must satisfy the size of the int type OK

(5) FLOAT (M,d), D represents the D bit after the decimal point, calculated by rounding, M represents the sum of all the digits except the decimal point

⑤mysql altering tables and character sets

Show variables like ' character% ';

Set CHARACTER_SET_RESULTS=GBK;

(3) MySQL Fix Chinese garbled (under XP)

2 ways to solve the problem:

A) in the current mysqlclient changes the input and output of the MySQL environment variable is GBK or GB2312, note that this method is only valid in the current form

b) Change the My.ini file under the MySQL folder to change the client's default encoding to GBK or GB2312, note that the MySQL service needs to be started again

(4) operation of adding and deleting tables

a) Insert

b) Update

c) Delete from or TRUNCATE TABLE or drop table

Delete from: Deletes all the records in the table by row, but preserves the table, which is suitable for deleting data with little data and can be deleted by condition

TRUNCATE TABLE: Copy the original table structure-〉 one-time delete the whole table, and self-actively restore the original table structure, suitable for deleting large data volumes, can not be deleted by criteria

drop TABLE: Delete the table itself

When deleting records, be sure to keep an eye on the relationship between the tables

(5) Query operation of table

(1) Select distinct/*/column name from table name

(2) Select expression/Add alias from table name to column name Null+x=null

(3) Where clause, which is now from behind, where is filter by row

(4) Logical operation and relational operation

(5) Sort: null value is minimal, use ORDER BY clause, default ascending, ORDER BY clause must be placed in last

(6) Compound function

①count () function, for statistical purposes, not to count null values

②sum () function, statistics and use, do not count non-values, assuming statistical non-value, return 0

*************************************************************************************************************** ******************

Try not to use SQL statements in loop statements such as For/foreach, which is very expensive for the resources of the database, and if the data is not large, it can be filtered through PHP with the complete (join clause) or using MySQL's in () query. Assuming that the amount of data in the table is very large, using join to correlate the query will slow down the query efficiency by first identifying all records from table A, synthesizing the ID group, and then using the IN () clause to find the corresponding record in table B.

MySQL very full and complete summary