MySQL: Grammar Quick Reference [Continuously updated…]

1. Define basic tables

1. Commonly used integrity constraints

• Primary code constraints

primary key

• foreign key constraints

foreign key

• uniqueness constraint

unique

• non-nullity constraint

not null

• value constraints

check

2. Example questions

[Example 1] Create a “student” table Student, which consists of five attributes: student number Sno, name Sname, gender Ssex, age Sage, and department Sdept. The student ID cannot be empty, the value is unique, and the name value is also unique.

create table Student(
Sno char(5) not null unique,
    Sname char(20) unique,
    Sex char(1),
    Sage number,
    Step char(15)
);

[Example 2] Create a “student course selection” table SC, which consists of student number Sno, course number Cno, and course grade Grade, in which (Sno, Cno) is the main code

create table SC(
Sno char(5),
    Cno char(3),
    Grade number,
    Primary key(Sno, Cno)
);

2. Modify the basic table

1. Grammar format

ALTER TABLE <table name>
[ ADD <new column name> <data type> [integrity constraint] ]
[ DROP <integrity constraint name> ]
[ MODIFY <column name> <data type> ];

• ADD clause: Add new columns and new integrity constraints
• DROP clause: removes the specified integrity constraint
• MODIFY clause: used to modify column names and data types

2. Example questions

[Example 1] Add the “Admission Time” column to the Student table, and its data type is date.

alter table Student add Score date;

[Example 2] Change the data type of age to character type

alter table Student modify Sage char;

[Example 3] Delete the constraint that student names must have unique values

alter table Student drop unique(Sname);

3. Delete basic table

1. Grammar format

drop table <table name>

2. Example questions

[Example 1] Delete the Student table

drop table Student;

4. Creating and deleting indexes

1. Grammar format

-- Create index
CREATE [UNIQUE] INDEX <index name> ON <table name>(<column name>[<order>][,<column name>[<order>] ]…);
-- Delete index
DROP INDEX <index name>;

2. Example questions

[Example 1] Create indexes for the three tables Student, Course, and SC in the student-course database.

• Create a unique index on the Student table in ascending order by student number

• Create a unique index in the Course table in ascending order by course number

• The SC table creates a unique index in ascending order of student number and descending order of course number.

create unique index Stusno on Student(Sno);
create unique index Coucno on Course(Cno);
create unique index SCno on SC(Sno ASC, Cno DESC);

[Example 2] Delete the Stusname index of the Student table

drop index Stusname;

5. Query

SELECT [ALL|DISTINCT] <target column expression> [<alias>]
                 [,<target column expression>[<alias>]]…
FROM <table name or view name>[<alias>]
                [, <table name or view name>[<alias>]] …
[ WHERE <conditional expression> ]
[ GROUP BY <column name> [, <column name>] …
                  [ HAVING <conditional expression> ] ]
[ ORDER BY <column name> [, <column name>] … [ ASC|DESC ] ];

① from clause – specify the query object (basic table or view)

② where clause: specify query conditions

③ group by clause: Group the query results according to the value of the specified column. Tuples with equal values in the attribute column are a group.

④ having short sentence: filter out the groups that meet the specified conditions

⑤ order by clause: Sort the query result table in ascending or descending order of the specified column value

The following is a sample database

Students - Course Database
Student table:
        Student(Sno,Sname,Ssex,Sage,Sdept)
Class Schedule:
        Course(Cno,Cname,Cpno,Ccredit)
Student course selection schedule:
         SC (Sno, Cno, Grade)

1. Single table query

① Select several columns in the table

“Query specified column”

[Example 1] Query the student number and name of all students

select Sno, Sname from Student;

[Example 2] Query the names, student numbers, and departments of all students

select Sname, Sno, Sdept from Student;

“Query all columns”

[Example] Query the detailed records of all students

select Sno, Sname, Ssex, Sage, Sdept from Student;
select * from Student;

“Querying Calculated Values”

[Example] Check the names and birth years of all students (list aliases)

select Sname, Sage as 2021-Sage from Student;

② Select several tuples in the table

“Eliminate rows with duplicate values”

Use the DISTINCT phrase in the SELECT clause

