MySQL server common commands and SQL statement Summary (1/5)

First, start and exit
1, into MySQL: Start the MySQL Command line Client (MySQL's DOS interface), directly enter the installation of the password can be. The prompt at this point is:mysql>
2, Exit Mysql:quit or exit
Second, the library operation
1. Create a database
Command: CREATE DATABASE < database name >
For example: Create a database named XHKDB
mysql> CREATE DATABASE xhkdb;
2, display all the database
Command: Show databases (note: Last has an S)
mysql> show databases;
3, delete the database
Command: Drop Database < database name >
For example, to delete a database named Xhkdb
mysql> drop Database xhkdb;
4, connect the database
Command: Use < database name >
For example: If the XHKDB database exists, try to access it:
mysql> use XHKDB;
ScreenTip: Database changed
5. View the database currently in use
Mysql> Select Database ();

6, the current database contains table information:
Mysql> Show tables; (Note: Last has an S)

Third, table operation, the operation should be connected to a database
1, build the table
Command: CREATE table < 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 (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 the table
Command: DROP table < table name >
For example, to delete a table named MyClass
mysql> drop table MyClass;
4. Inserting data
Command: INSERT into < table name > [< field name 1>[,.. < field name n >]] VALUES (value 1) [, (value N)]
For example, insert two records into table MyClass, two records that say: Tom with a number 1 is 96.45, and the number 2 for Joan is 82.99, and the number 3 for Wang is 96.5.
mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);
5, the data in the query table
1), Query all lines
Command: Select < Field 1, field 2,...> from < table name > where < expression >
For example: View all data in table MyClass
Mysql> select * from MyClass;
2), query the first few lines of data
For example: View the first 2 rows of data in table MyClass
Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;

Or:

Mysql> select * from MyClass limit 0, 2;
6, delete the data in the table
Command: Delete from table name where expression
For example: Delete a record in table MyClass that is numbered 1
Mysql> Delete from MyClass where id=1;
7, modify the data in the table: Update table name SET field = new value,... WHERE condition
mysql> Update MyClass set name= ' Mary ' where id=1;
7. Add fields to the table:

home 1 2 3 4 5 last