A comprehensive and complete summary of mysql

(1) Data Type

Type	Remarks
Tinyint/smallint/mediumint/int/bigint	1B/2B/3B/4B/8B
Float/double	Single precision/Double Precision Floating Point
Decimal	No single-precision/double-precision floating point type with precision loss
Date	Date type
Time	Time Type
Datetime/TimeStamp	Date and Time type/TimeStamp (logon time, automatically filled)
Year	Year type
Char	Fixed-length string type
Varchar	Variable Length string type
Tinyblob/blob/mediumblob/longblob	255B/64 K/16 M/4G size image/music 2 for Data
Tinytext/text/mediumtext/longtext	255B/64 K/16 M/4G text data

(2) knowledge of mysql

① Login

Mysql> mysql-u root-p press ENTER

* *** Press ENTER

Mysql> exit press ENTER

② Create/use/View/delete a database

Create database Name (ended with a semicolon), SQL command case-insensitive

Show databases;

Use Database Name;

③ Modify/backup/Restore database data

Backup: c :\> mysqldump-u root-p mydb2> d: \ mydb2. SQL press enter (you can end with a semicolon) and press ENTER

Note: This SQL command is unique to MySQL and must be executed outside the MySQL environment, that is, in the WindowXp environment (exit the mysql platform)

Restore: mysql: \> source d: \ mydb2. SQL; press enter (end with a semicolon)

Note: This SQL command is unique to MySQL and must be executed in the MySQL environment.

④ MySQL Data Types

(1) Date/Datetime/TimeStamp. the delimiters use ''or" ", but some databases may not support" ". We recommend'' as the delimiters first. For Date types, mySQL database has a segment determination Function

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

(3) Text (Data larger than 65536 characters)/Blob (storing binary multimedia data, such as Mp3). Each of these two types has four sub-types, which are selected based on the size of the storage content.

(4) The INT type can be signed or unsigned. int (5) indicates that the default value of int Is 5 bits. If the value of id is inserted, it must be smaller than 5 bits and left with spaces. If the value of id is inserted, the value must be greater than 5 characters, which is determined based on the inserted value but must meet the size of the int type.

(5) FLOAT (M, D), D indicates the D digit after the decimal point. It is calculated by rounding. M indicates the total number of all digits except the decimal point.

⑤ MySQL modifies tables and character sets

Show variables like 'character % ';

Set character_set_results = gbk;

(3) MySQL solves Chinese garbled characters (under XP)

Two solutions:

A) modify the input and output MySQL environment variables to GBK or GB2312 on the current MySQL client. Note that this method is only valid in the current window.

B) modify the my. ini file in the MySQL directory and change the default encoding method of the client to GBK or GB2312. Note that you need to restart the MySQL service.

(4) add, delete, modify, and delete tables

A) insert

B) update

C) delete from or truncate table or drop table

Delete from: delete all records in the table by row, but the table is retained. It is suitable for deleting small data volumes and can be deleted based on conditions.

Truncate table: copy the original table structure-> Delete the entire table at a time-> restore the original table structure automatically. It is suitable for deleting large data volumes and cannot be deleted based on conditions.

Drop table: Delete the table itself

When deleting a record, pay attention to the association between tables.

(5) Table query operations

(1) select distinct/*/column name from Table Name

(2) select expression/alias for the column name from table name NULL + X = NULL

(3) The where clause appears after the from clause, where is filtered by row.

(4) logical operations and relational operations

(5) sorting: If the NULL value is the minimum value, the order by clause is used. The default value is ascending. The order by clause must be placed at the end.

(6) composite Functions

① Count () function. Used for statistics. NULL values are not counted.

② Sum () function. Used for statistics. Do not count non-numeric values. If non-numeric values are counted, 0 is returned.