数据库学习总结（四）——条件查询及常用函数

1.简单查询

select语句的基本语法格式

select [distinct] *|字段名1，字段名2…
         from 表名
         [where 条件表达式1]
         [group by 字段名 [having 条件表达式2]]
         [order by 字段名 [ASC|DESC]]
         [limit [offset] 记录数]

[ ] 是可选的，|是或者关系，是两选一的，互斥的
解释：
（1）distinct 剔除查询结果中重复的数据
（2）” * “通配符，表示表中所有的字段
（3）group by 用于将查询结果按照指定字段进行分组 having 用于对分组后的结果进行过滤，都是可选参数
（4）order by 将查询结果按照指定字段进行分组， ASC表示升序 DESC表示降序，如果不指定升序降序，则默认为升序
（5）limit用于限制查询结果的数量，”offset“表示偏移量
偏移量为0，则从查询结果第一条记录开始
偏移量为1，则从查询结果的第二天记录开始
”记录数“表示返回查询记录的条数

1.1查询所有字段

select *
from 表名；

” * “通配符来代替所有的字段名

1.2查询指定字段

select 字段名1,字段名2,…
from 表名；

例题：列出XSB表中所有记录的XM、XB和ZY列。
在查询编辑器里面输入：

select xm,xb,zy  
form xsb；

select 后面跟查询的字段，也就是结果要显示出来的字段

2.按条件查找

2.1 带关系运算符的查询

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

where子句中可以使用关系运算符连接操作数作为查询条件对数据进行过滤
例题：显示CJB表中成绩在60到80之间的所有记录

select * form cjb where cj>=60 and cj<=80;

用and连接查询条件 ，必须满足两边的要求才会被查询，是并且关系

显示CJB表中成绩为85，86或88的记录。

select *
from cjb 
where cj=85 or cj=86 or cj=88；

用or连接查询条件，只需要满足其中的一个条件就可以被查找出，是或者关系
显示XSB表中“计算机”专业或性别为“女”的同学记录。

select * 
from xsb 
where zy='计算机' or xb='女'； 

字段要加单引号

2.2 带in关键字的查询

in关键字用于判断某个字段值是否在指定集合中，满足其中一个元素即可被查询出来

select *|字段名1，字段名2…
from 表名
where 字段名 [not] in (元素1，元素2…)

not为可选字段，使用not表示查询不在in关键字制定集合范围中的记录

例题：显示CJB表中成绩为85，86或88的记录。

    select *
    from cjb 
    where cj in（85,86,88）；

2.3 带between and 关键字的查询

between and 指定的是字段范围，在指定范围内，则满足条件，该字段可被查询出来，否则，不会被查询出来

select *|字段名1，字段名2…
from 表名
where 字段名 [not] between 值1 and 值2；

值1 表示范围条件的起始值，值2 表示范围条件的结束值

例题：显示CJB表中成绩在60到80之间的所有记录

select *
from cjb 
where cj between 60 and 80;

2.4 空值查询

空值不同于0，也不同于空字符串，空值为NULL

select *|字段名1，字段名2…
from 表名
where 字段名 is [not] null; 

2.5 带DISTINCT关键字的查询

distinct 去除数据存在重复的值

select  distinct 字段名
from 表名；

字段名表示要过滤重复记录的字段
例题：显示所有的专业名称即不重复的专业列

SELECT DISTINCT zy 
from xsb; 

distinct 后跟多个字段名时，多个字段值都相同才会被认作是重复记录

2.6 带like关键字的查询

like 用于模糊查找
”%“ 匹配任意长度的字符 例：”c%“ 可表示”ca“ “cap” “current”
“_” 匹配单个字符，若要匹配多个字符，需要打多个下划线通配符
例“c__" 可表示”cat“ “cod”

select *|字段名1，字段名2…
from 表名
where 字段名 [not]  like '匹配字符串'；

例题：显示xsb中xm为”刘某某“的全部信息

select * from xsb where xm like '刘__'；

2.7 带and关键字的多条件查询

对条件进行and连接时，必须满足所有条件记录才会被返回

select *|字段名1，字段名2…
from 表名
where 条件表达式1 and 条件表达式2…；

2.7 带or关键字的多条件查询

使用or关键字连接多个查询条件时，只要记录满足任意一个条件就会被查询出来

select *|字段名1，字段名2…
from 表名
where 条件表达式1 or 条件表达式2…；

and与or也可一起使用，and优先级高于or，因此当两者一起使用时，应该先运算and两边的条件表达式，再运算or两边的条件表达式

3.高级查询

3.1 聚合函数

聚合函数用于对一组值进行统计，并返回唯一值

count 返回某列的行数
sum 返回某列值得和
avg 返回某列的平均值
max 返回某列的最大值
min 返回某列的最小值

count函数

用来统计记录的条数

selec count(*)
from 表名;

例题：显示“计算机”专业的学生人数。

select count(*) from xsb where zy='计算机';

sum函数

