SQL语法:
1.不区分大小写。
2.不区分是字符还是字符串,统一用单引号,或者双引号。
3.每句代码的结尾分号可有可无。
4.单行注释用 #
5.sql中索引从1开始
DQL语言
- 基础查询
- 1.查询表中单个字段
- 2.查询表中多个字段
- 3.查询表中所有字段
- 4.查询常量值
- 5.查询表达式
- 6.查询函数
- 7.取别名
- 8.去重
- 9.+ 的作用
- SQL中的字符串拼接
- 条件查询
- 通用语法
- 按条件表达式筛选
- 按逻辑表达式
- 模糊查询
- 使用like 模糊查询
- 使用 between and 模糊查询
- 使用 in 模糊查询
- 使用 (is null 或者 is not null)模糊查询
- 排序查询
- 按单个字段排序
- 添加筛选条件在排序
- 按表达式排序
- 按别名排序
- 按函数排序
- 按多个字段排序
- 数据库的函数
- 单行函数
- 字符函数
- 数学函数
- 时间函数
- 流程控制函数
- 分组函数
- 分组查询
- 连接查询
- sql92标准的连接查询
- 等值连接
- 非等值连接
- 自连接
- sql99标准的连接查询
- 内连接
- 外连接
- 交叉连接
- 子查询
- 分页查询
- 联合查询
基础查询
语法:
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 排序的字段
筛选分为两类:分组前筛选和分组后筛选
分组前筛选 | 分组后筛选 |
---|---|
针对原始表筛选 | 针对分组后的表筛选 |
位置在group by 之前 | 位置在group by 之后 |
用关键字 where | 用关键字having |
最简单的分组查询:
# 查询每个班级学生成绩的平均成绩,最高分,最低分,所有学生成绩的总分,和每个班级的学人数
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 可以包含重复项