查询

• 一、基本SELECt语句
• 二、过滤和排序数据
• 三、分组函数
• 四、分组查询
• 五、多表查询
• 六、分页查询

一、基本SELECT语句

SELECT *|{[DISTINCT] column|expression}
FROM table;

#SELECt 标识选择哪些列
#FROM 表示从哪个表中选择

SELECt *     #*表示选择全部列
FROM departments;


SELECt department_id,location_id       #选择特定的列
FROM departments;

SQL语言大小写不敏感，SQL可以写在一行或者多行，关键字不能被缩写也不能分行，各子句一般要分行写。使用缩进提高语句的可读性。
日期和字符只能在单引号中出现

列的别名：重命名一个列，能便于计算。

SELECt first_name AS "名字",salary "工资"      #去掉AS也可以 取别名
FROM employees;

使用DESCRIBE命令，显示表结构:

DESCRIBE employees

二、过滤和排序数据

使用WHERe子句，将不满足条件的行过滤，WHERe子句紧跟随FROM子句。

SELECt *|{[DISTINCT] column|expression[alias],...}
FROM table
[WHERe condition(s)];

其中还有一个不常用的<=>符号，表示可能等于，虽然可以用，但不推荐，通常使用<=> null

SELECt last_name,salary
FROM empoyees
WHERe salary <=3000;

（1）BETWEEN…AND…的用法

SELECt last_name,salary
FROM employees
WHERe salary BETWEEN 2500 AND 3500;

（2）IN的用法

SELECt employee_id,last_name,salary,manager_id
FROM employees
WHERe manager_id IN(100,101,201);

（3）LIKE的用法

SELECt first_name
FROM employees
WHERe first_name LIKE 'S%';

（4）NULL的用法

SELECt last_name,manager_id
FROM employees
WHERe manager_id IS NULL;

（5）AND的用法

SELECt employee_id,last_name,job_id,salary
FROM employees
WHERe salary >=100
AND job_id LIKE '%MAN%';

（6）OR的用法

SELECt employee_id,last_name,job_id,salary
FROM employees
WHERe salary >= 10000
OR job_id LIKE '%MAN%';

（7）NOT的用法

SELECt last_name,job_id
FROM employees
WHERe job_id
   NOT IN ('IT_PROG','ST_CLERK','SA_REP');

（8）ORDER BY子句

ASC（ascend）：升序
DESC（descend）：降序

SELECt last_name,job_id,department_id,hire_date
FROM employees
ORDER BY hire_date;         #默认是升序，若想要改为降序则为ORDER BY hire_date DESC;

（9）按别名排序

SELECt employee_id,last_name,salary*12 AS annsal
FROM employees
ORDER BY annsal;

（10）多个列排序

SELECt last_name,department_id,salary
FROM employees
ORDER BY department_id,salary DESC;

三、分组函数

定义：分组函数作用于一组数据，并对一组数据返回一个值。

函数类型：

AVG() ——求平均
COUNT() ——计数
MAX() ——最大值
MIN() ——最小值
SUM() ——求和

函数语法：

SELECt [column,] group_function(column), ...
FROM table
[WHERe condition]
[GROUP BY column]
[ORDER BY column];

（1）AVG（平均值）和 SUM （合计）函数：
可以对数值型数据使用AVG 和 SUM 函数

SELECt AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM employees
WHERe job_id LIKE '%REP%';

（2）MIN（最小值）和 MAX（最大值）函数
可以对任意数据类型的数据使用 MIN 和 MAX 函数

SELECt MIN(hire_date), MAX(hire_date)
FROM employees;

（3）COUNT（计数）函数

COUNT(*) 返回表中记录总数,适用于任意数据类型

SELECt COUNT(*)
FROM employees
WHERe department_id = 50;

COUNT(expr) 返回expr不为空的记录总数

SELECt COUNT(commission_pct)
FROM employees
WHERe department_id = 50;

四、分组查询

（1）GROUP BY 子句语法：

