Examples of common command applications

Appendix B: examples of common command applications
========================================================== ========================================================== ==================================
1. Create and delete Databases
Create Database db1; -- create database db1
Drop database db1; -- delete database db1

2. Create and delete data tables
Create Table T1 (-- Create Table T1, T1 contains two fields
Col1 varchar (100 ),
Col2 int
);
Drop table T1; -- delete table T1

Create a foreign key association between two tables:
Create Table T2 (-- create a master table
T2_c1 char (8) primary key, -- Define the primary key
T2_c2 int
);
Create Table T3 (-- create a slave table
T3_c1 char (8 ),
T3_c2 int,
Foreign key (t3_c1) References T2 (t2_c1) -- defines a foreign key
);
Drop table T3, T2; -- T3 should be deleted before T2

3. Create and delete Indexes
Create index index1 on T1 (col1); -- create a non-clustered index index1
Drop index index1 on T1; -- delete non-clustered index index1

4. View creation and Deletion
Create view view1 -- create view view1
As select * from T1;
Drop view view1; -- delete view view1

5. Create and delete Super Users
Create login log1 -- create a login named log1
With Password = '000000', -- the password is 123456
Default_database = db1; -- the default database is db1.
Go
Exec SP_ADDSRVROLEMEMBER 'log1', 'sysadmin'; -- Grant the highest permission
Go
--------------------------------
Drop login log1; -- delete log1

6. Create, authorize, and delete database users
Create login mylog with Password = '000000', -- create a login first
Default_database = db1;
Go
Use db1;
Go
Create user user1 for login mylog; -- user1 depends on mylog
Go
Grant create table, create view, create procedure, alter any schema to user1; -- authorize
Go
--------------------------------
Drop user user1; -- delete user user1
Drop login mylog; -- delete logon mylog

7. database backup and Restoration
Use master;
Go
Alter database db1 set recovery simple; -- switch to simple recovery mode
Go
Backup database db1 -- back up database db1 to the file db1_disk.bak
To disk = 'd:/backup/db1_disk.bak'
With format;
-----------------------------------------
Use master;
Go
Exec sp_addumpdevice 'disk', 'db1 _ simple', 'd:/backup/db1_disk.bak ';
Go
Restore database db1 -- use the backup file db1_disk.bak to completely back up db1
From db1_simple;

