You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
185 lines
4.0 KiB
185 lines
4.0 KiB
# 数据库查询
|
|
|
|
## 一、基础查询
|
|
|
|
### 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查询一般效率比较低，建议使用多表连接
|
|
```
|
|
|