1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
| ## ------------------| Create Database
CREATE DATABASE <DB_NAME>;
## ------------------| Create Table
CREATE TABLE <TABLE_NAME> (
<VARIABLE> <DATA_TYPE>,
<VARIABLE> <DATA_TYPE>,
);
## ------------------| Create user and password
CREATE USER '<USER>'@'<IP>' IDENTIFIED BY '<PASSWORD>';
## ------------------| Show Database / Tables
SHOW DATABASES;
SHOW TABLES;
## ------------------| Select Database / Table
USE <DB_NAME>;
DESCRIBE <TABLE_NAME>;
## ------------------| 删除表
DROP TABLE <TABLE_NAME>;
## ------------------| 更改查询
### Add new column
ALTER TABLE <TABLE_NAME> ADD <VARIABLE> <DATA_TYPE>;
### Rename a column
ALTER TABLE <TABLE_NAME> RENAME COLUMN <NEW_VARIABLE> TO <OLD_VARIABLE>;
### Change a column
ALTER TABLE <TABLE_NAME> MODIFY <OLD_VARIABLE> DATE;
### Drop, Delete a column
ALTER TABLE <TABLE_NAME> DROP <VARIABLE>;
## ------------------| Update queries
UPDATE <TABLE_NAME> SET column1=newvalue1, column2=newvalue2, ... WHERE <condition>;
## ------------------| 排序查询
SELECT * FROM <TABLE_NAME> ORDER BY <VARIABLE> DESC, <VARIABLE> ASC;
## ------------------| Limit query
SELECT * FROM <TABLE_NAME> LIMIT <VALUE>,<VALUE>;
## ------------------| Filter or search for specific data,
SELECT * FROM <TABLE_NAME> WHERE <condition>;
## ------------------| Matching a certain pattern
SELECT * FROM <TABLE_NAME> WHERE <VARIABLE> LIKE '<REGEX>'; ## __ or %%
## ------------------| Insert data to table
INSERT INTO <TABLE_NAME> VALUES (<COL-01_VALUE>,<COL-02_VALUE>, ...);
INSERT INTO <TABLE_NAME>(<COL-01>, <COL-02>, ...) VALUES (<COL-01_VALUE>,<COL-02_VALUE>, ...);
## ------------------| View the table
SELECT * FROM <TABLE_NAME>;
SELECT <COL-01>, <COL-02> FROM <TABLE_NAME>;
## ------------------| Table Properties
### Uniquely identify propertie
PRIMARY KEY (<VARIABLE>)
### Automatically Increments
<VARIABLE> INT NOT NULL AUTO_INCREMENT,
### Always unique.
<VARIABLE> <DATA_TYPE> UNIQUE NOT NULL,
### Set the default value
<VARIABLE> <DATA_TYPE> DEFAULT NOW(),
## ------------------| Grannt Privileges
GRANT ALL ON <DB_NAME>.* TO '<USER>'@'<IP>';
FLUSH PRIVILEGES;
|