SQL Server数据库学习

1、什么是数据库？数据库特点？

数据库就是存放数据的仓库

海量存储、查找速度快、并发性问题控制、安全性、数据完整性(保存在数据库中的数据是正确的，真是的)

2、主键的作用？选择主键的注意事项？

唯一标识表中的一条记录

尽量选择单列作为主键：

1>必须唯一（不能有重复）

2>该列不能为空值

3>比较稳定的列（不经常更新的，最好是建好以后再也不更新）

4>选择主键列的时候，推荐使用“逻辑主键”(例如：自动编号、guid等)，不推荐“业务主键”（选择有实际意义的列作为主键（例如：身份证号，工号、学号等。））

3、下表，分析为什么要有外键？

数据冗余-数据重复出现，占用空间多，想修改厂家信息得修改很多行，每次录入新的货物的话必须把厂家地址、厂家电话等信息重新录入一次。

使用外键：可以降低数据冗余量~不用太多字符串占硬盘空间~之间用整形数据代表编号就行~

假设两张表中都有主键，A表，B表。A表中的主键为AId。B表中的主键为Bid.

这时，A表中引用了B表中的Bid作为一列，这时，我们就叫A表为B表的外键表，而B表叫做主键表。

当创建了主外键关系后，如果在外键表中有任何记录引用了主键表中的某条记录，则在主键表中不能删除该记录。同时主键表也不能删除。

4、登陆数据库的方式及区别是什么？

用户名验证：通过用户名和密码登陆，在互联网使用较多

Windows身份验证：当前操作系统的身份来验证，一般在局域网访问系统使用

5、、char和varchar的区别是什么？最长长度8000

英文和数字都是占一个字节，其它字符占两个字节（中文）

a)Char声明无论多少字节，它都会自动补齐，空格补齐（声明4，输入22，则会有两个空格补齐）~

不可变的~就是说声明多少输入少了它依然补齐声明存放的大小

b)Varchar声明多少字节，存多少数据就是多少，不会空格补齐~

可变的~就是说声明多少就存多少~

6、nchar和nvchar区别是什么？最长长度4000

nchar（4）：Unicode编码~实际一个字符（任何字符）要两个字节存固定长度

nvchar（4）Unicode编码~实际一个字符（任何字符）要两个字节存可变长度

7、len函数和datalength函数返回的是什么？

字符数字节数

select len(‘a’),datalength(‘a’) 输出都是1

select len(‘没’),datalength(‘没’) 输出1,2

8、创建数据库的关键字是什么？

----创建数据库数据库名称

CREATE DATABASE GuangzhouBlog

ON PRIMARY --默认就属于PRIMARY主文件组，可省略

(

NAME='GuangzhouBlog', --主数据文件的逻辑名

FILENAME='E:\Itcast\广州\广州Dotnet一期\db\GuangzhouBlog_data.mdf', --主数据文件的物理名

SIZE=3mb, --主数据文件初始大小

MAXSIZE=10mb, --主数据文件最大的值

FILEGROWTH=15% --主数据文件的增长率

)

LOG ON-- 日志文件

(

NAME='GuangzhouBlog_Log',

FILENAME='E:\Itcast\广州\广州Dotnet一期\db\GuangzhouBlog_log.ldf',

SIZE=3mb, --日志文件初始大小

MaxSize=20mb,

FILEGROWTH=1MB

)

9、怎么指定一个数据库来创建数据表？

USE GuangzhouBlog -- 设置当前使用的数据库,即数据表要创建到哪个数据库

--创建表表名

CREATE TABLE Score

(

ScoreId INT IDENTITY(1,1),

SId INT NOT NULL ,

English INT NOT NULL,

Math INT NOT NULL

)

10、bit数据类型在sql语句和设计器里面分别用什么表示？

bit数据类型在写SQL语句时，用1和0表示，设计器里面就是用true和false表示

11、插入数据的sql语句是什么？

--a.不指定列，直接新增所有列的值

INSERT INTO Classes VALUES ('黑豹训练营一期',38,getdate(),0)

--b.指定列新增 insert into 表(列值1，列值2…列值n) values()

INSERT INTO Classes(className,count) VALUES ('黑豹训练营二期',138)

--c.insert into talbe select from table

从自己表里复制一些到自己表 insert into 数据表（列值1，列值2….列值n） select 列值1，列值2….列值n 数据表

INSERT INTO Classes(className,count) SELECT classname,count FROM classes

--c.2INTO select （前提是自己先建好Classes2表，列名相同） insert into 目标表（列值1，列值2….列值n） select 列值1，列值2….列值n 数据源表

-- 从Classes复制一些数据到Classes2表

-- 先执行后面的select语句，获得一个结果集（两列），然后循环结果集里的行，每次取出行里的两个值，

-- 然后再执行insert 语句，生成values语法，并将循环到的两个值赋进去，最后，完成新增

INSERT INTO Classes2(className,count) --values('班级名',40)

SELECT classname,count FROM classes

--c.3 insert select 常量union

