Day 5 continue SQL multi table query

SQL multi table query

#Alias table names

SELECT st.class_num,cl.class_name FROM student st,class cl

#Connection query

Find out the class name of each student

#The table on the left is the main table (the most used)

SELECT st.class_num,cl.class_name FROM student st,class cl

# the table on the right is the main table

SELECT student.name,student.class_num,class.class_name FROM student RIGHT JOIN class on student.class_num=class.class_num

#Who is the main table is the main query

Nested queries (loss of computer performance)

-->Two dimensional table -- > two dimensional table # first finds a table, and then continues to query from this table

#First query the inside and the outside

SELECT t1.name FROM

(SELECT student.name,student.class_num,class.class_name FROM student LEFT JOIN class on student.class_num=class.class_num) t1 WHERE class_num='20201001'

#Find the names of all students whose course number is 20201001

SELECT t2.name FROM

(SELECT student.name,relationship.cno FROM student LEFT JOIN relationship on student.sno = relationship.sno) t2 WHERE t2.cno = '1001'

#Find the names of all students who choose the course number mathematics

SELECT t4.name FROM

(SELECT t3.name,scoure.gradeName FROM

(SELECT student.name,relationship.cno FROM student LEFT JOIN relationship on student.sno = relationship.sno) t3 LEFT JOIN scoure on t3.cno = scoure.cno) t4 WHERE t4.gradeName = 'mathematics'

#Find out the class name of each student

SELECT student.name,class.class_name FROM student INNER JOIN class on student.class_num=class.class_num

#Query the information of the oldest students

SELECT * from student ORDER BY age DESC LIMIT 1 OFFSET 0

#Find out which age group of students has the most statistics

SELECT age,COUNT(age) FROM student GROUP BY age LIMIT 1 OFFSET 0

#Find out the names of students who are over 21 years old and choose Chinese

SELECT a1.name,a1.age,scoure.gradeName FROM

(SELECT student.name,student.age,relationship.cno FROM student INNER JOIN relationship on student.sno = relationship.sno) a1 inner JOIN scoure on a1.cno=scoure.cno

#Query the course name and the name of the oldest student in the software engineering class

SELECT a4.name,scoure.gradeName FROM

(SELECT a3.name,relationship.cno FROM

(SELECT a2.name,a2.sno FROM

(SELECT a1.sno,a1.age,a1.name from

(SELECT student.sno,student.name,student.age,class.class_name FROM student INNER join class on student.class_num = class.class_num) a1 WHERE a1.class_name='software engineering') a2 ORDER BY age DESC LIMIT 1 OFFSET 0) a3 INNER JOIN relationship on a3.sno = relationship.sno) a4 INNER join scoure on a4.cno =scoure.cno

#Data modification

#Add delete modify

#Increase

INSERT if the id column is automatically incremented, no insertion is required

#INSERT into table name (column 1, column 2, column 3, column 4, column 5) VALUES (value 1, value 2, value 3, value 4, value 5)

INSERT into student(name,age,sex) VALUES('Song Yu',18,'male')

#Insert multiple pieces of data

#INSERT into table name (column 1, column 2, column 3, column 4, column 5) VALUES (value 1, value 2, value 3, value 4, value 5), (value 1, value 2, value 3, value 4, value 5), (value 1, value 2, value 3, value 4, value 5)

INSERT into student(name,age,sex) VALUES('Song Yu',18,'male'),('Song Yiyu',18,'male'),('Song 2yu',18,'male')

#Another form of insertion

#INSERT INTO table name SET = value, column = value, column = value, column = value,

INSERT INTO student SET name ='ssss',age=11

#Modification of data

#Specifies the row for data modification

#UPDATE table name set column = value, column = value WHERE

UPDATE student set name='123' WHERE id='1'

#Deletion of data

#DELETE FROM table name WHERE column = value specifies the column to delete

DELETE FROM student WHERE id='1'

#DELETE FROM deletes the entire table in one row of table name (the speed of deleting the entire table is slow)

DELETE FROM

#TRUNCATE TABLE table name; Empty table

TRUNCATE table

Little practice

1. Find out the class name of each student: prompt, not all students have class names

SELECT student.name,class_name from student INNER JOIN clss on student.class_num = class.class_num

2. Query the information of the oldest students: prompt: limit

SELECT * from student ORDER BY age DESC LIMIT 1 OFFSET 0

3. Find out which age group has the most students among the students, and count their occurrence times

SELECT age,count(age) num from student GROUP BY age LIMIT 1 OFFSET 0

4. Find out the names of students who are over 21 years old and choose Chinese

SELECT t2.name from
(SELECT t1.name,t1.age,scoure.gradeName from
(SELECT student.name,student,age relationship.cno FROM student INNER JOIN relationship on student.sno = relationship.sno) t1 inner join scoure on t1.cno = scoure.cno) t2 where t2.gradeName='language' and age>21

5. Query the course name and the name of the oldest student in the software engineering class

SELECT a4.name,scoure.gradeName FROM
(SELECT a3.name,relationship.cno FROM
(SELECT a2.name,a2.sno FROM
(SELECT a1.sno,a1.age,a1.name from
(SELECT student.sno,student.name,student.age,class.class_name FROM student INNER join class on student.class_num = class.class_num) a1 WHERE a1.class_name='software engineering') a2 ORDER BY age DESC LIMIT 1 OFFSET 0) a3 INNER JOIN relationship on a3.sno = relationship.sno) a4 INNER join scoure on a4.cno =scoure.cno

Keywords: Database SQL

Added by msore on Thu, 06 Jan 2022 14:49:00 +0200

Programming VIP