数据库的基本语句

发表于 | 分类于 |
字数统计: 2.5k | 阅读时长 ≈ 13

数据库

MySQL

– 查询学号前五位同学

1
2
3
4
5
6
7
SELECT STUDENT_ID,STUDENT_NM,MOBILE_PHONE

FROM t_student 

ORDER BY STUDENT_ID 

LIMIT 5

– 去除重复的人

1
2
3
SELECT DISTINCT STUDENT_NM

FROM t_student

– 查询0010学号往后的5位同学

1
2
3
4
5
6
7
8
9
SELECT STUDENT_ID

FROM t_student

WHERE STUDENT_ID LIKE 'SX00%' 

ORDER BY STUDENT_ID 

LIMIT 10,5

– 查询7月份出生的

1
2
3
4
5
SELECT BIRTHDAY

FROM t_student

WHERE BIRTHDAY LIKE '%/07/%'

– 查询学号是0001和0006的学生

1
2
3
4
5
SELECT STUDENT_ID,STUDENT_NM

FROM t_student

WHERE STUDENT_ID = 'SX0001' OR STUDENT_ID = 'SX0006'

– 查询学号是0001和0006的学生

1
2
3
4
5
SELECT STUDENT_ID,STUDENT_NM

FROM t_student

WHERE STUDENT_ID IN ('SX0001','SX0006')

– 班级是001和97年出生的

1
2
3
4
5
6
7
SELECT CLASSID,BIRTHDAY,STUDENT_ID,STUDENT_NM

FROM t_student

WHERE CLASSID = '001' OR BIRTHDAY LIKE '1997/%'

ORDER BY CLASSID

– 班级是001并且97年出生的

1
2
3
4
5
6
7
SELECT CLASSID,BIRTHDAY,STUDENT_ID,STUDENT_NM

FROM t_student

WHERE CLASSID = '001' AND BIRTHDAY LIKE '1997/%'

ORDER BY CLASSID

– 在输入的时候能查询，不输入的时候输出所有

1
2
3
4
5
6
7
SET @student = 'SX0001';

SELECT CLASSID,BIRTHDAY,STUDENT_ID,STUDENT_NM

FROM t_student t

WHERE (@student <> '' AND t.STUDENT_ID = @student) OR (@student = '' t.STUDENT_ID = t.STUDENT_ID)

– 同上

1
2
3
4
5
6
7
SET @student = 'sx0001';

SELECT CLASSID,BIRTHDAY,STUDENT_ID,STUDENT_NM

FROM t_student t

WHERE (@student = '' AND 1=1) OR t.STUDENT_ID = @student

– 同上

1
2
3
4
5
6
7
SET @student = 'sx0001';

SELECT CLASSID,BIRTHDAY,STUDENT_ID,STUDENT_NM

FROM t_student t

WHERE @student = '' OR t.STUDENT_ID = @student

– 查询出生97年1~6月份的

1
2
3
4
5
SELECT STUDENT_NM,BIRTHDAY

FROM t_student t

WHERE t.BIRTHDAY BETWEEN '1997/01/01' AND '1997/06/31'

– 查询用户ID,用户姓名,性别,班级,教师,职称,成绩,成绩总和

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT s.STUDENT_ID,s.STUDENT_NM,m1.NM,c.CLASS_NM,t.TEACHER_NM,m.NM,a.CHINESE,a.MATHEMATICS,a.ENGLISH,a.CHINESE+a.MATHEMATICS+a.ENGLISH AS 总和

FROM t_student s

LEFT JOIN t_class c on s.STUDENT_ID = c.STUDENT_ID AND S.CLASSID = C.CLASS_ID

LEFT JOIN t_teacher t on t.TEACHER_ID = c.HEAD_MASTER

LEFT JOIN t_mast m on t.TEACHER_LEVEL = m.CD AND m.TYPE_CD = '02'

LEFT JOIN t_mast m1 on s.SEX = m1.CD AND m1.TYPE_CD = '01'

LEFT JOIN t_achievement a on a.STUDENT_ID = s.STUDENT_ID 

ORDER BY s.STUDENT_ID

– 当遇到成绩中为空值的时候需要使用IFNULL函数，将空值变成0在继续计算

I

1
FNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0) AS 总成绩

– 查询出平局成绩小于60的不及格,无成绩的显示空

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
SELECT s.STUDENT_ID AS 学生ID,

