186 lines
4.0 KiB
Markdown
186 lines
4.0 KiB
Markdown
# 数据库查询
|
||
|
||
## 一、基础查询
|
||
|
||
### 1.1SELECT
|
||
|
||
#### 查全部
|
||
|
||
```sql
|
||
SELECT * FROM table;
|
||
```
|
||
|
||
#### 查指定字段
|
||
|
||
```sql
|
||
SELECT name,age FROM table;
|
||
```
|
||
|
||
#### 字段别名
|
||
|
||
```sql
|
||
SELECT name as newName,age as '年龄' FROM table;
|
||
```
|
||
|
||
#### 过滤字段重复
|
||
|
||
```sql
|
||
# 过滤掉目标所有字段都重复的行
|
||
SELECT DISTINCT name, age FROM table;
|
||
```
|
||
### 1.2聚合函数
|
||
|
||
```sql
|
||
SELECT COUNT(id) AS '数量', SUM(id) AS '总和', AVG(id) AS '平均数', MAX(id) AS '最大数', MIN(id) AS '最小数' FROM table;
|
||
```
|
||
|
||
### 1.3WHERE
|
||
|
||
```sql
|
||
SELECT * FROM table WHERE id = 1;
|
||
/* SELECT * FROM user_info_structs WHERE user_info_struct_sequence= '3' OR field_name = '头像'; */
|
||
/* SELECT * FROM user_info_structs WHERE user_info_struct_sequence= '1' AND field_name = '头像'; */
|
||
```
|
||
|
||
### 1.4模糊查询 LIKE
|
||
|
||
```sql
|
||
/*
|
||
任意字符 %
|
||
单一字符 _
|
||
*/
|
||
SELECT name WHERE name LIKE '何%';
|
||
/*
|
||
何% 何（任意个字符）
|
||
何_ 何X
|
||
何__ 何XX
|
||
*/
|
||
```
|
||
|
||
### 1.5排序 ORDER BY
|
||
|
||
```sql
|
||
/*
|
||
默认 ASC 从小到大 升序
|
||
DESC 从大到小 降序
|
||
*/
|
||
SELECT * FROM table ORDER BY id DESC;
|
||
SELECT * FROM user_info_structs WHERE field_name LIKE '手机%' ORDER BY user_info_struct_sequence ASC;
|
||
/* ORDER BY 要写在 WHERE后面*/
|
||
|
||
/*多顺序*/
|
||
SELECT * FROM table ORDER BY id DESC, name ASC;
|
||
```
|
||
|
||
### 1.6分组 GROUP BY
|
||
|
||
```sql
|
||
/*
|
||
分组只会出现分组数量的列
|
||
最好用分组目标作为一个列明区分，然后用count之类的几何函数
|
||
ORDER BY放在WHERE后面
|
||
HAVING 相当于条件中的条件 放在ORDER BY后面
|
||
|
||
*/
|
||
SELECT field_name,COUNT(user_info_struct_sequence) AS 'num' FROM user_info_structs WHERE user_info_struct_sequence >= 1 GROUP BY field_name HAVING field_name != '手机';
|
||
```
|
||
### 1.7分页LIMIT
|
||
|
||
```sql
|
||
/*LIMIT要放在后面，在ORDER BY之后*/
|
||
|
||
SELECT * FROM table WHERE id >= 3 ORDER BY age DESC LIMIT 0,10;
|
||
SELECT * FROM user_info_structs WHERE user_info_struct_sequence >= 1 ORDER BY field_display_type DESC LIMIT 0,3;
|
||
/*想要先切片在排序，需要写子查询*/
|
||
SELECT column1, column2
|
||
FROM (
|
||
SELECT column1, column2
|
||
FROM table_name
|
||
LIMIT 10
|
||
) AS subquery
|
||
ORDER BY column1;
|
||
```
|
||
## 二、比较逻辑运算
|
||
|
||
### 2.1 AND
|
||
|
||
```sql
|
||
SELECT * FROM table WHERE age > 13 AND age < 20;
|
||
```
|
||
|
||
### 2.2 BETWEEN < >
|
||
```sql
|
||
SELECT * FROM table WHERE age > 13 AND age < 20;
|
||
/*相当于*/
|
||
SELECT * FROM table WHERE BETWEEN 13 AND 20;
|
||
```
|
||
### 2.3 OR
|
||
|
||
```sql
|
||
SELECT * FROM table WHERE age >= 13 OR sex = 1;
|
||
```
|
||
### 2.4 NULL和''
|
||
|
||
```sql
|
||
SELECT * FROM table WHERE sex = '';
|
||
SELECT * FROM table WHERE sex IS NULL;
|
||
SELECT * FROM table WHERE sex IS NOT NULL;
|
||
```
|
||
### 2.5 !=,<>,NOT
|
||
|
||
```sql
|
||
SELECT * FROM table WHERE sex != '';
|
||
/*相当与*/
|
||
SELECT * FROM table WHERE sex <> '';
|
||
```
|
||
|
||
## 三、多表连接
|
||
|
||
### 3.1 内连接
|
||
|
||
```sql
|
||
/*student是学生表，dept是专业表，差学生所有信息，并获取学生的所在专业ID的名称*/
|
||
SELECT s.*, d.name FROM student s INNER JOIN dept d ON s.did = d.did;
|
||
SELECT s.*, d.name FROM student s JOIN dept d ON s.did = d.did;
|
||
/*不推荐写法*/
|
||
SELECT s.*, d.name FROM student s , dept d WHERE s.did = d.did;
|
||
```
|
||
### 3.2 左外连接/右外连接
|
||
|
||
```sql
|
||
/*以A表为基准， 查出来的数据A表是满的，*/
|
||
-- 左连接
|
||
SELECT a.*,b.* FROM ATABLE a LEFT JOIN BTABLE b ON a.bid = b.id;
|
||
-- 左外连接
|
||
SELECT a.*,b.* FROM ATABLE a LEFT OUTER JOIN BTABLE b ON a.bid = b.id;
|
||
-- 以b表为基准
|
||
SELECT a.*,b.* FROM BTABLE b LEFT JOIN ATABLE a ON a.bid = b.id;
|
||
```
|
||
## 四、子查询
|
||
|
||
### 4.1 =
|
||
```sql
|
||
-- 查询和id等于2的列名一样的数据
|
||
SELECT *
|
||
FROM user_info_structs
|
||
WHERE field_name = (
|
||
SELECT field_name
|
||
FROM user_info_structs
|
||
WHERE user_info_struct_sequence = 2
|
||
);
|
||
```
|
||
|
||
### 4.2 IN
|
||
```sql
|
||
-- 查列名为生日的id的数据
|
||
SELECT *
|
||
FROM user_info_structs
|
||
WHERE user_info_struct_sequence IN (
|
||
SELECT user_info_struct_sequence
|
||
FROM user_info_structs
|
||
WHERE field_name = "生日"
|
||
);
|
||
-- 可以使用NOT IN
|
||
-- IN查询一般效率比较低，建议使用多表连接
|
||
```
|