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]
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
AS
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
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
AS
BEGIN
SELECT * FROM Students
SELECT * FROM classes
END
--9.2调用不带参数的存储过程
EXEC up_justforfun
--9.3修改存储过程alert(警报) alter(修改)
ALTER PROC up_justforfun
@age INT =20 --定义参数，并设置默认值
AS
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)
AS
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 --定义：输出类型的参数
AS
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
4134
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
