Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2cf01ed

Browse files
committed
SQL经典笔试题目
1 parentf9ae7f6 commit2cf01ed

File tree

7 files changed

+258
-49
lines changed

7 files changed

+258
-49
lines changed

‎_sidebar.md

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -56,15 +56,18 @@
5656
*[SpringBoot框架](./docs/框架/SpringBoot.md)
5757

5858
* 数据库
59-
*[SQL进阶](./docs/database/SQL进阶.md)
60-
*[常见SQL优化方式](./docs/database/常见SQL优化方式.md)
6159
*[MySQL基础概念](./docs/database/MySQL.md)
6260
*[如何使用索引](./docs/database/如何使用索引.md)
6361
*[什么情况下索引会失效](./docs/database/什么情况下索引失效.md)
6462
*[什么时候不需要创建索引](./docs/database/什么时候不需要创建索引.md)
6563
*[B树与B+树详谈](./docs/database/B树与B+树详谈.md)
6664
*[Hash索引与B+树索引的区别](./docs/database/Hash索引与B+树索引的区别.md)
6765
*[如何使用EXPLAIN查看执行计划](./docs/database/如何使用EXPLAIN查看执行计划.md)
66+
67+
* SQL
68+
*[SQL进阶](./docs/database/SQL进阶.md)
69+
*[常见SQL优化方式](./docs/database/常见SQL优化方式.md)
70+
*[SQL经典笔试题目](./docs/database/SQL经典笔试题目.md)
6871

6972
* Redis
7073
*[Redis为什么会这么快](./docs/Redis/Redis为什么会这么快.md)
Lines changed: 162 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,162 @@
1+
#SQL经典练习题目
2+
3+
##1. 创建表结构及测试数据
4+
5+
###**学生表（student)**
6+
7+
```sql
8+
CREATETABLE `student` (
9+
`sid`varchar(10) DEFAULTNULL,
10+
`sname`varchar(10) DEFAULTNULL,
11+
`sage` datetime DEFAULTNULL,
12+
`ssex`varchar(10) DEFAULTNULL
13+
) ENGINE=InnoDB DEFAULT CHARSET=utf8
14+
```
15+
16+
###**教师表(teacher)**
17+
18+
```sql
19+
CREATETABLE `teacher` (
20+
`tid`varchar(10) DEFAULTNULL,
21+
`tname`varchar(10) DEFAULTNULL
22+
) ENGINE=InnoDB DEFAULT CHARSET=utf8
23+
```
24+
25+
###**成绩表（sc）**
26+
27+
```sql
28+
CREATETABLE `sc` (
29+
`sid`varchar(10) DEFAULTNULL,
30+
`cid`varchar(10) DEFAULTNULL,
31+
`score`decimal(18,1) DEFAULTNULL
32+
) ENGINE=InnoDB DEFAULT CHARSET=utf8
33+
```
34+
35+
###**课程表（course）**
36+
37+
```sql
38+
CREATETABLE `course` (
39+
`cid`varchar(10) DEFAULTNULL,
40+
`cname`varchar(10) DEFAULTNULL,
41+
`tid`varchar(10) DEFAULTNULL
42+
) ENGINE=InnoDB DEFAULT CHARSET=utf8
43+
```
44+
45+
###创建测试数据
46+
47+
```sql
48+
# 学生表数据
49+
insert into Studentvalues('01' ,'赵雷' ,'1990-01-01' ,'男');
50+
insert into Studentvalues('02' ,'钱电' ,'1990-12-21' ,'男');
51+
insert into Studentvalues('03' ,'孙风' ,'1990-05-20' ,'男');
52+
insert into Studentvalues('04' ,'李云' ,'1990-08-06' ,'男');
53+
insert into Studentvalues('05' ,'周梅' ,'1991-12-01' ,'女');
54+
insert into Studentvalues('06' ,'吴兰' ,'1992-03-01' ,'女');
55+
insert into Studentvalues('07' ,'郑竹' ,'1989-07-01' ,'女');
56+
insert into Studentvalues('08' ,'王菊' ,'1990-01-20' ,'女');
57+
58+
# 课程表数据
59+
insert into Coursevalues('01' ,'语文' ,'02');
60+
insert into Coursevalues('02' ,'数学' ,'01');
61+
insert into Coursevalues('03' ,'英语' ,'03');
62+
63+
64+
# 教师表数据
65+
insert into Teachervalues('01' ,'张三');
66+
insert into Teachervalues('02' ,'李四');
67+
insert into Teachervalues('03' ,'王五');
68+
69+
70+
# 成绩表数据
71+
insert into SCvalues('01' ,'01' ,80);
72+
insert into SCvalues('01' ,'02' ,90);
73+
insert into SCvalues('01' ,'03' ,99);
74+
insert into SCvalues('02' ,'01' ,70);
75+
insert into SCvalues('02' ,'02' ,60);
76+
insert into SCvalues('02' ,'03' ,80);
77+
insert into SCvalues('03' ,'01' ,80);
78+
insert into SCvalues('03' ,'02' ,80);
79+
insert into SCvalues('03' ,'03' ,80);
80+
insert into SCvalues('04' ,'01' ,50);
81+
insert into SCvalues('04' ,'02' ,30);
82+
insert into SCvalues('04' ,'03' ,20);
83+
insert into SCvalues('05' ,'01' ,76);
84+
insert into SCvalues('05' ,'02' ,87);
85+
insert into SCvalues('06' ,'01' ,31);
86+
insert into SCvalues('06' ,'03' ,34);
87+
insert into SCvalues('07' ,'02' ,89);
88+
insert into SCvalues('07' ,'03' ,98);
89+
```
90+
91+
92+
93+
- 查询所有同学的学号、姓名、选课数、总成绩；
94+
95+
```sql
96+
97+
```
98+
99+
100+
101+
- 查询平均成绩大于60分的同学的学号和平均成绩；
102+
103+
```sql
104+
select sid,avg(score)as avgscore
105+
from sc
106+
group by score
107+
having avgscore>60
108+
```
109+
110+
- 查询“01”课程比“02”课程成绩高的所有学生的学号；
111+
112+
```sql
113+
SELECTt1.sid
114+
FROM
115+
(select sid,score
116+
from sc
117+
where cid='01')as t1
118+
JOIN
119+
(select sid,score
120+
from sc
121+
where cid='02')as t2
122+
ONt1.sid=t2.sidandt1.score>t2.score
123+
```
124+
125+
- 查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名；
126+
- 查询姓“李”的老师的个数；
127+
128+
```sql
129+
select*from teacherwhere tnamelike"李%"
130+
```
131+
132+
- 查询没学过“张三”老师课的同学的学号、姓名；
133+
134+
```sql
135+
select distinct sid,sname
136+
from student
137+
where sid notin (
138+
select sidfrom sc
139+
join courseas consc.cid=c.cid
140+
join teacheras tonc.tid=t.tid
141+
wheret.tname='张三'
142+
)
143+
```
144+
145+
- 查询学过“张三”老师所教的课的同学的学号、姓名；
146+
147+
```sql
148+
select distinct sid,sname
149+
from student
150+
where sidin (
151+
select sidfrom sc
152+
join courseas consc.cid=c.cid
153+
join teacheras tonc.tid=t.tid
154+
wheret.tname='张三'
155+
)
156+
```
157+
158+
- 查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名;
159+
- 查询**所有课程**成绩小于60分的同学的学号、姓名；
160+
- 查询没有学全所有课的同学的学号、姓名；
161+
-
162+

