SQL 单表查询

说明

一些说明

本文中所有中括号扩起的文字均代表可以替换的文字(包括中括号本身)
例如在我博客中的:

select [字段1],[字段2],...,[字段n] from [表名] where [查询条件];

在实际使用时,可以写作:

select name,age from users where id=3;

表结构

users表

工号姓名性别年龄职位办公电话手机号
201215121李勇40主管1365013365245865
201215122刘晨29收银员1362212535468521
201215123王敏29会计(Null)17685324586
201215124刘柳42总监1362319658245753
201215125王辉29会计(Null)16698532547

如上users表所示,

  1. users叫做表名
  2. “工号”、“姓名”、“性别”、“年龄”和“职位”等叫做一个字段
  3. 该表的一行(例如:201215122 刘晨 女 29 收银员 13622 12535468521)叫做一个记录

1. 基本查询操作

1.1. 基本语法

表的查询,即查询表中的数据记录,表的查询操作是SQL的基本数据操作之一,也是使用频率最高、最重要的数据操作。

一个一般的查询语法如下:

select [字段1],[字段2],...,[字段n] from [表名] where [查询条件];

关键字解释:

select: 查询语句的起始关键字,表示该操作为查询操作
from: 后接数据源,表示在[表名]中查询[字段i]
where: 后接查询条件,表示要对查询做的限制,该操作可以帮助数据库使用者做精确的查找

1.2. * 查询所有数据

查询所有字段会把表内所有的数据都查询出来,在select语句中使用星号" * "通配符来查询表中的所有字段

语法为:

select * from [表名];

例如:

select * from users; --表示从users表中查询所有字段,

查询结果为:
在这里插入图片描述

1.3. as 给字段取别名

给字段取别名的意义在于可以清晰的展示某一字段所表示的内容,例如表中有一列“月工资”,要计算“年工资”时可将月工资12,这时候sql会显示该字段的字段名为“月工资12”,这对查询结果的展示很不友好,通常情况下都会给它取一个别名。

取表名的可以使用“as”关键字,或者直接在字段后面接“"[别名]"”
语法为:

select [字段名] as "[别名]", [字段名]"[别名]" from users;

例如下例:

select 工号, 工号 as "工", 姓名, 姓名"姓" from users;

在这里插入图片描述

1.4. distinct 消除重复

如果查询结果中出现重复数据,可用关键字“distinct”消除,若没有加上“distinct”关键字,查询结果将默认展示重复数据。

需要注意的是,“distinct”关键字是作用于所有字段的组合,只有在所有字段值都重复的情况下,才会消除重复。

语法如下:

select distinct [字段1],[字段2],...,[字段n] from [表名]

例如:

select 姓名,性别,年龄,职位 from users;
select distinct 姓名,性别,年龄,职位 from users;

左边是未加distinct关键字的查询结果,右边是加上distinct关键字的查询结果
在这里插入图片描述

1.4. 运算符

SQL提供的基本算术运算符有:

+ :加法
- :减法
* :乘法
/ :除法
% :取余

SQL的算术运算符可以在列、常量之间运算,例如:

select 姓名, 年龄, 年龄+2, 年龄*2, 年龄*2, 年龄/2, 年龄%2 from users;

查询结果为:
在这里插入图片描述
要注意的是,运算符也是有优先级的,优先级规则与我们的数学运算符号规则一致。

2. where 条件查询

在select语句中,可以通过where子句,对数据进行过滤,其语法格式为

select [字段1],[字段2],...,[字段n] from [表名] where [查询条件];

例如:

-- 筛选出年龄大于30的人
select 姓名, 年龄 from users where 年龄 > 30;

在这里插入图片描述

2.1. where中的比较运算符

比较运算符作用
=等于<>不等于
>大于<小于
>=大于等于<=小于等于
between [值1] and [值2]介于[值1]和[值2]之间in([值1], [值2],…, [值n])包含在[值1], [值2],…, [值n]之中
like条件匹配(%:匹配多个字符,_:匹配一个字符)[值] is null判断[值]是否为空

例如:

-- 查询年龄不等于40的人
select 姓名, 年龄 from users where 年龄 <> 40;

在这里插入图片描述

-- 查询年龄介于20到30之间的人
select 姓名, 年龄 from users where 年龄 between 20 and 30;

在这里插入图片描述

-- 匹配姓名中第一个字是‘刘’,第二个字随意的人
select 姓名 from users where 姓名 like '刘_';

在这里插入图片描述

2.2. where中的逻辑运算符

逻辑运算符作用
[表达式1] and [表达式2]两个表达式都为true,才返回true ,否则返回false
[表达式1] or [表达式2]两个表达式其中之一为true,就返回true,两个都为false,才返回false
not [表达式]若表达式为true,则返回false,若表达式为false,则返回true

例如:

