Article Catalog
Hello everyone, this is Xiaoxinghang. Today I bring you MySQL table additions, deletions and modifications (basic) related to the explanation!
Objective of this section:
- CRUD : Create, Retrieve，Update，Delete
- New data
- Query Data
- Modify data
- Delete data
☕️1. CRUD☕️
- Comment: In SQL, you can use “-space+description” to indicate a comment description.
CRUDThat is, add (Create), query (Retrieve), update (Update), delete (Delete) the acronym of four words.
2. Create
Grammar:
INSERT [INTO] table_name
[(column [, column] ...)]
VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...
Case in point:
-- Create a table of students
DROP TABLE IF EXISTS student;
CREATE TABLE student (
id INT,
sn INT comment 'student number ',
name VARCHAR(20) comment 'Name ',
qq_mail VARCHAR(20) comment 'QQ Mail '
);2.1 Single-line data + full-column insertion
-- Insert two rows, the number of value_lists must match the number and order of columns in the defined table.
INSERT INTO student VALUES (100, 10000, 'Tang Sanzang ', NULL);
INSERT INTO student VALUES (101, 10001, 'Sun Wukong ', '11111'), (102, 10002,' Bai Longma ', '10000');
Note: The data added here must match the table header structure, or it will report an error.
Multi-row data insertion:
2.2 Multiple Rows + Specified Column Insertion
-- Insert two rows, the number of value_lists must be the same as the number and order of columns specified.
INSERT INTO student (id, sn, name) VALUES
(102, 20001, 'Cao Mengde '),
(103, 20002, 'Sun Zhongmou ');Specifies the column to insert:
In our data table student we have three fields id name gender but we can also specify two fields to be inserted name and gender.
2.3 Insertion format on time (homework data table):
Insert the time format manually:
Use the now(); function to get the time at the current moment:
3. Query (Retrieve)
Grammar:
SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...
* Indicates all columns.
This special meaning symbol is called a “wildcard” in computers.
Case in point:
-- Creation of a table of examination results
DROP TABLE IF EXISTS exam_result;
CREATE TABLE exam_result (
id INT,
name VARCHAR(20),
chinese DECIMAL(3,1),
math DECIMAL(3,1),
english DECIMAL(3,1)
);
-- Insertion of test data
INSERT INTO exam_result (id,name, chinese, math, english) VALUES
(1,' Tang Sanzang ', 67, 98, 56),
(2,' Monkey King ', 87.5, 78, 77),
(3,' Pig Wuneng ', 88, 98.5, 90),
(4,' Cao Mengde ', 82, 84, 67),
(5,' Liu Xande ', 55.5, 85, 45),
(6,' Sun Quan ', 70, 73, 78.5),
(7,' Song Gongming ', 75, 65, 30);
3.1 Full-column queries
-- The use of * for full-column queries is not normally recommended.
-- 1. More columns in the query means a larger amount of data to be transferred;
-- 2. May affect the use of indexes. (Indexing to be covered later in the course)
SELECT * FROM exam_result;
Note: Our data table here is named student
3.2 Specified Column Queries
-- Specify that the order of the columns need not be in the order in which the table is defined
SELECT id, name, english FROM exam_result;designationsequential queryData:
According to theDemand InquiryData:
3.3 Query Fields as Expressions
-- expression does not contain fields
SELECT id, name, 10 FROM exam_result;
-- expression contains a field
SELECT id, name, english + 10 FROM exam_result;
-- Expression contains multiple fields
SELECT id, name, chinese + math + english FROM exam_result;
Question: Does a query such as the one above change the data on our database server? Is the result before or after +10?
A: As we can see from the above graph, this data is still the original data and has not produced any changes.
The query expression contains multiple fields:
3.4 Alias
Specifies an alias for a column in the query result, indicating that the alias is used as the name of the column in the returned result set. syntax:
SELECT column [AS] alias_name [...] FROM table_name;
-- Column names of table headers in result sets = aliases
SELECT id, name, chinese + math + english as total FROM exam_result;
3.5 De-weighting: DISTINCT
Use the DISTINCT keyword to de-duplicate a column of data:
-- 98 points duplicated
SELECT math FROM exam_result;
+--------+
| math |
+--------+
| 98 |
| 78 |
| 98 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
7 rows in set (0.00 sec)The result after de-weighting:
-- De-weighting results
SELECT DISTINCT math FROM exam_result;
+--------+
| math |
+--------+
| 98 |
| 78 |
| 84 |
| 85 |
| 73 |
| 65 |
+--------+
6 rows in set (0.00 sec)
3.6 Sorting: ORDER BY
Grammar:
-- ASC is ascending (smallest to largest)
-- DESC in descending order (largest to smallest)
-- Defaults to ASC
SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];- Queries without an ORDER BY clause return an undefined order that should never be relied upon!
- NULL data is sorted as if it were smaller than any value, appearing at the top in ascending order and at the bottom in descending order
-- Query classmate's name and qq_mail, sort by qq_mail
SELECT name, qq_mail FROM student ORDER BY qq_mail;
SELECT name, qq_mail FROM student ORDER BY qq_mail DESC;- Sorting with expressions and aliases
-- Check classmates and total scores, highest to lowest
SELECT name, chinese + english + math FROM exam_result
ORDER BY chinese + english + math DESC;
SELECT name, chinese + english + math total FROM exam_result
ORDER BY total DESC;- Multiple fields can be sorted, with sort priority following the order in which they are written
-- Check students' grades in each subject, displaying them in descending order of Math, ascending order of English, and ascending order of Language.
select id, name, math, english, chinese from exam_result order by math desc, english, chinese;
The figure above shows the data in descending order of math, ascending order of English, and ascending order of language, as described in point 4.
order by statement, which specifies that certain columns are to beAscending order：
select name, math from exam_result order by math;
Sort the two columns using order by.
For mysql, if a sql does not specify an order by, the order of the data in the query result set is not expected.
order by sort by desc(descend -> descending) statement that specifies certain columns for thedescending order：
select name, math from exam_result order by math desc;
Sort multiple fields using order by sort content desc:
3.7 Conditional queries: WHERE
Comparison operators:
Logical operators:
Notes:
- WHERE conditions can use expressions, but not aliases.
- AND has a higher priority than OR, and when used together, you need to use parentheses () to wrap the prioritized part of the execution
Basic format:
select *(data to be displayed) from exam_result where judgment statement.Case in point:
- Basic Query:
-- Enquire about students who failed in English and their English grades ( < 60 )
SELECT name, english FROM exam_result WHERE english < 60;
-- Students whose language scores are better than their English scores are queried
SELECT name, chinese, english FROM exam_result WHERE chinese > english;
-- Checking for students with less than 200 total points
SELECT name, total score of chinese + math + english FROM exam_result
WHERE chinese + math + english < 200;- AND vs. OR:
-- Students who have checked a language score greater than 80 and an English score greater than 80
SELECT * FROM exam_result WHERE chinese > 80 and english > 80;
-- Students who have checked a language score of 80 or higher, or an English score of 80 or higher.
SELECT * FROM exam_result WHERE chinese > 80 or english > 80;
-- Observe the prioritization of AND and OR:
SELECT * FROM exam_result WHERE chinese > 80 or math>70 and english > 70;
SELECT * FROM exam_result WHERE (chinese > 80 or math>70) and english > 70my;AND：
OR：
-
Scope Query:
-
- BETWEEN … AND …
-- Search for students with language scores of [80, 90] and their language scores
SELECT name, chinese FROM exam_result WHERE chinese BETWEEN 80 AND 90;
-- can also be achieved using AND
SELECT name, chinese FROM exam_result WHERE chinese >= 80 AND chinese
<=90;
-
- IN
-- Find out who scored 58 or 59 or 98 or 99 in math and their math scores.
SELECT name, math FROM exam_result WHERE math IN (58, 59, 98, 99);
-- can also be achieved using OR
SELECT name, math FROM exam_result WHERE math = 58 OR math = 59 OR math = 98 OR math = 99;
- Fuzzy query: LIKE
-- % matches any number of characters (including 0)
SELECT name FROM exam_result WHERE name LIKE 'Sun%';-- Match to Sun Wukong, Sun Quan
-- _ matches strictly an arbitrary character
SELECT name FROM exam_result WHERE name LIKE 'sun _'; -- Matched to Sun Quan
- Queries for NULL: IS [NOT] NULL
-- query qq_mail known classmate's name
SELECT name, qq_mail FROM student WHERE qq_mail IS NOT NULL;
-- query qq_mail unknown classmate name
SELECT name, qq_mail FROM student WHERE qq_mail IS NULL;At this point all the data in the table is displayed:
The chinese score is not null:
The chinese score is null:
- Use an alias to name the sum of the results of the query when querying the list of people who scored less than 240 in MATH, CHINESE, and ENGLISH:
3.8 Paging queries: LIMIT
For example, one page displays 10 pieces of data divided into multiple pages.
Grammar:
-- Starting subscript 0
-- Filter n results, starting from 0
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
-- Starting with s, filter n results
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;
-- Starting with s, filter n results，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;Example: Paging by id, 3 records per page, display page 1, 2, 3 respectively.
-- page 1
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 0;
-- page 2
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 3;
-- page 3, if there are less than 3 results, there will be no effect
SELECT id, name, math, english, chinese FROM exam_result ORDER BY id LIMIT 3
OFFSET 6;Get only the first n data (data from 0 to n):
Get data for s to start filtering n results (2 is s 3 is n in the figure below) –Not recommended for low readability and easy ambiguity：
Get data for s to start screening n results (2 is s 4 is n in the figure below) –It is recommended to use a readable offset as the starting data.：
Note: We show the data from the beginning + 1 to show, for example, from the beginning of the 2 back to find 3, then we see the serial number 3, 4, 5 of the three data.
Example: Query the grades of the top three students in our data table and display them:
order by is executed later, so you can use aliases.
But where is executed first, so you can’t use an alias in place of the original.
4. Update
Grammar:
update table name set column name = value, column name = value... [where condition] [order by condition] [limit condition].Case in point:
-- Change the math grade of the Monkey King to 80.
UPDATE exam_result SET math = 80 WHERE name = 'Monkey King ';
-- Change Mr. Cao Mengde's math score to 60 and his language score to 70.
UPDATE exam_result SET math = 60, chinese = 70 WHERE name = 'Cao Mengde ';
-- Add 30 points to the math scores of the bottom three overall students.
UPDATE exam_result SET math = math + 10 ORDER BY chinese + math + english LIMIT 3;
-- Updated language scores for all students by a factor of 2
UPDATE exam_result SET chinese = chinese * 2;Change Mr. Monkey King’s math score to 80.
Change Mr. Cao Mengde’s math grade to 60 and his language arts grade to 70:
Add 10 points to the math scores of the bottom three overall students:
Update the language scores of all students by a factor of 2:
5. Delete
Grammar:
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]
Case in point:
-- Delete the test scores of the Monkey King students
DELETE FROM exam_result WHERE name = 'Monkey King ';
-- Delete entire table data
-- Preparation of test forms
DROP TABLE IF EXISTS for_delete;
CREATE TABLE for_delete (
id INT,
name VARCHAR(20)
);
-- Insertion of test data
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');
-- Delete whole table data
DELETE FROM for_delete;
The difference between delete and drop:
delete from table name; – the table is still there, but the data inside is gone
drop table table name; – table and data are gone
6. Summary of content highlights
- Added:
-- single-line insertion
insert into table (Field 1,... , field N) values (value1,... , value N);
-- multi-line insertion
insert into table (Field 1,... , field N) values
(value1, ...),
(value2, ...),
(value3, ...);- Query:
-- full-column queries
select * from table name;
-- Specified Column Queries
select field1,field2... from table name;
-- Query expression fields
select field1+100,field2+field3 from table name.
-- Alias
select field1 as alias1, field2 as alias2 from Table Name;
-- De-duplication of DISTINCT
select distinct field/expression from table name.
-- ORDER BY
select field(column name) from table name order by sort field[desc], field[desc];
-- Conditional query WHERE:
select field(column name) from table name where Condition;
-- (1) Comparison operators (2) BETWEEN ... AND ... (3)IN (4)IS NULL (5)LIKE (6)AND (7)OR
(8)NOT
select * from the table where condition- Modification:
update table set field 1=value1, field 2=value2... where condition- Delete:
delete from table where conditionAdditional points of knowledge:
- If it takes a long time to execute a sql command, or if you made a mistake with drop or delete, you can always press ctrl + c to cancel.
- truncate table table name; – is also a command to delete a table The difference between drop and truncate is that drop deletes slowly, line by line, while truncate deletes them all.
Thank you for reading, this article has any errors can be published in the comments section of your comments, I will correct the article. If this article is helpful to you please move your agile little hand point a little praise, your every encouragement is the author of the power of creation Oh!