有次笔试最后一页的三个数据库连接查询，没有写出来，被考官暗讽了下。现在想来，实习初，确实很LOW。现公司刚入职的时候，负责过ETL方面，所以和数据库打了不少交道，五十行的联合查询、上百行的存储过程很常见，游标、视图、索引频频接触，包括在SQL中断点调试，测值等等，在这里将所接触所了解的进行整理，做一个小总结，慢慢的积累，对游标、视图等等也做一个简单的说明。我有时候遇到相同问题，会出现忘记上次是如何解决问题的情况，直到花费大量的时间、精力去重新找到解决方案。这很浪费，所以凡是遇到的问题，所做解决的方案，我强迫自己写个备注，将大致思路进行汇总整理，以方便自己及时回顾。毕竟好记性不如烂笔头，古人诚不欺我也！
1、数据库常见三种数据模型： 层次、网状、关系
2、关系的 三类完整性约束：实体完整性（主键不为空）、参照完整性、用户自定义完整性
3、常用易忘数据类型：
①int 4byte = 32bit 一个汉字2字节，中文标点、全角占两字节；字母、英文标点、半角占一个字节
②char 固定长度的非unicode 字符数据，最大长度为8000个字符
③varchar 可变长度的非unicode 字符数据，最大长度为8000个字符，存储大小为输入数据的实际长度
④nchar 固定长度的unicode 字符数据，最大长度为4000个字符，存储大小为实际长度的两倍
⑤nvarchar 可变长度的unicode 字符数据，最大长度为4000个字符，存储大小为实际长度的两倍
4. 查询31到40条记录（面试）
--id连续
SELECT * FROM IBBC.dbo.Students AS s WHERE s.Stu_id BETWEEN 31 AND 40 --id不连续
--普快
SELECT TOP 10 * FROM Students AS s WHERE s.Stu_id NOT IN (SELECT TOP 30 s2.Stu_id FROM Students AS s2)
--动车
SELECT TOP 10 * FROM (SELECT TOP 40 * FROM IBBC.dbo.Students AS s ORDER BY s.Stu_id ASC) AS temp ORDER BY temp.Stu_id DESC
--高铁
SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY s.Stu_id) Newcolumn,* FROM Students AS s)s1 WHERE s1.Newcolumn BETWEEN 31 AND 40 --蹦蹦车 易混淆 实际查询的是最后十条记录的降序
SELECT TOP 10 * FROM (SELECT TOP 40 * FROM IBBC.dbo.Students AS s) AS temp ORDER BY temp.Stu_id DESC
5、常见的判断操作
--判断表是否存在
IF exists(select * from dbo.sysobjects where id = object_id(N'[dbo].[Table_22]')) --判断表栏位是否存在
IF EXISTS (select * from syscolumns where id=object_id('Table_1') and name='IDD') --判断存储过程是否存在
If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[test]')) --判断视图是否存在
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_test]'))
6、常见的表操作
--增加栏位
ALTER TABLE IBBC.dbo.Table_1 ADD YYY CHAR(5)
--删除栏位
ALTER TABLE IBBC.dbo.Table_1 DROP COLUMN YYY
--更改栏位类型型
ALTER TABLE IBBC.dbo.Table_1
ALTER COLUMN b_id INT NOT NULL --重命名栏位
EXEC sp_rename 'TableName.ColumnName', 'NewColumnName', 'column' --指定栏位添加描述
exec sp_addextendedproperty N'MS_Description', N'我是描述信息', N'user', N'dbo', N'table', N'Table_1', N'column', N'YYY' --添加主键
ALTER TABLE IBBC.dbo.Table_2 ADD CONSTRAINT test_main PRIMARY KEY(b_id)
--删除主键
ALTER TABLE IBBC.dbo.Table_2 DROP CONSTRAINT test_main --添加外键
alter table IBBC.dbo.Table_1 add constraint test_foreign foreign key(bas_id) references IBBC.dbo.Table_2(b_id)
--删除外键
ALTER TABLE IBBC.dbo.Table_1 DROP CONSTRAINT test_foreign --设计器修改后保存失败： 工具->选项->设计器->表和数据库设计->保存时拒绝修改数据设计内容（取消勾选）
7. 常用查询操作
--case when then
SELECT s.Stu_id,s.Stu_Name,s.Date_,
(CASE s.Stu_id
WHEN 1 THEN DATEADD(mm,1,s.Date_)
WHEN 2 THEN DATEADD(mm,-1,s.Date_)
ELSE DATEADD(mm,2,s.Date_)
END) New_date
FROM IBBC.dbo.Students AS s --日期函數
SELECT DATEPART(yy,GETDATE()) AS 'Year' --yy/yyyy/yy
SELECT DATEPART(mm,GETDATE()) AS 'Month' --mm/m/Month
SELECT DATEPART(ww,GETDATE()) AS 'Week' -- ww/wk/Week --日期轉換為字符串
SELECT CONVERT(VARCHAR,GETDATE(),112) --標準字符串转换为日期 20080808
SELECT CASt('2003-02-02' AS DATETIME)
--部分字符串转换为日期
select convert(datetime,substring('',1,4)+'-'+substring('',5,2)+'-1') --类型转换
SELECT CONVERT(VARCHAR(8),GETDATE(),112)
SELECT CAST (GETDATE() AS VARCHAR(11)) --查询栏位值为null的数据
select * from table where column is null;
--查询栏位值为''的数据
select * from table where column = '';
8. 聚合函数
--当前顺序 where -> group by -> having 函数 -> order by
--SQl顺序 from -> Join on -> where -> group by -> having -> select -> distinct -> order by -> top
--聚合函数 分组
select s.StudentName,sum(grade) as '总成绩' from IBCC.dbo.Student s group by s.StudentName --having 对聚合函数查询后的结果集进行筛选
select s.StudentName, sum(grade) as '总成绩' from ibcc.dbo.Student s group by s.StudentName having sum(grade)>100
9. 视图
--视图 虚拟表
use IBCC
go
--创建
create view view_test as
select * from IBCC.dbo.Student where grade >80
go
--查询
select * from ibcc.dbo.view_test
--删除 不能指定数据库名为前缀
drop view dbo.view_test
10.存储过程
--不带参数的存储过程
--创建
create proc proc_test
as
select * from ibcc.dbo.Student
go --带参数的存储过程
--创建
create proc proc_test1 @s_id int
as
select * from IBCC.dbo.Student where StudentID=@s_id
go
--调用
exec proc_test1 1002
exec proc_test1 @s_id =1002
--删除
drop proc proc_test1
11. 事务
--一段SQl中途执行失败，可回滚至员状态，eg:转账、删除、更新等操作
BEGIN TRAN --开始事务
DECLARE @b INT;
SET @b = 0;
DELETE FROM A ...
DELETE FROM B ...
DELETE FROM C ... IF(@b>0)
BEGIN COMMIT TRAN --提交事务
END
ELSE
BEGIN
ROLLBACK TRAN --回滚事务
END END