首页 > 代码库 > 3.sql进阶文档(知识点)

3.sql进阶文档(知识点)

CASE

在数据库中,对数据进行比对,

两种用法

第一种,不对任何列进行case 条件判断

SELECT

Sid,English AS en,

CASE

WHEN English >=60 THEN ‘及格‘

WHEN English <60 THEN ‘不及格‘

END AS jige

FROM dbo.Score

从case开始:开始判断,判断什么?不知道,,当when开始的时候才知道判断什么东西

?

如果在判断一个列的值的时候,满足多个条件(when),就先满足谁就执行谁

类洗浴c#中的 if else if else if else if else if else if else if.........else

SELECT

Sid,English AS en,

CASE

WHEN English >60 THEN ‘英语及格‘

WHEN Math >60 THEN ‘数学及格‘

ELSE ‘都不及格‘

END AS jige

FROM dbo.Score

?

?

?

第二种 直接case列 值判断

?

SELECT

CASE ptId

WHEN 1 THEN ‘家人‘

WHEN 2 THEN ‘同事‘

WHEN 3 THEN ‘同学‘

END

FROM dbo.PhoneType

从case开始,一开是就知道要判断ptid的值,如果这个值是1就输出"家人"

?

相当于c#中的switch(age) case

?

例1:

SELECT id,Name,

(CASE Gender

WHEN 0 THEN ‘女‘

WHEN 1 THEN ‘男‘

END) AS Gender,Salary

FROM dbo.Teacher

在结果集中,可以包含两个列,列名完全相同的,

?

在Ado中,如果我们习惯了用列名来取数据,这样就会出问题了,

最终,会取出哪个数据呢?

?

例2:

SELECT

Sid,English AS en,

CASE

WHEN English >60 THEN ‘及格‘

WHEN English <60 THEN English

END AS jige

FROM dbo.Score

说明:多个then后面的所有数据类型都必须相同

SELECT

Sid,English AS en,

CASE

WHEN English >60 THEN ‘及格‘

WHEN English <60 THEN CAST(English AS NVARCHAR(10))

END AS jige

FROM dbo.Score

如果多个then后面的数据类型不相同可以做数据类型转换

?

子查询

在一个查询中,有另外一个查询,这个查询就叫做主查询的子查询

?

?

SELECT * FROM dbo.Student WHERE CId = (SELECT id FROM dbo.Classes WHERE Name=‘四期班‘)

把小括号里面的查询的结果给cid,然后主查询继续执行

?

SELECT * FROM dbo.Student WHERE CId = (SELECT * FROM dbo.Classes WHERE Name=‘四期班‘)

Error:在选择列表中只能指定一个表达式。

小括号内的查询返回的是多个列,然后cid去看一下,就不知道到底是用哪一个列,然后就报错了

?

一般子查询都是用在where条件后面的,

?

--要查出所有九期班以后的所有学员

--1.先查出所有九期班以后的班级,只取id

--2.根据id查找所有符合条件的学员

SELECT * FROM dbo.Student WHERE CId IN (

SELECT id FROM dbo.Classes WHERE id>(

SELECT id FROM dbo.Classes WHERE Name =‘九期班‘

)

)

?

只有子查询返回的结果集是一行一列的才能当做值来用,才能用等值判断

?

In 是判断一个值,是否在后面的所有值里面出现过,

Not in是判断一个值,是否在后面的所有值里面没有出现过,

?

?

EXISTS

SELECT * FROM dbo.Student WHERE EXISTS (SELECT * FROM dbo.Classes WHERE dbo.Classes.id=dbo.Student.CId)

?

SELECT * FROM dbo.PhoneNum WHERE EXISTS ( SELECT ‘xxxx‘ FROM dbo.PhoneType WHERE dbo.PhoneNum.pTypeId=dbo.PhoneType.ptId )

?

EXISTS是引用了主查询的数据进行子查询的一种方式,子查询中,只做条件判断,不做任何列的输出,

?

?

分页查询

AS t 可以吧结果集当成一张表,t就是这张表的表名,我们可以对这张表做任何查询操作

?

SELECT * FROM