​	s.STUDENT_NM AS 学生姓名,

​	m1.NM AS 性别,

​	c.CLASS_NM AS 班级,

​	t.TEACHER_NM AS 班主任,

​	m.NM AS 教师等级,

​	a.CHINESE AS 语文,

​	a.MATHEMATICS AS 数学成绩,

​	a.ENGLISH AS 英语, 

​	a.CHINESE+a.MATHEMATICS+a.ENGLISH AS 总成绩,

​	(a.CHINESE+a.MATHEMATICS+a.ENGLISH)/3 AS 平均成绩,

​	TIMESTAMPDIFF(YEAR,s.BIRTHDAY,CURDATE()) AS 年龄,

CASE 

​	WHEN (a.CHINESE+a.ENGLISH+a.MATHEMATICS)/3 >= 60 THEN

​		'及格'

​	WHEN (a.CHINESE+a.ENGLISH+a.MATHEMATICS)/3 < 60 THEN

​		'不及格'

​	ELSE

​		''

END AS 是否及格

FROM t_student s

LEFT JOIN t_class c on s.STUDENT_ID = c.STUDENT_ID AND S.CLASSID = C.CLASS_ID

LEFT JOIN t_teacher t on t.TEACHER_ID = c.HEAD_MASTER

LEFT JOIN t_mast m on t.TEACHER_LEVEL = m.CD AND m.TYPE_CD = '02'

LEFT JOIN t_mast m1 on s.SEX = m1.CD AND m1.TYPE_CD = '01'

LEFT JOIN t_achievement a on a.STUDENT_ID = s.STUDENT_ID 

ORDER BY s.STUDENT_ID

– 查询学号SX0002和班级001的学生(UNION练习)

1
2
3
4
5
6
7
8
9
10
11
SELECT *

FROM t_student s 

WHERE s.STUDENT_ID = 'SX0002'

UNION ALL

SELECT *

FROM t_student s WHERE  S.CLASSID = '001'

– 查询三个班级的总成绩

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
SELECT

SUM(CASE 

​	WHEN c.CLASS_ID = '001'  THEN

​		IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0)

​	ELSE

​		0

END) AS 一年一班成绩总和,

SUM(CASE 

​	WHEN c.CLASS_ID = '002'  THEN

​		IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0)

​	ELSE

​		0

END) AS 一年二班成绩总和,

SUM(CASE 

​	WHEN c.CLASS_ID = '003'  THEN

​		IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0)

​	ELSE

​		0

END) AS 一年三班成绩总和,

SUM(CASE 

​	WHEN c.CLASS_ID = '004'  THEN

​		IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0)

​	ELSE

​		0

END) AS 一年四班成绩总和

 

FROM t_student s

LEFT JOIN t_class c on s.STUDENT_ID = c.STUDENT_ID AND S.CLASSID = C.CLASS_ID

LEFT JOIN t_achievement a on a.STUDENT_ID = s.STUDENT_ID 

ORDER BY s.STUDENT_ID

– 同上

1
2
3
4
5
6
7
8
9
10
11
SELECT c.CLASS_NM,SUM(IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0)) AS 总成绩

FROM t_student s

LEFT JOIN t_class c on s.STUDENT_ID = c.STUDENT_ID AND S.CLASSID = C.CLASS_ID

LEFT JOIN t_achievement a on a.STUDENT_ID = s.STUDENT_ID 

GROUP BY c.CLASS_NM

ORDER BY c.CLASS_ID

– 查询每个班的前三名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT s.STUDENT_ID,s.CLASSID,IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0) AS 总成绩

FROM t_student s

LEFT JOIN t_achievement a on a.STUDENT_ID = s.STUDENT_ID 

WHERE (SELECT count(*) 

​	FROM t_student s1 

​	LEFT JOIN t_achievement ac on ac.STUDENT_ID = s1.STUDENT_ID 

​	WHERE s.CLASSID = s1.CLASSID AND IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0) < IFNULL(ac.CHINESE,0)+IFNULL(ac.MATHEMATICS,0)+IFNULL(ac.ENGLISH,0)) < 3

ORDER BY CLASSID,IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0) DESC

– 同上

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
(SELECT s.STUDENT_NM,s.CLASSID,c.CLASS_NM,IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0) AS 总成绩

FROM t_student s

LEFT JOIN t_class c on s.STUDENT_ID = c.STUDENT_ID AND S.CLASSID = C.CLASS_ID

