Mysql数据查询

目录

  • 1.条件查询
    • 1.1条件查询
    • 1.2比较运算符
    • 1.3逻辑运算符
    • 1.4模糊查询
    • 1.5in查询
    • 1.6between查询
    • 1.7null值判断
    • 1.8排序
  • 2聚合函数
    • 2.1常见函数列表
      • count函数
      • 2.1.1max函数
      • 2.1.2min函数
      • 2.1.3数学函数
      • 2.1.4avg 求平均数
      • 2.1.5round函数
      • 2.1.6时间函数
      • 2.1.7字符串函数
  • 3分组与分页
    • 3.1分组查询
    • 3.2as 取别名
    • 3.3分组后条件筛选
    • 3.4where与having的区别
    • 3.5limit分页
  • 4连接查询
    • 4.1连接查询
    • 4.2mysql三种连接查询
    • 4.3sql语句中any、some、all的使用
  • 5子查询
    • 5.1子查询的概念
    • 5.2子查询四种类型
      • 5.2.1标量子查询
      • 5.2.2列级子查询
      • 5.2.3行级子查询
  • 6保存查询结果
    • 6.1保存查询结果
    • 6.2合并查询
  • 案例

1.条件查询

1.1条件查询

使用where限定语句，查询集只返回条件为True的内容。
如：select * from students where id>13; #这个查询语句将会返回id大于13的数据，不会返回小于13的

1.2比较运算符

案例：查询名字叫后羿的同学信息

案例：查询id小于15的同学的信息

案例：查询家乡不在广州的同学的信息

1.3逻辑运算符

案例：查询家在深圳的男同学

select * from students where hometown='深圳' and gender=0;

案例：查询性别为女生或者家住广州的

案例：查询除了id=2的同学信息

select * from students where not id=2;

1.4模糊查询

案例：

1.5in查询

in 用法匹配括号里面的，符合就返回结果集中

1.6between查询

between and 表示一段区间

案例：查询id 13到16:

select * from students where id between 13 and 16;

1.7null值判断

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

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

select * from students where hometown is null;

1.8排序

  • order by 字段 [desc/asc]
  • desc 表示降序（从大到小排序）
  • asc 默认排序规则，表示升序（从小到大排序）
select * from students order by id desc; #按照id从大到小排序

2聚合函数

2.1常见函数列表

  • count 统计行数
  • max 计算最大值
  • min 计算最小值
  • 数学函数
  • sum 求和
  • avg 求平均数
  • round 函数
  • 时间函数
  • substr函数

count函数

查询学生表中一共有多少人

select count(*) from students;

2.1.1max函数

查询这一列中的最大值
查询学生表中id最大的学生

2.1.2min函数

表示查询这一列中的最小值
查询students中id最小值

2.1.3数学函数

  • MOD(N,M)% 取模
mysql> SELECT MOD(234, 10); -> 4
  • FLOOR(X)向下取整
mysql> SELECT FLOOR(1.23); -> 1
  • CEILING(X)向上取整
mysql> SELECT CEILING(1.23); -> 2 mysql> SELECT CEILING(-1.23); -> -1
  • ROUND(X,D)四舍五入到最近的整数
mysql> SELECT ROUND(-1.23); -> -1 mysql> SELECT ROUND(-1.58); -> -2 mysql> SELECT ROUND(1.58); -> 2 mysql> SELECT ROUND(1.298, 1); -> 1.3 mysql> SELECT ROUND(1.298, 0); -> 1

2.1.4avg 求平均数

查询students表中的id的平均数

求平均数之后默认保留了4位小数

2.1.5round函数

round(decimal,num) #decimal小数，num保留位数

2.1.6时间函数

SELECT NOW()显示当前日期及时间

CURDATE()当前日期

CURTIME()当前时间

2.1.7字符串函数

  • mysql substr() 函数

用法：substr(string, start, length);
string为字符串；start为起始位置；length为长度。
注意：mysql中的start是从1开始的。

  • left(str,len)函数

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

select LEFT('foobarbar', 5);
  • right(str,len)

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

select RIGHT('foobarbar', 4);

3分组与分页

3.1分组查询

group by 字段 # 以xx字段作为分组依据分组
注意：分组后分组依据会显示在结果集，其他列不会出现
如：统计男生，女生分别有多少人

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

3.2as 取别名

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

3.3分组后条件筛选

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

3.4where与having的区别

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

3.5limit分页

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

  select * from students limit start,count; #start从第几条数据开始，count表示获取几条数据  select * from students limit 0,3; #查询前3名同学信息

实例：每页显示3条数据，要求获取第3页的数据

4连接查询

4.1连接查询

新需求：给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;

4.2mysql三种连接查询

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

select * from 表1 inner join 表2 on 表1.列=表2.列； 使用内连接查询学生表和班级表

在连接查询的时候需要多次用到表名，如果表名过长可以使用as给表取别名

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

