SQL語句大全 收藏_百度文庫

1、SQL語句大全 收藏 -語 句 功 能-數(shù)據(jù)操作SELECT -從數(shù)據(jù)庫表中檢索數(shù)據(jù)行和列INSERT -向數(shù)據(jù)庫表添加新數(shù)據(jù)行DELETE -從數(shù)據(jù)庫表中刪除數(shù)據(jù)行UPDATE -更新數(shù)據(jù)庫表中的數(shù)據(jù)-數(shù)據(jù)定義CREATE TABLE -創(chuàng)建一個(gè)數(shù)據(jù)庫表DROP TABLE -從數(shù)據(jù)庫中刪除表ALTER TABLE -修改數(shù)據(jù)庫表結(jié)構(gòu)CREATE VIEW -創(chuàng)建一個(gè)視圖DROP VIEW -從數(shù)據(jù)庫中刪除視圖CREATE INDEX -為數(shù)據(jù)庫表創(chuàng)建一個(gè)索引DROP INDEX -從數(shù)據(jù)庫中刪除索引CREATE PROCEDURE -創(chuàng)建一個(gè)存儲過程DROP PROCEDURE -從數(shù)據(jù)庫

2、中刪除存儲過程CREATE TRIGGER -創(chuàng)建一個(gè)觸發(fā)器DROP TRIGGER -從數(shù)據(jù)庫中刪除觸發(fā)器CREATE SCHEMA -向數(shù)據(jù)庫添加一個(gè)新模式DROP SCHEMA -從數(shù)據(jù)庫中刪除一個(gè)模式CREATE DOMAIN -創(chuàng)建一個(gè)數(shù)據(jù)值域ALTER DOMAIN -改變域定義DROP DOMAIN -從數(shù)據(jù)庫中刪除一個(gè)域-數(shù)據(jù)控制GRANT -授予用戶訪問權(quán)限D(zhuǎn)ENY -拒絕用戶訪問REVOKE -解除用戶訪問權(quán)限-事務(wù)控制COMMIT -結(jié)束當(dāng)前事務(wù)ROLLBACK -中止當(dāng)前事務(wù)SET TRANSACTION -定義當(dāng)前事務(wù)數(shù)據(jù)訪問特征-程序化SQLDECLARE -為查詢