INSERT INTO Score (SId,English,Math)

SELECT 1,10,99 UNION

SELECT 2,66,77 UNION

SELECT 2,66,88 UNION

SELECT 1,77,54 UNION

SELECT 1,88,99

--d.SELECT INTO

复制一张表(是在复制的同时创建一张新表) select 列值1，列值2，…..列值n into 新表 from 数据源表

SELECT id,classname,count,adddate,isdel --(指定从数据源表中查询列出来，然后直接作为新表的列名)

INTO Classes4 -- 这个是新表的名称，此时表并不存在

FROM classes -- 数据源表

12、更新数据表的sql语句是什么？

update 表名称 set 要改变的列值 where 修改的条件

--1.普通更新

UPDATE classes SET count=45 WHERE id=3--将id=3的行的count列的值设置成45

--2.更新多个列

UPDATE classes SET count=55,className='黑豹训练营二.五期' WHERE id=3

--3.更新多行

UPDATE classes SET COUNT=COUNT+2 WHERE count<50

--4.或or

UPDATE classes SET COUNT=COUNT+10 WHERE count=55 OR count<50

--5.与and

UPDATE classes SET COUNT=COUNT+2 WHERE count=56 and className='广州传智播客.Net训练营一期'

13、删除数据表的sql语句是什么？

--1.删除整个表的数据

DELETE FROM classes4 --DELETE classes4

--2.删除满足条件的数据

DELETE FROM classes3 WHERE COUNT<50 --and or

--3.删除多条ID记录

DELETE FROM classes WHERE id=4 OR id=5 OR id=6

DELETE FROM classes WHERE id in(4,5,6)

--4.TRUNCATE 能够清空表数据，并且重置主键ID使ID从1开始，而且还不会为每行删除的记录生成删除的日志，而仅仅生成一条日志，不会触发删除触发器

TRUNCATE TABLE classes2

--DELETE TABLE classes2

truncate只能操作表，不能操作数据库

14、查询数据表的sql语句是什么？

--1.查询表里所有的行和列数据*代表查询所有列

-- （尽量不要使用*，因为会先到系统表里去查询classes表里的所有列名，然后再去查询classes表列数据）

SELECT * FROM classes

SELECT id,classname,count,adddate,isdel FROM classes--尽量使用指定列名的查询方式

--2.为列取别名

SELECT id AS 编号,classname 班级名,count,adddate,isdel FROM classes

--3.通过where 来筛选查询结果集的行

SELECT * FROM classes WHERE [count]>50

--4.直接使用select 显示常量和执行表达式

SELECT 1,'我爱北京天安门'

SELECT 1+1

SELECT getdate()

--5.使用top选择筛选行

SELECT * FROM classes

--5.1获得前几行

SELECT TOP 3 * FROM classes

--获得人数最多的前三个班

SELECT TOP 3 * FROM Classes ORDER BY [count] DESC --降序排列 asc 升序排列

--5.2根据百分比来取行

SELECT TOP 50 PERCENT * FROM Classes ORDER BY [count]

--6.distinct去除重复项,先把数据表里的行都获取，然后把列逐个的取出放入结果集，在放之前，先检查结果集中是否已存在相同值，如果有，就不添加

SELECT DISTINCT [count] FROM Classes

SELECT DISTINCT [count],classname FROM Classes

15、有哪些聚合函数？

--1.Max求最大值

SELECT * FROM classes

SELECT max(id) from classes

--2.Sum求和

SELECT sum(id) from classes

--3.Min 求最小值

SELECT min(id) from classes

--4.Count 求行数

SELECT COUNT(id) from classes

--5.求平均数

SELECT avg(id) from classes

SELECT sum(id)/COUNT(id) from classes

16、类型转换

--1.Cast

SELECT * FROM classes

SELECT cast('2012-08-04' AS DATETIME) + 1

--SELECT '2012-08-04' + 1

--2.Convert

SELECT convert(DATETIME,'2012-08-04')+1

SELECT cast(adddate AS VARCHAR(10)) FROM Classes c

17、查询

--查询----------------------------------------------

--1.Between and

SELECT * FROM Students

SELECT * FROM Students s WHERE s.age BETWEEN 21 AND 26

--2. in

SELECT * FROM Students s WHERE age in(20,21,22)

SELECT * FROM Students s WHERE s.age IN (SELECT age FROM Students s WHERE gender=1)

18、什么是数据库约束？数据库约束有哪些？

数据库约束：是为了保证数据的完整性(正确性)而实现的一套机制

非空约束：设计器里面的表格数据不允许为空

主键约束：设置主键，唯一约束，值不能重复

唯一约束：设计器选定表格变量右键，索引键，添加一个列名，选择列名，选择是唯一

默认约束：找到某列的列属性有默认值绑定，添加默认值

检查约束：设计器变量右键添加Check约束，添加年龄一个表达式age>0 and age<100

外键约束：就是主外键，右键变量关系，删除一个关系，添加新的关系，选择主外键

19、什么是级联删除？

