MySQL Cheatsheet
- Classification
- MySQL data types
- MySQL DDL & DML
- MySQL DQL
- MySQL Subquery
- MySQL Table Association
- MySQL in terminal
- Reference
Classification
Data Definition Language (DDL)
1
CREATE
1
DROP
1
ALTER
Data Manipulation Language (DML)
1
INSERT
1
DELETE
1
UPDATE
Data Query Language (DQL)
1
SELECT
1
WHERE
Data Control Language (DCL)
1
GRANT
1
REVOKE
Transaction Control Language (TCL)
1
COMMIT
## MySQL data types1
ROLLBACK
Numerical type
Integer | INTEGER/INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT |
---|---|
Fixed-point | DECIMAL, NUMERIC |
Floating-point | FLOAT, DOUBLE |
Date/time tyoe
DATE, DATETIME, TIMESTAMP, TIME, YEAR
String type
CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET
MySQL DDL & DML
Create table
1
2
3
4
5
6
7CREATE table player (
id INT,
name VARCHAR(100),
level INT,
exp INT,
gold DECIMAL(10, 2)
);Insert (single) Data
1
INSERT INTO player (id, name, level, exp, gold) VALUES (1, '张三', 1, 1, 1);
read data
1
SELECT * from player;
Insert (multiple) data
(blank means NULL, because we did not specify the default data)1
INSERT INTO player (id, name) VALUES (2, '李四'), (3, '王五')
Modify the structure of table
1
2
3
4-- 将level的默认值设置为1
ALTER TABLE player MODIFY LEVEL INT DEFAULT 1;
-- 将name的类型改为VARCHAR(200)
ALTER TABLE player MODIFY COLUMN name VARCHAR(200);Update the data in the table
1
2
3
4
5
6-- 只修改李四的数据
UPDATE player set level = 1 where name = '李四';
-- 全部修改(比较危险)
UPDATE player set level = 1;
-- 修改多列值
UPDATE player set exp=0, gold=0;create index
1
2
3
4
5-- unique: 唯一索引
-- full text: 全文索引
-- spacial: 空间索引
CREATE [UNIQUE|FULL TEXT|SPATIAL] INDEX index_name
ON table_name (index_col_name, ...)delete index
1
drop index index_name on table_name
alter table by inserting index
1
alter table table_name add index name_index (col_name)
MySQL DQL
SELECT
1 | SELECT name, level |
WHERE
1 | SELECT * FROM player where level = 1; |
IN
1 | SELECT * FROM player WHERE level IN (1, 3, 5); |
BETWEEN...AND...
1 | SELECT * FROM player WHERE level BETWEEN 1 AND 10; |
NOT
1 | SELECT * FROM player WHERE level NOT BETWEEN 1 AND 10; |
LIKE
char | match |
---|---|
% |
any number of any character |
_ |
any one character |
1 | SELECT * FROM player WHERE name LIKE '王%'; |
Regular Expression
char | match |
---|---|
. |
any character |
^ |
begin |
$ |
end |
[abc] |
any character inside bracket |
[a-z] |
any character in range(a, z) |
A | B | A or B |
1 | SELECT * FROM player WHERE name REGEXP '[0-9]'; |
NULL (这个比较特殊)
1 | SELECT * FROM player WHERE email is NULL; |
NULL != ' ' (empty string)
1 | SELECT * FROM player WHERE email = '' or email is NULL; |
Sort
1 | -- sort by level in descending order |
EXISTS
to know whether a subquery exists. 0 means no, and 1 means yes.
1
SELECT EXISTS(SELECT * FROM player WHERE level > 10)
Aggregate Functions
AVG/COUNT/SUM/MAX/MIN
1 | SELECT AVG(level) FROM player |
GROUPING
1 | SELECT sex, count(*) FROM player GROUP BY sex; |
HAVING
1 | SELECT level, count(level) from player GROUP BY level HAVING COUNT(level) > 4 |
LIMIT
1 | SELECT SUBSTR(name, 1, 1), COUNT(SUBSTR(name, 1, 1)) from player |
DISTINCT (remove repeated element)
1 | SELECT DISTINCT sex from player |
UNION (合并查询结果集)
1 | SELECT * FROM player where level BETWEEN 1 AND 3 |
INTERSECT (交集)
1 | SELECT * FROM player WHERE level BETWEEN 1 AND 3 |
EXCEPT (差集)
1 | SELECT * FROM player WHERE level BETWEEN 1 AND 3 |
MySQL Subquery
sometimes we need one query to be the condition of another query.
1
2
3
4
5
6
7
8
9
10
11
12
13SELECT * FROM player WHERE level > (SELECT AVG(level) FROM player)
-- remember to use alias
SELECT
level, ROUND(SELECT AVG(level) FROM player) as average,
level - ROUND(SELECT AVG(level) from player) as diff
FROM player
-- can also used in creating new table
create table new_player select * from player where level < 5;
-- insert new data
insert into new_player select * from player where level between 6 and 10;
MySQL Table Association
INNER JOIN
1 | -- display what both table have |
LEFT JOIN
1 | -- display what left table have |
RIGHT JOIN
1 | -- similarly, display what right table have |
MySQL in terminal
- Login (local)
Login the localhost mysql database, -u
specifies the
username, -p
specify to use password to login
1
mysql -u root -p
- Login (remote)
Login the remote mysql database, -h
specifies the IP
address of the host machine 1
mysql -h 10.211.55.5 -u root -p
Switch database
1
use database_name;
Show database
1
show databases;
Create a database
1
create database game;
Create tables
1
2
3
4
5
6
7create table player (
id INT,
name VARCHAR(100),
level INT,
exp INT,
gold DECIMAL(10, 2) <- 长度为10保留两位小数的十进制数值
)Describe the structure of the table
1
mysql> DESC player;
CRUD
1
SELECT/UPDATE/DELETE/ALTER ...
exit
1
exit;
export database
1
mysqldump -u root -p game player > player.sql
import database
1
mysql> mysql -u root -p game < game.sql
Reference
MySQL Cheatsheet