《自定义查询SQL基础培训.doc》由会员分享，可在线阅读，更多相关《自定义查询SQL基础培训.doc（29页珍藏版）》请在金锄头文库上搜索。
1、1SQL 语句的常用操作1.1创建数据库举例：CREATE DATABASE Test -数据库名称ON(name=test_data, -逻辑名称filename=d:test_data.mdf, -物理名称size=3mb, -初始大小maxsize=15mb, -上限filegrowth=1mb - 每次增长1Mb)LOG ON(name=test_log,filename=d:test_log.ldf,size=3mb,maxsize=15mb,filegrowth=1mb)1.2创建表举例：Create Table Customer(CustID int IDENTITY(1,1)
2、PRIMARY KEY NOT NULL,CustName nvarchar(50) NOT NULL,City nvarchar(20) NOT NULL DEFAULT 暂无,Phone nvarchar (50) NULL,Address nvarchar (200) NULL)1.3添加数据方法1举例：INSERT INTO Customer(CustName,Phone,Address)VALUES (张三,18123556554,北京)DECLARE i INTSET i=0WHILE i15方法2举例：删除全部数据没有WHERE子句，速度快TRUNCATE TABLE Custo
3、merTempTRUNCATE不能触发任何Delete触发器，DELETE对每条记录的删除均需要记录日志，TRUNCATE全部删除，速度比DELETE快，占资源少，并且只记录页删除的日志1.7删除表举例：DROP TABLE Customer21.8查询数据举例：SELECT TOP 100 * FROM Customer WITH(NOLOCK)WHERE City = 暂无 ORDER BY CustName大数据量大的时侯，没有加nolock有可能会锁表，加上了nolock可能会读取未提交的事务或一组在读取中间回滚的页面，有可能发生脏读的风险。分组查询举例：UPDATE Customer
4、 SET SumCustNo = 1SELECT Address,SUM(CustNo) AS SumCustNoFROM CustomerGROUP BY AddressHaving COUNT(*)1一般都和聚和函数使用，如果没有用聚合函数的话用DISTINCT来代替速度快。1.9SQL分类DDL数据定义语言(CREATE，ALTER，DROP，DECLARE)DML数据操纵语言(SELECT，DELETE，UPDATE，INSERT)DCL数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)1.10SQL执行顺序5SELECT 5-1列,5-2DISTINCT,5-3
5、TOP1FROM 表2WHERE 条件3GROUP BY 列4HAVING 对组筛选条件6ORDER BY 列 排序2SQL SERVER的关联查询CREATE TABLE CustomerRoom(RoomID int identity(1,1) primary key not null,RoomSign nvarchar(50) not null,CustID int,RoomType nvarchar(50)insert into CustomerRoom(RoomSign,CustID,RoomType)select 1-1-1,1,住宅union allselect 1-1-2,32
6、,商铺union allselect 1-1-3,34,公寓union allselect 1-1-4,41,写字楼union allselect 1-1-5,0,写字楼2.1内关联连接INNER JOIN内关联连接查询结果只显示满足的条件。举例：SELECT *FROM Customer AS aINNER JOIN CustomerRoom AS bON a.CustID = b.CustID和如下语句得到的信息是一样的：SELECT a.*,b.RoomSign, b.RoomTypeFROM Customer AS a, CustomerRoom AS bWHERE a.CustID
7、= b.CustID2.2左外关联连接 LEFT JOIN(即LEFT JOIN)左外关联连接结果显示左表所有记录，右表符合JOIN条件的信息显示，不符合的置空。举例：SELECT *FROM Customer AS aLEFT JOIN CustomerRoom AS bON a.CustID = b.CustID2.3右外关联连接 RIGHT OUTER JOIN(即RIGHT JOIN)右外关联连接和左关联连接正好相反，结果显示右表所有记录，左表符合JOIN条件的信息显示，不符合的置空。举例：SELECT *FROM Customer AS aRIGHT JOIN CustomerRoo
8、m AS bON a.CustID = b.CustID2.4完全外关联连接 FULL OUTER JOIN(即FULL JOIN)完全外关联连接结果显示左右两表的全部记录，符合join条件的信息显示，不符合的置空。SELECT *FROM Customer AS aFULL JOIN CustomerRoom AS bON a.CustID = b.CustID2.5合并行关联连接 UNION，UNION ALLSELECT *INTO Customer_tempFROM CustomerWHERE CUSTID = 1合并行关联连接 UNION结果满足条件的行举例：SELECT * FRO
9、M CustomerUNIONSELECT * FROM Customer_temp合并行关联连接 UNION ALL结果合并两个表中的所有行举例：SELECT * FROM CustomerUNION ALLSELECT * FROM Customer_temp3SQL SERVER的子查询子查询是指将一条SQL Sever语句嵌入到另一条SQL Sever语句中。数据库引擎将子查询做为虚表执行查询操作，提供了一种进一步有效的方式来表示WHERE子句中的条件。子查询就是在SELECT,INSERT,UPDATE,DELETE中都可以使用。3.1子查询=举例：子查询中返回1个值SELECT *
10、 FROM Customer WHERE CustID = (SELECT MAX(CustID) FROM CustomerRoom)3.2子查询IN举例：子查询中返回多个值SELECT * FROM Customer WHERE CustID IN (SELECT CustID FROM CustomerRoom) 或IN（1,2,3）3.3子查询EXISTS举例：SELECT * FROM Customer AS a WHERE EXISTS (SELECT 1 FROM CustomerRoom WHERE CustID = a.CustID)注：EXISTS除了子查询外还可以有其它的
11、用途：举例：IF EXISTS(SELECT 1 FROM Customer WHERE CustID = 1)BEGINPRINT 存在END4SQL SERVER的视图4.1普通视图普通视图是一个包含了一个或多个表的数据列的虚拟表。通常情况下，它仅仅是存储了查询的对象，一个视图可以当作一个表，可以用于存储过程、JOIN、用户自定义函数等等。举例：CREATE VIEW View_Customer_FilterASSELECT a.*,b.RoomSign,b.RoomTypeFROM Customer AS a, CustomerRoom bWHERE a.CustID = b.CustI
12、D4.2索引视图当创建了索引视图之后，视图的结果就会在创建时物化并以物理方式存储在数据库中，减少了运行大表上的复杂查询的开销。缺点是耗费硬盘空间存放，在DML语句如增删改的时候，索引创建会有开销，因为这些一旦执行，索引必须随时更新。举例：CREATE VIEW View_Customer_TableFilterWITH SCHEMABINDINGASSELECT a.CustID,a.CustName,a.City,a.Phone,a.Address,b.RoomSign,b.RoomTypeFROM dbo.Customer AS a,dbo.CustomerRoom bWHERE a.Cu
13、stID = b.CustIDCREATE UNIQUE CLUSTERED INDEX PK_Index_Customer ON View_Customer_TableFilter(CustID)5SQL SERVER的函数，临时表，表变量5.1函数5.1.1内部函数字符串函数常见的有：substring(截取字符串),left(左截取),right(右截取),upper(转换成大写),lower(转换成小写),space(增加空格), ltrim,rtrim,trim(去掉空格),charindex(查找字符串位置),reverse(反转字符),replace(替换)数学函数常见的有：abs(绝对值),floor(返回最小整数)，round(四舍五入),rand(随机数)聚合函数常见的有：a
