> For the complete documentation index, see [llms.txt](https://chesterzhang666.gitbook.io/intro-mysql/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://chesterzhang666.gitbook.io/intro-mysql/basic-select-ji-ben-cha-xun.md).

# Basic Select

## 数据准备

右键 连接，点击 运行SQL 文件，选择 demo.SQL ([获取源代码](https://github.com/chesterzhang/intro_MySQL/tree/zhc_dev/data)), 随后即可加载一个名为 demo 的数据库, 内含四张数据表，分别如下: 

![t\_bonus](/files/-MfNiUEyJLJ-2Ow5zSkx)

![t\_dept](/files/-MfNiaclwA5WZmDrTmcr)

![t\_emp](/files/-MfNidUE9owh-3TrjZbN)

![t\_salary](/files/-MfNj2KqXOnP8dPUmEW4)

## SELECT 语句

SELECT 查询字段 FROM 表名

```sql
//查询 t_emp 所有字段的数据
SELECT *FROM t_emp;
```

```sql
//查询 t_emp 的 empno, ename 字段的数据
SELECT empno, ename FROM t_emp;
```

### AS 关键字

我们可以用 AS 关键字 

```sql
#对字段使用别名, 比如字段是月薪,月薪*12叫做年薪
SELECT empno, sal*12 AS "annual income"
FROM t_emp;
```

### LIMIT 关键字

LIMIT startIdx, bias 选取第 startIdx到 startIdx+bias-1 条数据，索引从0开始

```sql
#对数据进行分段,使用LIMIT start_idx, bias
SELECT empno, ename FROM t_emp LIMIT 5,5;
```

上面SQL语句: 从 t\_emp中的选择 第6-10条empno,ename数据

### ORDER BY,  ASC, DESC 关键字

ORDER BY 字段名，将数据按照某一字段进行排序，字段后可以跟ASC 表示升序，DESC表示降序。

```sql
#按照某一字段的顺序进行提取,使用ORDER BY
SELECT  ename, sal FROM t_emp ORDER BY sal ASC;
SELECT  ename, sal FROM t_emp ORDER BY sal DESC;
```

也可以对多个字段进行排序，但是前面的字段排序会优先于后面的字段排序，如: 

```sql
# 优先按照 sal 降序排序, 若sal相等, 则按照 hiredate 升序排序
SELECT  ename, sal ,hiredate FROM t_emp ORDER BY sal DESC, hiredate ASC;
```

### ORDER BY 与 LIMIT 联合使用

```sql
# ORDER BY 可以和 LIMIT 联合使用,找出工资排名前五的员工
SELECT  
	ename, sal 
FROM t_emp ORDER BY sal DESC LIMIT 0, 5;
```

### DISTINCT 关键字

DISTINCT 字段名，对某一字段内重复的元素进行去重

```sql
# 使用DISTINCT对某一列提取出来的内容去重,DISTINCT 只能对某一列起作用
SELECT DISTINCT job FROM t_emp;
```

### WHERE 条件查询

```sql
# 条件查询,使用 WHERE 语句
# 如查询部门为10或20,月薪大于2000的员工
SELECT empno,ename sal FROM t_emp
WHERE (deptno=10 OR deptno=20) AND sal>=2000;
```

 SQL 中可以使用AND, OR, NOT 逻辑运算符

### IFNULL(字段, 默认值)若某一字段的元素为NULL, 返回一个默认值

### DATEDIFF(NOW(), 过去日期) 返回现在日期与过去日期的天数差值

```sql
# 查询部门为10 且年薪大于15000,工龄大于20年的员工,年薪等于月薪*12+佣金
# 有的人佣金为NULL, 需要用IFNULL(NULL,0)返回0进行计算
# 使用DATEDIFF(NOW(),hiredate)/365计算工龄
SELECT 
empno, ename, hiredate
FROM t_emp 
WHERE deptno=10 AND 12*sal+IFNULL(comm,0)>=15000 AND DATEDIFF(NOW(),hiredate)/365>=20 ;
```

SQL 支持 +，- , \*, / 运算符

### IN 关键字

IN 关键字能对某一字段的元素内容进行具体的约束，只能为某几个值

```sql
#查询 部门为10, 20, 30 且职务不为"SALESMAN" 的员工信息
# 使用IN
SELECT
empno, ename, deptno
FROM t_emp 
WHERE deptno IN(10,20,30) AND job!="SALESMAN";
```

```sql
#查询部门不为10,20的员工
# 使用NOT
SELECT
empno, ename ,deptno
FROM t_emp 
WHERE NOT deptno IN (10,20);
```

### BETWEEN关键字

BETWEEN value1 AND value2 对字段的值约束到某一范围进行查询 

```sql
#查询月薪1000-2000的员工
#使用 BETWEEN
SELECT
empno, ename 
FROM t_emp 
WHERE  sal BETWEEN 1000 AND 2000;
```

### IS 关键字

```sql
#查询佣金 为NULL 的员工
SELECT
 ename 
FROM t_emp 
WHERE  comm IS NULL;
```

### LIKE 模糊查询

```sql
#查询名字以A打头的员工
#使用 LIKE %
SELECT
empno, ename 
FROM t_emp 
WHERE ename LIKE "A%";
```

```sql
#查询名字中含有A的员工
#使用 LIKE %
SELECT
empno, ename 
FROM t_emp 
WHERE ename LIKE "%A%";
```

```sql
#查询名字为_LAKE的员工
#使用 LIKE %
SELECT
empno, ename 
FROM t_emp 
WHERE ename LIKE "_LAKE";
```

### REGEXP 正则表达式

```sql
#查询中文名字的员工
# 使用REGEXP
SELECT
empno, ename 
FROM t_emp 
WHERE ename REGEXP "^[\\u4e00-\\u9f15]{2,4}$";
```

## 总结一下

### 关键字的执行顺序

#### FROM > WHERE > SELECT > ORDER BY > LIMIT

WHERE 语句执行顺序是从左到右, 应该把带索引的列, 筛选后较少的条件写在左边, 以便快速筛选


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://chesterzhang666.gitbook.io/intro-mysql/basic-select-ji-ben-cha-xun.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.