Summary of SQL Statements in MySQL Database

Posted by Mikester on Thu, 06 Jun 2019 21:53:47 +0200

  1. Create a database

    create database database name [other options];
  2. Create database tables

    create table Table Name(Column Statement);
    
    ## Taking the creation of students table as an example, the table will store such contents as student number (id), name (name), sex (age), contact telephone (tel):
        create table students
        (
            id int unsigned not null auto_increment primary key,
            name char(8) not null,
            sex char(4) not null,
            age tinyint unsigned not null,
            tel char(13) null default "-"
        );
  3. insert data

    insert [into] Table name [(List 1, List 2, List 3, ...)] values (Value 1, Value 2, Value 3, ...);
    
    ##The content in [] is optional, for example, to insert a record into the students table in the samp_db database and execute the statement:
    insert into students values(NULL, "Wang Gang", "male", 20, "13811371377");
    
    ##Sometimes we just need to insert part of the data, or not in the order of columns, we can use this form of insertion:
    insert into students (name, sex, age) values("Sun Lihua", "female", 21);
  4. Single table query

    select Column name from Table Name [query criteria];    
    select Column name from Table Name where condition;
    
    ## For example, to query the names and ages of all students in the students table, enter the sentence select name, age from students; the results are as follows:
        mysql> select name as Full name, age as Age from students;
        +--------+-----+
        | Full name   | Age |
        +--------+-----+
        | Wang Gang   |  20 |
        | Sun Lihua |  21 |
        | Wang Yongheng |  23 |
        | Zheng Junjie |  19 |
        | Chen Fang   |  22 |
        | Zhang Weipeng |  21 |
        +--------+-----+
        6 rows in set (0.00 sec)
    
    
    ##Query all persons over 21 years of age for information: 
    select * from students where age > 21;
    
    ##Query all the information with the word "king" in the name: 
    select * from students where name like "%king%";
    
    ##Query the owner information whose id is less than 5 and whose age is more than 20: 
    select * from students where id<5 and age>20;
    
    ##Eliminate duplicated rows
    select distinct Sno as Student number of elective course from SC;
    
    ##Determine the scope and inquire the names and gender of all students in IS and CS departments
    select Sname as Full name,Sdept as Tie,Sage as Age from student where Sage between 20 and 23;
    
    ##Query the names and gender of all students in IS and CS departments
    select Sname as Full name,Ssex as Gender from student where Sdept='IS' or Sdept='CS';
    select Sname as Full name,Ssex as Gender from student where Sdept in ('IS','CS');
    
    ##Query the names and ages of students who do not belong to either IS or MA departments
    select Sname as Full name,Sage as Age from student where Sdept !='IS'and Sdept!='CS';
    select Sname as Full name,Sage as Age from student where Sdept not in('IS','MA');
    
    ##Queries involving null values (is null)
    ##Query the course number and course name of no pre-course
    select Cno as Course Number,Cname as Course Name,Cpno from course where Cpno is null;
    
    ##Query result ranking (order by)
    ##Query the number and achievement of students taking Course 3. The results are arranged in descending/ascending order.
    ##Decending Order
    select Sno as Student ID,Grade as achievement from SC where Cno=3 order by Grade desc;
    ##Ascending order
    select Sno as Student ID,Grade as achievement from SC where Cno=3 order by Grade asc;
    
    ##Aggregation functions count, sum, avg, max, min
    ##Query the total number of students
    select count(*) as Total number of students from student;
    ##Query the total credits of all courses
    select sum(Ccredit) as Total Credit for All Courses from course;
    ##Query the average age of all students
    select avg(Sage) as Average age from student;
    ##Search for the highest score of Course No. 1
    select max(Grade) as 1 Top Score in Course No. from SC where Cno=1;
    
    ##group by
    ##Query how many boys and girls each have
    select Ssex as Gender,count(*) as Number from student group by Ssex;
    ##Query the course number and average score of each course.
    select Cno as Course Number,avg(Grade) as Average from SC group by Cno;

    having keywords are followed directly by aggregation functions
    The reason for adding the HAVING clause in SQL is that the WHERE keyword cannot be used with the aggregate function.

    SELECT column_name, aggregate_function(column_name)
    FROM table_name
    WHERE column_name operator value
    GROUP BY column_name
    HAVING aggregate_function(column_name) operator value

    Complex example of single form:

    ##Query the number of elective courses (including 2 courses, but not No. 1 courses), student number and elective courses.
    select Sno as Student ID, count(course.Cno) as Number of elective courses
    From SC where course.Cno = SC.Cno and course.Cno != 1
    Group by Sno
    Having Count(course.Cno)>=2;
    
    ##Query the number of students who have failed more than 2 entries.
    select Sno as Student ID
    From SC
    Where sc.Grade < 60
    Group by Sno
    Having count(Cno) > 2
    
    ##Query the number of courses and the number of students selected by more than 2 (including 2) students.
    select Cno as Course Number, count(Sno)
    From SC
    Group by Cno
    Having count(Sno) >= 2
  5. Multi-table join query

    Equivalent and non-equivalent join queries:

    ##Query each student and their elective courses
    select student.Sno as Student ID,course.Cno as Elective Course Number,SC.Grade as achievement 
    from student,course,SC 
    where student.Sno=SC.Sno and course.Cno=SC.Cno ;

    Self-connection:

    ##Inquire about indirect elective courses for each student
    select SC.Sno as Student ID,
    FIRST.Cname as Direct elective courses,
    SECOND.Cname as Indirect elective courses
    from SC,
    course as FIRST,
    course as SECOND
    where FIRST.Cno=SC.Cno
    and FIRST.Cpno=SECOND.Cno;

    External connection

    ##Inquire about all students'elective courses (including those without elective courses)
    select student.Sno as Student ID,
    Sname as Full name,
    sc.Cno as Elective Course Number
    from student 
    LEFT OUTER JOIN SC ON student.Sno=SC.Sno;

    JOIN is used to query data from two or more tables based on the relationship between columns:

    JOIN: Returns rows if there is at least one match in the table
    LEFT JOIN: Returns all rows from the left table even if there is no match in the right table
    RIGHT JOIN: Returns all rows from the right table even if there is no match in the left table
    FULL JOIN: Returns rows whenever a match exists in one of the tables

    Be careful:

    The UNION operator is used to merge the result set of two or more SELECT statements.
    Note that SELECT statements within UNION must have the same number of columns. Columns must also have similar data types. At the same time, the order of columns in each SELECT statement must be the same.

  6. nested queries
    Sub-query with IN predicate (attribute in (query result of sub-query))

    ##Query the information of students in the same department with Wang Min.
    select *
    from student
    where Sdept in (
     select Sdept
     from student
     where Sname='Wang min.'
    );
    
    ##Query the information of students who are not in the same department as Wang Min.
    select *
    from student
    where Sdept not in (
     select Sdept
     from student
      whereSname='Wang min.'
    );
    
    ##Query the student number and name of the course named "Information System".
    select student.Sno as Student ID, Sname as Full name
    from student,SC
    where student.Sno=SC.Sno and Cno in (
     select Cno
     from course
     where Cname='information system'
    )
    
    ##Inquire the student number and name of the students who have attended the class with Liu Chen. (Assume that there is only one class in a course)
    select distinct student.Sno as Student ID, Sname as Full name
    from student,SC
    where student.Sno=SC.Sno and Cno in (
     select Cno
     from SC,student
     where SC.Sno=student.Sno and student.Sno in (
       select Sno
       from student
       where student.Sname='Liu Chen'
      )
    )

    Subqueries with comparison operators (=,>=,<=,<> or!=)

    ##Query all the students'information in the same department with Wang Min (= judgement)
    select *
    from student
    where Sdept=(
       select Sdept
       from student
       where Sname='Wang min.'
    )
    
    ##Query the course number of each student who has exceeded the minimum mark of the course. When the result of the sub-query returns a number, can the sub-query be used as a number? You can use in symbols, or greater than or less than symbols.
    select Cno
    from SC a
    where Grade> (
     select min(Grade)
     from SC b
     where a.Cno=b.Cno
    )
    
    ##Query the number of courses each student has exceeded the average score of his elective courses.
    select Cno
    from SC a
    where Grade> (
     select avg(Grade)
     from SC b
     where a.Sno=b.Sno
    )

    Subqueries with ANY or ALL predicates:
    ANY denotes any one, ALL denotes all, and can be used before parentheses of subqueries

    ##Query the names, gender, age and Department of a student younger than that of a computer department in other departments.
    select Sname as Full name,Ssex as Gender, Sage as Age, Sdept as Department
    from student
    where Sage <(
     select Sage
     from student
     where Sdept='CS'
    );
    
    ##Find out the names and ages of all the younger students in other departments than computer department.
    select Sname as Full name, Sage as Age
    from student
    where Sdept<>'CS' and  Sage <ALL (
     select Sage
     from student
     where Sdept='CS'
    );

    Subqueries with Exists predicates:

    ##Look up the names of all the students who took Course No. 1.
    select Sname as Full name
    from student
    where Exists (
     select *
     from SC
     where Cno=1 and Sno=Student.Sno
    );
  7. Collection Query
    And UNION

    ##Find out the details of the students in the computer department and those under the age of 19.
    select *
    from student
    where student.Sdept='CS'
    union
    select *
    from student
    where student.Sage<=19;

    InterRSECT

    ##Inquire about the intersection of the details of the students who took Course No. 1 with those who were under 19 years of age.
    Select *
    from student,SC
    where student.Sno=SC.Sno and SC.Cno=1
    INTERSECT
    Select *
    from student
    where student.Sage<=19;

    Differential EXCEPT

    ##Query the difference set of detailed information between computer science students and students younger than 19 years old.
    select *
    from student
    where student.Sdept='SC'
    EXCEPT
    select *
    from student
    where student.Sage<=19;
  8. Update data

    update Table Name set Column name=New value where update criteria;
    
    ##Change the phone number with id 5 to the default "-": 
    update students set tel=default where id=5;
    
    ##Increase the age of all by 1: 
    update students set age=age+1;
    
    ##Change the name of the mobile phone number 1328097888 to "Zhang Weipeng" and the age to 19: 
    update students set name="Zhang Weipeng", age=19 where tel="13288097888";
    
  9. Delete data

    delete from Table Name where Delete condition;
    
    ##Delete lines with id 2: 
    delete from students where id=2;
    
    ##Delete all data under the age of 21: 
    delete from students where age<20;
    
    ##Delete all data in the table: 
    delete from students;
    
  10. Modify the created table

    The alter table statement is used to modify the table after it is created. The basic usage is as follows:
    Add columns:

    ##Basic form: 
    alter table Table name add Column and column data types [after Insertion position];
    
    ##Add address at the end of the table: 
    alter table students add address char(60);
    
    ##Insert the column birthday after the column named age: 
    alter table students add birthday date after age;

    Modify columns

    ##Basic form: 
    alter table Table name change Column Name Column New Name New Data Type;
    
    ##Rename the table tel column telphone: 
    alter table students change tel telphone char(13) default "-";
    
    ##Change the data type of the name column to char(16): 
    alter table students change name name char(16) not null;

    Delete columns

    ##Basic form: 
    alter table Table name drop Column name;
    
    ##Delete the birthday column: 
    alter table students drop birthday;

    rename table

    ##Basic form: 
    alter table Table name rename New table name;
    
    ##Rename the students table as workmates: 
    alter table students rename workmates;
  11. Delete the entire table

    ##Basic form: 
    drop table Table name;
    
    ##Delete the workmates table: 
    drop table workmates;
  12. Delete the entire database

    ##Basic form: 
    drop database Database name;
    
    ##Delete samp_db database: 
    drop database samp_db;

Topics: MySQL Database less SQL