select基础查询

select查询

distinct取消重复

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,'ymm',89,78,99);
insert into student(id,name,chinese,english,math) values(2,'hh',99,89,88);
insert into student(id,name,chinese,english,math) values(3,'yxc',90,99,90);

1
2
3
4
5
6
7
8
9
10
11
12

1. 查询表中所有学生的信息
2. 查询表中所有学生的姓名和对应的英语成绩
3. 过滤表中重复的数据 distinct
4. 要查询的记录，每个字段都相同，才会去重

-- select 查询
select * from student;
select name,english from student;
select distinct * from student;
select distinct chinese from student;
select distinct name,chinese from student;
-- 要查询的记录，每个字段都相同，才会去重

1
2
3
4
5
6
7
8

使用表达式进行运算，使用as语句

1. 统计每个学生的总分
2. 在所有学生总分加10分的情况
3. 使用别名表示学生的数学分数

-- 总分
select `name`,(chinese+english+math) from student;
select `name`,(chinese+english+math+10) from student;

select `name`,(chinese+english+math) as total from student;
select `name` as '名字',(chinese+english+math) from student;


1
2
3
4
5
6
7
8

在where子句中使用运算符

-- where
select * from student where `name` = 'ymm';
select * from student where `english` >90;
select * from student where (chinese+english+math) > 200;
1
2
3
4

-- 查询math>60 并且 english > 90
select * from student where `math`>60 and `english`>90;

-- 查询总分大于200并且math大于chinese的首字母为y的学生
select * from student where (chinese+math+english) > 200 and math>chinese and `name` like 'y%';

-- 查询English在80到90分之间的
select * from student where english between 80 and 90; -- 闭区间
select * from student where english >=80 and english <= 90;
1
2
3
4
5
6
7
8
9

使用order by子句排序查询

升序：Ascending order

降序：Descending order

-- 排序
select * from student order by math;

-- 总分降序
select * from student order by (chinese + math + english) desc;
-- 首字母为y升序
select * from student where `name` like 'y%' order by (chinese+math+english);

select `name`,(chinese+english+math) as total from student where `name` like 'y%' order by total;
1
2
3
4
5
6
7
8
9