ch15.sql

SQLServer2005基础教程_配套课件和脚本

sp_who
sp_monitor
USE AdventureWorks
SELECT *
FROM Production.Product
USE AdventureWorks
DBCC CHECKDB

DROP TABLE employees
 

USE ETravelCom
CREATE TABLE employees(
emp_ID INT NOT NULL,
birthdate DATETIME,
hiredate DATETIME,
salary MONEY)
GO
CREATE TABLE emp_orders(
order_ID INT NOT NULL,
emp_ID INT NOT NULL,
total MONEY,
amount INT)


USE ETravelCom
DECLARE @i INT
DECLARE @emp_ID INT
DECLARE @birthdate DATETIME
DECLARE @hiredate DATETIME
DECLARE @salary MONEY
SET @i = 1
SET @birthdate = GETDATE()
SET @hiredate = GETDATE()
SET @salary = 100.00
WHILE @i < 3001
BEGIN
	INSERT INTO employees(emp_ID, birthdate, hiredate, salary)
	VALUES(@i, @birthdate, @hiredate, @salary)
	SET @i = @i + 1
END

SELECT * FROM emp_orders WHERE order_ID in (1511, 2678)
DELETE FROM emp_orders

USE ETravelCom
DECLARE @i INT, @j INT
SET @i = 3000
SET @j = 10
WHILE @j > 0
BEGIN
	IF @i > 0
	BEGIN
		INSERT INTO emp_orders(order_ID, emp_ID, total)
		VALUES(@i, @j, 5)
		SET @i = @i - 1
	END
	ELSE
	BEGIN
		SET @j = @j -1
		SET @i = 3000
	END
END
GO
UPDATE emp_orders SET amount = 3 WHERE order_ID in (1511, 2678)







USE ETravelCom
SELECT employees.emp_ID, employees.birthdate, emp_orders.total
FROM employees, emp_orders
WHERE employees.emp_ID = emp_orders.emp_ID AND emp_orders.amount = 3









USE ETravelCom
CREATE UNIQUE INDEX i_employees_emp_ID ON employees(emp_ID)
CREATE INDEX i_emp_orders_order_ID ON emp_orders(order_ID)
CREATE INDEX i_emp_orders_emp_ID ON emp_orders(emp_ID)


USE ETravelCom
DROP INDEX employees.i_employees_emp_ID
DROP INDEX emp_orders.i_emp_orders_order_ID
DROP INDEX emp_orders.i_emp_orders_emp_ID
CREATE UNIQUE CLUSTERED INDEX ci_employees_emp_ID ON employees(emp_ID)
CREATE CLUSTERED INDEX ci_emp_orders_emp_ID ON emp_orders(emp_ID)