Database Add Data View data statement

Create DATABASE lianxi0831
Go
Use lianxi0831
Go
CREATE TABLE SAAA (
code int,
Name varchar (10),
Sex varchar (10),
Banji varchar (10),
CID varchar (20),
Age int,
Yufen Decimal (18,2),
Shufen Decimal (18,2),
Yinfen Decimal (18,2)
)
Go
INSERT into SAAA values (1001, ' Zhang San ', ' Male ', ' one shift ', ' 370301198502051546 ', 32,88,89,90)
INSERT into SAAA values (1002, ' John Doe ', ' female ', ' one shift ', ' 370301199005162586 ', 22,66,68,66)
INSERT into SAAA values (1003, ' Zhang Qiang ', ' Male ', ' one shift ', ' 370301199205265987 ', 32,88,89,90)
INSERT into SAAA values (1004, ' King Eight ', ' Male ', ' one shift ', ' 370301199602196654 ', 18,55,56,57)
INSERT into SAAA values (1005, ' Lihe ', ' Male ', ' Class two ', ' 370301198703156978 ', 27,44,45,46)
INSERT into SAAA values (1006, ' Bull cool ', ' Female ', ' class two ', ' 370301199502246635 ', 22,81,69,50)
INSERT into SAAA values (1007, ' Zhao Si ', ' Male ', ' Class two ', ' 370301199204197895 ', 23,44,55,66)
INSERT into SAAA values (1008, ' Tian Shi ', ' Female ', ' class two ', ' 370301199204153254 ', 24,84,55,60)
INSERT into SAAA values (1009, ' side cool ', ' female ', ' three shifts ', ' 370301199508249875 ', 25,66,79,40)
INSERT into SAAA values (1010, ' Lynn ', ' Male ', ' class three ', ' 370301199504156354 ', 25,55,56,45)
INSERT into SAAA values (1011, ' dongdong ', ' Male ', ' class three ', ' 370301199604192596 ', 26,66,77,80)
INSERT into SAAA values (1012, ' Liu ', ' female ', ' three shifts ', ' 370301995081936978 ', 27,66,87,80)
Go
--View all information
Select *from saaa
--View the language scores of the name John Doe
Select Yufen from saaa where name= ' John Doe '
--View the name, gender, age of language score 80-100
Select Name,sex,age from SAAA where Yufen between and 100
--View names who John Doe all information between the ages of 24-26
Select *from saaa where name= ' John Doe ' or Age in (24,25,26)
--see four of all information in your name
Select *from saaa where name like '% four '
--View all information on sex Lee
Select *from saaa where name like ' Li% '
--Sort by age and view the first three bits of information
Select Top 3 * from SAAA ORDER BY age DESC
--in descending order of mathematical fractions view top three names math scores
Select top 3 name as name, Shufen as math score from SAAA ORDER by Shufen Desc
--average of the language of the first class
Select AVG (Yufen) from saaa where banji= ' a class '
--The math average of class two
Select AVG (Shufen) from saaa where banji= ' second class '
--average of English in class three
Select AVG (Yinfen) from saaa where banji= ' class three '
--View Count
Select COUNT (*) from SAAA
--View the number of Kings
Select COUNT (*) from SAAA where name like ' King% '
--To see the number after the heavy
Select COUNT (Distinct Banji) from SAAA
--the highest score of the first class of Chinese
Select MAX (Yufen) from saaa where banji= ' a class '
--View the minimum score of the language according to class group
Select Banji,min (yufen) from SAAA GROUP by Banji
--view age of older than 24 according to age group
Select age from SAAA where age>24 group by age
--classes with a language score greater than 70 and a population greater than 1 according to the class group
Select Banji from SAAA where yufen>70 GROUP by Banji have COUNT (*) >1
--View the number of classes with a language score greater than 60 in descending order by class group
Select Banji,count (*) from SAAA where yufen>60 Group by Banji ORDER by COUNT (*) desc
--According to the class group, the highest score of the language of each class is the lowest score of the highest score mathematics, the lowest score of English maximum
Select Banji,max (Yufen), Min (Yufen), Max (Shufen), Min (Shufen), Max (Yinfen
), MIN (Yinfen) from SAAA GROUP by Banji
--Take the upper limit
Select CEILING (yufen) from SAAA
--Take the lower limit
Select Floor (shufen) from SAAA
--to ask for a number of several parties
Select POWER (3,4)
--Rounded, comma indicates a few decimal places
Select ROUND (3.1415926,4)
--Open square root
Select SQRT (4)
--Returns the ASCII code of the first letter of the string
Select ASCII (name) from SAAA
--Convert ASCII code to corresponding character
Select CHAR (97)
Select CHAR (age) from SAAA
--Search for another expression in the expression, returns the index of the first letter, the index starts at 1, and the return value is 0 for not found
Select CHARINDEX (' AAs ', ' SSSDDFAASDDF ')
--string concatenation
Select ' A ' + ' ll ' + ' SE '
--the number of characters Fu Chong from left to right
Select Left (name,1) from SAAA
--the number of characters Fu Chong from right to left
Select Right (' Qwerts ', 3)
--string length, front space count, trailing space not counted
Select LEN (' AASD ')
--uppercase to lowercase lowercase to uppercase
Select LOWER (' asdddd ')
Select UPPER (' Aaasds ')
--replace display is not a change
Select REPLACE (Sex, ' female ', ' girl ') from SAAA
--Replication can specify the number of times
Select REPLICATE (' ASD ', 2)
--Reverse string
Select REVERSE (name) from SAAA
--intercept the specified length from the specified index
Select SUBSTRING (name,1,1) from SAAA
--Get the current time
Select GETDATE ()
--Accurate acquisition of time
Select Sysdatetime ()
SELECT @ @DATEFIRST
-Increase the amount of time to increase the number of items, increase in that parameter
Select DATEADD (year,2, ' 2016-9-24 ')
Select DATEDIFF (year, ' 2014-1-25 ', ' 2018-5-25 ')
Select CAST (123 as varchar (10))
Select CONVERT (varchar (10), 123)
Select Name, (SUBSTRING (cid,7,4) + ' year ' +substring (cid,11,2) + ' month ' +substring (cid,13,2) + ' Day ')
As Birthday from SAAA

Database Add Data View data statement