The purpose of the experiment was to investigate the effect of the treatmentUnderstand the use of SQL language, further understand the relationship operation, consolidate the basic knowledge of database.
[experimental requirements]:Master the use of select statements for a variety of query operations: single table query, multi table connection and query, nested query, set query, etc.
1、 Single table query
1. Simple query
Open the query analyzer, create the teacher table and add the data. All the information of teachers can be retrieved from the teacher table, and only the number, name and professional title of teachers can be queried. The sentence is as follows:
select * from teacher select tno, tname from teacher
If you want to change the display of the column title when querying, you can retrieve the teacher’s teaching staff number, name and e-mail information from the teacher table, and add the title information such as’ teacher’s name ‘,’ teaching staff number ‘and’ e-mail ‘respectively.
Select TNO job number, tname name, email from teacher
Use the top keyword: retrieve the first two items and 67% of the teachers’ information from the teacher.
select top 2 * from teacher select top 67 percent * from teacher
Use the distinct keyword: retrieve the teacher’s professional title from the teacher table, and the displayed professional title should not be repeated. select distinct tposition from teacher
2. Use calculation column: the name, staff number and salary of each teacher in the teacher table are paid according to 95%. In the second sentence, the salary is paid according to 95% and listed as “advance salary”. The sentence is as follows:
select tno tname ,tsalary*0.95 from teacher Select TNO job number, tname name, tsalary * 0.95 as advance salary from teacher
3. Use the order by clause to sort the query results
The order by statement can be used to sort the query results. ASC and desc are keywords in ascending and descending order respectively. The system default is ascending order. From the teacher table, query the number and name of teachers whose salary is more than 2800, and arrange them in ascending order
select tno, tname from teacher WHERE tsalary>2800 order by tsalary ASC
4. Condition query
(1) Use the relational operator: query the information of teachers whose workload is more than 288 from the teacher table
select * from teacherWHERE tamount>288 order by tamount DESC
(2) Use the between and predicate: query the teacher information whose workload is between 144 and 288 from the teacher table
select * from teacher WHERE tamount between 144 and 288
(3) Use the in predicate: from the teacher table, find out the teaching staff number, teacher’s name, professional title and home address of the teacher whose professional title is “Professor” or “associate professor”
select tno,tname,tposition, taddress from teacher Where tposition in
(4) Use the like predicate: retrieve the information of the teacher whose surname is’ Wang ‘or whose second word is’ Li’ or ‘Xuan’ from the teacher table
Select * from teacher where tname like 'Wang%' select * from teacher WHERE tname like '_ [Li, Xuan]% '
2、 Multi table query
Different data are stored in each table of the database. Users often need to combine the data in multiple tables to extract the required information. If a query needs to operate on multiple tables, it is called Association query. The result set or result table of association query is called connection between tables. In fact, association query queries data through the association of common columns among tables, which is the most basic feature of relational database query.
1. SQL 2000 is compatible with two connection forms: ANSI connection syntax for from clause and SQL Server connection syntax for where clause.
Search student number, name, course number, course name and course score from student, course and SC
select student.sno, sname, cno, grade from student inner join SC on student.sno=SC.sno select student.sname,sc.grade from student,sc WHERE student.sno=sc.sno select student.sno,student.sname,sc.cno,course.cname,sc.grade from student,sc,course WHERE student.sno=sc.sno and sc.cno=course.cno
2. Query with union clause
The union clause can be used to display some columns of the same data type in one or more tables on the same column. For example, the teacher number and name are listed in the teacher table, and the student number and student name are listed in the student table
Select SnO as student number or job number, sname as name from student union select tno, tname from teacher
3. Query with group clause
If you want to group and summarize or average the data in the table according to certain conditions during data retrieval, you need to use the set function together with the group by clause in the select statement. Using group by clause to retrieve data can get summary statistics, average value or other statistical information of data classification.
(1) Use the group by clause without having.
Use the group by clause without having to summarize the student number and total score of the students in the SC table
Select 'student number' = SnO, 'total score' = sum (grade) from SC group by Sno
(2) Use the group by clause with having.
Use the group by clause with having to summarize the student number and total score of the students whose total score is more than 450 in SC table
Select 'student number' = SnO, 'total score' = sum (grade) from SC group by Sno Having SUM(Grade)>160
4. Query with compute and compute by clauses
Using compute and compute by, you can not only browse the data, but also see the statistical results.
(1) Use the compute clause to summarize the student number and total score of each student in the SC table
Select 'student number' = SnO, 'grade from SC' order by sno COMPUTE SUM(Grade)
(2) Use the compute by clause to summarize the student number and total score of each student in the SC table
Select 'student number' = SnO, 'grade from SC' ORDER BY Sno COMPUTE SUM(Grade) by sno
What’s the difference between observing the results of executing the compute and compute by clauses?
5. Nested query
(1) Use the in or not in keyword
Use the in keyword to find out the student number, course number and corresponding grades of all boys in class j10011
select SC.sno , SC.cno,SC.grade FROM SC WHERE sno IN ( SELECT sno FROM student Where class ='j10011 'and ssex ='male')
Use the in keyword to find out the students (student number, name) with different names from the teacher
Select SnO student number, sname name from student where sname not in (select tname from teacher)
(2) Use the exists or not exists keyword.
Use the exists keyword to find out the student number, course number and corresponding grades of the students in class’ j10011 ‘
SELECT SC.sno，SC.cno，SC.grade FROM SC WHERE EXISTS （ SELECT * FROM student WHERE SC.sno=student.sno AND student.sclass=‘j10011' ）
The above is the table query operation experiment all content, hope to help you learn, we personally practice.