SQL operations and queries
1. Introduction to SQL
SQL is a Structured Query Language (Structured Query Language), which is specially used for operations such as data access, data update, and database management.
In Oracle development, the client sends the SQL statement to the server, the server compiles and executes the SQL statement, and returns the execution result to the client. An Oracle SQL statement consists of the following commands:
The current mainstream database products (such as: SQL Server, Oracle,MySQL) all support standard SQL statements. Data definition language, table addition, deletion and modification operations, simple data query, transaction submission and rollback, permission authorization and revocation, etc., Oracle and MySQL are basically the same in operation.
2. Explanation of data types
The core of the Oracle database is the table, and the common data types used by the columns in the table are as follows:
type | meaning |
---|---|
CHAR(length) | Stores a fixed-length string. The parameter length specifies the length, if the length of the stored string is less than length, it will be filled with spaces. The default length is 1, and the maximum length does not exceed 2000 bytes. |
VARCHAR2(length) | Store variable-length strings. length specifies the maximum length of the string. The default length is 1, and the maximum length cannot exceed 4000 characters. |
NUMBER(p,s) | It can store both floating-point numbers and integers. p represents the maximum number of digits (if it is a decimal, it includes the integer part, decimal part and decimal point, and p defaults to 38), and s refers to the number of decimal places. |
DATE | Store date and time, store era, 4-digit year, month, day, hour, minute, second, and store time from January 1, 4712 BC to December 31, 4712 AD. |
TIMESTAMP | Not only the year, month, day, hour, minute, second, and 6 digits after the second are stored, but also the time zone is stored. |
CLOB | Store large text, such as storing unstructured XML documents |
BLOB | Stores binary objects such as graphics, video, sound, etc. |
Example corresponding to the NUMBER type:
Format | entered number | actual storage |
---|---|---|
NUMBER | 1234.567 | 1234.567 |
NUMBER(6,2) | 123.4567 | 123.46 |
NUMBER(4,2) | 12345.67 | The entered number exceeds the specified precision and cannot be stored in the database |
For the date type, you can use the sysdate built-in function to obtain the current system date and time, return the DATE type, and use the systimestamp function to return the current date, time and time zone.
In Oracle query, the complete syntax of "select column... from table" must be used. When querying a single-row function, the DUAL table is used after from. The dual table has only one row and one column in the system. When outputting a single-row function, the table is for select... from for grammatical integrity.
3. Create tables and constraints
1. Table structure
The syntax of creating a table in Oracle
CREATE TABLE table name (
field name type constraints,
field name type constraints,
Field name Type Constraint
)
Case insensitivity in the database
Related cases of creating tables and comments
-- Create a user table. Used to store user information CREATE TABLE t_student( id number(5) , -- student number name varchar2(20) , -- student's name age number(2) , -- student's age address varchar2(100) -- student's address ); -- SQL At the end of the statement, we add a ; -- Give table add notes: COMMENT ON TABLE t_student is 'student table'; -- Add comments to the table COMMENT ON COLUMN t_student.id is 'student number' ; -- Add comments to fields in the table COMMENT ON COLUMN t_student.name is 'student name' ; COMMENT ON COLUMN t_student.age is 'age' ; COMMENT ON COLUMN t_student.address is 'student address';
2. Update table structure
We may need to modify and adjust the table after we create a table structure
-- Operations on table structures -- 1.delete table DROP TABLE t_student; CREATE TABLE t_student( id number(5) , -- student number name varchar2(20) , -- student's name age number(2) , -- student's age address varchar2(100) -- student's address ); -- SQL At the end of the statement, we add a ; -- add field ALTER TABLE t_student ADD gender char(3); -- Modify field type ALTER TABLE t_student MODIFY gender varchar2(3); -- Modify field name ALTER TABLE t_student RENAME COLUMN gender to sex; -- delete field ALTER TABLE t_student DROP COLUMN sex;
3. Constraints
1. Non-null constraint The content of this field is not allowed to be empty
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) , – student ID
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;
2. Default value: If the value of the column is null, it will be filled with the default value
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) , – student ID
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;
3. Unique constraint: when there is data. Duplicate records cannot appear in this column. null does not include
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) unique , – student number
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;
– not null + unique constraint
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) unique not null , – student number
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;
4. Primary key constraint: a constraint that can uniquely identify the field. The field modified by the primary key cannot be repeated and cannot have null values
– And only one annotation can appear in a table [joint primary key: a primary key contains multiple fields]
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) primary key , – student number
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;
– Create a table and then add a primary key
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) , – student ID
name varchar2(20) not null, – student's name
age number(2) default 18, – the age of the student
address varchar2(100), – the address of the student
gender char(3) default 'male'
); – the end of the SQL statement, we add an English status ;
alter table t_student add constraints pk_student_id primary key(id);
5. Foreign key: the relationship between tables and tables
– Foreign key: It can appear repeatedly in the main table, but its value is the primary key of another table, and the foreign key associates the two tables
drop table t_class;
create table t_class(
id number(3) primary key,
name varchar2(30)
);
drop table t_student ;
create table t_student(
id number(3) primary key,
name varchar2(30) ,
classid number(3)
);
– Maintain foreign key associations
alter table t_student add constraints fk_student_classid foreign key(classid) references t_class(id);
6.check constraints: based on the field type. Further improve the accuracy of the data
DROP TABLE t_student;
CREATE TABLE t_student(
id number(5) unique not null , – student number
name varchar2(20) not null, – student's name
–age number(2) check(age > 1 and age < 25), – age of the student
age number(2) check(age between 1 and 25), – the age of the student
address varchar2(100), – the address of the student
gender char(3) check (gender in ('male','female'))
); – the end of the SQL statement, we add an English status ;
Four, DML statement
DML data manipulation language: implement data insertion, modification and deletion operations through SQL, and the commonly used data manipulation voices in Oracle are
- INSERT
- UPDATE
- DELETE
- SELECT ... FOR UPDATE
1.INSERT
Data insertion statement
INSERT INTO Table Name(fieldName1,fieldName1,...fieldNameN)values(value1,value2,...,valueN) -- the case insert into t_class(id,name)values(1,'Computer Class 1');
Short syntax: If the records we insert need to add information to each field in the table. Then we can omit the field list, but the subsequent value list must be consistent with the order of the fields in the table structure
INSERT INTO Table Name values(value1,value2,...,valueN) INSERT INTO t_class values(2,'Computer Class 2'); -- The following is an example of the error INSERT INTO t_class values('English class one',3);
2. Serial number
Primary key: When we insert data, we need to ensure its uniqueness
- The serial number solution provided in Orlace to solve
- MySQL provides a primary key auto-increment scheme
- In a distributed environment. We can solve it by distributed ID
Syntax for serial number:
CREATE SEQUENCE sequence name [INCREMENT BY] -- The number of increments each time [START WITH 1] -- counting from 1 [NOMAXVALUE] -- Do not set a maximum [NOCYCLE] -- Always accumulate, no loop CACHE 10; -- Cache 10
case application
create sequence s_class; -- Start from 1 and increase by 1 each time -- currval is executing nextval will take effect after select s_class.currval from dual; select s_class.nextval from dual;
Application of insert statement
INSERT INTO t_class values(s_class.nextval,'Computer Class 2');
3.UPDATE
Need to make adjustments to the data that has been inserted into the table structure. corresponding grammatical structure
UPDATE Table Name SET field1=value1,field2=value2 ... [where condition] update t_class set name = 'software class' ; update t_class set name = 'Computer Class 1' where id = 15; -- renew id The record for 18 19 21 is 'Computer Class 2' update t_class set name = 'Computer Class 2' where id = 18 or id=19 or id=21 ; update t_class set name = 'Computer Class 3' where id in (14,16,17) ; update t_class set name = 'English Class 1' where id >= 100 and id <=200 ; update t_class set name = 'English Class 2' where id between 100 and 130 ; update t_class set name = 'test' where id != 24 ; update t_class set name = 'test666' where id <> 22 ;
4. Delete statement
DELETE FROM Table Name [where condition] delete from t_class where id = 17; -- for null The query we use is is null to match delete from t_class where name is null; -- delete Delete the cache that will do data delete from t_class ; -- All data in the table will be deleted. Delete the library and run away, use it carefully -- Delete the data of the whole table, delete the data directly. Do not cache. efficient. high risk truncate table t_class ;
5. Insert multiple rows
-- multiline insert insert into t_class_copy(id,name) select id,name from t_class select * from t_class_copy1 -- copy table create table t_class_copy as select * from t_class where 1 != 1; -- Copy table with data create table t_class_copy1 as select * from t_class ;
Five, DQL statement
DQL: Data Query Language.
Syntax Structure of Query Statement
--Grammatical structures SELECT <column name> FROM <Table Name> [WHERE <Query conditions> ] [ORDER BY <sorted column name>[ASC or DESC] ] [GROUP BY <grouping field> ]
1. Simple query statement
1.1 Explanation of knowledge points
Contents in a full table query:
create sequence s_student; insert into t_student(id,name,age,address,gender)values(s_student.nextval,'Wang Wu',18,'Changsha, Hunan','female') ; -- 1.Query all the information of the student table * Represents all columns in the query table select * from t_student; -- 2.query specific columns select id,name,age from t_student; -- 3.Query information is identified by an alias,alias cannot be used' we have to use " select id as "student number", name as "student name", age as "student age" from t_student; -- we can omit "" select id as student number, name as student name, age as student age from t_student; -- We can also omit as keywords select id student number, name student name, age student age from t_student; -- If there are special symbols in the alias. then you can't omit"" select id "[Student ID]", name as student name, age as student age from t_student; -- 4.constant handling select id,name,age ,29 weight from t_student -- 5.Query student information. id and name stitched together || select id,name,age , '['||id||'-'||'name'||']' combination from t_student
Conditional query:
drop table t_student ; create table t_student( id number(3) primary key, name varchar2(30) , gender char(3) , age number(2), class_id number(5) ) Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (1,'Zhang San','male ',18,1001); Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (2,'Li Si','female ',22,1002); Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (3,'Xiao Ming','male ',35,1003); Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (4,'little flower','female ',16,1001); Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (5,'Zhang Sanfeng','male ',18,1001); Insert into DPB.T_STUDENT (ID,NAME,GENDER,AGE,CLASS_ID) values (6,'Wang Zhangni','female ',25,1002); -- 6.Find students between the ages of 18 and 25 select * from t_student where age >= 18 and age <= 25 select * from t_student where age between 18 and 25; -- 7.Query the student information with the class number 1001 or 1002 select * from t_student where t_student.class_id = 1001 or t_student.class_id = 1002 select * from t_student t where t.class_id = 1001 or t.class_id = 1002 select * from t_student t where t.class_id in (1001,1002) -- 8.Query the information of students who are not in the class number 1001 and 1002 select * from t_student where class_id not in (1001,1002) or class_id is null -- 9.Query the information of all students surnamed Zhang Fuzzy query: like select * from t_student where name like 'open%' select * from t_student where name like '%open%' -- 10.Query information about all students with the surname Zhang. And the name is composed of two characters select * from t_student where name like 'open_' -- 11.Find all student information. Sort by age ascending asc Ascending can be omitted select * from t_student; select * from t_student order by age ; select * from t_student order by age desc; -- 11.Find all student information. Sort by class in ascending order. If the class is the same then sort by age descending select * from t_student order by class_id , age desc -- 12.Query all class numbers in the student table distinct Keywords can help us remove duplicate records. -- If there are multiple columns behind. What is removed is the same record in multiple column combinations select distinct class_id,age from t_student
1.2 Case explanation
SQL practice explanation:
Table Structure
Emp----employee information form Ename varchar2(30), --Name Empno number(5), --serial number Deptno number(5), --department Job varchar2(20), --type of work(Personnel category),like: manager manager, clerk clerk Hiredate Date, --date of employment Comm number(6,2), --commission Sal number(6,2) --salary Mgr number(5) --employee supervisor number Dept-----department table Dname varchar2(30), --department name Deptno number(5), --department number Loc varchar2(50) --Location
Prepare data:
create table emp --–Create employee information form ( Ename varchar2(30), --Name Empno number(5), --serial number Deptno number(5), --department Job varchar2(20), --type of work(Personnel category),like: manager manager, clerk clerk Hiredate Date, --date of employment Comm number(7,2), --commission Sal number(7,2) , --salary Mgr number(5) --serial number ); insert into EMP (ENAME, EMPNO, DEPTNO, JOB, HIREDATE, COMM, SAL, MGR) values ('Zhang San', 1, 10, 'clerk', null, 500, 2000, null); insert into EMP (ENAME, EMPNO, DEPTNO, JOB, HIREDATE, COMM, SAL, MGR) values ('Li Si', 2, 10, 'clerk', null, 650, 2333, null); insert into EMP (ENAME, EMPNO, DEPTNO, JOB, HIREDATE, COMM, SAL, MGR) values ('Wang Wu', 3, 20, 'clerk', null, 1650, 1221, null); insert into EMP (ENAME, EMPNO, DEPTNO, JOB, HIREDATE, COMM, SAL, MGR) values ('Xiao Zhang', 4, 20, 'manager', null, 980, 3200, null); insert into EMP (ENAME, EMPNO, DEPTNO, JOB, HIREDATE, COMM, SAL, MGR) values ('Xiao Liu', 5, 10, 'clerk', null, 500, 2000, null); commit; create table dept --department table ( Dname varchar2(30), --department name Deptno number(5), --department number Loc varchar2(50) -- department location ); insert into DEPT (DNAME, DEPTNO, LOC) values ('Marketing Department', 10, 'Dalian'); insert into DEPT (DNAME, DEPTNO, LOC) values ('R & D department', 20, 'Shenyang'); insert into DEPT (DNAME, DEPTNO, LOC) values ('Personnel Department', 30, 'Shenzhen'); insert into DEPT (DNAME, DEPTNO, LOC) values ('Personnel Department', 40, 'Guangzhou'); commit;
topic:
single table query 1.Select employees in department 30 select * from emp where emp.deptno = 30 2.List all clerks by name, number and department select ename as Name,empno "serial number",deptno department from emp where emp.job = 'clerk' select ename as Name,empno "serial number",deptno department ,(select dname from dept where dept.deptno = emp.deptno) Department name from emp where emp.job = 'clerk' 3.Find employees whose commission is higher than their salary select * from emp where comm > sal 4.Find out if the commission is higher than the salary 60%employees select emp.*,sal*0.6 from emp where comm > sal*0.6 5.Find the details of all managers in department 10 and all clerks in department 20 select * from emp where (deptno = 10 and job = 'manager') or (deptno = 20 and job = 'clerk') 6.Find out the different jobs of employees on commission select distinct job from emp where comm > 0 7.Find employees who take no commission or take less than 100 commission select * from emp where (comm <= 0 or comm is null ) or comm < 100 select * from emp where comm <= 100 or comm is null 8.Find all employees who were hired on the last day of each month select emp.*,last_day(emp.hiredate) from emp where emp.hiredate = last_day(emp.hiredate) 9.Find employees who were hired more than 25 years ago select emp.*,sysdate from emp where add_months(emp.hiredate,25*12) < sysdate 10.Display names of all employees with only the first letter capitalized select emp.*,ename,substr(ename,1,1) from emp where substr(ename,1,1) >= 'A' and substr(ename,1,1) <= 'Z' select emp.*,ename,substr(ename,1,1) from emp where substr(ename,1,1) between 'A' and 'Z' 11.Displays the employee name with exactly 6 characters select emp.*,emp.ename,length(emp.ename) from emp where length(emp.ename) = 6 12.show without'R'employee name of select * from emp where emp.ename not like '%R%' 13.Display the first three characters of all employees' names select emp.*,substr(emp.ename,1,2) from emp 14.To display the names of all employees, use a replace all'A' select ename,replace(ename,'A','a') from emp 15.Show the names of all employees and the date they completed their 10 years of service select ename,hiredate,add_months(hiredate,10*12) from emp 16.Display employee details, sorted by name select * from emp order by ename desc 17.Display employee names, with the oldest employee first, based on their years of service select ename Name from emp order by hiredate asc 18.Displays the names, jobs, and salaries of all employees, sorted in descending order by job, and ascending by salary if the job is the same select ename Name, job Work ,sal salary from emp order by job desc ,sal asc 19.Displays the names of all employees and the year and month they joined the company, sorted by the month the employee was hired, with the oldest year first select emp.ename, emp.hiredate, extract(YEAR from emp.hiredate) year, extract(MONTH from emp.hiredate) month from emp order by extract(MONTH from emp.hiredate) ,extract(YEAR from emp.hiredate) ; 20.Display the daily salaries of all employees in a month with 30 days select emp.*,nvl(sal,0),round(nvl(sal,0)/30,2) Daily salary from emp 21.Find all employees hired in February (of any year) select * from emp where extract(MONTH from hiredate) = 2 22.For each employee, show the number of days they have been with the company select emp.*,round(sysdate-hiredate ) number of days from emp 23.Anywhere the name field is displayed, including "A" The names of all employees of select ename Name from emp where ename like '%A%' 24.Displays the years of service of all employees in years, months and days select trunc((sysdate-hiredate)/365) year, trunc((sysdate-hiredate)/12) month, trunc((sysdate-hiredate)) days from emp
2. Aggregate function
The role of aggregate functions: to solve our statistical needs for data
-- Aggregate and group functions -- 1.Count the number of students count(Field Name) Count the number of records whose data in this column is not empty select count(*),count(name),count(gender),count(age),count(1) from t_student ; -- count(*) and count(1) the difference select t_student.*,1 from t_student ; -- 2.Other commonly used statistical functions select count(1),max(age),min(age),sum(age),round(avg(age)) from t_student ;
3. Group query
grouping function group by -- 1> Not used together with the aggregate function, the effect is the same as distinct The same one. Duplicate records can be removed select * from emp ; select deptno from emp group by deptno ; select distinct deptno from emp; select deptno,job from emp group by deptno , job order by deptno; -- 2>Scenarios for use with aggregate functions,The data counted by the aggregation function is not all the data queried. but the grouped data -- We cannot directly appear non-grouped fields in the grouped data -- a.Count the number of boys and girls in the student table select gender,count(1) from t_student group by gender; -- b.Count the number of people in each class in the student table select class_id,count(1) from t_student t group by t.class_id; -- c.Count the number of boys and girls in each class in the student table. The aggregation function counts the data of the smallest unit after grouping select class_id,gender,count(1) from t_student group by class_id ,gender ; -- d.Count the number of boys and girls who are older than 18 in the student table select gender,count(1) from t_student where age > 18 -- where The location must be in the group by Before. The function is to filter the data to be grouped group by gender -- e.Count the records in the learning table where the class size is greater than 1 and the age is greater than 18 select class_id,count(1) from t_student where age > 18 group by class_id having count(1) > 1 -- exist group by after. and group by With the use of. The function is to filter the grouped data
4. Multi-table query
Oracle and MySQL are both relational databases. [Relationship] refers to the relationship between tables and data between tables.
multi-table query -- 1.cross connect:Get the Cartesian product of two tables select * from t_class ; select * from t_student; -- Query student information and corresponding class information select t1.*,t2.* from t_student t1,t_class t2 -- Equivalent connection: add filter conditions on the basis of cross connection select t1.*,t2.* from t_student t1,t_class t2 -- 1000 * 1000 = 100W where t1.class_id = t2.id -- where The key is the conditional filter after the result set -- Inner join: When the records in the left table structure and the records in the right table structure are connected, they will be based on on Conditional judgment in . Get it if you are satisfied. otherwise all lost select t1.*,t2.* from t_student t1 inner join t_class t2 on t1.class_id = t2.id -- Query all the records in the student table. At the same time, the class name corresponding to the student is displayed select t1.*,t2.* from t_student t1 inner join t_class t2 on t1.class_id = t2.id -- left join:based on inner joins. Keep the data that does not meet the condition on the left select t1.*,t2.* from t_student t1 left outer join t_class t2 on t1.class_id = t2.id -- right join:based on inner joins. Keep the data that does not meet the conditions on the right -- Query all class information. Query relevant student information at the same time select t2.*,t1.* from t_student t1 right outer join t_class t2 on t1.class_id = t2.id -- Full connection: Based on the inner connection, the data on the left and right sides that do not meet the conditions are retained select t1.*,t2.* from t_student t1 full join t_class t2 on t1.class_id = t2.id -- union union all merge result set select t1.*,t2.* from t_student t1 left outer join t_class t2 on t1.class_id = t2.id union -- Merge result sets. Duplicate records will be removed, similar to full connections select t1.*,t2.* from t_student t1 right outer join t_class t2 on t1.class_id = t2.id select t1.*,t2.* from t_student t1 left outer join t_class t2 on t1.class_id = t2.id union all -- Merge result sets. Duplicate records will not be removed select t1.*,t2.* from t_student t1 right outer join t_class t2 on t1.class_id = t2.id
5. Case explanation
drop table student; create table student ( id number(3) PRIMARY key, name VARCHAR2(20) not null, sex varchar2(4), birth number(4), department varchar2(20), address VARCHAR2(50)); create score surface. SQL code show as below: create table score( id number(3) PRIMARY key, stu_id number(3) not null, c_name VARCHAR(20) , grade number(3) ); -- Towards student table insert record INSERT The statement is as follows: insert into student values(901,'Boss Zhang','male',1985,'computer science','Haiding district, Beijing'); insert into student values(902,'Zhang Laoer','male',1986,'Department of Chinese','Changping district, Beijing'); insert into student values(903,'Zhang San','female',1990,'Department of Chinese','Yongzhou City, Hunan Province'); insert into student values(904,'Li Si','male',1990,'English major','Fuxin City, Liaoning Province'); insert into student values(905,'Wang Wu','female',1991,'English major','Xiamen City, Fujian Province'); insert into student values(906,'Wang Liu','male',1988,'computer science','Hengyang City, Hunan Province'); -- Towards score table insert record INSERT The statement is as follows: insert into score values(1,901,'computer',98); insert into score values(2,901,'English',80); insert into score values(3,902,'computer',65); insert into score values(4,902,'Chinese',88); insert into score values(5,903,'Chinese',95); insert into score values(6,904,'computer',70); insert into score values(7,904,'English',92); insert into score values(8,905,'English',94); insert into score values(9,906,'computer',90); insert into score values(10,906,'English',85); SELECT * from student; select * from score; 1,Inquire student The 2nd to 4th records of the table Steps for paging query: -- rownum:The system is automatically maintained from 1 select t.* ,rownum from ( select s.*,rownum num from student s where rownum <=4 ) t where t.num >=2 2,from student The table queries the student ID numbers of all students ( id), Name( name)and departments ( department)Information select id student ID, name Name, department Faculty from student 3,from student Query the information of students in the Department of Computer Science and Department of English in the table select * from student -- where department = 'computer science' or department = 'English major' where department in ('English major','computer science' ) 4,from student Query age in table 25~30 Year-old Student Information select s.*,(extract(year from sysdate) - birth) age from student s where (extract(year from sysdate) - birth) between 30 and 35 5,from student Query the number of people in each department in the table? select department,count(1) from student group by department 6,from score Query the highest score of each subject in the table select c_name,max(grade) from score group by c_name 7,Query Li Si's test subjects ( c_name)and test scores ( grade) Notice: '=' It is only used when the result is determined to be one. If it is not determined, use 'in' select * from student for update; select c_name,grade from score where stu_id in ( select id from student where name = 'Li Si' ) select c_name,grade from score where exists ( -- Whether the condition of the following subquery is satisfied select id from student where name = 'Li Si' and score.stu_id = student.id ) select s1.c_name,s1.grade,s2.name from score s1 , student s2 where s2.name = 'Li Si' and s1.stu_id = s2.id select s1.name,s2.c_name,s2.grade from student s1 left join score s2 on s1.id = s2.stu_id -- and s1.name = 'Li Si' The conditions for the connection to match where s1.name = 'Li Si' select s1.name,s2.c_name,s2.grade from ( select * from student where name = 'Li Si' ) s1 left join score s2 on s1.id = s2.stu_id -- and s1.name = 'Li Si' The conditions for the connection to match -- where s1.name = 'Li Si' 8,Query all student information and test information by inner connection select s1.*,s2.* from student s1 inner join score s2 on s1.id = s2.stu_id 9,Compute the total grade for each student select stu_id,(select name from student where id = stu_id) name,sum(grade) from score group by stu_id 10,Calculate the average grade for each exam subject select c_name,round(avg(grade),2) average score from score group by c_name 11,Query the information of students whose computer scores are lower than 95 select s1.*,s2.grade from student s1 left join score s2 on s1.id = s2.stu_id where s2.grade < 95 and s1.department = 'computer science' 12,Inquire about the information of students who take computer and English exams at the same time select * from student where id in ( select stu_id from score where c_name = 'computer' and c_name = 'English' ) select t1.stu_id from ( select * from score where c_name = 'computer' ) t1, (select * from score where c_name = 'English' ) t2 where t1.stu_id = t2.stu_id 13,Sort computerized test scores from high to low select * from score where c_name = 'computer' order by grade desc 14,from student table and score Query the student's student number in the table, Then combine query results UNION and union all select id from student union all select stu_id from score 15,Query the name, department, examination subjects and results of students surnamed Zhang or Wang select s1.name ,s1.department,s2.c_name,s2.grade from (select * from student where name like 'open%' or name like 'king%') s1 left join score s2 on s1.id = s2.stu_id 16,Query the names, ages, faculties, exam subjects and grades of students from Hunan select s1.name ,s1.department,s2.c_name,s2.grade from (select * from student where address like 'Hunan%' ) s1 left join score s2 on s1.id = s2.stu_id