sqlserver中子查询

/*
子查询(一个查询的结果作为另外一个查询的条件
一般子查询要保证返回结果只有一个(即一行,一列)in子查询返回一列多行
子查询从内往外读(写)
当要显示多个表数据时使用联结，只显示一个表数据时使用子查询
)
*/
SELECT * FROM student WHERE 
stuAge > (SELECT stuAge FROM student WHERE stuName = '李四')
 
--
SELECT 
* 
FROM 
student stu
INNER JOIN
exam exa
ON
(stu.stuId = exa.stuId)
WHERE
exa.writtenAxam > 95
--
SELECT 
stu.*, exa.*
FROM 
student AS stu, exam exa
WHERE
stu.stuId IN (SELECT stuId FROM exam WHERE exa.writtenAxam > 95)
AND
stu.stuId = exa.stuId
--
SELECT 
*
FROM 
student
WHERE
stuId IN (SELECT stuId FROM exam WHERE writtenAxam > 95)
--
SELECT 
*
FROM 
student
WHERE
stuId NOT IN (SELECT stuId FROM exam WHERE writtenAxam > 95)
--
IF EXISTS(SELECT * FROM student)
PRINT '有数据'
ELSE
PRINT '无数据'
--
IF EXISTS(SELECT * FROM student WHERE stuId = '199')
PRINT '有数据'
ELSE
PRINT '无数据'
 
--取反NOT EXISTS
IF NOT EXISTS(SELECT * FROM student WHERE stuId = '199')
PRINT '有数据'
ELSE
PRINT '无数据'
 
--第一种写法
IF EXISTS(SELECT * FROM exam WHERE writtenAxam >= 80)
	UPDATE exam SET writtenAxam = writtenAxam + 2 
ELSE
	UPDATE exam SET writtenAxam = writtenAxam + 5 
	
--第二种写法
DECLARE @score INT
SET @score = 5
IF EXISTS(SELECT * FROM exam WHERE writtenAxam >= 80)
	SET @score = 2
	UPDATE exam SET writtenAxam = writtenAxam + @score 
--取常量列的2种方式如下	
SELECT *, '是否通过' = '是' FROM student
SELECT *, 是否通过 = '是' FROM student
SELECT *, '是' AS '是否通过' FROM student
SELECT *, '是' AS 是否通过 FROM student
SELECT *, '是' '是否通过' FROM student --as关键字也可以省略
--
SELECT 
	* ,
	'等级' = 
	CASE
		WHEN labExam >= 90 THEN '优秀'
		WHEN labExam >= 80 AND labExam <= 90 THEN '及格'
		ELSE '不及格'
	END
FROM exam
--
SELECT *, '计算结果' = 2 / 5 FROM student
SELECT *, '计算结果' = (2 * 1.0) / (5 * 1.0) FROM student