MySQL Basics

Time:2021-10-7

MySQL table query statement

DQL: query statement

Sort query

  • Syntax: order by clause
  • Sort by:

    • ASC: ascending, 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:

Take a column of data as a whole for longitudinal calculation

  1. Count: count

    select count(age) from userinfo; --  Return the number. Null will not be counted in this way
    select count(ifnull(age,0)) from userinfo; --  Determine whether it is null. If yes, calculate by 0
    select count(8) from userinfo; --  Query record (not recommended)
  2. Max: calculate maximum

    select Max(age) from userinfo; --  The oldest person in the calculation table
  3. Min: calculate the minimum value

    select Min(age) from userinfo; --  The youngest in the calculation table
  4. Sum: Calculation and

    select sum(age) from userinfo; --  Calculate the sum of all ages in the table
  5. AVG: calculate average

    select avg(age) from userinfo; --  Average age in calculation table

Group query:

  1. Syntax: group by group field;
  2. be careful:

    • Fields queried after grouping: grouping fields, aggregation functions
    • What’s the difference between where and having?

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

        --Groups were grouped according to sex, and the average age of each group was found
        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 people participating 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 people participating in the average age 并且过滤掉大于20岁的人
        select sex,avg(age),count(age) from userinfo where age>20 group by sex;
        --Ditto and filter groups 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: (initial index – 1) * number of queries per page

    select * from userinfo limit 0,3; --  first page
    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, effectiveness and integrity of the data in the table;

  • Classification:

    • Primary key constraint: primary key
    • Non NULL constraint: not null
    • Unique constraint: unique
    • Foreign key constraint
  • Non NULL 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, the value cannot be repeated

    • When creating tables, add unique constraints

      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) -- this is wrong
      Alter table userinfo drop index name -- this is correct
  • Primary key constraint primary key

    • be careful

      • Meaning: non 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 a primary key constraint when creating a 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 wrong
      ALTER TABLE userinfo DROP primary key;  --  This is correct
    • Automatic growth

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

        CREATE TABLE userinfo(
           id INT PRIMARY KEY AUTO_ Increment, -- add automatic growth
           name VARCHAR(20)  
        )
        --Note: automatic growth only keeps up with 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;
  • The foreign key constrains the foreign key to generate a relationship between tables, so as to ensure the correctness of data

    • grammar

      create table userinfo (
          ....
          Foreign key columns,
          Constraint foreign key name foreign key references primary table name (primary table column name, that is, primary key name)
      )
    • Delete foreign key

      Alter table userinfo drop foreign key foreign key column name
    • Add foreign keys after creating tables

      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 update on delete cascade delete

multi-table query

Inner connection

Implicit join query:

Select * from EMP, Dept where EMP. Id = dept.rid -- emp.id is the 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. From which tables do you query data
  2. What are the conditions?
  3. Which fields to query

External connection query

  1. Left outer connection:
select * from emp left [outer] join dept on emp.id=dept.rid;  --  [outer] is optional 
  --The left outer join queries the left table and its intersection
  1. Right outer connection
select * from emp right [[outer]] join dept on emp.id=dept.rid;
--The right outer join query is the right table and its intersection
  1. Subqueries: nested queries
--Get the information of the maximum 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 subquery is single row and single column. Subquery can be used as a condition and judged by operators
--Query employees 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 = "name of a department" or name = "name of another department";);
  • The result of subquery is multi row and multi column
--Treat the 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