Data CRUD operation
CRUD refers to create, retrieve, update and delete. It is also what we often call add, delete, modify and query.
insert data
1. Insert a single piece of data
INSERT INTO Table name VALUES(val_list);
INSERT INTO Table name(col_list) VALUES(val_list);
2. Insert multiple pieces of data
INSERT INTO Table name(col_list) VALUES(val_list1),(val_list2),(val_list3)(val_list4);
3. Backup data table (table does not exist)
CREATE TABLE emp_bak AS SELECT * FROM emp;
4. Backup data table (table already exists)
INSERT INTO emp_bak SELECT * FROM emp;
Update data
UPDATE table_name SET col=val,col=val [WHERE <condition>];
Example: update the data with column id 1 to (Jerry, Yan'an, 118)
UPDATE emp SET empname='jerry',empaddr='Yan'an',empphone='118' WHERE empid=1;
Delete data
DELETE FROM table_name [WHERE <condition>];
Note: if the delete statement does not specify where, all records in the data table will be deleted, similar to TRUNCATE TABLE.
TRUNCATE TABLE table_name；
Note: TRUNCATE will directly DELETE the original table and recreate a table. TRUNCATE will directly DELETE the table instead of deleting records, so the execution speed is faster than DELETE. And it cannot be used in the primary table with primary foreign key relationship.
mysql Why not delete data? delete It will affect the performance. Generally, it is not recommended to be rigid delete Data, but tags deleted = 1 This soft deletion Why? According to previous knowledge mysql Bottom storage principle minimum storage unit page Pages, whether non leaf nodes page The index is stored key And finger Does the needle or leaf node store row data. 1.When deleting large amounts of data MySQL The internal space will not be really deleted, and it will be marked for deletion delflag:N Change to delflag:Y，commit Later Will be purge Enter delete linked list, if next time insert A bigger record, delete Subsequent spaces will not be reused if inserted The record of is less than or equal to delete Empty records will be reused 2.When a small amount of intermediate data is deleted You delete some values in the middle, but you actually just find that page The corresponding data found on the page will be marked for deletion, which will not actually affect page The size occupied by the page may never be used, resulting in memory fragmentation, frequent index splitting and impact SQL Executive plan Stability of stroke Correct posture: use deleted = 1 Field to ensure index continuity, If necessary, the deleted = 0 The fields of are completely migrated to the new table to solve the fragmentation problem
Query data
The basic statement for MySQL to query data from a data table is a SELECT statement. The basic format of the SELECT statement is:
SELECT Field list FROM Table 1,Table 2... WHERE expression GROUP BY field HAVING condition ORDER BY field LIMIT [<offset>,] <row count>]
Single table query
1. Query all fields
SELECT * FROM Table name;
2. Query specified fields
SELECT Listing FROM Table name;
3. Query multiple fields
SELECT Field name 1,Field name 2,...,Field name n FROM Table name;
Example: query id, name, sex, birth in emp table
select id,name,sex,birth from emp;
4. Query specified records
The database contains a large amount of data. According to special requirements, you may only need to query the specified data in the table, that is, filter the data. In the SELECT statement, data can be filtered through the WHERE clause. The syntax format is:
SELECT Field name 1,Field name 2,...,Field name n FROM Table name WHERE query criteria
Example: query the information of female employees in emp table
SELECT * FROM emp WHERE sex='female';
5. Query with in keyword
The IN operator is used to query records that meet the conditions within the specified range. Use the IN operator to enclose all search conditions IN parentheses and separate the search conditions with commas. As long as a value within the condition range is met, it is a match.
SELECT * FROM emp WHERE id=1 OR id=4 OR id=8 OR id=10; SELECT * FROM emp WHERE id IN (1,4,8,10);
The NOT keyword is added before the IN keyword, which makes the query result opposite to the previous one,
SELECT * FROM emp WHERE id NOT IN (1,4,8,10);
6. Query with between and keyword
BETWEEN AND is used to query the values within a certain range. The operator requires two parameters, namely, the start value and end value of the range. If the field values meet the specified range query criteria, these records will be returned.
SELECT * FROM emp WHERE id>=6 AND id<=10; SELECT * FROM emp WHERE id BETWEEN 6 AND 10;
Similarly, the keyword NOT can be added before the BETWEEN AND operator to indicate values outside the specified range. If the field values do NOT meet the values within the specified range, these records will be returned.
Note: when using between and, it must be a small value and a large value.
7. Character matching query with like
Wildcard is a character with special meaning in the WHERE clause of SQL. Multiple wildcards are supported in SQL statements. The wildcards that can be used with LIKE are '%' and ''.
- The percent sign wildcard '%' matches characters of any length, even zero characters
2) Underscore wildcard '', Only one character can be matched at a time
SELECT * FROM emp WHERE name LIKE 'a%'; -- with a start SELECT * FROM emp WHERE name LIKE '%a'; -- with a ending SELECT * FROM emp WHERE name LIKE '%a%'; -- contain a
Example: the name is two letters and begins with a
SELECT * FROM emp WHERE name LIKE 'a_';
8. Query null value
When creating a data table, the designer can specify whether a column contains NULL values. In the SELECT statement, the IS NULL clause can be used to query records with empty contents in a field. In contrast to IS NULL, NOT NULL is used to find records whose fields are not empty.
Example: query employee information with empty address
SELECT * FROM emp WHERE address IS NULL; SELECT * FROM emp WHERE address =''; SELECT * FROM emp WHERE address IS NULL OR address='';
Non empty query
SELECT * FROM emp WHERE address IS NOT NULL; SELECT * FROM emp WHERE address !=''; SELECT * FROM emp WHERE address IS NOT NULL AND adress!=''
9. Multi condition query with and
MySQL uses the AND operator in the WHERE clause to restrict that only records that meet all query conditions will be returned. (that is, what we call AND relationship)
Example: the query gender is male, and the address is Gongxue North Road
SELECT * FROM emp WHERE sex='male' AND empaddr='Gongxue North Road';
10. Multi condition query with or
In contrast to AND, the OR operator is used in the WHERE declaration to indicate that only records that meet one of the conditions can be returned. (that is, what we call OR relationship)
11. No duplicate query results
In the SELECT statement, you can use the DISTINCT keyword to instruct MySQL to de duplicate record values.
SELECT DISTINCT empid,empname,empsex FROM emp;
12. Sort query results
Use the ORDER BY clause to sort the specified column data. desc: flashback asc: default, ascending
1) single column sorting
SELECT * FROM emp ORDER BY id DESC;
2) multi column sorting
SELECT * FROM emp ORDER BY id DESC ,deptid DESC;
13. Aggregate function
Sometimes you don't need to return the data in the actual table, but just summarize the data. MySQL provides some query functions to analyze and report the obtained data.
| function | effect |
|---|---|
| AVG() | Returns the average value of a column |
| COUNT() | Returns the number of rows in a column |
| MAX() | Returns the maximum value of a column |
| MIN() | Returns the minimum value of a column |
| SUM() | Returns the sum of a column |
1) the COUNT() function counts the total number of record rows contained in the data table, or returns the number of data rows contained in the column according to the query result. It can be used in two ways:
COUNT(*) calculates the total number of rows in the table, regardless of whether a column has a value or is empty.
Count (field name) calculates the total number of rows under the specified column. Rows with null values will be ignored during calculation.
2) SUM() is a summation function that returns the sum of the specified column values. The SUM() function ignores rows with NULL column values when calculating.
3) AVG() function obtains the average value of the specified column data by calculating the sum of the returned rows and the data of each row.
4) the MAX() function returns the maximum value in the specified column.
In addition to finding the maximum column value or date value, the MAX() function can also return the maximum value in any column, including the maximum value of character type. When comparing character type data, compare according to the ASCII code value of characters, from a~z, the ASCII code of a is the smallest and the ASCII code of z is the largest. When comparing, first compare the first letter. If they are equal, continue to compare the next character until the two characters are not equal or the character ends. For example,'t 'is the maximum value when comparing' b 'with't'; "bcd" is the maximum value when "bcd" is compared with "bca".
5) the MIN() function is similar to the MAX() function. It is not only applicable to finding numeric types, but also applicable to character types.
14. Grouping query
The GROUP BY keyword is usually used with a collection function
Example: query the number of people in each department
SELECT deptid,COUNT(1) FROM emp GROUP BY deptid;
Example: count the number of men and women in each department
SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,sex;
It can be used together with the previous sorting: sorting after grouping
SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,sex ORDER BY deptid DESC,COUNT(1) DESC;
15. Filtering groups using HAVING
GROUP BY and HAVING can define the conditions to be met for displaying records. Only the groups that meet the conditions will be displayed
For example, the number of men and women in each department is only 3 or more
SELECT deptid,empsex,COUNT(1) FROM emp GROUP BY deptid,empsex HAVING COUNT(1)>=3 ORDER BY COUNT(1) DESC,deptid DESC;
Table relation
There are 1-to-1, 1-to-many, many to many and self association relationships between tables.
1. 1 to 1 Relationship
Example: relationship between person and ID card
2. 1 to many relationship
Example: the relationship between a class's curriculum and grades. A course corresponds to multiple grades.
3. Many to many relationship
Example: the relationship between users and characters in the game. A user can create multiple roles, and a role can also be created by multiple users.