2.mysql中DQL：查询语言 - 《Mysql》

1.基础查询
2.条件查询
3.排序查询
- 1,语法：
- 2.特点：
4.常见函数
5.分组查询group by
- 1.语法
- 2.特点
6.连接查询join
- 1.概念：
7、子查询
8、分页查询limit
9、联合查询union
10.查询总结
测试题：

1.基础查询

1.语法：

select 查询列表 from 表名

2.特点：

1.查询列表可以是字段，常量，表达式，函数，也可以是多个混合。
2.查询结果是一个虚拟的表

3.示例

1.查询单个字段

select 字段名 from 表名

2.查询多个字段

select 字段名，字段名 from 表名

3.查询所有字段

select * from 表名

4.查询常量

select 常量值
注意：字符型和日期型的常量值必须用单引号引起来，数值型不需要。

5.查询函数

select 函数名(实参列表)

SELECT COUNT(*) from employees;

6.查询表达式：

支持简单的加减乘除不支持 ++ —等复杂类型的表达式

SELECT 100+22;

7。起别名

方式一：as 优点提高可读性
方式二：空格优点方便

8.去重

select distinct 字段名 from表名

9.+

作用：做加法运算
数值+数值：直接运算
SELECT ‘2’+1; 数值加字符先尝试将字符转成数字如果可以就做加运算
SELECT ‘w’+1; 如果不行就将字符转成0做加法运算。
null + 任何数都是null；
10.拼接字符concat函数
功能：拼接字符
select concat(字符1，字符2，字符3)
SELECT CONCAT(1,’wowo’);

11.ifnull函数

作用：判断某字段或表达式是否为null，如果为null，返回指定的值，否则返回原本的值。
select ifnull(username,null) from 表；

12.isnull函数

功能：判断查询的字段值是否为null，如果为null 返回1.如果不为null 返回0；

2.条件查询

1.语法：

select 查询列表 from 表名 where 筛选条件。
2.删选条件的分类
1.简单条件运算符

:大于 >:小于 =：等于 <> ：不等于！=：不等于 <=:大于等于 >=：小于等于

2.逻辑运算符

&& and
|| or
！ not

3.模糊查询

like: 一般搭配通配符使用，用于判断字符型
%% 代表通配符代表多个字符
_代表任意单个字符，可以用\进行转义|asc

-- 查询员工姓名中包含a的
SELECT last_name from employees where last_name like '%a%';
SELECT last_name,employee_id,first_name from  employees where first_name LIKE 'G_%';
-- 查询第二个字母是_的员工姓名
SELECT last_name from employees WHERE last_name LIKE    '_\_%';

between and：
in


-- 查询员工的工种编号是 IT_PROG,AD_VP,AD_PRES中的一个员工名和工种编号
SELECT last_name,job_id from employees where job_id in (' IT_PROG','AD_VP','AD_PRES');

is null/is not null

3.排序查询

1,语法：

  select  字段名 from 表 <br />where 查询条件 <br />order by 字段名 desc | asc

2.特点：

1.asc 表示升序默认为asc
2.desc 表示降序
3.order by子句中可以支持单个字段，多个字段，表达式，函数，别名。
4.order by 一般写在最后边，limit语句除外

-- 查询员工工资，部门编号，姓名 部门编号从高到低
SELECT employee_id,salary,last_name from employees     where manager_id >107 ORDER BY employee_id desc;

4.常见函数

1.概述

功能：类似于java中的方法
好处：提高了代码重用性和隐藏了实现细节
调用函数：select 函数名(参数列表)

2.单行函数

1.字符函数

concat：连接
substr：截取字串
upper：变大写
lower：变小写
replace：替换
length：获取字节长度
trim：去前后空格
lpad：左填充
rpad：右填充
instr：获取字串第一次出现的索引

2.数学函数

ceil：向上取整
round：四舍五入
mod:取模
floor：向下取整
trancate：截断
rand：获取随机数，返回 0-1，无限接近于1

3.日期函数

now：返回当前日期和时间
year：返回年
month：返回月
monthname：以英文形式返回月
day：返回日
hour：返回小时
minute：返回分钟
second：返回秒
detediff：返回两个日期相差的天数
date_format:将日期转成字符
curdate：返回当前日期
str_to_date:将字符转换成日期
crutime：返回当前时间

4.其他函数

version：当前数据库服务器的版本
database：当前打开的数据库
user：当前用户
password（’字符’）;返回该字符的密码形式
MD5(‘字符’)：返回该字符的加密形式

5.流程控制语句

1.if（条件表达式，表达式1，表达式2）；如果条件表达式成立，返回表达式1.否则返回表达式2
2.case情况1
case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
。。。
else 值a
end

