Mysql_select（1）

Mysql_select（1）

create table student(
				id int not null default 1,
				`name` varchar(20) not null default '',
				chinese float not null default 0.0,
				english float not null default 0.0,
				math float not null default 0.0
);

insert into student(id,name,chinese,english,math)values(1,'刘备',89,78,90);
insert into student(id,name,chinese,english,math)values(2,'张飞',67,98,56);
insert into student(id,name,chinese,english,math)values(3,'宋江',87,78,77);
insert into student(id,name,chinese,english,math)values(4,'关羽',88,98,90);
insert into student(id,name,chinese,english,math)values(5,'赵云',82,84,67);
insert into student(id,name,chinese,english,math)values(6,'欧阳锋',55,85,45);
insert into student(id,name,chinese,english,math)values(7,'黄蓉',75,65,30);

-- 查询表中所有学生信息
select * from student;

-- 查询表中所有学生姓名与对应的英语成绩
select `name` ,english from student;

-- 过滤表中重复数据 distinct 
select distinct english from student;

-- 统计每个学生的总分
select `name` ,(chinese + english + math) from student;
-- 在所有学生总分加10分的情况
select `name` ,(chinese + english + math + 10) from student;

-- 使用别名表示学生分数
select `name` ,(chinese + english + math) as total_score from student;



-- 查询姓名为赵云的学生成绩
select * from student where `name`='赵云';

-- 查询英语成绩大于90分的同学
select * from student where english > 90;

-- 查询总分大于200分的所有同学
select * from student where(chinese + english + math) > 200;



-- 查询math大于60 并且（and）id大于4的学生成绩
select * from student where math > 60 and id > 4 ;

-- 查询英语成绩大于语文成绩的同学
select * from student where english > chinese ;

-- 查询总分大于200分 并且 数学成绩小于语文成绩，的姓刘的学生
-- 宋% 表示名字以 刘 开头的就可以（一个刘字也可以）
select * from student
				where (chinese + english + math) > 200 and 
				math < chinese and `name` like '宋%';



-- 查询英语在80 —— 90之间的同学
select * from student
		where english >=80 and english<=90;

select * from student
	where english between 80 and 90;
		
-- 查询数学分数为89，90，91的同学
select * from student 
		where math=89 or math=90 or math=91;
		
select * from student
		where math in (89,90,91); 
		
-- 查询所有 关 姓同学的成绩
select * from student
		where `name` like '关%';

-- 查询数学分>80，语文分>80的同学
select * from student 
		where math>80 and chinese>80;

order by

-- order by 使用
-- 对数学成绩排序后输出（升序）
select * from student
		order by math ;
		
-- 对总分从高到低输出
select `name`, (chinese+math+english) as total_score  from student
    order by total_score desc;
		
-- 对赵姓同学成绩排序输出（升序）
select * from student
		where `name` like '赵%'
		order by (chinese+math+english);