查询

##01.mysql-查询
–数据准备
–创建一个数据库
create database python_test charset = utf8;

--使用一个数据库

use python_test;

-- 显示使用的的当前数据库

select database();

--创建一个数据表  

  create table students (

  id int unsigned primary key auto_ increment not null ,
  name varchar(20) default '',
  age tinyint unsigned default 0,
  height decimal(5,2),
  gender enum('男','女', '中性', '保密') default '保密',
  cls_ id int unsigned default 0,
  is_ delete bit default 0);

 -- classes
  create table classes (
           id int uns igned auto_ increment primary key not null,
           name varchar(30) not null);

-查询
– 查询所有字段
– select * from表名;
select * from xxxx
select id， name from students;

  --查询指定字段   
  -- select 列1,列2,... from表名;  
  select name,age from students;
  
  --使用as给字段起别名  
  -- select 字段as名字.... from表名;  
  select name as 名字, age as 年龄 from students;
  
  -- select 表名.字段.... from 表名;  
select students.name,students.age from students;
  
  --可以通过as给表起别名  
  -- select 别名.字段.... from表名as别名;  
    select students.name, students.age from students
  select s.name, s,age from students as s;
  
  消除重复行  
  一distinct 字段  
  select distinct gender from students;

一条件查询
一比较运算符
select …. from表名where ….
一查询大于18岁的信息
select * from students where age >18;
select id,name,gender from students where age >18;
– <
一查询小于18岁的信息select * from
select * from students where age<18;
- >=
一查询小于或者等于18岁的信息

1 2	-查询年龄为18岁的所有学生的名字 select * from students where age=18;

-!=或者。
一逻辑运算符

and
18到28之间的所以学生信息
select * from students where age>18 and age<28;
-失败select * from students where age>18 and <28;
18岁以上的女性
select * from students where age>18 and gender=”女”
select * from students where age>18 and gender=”女”

– or
– 18以上或者身高查过180(包含 )以上
select * from students where age>18 or height>=180;

– not
-不在18岁以上的女性这个范围内的信息

select * from students where not age>18 and gender=2;select * from students where not (age>18 and gender-2);

–年龄不是小于或者等于18并且是女性
select * from students where (not age =18) and gender=2;

–模糊查询
–like
– % 替换一个或多个
– 替换一个
– 查询名字中以”小”开始的名
select name from students where name = “小”；
select name from students where name like “小%”;
–查询名字中有“小“的所有名字
select name from students where name like “%小%”;
–查询名字中有2个字的名字
select name from students where name like ““;
–查询有3个字的名字
select name from students where name like ““;
–查询至少有2个字的名字
select name from students where name like “__%”;
–rlike 正则
–查询以周开始的姓名
select name from students where name rlike “^周.*”;
select name from students where name rlike “^周.*轮$”;

-范围查询

in (1, 3, 8)表示在一个非连续的范围内
–查询年龄为18、34的姓名
select name ,age from students where age=18 or age=34;
select name ,age from students where age=18 or age=34 or age=12;
select name ,age from students where in (12, 18,34);

– not in不非连续的范围之内
–年龄不是18、34岁之间的信息
select name ,age from students where age not in (12,18,34)

– between … and ..表示在一个连续的范围内
–查询年龄在18到34之间的的信息
select name ,age from students where age between 18 and 34;

– not between …. and ..表示不在一个连续的范围内
–查询年龄不在在18到34之间的的信息
select * from students where age not between 18 and 34;
select * from students where not age between 18 and 34;
-失败select * from students where age not (between 18 and 34);

–空判断
–判断is null
查询身高为空的信息
select * from students where height is null;

–判非空is not null
select * from students where height is not null;

排序

order by字段
asc从小到大排列，即升序- desc从大到小排序，即降序

