Summary of common SQL statements in MySQL foundation 1

Time:2021-1-27

Database and table operation

Create, delete and view databases

create database mysql1 charset utf8;
drop database mysql1;
show databases;
use mysql1;

Create, delete and view tables

create table mytab(
    id int primary key auto_increment,
    name varchar(50),
    gender varchar(10) not null,
    date date,
);    
//View table structure
desc mytab;

drop table mytab;

Add, update and delete table records


Insert into mytab value (null, "Zhang San", "male", "2222-1-22");
Update mytab set name = "Zhang San" where id = 1;
delete from mytab where id=1;

//Query all student information
select * from mytab;
//Query all boys
Select * from mytab where gender = male;
//Look up the boy's name
Select name from mytab where gender = male;
//Query ID > 3
select * from mytab where id>3;
//Use as to specify the header alias, as can be omitted
Select name as name from mytab;

Query table record

emp10;~~~~

select * from emp;
select job,dept from emp;
//Remove duplicate data
select distinct job,dept from emp;
//Choose all the people whose SAL is more than 3000
select name from emp where sal>3000;
//Select the names of SAL and bonus whose sum is more than 4500 ~ ~ those with null value will not be displayed
select name from emp where sal+bonus>4500;
//If null (bonus, 0), if NULL exists, set it to 0
select name from emp where sal+ifnull(bonus,0)>4500;
//
select name,sal from emp where sal>3000 and sal<4500;
//Including 4500~~~~
select name,sal from emp where sal between 3000 and 4500;
//SAL is 370045004200
select name,sal from emp where sal=3700 or sal=4500 or sal=4200;
select name,sal from emp where sal in (3700,4500,4200);
//Choose the ones who are not here
select name,sal from emp where sal not in (3700,4500,4200);
//
select name,sal from emp where sal<3000 or sal>4500;
select name,sal+bonus from emp where sal+ifnull(bonus,0)<3000 or sal+ifnull(bonus,0)>4500;
. query employees without Department (i.e. Department column is null value)
select dept from emp where dept is null;
How to query employees with departments (i.e. Department column is not null)
select dept from emp where dept is not null;

Fuzzy query

Query the employee whose name begins with "Liu" in EMP table to display the employee's name.
Select name from EMP where name like "Liu%";
Query the EMP table for employees whose names contain the word "Tao" and display their names.
Select name from EMP where name like "%";
Query EMP table name with "Liu" at the beginning, and the name of two words of employees, display the name of employees
Select name from EMP where name like "Liu"_ ";

Multiline function query

Multiline functions, also known as aggregate (aggregate) functions, perform statistics based on a column or all columns

Multiline function
        effect
        Count (list *)
        Counts the number of rows of records for the specified column in the result set.
        Max (column name)
        The maximum value of a column in the result set
        Min (column name)
        The minimum value of a column in the result set
        Sum (column name)
        The sum of all values of a column in the result set
        AVG (column name)
        The average value of a column in the result set
        be careful:
        (1) Aggregate functions cannot be used in where clauses~~~~
        (2) The result of aggregate function is related to whether it is grouped or not
        (3) Null values are filtered when aggregate functions count
        ~~~~
Count the number of employees whose salary is more than 3000 in EMP table
 select count(*) from emp where sal>3000
 Find the highest salary in EMP table
select MAX(sal) from emp;
Calculate the total salary of all employees in EMP table (excluding bonus)
select SUM(sal) from emp;
Calculate the average salary of EMP employees (excluding bonus)
select AVG(sal) from emp;

Group by

For EMP table, group employees by department, and view the effect after grouping.
select * from emp group by dept;
select dept,count(*) from emp group by dept;
The EMP table is grouped according to the position, and the number of people in each position is counted, showing the position and the corresponding number
  select job,count(*) from emp group by job;
Group EMP table by department, calculate the maximum salary of each department (excluding bonus), and display the Department name and maximum salary
 select dept,MAX(sal) from emp group by dept;

Order by

Sort the salary of all employees in EMP table in ascending order (from low to high), and display the name and salary of employees.
select name,sal from emp order by sal;
The bonus of all employees in EMP table is sorted in descending order (from high to low), showing the name and bonus of employees.
select name,sal from emp order by sal desc;

Paging query limit

Query all the records in EMP table and display them in pages: 3 records are displayed on each page,
select * from emp limit 0,3;
Find the information of the top three employees with the highest salary in EMP table, and display the name and salary
select name,sal from emp order by sal desc limit 0,3;

Other functions

Function name interpretative statement
curdate() Get the current date, the format is: mm / DD / yyyy
curtime() Get the current time, the format is: hours, minutes and seconds
sysdate()/now() Get the current date + time, the format is: month, year, day, hour, minute and second
year(date) Returns the year in date
month(date) Returns the month in date
day(date) Returns the number of days in date
hour(date) Returns the hour in date
minute(date) Returns the minutes in date
second(date) Returns the second in date
CONCAT(s1,s2..) Combine multiple strings such as S1 and S2 into one string
CONCAT_WS(x,s1,s2..) The same as the concat (S1, S2,…) function, but x is added between each string, where x is the separator
. query all employees in EMP table [born between 1993 and 1995], and display name and date of birth.
select name,birthday from emp where birthday>"1993-1-1" and birthday<"1995-12-31";
perhaps
select name,birthday from emp where  year(birthday)>=1993 and year(birthday)<=1995;
Query the EMP table for all employees who have their birthdays this month
select name,birthday from emp where month(birthday) = month(now());
Query EMP table employee's name and salary (salary format: XXX (yuan))
Select name, concat (SAL, "" (yuan) ") as salary from EMP;
Query EMP table employee's name and salary (salary format: XXX / yuan)
select name,concat_ WS ("/", Sal, "Yuan") as salary from EMP;

Field constraints of MySQL

1. Primary key constraint primary key
       Primary key is unique and cannot be empty
       2. Non NULL constraint
       not null;
       3. Unique constraint uinque
       Features: the value is unique and cannot be repeated
       4. Foreign key constraints: foreign key (x) references TBALE (x)

multi-table query

db30

Query department and corresponding employee information
select * from emp c,dept p where c.dept_id=p.id;
Inner connection query
select * from emp c join dept p where c.dept_id=p.id;
Query all departments and their corresponding employees. If there is no employee in a department, the employee will be displayed as null. The left outer connection query will display all the data in the left table. If there is no corresponding data in the right table, null will be displayed;
select * from emp c left join dept p on c.dept_id=p.id;
Union, you can connect left foreign key and right foreign key queries, but the number of query results in the left and right tables must be the same, and the order and number of columns must also be the same; Union will automatically de duplicate