设置级联删除，能够将两个表中的主外键联系的数据删掉，如果没有设置级联删除，那么删除主外键有关系的id就不能删除

表中设计器打开-右键变量-关系=删除规则-级联

20、模糊查询的关键字是什么？4个通配符的含义是什么？

LIKE 关键字

a)% 包含零个或多个字符的任意字符串。

b)_ 任何单个字符。

c)[ ] 指定范围（例如 [a-f]）或集合（例如 [abcdef]）内的任何单个字符。

d)[^] 不在指定范围（例如 [^a - f]）或集合（例如 [^abcdef]）内的任何单个字符。请将通配符和字符串用单引号引起来

如：把所有姓王的人查出来

-- %代表任意长度的任意字符

SELECT * FROM Students WHERE NAME LIKE '王%'

如：取姓王的，有两个、三个字符

-- _代表长度的任意字符

SELECT * FROM Students WHERE NAME LIKE '王_'

SELECT * FROM Students WHERE NAME LIKE '王_ _'

如：取一位、两位数，[]范围只能是[0-9]或[a-z]

-- []代表长度的[范围]内字符

SELECT * FROM Students WHERE id LIKE '[0-9]' 只能查出一位数

SELECT * FROM Students WHERE id LIKE '[0-9][0-9]' 能查两位数

SELECT * FROM Students WHERE name LIKE '[a-z]'

SELECT * FROM Students WHERE name LIKE '[a-z][a-z]'

-- ^ 代表取反，常结合[范围]通配符使用（只有MSSQLSERVER支持）

SELECT * FROM Students WHERE name LIKE '[^a-b]' --查询名字为一个字符，但不在a-b之间。

-- not 取反几乎所有数据库都支持

SELECT * FROM Students WHERE NAME not LIKE '[a-b]' --查询不是单个的a-b之间的字符的所有名字

21、数据库中的null表示什么？怎么进行空值判断？

数据库中的null表示“不知道”，而不是表示没有。因此select null+1结果是null，因为“不知道”加1的结果还是“不知道”

is null、is not null

SELECT * FROM Students WHERE cid is NULL

SELECT * FROM Students WHERE cid IS NOT NULL

--isnull函数isnull(值，为空默认值)--------------------------------

SELECT isnull(null,'默认值')

SELECT id,isnull(cid,0),name,age,gender,birthdate,regdate,isdel FROM Students

22、数据排序的关键字是什么？一般放在语句的什么位置？

order by 一般要放到所有语句的后面

--order by 排序(升序-asc 降序-desc)---------------------------------

SELECT * FROM Students ORDER BY id--默认为asc 升序排列

SELECT * FROM Students ORDER BY id DESC --倒序排列

--按照字符排序

SELECT * FROM students ORDER BY NAME --如果排序列的值是字符类型，则按照字母的先后次序排列，若果有中文，则按照中文的拼音字母排列

--按照多个列排序

SELECT * FROM students ORDER BY name,age--当name相等时，按照年龄升序排列

SELECT * FROM students ORDER BY name,age DESC --当name相等时，按照年龄降序排列

23、数据分组的关键字是什么？必须放在语句的什么位置？

group by必须放到WHERE语句之后

--分组（结果集不再是表数据，而是分组的组数据，每一行代表一个组）--------

--SELECT * FROM students GROUP BY cid

--再次强调：分组的结果集是组数据

SELECT cid,count(id) AS 人数 FROM students GROUP BY cid

--分组查询的结果集里的列，必须是group by 的列名或者用聚合函数求的的列值

SELECT age,count(id) AS 人数,max(id) as 每个组id最大的值 FROM students GROUP BY age

--分组:最后一次强调，查询的结果是组信息，不是表信息。组信息是程序员自己求出来的！！！

-- 也就是说，查询的列，必须是组的聚合（行数，最大值，最小值，平均值）信息。

--按照多个条件分组(就可以看成是按照多个列的组合值来分组--也就是看组合值是否有相同的，如果有，就算成一个组)

--按照年龄和性别分组

SELECT age,gender FROM students GROUP BY age,gender

SELECT * FROM students

--按照班级和年龄分组

SELECT cid FROM students GROUP BY cid

SELECT cid,age FROM students GROUP BY cid,age

--先查询满足条件的人，再分组一般不用这个方法！！！

--SELECT age FROM students WHERE age>22 GROUP BY age

24、having和where的区别是什么？

where是先查询满足条件，再分组；having是先分组再筛选

a)注意Having中不能使用未参与分组的列，Having不能替代where。作用不一样，Having是对组进行过滤。

b)Having 是Group By的条件对分组后的数据进行筛选（与Where类似，都是筛选，只不过having是用来筛选分组后的组的。）

c)在Where中不能使用聚合函数，必须使用Having，Having要位于Group By之后。

d)Having的使用几乎是与where一样的，也可以用in。

Having count(*) in (5,8,10)

--having 分组之后筛选条件

--先分组，然后再筛选满足条件的组

SELECT age,count(id)AS 总人数 FROM students GROUP BY age HAVING age>22 and COUNT(id) >3