-查询年龄在18到34岁之间的男性，按照年龄从小到到排序
select * from students where (age bebetween 18 and 34) and gender=1;
select * from students where (age bebetween 18 and 34) and gender=1 oder by age; (按年龄默认排序从大到小）
select * from students where (age bebetween 18 and 34) and gender=1 oder by age asc; (从小到大)

-查询年龄在18到34岁之间的女性，身高从高到矮排序
select * from students where (age bebetween 18 and 34) and gender=2 order by height;

order by多个字段
-查询年龄在18到34岁之间的女性，身高从高到矮排序，如果身高相同的情况下按年龄从大到小排序
select * from students where (age bebetween 18 and 34) and gender=2 order by height desc,id desc;

-查询年龄在18到34岁之间的女性，身高从高到矮排序，如果身高相同的情况下按用-如果年龄也相同那么按照id从大到小排序
select * from students where (age bebetween 18 and 34) and gender=2 order by height desc,age asc,id desc;

-按照年龄从小到大、身高从高到矮的排序
select * from students where order age asc, height desc;

–聚合函数
–总数
–count
-查询男性有多少人，女性有多少人
select from students where gender=1;
select count() from students where gender=1;
select count(*) as 男性人数 from students where gender=1;

–最大值

max
-查询最大的年龄
select age from students;
select max(age) from students;

–最小值
–mine()

–求和
–sum()
–计算所有人的年龄总和
select sum(age) from students;

–平均值
–avg
计算平均年龄
select avg(age) from students;

–计算平均年龄 sum(age)/count()
select sum(age)/count() from students;

–四舍五入 round(123.23 , 1) 保留一位小数
–计算所有人的平均年龄，保留两位小数
select round(sum(age)/count(*),2) from students;

–分组

–group by
–按照性别分组，查询所有性别
select name from students group by gender;
select * from students group by gender;
–失败select *from students group by gender;

–计算每种性别中的人数
select gender, count(*) from students group by gender;

–计算男性人数
select gender,count(*) from students group by gender;

–group_count(……)
–查询同种性别中的姓名
select gender,group_coucat(name) from students where gender =1 group by gender;
select gender,group_coucat(name,age,id) from students where gender =1 group by gender;
select gender,group_coucat(name,”_”,age,” “,id) from students where gender =1 group by gender;

–having
–查询平均年龄超过三十岁的性别，以及姓名哈宁 avg(age) > 30
select gender,group_coucat(name) ,avg(age) from students group by gender having avg(age) > 30;

–查询每种性别中的人数多于2个的信息
select gender,group_concat(name) from students group by gender having count(*)>2;

-分页
– limit start, count

–限制查询出来的数据个数
select * from students where gender=1 limit 2;

–查询前5个数据
select * from students where limit 0,5;

–查询id6-10 (包含) 的书序
select * from students limit 5,5;

-每页显示2个，第1个页面
select * from students limit 0,2;

-每页显示2个，第2个页面
select * from students limit 2,2;

–每页显示2个，第3个页面
select * from students limit 4,2;

–每页显示2个，第4个页面
select * from students limit 6,2; ——>limit (第N页-1)*每个的个数，每页的个数；

–每页显示2个，显示第6页的信息，按照年龄从小到大排序
–失败select * from students limit 2*(6-1),2;
–失败select * from students limit 10,2 order by age asc;(将limit放到最后)

-连接查询

inner join … on
-select *from students inner join classes;

–查询有能够对应班级的学生以及班级信息
select * from students inner join classes on students.cls_id=classes.id;

–按照要求显示姓名、班级
select students.*,classes.name fromstudents inner join classes on students.cls_id=classes.id;
select students.name,classes.name from students inner join classes on students.cls_id=classes.id;

–给数据表起名字
select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;

–查询有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称
select s.* c.name from students as s inner join classes as c on s.cls_id=c.id;

-在以上的查询中，将班级姓名显示在第1列
select c.name ,s.* from students as s inner join classes as c on s.cls_id=c.id;

-查询有能够对应班级的学生以及班级信息，按照班级进行排序
– select C.XXX S.XXX from student as S inner join clssses as C on … order by …
select c.name ,s.* from students as s inner join classes as c on s.cls_id=c.id odder by c.name;

-当时同一个班级的时候，按照学生的i进行从小到大排序
select c.name ,s.* from students as s inner join classes as c on s.cls_id=c.id odder by c.name，s.id;

Left join
–查询每位学生对应的班级信息
select * from student as sleft jion classes as c on s.cls_id=c.id;

–查询没有对应班级信息的学生
– select … from XXX as S left join XXX as C 0….. where…..
– select … from XXX as 5 Left join XXX as C 0….. having ….
select * from students as s left jion classes as c on s.cls_id=sc.id having c.id is null;
select * from students as s left jion classes as c on s.cls_id=sc.id where c.id is null;

right join on
–将数据表名字互换位置，用Left join完成

###子查询

1.子查询
在一个 select 语句中,嵌入了另外-个selet语句,那么被嵌入的select语句称之为子查询语句

2.主查询
主要查询的对象,第 -条select语句

主查询和子查询的关系
●子查询是嵌入到主查询中
●子查询是辅助主查询的，要么充当条件要么充当数据源
●子查询是可以独立存在的语句,是一条完整的 select语句

子查询分类
●标量子查询:子查询返回的结果是一个数据(一行一列)
●列子查询:返回的结果是一列(一列多行)
●行子查询:返回的结果是一行(一行多列)

标量子查询

1.查询班级学生平均年龄
2.查询大于平均年龄的学生