最新消息:XAMPP默认安装之后是很不安全的,我们只需要点击左方菜单的 "安全"选项,按照向导操作即可完成安全设置。

mysql学习笔记——mysql查询

XAMPP案例 admin 603浏览 0评论

01mysql

–聚合函数

–总数
–count
–查询男性有多少人,女性有多少人
select count(*) from students where gender=1;
select count(*) as 男性人数 from students where gender=1;
select count(*) as 女性人数 from students where gender=2;
–最大值
–max
–查询最大的年龄
select max(age) from students;
–查询女性的最高 身高
select max(height) from students where gender=2;
–最小值
–min
–求和
–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) 保留1位小数
–计算所有人的平均年龄,保留2位小数
select round(sum(age)/count(*),2) from students;
–计算男性的平均身高 保留2位小数
select round(sum(age)/count(*),2) from students where gender=1;
–失败select round(sum(age)/count(*),2) from students where gender=1;

 

–分组

 

–group by
–按照性别分组,查询所有性别
–失败select name from students group by gender;
–失败select * from students group by gender;
select gender from students group by gender;
–计算每种性别中的人数
select gender, count(*) from students group by gender;
–计算男性的人数
select gender,count(*) from students where gender =1 group by gender;
–group_concat(…) (链接在一起显示)
–查询同种性别的姓名
select gender, group_concat(name) from students group by gender;
>>>彭于晏,刘德华
select gender, group_concat(name, age, id) from students group by gender;
>>> 彭于晏293,刘德华596…
select gender, group_concat(name, “_”, age, “ ”, id) from students group by gender;
>>> 彭于晏_29 3,刘德华_59
–having
–查询平均年龄超过30岁的性别,以及姓名 having avg(age)>30
select gender , group_concat(name), avg(name) from students group by gender having avg(name)>3;
where对原始表判断,having对结果进行判断
–查询每种性别中的人数多于2个的信息
–select gender, group_concat(name) from students group by gender having count(*)>2;

 

–分页

 

–limit start, count
–限制查询出来的数量
select * from students where gender=1 limit2;
–查询前5个数据
select *from students limit 5;
select *from students limit 0,5; (从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在最后)
select *from students order by age asc limit 10, 2;

 

where->orderby->limit

 

–连接查询

 

–inner join …on
–select… from 表a inner join 表b;
select * from students inner join classes;
–查询 有能够对应班级的学生以及班级信息
select * from students 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 students.cls_id=classes.id;
–在以上的查询中,将班级姓名显示在第1列
select c.name, s.* from students as s inner join classes as c on students.cls_id=classes.id;
–查询 有能够对应班级的学生以及班级信息,按照班级进行排序
–select c.xxx s.xxx from student as s inner join classes as c on …order by…;
select c.name, s.* from students as s inner join classes as c on students.cls_id=classes.id order by c.name;
–当时同一个班级的时候,按照学生的id进行从小到大排序
select c.name, s.* from students as s inner join classes as c on students.cls_id=classes.id order by c.name, s.id;
–left join
–查询每位学生对应的班级信息
select * from students as s left join classes as c on s.cls_id=c.id;
students表在左边,以students表所有的记录为基准,没有对应显示null
–查询没有对应班级信息的学生
–select … from xxx as s left join xxx as c on …where …
–select … from xxx as s left join xxx as c on …having…
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null;
select * from students as s left join classes as c on s.cls_id=c.id where c.id is null;
–right join  on
–将数据表名字互换位置,用left join完成

 

–自关联

 

–省级联动 url:http://demo.lanrenzhijia.com/2014/city0605/
–创建areas表 (用一张表完成)
Create table areas(
      aid int primary key,
      atitle varchar(20),
      pid int
  );
–查询所有省份
select * from areas where pid is null;
–查询出山东省有哪些城市
select aid from areas where atitle= “山东省”;
select * from areas where pid=370000;
select * from areas as province inner join areas as city on city.pid=provice.aid having province.artitle= “山东省”;
select province.atitle, city.atitle from areas as province inner join areas as city on city.pid=provice.aid having province.artitle= “山东省”;
(一张表当多个表对待)
select * from areas where pid=(select aid from areas where atitle= “山东省”);

 

–子查询

–标量子查询

–查询出高于平均身高的信息

 

–查询最高的男生信息

select * from students where height(select max(height) from students);

 

–列级子查询

–查询学生的班级号能够对应的学生信息

select * from students where cls_id in (select id from classes);

转载请注明:XAMPP中文组官网 » mysql学习笔记——mysql查询

您必须 登录 才能发表评论!