--注意：常见错误

--此举报错，因为having是在select之前执行，也就是说执行having的时候压根儿就没有总人数这个列！

--SELECT age,count(id)AS 总人数FROM students GROUP BY age HAVING age>22 and 总人数>3

SELECT age,count(id)AS 总人数 FROM students GROUP BY age HAVING age>22 and COUNT(id) >3 order by 总人数 desc

25、SQL语句的执行顺序

5>…Select 5-1>选择列,5-2>distinct,5-3>top

1>…From 表

2>…Where 条件

3>…Group by 列

4>…Having 对组来做筛选条件

6>…Order by 列

26、类型转换

--类型转换

SELECT '您的班级编号'+ 1 报错！！！

SELECT convert(INT,'123')+ 1 不会报错！！！因为转成的是数字！！！

SELECT cast('123' as int)+ 1

--补充截取方法

SELECT left('abcd',2)

SELECT right('abcd',2)

--convert来转日期

SELECT getdate()

SELECT CONVERT(VARCHAR,GETDATE(),110)

SELECT CONVERT(VARCHAR,GETDATE(),102)

SELECT CONVERT(VARCHAR,GETDATE(),113)

27、Union集合运算符的基本原则是什么？

每个结果集必须有相同的列数；每个结果集的列必须类型相容

c)联合：将多个结果集合并成一个结果集。union(去除重复，相当于默认应用了distinct)、union all

d)Union因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，那么就用UNION ALL

--Union联合结果集

--联合结果集时要注意列的数量和数据类型必须一致或兼容

--union会自动合并相同数据行

SELECT * FROM Classes 有7条内容

UNION

SELECT * FROM Classes2 有1条内容

输出显示为合并成8条

--union all 联合结果集，不会去除重复行

SELECT * FROM Classes

UNION ALL

SELECT * FROM Classes2

SELECT * FROM students

UNION

sELECT NULL,NULL,'年龄统计',sum(age),null,null,null,null FROM students

--Union因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，那么就用UNION ALL

SELECT [sid]
      ,[name]
      ,[age]
      ,[sex]
      ,[address]
  FROM [TestDB].[dbo].[UserInfo]
  UNION ALL
SELECT [sid]
      ,[name]
      ,[age]
      ,[sex]
      ,[address]
  FROM [TestDB].[dbo].[UserInfoTest]

View Code

28、len和datalength求出的是什么？

len-求字符个数 datalength-求字节数

SELECT len('james'),datalength('james')

都是输出5因为都是英文字母

SELECT len('中国第一'),datalength('中国第一')

第一个输出4，第二个输出8，中文一个字符占两个字节

LTRIM()：字符串左侧的空格去掉

RTRIM () ：字符串右侧的空格去掉

SELECT ' bb '

select LTRIM(RTRIM(' bb ')) AS '1'

LEFT()、RIGHT() 截取取字符串

29、日期函数

a)GETDATE() ：取得当前日期时间

b)DATEADD (datepart , number, date )，计算增加以后的日期。参数date为待计算的日期；参数number为增量；参数datepart为计量单位。DATEADD(DAY, 3,date)为计算日期date的3天后的日期，而DATEADD(MONTH ,-8,date)为计算日期date的8个月之前的日期 c)DATEDIFF ( datepart , startdate , enddate ) ：计算两个日期之间的差额。 datepart 为计量单位，可取值参考DateAdd。

d)DATEPART (datepart,date)：返回一个日期的特定部分

Month()、year()、day()来代替

--日期函数

--获得当前日期

select getdate()

--日期操作dateadd(单位,数值，日期)

SELECT dateadd(MONTH,2,getdate()) 加2个月

SELECT dateadd(MONTH,-2,getdate()) 减2个月

SELECT dateadd(YEAR,-2,getdate())

SELECT * FROM classes ORDER BY adddate

--求日期差

SELECT datediff(DAY,GETDATE(),'2012-08-04')

--查询昨天和前天的所有添加的数据

SELECT * FROM Classes WHERE datediff(day,addDate,GETDATE())in(2,1)

--DELETE FROM classes WHERE datediff(day,addDate,GETDATE())in(2,1)

--求日期部分

SELECT datepart(year,getdate())

SELECT year(getdate())

SELECT MONTH(getdate())

SELECT DAY(getdate())

--练习：统计学员生日的日的个数

SELECT * FROM students

SELECT day(birthdate) AS 出生日,COUNt(*) FROM students GROUP BY day(birthdate)

SELECT DATEDIFF(minute,'2012-08-06','2012-08-05')

30、case的两种用法是什么？

--1.case语法。要注意，then后的数据类型要一致

--1.1等值判断：

SELECT id, case cid

WHEN 1 THEN '一班'

WHEN 2 THEN '二班'

ELSE '未知'

END AS cid

,name,age FROM Students

--1.2区间判断

SELECT id, case

WHEN age>0 AND age <=20 THEN '小女孩'

WHEN age>20 AND age <=30 THEN '少女'

