[大数据]数据库之数据库语言一数据库查询DQL

SQL语法：
1.不区分大小写。
2.不区分是字符还是字符串，统一用单引号，或者双引号。
3.每句代码的结尾分号可有可无。
4.单行注释用 #
5.sql中索引从1开始

基础查询

语法：
select 查询列表 from 表名;
特点：
1、查询列表可以是：表中的字段、常量值、表达式、函数
2、查询的结果是一个虚拟的表格

1.查询表中单个字段

#语法：
SELECT 字段名 FROM 表名;
#举例：
SELECT last_name FROM employees;

2.查询表中多个字段

# 语法
SELECT 字段一,字段二  FROM employees;
#举例
SELECT last_name,salary  FROM employees;
# 三个以上字段类比两个字段

3.查询表中所有字段

#方式一
#语法
SELECT 表中所有字段名 FROM 表名
#举例
SELECT department_id,department_name,manager_id,location_id FROM departments;
#方式二
#语法
SELECT * FROM 表名
#举例
SELECT * FROM departments;

4.查询常量值

#语法
SELECT 常量
#举例
SELECT 'ggg';
SELECT 100;

5.查询表达式

#语法
SELECT 表达式
#举例
SELECT 3+5;
SELECT 9*8;

6.查询函数

#语法
SELECT 函数名
#举例
SELECT VERSION();
# VERSION()d的作用是获取版本信息

7.取别名

取别名的好处：
①便于理解
②如果要查询的字段有重名的情况，使用别名可以区分开来
不取别名的情况：

SELECT first_name  FROM employees

取别名的情况

SELECT first_name AS 姓名 FROM employees

取别名的方式：
在需要取别名的字段，常量，表达式等后面直接用空格隔开加别名，或者加上as 别名

#举例
SELECT first_name AS 姓名 FROM employees
#或者
SELECT first_name  姓名 FROM employees

8.去重

当某一个字段的值有重复的时候，例如想获取某一个表中所有学生的年龄情况，18岁 的就不用全部显示，只显示一个18即可。

# 去重关键字   DISTINCT
# 举例
select distinct old as 年龄 from student

9.+ 的作用

在Java中 + 有两种作用 1 作为运算符，2.做字符串的拼接操作
在SQL语法中 + 只作为运算符。

select 90+80
#两边都是数值类型的数据，做数学加法运算 ,结果为170。
select '156'+15
#当一边为字符型时，系统会试图把字符型强转为数值型，如果转换成功，就会执行+操作,结果为171
select 'cat'+45
select  'www'+'www'
#当一边的字符型数据转为数值型数据不成功时，就会把字符型默认转成0，在去做算数运算，第一个结果为45，第二个结果为0。
select null+45
select null+'qqq'
#当一边的值为null时，结果都为null

SQL中的字符串拼接

concat（）方法，参数为要拼接的两个或者若干个字符串

select concat('aaa','bbb')
# 结果为aaabbb
select concat('aaa','bbb','ccc')
# 结果为aaabbbccc

条件查询

条件查询 连接条件控制语句的关键字是where

通用语法

select 字段 from 表名 where 筛选的条件

按条件表达式筛选

常用的比较运算符：
<,>,=,<=,>=,!=,<>
!= 和<>都是不等于的意思

#举例
#查询学生表中，年龄小于12岁的学生姓名
select 姓名 from 学生表 where 年龄<12

按逻辑表达式

SQL 中的逻辑运算符 and or not 对应Java中的&& || ！（与 或 非）

#举例
#查询学生表中，年龄小于16岁大于12的学生姓名
select 姓名 from 学生表 where 年龄>12 and 年龄<16
#查询学生表中，年龄小于12岁大于16的学生姓名
select 姓名 from 学生表 where 年龄<12 or 年龄>16
#查询学生表中，年龄不等于12岁的学生姓名
select 姓名 from 学生表 where not 年龄=12

模糊查询

模糊查询的关键字：
like：like通常的作用是用来筛选某个字段里面含有某个字符串，通常与字符通配符一起使用。
字符通配符：% 代表任意多个字符，包括0个字符
_ 代表一个字符
特例：如果某字符串中原有的字符为’%’,’_'与通配符冲突的情况下，用一个关键字 escape 来区分通配符还是关键字 例子如下

'%a%'   #表示含有a 的字符串
'a%'    #表示以字符串a 开头的字符串
'_a%'   #表示第二个字母为a的字符串
'%a_'   #表示倒数第二个为a的字符串
 '_&_%' escape'&' #表示第二个字符为下划线的字符串，&符号后面的下划线不是通配符，用escape'&' 来解释，&这个符号可以是其他符号

between and：between A and B ，表示在A和B之间的值，包括A和B ， A和B之间的位置，A小于B。
in：含义：判断某字段的值是否属于in列表中的某一项
特点：
①使用in提高语句简洁度
②in列表的值类型必须一致或兼容
③in列表中不支持通配符
is null：判断为空
is not null：判断不为空

使用like 模糊查询

#查询学生表中姓为张的学生所有信息
select * from 学生表 where name like '张%'

使用 between and 模糊查询