‎src/com/imood/msjava/Main.java

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,5 @@
11
packagecom.imood.msjava;
22

3-
4-
importjava.util.Scanner;
5-
63
/**
74
* @description: 三次反转实现字符串循环右边移动N位
85
* @author: msJava

‎src/com/imood/msjava/Solution.java

Lines changed: 0 additions & 44 deletions
This file was deleted.
Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,39 @@
1+
packagecom.imood.msjava.leetcode;
2+
3+
importjava.util.Stack;
4+
5+
/**
6+
* @description: 用栈实现括号匹配
7+
* @author: 微信公众号：码上Java
8+
* @createDate: 2020/7/29/0029
9+
*/
10+
publicclassIsValid_20 {
11+
12+
13+
/**
14+
* 用栈实现括号匹配
15+
* @param s
16+
* @return
17+
*/
18+
publicbooleanisValid(Strings) {
19+
Stack<Character>stack =newStack<>();
20+
for (charc :s.toCharArray()) {
21+
if (c =='(' ||c =='{' ||c =='[') {
22+
stack.push(c);
23+
}else {
24+
if (stack.isEmpty()) {
25+
returnfalse;
26+
}
27+
charcStack =stack.pop();
28+
booleanb1 =c ==')' &&cStack !='(';
29+
booleanb2 =c ==']' &&cStack !='[';
30+
booleanb3 =c =='}' &&cStack !='{';
31+
if (b1 ||b2 ||b3) {
32+
returnfalse;
33+
}
34+
}
35+
}
36+
returnstack.isEmpty();
37+
}
38+
39+
}
Lines changed: 48 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,48 @@
1+
packagecom.imood.msjava.数据结构;
2+
3+
importjava.util.Stack;
4+
/**
5+
* @description: 用栈实现队列
6+
* @author: 微信公众号：码上Java
7+
* @createDate: 2020/7/29/0029
8+
*/
9+
classMyQueue {
10+
11+
12+
/**
13+
* 栈的顺序为后进先出，而队列的顺序为先进先出。
14+
* 使用两个栈实现队列，一个元素需要经过两个栈才能出队列，在经过第一个栈时元素顺序被反转，
15+
* 经过第二个栈时再次被反转，此时就是先进先出顺序。
16+
*/
17+
privateStack<Integer>in =newStack<>();
18+
privateStack<Integer>out =newStack<>();
19+
20+
21+
publicvoidpush(intx) {
22+
23+
in.push(x);
24+
}
25+
26+
publicintpop() {
27+
in2out();
28+
returnout.pop();
29+
}
30+
31+
publicintpeek() {
32+
in2out();
33+
returnout.peek();
34+
}
35+
36+
privatevoidin2out() {
37+
if (out.isEmpty()) {
38+
while (!in.isEmpty()) {
39+
out.push(in.pop());
40+
}
41+
}
42+
}
43+
44+
publicbooleanempty() {
45+
returnin.isEmpty() &&out.isEmpty();
46+
}
47+
}
48+

‎src/com/imood/msjava/数据结构/Stack.java

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -62,6 +62,10 @@ public E peek(){
6262
}
6363

6464

65+
publicbooleanisEmpty(){
66+
returnmaxSize==0;
67+
}
68+
6569

6670

6771

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp