Non-trivial SQL queries and sort statements

It's not easy!!

One: Querying using the SELECT statement

Grammar:

SELECT < column name >

From < table name >

[WHERE < query condition expression;]

[ORDER BY < sorted column name >[ASC or DESC]]

EG1:

SELECT scode,sname,saddress

From Students

WHERE SSEX = 0

ORDER by SCode

Two: Query all columns and rows:

eg

SELECT * from Course

SELECT * from Students

Three: Query some columns

eg

SELECT SName, saddress from Students

SELECT SName, Sgrade from Students

WHERE saddress = ' Beijing '

Four: Column aliases

eg

SELECT SCode as student number, SName as student name,

Saddress as Student address

From Students

WHERE saddress <> ' Henan Xinxiang '

SELECT FirstName + '. ' + LastName as name

From Employees

Attention:

1. + The data type of the connection must be compatible

2. If + Connect character data, the result is a connection to the string data

3. If + connect the numeric data, the result is the value of the

Five: Use the equals sign to name the column

eg

SELECT name = Firstname+ '. ' +lastname

From Employees

Six: Querying for empty rows

eg

SELECT SName from Students WHERE Semail is NULL

Seven: using constant Columns

eg

SELECT name =sname, address = saddress, ' Beijing Xinxing Bridge ' as school name

From Students

Eight: Limit the number of fixed lines

eg

SELECT TOP 5 SName, saddress

From Students WHERE SSex = 0

Nine: Return rows by percentage

eg:

SELECT TOP PERCENT SName, saddress

From Students WHERE SSex = 0

Ten: Related exercises:

Check all S1 student Information check the names and phone numbers of all S2 students Find out all S1 female students ' information track more than 60 of course information Query the account name for S2 check the name and address of the S2 male student Check student's name and grade information without e-mail Check the student name and grade information for S2 after 1993 years of birth date Check the score information for the "HTML and CSS Web technology" Subject exam that was dated February 15, 201311: Ascending order eg:

SELECT Studentid,score from score ORDER by score

SELECT StudentID as student number, (SCORE*0.9+5) as comprehensive results

From score

WHERE (score*0.9+5) >60

ORDER by Score

12: Descending order

eg

SELECT Studentid,score from score ORDER by score DESC

13: Sort by multiple columns

eg

SELECT StudentID as student number, CourseID as course ID, score as Score

From score

WHERE Score > 60

ORDER by CourseID, score

Problems:

. SQL statements are case insensitive

. Both string and date types require single quotation marks

When starting a table name, do not get up too well, for example, do not use user as the table name.

if the table name and the keyword are repeated, we can cancel the escape by [] .

SELECT * FROM [User]

Note: The Chinese space in the new query window.

SELECT * FROM Message

14: Common Types of functions:

String Functions used to control the string returned to the user Date Function used to manipulate date values Mathematical Functions used to perform algebraic operations on numeric values system Functions get system information about objects and settings in SQL Server1: String function

Function name: CharIndex (' str1 ', ' str2 ', index)//position starting from

--Parameter meaning:

--The first parameter: the string to query

--second argument: in which string to search

--The third parameter: start with the first few letters of STR2

eg

Select CHARINDEX (' Good learning ', ' S1 is the foundation of S2, so everybody learn ', 1)

SELECT CHARINDEX (' Jbns ', ' My jbns Course ')

Select Charindex (' Cold rain ', ' Good Man, Cold Rain ', 4)

Select CHARINDEX (' mother said ', ' we want to win glory for the country, mother said, is people will have the ideal ', 11)

Len (): Gets the length of the string in parentheses, and the space is a character length

eg