WHEN age>30 AND age<=40 THEN '阿姨'

WHEN age>40 AND age<=100 THEN '大妈'

ELSE '未知'

END AS 怪物等级

,name,cid FROM Students

31、绝对值函数语法是什么？

SELECT ABS(-1)

32、子查询的概念什么？分哪两类？

a)概念：把一个查询的结果在另一个查询中使用就叫子查询。(将一个查询语句做为一个结果集供其他SQL语句使用)

b)子查询基本分类：

独立子查询

子查询可以独立运行

相关子查询

子查询中引用了父查询中的结果

--2.子查询（就是查询另一个sql语句的结果集）***************************************

SELECT * FROM (SELECT * FROM Area WHERE ar_uid=0) AS tempTable //一定要别名！！！

--2.2使用子查询搜索外键表数据(单列多行)

SELECT * FROM Students WHERE cid in(--然后再根据黑猫班的id查询学员

SELECT ID FROM dbo.Classes WHERE classNAME LIKE '%黑猫%'--先查出黑猫班的id

)

--2.3使用子查询的=、!=、<、<=、>、>= 符号时,子查询的结果里只能有一个值,不能有多个列和行

SELECT * FROM Students WHERE age > (SELECT age FROM Students WHERE name='地球超人')

SELECT * FROM Students WHERE Students.cid in(36,37)

--2.3.1Any 集合：=、!=、<、<=、>、>= 符号使用，相当于or

SELECT * FROM Students WHERE Students.cid >= Any(SELECT 36 UNION select 37)

SELECT * FROM Students WHERE Students.cid >= 36 or Students.cid >=37

--2.3.2All 集合：=、!=、<、<=、>、>= 符号使用，相当于and

SELECT * FROM Students WHERE Students.cid >= All(SELECT 36 UNION select 37)

SELECT * FROM Students WHERE Students.cid >= 36 and Students.cid >=37

--exists 表示是否存在

SELECT * FROM Students where exists (SELECT * FROM Classes WHERE classNAME LIKE '%黑猫%' and Classes.id=Students.cid)

SELECT * FROM Students where cid in (SELECT id FROM Classes WHERE classNAME LIKE '%黑猫%' and Classes.id=Students.cid)

33、分页的三种方式是什么？

--3.1 top+orderby 来实现分页（页容量：）效率低,不用

--获得第一页数据

SELECT TOP 10 * FROM Area

--获得第二页数据

SELECT TOP 10 * FROM

(SELECT TOP 20 * FROM Area ORDER BY ar_id ASC) AS tempTable

ORDER BY ar_id DESC

--3.2 使用not in 分页

SELECT top 10 * FROM Area WHERE ar_id NOT IN (SELECT TOP 30 ar_id FROM Area)

--3.3使用and / between and /row_number()

SELECT * FROM Area

SELECT * FROM Area WHERE ar_id>=10 AND ar_id<=19--直接使用id来作为分页的条件不好，因为中间有id被删除了

--所以应该使用Row_Number() 生成一个连续的序号列over告诉Row_Number是按照某个列的顺序来生成序号

SELECT Row_Number() OVER (ORDER BY ar_id ASC) AS 序号,* FROM Area

SELECT Row_Number() OVER (ORDER BY ar_name ASC) AS 序号,* FROM Area

--使用row_number分页(微软推荐分页方法)

SELECT * FROM

(SELECT Row_Number() OVER (ORDER BY ar_id) AS 序号,* FROM Area) as tempTable

WHERE 序号>=10 AND 序号<=19

SELECT * FROM Students

SELECT * FROM classes

34、连接查询有哪几种？

--4.1内连接-根据条件将两个表的指定列合成一行数据(查询出满足条件的行) 例：查询学员及其所在班级信息

SELECT * FROM Students INNER JOIN Classes ON students.cid=classes.id

--相当于如下多表查询语句：

SELECT * FROM Students,Classes WHERE students.cid=classes.id

--左右两张表中任何一行不满足条件，就都不输出

SELECT * FROM Students INNER JOIN Classes2 on students.cid=classes2.id

--4.2左外连接-保证左表中每行记录都显示，右表中如果有满足on条件的，就显示，没有就不显示

SELECT * FROM Students LEFT JOIN Classes2 on students.cid=classes2.id

--4.3右外连接-保证右表中每行记录都显示，左表中如果有满足on条件的，就显示，没有就不显示

SELECT * FROM Students RIGHT JOIN Classes3 on students.cid=classes3.id

--4.4交叉连接- 将左表的每一行与右表的每一行都连接一次，总行数= 左表行数* 右表行数（笛卡尔乘ji）

SELECT * FROM Students CROSS JOIN Classes3

35、什么是视图？视图和数据表的本质区别是什么？

a)视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上

b)视图在操作上和数据表没有什么区别，但两者的差异是其本质是不同:数据表是实际存储记录的地方，然而视图并不保存任何记录。

c)相同的数据表，根据不同用户的不同需求，可以创建不同的视图（不同的查询语句）