LEFT JOIN t_achievement a ON a.STUDENT_ID = s.STUDENT_ID

WHERE c.CLASS_NM = '一年一班'

ORDER BY 总成绩 DESC

LIMIT 3)

UNION

(SELECT s.STUDENT_NM,s.CLASSID,c.CLASS_NM,IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0) AS 总成绩

FROM t_student s

LEFT JOIN t_class c on s.STUDENT_ID = c.STUDENT_ID AND S.CLASSID = C.CLASS_ID

LEFT JOIN t_achievement a ON a.STUDENT_ID = s.STUDENT_ID

WHERE c.CLASS_NM = '一年二班'

ORDER BY 总成绩 DESC

LIMIT 3)

UNION

(SELECT s.STUDENT_NM,s.CLASSID,c.CLASS_NM,IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0) AS 总成绩

FROM t_student s

LEFT JOIN t_class c on s.STUDENT_ID = c.STUDENT_ID AND S.CLASSID = C.CLASS_ID

LEFT JOIN t_achievement a ON a.STUDENT_ID = s.STUDENT_ID

WHERE c.CLASS_NM = '一年三班'

ORDER BY 总成绩 DESC

LIMIT 3)

UNION

(SELECT s.STUDENT_NM,s.CLASSID,c.CLASS_NM,IFNULL(a.CHINESE,0)+IFNULL(a.MATHEMATICS,0)+IFNULL(a.ENGLISH,0) AS 总成绩

FROM t_student s

LEFT JOIN t_class c on s.STUDENT_ID = c.STUDENT_ID AND S.CLASSID = C.CLASS_ID

LEFT JOIN t_achievement a ON a.STUDENT_ID = s.STUDENT_ID

WHERE c.CLASS_NM = '一年四班'

ORDER BY 总成绩 DESC

LIMIT 3)

– 同理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
SELECT

​	t.STUDENT_ID,

​	IFNULL( ach.CHINESE, 0 ),

​	t.CLASSID 

FROM

​	t_achievement ach

​	LEFT JOIN t_student t ON ach.STUDENT_ID = t.STUDENT_ID 

WHERE

​	EXISTS (

​	SELECT

​		COUNT( * ) 

​	FROM

​		t_achievement ach1

​		LEFT JOIN t_student t1 ON ach1.STUDENT_ID = t1.STUDENT_ID 

​	WHERE

​		t.CLASSID = t1.CLASSID 

​		AND IFNULL( ach.CHINESE, 0 ) <= IFNULL( ach1.CHINESE, 0 )  

​	GROUP BY

​		t.CLASSID,

​		ach.CHINESE 

​	HAVING

​		COUNT( * ) <= 3 

​	) 

ORDER BY

t.CLASSID,

IFNULL( ach.CHINESE, 0 ) DESC;

–同理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
SET @rown := 0,

 @classid = '';

SELECT

​	*

FROM

​	(

​		SELECT

​			@rown := CASE

​		WHEN @classid = aa.CLASS_ID THEN

​			@rown + 1

​		ELSE

​			1

​		END AS num,

​		@classid := aa.CLASS_ID AS CLASS_ID11,

​		aa.STUDENT_ID,

​		aa.CHINESE,

​		aa.CLASS_ID

​	FROM

​		(

​			SELECT

​				t.STUDENT_ID,

​				a.CHINESE,

​				c.CLASS_ID

​			FROM

​				t_student t

​			INNER JOIN t_achievement a ON t.STUDENT_ID = a.STUDENT_ID

​			INNER JOIN t_class c ON t.CLASSID = c.CLASS_ID

​			AND c.STUDENT_ID = t.STUDENT_ID

​			ORDER BY

​				c.CLASS_ID,

​				a.CHINESE DESC

​		) aa

​	) bb

WHERE

​	bb.num < 4

–判断如果是11位手机号则输出,座机号位8位的前面加’’0411-‘，不足8位补全加“0411-”

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT s.STUDENT_ID,s.STUDENT_NM,

CASE 

​	WHEN LENGTH(MOBILE_PHONE) = 11 THEN -- 搜索字节长度为11的字段

​		MOBILE_PHONE

​	WHEN MID(s.FIXED_TELEPHONE,1,4) = '0411'THEN

​		s.FIXED_TELEPHONE

​	ELSE

​		CONCAT('0411','-',LPAD(s.FIXED_TELEPHONE,8,'0')) -- 补齐位数

