了解MySQL高级语句

#了解MySQL高级语句

首先准备表

mysql -uroot -p
show databases;

create database school;
use school;
create table info(id int(10) primary key not null auto_increment,name varchar(20) not null,score decimal(5,2),address char(40),hobby varchar(10));

insert into info values(1,‘liuyi’,80,‘beijing’,‘2’);
insert into info values(2,‘wangwu’,90,‘shenzheng’,‘2’);
insert into info values(3,‘lisi’,60,‘shanghai’,‘4’);
insert into info values(4,‘tinqi’,99,‘nanjing’,‘5’);
insert into info values(5,‘jiaoshou’,100,‘laowo’,‘3’);
insert into info values(6,‘xiaoming’,10,‘zhenjiang’,‘3’);

##一、常用查询语句
对MySQL数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。

使用select语句可以将需要的数据从MySQL数据库中查询出来，如果对查询的结果进行排序，可以使用order by 语句来完成排序，并最终将排序后的结果返回给用户，这个语句的排序不光可以针对某个字段，也可以针对多个字段

1.ORDER BY 语句
SELECT 字段1,字段2 FROM 表名 [WHERE 条件] ORDER BY 字段1,字段2 ASC|DESC;
ASC|DESC
ASC： 是按照升序进行排序的，默认的排序方式，即ASC可以省略。select 语句中没有指定具体的排序方式，则按照默认ASC方式进行排序
DESC： 是按照降序方式进行排列，order by 前面也可以使用 where 子句对查询结果进一步过滤。
（1）单字段排序
//按hobby字段排序默认不指定则为升序排列asc
例：select id,name,hobby from info order by hobby desc;

//按降序排列，使用DESC
例：select id,name,hobby from info order by hobby desc;

（2）多字段排序

order by 之后的参数，使用“，”分隔，优先级是按照先后顺序而定，

select id,name,hobby from info order by hobby desc,id desc;

小结：order by 之后的第一个参数只有在出现相同的数值，第二字段才有意义

2.or/and （或/且）

select * from info where score > 70 and score <=90;

select * from info where score > 70 or score <=90

3.嵌套/多条件

select * from info where score > 70 or (score >75 and score <90);

4.distinct查询不重复记录

select distinct 字段 from 表名;
例：
select distinct hobby from info;

思考题:该语句是否能执行

select name.hobby from info where hobby in (select distinct hobby from info);
//无法执行
1.distinct必须放在最开头
2.distinct只能使用需要去重的字段进行操作，无法传递值
3.distinct去重多个字段时，几个字段同时重复才能被过滤
4.先查询in内的子查询

5.IN显示已知的值的资料
语法：SELECT “字段” FROM “表名” WHERE 字段" IN (‘值1’,‘值2’, …);
例：
select * from info where address in (‘beijing’);
select * from info where address in (‘beijing’,‘shanghai’);

6.GROUP BY 对GROUP BY后面的栏位的查询结果进行汇总分组
通过SQL查询出来的结果，还可以对其进行分组，使用GROUP BY 语句来实现，GROUP BY
通常都是结合聚合函数一起使用，常用的聚合函数包括：计数（count）、求和（sum）、求平均数（avg）、最大值（max）、最小值（min）、group by 分组的时候可以按一个或多个字段对结果进行分组处理
语法：

select 字段,聚合函数 from 表名(where 字段名(匹配)数值) group by 字段名;
例：
分组排序 select count(id),hobby from info group by hobby;
//对表中hobby相同的id进行数量统计，并按照相同hobby进行分组

select count(id),hobby from info group by hobby order by count(id) desc;
//结合order by 把统计的id数量进行按升序排列

7.函数
（1）数学函数
函数 解释
abs(x) 返回 x 的绝对值
rand() 返回 0 到 1 的随机数
mod(x,y) 返回 x 除以 y 以后的余数
power(x,y) 返回 x 的 y 次方
round(x) 返回离 x 最近的整数
round(x,y) 保留 x 的 y 位小数四舍五入后的值
sqrt(x) 返回 x 的平方根
truncate(x,y) 返回数字 x 截断为 y 位小数的值
ceil(x) 返回大于或等于 x 的最小整数
floor(x) 返回小于或等于 x 的最大整数
greatest(x1,x2…) 返回集合中最大的值比较中不能有字母
least(x1,x2…) 返回集合中最小的值不能有字母

SELECT abs(-1), rand(), mod(5,3), power(2,3), round(1.89);
SELECT round(1.8937,3), truncate(1.235,2), ceil(5.2), floor(2.1), least(1.89,3,6.1,2.1)

（2）聚合函数

函数	解释
avg()	返回指定列的平均值
count()	返回指定列中非 NULL 值的个数
min()	返回指定列的最小值
max()	返回指定列的最大值
sum(x)	返回指定列的所有值之和

//count(*)包括了所有的列的行数，在统计结果的时候，不会忽略值为NULL
//count(列名)只包括列名那一列的行数，在统计结果的时候，会忽略列值为NULL的行
例:
select avg(score) from info;

select count(name) from info;
select count(address) from info;

select max(score) from info;
select min(score) from info;

select sum(score) from info;

（3）字符串函数
函数 解释
trim() 返回去除指定格式的值
concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串
substr(x,y) 获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同
substr(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串
replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y
upper(x) 将字符串 x 的所有字母变成大写字母
lower(x) 将字符串 x 的所有字母变成小写字母
left(x,y) 返回字符串 x 的前 y 个字符
right(x,y) 返回字符串 x 的后 y 个字符
repeat(x,y) 将字符串 x 重复 y 次
space(x) 返回 x 个空格
strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1
reverse(x) 将字符串 x 反转
length 返回字符长度length(3)规定字符长度为3个

（4）日期时间函数
日期时间函数 描述
curdate() 返回当前时间的年月日
curtime() 返回当前市价你的时分秒
now()