Data are as follows:

Sno Cno Grade
----- --- ----
19001 1 92
19001 2 85
19001 3 88
19002 2 90
19002 3 80

• Query the student ID number of students who have taken courses

-- before optimization
select Sno from SC;
selct all Sno from SC;

 Result: Sno
19001
    19001
    19001
    19002
    19002
-----------------------
-- Optimized
select distinct Sno from SC;
 Result: Sno
19001
    19002

“Query the tuples that meet the conditions”

1. Comparison of size

=, >, <, >=, <=, != or <>, >, !<

[Example 1] Query the list of all students in the Department of Computer Science

select Sname from Student where Sdep = 'CS';

[Example 2] Query the names and ages of all students under 20 years old

select Sname, Sage from Student where age < 20;
select Sname, Sage from Student where not age >= 20;

[Example 3] Query the student numbers of students who have failed exams

select distinct Sno from SC where Grade < 60;

2. Scope

between ... and ...
not between ... and ...

[Example 1] Query the name, department and age of students aged between 20 and 23 years old (including 20 and 23 years old)

select Sname, Sdept, Sage from Student where Sage between 20 and 23;

[Example 2] Query the name, department and age of students who are not between 20 and 23 years old

select Sname, Sdept, Sage from Student where not Sage between 20 and 23;

3. Determine the set

IN <value table>
NOT IN <value list>

[Example] Query the names and genders of students in the Department of Information (IS), Department of Mathematics (MA), and Department of Computer Science (CS)

select Sname, Ssex from Student where Sdept in ('IS', 'MA', 'CS');

4. string matching

LIKE or NOT LIKE
[NOT] LIKE <match string>’ [ESCAPE <escape character>’]

• Fixed template string

[Example] Query the details of the student whose student ID is 95001

select * from Student where Sno like '19001';
select * from Student where Sno = '19001';

• wildcard
  • % – represents any length
  • _ – represents a single character

[Example 1] Query the name, student number and gender of all students with the surname Liu

select Sname, Sno, Ssex from Student Sname like 'Liu%';

[Example 2] Query the names of students whose surname is “Ouyang” and whose full name is three Chinese characters

select Sname from Student where Sname like 'Ouyang_';

[Example 3] Query the name and student number of students whose second character is “Yang”

select Sname, Sno from Student where like '_阳%';

[Example 4] Query the names of all students whose surname is not Liu

select Sname from Student Sname not like 'Liu%';

• Escape character (escape phrase)

[Example] Query the course number and credits of the DB_Design course

select Cno, Ccredit from Course where Cname like 'DB\_Design' escape '\';

5. Queries involving null values

is null or is not null

[Example] Some students did not take the exam after taking elective courses, so they have course selection records but no exam results. Query the student number and corresponding course number of students who lack grades

select Sno, Cno from SC where Grade is null;

[Example] Check the student ID and course ID of all students with grades

select Sno, Cno from SC where Grade is not null; -- if there is a grade, it is not null

6. Multiple condition query

• Use logical operators AND and OR to connect multiple query conditions
  • AND has higher priority than OR
  • Priority can be changed using parentheses
• Can be used to implement a variety of other predicates
  • [NOT] IN [NOT]
  • BETWEEN … AND …

[Example 1] Query the names of students under the age of 20 in the Department of Computer Science

select Sname from Student where Sdept = 'CS' and Sage < 20;

[Example 2] Query the names and genders of students in the Department of Information (IS), Department of Mathematics (MA), and Department of Computer Science (CS)

select Sname, Ssex from Student where Sdept in ('IS', 'MA',' CS');

[Example 3] Query the name, department and age of students aged between 20 and 23 years old (including 20 and 23 years old)

select Sname, Sdept, Sage from Student where Sage between 20 and 23;

③ Sort query results

Use the order by clause

1. Ascending: ASC
2. Descending order: DESC

[Example 1] Query the student numbers and grades of students who took course No. 3. The query results are sorted in descending order by scores.

select Sno, Grade from SC where Cno = '3' order by Grade desc;

result:

Sno Grade
------- -------
19010
19024
19007 92
19003 82
19010 82
19009 75
19014 61
19002 55