END  AS PHONE

FROM t_student s’

–查询当前3时间，增加两天，两月，减少两天，和时间段的间隔

1
2
3
4
5
6
7
8
9
SELECT DATE_FORMAT(NOW(),'%Y/%m/%d%T') AS 当前时间,

DATE_ADD(NOW(),INTERVAL 2 DAY) AS 增加两天,

DATE_ADD(NOW(),INTERVAL 2 MONTH) AS 增加两月,

DATE_SUB(NOW(),INTERVAL 2 DAY) AS 减少两天,

DATEDIFF(DATE_ADD(NOW(),INTERVAL 2 DAY),DATE_SUB(NOW(),INTERVAL 2 DAY)) AS 间隔时间

–取出班主任老师所属学生的英语成绩

1
2
3
4
5
6
7
8
9
SELECT t.TEACHER_NM,s.STUDENT_NM,a.ENGLISH

FROM t_student s

LEFT JOIN t_achievement a ON a.STUDENT_ID = s.STUDENT_ID 

LEFT JOIN t_class c ON s.STUDENT_ID = c.STUDENT_ID AND S.CLASSID = C.CLASS_ID

LEFT JOIN t_teacher t ON t.TEACHER_ID = c.HEAD_MASTER

–横向转纵向查询男女和职称（max是把那一列最大的排在前面，例如一个数列里面有1 和空格，那样就取1）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT

​	m.TYPE_CD,

​	MAX( CASE WHEN m.CD = '1' THEN m.CD ELSE '' END ),

​	MAX( CASE WHEN m.CD = '1' THEN m.NM ELSE '' END ),

​	MAX( CASE WHEN m.CD = '2' THEN m.CD ELSE '' END ),

​	MAX( CASE WHEN m.CD = '2' THEN m.NM ELSE '' END ),

​	MAX( CASE WHEN m.CD = '3' THEN m.CD ELSE '' END ),

​	MAX( CASE WHEN m.CD = '3' THEN m.NM ELSE '' END ) 

FROM

​	t_mast m 

GROUP BY

​	m.TYPE_CD

–给每个同学的数学成绩+5 超过100的显示100

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
SELECT

​	s.STUDENT_NM,

​	IFNULL(a.MATHEMATICS,0),

​	CASE 

​	WHEN IFNULL(a.MATHEMATICS,0) < 95 THEN

​		IFNULL(a.MATHEMATICS,0) + 5

​	ELSE

​		'100'

END 

 

FROM

​	t_student s

​	LEFT JOIN t_achievement a ON a.STUDENT_ID = s.STUDENT_ID

​	LEFT JOIN t_class c ON s.STUDENT_ID = c.STUDENT_ID 

​	AND S.CLASSID = C.CLASS_ID

–查询学生的性别以及班主任和班主任的教学等级

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
SELECT

​	s.STUDENT_ID,

​	s.STUDENT_NM,

​	( SELECT NM FROM t_mast m WHERE s.SEX = m.CD AND m.TYPE_CD = '01' ),

​	(

SELECT

​	t.TEACHER_NM 

FROM

​	t_teacher t,

​	t_class c

WHERE

​	t.TEACHER_ID = c.HEAD_MASTER 

​	AND s.STUDENT_ID = c.STUDENT_ID 

​	AND S.CLASSID = C.CLASS_ID

​	),

​	(

SELECT

​	NM 

FROM

​	t_teacher t,

​	t_class c,

​	t_mast m1 

WHERE

​	t.TEACHER_ID = c.HEAD_MASTER 

​	AND s.STUDENT_ID = c.STUDENT_ID 

​	AND S.CLASSID = C.CLASS_ID 

​	AND s.SEX = m1.CD 

​	AND m1.TYPE_CD = '02' 

​	) 

FROM

​	t_student S

Oracle

–查询每个班的前三名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
SELECT

​	* 

FROM

​	(

SELECT

​	s.student_id,

​	s.student_nm,

​	s.classid,

​	c.class_nm,

​	SUM( NVL ( a.achievement, 0 ) ),

​	( Row_number ( ) OVER ( PARTITION BY s.classid 

​	ORDER BY SUM( NVL ( a.achievement, 0 ) ) DESC ) ) rn 

FROM

​	t_student s

​	LEFT JOIN t_achievement a ON a.student_id = s.student_id

​	LEFT JOIN t_class c ON s.classid = c.class_id 

​	AND s.student_id = c.student_id 

GROUP BY

​	s.student_id,

​	s.student_nm,

​	s.classid,

​	c.class_nm 

ORDER BY

​	s.classid 

​	) 

