MySql Summary (1)

MySql Summary (1)

I have read MySql for the second time. The main purpose of this time is to sum up the MySql system. I used to write my notes on MySql before, but I don't want to always take it with me in the future, so I will spend some time summarizing my notes in my blog. The installation steps have been detailed in the afternoon, and now the basic syntax is summarized. Many of the syntaxes meet the SQL syntax, which is similar to SQL Server and Oracle. All the items that match ">" are verified in dos and can be run directly after replication.

1. Connect to the database:

> # In mysql, '#' is the annotation symbol. When connecting to the database, enter the account and password.

> Mysql-u root-p wwh

2. display all databases

> Show databases;

3. Switch to display a database

> Use test;

4. display all tables in the corresponding database:
> Show tables;

5. Create a database named "user"
> Create database user;
6. Create a user database using the utf8 Character Set
> Create database if not exists user character set UTF8;
> # If no user exists, create the database. If yes, no user database is created.
7. Create a user database that uses the utf8 Character Set and has verification rules
> Create database if not exists user character set UTF8 collate utf8_general_ci;

8. view the definition information of the user database created earlier.
> Show create database user;

9. Delete the user database created earlier
> Drop database if exists user;

10. view the database on the server and modify the character set of the user database to gbk.
> Alter database user character set gbk;

11. Modify, back up, and recover database data (this should be unique in MySql. Different database backup and recovery commands are different)
Backup:
C: \> mysqldump-u root-p wwh> d: \ user. SQL enter (you can end with a semicolon)
Press ENTER
Note: This SQL command is unique to MySQL and must be executed outside the MySQL environment. That is, it is executed in the dos interface environment in the Window and is not entered into the MySql server.
Recovery:
Mysql: \> source d: \ mydb2. SQL; press enter (you need to end with a semicolon)
Note: This SQL command is unique to MySQL and must be executed in the MySQL environment.

12. MySQL supports data types

Note:

 

(1)Date/Datetime/TimeStamp, the delimiters use ''or" "(single quotes and double quotes), 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 (storage of binary multimedia data, such as Mp3), the two types have four sub-types, depending on the size of the storage content to select
(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.

(6)Tinyint/smallint/mediumint/int/bigint --> 1B/2B/3B/4B/8B

13. Optimization Methods:

Use Time for space or space for time.

14. Note:

In actual development, you can consider frequently used information, give priority to efficiency, store it in a table, and take less frequently used information and space-consuming information for a limited consideration, store the data in the secondary table. For example, the user name and password in the QQ space, and the storage of logs and message boards.