Mysql基础操作（三）

-- 升序
-- select * from students order by age asc
-- asc可省略
-- select * from students order by age 

-- 如果不用排序就按添加的顺序排序
-- select * from students 

-- 降序
-- select * from students order by age desc

-- 查询所有学生信息，按年龄从大到小排序，如果年龄相同，就按照学号降序排列
-- 优先按第一个字段进行排序，如果第一个字段的值相同了，才按照第二个字段进行排序
select * from students order by age,studentno desc

-- 查询所有学生信息，按班级从小到大排序，班级相同时，再按学号从小到大
-- select * from students order by class desc,studentno desc

-- gbk是中国的编码  对中文数据进行排序
-- select * from students order by convert(name using gbk)

-- 统计所有记录 查询学生总数
-- select count(*) from students 

-- 统计某个字段
-- select count(card) from students

-- 查询女生的最大年龄 
-- select max(age) from students where sex='女'

-- 查询1班的最小年龄
-- select min(age) from students where class='1班'


-- 查询北京学生的年龄总和
-- select sum(age) from students where hometown='北京'

-- 查询女生的平均年龄
select avg(age) from students where sex='女'

-- 查询各种性别的人数
-- select sex,count(*) from students group by sex

-- 查询各种年龄的人数
select age,count(*) from students group by age

-- 查询男生总人数
-- select count(*) from students where sex='男'

select sex,count(*) from students group by sex having sex='男'

-- 查询1班除外其它班级学生的平均年龄、最大年龄、最小年龄
-- select class,avg(age),max(age),min(age) from students where class !='1班' group by class

select class,avg(age),max(age),min(age) from students group by class having class !='1班'

-- 查询前3行学生信息
-- select * from students limit 0,3
-- select * from students limit 4,4
-- select * from students order by age limit 0,3

-- 要求每页显示3条

-- select count(*) from students 
-- 12/3 获取总页数

-- 第一页
-- select * from students limit 0,3 

-- 第二页
-- select * from students limit 3,3

-- 第三页
-- select * from students limit 6,3

-- 第四页
select * from students limit 9,3

-- 每页显示5条数据，显示每一页的数据
-- 12/5 2+1

-- 第一页
-- select * from students limit 0,5
-- 第二页
-- select * from students limit 5,5
-- 第三页
select * from students limit 10,5

-- select * from students limit 7

-- 相当于
select * from students limit 0,7

-- drop table if exists courses;
-- 
-- create table courses(
--   courses int(10) unsigned primary key auto_increment,
--  name varchar(10)
-- );
-- 

-- insert into courses values
-- ("1","数据库"),
-- ("2","qtp"),
-- ("3","linux"),
-- ("4","系统测试"),
-- ("5","单元测试"),
-- ("6","测试过程");

-- drop table if exists scores;
-- create table scores(
-- id int(10) unsigned primary key auto_increment,
-- courseNo int(10),
-- studentno varchar(10),
-- score tinyint(4)
-- );

insert into scores values
("1","1","001","90"),
("2","1","002","75"),
("3","2","002","98"),
("4","1","001","86"),
("5","3","003","80"),
("6","4","004","79"),
("7","5","005","96"),
("8","6","006","80");

-- 查询学生信息以及学生的成绩
select * from students,scores where students.studentno=scores.studentno

-- 方式一
-- select * from students,scores where students.studentno=scores.studentno

-- 方式二  不会产生笛卡尔积，不会产生临时表，性能高

select * from students inner join scores on students.studentno=scores.studentno