8. Database separation and Addition
Use master;
Go
Exec sp_detach_db 'db1', null, 'true'; -- detach a database
-----------------------------------------
-- Use the isolated db1.mdf for additional operations
Declare @ data_path nvarchar (256 );
Set @ data_path
= 'C:/program files/Microsoft SQL Server/mssql10.mssqlserver/MSSQL/data /';
Exec ('create database db1
On (filename = ''' + @ data_path + 'db1. MDF '')
For attach ');
Go

9. Create, call, and delete stored procedures
Create procedure mypro -- Define a stored procedure with two parameters
@ Min_avgrade numeric (3, 1 ),
@ Max_avgrade numeric (3, 1)
As
Select s_no, s_name, s_sex, s_avgrade, s_dept
From student
Where s_avgrade >=@ min_avgrade and s_avgrade <= @ max_avgrade
;
-----------------------------------------
Exec mypro 50, 88.8; -- call the Stored Procedure
-----------------------------------------
Drop procedure mypro -- delete a stored procedure

10. Trigger creation, application, and deletion
Create trigger mytrigger on T1 -- create a trigger
After Delete
As
Begin
Print 'someone deleted the data in Table T1! '
End
------------------------------------------------------
-- This trigger is triggered when the delete statement is executed, for example:
Delete from T1;
------------------------------------------------------
Drop trigger mytrigger; -- delete a trigger

11. Create, apply, and delete a cursor
Declare mycursor cursor -- create a cursor
Static
For select col1 from T1;
Open mycursor;
Declare @ rowcount integer, @ I integer, @ Col varchar (100 );
Set @ I = 0
Set @ rowcount = @ cursor_rows
While @ I <@ rowcount
Begin
Fetch next from mycursor -- output data in the cursor one by one
Into @ Col;
Print @ Col;
Set @ I = @ I + 1
End
Close mycursor;
Deallocate mycursor; -- delete a cursor

12. view all databases on the server
Select *
From SYS. Databases
Order by name

13. view all data tables in the database
Use db1; -- Query all data tables contained in database db1
Select * From SYS. tables;

14. view the data table structure
Select a. Name field name, B. Name field type, A. max_length Field Length
From SYS. all_columns as
Join SYS. types as B
On a. system_type_id = B. system_type_id
Where object_id = object_id ('t1 ');

15. insert data into a data table
Insert into T1 values ('celebrate the 60 th anniversary of the founding of the People's Republic of China! ', 60); -- insert data to table T1
Insert into T1 values ('Beijing Olympics! ', 29 );
Insert into T1 values ('World Expo Shanghai! ', 51 );

16. Update table data
Update T1
Set col1 = 'celebrate the 60 th anniversary of the founding of the People's Republic of China! '
Where col2 = 60;

17. Delete table data
Delete from T1
Where col2 = 29;

18. Descending Order and conditional Query
Select col1, col2
From T1
Where col2> 50
Order by col2 DESC

19. equi join query
Select t2.t2 _ C1, t2.t2 _ C2, t3.t3 _ C2
From T2
Inner join T3
On (t2.t2 _ C1 = t3.t3 _ C1 );

20. Left outer join query
Select t2.t2 _ C1, t2.t2 _ C2, t3.t3 _ C2
From T2
Left join T3
On (t2.t2 _ C1 = t3.t3 _ C1 );

21. Right outer join query
Select t2.t2 _ C1, t2.t2 _ C2, t3.t3 _ C2
From T2
Right join T3
On (t2.t2 _ C1 = t3.t3 _ C1 );

22. query all external connections
Select t2.t2 _ C1, t2.t2 _ C2, t3.t3 _ C2
From T2
Full join T3
On (t2.t2 _ C1 = t3.t3 _ C1 );

23. If statement
Declare @ N1 int, @ N2 int
Set @ n1 = 2
Set @ n2 = 20
If @ N1 <@ N2
Begin
Print '@ N1
Less than @ N2 ';
End
Else if @ n1 = @ N2
Begin
Print '@ N1
Equal to @ N2 ';
End
Else
Begin
Print '@ N1
Greater than @ N2 ';
End
Go

24. Case statements
Select student ID = s_no, name and hobby =
Case s_no
When '100' then' Li Hao, swimming'
When '100' then' Wang Ya, Mountaineer'
Else 'no such person'
End
From student

25. While statement
Declare @ n int, @ I int, @ resulet int; -- calculate the factorial n of n!
Set @ n = 5;
Set @ I = 1;
Set @ resulet = 1;
While @ I <= @ n
Begin
Set @ resulet = @ resulet * @ I;
Set @ I = @ I + 1;
End
Print @ resulet

26. Conversion of string data and numeric data
Declare @ F float, @ s varchar (20); -- numeric data → string data
Set @ F = 3.14159;
Set @ s = convert (varchar (20), @ F );
--------------------------------------------------
Declare @ F float, @ s varchar (20); -- string data → numeric data
Set @ s = '1. 73205 ';
Set @ F = convert (float, @ s );

27. Conversion of time and string data
Declare @ DT datetime, @ STR varchar (30); -- time data → string data
Set @ dt = getdate ();
-Set @ STR = convert (varchar (30), @ DT, 114 );
Set @ STR = convert (varchar (30), @ DT, 111 );
Print @ Str
--------------------------------------------------
Declare @ DT datetime, @ STR varchar (30); -- string data → time data
Set @ STR = '2014/1/12 ';
Set @ dt = convert (datetime, @ Str );
Print @ DT;

28. Obtain names of servers, databases, applications, users, and logon.
Print host_name (); -- get the server name
Print db_name (); -- get the Database Name
Print app_name (); -- get the application name
Print user_name (); -- get the database username
Print suser_sname (); -- get the login name