SQLSERVER学习资料（SQLSERVER learning materials）

SQLSERVER学习资料（SQLSERVER learning materials） SQLSERVER学习资料(SQLSERVER learning materials) Aggregate function MAX (field) to find the maximum value in a field MIN (field) to find the minimum value in a field AVG (field) finds the average in a field SUM (field) to find the sum in a field COUNT (field) counts the non empty record of a field COUNT (*) statistical records Aggregate function exercise Query the Products table, and all the sum of UnitPrice Query the Products table, all the sum of UnitPrice higher than 50 Query the Products table, the average value of all UnitPrice Query Customers table, how many Customer? Query Customers table, how many Country? Query the Products table, the maximum value of UnitPrice Date function GETDATE () Action: get current date, time DATEPART (datepart, date) The act of getting a part of a date or time. There are two parameters, and date represents the date to be manipulated. Datepart stands for what you want. DATEDIFF (datepart, StartDate, enddate) Role: compare the difference between two dates and a part. Usually StartDate stands for an earlier date, and enddate stands for later DATEADD (datepart, number, date) Role: add a period of time based on the date YEAR (date) Equivalent to DATEPART (year, date) MONTH (date) Equivalent to DATEPART (month, date) DAY (date) Equivalent to DATEPART (day, date) Datepart Abbr. Year, YY, YYYY Quarter, QQ, q Month, mm, M Dayofyear, Dy, y Day, DD, D Weekday DW Hour HH Minute, MI, n Second, SS, s Millisecond MS Four to five, keep two decimal places Print Round (5.5678,2) Round down Print Floor (1.9) ceil Print Ceiling (2.1) Create table score (stuId int, PS int, JM int ) Insert, into, score, values (1,99,54) Insert, into, score, values (1,85,43) Insert, into, score, values (1,92,71) Select, stuId, ceiling (ps*0.35+jm*0.65) From Score Print rand () *100 CHARINDEX Returns the starting position of the specified expression in the string. grammar CHARINDEX (expression_r1, expression_r2 [, start_location]) Print CHARINDEX ('hello','gsfjhellokjhellosa', 7) REPLACE Replaces all second given string expressions that appear in the first string expression with third expressions. grammar REPLACE ('string_expression_r1','string_expression_r2','string_expression_r3') Print replace ('hjgfyangjhg, Yang, yangjhgujj','Yang','chen') LEFT RIGHT Returns the character that starts with the specified number from the left of the string. grammar LEFT (character_expression_r, integer_expression_r) Print left ('zhang', 2) SUBSTRING Returns a part of a character expression. SUBSTRING (expression_r, start, length) Print substring ('zhang', 2,3) LEN Returns the number of characters (rather than bytes) of a given string expression, which does not contain trailing spaces. grammar LEN (string_expression_r) Print len ('Yang') LTRIM RTRIM Returns the character expression after the initial space is deleted. grammar LTRIM (character_expression_r) Print rtrim (ltrim ('hfdfd' ')) String function exercises There is a school number format: Area code (1-2 persons) - year of enrollment (4 persons) - student number (1-3 persons) Such as 5-2006-34 or 12-2004-7, Create table students ( Stuid varchar (12), Stuname varchar (10) ) Insert, into, students, values ('5-2006-34','zhang') Insert, into, students, values ('9-2007-4','wang') Insert, into, students, values ('16-2006-154','Yang') Insert, into, students, values ('16-2007-13','lang') 1. write a SQL statement to list the 2006 students; Select * from students Where substring (stuid, CHARINDEX (-, stuid) +1,4) ='2006' 2. list 2006 and sort by student number Select * from students Where substring (stuid, CHARINDEX (-, stuid) +1,4) ='2006' Order by convert --5. converts strings into numbers (int, Take back the second - part right --4. (stuid, Len (stuid) --3. string length -charindex (-, stuid, --2. second '-' position CHARINDEX +1 --1. (-, stuid) to get the first one - the next position ) ) ) Desc Select field 1, from table 1, where field 1.IndexOf ("cloud"), =1; The reason for this incorrect statement is that the indexof () function is not a SQL function, instead of the corresponding function of the sql. Left () is the SQL function. Select fields 1, from, table 1, where, CHARINDEX (` cloud ', field 1) =1; string functions perform different operations on binary data, strings, and expressions. Such functions act on CHAR, VARCHAR, BINARY, and VARBINARY data types, and can implicitly be converted to CHAR or VARCHAR data types. You can use string functions in the SELECT and WHERE clauses of the SELECT statement as well as expressions. Commonly used string functions are: Character conversion function 1, ASCII () Returns the ASCII code value of the leftmost character of the character expression. In the ASCII () function, a string of pure numbers is not enclosed, but strings containing other characters must be used with '', or they will go wrong. 2, CHAR () Converts ASCII code to characters. If no ASCII code value between 0 and 255 is entered, CHAR () returns NULL. 3, LOWER () and UPPER () LOWER () converts all strings to lowercase; UPPER () converts all strings into uppercase. 4, STR () Converts numeric data into character data. STR ([, length[, ]]) Length specifies the length of the string to return, and decimal specifies the number of digits to return. If no length is specified, the default length value is 10, and the decimal defaults to 0. When length or decimal is negative, return NULL; When length is less than the number of digits to the left of the decimal point (including symbol bits), returns length *; Submit to length, then decimal; When the number of strings returned is less than length, the left side makes up the space. Two, to the space function 1, LTRIM () the string header space removed. 2, RTRIM () remove the space at the end of the string. Three, take the child string function 1, left () LEFT (, ) Return character_expression, left, integer_expression characters. 2, RIGHT () RIGHT (, ) Returns character_expression, integer_expression characters on the right. 3、substring() 子串(<表达>，< starting_位置>，长度) 返回从字符串左边第starting_位置个字符起长度个字符的部分。 四、字符串比较函数 1、charindex() 返回字符串中某个指定的子串出现的开始位置。 charindex(< 'substring_expression”>，<表达式>) 其中串_expression是所要查找的字符表达式，表达可为字符串也可为列名表达式。如果没有发现子串，则返回0值。 此函数不能用于文本和图像数据类型。 2、patindex() 返回字符串中某个指定的子串出现的开始位置。 patindex(<“%子_expression % >，< column_名称>)其中子串表达式前后必须有百分号“%”否则返回值为0。 与charindex函数不同的是，patindex函数的子串中可以使用通配符，且此函数可用于焦、varchar和文本数据类型。 五、字符串操作函数 1、quotename() 返回被特定字符括起来的字符串。 quotename(< 'character_expression”> [字符]，quote_)其中quote_字符标明括字符串所用的字符，缺省值为“[ ]”。 2、replicate() 返回一个重复character_expression指定次数的字符串。 复制(character_expression integer_expression)如果integer_expression值为负值，则返回空。 3、reverse() 将指定的字符串的字符排列顺序颠倒。 反向(< character_expression >)其中character_expression可以是字符串、常数或一个列的值。 4、replace() 返回被替换了指定子串的字符串。 取代(< string_expression1 >，< string_expression2 >，< string_expression3 >)用string_expression3替换在string_expression1中的子串string_expression2。 4、space() 返回一个有指定长度的空白字符串。 空间(< integer_expression >)如果integer_expression值为负值，则返回空。 5、stuff() 用另一子串替换字符串指定位置、长度的子串。 东西(< character_expression1 >，< start_位置>，<长>，< character_expression2 >) 如果起始位置为负或长度值为负，或者起始位置大于 character_expression1的长度，则返回空值。 如果长度长度大于character_expression1中start_位置以右的长度，则character_expression1只保留首字符。 六、数据类型转换函数 1、cast() 铸造(<表达>为< data_类型> [长度]) 2、convert() 转换(< data_类型> [长度]，<表达> [风格]) 1)data_type为SQL Server系统定义的数据类型，用户自定义的数据类型不能在此使用。 2)长度用于指定数据的长度，缺省值为30。 3)把焦或varchar类型转换为诸如int或samllint这样的整数类型、结果必须是带正号或负号的数值。 4)文本类型到焦或varchar类型转换最多为8000个字符，即焦或varchar数据类型是最大长度。 5)图像类型存储的数据转换到二进制或varbinary类型，最多为8000个字符。 6) convert an integer value to a MONEY or SMALLMONEY type and deal with the monetary unit of the defined country, such as the renminbi, the dollar, the pound, etc.. 7) BIT type conversions convert nonzero values to 1, and are still stored in BIT type. 8) attempting to convert data types of different lengths will truncate the conversion value and show '+' after the conversion value to indicate the occurrence of such truncation. 9) use the style option of the CONVERT () function to display date and time in different formats. Style is the conversion style number provided by the SQL Server system when converting DATATIME and SMALLDATETIME data into strings, and different style numbers have different output formats. Seven, date function 1, day (date_expression) Returns the date value in date_expression 2, month (date_expression) Returns the month value in date_expression 3, year (date_expression) Returns the year value in date_expression 4, DATEADD () DATEADD (, , ) Returns the new date created by the specified date date plus the specified additional date interval number. 5, DATEDIFF () DATEDIFF (, , ) Returns the difference between the two specified dates in the datepart, that is, date2 exceeds the date1 gap value, and the result is an integer value with a plus or minus sign. 6, DATENAME () DATENAME (, ) Returns the part of the specified portion of the date in string. Specified by datepart. 7, DATEPART () DATEPART (, ) Returns the specified part of the date in integer value. This section is specified by datepart. DATEPART (DD, date) is equivalent to DAY (date) DATEPART (mm, date) is equivalent to MONTH (date) DATEPART (YY, date) is equivalent to YEAR (date) 8, GETDATE () Returns the current date and time of the system in the default format of DATETIME. Aggregate function Use pubs Go Select AVG (distinct, price) - mean From titles Where type='business' Go Use pubs Go Select max (ytd_sales) - maximum number From titles Go Use pubs Go Select min (ytd_sales) -- the minimum number From titles Go Use pubs Go Select, type, sum (price), sum (advance) - summing From titles Group by type Order by type Go Use pubs Go Select count (distinct, city) -- find the number From authors Go Use pubs Go Select STDev (royalty) -- returns the statistical standard deviation of all values in a given expression From titles Go Use pubs Go Select StdevP (royalty) -- returns the padding statistical standard deviation of the ownership in the expression From titles Go Use pubs Go Select var (royalty) -- returns the statistical variance of all values From titles Go Use pubs Go Select VarP (royalty) -- returns the statistical variance of the fill of all values From titles Go - mathematical function Select, sin (23.45), atan (1.234), Rand (), PI (), sign (-2.34) - where Rand is to obtain a random number - configuration function SELECT @@VERSION - get the current database version SELECT @@LANGUAGE - current language Time function Select getdate () as'wawa_getdate'- current time Select getutcdate () as'wawa_getutcdate'- get UTC time 选择一天(getdate())为“wawa_day --取出天 选择月份(getdate())为“wawa_month --取出月 选择年(getdate())为“wawa_year --取出年 选择DateAdd(D，3，getdate())作为wawa_dateadd --加三天，注意会表示天，很表示月，'yy”表示年，下面一样 选择DateDiff(D，'2004-07-01 '，'2004-07-15”)作为 wawa_datediff --计算两个时间的差 选择datename(D，'2004-07-15”)作为wawa_datename --取出时间的某一部分 选择日期部分(D，getdate())作为wawa_datepart --取出时间的某一部分，和上面的那个差不多 ——字符串函数 (123)选择ASCII码为“123”，ASCII('123”)为“123”，ASCII(ABC”)作为“ABC”--转换成ASCII码 选择字符(123)，字符(321)，char(123)--根据ASCII转换成字符 选择较低(ABC”)、低(ABC”)，上(ABC”)，上(ABC”)——转 换大小写 选择STR(123.45,6,1)、STR(123.45,2,2)--把数值转换成字符串 选择LTrim(“左边没有空格”)——去空格 选择RTrim(“右边没有空格”)——去空格 选择LTrim(RTrim(“左右都没有空格”))——去空格 选择左('sql服务器”，3)，右('sql服务器，6)--取左或者取右 使用酒吧 选择au_lname，子串(au_fname，1,1)--取子串 从作者 为了au_lname 选择charindex('123 '，'abc123def '，2)--返回字符串中指定表达式的起始位置 选择patindex('123 '，'abc123def”)，patindex(“% 123%”，'abc123def ')--返回表达式中某模式第一次出现的起始位置 选择quotename(ABC”、“{”)，quotename(ABC”)——返回由指定字符扩住的字符串 选择反向(ABC”)、反(“上海')--颠倒字符串顺序 选择替换('abcdefghicde '，'cde '，'xxxx ')--返回呗替换了指定子串的字符串 选择空格(5)，空格(- 2) ——系统函数 选择host_name()“host_name '，host_id()“host_id '， user_name()“user_name '，user_id()“user_id '，' db_name”db_name() ——变量的定义使用 ——声明局部变量 声明@ mycounter int 声明@ last_name varchar(30)，“名为varchar(20)，“国家varchar(2)，一下声明多个变量 ——给变量赋值 使用Northwind 去 声明@ firstnamevariable varchar(20)， “regionvariable varchar(30) 设置“firstnamevariable = 'anne --可以用集，也可以用选择给变 量赋值，微软推荐用集，但选择在选择一个值直接赋值时很有用 设置“regionvariable = 'wa” 选择姓氏，名字，头衔，用声明并赋值过的变量构建一个选择语句并 查询 从员工 其中firstName= @ firstnamevariable或地区=“regionvariable 去 ——全局变量 版本--返回数据库版本 选择@ @错误--返回最后的一次脚本错误 选择@ @身份返回最后的一个自动增长列的ID 同时，休息，继续的使用 ——首先计算所有数的平均价格，如果低于30的话进入循环让所有 的价格翻倍， - and there is a if to judge, if the maximum unit price is still greater than 50, quit the cycle, or continue to cycle, know the maximum unit price greater than 50, on the break out of circulation, huh, huh, I should have analyzed it, didn't I? Use pubs Go While (select, AVG (price), from, titles) <$30 Begin Update titles Set price=price*2 Select max (price) from titles If (select, max (price), from, titles) >$50 Break Else Continue End Print,'Too, much, for, the, marker, to, bear' -- transaction programming classic example --begin transaction is a start transaction, commit transaction is a commit transaction, and rollback transaction is a rollback transaction - this example is to insert a record and, if anything goes wrong, roll back the transaction, that is, cancel and direct return (return), and if that's right, submit the transaction to commit - the return above returns a single integer value, and if this value is 0, it does not make a mistake when it is executed. If a mistake is made, it is a negative number, - this return can also be used in stored procedures, and this value can be obtained with exec @return_status= pro_name Use pubs Go Begin tran mytran Insert, into, stores (stor_id, stor_name) Values ('333','My, books') Go Insert, into, discounts (discounttype, stor_id, discount) Values ("clearance sale",'9999', 50) If @@error <>0 Begin Rollback tran mytran Print 'insert a discount record error' Return End Commit tran mytran -- an example of a save point for transactions After doing the transaction save point, you can rollback (rollback) to the specified save point, and not all operations are not available Use pubs Go Select * from stores Begin transaction testsavetran Insert, into, stores (stor_id, stor_name) Values ('1234','W.Z.D, Book') Save transaction before_insert_data2 Go Insert, into, stores (stor_id, stor_name) Values ('5678','foreat, Books') Go Rollback transaction before_insert_data2 Select * from stores