# 查询学生表中成绩在70到90之间的学生学号
select 学号 from 学生表 where 成绩 between 70 and 90

使用 in 模糊查询

#查询学生表中成绩为70，80，90的学生所有信息。
select * from 学生表 where 成绩 in (70,80,90)
#用逻辑表达式筛选 ，与 in 的效果一样，只不过看起来更简洁
select * from 学生表 where 成绩=70 or 成绩=80 or 成绩=90

使用 （is null 或者 is not null）模糊查询

使用场景:假如学生表里面有奖学金这样一个字段，奖学金不是每个学生都有的，没有奖学金的学生，在奖学金的字段里面就为null.

# 查询没有奖学金的学生学号
select 学号 from 学生表 where 奖学金 is null
#查询有奖学金的学生年龄
select 年龄 from 学生表 where 奖学金 is not null

排序查询

排序查询的关键字：
order by :后面跟需要排序的字段或者表达式
asc ：表示升序 可以不写
desc：表示降序
排序查询的语法:

select 字段名
from 表名
where 筛选条件    #依据实际情况可有可无
order by 需要排序的字段或者表达式  asc 或者desc 按升序或者降序
#查询学生表中所有学生信息，成绩降序排序
select * from 学生表 order by 成绩 desc

按单个字段排序

#查询员工表中所有员工信息，年龄从小到大排序
select * from 员工表 order by 年龄 asc

添加筛选条件在排序

#查询员工表的年龄大于22岁的所有员工信息，按工资降序排序
select * from where 年龄>22 order by 工资 desc

按表达式排序

#查询员工表所有信息，按年薪的升序排序
select * from 员工表 order by 工资*12 asc

按别名排序

查询学生表的学生年龄，按年龄从小到大排序
select old as 年龄 from 学生表 order by 年龄 asc

按函数排序

学习函数排序先了解一个函数，会以这个函数为例
length（）作用是返回字符串的字节个数，一个字母为一个字节，汉字用utf-8编码一个汉字代表3个字节,用gbk编码为2个字节
length（‘asdf’）的结果为4

#按姓名的长度排序
select 姓名 from 学生表 order by length(姓名) asc

按多个字段排序

按多个字段排序就是有多个排序条件，排序条件之间有主次

#查询所有学生信息，先按年龄从小到大的排序，在按成绩降序排序
select * from 学生表 order by 年龄 asc,成绩 desc
#   原理：先按年龄排序，如果年龄相同，相同年龄的再按成绩排序

数据库的函数

数据库的函数类似与Java中的方法
函数分类分为：
1.单行函数
2.分组函数

单行函数

单行函数大致分为：字符函数，日期函数，数学函数，控制函数，其他函数

字符函数

length（）
作用：获取字符串的字节长度，前面函数排序提到过。

length('qwerasdf')   #结果为8

concat（）
作用：字符串的拼接，前面讲过字符串的拼接

concat('a','a')   #结果为aa

upper()和lower()
作用：

• upper（）将小写转换为大写

• lower（）将大写转换为小写

upper(abc)   #结果为ABC
lower(ABC)   #结果为abc

substr（）substring（）
SQL中字符串索引从1开始
作用：1.截取从指定索引处开始到结束所有字符，2.截取从指定索引处开始到指定长度的字符

substr('asdfghjkl',5)  # 结果为ghjkl
substring('asdfghjkl',5)  # 结果为ghjkl
substr('asdfghjkl',5,2)  # 结果为gh
substring('asdfghjkl',5,2)  # 结果为gh

instr()
作用：返回子串第一次出现的索引，如果找不到返回0

instr('123456789',5)    # 结果为5
instr('123456789',55)   # 结果为0

trim
作用：去除字符串中头尾的空格，字符串中间的空格不会去除，或去除字符串中的指定字符串，也只能去除头尾

trim('   asdd   ')   # 结果为asdd
trim('a'from'abcabcabca')   # 去除字符串中的头字符‘a’结果为bcabcabc

replace
作用： 替换

replace('aaabbbccc','a','d')#把字符串中的a替换成d，结果为dddbbbccc

数学函数

round 四舍五入

round(1.567)   #  结果2
round(1.567,2)  #保留两位小数   结果1.57

ceil 向上取整

ceil(1.4)  #结果2
ceil(-1.9)  #结果-1

floor 向下取整

floor(1.95)     # 结果1
floor(-1.98)    # 结果-2

mod 取余

mod(10,3)#10对3取余  结果1

时间函数

now()
获取系统当前时间，日期+具体时间
curdate()
获取日期，不包括具体时间
curtime()
获取时间，不包括日期
year()
获取指定的年份

year(now()) #获取现在的年份
year(2021-8-9)# 只获取年份

month()
参考year(),举一反三，天数，小时数，分钟数，秒数
str_to_date()
将字符转换成日期
date_format()
将日期转换成字符

流程控制函数

if

if(10<5,'好的','不错')   # 结果为 不错

与三元运算符相似

case
case的用法一：
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end

与Java中switch case的效果差不多