2.case情况1
case
when 条件1 then 值1
when 条件2 then值2
、、、
else 值 a
end

3.分组函数

1.分类

max 最大值
min 最小值
sum 和
avg 平均值
count 计算个数

2、特点

1.语法
select max(字段) from 表名
2.支持的类型
sum 和 avg 一般用于支持数值型
max，min 可以处理任何数据类型
3.以上分组函数都忽略null值
4.都可以搭配关键字 distinct使用，实现去重的统计
5.count(字段)
统计该字段中非空值的个数
count(*):统计行数

5.分组查询group by

1.语法

select 分组函数，分组后的字段
from 表
【where 筛选条件】
group by 分组的字段
having 分组后筛选
【order by 排序】

2.特点

使用关键字筛选的表位置
分组前筛选 where 原始表 group by 前边
分组后筛选 having 查询之后的表 group by 后边

6.连接查询join

1.概念：

连接查询又称为多表查询
当查询的字段来自多个表的时候就用用到多表连接查询。
select 字段1，字段2
from 表1，表2

笛卡尔乘积：
当查询多个表时，没有添加有效的连接条件，导致多个表所有行实现完全连接
如何解决：添加有效的连接条件。
2.分类：
按年代分类：
sql92语法：
等值，非等值，自连接
也支持一部分外连接(用于oracle，sqlserver)
sql99语法(推荐)：
内连接
等值
非等值
自连接
外连接
左外
右外
全外(不支持)
交叉连接

3.语法：
sql99语法

外连接：应用场景，用于查询一个表中有，另一个表中没有的记录
特点：
1.外连接的查询结果是主表中的所有记录
如果从表中有和他匹配的，则显示匹配的值
如果从表中没有和他匹配的，则显示null
外连接查询结果=内连接查询结果+主表中有而从表中没有的结果
2.左外连接，left join左边的是主表
右外连接，right join 右边的是主表
3.左外和右外如果表的顺序交换，其实可以实现一样的效果

判断主表和从表：判断你要查询的信息在那个表，那个表就作为主表。

7、子查询

8、分页查询limit

应用场景：当要显示的数据，一页显示不完的时候，就需要使用分页查询
语法：
select 查询列表
from 表
[join type join 表]
where 筛选条件
order by 分组条件
having by 分组后筛选
order by 排序
limit start，size
start ：从0开始，代表记录数
size：代表查多少个

分页公式
select * from 表 where 筛选条件 limit (n-1)size,size;

9、联合查询union

1.概念：就是将多条查询语句合并成一个结果
优点：如果需要查询的条件过多，这样可以使逻辑更加清晰。
缺点：两个联合查询的语句必须要有相同的列数
并且会自动去重
如果不想去重可以变成 union all
举例：

-- 1.案例：查询部门编号>90 或者邮箱中包含a 的员工信息
-- 原来做法
SELECT * from employees where department_id > 90 or email LIKE '%a%';
-- 联合查询做法
SELECT * from employees WHERE department_id > 90
UNION 
SELECT * from employees where email LIKE '%a%';

10.查询总结

执行顺序

测试题：


-- 测试
-- 1.查询员工的姓名和部门号和年薪，按年薪降序，按姓名升序
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) from employees  ORDER BY salary DESC ,last_name asc;
-- 2.选择工资不在8000到17000的员工的姓名和工资，按工资降序
SELECT last_name,salary from employees where salary not in(8000,17000) ORDER BY salary DESC;
-- 3.查询邮箱中包含e的员工信息，并按邮箱的字节数降序，再按部门号升序
SELECT * FROM employees where email like '%e%' ORDER BY count(email),department_id ASC;
-- 测试
-- 1.显示员工表的最大工资，工资平均值
SELECT MAX(salary),AVG(salary) from employees;
-- 2.查询员工表的Employee_id,job_id,last_name,按department_id降序，salary升序
SELECT employee_id,job_id,last_name from employees ORDER BY department_id desc,salary asc;
SELECT employee_id,job_id,last_name from employees ORDER BY department_id DESC,salary asc;
-- 3.查询员工的job_id包含 a和 e的，并且a在e的前面
SELECT job_id from employees where job_id LIKE '%a%e%';
-- 4.已知表 student里边有 id，name，gradeId
-- 已知表grade里面有id name
-- 已知彪啊result，里边有id，score，studentNo
-- 要求查询姓名，年纪名，成绩
SELECT s.name, g.name,r.score FROM gradeID s,grade g,resule r WHERE s.id = g.id and s.id = r.studentNo;
creat table student(
    id int(10) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20)  not NULL,
    gradId Int(10) not null
);
-- 5显示当前日期，以及去前后空格，截取子字符串的函数
SELECT NOW();
SELECT trim(字符 from    '');
SELECT SUBSTR(str,startIndex);