sql server查询语句大全SQL Server Query Daquan.docx

1、sql server查询语句大全SQL Server Query Daquansql server查询语句大全（SQL Server Query Daquan）Statement function1, data operationSelect - retrieve data rows and columns from a database tableInsert - adding new data rows to a database tableDelete - deletes rows of data from the database tableUpdate - update the da

2、ta in the database table2, data definitionCreate TABLE - create a database tableDrop TABLE - delete tables from the databaseAlter TABLE - modify the database table structureCreate VIEW - create a viewDrop VIEW - delete views from the databaseCreate INDEX - creates an index for the database tableDrop

3、 INDEX - removes indexes from the databaseCreate PROCEDURE - creates a stored procedureDrop PROCEDURE - deletes stored procedures from the databaseCreate TRIGGER - create a triggerDrop TRIGGER - removes triggers from the databaseCreate SCHEMA - add a new schema to the databaseDrop SCHEMA - deletes a

4、 schema from the databaseCreate DOMAIN - creates a data rangeAlter DOMAIN - change domain definitionsDrop DOMAIN - deletes a domain from the database3, data controlGRANT - Grant user access rightsDENY - deny user accessREVOKE - relieves user access rights4, transaction controlCOMMIT - end the curren

5、t transactionROLLBACK - aborts the current transactionSET TRANSACTION - defines the current transaction data access feature5, programmed SQLDECLARE - sets the cursor for the queryEXPLAN - describes data access plans for queriesOPEN - retrieve the query results and open a cursorFETCH - retrieve a row

6、 of query resultsCLOSE - close the cursorPREPARE - prepares SQL statements for dynamic executionEXECUTE - dynamically executing SQL statementsDESCRIBE - describes the prepared query6, local variablesDeclare id char (10)-set id =10010001Select id =100100017, global variables- must be at the beginning

7、 of 8, IF statementDeclare, x, int, y, int, z, intSelect x = 1, y = 2 z=3If x yPrintx Y- print stringx YElse, if, y zPrinty zElse, print,z Y9, CASE statementUse 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.

8、06Else e_wage*1.05End10, WHILE, CONTINUE, BREAK statementDeclare, x, int, y, int, c, intSelect x = 1 y=1While x 3BeginPrint x - print the value of the variable xWhile y 截断table_name -删除表中所有行，仍保持表的完整性表table_name -完全删除表16、ALTER TABLE * -修改数据库表结构修改表database.owner.table_name添加column_name char（2）空.sp_hel

9、p table_name -显示表已有特征创建表table_name（name char（20），年龄为SmallInt LName varchar（30）插入table_name选择.-实现删除列的方法（创建新表）修改表table_name约束stockname_default -删除stockname的默认约束17、常用函数-统计函数-求平均值AVG统计统计数目马克斯-求最大值民-求最小值总结求和AVG用盘古选择AVG（e_wage）作为dept_avgwage从员工集团通过dept_id迈克斯求工资最高的员工姓名用盘古选择e_name从员工在e_wage =（选择最大（e_wage）从员

10、工）stdev()stdev()函数返回表达式中所有数据的标准差stdevp()stdevp()函数返回总体标准差var()var()函数返回表达式中所有值的统计变异数varp()varp()函数返回总体变异数-算术函数-/ * * * * * * /三角函数罪（float_expression）-返回以弧度表示的角的正弦COS（float_expression）-返回以弧度表示的角的余弦谭（float_expression）-返回以弧度表示的角的正切COT（float_expression）-返回以弧度表示的角的余切/ * * * * * * /反三角函数在（float_expression）-返回正弦是浮值的以弧度表示的角AC