可以使用GROUP BY子句将表中的数据分成若干组

SELECt column, group_function(column)
FROM table
[WHERe condition]
[GROUP BY group_by_expression]
[ORDER BY column];

注意：WHERe一定放在FROM后面

在SELECt 列表中所有未包含在组函数中的列都应该包含在 GROUP BY 子句中。

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;

包含在 GROUP BY 子句中的列不必包含在SELECt 列表中：

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;

（2）使用多个列分组
在GROUP BY子句中包含多个列：

SELECt department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;

（3）非法使用组函数

• 不能在 WHERe 子句中使用组函数。
• 可以在 HAVINg 子句中使用组函数。

SELECt department_id, AVG(salary)
FROM employees
WHERe AVG(salary) > 8000
GROUP BY department_id;

注意：WHERe 子句中不能使用组函数

（4）过滤分组
使用 HAVINg 过滤分组：

1. 行已经被分组
2. 使用了组函数
3. 满足HAVINg 子句中条件的分组将被显示

#语法：

SELECt column, group_function
FROM table
[WHERe condition]
[GROUP BY group_by_expression]
[HAVINg group_condition]
[ORDER BY column];

SELECt department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVINg MAX(salary)>10000 ;

五、多表查询

案例：查询女神名称和对应男神名称

语法：select name,boyName from beauty,boys;

笛卡尔集的错误情况：
select count(*) from beauty;
假设输出12行
select count(*)from boys;
假设输出4行
最终结果：12*4=48行

笛卡尔集会在下面条件下产生：

– 省略连接条件
– 连接条件无效
– 所有表中的所有行互相连接

为了避免笛卡尔集， 可以在 WHERe 加入有效的连接条件。

（1）Mysql 连接
使用连接在多个表中查询数据

SELECt table1.column, table2.column
FROM table1, table2
WHERe table1.column1 = table2.column2;

在 WHERe 子句中写入连接条件
在表中有相同列时，在列名之前加上表名前缀

（2）等值连接

SELECt beauty.id,NAME,boyname FROM beauty ,boys
WHERe beauty.`boyfriend_id`=boys.id;

（3）区分重复的列名

• 使用表名前缀在多个表中区分相同的列。
• 在不同表中具有相同列名的列可以用表的别名 加以区分
• 如果使用了表别名，则在select语句中需要使 用表别名代替表名
• 表别名最多支持32个字符长度，但建议越少越 好

（4）表的别名

优点：

• 使用别名可以简化查询。
• 使用表名前缀可以提高执行效率。

SELECT bt.id,NAME,boyname
FROM beauty bt,boys b;
WHERe bt.`boyfriend_id`=b.id ;

（5）连接多个表

连接 n个表,至少需要 n-1个连接条件

例如：连接 三个表，至少需要两个连接条件。

（6）SQL99：使用ON 子句创建连接

• 自然连接中是以具有相同名字的列为连接条件的。
• 可以使用 ON 子句指定额外的连接条件。
• 这个连接条件是与其它条件分开的。
•ON 子句使语句具有更高的易读性。

（7）join连接

分类：

（8）ON子句

SELECt bt.id,NAME,boyname
FROM beauty bt
Inner join boys b
On bt.`boyfriend_id`=b.id ;

六、分页查询

（1）imit 基本实现方式

一般情况下，客户端通过传递 pageNo（页码）、pageSize（每页条数）两个参数去分页查询数据库中的数据，在数据量较小（元组百/千级）时使用 MySQL自带的 limit 来解决这个问题：

（2）eg.从id开始的10条数据

（3）建立主键或者唯一索引

在数据量较小的时候简单的使用 limit 进行数据分页在性能上面不会有明显的缓慢，但是数据量达到了 万级到百万级 sql语句的性能将会影响数据的返回。这时需要利用主键或者唯一索引进行数据分页；

（4）基于数据再排序

当需要返回的信息为顺序或者倒序时，对上面的语句基于数据再排序。order by ASC/DESC 顺序或倒序 默认为顺序