目录
改变列的显示顺序:
通配符 (wildcard) : ' * ' 可以替代任何字符;
问题:显示员工信息表tb只包含销售额(sales)和员工号(empid)的信息,并按照顺序显示出来;
select sales, empid from tb;
使用别名
别名(alias):指一般称谓以外的名称;
select 列名 as 别名 from 别名
ex:
给列empid加上别名“员工号”,给列sales加上别名“销售额”,然后把表tb中的所有记录都显示出来
select empid as '员工号', sales as '销售额' from tb;
使用列值进行计算并显示
算术运算符
ex:
给表tb的列sales的值乘以10000并给该列添加别名“销售额”,然后显示所有记录;
select sales*10000 as 销售额 from tb;
ex:
命令让列a的值除以列b的值
select a/b from 表名
ex:
列a的值和列b的值相加
select a+b from 表名;
使用函数进行计算
参数:放在()中的数据称为参数;
当指定作为函数处理对象的记录时, 我们可以使用where 设置条件进行提取,也可以使用group by 对记录进行分组计算;
avg () :计算平均数
ex:
计算一下D公司2018年第二季度的平均销售额。试着显示表tb的列sales的平均值
select avg(sales) from tb;
sum():计算总和
ex:
select sum(sales) from tb;
count():统计记录个数;
ex:
select count(sales) from tb;
用于显示各种信息的函数
一个和表完全无关的函数,PI 用于返回圆周率的函数;
select pi();
显示MySQL 服务器的版本
select version();
显示当前使用的数据库
select database();
显示当前用户
select user();
显示由参数指定的字符的字符编码
select charset('这个字符');
连接字符串
concat():连接字符串;
ex:
员工信息表tb1的列empid表示员工号,列name表示姓名。下面试着显示内容为“员工号+姓名+先生”的字符串;
select concat(empid,name,'先生') from tb1;
字符串操作中常用的函数
从右取出:right 函数
ex:
命令显示列empid 最右边的两个字
select right(empid,2) from tb1;
从左取出:left函数
命令用于显示列empid最左边的两个字
select left(empid,2) from tb1;
从第x个字符开始截取字符:subsering函数
从列empid的第2个字符开始连续显示3个字符
select substring(empid,2,3) from tb1;
重复显示:repeat函数
下面的命令用于重复显示字符 ' . ',其重复次数位列age的值;
select repeat('.',age) from tb1;
反转显示:reverse函数
将姓名倒过来显示;
使用reverse函数能够找到字符顺序颠倒的单词
select reverse(name) from tb1;
日期和时间函数
now用于返回当前日期和时间的函数
创建表t_now。将表t_now的列a设置为INT类型,列b设置为DATETIME类型,并给列a添加自动连续编号功能,然后将当前的日期和时间保存在列b中并插入5条记录。
create table t_now (a int auto_increment primary key, b datetime);
insert into t_now(b) values (now());
设置条件进行显示
limit : 限制要显示的记录数
select 列名 from 表名 limit 显示的记录数;
ex:
让销售信息表tb仅显示3条记录;
select sales from tb limit 3;
使用where 提取记录
仅显示符合条件的记录
select 列名 from 表名 where 条件;
ex:
只显示列sales中值大于等于100的记录。
select* from tb where sales >= 100;
比较运算符
列sales的值小于50
select * from tb where sales < 50;
列month的值不等于4
select * from tb where month <> 4;
列sales的值在50到100之间(大于等于50、小于等于100)
select * from tb where saless between 50 and 100;
列sales的值不在50到200之间(小于50或大于200)
select * from tb where sales not between 50 and 200;
列month的值等于5或者6
select * from tb where month in (5,6);
使用字符串为条件
检索empid = 'A101' 值;
select * from tb where empid = 'A101';
like:模糊查询
select * from tb where empid like 'A1001';
like 可以把包含某字符串的所有内容当成查询对象,需要使用通配符;
字符串的通配符及其使用示例
ex:
提取 ‘最后是1’为条件的记录
# '%1' 表示任意字符串 +1
select * from tb where empid like '%1';
提取包含字符 ‘川’的记录
select * from tb1 where name like '%川%';
只要包含川的字符都会被提取,
'川%' 进行前方一致检索;
'%川 进行后方一致检索;
' %川% ' 进行部分一致检索;
not like : 提取不包含指定自负串记录
select * from tb1 where name not like '佐%'
使用null作为条件
仅向姓名列name中输入数据,其他列中就会输入null
insert into tb1H(name) value('仅仅是姓名')
提取表tb1H中列age为null的记录
select * from tb1 where age is null
提取表tb1H中列age不为NULL的记录
select * from tb1H where age is not null;
使用distinct 删除重复的记录(换句话说 去重)
select distinct empid from tb;
指定多个条件进行选择
使用and :将多个条件连接起来;
select * from tb where sales > 50 and sales <= 100;
ex:
提取员工号为A101的员工4月份的销售额
select* from tb where empid like '%1' and month = 4;
使用 OR
显示表tb中列sales的值小于50或者大于200的记录
select * from tb where sales < 50 or sales >200;
使用多个and 或or
员工号为A101的员工4月份的销售额”的基础上添加“销售额大于等于200万元”的记录
select * from tb where empid like '%1' and month = 4 or sales >= 200;
and 和or 混合使用规则:
- 当and和or混合使用时候,会优先处理and
如何优先处理or
例子
select * from tb where (sale>=200 or empid like '%1') and month = 4;
使用case when
根据条件改变并显示值
case
when 条件1 then显示的值
when 条件2 then显示的值
when 条件3 then显示的值
...
else 不满足所有条件时的值
end
ex:当销售额(sales)大于等于100时为“高”,大于等于50小于100时为“中等”,否则为“低”
select
case
when sales>100 then '高'
when sales>50 then '中等'
else '低'
end
from tb;
显示empid,sales,评价
select empid,sales,
case
when sales>100 then '高'
when sales>50 then '中等'
else '低'
end as 评价
from tb;
排序
order by 按升序排序并显示
select 列名 from 表名 order by 作为键的列;
ex:
select * from tb order by sales;
ASC :明确指定按升序排列
select * from tb order by sales ASC;
desc:按降序排序并显示
select * from tb order by sales desc limit 5;
offset :指定记录的显示范围
select 列名 from 表名 limit 显示的记录数 offset 开始显示记录的移位数;
设置offset 3,表示‘从第1条记录开始移动3位后,从第4条记录开始显示‘;
select * from tb order by sales desc limit 2 offset 3;
⚠️:(
-
LIMIT X, Y表示:跳过X条数据,读取Y条数据。
-
LIMIT Y OFFSET X表示:跳过X条数据,读取Y条数据。
)
分组显示
分组时需要使用group by命令:
select 列名 from 表名 group by 用于分组的列名;
例子:
命令用于显示按照列empid分组后的记录
select * from tb group by empid;
使用count():计算记录的数量,并进行group by 分组
select count(*) from tb group by empid;
select
empid, count(*) as 个数
from tb
group by empid;
显示各组的总和以及平均值
select empid, sum(sales) as 合计 from tb
group by empid;
按empid分组并计算每组销售额平均值的示例。
select empid, avg(sales) from tb group by empid;
设置条件分组显示
使用having为分组的结果值设置提取条件
格式:分组并设置记录的提取条件
select 统计列 from 表名 group by 分组列 having 条件;
ex:
设置“按员工号分组计算总销售额,但仅显示小组总销售额大于等于200万元的记录”的条件
select empid, sum(sales) from tb group by empid
having sum(sales)>=200;
提取记录后分组
ex:
仅提取销售额(sales)大于等于50万元的交易记录,并以该记录为对象计算各员工的平均销售额。
select empid, avg(sales) from tb where sales>= 50 group by empid;
分组后排序
ex:
试着将表tb各员工的平均销售额(AVG(sales))按从高到低的顺序显示
select empid, avg(sales) from tb group by empid order by avg(sales) desc;
分组方法总结:
- 提取记录后分组
- 需使用where设置条件并提取记录,然后通过group by进行分组;
- 分组后提取记录
- 需要先用group by进行分组,然后使用having提取记录;
ex:
以销售额大于等于50万元的数据为对象,按照员工号(empid)分组,计算各员工的平均销售额(AVG(sales)),然后按照降序显示。
select empid, avg(sales) from tb
where sales >50
group by empid
order by avg(sales)
desc;
练习题:
问题一:
请在表tb的列sales的总和前后加上字符串“合计”和“万元”并显示出来。另外,在显示的项目名上添加别名“销售额”。
select concat('合计: ', sum(sales), '万元') as 销售额 from tb;
问题二:
在下面的表tb中,以列sales大于等于50的数据为对象,按照列empid分组,并降序显示各组销售额平均值大于等于120的记录。
select empid, avg(sales) from tb
where sales >= 50
group by empid
having avg(sales) >= 120
order by avg(sales) desc;
where 和having区别
使用GROUP BY、ORDER BY和HAVING的SELECT语句通常会按照如下方式描述。“~”部分是可选的。
SELECT ~ FROM ~ WHERE ~ GROUP BY ~ HAVING ~ ORDER BY ~
但是,实际的执行顺序却是下面这样的
FROM ~→ WHERE ~→ GROUP BY ~→ HAVING ~→ SELECT ~→ ORDER BY ~
也就是说,在通过GROUP BY分组之前会先执行WHERE,而HAVING执行的对象是GROUP BY分组后的结果。另外,可以看到ORDER BY重新排列了SELECT的结果。