Basic mysql5.0 Common Operations

Basic Common 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 User Name -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_rj_wcnc > D: wcnc_db. SQL
-D No data -Add-drop-table Add a drop before each create statement Table
4. Import the database
Common source Command
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. Enter MYSQL: Start MySQLCommandLineClient (MySQL dos Interface), enter the password for installation. The prompt is: mysql>
2. Exit MYSQL: Quit or exit
Ii. Database Operations
1. Create a database
Command: CreateDatabase<Database Name>
For example, create a database named xhkdb.
Mysql>CreateDatabaseXhkdb;
2. display all databases
Command: ShowDatabases(Note: There is a last S)
Mysql>ShowDatabases;
3. delete a database
Command: DropDatabase<Database Name>
For example, delete a file namedXhkdb Database
Mysql>DropDatabaseXhkdb;
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: DatabaseChanged
5. Database Selected (connected)
Mysql>SelectDatabase ();

6. Table information contained in the current database:
Mysql>ShowTables;(Note: There is a last S)

Iii. Table operations. A database should be connected before operations
1. Create a table
Command: CreateTable<Table Name>(<Field name 1><Type 1>[,... <Field name n><Type N>]);

Mysql> Create Table Myclass (
> ID INT (4) Not Null Primary Key Auto_increment,
> Name Char (20) Not Null,
> Sex INT (4) Not Null Default '0 ',
> Degree Double (16,2 ));
2. Get the table structure
Command: Desc Table name or show Columns From Table Name
Mysql> describe Myclass
Mysql> Desc Myclass;
Mysql> Show Columns From Myclass;
3. delete a table
Command: Drop Table <Table Name>
For example, delete a table named Myclass Table
Mysql> Drop Table Myclass;
4. insert data
Command: insert Into <Table Name> [( <Field name 1> [,... <field name n > ])] Values ( Value 1 )[, ( Value n )]
For example Insert two records in myclass, The two records indicate that the score of Tom numbered 1 is 96.45, 2 Named Joan The score is 82.99, and the number is 3. Name: Wang The score is 96.5.
Mysql> Insert Into Myclass Values (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 >
Example: view a table Myclass All data in
Mysql> Select * From Myclass;
2) query the first few rows of data
Example: view a table Myclass First 2 rows of data
Mysql> Select * From Myclass Order By ID Limit 0, 2;
6. Delete table data
Command: Delete From Table Name Where Expression
For example, deleting a table In myclass, the number is 1. Records
Mysql> Delete From Myclass Where Id = 1;
7. Modify Table data: Update Table Name Set Field = new value ,... Where Condition
Mysql> Update Myclass Set Name = 'Mary' Where Id = 1;
7. Add fields to the table:

Command: AlterTableTable NameAdd FieldTypeOthers;
For example, a passtest field is added to the myclass table. The type is int (4) and the default value is 0.
Mysql>AlterTableMyclassAddPasstestINT (4)Default'0'
8. Change the table name:
Command: RenameTableOriginal table nameToNew table name;
For example, the myclass name in the table is changed to youclass.
Mysql>RenameTableMyclassToYouclass;

Update field content
UpdateTable NameSetField name=New Content
UpdateTable NameSetField name=Replace (field name, 'old content', 'new content ');

ArticleAdd four spaces to the front
UpdateArticleSetContent = Concat ('', content );

Field Type
1.int[ (m)] type: normal Integer type
2. double [(m, d)] [zerofill] type: normal size (Double Precision) floating point numeric type
3. date date type: the supported range is 1000-01-01 to 9999-12-31. MySQL displays the date value in YYYY-MM-DD format, but allows you to assign the value to the date column with a string or number
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. The maximum length is 65535 (2 ^ 16-1) characters.