Basic database operation (Advanced)

1, Database Constraints

Constraint type:
NOT NULL: indicates that a column cannot store NULL values (required)
UNIQUE: ensure that each row of a column must have a UNIQUE value (it cannot be repeated with other records. If it is repeated, an error will be reported!)
DEFAULT: Specifies the DEFAULT value when no value is assigned to the column
PRIMARY KEY: the combination of NOT NULL and UNIQUE. Ensuring that a column (or a combination of two columns and multiple columns) has a UNIQUE identification helps to find a specific record in the table more easily and quickly. (UNIQUE identification of a record)
FOREIGN KEY: ensure the referential integrity of the data in one table matching the values in another table. (association between two tables)
CHECK: ensure that the values in the column meet the specified conditions. For MySQL data, the CHECK clause is analyzed, but the CHECK clause is ignored. (in MySQL 5, writing CHECK will not report an error, but it will not take effect)
Example: student table structure

--appoint id Is a self incrementing primary key, name When empty, the default value is null
create table if not exists student(
	id int primary key auto_increment,
	sn int unique,
	name varchar(20) default 'unknow',
	qq_mail varchar(20),
);

For integer type primary keys, auto growth is often used_ Increment. When the field corresponding to the inserted data does not give a value, the maximum value + 1 is used.
☆ foreign key: foreign key constraint
Foreign keys are used to associate primary keys or unique keys of other tables. The basic syntax is:
foreign key (column specified in the current table) references need to be associated with the table name (column);

--Class table
CREATE TABLE classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
`desc` VARCHAR(100)
);

--One student corresponds to one class, and one class corresponds to multiple students, 
--id Is the primary key, class_id Is a foreign key associated with the class table
--Reset student table structure
create table if not exists student(
	id int primary key auto_increment,
	sn int unique,
	name varchar(20) default 'unknow',
	qq_mail varchar(20),
	class_id int,
	foreign key (class_id) references classes(id)
);

2, Table design

Three designs

one-on-one:
eg: as in the above example, one student corresponds to one class
One to many:
eg: one class corresponds to multiple students
It is often necessary to add a field to the student table to represent the id of the class to which the student belongs.
classes(classId, className);
student(studentId, studentName, classId);
Many to many:
eg: a student can choose multiple courses, and a course can be selected by multiple students.
It is often necessary to use an intermediate table~
student(studentId, studentName);
course(courseId, courseName);
Middle table design: student_course(studentId, courseId);
ER diagram reference blog: Arrangement of basic concepts of database ER diagram

3, Add

You can insert query results (the number and type of columns of query results must match the table with inserted data)

insert into Table name [column..] select ..;

4, Inquiry

1. Aggregate query
① Aggregate function
Common operations such as counting the total number and calculating the average value (no spaces are allowed). When summing / averaging, the aggregation function is equivalent to calculating the data as a double.

functionexplain
count([distinct] expr)Returns the number of query data (not counted if NULL)
sum([distinct] expr)Returns the sum of the query data (listed as a number)
avg([distinct] expr)Returns the average value of query data (listed as a number)
max([distinct] expr)Returns the maximum value of query data (listed as a number)
min([distinct] expr)Returns the minimum value of the query data (listed as a number)

② group by clause
Use the group by clause in select to group and query the specified columns.
Need to meet: when using group by for grouping query, the field specified in select must be "grouping by field". If other fields want to appear in select, they must be included in the aggregation function.
eg: employee table, including id (primary key), name (name), role (role) and salary (salary)

--Staff table
create table emp(
 id int primary key auto_increment,
 name varchar(20) not null,
 role varchar(20) not null,
 salary numeric(11,2)
);
insert into emp(name, role, salary) values
('Jack Ma','waiter', 1000.20),
('pony ','Game accompaniment', 2000.99),
('Sun WuKong','Game character', 999.11),
('Pig incompetence','Game character', 333.5),
('Sand monk','Game character', 700.33),
('Lao Wang next door','chairman', 12000.66);
--Query the maximum wage, minimum wage and average wage of each role
select role,max(salary),min(salary),avg(salary) from emp group by role;

③ having clause (grouping aggregation)
After grouping with the group by clause, if you need to filter the grouping results conditionally, you can't use the where statement, but you need to use having. Where condition filtering is used to filter the original data (data before aggregation) in the current table, but now it is used to filter the aggregated data, so having is used.

--Displays the role whose average salary is less than 1500 and its average salary
select role,max(salary),min(salary),avg(salary) from emp group by role 
having avg(salary)<1500;

2. Joint query (Cartesian product + filter criteria)
① Inner connection
There are two ways:

--Alias to be used for fields, connection conditions and other conditions.How member variables
select field from Table 1 alias 1 [inner] join Table 2 alias 2 on Connection conditions and Other conditions;
select field from Table 1 alias 1,Table 2 alias 2 where Connection conditions and Other conditions;

② External connection
The external connection is divided into left external connection and right external connection. If the table on the left is completely displayed in the joint query, we say it is a left outer connection; The table on the right completely shows that it is the right outer connection.
Syntax:

-- Left outer connection, fully shown in Table 1
select Field name  from Table name 1 left join Table name 2 on Connection conditions;
-- Right outer connection, fully shown in Table 2
select field from Table name 1 right join Table name 2 on Connection conditions;

③ Self connection
Self join refers to connecting itself to query in the same table.
eg: display all the information that the score of "computer principle" is higher than that of "Java"

-- First query "computer principle" and“ Java"Curricular id
select id,name from course where name='Java' or name='Computer principle';
-- Then query the score ratio of "computer principle" in the score table“ Java"Good information
select
 s1.* 
from 
 score s1,
 score s2 
where
 s1.student_id = s2.student_id
 and s1.score < s2.score
 and s1.course_id = 1
 and s2.course_id = 3;

④ Subquery
Subquery refers to the select statement embedded in its SQL statement, also known as nested query

--single-row subqueries 
select * from student where classes_id=(select classes_id from student where name = 'I don't want to graduate'); 

--Multiline subquery
// [not]in keyword
select * from score where course_id in (select id from course where
name='chinese' or name='english');

// [not]exists keyword
select * from score sco where exists (select sco.id from course cou 
where (name='chinese' or name='english') and cou.id = sco.course_id);

⑤ Merge query
In practical application, in order to merge the execution results of multiple select ions, you can use the set operators UNION, UNION all. When using UNION and UNION all, the fields in the result set of the previous and subsequent queries need to be consistent.
union: when this operator is used, the duplicate rows in the result set will be automatically removed.
union all: this operator is used to obtain the union of two result sets. When this operator is used, the duplicate rows in the result set will not be removed.

select ... from ... where condition 
union
select ... from ... where condition;

Come on, little butterfly~

Tags: Database MySQL Big Data

Posted by alemapo on Wed, 22 Sep 2021 07:55:48 +0530