MySQL command-line commands and SQL statements

I. Common MySQL command-line commands

1. Start MySQL service net start MySQL

Stop MySQL service net stop MySQL

2.netstat-na|findstr 3306 View the port being monitored, findstr is used to find out if the following port exists

3. Log in to the MySQL console at the command line, using the MySQL commend Lines TOOL

Syntax Format mysql-user=root-password=123456 db_name

or shorthand format mysql-uroot-p123456 db_name

Or can not write the password, do not write the database, like this mysql-uroot-p (enter this command, will prompt you to enter the password, the password is not visible, after login, select the database as needed)

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. Show all databases: show databases;

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

8. Display all information when a table is created: show CREATE TABLE table_name;

9. View the specific property information of the table and the description of each field in the table

Describe table_name; abbreviated FORM: DESC table_name;

Two. SQL statements in MySQL

1. Database creation: create databases db_name;

Database deletion: Drop db db_name, delete can first determine whether there is, write: Drop database if exits db_name;

2. Create data table: syntax------>> CREATE TABLE table_name (field 1 data type, field 2 data type);

Example: CREATE TABLE Test (ID int,username varchar (20);

Delete data table: syntax------>> DROP TABLE table_name;

Example: drop table test;

3. Add data: syntax------>> INSERT INTO table name [(Field 1, Field 2 ...)] VALUES (value 1, value 2,.....);

If you insert a value into each field in the table, the field name in the preceding [] parentheses is writable

Example: INSERT into Test (Id,username) VALUES (1, ' Zhangsan ');

If you want to insert a field into a table, you only need to write a field in [] parentheses, and then the values in the value one by one correspond

4. Query statement: syntax------->> SELECT * FROM table_name;

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

Example: Select Id,username from test where id = 1 ORDER by DESC;

5. Update the specified data to update the data for a field (note that the name of the field is not updated)

Syntax------>> UPDATE table_name SET field name = ' new value ' [, Field 2 = ' new value ',.......] [WHERE id = id_num] [Order BY Field]

Example: Update Test set username = ' Lisi ' where id = 1;

6.Order statements are the order of the queries, such as: ORDER BY id DESC (or ASC), in two order: Desc Flashback (100--1) ASC Ascending (1--100)

The WHERE and order statements can also be used for querying select and deleting delete

7. Delete the information in the table:

Syntax------>> Delete information from the entire table: delete from table_name;

Delete the information specified in the table: DELETE from table_name where condition statement; Conditional statement such as: id = 3;

8. Create a database user

Syntax------->> create user username1 identified by ' password ', username2 identified by ' password ' ....

Multiple database users can be created at one time

9. Control of user rights:

Syntax------>> grant all on Db_name.table_name to user_name[identified by ' password '];

10. Changes to the table structure

1) Add a field format: ALTER TABLE table_name add column (field name segment type);----This method with parentheses

Specify the location where the field is inserted: ALTER TABLE table_name ADD column Name field type after a field name;

2) Delete a field format: ALTER TABLE table_name drop field name;

3) Modify field name/Type: ALTER TABLE talbe_name change old field name new field name type of new field;

4) Change the name of the table: ALTER TABLE table_name rename to New_table_name;

5) empty all data in the table at once: TRUNCATE TABLE table_name; This method also causes the number in the table to start at 1

11. Add primary key, foreign key, constraint, index ...

1) constraint (primary key primary key, uniqueness unique, non-null NOT NULL)

2) Automatic growth auto_increment

3) Foreign key foreign key----used in conjunction with reference table_name (COL_NAME) and used separately when building a table

4) Delete multiple tables with associated data----set foreign key to set NULL----specific settings reference Help document

12. View the current engine of the database

Syntax------>>show CREATE TABLE table_name;

Modifying the Database Engine

ALTER TABLE table_name ENGINE=MYISAM|INNODB;