Basic version of addition, deletion, query and modification (CRUD) in MySQL ~ data table

-- Create student table
create table student(
    id int,
    sname varchar(10),
    sex varchar(1),
    birthday datetime,
    grade int comment 'achievement'
);

Single row data + full column insertion

Note: when inserting all columns, the quantity must be consistent with the quantity and order of the columns in the definition table

For example:

-- Single line data + Full column insertion
insert into student values('20201', 'Zhang San', 'male', '2002-11-17', 95);
insert into student values('20202', 'Li Si', 'female', '2002-11-05', 92);

It can be seen from the following command window that it has been successfully inserted. You can query it later by using the select statement

Multi row data + specified column insertion

--Multiline data + Specify column insertion
insert into student(id, sname, grade) values
('20203', 'Wang Wu', 92),
('20204', 'Meng Liu', 85);

Successfully inserted data

Query data (Retrieve)

In order to make the query more impressive, add a part of data in the way of full column insertion + multi row data in the data table

--Multiline data + Full column insertion
insert into student values
('20205', 'Zhao Qi', 'male', '2022-10-15', 91),
('20206', 'Meng Ba', 'male', '2022-10-16', 84),
('20205', 'Li Qi', 'female', '2022-10-15', 93),
('20206', 'Li ba', 'female', '2022-10-15', 89);

Full column query

-- Full column query
select * from student;

Specify column query

Specify the order of columns to query. You don't need to define the order of tables. You can customize the order of queries

-- Specify the order of columns for query, and you do not need to query in the order of defining tables
select sname, id, grade from student;

The query field is an expression

① The expression does not contain a field

-- The expression does not contain a field
select id,sname,10 from student;

② The expression contains a field

-- The expression contains a field
select id, sname, grade+10 from student;

Alias

Specify an alias for the column in the query result, which indicates the returned result set. Take the alias as the name of the column

The role of alias: it is more concise and regular, and clearer when it involves multi table query

-- alias
select id, sname, grade+10 Total score from student;

De duplication distinct

distinct can be used to de duplicate the specified column or multiple columns

-- Weight removal
select distinct id from student;

Sort order by

Keyword: ORDER BY

·ASC is in ascending order (from small to large)

`DESC is in descending order (from large to small)

`The default is ASC

· sorting of null data is considered to be smaller than all data. Ascending order appears at the top and descending order appears at the bottom

-- Sort query by score
select id, sname, sex, grade from student order by grade;

Conditional query where

operator

Comparison operator

>, > =, <, < =: greater than, greater than or equal to, less than, less than or equal to;

=: equal to, NULL is unsafe. For example, the result of NULL = NULL is NULL;

< = >: equal to, NULL is safe. For example, the result of < NULL = NULL > is TRUE(1);

!=, <>: Not equal to;

BETWEEN a0 AND a1: range matching, [a0, a1], if a0 < = value < = a1, return TRUE(1);

IN(option,.....): If it is one of the options, return TRUE(1);

IS NULL: IS NULL;

IS NOT NULL: not NULL;

LIKE: fuzzy matching% Represents any number of (including 0) arbitrary characters_ Represents any character;

Logical operator

AND: multiple conditions must be TRUE(1) before the result is TRUE(1);

OR: if any condition is TRUE(1), the result is TRUE(1);

NOT: the condition is TRUE(1) and the result is FALSE(0)

be careful:

1. where conditions can use expressions, but cannot use aliases;

2. and takes precedence over or. When used at the same time, you need to use () to package the priority part

Basic query

Query students with scores greater than 90

-- Query students with scores greater than 90
select sname, grade from student where grade > 90;

AND and OR

Query students with student number greater than 20202 and score greater than 90

-- Query students with student number greater than 20202 and score greater than 90
select id, sname, grade from student where id > '20202' and grade > 90;

Query students with student number greater than 20202 or score greater than 90

-- Query students with student number greater than 20202 or score greater than 90
select id, sname, grade from student where id > '20202' or grade > 90;

Range query

1,BETWEEN ... AND ...

Query students with grades between 89-100 and their grades

-- Query students with grades between 89-100 and their grades
select sname, grade from student where grade between 89 and 100;

2,IN

Inquire about students whose grades are 89 or 92 or 91 or 95 and their grades

-- Inquire about students whose grades are 89 or 92 or 91 or 95 and their grades
select sname, grade from student where grade in (89, 91, 92, 95);

3. Fuzzy query (LIKE)

Match any number of characters'% '

-- Match any number of characters
select id, sname from student where id like '2%';

Match any strict character ''

-- Match any character strictly
select sname from student where sname like 'Lee_';

Paging query LIMIT

Starting from 0, filter 3 records and sort them in ascending order according to students' grades

-- Paging query
select id, sname, sex, grade from student order by grade limit 3 offset 0;

Starting from 3, filter 3 records and sort them in ascending order according to students' grades

-- Paging query
select id, sname, sex, grade from student order by grade limit 3 offset 3;

Modify (Update)

· change Zhang San's score to 100 points

-- Change Zhang San's score to 100
update student set grade = 100 where sname = 'Zhang San';

·Add ten to the score of the last three students

-- Add ten to the score of the last three students
update student set grade = grade + 10 order by grade limit 3;

· update the scores of all students to double the original

-- Update the grades of all students to double the original
update student set grade = grade * 2 ;

Delete delete

·Delete Zhang San's data

-- Delete Zhang San's data
delete from student where sname = 'Zhang San';

·Delete the data of the whole table

-- Delete the data of the whole table
delete from student;

Content summary

·Add

-- Single line insertion
insert into surface(Field 1, ..., field N) values (value1, ..., valueN);

-- Multiline insertion
insert into surface(Field 1, ..., field N) values
(value1, ...),
(value2, ...),
(value3, ...);

·Inquiry

-- Full column query
select * from surface

-- Specify column query
select Field 1, Field 2, ... from surface;

-- Query expression field
select Field 1 + 100, Field 2 + Field 3 from surface;

-- alias
select Field 1 alias 1, Field 2 alias 2 from surface;

-- Weight removal DISTINCT
select distinct field from surface;

-- sort ORDER BY
select * from surface order by sort field;

-- Condition query WHERE
-- (1)Comparison operator (2)BETWEEN ... AND ... (3)IN (4)IS NULL 
--  (5)LIKE (6)AND (7)OR (8)NOT
select * from surface where condition;

·Modification

-- modify
update surface set Field 1 = value1, Field 2 = value2... where condition;

·Delete

-- delete
delete from surface where condition;

All codes of this blog have been uploaded to gitee. Click the link to get: Add, delete, check and modify code

Tags: MySQL Database SQL

Posted by scotthoff on Fri, 15 Apr 2022 10:53:27 +0930

Programs Wiki