MySQL's basic commands

"Database MySQL"
Role:
Save data
Such as:
1. Your open room record
2. Bank card information
3. Identity card Information

Database includes
1. Library
2. Table
3. Fields
4, Data line

"Relational database and non-relational database"

Interview questions often test themselves Baidu said more clearly

relational database;
Mysql
Oracle
Sql server

Non-relational database
Mongdb
Redis
"SQL Classification"

Ddl
Create (creation) drop (delete) alter (modify)
Dml
Insert (INSERT) Update (Modify) Delete (delete)
DQL (Key)
Select

DCL
Grant (AUTHORIZATION) Revoke (contrary to authorization)

DTL (Business)
Bengin (START) commit (COMMIT) rollback (rollback)

Configure your environment variables if you enter MYSQL-UROOT-P to indicate that you are not an internal command
C:\wamp64\bin\mysql\mysql5.7.9\bin

"Data Connection"
First Way (recommended)
Msyql-uroot-p
Password: If no password directly enter

Exit
Quit

The second way to access the database

Mysql-uroot-pkungezuishuai not recommended for use

The Third Way
Mysql-hlocalhost-uroot-p

Clear DOS command
Cls

\h Help command

\G executes the current command as a direct semicolon

\c ending the current command

"View Database"
Show databases

"Create Library"
Create DATABASE library Name
"Delete Library"
Drop DATABASE Database name
"View your current library"
Select Database ();

Be sure to select a library before creating a table
"See what tables are under the current library"
Show tables;
"CREATE TABLE"
CREATE TABLE table name (field 1 type (length), field 2 type (length) ...)
"Delete Table"
Dorp Table Name
"View Table Structure"
DESC Table Name
"View the Build Table statement"
Show CREATE TABLE table name
"Modify the value of a table field"
ALTER TABLE name modify field (length)
"Modify table Field"
ALTER TABLE name change the original field of your table you want to modify into the field name type (length)
"Delete table field"
ALTER TABLE name drop the field you want to delete
"Insert a new field"
ALTER TABLE name add the field you want to add
"Insert field order Problem"
After who is inserted after who
Alert table name Add the type of field you want to insert (length) after which field

First inserts the front
Alert table name Add the type of field you want to insert (length) First

"Modify Table Name"
ALTER TABLE original table name rename new table name
"Modify table field order"
ALTER TABLE name modify the original field first or after which field

"Data Type"

Integral type
tinyint
Int

Floating-point types
Float (M, D)
Double (M, D)

String type (emphasis)
Text

Char fixed length
varchar indefinite length
Interview questions frequently tested

The database is generally stored timestamp timestamp is the integer type of the number int can be

Index
1. Index of PRIMARY Key name
ALTER TABLE name add primary key (which field you want to add)

2. Unique index
ALTER TABLE name add unique (which field do you want to add)

3. General Index
Alter tale table name add index (which field you are adding to);

4, full-text index integer type is not available
Alert Table name Add fulltext (which field you are adding);

"Insert Data"

The first way to insert data

Insert into table name values (value 1, value 2, value 3 ...). ）
INSERT into Bbs_user values (2, ' Cheng ', 123,1,0,1234,1231231,260, ' Henan ');
Second way of inserting data (recommended)
Insert into table name (Field 1, Field 2, Field 3 ...). Values (value 1, value 2, value 3 ...). )
mysql> INSERT INTO Bbs_user (Username,password,province,price) VALUES (' Nicholas Tse Feng ', ' 123456 ', ' Hong Kong ', 300);

The third type of insertion (multiple data insertions.) BULK INSERT data)
Insert into table name (Field 1, Field 2, Field 3 ...). Values (value 1, value 2, value 3 ....) ), (value 1, value 2, value 3 ....) ), (value 1, value 2, value 3 ....) )......
mysql> INSERT into Bbs_user (username, password, price, province) values (' Cecilia Cheung ', ' 456 ', 500, ' Macau '), (' Gillian Chung ', ' 6666 ', 100, ' Incense Hong Kong '), (' Faye Wong ', ' 3333 ', 1, ' Beijing ');

Auto_increment If you set up auto-growth, each time you add 1 to the original base

Default value: If you set the default value, you insert the data when you do not fill in the value, you will use the default value you fill in with your value

"Delete Data"
Note: You are cautious when doing the delete operation, generally do not delete data, generally do pseudo-delete (false delete)-"Modify

When deleting, be sure to add the condition, test your SQL statement to make sure no problem and then delete

Delete from table name where condition

Mysql> Delete from Bbs_user where price>1 and price <500;
Query OK, 7 rows affected (0.01 sec)

Mysql> select * from Bbs_user;
+----+-----------+----------+------+--------+-------+------+-------+----------+
| ID | Username | password | sex | Status | CTime | RIP | Price | Province |
+----+-----------+----------+------+--------+-------+------+-------+----------+
| 4 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau |
| 6 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 1 | Beijing |
| 7 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau |
| 9 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 1 | Beijing |
| 10 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau |
| 12 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 1 | Beijing |
| 13 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau |
| 15 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 1 | Beijing |
| 16 | Cecilia Cheung | 456 | NULL | 1 | NULL | NULL | 500 | Macau |
+----+-----------+----------+------+--------+-------+------+-------+----------+
"Modify Data"
Update Set table name Original field = value you want the Where condition

