MySQL focus, views, transactions, backup Restore "mysqldump", MySQL programming "triggers, storage functions, stored procedures", storage engine

1. Issues needing attention when deleting data "Delete Record Delete" Delete the data itself does not have much to say, or delete the table when there is truncate usage delete from is deleted record by article "Plus no conditions all deleted, inefficient, in the delete table aspect" Truncate is the reconstruction table " High efficiency when deleting tables "

2. Modify the data "update"

"Focus on performance"

3. View "is a virtual table: make the client operate in a virtual table" view is the result of a query statement "because the result of the query itself will form a table to the client" "View using the built-in mechanism of MySQL" CREATE VIEW name as query statement; " This is the syntax of the above view, according to a query statement to form a table "" View function is to facilitate the generation of a table, and then directly to the table operation can be: is actually a subquery (the view itself does not have data). Just have the view this function, the MySQL mechanism simplifies the sub-query command spelling difficulty "

Then this generated view as a normal data table, you can directly operate "note this" but the view is not to save the real data, there is only one view of the build view of the SELECT statement, each time from the view query, you need to use the view, in the real table to find. So that looks like a subquery for two queries "

"View Focus:" View is difficult to improve performance, there are two algorithms, but the performance improvement is not obvious "" http://wangyuanzju.blog.163.com/blog/static/130292007714102859807/ The algorithm after using the view is not compatible with the aggregation function and the like "" http://www.jb51.net/article/36363.htm about the use of views "

View algorithm, the Problem "(2) temptable" occurs when you specify an algorithm to implement algorithm "(1) Merge" statement for a subquery after the view is created, and the issue "()" is equivalent to handling the subquery as a temporary table " (3) Undefined: undefined "MySQL self-determined use algorithm"

4. The transaction "Focus" transaction a set of SQL collections, with all successful execution or collective failure. A business logic requires multiple SQL to guarantee execution.

Syntax: Open transaction: Start transaction; "or use Begin" "To log SQL statements and then execute these SQL commits: commit; If all SQL executes successfully, commit. Persist the results of SQL execution into the data table! Persistence: The result of SQL execution is not written to the data table, but the space that MySQL takes to execute the results separately, only all SQL execution completes, persisting the result to the data table "rollback: rollback;" If there is a failure in SQL execution, you need to roll back. Will be returned to the entire transaction before opening "

The above three grammars are used in order to ensure the proper use of the entire transaction. "Three grammars need to be combined to implement a transaction mechanism"

If you do not declare a transaction, MySQL is automatically committed to the SQL command by default. "So turn on the transaction is to turn off the auto-commit feature, and need to use manual submission!!!" "" "The autocommit variable is the associated configuration set sutocommit = 0; You can implement the set variable (set names encoding is also the reason)" The transaction is supported by the InnoDB engine.

Characteristics of the transaction: ACID atomicity: Transactions are indivisible consistency: ensure that data is consistent throughout the execution cycle of a transaction "data is placed in the execution result alone and does not affect" isolation: interference between multiple transactions is isolated "isolation level persistence: Once a transaction is committed, it is not rolled back

"Http://blog.csdn.net/forever_feng/article/details/4368003"

5. Backup restore (1) Simple data Backup "single-table backup" stores the data that is queried through a SELECT statement in the form of a text file (but the text cannot exist, equivalent to being created at the time the statement is executed), and is stored "SELECT INTO outfile filename from ..." Control the format of the generated file by controlling the spacing symbols in the file.

You can also restore "load data from a file into a library" load data infile ' file address ' into ' table name '; "Implement to import information from a file into the database"

(2) "mysqldump" This tool integrates these backup and restore "equivalent to a special client (not related to the MySQL client), after connecting with the MySQL server" Mysqldump is the executable file under the bin file of MySQL: mysqldump start to implement backup and restore Operations "backup generates SQL statement" note "" Using these SQL statements requires MySQL to execute the source command "

(3) Directly copy the data file for backup "files in the relevant library under the data directory for copy backup: But not every time, the MyISAM engine can"

6. Trigger "programmed" when an operation occurs, an event that is absolutely triggered "event triggering scenario similar to JS" is a programming design that is programmed by "registering events" at the beginning of a definition, and then listening to events after the program has started, thus achieving a description of what happens after the event occurs " Implementing programmatic control after an event occurs "

Syntax: CREATE TRIGGER Trigger name trigger condition, listen content, triggered after the action trigger is a table-related named Database object, when a particular event occurs on the table, fires the object "Before/after insert/delete/update" Six events for example: Create TRIGGER test_trigger//trigger name after insert//trigger condition on select_student for each row//listener object insert INTO Student_lo G values ()//triggered after the operation "a set of SQL Set";//complete the definition of the trigger

