SQL query analyzer operations (modify, add, delete) tables and fields

I. 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: The last S is available)
Mysql> show databases;
3. delete a database
Command: drop database <Database Name>
For example, delete a database named xhkdb.
Mysql> drop database xhkdb;
4. Connect to the database
Command: Use <Database Name>
For example, if the xhkdb database exists, try to access it:
Mysql> Use xhkdb;
On-screen prompt: Database changed
5. view the currently used database
Mysql> select database ();

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

 

2. 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 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.
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 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> 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>
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 * From myclass order by ID limit 0, 2;

Or:

Mysql> select * From myclass limit 0, 2;
6. Delete table data
Command: delete from table name where expression
For example, delete the record numbered 1 in myclass.
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;
8. Add fields to the table:

Command: alter table table name, add, other 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 table myclass add passtest int (4) default '0'

9. Modify the field names and types of a table and specify the fields as null or non-empty.

1> alter table name change field Name field type [whether to allow non-null];
2> alter table table name Modify Field Name field type [whether to allow non-null];

10. Change the table name:
Command: rename table original table name to new table name;
For example, the myclass name in the table is changed to youclass.
Mysql> rename table myclass to youclass;

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

 

Source: http://www.cnblogs.com/hateislove214/archive/2010/11/05/1869889.html