Note: Conditions must be added

Mysql> Update Bbs_user Set price = n WHERE id = 10;
Query OK, 1 row Affected (0.00 sec)
Rows matched:1 changed:1 warnings:0

Mysql> select * from Bbs_user;
+----+-----------+----------+------+--------+-------+------+-------+----------+
| ID | Username | password | sex | Status | CTime | RIP | Price | Province |
+----+-----------+----------+------+--------+-------+------+-------+----------+
| 4 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau |
| 6 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 1 | Beijing |
| 7 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau |
| 9 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 1 | Beijing |
| 10 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 600 | Macau |
| 12 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 1 | Beijing |
| 13 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau |
| 15 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 1 | Beijing |
| 16 | Cecilia Cheung | 456 | NULL | 1 | NULL | NULL | 500 | Macau |
+----+-----------+----------+------+--------+-------+------+-------+----------+

"Key points in the focus"--query

* The general situation does not need to be in the work or development process if you want to use any data to query a specific field

1, according to one of the fields you specifically refer to the query
Select field 1, Field 2, Field 3 from table name
Mysql> select Username,sex,status from Bbs_user;
+-----------+------+--------+
| Username | sex | Status |
+-----------+------+--------+
| Cecilia Cheung | NULL | 0 |
| Faye Wong | NULL | 0 |
| Cecilia Cheung | NULL | 0 |
| Faye Wong | NULL | 0 |
| Cecilia Cheung | NULL | 0 |
| Faye Wong | NULL | 0 |
| Cecilia Cheung | NULL | 0 |
| Faye Wong | NULL | 0 |
| Cecilia Cheung | NULL | 1 |
+-----------+------+--------+
2, remove duplicate values of the Query method
Select DISTINCT (which field you want to go to) from table name
Mysql> SELECT DISTINCT (username) from Bbs_user;
+-----------+
| Username |
+-----------+
| Cheng |
| Nicholas Tse Feng |
| Cecilia Cheung |
| Gillian Chung |
| Faye Wong |
+-----------+
5 rows in Set (0.00 sec)

3, with where conditions to use
Select field 1, Field 2, Field 3 from table name where condition

Mysql> SELECT * from Bbs_user where price >300;
+----+-----------+----------+------+--------+-------+------+-------+----------+
| ID | Username | password | sex | Status | CTime | RIP | Price | Province |
+----+-----------+----------+------+--------+-------+------+-------+----------+
| 4 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau |
| 7 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau |
| 10 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau |
| 13 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau |
| 16 | Cecilia Cheung | 456 | NULL | 1 | NULL | NULL | 500 | Macau |
+----+-----------+----------+------+--------+-------+------+-------+----------+
5 rows in Set (0.00 sec)

4, the query in what what interval is divided into within between and