#查询员工的年龄和工资，年龄为21，22，23的员工工资分别涨200，300，400
select 年龄,工资
case 年龄
when 21 then 工资+200
when 22 then 工资+300
when 23 then 工资+400
else 工资
end
from 员工表

case 的用法二
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end

类似与多重if语句

查询学生成绩，大于90分的成绩为A,大于80分的成绩为B,大于70分的成绩为C,其余+的成绩为D,
SELECT 成绩,
CASE 
WHEN 成绩>90 THEN 'A'
WHEN 成绩>80 THEN 'B'
WHEN 成绩>70 THEN 'C'
ELSE 'D'
END AS 成绩等级
FROM 学生表;

分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：sum 求和、avg 平均值、max 最大值 、min 最小值 、count 计算个数
这些函数的使用比较简单，不做详细介绍。
特点：
1、sum、avg一般用于处理数值型
max、min、count可以处理任何类型
2、以上分组函数都忽略null值

3、可以和distinct搭配实现去重的运算

4、count函数的单独介绍
一般使用count(*)用作统计行数

5、和分组函数一同查询的字段要求是group by后的字段

分组查询

分组的关键字：group by
语法：
select 要查询的字段
from 表名
where 筛选条件
group by 分组的字段
order by 排序的字段

筛选分为两类：分组前筛选和分组后筛选

最简单的分组查询：

# 查询每个班级学生成绩的平均成绩，最高分，最低分，所有学生成绩的总分,和每个班级的学人数
select avg(成绩) as 平均分,max(成绩) as 最高分,min(成绩) as 最低分,sun(成绩) as 总分,count(*) as 总人数,班级名
from 学生表
group by 班级名

实现分组前的筛选的分组查询：

# 查询每个班级男学生成绩的平均成绩，最高分，最低分，总分,和每个班级的男学人数
select avg(成绩) as 平均分,max(成绩) as 最高分,min(成绩) as 最低分,sun(成绩) as 总分,count(*) as 总人数,班级名
from 学生表
where 性别='男'
group by 班级名

实现分组后筛选的分组查询：
即是对分组就后的列表再次筛选
用having 作为筛选条件的关键字

# 在前面简单分组查询的基础上在添加一个条件，平均分大于70的班级
select avg(成绩) as 平均分,max(成绩) as 最高分,min(成绩) as 最低分,sun(成绩) as 总分,count(*) as 总人数,班级名
from 学生表
group by 班级名
having avg(成绩)>70

也可以队最终的结果加上 order by 排序

select avg(成绩) as 平均分,max(成绩) as 最高分,min(成绩) as 最低分,sun(成绩) as 总分,count(*) as 总人数,班级名
from 学生表
group by 班级名
having avg(成绩)>70
order by max(成绩) #按成绩排序

连接查询

定义：
又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：
表1 有m行，表2有n行，结果=m*n行
发生笛卡尔积原因：
没有有效的连接条件
连接查询的分类：
一.内连接：
等值连接
非等值连接
自连接
二.外连接：
左外连接
右外连接
全外连接
三.交叉连接
sql92标准：仅仅支持内连接
sql99标准：支持内连接+外连接（左外和右外）+交叉连接

sql92标准的连接查询

等值连接

有两张表：
一个学生表，里面有学号，和对应班主任的教师号
一个教师表，里面有教师号

#要查询学号对应的教师号
select 学号,教师号
from 学生表，教师表
where 学生表.教师号=教师表.教师号

为了方便和区别重名，也可以为表取别名

select 学号,教师号
from 学生表 as s，教师表 as  t
where s.教师号=t.教师号

一样可以进行分组，排序，筛选等

非等值连接

有两个表：
一个学生表，表里面有学号，和学生成绩
一个等级表，有成绩等级和最高分和最低分
及格 最低分60 最高分100
不及格 最低分0 最高分59

#查寻一个学生的学号和其对应的成绩等级
select 学号,成绩等级
from 学生表 as s, 等级表 as l
where s.学生成绩 between l.最低分 and l.最高分

自连接

有一个员工表，里面有员工号，员工名字，和上级的员工号。这里的上级员工号实际上就是另一位员工的员工号

#查询员工的名字和上级员工名字
select 1.员工名字  2.员工名字
from 员工表 as 1,员工表 as 2
where 1.上级员工号=2.员工号

自连接就是自己连接自己

sql99标准的连接查询

内连接

语法 ：
select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件：

• inner 可以省略
  99标准的等值 非等值和自连接使用场景和上面介绍的92标准一样，只是语法不一样。

外连接

应用场景：用于查询一个表中有，另一个表没有的记录

特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

交叉连接

关键字： cross join

子查询

含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面：
标量子查询（单行）
列子查询 （多行）
行子查询
exists后面（相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）

分页查询

应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size
select 查询列表
from 表
limit (page-1)*size,size;
size=10
page
1 0
2 10
3 20

联合查询

union 联合 合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
union
查询语句2
union
…
应用场景：
要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致时
特点：
1、要求多条查询语句的查询列数是一致的！
2、要求多条查询语句的查询的每一列的类型和顺序最好一致
3、union关键字默认去重，如果使用union all 可以包含重复项