SQL Server基础（三）

liuhongyu

　　1、表数据操作
　　表数据操作有四条基本语句：insert向数据表中添加记录，delete删除数据表中的记录，update修改数据表中的记录，select选择数据表中的记录
　　DML(Data Manipulation Language)数据操纵语言：利用insert、select、update、delete等语句来操作数据库对象所包含的数据。
　　DDL用于定义和管理对象，例如数据库、数据表以及视图。DDL语句通常包括每个对象的create、alter以及drop命令。
　　总之，DML是数据语言，而DDL是定义语言
　　2、用insert和values插入行：
　　必须遵守表的约束，否则 INSERT 事务将失败，使用字段列表指定用于保存新数据的列，指定相应的值列表，VALUES：引入要插入的数据值的列表
　　例：为northwind数据库中的customers表增加一行新数据
　　USE northwind
　　INSERT customers(customerid, companyname, contactname, contacttitle,address, city, region, postalcode, country, phone,fax)
　　VALUES (‘PECOF’, ‘Pecos Coffee Company’,’Michael Dunn’,’Owner’, ‘1900 Oak Street’, ‘Vancouver’, ‘BC’,’V3F 2K1’, ‘Canada’,’(604) 555-3392’,’(604) 555-7293’)
　　GO
　　3、用insert和select插入行：
　　所有满足 SELECT 语句的行都被插入最外层，必须检验被插入了新行的表是否存在数据库中，确保数据类型是兼容的，是否存在缺省值，或所有被忽略的列是否允许空值
　　例：为northwind数据库中的customers表增加新数据
　　USE northwind
　　INSERT customers
　　SELECT
　　substring(firstname, 1, 3)

　　+ substring (lastname, 1,2),lastname, firstname,>　　FROM employees
　　GO
　　4、可使用 SELECT INTO 语句创建一个表并在同一操作中往表里插入行：
　　（1）可以创建局部的或全局的临时性表，当前会话结束，局部临时性表的空间被收回，当表不再被任何会话使用，全局临时表的空间会被收回
　　（2）在选择列表中，必须为列起一个别名或指定新标的列的名字
　　例：从northwind数据库中的products表中查询部分数据，并插入到临时表中，列包括： productname AS products,unitprice AS price, (unitprice * 0.1) AS tax
　　USE northwind
　　SELECT productname AS products,unitprice AS price,
　　(unitprice * 0.1) AS tax
　　INTO #pricetable
　　FROM products
　　GO
　　5、使用top限制插入的行：
　　USE northwind
　　SELECT top 10
　　productname AS products,unitprice AS price,
　　(unitprice * 1.1) AS tax
　　INTO #pricetable
　　FROM products
　　GO
　　Select * from #pricetable
　　如果要查询后10行，可以先倒序排列，再查top10
　　6、插入部分数据：如果列有缺省值或允许空值，就可以在 INSERT 语句中忽略该列，SQL Server 将自动插入该值
　　注意：只需列出正为insert语句提供数据所在列的名
　　在字段列表中指定要提供值的列
　　如果具有identity属性、允许缺省值或空值，则不必在字段列表中指定列
　　不适用单引号，而是通过输入null来输入控制
　　例：为northwind数据库中的shippers表增加新数据，只插入Companyname字段的数据
　　USE northwind
　　INSERT shippers (companyname)
　　VALUES (‘Fitch & Mather’)
　　GO
　　SELECT * FROM shippers
　　WHERE companyname = ‘Fitch & Mather’
　　GO
　　7、使用delete语句：
　　（1）可在delete语句中加入where语句，否则将删除所有的行
　　（2）可使用top限制删除的行
　　（3）每个被删除的行都被存入事务日志中
　　USE northwind
　　delete shippers
　　WHERE companyname = ‘Fitch & Mather’
　　GO
　　8、使用truncate table语句：
　　（1）删除表中所有的行，但保留表的结构和与之相关的对象
　　（2）truncate table语句比delete语句执行速度快
　　（3）如果表中有identity列，truncate table语句会重新设置原始数据
　　USE northwind
　　TRUNCATE TABLE orders
　　GO
　　9、删除基于其他表的行：
　　（1）使用附加的from子句
　　第一个from子句指示哪一个表中的行被删除
　　第二个from语句引入一个连接或准则，作为delete语句限制的条件
　　（2）在where子句中指定条件
　　使用子查询决定删除那些行
　　DELETE [FROM] {表名 | 视图名}[FROM {} [,…n]][WHERE 搜索条件 ]
　　10、根据表中数据更新行：
　　（1）使用 WHERE 子句指出哪些行要更新
　　（2）用 SET 关键字指定新值
　　（3）输入值的类型必须与所定义的数据类型一致
　　（4）不能更新违反任何完整性约束的行
　　（5）每次只能修改一个表中的数据
　　（6）可以同时把一个或多个列、变量放在一个表达式中
　　例：更新northwind数据库中的products表使unitprice字段为原有值的1.5倍
　　USE northwind
　　UPDATE products
　　SET unitprice = (unitprice * 1.5)，列名=新值
　　GO
　　11、查询表的全部内容：Select *域[列名] from 表
　　Select * from product
　　12、比较操作符
　　= 等于 > 大于 < 小于 >= 大于或等于 and>or
　　从左到右
　　例：查询northwind数据库中products表中的数据
　　要求一：unitprice大于16
　　要求二：productname以字母T开头，或者productid为16
　　USE northwind
　　SELECT productid, productname, supplierid, unitprice FROM  products WHERE (productname LIKE 'T%' OR productid = 16)   AND  (unitprice > 16.00)
　　GO
　　14、检索一定范围的值：使用between来查询在一定范围内的值
　　注意：结果集中，包含范围内的边缘值
　　尽量使用between，而不用and和比较操作符表示的表达式
　　要使结果集不包含边缘值，则使用（>x and 500
　　ORDER BY SUM(quantity)
　　GO
　　22、连接：把来自两个表的信息放置在一个结果集中
　　分类：内部连接、外部连接、完全连接、交叉连接
　　（1）内部连接：是连接类型中最普通的一种，与大多数连接一样，内部连接根据一个或几个相同的字段将记录匹配在一起，但是内部连接仅仅返回那些存在的字段匹配的记录。
　　例1：Use northwind
　　select products.*,suppliers.supplierid from products inner
　　join suppliers on products.supplierid=suppliers.supplierid
　　例2：use pubs
　　select a.au_lname+', '+a.au_fname as author,t.title from authors a