d)视图的目的是方便查询，所以一般情况下不对视图进行增改，不能删

优点：

筛选表中的行
防止未经许可的用户访问敏感数据
降低数据库的复杂程度

e)普通视图

并不存储数据（虚拟表），访问的是真实表中的数据

f)使用视图注意事项：

1.视图中的查询不能使用order by ，除非指定了top语句。

视图被认为是一个虚拟表，表是一个集合，是不能有顺序的。而order by 则返回的是一个有顺序的，是一个游标。

在视图中使用select top percent + order by 问题。

如果指定列名，则列名必须唯一（使用*不考虑）
create view vw_name as 后不能跟begin end.

g)（*）索引视图

在视图上创建唯一聚集索引

数据会保存在数据库中而不是引用表中的数据

--视图内部储存了一个查询sql语句，那么当我们查询视图时，就相当于是查询了视图里的sql语句的结果集（子查询）

--视图内的列名不能重复

SELECT * FROM vwStudentClass

SELECT * FROM vwcontact_group

--5.1新增视图数据

--错误：视图或函数'vwcontact_group' 不可更新，因为修改会影响多个基表

--INSERT INTO vwcontact_group(uname,cellphone,homephone,groupName,gid)

--VALUES ('jam',123123123,123123123,'小三们',1)

--通过：新增的列都来源于一张基表，所以可以新增

INSERT INTO vwcontact_group(uname,cellphone,homephone,groupName,groupId)

VALUES ('jam',123123123,123123123,'小三们',1)

SELECT * FROM vwcontact_group

--5.2修改视图数据

--通过：因为只更新了视图里一张基表的列

UPDATE vwcontact_group SET uName='bobo' WHERE id=7

--错误：修改会影响多个基表。

UPDATE vwcontact_group SET uName='网秦',name='情网' WHERE id=7

SELECT * FROM vwcontact_group

--5.3删除视图数据

--错误：因为删除会影响多个基表

DELETE FROM vwcontact_group WHERE id=7

--通过：如果视图里只包含一个基表，那么就可以使用删除

SELECT * FROM vwTeacher

DELETE FROM vwTeacher WHERE id=4

--5.4视图中的查询不能使用order by ，除非指定了top语句

SELECT * FROM Teacher ORDER BY salary

--5.5代码创建视图

CREATE VIEW vwArticle_Cate

SELECT a.id AS aid,a.cid,a.title,a.content,ac.id AS cateid,ac.name,ac.parentId FROM Article a INNER JOIN ArticleCate ac ON a.cid=ac.id

SELECT * FROM vwArticle_Cate

36、局部变量的声明？赋值？

a)声明

DECLARE @变量名数据类型

b)赋值
SET @变量名 =值 --set用于普通的赋值
SELECT @变量名 = 值 --用于从表中查询数据并赋值，可以一次给多个变量赋值

--6.1变量的声明

DECLARE @name VARCHAR(40),@age int

--6.2赋值

SET @name='我爱北京天安门~~~天安门前武警手里有灭火器！'

SELECT @age=age FROM Teacher --如果该列中有多个值，则将最后一个值给变量

--6.3显示变量

SELECT @name,@age--在结果集中显示

print @name --在消息框中打印

SELECT *,ac FROM Article a

SELECT @@version

备注：全局变量（系统变量）：

全局变量必须以标记@@作为前缀，如@@version

全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值

@@error变量，在每次执行完SQL语句后，都会为@@error变量赋值，如果上次执行的SQL语句有错，则将@@errro赋值为一个不为0的值，否则（执行没错），则将@@error赋值为0.

@@Identity最后一次插入的标识值

37、if else 和 while

a)格式

IF(条件表达式)

BEGIN --相当于C#里的{

语句1
……

END --相当于C#里的}

ELSE

BEGIN

语句1

……

END

DECLARE @name VARCHAR(40)

SET @name='你很优秀~~很不错~~~好英俊！你信吗？！'

if(len(@name)>20)

BEGIN

SELECT '文字长度太长了'

END

ELSE

BEGIN

SELECT '你的文字太短了'

END

--练习：

--计算平均分数并输出，如果平均分数超过分输出成绩最高的三个学生的成绩，否则输出后三名的学生

DECLARE @avgScore INT

SELECT @avgScore = avg(math+english) FROM Score

if(@avgScore > = 120)

BEGIN

SELECT TOP 3 * FROM Score ORDER BY math,english desc

END

ELSE

BEGIN

SELECT TOP 3 * FROM Score ORDER BY math,english

END

--练习：

--计算平均分数并输出，如果平均分数超过分输出成绩最高的三个学生的成绩，否则输出后三名的学生

DECLARE @avgScore INT

SELECT @avgScore = avg(math+english) FROM Score

if(@avgScore > = 120)

BEGIN

SELECT TOP 3 * FROM Score ORDER BY math,english desc

END

ELSE

BEGIN

SELECT TOP 3 * FROM Score ORDER BY math,english

END

b)格式

WHILE(条件表达式)

BEGIN --相当于C#里的{

