MySQL table operation commands

1. Create a database: Create Database db_name;

Database deletion: drop database db_name; When deleting a database, you can first determine whether it exists and write it as: drop database if exits db_name

2. Create a table: Create Table table_name (Field 1 data type, Field 2 data type );

Example: Create Table mytable (ID int, username char (20 ));

Delete table: Drop table table_name; example: Drop table mytable;

8. add data: insert into table name [(Field 1, Field 2,...)] Values (value 1, value 2 ,.....);

If you insert a value to each field in the table, the field names in the front [] brackets can be written or not written.

Example: insert into mytable (ID, username) values (1, 'hangsan ');

9. Query: Query all data: Select * From table_name;

Query the data of a specified field: Select Field 1, Field 2 from table_name;

Example: Select ID, username from mytable where id = 1 order by DESC;

Multi-Table query statement ---------- refer to 17th instances
10. Update the specified data and update the data of a field (Note: it is not the name of the updated field)

Update table_name set field name = 'new value' [, Field 2 = 'new value',...] [Where id = id_num] [order by field order]

For example, update mytable set username = 'lisi' where id = 1;

The Order statement is the query order, for example, order by id desc (or ASC). There are two types of order: DESC Reverse Order (100-1, that is, from the latest number

Query data later), ASC (from 1-100)

The where and order statements can also be used to query select and delete statements.

11. Delete the information in the table:

Delete the information in the entire table: delete from table_name;

Statement for deleting the specified condition in the Table: delete from table_name where Condition Statement; Condition Statement: Id = 3;

12. Create a database user

Create user username1 identified by 'Password', username2 identified by 'Password '....

You can create multiple database users at a time.

13. user permission control: Grant

Database and table-level permission control: grant the control of a table in a database to a user

Grant all on db_name.table_name to user_name [indentified by 'Password'];

14. Modify the table structure
① Add a Field Format:

Alter table table_name add column (field Name field type); ---- This method contains parentheses

Specify the field Insertion Location:

Alter table table_name add column field Name field type after a field;

② Delete a field:

Alter table table_name drop field name;

③ Modify the field name/Type

Alter table table_name change the type of the new field in the old field name;

④ Change the table name

Alter table table_name Rename to new_table_name;

⑤ Clear all data in the table at one time

Truncate table table_name; this method also enables the number generator (ID) in the table to start from 1.

15. Add the primary key, foreign key, constraint, index .... (For how to use this function, see instance 17)
① Constraint (primary key, unique, non-null not null)

② Automatically add auto_increment

③ Use the foreign key ----- with reference table_name (col_name column name) separately during table Creation

④ Delete data associated with multiple tables ---- set foreign key to set null --- for detailed settings, see the help documentation.

16. view the current database engine

Show create table table_name;

Modify Database Engine

Alter table table_name engine = MyISAM | InnoDB;

 

17.

Show tables;
Select * From;
Alter table a change oldfiled newfiled varchar (256 );

Alter table c_user add filed varchar (256 );