select * from 表1 right join 表2 on 表1.列=表2.列；使右连接查询学生表和班级表

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

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

案例：
1、查询学生姓名以及对应班级名称

select s.name,c.name from students as s  inner join class as c  on s.class_id=c.id;

备注：students和class表中都有name字段，在显示结果集的时候需要指定表

2、查询python1班所有学生的个人信息和班级信息

select * from students as s inner join class as c on s.class_id=c.id where class_id=1;

4.3sql语句中any、some、all的使用

都是用在子查询里面 用作比较运算

any 表示任意一个 有一个满足了结果就为true 和some是一样的效果
all 全部满足了 结果才为True

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

select * from students where class_id=2 and age>all(select age from students where class_id=1)select * from students where 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 t1.xs,t1.banji from (select b1.name as xs ,b2.name banji from students as b1 inner join class as b2 on b1.class_id=b2.id) as t1

5子查询

5.1子查询的概念

在一个select语句中嵌入了另外一个select语句，嵌入的这个select语句就是子查询语句。
子查询是辅助主查询的，充当数据源，或者充当条件。子查询是一条独立的语句，即使单独拿出子查询也是可以正常执行的
备注：为方便下面演示：在students表中再加一列年龄age，将学生的年龄补充完整

5.2子查询四种类型

  1. 标量子查询
  2. 列级子查询
  3. 行级子查询
  4. 表级子查询

5.2.1标量子查询

子查询返回一行一列的数据，称之为标量子查询

–查询学生年龄小于平均年龄的学生信息
–子查询语句先查出平均年龄：

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

5.2.2列级子查询

子查询返回的是一列多行的数据，称之为列级子查询

  • 查询class表中已经安排学生的班级信息
select * from class where id in (select class_id from students);

备注:可用于子查询的关键字有4个（in、all、any、some）
in： 符合列子查询里面一个
any、some：功能相同，表示任意一个如： where 列 = any(列子查询)
all：等于里面所有，如：where 列 = all(列子查询)

5.2.3行级子查询

子查询返回的是一行多列，称之为行级子查询

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

表级子查询
子查询返回多行多列，称之为表级子查询

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

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;

6保存查询结果

6.1保存查询结果

语句格式：insert into 表名 （列1，列2） select … #这个方法可以将查询的结果直接保存到表里
新建一个表用来保存查询结果，学生id，名字，班级，年龄

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

指定列插入，约束条件为not null的必须插入，其他可用为null的列自动使用null填充

6.2合并查询

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

案例

1、创建一个名为Mydb的数据库，编码格式为utf-8，在Mydb库中创建tb_student学生表.课程表 tb_course. 选课表 tb_score 在表中随便插入一些数据

2、查询所有80后学生的姓名、性别和出生日期(筛选)

-- select sname as 姓名, if(gender,'男','女') as 性别,birth as 出生日期 from tb_student where birth BETWEEN '1980-1-1' and '1989-12-31';
-- select sname as 姓名, CASE gender when 1 then '男' else '女' END as 性别,birth as 出生日期 from tb_student where birth BETWEEN '1980-1-1' and '1989-12-31';

3、查询名字由4个中文字符的学生学号和姓名（运算 函数）

-- SELECT sname as 姓名,stuid as 学号 from tb_student where LENGTH(sname)/3=4  LENGTH #返回的是字节长度  一个中文3个字节

4、查询名字中有”不“字或“嫣”字的学生的姓名(模糊)

-- SELECT sname as 姓名,stuid as 学号 from tb_student where sname LIKE '%不%' or sname LIKE '%嫣%'

5、查询学生选课的所有日期(去重)

-- SELECT DISTINCT seldate as 选课日期 from tb_score   #DISTINCT 可以实现去重

6、查询男学生的姓名和生日按年龄从大到小排列(排序)

-- SELECT sname as 姓名,birth as 生日 from tb_student where gender=1 ORDER By birth desc;

7、查询每个学生的学号和平均成绩(分组和聚合函数)

-- SELECT sid as 学号,avg(mark) as 平均成绩 from tb_score group by sid;

8、查询选了两门以上的课程的学生姓名(子查询/分组条件/集合运算)

-- SELECT sname as 姓名 from tb_student where stuid in (SELECT sid as 学号 from tb_score GROUP BY sid HAVING count(sid)>2);

9、查询选课学生的姓名和平均成绩(子查询和连接查询)

-- SELECT sname as 姓名, avgMark  as 平均分 from tb_student t1 INNER JOIN (SELECT sid ,avg(mark) as avgMark from tb_score GROUP BY sid) as t2 on t1.stuid=t2.sid;

10、查询每个学生的姓名和选课数量(左外连接和子查询)

SELECT sname as 姓名, IFNULL(total,0) as 选课数量 from tb_student t1 LEFT JOIN (SELECT sid,count(sid) as total from tb_score GROUP BY sid) t2 on t1.stuid=t2.sid;
赞 (0)

相关推荐