Shulou(Shulou.com)05/31 Report--

What are the commonly used SQL sentences in MySQL? I believe many inexperienced people don't know what to do about it. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

1. Complex SQL query

1.1. Single table query

(1) Select the specified column

Inquire about the student numbers and names of all the students

Select Sno as student number, Sname as name from student;select Sno,Sname from student

(2) query all columns

Inquire about the details of all the students

Select * from student

(3) name the specified column after the query

Query the "name" and "year of birth" of all students

Select Sname as name, (2014-Sage) as year of birth from student;select Sname, (2014-Sage) from student

(4) eliminate rows with duplicate values

Inquire about the student number of the elective course

The student number of select distinct Sno as who took the course is from SC;select distinct Sno from SC.

(5) Select several tuples in the table (those that meet the conditions)

1.2. Size comparison

Query the list of all students in the Department of computer Science (IS)

Select Sname as student name from student where Sdept='IS'

Inquire about the name and age of all students under the age of 20

Select Sname as name, Sage as age from student where Sage=3

The having keyword is followed directly by the aggregate function.

The reason for adding the HAVING clause in SQL is that the WHERE keyword cannot be used with the aggregate function.

SELECT column_name, aggregate_function (column_name) FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function (column_name) operator value

Inquire about more than 2 elective courses (including 2 courses, but excluding course 1), student number and the number of elective courses.

Select Sno as student number, number of count (course.Cno) as elective courses From SC,courseWhere course.Cno=SC.Cno and course.Cno! = 1Group by SnoHaving Count (course.Cno) > = 2

Inquire the student number of more than 2 students who fail.

Select Snofrom scWhere sc.Grade=2

Inquire about the course number and the number of electives taken by more than 2 students (including 2 students).

Select Cno,count (Sno) From SCGroup by CnoHaving count (sno) > = 2

2. Join query

(1) equivalent and non-equivalent join query

Inquire about each student and his elective courses

Select student.Sno as student number, course.Cno as elective course number, SC.Grade as score from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno

(2) self-connection

Inquire about indirect elective courses for each student

Select SC.Sno as number, FIRST.Cname as direct elective course, SECOND.Cname as indirect elective course from SC,course as FIRST,course as SECONDwhere FIRST.Cno=SC.Cnoand FIRST.Cpno=SECOND.Cno

(3) external connection

Inquire about the elective courses of all students (including those who do not take elective courses)

Select student.Sno as student number, Sname as name, sc.Cno as elective course number from student LEFT OUTER JOIN SC ON student.Sno=SC.Sno

Join is used to query data from two or more tables based on the relationship between the columns in these tables

JOIN: if there is at least one match in the table, return row LEFT JOIN: return all row RIGHT JOIN from the left table even if there is no match in the right table; return all row FULL JOIN from the right table even if there is no match in the left table: return the row UNION operator to merge the result set of two or more SELECT statements as long as there is a match in one of the tables. Note that SELECT statements within UNION must have the same number of columns. Columns must also have similar data types. At the same time, the columns in each SELECT statement must be in the same order.

3. Nested query

(1) subquery with IN predicate (attribute in (query result of subquery))

Inquire about the information of students in the same department as Wang Min.

Select * from studentwhere Sdept in (select Sdept from studentwhere Sname=' Wang Min')

Inquire about the information of students who are not in the same department as Wang Min.

Select * from studentwhere Sdept not in (select Sdept from student whereSname=' Wang Min')

Inquire about the student number and name of the course whose name is "Information system".

Select student.Sno as student number, Sname as name from student,SCwhere student.Sno=SC.Sno and Cno in (select Cno from course where Cname=' Information system')

Inquire about the student number and name of the student who attended the class with Liu Chen. (suppose: there is only one class for a course)

Select distinct student.Sno as student number, Sname as name from student,SCwhere student.Sno=SC.Sno and Cno in (select Cno from SC,student where SC.Sno=student.Sno and student.Sno in (select Sno from student where student.Sname=' Liu Chen'))

The inner in finds out Liu Chen's student number sno, and the outer in finds out the course number of Liu Chen's course.

(2) subquery with comparison operator (=, > =, (select min (Grade) from SC b where a.Cno=b.Cno)

Inquire about the course number of each student who exceeds the average score of his elective course.

Select Cnofrom SC awhere Grade > (select avg (Grade) from SC b where a.Sno=b.Sno)

(3) subqueries with ANY or ALL predicates

ANY stands for any one, ALL for all, and can be used in front of parentheses in subqueries

Inquire about the name, sex, age and department of a student who is younger than a student in a computer department in other departments.

Select Sname as name, Ssex as gender, Sage as age, Sdept as belongs to from studentwhere Sage

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.