MySQL DATABASE statement

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 running environment information

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; The deletion can be judged by the existence, written as: drop database if exits 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;

3. 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 ');

4. 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
5. 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: desc reverse (100-1, that is, from the newest number

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

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

6. 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;

7. Create a database user

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

Multiple database users can be created at one time

8. Permissions control for users: 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 '];

9. Changes to the 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

10. 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 reference table_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

11. View database Current engine

SHOW CREATE TABLE table_name;

Modifying the Database Engine

ALTER TABLE table_name Engine=myisam | InnoDB;

12. Example of an SQL statement:--1 Users table CREATE table users (ID int primary KEY auto_increment,nikename varchar) NOT NULL Unique,password varchar (+) not null,address varchar (200)); --2 build Articles table, set foreign key create table articles when building tables (ID int primary key auto_increment,content Longtext not Null,userid int,const Raint foreign KEY (userid) references users (ID) on delete set null); -----------------------------------------------------------------------          --2.1 Build Articles table, do not set foreign key when table          create table Articles (ID int primary key auto_increment,content longtext not null,userid int);       & nbsp;  --2.2 setting foreign keys          alter table articles for articles tables Add constraint foreign KEY (userid) references users (ID) on delete set null;-------------------------------------------- ------------------------------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 the article insert into articles (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 select articles.id,articles.content,users.* from users,articles where Users.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 TABLE users Engine=myisam; ---because the IDs in the users table are set to foreign keys, executing this sentence will cause an error--8. In the case of a table query, a condition is known. Query ID number is greater than user LYH1 ID number for all users select a.id,a.nikename,a.address from users a,users b where b.nikename= ' lyh1 ' and a.id>b.id;------can also be written

Select Id,nikename,address from the Users where id> (select ID from users where nikename= ' lyh1 ');

MySQL DATABASE statement