SQL语句面试问答（一）

一、单表查询面试题：

CREATE TABLE students (
	studentNo int(10) primary key auto_increment, 
	name varchar(10),  
	sex varchar(1), 
	hometown varchar(20), 
	age int(4), 
	class varchar(10), 
	card varchar(20)
)；

INSERT INTO students (name, sex, hometown, age, class, card)
VALUES('王一','男','北京','20','1班','123456'), 
	('诸二','男','上海','18','2班','123789'), 
	('张三','男','南京','124','3班','123147'), 
	('白四','男','安徽','22','4班','123258'), 
	('杨五','女','天津','19','3班','123369'), 
	('孙六','女','河北','18','1班','145236'), 
	('赵七','男','山西','20','2班','125698'), 
	('小八','女','河南','15','3班',NULL), 
	('久久','男','湖南','21','1班',''), 
	('史十','女','广东','26','2班','159875'),  
	('李十一','男','北京','30','4班','147896'),  
	('孙十二','男','新疆','26','3班','125632');
 
CREATE TABLE courses (
courseNo int(10) PRIMARY KEY AUTO_INCREMENT, 
name varchar(10)
);
 
INSERT INTO courses
VALUES ('1','数据库'), 
	('2','qtp'), 
	('3','Linux'), 
	('4','系统测试'), 
	('5','单元测试'), 
	('6','测试过程');

CREATE TABLE scores (
	id int(10) PRIMARY KEY AUTO_INCREMENT, 
	courseNo int(10), 
	studentNo int(10),  
	score int(4)
)；

INSERT INTO scores
VALUES ('1','1',1,'90'), 
	('2','2',2,'98'), 
	('3','1',2,'75'), 
	('4','3',1,'86'), 
	('5','3',3,'80'), 
	('6','4',4,'79'), 
	('7','5',5,'96'), 
	('8','6',6,'80');

1、查询学生"张三”的基本信息

select * from students where name='张三'

2、查询学生“李十一“或“孙十二”的基本信息

select * from students where name='李十一' or name='孙十二'

3、查询姓“张”学生的姓名，年龄，班级

select name,age,class from students where name='张%'

4、查询姓名中含有“一”字的学生的基本信息

select * from student where name='%一%'

5、查询姓名长度为三个字，姓“孙”的学生的学号，姓名，年龄，班级，身份证号

select studentNo，name, age, class, card from students where name like '孙__'

select studentNo，name, age, class, card from students 
where char_length(name) = 3  -- 确保姓名长度为3个字符
    and 姓名 like '孙%';   -- 确保姓"孙"

6、查询姓“白”或者姓“孙”的学生的基本信息

select * from students where name like '白%' or name like '孙%'

7、查询姓"白"并且家乡是"山西”的学生信息

select * from students where name like '白%' and hometown='山西'

8、查询家乡是“北京”、“新疆”、“山东"或者"上海"的学生的信息

select * from students where hometown='北京' or hometown='新疆' or hometown='山东' or hometown='上海'

select * from students where hometown in ('北京','新疆','山东','上海')

9、查询姓“孙”，但是家乡不是“河北”的学生信息

select * from students where name like '孙%' and honmetown!='河北'

10、查询家乡不是“北京”、“新疆”、"山东”、”上海”的学生的信息

select * from students 
where hometown!='北京' and hometown!='新疆' and hometown!='山东' and hometown!='上海'

select * from students where hometown not in ('北京','新疆','山东','上海')

11、查询全部学生信息，并按照“性别”排序

select * from students order by sex

12、查询现有学生都来自于哪些不同的省份

select honetown from students group by hometown

13、查询所有男生，并按年龄升序排序

select sex from students where sex='男' order by age asc

14、统计共有多少个学生

select count(*) from students 

15、统计年龄大于20岁的学生有多少个

select count(*) from students where age>'20'

16、统计男生的平均年龄

select avg(age) from students where sex='男'

17、查询1班学生中的最大年龄是多少

select max(age) from students where class='1班'

18、统计2班男女生各有多少人

select sum(sex='男') as '男生总数',sum(sex='女') as '女生总数' from students where class='2班'

select sex,count(*) from students where class='2班' group by sex

19、统计每个班级中每种性别的学生人数，并按照班级升序排序

select class,sex,count(*) from students group by class,sex order by class

问题来源：米兔软件测试