MySQL 速查表

1

SHOW DATABASES;

1

SHOW SCHEMAS;

1

USE <database_name>;

1
2
3

CREATE DATABASE IF NOT EXISTS <database_name>
DEFAULT CHARACTER SET=utf8mb4
DEFAULT COLLATE=utf8mb4_0900_ai_ci;

1
2
3
4
5
6
7
8
9

-- 5.1.7~5.1.23 版本可以用的，但是官方不推荐，会有丢失数据的危险
SHOW PROCESSLIST;
KILL <connection_id>;
RENAME DATABASE <old_database_name> TO <new_database_name>;

-- 使用其它方式重命名
CREATE DATABASE <new_database_name>;
CREATE TABLE <new_database_name.table_name> AS SELECT * FROM <old_database_name.table_name>;
DROP DATABASE <old_database_name>;

1
2
3
4
5
6
7

#!/usr/bin/env bash

tables=$(mysql -uroot -p123456 -Nse "SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='<old_database_name>'")
for table in $tables
do
  mysql -uroot -p123456 -e "RENAME TABLE <old_database_name>.$table TO <new_database_name>.$table"
done

1

DROP DATABASE <database_name>;

1

SHOW CREATE DATABASE <database_name>;

1
2
3
4
5
6
7

CREATE TABLE <table_name> (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '主键', -- 自动增长的主键
  username VARCHAR(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '账户名称',
  preferences JSON DEFAULT NULL COMMENT '偏好信息', -- JSON 列非常适合存储非结构化数据，并且从 MySQL 5.7.8 版本开始支持 JSON 列
  actived BOOLEAN default true COMMENT '是否激活',
  created_at DATETIME DEFAULT NULL COMMENT '创建日期时间'
) ENGINE=INNODB DEFAULT CHARSET=ubf8mb4 COLLATE=utf8mb4_unicode_ci;

1

DROP TABLE <table_name>;

1
2
3
4
5
6
7
8
9

ALTER TABLE <old_table_name> RENAME <new_table_name>;

或者

RENAME TABLE <old_table_name> TO <new_table_name>;

或者

RENAME TABLE <table_name_1> TO <old_table_name>,<old_table_name> TO <table_name_2>;

1
2
3
4

TRUNCATE TABLE <table_name>;

-- The TABLE keyword is actually optional
TRUNCATE <table_name>;

1

DELETE TABLE <table_name>;

1
2
3
4
5
6
7

CREATE TABLE {new_table} AS SELECT * FROM {src_table};


-- 继承表的定义，即结构。
CREATE TABLE {new_table} LIKE {src_table};

INSERT INTO {new_table} SELECT * FROM {src_table};

1

SHOW TABLES;

1
2
3
4
5

DESC <table_name>;

或者

DESCRIBE <table_name>;

1

SHOW CREATE TABLE <table_name>;

 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

INSERT INTO <table_name> VALUES ('Tony','tony@gmail.com');

INSERT INTO <table_name> (uname,email) VALUES ('Tony','tony@gmail.com');

INSERT INTO <table_name> SET uname='Tony',email='tony@gmail.com';

INSERT INTO <table_name> (uname,email)
VALUES
  ('John','john@gmail.com'),
  ('Acme','acme@gmail.com');

INSERT INTO <table_name_1> (uname,email)
SELECT uname,email
FROM {table_name_2}
WHERE id>1001;

INSERT INTO <table_name> SET info='{"beta": true, "status": "for review", "test_count": 1}';

INSERT IGNORE INTO <table_name> (product_id,product_name,stocks) VALUES (1, 'Product 1', 50);

INSERT INTO <table_name> (product_id,product_name,stocks)
VALUES (1, 'Product 1', 45)
ON DUPLICATE KEY UPDATE stocks=VALUES(stocks);

REPLACE INTO <table_name> (product_id,product_name,stocks)
VALUES (1, 'Product 1', 35)
ON DUPLICATE KEY UPDATE stocks = VALUES(stocks);

1
2
3
4
5
6
7

UPDATE <table_name> SET stocks=100;

UPDATE <table_name> SET stocks=100,available=true;

UPDATE <table_name> SET stocks=100,available=true WHERE product_id=1;

UPDATE <table_name> SET stocks=50,available=true WHERE category='Computer';

1

DELETE FROM <table_name> WHERE product_id=1;

1

SELECT * FROM <table_name> WHERE product_id<1000 ANS product_name LIKE '%foo%' LIMIT 5;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

CREATE OR REPLACE VIEW <view_name> AS <select statement>;

-- A view to show only beta users
CREATE VIEW beta_users_vw AS
SELECT * FROM <table_name> WHERE beta = 1;

-- A view to limit read access to only certain columns
CREATE VIEW users_basic_vw AS
SELECT first_name, last_name, telephone_number
FROM <table_name>;

-- A view for management so they only need to do a "SELECT * FROM top_20_customers_vw" instead of learning a complex SQL
CREATE OR REPLACE VIEW top_20_customers_vw AS
SELECT c.customer_name, sum(p.price*od.quantity) order_total
FROM customers c
JOIN orders o USING (customer_id)
JOIN order_details od USING (order_id)
JOIN products p USING (product_id)
GROUP BY c.customer_name
ORDER BY order_total DESC
LIMIT 20;

1

DROP VIEW <view_name>;

1
2
3
4
5

ALTER TABLE <table_name> ADD updated_at DATETIME NOT NULL COMMENT '更新日期时间';

ALTER TABLE <table_name> ADD nickname VARCHAR(100) NOT NULL;

ALTER TABLE <table_name> ADD actived BOOLEAN DEFAULT TRUE;

1
2
3
4

ALTER TABLE <table_name> DROP COLUMN {column_name};

-- The COLUMN keyword is actually optional
ALTER TABLE <table_name> DROP {column_name};

1
2
3
4
5

-- MySQL 5.x
ALTER TABLE <table_name> CHANGE product_name product_full_name VARCHAR(100) NOT NULL;

-- MySQL 8.0
ALTER TABLE <table_name> RENAME COLUMN product_name TO product_full_name;

1
2
3
4
5

-- Example: <table_name> have a default stock of 0
ALTER TABLE <table_name> ALTER COLUMN stocks integer SET DEFAULT 0;

-- Example: <table_name> are available by default (removed optional COLUMN keyword)
ALTER TABLE <table_name> ALTER available SET DEFAULT true;

1
2
3
4
5

--Example: <table_name> have a default stock of 0
ALTER TABLE <table_name> ALTER COLUMN stocks integer DROP DEFAULT;

--Example: <table_name> are available by default (removed optional COLUMN keyword)
ALTER TABLE <table_name> ALTER available DROP DEFAULT;

1
2
3

ALTER TABLE <table_name> MODIFY stocks INT NOT NULL;

ALTER TABLE <table_name> MODIFY stocks INT NOT NULL DEFAULT 0;

1
2
3

ALTER TABLE <table_name> MODIFY stocks INT;

ALTER TABLE <table_name> MODIFY stocks INT DEFAULT 0;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE INDEX idx_username ON <table_name> (username);

CREATE INDEX idx_user_org_id ON <table_name> (user_id, org_id);

CREATE UNIQUE INDEX udx_email ON <table_name> (email);

CREATE INDEX idx_nickname ON <table_name> (nickname(20));

CREATE INDEX idx_reverse_truename ON <table_name> (truename DESC);

-- MySQL 8.0.13 and higher also supports functional key parts
CREATE INDEX idx_line_item ON order_line_items ((product_price * quantity));

1
2
3
4
5
6
7

DROP INDEX idx_name ON <table_name>;

DROP INDEX `PRIMARY` ON <table_name>;

ALTER TABLE <table_name> DROP INDEX idx_name;

ALTER TABLE <table_name> DROP PRIMARY KEY;

1

ALTER TABLE <table_name> AUTO_INCREMENT=1000;

 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

mysql> CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `day` date DEFAULT NULL,
  `tickets` tinyint unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

mysql> INSERT INTO test (day, tickets) VALUES ('2022-10-12',1),('2022-11-13',2),('2023-01-18',NULL),('2023-02-21',3);

mysql> SELECT * FROM test;
+----+------------+---------+
| id | day        | tickets |
+----+------------+---------+
|  1 | 2022-10-12 |       1 |
|  2 | 2022-11-13 |       2 |
|  3 | 2023-01-18 |    NULL |
|  4 | 2023-02-21 |       3 |
+----+------------+---------+

mysql> SELECT day,IFNULL(tickets, 0) FROM test;
+------------+--------------------+
| day        | IFNULL(tickets, 0) |
+------------+--------------------+
| 2022-10-12 |                  1 |
| 2022-11-13 |                  2 |
| 2023-01-18 |                  0 |
| 2023-02-21 |                  3 |
+------------+--------------------+

 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

mysql> SELECT
  f.title,
  ROUND(100.0 * (SELECT COUNT(*) FROM film AS f2 WHERE f2.length <= f.length) / totals.film_count, 1) AS percentile
FROM film f
CROSS JOIN (
  SELECT COUNT(*) AS film_count
  FROM film
) AS totals
ORDER BY percentile DESC
LIMIT 25;

+--------------------+------------+
| title              | percentile |
+--------------------+------------+
| HOME PITY          |      100.0 |
| DARN FORRESTER     |      100.0 |
| MUSCLE BRIGHT      |      100.0 |
| CONTROL ANTHEM     |      100.0 |
| SOLDIERS EVOLUTION |      100.0 |
| CHICAGO NORTH      |      100.0 |
| POND SEATTLE       |      100.0 |
| GANGS PRIDE        |      100.0 |
| SWEET BROTHERHOOD  |      100.0 |
| WORST BANGER       |      100.0 |
| SORORITY QUEEN     |       99.0 |
| THEORY MERMAID     |       99.0 |
| CRYSTAL BREAKING   |       99.0 |
| SMOOCHY CONTROL    |       99.0 |
| MOONWALKER FOOL    |       99.0 |
| SONS INTERVIEW     |       99.0 |
| CONSPIRACY SPIRIT  |       99.0 |
| KING EVOLUTION     |       99.0 |
| WIFE TURN          |       98.2 |
| FRONTIER CABIN     |       98.2 |
| YOUNG LANGUAGE     |       98.2 |
| SCALAWAG DUCK      |       98.2 |
| CATCH AMISTAD      |       98.2 |
| SEARCHERS WAIT     |       97.7 |
| BAKED CLEOPATRA    |       97.7 |
+--------------------+------------+
25 rows in set (0.36 sec)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

SELECT *,row_number() OVER (PARTITION BY rating ORDER BY release_year) as row_num
FROM film;

WITH myfilm AS (
  SELECT *,row_number() OVER (PARTITION BY rating ORDER BY release_year) as row_num FROM film
) SELECT * FROM myfilm WHERE row_num = 1;
+---------+------------------+------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+----------------------------------+---------------------+---------+
| film_id | title            | description                                                                                          | release_year | language_id | original_language_id | rental_duration | rental_rate | length | replacement_cost | rating | special_features                 | last_update         | row_num |
+---------+------------------+------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+----------------------------------+---------------------+---------+
|       2 | ACE GOLDFINGER   | A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China |         2006 |           1 |                 NULL |               3 |        4.99 |     48 |            12.99 | G      | Trailers,Deleted Scenes          | 2006-02-15 05:03:42 |       1 |
|       1 | ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies     |         2006 |           1 |                 NULL |               6 |        0.99 |     86 |            20.99 | PG     | Deleted Scenes,Behind the Scenes | 2006-02-15 05:03:42 |       1 |
|       7 | AIRPLANE SIERRA  | A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat                    |         2006 |           1 |                 NULL |               6 |        4.99 |     62 |            28.99 | PG-13  | Trailers,Deleted Scenes          | 2006-02-15 05:03:42 |       1 |
|       8 | AIRPORT POLLOCK  | A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India                        |         2006 |           1 |                 NULL |               6 |        4.99 |     54 |            15.99 | R      | Trailers                         | 2006-02-15 05:03:42 |       1 |
|       3 | ADAPTATION HOLES | A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory     |         2006 |           1 |                 NULL |               7 |        2.99 |     50 |            18.99 | NC-17  | Trailers,Deleted Scenes          | 2006-02-15 05:03:42 |       1 |
+---------+------------------+------------------------------------------------------------------------------------------------------+--------------+-------------+----------------------+-----------------+-------------+--------+------------------+--------+----------------------------------+---------------------+---------+
5 rows in set (0.01 sec)