Basic MySQL knowledge and basic MySQL knowledge

Basic MySQL knowledge and basic MySQL knowledge

I. Five clauses of MySQL query:

1. where (conditional query), common operators:

① Comparison Operator

>,<, = ,! =, >=, <=, In (), between val1 and val2

② Logical operators

Non-logical: not /! , Logical or: or/|, logical and: and /&&

③ Fuzzy query "like"

Wildcard: % any character, for example, where name like 'apple %'

_ Match a single character, for example, where name like 'apple __'

 

2. group by, which is generally used together with the statistical function (aggregate function ).

Five statistical functions of mysql:

① Max: calculate the maximum value. For example, query the highest price items under each category:

Select cat_id max (price) from goods group_by cat_id

② Min: Minimum value

③ Sum: sum. For example, select sum (goods_number) from goods

④ Avg: calculate the average value. For example, calculate the average price of each product category.

Select cat_id avg (price) from goods group_by cat_id

⑤ Count: calculate the number of items, for example, select count (*) from goods

Each field name can be understood as a variable and can be computed as follows:

Example: How much is the price of each product in our store lower than the market price; select goods_id, goods_name, goods_price-market_price from goods;

You can use as to alias the field name. If the aggregate function is not used before group by, the first row of data of each group is obtained by default.

 

3. Similarities and Differences between having and where

Having is similar to where. It can filter data. How to Write the where expression and how to write the having expression

Where queries data for columns in a table.

Having filters data for columns in the query results

# Query how much the price of our products is lower than the market price, and output products with a price lower than 200 yuan

Select goods_id, good_name, market_price-shop_price as s from goodshaving s> 200;

# If where is used, select goods_id, goods_name from goods where market_price-shop_price> 200;

# Use where and having at the same time

Select cat_id, goods_name, market_price-shop_price as s from goods where Cat _id = 3 having s> 200;

 

4, order

(1) order by price // sort by default in ascending order

(2) order by price desc // sort in descending order

(3) order by price asc // sort in ascending order, the same as the default

(4) order by rand () // random arrangement with Low Efficiency

# Sort by column number in ascending order, and sort the commodity prices under each column in descending order: select * from goods where cat_id! = 2 order by cat_id, price desc;

 

5, limit: limit [offset,] N

Offset. Optional. If this parameter is left blank, the offset is equivalent to limit 0 and N. N retrieve entries

# Select good_id, goods_name, goods_price from goods order by good_price desc limit 3;

 

Ii. mysql subquery

1. where subquery

(The inner query result is considered as a comparison condition for the outer query)

# Query the latest product without order

Select goods_id, goods_name from goods where goods_id = (select max (goods_id) from goods );

# Retrieve the latest product under each topic (goods_id is unique)

Select cat_id, goods_id, goods_name from goods where goods_id in (select max (goods_id) from goods group by cat_id );

 

2. from subquery (the query result of the inner layer is used for another query of the outer layer)

# Use A subquery to find out the average scores of students with two or more subjects

Idea: # first, find out which students have two or more subjects: select name, count (*) as gk from stu where score <60 having gk> = 2;

# Put the preceding query results as a table into the following query to select name from (select name, count (*) as gk from stu having gk> = 2) as t;

# Find out these students and calculate their average score

Select name, avg (score) from stu where name in (select name from (select name, count (*) as gk from stu having gk> = 2) as t) group by name;

 

3. exists subquery (obtain the outer query result to the inner layer to check whether the inner layer query is true)

# Query which columns have products, column table category, and item table goods

Select cat_id, cat_name from category where exists (select * from goods where goods. cat_id = category. cat_id );

If the item column id in the item table is equal to the id of a topic, it proves that there is a commodity under this topic, so these columns are established under the condition. Will be queried.

 

Iii. Usage of union

UNION is used to combine the results from many SELECT statements into a result set.

 

Iv. Internal Connection, left connection, right connection, and full connection

Table T1: Table T2:

Id name passwd id jifen dengji

1 jack jpw 1 20 3

2 tom tpw 3 50 4

1. inner connection

If you want to list user information, points, and levels, you will generally write as follows:

Normal: select * from T1, T2 where T1.id = T2.id

Inner connection: select * from T1 inner join T2 on T1.id = T2.id

The latter is much more efficient than the previous one. We recommend that you use internal connections.

 

2. Left join

Show all rows in T1 of the Left table, and add the qualified rows in T2 of the right table to T1 of the Left table;

If the table T2.

SQL statement: select * from T1 left join T2 on T1.id = T2.id

Running result:

T1.id name passwd T2.id jifen dengji

1 jack jpw 1 20 3

2 tom tpw NULL

 

3. Right join

Show all rows in the right table T2, and add the qualified rows in the left table T1 to the right table T2;

If the table T1 on the Left does not meet the conditions, it does not need to be added to the result table and is NULL.

SQL statement: select * from T1 right join T2 on T1.id = T2.id

Running result:

T1.id name passwd T2.id jifen dengji

1 jack jpw 1 20 3

NULL 3 50 4

 

4. Full connection

Display All rows on both sides of the Left table T1 and right table T2, that is, combine the left join result table and the right join result table, and then filter out duplicate rows. SQL statement:

Select * from T1 full join T2 on T1.id = T2.id

Running result

T1.id name passwd T2.id jifen dengji

1 jack jpw 1 20 3

2 tom tpw NULL

NULL 3 50 4

 

Iv. MySQL Indexes

1. What is an index?

