1. Basic concepts of SQL

1.1 SQL concept and classification

• Definition: SQL is the abbreviation of Structured Query Language, which means Structured Query Language. It is a language for querying or changing the data in the database in the database management system

• Mainstream database management system

  • Relational database
    • MySQL
    • Oracle
    • Postgre SQL
    • SQL Server
  • Non relational database
    • redis
    • mongo DB

• Data definition language (DDL)

  • SQL data definition language is mainly used to define logical structures, including defining base tables, views and indexes, deleting tables, defining tables and modifying tables

• Data query language (DQL)

  • The data query language of SQL is mainly used to query various data objects in the database

• Data manipulation language (DML)

  • The data manipulation language of SQL is used to change the data in the database, including insertion, deletion and modification

• Data control language (DCL)

  • SQL data control language, authorization of tables and views, description of integrity rules, and control statements such as transaction start and end

1.2 features of SQL language

• Comprehensive and unified: independently complete all activities in the database life cycle, including defining relationship mode, entering data, establishing database, query, update, maintenance, database reconstruction and database security
• Highly non procedural: users only need to ask "what to do" without indicating "how to do it"
• Set oriented operation mode: SQL adopts set operation mode
• Two usage modes are provided with the same syntax structure: SQL is both a self-contained language and an embedded language, and SQL statements can be embedded into high-level language programs
• Simple language, easy to learn and use: SQL language has simple syntax and is close to oral English

1.3 basic SQL syntax

1 SQL data type

data type	meaning
CHAR(n) CHARACTER(n)	Fixed length string with length n
VARCHAR(n) CHARACTERVARYING(n)	Variable length string with maximum length n
CLOB	String large object
BLOB	Binary large object
INT INTEGER	Long integer (4 bytes)
SMALLINT	Short integer (2 bytes)
BIGINT	Large integer (8 bytes)

data type	meaning
NUMERIC(p, d)	Fixed point number, consisting of p digits (excluding symbols and decimal points), There are d digits after the decimal point
DECIMAL(p, d) DEC(p, d)	Same as numerical
REAL	Single precision floating point number depending on machine accuracy
DOUBLE PRECISION	Double precision floating point number depending on machine accuracy
FLOAT(n)	Floating point number with optional precision. The precision is at least n digits
BOOLEAN	Logical Boolean
DATE	Date, including year, month and day, in the format of YYYY-MM-DD
TIME	Time, including hour, minute and second of the day, in HH:MM:SS format
TIMESTAMP	Timestamp type
TIMERVAL	Interval type

2 Definition and deletion of mode

• Pattern definition

  • Syntax: create schema < schema name > authorization < user name >
  • Example: CREATE SCHEMA "S-T" AUTHORIZATION WANG;

• Mode definition + view

  • Syntax: create schema < schema name > authorization < user name > [< table definition clause > < view definition clause > < authorization definition clause >];

• Mode deletion

  • Syntax: drop schema < schema name > < cascade | restrict >;
  • Either CASCADE or RESTRICT must be selected
  • Vascade (cascading): deleting a schema also deletes all database objects assigned to the schema
  • Restrict: if there are subordinate objects (such as table view) in this mode, the execution of the deletion statement will be rejected

3 addition, deletion, modification and query of table

• Create table

CREATE TABLE Table name (Field name type field constraint, Field name type field constraint, Field name type field constraint);

CREATE TABLE Student (
    name VARCHAR(20),
    age INT,
    sex CHAR(1),
);

• Delete table

DROP TABLE <Table name> [RESTRICT|CASCADE];

DROP TABLE Student RESTRICT;

• modify

ALTER TABLE <Table name>
[ADD [COLUMN] <New column name><data type> [integrity constraint]]
[ADD <Table level integrity constraints>]

ALTER TABLE Student ADD S_entrance DATE;

ALTER TABLE Student ALTER COLUMN S_age INT;

4 addition, deletion, modification and query of index

• When there is a large amount of data in the database, establishing the index can reduce the query time, and the index can be established on one or more columns
• Indexing
  • CLUSTER: CLUSTER index. The physical order is the same as the logical order of the index
  • UNIQUE: UNIQUE index

CREATE [UNIQUE] [CLUSTER] INDEX <Index name> 
ON <Table name>(<Listing>[<order>][,<Listing> [<order>]] ...);

CREATE UNIQUE INDEX Stusno ON Student(Sno);

• Modify index

ALTER INDEX <Old index name> RENAME TO <New citation>;

ALTER INDEX SCno RENAME TO SCSno;

• Delete index

DROP INDEX <Index name>

DROP INDEX Stusno;

SQL query 2

Assume that the relationship tables Student, Course and SC are as follows:

• Student

Student number (Sno)	Name (Sname)	Gender (Ssex)	Age (Sage)	Department (Sdept)
201215121	Li Yong	male	20	CS
201215122	Liu Chen	female	19	CS
201215123	Wang Min	female	18	MA
201215125	tension	male	19	ES

• Course

