MySQL constraints & special queries

MySQL Constraints & Special Queries

Article directory

• MySQL Constraints & Special Queries
• • 1. Database constraints
  • • 1.1 Constraint types
    • 1.2 NULL constraints
    • 1.3 NUIQUE: unique constraint
    • 1.4 DEFAULT: Default value constraints
    • 1.5 PRIMARY KEY: Primary key constraints
    • 1.6 FOREIGN KEY: foreign key constraints
    • 1.7 CHECK constraints
  • 2. Relationship between tables
  • • 2.1 One-on-one
    • 2.2 One-to-many
    • 2.3 Many-to-many
  • 3. Special additions
  • 4. Query
  • • 4.1 Aggregation query
    • • 4.1.1 Aggregation functions
      • 4.1.2 GROUP BY clause
      • 4.1.3 HAVING
    • 4.2 Joint query
    • • 4.2.1 Initialization
      • 4.2.2 Operation steps
      • 4.2.3 Inner join
      • 4.2.4 Outer joins
      • 4.2.5 Self-connection
      • 4.2.6 Combined query

1. Database constraints

1.1 Constraint Type

NOT NULL: Indicates that a column cannot store NULL values

UNIQUE: Ensure that each row of a column must have a unique value

DEFAULT: Specifies the default value when no value is assigned to the column

PRIMARY KEY: Ensure that a certain column (or a combination of two columns and multiple columns) has a unique identifier, which helps to find a specific record in the table more easily and quickly

FOREIGN KEY: Ensures referential integrity that data in one table matches values in another table

CHECK: Ensures that the values in the column meet the specified conditions. For MySQL databases, the CHECK clause is analyzed, but the CHECK clause is ignored

1.2 NULL constraint

When creating a table, you can specify that a column is not empty:

--Set the student table structure
CREATE TABLE student_5 (
id INT NOT NULL,
snINT,
name VARCHAR(20),
qq_mail VARCHAR(20)
);

1.3 NUIQUE: unique constraint

Specify that the values in the sn column are unique and non-duplicate:

-- Reset the student table structure
DROP TABLE IF EXISTS student_5;
CREATE TABLE student (
id INT NOT NULL,
snINT UNIQUE,
name VARCHAR(20),
qq_mail VARCHAR(20)
);

1.4 DEFAULT: Default value constraints

When inserting data, the name column is empty and the default value is unknown.

-- Reset the student table structure
DROP TABLE IF EXISTS student_5;
CREATE TABLE student (
id INT NOT NULL,
snINT UNIQUE,
name VARCHAR(20) DEFAULT 'Anonymous',
qq_mail VARCHAR(20)
);

1.5 PRIMARY KEY: primary key constraints

Specify the id column as the primary key:

-- Reset the student table structure
DROP TABLE IF EXISTS student_5;
CREATE TABLE student (
id INT NOT NULL PRIMARY KEY,
snINT UNIQUE,
name VARCHAR(20) DEFAULT 'Anonymous',
qq_mail VARCHAR(20)
);

primary key is generally used for main fields, such as id serial number

For primary keys of integer type, they are often used with auto_increment. When inserting data and no value is given for the field, use the maximum value + 1

--The primary key is a combination of NOT NULL and UNIQUE, NOT NULL is not required
id INT PRIMARY KEY auto_increment

1.6 FOREIGN KEY: foreign key constraints

Foreign keys are used to relate other tables‘s primary key or unique value

foreign key (field name) references main table (column)

Case:

• Create the class table classes, with id as the primary key

  -- When creating a class table and using MySQL keywords as fields, you need to use `` to identify them.
  CREATE TABLE classes_5 (
  id INT PRIMARY KEY auto_increment,
  name VARCHAR(20),
  `desc` VARCHAR(100)
  );
  

  

• Create a student table student, one student corresponds to one class, and one class corresponds to multiple students. Use id as the primary key, class_id as the foreign key, and associate the class table id

  DROP TABLE IF EXISTS student_5;
  CREATE TABLE student (
  id INT NOT NULL PRIMARY KEY auto_increment,
  snINT UNIQUE,
  name VARCHAR(20) DEFAULT 'Anonymous',
  qq_mail VARCHAR(20),
      classes_id int,
     FOREIGN KEY (classes_id) REFERENCES classes_5(id)
  );
  

  

1.7 CHECK constraints

MySQL does not report an error when used, but ignores the constraint (the usage rate of this constraint is not high)

create table test_user (
id int,
namevarchar(20),
sex varchar(1),
check (sex = 'male' or sex = 'female')
);

2. Table relationships

2.1 One-on-one

-- One person can only correspond to one ID card, that is, the IDs in the two tables must be the same.
create table person (
    person_id int primary key, name varchar(20)
);
create table id_card (
    person_id int primary key,
    namevarchar(20),
    birthday datetime,
    address varchar(30),
    foreign key (person_id) references person(person_id)
);

2.2 One-to-many