-- 找出年龄大于30的男性
select 姓名, 年龄, 性别 from users where 性别='男' or 年龄>30;

在这里插入图片描述

2.3. 排序

对查询结果进行排序使用order by 关键词

语法:

select [字段1], [字段2],..., [字段n] 
	from [表名] 
	order by [字段名1] [排序方式], [字段名2], [排序方式],..., [字段名n] [排序方式];

排序方式有两种:

  1. asc 顺序排列 (从小到大)
  2. desc 逆向排列 (从大到小)

例如:

-- 先按年龄顺序排列,再将重复值按照工号逆序排列
select 工号, 姓名, 年龄 from users order by 年龄 asc, 工号 desc;

在这里插入图片描述

3. 常用函数

3.1. 字符串函数

函数功能
concat([字符串1],[字符串2],…,[字符串n])连接字符串
group_concat([字段])对分组后的字段进行连接
insert([字符串1], [下标], [个数], [字符串2])将[字符串1]中从[下标]开始的[个数]个字符替换为[字符串2]
lower([字符串])将[字符串]中的字母转换为小写
upper([字符串])将[字符串]中的字母转换为大写
length([字符串])计算[字符串]的字节长度
char_length([字符串])计算[字符串]的字符长度
lpad([字符串1], [长度], [字符串2])在[字符串1]的左边填充[字符串2],使字符串长度达到[长度]
rpad([字符串1], [长度], [字符串2])在[字符串1]的右边填充[字符串2],使字符串长度达到[长度]
trim([字符串])去掉[字符串]首尾的空格
repeat([字符串],[次数])将[字符串]重复[次数]次
replace([字符串1], [字符串2], [字符串3])用[字符串3]替换[字符串1]中所有的[字符串2]
substring([字符串], [下标], [长度])返回[字符串1]中从[下标]位置其长度为[长度]的子串

举例:

-- concat(工号,姓名)连接字段`工号`和`姓名`
select 工号, 姓名, concat(工号,姓名) from users; 

在这里插入图片描述

--lpad(姓名, 5, 'X')在姓名字段的左边填充字符'L',使其字符长度达到5
--rpad(姓名, 5, 'X')在姓名字段的右边填充字符'R',使其字符长度达到5
select lpad(姓名, 5, 'L'),rpad(姓名, 5, 'R') from users; 

在这里插入图片描述

3.2. 数值函数

函数功能
abs([数值])返回[数值]的绝对值
ceil([数值])返回不小于[数值]的最小整数值
floor([数值])返回不大于[数值]的最小整数值
mod([数值1], [数值2])返回[数值1]/[数值2]的模
rand()返回一个0~1之间的随机数
round([数值1], [数值2])返回[数值1]四舍五入后保留[数值2]位小数的值
truncate([数值1], [数值2])返回[数值1]截断后保留[数值2]位小数的值

例如:

-- 返回不小于0.8、-0.8和不大于0.8、-0.8的数值
select ceil(0.8), ceil(-0.8), floor(0.8), floor(-0.8) from users; 

在这里插入图片描述

-- round(3.14159,3)返回3.14159四舍五入后保留3位小数的值
-- truncate(3.14159,3)返回3.14159截断后保留3位小数的值
select round(3.14159,3), truncate(3.14159,3) from users; 

在这里插入图片描述

3.3. 日期和时间函数

函数功能
curdate()返回当前日期
curtime()返回当前时间
now()返回当前的日期和时间
year([日期])计算[日期]的年份
monthname([日期])计算[日期]的月份,并返回其英文名
week([日期])计算[日期]为一年中的第几周
hour([时间])计算[时间]的小时值
minute([时间])计算[时间]的分钟值
date_format([日期],[格式])将[日期]按[格式]返回
date_add([日期或时间],interval [时间间隔] [间隔类型])返回一个[日期或时间]后接一个[时间间隔]的时间值
datediff([时间1],[时间2])计算[时间1]和时间[2]之间的间隔天数

例如:

select now(), 
	   year(now()), monthname(now()), week(now()), 
       hour(now()), minute(now()); 

在这里插入图片描述

3.3.1. date_format()函数
格式描述格式描述
%M英文月名%j该日期所在一年中的第几天(000-366)
%b英文月名缩写%D该日期所在一月中的第几天(带次序1st,2nd,…,31th)
%c月数(0-12)%e该日期所在一月中的第几天(0-31)
%m月数(01-12)%d该日期所在一月中的第几天(00-31)
%k该时间所在的小时(0-23)%i该时间所在一小时中的分钟数(00-59)
%H该时间所在的小时(00-23)%f该时间所在一秒中的微秒数
%h该时间所在一天中的小时(01-12)%a该时间所在的星期名的英文缩写
%I该时间所在一天中的小时(01-12),同%h

例如:

select now(), date_format(now(), '%M %m %D'); 

在这里插入图片描述

