Summary of simple SQL statements [database]

1. Display the column name in the query results:
A. With the AS keyword: select name as ' name ' from students order by age
B. Direct representation: select name ' name ' from students order by age

2. Exact lookup:
a. Using in bounds: select * from students where native in (' Hunan ', ' Sichuan ')  
b.between...and: select * from students where age between and
c. " = ": select * from students where name = ' Li Shan '  
d.like: select * from students where name  like  ' li% '   ( Note that there is a "%" in the query condition, then the description is partially matched, and there is a sequential message inside, that is, to find a match that begins with "Li". Therefore, if the query has "Li" all objects, should command: '% li% '; If the second word is Li, it should be ' _ Li% ' or ' _ Li ' or ' _ li _ '.
e.["match checker: select * from courses where CNO Like ' [ac]% '   (the relationship that represents or, with "in (...)" Similarly, and "[]" can represent a range, such as: select * from courses where CNO like ' [a-c]% ' )

3. Handling of time-type variables
a.smalldatetime: processed directly by string processing, for example:
SELECT * from students where birth > = ' 1980-1-1 ' and birth <= ' 1980-12-31 '

4. Set function
A.count () summation, such as:select COUNT (*) from students (total number of students)
b.avg (column) averaging, such as:select AVG (Mark) from grades where cno= ' B2 '
C.max (column) and min (column) for Maximum and minimum

5. Grouping Group
Commonly used for statistics, such as the total number of groups check:
Select Gender,count (SNO)
From students
GROUP BY gender
(See how many boys and girls each)
Note: from which angle to group, from which column "group by"
For multiple groupings, you simply list the grouping rules. For example, the number of male and female students of various professions, then the group rules are: session (grade), Professional (MNO) and gender (gender), so there are "group by grade, MNOs, gender"
Select grade, MNO, Gender, COUNT (*)
From students
Group by grade, MNO, gender
Usually group is also associated with having, for example, to inquire about 1 students who fail the course, according to the school number (SNO) categories are:
Select Sno,count (*) from grades
where mark<60
GROUP BY Sno
Having Count (*) >1

6.UNION Union
Merge query results, such as:
SELECT * FROM students
WHERE name like ' sheet% '
UNION [All]
SELECT * FROM students
WHERE name like ' li% '

Note the difference between Union and UNION ALL:

The former wants to filter the duplicates to show only a duplicate message in the result, while the latter does not. So you can see that the Union is different from the algorithm used by union all, which is much simpler. From the perspective of tuning, it is recommended to use Union all, so that the query efficiency will improve a lot.

7. Multi-Table Query
A. Internal connection
Select G.sno,s.name,c.coursename
From grades G joins students s on G.sno=s.sno
JOIN courses C on G.CNO=C.CNO
(Note that you can refer to aliases)
B. Outer connection
B1. Left connection
Select Courses.cno,max (Coursename), COUNT (Sno)
From courses left JOIN grades on COURSES.CNO=GRADES.CNO
GROUP BY Courses.cno
Left JOIN feature: Displays all items in the left table, even if the data in some of these items is not filled in completely.

The

Left OUTER join returns rows that exist in the left table but not in the right table, plus the inner joined rows.
b2. Right connection
similar to left join
b3. Full connection
Span style= "color: #008000;" >select sno,name,major 
from students full JOIN majors on Students.mno=majors.mno
the contents of both tables are displayed
Span style= "color: #0000ff;" >c. Self-Connection
select c1.cno,c1.coursename,c1.pno,c2.coursename 
from Courses c1,courses c2  where C1.pno=c2.cno
addresses the issue with aliases.
d. Cross connect
select Lastname+firstname from LastName Cross JOIN Firstanme
is equivalent to doing a Cartesian product

8. Nested queries
A. Use keywords in, such as query Li Shan's fellow:
SELECT * FROM Students
where native in (select native from students where Name= ' Li Shan ')
B. Use the keyword exist, for example, the following two sentences are equivalent:
SELECT * FROM Students
Where Sno in (select Sno from grades where cno= ' B2 ')

SELECT * from students where exists
(SELECT * from grades where
Grades.sno=students.sno and cno= ' B2 ')

9. About sort order

b. For sort order, you can arrange by an item in the query condition, and the available number indicates that For example:
select sno,count (*), AVG (Mark) from grades 
GROUP by Sno
have avg (Mark) >85
ORDER by 3

10. Other
a. For recognized names with spaces, enclose them in "[]".
b. For a specific query that has no data in a column, you can use NULL to determine , such as
c. Note the distinction between any and all used in nested queries, any equals the logical operation "| |" And all is equivalent to the logical operation "&&"
d. Note that queries that make a negative sense are carefully entered into the trap:
For example, students who do not have elective ' B2 ' courses:
select students.*
from students, grades
where Students.sno=grades.sno
          grades.cno <> ' B2 '                    
The Query method above is wrong, the correct way to see below:
select * from students
where isn't exists  (select * from grades 
where Grades.sno=students.sno and cno= ' B2 ')

11. A solution to the difficulty of multiple nested queries: [Database] Summary of simple SQL statements
For example, students who have enrolled in all courses:
SELECT *
From students
Where NOT EXISTS (SELECT *
From courses
Where not EXISTS
(SELECT *
From grades
where Sno=students.sno
and Cno=courses.cno))
The most external weight: Choose from the student table, exclude those who have not selected classes. Use not exist. Since the subject is a course, the second query is found from the course table, excluding those who have chosen the course.