[Example 2] Query the status of all students. The query results are arranged in ascending order by department number, and students in the same department are arranged in descending order by age.

select * from Student where Sdept, Sage desc;

④ Use aggregate functions

• count

COUNT([DISTINCT|ALL] *)
COUNT([DISTINCT|ALL] <column name>)

• Calculate the sum

SUM([DISTINCT|ALL] <column name>)

• Calculate average

AVG([DISTINCT|ALL] <column name>)

• Find the maximum value

MAX([DISTINCT|ALL] <column name>)

• Find the minimum value

MIN([DISTINCT|ALL] <column name>)

[Example 1] Query the total number of students

select count(*) from Student;

[Example 2] Query the number of students who have taken a course

select count(distinct Sno) from SC;
-- Note: Use DISTINCT to avoid double counting of students.

[Example 3] Calculate the average scores of students in course No. 1

select AVG(Grade) from SC where Cno = '1';

[Example 4] Query the highest scores of students taking course No. 1

select max(Grade) from SC where Cno = '1';

⑤ Group query results

The query results are not grouped, and the aggregate function will be applied to the entire query results

After grouping the query results, the aggregate function will act on each group separately

====> Group using GROUP BY clause

[Example 1] Find each course number and the corresponding number of course enrollees

select Cno, count(Cno) from Sc Group by Cno;
-----------------------------------------------
result:
Cno COUNT(Sno)
 1 22
 2 34
 3 44
 4 33
 5 48

[Example 2] Find the number of students with each course number and the corresponding course score above 90 points

select Cno, Count(Sno) from Sc where Grade > 90 Group by Cno;
----------------------------------------
result
Cno COUNT(Sno)
 1 13
 2 7
 4 3
 5 8

====> Use the HAVING phrase to filter the final output

[Example 1] Query the student ID number of students who have taken more than 3 courses

select Sno from SC Group by Sno Having Count(*) > 3;

[Example 2] Query the student number of students who have more than 3 courses with scores above 90 and the number of courses with scores above 90

select Sno, Count(*) from SC where Grade > 90 Group by Sno Having Count(*) > 3;

What is the difference between HAVING phrase and WHERE clause?

• The WHERE clause acts on the base table or view to select tuples that meet the conditions
• HAVING phrase acts on groups, selecting groups that meet the conditions

2. Connection query

A query involving multiple tables at the same time is called a join query

1. Generalized Cartesian product

select Student.*, Sc.* from Student, SC;
-- .* represents all fields in this table

2. Equivalent and non-equivalent join queries

Join operation with = join operator

 [<table name1>.]<column name1> = [<table name2>.]<column name2>
 --When any clause refers to attributes with the same name in Table 1 and Table 2, the table name must be prefixed. When referencing unique attribute names, you can add or omit the table name prefix

select Student.*, Sc.* from Student, SC where Student.Sno == SC.Sno;

3. Self-connection query

A table is joined to itself, which is called a table’s self-join

1. Tables need to be aliased to distinguish them
2. Since all property names have the same name, an alias prefix must be used

[Example] Query the indirect prerequisite courses for each course (i.e. the prerequisite courses for prerequisite courses)

select a.Cno, b.Cno from Course a, Course b where a.Cpon = b.Cno;

4. External connection query

The difference between outer join and ordinary join

• Ordinary join operations only output tuples that meet the join conditions
• The outer join operation takes the specified table as the connection subject and outputs the tuples in the subject table that do not meet the connection conditions together.

[Example] Taking students as the subject, query the status of each student and their elective courses (using external connections)

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student，SC
     WHERE Student.Sno = SC.Sno( + );

5. Compound conditional connection query

When the WHERE clause contains multiple connection conditions, it is called a compound conditional connection.

Assume that students of the same gender in the school do not have the same name. Now design the student table and course selection table as follows:

Std(Sname, Ssex, Sage, Sdept)
StdC(Sname, Ssex, Cno, Grade)

[Example 1] Query the name, gender and department of all students who took course No. 2 and scored above 90 points

