MySQL table addition, deletion, modification and query basic tutorial

insert into [table name] (field1, field2,....) value (value1, value2, ...);

insert into [table name] (field 1, field 2, ....) values
(value1, ...),
(value2, ...),
(value3, ...);

CREATE TABLE exam_result (
 id INT,
 name VARCHAR(20),
 chinese DECIMAL(3,1),
 math DECIMAL(3,1),
 english DECIMAL(3,1)
);

-- Insert two records. The number of value_list must be consistent with the number and order of columns in the definition table. insert into exam_result value (1, 'tom', 68, 98, 56); 
insert into exam_result value ( 2, 'jum', 87.5, 78, 77); 

- Insert two records. The number of value_list must be consistent with the number and order of the specified columns. INSERT INTO exam_result (id,name, chinese, math, english) VALUES
 (1,'tom', 67, 98, 56),
 (2,'jum', 87.5, 78, 77),
 (3,'lim', 88, 98.5, 90),
 (4,'tim', 82, 84, 67),
 (5,'huy', 55.5, 85, 45),
 (6,'sun', 70, 73, 78.5),
 (7,'ming', 75, 65, 30);

select * from [table name];

select [specify query column] from [table name];

select [field expression] from [table name];

select colum [as] [column name] from [table name];

--98 points repeated select math from exam_result;
+------+
|math|
+------+
| 98.0 |
| 78.0 |
| 98.0 |
| 84.0 |
| 85.0 |
| 73.0 |
| 65.0 |
+------+

-- Remove duplicate results select distinct math from exam_result;
+------+
|math|
+------+
| 98.0 |
| 78.0 |
| 84.0 |
| 85.0 |
| 73.0 |
| 65.0 |
+------+

select * from [table name] order by [sort field];

select name, math from exam_result order by math desc;
+------+------+
| name | math |
+------+------+
| tom | 98.0 |
| lim | 98.0 |
| huy | 85.0 |
| tim | 84.0 |
| jum | 78.0 |
| sun | 73.0 |
| ming | 65.0 |
+------+------+

select * from exam_result order by math desc, chinese desc;
+------+------+---------+------+---------+
| id | name | chinese | math | english |
+------+------+---------+------+---------+
| 3 | lim | 88.0 | 98.0 | 90.0 |
| 1 | tom | 67.0 | 98.0 | 56.0 |
| 5 | huy | 55.5 | 85.0 | 45.0 |
| 4 | tim | 82.0 | 84.0 | 67.0 |
| 2 | jum | 87.5 | 78.0 | 77.0 |
| 6 | sun | 70.0 | 73.0 | 78.5 |
| 7 | ming | 75.0 | 65.0 | 30.0 |
+------+------+---------+------+---------+

-- Query students who failed English and their English scores (< 60)
select name, english from exam_result where english < 60;

-- Query students whose Chinese scores are better than their English scores select name, chinese, english from exam_result where chinese > english;

-- Query students whose total scores are below 200 select name, chinese + math + english as total from exam_result where chinese + math + english < 200;

-- Query students whose Chinese scores are greater than 80 and whose English scores are greater than 80 select * from exam_result where chinese > 80 and english > 80;

-- Query students whose Chinese scores are greater than 80 points or whose English scores are greater than 80 points select * from exam_result where chinese > 80 or english > 80;

-- Query the students whose Chinese scores are between [80, 90] and their Chinese scores select name, chinese from exam_result where chinese BETWEEN 80 AND 90;

select name, chinese, from exam_result where chinese >= 80 and chinese <= 90;

 -- Query the students whose math scores are 58, 59, 98 or 99 and their math scores select name, math from exam_result where math in (58, 59, 98, 99);

select name from exam_result where name like 't%';
+------+
| name |
+------+
| tom |
| tim |
+------+

select name from exam_result where name like 't__';
+------+
| name |
+------+
| tom |
| tim |
+------+

select name, chinese from exam_result where chinese like '%8%';
+------+---------+
| name | chinese |
+------+---------+
| jum | 87.5 |
| lim | 88.0 |
| tim | 82.0 |
+------+---------+

 select name from exam_result where id id not null;

-- Initial data table select * from exam_result;
+------+------+---------+------+---------+
| id | name | chinese | math | english |
+------+------+---------+------+---------+
| 1 | tom | 67.0 | 98.0 | 56.0 |
| 2 | jum | 87.5 | 78.0 | 77.0 |
| 3 | lim | 88.0 | 98.0 | 90.0 |
| 4 | tim | 82.0 | 84.0 | 67.0 |
| 5 | huy | 55.5 | 85.0 | 45.0 |
| 6 | sun | 70.0 | 73.0 | 78.5 |
| 7 | ming | 75.0 | 65.0 | 30.0 |
+------+------+---------+------+---------+

-- The first three records select * from exam_result limit 3;
+------+------+---------+------+---------+
| id | name | chinese | math | english |
+------+------+---------+------+---------+
| 1 | tom | 67.0 | 98.0 | 56.0 |
| 2 | jum | 87.5 | 78.0 | 77.0 |
| 3 | lim | 88.0 | 98.0 | 90.0 |
+------+------+---------+------+---------+

-- Three records starting from the third one select * from exam_result limit 3 offset 3;
+------+------+---------+------+---------+
| id | name | chinese | math | english |
+------+------+---------+------+---------+
| 4 | tim | 82.0 | 84.0 | 67.0 |
| 5 | huy | 55.5 | 85.0 | 45.0 |
| 6 | sun | 70.0 | 73.0 | 78.5 |
+------+------+---------+------+---------+

select * from exam_result limit 3 , 4;
+------+------+---------+------+---------+
| id | name | chinese | math | english |
+------+------+---------+------+---------+
| 4 | tim | 82.0 | 84.0 | 67.0 |
| 5 | huy | 55.5 | 85.0 | 45.0 |
| 6 | sun | 70.0 | 73.0 | 78.5 |
| 7 | ming | 75.0 | 65.0 | 30.0 |
+------+------+---------+------+---------+

update exam_result set math = math + 30 order by chinese + math + english limit 3;

delete from [table name];

-- Delete Ming's test score delete from exam_result where name = 'ming';

-- Delete the entire table delete from exam_result;

-- Single row insert insert into [table name] (field 1, ..., field N) values ​​(value1, ...,value N);
-- insert into [table name](field 1, ..., field N) values
(value1, ...),
(value2, ...),
(value3, ...);

--Full table query select * from [table name];
--Specify column query select [column name 1, column name 2,...] from [table name];
--Query expression field select [expression 1, expression 2,...] from [table name];
--alias select 
--DISTINCT
select distinct [field] from [table name];
-- Sorting ORDER BY
select * from [table name] order by [sort field];
-- Conditional query WHERE
-- (1) Comparison operator (2) BETWEEN ... AND ... (3) IN (4) IS NULL (5) LIKE (6) AND (7) OR 
(8)NOT
select * from [table name] where [condition];

update [table] set [modification content 1, modification content 2, ....] where [condition];

delete from [table name] where [condition];