语句

……

continue --退出本次循环

BREAK --退出整个循环

END --相当于C#里的}

--计算-100之间所有奇数的和

DECLARE @totalNum INT =0,@times int=0 --声明变量同时给初始值

WHILE(@times<100)--循环

BEGIN

if(@times%2!=0)--取模

BEGIN

set @totalNum+=@times

END

set @times+=1

END

SELECT @totalNum

--练习：如果english不及格的人超过半数(考试题出难了)，则给每个人增加分,循环加，直到不及格的人数少于一半

DECLARE @failNum float--不及格人数

DECLARE @stuNum float--参加考试的总人数

WHILE(1=1)

BEGIN

SELECT @failNum=count(*) FROM Score WHERE English<60

SELECT @stuNum=count(*) FROM Score WHERE English IS NOT NULL

--if(@failNum*2 > @stuNum)

if(@failNum >= (@stuNum/2))

BEGIN

UPDATE Score SET English+=2

PRINT '不及格人数超过了总人数一半'

END

ELSE

BEGIN

PRINT '不及格人数没有超过总人数一半'

BREAK--退出循环

END

SELECT @failNum AS 不及格人数

SELECT 5.0/2

SELECT * FROM Score

38、事务

a)事务：同生共死

b)指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)--也就是由多个sql语句组成，必须作为一个整体执行

c)这些sql语句作为一个整体一起向系统提交，要么都执行、要么都不执行

d)语法步骤：

开始事务：BEGIN TRANSACTION
事务提交：COMMIT TRANSACTION
事务回滚：ROLLBACK TRANSACTION

e)判断某条语句执行是否出错：

全局变量@@ERROR；
@@ERROR只能判断当前一条T-SQL语句执行是否有错，为了判断事务中所有T-SQL语句是否有错，我们需要对错误进行累计；

为什么需要事务？

如，转账问题：

假定钱从A转到B，至少需要两步：

A的资金减少

然后B的资金相应增加

l Update bank set balance=balance-1000 where cid='0001'

l Update bank set balance=balance + 1000 where cid='0002'

--查看结果。

SELECT * FROM bank

注意约束：金额不能小于10

假设cid='0001'的balance为1000，cid='0002'的balance为10，则会出现第一个更新语句不能通过，因为金额不能小于10，但是第二条更新语句通过，增加了1000，这就是漏洞！

--8.1创建表，准备数据

create table bank

(

cId char(4) primary key,

balance money, --余额

)

alter table bank

add constraint CH_balance check(balance >=10)

DELETE FROM bank

insert into bank values('0001',1000)

insert into bank values('0002',10)

--8.2事务:将多条语句作为一个执行单元,单元中任意语句出错的话,其他语句对数据库造成的影响都要取消掉

SELECT * FROM bank

DECLARE @errCount INT =0 --声明变量，用来累计错误号

---------------------------------事务开始------------------------

BEGIN TRANSACTION--开启事务***

UPDATE bank SET balance = balance - 1000 WHERE cId='0001'

SET @errCount = @errCount + @@error --统计错误号

UPDATE bank SET balance = balance + 1000 WHERE cId='0002'

SET @errCount = @errCount + @@error --统计错误号

if(@errCount>0)--如果统计的错误号大于，说明之前某语句执行时有错误，必须回滚事务

BEGIN

ROLLBACK TRANSACTION --一旦***回滚事务，那么事务中的任何对数据库的影响都会被还原

PRINT '事务回滚完毕'

END

ELSE

BEGIN

COMMIT TRANSACTION --一旦***提交事务，那么事务中所有的影响都会保存到数据库中

PRINT '事务提交完毕'

END

39、存储过程

a)存储过程---就像数据库中运行方法(函数)

b)和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。

c)前面学的if else/while/变量/insert/select 等，都可以在存储过程中使用

优点：

执行速度更快 – 在数据库中保存的存储过程SQL语句都是编译过的

允许模块化程序设计 – 类似方法的复用

提高系统安全性 – 防止SQL注入

减少网络流通量 – 只要传输存储过程的名称

l 系统存储过程

由系统定义，存放在master数据库中

名称以“sp_”开头或”xp_”开头,自定义的存储过程可以以usp_开头

--9.1创建一个简单的存储过程

CREATE PROC up_justforfun

BEGIN

SELECT * FROM Students

SELECT * FROM classes

END

--9.2调用不带参数的存储过程

EXEC up_justforfun

--9.3修改存储过程alert(警报) alter(修改)

ALTER PROC up_justforfun

@age INT =20 --定义参数，并设置默认值

BEGIN

SELECT * FROM Students WHERE age>@age

SELECT * FROM classes

END

--9.2调用带输入参数的存储过程

EXEC up_justforfun 30

EXEC up_justforfun

--9.3创建带多个参数的存储过程

create PROC up_justforfunWithParas

@age INT =20,

@name VARCHAR(20)

BEGIN

SELECT * FROM Students WHERE age>@age AND NAME LIKE @name

SELECT * FROM classes

END