Course number (Cno)	Course name (Cname)	Advance course (Cpno)	Credit (Ccredit)
1	database	5	4
2	mathematics	/	2
3	information system	1	4
4	operating system	6	3
5	data structure	7	4
6	data processing	/	2
7	Java language	6	4

• SC

Student number (Sno)	Course number (Cno)	Grade
201215121	1	92
201215121	2	85
201215121	3	88
201215122	2	90
201515122	3	80

2.1 query all columns

Query all columns in Student table

SELECT * FROM Student;

2.2 query some columns

Query the Sno and Ssex columns in the Student table

SELECT Sno, Ssex FROM Student;

2.3 column alias

Query the Sno and Ssex columns in the Student table, and name Sno as "Student number" and Ssex as "gender"

SELECT Sno Student number, Ssex Gender FREOM Student;

SELECT Sno AS Student number, Ssex AS Gender FROM Student;

2.4 table alias

Query the Cno column in the Course table and name the query result CC

SELECT Cno FROM Course AS CC;

2.5 de duplication of query results

Query the Sno column in SC and de duplicate the result

SELECT DISTINCT Sno AS Course name FROM SC;

2.6 adding conditions to query results

• The query criteria are as follows:

query criteria	predicate
compare	\(=,>,<,\le,\ge\) !=,<>,!>,!<,NOT
Determine the scope	\(BETWEEN AND\) \(NOT BETWEEN AND\)
Determine set	\(IN,NOT IN\)
Character matching	\(LIKE,NOT LIKE\)
Null value	\(IS NULL,IS NOT NULL\)
Logical operation	\(AND,OR,NOT\)

Query the information with Grade greater than 88 in the SC table

SELECT * FROM SC WHERE Grade >= 88;

Query the information of Grade between 85 and 95 in SC table

SELECT * FROM  SC WHERE Geade BETWEEN 85 AND 95;

Query whether 1 is included between (1,2)

SELECT 1 FROM (1, 2);

Query the Course number and Course name of the Course containing "system" in the Course table

"%" can approximate multiple characters, "" Can only approximate one character

SELECT Cno, Cname FROM Course WHERE Cname LIKE '%system';

Query the information that the advance Course is empty in Course

SELECT * FROM Course WHERE Cpno IS NULL;

Query the Course name of the Course with a score of 4 and a leading Course of 1 in Course middle school

SELECT Cname FROM Course WHERE Ccredit = 3 AND Cpno = 1;

Query the Course name of courses with 4 or 3 scores in Course middle school

SELECT Cname FROM Course WHERE Ccredit = 3 OR Ccredit = 4;

2.7 aggregation function

function	meaning
\(COUNT(*)\)	Count the number of tuples
\(count ([distinct all] < column name >) \)	Count the number of median values in a column
\(sum ([distinct all] < column name >) \)	Calculate the sum of a column of values
\(AVG ([distinct all] < column name >) \)	Calculate the average of a column of values
\(max ([distinct all] < column name >) \)	Calculate the maximum value in a column
\(min ([distinct all] < column name >) \)	Calculate the minimum value in a list of values

Note: DISTINCT is the value to remove duplicates

Query the number (de duplication), total, average and maximum of grades in CS table

SELECT COUNT(*) FROM SC;

SELECT COUNT(DISTINCT Grade) FROM SC;

SELECT SUM(Grade) FROM SC;

SELECT AVG(Grade) FROM SC;

SELECT MAX(Grade) FROM SC;

SELECT MIN(Grade) FROM SC;

2.8 group query

Group the table SC according to Sno, and then filter out a group in which Sno is "201215121"

SELECT Sno FROM SC GROUP BY Sno HAVING Sno = '201215121';

2.9 equivalent connection

Connect Student and SC with Sno

SELECT Student AS S, SC WHERE S.Sno = SC.Sno;

2.10 self connection

Connect the Student table to the self generated

SELECT Student AD S1, Student AS S2 WHERE S1.Sno = S2.Sno;

2.11 external connection

Connect Course and SC with Cno for external connection, left external connection and right external connection respectively

SELECT * FROM Course OUTER JOIN SC ON Course.Cno = SC.Cno; 

SELECT * FROM Course LEFT OUTER JOIN SC ON Course.Cno = SC.Cno; 

SELECT * FROM Course RIGHT OUTER JOIN SC ON Course.Cno = SC.Cno; 

2.12 multi table query

Connect Student with SC by Sno, and connect Course with SC by Cno

SELECT * 
FROM Student JOIN SC ON Student.Sno = SC.Sno 
JOIN Course ON SC.Cno = Course.Cno;

SELECT *
FROM Student, Course, SC 
WHERE Student.Sno = SC.Sno AND Course.Cno = SC.Cno;

2.13 nested query

Query the student number (Sno), name (Sname) and department (Sdept) of students with grade > 90

SELECT Sno, Sname, Sdept
FROM Student
WHERE Sno IN (
    SELECT Sno 
    FROM SC
    WHERE Grade > 90;
);

2.14 query with ANY

