Large MySQL database learning series

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)
4. Backup data table (table already exists)

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
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;

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 ''.

  1. 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 ='';
SELECT * FROM emp WHERE address IS NULL OR address='';

Non empty query

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


2) multi column sorting

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.

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

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.

Tags: Database MySQL

Posted on Fri, 29 Oct 2021 11:58:38 -0400 by razta