MySQL command line commands and SQL statements, MySQL modification and deletion add field types, field data, and so on ..

I. Commonly Used MySQL command line commands

1. Start the MySQL service. Net start MySQL

Stop MySQL service. net stop MySQL

2. netstat-Na | findstr 3306: Check the listening port. findstr is used to check whether the following port exists.

3. log on to the MySQL console using the MySQL commend line tool.

Syntax format: mysql-user = root-Password = 123456 db_name

Or mysql-uroot-p123456 db_name

4. After Entering the MySQL command line tool, use status; or \ s to view the running environment information

5. Switch the database connection Syntax: Use new_dbname;

6. display all databases: Show databases;

7. display all tables in the database: show tables;

8. Show all information about a table: Show create table table_name;

9. view the specific attribute information of the table and the description of each field in the table.

Describe table_name; Abbreviation: DESC table_name;

Ii. SQL statements in MySQL
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. example of an SQL statement: -- 1 create users table CREATE TABLE users (ID int primary key auto_increment, nikename varchar (20) not null unique, password varchar (100) not null, address varchar (200); -- 2: Create an articles table. When creating a table, set the foreign key create table articles (ID int primary key auto_increment, content
Longtext not null, userid int, constraint foreign key (userid) References users (ID) on Delete set null); comment -- 2.1 create the articles table, when creating a table, do not set the foreign key create table articles (ID int primary key
Auto_increment, content longtext not null, userid INT); -- 2.2 set the foreign key alter table articles add constraint foreign key (userid) References users (ID) on Delete set NULL for the articles table ;------------------------------------------------------------------------
-- 3. insert data to the users table and insert multiple insert into users (ID, nikename, password, address) values (1, 'lyh1 ', '123', null), (10, 'ly22', '123', 'hubei Wuhan '), (null, 'lyh333', '123', 'Beijing Haidian'); -- 4. insert three pieces of data into articles (ID, content, userid) values (2, 'hahahahahahahaha', 11), (null, 'xixixixix ', 10), (13, 'aiaiaiaiaiaiaiaiaiaia ', 1), (14, 'hoahaoa oooooooooooooo', 10 );
-- 5. for multi-Table query, select articles for all messages published by the user whose ID is 10 in the users table and all information of the user. ID, articles. content, users. * from users, articles where users. id = 10 and articles. userid = users. id order by articles. id DESC; -- 6. view the database engine type show create table users; -- 7. alter table users engine = MyISAM;
--- Because the IDs in the users table are set as foreign keys, an error occurs when executing this sentence. -- 8. same Table query. If one condition is known. query all users whose ID number is greater than the user's ID number lyh1 select. ID,. nikename,. address from users a, users B where B. nikename = 'lyh1 'and. id> B. ID; ------ can also be written
Select ID, nikename, address from users where ID> (select ID from users where nikename = 'lyh1 ');

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/phoebird/archive/2008/08/19/2797961.aspx