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

This article mainly introduces "how to use SQL sentence to realize query". In daily operation, I believe that many people have doubts about how to use SQL sentence to realize query. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubt of "how to use SQL statement to realize query"! Next, please follow the editor to study!

As we all know, SQL is a structured query language and a standard language for relational databases. It is a comprehensive, powerful, concise and easy-to-learn language. It integrates Data Quest, Data Manipulation, Data Definition and Data Control.

The SQL language consists of four parts:

Describe data definition language (DDL), such as CREATE, DROP, ALTER, etc.

Data manipulation language (DML), such as INSERT (insert), UPDATE (modify), DELETE (delete) statements

Query data query language (DQL), for example: SELECT statement

GRANT data Control language (DCL), such as GRANT, REVOKE, COMMIT, ROLLBACK, etc.

SQL language includes statements of three main programming language categories: data definition language (DDL), data manipulation language (DML) and data control language (DCL).

Now let's talk about a few basic concepts of SQL:

Basic table: a table that exists independently, and a relationship corresponds to a basic table.

Internal schema: the logical structure of storing files constitutes the internal schema of a relational database.

View: a table derived from one or more basic tables, which is a virtual table

Simple practice

Set up a database, including "student table", "teacher table", "course schedule", "major table", "course schedule", "student personal situation table", "grade table" seven tables

The following tables are the tables we will use in this practice: create tables with SQL statements:

Set up a database cc

The copy code is as follows:

Create database cc

Use cc

Create a student table named tb_student

The copy code is as follows:

Tb_student 's student form

