MySQL Common skills sharing

One, MySQL queries the five seed sentence:

1,where (conditional query), commonly used operators:

① comparison Operators

>, <, =,! =, >=, <=, in (), between Val1 and Val2

② logical operators

Logical non: NOT/! , logical OR: OR/| | , logic with: and/&&

③ fuzzy query "like"

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

_ matches a single character, for example: where name like ' Apple __ '

2,group by (grouping), typically used with statistical functions (aggregate functions)

MySQL Five statistical functions:

①max: Ask for the maximum value, for example, to query for items with the highest price below each category:

Select cat_id Max (price) from goods group_by cat_id

②min: To find the minimum value

③sum: Find sum, for example: select SUM (goods_number) from goods

④avg: Averaging, for example: the average price of an item for each category

Select cat_id avg (price) from goods group_by cat_id

⑤count: Ask for the number of items, for example: SELECT COUNT (*) from goods

Each field name can be interpreted as a variable, and it can be operated on:

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

You can alias a field name with AS, and if you do not use an aggregate function in front of group by, the first row of data for each group is taken by default

The similarities and differences between 3,having and where

Having, like where, can filter the data, how to write the expression after the where, and how to write the expression after having a

Where the columns in the table work, querying the data

Having a function for the columns in the query results, filtering the data

#查询本店商品价格比市场价低多少钱, output less than 200 yuan of goods

Select -  as  from  having s> ;

#如果用where的话则是:

Select  from where - > ;

#同时使用where与having

Select -  as  from where = 3  having > ;

4,order by

(1) Order BY price//default ascending order

(2) Order BY price desc//Descending order

(3) Order BY price ASC//ascending order, as default

(4) Order by RAND ()//random arrangement, not high efficiency

#按栏目号升序排列, the prices of commodities under each column are listed in descending order:

Select *  from where != 2 Order  by desc;

5,limit:limit [Offset,] N

Offset offsets, optional, no write is equivalent to limit 0,n. N Remove Entry

#取价格4-6 High product

Select  from Order  by desc 3,3;

Two, MySQL sub-query

1,where Sub-query

(The inner query result is treated as the comparison condition of the outer query)

#不用order by to find the latest products

Select  from where = (selectmax from goods);

#取出每个栏目下最新的产品 (goods_id only)

Select  from where inch (selectmaxfromgroup by cat_id);

2,from Sub-query (the inner query results for the outer layer again query)

#用子查询查出挂科两门及以上的同学的平均成绩

Idea: #先查出哪些同学挂科两门以上