select Sname, Ssex, Sdept from Std, StdC
where Std.Sname = StdC.Sname and Std.Ssex = StdC.Ssex
and StdC.Cno = '2'
and StdC.Grade > 90;

[Example 2] Query each student’s student number, name, elective course name and grades (Student, SC, Course)

select Student.Sno, Student.Sname, Course.Cname, SC.Grade
from Student, SC, Course
where Student.Sno = SC.Sno
    and SC.Cno = Course.Cno;

3. Nested query

① Nested query overview

[A query block]: a SELECT-FROM-WHERE statement

[Nested query]: A query that nests a query block in the WHERE clause or HAVING phrase of another query block.

-- Query the names of all students who have taken course No. 2
-- Outer query/parent query
select Sname from Student where Sno in (
    -- Inner query/subquery
select Sno from SC where Cno = '02'
);

• Subquery restrictions

  • Cannot use order by clause

• The layer-by-layer nesting method reflects the structure of the SQL language

• Some nested queries can be replaced by join operations

select Sname from Student, SC where Student.Sno = SC.Sno and Cno = '02';

② Nested query classification

1. Uncorrelated subquery

• The query conditions of the subquery do not depend on the parent query

2. Correlated subquery

• The query conditions of the subquery depend on the parent query

③ Nested query solution method

1. Uncorrelated subquery

[Example] Search for students studying in the same department as “Liu Chen”

-- Determine the name of the department where "Liu Chen" is located
select Sdept from Student where Sname = 'Liu Chen';
-- Find all students studying in the IS department
select Sno, Sname, sdept from Student where Sdept = 'IS';
-- merge query
select Sno, Sname, Sdept from Student where Sdept in (
    select Sdept from Student where Sname = 'Liu Chen';
)

2. Related subqueries

[Example] Query the student numbers and names of all students who have taken course No. 1. Use nested queries

select Sno, Sname from Student where exists (
select * from SC where Sno = Student.Sno and Cno = '1'
);
-- Note: The EXISTS keyword is used in SQL to check whether a subquery will return at least one row of data. It is usually used with a correlated subquery.

④ Lead to the predicate of the subquery

1. Subquery with IN predicate

[Example] Query the student ID and name of the students who took the course named “Information System”

-- Finally, get the name of the relevant student based on the student's student number in the Student table
select Sno, Sname from Student where Sno in (
    --Then based on the found course number, the student number of the student who took course No. 3 is selected in SC.
select Sno from SC where Cno in (
        -- First find the course number of the course named "Information System" in the Course table
        select Cno from Course where Cname = 'Information System'
    )
);

• Join query

select Sno, Sname from Student, SC, Course
where Student.Sno = SC.Sno and SC.cno = Course.cno
and Course.Cname = 'Information System'

2. Subquery with comparison operator

[Example] Search for students studying in the same department as “Liu Chen”

select Sno, Sname, Sdept from Student where Sdept = (
select Sdept from Student where Sname = 'Liu Chen'
);

3. Subquery with ANY or ALL predicate

• ANY: any value

• ALL: all values

Requires the use of comparison operators:
    > ANY is greater than a value in the subquery result
     > ALL is greater than all values in the subquery results
    < ANY is less than a value in the subquery result
    < ALL is less than all values in the subquery results
    >= ANY is greater than or equal to a value in the subquery result
    >= ALL is greater than or equal to all values in the subquery results
    <= ANY is less than or equal to a value in the subquery result
    <= ALL is less than or equal to all values in the subquery results
    = ANY is equal to a value in the subquery result
    =ALL equals all values in the subquery result (usually meaningless)
    != (or <>) ANY is not equal to a value in the subquery result
    != (or <>) ALL is not equal to any value in the subquery result

[Example] Query the names and ages of students in other departments who are younger than a certain student in the Department of Information

select Sname, Sage from Student where Sage < any(
select Sage from Student where Sdept = 'IS'
) and Sdept <> 'IS';

4. Subquery with EXISTS predicate

When exists is used as a where condition, the main query before where is first queried, and then the results of the main query are substituted into exists subqueries one by one for judgment. If it is true, the result of the current main query is output, otherwise it is not output.

