<>MySQL constraint

What to learn today
1.DQL: Query statement 1. Sort query 2. Aggregate function 3. Group query 4. Paging query 2. constraint 3. Multi table relation 4. normal form 5. Database backup and restore
<>DQL Query statement

<> sort

adopt ORDER BY clause , You can sort the query results
SELECT Field name FROM Table name WHERE field = value ORDER BY Field name [ASC|DESC]; ASC: Ascending order , Default value DESC: Descending order
Single column sorting : Sort by one field only , Single column sorting
-- Query all data in student table , Use age to sort in descending order select * from Student order by age desc
Combinatorial sorting : Sorting multiple fields at the same time , If the first field is equal , Then sort by the second field , Push back
SELECT Field name FROM Table name WHERE field = value ORDER BY Field name 1 [ASC|DESC], Field name 2 [ASC|DESC]; --
Query all data of student table , On the basis of descending order of age , If the age is the same, it will be sorted in ascending order select * from student order by age desc,score
asc;
<> Aggregate function

Aggregate functions are vertical queries , Is to calculate the value of a column , Then return the result .

Aggregate functions ignore null values NULL, solve : We can choose not to include non empty column calculations , Or use IFNULL function
IFNULL( Listing , Default value ) If the column name is not empty , Returns the value of this column . If NULL, The default value is returned . -- query id field , If null, Then use 0 replace select
ifnull(id,0) from student;
Aggregate function

SQL The role of aggregation function
max Find the maximum of this column
min Find the minimum value of this column
avg Find the average of this column
count Count how many records there are in this column
sum Sum this column -- Query age greater than 18 Total number of select count(*) from student where age>18; --
Query the total score of English select sum(english) Total score from student; -- Minimum score for English query select min(english)
Lowest score from student; -- Query the highest score in English select max(english) Highest score from student; -- Query the average score of English
select avg(english) average from student;
<> grouping

Group query information , Same data as a group
SELECT field 1, field 2... FROM Table name GROUP BY Group field [HAVING condition ];
where and having The difference between

1.where Define before grouping , If the conditions are not met , Do not participate in the grouping

having Define after grouping , If the result is not satisfied , Will not be queried

2.where Postirreducible aggregate function ,having We can judge the aggregate function
-- Group by gender . Male and female respectively , Average score of female students select sex ,avg(english) from student group by sex; --
Group by gender . Male and female respectively , Average score of female students , Number of people select sex ,avg(english), count(id) from student group
by sex; -- Group by gender . Male and female respectively , Average score of female students , Number of people requirement : Score below 60 People who share , Do not participate in grouping select sex,avg(score),
count(id) from student where score >=60 group by sex;A --
Group by gender . Male and female respectively , Average score of female students , Number of people requirement : Score below 60 People who share , Do not participate in grouping , After grouping . The number of people should be more than 3 personal select sex,avg(score),
count(id) from student where score>=60 group by sex having count(id)>3;
limit sentence

LIMIT The function of is to limit the number of query records .
SELECT *| Field list [as alias ] FROM Table name [WHERE clause ] [GROUP BY clause ][HAVING clause ][ORDER BY clause ][
LIMIT clause ];
format
LIMIT offset,length; -- offset: Number of starting lines , from 0 start , Default to 0 -- length: Number of rows returned --
Query student table information , From the first 3 Line start display , display 5 strip select * from student limit 2,5;
Paging query
limit Start index , Number of queries per page ; Start index = ( Current page number - 1) * Number of items per page -- Display on each page 4 strip SELECT * FROM
studentLIMIT 0,4; -- The third 1 page SELECT * FROM student LIMIT 4,3; -- The third 2 page SELECT * FROM
studentLIMIT 7,3; -- The third 3 page
<> constraint

Why restrict ?
Limit the data in the table , Ensure the correctness of the data , Effectiveness and completeness , Constraint added , The wrong data cannot be added to the table
<> Constraint classification

Constraint name constraint keyword
Primary key constraint primary key
Unique constraint uniqe
Nonempty constraint not null
Foreign key constraints foreign key
Check constraints check remarks :mysql I won't support it
<> Primary key constraint

*
The role of primary key : Used to uniquely identify each record in the database

*
Business fields are not usually used as primary keys , Generally, each table will be designed separately id field , hold id As primary key . Primary keys serve databases and programs , It's not for customers

*
Characteristics of primary key : Not empty (not null) , only ( No repetition )

*
Primary key auto increment : The database automatically generates the value of the primary key field ,AUTO_INCREMENT Indicates automatic growth ( The field type must be an integer type )

Create primary key
1. Add primary key when creating table Field name Field type primary key 2. Add primary key to existing table alter table Table name add primary key( Field name )
; -- Create student table stu, Include fields (id,name,age)id As primary key create table stu( id int primary key,
--id Is the primary key name varchar(20), age int ) desc stu; -- Delete primary key alter table stu drop
primary key; -- Add primary key alter table stu add primary key(id);
Modify the default starting value for self growth

The default starting value of self growth is 1, But it can be modified

