SQL面试题

1、

#查询名下所有库
#mysql> show databases
#使用库
#mysql> use mysql
#建表
#create table A(ID int,NAME char(10),ADDRESS char(40),DATE char(20));
#create table B(ID int,GROUPID int,ADDRESS char(40));
#create table C(ID int,NAME char(10),GRADE int);
#insert into A(ID,NAME,ADDRESS,DATE) values(1,'小明','深圳宝安','2023-3-4');
#insert into A(ID,NAME,ADDRESS,DATE) values(2,'小红','深圳南山','2023-3-5');
#insert into A(ID,NAME,ADDRESS,DATE) values(3,'大左红','深圳龙岗','2023-3-5');
#insert into A(ID,NAME,ADDRESS,DATE) values(4,'小明','深圳光明','2023-3-5');
#答案1
select * from A where NAME in (select NAME from A group by NAME having count(NAME) > 1);

#答案2
#方法1：使用逗号的内连接方法，不影响表A中条件不同的数据
update A,B set A.ADDRESS =B.ADDRESS where A.ID = GROUPID;
#方法2：使用内连接或左连接，不影响表A中条件不同的数据
update A inner join B on A.ID = B.GROUPID set A.ADDRESS =B.ADDRESS;
#方法3：使用子查询，但没where条件，会把此表此字段数据全改了，条件不同的，显示空。所以此语句不适用此题
update A set ADDRESS = (select ADDRESS from B where A.ID = B.GROUPID);
#方法4：使用子查询，使用where条件，不影响表A中条件不同的数据
update A set ADDRESS = (select ADDRESS from B where A.ID = B.GROUPID) where exists(select 1 from B where A.ID = B.GROUPID);

#答案3
delete from C where name like '王%' and GRADE <60;

更改表中字段名和类型：
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 字段类型(长度);
ALTER TABLE system_config CHANGE description remark VARCHAR(50) DEFAULT NULL;
解释 ：
把system_config表description字段名改成remark，VARCHAR(50)类型，默认值为NULL
注：1、不填默认值时，默认为NULL
————————————————

更新表中数据：
http://c.biancheng.net/view/2579.html
模糊查询使用：
https://blog.csdn.net/qq_39390545/article/details/106414765

2、

#create table Students(StudentID int,Name char(10),Age int,Sex char(5),Origin char(20));
#create table Courses(CourseID int,CourseName char(10),Score int);
#create table Enrolls(StudentID int,CourseID int,Grade int);
insert into Students(StudentID,Name,Age,Sex,Origin) values
	(1,'小明',15,'男','广东'),
	(2,'小红',14,'女','北京'),
	(3,'小东',16,'男','湖南');
	(4,'小林',17,'男','湖北');
insert into Courses(CourseID,CourseName,Score ) values
	(1001,'数据库',100),
	(1002,'python',100),
	(1003,'CAD',100);
insert into Enrolls(StudentID,CourseID,Grade ) values
	(1,1001,80),
	(2,1001,91),
	(2,1002,90),
	(1,1003,90),
	(2,1003,95),
	(3,1003,92),
	(4,1003,91);
#三张表如下
Students：
+-----------+------+------+------+--------+
| StudentID | name | age  | sex  | origin |
+-----------+------+------+------+--------+
|         1 | 小明 |   15 | 男   | 广东   |
|         2 | 小红 |   14 | 女   | 北京   |
|         3 | 小东 |   16 | 男   | 湖南   |
|         4 | 小林 |   17 | 男   | 湖北   |
+-----------+------+------+------+--------+
Courses：
+----------+------------+-------+
| CourseID | CourseName | Score |
+----------+------------+-------+
|     1001 | 数据库     |   100 |
|     1002 | python     |   100 |
|     1003 | CAD        |   100 |
+----------+------------+-------+
Enrolls：
+-----------+----------+-------+
| StudentID | CourseID | Grade |
+-----------+----------+-------+
|         1 |     1001 |    80 |
|         2 |     1001 |    91 |
|         2 |     1002 |    90 |
|         1 |     1003 |    90 |
|         2 |     1003 |    95 |
|         3 |     1003 |    92 |
|         4 |     1003 |    91 |
+-----------+----------+-------+

#第1题
select coursename from Courses where CourseID not in(
select CourseID from Enrolls where Enrolls.StudentID  in(select StudentID from Students where sex = '男'));
#查询有女生选修的课程
select CourseID from Enrolls where Enrolls.StudentID  in(
select StudentID from Students where sex = '女')

#第2题
select CourseID,AVG(Grade) from Enrolls group by CourseID having count(StudentID) >=3;
#第3题
select studentID,name from students where studentID not in(
select studentID from Enrolls as E,Courses as C where E.CourseID = C.CourseID and  CourseName = '数据库');
#第4题,先统计全部课程数量，再统计选修表中按学号分组的人分别选了多少课，选的课数量和课程表的数量一致的人就是选修了全部课程的人
select name from students where studentID in(
select studentID from Enrolls  group by studentID having count(CourseID) = (select count(CourseID) from Courses));
#第5题 
未做
#第6题
select CourseName from Courses where CourseID in(
select CourseID from Enrolls group by CourseID having count(StudentID) >=3 and AVG(Grade)>=90);

3、内连接，左右边接，分组查找
参考：https://blog.csdn.net/Long_xu/article/details/127545941?spm=1001.2014.3001.5506

 选课表a ,xh表示学号  ,xcmc表示选课编号
 xh      xcmc 
001     A 
001     B
001     C 
002     A 
002     D
002     F
003     A
003     C
...         ...
查询a中选修课程多于2名的学员的学号

select xh from a group by xh having count(xcmc)>2;