Select name,count(*as fromwhere<  having >= 2;

#以上查询结果当做一个表放入下面的查询中去

Select  from (Select name,count(*as from have>=2  as T;

#找出这些同学了, then calculate their average score.

Select name,avgfrom thewhere in (select name,count(*as from having>=2  as Group  by name;

3,exists Sub-query (the outer query results to the inner layer, to see if the inner layer of the query is established)

#查询哪些栏目下有商品, column list category, Commodity table goods

Select  from where exists (Select*fromwhere= category.cat_id);

If the item ID of the product in the product table is equal to the ID of a certain column, then it proves that there is a product under the column, so these columns are established under the conditions. will be queried.

Third, the use of Union

Union is used to combine the results from many SELECT statements into a single result set

Four, inner connection, left connection, right connection, full connection

Table T1:                     table t2:id name passwd        ID jifen dengji1  jack   jpw             1  Tom   TPW  3    4

1, internal connection

If you want to list the user information, points, ratings, then generally write: normal: SELECT * from T1, T2 where t1.id = t2.id in: SELECT * from T1 inner join T2 on t1.id = t2.id Efficiency is much higher than the previous efficiency, it is recommended to use the internal connection

2, left connection

All rows in the left table T1 are displayed, and the matching conditions in the right table T2 are added to the left table T1, and the right table T2 does not fit in the result table, and the null representation is not included.　SQL statement: SELECT * from T1 LEFT join T2 on t1.id = t2.id Run Result: t1.id name  passwd  t2.id  jifen   dengji   1    Jack    JPW    1    3   2       Tom    tpw      null   NULL NULL   

3, right connection

All rows in the right table T2 are displayed, and the T1 in the left table is added to the right table T2, and the left table T1 does not fit in the result table, and Null is represented. SQL statement: SELECT * from T1 right join T2 on t1.id = t2.id Run Result: t1.id   name   passwd   t2.id   jifen   dengji< C25/>1        Jack      jpw         1      3   null null        3      4

4, fully connected

Display the left table T1, the right table T2 all the rows in both sides, that is, the leftist result table + right-side result table is combined, and then filter out the duplicate. SQL statement: SELECT * from T1 full join T2 on t1.id = t2.id run result   t1.id   name   passwd t2.id jifen   dengji
    1         Jack      jpw        1    3   2         Tom      tpw     null   NULL NULL   NULL   NULL        3      50      

Four, the MySQL index

1, what is an index

An index is a data structure for a particular column in a stored table (the most common is b-tree). The index is created on the columns of the table. So, the key to remember is that the index contains the values of the columns in a table, and these values are stored in a data structure. The bottom line is that an index is something that is structured to sort the values of a data column. Remember this: An index is a data structure

B-tree is the most commonly used data structure for indexing. Because they are low in time complexity, find, delete, and insert operations can be done in logarithmic time. Another important reason is that the data stored in the B-tree is ordered. A database management system (RDBMS) typically determines which data structures should be used for indexing. However, in some cases, when you create an index, you can specify the data structure to use for the index.

2, how does the index improve performance?

Because the index is basically the data structure used to store column values, this makes finding these column values faster. If the index uses the most frequently used data structure-b-tree-then the data in it is ordered. An ordered column value can greatly improve performance. The reasons are explained below.

Suppose we create a b-tree index on the employee_name column. This means that when we use the previous SQL to find the employee whose name is ' Jesus ', we do not need to scan the whole table again. Instead, an index lookup is used to find an employee whose name is ' Jesus ' because the index is sorted alphabetically by alphabetical order. The index is sorted, which means that querying a name is much faster because the employees with the first letter ' J ' are all lined up. Another important point is that the index stores a pointer to the corresponding row in the table to get the data for the other columns.

3, what exactly is stored in the database index?

You now know that a database index is created on a column of a table and stores all values for that column. However, it is important to understand that database indexes do not store values for 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 the index.

Index stores a pointer to a row in the table

How can we find other values of this record if we find the value of a column in the index as an index? This is simple-the database index stores a pointer to the corresponding row in the table. A pointer is a piece of memory area that records a reference to the data of the corresponding row recorded on the hard disk. Therefore, in addition to the value of the stored column in the index, an index that points to the row data is stored. That is, a value (or node) of the Employee_Name column in the index can be described as ("Jesus", 0x82829), and 0x82829 is the address of the data on the hard disk that contains "Jesus". Without this reference, you can only access a single value ("Jesus"), which makes no sense

4, how does the hash table index work?

A hash table is another form of data structure that you might see as an index-these indexes are often referred to as hash indexes. The hash index is used because the hash table is extremely efficient when looking for a value. Therefore, if you use a hash index, queries that compare strings for equality can retrieve the values very quickly. For example, the query we discussed earlier (SELECT * from Employee WHERE employee_name = ' Jesus ') can benefit from the hash index created on the Employee_Name column. A HA index works by the value of the column as the key value (key) of the index, and the corresponding actual value (value) of the key value is a pointer to the corresponding row in the table. Because a hash table can basically be thought of as an associative array, a typical data item is like "Jesus = 0x28939″", and 0x28939 is a reference to a row in the in-memory table that contains Jesus. Querying a value like "Jesus" in a ha index and getting a reference to the corresponding row in memory is significantly faster than scanning the entire table for rows that have a value of "Jesus".

5, disadvantages of hash index

A hash table is an order-free data structure that is powerless for many types of hash indexes on query statements. For example, if you want to find all employees younger than 40 years old. How do you query using a hash index? Because hash tables are only suitable for querying key-value pairs-that is, queries that query for equality (example: like "WHERE name = ' Jesus '). The hash table's key-value mappings also imply that the storage of its keys is unordered. This is why a hash index is not usually the default data structure for a database index-because it is not as flexible as the data structure of the index B-tree

6, clustered index:

The actual stored order structure and the physical structure of the data storage is consistent, so usually the physical order structure only one, then the clustered index of a table can only have one, usually the default is the primary key, set the primary key, the system by default you add a clustered index, of course, some people say I do not want to take the primary key as a clustered index I need to use a different field as an index, which is also possible, which requires you to manually add the unique clustered index before setting the primary key, and then set the primary key, so there is a problem with the wood. In summary, a clustered index is an index in which the sequential structure is consistent with the physical structure of the data store, and the clustered index of a table can only have a single bar.

7, non-clustered index:

The physical order of the non-clustered index records is not necessarily related to the logical order, and it has no relation with the storage physical structure of the data; a table corresponding to a nonclustered index can have multiple, according to the constraints of different columns can establish different requirements of non-clustered index.

8, the difference between a primary key index and a unique index

The primary key must be created with a unique index, and the uniqueness index is not necessarily the primary key.

The uniqueness index column allows null values, while primary key columns are not allowed to be null.

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

A primary key can be referenced by another table as a foreign key, and a unique index cannot.

A table can create a maximum of one primary key, but multiple unique indexes may be created.

Primary keys are more suitable for unique identities that are not easily changed, such as auto-increment columns, Social Security numbers, and so on.

Summarize Index usage principles:

1: Do not index the data volume of the table, for the small table, the cost of table scanning is not very high.

2: Do not set too many indexes, in a table without a clustered index, the maximum can be set 249 nonclustered indexes, too many indexes will first bring more disk space, and when the data changes, the maintenance of the index is particularly performance-consuming.

3: Rational application of composite indexes, there are cases where you might consider creating an overlay index that contains all the output columns.

4: Clustered indexes may be considered for fields that frequently use range queries.

5: Avoid creating indexes on columns that are not commonly used, logical columns, and large fields.

MySQL uses the index only for the following operators: <,<=,=,>,>=,between,in, and sometimes like (cases that do not start with a wildcard character% or _)

SOURCE [http://www.cnblogs.com/rollenholt/archive/2012/05/15/2502551.html],[http://blog.csdn.net/weiliangliang111/ ARTICLE/DETAILS/51333169]

Thank the above Bo friends to share!

MySQL Common skills sharing