--9.4调用带多个参数的存储过程

EXEC up_justforfunWithParas 20,'%a%'

--9.5显示的传参（可以不为有默认值的参数传参了）

EXEC up_justforfunWithParas @age=30,@name='%a%'

EXEC up_justforfunWithParas @name='%a%'

--9.6创建带输出参数的存储过程

create PROC up_justforfunWithOutPutParas

@age INT =20,

@name VARCHAR(20),

@stuCount INT OUTPUT --定义：输出类型的参数

BEGIN

SELECT * FROM Students WHERE age>@age AND NAME LIKE @name

SELECT @stuCount=COUNT(*) FROM Students--将总行数赋值给输出参数@stuCount

END

--9.7调用带输出参数的存储过程

DECLARE @studentCount int--先在外面定义一个变量

EXEC up_justforfunWithOutPutParas 30,'%a%',@studentCount OUTPUT --将变量作为输出参数传入存储过程

SELECT @studentCount AS 学生总人数 --将存储过程修改后的变量显示

SQL语句：两个单引号相当于一个单引号

习题：

练习：

--输出所有数据中通话时间最长的条记录。orderby datediff

select top 5* ,datediff(second,startdatetime,enddatetime) 时长 from callrecords

order by 时长 desc

--输出所有数据中拨打长途号码（对方号码以开头）的总时长。like、sum

select sum(datediff(second,startdatetime,enddatetime)) 长途号码总时长 from callrecords

where telnum like '0%'

--输出本月通话总时长最多的前三个呼叫员的编号。

select top 3 callernumber 呼叫员编号,sum(datediff(second,startdatetime,enddatetime)) 通话时长 from callrecords 呼叫员编号

where month(startdatetime)=7

group by callernumber

order by 通话时长 desc

--输出本月拨打电话次数最多的前三个呼叫员的编号.group by,count(*)

select top 3 callernumber 呼叫员编号 ,count(id) 打电话次数最多 from callrecords

where month(startdatetime)=7

group by callernumber

order by count(id) desc

--按照月份分组。

SELECT month(startdatetime) 月份 ,count(id) 月份个数 FROM callrecords

GROUP BY month(StartDateTime)

--练习

--按照班级人数排序

SELECT cid AS 班级,count(id) as 总人数 FROM students GROUP BY cid ORDER BY count(id)

--请统计总年龄超过的班级名称和总年龄，并按总年龄排序

SELECT cid AS 班级,sum(age) as 总年龄 FROM students GROUP BY cid having sum(age)>100 ORDER BY sum(age)

--统计各个性别在cid=2的班的总人数，并按照总人数排序

SELECT cid AS 班级,count(id) as 总人数 FROM students where cid=2 GROUP BY cid ORDER BY count(id)

SELECT cid AS 班级,count(id) as 总人数 FROM students GROUP BY cid having cid=2 ORDER BY count(id)

--练习题:

--显示学生表的所有列和班级表的班级名称列

SELECT s.*,c.classname FROM Students s INNER JOIN Classes c ON s.cid=c.id WHERE s.age>20

--询学生姓名、年龄、班级及成绩(忽略没有参加考试和没有所在班级的学员)

SELECT * FROM Students s INNER JOIN Classes c ON s.cid=c.id inner join Score sc ON s.id=sc.SID

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

SELECT * FROM Students s left JOIN Score sc ON s.id=sc.SID

--请查询出所有没有参加考试（在成绩表中不存在的学生）的学生信息

SELECT * FROM Students s WHERE s.id NOT in(SELECT SID FROM Score)

--练习题:

--练习：查询所有英语及格的学生姓名、年龄及成绩

SELECT s.name,s.age,sc.english,sc.math FROM Students s INNER JOIN Score sc ON s.id=sc.SID WHERE sc.english>=60

--练习：查询所有参加考试的(english分数不为null)学生姓名、年龄及成绩

SELECT s.name,s.age,sc.english,sc.math FROM Students s INNER JOIN Score sc ON s.id=sc.SID WHERE sc.english IS NOT NULL

--练习：查询所有学生(参加和未参加考试)的学生姓名、年龄、成绩，如果没有参加考试显示缺考,如果小于english&math60分显示不及格

SELECT s.name,s.age,CASE

WHEN sc.english IS NULL THEN '缺考'

WHEN sc.english<60 THEN'不及格'

ELSE cast(sc.english AS VARCHAR)--因为case是生成一个列，这个列的数据类型必须统一

end as 英语考试,

CASE

WHEN sc.math IS NULL THEN '缺考'

WHEN sc.math<60 THEN'不及格'

ELSE cast(sc.math AS VARCHAR)--因为case是生成一个列，这个列的数据类型必须统一

end as 数学考试

FROM Students s left JOIN Score sc

ON s.id=sc.SID

posted @ 2013-05-22 20:54 种花生的读书人阅读(668) 评论(0) 编辑收藏举报

刷新页面返回顶部

种花生的读书人

不积跬步无以至千里，不积小流无以成江河！

SQL Server数据库学习

公告