An index is the value data structure of a specific column in a stored table (the most common is B-Tree ). An index is created on a column of a table. Therefore, the key point to remember is that the index contains the values of the columns in a table and these values are stored in a data structure. In the end, indexes are structured sorting of the values of Data columns. Remember this: an index is a data structure.

B-Tree is the most common data structure used for indexing. Because of their low time complexity, search, delete, and insert operations can be completed within the logarithm time. Another important reason is that the data stored in B-Tree is ordered. The Database Management System (RDBMS) usually determines which data structures should be used for indexing. However, in some cases, you can specify the data structure to be used when creating an index.

 

2. How does an index improve performance?

Because indexes are basically used to store the data structure of column values, this makes it faster to search for these column values. If the index uses the most common data structure-B-Tree-, the data in the index is ordered. Ordered column values can greatly improve performance. The following describes the cause.

Suppose we create a B-Tree index in the column 'employee_name. This means that when we use the previous SQL statement to find the employee whose name is 'Jesus', we do not need to scan the entire table. Instead, you can use index search to find the employees whose names are 'Jesus', because the indexes are sorted alphabetically. Sorting indexes means that the query of a name is much faster, because the employees whose first letter is 'J' are all arranged together. In addition, the index also stores the pointer of the corresponding row in the table to obtain data from other columns.

 

3. What exactly is stored in database indexes?

You now know that the database index is created on a column in the table and all values in this column are stored. However, it is important to understand that database indexes do not store the values of other columns (fields) in this table. For example, if we create an index in the Employee_Name column, the values on the columns 'employee_age 'and 'employee_address' are not stored in this index.

The index stores the pointer to a row in the table.

If we find the value of a record as the index column in the index, how can we find other values of this record? This is simple-database indexes store pointers to corresponding rows in the table at the same time. A pointer refers to a memory area that records the reference of the corresponding row of data recorded on the hard disk. Therefore, in addition to column values, indexes also store an index pointing to the row data. That is to say, the value (or node) of the column 'employee_name 'in the index can be described as ("Jesus", 0x82829 ), 0x82829 is the address of the row of data containing "Jesus" on the hard disk. Without this reference, you can only access a single value ("Jesus ").

 

4. How does a hash table index work?

A hash table is another data structure that you may see as an index-these indexes are generally called hash indexes. The reason for using the hash index is that the hash table is highly efficient when looking for values. Therefore, if you use a hash index, you can quickly retrieve the value of a query that compares the same strings. For example, the query we discussed earlier (SELECT * FROM Employee WHERE Employee_Name = 'Jesus') can benefit FROM creating a hash index on the column 'employee_name. The operating method of the HA index is to use the column value as the index key, and the actual value corresponding to the key value is a pointer to the corresponding row in the table. Because the hash table can basically be seen as an associated array, a typical data item is like "Jesus => 0x28939", and 0x28939 is a reference to the row containing the Jesus in the memory table. Query a value such as "Jesus" in the HA index and obtain the reference of the corresponding row in the memory, obviously, it is much faster than scanning the full table to obtain rows with a value of "Jesus.

 

5. disadvantages of hash Index

A hash table is a non-ordered data structure and does not support hash indexes of many types of query statements. For example, if you want to find all employees younger than 40 years old. How do you use Hash indexes for queries? Because the hash table is only suitable for querying key-value pairs-that is, the query is equal (for example, like "WHERE name = 'Jesus '). The key-value ing of a hash table also implies that the key storage is unordered. This is why the hash index is generally not the default data structure of the database index-because it is not as flexible as B-Tree when used as the index data structure

 

6. Clustered index:

The actual storage sequence structure is the same as the data storage physical structure. Therefore, generally, there is only one physical sequence structure, so there can be only one clustered index for a table, generally, the primary key is used by default. The system adds a clustered index to you by default. Some people say that I don't want to use the primary key as the clustered index. I need to use other fields as the index, of course, this is also possible. You need to manually add the unique clustered index before setting the primary key, and then set the primary key. This is a problem. All in all, clustering index is an index consistent with the physical structure of data storage, and the clustering index of a table can only have one unique one.

 

7. Non-clustered index:

The physical sequence and logical sequence of non-clustered index records are not necessarily related, and there is no relationship with the physical structure of data storage. There can be multiple non-clustered indexes corresponding to a table, non-clustered indexes with different requirements can be created based on the constraints of different columns.

 

8. Differences between primary key indexes and unique Indexes

A primary key must contain a unique index. A unique index is not necessarily a primary key.

The unique index column allows null values, while the primary key column does not.

When a primary key column is created, the default value is null + unique index.

A primary key can be referenced as a foreign key by other tables, but a unique index cannot.

A table can only create one primary key, but multiple unique indexes can be created.

Primary keys are more suitable for unique identifiers that are not easy to change, such as auto-incrementing columns and ID card numbers.

 

Summarize the index usage principles:

1: Do not index tables with a small amount of data. For small tables, the cost of table scanning is not high.

2: Do not set too many indexes. In a table without clustered indexes, you can set up to 249 non-clustered indexes. Excessive indexes will first increase disk space, in addition, the maintenance of indexes especially consumes performance when data is modified.

3: Apply composite indexes properly. In some cases, you can consider creating an overwriting index that contains all output columns.

4: Clustering indexes may be considered for fields frequently used for range query.

5: Avoid creating indexes for infrequently used columns, logical columns, and large field columns.

MySQL uses indexes only for the following operators: <, <=, =,>,> =, between, in, and sometimes like (not starting with a wildcard "%" or)

 

Source [region

Thank you for sharing your feedback!