Select Len (' We're going to class tomorrow, we want everyone back to a good rest ')

Select Len (' Everyone is a good boy ')

Note: If the content ends with more than one space, then the end of multiple spaces is not calculated for the length

Upper (): Converts all English words that appear in parentheses to uppercase

Select Upper (' I can speak 中文版. Can you? ‘)

Select Lower (' I have A DREAM ')

Select Upper (' I have a dream, I've got a Vision ')

LTrim (): Clear space to the left of content in parentheses (left:right)

Select LTrim (' Life is not Lin Daiyu, '

Select RTrim (' Life is not Lin Daiyu, ')

--You want to remove the left space and remove the right space

Select RTrim (LTrim (' Life, '))

Select LTrim (RTrim (' You're all right! ‘))

Select Len (RTrim (' Strange this year, Beijing doesn't snow, ')

SUBSTRING (' string ', intercept position, intercept length)

Select substring (' Well, I love my great motherland, really ', 6, 1)

Right (): Returns the character of the specified type from the left of the string

Select Left (' Everyone doesn't seem to have a diary today, I'm going ', 3)

Select Right (' Everyone doesn't seem to have a diary today, I'm going ', 2)

Replace (' str1 ', ' character to replace ', ' target character '):

Select Replace (' Jay Chou, disabled, although hand not disabled, but X disabled ', ' disabled ', ' good ')

Stuff (' string ', delete the starting position, delete the length, ' Insert string ')

Select Stuff (' I love you china, I love your Hometown ', 4, 2, ' Beijing ')

Select Stuff (' We should all have ideals, for the ideal we stay up late, of course, at the cost of sacrifice body ', 6, 2, ' self-confidence ')

2: Date function

--01.getdate (): Gets the current date and time

Select GETDATE ()

--1000MS is S

--02.dateadd (add, increment, time by year/month/day)

Select DATEADD (yy,100, ' 2014-8-6 ')

Select DATEADD (Year,-20,getdate ())

--03.datediff (by year/month/day, small time, big time)

Select DateDiff (year, ' 1998-01-01 ', GETDATE ())

--04.datename: Getting a date is the day of the week

Select Datename (DW, ' 2014-08-06 ')

--05.datepart (mm, date): Gets the year/month/day portion corresponding to the specified date

Select DATEPART (yy,getdate ())

--year yy

--month mm

--day DD

--push (push) pulls (pull)

--by this way can also get the day of the week

Select DATEPART (dw,getdate ())

3: Mathematical Functions

--01.rand (): Produces a random number to between

Select Right (rand (), 4)

Select rand ()

--If a four-digit number (-9999) is generated

--02.abs: Take absolute value

Select ABS (-10)

Select ABS (10)

--non-negative

--03.ceiling: Ceiling, you need to look at the ceiling, head up, take the whole

Select Ceiling (1.999991)

--04.floor: Rounding down

Select Floor (1.999999)

Select Ceiling (1.000000000000001)

Select Floor (2.999999999999999)

--04.power (5,2) power value

Select Power (BIS)

Select 1*2/3+5-56+100-5/100

Select Power (2,3)

--05.round: Rounding a number to the specified precision

Select Round (42.564,1)

--06.sign: If a positive number is written in parentheses, returns, if negative, returns-1

--If it is returned

Select sign (20)

Select sign (0)

--07.SQRT () Open square

Select sqrt (9)

4: System functions Related exercises: 1: a company printed a number of prepaid cards, the card's password is randomly generated, and now this problem occurs:

The letter "O and the number 0", "Letter I and Number 1", the user reflects said not clear, the company decided to store in the database password all the "O" is changed to "0", all the "I" are changed to "1";

please write the SQL statement to implement the above requirements database table name: Card Password column name: PassWord Analysis: the ability to implement card password updates requires the use of the UPDATE statement involves substitution of strings, need to use functions in SQL Server replace () Answer:

UPDATE Card SET PassWord = REPLACE (PassWord, ' O ', ' 0 ')

UPDATE Card SET PassWord = REPLACE (PassWord, ' I ', ' 1 ')

2: The following character data is available in the database table, such as:

13-1, 13-2, 13-3, 13-10, 13-100, 13-108, 13-18, 13-11, 13-15, 14-1, 14-2

now you want to sort through the SQL statements, and first sort by the numbers in the first half, then sort by the numbers in the second half, and the output goes like this:

13-1, 13-2, 13-3, 13-10, 13-11, 13-15, 13-18, 13-100, 13-108, 14-1, 14-2

database table name: Sellrecord column Name: Listnumber, Analysis: Sort: Order by in order BY, the sorted numbers need to be recalculated number of the first half: find the location of the "-" symbol take the left half of the part Use the CONVERT function to convert it to a number:

Number of the second half:

find the location of the "-" symbol Replace all characters from the first position to the position with a space Use the CONVERT function to convert it to a number: 0 Answer:

SELECT Listnumber

From Sellrecord

ORDER by

CONVERT (int, left (Listnumber, CHARINDEX ('-', Listnumber)-1)),

CONVERT (int, STUFF (listnumber,1, CHARINDEX ('-', Listnumber), "))

3:

querying data using the SELECT statement Working with data using functions Requirements Description: find out about Y2 students over 20 years of age find out about the January birthday student Information find the name and grade of your birthday today The domain name of the student e-mail with the "Y21003007" number

Freshman enrollment, assign an email address to it

Analysis:

conditions of 20 years of age or above

DATEDIFF (Dd,borndate, GETDATE ()) >=365*20

get the domain name of your email

Right (email, LEN (email)-CHARINDEX (' @ ', email))

gets the year, month, day of the current date

CONVERT (VARCHAR (4), DATEPART (Yyyy,getdate ()))

Get 4-bit random number

Right (RAND (), 4)

Nothing is difficult, I am afraid of a conscientious.

Non-trivial SQL queries and sort statements