Basic knowledge of MySQL

MySQL table query statement

DQL: query statement

Sort query

• Syntax: order by clause

• Sort by:

  • ASC: ascending order, default
  • Desc: descending order.

• be careful:

  • If there are multiple sorting conditions, the second condition will be judged only when the condition values of the current edge are the same

    SELECT * from userinfo ORDER BY age ASC,id DESC;

Aggregate function:

A column of data is taken as a whole for longitudinal calculation

1. Count: count

   Select count (age) from userinfo; -- returns the number. This method does not include null
   Select count (if null (age, 0)) from userinfo; -- judge whether it is null, if yes, calculate by 0
   Select count (8) from userinfo; -- query records (not recommended)

2. Max: calculate the maximum value

   Select max (age) from userinfo

3. Min: calculate the minimum value

   Select min (age) from userinfo; -- calculate the youngest in the table

4. Sum: calculate and

   Select sum (age) from userinfo; -- calculate the sum of all ages in the table

5. AVG: calculate the average value

   Select AVG (age) from userinfo; -- calculate the average age in the table

Group query:

1. Syntax: group by group field;

2. be careful:

   • Query fields after grouping: grouping fields, aggregate functions

   • What’s the difference between where and having?

     1. Where is defined before grouping. If it does not meet the conditions, it will not participate in grouping. Having is defined after grouping. If the result is not satisfied, it will not be queried;
     2. Where condition can not be followed by aggregate function. Having can judge aggregate function

     3. Implementation statement

        --They were grouped according to sex, and the average age of each group was found out
        select sex,avg(age) from userinfo group by sex; 
        --Group according to sex, find out the average age of each group, and find out the number of participants in the average age
        select sex,avg(age),count(age) from userinfo group by sex;
        --Group according to sex, find out the average age of each group, and find out the number of participants in the average age 并且过滤掉大于20岁的人
        select sex,avg(age),count(age) from userinfo where age>20 group by sex;
        --The same as above and filter the group with less than 2 participants
        select sex,avg(age),count(age) from userinfo where age>20 group by sex having count(age)<2;

Paging query:

1. Syntax: index starting from limit, number of queries per page

2. Formula: (starting index – 1) * number of queries per page

   Select * from userinfo limit 0,3; -- page 1
   Select * from userinfo limit 3,3; -- page 2
   Select * from userinfo limit 6,3; -- page 3

3. Limit is only applicable to MySQL; each database has its own paging mode

Constraints:

• Concept: limit the data in the table to ensure the correctness, validity and integrity of the data in the table;

• Classification:

  • Primary key constraint: primary key
  • Non NULL constraint: not null
  • Unique constraint: unique
  • Foreign key constraints

• Nonempty constraint

  • Add constraints when creating tables

    CREATE TABLE userinfo(
            id:INT,
            name:VARCHAR (20) Not null -- name is a non empty field
    )

  • After creating the table, add non empty fields to the fields in the table

    ALTER TABLE userinfo MODIFY NAME VARCHAR(20) NOT NULL;

  • Delete non empty fields

    ALTER TABLE userinfo MODIFY NAME VARCHAR(20);

• Unique constraint: unique, value cannot be repeated

  • When creating a table, add a unique constraint

    CREATE TABLE userinfo(
        id INT,
        name:VARCHAR(20) UNIQUE
    )

  • Add a unique constraint after creation (Note: the unique constraint field can have multiple nulls)

    ALTER TABLE userinfo MODIFY NAME VARCHAR(20) UNIQUE;

  • Delete unique constraint

    Alter table userinfo modify name varchar (20)
    Alter table userinfo drop index name -- this is correct

• Primary key constraint primary key

  • be careful

    • Meaning: not empty and unique
    • A table can only have one primary key
    • A primary key is the unique identifier of a record in a table

  • Add primary key constraint when creating table

    CREATE TABLE userinfo(
       Id int primary key, -- add primary key  
       name VARCHAR(20)  
    )

  • Add primary key after creating table

    ALTER TABLE userinfo MODIFY id INT PRIMARY KEY;

  • Delete primary key

    Alter table userinfo modify id int; -- this is a wrong way to write
    Alter table userinfo drop primary key; -- this is correct

  • Automatic growth

    • Concept: if a column is of type int, use auto_ Increment can achieve automatic growth

      CREATE TABLE userinfo(
         id INT PRIMARY KEY AUTO_ Increment, - add auto growth
         name VARCHAR(20)  
      )
      --Note: automatic growth is only related to one item. For example, the previous item is 100, and the next item is 101
      --Delete auto growth
      ALTER TABLE userinfo MODIFY id int;
      ALTER TABLE userinfo MODIFY id int auto_increment;

• Foreign key constraints foreign key, so that the relationship between tables, so as to ensure the correctness of the data

  • grammar

    create table userinfo (
        ....
        Foreign key columns,
        Foreign key name (foreign key column name) references main table name (primary key column name)
    )

  • Delete foreign key

    Alter table userinfo drop foreign key column name

  • Add foreign key after creating table

    Alter table userinfo add constraint foreign key name foreign key references main table name

  • Cascade update operation

    Alter table userinfo add constraint foreign key name foreign key references main table name on update cascade on delete cascade; -- on update cascade cascade on delete cascade

multi-table query

Internal connection

Implicit join query:

select * from emp,dept where  emp.id=dept .rid --  emp.id  Is a foreign key dept.rid  Is the primary key

select 
    t1.name,
    t1.gender,
    t2.name
from 
    emp t1,
    dept t2
where 
    t1.id=t2.rid;

Show internal connections

select * from emp inner join dept on emp.id=dept.rid;
 select * from emp join dept on  emp.id=dept . rid; -- inner is optional

Internal connection query:

1. Which tables to query data from
  2. What are the conditions?
  3. Which fields to query

External connection query

1. Left external connection:

select * from emp left [outer] join dept on  emp.id=dept . rid; -- [outer] is optional 
  --Left outer join queries the left table and its intersection

1. Right outer link

select * from emp right [[outer]] join dept on emp.id=dept.rid;
--The right outer join queries the right table and its intersection

1. Subqueries: nested queries

--Get the information about the oldest age
Select max (age) from userinfo; -- find out the result set, for example: 56
select * from userinfo where age=56；
select * from userinfo where age=( select Max(age) from userinfo )；

• The result of the subquery is single row and single column. A subquery can be used as a condition, using operator judgment

--Query the person whose salary is less than the average salary
select * from userinfo where salary < ( select avg(salary) from userinfo )

• The result of subquery is single row and multi column

--Query all employee information of two departments
 Select * from userinfo where rid in (select id from userinfo where name = the name of a department or name = the name of another department;);

• The result of subquery is multi row and multi column

--Think of a subquery as a virtual table
select * from dept t1 ,(select * from emp where join_date>"2011-11-11") t2 where t1.id=t2.rid;

Update complete

​

​