SQL Server (c) grouping type conversion functions insert multiple data String functions

SQL Server (c) grouping type conversion functions insert multiple data String functions

My own study notes, reproduced please indicate the source, thank you!---sauerkraut

1.SQL Data grouping----Group by/having

① has a student information sheet: STUID/STUNAME/STUAGE/STUGENDER/STUCLASSID;

For each class ID and corresponding number: Select Stuclassid class Id,count (*) class number from Stuinfo group by Stuclassid;

Ask for the number of boys and girls in all the students: Select Stugender Gender, COUNT (*) Number of people from the Stuinfo group by Stugender;

Each class ID corresponds to the number of male students in each class: Select Stuclass class, COUNT (*) Number of male students from Stuinfo where stugender= ' Men ' Group by STUCLASSID;

The order of execution is:

Select Stuclass class, COUNT (*) Number of male students-------------------4

From Stuinfo--------------------1

Where stugender= ' man '-------------------2

GROUP BY Stuclassid------------------3

② when a grouping statement (group BY)/aggregate function is used, no additional column names can be included in the select query, but only in the aggregate function to appear;

③the difference between where and having:

Where: Filter the data for each column before grouping, and can be followed by any column and not with the aggregate function;

Having: After grouping each group of data to filter, only then can follow the Group column/aggregate function;

Execution order:

Select Stuclass class, COUNT (*) Number of male students------------------5

From Stuinfo--------------------1

Where stugender= ' man '-------------------2

GROUP BY Stuclassid------------------3

Having male student number >2-----------------wrong wording, because has not been renamed;

Having Count (*) >2------------------4

2.SQL Processing sequence Analysis

①from

②on

③join

④where

⑤group by

⑥with Cube/with Rollup

⑦having

⑧select

⑨distinct

⑩order by

? top

3. type conversion function

Cast (expression as data type)

Convert (data type, expression)

Select ' Your class number is: ' + convert (char (1), 1)

Select 100.0 + cast (int, ' 100 ')

4. Federated result set Union (set operator)

Union and union All can be combined, the difference: Union can be combined to remove duplicates and reorder; Union ALL does not remove duplicates and does not sort;

In most cases, there is no need to remove duplicates at the time of Union, and to maintain the order of the original data, it is generally recommended to use Union ALL

Union because of repeated scanning, so the efficiency will be very low;

Select Stuname,stuage,stuid from Studentinfo

Union (All)

Select Techname,techage, Techid from Teacherinfo

You can use union to insert multiple data into a table, and the Union will also drain

5. Backing up data

SELECT * FROM Studentinfo

SELECT * Into Studentinfo20170404backup from Studentinfo

The structure of the table Studentinfo table and the data in the table are backed up into studentinfo20170404backup, and the table studentinfo20170404backup is created when the SELECT INTO statement is executed. However, the constraints of the original table will not be backed up in the past;

So the SELECT INTO statement cannot be repeated because each execution creates a table

The structure of the original table including the self-increment column will be created in the backup table, but the constraints of the original table will not appear in the backup table;

Only table structure is not evaluated: SELECT top 0 * into backuptable from table

6. String functions

①len () calculates the number of characters, not in Chinese and English, only number characters;

Print Len (' hi~ recently? ') 8

②datalength () returns the number of bytes consumed, which is not a string function;

Print datalength (' hi~ recently? ') 12, Chinese two byte, English one own

Print datalength (N ' hi~ recently? ') All are Unicode and are counted in 2 bytes

③upper Turn capital/lower to lowercase

Print Upper (' Hello,how is You? ')

Print lower (' Hello,how is You? ')

④ltrim left side space/rtrim remove the right side of the Space/ltrim (rtirm ()) Remove both ends of the space
⑤ string intercept function

<1> left () intercept from the right: print Ieft (' People's Republic ', 2) Zhonghua

<2> right () to intercept from the start of the number: print "(' People's Republic of China ', 2) and state

<3> substring () Print substring (' People's Republic ', 1, 3) starting from the first position, intercept 3: Chinese people

Print substring (' People's Republic ', -2,3) empty

7. Date and Time functions

① get current date and time: GetDate ()/sysdatetime () (relatively high accuracy)

② at some time add: DateAdd (datepart,num,date) DateAdd (Day/month/year/minute/second/hour,200,getdate ())

③ Two date difference: DateDiff (DATEPART,DATE1,DATE2) date1<date2= positive, date1>date2= negative

④ gets the value of a part of the date: DatePart (year/month/day/hour/minute/second,getdate ()) returns the int type;

SQL Server (c) grouping type conversion functions insert multiple data String functions