preface

This semester, we have a course of database principle and application, which is very difficult at the beginning, mainly because we didn't clarify the relationship between mathematical logic and database syntax in the process of SQL learning. Today, I would like to summarize my learning about SQL. I only briefly introduce the implementation of SQL code. If there are any errors, please correct them.

SQL syntax has some fixed rules in it

SQL language, like Java and Python, has its own syntax system.
At the beginning of learning database, the teacher didn't ask for anything, but kept asking us to create a new database and a new table, then delete it and build it again.
At first, I felt very boring, but now I find that the original saying that "basic conditions determine superstructure" is true.
First of all, before learning SQL, I suggest that students with self-study ideas first remember a principle, that is, "constant, should change". They must remember that in SQL, whether it is data update, query or any other operation, the first thing to consider must be "what to achieve", followed by "how to achieve", and finally "what conditions".

First, a brief introduction to SQL language

Composition of SQL language:

1. An SQL database is a collection of tables, which is defined by one or more SQL schemas.
2. An SQL table consists of a row set. A row is a sequence (set) of columns, and each column corresponds to a data item.
SQL database
SQL database
3. A table is either a basic table or a view. A basic table is a table actually stored in a database, while a view is a definition of a table composed of several basic tables or other views.
4. A basic table can span one or more storage files, and a storage file can also store one or more basic tables. Each storage file corresponds to the last physical file stored externally.
5. Users can query views and basic tables with SQL statements. From the user's point of view, the view is the same as the basic table. There is no difference. They are all relationships (tables).
6.SQL users can be applications or end users. SQL statements can be embedded in programs of host languages, such as FORTRAN, COBOL, PASCAL, PL/I, C and Ada. SQL user can also be used as an independent user interface for end users in interactive environment.

SQL includes all operations on the database and is mainly composed of six parts:

1. Data definition: also known as "DDL language", it defines the logical structure of the database, including the definition of database, basic table, view and index.
2. Data manipulation: also known as "DML language", including three operations: insert, delete and update.
3. Data query: also known as "DQL language", including data query operations.
4. Data control: also known as "DCL language", the control of user access to data includes authorization and recycling of basic tables and views.
5. Transaction control: also known as "TCL language", including transaction commit and rollback.
6. Regulations on the use of embedded SQL language: specify the rules for the use of SQL statements in the host language program.
Next, I will focus on the first four parts.

First of all, I'll post the new databases and basic tables mentioned above below to facilitate the explanation later:

create database student #New database student
go  #Go means that the system will automatically run the code before go. In SQL, the system will not automatically run sentence by sentence after clicking run like python. It must be run manually
use student #Call database student
go

create table student #New basic table student
(
	sno char(10) primary key,
	sname char(10) not null,
	ssex char(2) check(ssex in ('male','female')),
	sage int check(sage between 0 and 100),
	sdept nvarchar(10)
)

create table course #New basic table course
(
	cno char(2) primary key,
	cname char(10) not null,
	cpno char(2),
	ccredit int check(ccredit>0),
	teacher varchar(10) 
)

create table sc #Create a new basic table sc, and the sc table part refers to the table student and the table course
(
	sno char(10),
	cno char(2),
	grade int check(grade between 0 and 100),
	primary key(sno,cno),
	constraint fk_sc_sno foreign key(sno) references student(sno),
	constraint fk_sc_cno foreign key(cno) references course(cno)
)

Data definition language (DDL)

It includes three contents:

create (New)
for example
Create a new database student:

create database student

Create a new table course:

create table course

drop (delete)
for example
Delete database student:

drop database student

Delete table course:

drop table course

alter (modify)
for example
Modify the basic table course:

alter table course

Modify column cname:

alter column cname

Data Manipulation Language (DML)

It also includes three parts:

Insert (insert data)
for example
Insert the data ('2020103025 ',' Xiaoming ',' male ', 19,' is') into the table student:
insert into student (all inserts insert data into the table, so here is to insert data into the table student)

insert into student
value '2020103025','Xiao Ming','male',19,'is'

Update (update data)
for example
Change the age of students with student number '2020103022' to 20:
(age is int type, so do not use quotation marks)

update student
set sage = 20
where sno = '2020103022'

Delete (delete data)
for example
Delete the information of the student with student number '2020204011':

delete
from student
where sno = '2020204011'

Data Query Language (DQL)

This part is the most interesting and difficult part of the whole learning process of basic principles and applications of database
First, an appetizer:

Query the course number of the course taught by "Mr. Wang":

select cno 
from course
where teacher like 'king%'

Here, I finally used the previously mentioned method of "all changes are inseparable from their religion", that is:
1. What to achieve
2. How
3. What are the conditions
In DQL:
1. Check what
2. Where to check
3. What are the conditions

Next, a little more difficult:

To inquire about the course number of Zhang Xiaoming's course:

To solve this problem, we must first know in which table the things to be checked are in, and then in which table the conditions are in.
Of course, the course number is in the course table, provided that the student's name is Zhang Xiaoming and in the student table.
Therefore, we can either join with tables or use nested queries:
1) Table connection:

select cno
from student,course,sc
where student.sno = sc.sno and course.cno = sc.cno and 
sname = 'Xiao Ming Zhang' #Because the tables student and course have no common attributes, you need to reference the sc table to concatenate the two tables

2) Nesting:

select cno
from course
where cno in 
			(
			select cno #What are the outer conditions and what are the inner conditions
			from sc
			where sno in
						(
						select sno
						from student
						where sname = 'Xiao Ming Zhang'
						)
			)

Some students will certainly say:

Next, let's do something difficult (which bothered me for a long time):

Query the student numbers and names of the three students with the best scores in the database class (the audience can think about how to write it first):

The code is as follows:

select sc.sno,sname
from sc,student,course
where sno in
            (
            select sno
            from sc
            where grade in
                         (
                         select top 3 grade  #The top function is used to pick out the maximum value of all elements in the target column
                         from sc
                         where cname in
                                      (
                                      select cname
                                      from course
                                      where cname = 'database'
                                      )
                          )
             )

Here I use a function and nesting. You can try to think about how to write if you don't use nesting and top functions. Of course, I don't dare to think about it. After all, I'm a vegetable chicken
Next is the last one:
Count the number of students in each course (more than 10 talents). It is required to output the course number and the number of students. The query results are arranged in descending order by the number of people. If the number of people is the same, they are arranged in ascending order by the course number:
(please also think about how to realize it first)
Code implementation:

select cno,COUNT(sno)
from sc
group by cno
having count(sno) > 10
order by COUNT(sno) desc,cno asc #Here, it means to arrange in descending order according to sno first. If tuples with the same comparison column are encountered, they will be arranged in ascending order according to cno

Personally, I think the implementation of data query in the actual production environment is really not simple, because it requires you to have a deep understanding of SQL and clear logic. But data query is really interesting.

Data control language (DCL)

grant (grant user rights)
for example
Grant the permission to query the course table and modify the course number to the user u1:

grant select，update(cno)
on course
to u1

Grant the update permission to the sc table to the user u3 and allow the user to grant the permission to other users:

grant update
on sc
to u3
with grant option #Allow authorized users to re authorize

Revoke (revoke user permissions)
for example
Withdraw the permission to insert the user u7 into the student ID:

revoke insert(sno)
on student
from u7

Withdraw the user u9's permission to modify the score of sc table:

revoke update(grade)
on sc
from u9

The above is all my content. Although I feel very confused, it is also a review of my knowledge. I hope readers can gain something after reading this article.