MySQL_day 2

Content-MySQL数据查询

• 条件查询（重点，难点）
• 聚合函数
• 分组与分页（重点）
• 连接查询（重点）
• 子查询（重点，难点）
• 保存查询结果

条件查询

修改与删除的时候提到过where，使用where限定语句，查询集只返回条件为True内容

如：select * from students where id>13;  #这个查询语句将会返回id大于13的数据

区间：where id between 5 and 9

比较运算符

比较运算符	描述
=	等于
>	大于
<	小于
>=	大于等于
<=	小于等于
!=或者<>	不等于

逻辑运算符

逻辑运算符	描述
and	与
or	或
not	非

模糊查询

• like 表示模糊查询
• % 表示任意多个字符
• _ 表示一个字符
• rlike 可以匹配一个正则
• in 用法匹配括号里面的，符合就返回结果集中

注意：如果需要匹配%本身。那么则需要使用%%

例：select name,hometown from students where hometown like '%州'；

匹配到所有以州为结尾的地点

例：select name,hometown from students where hometown like '_州';

匹配到所有2个字符以州结尾的地点

例：select name,hometown from students where hometown rlike '.*州';

正则匹配，任意字符只要包含州字的地点

例：select name,hometown from students where id in(3,6,13);

匹配到id是3，6，13的

between查询

• between and 表示一段区间

例：查询id 13-16：select * from students where id between 13 and 16;

null值判断

例：

先插入几条数据hometown为null值的

直接用hometown=null并不能得到结果，在mysql中null表示空。如果要查询为null值的应该是使用is：select * from students where hometown is null;

排序

• order by 字段 [desc/asc]
• desc 表示降序（从大到小排序）
• asc 默认排序规则，表示升序（从小到大排序）

例：select * from students order by id;  #按照id升序排

select * from students order by id desc;  #按照id降序排

select * from students order by id desc, hometown asc; 先按照id降序，再按照hometown升序

聚合函数 - 统计使用

主要是为了快速得到结果，经常使用的几个聚合函数

常见函数列表

• count 统计个数
• max 计算最大值
• min 计算最小值
• 数学函数
• sum 求和
• avg 求平均数
• round 函数
• 时间函数
• substr 字符串处理函数

例：

select count(*) from students;

select max(id) from students;

select min(id) from students;

数学函数

• MOD(N,M)%  取模

select mod(234,10);   #值4

• FLOOR(X)  向下取整

select floor(1.23);    #值1

• CELLING(X)  向上取整

select celling(-1.02);   #值-1 

• ROUND(X,D)  四舍五入到最近的整数   #X是小数，D保留位数

select round(-1.55);   #值-2

select round(1.58);   #值2

select round(1.298,1);   #值1.3

select round(1.298,0);    #值1

avg求平均数，计算数值型

select avg(id) from students;   #保留小数点后四位

select round(avg(id),1) from students;   #先求id平均数，再四舍五入小数点后保留一位

时间函数

• select now();  显示当前日期及时间
• curdate();  当前日期
• curtime();  当前时间

字符串函数

• mysql substr() 函数

用法：substr(string,start,length)

string字符串，start起始位置，length长度

注意：mysql中的start是从1开始的

例：select substr('abcdefg',2,5)；  #输出bcdef

• left(str,len) 函数

返回字符串str的最左面len个字符

例：select left('abcdefg',5);   #输出abcde

• right(str,len) 函数

返回字符串str的最右面len个字符

select right('abcdefg',4);    #输出defg

求一个字符的长度

select length('abcdefg');   #输出7

select length('吴老师');   #输出9  mysql中一个汉字占3个字节

小写变大写

select upper('abcdefg');   #输出ABCDEFG

大写变小写

select lower('ABCDEFG');   #输出abcdefg

拼接

select concat('wu','_','laoshi 很帅气');   #输出wu_laoshi 很帅气

分组和分页

• group by字段   #以xx字段作为分组依据分组

注意：分组后分组依据会显示在结果集，其他列不会出现

如：统计男生，女生有多少人

select gender,count(*) from students group by gender;

• as取别名

在统计人数的时候，结果集中显示的是count(*)这个有时候我们并不知道它代表的是什么，如何改成有语义的命名

select gender,count(*) as '人数' from students group by gender;

select gender as '性别',count(*) as '人数' from students group by gender;

• 分组后条件筛选

分组后不能使用where做条件过滤，需要一个使用新的having函数