SELECT * FROM table name
Mysql> Select username, age from bbs_user where age between and 30;
+-----------+------+
| Username | Age |
+-----------+------+
| Cheng | 30 |
| Cecilia Cheung | 20 |
| Gillian Chung | 23 |
| Gillian Chung | 30 |
| Faye Wong | 23 |
+-----------+------+
5, the use of query with or
mysql> Select field 1, Field 2 ... From table name where Condition 1>500 or condition 2 > 30;
+-----------+-------+
Mysql> Select Username, price from bbs_user where price>500 or age > 30;
+-----------+-------+
| Username | Price |
+-----------+-------+
| Cecilia Cheung | 550 |
| Gillian Chung | 100 |
| Faye Wong | 400 |
| Faye Wong | 444 |
| Cecilia Cheung | 500 |
| Cecilia Cheung | 119 |
+-----------+-------+
6. Not equal to
Mysql> Select COUNT (*) Username from Bbs_user where age! = 23;
+----------+
| Username |
+----------+ ||
| 14 |
+----------+
Mysql> Select COUNT (*) Username from Bbs_user where age <> 23;
+----------+
| Username |
+----------+
| 14 |
+----------+
7, in what what inside query (through in can do nested query A will say???? ）

Mysql> Select username, age from Bbs_user where age in (30,23);
+-----------+------+
| Username | Age |
+-----------+------+
| Cheng | 30 |
| Gillian Chung | 23 |
| Gillian Chung | 30 |
| Faye Wong | 23 |
+-----------+------+
4 rows in Set (0.00 sec)

8, fuzzy query like what is the use of the index???

SELECT * from table name where field like '% '
Mysql> SELECT * from Bbs_user where province like ' river% ';
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| ID | Username | password | sex | Status | CTime | RIP | Price | Province | Age |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| 1 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan | 10 |
| 2 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan North | 30 |
| 9 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 444 | Hebei | 44 |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
3 Rows in Set (0.00 sec)
Mysql> SELECT * from Bbs_user where province like '% South ';
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| ID | Username | password | sex | Status | CTime | RIP | Price | Province | Age |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| 1 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan | 10 |
| 2 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan North | 30 |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
2 rows in Set (0.00 sec)
Mysql> SELECT * from Bbs_user where province like ' _ South ';
+----+-----------+----------+------+--------+-------+---------+-------+----------+------+
| ID | Username | password | sex | Status | CTime | RIP | Price | Province | Age |
+----+-----------+----------+------+--------+-------+---------+-------+----------+------+
| 1 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan | 10 |
+----+-----------+----------+------+--------+-------+---------+-------+----------+------+
1 row in Set (0.00 sec)
Mysql> SELECT * from Bbs_user where province like ' _ South _ ';
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| ID | Username | password | sex | Status | CTime | RIP | Price | Province | Age |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| 2 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan North | 30 |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
1 row in Set (0.00 sec)
9, with and use
Mysql> SELECT * from Bbs_user where price > $ and Price <300;
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| ID | Username | password | sex | Status | CTime | RIP | Price | Province | Age |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| 1 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan | 10 |
| 2 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan North | 30 |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
2 rows in Set (0.00 sec)

or | |
&&

What is the use of the index???

10. Sort Ascending
SELECT * FROM table name order by (field) (the default is ascending if you don't add anything behind)
||
SELECT * FROM table name order by (field) ASC;
Mysql> SELECT * from Bbs_user order by age ASC;
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| ID | Username | password | sex | Status | CTime | RIP | Price | Province | Age |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| 3 | Nicholas Tse Feng | 123456 | NULL | 0 | NULL | NULL | 300 | Hong Kong | 1 |
| 1 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan | 10 |
| 13 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau | 12 |
| 14 | Gillian Chung | 6666 | NULL | 0 | NULL | NULL | 100 | Hong Kong | 18 |
| 15 | Faye Wong | 3333 | NULL | 1 | NULL | NULL | 110 | Beijing | 19 |
| 7 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | shenzhen | 20 |
| 8 | Gillian Chung | 6666 | NULL | 0 | NULL | NULL | 100 | Hong Kong | 23 |
| 12 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 120 | Beijing | 23 |
| 2 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan North | 30 |
| 11 | Gillian Chung | 6666 | NULL | 0 | NULL | NULL | 100 | Hong Kong | 30 |
| 16 | Cecilia Cheung | 456 | NULL | 1 | NULL | NULL | 119 | Macau | 33 |
| 4 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 550 | Macau | 40 |
| 9 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 444 | Hebei | 44 |
| 5 | Gillian Chung | 6666 | NULL | 0 | NULL | NULL | 100 | Hong Kong | 50 |
| 10 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau | 56 |
| 6 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 400 | Beijing | 100 |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+

11, descending
SELECT * FROM table name order by (FIELD) desc
Mysql> SELECT * from Bbs_user ORDER by (age) Desc;
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| ID | Username | password | sex | Status | CTime | RIP | Price | Province | Age |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
| 6 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 400 | Beijing | 100 |
| 10 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau | 56 |
| 5 | Gillian Chung | 6666 | NULL | 0 | NULL | NULL | 100 | Hong Kong | 50 |
| 9 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 444 | Hebei | 44 |
| 4 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 550 | Macau | 40 |
| 16 | Cecilia Cheung | 456 | NULL | 1 | NULL | NULL | 119 | Macau | 33 |
| 2 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan North | 30 |
| 11 | Gillian Chung | 6666 | NULL | 0 | NULL | NULL | 100 | Hong Kong | 30 |
| 8 | Gillian Chung | 6666 | NULL | 0 | NULL | NULL | 100 | Hong Kong | 23 |
| 12 | Faye Wong | 3333 | NULL | 0 | NULL | NULL | 120 | Beijing | 23 |
| 7 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | shenzhen | 20 |
| 15 | Faye Wong | 3333 | NULL | 1 | NULL | NULL | 110 | Beijing | 19 |
| 14 | Gillian Chung | 6666 | NULL | 0 | NULL | NULL | 100 | Hong Kong | 18 |
| 13 | Cecilia Cheung | 456 | NULL | 0 | NULL | NULL | 500 | Macau | 12 |
| 1 | Cheng | 123 | 1 | 0 | 1234 | 1231231 | 260 | Henan | 10 |
| 3 | Nicholas Tse Feng | 123456 | NULL | 0 | NULL | NULL | 300 | Hong Kong | 1 |
+----+-----------+----------+------+--------+-------+---------+-------+-----------+------+
Rows in Set (0.00 sec)

12, take out a few data

Select field 1, Field 2 ... From table name limit 0, 5 This is 5 is called offset

Pagination can only be achieved through it

First page

SELECT * from table name limit 0, 5;

Second page
SELECT * FROM table name limit 5, 5,
Third page
10, 5
Fourth page
15, 5

$limit = (n-1) * Offset, 5

SELECT * from Bbs_user limit $limit;

"The simplest query"

About the MySQL data engine

MyISAM (does not support transactions) and InnoDB (support transactions) the difference between the questions frequently tested Baidu

MySQL's basic commands