SQL Statement Basics

A Query constant
1. SELECT student number, student name, gender from Tb_student
2. SELECT student name as name, gender as student gender from Tb_student
3. SELECT student name, gender student gender from Tb_student
4. SELECT student Name, high number + foreign language as high number plus foreign language score from tb_student
5. SELECT student name, student number from tb_student WHERE LEN (student name) = 2
6. SELECT student name, age from tb_student WHERE age = 24
7. SELECT student name, age, gender from tb_student WHERE student name = ' Peace of mind '
8. SELECT student name, age, date of birth from tb_student WHERE Birth date = ' 1981/12/8 '
9. SELECT student name, age, recruit no from tb_student WHERE Birth date = ' TRUE '
10.SELECT student's name, age, home address from Tb_student WHERE home addresses is NULL OR home = '
Two query variables (string. Format ())
1. SELECT student name, age, gender, home address from tb_student WHERE student name = ' {0} ', name
2. SELECT student name, age, gender, home address from tb_student WHERE age = {0},age
3. SELECT student name, age, gender, date of birth from tb_student WHERE Birth date = {0},dt. ToShortDateString
4. SELECT student name, age, gender, date of birth from tb_student WHERE date of birth between ' 2014/1/1 ' and ' 2014/2/1 '
Three fuzzy query
1. SELECT student name, age, gender from tb_student WHERE student name like ' Zhang _ '
2. SELECT student name, age, gender from tb_student WHERE student name like ' Zhang __ '
3. SELECT student name, age, gender from tb_student WHERE student name like ' Zhang% '
4. SELECT student name, age, date of birth from Tb_student WHERE Birth date like '%1981% '
5. SELECT student name, age, date of birth from Tb_student WHERE age like ' 2[0-9] '
6. SELECT student name, age, date of birth from Tb_student WHERE age like ' [1-2][0-9] '
7. SELECT student name, age, date of birth from Tb_student WHERE student name like ' [^ li]% '
8. SELECT student name, age, date of birth from Tb_student WHERE age like ' [1-2][^0-5]
9. SELECT student name, age, date of birth from Tb_student WHERE age like ' [1-2][^0-5] and student name like ' Li% '
Four TOP and PERCENT limit query results
1. SELECT Top 10 student number, student name, age, home address from tb_student ORDER by student number ASC
2. SELECT Top 10 student number, student name, high number, foreign language from Tb_student ORDER by high + foreign language ASC
3. Select Top * FROM (select top * from Tb_grade ORDER by total DESC) as St ORDER by total ASC
4. SELECT TOP PERCENT ISBN, Title, sum (sales quantity) as total sales quantity from Tb_book GROUP by ISBN, title, author ORDER by 3 DESC
5. SELECT TOP PERCENT * from Tb_bookmessage ORDER by existing number ASC
Five-value query
1. SELECT Student Name, IsNumeric (age) from Tb_student
2. SELECT DM as value before rounding, CEILING (DM) as value after rounding from Tb_money
3. SELECT DM as value before rounding, floor (DM) as value after rounding from Tb_money
4. SELECT DM as rounded up, ROUND (dm,2) as rounded back from Tb_money
5. SELECT angle as angle, sin (angle*pi ()/180) as sine value from Tb_angle
6. SELECT angle as angle, cos (ANGLE*PI ()/180) as cosine value from Tb_angle
7. SELECT myvalue as value, ABS (myvalue) As for absolute value after from Tb_value
8. SELECT * FROM tb_student WHERE student number = 22050110+floor (RAND () *10)
9. SELECT MyValue as value, sign (myvalue) as judgment value from Tb_value
Six-string query
1. SELECT Friendname As String, LOWER (Friendname) as all converted to lowercase, UPPER (friendname) as all converted to uppercase from tb_string
2. SELECT Friendname As String, (LEN (Friendname)-len (REPLACE (friendname, ' I ', ')))/len (' I ') from tb_string
3. SELECT student name, institution, SUBSTRING (institution, 2,3) as school substring from tb_student
4. SELECT Birth date, SUBSTRING (CONVERT (VARCHAR (20), date of birth), 6,5) as year from Tb_student
5. SELECT Institute as before deleting a string, STUFF (the institution, 2,3, ') as removes the string from the Tb_student
6. SELECT Institute, CHARINDEX (' College ', school) as index location from Tb_student
7. SELECT Institute, replace (Institute, ' College ', ' Xueyuan ') as replaced information from tb_student
8. SELECT student name, case where the institute = ' Computer College ' then ' Computer college student ' ELSE ' no ' END as a computer college student from Tb_student
Seven period and date of enquiry
1. SELECT student number, student name, Case time ISDATE (birthdate) =1 Then ' is the date information ' ELSE ' non-date novelty ' END as Birthday field is date information from tb_student
2. SELECT student number, student name from tb_student WHERE Birth date = ' 1980/12/24 '
3. SELECT student number, student name from Tb_student WHERE Birth date like '%1980% '
4. SELECT * from Tb_book WHERE date between ' 2005/8/1 ' and ' 2005/10/1 '
5. SELECT ISBN, title from Tb_book WHERE Year (date) = ' All ' and MONTH (date) = ' 9 ' and day (date) = ' 26 '
6. SELECT GETDATE () as current date time, Datename (Weekday,getdata ()) as Week
7. SELECT Student's name, date of birth, DATEDIFF (TEAR, date of birth, GETDATE ()) as student's age from tb_student
Eight comparison, logical, duplicate query
1. SELECT * FROM Tb_grade WHERE Software engineering >90 and foreign languages not between and 85
2. SELECT DISTINCT ISBN, Barcode, title, author from Tb_book
3. SELECT COUNT (ISBN) as record Number, ISBN, title, author from Tb_book GROUP by ISBN, title, author having COUNT (ISBN) >1
Ix. using OR and and in queries
1. SELECT * from Tb_grade WHERE (Foreign Languages > "High >") and Chinese >80
2. SELECT * from Tb_grade WHERE foreign languages > and High > 90
Ten sorting, grouping statistics
1. SELECT Publisher sum (amount) as total amount from Tb_book GROUP by publishing house
2. SELECT title, publisher, sum (amount) as total amount from tb_book WHERE publishing house = ' People mail ' GROUP by all, title, publishing house
3. SELECT Department, Gender, AVG (payroll) as average salary from Tb_employee GROUP by department, gender with CUBE
4. SELECT department, Gender, AVG (payroll) as average salary from Tb_employee GROUP by department, gender with ROLLUP
5. SELECT * from Tb_grade ORDER by Ma via ASC high number DESC
6. SELECT TOP 5 ISBN, title, author, publisher, sum (sales quantity) as total sales quantity from Tb_book GROUP by ISBN, title, author, publisher ORDER by SUM (sales quantity) DESC
7. SELECT student name, gender, age, school from Tb_student ORDER by student name COLLATE Chinese_prc_stroke_cs_as_ks_ws
8.SELECT student name, gender, age, school from Tb_student ORDER by student name COLLATE Chinese_prc_cs_as
9. SELECT storage location, title, SUM (stock quantity) as total inventory quantity from Tb_depot GROUP by storage location, title ORDER by SUM (stock quantity) DESC
X. SELECT K. ISBN, K. Author, sum (k. Existing quantity) as existing quantity, sum (x. Sales quantity) As sales quantity from tb_vendition as X, tb_bookmessage as K WHERE x. ISBN = k. ISBN GROUP K. ISBN, K. Title, x. Author, K. Number of existing ORDER by ISBN
SELECT * from Tb_employee ORDER by Department COMPUTE SUM (payroll)
SELECT * from Tb_employee ORDER by Department COMPUTE SUM (payroll) by Department
11 Aggregation functions
1. SELECT sum (sales quantity) as total quantity, sum (amount) as total amount from Tb_book
2. SELECT avg (age) As average age from tb_student
3. Select * FROM Tb_ware WHERE pin price in (select MIN (pin price) from Tb_ware)
4. Select * FROM Tb_ware WHERE pin price in (select MAX (pin price) from Tb_ware)
5. SELECT COUNT (DISTINCT product name) as merchandise from Tb_ware WHERE pin price > 1000
6. Booknames as BookName, first (author) as PEO, Primary (sellsum) as data record from Tab_booksort
7. SELECT last (Booknames) as BookName, last (author) as PEO, last (sellsum) as first data record from Tab_booksort
8. SELECT Institute Count (school) as student number from Tb_student GROUP by Institute
9. Select student number, student name, high number from Tb_grade WHERE high > (SELECT AVG (High) from Tb_grade)
Ten SELECT COUNT (DISTINCT home address) from Tb_student WHERE not (home address is NULL OR home address = ')
Select COUNT (student number) as number of students, SUM (high number) as high score from Tb_grade WHERE student number in (SELECT floor (RAND () *10) +22050110 as random number UNION Select Floor (rand () *10) +22050110 UNION SELECT Floor (rand () *10) +22050110)
12 Multi-Table query
1. SELECT s student number, S. Student name, S. Gender, M. High number from tb_student as S, Tb_grade as M WHERE S. Student number = M. Student number and M. height > 85
2. SELECT s student number, S. Student name, M. High number, M. Foreign language from Tb_student as s,tb_grade as M WHERE S. Student number = M. student number and S. School = ' Computer Academy '
3. Select customer number as number, customer name as name, city, postcode from Tb_buyer UNION SELECT employee number, employee name, home address, zip from Tb_saleman
4. SELECT St. student name, St. Sex, St. Age, Gr. high number, Gr. foreign language, Gr. Software engineering from Tb_student as St, Tb_grade as GR (Cartesian results query)
13 Nested queries
1. Select student number, gender, name, professional from tb_student where student number in (select student number from Tb_grade where total score >= 580)
2. Select * from tb_employeelaborage where wage month = Ten and staff name in (select owner from Tb_employeedepartment where owner in (select Person name from Tb_employeeperson WHERE education = ' undergraduate ') ORDER by personnel number
3. Select * from Tb_grade where high > All (SELECT high number from Tb_grade where student name in (' Wang Li ', ' Lily '))
4. Select * from Tb_grade where high > any (SELECT high number from Tb_grade where student name in (' Wang Li ', ' Lily '))
14 Sub-query
1. Select * from tb_student where student number in (select student number from Tb_grade where software engineering > (select Software Engineering > from Tb_grade where tb_grade where student name = ' Wang Xiaoliang '))) as Stu ORDER by Stu. Student number
2. Select * from tb_student where student number in (select student number from Tb_grade where high number > (select AVG (High) from Tb_grade))
3. SELECT st.*, Gr. Total score from tb_student as St INNER JOIN Tb_grade as GR on St. Student Number = gr. student number where the Academy = ' Science ' and Gr. Total Score &G T All (SELECT Gr. Total score from Tb_grade as Gr INNER JOIN tb_student as St on St. Student Number = gr. student number where St. School = ' School of Management ')
4. SELECT st.*, Gr. Total score from tb_student as St INNER JOIN Tb_grade as GR on St. Student Number = gr. student number where the Academy = ' Science ' and Gr. Total Score &G T Any (SELECT AVG (CONVERT (INT, total) from Tb_grade)
5. Select * from Tb_student as St where EXISTS (SELECT * from Tb_grade as GR where St. Student Number = Gr. student number and Gr. Total score > 580)
6. Select Institute, student name, age, (SELECT avg (age) from tb_student) as average age from Tb_student GROUP by Institute, student name, age having age > (Sele CT AVG (age) from Tb_student)
7. UPDATE Tb_laborage SET Base pay = (SELECT base pay from tb_appointedlaborage where work time = ' 1 year ') where Employee name = ' Jiangnan '
8. DELETE from Tb_student WHERE student number in (SELECT student number from tb_student where gender = ' woman ' and birth date = ' 1981/12/12 ' and the major = ' Accounting ' )
XV Combination Statement
1. Select student number, student name, gender, age from tb_student where school = ' science ' UNION SELECT student number, student name, gender, age from tb_student where student number in (SE Lect student number from Tb_grade WHERE total score > 600)
2. Select Student name from tb_student Union select CONVERT (VARCHAR (20), total score) from Tb_grade WHERE Total score > 570 UNION SELECT course name from Tb_course
3. Select Age as information from tb_student Union Select Total from Tb_grade Union Select course number from Tb_course ORDER by information ASC
4. Select student number, student name from tb_student INTERSECT SELECT student number, student name from Tb_grade
5. Select student number, student name from tb_student EXCEPT SELECT student number, student name from Tb_grade
16 Internal Connection Query
1. SELECT tb_student. Student name, tb_student. Gender, Tb_grade. Total score from tb_student INNER JOIN tb_grade on tb_student. Student number = TB. Grade. Student number
2. SELECT St. student name, St. Sex, St. Age, Gr. total score, TC. Attendance from tb_student as
St INNER join Tb_grade as GR on St. student Number = gr. student number INNER join Tb_studenttimecard as TC on St. Student number = TC. Student number
3. SELECT st1.* from Tb_student as St1 INNER joins Tb_student as St2 on st1. School = st2. School and st2. Student name = ' Bruce Lee '
4. SELECT st1.* from Tb_student as St1 INNER joins Tb_student as St2 on St1. School <>st2. School and st2. Student name = ' Bruce Lee '
5. SELECT employee Information Form. Personnel number, employee information sheet. Name from Tb_employeeperson as employee Information table INNER JOIN tb_employeelaborage as Employee Payroll table on Employee information sheet. As personnel number = Employee Payroll. Personnel Number
17 External Connection Query
1. SELECT tb_student. Student name, tb_student. Gender, Tb_grade. Total score from tb_student left OUTER JOIN tb_grade on tb_student. Student number = TB. Grade. Student number
2. SELECT tb_student. Student name, tb_student. Gender, Tb_grade. Total score from tb_student right OUTER JOIN tb_grade on tb_student. Student number = TB. Grade. Student number
3.SELECT St. Student name, St. Sex, St. Age, Gr. total score, TC. Attendance from tb_student as
St left OUTER joins Tb_grade as GR on St. student Number = gr. Student number left OUTER JOIN Tb_studenttimecard as TC on st. student number = TC. Student number
18 Querying using in
1. Select * FROM Tb_ware WHERE pin price in (select MAX (pin price) from Tb_ware)
2. Select * FROM Tb_ware WHERE pin price not IN (SELECT MAX (pin price) from Tb_ware)
19 Crossover operation
1. Transform aggfunction selectstatement PIVOT PivotField [In (value1[value2[...])]
2. Case Input_expression
When when_expression then result_expression
[... n]
ELSE else_result_expression
END
3. Stored procedures?

20 function queries
1. SELECT employee name, date of birth as formatted pre-Birth date format (date of birth, ' yyyy mm DD Day ') as formatted date from Employee Birthday table
2. SELECT Birth date, datename (year, date of birth) + ' +datename (month, date of birth) + ' months ' +datename (day, date of birth) + ' days ' as processed after birth from Tb_student
3. SELECT Employee Name, Fornat (date of birth, ' yyyy mm DD day ') as date of birth, MID (date of birth, 1,7) as birthdate from employee birthday table
4. SELECT Employee Name, Fornat (date of birth, ' yyyy mm DD day ') as birth date, SUBSTRING (date of birth, 1,7) as birthdate from employee birthday table
5. SELECT employee name, date of birth, DATEDIFF (' yyyy ', date of birth, date ()) as age from employee birthday table
21 Index Query
1. CREATE UNIQUE INDEX index_student on tb_student (student number)
2. DROP INDEX tb_student.index_student
3. CREATE CLUSTERED INDEX index_student on tb_student (student number)
4.
22 Applying stored Procedures
1.
23 Having statement application
1. SELECT Institute, COUNT (*) as number, AVG (age) As average age from Tb_student GROUP by Institute having AVG (age) > 22
2. SELECT Institute, COUNT (*) as number, Max (high number) as highest score, AVG (high) as high average score from tb_student as St INNER JOIN Tb_grade as Gr Number = gr. Student number GROUP by school having AVG (high number) > 60

SQL Statement Basics