MySQL Advanced programming optimization

★ Programming Optimization
One, character encoding (MySQL console garbled output resolved: character_set_results= ' GBK ')
Table/Column encoding settings
Columns: ALTER TABLE name change column list name data type character Set character set name
Table: ALTER TABLE name character Set UTF8
Server encoding Settings
Multi-table structure settings
Second, the View

① Introduction
• What is a view? A view is a virtual table formed by the results of a query. The primary function of a view is to query.
• When to use a view? If a query result occurs very frequently, that is, you should always take this query result to make subqueries.
• Benefits of using Views: Simplify query statements, allow permission control, large data table sub-table
• View-to-table relationships: Views are the result of a table's query, and the data for the natural table changes, affecting the view's results.
• Can I increase and revise the view of the operation?
The addition and deletion of the 1> view will also affect the table;2> but the view is not always modifiable; The view is modified when it corresponds to the table's data one by one and should be noted for insert on the view; The view must contain a column that does not have a default value in the table.
② Create
create [or Replace] [algorithm={undefined|merge|temptable}]
View view_name[(colum_list)]
As Select_statement
[with[cascaded|local] Check option]
③ Management
• Where the view is stored
SELECT * from Iinformation_schema.views; The location where the query view is stored; show tables; you can also view
• View definition: Show Table status from view name like ' View name ' \g
• Delete view, only delete definition of view, cannot delete data, must have drop permission
Drop view if exists view name
Update view name set salary=1500 where emp_no=1001; update emp_no=1001 wages in the view;
• Some views are updatable, that is, you can use them in statements such as update, delete, or insert to update the contents of the base table, and for updatable views, you must have a one-to-one relationship between the rows in the view and the rows in the base table, including the following views that are not updatable
1> aggregate function sum () min () max () count ()
2>distinct
3>group by
4>having
5>union or UNION ALL
6> subqueries in the select list
7>join
Non-updatable views in the 8>FROM clause
9>algorithm=temptable (using temporary tables always makes the view non-updatable)
• about the understanding and application of with CHECK option?
Modifications made through the view must also be able to see the modified results from that view.
Three, variable and process control
• Local variables: Local variables are defined in stored procedures, so local variables are valid only in stored procedures and other scopes are not valid.
Use companys;
Delimiter $$;
CREATE PROCEDURE P_vartest ()
Begin
Declare a varchar (default ' ABC ');
Select a
End
$$
Delimiter
Call P_vartest
• Session Variables: When the client is associated with the server retransmitting, the variables you set are the variables owned by the client, which are session variables.
View all session variables show session variables;
Show session variables like ' auto% ';
When the a client and the B client operate the MySQL database at the same time, the respective set of variables only work for themselves, do not interfere, and so the next time you operate MySQL, MySQL will put some of its own variables on the client.
To modify the session variable method:
SET @ @session. autocommit= ' off ';
Set Variable name = ' some value ';
• Global variables: Either Sessiona or SESSIONB, as long as you make changes to the MySQL global variables, both a access and B access are accessing the same variable values.
View all global variables
Show global variables;
View a global variable
Show global variables like ' auto% ';
Setting Global variables
SET @ @global. Variable name = a value
• SELECT statements
Use companys;
Delimiter $$;
CREATE PROCEDURE P_showage (in age int)
Begin
If Age>=18 Then
Select ' Adults ';
Else
Select ' Minors ';
End If;
End
$$

MySQL Advanced programming optimization