选择sql查询语句大全文库（SQL query Daquan Library）

sql查询语句大全文库SQL query Daquan LibrarySQL query Daquan.Txt library to stay up late, because thecourage to end the day; stay in bed because of the courage tostart the day. Friend is who can see you and love you. SQL querystatement Daquan

Statement function

1, data manipulation

Select -- retrieving rows and columns from a database tableInsert -- adding new data rows to a database table

Delete -- deleting data rows from database tables

Update -- update the data in the database table

2, data definition

Create TABLE -- create a database table

Drop TABLE -- delete tables from the database

Alter TABLE -- modifying the structure of the database tableCreate VIEW - create a view

Drop VIEW -- delete views from the database

Create INDEX -- create an index for the database table

Drop INDEX -- delete index from database

Create PROCEDURE - creating a stored procedure

Drop PROCEDURE -- delete stored procedures from the databaseCreate TRIGGER - creating a trigger

Drop TRIGGER -- delete triggers from the database

Create SCHEMA -- add a new schema to the database

Drop SCHEMA - deletes a schema from the database

Create DOMAIN -- creating a data range

Alter DOMAIN - change domain definition

Drop DOMAIN -- deletes a domain from the database

3, data control

GRANT -- grant user access rights

DENY -- deny user access

REVOKE -- removing user access rights

4, transaction control

COMMIT - end the current transaction

ROLLBACK -- abort the current transaction

SET TRANSACTION -- defining the access characteristics ofcurrent transaction data

5, programmed SQL

DECLARE -- setting cursors for queries

EXPLAN -- description of data access plan for queriesOPEN - retrieve the query results and open a cursorFETCH -- retrieve a row of query results

CLOSE - close the cursor

PREPARE -- preparing SQL statements for dynamic executionEXECUTE -- dynamic execution of SQL statements

DESCRIBE - describe the prepared query

6, local variables

Declare @id char (10)

--set @id =' 10010001'

Select @id =' 10010001'

7, global variables

- must be at the beginning of @ @8, IF statement

Declare @x int @y int @z int

Select @x = 1 @y = 2 @z=3

If @x > @y

Print'x > Y'- print string'x > Y'Else if @y > @z

Print'y > z'

Else print'z > Y'

9, CASE statement

Use Pangu

Update employee

Set e_wage =

Case

When job_level = ' 1' then e_wage*1.08

When job_level = '2' then e_wage*1.07

When job_level = '3' then e_wage*1.06

Else e_wage*1.05

End

10, WHILE CONTINUE BREAK statement

Declare @x int @y int @c int

Select @x = 1 @y=1

While @x < 3

Begin

Print @x -- the value of the print variable xWhile @y < 3

Begin

Select @c =100*@x+ @y

Print @c -- the value of the print variable C

Select @y = @y + 1

End

Select @x = @x + 1

Select @y = 1

End

11, WAITFOR statement

The example waits for 1 hours, 2 minutes, 3 seconds to executethe Select statement

Waitfor delay '01:02:03'

从雇员中选择*

——例等到晚上11点零8分后才执行选择语句

等待时间的23:08:00”

从雇员中选择*

12、选择语句

选择* 列名从table_name 表名在col umn_name算子值例 宿主

SELECT * FROM stock_information哪里StockID中= str NIDs tockname = “str_name”

s tockname像“%发现这%stockname如[A-Za-z] % ---------  [ ]指定值的范围s tockname喜欢[ ^调频] % --------- ^排除指定范围

---------只能在使用像关键字的哪里子句中使用通配符

或stockpath = “stock_path”

或sto cknumb er1000

和股票指数= 24

不stocksex = '人's tocknumber之间的20和100

在sto cknumb er 10、 20、 30

通过StockID中倒序ASC ---------排序 desc -降序 ASC升序由1,2 ---------通过列号秩序stockname = 选择从哪里stockname stock_information StockID中= 4

---------子查询

---------除非能确保内层选择只返回一个行的值

---------否则应在外层哪里子句中用一个在限定符

选择不同的c o lumn_name形式tab l e_name ---------不同指定检索独有的列值不重复

选择s tocknumber “s tocknumber + 10 + 10 = sto cknumb er从t ab l e_n ame

选择stockname “stocknumber” =数*从table_name组s tockname---------组将表按行分组指定列中有相同的值

具有计数* = 2 ---------具有选定指定的组

选择*

从表1表2

在table1. id * = tabl e2. id --------左外部连接表1中有的而表中没有得以空表示table1. id = * table2. id --------右外部连接

选择s tockname从表1

联盟[全部] -----联盟合并查询结果集全保留重复行

选择s tockname从表2

13、插入语句

插入tab l e_names toc k_names toc k_numb er值 “XXX” 、 “XXXX” 值选择stockname s tocknumber从stock_tabl e2 ---价值为选择语句

14、更新语句

更新tabl e_name集stockname = “XXX” [ 3 ]在Stock ID中=s tockname =默认stockname = nulls tocknumber = st ockname + 4

15、删除语句

删除从table_name哪里StockID中= 3

截断t ab l e_n ame -----------删除表中所有行仍保持表的完整性表t ab l e_nam e---------------完全删除表

16、 ALTER TABLE *** ---修改数据库表结构

修改表database.owner.table_name添加column_name char 2空. . . . .