3、設(shè)定游標(biāo)EXPLAN -為查詢描述數(shù)據(jù)訪問計(jì)劃OPEN -檢索查詢結(jié)果打開一個(gè)游標(biāo)FETCH -檢索一行查詢結(jié)果CLOSE -關(guān)閉游標(biāo)PREPARE -為動態(tài)執(zhí)行準(zhǔn)備SQL 語句EXECUTE -動態(tài)地執(zhí)行SQL 語句DESCRIBE -描述準(zhǔn)備好的查詢 -局部變量declare id char(10-set id = '10010001'select id = '10010001' -全局變量-必須以開頭-IF ELSEdeclare x int y int z intselect x = 1 y = 2 z=3if x > yprint 'x

4、> y' -打印字符串'x > y'else if y > zprint 'y > z'else print 'z > y'-CASEuse panguupdate employeeset e_wage =casewhen job_level = 1 then e_wage*1.08when job_level = 2 then e_wage*1.07when job_level = 3 then e_wage*1.06else e_wage*1.05end-WHILE CONTINUE BREAKdecla

5、re x int y int c intselect x = 1 y=1while x < 3beginprint x -打印變量x 的值while y < 3beginselect c = 100*x + yprint c -打印變量c 的值select y = y + 1endselect x = x + 1select y = 1end -WAITFOR-例 等待1 小時(shí)2 分零3 秒后才執(zhí)行SELECT 語句waitfor delay 01:02:03select * from employee-例 等到晚上11 點(diǎn)零8 分后才執(zhí)行SELECT 語句waitfor time

6、 23:08:00select * from employee*SELECT*select *(列名 from table_name(表名 where column_name operator valueex:(宿主select * from stock_information where stockid = str(nidstockname = 'str_name' stockname like '% find this %' stockname like 'a-zA-Z%' - (指定值的范圍stockname like 'F-M%&

7、#39; - (排除指定范圍- 只能在使用like關(guān)鍵字的where子句中使用通配符or stockpath = 'stock_path'or stocknumber < 1000and stockindex = 24not stock* = 'man'stocknumber between 20 and 100stocknumber in(10,20,30order by stockid desc(asc - 排序，desc-降序，asc-升序order by 1,2 - by列號stockname = (select stockname from st

8、ock_information where stockid = 4- 子查詢- 除非能確保內(nèi)層select只返回一個(gè)行的值，- 否則應(yīng)在外層where子句中用一個(gè)in限定符select distinct column_name form table_name - distinct指定檢索獨(dú)有的列值，不重復(fù)select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_nameselect stockname , "stocknumber" = count(* from table_

9、name group by stockname- group by 將表按行分組,指定列中有相同的值having count(* = 2 - having選定指定的組select * from table1, table2 where table1.id *= table2.id - 左外部連接，table1中有的而table2中沒有得以null表示table1.id =* table2.id - 右外部連接 select stockname from table1union all - union合并查詢結(jié)果集，all-保留重復(fù)行select stockname from table2*in

10、sert*insert into table_name (Stock_name,Stock_number value ("xxx","xxxx"value (select Stockname , Stocknumber from Stock_table2-value為select語句*update*update table_name set Stockname = "xxx" where Stockid = 3Stockname = defaultStockname = nullStocknumber = Stockname + 4*

11、delete*delete from table_name where Stockid = 3truncate table_name - 刪除表中所有行，仍保持表的完整性drop table table_name - 完全刪除表*alter table* - 修改數(shù)據(jù)庫表結(jié)構(gòu)alter table database.owner.table_name add column_name char(2 null .sp_help table_name - 顯示表已有特征create table table_name (name char(20, age smallint, lname varchar(

12、30insert into table_name select . - 實(shí)現(xiàn)刪除列的方法（創(chuàng)建新表）alter table table_name drop constraint Stockname_default - 刪除Stockname的default約束*function(/*常用函數(shù)*/*-統(tǒng)計(jì)函數(shù)-AVG -求平均值COUNT -統(tǒng)計(jì)數(shù)目MAX -求最大值MIN -求最小值SUM -求和-AVGuse panguselect avg(e_wage as dept_avgWagefrom employeegroup by dept_id-MAX-求工資最高的員工姓名use pangus

13、elect e_namefrom employeewhere e_wage =(select max(e_wagefrom employee-STDEV(-STDEV(函數(shù)返回表達(dá)式中所有數(shù)據(jù)的標(biāo)準(zhǔn)差-STDEVP(-STDEVP(函數(shù)返回總體標(biāo)準(zhǔn)差-VAR(-VAR(函數(shù)返回表達(dá)式中所有值的統(tǒng)計(jì)變異數(shù)-VARP(-VARP(函數(shù)返回總體變異數(shù)-算術(shù)函數(shù)-/*三角函數(shù)*/SIN(float_expression -返回以弧度表示的角的正弦COS(float_expression -返回以弧度表示的角的余弦TAN(float_expression -返回以弧度表示的角的正切COT(float_e

14、xpression -返回以弧度表示的角的余切/*反三角函數(shù)*/ASIN(float_expression -返回正弦是FLOAT 值的以弧度表示的角ACOS(float_expression -返回余弦是FLOAT 值的以弧度表示的角ATAN(float_expression -返回正切是FLOAT 值的以弧度表示的角ATAN2(float_expression1,float_expression2 -返回正切是float_expression1 /float_expres-sion2的以弧度表示的角DEGREES(numeric_expression-把弧度轉(zhuǎn)換為角度返回與表達(dá)式相同的數(shù)據(jù)

15、類型可為-INTEGER/MONEY/REAL/FLOAT 類型RADIANS(numeric_expression -把角度轉(zhuǎn)換為弧度返回與表達(dá)式相同的數(shù)據(jù)類型可為-INTEGER/MONEY/REAL/FLOAT 類型EXP(float_expression -返回表達(dá)式的指數(shù)值LOG(float_expression -返回表達(dá)式的自然對數(shù)值LOG10(float_expression-返回表達(dá)式的以10 為底的對數(shù)值SQRT(float_expression -返回表達(dá)式的平方根/*取近似值函數(shù)*/CEILING(numeric_expression -返回>=表達(dá)式的最小整數(shù)返

16、回的數(shù)據(jù)類型與表達(dá)式相同可為-INTEGER/MONEY/REAL/FLOAT 類型FLOOR(numeric_expression -返回<=表達(dá)式的最小整數(shù)返回的數(shù)據(jù)類型與表達(dá)式相同可為-INTEGER/MONEY/REAL/FLOAT 類型ROUND(numeric_expression -返回以integer_expression 為精度的四舍五入值返回的數(shù)據(jù)-類型與表達(dá)式相同可為INTEGER/MONEY/REAL/FLOAT 類型ABS(numeric_expression -返回表達(dá)式的絕對值返回的數(shù)據(jù)類型與表達(dá)式相同可為-INTEGER/MONEY/REAL/FLOAT

17、類型SIGN(numeric_expression -測試參數(shù)的正負(fù)號返回0 零值1 正數(shù)或-1 負(fù)數(shù)返回的數(shù)據(jù)類型-與表達(dá)式相同可為INTEGER/MONEY/REAL/FLOAT 類型PI( -返回值為 即3.1415926535897936RAND(integer_expression -用任選的integer_expression做種子值得出0-1 間的隨機(jī)浮點(diǎn)數(shù)-字符串函數(shù)-ASCII( -函數(shù)返回字符表達(dá)式最左端字符的ASCII 碼值CHAR( -函數(shù)用于將ASCII 碼轉(zhuǎn)換為字符-如果沒有輸入0 255 之間的ASCII 碼值CHAR 函數(shù)會返回一個(gè)NULL 值LOWER( -函

18、數(shù)把字符串全部轉(zhuǎn)換為小寫UPPER( -函數(shù)把字符串全部轉(zhuǎn)換為大寫STR( -函數(shù)把數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)LTRIM( -函數(shù)把字符串頭部的空格去掉RTRIM( -函數(shù)把字符串尾部的空格去掉LEFT(,RIGHT(,SUBSTRING( -函數(shù)返回部分字符串CHARINDEX(,PATINDEX( -函數(shù)返回字符串中某個(gè)指定的子串出現(xiàn)的開始位置SOUNDEX( -函數(shù)返回一個(gè)四位字符碼 -SOUNDEX函數(shù)可用來查找聲音相似的字符串但SOUNDEX函數(shù)對數(shù)字和漢字均只返回0 值 DIFFERENCE( -函數(shù)返回由SOUNDEX 函數(shù)返回的兩個(gè)字符表達(dá)式的值的差異-0 兩個(gè)SOUNDEX

19、函數(shù)返回值的第一個(gè)字符不同-1 兩個(gè)SOUNDEX 函數(shù)返回值的第一個(gè)字符相同-2 兩個(gè)SOUNDEX 函數(shù)返回值的第一二個(gè)字符相同-3 兩個(gè)SOUNDEX 函數(shù)返回值的第一二三個(gè)字符相同-4 兩個(gè)SOUNDEX 函數(shù)返回值完全相同QUOTENAME( -函數(shù)返回被特定字符括起來的字符串/*select quotename('abc', '' quotename('abc'運(yùn)行結(jié)果如下-abc abc*/REPLICATE( -函數(shù)返回一個(gè)重復(fù)character_expression 指定次數(shù)的字符串/*select replicate('

20、;abc', 3 replicate( 'abc', -2運(yùn)行結(jié)果如下- -abcabcabc NULL*/REVERSE( -函數(shù)將指定的字符串的字符排列順序顛倒REPLACE( -函數(shù)返回被替換了指定子串的字符串/*select replace('abc123g', '123', 'def'運(yùn)行結(jié)果如下- -abcdefg*/SPACE( -函數(shù)返回一個(gè)有指定長度的空白字符串STUFF( -函數(shù)用另一子串替換字符串指定位置長度的子串-數(shù)據(jù)類型轉(zhuǎn)換函數(shù)-CAST( 函數(shù)語法如下CAST( ( AS length CONV

21、ERT( 函數(shù)語法如下CONVERT( ( length , , style select cast(100+99 as char convert(varchar(12, getdate(運(yùn)行結(jié)果如下- -199 Jan 15 2000-日期函數(shù)-DAY( -函數(shù)返回date_expression 中的日期值MONTH( -函數(shù)返回date_expression 中的月份值YEAR( -函數(shù)返回date_expression 中的年份值DATEADD( , , -函數(shù)返回指定日期date 加上指定的額外日期間隔number 產(chǎn)生的新日期DATEDIFF( , , -函數(shù)返回兩個(gè)指定日期在dat

22、epart 方面的不同之處DATENAME( , - 函數(shù)以字符串的形式返回日期的指定部分DATEPART( , - 函數(shù)以整數(shù)值的形式返回日期的指定部分GETDATE( -函數(shù)以DATETIME 的缺省格式返回系統(tǒng)當(dāng)前的日期和時(shí)間-系統(tǒng)函數(shù)-APP_NAME( -函數(shù)返回當(dāng)前執(zhí)行的應(yīng)用程序的名稱COALESCE( -函數(shù)返回眾多表達(dá)式中第一個(gè)非NULL 表達(dá)式的值COL_LENGTH(<'table_name'>, <'column_name'> -函數(shù)返回表中指定字段的長度值COL_NAME( , - 函數(shù)返回表中指定字段的名稱即列名

23、DATALENGTH( -函數(shù)返回?cái)?shù)據(jù)表達(dá)式的數(shù)據(jù)的實(shí)際長度DB_ID('database_name' -函數(shù)返回?cái)?shù)據(jù)庫的編號DB_NAME(database_id -函數(shù)返回?cái)?shù)據(jù)庫的名稱HOST_ID( -函數(shù)返回服務(wù)器端計(jì)算機(jī)的名稱HOST_NAME( -函數(shù)返回服務(wù)器端計(jì)算機(jī)的名稱IDENTITY( , seed increment AS column_name -IDENTITY( 函數(shù)只在SELECT INTO 語句中使用用于插入一個(gè)identity column列到新表中/*select identity(int, 1, 1 as column_nameinto n

24、ewtablefrom oldtable*/ISDATE( -函數(shù)判斷所給定的表達(dá)式是否為合理日期ISNULL( , - 函數(shù)將表達(dá)式中的NULL 值用指定值替換ISNUMERIC( -函數(shù)判斷所給定的表達(dá)式是否為合理的數(shù)值NEWID( -函數(shù)返回一個(gè)UNIQUEIDENTIFIER 類型的數(shù)值NULLIF( , -NULLIF 函數(shù)在expression1 與expression2 相等時(shí)返回NULL 值若不相等時(shí)則返回expression1 的值sql中的保留字action add aggregate all alter after and as asc avg avg_row_lengt

25、h auto_increment between bigint bit binary blob bool both by cascade case char character change check checksum column columns comment constraint create cross current_date current_time current_timestamp data database databases date datetime day day_hour day_minute day_second dayofmonth dayofweek dayo

26、fyear dec decimal default delayed delay_key_write delete desc describe distinct distinctrow double drop end else escape escaped enclosed enum explain exists fields file first float float4 float8 flush foreign from for full function global grant grants group having heap high_priority hour hour_minute

27、 hour_second hosts identified ignore in index infile inner insert insert_id int integer interval int1 int2 int3 int4 int8 into if is isam join key keys kill last_insert_id leading left length like lines limit load local lock logs long longblob longtext low_priority max max_rows match mediumblob medi

28、umtext mediumint middleint min_rows minute minute_second modify month monthname myisam natural numeric no not null on optimize option optionally or order outer outfile pack_keys partial password precision primary procedure process processlist privileges read real references reload regexp rename repl

29、ace restrict returns revoke rlike row rows second select set show shutdown smallint soname sql_big_tables sql_big_selects sql_low_priority_updates sql_log_off sql_log_update sql_select_limit sql_small_result sql_big_result sql_warnings straight_join starting status string table tables temporary term

30、inated text then time timestamp tinyblob tinytext tinyint trailing to type use using unique unlock unsigned update usage values varchar variables varying varbinary with write when where year year_month zerofill -WAITFOR-例 等待1 小時(shí)2 分零3 秒后才執(zhí)行SELECT 語句waitfor delay 01:02:03select * from employee-例 等到晚上1

31、1 點(diǎn)零8 分后才執(zhí)行SELECT 語句waitfor time 23:08:00select * from employee*SELECT*select *(列名 from table_name(表名 where column_name operator valueex:(宿主select * from stock_information where stockid = str(nidstockname = 'str_name' stockname like '% find this %' stockname like 'a-zA-Z%' -

32、(指定值的范圍stockname like 'F-M%' - (排除指定范圍- 只能在使用like關(guān)鍵字的where子句中使用通配符or stockpath = 'stock_path'or stocknumber < 1000and stockindex = 24not stock* = 'man'stocknumber between 20 and 100stocknumber in(10,20,30order by stockid desc(asc - 排序，desc-降序，asc-升序order by 1,2 - by列號stock

33、name = (select stockname from stock_information where stockid = 4- 子查詢- 除非能確保內(nèi)層select只返回一個(gè)行的值，- 否則應(yīng)在外層where子句中用一個(gè)in限定符select distinct column_name form table_name - distinct指定檢索獨(dú)有的列值，不重復(fù)select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_nameselect stockname , "stocknu

34、mber" = count(* from table_name group by stockname- group by 將表按行分組,指定列中有相同的值having count(* = 2 - having選定指定的組select * from table1, table2 where table1.id *= table2.id - 左外部連接，table1中有的而table2中沒有得以null表示table1.id =* table2.id - 右外部連接 select stockname from table1union all - union合并查詢結(jié)果集，all-保留重復(fù)

35、行select stockname from table2*insert*insert into table_name (Stock_name,Stock_number value ("xxx","xxxx"value (select Stockname , Stocknumber from Stock_table2-value為select語句*update*update table_name set Stockname = "xxx" where Stockid = 3Stockname = defaultStockname =

36、nullStocknumber = Stockname + 4*delete*delete from table_name where Stockid = 3truncate table_name - 刪除表中所有行，仍保持表的完整性drop table table_name - 完全刪除表*alter table* - 修改數(shù)據(jù)庫表結(jié)構(gòu)alter table database.owner.table_name add column_name char(2 null .sp_help table_name - 顯示表已有特征create table table_name (name char(

37、20, age smallint, lname varchar(30insert into table_name select . - 實(shí)現(xiàn)刪除列的方法（創(chuàng)建新表）alter table table_name drop constraint Stockname_default - 刪除Stockname的default約束*function(/*常用函數(shù)*/*-統(tǒng)計(jì)函數(shù)-AVG -求平均值COUNT -統(tǒng)計(jì)數(shù)目MAX -求最大值MIN -求最小值SUM -求和-AVGuse panguselect avg(e_wage as dept_avgWagefrom employeegroup by

38、dept_id-MAX-求工資最高的員工姓名use panguselect e_namefrom employeewhere e_wage =(select max(e_wagefrom employee-STDEV(-STDEV(函數(shù)返回表達(dá)式中所有數(shù)據(jù)的標(biāo)準(zhǔn)差-STDEVP(-STDEVP(函數(shù)返回總體標(biāo)準(zhǔn)差-VAR(-VAR(函數(shù)返回表達(dá)式中所有值的統(tǒng)計(jì)變異數(shù)-VARP(-VARP(函數(shù)返回總體變異數(shù)-算術(shù)函數(shù)-/*三角函數(shù)*/SIN(float_expression -返回以弧度表示的角的正弦COS(float_expression -返回以弧度表示的角的余弦TAN(float_exp

39、ression -返回以弧度表示的角的正切COT(float_expression -返回以弧度表示的角的余切/*反三角函數(shù)*/ASIN(float_expression -返回正弦是FLOAT 值的以弧度表示的角ACOS(float_expression -返回余弦是FLOAT 值的以弧度表示的角ATAN(float_expression -返回正切是FLOAT 值的以弧度表示的角ATAN2(float_expression1,float_expression2 -返回正切是float_expression1 /float_expres-sion2的以弧度表示的角DEGREES(numeri

40、c_expression-把弧度轉(zhuǎn)換為角度返回與表達(dá)式相同的數(shù)據(jù)類型可為-INTEGER/MONEY/REAL/FLOAT 類型RADIANS(numeric_expression -把角度轉(zhuǎn)換為弧度返回與表達(dá)式相同的數(shù)據(jù)類型可為-INTEGER/MONEY/REAL/FLOAT 類型EXP(float_expression -返回表達(dá)式的指數(shù)值LOG(float_expression -返回表達(dá)式的自然對數(shù)值LOG10(float_expression-返回表達(dá)式的以10 為底的對數(shù)值SQRT(float_expression -返回表達(dá)式的平方根/*取近似值函數(shù)*/CEILING(nume

41、ric_expression -返回>=表達(dá)式的最小整數(shù)返回的數(shù)據(jù)類型與表達(dá)式相同可為-INTEGER/MONEY/REAL/FLOAT 類型FLOOR(numeric_expression -返回<=表達(dá)式的最小整數(shù)返回的數(shù)據(jù)類型與表達(dá)式相同可為-INTEGER/MONEY/REAL/FLOAT 類型ROUND(numeric_expression -返回以integer_expression 為精度的四舍五入值返回的數(shù)據(jù)-類型與表達(dá)式相同可為INTEGER/MONEY/REAL/FLOAT 類型ABS(numeric_expression -返回表達(dá)式的絕對值返回的數(shù)據(jù)類型與表

42、達(dá)式相同可為-INTEGER/MONEY/REAL/FLOAT 類型SIGN(numeric_expression -測試參數(shù)的正負(fù)號返回0 零值1 正數(shù)或-1 負(fù)數(shù)返回的數(shù)據(jù)類型-與表達(dá)式相同可為INTEGER/MONEY/REAL/FLOAT 類型PI( -返回值為 即3.1415926535897936RAND(integer_expression -用任選的integer_expression做種子值得出0-1 間的隨機(jī)浮點(diǎn)數(shù)-字符串函數(shù)-ASCII( -函數(shù)返回字符表達(dá)式最左端字符的ASCII 碼值CHAR( -函數(shù)用于將ASCII 碼轉(zhuǎn)換為字符-如果沒有輸入0 255 之間的ASC

43、II 碼值CHAR 函數(shù)會返回一個(gè)NULL 值LOWER( -函數(shù)把字符串全部轉(zhuǎn)換為小寫UPPER( -函數(shù)把字符串全部轉(zhuǎn)換為大寫STR( -函數(shù)把數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)LTRIM( -函數(shù)把字符串頭部的空格去掉RTRIM( -函數(shù)把字符串尾部的空格去掉LEFT(,RIGHT(,SUBSTRING( -函數(shù)返回部分字符串CHARINDEX(,PATINDEX( -函數(shù)返回字符串中某個(gè)指定的子串出現(xiàn)的開始位置SOUNDEX( -函數(shù)返回一個(gè)四位字符碼 -SOUNDEX函數(shù)可用來查找聲音相似的字符串但SOUNDEX函數(shù)對數(shù)字和漢字均只返回0 值 DIFFERENCE( -函數(shù)返回由SOUNDE

44、X 函數(shù)返回的兩個(gè)字符表達(dá)式的值的差異-0 兩個(gè)SOUNDEX 函數(shù)返回值的第一個(gè)字符不同-1 兩個(gè)SOUNDEX 函數(shù)返回值的第一個(gè)字符相同-2 兩個(gè)SOUNDEX 函數(shù)返回值的第一二個(gè)字符相同-3 兩個(gè)SOUNDEX 函數(shù)返回值的第一二三個(gè)字符相同-4 兩個(gè)SOUNDEX 函數(shù)返回值完全相同QUOTENAME( -函數(shù)返回被特定字符括起來的字符串/*select quotename('abc', '' quotename('abc'運(yùn)行結(jié)果如下-abc abc*/REPLICATE( -函數(shù)返回一個(gè)重復(fù)character_expression