current position:Home>MySQL learning notes (VI) distinct, order by, limit, regular and connected table operations of supplementary table operations

MySQL learning notes (VI) distinct, order by, limit, regular and connected table operations of supplementary table operations

2022-01-27 02:47:38 Guard of Tuanzi

The following link is based on the last article , This time, I'll add the knowledge that I didn't finish last time, that is, the description of table linking operation

​​​​​​​​​​​​​​​​​​​​MySQL Learning record ( 5、 ... and ) Some operations of the supplementary table are where,group by,having usage

One 、 duplicate removal distinct

# Note that the data must be exactly the same in order to be de duplicated

# Don't ignore the primary key , When there is a primary key, it is impossible to redo

[

        {'id':1,'name':'egon','age':18}

        {'id':2,'name':'egon','age':18}

]

# These two sets of data cannot be duplicated , Not as like as two peas. , Because the primary key values of the data are different and cannot be the same

select distinct age from emp; # You can get rid of it

select distinct id,age,name from emp;# It's not going to be heavy

select distinct age,name from emp; # You can get rid of it

Two 、order by Sort

"""

order by The default is ascending asc The asc By default, you can omit not to write

It can also be modified to descending desc

""" 

select * from emp order by salary; # Get according to salary Data sorted in ascending order

select * from emp order by salary desc; # Get according to salary Data sorted in descending order

# Portfolio model

select * from emp order by age desc,salary asc;

# First according to age Descending order , If you come across age The same is according to salary Ascending sort

# Case study

"""

The age of each department is in 10 The average salary of employees over years old and keep the average salary greater than 1000 The department in charge of the , And then sort the average wage in descending order

"""

select post,avg(salary) from emp

        where age>10

        group by post

        having avg(salary)>10000

        order by avg(salary) desc;

3、 ... and 、limit Limit the number of displays

"""

For the situation with a large amount of data , Generally, paging processing is carried out

""" 

select * from emp limit 3; # Show only three pieces of data

select * from emp limit 0,5; # Start with the first data and take it back 5 strip

select * from emp limit 5,5; # Start with the sixth data and take it back 5 strip

# The first parameter is the starting position , The second parameter is the number of display entries

Four 、 Regular

# regexp

select * from emp where name regexp '^j.*(n|y)$' ;

"""

^ : Said to .... start

$ : Said to .... ending

 | : Represents or

 .*: Greedy matching

"""

#  '^j.*(n|y)$': Said to j Starts with n or y Data at the end

5、 ... and 、 Linked table operation

# Table preparation

create table dep(

id int,

name char(4)

) ;

create table emp(

id int primary key auto_increment,

name varchar(20),

sex enum('male','female') not null default 'male',

age int,

dep_id int

);

# Spelling table

select * from emp,dep; # result ----> The cartesian product

 

select * from emp,dep where emp.dep_id=dep.id;

 # The above results meet our expectations ,emp In the table dep_id And dep In the table id Corresponding

"""

MYSQL Also know that we will query the data in the future , It must also be used frequently , All specially prepared the following methods :

inner join Internal connection

left join    Left connection

right join  The right connection

union        Full connection

"""

 

 # inner join

select * from emp inner join dep on emp.dep_id=dep.id;

# Only the public data parts in the two tables can be spliced

 

# You will find that the above results are not dep Table corresponding to the id by 203 The data and emp In the table id by 4 The data of ,

# This is because the above results have only two tables  emp.dep_id=dep.id Corresponding common data part

# left join

select * from emp left join dep on emp.dep_id=dep.id;

# Take the left table emp Subject to , Show all the data in the left table , If there is no corresponding data, use null

 

# right join

select * from emp right join dep on emp.dep_id=dep.id;

 # The same principle is shown on the right dep As the standard , Show all the data in the right table , If there is no corresponding data, use null

# union Full connection Take out all the data in the left and right tables  

select * from emp left join dep on emp.dep_id=dep.id 

union

select * from emp right join dep on emp.dep_id=dep.id;

 

copyright notice
author[Guard of Tuanzi],Please bring the original link to reprint, thank you.
https://en.cdmana.com/2022/01/202201270247357437.html

Random recommended