模块 2 开发用户登录模块 任务二设计表及存储过程
议程 : SQL Server 数据库对象 数据库 表 存储过程 T-SQL 基础 Select Update Delete Insert
1 数据库对象
1.1SQL Server 数据库对象 数据库 表 存储过程
1.2 数据库 创建数据库 事务日志 数据存储方式
1.2.1 创建数据库 创建数据库需要定义 : 数据库名 数据库大小 存储数据库的文件 CREATE DATABASE Sample ON PRIMARY ( NAME=SampleData, FILENAME='c:\Program Files\..\..\Data\Sample.mdf', SIZE=10MB, MAXSIZE=15MB, FILEGROWTH=20%) LOG ON ( NAME=SampleLog, FILENAME= 'c:\program Files\..\..\Data\Sample.ldf', SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=1MB) COLLATE SQL_Latin1_General_Cp1_CI_AS
1.2.2 创建文件组 sys... sys... sysusers sysobjects Northwind Database... Orders Customers Products OrdHistYear2 OrdHistYear1 C:\ D:\ E:\ Northwind.mdf OrdHist1.ndf OrdHist2.ndf Northwind.Idf Default Filegroup OrderHistoryGroup
1.3 表 创建表 增加列
1.3.1 创建和删除表 创建表 Column name CREATE TABLE dbo.categories (CategoryID CategoryName Description Picture 列 Collation 指定 NULL 或 NOT NULL 计算列 删除表 Data type int IDENTITY (1,1) nvarchar(15) ntext image NULL or NOT NULL NOT NULL, NOT NULL, NULL, NULL)
1.3.2 增加和删除列 增加 ALTER TABLE CategoriesNew ADD Commission money null Customer_name Sales_amount Sales_date Customer ID Commission ALTER TABLE CategoriesNew DROP COLUMN Sales_date 删除
1.4 存储过程 存储过程定义 存储过程处理过程 创建和修改存储过程
1.4.1 存储过程 命名的 T-SQL 语句的集合 ( 批量 SQL) 封装了重复性的任务代码 ( 复用 ) 接受输入参数和返回值 返回状态值表示成功或失败 返回数据行
1.4.2 创建存储过程 在当前数据库中使用 CREATE PROCEDURE 语句创建 USE Northwind CREATE PROC dbo.overdueorders AS SELECT * 至多嵌套 32 级 使用 FROM sp_help dbo.orders 显示相关信息 WHERE RequiredDate < GETDATE() AND ShippedDate IS Null
1.4.3 执行存储过程 执行存储过程 EXEC OverdueOrders 在 INSERT 语句中使用存储过程 INSERT INTO Customers EXEC EmployeeCustomer
1.4.5 使用输入参数 Validate All Incoming Parameter Values First Provide Appropriate Default Values and Include Null Checks CREATE PROCEDURE dbo.[year to Year Sales] @BeginningDate DateTime, @EndingDate DateTime AS IF @BeginningDate IS NULL OR @EndingDate IS NULL BEGIN RAISERROR('NULL values are not allowed', 14, 1) RETURN END SELECT O.ShippedDate, O.OrderID, OS.Subtotal, DATENAME(yy,ShippedDate) AS Year FROM ORDERS O INNER JOIN [Order Subtotals] OS ON O.OrderID = OS.OrderID WHERE O.ShippedDate BETWEEN @BeginningDate AND @EndingDate
1.4.6 执行带参数的存储过程 Passing Values by Parameter Name EXEC AddCustomer @CustomerID = 'ALFKI', @ContactName = 'Maria Anders', @CompanyName = 'Alfreds Futterkiste', @ContactTitle = 'Sales Representative', @Address = 'Obere Str. 57', @City = 'Berlin', @PostalCode = '12209', @Country = 'Germany', @Phone = '030-0074321' Passing Values by Position EXEC AddCustomer 'ALFKI2', 'Alfreds Futterkiste', 'Maria Anders', 'Sales Representative', 'Obere Str. 57', 'Berlin', NULL, '12209', 'Germany', '030-0074321'
1.4.7 使用输出参数返回值 Creating Stored Procedure Executing Stored Procedure Results of Stored Procedure CREATE PROCEDURE dbo.mathtutor @m1 smallint, @m2 smallint, @result smallint OUTPUT AS SET @result = @m1* @m2 DECLARE @answer smallint EXECUTE MathTutor 5,6, @answer OUTPUT SELECT 'The result is: ', @answer The result is: 30
2 T-SQL 基础
2.1T-SQL 基础 T-SQL 概述 SELECT 语句 INSERT 语句 UPDATE 语句 DELETE 语句
2.2Transact-SQL 语句类型 数据定义语句 (DDL Data Definition Language) CREATE ALTER DROP 数据控制语句 (DCL Data Control Language) GRANT DENY REVOKE 数据操纵语句 (DML Data Manipulation Language) SELECT INSERT UPDATE DELETE
2.3Transact-SQL 语法元素 批指令 (go) 注释 Identifiers 数据类型 变量 系统函数 操作符 表达式 控制流语句 保留关键词
2.4 使用 SELECT 语句获取数据 基本语法 多表的关联 子查询
2.4.1SELECT 语句基本语法 Select 列表中指定列 WHERE 子句中选定行 FROM 子句中选定表 Partial Syntax SELECT [ALL DISTINCT] <select_list> FROM {<table_source>} [, n] WHERE <search_condition>
2.4.2 指定列 USE northwind SELECT employeeid, lastname, firstname, title FROM employees employeeid lastname firstname title 1 Davolio Nancy Sales Representative 2 Fuller Andrew Vice President, Sales 3 Leverling Janet Sales Representative 4 Peacock Margaret Sales Representative 5 Buchanan Steven Sales Manager 6 Suyama Michael Sales Representative 7 King Robert Sales Representative 8 Callahan Laura Inside Sales Coordinator 9 Dodsworth Anne Sales Representative
2.4.3 使用 WHERE 子句选定行 USE northwind SELECT employeeid, lastname, firstname, title FROM employees WHERE employeeid = 5 employeeid lastname firstname title 5 Buchanan Steven Sales Manager
2.4.4 过滤数据的方式 使用比较操作符 (>, <, =) 使用字符串比较 (like) 使用逻辑操作符 (and/or) 获取一个范围内的值 (between) 使用值列表作为查询条件 (list) 获取无值的数据 (null)
2.4.5 使用比较操作符 USE northwind SELECT lastname, city FROM employees WHERE country = 'USA Example 1 lastname Davolio Fuller Leverling Peacock Callahan city Seattle Tacoma Kirkland Redmond Seattle
2.5.6 使用字符串比较 USE northwind SELECT companyname FROM customers WHERE companyname LIKE '%Restaurant% companyname GROSELLA-Restaurante Lonesome Pine Restaurant Tortuga Restaurante
2.4.7 使用逻辑操作符 Example 1 USE northwind SELECT productid, productname, supplierid, unitprice FROM products WHERE (productname LIKE 'T%' OR productid = 46) AND (unitprice > 16.00) productid productname supplierid unitprice 14 Tofu 6 23.25 29 Thüringer Rostbratwurst 12 123.79 62 Tarte au sucre 29 49.3
2.4.8 获取一个范围内的值 USE northwind SELECT productname, unitprice FROM products WHERE unitprice BETWEEN 10 AND 20 Example 1 productname unitprice Chai 18 Chang 19 Aniseed Syrup 10 Genen Shouyu 15.5 Pavlova 17.45 Sir Rodney s Scones 10
2.4.9 使用值列表作为查询条件 USE northwind SELECT companyname, country FROM suppliers WHERE country IN ('Japan', 'Italy') Example 1 companyname Tokyo Traders Mayumi s Formaggi Fortini s.r.l. Pasta Buttini s.r.l. country Japan Japan Italy Italy
2.4.10 获取无值的数据 USE northwind SELECT companyname, fax FROM suppliers WHERE fax IS NULL companyname Exotic Liquids New Orleans Cajun Delights Tokyo Traders Cooperativa de Quesos Las Cabras fax NULL NULL NULL NULL
2.4.11 数据排序 Example 1 USE northwind SELECT productid, productname, categoryid, unitprice FROM products ORDER BY categoryid, unitprice DESC productid productname categoryid unitprice 38 Cote de Blaye 1 263.5000 43 Ipoh Coffee 1 46.0000 2 Chang 1 19.0000 63 Vegie-spread 2 43.9000 8 Northwoods Cranberry Sauce 2 40.0000 61 Sirop d'érable 2 28.5000
2.4.12 消除重复行 USE northwind SELECT DISTINCT country FROM suppliers ORDER BY country Example 1 country Australia Brazil Canada Denmark Finland France Germany Italy Japan Netherlands Norway Singapore Spain Sweden UK USA
2.4.13 列出 TOP n 记录 Lists Only the First n Rows of a Result Set Specifies the Range of Values in the ORDER BY Clause Returns Ties if WITH TIES Is Used Example 1 USE northwind SELECT TOP 5 orderid, productid, quantity FROM [order details] ORDER BY quantity DESC Example 2 USE northwind SELECT TOP 5 WITH TIES orderid, productid, quantity FROM [order details] ORDER BY quantity DESC
2.4.14 使用聚合函数 Aggregate function Description AVG COUNT COUNT (*) MAX MIN SUM STDEV STDEVP VAR VARP Average of values in a numeric expression Number of values in an expression Number of selected rows Highest value in the expression Lowest value in the expression Total values in a numeric expression Statistical deviation of all values Statistical deviation for the population Statistical variance of all values Statistical variance of all values for the population
2.4.15 使用 GROUP BY 子句 USE northwind SELECT productid, orderid,quantity FROM orderhist productid orderid quantity 1 1 5 1 1 10 2 1 10 2 2 25 3 1 15 3 2 30 USE northwind SELECT productid,sum(quantity) AS total_quantity FROM orderhist GROUP BY productid Only rows that satisfy the WHERE clause are grouped productid total_quantity 1 15 2 35 3 45 productid total_quantity 2 35 USE northwind SELECT productid,sum(quantity) AS total_quantity FROM orderhist WHERE productid = 2 GROUP BY productid
2.4.16 使用 GROUP BY 和 HAVING 子句 USE northwind SELECT productid, orderid,quantity FROM orderhist productid orderid quantity 1 1 5 1 1 10 2 1 10 2 2 25 USE northwind SELECT productid, SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid HAVING SUM(quantity)>=30 productid total_quantity 2 35 3 45 3 1 15 3 2 30
2.5 修改数据 INSERT UPDATE DELETE
2.5.1 插入一行记录 Must Adhere to Destination Constraints or the INSERT Transaction Fails Use a Column List to Specify Destination Columns Specify a Corresponding List of Values 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')
2.5.3 使用 DELETE 语句删除数据 DELECT 和 WHERE 语句合用删除一条或多条语句 所有删除的行都记录在事务日志中 USE northwind DELETE orders WHERE DATEDIFF(MONTH, shippeddate, GETDATE()) >= 6
2.5.4 使用 TRUNCATE TABLE 语句 TRUNCATE TABLE 语句删除表中所有记录 SQL Server 保留表结构和相关对象 事务日志中仅记录释放数据页面的行为 USE northwind TRUNCATE TABLE orders
2.5.5 基于表中数据进行更新 WHERE 子句中指定要更新的行 SET 关键词指定值 输入值必须与列的数据类型相同 USE northwind UPDATE products SET unitprice = (unitprice * 1.1)
任务 : 设计用户表及存储过程 1. 设计用户表 2. 创建用户存储过程 3. 修改用户存储过程 4. 删除用户存储过程 5. 判断用户登录存储过程
1. 数据库 ; 2. 表 ; 3. 存储过程 ; 4.T-SQL 语句 小结 & 问题讨论