-- A class can have multiple students (that is, it can have different student IDs), so a class_id must be set for the student to match the ID in the class
 create table classes (id int primary key, class_name varchar(20));
 create table student (
     student_id int primary key,
     namevarchar(20),
     class_id int,
     foreign key (class_id) references classes(id)
);

2.3 Many-to-many

-- A course intermediate table (examination score table) must correspond to both students and courses.
create table course (course_id int primary key, course_name varchar(20));
create table score (
    id int primary key,
    score int,
    student_id int,
    course_id int,
    foreign key (student_id) references student(student_id),
    foreign key (course_id) references course(course_id)
);

3. Special addition

We can insert the query results into a new table

grammar:

INSERT INTO table_name [(column [, column ...])] SELECT ...

Case: Create a student user table and design it with ID, student number, name, and age fields. You need to copy the existing student data in. The newly created fields that have not been copied and assigned are NULL.

-- Insert data into the student table
-- Because the ID of the student table has been linked to the ID of the class table, you need to assign a value to the class table before inserting data into the student table.
-- (And the class ID inserted into the student table must be corresponding to the class table)
insert into classes values (707, 'Class 7 of Accounting Science'), (808, 'Class 8 of Software Engineering');
insert into student values (101, 'Zhang San', 707), (201, 'Reese', 808);

--Copy the assignable data in the student table to the student user table
insert into test_user (id, name) select student_id, name from student;

4. Query

4.1 Aggregation query

4.1.1 Aggregation function

Common operations such as counting totals and calculating averages can be implemented using aggregate functions. Common aggregate functions include:

Function	Description
COUNT([DISTINCT] expr)	Returns the number of queried data
SUM([DISTINCT] expr)	Returns the sum of queried data, not Numbers are meaningless
AVG([DISTINCT] expr)	Returns the average value of the queried data. If it is not a number, it is meaningless
MAX([DISTINCT] expr)	Returns the maximum value of the queried data, which is meaningless if it is not a number
MIN ([DISTINCT] expr)	Returns the minimum value of the queried data. It is meaningless if it is not a number

• COUNT

  -- Count how many students there are in the class
  SELECT COUNT(*) FROM student;
  
  -- Count the total number of student IDs in the class
  SELECT COUNT(class_id) FROM student;
  

  

  Note: When the query result contains null values, non-count(*) other count() queries will not include records with null values in the data

  insert into student(student_id, name) values (125, 'Xiao Li');
  select count(class_id) from student;
  

  

• SUM

  -- Total score of statistical mathematics scores
  select sum(math) from exam_result;
  
  -- Count the total math scores of all those who failed math (<60), if not, return NULL
  select sum(math) from exam_result where math < 60;
  

  

• AVG

  -- Statistical average total score
  select avg(chinese + math + english) from exam_result;
  

  

• MAX

  -- Return the highest score in English
  select max(english) from exam_result;
  

  

• MIN

  -- Return the lowest score in English
  select min(english) from exam_result;
  
  -- Return > Minimum English score of 60 points
  select min(english) from exam_result where english > 60;
  

  

4.1.2 GROUP BY clause

Use the GROUP BY clause in SELECT to group the specified column.

Conditions: When using GROUP BY for grouping query, the field specified by SELECT must be the “grouping field”. If other fields want to appear in SELECT, they must be included in the aggregate function.

Case:

• Prepare test tables and data: employee table, with id (primary key), name (name), role (role), salary (salary)

  create table emp (
  id int primary key auto_increment,
  name varchar(20) not null,
  role varchar(20) not null,
  salary numeric(11,2)
  );
  

  

• Query the maximum salary, minimum salary and average salary for each role

  select role, max(salary), min(salary), avg(salary) from emp group by role;
  

  

4.1.3 HAVING

After grouping using the GROUP BY clause, when it is necessary to conditionally filter the grouped results. The WHERE statement cannot be used, but HAVING needs to be used

• Show roles with average salary below 1500 and their average salary

  select role, avg(salary) from emp group by role having avg(salary) < 1500;
  

  

4.2 Joint query

4.2.1 Initialization

In actual development, data often comes from different tables, so joint queries of multiple tables are required. Multi-table query takes Cartesian product of data from multiple tables:

Here we first initialize the test data:

drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20), classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) value
('Grade 22 Accounting Class 7', 'Learned computer principles, Python, C, Java, data structures and algorithms'),
('19th grade Chinese language class 3', 'studied traditional Chinese literature'),
('Level 23 Automation Class 2', 'Learned mechanical automation and microcontroller');

