---
title: SQL
category:
tag:
-
- SQL
---
>
>
> - [SQL ](https://juejin.cn/post/6844903790571700231)
> - [MySQL ](https://www.begtut.com/mysql/mysql-tutorial.html)
##
###
- `database` -
- `table` -
- `schema` -
- `column` -
- `row` -
- `primary key` -
### SQL
SQLStructured Query Language) SQL ANSI ANSI SQL DBMS PL/SQLTransact-SQL
#### SQL
SQL
- **``** -
- **``** -
- **``** - SQL 3VLtrue/false/unknown
- **``** - SQL
- **``** -
#### SQL
- **SQL ** DBMS `SELECT` `select` `Select`
- ** SQL `;`**
- SQL ****
SQL
```sql
-- SQL
UPDATE user SET username='robot', password='robot' WHERE username = 'root';
-- SQL
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';
```
SQL
```sql
## 1
-- 2
/* 3 */
```
### SQL
#### DDL
Data Definition LanguageDDL SQL
DDL ****
DDL `CREATE``ALTER``DROP`
#### DML
Data Manipulation Language, DML
DML ********
DML `INSERT``UPDATE``DELETE``SELECT` CRUD(Create, Read, Update, Delete)
#### TCL
(Transaction Control Language, TCL) **** DML
TCL `COMMIT``ROLLBACK`
#### DCL
(Data Control Language, DCL)
DCL `GRANT``REVOKE`
DCL **** DCL `CONNECT``SELECT``INSERT``UPDATE``DELETE``EXECUTE``USAGE``REFERENCES`
DBMS
** DML DML **
##
CRUD
###
`INSERT INTO`
****
```sql
#
INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com');
#
INSERT INTO user
VALUES (10, 'root', 'root', 'xxxx@163.com'), (12, 'user1', 'user1', 'xxxx@163.com'), (18, 'user2', 'user2', 'xxxx@163.com');
```
****
```sql
INSERT INTO user(username, password, email)
VALUES ('admin', 'admin', 'xxxx@163.com');
```
****
```sql
INSERT INTO user(username)
SELECT name
FROM account;
```
###
`UPDATE`
```sql
UPDATE user
SET username='robot', password='robot'
WHERE username = 'root';
```
###
- `DELETE`
- `TRUNCATE TABLE`
****
```sql
DELETE FROM user
WHERE username = 'robot';
```
****
```sql
TRUNCATE TABLE user;
```
###
`SELECT`
`DISTINCT`
`LIMIT` 0
- `ASC`
- `DESC`
****
```sql
SELECT prod_name
FROM products;
```
****
```sql
SELECT prod_id, prod_name, prod_price
FROM products;
```
****
```sql
SELECT *
FROM products;
```
****
```sql
SELECT DISTINCT
vend_id FROM products;
```
****
```sql
-- 5
SELECT * FROM mytable LIMIT 5;
SELECT * FROM mytable LIMIT 0, 5;
-- 3 ~ 5
SELECT * FROM mytable LIMIT 2, 3;
```
##
`order by` `desc`
`order by`
```sql
SELECT * FROM products
ORDER BY prod_price DESC, prod_name ASC;
```
##
**`group by`**
- `group by`
- `group by`
- `group by` `count``max``sum``avg`
- `group by`
- `group by` `order by`
****
```sql
SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name;
```
****
```sql
SELECT cust_name, COUNT(cust_address) AS addr_num
FROM Customers GROUP BY cust_name
ORDER BY cust_name DESC;
```
**`having`**
- `having` `group by`
- `having` `group by`
- `where` `having`
** WHERE HAVING **
```sql
SELECT cust_name, COUNT(*) AS num
FROM Customers
WHERE cust_email IS NOT NULL
GROUP BY cust_name
HAVING COUNT(*) >= 1;
```
**`having` vs `where`**
- `where``where` `group by`
- `having` `group by` `having` `group by`
##
SQL `select` SQL
`SELECT``INSERT``UPDATE` `DELETE` `=````IN``BETWEEN``EXISTS`
`WHERE` `FROM`
- `WHERE` `WHERE`
- `FROM` `FROM`
> MYSQL 4.1
`WHERE`
```sql
select column_name [, column_name ]
from table1 [, table2 ]
where column_name operator
(select column_name [, column_name ]
from table1 [, table2 ]
[where])
```
- `( )`
- `operator` where
`FROM`
```sql
select column_name [, column_name ]
from (select column_name [, column_name ]
from table1 [, table2 ]
[where]) as temp_table_name
where condition
```
`FROM` AS
****
```sql
SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
FROM orderitems
WHERE prod_id = 'RGAN01'));
```
### WHERE
- `WHERE`
- `WHERE` `true` `false`
- `WHERE` `SELECT``UPDATE` `DELETE`
- `WHERE`
| | |
| ------- | ------------------------------------------------------ |
| = | |
| | SQL != |
| > | |
| < | |
| >= | |
| MySQL `;` MySQL BEGIN END
>
> `DELIMITER` DELIMITER `DELIMITER new_delemiter``new_delemiter` 1 `;` `$` - `DELIMITER $` `$`
MySQL 5.7.2
- `BEFORE INSERT` -
- `AFTER INSERT` -
- `BEFORE UPDATE` -
- `AFTER UPDATE` -
- `BEFORE DELETE` -
- `AFTER DELETE` -
MySQL 5.7.2+
**`NEW` `OLD`**
- MySQL `NEW` `OLD`
- `INSERT` `NEW` `BEFORE``AFTER`
- `UPDATE` `OLD` `NEW`
- `DELETE` `OLD`
- `NEW.columnName` columnName
###
>
`CREATE TRIGGER`
```sql
CREATE TRIGGER trigger_name
trigger_time
trigger_event
ON table_name
FOR EACH ROW
BEGIN
trigger_statements
END;
```
- `trigger_name`
- `trigger_time` : `BEFORE` `AFTER`
- `trigger_event` : `INSERT``UPDATE` `DELETE`
- `table_name` :
- `FOR EACH ROW`: Mysql DBMS
- `trigger_statements`: SQL `;`
`BEGIN` `END`
```sql
DELIMITER $
CREATE TRIGGER `trigger_insert_user`
AFTER INSERT ON `user`
FOR EACH ROW
BEGIN
INSERT INTO `user_history`(user_id, operate_type, operate_time)
VALUES (NEW.id, 'add a user', now());
END $
DELIMITER ;
```
###
```sql
SHOW TRIGGERS;
```
###
```sql
DROP TRIGGER IF EXISTS trigger_insert_user;
```
##
- [SQL35+GET!](https://mp.weixin.qq.com/s/I-ZT3zGTNBZ6egS7T09jyQ)
- [SQL30](https://mp.weixin.qq.com/s?__biz=Mzg2OTA0Njk0OA==&mid=2247486461&idx=1&sn=60a22279196d084cc398936fe3b37772&chksm=cea24436f9d5cd20a4fa0e907590f3e700d7378b3f608d7b33bb52cfb96f503b7ccb65a1deed&token=1987003517&lang=zh_CN#rd)