The starting value for creating a table
create table Table name ( Listing int primary key auto_increment, )auto_increment= Starting value ; --
Specifies that the starting value is 100 create table stu1 ( id int primary key auto_increment, name varchar(20
) ) auto_increment = 100; insert into stu1 values (null, ' Xuanyue '); select * from
stu1; result : id name 100 Xuanyue -- Delete auto growth alter table stu1 modify id int; -- Add auto growth
alter table stu1 modify id int auto_increment;
<> Unique constraint

unique, A column in the table cannot have duplicate values
Field name Field type uniqe
Create student table stu2, Include fields (id,name)name Column set unique constraint , That is, students with the same name cannot appear
-- When creating a table , Add unique constraint create table stu2 ( id int, name varchar(20) unique ); -- null
no data , There is no problem of repetition , There can be multiple column values null -- Delete unique constraint alter table stu2 drop index name;
-- After creating the table , Add unique constraint alter table stu2 modify name varchar(20) unique;
<> Nonempty constraint

Cannot be in a column null
Field name Field type not null
Create student table stu3, Include fields (id,name,age)name Cannot be empty null
-- Add a non empty constraint when creating a table create table stu3 ( id int, name varchar(20) not null, age int );
-- After creating the table , Add non empty constraint alter table stu3 modify name varchar(20) not null;
<> Foreign key constraints

foreign key, Make a table relate to a slave table
-- When creating a table , Add foreign key create table Table name ( ... Foreign key column constraint Foreign key name foreign key ( Foreign key column name )
references Main table name ( Pig list name ) ); -- Delete foreign key alter table Table name drop foreign key Foreign key name ; --
After creating the table , Add foreign key alter table Table name add constraint Foreign key name foreign key( Foreign key field name ) references Main table name
( Primary table column name );
Cascade operation : When modifying and deleting the primary key of a primary table , At the same time, update or delete the foreign key value of the secondary table , This is called cascading operation

Description of cascading operation syntax
on update cascade update cascade , You can only create cascading relationships when creating tables . Update primary key in primary table , Foreign key from table Columns are also updated automatically
on delete cascade cascading deletion -- Add cascade operation alter table Table name add constraint Foreign key name foreign key(
Foreign key field name ) references Main table name ( Primary table column name ) on update cascade on delete cascade ;
<> Database design

<> Relationship between tables

Three relationships between tables
one-on-one (1:1) : Employee list Resume And so on
One to many (1:n): Employees and departments Most commonly used
Many to many (m:n): Student selection list and student list , Multiple students choose a course , A student chooses multiple courses
<> Data normalization

What is paradigm : When designing a database , Some rules to follow . We should follow the requirements of the latter paradigm , You have to follow all the previous paradigm requirements first

At present, there are six paradigms of relational database : The first paradigm (1NF), The second paradigm (2NF), The third paradigm (3NF), Bath - Koder paradigm (BCNF), The fourth paradigm (4NF) And the fifth paradigm (5NF, Also known as the perfect paradigm ).

The first three paradigms are generally used

Characteristics of paradigm
1NF Atomicity : Each column in the table cannot be split
2NF No local dependency , A table only describes one thing , Each column in the table is completely dependent on the primary key
3NF No delivery dependency , Each column in the table depends directly on the primary key , Instead of relying on the primary key indirectly through other columns
Several concepts of dependence
1. Functional dependence :A--->B adopt A attribute ( Attribute group ) Value of , You can be sure that it's unique B Property value , be called B Depend on A // Student number --> full name ( Student number , Course name --> fraction ) 2.
Partial functional dependency :A-->B, If A Is a property group , be B Property values really only need to depend on A Some values of the property group are available //( Student number , Course name ) -- > full name 3. Complete functional dependence :A-->
B, If A Is a property group , be B Property value needs to depend on A All attribute values of the attribute group //( Student number , Course name ) --> fraction 4. Transfer function dependency :A-->B, B-->C , If there is
"A → B → C" The decisive relationship , be C Delivery depends on A // Student number --> Department name , Department name --> Head of Department 5.
code : If it's in a table , A property or group of properties , Completely dependent on all other properties , It is called this property ( Attribute group ) Is the code of the table Main attribute : All attributes in the code attribute group Non primary attribute : Attributes of the division over code attribute group
// For example, the code in the table is :( Student number , Course name )
<> Backup and restore of database
-- backups mysqldump -u user name -p password Database name > Saved path -- reduction 1. Login database 2. Create database 3. Using the database 4. Executive document
source File path -- Restore format use database ; source Path to import file ;

Technology
©2019-2020 Toolsou All rights reserved,
Huawei 2021 session Hardware Engineer Logical post (FPGA) Super detailed surface !!!Vue-element-admin upgrade ui edition virtual machine VMware Download and install the most detailed tutorial !C++ Move constructor and copy constructor sound of dripping water java Backstage interview pygame Realize full screen mode and adjustable window size mysql Database setting character set configuration modification my.ini file (windows)30 What's the experience of being a junior programmer at the age of 20 C++ Multithreading programming ( Summary of common functions and parameters )python_ cherry tree