用于求出表中某个字段所有值的总和

select sum(字段名)
from 表名;

avg函数

用于求出某个字段所有值的平均值

select avg(字段名)
from 表名;

max函数

用于求出某个字段的最大值

select max(字段名)
from 表名;

min函数

用于求出某个字段的最小值

select min(字段名)
from 表名;

3.2 对查询结果排序

select 字段名1，字段名2，…
from 表名
order by 字段名1 [ASC|DESC],字段名2 [ASC|DESC]…;

先按照字段名1升序或降序，当字段名1的返回值相同时，再按照字段2进行排序
ASC 升序 DESC 降序
默认为升序
例题：查询student表中的所有记录，按照gender字段的升序和grade字段的降序进行排列

select *
from student
order by gender asc,grade desc;

3.3分组查询

分组查询使用group by语句，一般与聚合函数一起使用
根据group by后的字段名进行分组，地段名相同的为一组，只会返回出一条记录，如果没有使用聚合函数（即有多条返回值时），该字段显示的是分组后的第一条记录的值，这样的查询结果意义不大

select 字段名1,字段名2
from 表名
group by 字段名1,字段名2，…[having 条件表达式]；

在分组前的筛选用where ，不可以跟聚合函数
分组后的筛选用having，对分组后的结果进行过滤，可以跟聚合函数

例题：请统计各专业姓王的男女生人数,只显示人数多于2人的

select zy,xb,count(xh)
from xsb
where xm like '王%'
group by zy,xb，
having count(*)>=2;

如果有group by，则select 后只能放分组相关的列及统计函数，否则会出错或无意义；
如果有统计函数，且统计函数值可能多于一个，此时必须用group by

例题：显示最低分大于70，最高分小于90 的XH。

select xh
from cjb
group by xh
having max(cj)<=90 and min(cj)>=70;

3.4 使用limit限制查询结果的数量

limit 可以指定查询结果从哪一条记录开始以及一共查询多少条信息
offset“表示偏移量
偏移量为0，则从查询结果第一条记录开始
偏移量为1，则从查询结果的第二天记录开始
”记录数“表示返回查询记录的条数

select 字段名1，字段名2…
from 表名
limit [offset,] 记录数

例题1：查询student表中的前四条记录

select * 
from student
limit 4

offset 可省略，默认为从第一条记录开始，一共返回4条记录

例题2：查询student表中的第5条到第8条记录

select * 
from student
limit 4,4

偏移量为4，即从第5条记录开始查询，一共返回4条记录

3.5 函数

数学函数

函数名称	作用
ABS(X)	返回x的绝对值
SQRT(X)	返回x的非负2次方根
MOD(x, y)	返回x被y除后的余数
ROUND(X, y)	对x进行四舍五入操作小数点后保留y位
CEILING(x)	返回不小于x的最小整数
FLOOR(x)	返回不大于x的最大整数
TRUNCATE(X, y)	含去x中小数点y位后面的数
SIGN(x)	返回x的符号， -1、 0或者1

字符串函数

函数名称	作用
LENGTH(str)	返回字符串str的长度
CONCAT(SI, s2, … )	返回一个或者多个字符串连接产生的新的字符串
TRIM(str)	删除字符串两侧的空格
REPLACE(str,s1,s2)	使用字符串s2替换字符串str 中所有的字符串sl
SUBSTRING(str.n,len)	返回字符串str的子串，起始位置为n,长度为len
REVERSE(str)	返回字符串反转后的结果
LOCATE(sl,str)	返回子串s1在字符串str中的起始位置

日期和时间函数

函数名称	作用
CURDATE()	获取系统当前日期
CURTIME()	获取系统当前时间
SYSDATE()	获取当前系统日期和时间
TIME_TO_SEC()	返回将时间转换成秒的结果
ADDDATE()	执行日期的加运算
SBUDATE()	执行日期的减运算
DATE FORMAT()	格式化输出日期和时间值

条件判断函数

函数名称	作用
IF(expr,v1,v2)	如果expr表达式为true返回v1,否则返回v2
IFNULL(v1,v2)	如果v1不为NULL返回v1,否则返回v2
CASE expr WHEN v1 THEN rl[WHEN v2 THEN r2…] [ELSE rn] END	如果 expr值等于vl、v2等，则返回对应位置then后面的结果，否则返回ELSE后的结果rn

加密函数

函数名称	作用
MD5(str)	对字符串str进行MD5加密
ENCODE(str,pwd_ str)	使用pwd作为密码加密字符串str
DECODE(str,pwd_ str)	使用pwd作为密码解密字符串str

4 为表和字段名取别名

在查询数据时，可以为表和字段取别名，这个别名可以代替其指定的表和字段

4.1为表取别名

select * 
from 表名
[AS] 别名；

as可以省略不写

4.2为字段取别名

select 字段名 [AS] 别名
from 表名

AS可以省略不写，所以在select后看到字段名后跟了其他字母其实就是给它取别名啦~（另一个字段名开始之前）