(SELECT TOP 10 * FROM dbo.Area WHERE ar_id NOT IN (SELECT TOP 10 ar_id FROM dbo.Area) ORDER BY dbo.Area.ar_id ASC)

AS t ORDER BY t.ar_id DESC

?

--第二种分页方式

SELECT * FROM

(SELECT *,ROW_NUMBER() OVER(ORDER BY ar_id) AS nrows FROM dbo.Area) AS t

WHERE t.nrows BETWEEN 11 AND 20

ROW_NUMBER()在结果集的基础上加上了一列,这一列从1开始,中间不会有间断的

然后配合between and使用,效率比较高

?

JOIN

把两张表或多张表的数据行,按照指定的规则连接在一起,

--1:查询所有学生的姓名、年龄及所在班级

SELECT dbo.Student.Name,Age,dbo.Classes.Name FROM dbo.Student

JOIN dbo.Classes ON dbo.Classes.id=Student.CId

Join 连击多张表的数据时,会筛选要满足所有连接条件的数据才会留下来,其中任何一个条件不满足都不会出现数据

LEFT JOIN

--4:查询所有学生(参加及未参加考试的都算)及成绩

SELECT * FROM dbo.Student

LEFT JOIN dbo.Score ON dbo.Student.id =dbo.Score.Sid

在普通的join基础上,优先显示左边的表的数据,如果能满足连接条件,就显示连接之后的右表的数据,如果不满足连接条件,还是会显示左表数据,右表数据部分就是null(不知道)

?

RIGHT JOIN

在普通join的基础上,优先显示右表的数据,如连接条件满足,就显示左表的数据,如果不满足就显示为null

--5:请查询出所有没有参加考试

SELECT * FROM dbo.Student WHERE

id NOT IN (

SELECT dbo.Student.id

FROM dbo.Student

RIGHT JOIN dbo.Score ON dbo.Student.id = dbo.Score.Sid

WHERE dbo.Student.id IS NOT NULL

)

Join配合子查询

?

?

INNER JOIN

其实就是join,我们在用join的时候,系统就默认识别为innerjoin了

?

CROSS JOIN

会得出两张表的乘积行

?

?

视图

视图其实就是一个虚拟表,这张虚拟表的数据,其实是来自于一个查询,这个查询查询了其他一个或者多个表的数据,

当我们查询一个视图的时候,其实就是在查询"视图的查询的结果集",这个结果集里面有什么列,就只能显示什么列,

?

可以做修改

?

--用sql语句创建一个视图

CREATE VIEW view_phone

AS

SELECT pId,pName,pCellPhone,pHomePhone,ptName FROM dbo.PhoneNum

JOIN dbo.PhoneType ON dbo.PhoneNum.pTypeId = dbo.PhoneType.ptId

?

SELECT * FROM View_Phone

INSERT INTO View_Phone VALUES(‘貂蝉‘,‘13546869526‘,‘010-5264879‘,‘同事‘)

Insert语句报错:视图或函数 ‘View_Phone‘ 不可更新,因为修改会影响多个基表。

Insert语句在不会影响多张表的前提下,可以用的,(只影响一张表)

?

在做增改的时候,如果没有视图没有吧原始表,不能为空的字段显示出来,那么,新增是不成功的,

?

修改的时候,

UPDATE View_Phone SET pName=‘张三‘ WHERE pId=15

这样可以,因为,视图显示了pName字段,我们也只修改了一个字段,也就是一张表

UPDATE View_Phone SET pName=‘张三‘,ptName=‘朋友‘ WHERE pId=15

这样是错误的,因为修还了两张表的数据

?

?

视图只用来做查询,一次增改操作只能针对一张表,新增进去的数据,还不能违的反原始表约束

?

删除是一定会影响多多张表的数据,所以是绝对不可以的

?

索引:

  1. 模糊查询不能在参数位置添加%....
  2. 非聚集索引:相反
  3. 唯一索引:唯一约束?!

?

临时表

4.局部临时表--#--,类似局部变量的作用域。在连接断开,或者存储过程调用完后删除。

5.全局临时表--##--,作用域

6.临时数据表存在tempdb数据库

?

?

3.sql进阶文档(知识点)