文章目录
SQL
分类：
- DDL：数据定义语言，用于定义不同的数据段、数据库、表、列、索引等数据对象，常用的语句关键字包括create、drop、alter等
- DML：数据操纵语言，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字有insert、delete、update和select等
- DCL：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句，这些语句定义了数据库、表、字段、用户的访问权限和安全级别，常用的语句关键字有grant、revoke等
DDL语句
-
创建数据库：
CREATE DATABSES dbname -
查看已有的数据库:
SHOW DATABASES;mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sakila | | sql_hr | | sql_inventory | | sql_invoicing | | sql_store | | sys | | world | +--------------------+ 10 rows in set (0.00 sec) -
选择数据库：
USE dbname;mysql> USE sql_store; Database changed -
查看数据库中的表：
SHOW Tables;mysql> SHOW TABLES; +---------------------+ | Tables_in_sql_store | +---------------------+ | customers | | order_item_notes | | order_items | | order_statuses | | orders | | products | | shippers | +---------------------+ 7 rows in set (0.00 sec) -
删除数据库：
DROP DATABASE dbname -
创建表：
CREATE TABLE tablename(column_name_1 column_type_1 constraints, column_name_2 column_type_2 constraints, ...... column_name_n column_type_n constraints);CREATE TABLE `customers` ( `customer_id` int NOT NULL AUTO_INCREMENT, `first_name` varchar(50) NOT NULL, `last_name` varchar(50) NOT NULL, `birth_date` date DEFAULT NULL, `phone` varchar(50) DEFAULT NULL, `address` varchar(50) NOT NULL, `city` varchar(50) NOT NULL, `state` char(2) NOT NULL, `points` int NOT NULL DEFAULT '0', PRIMARY KEY (`customer_id`) ) ENGINE=InnoDB当数据库中已存在表时，可以使用
SHOW CREATE TABLE dbname \G;查看创建表的SQL语句。
mysql> SHOW CREATE TABLE customers \G; *************************** 1. row *************************** Table: customers Create Table: CREATE TABLE `customers` ( `customer_id` int NOT NULL AUTO_INCREMENT, `first_name` varchar(50) NOT NULL, `last_name` varchar(50) NOT NULL, `birth_date` date DEFAULT NULL, `phone` varchar(50) DEFAULT NULL, `address` varchar(50) NOT NULL, `city` varchar(50) NOT NULL, `state` char(2) NOT NULL, `points` int NOT NULL DEFAULT '0', PRIMARY KEY (`customer_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec)当数据库中存在表时，可以使用
DESC tablenamemysql> DESC customers; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | customer_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(50) | NO | | NULL | | | last_name | varchar(50) | NO | | NULL | | | birth_date | date | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | | address | varchar(50) | NO | | NULL | | | city | varchar(50) | NO | | NULL | | | state | char(2) | NO | | NULL | | | points | int | NO | | 0 | | +-------------+-------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec) -
删除表：
DROP TABLE tablename -
修改表：
ALTER TABLE tablename MODIFY [COLUMN] column_definition [FIRST | AFTER col_name];mysql> DESC customers; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | customer_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(50) | NO | | NULL | | | last_name | varchar(50) | NO | | NULL | | | birth_date | date | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | | address | varchar(50) | NO | | NULL | | | city | varchar(50) | NO | | NULL | | | state | char(2) | NO | | NULL | | | points | int | NO | | 0 | | +-------------+-------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec) mysql> ALTER TABLE customers MODIFY first_name VARCHAR(20); Query OK, 10 rows affected (0.10 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> DESC customers; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | customer_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(50) | NO | | NULL | | | birth_date | date | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | | address | varchar(50) | NO | | NULL | | | city | varchar(50) | NO | | NULL | | | state | char(2) | NO | | NULL | | | points | int | NO | | 0 | | +-------------+-------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec) -
增加表字段：
ALTER TABLE tablename ADD [COLUMN] column_definition [FIRST | AFTER col_name];mysql> ALTER TABLE customers ADD COLUMN grade VARCHAR(20); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC customers; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | customer_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(50) | NO | | NULL | | | birth_date | date | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | | address | varchar(50) | NO | | NULL | | | city | varchar(50) | NO | | NULL | | | state | char(2) | NO | | NULL | | | points | int | NO | | 0 | | | grade | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+ 10 rows in set (0.01 sec) -
删除表字段：
ALTER TABLE tablename DROP [COLUMN] col_name; -
表字段改名：
ALTER TABLE tablename CHANGE [COLUMN] old_col_name column_definition [FIRST | AFTER col_name];mysql> DESC customers; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | customer_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(50) | NO | | NULL | | | birth_date | date | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | | address | varchar(50) | NO | | NULL | | | city | varchar(50) | NO | | NULL | | | state | char(2) | NO | | NULL | | | points | int | NO | | 0 | | | grade | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+ 10 rows in set (0.01 sec) mysql> ALTER TABLE customers CHANGE grade greade_new VARCHAR(20); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC customers; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | customer_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(50) | NO | | NULL | | | birth_date | date | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | | address | varchar(50) | NO | | NULL | | | city | varchar(50) | NO | | NULL | | | state | char(2) | NO | | NULL | | | points | int | NO | | 0 | | | greade_new | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+ 10 rows in set (0.00 sec)
change和modify都可以修改表的定义，不同的是change后面需要写两次列名，不方便。但是change的优点是可以修改列名称，modify则不能。
-
修改字段的排列顺序：
上面字段增加和修改语法（ADD、CHANGE、MODIFY）中，都有可选项
FIRST | AFTER column_name默认ADD增加的新字段是加在表的最后位置，而CHANGE/MODIFY默认都不会改变字段的位置。
- FIRST :代表将字段位置添加或修改到第一列
- AFTER column_name : 代表将字段位置添加或者修改到column_name字段的后面。
mysql> DESC customers; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | customer_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(50) | NO | | NULL | | | birth_date | date | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | | address | varchar(50) | NO | | NULL | | | city | varchar(50) | NO | | NULL | | | state | char(2) | NO | | NULL | | | points | int | NO | | 0 | | | grade_new | varchar(20) | YES | | NULL | | +-------------+-------------+------+-----+---------+----------------+ 10 rows in set (0.00 sec) mysql> ALTER TABLE customers CHANGE grade_new grade VARCHAR(20) FIRST; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC customers; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | grade | varchar(20) | YES | | NULL | | | customer_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(50) | NO | | NULL | | | birth_date | date | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | | address | varchar(50) | NO | | NULL | | | city | varchar(50) | NO | | NULL | | | state | char(2) | NO | | NULL | | | points | int | NO | | 0 | | +-------------+-------------+------+-----+---------+----------------+ 10 rows in set (0.00 sec)
DCL 语句
DCL语句是DBA用于管理系统中的对象权限时使用的语句，了解即可。
DML 语句
DML语句用于对数据库记录的增删改查等操作。
-
插入记录：
INSERT INTO tablename (field1, field2, ...,fieldn) VALUES (v1, v2, ...,vn);如果不指定表的字段名，VALUES后的值要和表中定义的FIELD一一对应。
mysql> INSERT INTO customers (first_name, last_name, birth_date, phone, address, city, state, points) -> VALUES ('kobe', 'bryant', '1978-08-23', 888-888-2424, 'Lakers', 'LA', 'C', 81); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM customers; +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 | | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 | | 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 | | 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 | | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | | 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 | | 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 | | 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 | | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 | | 11 | kobe | bryant | 1978-08-23 | -2424 | Lakers | LA | C | 81 | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ 11 rows in set (0.00 sec)一次性插入多条记录：
INSERT INTO tablename (field1, field2, ...,fieldn) VALUES (v1, v2, ...,vn)， (v1, v2, ...,vn)， ... (v1, v2, ...,vn)，; -
更新记录
UPDATE tablename SET field1 = value1, field2 = value2, ..., fieldn = valuen [WHERE CONDITION]mysql> SELECT * FROM customers; +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 | | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 | | 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 | | 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 | | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | | 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 | | 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 | | 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 | | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 | | 11 | kobe | bryant | 1978-08-23 | -2424 | Lakers | LA | C | 81 | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ 11 rows in set (0.00 sec) mysql> UPDATE customers SET phone = '888-888-2424' WHERE first_name = 'kobe'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM customers; +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 | | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 | | 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 | | 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 | | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | | 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 | | 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 | | 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 | | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 | | 11 | kobe | bryant | 1978-08-23 | 888-888-2424 | Lakers | LA | C | 81 | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ 11 rows in set (0.00 sec)UPDATE支持同时更新多个表中的记录。
-
删除记录：
DELETE FROM tablename [WHERE CONDITION]mysql> SELECT * FROM customers; +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 | | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 | | 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 | | 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 | | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | | 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 | | 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 | | 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 | | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 | | 11 | kobe | bryant | 1978-08-23 | 888-888-2424 | Lakers | LA | C | 81 | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ 11 rows in set (0.00 sec) mysql> DELETE FROM customers WHERE first_name = 'kobe'; Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM customers; +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 | | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 | | 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 | | 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 | | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | | 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 | | 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 | | 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 | | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ 10 rows in set (0.00 sec) -
查询记录：
SELECT * FROM tablename [WHERE CONDITION]-
查询不重复的记录，可以用DISTINCT关键字实现
-
排序和限制：
SELECT * FROM tablename [WHERE CONDITION] [ORDER BY field1 [DESC|ASC], field2 [DESC|ASC],...fieldn [DESC|ASC]]; -
对于排序后的记录可使用LIMIT关键字限制输出的部分
mysql> SELECT * FROM customers; +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 | | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 | | 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 | | 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 | | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | | 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 | | 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 | | 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 | | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ 10 rows in set (0.00 sec) mysql> SELECT * FROM customers WHERE state = 'MA'; +-------------+------------+------------+------------+--------------+----------------+---------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+------------+------------+--------------+----------------+---------+-------+--------+ | 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 | +-------------+------------+------------+------------+--------------+----------------+---------+-------+--------+ 1 row in set (0.00 sec) mysql> SELECT * FROM customers ORDER BY Points DESC; +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | | 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 | | 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 | | 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 | | 9 | Romola | Rumgay | 1992-05-23 | 559-181-3744 | 3520 Ohio Trail | Visalia | CA | 1486 | | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 | | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 | | 4 | Ambur | Roseburgh | 1974-04-14 | 407-231-8017 | 30 Arapahoe Terrace | Orlando | FL | 457 | | 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 | +-------------+------------+------------+------------+--------------+------------------------+------------------+-------+--------+ 10 rows in set (0.00 sec) mysql> SELECT * FROM customers WHERE points > 3000; +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+ | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM customers ORDER BY Points DESC LIMIT 3; +-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+ | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | | 3 | Freddi | Boagey | 1985-02-07 | 719-724-7869 | 251 Springs Junction | Colorado Springs | CO | 2967 | +-------------+------------+-----------+------------+--------------+----------------------+------------------+-------+--------+ 3 rows in set (0.00 sec) -
-
聚合：
SELECT [field1,field2,......fieldn] fun_name FROM tablename [WHERE where_contition] [GROUP BY field1,field2,......fieldn [WITH ROLLUP]] [HAVING where_contition];- fun_name 表示要做的集合操作，也就是聚合函数，常用的有sum（求和）、count(*)（记录数）、max（最大值）、min（最小值）
- GROUP BY 关键字表示要进行分类聚合的字段，比如要按照部门分类统计员工数量，部门就应该写在group by后面
- WITH ROLLUP 是可选语法，表明是否对分类聚合后的结果进行再汇总
- HAVING 关键字表示对分类后的结果再进行条件的过滤
mysql> SELECT COUNT(1) FROM customers; +----------+ | COUNT(1) | +----------+ | 10 | +----------+ 1 row in set (0.00 sec) mysql> SELECT state, COUNT(1) FROM customers GROUP BY state; +-------+----------+ | state | COUNT(1) | +-------+----------+ | MA | 1 | | VA | 1 | | CO | 1 | | FL | 2 | | TX | 1 | | IL | 1 | | TN | 1 | | CA | 1 | | GA | 1 | +-------+----------+ 9 rows in set (0.00 sec) mysql> SELECT max(points), min(points), sum(points) FROM customers; +-------------+-------------+-------------+ | max(points) | min(points) | sum(points) | +-------------+-------------+-------------+ | 3675 | 205 | 17551 | +-------------+-------------+-------------+ 1 row in set (0.00 sec) -
表连接：表连接分为内连接和外连接，它们之间的最主要区别是：内连接仅选出两张表中互相匹配的记录，而外连接会选出其他不匹配的记录。我们常用的是内连接。外连接有分为左连接和右连接，具体定义如下：
- 左连接：包含所有的左边表中的记录甚至是右边表中没有和它匹配的记录
- 右连接：包含所有的右边表中的记录甚至是左边表中没有和它匹配的记录
内连接：
mysql> DESC customers; +-------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+----------------+ | customer_id | int | NO | PRI | NULL | auto_increment | | first_name | varchar(20) | YES | | NULL | | | last_name | varchar(50) | NO | | NULL | | | birth_date | date | YES | | NULL | | | phone | varchar(50) | YES | | NULL | | | address | varchar(50) | NO | | NULL | | | city | varchar(50) | NO | | NULL | | | state | char(2) | NO | | NULL | | | points | int | NO | | 0 | | +-------------+-------------+------+-----+---------+----------------+ 9 rows in set (0.00 sec) mysql> DESC orders; +--------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+---------------+------+-----+---------+----------------+ | order_id | int | NO | PRI | NULL | auto_increment | | customer_id | int | NO | MUL | NULL | | | order_date | date | NO | | NULL | | | status | tinyint | NO | MUL | 1 | | | comments | varchar(2000) | YES | | NULL | | | shipped_date | date | YES | | NULL | | | shipper_id | smallint | YES | MUL | NULL | | +--------------+---------------+------+-----+---------+----------------+ 7 rows in set (0.00 sec) mysql> SELECT c.first_name, c.address, c.city, o.order_id, o.order_date, o. status FROM customers c, orders o WHERE c.customer_id = o.customer_id; +------------+------------------------+-----------+----------+------------+--------+ | first_name | address | city | order_id | order_date | status | +------------+------------------------+-----------+----------+------------+--------+ | Elka | 7 Manley Drive | Chicago | 1 | 2019-01-30 | 1 | | Ilene | 50 Lillian Crossing | Nashville | 2 | 2018-08-02 | 2 | | Thacher | 538 Mosinee Center | Sarasota | 3 | 2017-12-01 | 1 | | Ines | 14187 Commercial Trail | Hampton | 4 | 2017-01-22 | 1 | | Clemmie | 5 Spohn Circle | Arlington | 5 | 2017-08-25 | 2 | | Levy | 68 Lawn Avenue | Atlanta | 6 | 2018-11-18 | 1 | | Ines | 14187 Commercial Trail | Hampton | 7 | 2018-09-22 | 2 | | Clemmie | 5 Spohn Circle | Arlington | 8 | 2018-06-08 | 1 | | Levy | 68 Lawn Avenue | Atlanta | 9 | 2017-07-05 | 2 | | Elka | 7 Manley Drive | Chicago | 10 | 2018-04-22 | 2 | +------------+------------------------+-----------+----------+------------+--------+ 10 rows in set (0.00 sec) mysql> SELECT c.first_name, c.address, c.city, o.order_id, o.order_date, o. status FROM customers c JOIN orders o WHERE c.customer_id = o.customer_id; +------------+------------------------+-----------+----------+------------+--------+ | first_name | address | city | order_id | order_date | status | +------------+------------------------+-----------+----------+------------+--------+ | Elka | 7 Manley Drive | Chicago | 1 | 2019-01-30 | 1 | | Ilene | 50 Lillian Crossing | Nashville | 2 | 2018-08-02 | 2 | | Thacher | 538 Mosinee Center | Sarasota | 3 | 2017-12-01 | 1 | | Ines | 14187 Commercial Trail | Hampton | 4 | 2017-01-22 | 1 | | Clemmie | 5 Spohn Circle | Arlington | 5 | 2017-08-25 | 2 | | Levy | 68 Lawn Avenue | Atlanta | 6 | 2018-11-18 | 1 | | Ines | 14187 Commercial Trail | Hampton | 7 | 2018-09-22 | 2 | | Clemmie | 5 Spohn Circle | Arlington | 8 | 2018-06-08 | 1 | | Levy | 68 Lawn Avenue | Atlanta | 9 | 2017-07-05 | 2 | | Elka | 7 Manley Drive | Chicago | 10 | 2018-04-22 | 2 | +------------+------------------------+-----------+----------+------------+--------+ 10 rows in set (0.00 sec)左连接：由于
customer_id = 12的记录没有在orders表中存在，所以对应的字段部分全为NULLmysql> SELECT c.first_name, c.address, c.city, o.order_id, o.order_date, o. status FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id; +------------+------------------------+------------------+----------+------------+--------+ | first_name | address | city | order_id | order_date | status | +------------+------------------------+------------------+----------+------------+--------+ | Babara | 0 Sage Terrace | Waltham | NULL | NULL | NULL | | Ines | 14187 Commercial Trail | Hampton | 4 | 2017-01-22 | 1 | | Ines | 14187 Commercial Trail | Hampton | 7 | 2018-09-22 | 2 | | Freddi | 251 Springs Junction | Colorado Springs | NULL | NULL | NULL | | Ambur | 30 Arapahoe Terrace | Orlando | NULL | NULL | NULL | | Clemmie | 5 Spohn Circle | Arlington | 5 | 2017-08-25 | 2 | | Clemmie | 5 Spohn Circle | Arlington | 8 | 2018-06-08 | 1 | | Elka | 7 Manley Drive | Chicago | 1 | 2019-01-30 | 1 | | Elka | 7 Manley Drive | Chicago | 10 | 2018-04-22 | 2 | | Ilene | 50 Lillian Crossing | Nashville | 2 | 2018-08-02 | 2 | | Thacher | 538 Mosinee Center | Sarasota | 3 | 2017-12-01 | 1 | | Romola | 3520 Ohio Trail | Visalia | NULL | NULL | NULL | | Levy | 68 Lawn Avenue | Atlanta | 6 | 2018-11-18 | 1 | | Levy | 68 Lawn Avenue | Atlanta | 9 | 2017-07-05 | 2 | | kobe | Lakers | LA | NULL | NULL | NULL | +------------+------------------------+------------------+----------+------------+--------+ 15 rows in set (0.00 sec) -
记录联合：用于将两个表的数据按照一定的查询条件查询出来后，将结果合并到一起显示出来。语句关键字为union和union all，具体语法如下：
SELECT` `* ``FROM` `t1``UNION``|``UNION` `ALL``SELECT` `* ``FROM` `t2``......``UNION``|``UNION` `ALL``SELECT` `* ``FROM` `tn;UNION 和 UNION ALL的主要区别是 UNION ALL 是把结果集直接合并在一起，而UNION是将UNION ALL后的结果进行一次DISTINCT，去除重复记录后的结果。
mysql> SELECT * FROM customers WHERE points > 3000 ; +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+ | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | +-------------+------------+-----------+------------+--------------+----------------+-----------+-------+--------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM customers WHERE points > 3000 -> UNION -> SELECT * FROM customers WHERE state = 'MA'; +-------------+------------+------------+------------+--------------+----------------+-----------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+------------+------------+--------------+----------------+-----------+-------+--------+ | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | | 1 | Babara | MacCaffrey | 1986-03-28 | 781-932-9754 | 0 Sage Terrace | Waltham | MA | 2273 | +-------------+------------+------------+------------+--------------+----------------+-----------+-------+--------+ 3 rows in set (0.00 sec) -
子查询：某些情况，当我们查询的时候，需要的条件是另一个SELECT语句的结果，这个时候，就要用到子查询。
用于子查询的关键字主要包括in、not in、=、!=、exists、not exists等。mysql> SELECT * FROM customers WHERE customer_id in (SELECT customer_id FROM orders ); +-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+ | customer_id | first_name | last_name | birth_date | phone | address | city | state | points | +-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+ | 2 | Ines | Brushfield | 1986-04-13 | 804-427-9456 | 14187 Commercial Trail | Hampton | VA | 947 | | 5 | Clemmie | Betchley | 1973-11-07 | NULL | 5 Spohn Circle | Arlington | TX | 3675 | | 6 | Elka | Twiddell | 1991-09-04 | 312-480-8498 | 7 Manley Drive | Chicago | IL | 3073 | | 7 | Ilene | Dowson | 1964-08-30 | 615-641-4759 | 50 Lillian Crossing | Nashville | TN | 1672 | | 8 | Thacher | Naseby | 1993-07-17 | 941-527-3977 | 538 Mosinee Center | Sarasota | FL | 205 | | 10 | Levy | Mynett | 1969-10-13 | 404-246-3370 | 68 Lawn Avenue | Atlanta | GA | 796 | +-------------+------------+------------+------------+--------------+------------------------+-----------+-------+--------+ 6 rows in set (0.00 sec)