insert into student(sn, name, qq_mail, classes_id) values
('09982','Black Whirlwind Li Kui','[email protected]',1),
('00835','Bodhi Patriarch',null,1),
('00391','Bai Suzhen',null,1),
('00031','Xuxian','[email protected]',1),
('00054','Don't want to graduate',null,1),
('51234','Speak well','[email protected]',2),
('83223','tellme',null,2),
('09527','Foreigners learn Chinese','[email protected]',2);

insert into course(name) values
('Java'),('Traditional Chinese Culture'),('Computer Principles'),('Chinese'),('Advanced Mathematics'),('English\ ');

insert into score(score, student_id, course_id) values
-- Black Tornado Li Kui
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- Ancestor Bodhi
(60, 2, 1),(59.5, 2, 5),
--Bai Suzhen
(33, 3, 1),(68, 3, 3),(99, 3, 5),
--Xu Xian
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- Don't want to graduate
(81, 5, 1),(37, 5, 5),
-- speak nicely
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

4.2.2 Operation steps

When performing multi-table queries, it can generally be divided into the following four steps:

1. First perform theCartesian product
2. Then specify Connection conditions
3. Secondly, you can specify other conditions
4. Finally, simplify the columns

Case: Query the grades of classmate “Xu Xian”

• Cartesian Product

  -- Specify two related tables for Cartesian product
  select * from student, score;
  

  

• Specify connection conditions

  --Specify the connection conditions to streamline the results (you can call the field by clicking [Table name.])
  select * from student, score where student.id = score.student_id;
  

  

• Then add other conditions as needed

  --Find Xu Xian’s results
  select * from student, score where student.id = score.student_id and student.name = 'Xu Xian';
  

  

• Simplify the last part of the above

  select student.name, score.score from student, score where student.id = score.student_id and student.name = 'Xu Xian';
  

  

Note: The entire process can also be summarized as a SQL statement to call

select student.name, score.score from student, score where student.id = score.student_id and student.name = 'Xu Xian';

4.2.3 Inner join

The steps are the same as above, except the syntax is different.

grammar:

select field from table 1 alias join table 2 alias on join condition and other conditions;

--You can also do this
select field from table 1 alias 1, table 2 alias 2 where connection conditions and other conditions;

Note: Related queries can use aliases for related tables

Case:

• Check the grades of classmate “Xu Xian”

  select stu.name, sco.score from student as stu join score as sco on stu.id = sco.student_id and stu.name = 'Xu Xian';
  
  select stu.name, sco.score from student as stu, score as sco where stu.id = sco.student_id and stu.name = 'Xu Xian';
  

  

• Query the total scores of all students and their personal information

  -- The score table has a many-to-one relationship with the student table. The total score query is grouped according to the student ID in the score table.
  select stu.sn, stu.name, stu.qq_mail, sum(sco.score) from student as stu join score as sco on stu.id = sco.student_id group by sco.student_id;
  

  

• Query the grades of all students and their personal information

  Here you need to perform a Cartesian product of three tables. First find the relationship between the tables, perform a Cartesian product on the two related tables, and then calculate the Cartesian product between the result and another table< /mark>

  -- calculate the Cartesian product of student and score
  select stu.id, stu.sn, stu.name, stu.qq_mail, sco.score, sco.course_id, cou.name from student as stu join score as sco on stu.id = sco.student_id course join as cou on sco .course_id = cou.id order by stu.id;
  

  

4.2.4 Outer connections

Outer joins are divided into left outer joins and right outer joins. If a joint query is performed, if the table on the left is completely displayed, it is a left outer join; if the table on the right is completely displayed, it is a right outer join.

grammar:

--Left outer join, table 1 is fully displayed
select field name from table name 1 left join table full 2 on connection condition;

-- Right outer join, Table 2 is fully displayed
select field name from table name 1 right join table full 2 on connection condition;

Case: Query the grades of all students and their personal information. If the student has no grades, it also needs to be displayed.

-- "Foreigners learn Chinese" classmates have no test scores, and they are also displayed.
select * from student as stu left join score as sco on stu.id = sco.student_id;
--The corresponding right outer join
select * from student as stu right join score as sco on stu.id = sco.student_id;

4.2.5 Self-connection

Self-join refers to connecting itself on the same table for query

Case: Display all the score information of “Computer Principles” with higher scores than “Java”

-- First query the IDs of "Computer Principles" and "Java" courses
select id, name from course where name = 'Java' or name = 'Computer Principles';

-- Then query the score table to find out the information about "Computer Principles" that has better results than "Java"
select s1.* from score as s1, score as s2 where s1.student_id = s2.student_id and s1.score < s2.score and s1.course_id = 1 and s2.course_id = 3;

4.2.6 Merged query

In practical applications, in order to merge the execution results of multiple selects, you can use the set operators union, union all. When using union and union all, the fields in the result sets of the front and back queries need to be consistent (SQL on the left and right sides of union can query two different tables, and or No)

• union

  This operator is used to obtain the union of two result sets. When this operator is used, duplicate rows in the result set will be automatically removed.

  Case: Query the courses whose ID is less than 3, or whose name is “English”:

  select * from course where id < 3 union select * from course where name = 'English';
  

  

• union all

  This operator is used to obtain the union of two result sets. When this operator is used, duplicate rows in the result set will not be removed.

  Case: Query courses with ID less than 3 or with the name “Java”

  --Duplicate data appears in the result set of union all Java
  select * from course where id < 3 union all select * from course where name = 'Java';