"In addition the keyword for the object that marks the event that triggers the action: New, old to mark the action of the Record"

"You need to be aware of the implementation scenarios for triggers" "triggers are composed of multiple SQL statements that need to use begin/end to compose multiple statements into a single combination"

There can only be one trigger on a table at a time "this natural" delete trigger drop trigger but an SQL statement may trigger multiple triggers "because the SQL statement may operate on more than one table, the execution of the trigger is raised"

Branches in 7.mysql "used for SQL combination": The terminator of the statements in the branch that need to execute SQL and the semicolon of the syntax itself "resolves this issue by modifying the outermost statement terminator: delimiter"

8. Custom Function SQL Programming problem "SQL is also a programming language: So the elements of programming-variables, operators, expressions, process controls, functions also exist" MySQL programming usage scenarios: triggers, stored procedures, stored functions (custom functions)

Variable: is the field name "This point of understanding to note" "There is also a special variable, which is the system custom variable: Set names GBK (names is a variable name) This is the custom setting of the variable" "gets the syntax of the variable value: Select Variable Name" " The variables here take into account the use of custom functions, you also need to pay attention to the global and local, global variables are local changes need to be referenced (the global variable definition needs to use @ to declare) so the scope of the two are differentiated, but can be directly modified. Different from PHP, but consistent with JS "

Function: The built-in function of the MySQL system itself "aggregate function is" "to pay attention to the problem of global and local variables" "Custom Function" "Create function Function_name () returns int//here returns is because of the SQL strong type Begin function Body End "There is no curly braces in MySQL to include examples of function bodies: delimiter $$//in order to not conflict with semicolons in internal SQL create function Hello_world () returns varchar ( Begin--Here is the comment statement in SQL return ' Hello World '; End $$ delimiter; The above creates a custom function called and the normal aggregate function "use all the same" note that the custom function here is related to the database at the time of customization, and cannot be called after switching the library "the type of the parameter needs to be noticed when the function is called." "So this function is also called a storage function"

The process Control "branching and looping" process control in MySQL is also used in MySQL programming, so there are only three scenarios where the "stored procedure" storage function "trigger" is used.

The above is the knowledge point of MySQL custom function

9. The stored procedure "is similar to a stored function and is also a collection code for a functional module. The difference is that the function tends to a function point.    While stored procedures tend to the overall implementation of a business logic "business logic is more inclined to represent the execution of certain operations, and the function point of the functions represents the ability to call syntax by any business logic: delimiter $$ CREATE PROCEDURE hello () beigin SQL Set body end $$ delimiter;

The call is not invoked directly using the procedure name, but it needs to be called using call "Note this"

Parameters in stored procedure: input "Incoming stored procedure" in output "passed out from stored procedure" out input output "simultaneous completion of input and output" inout

Used Location: Delimiter $$ CREATE PROCEDURE Say_hello (in name varchar (10))//Here are the restrictions on the parameters. In begin select Concat (' Hello ', name); End $$ delimiter;

Call Say_hello (' itcast ');//Here Pass "in" ... Pager

' Out ' delimiter $$ CREATE PROCEDURE Say_hello (in n int,out R int) Begin--The SQL collection of the process end $$ delimiter; Call Say_hello (' Itcast ', @r);//Note that the use of the @r called here itself has no value, which is used to receive the results of procedure processing

"InOut itself is a scenario where a variable is processed in a process. syntax is similar to in, but can receive results "

10. The storage engine storage engine is mainly about the format of data on the MySQL server "very many" MyISAM "three files:. FRM (structure file)/.myd (data file)/.myi (data index file)" InnoDB "Current Default" ". frm. The data and indexes of the InnoDB tables are managed uniformly. In the Ibdata file "" Actually InnoDB data created two files "memory merge

MyISAM and InnoDB differences: (1) The number of files created and the path inconsistency (2) InnoDB support foreign keys, transactions "to ensure data integrity and rationality" (3) MyISAM on the query function optimization "InnoDB is also in development"

Select the engine to determine the basis: (1) The first reference function (2) To delete and modify the operation more use InnoDB, query multi-use MyISAM

11.root password problem use MySQL; Find the User table copy the other user table three files to the MySQL directory "MyISAM mode storage"

Or in the MYSQLD server program, there is an option "--skip-grant-table" to bypass permission authentication, and then update the user table with update to implement the password update MySQL internal cryptographic function "password ()" Can

The above is the basic knowledge of MySQL

MySQL focus, views, transactions, backup Restore "mysqldump", MySQL programming "triggers, storage functions, stored procedures", storage engine