由于sql语句不区分大小写，为了书写方便，本文所有命令统一使用小写
往期内容
目录
一、数据处理函数
concat（字段名1，字段名2.....) #字符串的拼接
#将教师的工号与姓名进行拼接 mysql> select concat(teno,tename) as noname from t_teacher; +----------+ | noname | +----------+ | 9801点赞 | | 9802关注 | | 9803收藏 | | 9804订阅 | +----------+ 4 rows in set (0.00 sec)
length（字段名） #取长度
#查询教师英文名字的长度 mysql> select tename,length(Englishname) as namelength from t_teacher; +--------+------------+ | tename | namelength | +--------+------------+ | 点赞 | 7 | | 关注 | 6 | | 收藏 | 6 | | 订阅 | 5 | +--------+------------+ 4 rows in set (0.00 sec)
综合题目
将教师的英文名字改成首字母大写，其他字母小写，并查询出来
mysql> select -> tename,concat(substr(Englishname,1,1),lower(substr(Englishname,2,length(Englishname)-1))) -> as -> Englishname -> from -> t_teacher; +--------+-------------+ | tename | Englishname | +--------+-------------+ | 点赞 | Blake | | 关注 | Turner | | 收藏 | Miller | | 订阅 | Clark | +--------+-------------+ 4 rows in set (0.01 sec)
trim (' 值') #去除括号内字符串中的空格
mysql> select * from t_teacher where tename=trim(' 点赞'); +------+--------+-------------+----------+---------+------------+ | teno | tename | Englishname | tesalary | teclass | hiredate | +------+--------+-------------+----------+---------+------------+ | 9801 | 点赞 | BLAKE | 8500.00 | 1班 | 2005-03-17 | +------+--------+-------------+----------+---------+------------+ 1 row in set (0.00 sec)
case..when..then..when..then..else..end 语句
当满足......条件时做......,当满足......条件时做......当满足......条件时做......
#1班的教师工资上调30%，2班的老师工资上调20%，其他教师下调10% mysql> select -> tename,teclass,tesalary as oldsal, -> (case teclass when '1班' then tesalary*1.3 when '2班' then tesalary*1.2 else tesalary*0.9 end) as newsalary -> from -> t_teacher; +--------+---------+----------+-----------+ | tename | teclass | oldsal | newsalary | +--------+---------+----------+-----------+ | 点赞 | 1班 | 8500.00 | 11050.00 | | 关注 | 3班 | 9300.00 | 8370.00 | | 收藏 | 2班 | 13200.00 | 15840.00 | | 订阅 | 4班 | 8800.00 | 7920.00 | +--------+---------+----------+-----------+ 4 rows in set (0.00 sec)
round（字段名，参数） 四舍五入
round（字段名，0） 保留到个位数
round（字段名，1） 保留一位小数
round（字段名，2） 保留两位小数
round（字段名，-1） 保留到个位数
round（字段名，-2） 保留到十位数
#将2班的数学成绩四舍五入保留到十位数 mysql> select -> stname,stmath as oldmath, -> round(stmath,-1) as newmath -> from -> t_student -> where -> stclass='2班'; +--------+---------+---------+ | stname | oldmath | newmath | +--------+---------+---------+ | 李四 | 43 | 40 | | 东西 | 34 | 30 | | 左右 | 88 | 90 | | 周一 | 68 | 70 | | 周四 | 94 | 90 | | 周日 | 69 | 70 | | 赵六 | 76 | 80 | | 周五 | 99 | 100 | | 冷烬 | 100 | 100 | +--------+---------+---------+ 9 rows in set (0.00 sec)
rand() #默认生成0-1的随机小数
与round组合可以生成随机整数，当数据量够大且连续时，可以用于where
#演示一下随机生成10以内的整数，不报错，但也无意义 mysql> select round(rand()*10,0) from t_teacher; +--------------------+ | round(rand()*10,0) | +--------------------+ | 5 | | 7 | | 9 | | 2 | +--------------------+ 4 rows in set (0.00 sec)
ifnull(字段名, 被当做哪个值) #若为null改成xxx，null参与运算时，结果都为null
#null表示缺考，找出并改为0 mysql> select -> stname,stmath,ifnull(stmath,0) as newmath -> from -> t_student -> where -> stmath is null; +--------+--------+---------+ | stname | stmath | newmath | +--------+--------+---------+ | 南北 | NULL | 0 | +--------+--------+---------+ 1 row in set (0.00 sec)
二、多行数据处理函数
多行数据处理函数也叫分组函数，即使用时必须先进行分组，不然会默认整张表为一个组
特点：输入为多行，输出只有一行，自动忽略null
常见的有：count计数；sum 求和；avg 平均值；max 最大值；min 最小值
注！！！！！！！
count(字段名)与count（*）的区别
前者只计算该字段名的数据不为空的个数
后者计算该行至少有一个不为空的行数
用法：都为 函数名（字段名）
不进行分组，只能计算全部的，不能计算各班的！！！
分组函数不能运用在where语句上！！！！！
mysql> select avg(stmath) from t_student; +-------------+ | avg(stmath) | +-------------+ | 76.0400 | +-------------+ 1 row in set (0.04 sec)分组函数可以一起使用
mysql> select -> sum(stmath),min(stmath),max(stmath),avg(stmath),count(stmath) -> from -> t_student; +-------------+-------------+-------------+-------------+---------------+ | sum(stmath) | min(stmath) | max(stmath) | avg(stmath) | count(stmath) | +-------------+-------------+-------------+-------------+---------------+ | 1901 | 34 | 100 | 76.0400 | 25 | +-------------+-------------+-------------+-------------+---------------+ 1 row in set (0.04 sec)
distinct 去除重复值
select distinct 字段名1，.....from 表名。。。
distinct只能放在所有字段的最前面，distinct后面如果有多个字段，表示联合去重
mysql> select distinct stclass from t_student; +---------+ | stclass | +---------+ | 1班 | | 3班 | | 2班 | +---------+ 3 rows in set (0.00 sec)mysql> select distinct stclass,stsex from t_student; +---------+-------+ | stclass | stsex | +---------+-------+ | 1班 | 男 | | 3班 | 女 | | 1班 | 女 | | 2班 | 男 | | 3班 | 男 | +---------+-------+ 5 rows in set (0.00 sec)与count()结合，统计有多少个班
mysql> select count(distinct stclass) from t_student; +-------------------------+ | count(distinct stclass) | +-------------------------+ | 3 | +-------------------------+ 1 row in set (0.00 sec)
分组语法
select 字段名 from 表名 where 条件 group by 字段名；
#计算各个班的平均分 mysql> select -> stclass,avg(stmath) -> from -> t_student -> group by -> stclass; +---------+-------------+ | stclass | avg(stmath) | +---------+-------------+ | 1班 | 77.0000 | | 3班 | 76.7143 | | 2班 | 74.5556 | +---------+-------------+ 3 rows in set (0.04 sec)注！！！！！！
group by 语句中，select 后面只能跟分组的字段和分组函数，其他都不能跟，虽然MySQL不会报错，但oracle会报错，而且这样写毫无意义。
联合分组
select 字段名 from 表名 where 条件 group by 字段名1，字段名2......；
将两个及两个以上的字段联合起来分组，即将多个字段看成一个字段！
#查询不同班级中，不同性别的平均数学成绩和数学最高分 mysql> select -> stclass,stsex,avg(stmath) as avgmath,max(stmath) as maxmath -> from -> t_student -> group by -> stclass,stsex; +---------+-------+---------+---------+ | stclass | stsex | avgmath | maxmath | +---------+-------+---------+---------+ | 1班 | 男 | 72.0000 | 91 | | 3班 | 女 | 74.8000 | 88 | | 1班 | 女 | 79.5000 | 93 | | 2班 | 男 | 74.5556 | 100 | | 3班 | 男 | 81.5000 | 89 | +---------+-------+---------+---------+ 5 rows in set (0.00 sec)上述结果正确，2班没女生是在造数据时考虑不全导致的！！！！
having 语句，效果和where一样
区别：
- 执行顺序不一样，where语句比group先执行，所以不能只有分组函数，而having可以
- 筛选对象不一样，where是对分组前的数据进行筛选，而having是对筛选后的数据进行筛选
- 执行效率不一样，where效率比having高，因为having使用之前必须先执行分组操作，即使没有写group语句，也会自动将整张表分成一个组再执行having语句，所以能用where就不要用having！！！！
例子：找出各个班的最高分，显示高于90分的班级
方法一：先分组再用having语句筛选
需要先遍历全表进行分组，再遍历全表找出最高分的
注！！！！
having语句执行顺序在select语句之后，所以要用select语句执行后的字段名
mysql> select -> stclass,max(stmath) as maxmath -> from -> t_student -> group by -> stclass -> having -> maxmath>90; +---------+---------+ | stclass | maxmath | +---------+---------+ | 1班 | 93 | | 2班 | 100 | +---------+---------+ 2 rows in set (0.00 sec)
方法二：先用where语句筛选出分数高于90分的，再用分组函数求各组的最高分
首先遍历全表筛选出高于90分的，第二次遍历时，只需要遍历第一次筛选出来的数据，效率较高！！！！
mysql> select -> stclass, -> max(stmath) as maxmath -> from -> t_student -> where -> stmath>90 -> group by -> stclass; +---------+---------+ | stclass | maxmath | +---------+---------+ | 1班 | 93 | | 2班 | 100 | +---------+---------+ 2 rows in set (0.00 sec)
limit #查询结果截取
select 字段名 from 表名 limit 开始下标，截取长度；
开始下标不写默认从第一个开始！！！
下标从0开始，limit书写顺序在最后，执行顺序也是最后！！！
#查询数学成绩第5名到第10名 mysql> select -> stname, -> stmath -> from -> t_student -> order by -> stmath desc -> limit -> 4,5; +--------+--------+ | stname | stmath | +--------+--------+ | 前后 | 93 | | 丙丁 | 91 | | 秋 | 89 | | 左右 | 88 | | 甲乙 | 88 | +--------+--------+ 5 rows in set (0.00 sec)
union 合并查询结果集
将多条查询语句的结果进行合并,效率高于or语句
注！！！！！
结果集的列数和数据类型需要相同！！！
例子：查询1班和2班的学生的班级、学号和姓名
法一：
select stclass,stno,stname from t_student where stclass='1班' or stclass='2班';
法二：
select stclass,stno,stname from t_student where stclass in ('1班','2班');
法三：
select stclass,stno,stname from t_student where stclass ='1班'
union
select stclass,stno,stname from t_student where stclass ='2班';
将查询结果放到一张新表里
create table 表名 as select.........；
#将'1班'学生的数据存入class1中 mysql> create table class1 as select * from t_student where stclass ='1班'; Query OK, 9 rows affected (0.04 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> select * from class1; +------+--------+-------+---------+--------+ | stno | stname | stsex | stclass | stmath | +------+--------+-------+---------+--------+ | 5101 | 赵钱 | 男 | 1班 | 78 | | 5103 | 上下 | 男 | 1班 | 47 | | 5104 | 钱七 | 女 | 1班 | 93 | | 5109 | 周三 | 女 | 1班 | 77 | | 5114 | 张三 | 女 | 1班 | 87 | | 5117 | 前后 | 女 | 1班 | 93 | | 5119 | 春 | 女 | 1班 | 73 | | 5121 | 冬 | 女 | 1班 | 54 | | 5123 | 丙丁 | 男 | 1班 | 91 | +------+--------+-------+---------+--------+ 9 rows in set (0.00 sec)
将查询结果插入到已经存在的表中
insert into 表名 select........;
#将2班的数据放到上面建好的class1表中 mysql> insert into class1 select * from t_student where stclass='2班'; Query OK, 9 rows affected (0.04 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> select * from class1; +------+--------+-------+---------+--------+ | stno | stname | stsex | stclass | stmath | +------+--------+-------+---------+--------+ | 5101 | 赵钱 | 男 | 1班 | 78 | | 5103 | 上下 | 男 | 1班 | 47 | | 5104 | 钱七 | 女 | 1班 | 93 | | 5109 | 周三 | 女 | 1班 | 77 | | 5114 | 张三 | 女 | 1班 | 87 | | 5117 | 前后 | 女 | 1班 | 93 | | 5119 | 春 | 女 | 1班 | 73 | | 5121 | 冬 | 女 | 1班 | 54 | | 5123 | 丙丁 | 男 | 1班 | 91 | | 5105 | 李四 | 男 | 2班 | 43 | | 5106 | 东西 | 男 | 2班 | 34 | | 5107 | 左右 | 男 | 2班 | 88 | | 5108 | 周一 | 男 | 2班 | 68 | | 5110 | 周四 | 男 | 2班 | 94 | | 5111 | 周日 | 男 | 2班 | 69 | | 5113 | 赵六 | 男 | 2班 | 76 | | 5118 | 周五 | 男 | 2班 | 99 | | 5120 | 冷烬 | 男 | 2班 | 100 | +------+--------+-------+---------+--------+ 18 rows in set (0.00 sec)单表查询到此结束！！！！！
三、总结
查询语句书写顺序
select
... #查询的字段
from
... #来自哪张表
where
... #分组前筛选条件
group by
... #按哪个字段分组
having
... #分组后再次筛选
order by
... #排序 （asc 升序，desc 降序）limit #截取查询结果
...
执行顺序
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit
四、习题
问题描述：
找出各班男女生平均成绩，要求显示平均成绩高于73分的，2班除外，并按平均成绩降序输出。
mysql> select -> stclass, -> stsex, -> avg(stmath) as avgmath -> from -> t_student -> where -> stclass != '2班' -> group by -> stclass,stsex -> having -> avgmath>73 -> order by -> avgmath desc; +---------+-------+---------+ | stclass | stsex | avgmath | +---------+-------+---------+ | 3班 | 男 | 81.5000 | | 1班 | 女 | 79.5000 | | 3班 | 女 | 74.8000 | +---------+-------+---------+ 3 rows in set (0.00 sec)
未完待续。。。。。。。。。。
1030
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
