MySQL Basics Summary (1)

For MySQL has been the second time to see, the main purpose is to summarize the MySQL system on one side, before learning MySQL is to write notes in the book, later do not want to always take the book, so take a moment to summarize the notes in the blog. In the afternoon the installation steps have been written in detail and now sums up his basic grammar. As for many of the syntax is to satisfy the SQL syntax, and SQL Server, Oracle is similar. The ">" is validated in DOS and can be run directly after replication.

1. Connect to the database:

> #在mysql中 ' # ' is a comment symbol, enter the account number and password when connecting to the database

>mysql-u root-p WWH  

2. Show all databases

>show databases;

3. Toggle 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 that uses the UTF8 character set
>create database if not EXISTS user character set UTF8;
> #如果不存在user, create, if present, do not create user database
7. Create a user database that uses the UTF8 character set with the proofing rules
>create database if not EXISTS user character set UTF8 collate utf8_general_ci;

8. View the definition information for 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 in the server and modify the character set of the user library to GBK
>alter database user character set GBK;

11. Modify, back up, restore database data (this should be a special in MySQL, different database backup recovery instructions are not the same)
Backup:
c:\> mysqldump-u root-p wwh > D:\user.sql Enter (can end without semicolons)
Password return
Note: This SQL command is unique to MySQL and must be performed outside of the MySQL environment, which is executed in the DOS interface environment in Windows, and not into the MySQL server.
Recovery:
mysql:\> source D:\mydb2.sql; return (requires semicolon end)
Note: The SQL command is unique to MySQL and must be performed in a MySQL environment.

MySQL Support data type

Attention:

(1) Date/datetime/timestamp, delimiters use "or" "(single and double quotes), 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, based on the size of the storage content selected
(4) int is signed and unsigned, int (5) indicates that int defaults to 5 bits, if the Insert ID value, less than 5 bits, left fill space, if the Insert ID value, greater than 5 bits, according to the insertion value, but must satisfy the size of the int type determines

(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

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

13. Method of Optimization:

Change space by time or change time by space.

14. Note:

In the actual development, you can consider the information frequently used, prioritize efficiency, stored in a table, the less commonly used information and comparative space of information, limited consideration of space occupied, stored in the auxiliary table. For example: the user name and password in the QQ space, also has the log and the message board information storage.