OO
1 SQL Server 2000 2 SQL Server 2000 3 SQL Server 2000 DDL 1 2 3 DML 1 INSERT 2 DELETE 3 UPDATE SELECT DCL 1 SQL Server 2 3 GRANT REVOKE 1 2 1 2 3 4 5 6
1 SQL Server 2000 SQL Server SQL / Microsoft SQL Server 1 SQL Server SQL Server 2000 4 SQL Server 2000 DBA SQL Server SQL Server SQL Agent SQL Server Engine 1 SQL Server SQL Server 2000 SQL Server 2005 2 SQL Server 2000 SQL Server 2000 CPU Pentium 166MHz 64MB 180 MB SQL Server 2000 windows2000 Server Professional SQL Server 2000 1 SQL Server 2000 2 2 SQL Server 2000 2) 3 SQL Server2000 2 SQL Server 2000 3 SQL Server 2000-1 -
3 4 5 SQL Server 6 7 8 9 4 4 10 5 11 Windows 5 12 3 SQL Server 2000 1 Server Manager Server Manager 6 Server Manager SQL Server SQL Server 2000 Windows 6 Server Manager 7 7 2 SQL Server 2000 Enterprise Mamager - 2 -
SQL Server2000 Program > SQL Server -> Enterprise Manager, 8 DBA Enterprise Manager SQL Server 8 Master Model Msdb Tempdb Pubs Northwind SQL Server Test SQL SQL Server SQL Server Pubs 9 SQL Server E R 10 Pubs 9 DDL DBMS - 3-10 Pubs
3 SQL Server 2000 Query Analyzer SQL Program > SQL Server -> Query Analyzer 11 SQL 11 DDL Transact-SQL SQL Server SQL, SQL,, Transact-SQL Microsoft SQL Server SQL Server Transact-SQL Transact-SQL 1 ems CREATE DATABASE database_name 1 ems CREATE DATABASE ems ems 12 SQL 12-4 -
2 CREATE TABLE table_name Column Constraint Table Constraint 2 ems employee dept employee eno ename manager salary deptno eno manager deptno dept deptno dname location deptno employee manager eno ems pubs 12 USE databasename 13 SQL Use ems; Go create table dept(deptno char(3) primary key, deptname char(20) not null, location char(20)); create table employee (eno char(4), ename char(10) not null, manager char(4), salary int, deptno char(3), primary key (eno), Table Constraint Column Constraint foreign key (manager) references employee(eno), foreign key (deptno) references dept(deptno)); 13 SQL 3 SQL-Server CREATE VIEW view-name AS SELECT statement SELECT 3 ems Annualsal manager Annualsal eno ename annualsal annualsal manager manager name clerknum clerknum 14-5 -
Use ems ; create view annualsal(eno,ename,annualsal) as select eno,ename,12*salary from employee Use ems; create view manager (manager,name,clerknum) as select e1.manager,e2.ename,e1.clerknum from (select manager, count(*) as clerknum from employee group by manager having count(*)>0) e1,employee e2 where e1.manager = e2.eno 14 manager e1 DML SQL DML INSERT (DELETE) (UPDATE) DML SQL-Server 1 INSERT SQL INSERT INTO table-name (column-list) VALUES(values-list) 4 ems dept 1 employee 2 15 1 dept D01 Computer School D02 Communication Dept D03 Management School South1-405 South1-304 kejilou-408 use ems; go insert into dept values('d01','computer School','North1-405'); insert into dept values('d02','communication Dept','Notth1-304'); insert into dept values('d03','management School','kejilou-408'); insert into employee values('e01','lu',null,8000,'d01'); insert into employee values('e02','yin','e01',7000,'d01'); insert into employee values('e03','xu','e01',2000,'d01'); insert into employee values('e04','qu','e02',2000,'d01'); insert into employee values('e05','zao ',null,5000,'d02'); insert into employee values('e06','pana ','E05',4000,'D02'); insert into employee values('e07','panb ','E05',3000,'D03'); 15-6 - 2 employee E01 LU null 8000 D01 E02 YIN E01 7000 D01 E03 XU E01 2000 D01 E04 QU E02 2000 D01 E05 ZAO null 5000 D02 E06 PANA E05 4000 D02 E07 PANB E05 3000 D03
5 16 17 use ems; go insert into employee values('e10','wu', E11,8000,'D01'); insert into employee values('e02','yin','e01',7000,'d05'); 16 2 DELETE 17 SQL DELETE FROM table-name [WHERE condition-expression]; DELETE 6 = PANB 18 PANB - 7 -
use ems; go delete from employee where ename='panb' ; 18 PANB 3 UPDATE SQL UPDATE table-name SET column-name = expression [WHERE condition-expression]; UPDATE SET 7 employee salary 10% UPDATE employee SET salary = salary*1.1; 8 ENO= E01 ENO E00 19 UPDATE employee SET eno = E00 WHERE eno= E01 ; 19 8-8 -
SELECT SELECT SQL SELECT SELECT : SELECT query_expression column-list * FROM table_name_list view_name_list [WHERE condition-expression] [GROUP BY [HAVING condition-expression]] [ORDER BY order_expression column [ASC DESC]][, n]; SELECT, Transaction SQL SQL 1 9 ems SELECT * FROM employee; 2 IN LIKE NULL EXISTS BETWEEN WHERE 10 2000-3000, 20 SELECT ename,salary,deptno FROM employee WHERE salary BETWEEN 2000 AND 3000 ORDER BY salary DESC; 20 10 3 11 PANA SELECT e.deptno,d.location FROM employee e,dept d WHERE e.deptno = d.deptno AND e.ename = 'PANA';/*This is a Equijoin*/ 21 11-9 -
12 South1 SELECT eno,ename, e.deptno FROM employee e, dept d WHERE e.deptno = d.deptno AND d.location LIKE 'South1%' ; 22 12 13 Manager Manager SELECT e1.eno, e1.ename,e3.eno, e3.ename as manager_of_manager FROM employee e1, employee e2,employee e3 WHERE e1.manager = e2.eno and e2.manager = e3.eno 23 13 4 14 D01 D02 use ems; SELECT eno,ename,salary FROM employee WHERE (salary BETWEEN (SELECT MIN(salary) FROM employee WHERE deptno='d02') AND (SELECT MAX(salary) FROM employee WHERE deptno='d02')) AND deptno='d01'; 24 14-10 -
DCL 1 SQL Server DBMS SQL Server 2000 SQL Server 2000 SQL Server SQL Server 2000 WINDOWS WINDOWS WINDOWS SQL Server 2000 SQL Server WINDOWS SQL Server SQL Server 25 SQL Server SQL Server syslogins 1) SQL Server SQL Server 25 SQL Server Windows S 26-11 -
2 SQL Server 26 SQL Server 3 Transact_SQL SQL Server SQL Server sp_grantlogin sp_revokelogin sp_decnylogin sp_addlogin sp_droplogin sp_helplogins WINDOWS use ems SQL Server go WINDOWS SQL Server EXEC sp_addlogin qbb,qbb sp_grantlogin SQL Server 27 qbb sp_addlogins 27 sp_addlogin SQL Server qbb sa ems qbb qbb SQL Server SQL Server sp_droplogin use ems go sysuser EXEC sp_droplogin qbb WINDOWS sp_revokelogin 28 qbb WINDOWS SQL Server sp_decnylogin 28 bbb sp_helplogins SQL Server use ems go 29 ems EXEC sp_helplogins 29 2 1 SQL Server - 12 -
sa dbo dbo guest guest master pubs tempdb northwind SQL Server SQL Server 2 ems 30 public 30 SQL Server 3 Transact_SQL SQL Server sp_adduser sp_grangdbaccess sp_dropuser Sp_dropuser sp_revokedbaccess sp_helpuser sp_adduser Sp_dropuser SQL Server 2000 sp_grangdbaccess sp_revokedbaccess - 13 -
guest use ems go EXEC sp_grantdbaccess 'qbb','qbb' sp_grantdbaccess SQL Server NT 31 31 ems qbb qbb sp_revokedbaccess 32 ems qbb sp_helpuser 33 ems ems use ems go EXEC sp revokedbaccess 'qbb' 32 use ems go EXEC sp_helpuser 33 3 SQL Server SQL Server 1 UPDATE DELETE INSERT SELECT EXECUTE 3-14 -
3 SELECT UPDATE DELETE INSERT REFERENCE SELECT UPDATE DELETE INSERT EXECUTE UPDATE SELECT 15 ems qbb employee 34 35 qbb qbb SELECT * FROM employee SELECT * FROM dept, qbb dept Use ems Go Grant select on employee to qbb 34 REVOKE SELECT ON employee FRPM qbb qbb employee 35 2 CREATE 4 4 CREATE DTATBASE CREATE TABLE CREATE VIEW CREATE RULE CREATE DEFAULT CREATE PROCEDURE BACKUP DATABASE BACKUP LOG - 15 -
16 qbb ems 36 Use ems Go GRANT CREATE TABLE TO qbb 36 qbb SQL Server 2000 SQL SQL SQL SQL DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement [FOR {READ ONLY UPDATE[OF column_name [, n]]}] INSENSITIVE,, SCROLL FIRST LAST PRIOR RELATIVE ABSOLUTE NEXT READ ONLY UPDATE [OF column_name [, n]] 2-16 -
OPEN [GLOBAL]cursor_name,, GLOBAL LOCAL FETCH FETCH [[NEXT PRIOR FIRST LAST] FROM cursor_name [INTO @v_name[, n]] FETCH @@FETCH_STATUS FETCH 5 5 Fetch @@FETCH_STATUS 0 FETCH -1 FETCH -2 CLOSE DEALLOCATE CLOSE cursor_name DEALLOCATE cursor_name 17 employee 37-17 -
use ems; go declare cursor_emp cursor global for select * from employee for read only; /* */ declare @v_eno varchar(40),@v_ename varchar(10), @v_manager varchar(40),@v_salary int, @v_deptno varchar(30); /* */ open cursor_emp; /* */ print 'S********************************S'; fetch next from cursor_emp into @v_eno,@v_ename, @v_manager,@v_salary,@v_deptno; /* */ while @@fetch_status = 0 /* */ begin /*select @v_eno,@v_ename,@v_manager,@v_salary,@v_deptno; */ print @v_eno + @v_ename + @v_manager; fetch next from cursor_emp into @v_eno,@v_ename, @v_manager,@v_salary,@v_deptno; end print 'E********************************E'; close global cursor_emp; deallocate global cursor_emp; SQL Server SQL Server 2000 1 1 37 17 38-18 - 38
ems A 2 sp_addumpdevice ( ) ems Use ems emss, EXEC sp_addumpdevice disk, emss, d:\backupdev\ems.bak c:\backupdev\ems.bak 39 emss 39 sp_dropdevice( ) Use ems EXEC sp_dropdevice emss ems emss, 40 2. SQL Server 2000 1) SQL Server 41 42 S 40 emss 41 SQL Server - 19 -
2) BACKUP 18 ems backupdevice_ems D backupdev 43 42 ems use ems /* EXEC sp_addumpdevice 'DISK','backupdevice_ems', 'd:\backupdev\ems.bak'*/ BACKUP DATABASE ems TO backupdevice_ems 43 ems backupdevice_ems 44 ems backupdev use ems BACKUP LOG ems TO backupdevice_ems 44 3 SQL Server 1 45 45-20 -
2 RESTORE DATABASE/LOG ems ems backupdevice_ems restore database ems from backupdevice_ems RESTORE RESTORE 4 SQL Server SQL Server master msdb model Tempdb SQL Server tempdb tempdb master master master Rebuild Master Utility master SQL Server master SQL Server master Rebuild Master Utility master master Rebuild Master Utility master SQL Server program files\microsoft SQL Server\80\tools\binn 46 Master - 21 -
Rebuildm.exe master, 46 Data master master master msdb model SQL Server sp_detach_db SQL Server SQL Server sp_attach_db ems exec sp_detach_db 'smg1','true' ems SQL Server exec sp_attach_db smg1,'d:\program files\mssql\data\smg1.mdf' ems - 22 -
1 (1) Students SNO SNAME AGE SEX Courses CNO CNAME SCORE PC SC SNO CNO GRADE 2 INSERT 6 Students S1 LU 20 M S2 YIN 19 M S3 XU 18 F S4 QU 18 F S6 PAN 14 M S8 DONG 24 M 7 Courses C1 4 M C2 8 M C3 4 F C4 3.5 F C5 4 M 8 SC NULL SNO S1 S2 S3 S4 S6 S8 CNO C1 85 90 89 84 88 87 C2 73 NULL 86 82 75 85 C3 88 80 90 NULL C4 89 85 NULL 92 88 C5 73 NULL 87 2 (1) C2 2 3 C2 4 3 1 C2 10 2 SC 3 S SC S8 4 1 2 80-23 -
5 1 2 SC 3 GRANT students Courses SC 6 1 2 3 4-24 -
1 1 2 SQL Server 2000 3 4 SQL Server 2000 2 1 E-R 2 3 DBMS 4 5-25 -
6 7 DBMS DBMS 8 9 3 2 3 1 6 2 7 3 8 4 9 5 10 4 1 2 3-26 -