MySQL database operation statement Daquan

One. 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 there is a port in the back

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 mysql–uroot–p123456 db_name

4. After entering the MySQL command line tool, use status; Or \s View the running environment information mysql-v;

5. Syntax for switching connection databases: use New_dbname;

6. Show all databases: show databases;

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

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

9. View the specific property information of the table and the description of the fields 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 the first to determine whether the existence, written as: Drop database ifexits db_name

2. Build table: syntax for creating data tables: CREATE TABLE table_name (field 1 data type, field 2 data type);

Example: CREATE TABLE mytable (ID int, username char (20));

Delete tables: 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 into each field in the table, the field name in the preceding [] parentheses is not writable

Example: INSERT INTO MyTable (id,username) VALUES (1, ' Zhangsan ');

9. Query: Query all data: SELECT * FROM table_name;

Query data for the 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------------reference to 17th instance
10. Update specified data to update data for a field (note that the name of the field is not updated)

Update table_name SET field name = ' new value ' [, Field 2 = ' new value ', ...] [Where Id=id_num] [Order BY Field]

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

The order statement is a sequence of queries, such as ORDER by ID DESC (or ASC), in the order of two:d ESC reverse (100-1, that is, from the latest number

According to the query later), ASC (from 1-100)

Where and order statements can also be used to query select and delete deletes

11. To delete information from a table:

Delete information from the entire table: delete from table_name;

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

12. Create a database user

CREATE USER username1 identified by ' password ', username2identified by ' password ' ....

      Multiple database users can be created at once

13. User's permission control: Grant

Library, table-level permission control: assigning control of a table in a library to a user

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

14. Modification of table structure
① to add a field format:

ALTER TABLE table_name Add column (field name fields type); ----This method with parentheses

Specify where the field is inserted:

ALTER TABLE table_name ADD column Name field type after a field;

② Delete a field:

ALTER TABLE table_name DROP field name;

③ modifying field names/types

ALTER TABLE table_name change old field name new field name type of new field;

④ the name of the table change

ALTER TABLE table_name Rename to New_table_name;

⑤ all data in the table at once

TRUNCATE TABLE table_name; This method also causes the number picker (ID) in the table to start at 1

15. Add primary key, foreign key, constraint, index .... (see 17 examples for using the method)
① constraints (primary key primary key, uniqueness unique, non-null NOT NULL)

② Automatic add-on auto_increment

③ foreign key foreign key-----used in conjunction with Referencetable_name (col_name column name) and used separately when building a table

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

16. View the current engine of the database

SHOW CREATE TABLE table_name;

Modifying the Database Engine

ALTER TABLE table_name Engine=myisam |  InnoDB;     

17. An example of SQL statement application:--1 Users table CREATE table users (ID int primary keyauto_increment,nikename varchar) NOT NULL Unique,password varchar (+) not null,address varchar (200)); --2 build Articles table, set foreign key CREATE table articles (ID int primary keyauto_increment,content longtext not Null,userid int, Constraintforeign Key (userid) references users (ID) on delete set null);---------------------------------------------- -------------------------        --2.1 Build Articles table, do not set foreign keys when building the table          create table articles (ID int primary KEY auto_increment,contentlongtext NOT NULL, Useridint)         --2.2 to articles table set foreign key          alter table articles Add constraint foreign key (userid) referencesusers (id) on delete setnull;-------- ------------------------------------------------------------------3. Insert data into the Users table and insert multiple INSERT into users (id,nikename,password,address) ValuES (1, ' lyh1 ', ' 1234 ', null), (Ten, ' lyh22 ', ' 4321 ', ' Hubei Wuhan '), (null, ' lyh333 ', ' 5678 ', ' Beijing Haidian '); --4. Insert three data into article Insert Intoarticles (Id,content,userid) VALUES (2, ' Hahahahahaha ', one-by-one), (null, ' XIXIXIXIXIX ', 10), (13, ' Aiaiaiaiaiaiaiaiaiaiaiaia ', 1), (+, ' hohoahaoaoooooooooo ', 10);--5. Make a multi-table query, select all messages posted by users in the user table id=10 and all information about that user selectarticles.id,articles.content,users.* from Users,articles Whereusers.id=10 and Articles.userid=users.id ORDER by Articles.id Desc;--6. View Database engine Type show create table users; --7. Modifying the database engine type alter tableusers ENGINE=MYISAM; ---because the IDs in the users table are set to foreign keys, there is an error--8 the execution of this sentence. The same table query, known as a condition of the case. The query ID number is greater than the user LYH1 ID number for all users selecta.id,a.nikename,a.address from users A,users b whereb.nikename= ' lyh1 ' and a.id>b.id;------can also be written
Select Id,nikename,address from Users where id> (select id fromusers where nikename= ' lyh1 ');

MySQL database operation statement Daquan