SQL Server basic skill (Internal Skill Enhancement) blog category: SQL

Basic functions of SQL Server (enhanced internal strength)

Http://newerdragon.iteye.com/blog/1666157

SQL serversqlsql Server

-- Data operation select -- retrieve data rows and columns from the database table insert -- add new data rows to the database table delete -- delete data rows from the database table update -- update data in the database table -- Data Definition create Table -- create a database table drop table -- delete a table from data alter table -- modify the database table structure create View -- create a view drop View -- delete a view from data create index -- create an index in a database table drop index -- delete an index from the database create procedure -- create a stored procedure drop procedure -- delete a stored procedure create trigger from the database -- create a trigger drop trigger -- from the database delete trigger create schema -- Add a new mode drop schema to the database -- delete a mode create domain from the database -- create a data value domain drop Domain -- delete a domain from the database alter Domain -- change the domain definition -- Grant Data Control -- Grant the user access permission deny -- deny the user access revoke -- revoke the user access permission -- programmatic sqldeclare -- set the cursor for the query to explain -- open for the query description access plan -- search query results open a cursor fetch -- search a row of query results close -- close the cursor prepare -- prepare SQL statement for dynamic execution execute -- dynamically Execute SQL statement describe -- describe prepared query -- local variable -- global variable (must start) declare @ ID char (10) -- Define a variable set @ ID = '000000' -- assign a select @ ID = '000000' to the variable -- assign a value to the variable -- use declare @ x int @ Y int @ Z for if else intselect @ x = 1 @ Y = 2 @ z = 3if @ x> @ yprint 'x> y' -- print the else string if @ Y> @ zprint 'y> z'elseprint' z> y' -- caseuse panguupdate employee set e_wage = case when job_leve1 = '1' then e_wage * 1.08 case when job_leve1 = '2' then e_wage * 1.07 case when job_level = '3' then e_wage * 1.06 else e_wage * 1.05end -- while continue breakdeclare @ x int @ Y int @ C intselect @ x = 1 @ Y = 1 while @ Y <3 begin select @ C = 100 * @ x + @ yprint @ C -- print the end select @ x = @ x + 1 select @ Y = 1end -- waitfor example: the SELECT statement waitfor delay '01: 02: 03 'select * from employee -- Example: the SELECT statement waitfor time '23: 08: 00 'select * from employee -- the wildcard select * from table where s_name like '[A-Za-Z] %' can only be used in the WHERE clause of the like keyword. -- [] specifies the value. range of select * from table where s_name like '[^ F-M]' -- ^ exclude specified range select * from Table order by s_name ASC -- ASC ascending select * from Table order by s_name DESC -- DESC descending order -- subquery -- unless the inner select clause can only return the value of one row -- otherwise, use the in qualifier s_name = (select s_name from table where id = 1) -- distinct remove repeated field columns select distinct s_name from table -- left external link. Values in T1 and null in T2 indicate select * from T1, t2 where t1.id * = t2.idselect * from T1, T2 where t1.id = * t2.id -- right external link -- Union merge query result set, all save duplicate rows select s_name from T1 Union [all] insert into table (s_name, s_number) value ('xxx', 'xxx') value (select s_name, s_number from table) -- add update table set s_name = default from the query value in the SELECT statement -- change s_name to the default value of truncate table -- delete all rows in the table and keep table integrity. Drop table -- delete table completely