sql常用整理.docx

1、sql常用整理目录概览：1.数据库2.表3.临时表4.索引和约束5.范式6.增删改查7.连接8.分组和排序9.通配符10.视图11.存储过程和事务12.游标13.触发器14.作业自己亲手编写的一些常用的SQL，希望对大家有用喔，废话不多说了，直接入正题1.数据库创建数据库use masterif exists(select * from sysdatabases where name = OrderDB) drop database OrderDBcreate database OrderDBon( name=OrderDB_data, filename = D:DBOrderDB_data.

2、mdf, size=10, filegrowth=15%)log on( name=OrderDB_log, filename=D:DBOrderDB_log.ldf, size=3, filegrowth=10%)删除数据库drop database OrderDB2.表创建表-用户表if exists (select * from sysobjects where name = Tse_User) drop table Tse_UserCreate table Tse_User( ID int identity(1,1), UserID int not null, UserName var

3、char(64) not null, RealName varchar(64) null, PRIMARY KEY (UserID)-产品表if exists (select * from sysobjects where name = Tse_Product) drop table Tse_ProductCreate table Tse_Product( ID INT IDENTITY(1,1), ProductID varchar(64) not null, ProductName varchar(256) not null, Price float not null, Storage i

4、nt not null, -库存 PRIMARY KEY(ProductID)-订单表if exists (select * from sysobjects where name = Tse_Order) drop table Tse_OrderCreate table Tse_Order( ID int identity(1,1), OrderID varchar(64) not null, UserID int not null, ProductID varchar(64) not null, Number int not null, -购买数量 PostTime datetime not

5、 null, PRIMARY KEY(OrderID), FOREIGN KEY (UserID) REFERENCES Tse_User(UserID), FOREIGN KEY (ProductID) REFERENCES Tse_Product(ProductID) 删除表drop table Tse_User 清空表truncate table Tse_User 清除表中所有数据，下次插入编号从1开始delete from Tse_User 清除表中所有数据，但下次插入编号从原有编号+1开始3.临时表生成临时表，插入数据，将员工姓名全部打印出来use mastergocreate ta

6、ble #Employee( ID int identity(1,1), Name varchar(64) not null, primary key (ID)insert into #Employee(Name) values(zhangsan)insert into #Employee(Name) values(lisi)insert into #Employee(Name) values(wangwu)insert into #Employee(Name) values(tony)insert into #Employee(Name) values(mike)declare i int

7、declare Name varchar(64)declare Count intdeclare Str nvarchar(4000)set i = 0select Count = COUNT(0) from #Employeewhile(i 3),constraint CS_Email check (charindex(, Email) 0)5.范式第一范式1NF第一范式需满足两个条件：1）每个数据行必须包含具有原子性（即不可再分）的值；2）每个数据行必须包含一个独一无二的值，即主键。举例：假如客户表中存在地址列，如果经常需要按城市归类，那么，应该地址列拆分为省份，城市，县，街道地址等列。第

8、二范式2NF第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。举例：比如常用的选课表中，以学号和课程号为联合主键，不能将课程名，学分等课程相关信息写入选课表，因为他们只与主键的一部分（课程号）相关。第三范式3NF第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。举例：订单表中以订单号为主键，用户真实姓名和邮箱等信息与用户有关，与订单没有直接关系，因此，用户真实姓名和邮箱等不能放到订单表中。 由于时间关系，余下的几个问题在下一篇中讨论

9、，谢谢关注，下一篇地址为：工作经常使用的SQL整理，实战篇（二）接着上一篇“工作经常使用的SQL整理，实战篇（一）”继续讨论，这一篇中主要讨论增删改查，连接，分组和排序，通配符，视图，存储过程和事务，游标，触发器这些东西。6.增删改查插入-插入用户表数据insert into Tse_User(UserID, UserName, RealName, Email, Mobile)values(111, zhangsan, zhangsan, zs, ) -插入产品表数据INSERT INTO Tse_Product(ProductID, ProductName, Price, Storage)V

10、ALUES(PD00030, Benz, 500500.0, 30000) -插入订单表数据declare OrderID VARCHAR(64)-将年，月，日，时，分，秒，毫秒以字符串形式连接起来作为订单号 SET OrderID = DATENAME(YEAR, GETDATE() + DATENAME(MONTH, GETDATE() +DATENAME(DAY, GETDATE()+ DATENAME(HOUR, GETDATE() + DATENAME(MINUTE, GETDATE()+DATENAME(SECOND, GETDATE() +DATENAME(MILLISECOND

11、, GETDATE() INSERT INTO Tse_Order(OrderID, UserID, ProductID, Number, PostTime) VALUES(OrderID, 115, PD00040, 10, GETDATE()修改Update Tse_User set RealName = 李四 where UserID = 112删除Delete from Tse_User Where UserID = 111简单查询select * from Tse_User with(nolock)select * from Tse_Order with(nolock) where

12、ID = 27.连接内连接-左右表匹配的行SELECT * FROM Tse_Order AS O WITH(NOLOCK)INNER JOIN Tse_User AS U WITH(NOLOCK) ON O.UserID = U.UserIDWHERE U.UserID = 111 左连接（左外连接）-左边表中所有行，右边匹配左边, 右边为空的补NULLSELECT * FROM Tse_User AS U WITH(NOLOCK)LEFT JOIN Tse_Order AS O WITH(NOLOCK) ON U.UserID = O.UserID 右连接（右外连接）-右边表中所有行，左边

13、匹配右边，左边为空的补NULLSELECT * FROM Tse_Order AS O WITH(NOLOCK)RIGHT JOIN Tse_Product AS P WITH(NOLOCK) ON O.ProductID = P.ProductID 全连接-左右表所有行，为空的补NULLSELECT * FROM Tse_Order AS O WITH(NOLOCK)FULL JOIN Tse_Product AS P WITH(NOLOCK) ON O.ProductID = P.ProductID8.分组和排序按UserID分组SELECT UserID, COUNT(0) AS Num

14、ber FROM Tse_Order WITH(NOLOCK) GROUP BY UserID 按UserID分组，订单数量大于等于3SELECT UserID, COUNT(0) AS Number FROM Tse_Order WITH(NOLOCK) GROUP BY UserID HAVING COUNT(0) =3按UserID分组，订单数量大于等于1，按订单数量升序SELECT UserID, COUNT(0) AS Number FROM Tse_Order WITH(NOLOCK) GROUP BY UserID HAVING COUNT(0) =1 ORDER BY Numb

15、er ASC9.通配符LIKE：匹配多个未知字符_：匹配一个未知字符 -匹配126邮箱的SELECT * FROM Tse_User WITH(NOLOCK) WHERE Email LIKE % -匹配所有包含的邮箱 SELECT * FROM Tse_User WITH(NOLOCK) WHERE Email LIKE % -匹配16开头，后面跟一个任意字符的邮箱 SELECT * FROM Tse_User WITH(NOLOCK) WHERE Email LIKE %16_.com-匹配除126以外的所有邮箱SELECT * FROM Tse_User WITH(NOLOCK) WHE

16、RE Email NOT LIKE %10.视图 删除视图 IF EXISTS (SELECT * FROM SYSOBJECTS WHERE Name = V_Tse_TotalInfo) DROP VIEW V_Tse_TotalInfo 创建视图-包含用户表，产品表和订单表关联后的所有信息 CREATE VIEW V_Tse_TotalInfo AS SELECT O.OrderID, O.UserID, O.ProductID, O.PostTime, U.UserName, U.RealName, U.Email, U.Mobile, P.ProductName, P.Price F

17、ROM Tse_Order AS O WITH(NOLOCK) INNER JOIN Tse_User AS U WITH(NOLOCK) ON O.UserID = U.UserID INNER JOIN Tse_Product AS P WITH(NOLOCK) ON O.ProductID = P.ProductID11.存储过程和事务创建存储过程，先删除订单表（外键表）中的记录，再删除产品表（主键表）中的记录CREATE PROCEDURE dbo.SC_Tse_DeleteProduct( ProductID VARCHAR(64), Result int output)ASBEGI

18、N SET NOCOUNT ON; BEGIN TRAN -开始事务 BEGIN DELETE FROM Tse_Order WHERE ProductID = ProductID DELETE FROM Tse_Product WHERE ProductID = ProductID IF (ERROR 0) BEGIN SET Result = -999 ROLLBACK TRAN -回滚 END ELSE BEGIN SET Result = 888 COMMIT TRAN -提交 END ENDEND12.游标获取所有产品的名字，以|分隔，包含在输出参数Names中CREATE PROC

19、EDURE SC_Tse_GetProductNames ( Names varchar(max) OUTPUT ) AS BEGIN SET NOCOUNT ON; declare ProductName varchar(64) declare curTest cursor for (select ProductName from Tse_Product) open curTest -打开游标 fetch next from curTest into ProductName while fetch_status = 0 -获取成功 begin if (ProductName is not n

20、ull and ProductName ) begin if (Names is null or Names = ) begin set Names = ProductName end else begin set Names = Names + |+ ProductName end end fetch next from curTest into ProductName end close curTest -关闭游标 deallocate curTest -释放游标 END13.触发器因为用户编号在订单表中为外键，所以，直接删除某个用户时，如果该用户下了订单，就会提示有外键不能删除。针对这种

21、情况，可以考虑使用触发器。创建触发器，删除用户表中用户时，会自动先删除订单表中的订单CREATE TRIGGER TR_Tse_DelUser ON Tse_User INSTEAD OF DELETE -代替默认的删除 AS BEGIN SET NOCOUNT ON DELETE FROM Tse_Order WHERE UserID IN (SELECT UserID FROM Deleted) DELETE FROM Tse_User WHERE UserID IN (SELECT UserID FROM Deleted)END 使用触发器，添加订单时，产品表库存相应减少CREATE T

22、RIGGER TR_Tse_ADDOrder ON Tse_Order AFTER INSERT AS BEGIN UPDATE Tse_Product SET Storage = Storage - (SELECT Number FROM INSERTED) WHERE ProductID IN (SELECT ProductID FROM INSERTED)END关于SQL定时作业部门的介绍，请看“工作经常使用的SQL整理，实战篇（三）”接着本系列前面两篇继续讨论。有时候当你需要定时处理数据库数据时，你会怎么做呢？当然你可以手工编写一个程序去完成任务，但写程序既需要时间也需要考验程序的稳定

23、性，这里，介绍一种快捷的方法，这里只介绍SQLSERVER数据库中建立作业的方法，其他数据库我相信也有这个功能。14.作业在SQLSERVER数据库中建立作业的步骤大致如下：1.安装完SQLSERVER后，打开SQLSERVERManagementStudio，找到SQLSERVER代理，如下图。2.在“作业”上点击鼠标右键，选择新建作业。3.在弹出的界面上，常规属性中填写名称。4.单击“步骤”属性，再点击新建。5.填写步骤名称， 类型， 选择数据库， 命令填写你要执行的存储过程或sql语句。6.选择高级属性，由于这里只有一步，所以选择“退出报告成功的作业”，也可以选择脚本输出到文件的路径。点击确定。7.下一步点击“计划”-“新建”。8.执行计划中输入名称，选择计划类型，执行频率（每天/每周/每月）,按你想要的选择。9.还可以在通知属性中，选择作业完成后发电子邮件等操作（如果远程的话）。10.最后点击确定，这个作业就新建好了。11.新建成功后，从作业中可以看到这个作业。12.另外，你还可以通过作业活动监视器监视作业的执行情况。以上就是SQL SERVER数据库新建定时作业的全过程，希望对你有用，有错误的地方，请指出来喔，谢谢