103 lines
2.0 KiB
SQL
103 lines
2.0 KiB
SQL
create table student(
|
||
eid int(11) auto_increment primary key ,
|
||
ename varchar(40),
|
||
sex varchar(10),
|
||
tel varchar(50),
|
||
class_id int(11) ,
|
||
age int(11),
|
||
sal int(2)
|
||
)
|
||
|
||
|
||
insert into student(ename,sex,tel,class_id,age,sal) values
|
||
('小红','女','16738192312','1','25','3000'),
|
||
('小蓝','男','17863123412','2','10','1000'),
|
||
('小绿','女','16234231124','2','25','2000'),
|
||
('小黄','男','16867454532','1','25','8000')
|
||
|
||
|
||
insert into student(ename,sex,tel,class_id,age,sal) values
|
||
('小博','男','17639414630','1','50','10000')
|
||
|
||
|
||
|
||
#2.查询所有学生信息，并根据年龄进行降序排序。（10分）
|
||
|
||
SELECT
|
||
*
|
||
FROM
|
||
student
|
||
LEFT JOIN class ON class.class_id = student.class_id
|
||
ORDER BY
|
||
student.age DESC
|
||
|
||
|
||
#3.显示姓王的学生的详细信息。（10分）
|
||
SELECT
|
||
*
|
||
FROM
|
||
student
|
||
LEFT JOIN class ON class.class_id = student.class_id
|
||
where student.ename like ('%王%')
|
||
|
||
|
||
#4.查询工资大于5000的所有男同学的信息。（10分）
|
||
|
||
SELECT
|
||
*
|
||
FROM
|
||
student
|
||
LEFT JOIN class ON class.class_id = student.class_id
|
||
where student.sal > 5000
|
||
|
||
#5.查询班级编号为20的男同学的电话号码。（10分）
|
||
SELECT
|
||
student.tel
|
||
FROM
|
||
student
|
||
LEFT JOIN class ON class.class_id = student.class_id
|
||
where student.class_id = 20
|
||
and student.sex = '男'
|
||
|
||
|
||
|
||
#6.查询最低工资。（10分）
|
||
SELECT
|
||
student.sal
|
||
FROM
|
||
student
|
||
ORDER BY
|
||
student.sal
|
||
LIMIT 1
|
||
|
||
#7.查询班级为30的学生的最小年龄。（10分）
|
||
|
||
SELECT
|
||
*
|
||
FROM
|
||
student
|
||
LEFT JOIN class ON class.class_id = student.class_id
|
||
where student.class_id = 20
|
||
ORDER BY student.age limit 1
|
||
|
||
|
||
#8.查询女生的最低工资。（15分）
|
||
|
||
SELECT
|
||
student.sal
|
||
FROM
|
||
student
|
||
LEFT JOIN class ON class.class_id = student.class_id
|
||
where student.sex = '女' ORDER BY student.sal limit 1
|
||
|
||
#9.查询女学生中薪资最低的人的全部信息。（15分）
|
||
|
||
SELECT
|
||
*
|
||
FROM
|
||
student
|
||
LEFT JOIN class ON class.class_id = student.class_id
|
||
where student.sex = '女' ORDER BY student.sal limit 1
|
||
|
||
|
||
|