WHERE

​	rn < 4

–查询学号,姓名,总成绩

1
2
3
4
5
6
7
8
9
select s.STUDENT_ID,s.STUDENT_NM,sum(a.ACHIEVEMENT)

from t_student s

left join t_achievement a on s.STUDENT_ID = a.STUDENT_ID

group by s.STUDENT_ID,s.STUDENT_NM

order by s.STUDENT_ID

–查询各个科目的成绩

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
select 

​	s.STUDENT_ID,s.STUDENT_NM,

​	(select m.NM from t_mast m where m.TYPE_CD = 3 and m.CD = 1),

​	--max(case when a.COURSE = 1 then m.NM END),

​	max(case when a.COURSE = '1' then a.ACHIEVEMENT ELSE 0 END),

​	(select m.NM from t_mast m where m.TYPE_CD = 3 and m.CD = 2),

​	max(case when a.COURSE = '2' then a.ACHIEVEMENT ELSE 0 END),

​	(select m.NM from t_mast m where m.TYPE_CD = 3 and m.CD = 3),

​	max(case when a.COURSE = '3' then a.ACHIEVEMENT ELSE 0 END)

from t_student s

left join t_achievement a on s.STUDENT_ID = a.STUDENT_ID

left join t_mast m on m.CD = a.COURSE and m.TYPE_CD = '03'

group by s.STUDENT_id,s.STUDENT_NM

order by s.STUDENT_id

–同上的子查询方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
select 

​	s.STUDENT_ID,

​	s.STUDENT_NM,

​	max((select m.NM from t_mast m where m.TYPE_CD = 3 and m.CD = 1)),

​	max((select a.ACHIEVEMENT from t_mast m where m.CD = a.COURSE and s.STUDENT_ID = a.STUDENT_ID AND m.TYPE_CD = '03' and m.CD = '1')),

​	max((select m.NM from t_mast m where m.TYPE_CD = 3 and m.CD = 2)),

​	max((select a.ACHIEVEMENT from t_mast m where m.CD = a.COURSE and s.STUDENT_ID = a.STUDENT_ID AND m.TYPE_CD = '03' and m.CD = '2')),

​	max((select m.NM from t_mast m where m.TYPE_CD = 3 and m.CD = 3)),

​	max((select a.ACHIEVEMENT from t_mast m where m.CD = a.COURSE and s.STUDENT_ID = a.STUDENT_ID AND m.TYPE_CD = '03' and m.CD = '3'))

from t_student s

left join t_achievement a on s.STUDENT_ID = a.STUDENT_ID

left join t_mast m on m.CD = a.COURSE and m.TYPE_CD = '03'

group by s.STUDENT_id,s.STUDENT_NM

order by s.STUDENT_id

–Oracle查询时间大全

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select to_char(sysdate, 'yyyy') as 年,

​       to_char(sysdate, 'q') as 季度,

​       to_char(sysdate, 'MM') as 月,

​       to_char(sysdate, 'dd') as 天,

​       to_char(sysdate, 'iw') as 日,

​			 to_char(sysdate,'yy-mm-dd hh24:mm:ss') as 当前time,

​			 trunc(sysdate-1) as 前一天,

​			 trunc(sysdate,'d') as 当前星期的第一天,

​			 trunc(add_months(sysdate,-1),'mm') as 上个月第一天,

​			 add_months(trunc(sysdate),-1) as 前一个月,

​			 add_months(trunc(sysdate),1) as 后一个月,

​			 to_char(last_day(sysdate),'yy-mm-dd hh24:mm:ss') as 最后一天

​		from dual;

– 在输入的时候能查询**，不输入的时候输出所有

1
2
3
4
5
6
7
8
9
10
11
12
13
select *

  from t_student s

 where s.student_id =  case when &sid is null then

​    s.student_id

   else

​    &sid

 end

– 同上

1
2
3
4
5
select *

  from t_student s

 where s.student_id = decode(&sid, null, s.student_id, &sid)

当LEFT JOIN中的条件中存在WHERE的时候，外连接会默认变成内连接

TheMrxk个人博客 wechat
欢迎您扫一扫上面的本人微信号，获取更多资源！