Create table tb_student (

Stunum char (7) primary key,-- student number

Stuname char (8) not null,-name

Stusex char (2) check (stusex in ('female', 'male'))-gender

Stubirthday smalldatetime not null-- date of birth of the student

Stuspec char (6) not null,-Professional

Stuscore numeric (4pl 1),-admission scores

Stuloan char (2) check (stuloan in ('yes','no') not null,-- whether or not to lend

)

Create a teacher table named tb_teacher

Tb_teacher 's teacher list

The copy code is as follows:

Create table tb_teacher (

Teacher code char (7) primary key,-- teacher code

Name char (8) not null,-- teacher's name

Gender char (2) check (teasex in ('female', 'male'))-- teacher gender

Date of birth datetime not null,-- date of birth of the teacher

Professional title char (6)-- Professional title

)

Create a course schedule called tb_renke

Tb_renke 's course schedule

The copy code is as follows:

Create table tb_renke (

Course code char (5)-- course code

Teacher code char (7)-- teacher code

)

Create a professional table called tb_major

The copy code is as follows:

Tb_major 's specialty table

Create tabletb_major (

Professional name char (8) primary key,-- Professional name

Responsible person char (8) not null,-responsible person

)

Create a course schedule called tb_course

The copy code is as follows:

Tb_course 's class schedule

Create table course (

Course code char (5) primary key-- course code

Course name char (14) not null,-- course name

Zhou Xueshi int, Zhou Xueshi

Int credits-- credits

)

Create a student profile named tb_studentinfo

Tb_studentinfo 's student profile

The copy code is as follows:

Create table tb_studentinfo (

Student number char (7),-- student number

ID card char (15) unique-- ID card number

Place of origin char (8)-place of origin

Home address text-- Home address

Telephone char (30)-- telephone

Special text,-- Special strength

Reward text-- reward

Punish char (100)-- punish

)

Create a score sheet called tb_grade

Tb_grade 's transcript.

The copy code is as follows:

Create table tb_grade (

Student number char (7),-- student number

Course code char (5)-- course code

Usually numeric (3pm 1)-- usually

Mid-term numeric (3. 1),-- Mid-term

Final numeric (3jue 1)-- final term

)

Now insert records into each table

Insert data into the student table tb_student

The copy code is as follows:

Tb_student

Insert into tb_student values ('9607039' Deng Yingying', 'female', '1978-6-6' 'foreign trade', 666.6 'Yes')

Insert into tb_student values ('9907002' Ni Wenxian', 'female', '1981-4-1' 'foreign trade', 641.4 'yes')

Insert into tb_student values ('9801055' Zhao Dong', 'male', '1979-11-9' Chinese', 450'no')

Insert into tb_student values ('9902006' harmony', 'female', '1982-6-19' 'math', 487.1'no')

Insert into tb_student values ('9704001' Ke Minmin', 'female', '1978-7-22' 'Physics', 463'No')

Insert into tb_student values ('9603001' Shen Qiang', 'male', '1978-1-15' 'news', 512 'Shi')

Insert into tb_student values ('9606005' Chi Dawei', 'male', '1976-9-3' 'chemistry', 491.3'no')

Insert into tb_studentvalues ('9803011' 'Ouyang Xiaojuan', 'female', '1981-8-11' 'news', 526.5'no')

Insert into tb_student values ('9908088', 'Mao Jie', 'male', '1982-1-1' 'computer', 622.2'no')

Insert into tb_student values ('9608066' Kanghong', 'female', '1979-9-7' computer', 596.8 'yes')

Insert into tb_student values ('9805026' summer', 'male', '1980-5-7' 'history', 426.7'no')

Insert into tb_student values ('9702033' Li Li', 'male', '1979-7-7' 'mathematics', 463.9'No')

Insert data into teacher table tb_teacher

The copy code is as follows:

Tb_teacher

Insert into tb_teacher values ('20222' Yuduo', 'female', '1962-6-19' associate professor')

Insert into tb_teacher values ('20406' Zhang Jian', 'female', '1946-7-16' Professor')

Insert into tb_teacher values ('10429' Jiang Chenggong', 'male', '1959-3-12' 'associate professor')

Insert into tb_teacher values ('10616' million years', 'male', '1945-9-1' Professor')

Insert into tb_teacher values ('20626' recording 'Sun Le', 'female', '1971-12-15' Lecturer')

Insert into tb_teacher values ('10803' Li tie', 'male', '1958-9-22' 'associate professor')

Insert into tb_teacher values ('10812' rice millet', 'male', '1960-1-3' 'associate professor')

Insert into tb_teacher values ('11015' Chai Huai', 'male', '1973-8-26' 'instructor')

Insert into tb_teacher values ('11107 girls', 'Fang Hua', 'female', '1977 girls', 'lecturers')

Insert into tb_teacher values ('20836' Zhang Jing', 'female', '1974-11-15' Zhang Jing 'Lecturer')

Insert into tb_teacher values ('10101' Gao Shusheng', 'male', '1940-12-5' Professor')

Insert into tb_teacher values ('10312' Gong Wen', 'male', '1959-3-17' 'associate professor')

Insert into tb_teacher values ('20506' Wu Yan', 'female', '1947-10-6' Professor')

Insert into tb_teacher values ('20701' Shen Feifei', 'female', '1960-6-18' 'associate professor')

Insert into tb_teacher values ('10202' Liang Longlin', 'male', '1948-6-18' Professor')

Insert into tb_teacher values ('10428' Li Yang', 'male', '1955-8-12' Professor')

Insert into tb_teacher values ('10621' Lu Shi', 'male', '1943-11-18' Professor')

Insert into tb_teachervalues ('10809' Deng Weimin', 'male', '1957-1-26' 'associate professor')

Insert into tb_teacher values ('1961-6-5' associate professor 'Jiang Xiaohong', 'female', '2010-6-5')

Insert into tb_teacher values ('10131' Fu Lin', 'male', '1968-9-11' Fu Lin 'lecturer')

Insert into tb_teacher values ('10802' Yang Lianghong', 'male', '1941-5-23' Professor')

Insert into tb_teacher values ('10223' Zhou Yi', 'male', '1970-3-8' Zhouyi 'lecturer')

Insert into tb_teacher values ('20255 recording' Sun Lili', 'female', '1975-9-12' Lecturer')

Insert into tb_teacher values ('20705' summer snow', 'female', '1969-10-28' summer snow 'lecturer')

Insert into tb_teacher values ('10712' Southern', 'male', '1975-9-13' 'Lecturer')

Insert into tb_teacher values (Dai Shunda, male, 1940-12-17 Zhi Zheng, Lecturer)

Insert into tb_teacher values ('20301' Gao Shan', 'female', '1965-6-19' associate professor')

Insert into tb_teacher values ('20319' Lin Ni', 'female', '1973-4-1' 'Lecturer')

Insert into tb_teacher values ('21025' Zhangqi', 'female', '1972-6-6' Zhangqi 'lecturer')

Insert into tb_teacher values ('11117' Han Ming', 'male', '1976-2-14' 'assistant')

Insert into tb_teacher values ('10503' Sun Jianguo', 'male', '1949-10-1' professor')

Insert into tb_teacher values ('10509' Huang Ning', 'male', '1956-12-23' 'associate professor')

Insert data into tb_renke of any course schedule

The copy code is as follows:

Tb_renke

Insert into tb_renke values ('21003')

Insert into tb_renke values ('30211')

Insert into tb_renke values ('30232')

Insert into tb_renke values ('40711')

Insert into tb_renke values ('40722' and '20701')

Insert into tb_renke values ('10101' and '20106')

Insert into tb_renke values ('11101')

Insert into tb_renke values ('20511')

Insert into tb_renke values ('10101')

Insert intotb_renke values ('20534')

Insert into tb_renke values ('10712' and '20705')

Insert into tb_renke values ('20115 June 20106')

Insert into tb_renke values ('10222' and '10223')

Insert into tb_renke values ('30412 and 10429')

Insert into tb_renke values ('40316' recorder 20319')

Insert into tb_renke values ('40612' and '20626')

Insert into tb_renke values ('20328,0301')

Insert into tb_renke values ('10812 and 10429')

Insert into tb_renke values ('20801')

Insert into tb_renke values ('30802')

Insert into tb_renke values ('11001')

Insert into tb_renke values ('20113, last month 10131')

Insert into tb_renke values ('30416 million million 10428')

Insert into tb_renke values ('20327,0312)

Insert into tb_renke values ('20521' and '20506')

Insert into tb_renke values ('30213 June 10201')

Insert into tb_renke values ('11101')

Insert into tb_renke values ('10715', last month 20222')

Insert intotb_renke values ('20111, last month 10101')

Insert into tb_renke values ('10218')

Insert intotb_renke values ('30423,0406)

Insert into tb_renke values ('40331' and '20319')

Insert intotb_renke values ('40625,0616')

Insert into tb_renke values ('20314' and '20301')

Insert into tb_renke values ('10811')

Insert into tb_renke values ('30819' recording 10802')

Insert data into the professional table tb_major

The copy code is as follows:

Tb_major

Insert into tb_major values ('Chemistry','Lu Shi')

Insert into tb_major values ('computer', 'Deng Weimin')

Insert into tb_major values ('Software','Li Ming')

Insert into tb_major values ('Foreign Trade', 'Shen Feifei')

Insert into tb_major values ('Mathematics', 'Liang Longlin')

Insert into tb_major values ('Physics','Li Yang')

Insert intotb_major values ('physics', 'Wang Guoyu')

Insert into tb_major values ('History','Wu Yan')

Insert into tb_major values ('Chinese', 'Gao Shusheng')

Insert into tb_major values ('News', 'Gong Wen')

Insert data into the course schedule tb_course

The copy code is as follows:

Tb_course

Insert into tb_course values ('20511' modern history of the world', 4pd4)

Insert into tb_course values ('10101' College Chinese', 2Phone2)

Insert into tb_course values ('20801' computer fundamentals (1)', 4p3)

Insert into tb_course values ('10218' Advanced Algebra', 4Pol. 4)

Insert into tb_course values ('11001) English (1)', 6 ~ (6)

Insert into tb_course values ('20113 recordings' foreign literature', 4pas 4)

Insert into tb_course values ('30416' interface technology', 4prime3)

Insert into tb_course values ('20327' editorial Science of Newspapers and periodicals', 2Power2)

Insert into tb_course values ('20521' National History of China', 3jin2)

Insert into tb_course values ('30213 Jing Jing' number Theory', 4pr 4)

Insert into tb_course values ('11101' sports', 2pc2)

Insert into tb_course values ('21003' English (2)', 4Pol. 4)

Insert into tb_course values ('10715' Advanced Mathematics', 4Pol. 4)

Insert into tb_course values ('20111 Zhi Jing' Ancient Chinese', 3Pol 3)

Insert into tb_course values ('30802' computer fundamentals (2)', 3PEM 3)

Insert into tb_course values ('30423' electromagnetic field theory', 3prime3)

Insert into tb_course values ('40331' Communication Psychology', 2pc2)

Insert into tb_course values ('40625' chromatograph', 2prime2)

Insert into tb_course values (an introduction to Journalism in 20314)

Insert into tb_course values ('10811' discrete Mathematics', 2Pol. 2)

Insert into tb_course values ('30819' compiling techniques', 4pc4)

Insert into tb_course values ('20534' modern Chinese history', 4pd4)

Insert into tb_course values ('10712' political economy', 3pc3)

Insert into tb_course values ('20115 Jing Jing' Modern Chinese', 4Jing 4)

Insert into tb_course values ('30211' introduction to statistics, 3pc3)

Insert into tb_course values ('30232' mathematical analysis', 2meme2)

Insert into tb_course values ('40711' International Investment Studies', 2jin2)

Insert into tb_course values ('40722' International Commercial Law', 2jin2)

Insert into tb_course values ('30832' design of the algorithm', 4prime4)

Insert into tb_course values ('10812' digital circuits', 4pc4)

Insert into tb_course values ('10222' Analytic Geometry', 2prime2)

Insert into tb_course values ('30412' Modern Physics experiments', 3Phone2)

Insert into tb_course values ('40316' History of Contemporary Journalism', 2jin2)

Insert into tb_course values ('40612' coordination chemistry', 3prime3)

Insert into tb_course values ('20328' modern news reports', 4pr. 4)

Insert data into the student's personal situation table tb_studentinfo

The copy code is as follows:

Tb_studentinfo

Insert into tb_studentinfo values (530120169021101), 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101, 530120169021101

Insert into tb_studentinfo values (530120170060701), 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060701, 530120170060

Insert into tb_studentinfo values (530120171072501), 530120171072501, "Hubei", "Baima Community Building Unit", "4133224"" go ",", ")

Insert into tb_studentinfo values (530120170122901), "Hunan", "Jinxing Community Building", "3133218"Chess", "was rated as a three-good student",)

Insert into tb_studentinfo values (530120168121101), "Yunnan", "Jingyuan Community Building Unit", "2133227" volleyball, football,'')

Insert into tb_studentinfo values (530120174050101), 530120174050101, 530120174050101, 530120174050101, 530120174050101, 530120174050101, 530120174050101, 530120174050101, 530120174050101, 530120174050101, 530120174050101, 530120174050101, 530120174050101)

Insert into tb_studentinfo values (530120175040702), 530120175040702, 530120175040702, 530120175040702, 530120175040702, 530120175040702, 530120175040702, 530120175040702, 530120175040702, 530120175040702, 530120175040702, 530120175040702, 530120175040702, 530120175040702, Jiangsu, Jiangan community, 5033123)

Insert into tb_studentinfo values ("Sichuan", "Baima Community Building Unit", "4133124" philatelic "," won the third prize of Philatelic Exhibition "," third prize ")

Insert into tb_studentinfo values (530120172092801), 530120172092801, 3133177, 530120172092801, 3133177, 530120172092801, 3133177, long-distance running football, won the provincial governor's second place, and was punished for cheating)

Insert into tb_studentinfo values ("Yunnan", "Sunshine Community Building", "3133222", "Photography", "won the Outstanding composition Award",')

Insert into tb_studentinfo values (530120174110901 'Guizhou', 'Sunshine Community Building Unit', '3133189''go', 'won the first prize in math competition',')

Insert into tb_studentinfo values (530120170080401 'Heilongjiang', 'Sunshine Community Building Unit', '3148212''go', 'won the third prize in math competition',')

Insert data into the score sheet tb_grade

The copy code is as follows:

Tb_grade

Insert into tb_grade values ('9805026),' 20801,'75, 87, 82)

Insert into tb_grade values ('970, 2033, 80, 89, 91)

Insert into tb_grade values ('9907002, 9907002)

Insert into tb_grade values ('9801055, 2011 13, 70, 65, 55)

Insert into tb_grade values ('9607039),' 40711,'85, 80, 88)

Insert into tb_grade values ('9907002) 9907002)

Insert into tb_grade values ('9801055,' 20111','78, 60, 65)

Insert into tb_grade values ('9902006). 10218. 75pr. 63. 52)

Insert into tb_grade values ('9902006) 11001) 78, 86, 81

Insert into tb_grade values ('9704001),' 30416', '80,900.90)

Insert into tb_grade values ('9803011) 9327 (95pence93pc90)

Insert into tb_grade values ('9908088,' 9908088, '11001,' 90pence91,'87)

Insert into tb_grade values ('9805026),' 20521,'90, 97, 96)

Insert into tb_grade values ('970, 2033, 313, 88, 69, 76)

Insert into tb_grade values ('9907002),' 11101,'88, 65, 72)

Insert into tb_grade values ('9801055),' 21003, '70,90,84)

Insert into tb_grade values ('9902006,' 11101, '80, 70, and 70)

Insert into tb_grade values ('9803011) (' 9803011)

Insert into tb_grade values ('9908088,' 11101,'82, 75, 78)

Insert into tb_grade values ('9805026) 9805026) 83pence85 (84)

Insert into tb_grade values ('9805026),' 20511,'90, 82, 86)

Insert into tb_grade values ('970, 2033, 80, 84, 83)

Insert into tb_grade values ('9907002),' 10101,'84, 96, 92)

Insert into tb_grade values ('9801055', '20801','76,'78 and 60)

Insert into tb_grade values ('9902006) 85pr 88pc81)

Insert into tb_grade values ('9704001', '9704001', '30802', '907',' 87')

Insert into tb_grade values ('9803011),' 20801, '60,50,51)

Insert into tb_grade values ('9704001,' 30423', 80,81, 85)

Insert into tb_grade values ('9603001,' 40331,'67, 72, 70)

Insert into tb_grade values ('9606005),' 40625,'83, 85, 84)

Insert into tb_grade values ('9803011,' 0314','76, 76, 76)

Insert into tb_grade values ('9908088,' 10811,'82, 92, 89)

Insert into tb_grade values ('9608066),' 30819,'78, 84, 82)

Insert into tb_grade values ('9805026,0534,70,90,84)

Insert into tb_grade values ('970, 2033),' 30211,'93, 89, 90)

Insert into tb_grade values ('9607039) 90722)

Insert into tb_grade values ('9907002),' 10712,'90, 96, 97)

Insert into tb_grade values ('9801055, 20115, 80, 82, 87)

Insert into tb_grade values ('9902006) 10222 (85pr 91)

Insert into tb_grade values ('9704001),' 30412,'78, 87, 990)

Insert into tb_grade values ('9603001,' 30316, '66, 71, 73)

Insert into tb_grade values ('9606005),' 40612,'70, 78, 60)

Insert into tb_grade values ('9803011, 9803011, 0328, 90pr 88, 87)

Insert into tb_grade values ('9908088,' 10812', 80, 67, 83)

Insert into tb_grade values ('9608066),' 30832, '95, 92, 93)

Insert into tb_grade values ('9908088, 10101, 80, 82, 87)

The following is a data query:

(1) inquire about the student numbers and names of students born after 31 December 78

Think about it. Unfold.

Select student number, name from tb_student where date of birth > '1979'

Or:

Select student number, name from tb_student where date of birth > cast ('1978-12-31' as datetime)

(2) inquire about the names of female students with admission scores greater than 500 points and male students with admission scores greater than 600 points.

Think about it. Unfold.

Select name from tb_student where (admission score > 500and gender = 'female') or (admission score > 600and gender = 'male')

(3) inquire about the names and addresses of students living in Jiangan District.

Think about it. Unfold.

Select home address, name from tb_studentinfo,tb_student where (home address like' Jiangan District%') and (tb_studentinfo. Student number = tb_student. Student number)

(4) inquire about the student numbers of students who have not taken the courses taught by teacher Jiang Xiaohong.

Think about it. Unfold.

Select student number from tb_student where student number not in (select student number from tb_grade,tb_teacher,tb_renke where tb_grade. Course code = tb_renke. Course code and tb_renke. Teacher code = tb_teacher. Teacher code and name = 'Jiang Xiaohong')

(5) inquire about the average final grade, student number and name of the courses chosen by each student.

Think about it. Unfold.

Select tb_student. Name, tb_grade. Student number, avg (final) from tb_student,tb_grade where tb_student. Student number = tb_grade. Student number group by tb_student. Name, tb_grade. Student number

(6) inquire about the course name, student number and name of the course Zhao Dong took.

Think about it. Unfold.

Select tb_student. Name, tb_grade. Student number, course name from tb_student,tb_grade, tb_course where tb_grade. Student number = tb_student. Student number and tb_grade. Course code = tb_Course. Course code and name = 'Zhao Dong'

(7) inquire about the name of the course taught by the professor

Think about it. Unfold.

Select course name is from tb_course,tb_teacher, tb_renke where tb_course. Course code = tb_renke. Course code and tb_teacher. Teacher code = tb_renke. Teacher code and title = 'professor'

Or:

Select course name from tb_course where course code in (select course code from tb_renke where teacher code in (select teacher code from tb_teacher where title = 'professor'))

(8) Statistics on the number of students without loans

Think about it. Unfold.

Select count (*) the number of people who do not have a loan from tb_student where whether the loan = 'false'

(9) inquire about the student numbers of students whose average scores of each subject are above 80 in peacetime, mid-term and final.

Think about it. Unfold.

Select student number with average score above from tb_grade group by student number having avg (usually + mid-term + final) > 80

(10) inquire about the student numbers and names of people with the same major as Mao Jie.

Think about it. Unfold.

Select student number, name from tb_student where professional in (select professional from tb_student where name = 'Mao Jie')

(11) inquire about the names, elective course numbers and scores of students whose final grades are greater than those taught by any teacher.

Think about it. Unfold.

Select name, course code, final term, tb_grade. Student number from tb_grade,tb_student where tb_student. Student number = tb_grade. Student number and final > = all (select final from tb_grade)

(12) here comes a new student. The student number is 9607001 and the provincial certificate number is 530120169021100.

Think about it. Unfold.

Insert into tb_studentinfo (student number, ID card) values (530120169021100')

Insert into tb_student (student number) values ('9607001')

(13) add 10 points to those whose final grades are above everyone's final average.

Think about it. Unfold.

Update tb_grade set end = end + 10 where end > (select avg (final) from tb_grade)

(14) inquire about the student numbers who have taken at least all the courses taught by Mr. Fu Lin

Think about it. Unfold.

Select student number from tb_grade x Where not exists (select * from tb_grade y Where teacher code in (Select teacher code from tb_teacher,tb_renke Where name ='Fu Lin 'and tb_teacher. Teacher code = tb_renke. Teacher code) And not exists (select * from tb_grade z Where z. Course code = y. Course code and z. Student number = x.)

(15) Statistics on the number of classes taught by each teacher

Think about it. Unfold.

Select teacher code, count (*) course number from tb_renke group by tb_renke. Teacher's code name

(16) inquire about the student numbers and average scores of students taking more than 2 courses (refers to the average final scores of each course, only those that pass), and output them in descending order according to their average scores.

Think about it. Unfold.

Select student number, avg (final) average score from tb_grade where final > 60 group by student number having count (*) > 2 order by avg (final) desc

(17) inquire about the student numbers of students who do not have loans.

Think about it. Unfold.

Select student number does not have a loan student number from tb_student where whether payment = 'false'

(18) inquire about the student number and name of the oldest student

Think about it. Unfold.

Select student number, name from tb_student where birth date in (select min (date of birth) from tb_student)

At this point, the study on "how to use SQL statements to implement queries" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.