　　join>　　（2）外部连接：外部连接时必需跟上左侧连接还是右侧连接。
　　例：Use pubs
　　select discounttype,discount,s.stor_name from discounts d
　　left outer join stores s on d.stor_id=s.stor_id
　　select discounttype,discount,s.stor_name from discounts d
　　right join stores s on d.stor_id=s.stor_id
　　（3）完全连接是将join两侧的数据全部匹配，并返回所有记录。
　　例：Use pubs
　　select discounttype,discount,s.stor_name from discounts d
　　full join stores s on d.stor_id=s.stor_id
　　（4）交叉连接不使用on运算符，而将join左侧的所有记录与另一侧的所有记录连接，返回的是join两侧表记录的笛卡尔积。
　　例：Use pubs
　　select discounttype,discount,s.stor_name from discounts d
　　cross join stores s
　　（5）Union是一个特殊的运算符，用于将两个或两个以上的查询产生一个结果集。
　　例：use northwind
　　select companyname ,
　　address,
　　city
　　from customers
　　union
　　select companyname,
　　address,
　　city
　　from suppliers
　　23、子查询
　　（1）IN关键字用来判断一个表中指定列的值是否包含在已定义的列表中，或在另一个表中。
　　例：use northwind
　　select * from customers where  customerid in
　　(select distinct customerid from orders)
　　（2）EXISTS表示子查询不需要返回多行数据，而只需要返回一个真值或假值。作用是在where子句中测试子查询返回的行是否存在。
　　例：use northwind
　　select * from orders where
　　Exists(select *  from customers where customerid='alfki' )
　　（3）Any表示，子查询返回值中至少有一个值与条件比较为真，就满足搜索条件。
　　All表示，子查询返回的每个值与条件比较都为真，才满足条件
　　Any只要与一个子查询结果运算是真成立
　　All必须都与子查询的结果运算是真才成立
　　例：use northwind
　　select orderid,  freight from orders where freight