*where和having的区别

• where用户from之后的条件过滤
• having用在分组之后的条件过滤，两个功能是一样的，只是作用的位置不一样

例：select gender as '性别',count(*) as '人数' from students group by gender having gender=0;

• limit 分页

分页原因：如果数据量很大的话，一次性将所有数据查询出来，不仅不方便查看而且消耗传输带宽。那么就使用到了分页功能，一次只查询一页的数据，如：

select * from students limit start,count;   #start从第几条数据开始，count表示获取几条信息

select * from students limit 0,3;   #查询前3名同学信息

连接查询

新需求：给students表中的学生分下班，新加一个class_id字段，保存班级id，之前已经有一个class表已经保存了班级信息了，如：

alter table students add class_id int default null;

id小于15分到1班，大于等于分到2班，如：

update students set class_id=1 where id<15;

update students set class_id=2 where id>=15;

mysql三种连接查询

• 内连接查询：查询的结果为两个表匹配到的数据，两个表都能匹配上的数据将返回给结果表

select * from 表1 inner join 表2 on 表1.列=表2.列;

注意：这里如果多次使用到的表名/表名太长的情况下，也可以使用as给表取别名

select b1.name,b1.hometown,b2.name from students as b1 inner join class as b2 on b1.class_id=b2.id;

select b1.name as 学生姓名,b1.hometown as 籍贯,b2.name as 班级名称 from students as b1 inner join class as b2 on b1.class_id=b2.id;

• 右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充

select * from 表1 right join 表2 on 表1.列=表2.列

select * from students as b1 right join class as b2 on b1.class_id=b2.id;

• 左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充

select * from 表1 left join 表2 on 表1.列=表2.列

select * from students as b1 left join class as b2 on b1.class_id=b2.id;

子查询

• 概念：在一个select语句中嵌入另外一个select语句，嵌入的这个select语句就是子查询语句。子查询是辅助主查询的，充当数据源，或者充当条件。子查询是一条独立的语句。即使单独拿出子查询也是可以正常执行
• 四种类型：标量子查询；列级子查询；行级子查询；表级子查询
• 标量子查询：子查询返回一行一列的数据

例：查询学生年龄小于平均年龄的学生信息

子查询语句先查出平均年龄：select avg(age) from students;

select * from students where age < (select avg(age) from students);

select * from students where age < (select round(avg(age),0) from students);

• 列级子查询：子查询返回一列多行的数据

例：查询class表中已经安排学生的班级信息

select * from class where id in (select class_id from students);

备注：可用于子查询的关键字有4个（in/all/any/some）

in: 符合列子查询里面的一个

any/some（有满足的结果才为true）: 功能相同，表示任意一个如：where 列 = any(列子查询)

all（全部满足才为true）: 等于里面所有，如：where 列 = all(列子查询)

例：查询出在学生表当中，2班的学生年龄 都大于1班的所有同学年龄

select * from students where class_id=2 and age>all(select age from students where class_id=1)

select * from students whe

re class_id=2 and age>(select max(age) from students where class_id=1)

例：查询出在学生表当中，2班的学生年龄 任意大于1班所有同学年龄

select * from students where class_id=2 and age>any(select age from students where class_id=1)

select * from students where class_id=2 and age>(select min(age) from students where class_id=1)

• 行级子查询：子查询返回一行多列的数据

例：查询一班同学中年龄最大的同学信息，单独使用子查询这条语句查的结果可以看出结果集是一行多列。嵌套到主查询后将查出一班同学中年龄最大的同学信息

select * from students where (age,class_id)=(select max(age),class_id from students where class_id=1);

• 表级子查询：子查询返回多行多列的数据

例：查询学生信息对应班级名称，子查询返回的数据充当数据源，再进行过滤

select t1.name,t1.class_name from (select s.*,c.name as class_name from students as s inner join class as c on s.class_id=c.id) as t1;

保存查询结果

语句格式：insert into 表名（例1，例2） select... #这个方法可以将查询的结果直接保存到表里

例：新建一个表用来保存查询结果，学生id，名字，班级，年龄

全列插入，将查询结果插入到info表

insert into temp_table select 0, b1.id,b1,name,b2.name,b1.age from students as b1 inner join class b2 on b1.class_id=b2.id;

合并查询

1. union all 将两次查询的结果集合并到一起显示
2. union 将两个查询的结果集先去重后合并到一起显示