Complete collection of basic MySQL commands

-- Show all databases
show databases;
-- Use xxx database
use xxx;
-- establish db_1 database
create databases [if not exists] db_1[character set utf8mb4];
-- Delete db_1 database
drop database (if exists) db_1;
-- Show all tables in the database
show tables;

-- see tb_1 Table structure
desc tb_1;
-- Create table
-- comment 'a'   Comment to field a, The database will save and -- Can't ;
create table table_name (
field1 datatype,
field2 datatype comment 'a',
field3 datatype
);
-- take desc Methods defined as keywords
`desc` datatype;
-- If there is tb_1 surface , Delete tb_1 surface
drop table (if exists) tb_1;
-- newly added
insert into tb_1 values(------)(------);
insert into tb_1(id) values(a)(b);
-- Inquire about
-- All columns
select * from tb_1;
-- operation , Function query
select a+b,a*b,year(a),day(a),concat(name,' classmate ') from tb_1;
-- Alias
select a b from tb_1;
-- duplicate removal
select distinct a from tb_1;
-- according to a,b Two trains to remove the weight
select distinct a,b from tb_1;
-- Sort
--asc Ascending ;desc Descending
--null Top in ascending order
select a from tb_1 order by a （asc）;
SELECT a b FROM exam_result ORDER BY b DESC;
-- Sort multiple fields , The order of priority follows the order of writing
select a,b,c from tb_1 order by a,b desc,c;
--where Conditions of the query
WHERE Conditions can be expressed as , But you can't use aliases
null You can't use =,！=;
and Prior to the or
between a and b;>=a,<=b;
in(a,b,c---)---> matching （） Internal value  SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);
-- Fuzzy query ：LIKE
-- % Multiple _ One
SELECT name FROM exam_result WHERE name LIKE ' Grandchildren _';
-- Paging query
select ··· from tb_name limit a,b;
select ··· from tb_name [where···][order by···] limit a,b;
-- modify
 update table_name set ······
-- Delete
delete from table_name  [where···][order by···] 
-- null constraint
-- unique Unique constraint （null Not subject to unique constraint ）
-- default Default constraint
CREATE TABLE student (
id INT NOT NULL primary key,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20)
);
--primary key Primary key constraint
primary key = not null & unique
-- froeign key Foreign key constraints
foreign key ( Field name ) references Main table ( Column ）
CREATE TABLE classes (
id INT PRIMARY KEY auto_increment,
name VARCHAR(20),
`desc` VARCHAR(100)
);

CREATE TABLE student (
id INT PRIMARY KEY auto_increment,
sn INT UNIQUE,
name VARCHAR(20) DEFAULT 'unkown',
qq_mail VARCHAR(20),
classes_id int,
FOREIGN KEY (classes_id) REFERENCES classes(id)
);
-- Copy table
insert into table（a,b）select c,d from table;
-- function
COUNT([DISTINCT] expr) Returns the of the queried data Number SUM([DISTINCT] expr) Returns the of the queried data The sum of the , It's not that numbers don't make sense AVG([DISTINCT] expr) Returns the of the queried data Average , It's not that numbers don't make sense MAX([DISTINCT] expr) Returns the of the queried data Maximum , It's not that numbers don't make sense MIN([DISTINCT] expr) Returns the of the queried data minimum value , It's not that numbers don't make sense
count（ A field ）=count（*）=count( Constant ）
--group by grouping
-- select column1 [, sum(column2), ..]  from table group by column1 [,column3];
SELECT The specified field must be “ Group by field ”, If you want other fields to appear in SELECT Must be included in the aggregate function
-- having
GROUP BY After the clauses are grouped , When the grouping results need to be conditionally filtered , Out of commission WHERE sentence , You need to use HAVING
select role,max(salary),min(salary),avg(salary) from emp group by rolehaving avg(salary)<1500;