Create a database

create database database name [other options];

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 "-"
    );

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);

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

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.

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
);

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;

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";

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;

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;

Delete the entire table

##Basic form: 
drop table Table name;

##Delete the workmates table: 
drop table workmates;

Delete the entire database

##Basic form: 
drop database Database name;

##Delete samp_db database: 
drop database samp_db;