Summary of common MySQL commands and basic operations

Common MySQL operations: The following are all tests passed under mysql5.0. First, note that you must add them at the end of each command. (semicolon)
1. Export the entire database
Mysqldump-u username-p -- default-character-set = Latin1 Database Name> exported file name (the default database encoding is Latin1)
Mysqldump-u wcnc-P smgp_rj_wcnc> wcnc. SQL
2. Export a table
Mysqldump-u user name-P database name Table Name> exported file name
Mysqldump-u wcnc-P smgp_rj_wcnc users> wcnc_users. SQL
3. Export a database structure
Mysqldump-u wcnc-p-D-add-drop-Table smgp_apps_wcnc> D: wcnc_db. SQL
-D no data-add-drop-table add a drop table before each create statement
4. Import the database
Common source commands
Go to the MySQL Database Console,
For example, MySQL-u root-P
Mysql> Use Database
Then run the source command. The following parameter is the script file (for example,. SQL used here)
Mysql> source D: wcnc_db. SQL 1. Start and exit
1. Go to MySQL: Start MySQL command line client (MySQL dos Interface) and enter the password for installation. The prompt is: mysql>
2. Exit MYSQL: QuitOr Exit
Ii. Database Operations
1. Create a database
Command: Create Database<Database Name>
For example, create a database named xhkdb.
Mysql> Create Database xhkdb;
2. display all databases
Command: Show Databases(Note: There is a last S)
Mysql> Show Databases;
3. delete a database
Command: DROP DATABASE<Database Name>
For example, delete a database named xhkdb.
Mysql> DROP DATABASEXhkdb;
4. Connect to the database
Command: Use<Database Name>
For example, if the xhkdb database exists, try to access it:
Mysql> UseXhkdb;
On-screen prompt: Database changed
5. Database Selected (connected)
Mysql> Select database ();
6. Table information contained in the current database:
Mysql> Show tables; (Note: There is a last S)

Iii. Table operations. A database should be connected before operations
1. Create a table
Command:Create Table<Table Name> (<field name 1> <type 1> [,... <field name n> <type N>]); mysql>Create TableMyclass (
> ID int (4) not nullPrimary Key auto_increment,
> Name char (20) not null,
> Sex int (4) not nullDefault'0 ',
> Degree double (16, 2 ));
2. Get the table structure
Command:DescTable name, orShow columns fromTable Name

Mysql>DescMyclass;
Mysql>Show columns from myclass;
3. delete a table
Command:Drop table<Table Name>
For example, delete a table named myclass.
Mysql>Drop tableMyclass;
4. insert data
Command:Insert<Table Name> [(<field name 1> [,... <field name n>])]Values(Value 1) [, (value n)]
For example, insert two records into the myclass table. The two records indicate that the result of Tom numbered 1 is 96.45, and the result of Joan numbered 2 is 82.99, wang, numbered 3, scored 96.5.
Mysql>InsertMyclassValues(1, 'Tom ', 96.45), (2, 'job', 82.99), (2, 'wang', 96.59 );
5. query the data in the table
1) query all rows
Command:Select<Field 1, Field 2,...>From<Table Name>Where<Expression>
For example, you can view all data in the myclass table.
Mysql> select * From myclass;
2) query the first few rows of data
For example, view the first two rows of data in the myclass table.
Mysql>Select * fromMyclassOrderIDLimit0, 2;
6. Delete table data
Command:Delete fromTable NameWhereExpression
For example, delete the record numbered 1 in myclass.
Mysql>Delete fromMyclassWhereId = 1;
7. Modify Table data:UpdateTable NameSetField = new value ,...WhereCondition
Mysql>UpdateMyclassSetName = 'Mary'WhereId = 1;
7. Add fields to the table:
Command:ALTER TABLETable NameAddOther field types;
For example, a passtest field is added to the myclass table. The type is int (4) and the default value is 0.
Mysql>ALTER TABLEMyclassAddPasstestInt(4)Default'0'
8. Change the table name:
Command:RENAME TABLEOriginal table nameToNew table name;
For example, the myclass name in the table is changed to youclass.
Mysql>RENAME TABLEMyclassToYouclass;Update field content
Update table name set field name = new content
Update table name set field name = Replace (field name, 'old content', 'new content'); add four spaces before the article
Update article set content = Concat ('', content );

Field Type
1. Int [(m)] type: normal Integer type
2. Double [(m, D)] [zerofill] type: normal size (Double Precision) floating point number type
3. date type: the supported range is 1000-01-01 to 9999-12-31. MySQL displays date values in YYYY-MM-DD format, but allows you to assign values to the date column using strings or numbers
4. Char (m) type: fixed-length string type. When stored, it always fills the Right to the specified length with spaces
5. Blob text type, with a maximum length of 65535 (2 ^ 16-1) characters.
6. varchar: variable-length string type