Query the names of all students with sage > 19 (Sname) (the result can meet any value in the subset)

SELECT Sname
FROM Student
WHERE Sage > ANY (
    SELECT Sage
    FROM Student
    WHERE Sage > 19;
);

2.15 query with ALL

Query the student number (Sno) of all students with sage > 19 (the result should meet all values in the subset)

SELECT Sno
FROM Student
WHERE Sage > ALL (
    SELECT Sage
    FROM Student
    WHERE Sage > 19;
);

2.16 query with EXISTS

Query the names of students who do not take elective courses (Sname)

SELECT Sname
FROM Student
WHERE NOT EXISTS(
    SELECT *
    FROM Course
    WHERE NOT EXISTS (
        SELECT *
        FROM SC
        WHERE Sno = Student.Sno AND Cno = Course.Cno);
    );
)

2.17 set query

1 Union

Query the union of students whose department (Sdept) is "CS" and students whose age (Sage) is greater than 19

SELECT * FROM Student WHERE Sdept = "CS"
UNION
SELECT * FROM Student WHERE Sage > 19;

2 intersection

Query the intersection of students whose department (Sdept) is "CS" and students whose age (Sage) is greater than 19

SELECT * FROM Student WHERE Sdept = "CS"
INTERSECT
SELECT * FROM Student WHERE Sage > 19;

3 difference set

Query the difference set between students whose department (Sdept) is "CS" and students whose age (Sage) is greater than 19

SELECT * FROM Student WHERE Sdept = "CS"
EXCEPT
SELECT * FROM Student WHERE Sage > 19;

3. Addition, deletion and modification of SQL

3.1 data insertion

1 standard addition

INSERT INTO Student(Sno, Sname, Ssex, Sage, Sdept)
VALUES ('201215128', 'Shi Hao', 'male', 25, 'IS');

2 specify some fields

INSERT INTO Student (Sname, Sage, Sdept) VALUE ('Ye Fan', 22, 'CS');

3 do not specify fields

-- When fields are not specified, they need to be added one by one, and items cannot be missing
INSERT INTO VALUE Student ('201215128', 'Shi Hao', 'male', 25, 'IS');

4 batch addition

INSERT INTO Student VALUES
('201215128', 'Shi Hao', 'male', 25, 'IS'),
('201215129', 'Ye Fan', 'male', 22, 'CS'),
('201215130', 'Cruel man', 'female', 20, 'MX');

3.2 modification

Change the name of the Student whose middle school number (Sno) is "201215122" to "Shi Hao" and the age (Sage) to 25

UPDATE Student SET Sname = "Shi Hao", Sage = 25 WHERE Sno = "201215122";

3.3 deletion

Delete the data with age greater than 18 in the Student table

DELETE FROM Student WHERE Sage > 18;

4 view

4.1 view definition

• VIEW, also known as virtual table, is the logical representation of a group of data. Its essence is to correspond to an SQL statement, and the result set is given a name
• The view itself does not contain any data. It only contains a query statement mapped to the base table. When the base table data changes, the view data also changes
• Objective: to facilitate and simplify data operation
• When our business needs to find the data of multiple tables, we may associate multiple tables for query processing. If the query SQL is complex, it also affects the query efficiency. At this time, we can create a view. During query, we only need to select * from view

4.2 creating views

-- Last WITH CHECK OPTION The functions are as follows:
-- When the subsequent operations add, delete or modify the view, the view may not meet the sub conditions S Constraints of
-- After adding this statement, the system will automatically add, delete and modify the view in this sub condition S Operate under
CREATE VIEW S_STU
AS <Subquery>
[WITH CHECK OPTION];

Create a Student named s in the Student table for students whose age (Sage) > 18_ The view of new, which contains the Student's Student number (Sno), name (Sname), gender (Ssex) and department (Sdept)

CREATE VIEW S_NEW
AS
SELECT Sno, Sname, Ssex, Sdept
FROM Student
WHERE Sage > 18
WITH CHECK OPTION;

4.3 delete view

-- CASCADE The functions of the are as follows:
-- When this statement is added, the view and all views under it are deleted
-- When this statement is not added, if there are other views under this view, the deletion will fail
DROP VIEW <View name> [CASCADE];

Delete view s created in 4.2_ NEW

DROP VIEW S_NEW CASCADE;

4.4 query view

Query 4.2 view s_ Student number (Sno) of students whose gender (Ssex) is "female" in New

SELECT Sno
FROM S_NEW 
WHERE Ssex = "female";

4.5 view update

• The view is virtual and does not exist, so the operation of the view will eventually be reflected on the basic table

View s of 4.2_ The gender (Ssex) of students whose department (Sdept) is "CS" in new is changed to "male"

-- update the view
UPDATE S_NEW 
SET Ssex = "male"
WHERE Sdept = "CS";

-- Update basic table
-- Because 4.2 The view creation condition is Sage > 18，Therefore, this condition should be added when updating the basic table
UPDATE Student
SET Ssex = "male"
WHERE Sdept = "CS" AND Sage > 18;