• existential quantifier
• A subquery with an EXISTS predicate does not return any data, but only produces a logical truth value "true" or a logical false value "false".
  • If the inner query result is not empty, return true value
  • If the inner query result is empty, a false value is returned

Example 1: Query the names of all students who took course No. 1

-- Nested query
select sname from Student where exists (
select * from SC where sno = Student.sno and cno = '1';
);
-- Connection query
select sname from Student, SC where Student.sno = SC.sno and cno = '1';

Example 2: Query the names of students who have not taken course No. 1

select sname from Student where not exists (
select * from SC where sno = Student.sno and cno = '1';
);

Example 3: Query students studying in the same department as “Liu Chen”

-- Uncorrelated subquery
select Sno, Sname, Sdept from Student where Sdept in (
    select Sdept from Student where Sname = 'Liu Chen';
)
-- Subquery replacement with EXISTS predicate
select Sno, Sname, Sdept from Student S1 where exists (
select * from Student S2 where S2.Sdept = S1.Sdept and S2.Sname = 'Liu Chen'
);

Efficiency comparison

• Uncorrelated subqueries > Correlated subqueries > Join queries
• Since the correlated subquery with the EXISTS quantifier only cares about [whether the inner query has a return value] and does not need to check the specific value, its efficiency is not necessarily lower than other forms of queries.

Example 4: Query the names of students who have taken courses

-- Way1:
select Sname from Student where exists (
select * from SC where Sno = Student.Sno
);
-- Way2:
select Sname from Student, SC where Student.Sno = SC.Sno;
-- Way3:
select Sname from Student where sno in (
select distinct sno from sc
);

Example 5: Query the names of students who have taken all courses

-- Query that there are no students who do not choose all courses
select Sname from Student where not exists (
    select * from Course where not exists (
    select * from SC where Sno = Student.Sno and Cno = Course.cno
    )
);

Example 6: Query the student numbers who have taken at least all courses chosen by student 19002

-- There is no such course y. Student 19002 chose it, but student x did not.
select distinct Sno from SC SCX where not exists (
select * from SC SCY where SCY.Sno = '19002' and not exists (
    select * from SC SCZ where SCZ.Sno = SCX.Sno and SCZ.Cno = SCY.Cno
    )
);

4. Collection query

① Union

<query block>
UNION
<query block>
--The number of columns in each result table participating in the UNION operation must be the same; the data types of the corresponding items must also be the same

Example 1: Query students in the Department of Computer Science and students who are not older than 19 years old

-- way1: and
select * from Student where Sdept = 'CS' union
select * from Student where Sage <= 19;
-- way2: or
select distinct from Student where Sdept = 'CS' or Sage < 19;

Example 2: Query the names of all teachers and students in the school

select Sname from Student union
select Sname from Teacher;

② Intersect

Example 1: Query the intersection of the student collection of elective course 1 and the student collection of elective course 2

-- use insert
select Sno from SC where Cno = '1' intersect
select Sno from SC where Cno = '2';
-- Use in predicate
select Sno from SC where Cno = '1' and Sno in (
select Sno from SC where Cno = '2'
)

Example 2: Query the intersection of student name and teacher name

-- use insert
select distinct Sname from Student intersect
select distinct Sname from Teacher;
-- Use in predicate
select distinct from Student where Sname in (
select Sname from Teacher;
)

③ Difference operations (except, minus)

Example 1: Query the difference between a student’s name and a teacher’s name –> Query the names of students in the school who do not have the same name as the teacher

-- use except
select distinct Sname from Student except
select distinct Sname from Teacher;
-- Use in predicate
select distinct from Student where Sname not in (
select Sname from Teacher;
)

④ Sorting of set operation results

In any case, the ORDER BY clause can only appear at the end

5. Summary

SELECT [ALL|DISTINCT] <target column expression>
           [alias] [ , <target column expression> [alias]] …
FROM <table name or view name> [alias]
           [ ,<table name or view name> [alias]] …
[WHERE <conditional expression>]
[GROUP BY <column name 1>[, <column name 1’>] ...
[HAVING <conditional expression>]]
[ORDER BY <column name 2> [ASC|DESC]
           [, <Column name 2’> [ASC|DESC] ] … ];