3.3.2. date_add()函数
间隔类型描述间隔类型描述
microsecond微秒second
minute分钟hour小时
dayweek星期
monthquarter季度(一个季度三个月)
yearsecond_microsecond秒+微秒(写成浮点数形式
例如10.1表示间隔10秒加1微秒)
minute_microsecond分+微秒minute_second分+秒
hour_microsecond小时+微秒hour_second小时+秒

例如:

-- 间隔一个季度
select now(), date_add(now(), interval 1 quarter); 

在这里插入图片描述

-- 间隔1分2秒
select now(), date_add(now(), interval 1.2 minute_second); 

在这里插入图片描述

3.4. 流程控制函数

3.4.1. if 流程函数

根据表达式的真假值做出判断。
语法:

-- 如果[表达式]为真,则返回[true返回值],否则返回[false返回值]
if ([表达式], [true返回值], [false返回值]);

例如:

-- 若字段值为‘刘晨’,则返回‘是’,否则返回‘否’
select 姓名, if(姓名='刘晨', '是', '否') from users; 

在这里插入图片描述

3.4.2. ifnull 流程函数

ifnull用于判断表达式是否为空。
语法:

-- 若[表达式1]不为null,就返回[表达式1]的值,若[表达式1]为null,就返回[表达式2]的值
ifnull([表达式1], [表达式2])

例如:

-- (左图) 查询某单位人员的联系方式,有些人没有办公电话,只有手机号,这时会出现信息冗余
select 姓名, 办公电话, 手机号 from users

-- (右图) 若使用ifnull()函数来处理,可以消除这些信息冗余
-- ifnull(办公电话, 手机号) 若有办公电话,则显示办公电话,若没有办公电话,就用手机号代替
select 姓名, ifnull(办公电话, 手机号) from users

在这里插入图片描述

3.4.3. case 流程函数

case流程函数可以帮助数据库使用人员根据情况从多个选项中作出选择。

语法:

-- case表达式可以根据[表达式1]的结果[值1]、[值2]、...、[值n]
-- 返回相应的[返回值1]、[返回值2]、...、[返回值n]
-- 如果[表达式1]的结果不在[值1]、[值2]、...、[值n]中,则返回[默认返回值]
case [表达式1]
	when [1] then [返回值1]
	when [2] then [返回值2]
	...
	when [值n] then [返回值n]
	else [默认返回值]
end

例如:

-- 用case表达式对性别进行中英文转换
select 姓名, 性别,
	case 性别
	when '男' then 'male'
	when '女' then 'female'
	else 'unknown'
end
from users

在这里插入图片描述

3.5. 聚合函数

聚合函数用于对一组数进行运算,然后返回一个结果,需要注意的是,除了count()以外,其他的聚合函数在计算时会忽略null值。

常用的聚合函数有:

聚合函数作用
count([列名])计算某一列有多少行
avg([列名])计算某一列数值的平均值
sum([列名])计算某一列数值的和
max([列名])求出某一列的最大值
min([列名])求出某一列的最小值

例如:

select count(*), avg(年龄), sum(年龄), max(年龄), min(年龄) from users

在这里插入图片描述

3.6. 数据库属性函数

属性函数功能
database()返回当前数据库名
version()返回当前数据库版本
user()返回当前登录用户的用户名
inet_aton([IP地址])返回IP地址的数字表示
inet_ntoa([IP数])将IP的数字表示转换为IP地址
password([密码])返回加密后的[密码]
md5([字符串])返回[字符串]的MD5值(32位的16进制串)

例如:

-- IP地址'192.168.1.1'与其数字表示的相互转换
select inet_aton('192.168.1.1'), inet_ntoa(3232235777);

在这里插入图片描述

4. group by 分组查询

分组查询可以将值相同的字段分在同一个组,常和各种函数一起使用。关键词是group by,写在where子句之后

如果要对分组后的结果进行条件过滤,要使用having关键字,having就是group by中的where,但是having子句中可以使用聚合函数,where中不行。

语法:

select [字段],[函数1],[函数2],...,[函数n] 
	from [表名] 
	group by [字段] 
	having [表达式];

需要注意的是,select中两个的[字段]必须相同,因为对数据按[字段]进行分组以后,其他的字段都会被打乱,无法直接查询出来。

例如:

-- (右图)对分组后的姓名字段值进行连接,并统计每一个分组的年龄总和
select 年龄, group_concat(姓名), sum(年龄) from users group by 年龄;

在这里插入图片描述

-- 带having子句的分组查询
-- 首先对分组后的姓名字段值进行连接,并统计每一个分组的年龄总和
-- 然后从中选出年龄小于30的分组,返回
select 年龄, group_concat(姓名), sum(年龄) 
	from users 
	group by 年龄
	having 年龄 < 30;

在这里插入图片描述

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 撸撸猫 设计师:设计师小姐姐 返回首页