1. Add records for SQL tables

• grammar

  • Insert some columns into the table:

      insert into student (Column name 1,Column name 2,Column name 3...) values (Value 1, value 2, value 3...);
    

  • Insert all columns into the table:

      insert into student values (Value 1, value 2, value 3...);
    

• matters needing attention

  • 1. The type of value is consistent with the type of table column in the database.
  • 2. The order of values is consistent with that of table columns in the database.
  • 3. The maximum length of the value cannot exceed the maximum length set for the column.
  • 4. The value type is string or date type, enclosed in single quotation marks.

• Add record

  • Add columns

      insert into student (num,name) values ('3','rose');
    

    

  • Add all columns

       insert into student values('4','kobe','1980-8-24');
    

    

2. Record of SQL modification table

• grammar

    	update Table name set Listing=Value, column name=value [where condition];
  

• matters needing attention

  • The type of value is consistent with the type of column.
  • The maximum length of the value cannot exceed the maximum length set by the column.
  • Add single quotes to string types and date types

• Modify all values of a column

    update student set birthday='2001-03-02';
  

  

• Modify multiple columns by criteria

    update student set name='kobe',birthday='2006-6-6' where num='2';
  

  

3. Delete records from SQL table

• grammar
  delete from table name [where condition];

• matters needing attention

  • Deleting a table record means deleting a row of records in the table.
  • Delete if there are no conditions, all records in the table will be deleted by default.

• Delete a record

    delete from student where num='1';
  

  

• Delete all records in the table

    delete from student;
  

  

• There are two ways to delete a record in a bid:

  • delete from student;
    • Deleting all records belongs to DML statement. One record is deleted one by one. Transactions that can act on DML statements (recoverable)
  • truncate table student;
    • Deleting all records is a DDL statement. Delete the table and create a table with the same structure. Transaction cannot control DDL (cannot be recovered)
      

4. View records of SQL table (key)

4.1 basic query

• grammar

    	select [distinct] * |Listing from surface [condition];
  

  • Environmental preparation

    create table exam(
    	id int primary key auto_increment,
    	name varchar(20),
    	english int,
    	chinese int,
    	math int
    );
    insert into exam values (null,'Zhang San',85,74,91);
    insert into exam values(null,'Li Si',95,90,83);
    insert into exam values(null,'Wang Wu',85,84,59);
    insert into exam values(null,'Zhao Liu',75,79,76);
    insert into exam values(null,'pseudo-ginseng',69,63,98);
    insert into exam values(null,'Li Laoba',89,90,83);
    

    

  • Query all students' test score information

      select * from exam;
    

    

  • Check the names and English scores of all students

      select name,english from exam;
    

    

  • Query English score information (do not display duplicate values)

      select distinct english from exam;
    

    

  • View the student's name and the student's total score

      select name ,english+chinese+math from exam;
    

    

  • Alias query

      select name,english+chinese+math as sum from exam;
    

    

4.2 query criteria

• Use the where clause
  -Relationship >, <, > =, < =, < >=
  -like: fuzzy query
  -in: range query
  -Conditional association: and, or, not

  • To inquire about Li Si's grades:

      select * from exam where name = 'Li Si';
    

    

  • The query name is Li Si student and the English score is greater than 90

       select * from exam where name='Li Si' and english > 90;
    

    

  • Query the information of students surnamed Li

      like Fuzzy query can be carried out in like Clause can use_perhaps%As a placeholder._Can represent a character, and%Can represent any character.
      	 * like 'Lee_'		: The name must be two words and surnamed Li.
      	 * like 'Lee%'		: For students surnamed Li in their names, the word Li can be followed by 1 or any character.
      	 * like '%four'		: The name ends with four.
      	 * like '%king%'		: As long as the name contains this word.
    

    
    

  • Query the information of students whose English scores are 69, 75 and 89

       select * from exam where english in (69,75,89);
    

    

4.3 Sorting Query

• Use order instead of field name asc/desc;

     	select * from exam order by chinese;(The default is asc (positive order)
     ![Insert picture description here](https://img-blog.csdnimg.cn/1afd1ef8310b4ac5a5e022e8cc7ba700.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBA6aOe6KGM55qE6I235YWw5Lq6QA==,size_10,color_FFFFFF,t_70,g_se,x_16)
  

  • Query student information and sort according to Chinese scores;

      select * from exam order by chinese desc;
    

    

  • Query student information and sort it by Chinese score:

    

  • Query student information, first sort in descending order according to Chinese scores, and then sort in ascending order according to English scores if the scores are the same

       select * from exam order by chinese desc,english asc;
    

    

  • Query the information of students surnamed Li and sort them in descending order according to their English scores

      select * from exam where name like 'Lee%' order by english desc;
    

    

4.4 grouping statistics query

• Aggregate function usage
  • sum();

    • Get the sum of English scores of all students:

        	select sum(english) from exam;
        ![Insert picture description here](https://img-blog.csdnimg.cn/a33b6075647740a2a2afdc153729900e.png)
      

      • Total English and math scores of all students:

           select max(english),max(math) from exam;
        

        

      • Check the total scores of all students in each subject

          	select sum(english)+sum(chinese)+sum(math) from exam;
        

        

          	select sum(english+chinese+math) from exam;
        

        

          		What is the difference from the above statement?
          			* The above statement is the sum of English scores by column+Sum of Chinese achievements+Sum of math scores.
          			* The following statement calculates English first+language+Math and then sum.
          				* If one appears in the data null The first method can be used to calculate normal statistics, but the second method can't, because the second method is to calculate the row first, but any number and NULL Add all NULL So we can't get the correct value.
          				* Solution: use ifnull Function of
        

        
        

      • Total number of students obtained

          select count(*) from exam;
        

        

      • Number of students surnamed Li

          select count(*) from exam where name like 'Lee%';
        

        

    • max();

      • Get the highest score in Mathematics:

          select max(math) from exam;
        

        

    • min();

      • Get mathematical minimum:

          select min(math) from exam;
        

        

    • avg();

      • Get the average of Chinese scores

          select avg(chinese) from exam;
        

        

4.5 group query

• Syntax: use the group by field name;

• Environmental preparation

    create table orderitem(
    	id int primary key auto_increment,
    	product varchar(20),
    	price double
    );
    insert into orderitem values (null,'Television',2999);
    insert into orderitem values (null,'Television',2999);
    insert into orderitem values (null,'Washing machine',1000);
    insert into orderitem values (null,'Washing machine',1000);
    insert into orderitem values (null,'Washing machine',1000);
    insert into orderitem values (null,'Refrigerator',3999);
    insert into orderitem values (null,'Refrigerator',3999);
    insert into orderitem values (null,'Refrigerator',1999);
  

  • According to the commodity name, the number of purchased commodities of each category:

      select product,count(*) from orderitem group by product;
    

    

  • According to the statistics of commodity name, the total amount of each kind of commodity:

      select product,sum(price) from orderitem group by product;
    

    

    ****where clause cannot be followed by aggregate function * if conditional filtering with aggregate function (conditional filtering after grouping) is used, a keyword having is required
    select product,sum(price) from orderitem group by product having sum(price) > 5000;

    

  • According to the commodity name, the commodities with a total amount of more than 5000 yuan spent on each category of commodities shall be counted and sorted in ascending order according to the total amount

      select product,sum(price) from orderitem group by product having sum(price) > 5000 order by sum(price) asc;
    

    

  • Summary: (order of use)
    S(select)...F(from)...W(where)...G(group by)...H(having)...O(order by);