Knowledge points and details of mysql

I read the mysql system again these days, so I can learn more about it ......

1. Differences between char and varchar types

• A char fixed length string is the length declared when the table is created (0-255). If the length is insufficient, spaces are filled on the right to reach the declared length. When the CHAR value is retrieved, the trailing space is deleted.
• Varchar variable-length string (0-65535). When the VARCHAR value is saved, only the number of characters is saved, and a byte is added to record the length (if the length declared by the column exceeds 255, it uses two bytes). The varchar value is not filled when it is saved. When the value is saved and retrieved, the trailing space is retained.
• They are retrieved in different ways, and char speed is relatively faster

Note that their length is expressed as the number of characters, both in Chinese and English.

Text Search is a little slow, so if it is not very large, use char/varchar. In addition, the default value cannot be added to text.

2. In windows, the mysql client contains garbled content. This is because the system code is gbk, And you can simply send a "set names gbk" statement before using it.

3. decimal: float (M, D), double (M, D), decimal (M, D) M represents the total number of digits, excluding the decimal point, D represents the decimal point, such as (5, 2) -999.99 -- 999.99

MIs the total decimal digits,DIs the number of digits after the decimal point. IfMAndDOmitted. Save the value according to the restrictions allowed by the hardware. The single-precision floating point number is precise to about 7 decimal places.

4. the combination of group by and Aggregate functions makes sense (sum, avg, max, min, count). The principle of group by is that all columns after select, columns without Aggregate functions must appear after group by. If not, only the results of the first row in each type are returned.

If you want to query the most expensive products under each topic: id, price, product name, type. Use select goods_id, cat_id, goods_name, max (shop_price) from goods group by cat_id;The product name and id of the query result do not match the most expensive product. If order by is added? This is also incorrect because the Select statement is executed in sequence (before and after the syntax ):

• The where clause filters record rows based on specified conditions;
• The group by clause divides data into multiple groups;
• Use Aggregate functions for computation;
• Use the having clause to filter groups;
• Calculate all expressions;
• Use order by to sort the result set.

You can use the following query statement: select * from (select goods_id, cat_id, goods_name, shop_price from goods order by cat_id asc, shop_price desc) as tmp group by cat_id;

5. the having clause is slower than the aggregate statement (sum, min, max, avg, count) in the query process ). the where clause is faster than the aggregate statement (sum, min, max, avg, count) in the query process ).
Simply put:
Where clause:
Select sum (num) as RMB from order where id> 10
// Only records with IDs greater than 10 can be queried for aggregation.

Having clause:
Select reportsto as manager, count (*) as reports from employees
Group by reportsto having count (*)> 4

The following statement is incorrect:
Select goods_id, cat_id, market_price-shop_price as sheng where cat_id = 3 where sheng> 200; should be changed:
Select goods_id, cat_id, market_price-shop_price as sheng where cat_id = 3 having sheng> 200; where play a role in the column in the table, query data, having play a role in the query results column, filter data

Take a look at the following interview question:

There is a table below

Query: average score of two or more failed students

The following statement is used at the beginning: select name, count (score <60) as s, avg (score) from stu group by name having s> 1; this statement cannot work.

First, clarify the following two points:

No matter what the, count (exp) parameter is, the number of rows queried is not affected by the parameter results, as shown in figure

B,

You can use the following statement to replace count with sum:

Or: select name, avg (score) from stu group by name having sum (score <60)> 1;

6. subquery

A. where subquery: use the results of the inner query as the comparison condition for the outer query. Eg: query the latest products

Select max (goods_id), goods_name from goods; error: Mixing of GROUP columns (MIN (), MAX (), COUNT (),...) with no GROUP columns
Is illegal if there is no group by clause

You can use this query: select goods_id, goods_name from goods where goods_id = (select max (goods_id) from goods );

Query the latest items in each column: select goods_id, cat_id, goods_name from goods where goods_id in (select max (goods_id) from goods group by cat_id );

B. from subquery: Use the internal query result as a temporary table for the outer SQL to query again (an alias must be added to the temporary table)
Query the latest item under each column select * from (select goods_id, cat_id, goods_name from goods order by cat_id asc, goods_id desc) as t group by cat_id;
5. select sname from (select name as sname from stu) as tmp for the average score of two or more students involved in the query;

C. exists subquery: obtains the result variable of the outer query to the inner layer to check whether the inner layer query is true.

Query the columns with products: select cat_id, cat_name from category where exists
(Select * from goods where goods. cat_id = category. cat_id );

Because there are no conditions, all columns will be identified: select cat_id, cat_name from category where exists (select * from goods); in can also be used

7. in (v1, v2 -----) between v1 and v2 (including v1, v2) like (%, _) order by column1 (asc/desc), column2 (asc/desc) first sort by the first, then sort by the second

