MySQL 练习

MySQL 练习

2024 年 05 月 07 日

320 次浏览

2567字数

```sql
create database mydb1;
alter database mydb1 character set utf8;
use mydb1;
-- 建表
create table student(
	id int,
	name varchar(20),
	gender varchar(20),
	chinese int,
	math int,
	english int
);
insert into student values
(1,'张明','男',89,78,90),
(2,'李进','男',67,53,80),
(3,'王五','女',87,90,77),
(4,'李一','女',88,98,92),
(5,'李财','男',80,80,80),
(6,'张宝','男',55,91,45),
(7,'黄蓉','女',89,90,91),
(7,'黄蓉','女',89,90,91);
-- 1.	查询表中所有学生的信息
select * from student;

-- 2.	查询表中所有学生的姓名和英语成绩
select name,english from student;

-- 3.	过滤表中的重复数据
select distinct * from student;

-- 4.	统计每个学生的总分
select name,(chinese+math+english) as total from student;

-- 5.	所有学生总分上加上10
select name,(chinese+math+english)+10 as total from student;

-- 6.	使用别名表示学生分数
select name as "姓名",(chinese+math+english)+10 as '总分' from student;

-- 7.	查询英语成绩大于90分的所有同学
select name,english from student where english>90;

-- 8.	查询总分大于200分的所有同学
select name,(chinese+math+english) as total from student having total>200;

-- 9.	查询英语成绩在80到90之间的所有同学
select name,english from student where english between 80 and 90;

-- 10.	查询英语成绩不在80到90之间的所有同学
select name,english from student where english not between 80 and 90;

-- 11.	查询数学分数为89，90，91的同学
select name,math from student where math=89 || math=90 || math=91;
select name,math from student where math in(80,90,91);
-- 12.	查询数学分数不为89，90，91的同学
-- select name,math from student where math!=89 || math!=90 || math!=91;
select name,math from student where math not in(80,90,91);

-- 13.	查询所有姓李的学生英语成绩
select name,english from student where name like '李%';

-- 14.	查询数学分80且语文80分的同学
select name,math,chinese from student where math=80 && chinese=80;

-- 15.	查询英语80或者总分200的同学
select name,english,(chinese+math+english) as tatol from student where english=80 having tatol=200;

-- 16.	对学生数学成绩进行降序排序后输出
select name,math from student order by math desc;

-- 17.	对总分从高到低的顺序输出
select name,(chinese+math+english) as tatol from student order by tatol desc;

-- 18.	对姓李的学生按总成绩升序排序输出
select name,(chinese+math+english) as tatol from student where name like '李%' order by tatol asc ;

-- 19.	查询男生和女生分别有多少人，并将人数降序输出
select sum(gender='男') as male,sum(gender='女') as female from student;
select gender,count(*) as count from student group by gender order by count desc;

-- 20.	查询男生和女生分别有多少人，并将人数降序输出，并查询出大于9的性别人数
select sum(gender='男') as male,sum(gender='女') as female from student order by male desc;
select gender,count(*) as count from student group by gender having count>9 order by count desc;
```

MySQL 练习