8. union combines two or more query results

• The number of columns in the two queries is the same, and the type of the corresponding columns is the same.
• When the column names are inconsistent, take the column names of the first SQL statement
• If the values of rows retrieved from different statements are the same, the same rows will be merged (repeated). If union all is not reused
• If the clause contains order by, the limit clause must add (),

Select * from ta union all select * from tb;
Take the fourth column of goods, prices in descending order, also want to take the Fifth Column of goods, prices are also sorted in descending order
(Select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 4 order by shop_price desc) union (select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 5 order by shop_price desc );
It is recommended to put it after all clauses, that is, sort the final merged results.
(Select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 4 order by shop_price desc) union (select goods_id, cat_id, goods_name, shop_price from goods where cat_id = 5 order by shop_price desc );
9. Connection Query
Left join:
Select column1, column2, columnN from ta left join tb on ta column = tb column [the table is connected to a large table, which is regarded as a normal table]
Where group, having... write as usual
Right join:
Select column1, column2, columnN from ta right join tb on ta column = tb column [the table is connected to a large table, which is regarded as a normal table]
Where group, having... write as usual
Internal Connection:
Select column1, column2, columnN from ta inner join tb on ta column = tb column [the table is connected to a large table, which is regarded as a normal table]
Where group, having... write as usual

The left join is based on the left table. The right table is used to find matching data. If no matching columns exist, null is used to complete the query. Multiple columns are listed.

If there are two tables:

Select boy. *, girl. * from boy left join girl on boy. flower = girl. flower;
Result:

Left-side connections can be converted to each other. We recommend that you use left-side connections to facilitate database migration.
Inner join: queries data in both left and right tables (intersection of left and right connections) and selects a paired combination.

The Left or Right join query specifies the data of the table to be queried, and the default (Left or Right join is not specified) determines the column data in both tables, that is, inner join.

Mysql does not support outer join, that is, the Union of left and right connections.

When fields in multiple tables are specified

Query brands, goods, and category in three tables
Select g. goods_id, cat_name, g. brand_id, brand_name, goods_name fromGoods g left join brand B on B. brand_id = g. brand_idLeft join category c on g. cat_id = c. cat_id;

10. transaction: Engine innodb acid

Start transaction;

SQL statement

Commit (submit)/roolback (rollback)
Note that some statements may cause implicit transaction commit, such as start transaction.
11. mysql built-in tool for database backup and recovery: mysqldump
The following table shows the exported database t:
Mysqldump-u username-p Password Database Name Table 1 Table 2 table n> address
Eg: mysqldump-u root-p 123456 test boy> d: \ boy. SQL
Export all tables:
Mysqldump-u user name-p Password Database Name> address
Export in database units:
Mysqldump-u username-p password-database B database 1 Database 2 database n> address
Export all databases
Mysqldump-u username-p password-A> address

Recover database source Address
12: stored procedure: encapsulate a piece of code. When you want to execute this piece of code, you can call this stored procedure.
In the encapsulated statement body, you can use the if/else, case, while and other control structures.
SQL programming
Show all stored procedures: show procedure status;
Drop procedure name delimiter $

Create procedure p1 ()
Begin
Select * from boy;
End $
Call: call name ()
Create procedure p2 (num int)
Begin
Select * from boy where id> num
End $

Create procedure p2 (num int, level char (1 ))
Begin
If j = 'H' then
Select * from boy where id> num
Else
Select * from g where id <num;
End if;
End $

13. view a virtual table formed by query results

If a query result appears frequently, that is, this result is used as a subquery frequently, you can make this result into a view.
Create view name as select statement
14. trigger: trigger, which monitors a situation and triggers an operation
Four elements: Monitoring location (table), monitoring event (insert/update/delete), triggering time after/before, and triggering event (insert/update/delete)
Syntax: create trigger triggerName
After/before insert/update/delete on table name
For each row
Begin
SQL statement # One or more statements (insert/update/delete), end;
End;
How to reference the value of a row in a trigger
Insert: The new row is represented by new, and the values of each column in the row are represented by new. column names.
For delete: The deleted row is represented by old, and the value of each column in the row is represented by the old. Column name.
For update: the value of the modified row is represented by old, and the modified row is represented by new
Order table and commodity table
# Buy three lambs
# Monitoring location: o table
# Monitoring operation: insert
# Trigger cheer: update
# Trigger time: after
Delimiter $
Create trigger tg1
After insert on order
For each row
Begin
Update goods set num = num-new.much where id = new. gid; # much/gid is the column in order
End $

Before is verified before performing some operations
Create trigger tg1
Before insert on order
For each row
Begin
If new. much> 100 then
Set new. much = 5;
End if;
Update goods set num = num-new.much where id = new. gid; # much/gid is the column in order
End $