Python full stack database (ii) MySQL database advanced

MySQL Advanced table: Join up and down table: Union #自动去重 (when two tables of data, there are duplicates will automatically go to the weight) UNION ALL #不去重例如: Select Sid,sname from Snameunionselect ti D,tname from Teacherselect sid,sname to Studentunion allselect sid,sname from Student1, view (not commonly used, not long in development, written in development statements, Do not write in the database) view is a virtual table (non-real), the essence is "to get dynamic datasets based on SQL statements and named", users can only use the "name" to obtain the result set, and may be used as a table. In a physical table, a new virtual table is regenerated from the information that is queried by a condition, and the table is taken from the physical table in real time by the statement created. Views cannot be modified, the original physical table data of the view corresponding changes, depending on the data in the chart will also change!
CREATE VIEW: Syntax: CREATE VIEW name as SQL Statement Example: CREATE VIEW v1 as SELECT * from teacher where Tid >2; Delete view: Drop View name change view: syntax: Alter View view name as SQL statement changing views actually changes the corresponding SQL statement using the view: When using a view, use it as a table, and because the view is a virtual table, you cannot use it to create, update, and delete the real table, only to query. SELECT * FROM V1;2, triggers (the query does not raise this action!) When a row on a table, do the additions and deletions to change the operation, can be completed before or after the execution of a certain action when the row on a table, do additions and deletions, you can use the trigger to customize the association behavior when there are related operations, each time the relevant operation, the trigger will also be executed once. Trigger actions, which are triggered before execution and executed! Create TRIGGER tri_after_update_tb1 after update on TB1 for each row syntax: Increment # Before performing an add operation: Create TRIGGER operation name (custom, not repeating) before in SERT on the table name to manipulate for each rowbegin function body, the statement that performs the SQL Operation end# after the add operation: Create trigger operation name (custom, not duplicates) after insert on the table name to manipulate for each RO Wbegin function body, execute SQL Operation statement end

Delete # before deleting: Create trigger operation name (custom, not repeating) before drop on the table name to manipulate for each rowbegin function body, the statement that performs the SQL operation is end# after the delete operation: Create Trigger Operation name (custom, non-repeating) after drop on the table name to manipulate for each rowbegin function body, the statement that executes the SQL operation changes # before the change operation: Create trigger operation name (custom, not repeat) before UPDA Te on the name of the table to manipulate for each rowbegin function body, the statement that performs the SQL Operation end# after the change operation: Create trigger operation name (custom, do not repeat) after update on the table name to manipulate for each ROWB Egin function Body, the SQL Operation statement end before defining the trigger, you can first terminate the SQL statement with a semicolon-triggered operation to another symbol, and then change back when there are multiple change statements in the function body, each statement needs to end with a semicolon.
　　　　　　 If you want to not conflict with the system, use delimiter to change the punctuation that the SQL operation terminates, after the modification, and so on until the end of the operation to change back to the original semicolon ";" cannot affect other operational behavior such as: delimiter//Trigger example: Delimiter//create trigger T 1 before insert on student for each rowbegininsert to teacher (tname) values (new.sname); insert into teacher (Tname) values (New.sname); INSERT into teacher (tname) values (new.sname); INSERT into teacher (tname) values (new.sname); END//delimiter; insert into student (Gender,class_id,sname) VALUES (' Male ', 1, ' Chen xx '), (' Female ', 1, ' Li xx '); New executes the corresponding action outside the trigger, and the data in the newly inserted code! Used to insert new data into inserts into the insert to get the content of the old on behalf of the trigger to perform the corresponding action, corresponding to the original code to change or to delete the data! Commonly used for deleting and changing drop and update delete triggers: Drop trigger trigger name 3, Function function execution method: Select Function name built-in function time Format function date_format () syntax: Date_format (Time, Format operation time structure, different placeholders) for example: Date_format (CTime, "%y-%m") displays the year and month custom functions (which exist in the database, are set up, are called directly in Python) syntax: The data type is mandatory, what type is specified at the time of creation, You can only pass in what type of CREATE function name (parameter 1 type 1 parameter 2 Type 2 ...) Returns data type #指定函数返回值的数据类型begindeclare (declared variable) variable name data type default initializer function body, statement end example of SQL operation: Delimiter \create function F1 (I1 in T,I2 int) returns intbegindeclare num int;set num = i1 + i2;return (num);END \delimiter; Note: The function must have a return value inside the function cannot write a SELECT * from TB1 This SQL statement delete function: Drop functions name 4, stored procedure stored procedure is a collection of SQL statements, when the active to call the stored procedure, The internal SQL statements are executed in logical order. Stored procedures can write SQL statements!!! is an alias stored on MySQL, he refers to a lot of SQL statement operations, as long as the use of this alias can be found in the results! Used instead of the programmer to write SQL statements in order to be more clear, SQL statements can be placed inside the program, there are several ways: way one: mysql: Stored procedure program: Call stored procedure way two: MySQL:. (No operation) Program: SQL statement method Three: MySQL:. Program: Class and object (SQL statement) (ORM Framework) Create stored procedure: syntax: Simple type: delimiter//create procedure name () Beginsql statement end//delimiter; pass parameter: (In,out, InOut) < parameters must be passed regardless of whether they are used, and the stored procedure has no return value!!! >delimiter//create procedure P2 (in N1 int,in n2 int) beginsql statement end//delimiter; Execute stored procedure: Sql:call name () Py:cursor.callproc ("name", (parameter)) Cursor.commit () Be sure to commit the Operation Cursor.fetchall () Get the result set of the query to delete the stored procedure: drop procedure name;
Category: 1. Simple CREATE PROCEDURE p1 () Beginselect * from Student;insert to teacher (tname) VALUES ("CT"); Endcall P1 () cursor.callproc (' P1 ') 2.  The arguments in call execution are executed with parentheses in order to reserve the parameters (in,out,inout) delimiter//create procedure P2 (in N1 int,in n2 int) beginselect * from student where Sid > N1; END//delimiter; sql:call p2 (12,2) py:cursor.callproc (' P2 ', (12,2)) 3. Parameter out to simulate the return value can only be passed back, get no incoming value delimiter//create procedure P3 (in N1 int,inout N2 int.) Beginset n2 = 123123;select * from St udent where SID > N1;  END//delimiter; Sql:set @v1 = 10; Create and set a variable for the reply level create session level variable @ set set Variable call P3 (@v1) Select @v1; After the print execution, the global variable Py:cursor.callproc (' P3 ', (12,2)) parameter R1 = Cursor.fetchall () takes the result set print (R1) cursor.execute (' Select @_p3_0,@_ P3_1 ') fixed notation fetching the execution result of the stored procedure r2 = Cursor.fetchall () print (R2) (equivalent to the following operation in SQL!) Set @_p3_0 = Session-level variable ser @_p3_1 = 2call P3 (@_p3_0,@_p3_1) Select @_p3_0,@_p3_1) summarizes:=======> features: A. Available parameters: in Out Inoutb. PYMYSQL1. Take result set Cursor.callproc (' P3 ', (12,2)) r1 = Cursor.fetchall () print (R1) 2. Take the return value Cursor.exEcute (' select @_p3_0,@_p3_1 ') r2 = Cursor.fetchall () print (R2) * Why is there a result set with an out-forged return value? (main reason: it is used to judge the results of executing SQL statements!) ) Delimiter//create procedure P3 (in N1 int,out n2 int sets a value that identifies the execution result of a stored procedure) BEGIN--Executes the majority of the increment statement insert into table name (column name) Valu ES (); INSERT into table name (column name) values (); INSERT into table name (column name) values (); INSERT into table name (column name) values (); INSERT into table name (column name) values (); END//delimiter; 4. Transactional transactions are used to operate multiple SQL for some operations as atomic, and once an error occurs, it can be rolled back to its original state, guaranteeing database data integrity. To determine the completion status of the operation: the completion of execution is completed, there is an error rollback pseudo-code: delimiter//create procedure P4 (out status int) BEGIN1.  Declares that {set status = 1 is executed if an exception occurs, and the status value returned by the error is detected rollback; The rollback operation must be added!  } Start Transaction--a 1 account minus 100 operation--a 2 account plus 90--a 3 account plus 10commit; When things are done, they will be submitted correctly! End Set status = 2; Normal execution succeeded, return status value end//delimiter; ===============================delimiter//create PROCEDURE P5 (out P_return_code tinyint ) Begin DECLARE Exit handler for SqlException Begin--ERROR set p_return_code = 1;   Rollback    END; START TRANSACTION;  --Start transaction delete from Tb1;insert to TB2 (name) VALUES (' seven ');    COMMIT;    --SUCCESS Set p_return_code = 2;End//delimiter; Get out return result: sql:set @v2 = 0;call P5 (@v2); Select @v2;p y:cursor.execute (' select @_p5_0 ') R5 = Cursor.fetchall () print (R5) 5. cursors cursor (related to loops) Note: performance is not high! For each row of data, disaggregated to calculate the time, will be used! 1. DECLARE cursor 2, get the data in table a my_cursor select ID, num from A3, loop operation! For Row_id,row_num in My_cursor: (not smart, don't know when the loop ends) #检测循环时候还有数据, if there is no data on the exit! INSERT into B (num) VALUES (row_id+row_num) 4, if you want to use a variable first declare delimiter//create procedure P6 () begin #声明变量declare row_id int; --Custom Variable 1 declare row_num int; ---Custom variable 2 declare done int DEFAULT false;declare temp int, #创建游标declare my_cursor cursor for select id,num from A; --Declares the cursor type declare CONTINUE HANDLER for not FOUND SET do = TRUE; --cursor internal no value done set to true# start operation Open my_cursor; --Open cursor Xxoo:loop syntax: Cycle name: Loop start loop fetch my_cursor into row_id,row_num;if done then leave Xxoo; End If;set temp = row_id + Row_num;insert into B (number) values (temp); end loop Xxoo;close my_cursor; --Close the cursor end//delimter; 6. Dynamic execution SQL (anti-SQL injection) pseudo-code: delimiter//create procedure P7 (in TPL varchar (255), # Receives a whole bunch of strings in Arg inT) begin 1. Pre-detect something SQL statement legality do anti-SQL injection problem 2. SQL = formatted TPL + ARG formats the incoming two parameters in string format 3. Execute SQL statement-----> FETCH result set set @xo = arg; PREPARE XXX (variable, random name) from the TPL (' select * from student where sid >? ');   EXECUTE xxx USING @xo; -Note: When formatting, the value passed in must be the session-level variable deallocate prepare xxx; --Execute the formatted SQL statement end//delimter; call P7 ("SELECT * from TB where ID >?", 9) #? Represents the placeholder ======> example <======delimiter \create PROCEDURE P8 (in nid int) beginset @nid = nid; PREPARE prod from ' select * from student where Sid >? '; EXECUTE prod USING @nid;D eallocate prepare prod; End\delimiter; Database related operations: 1. SQL statements * * * * * PRIORITY (speed required!)  )-select XX () from XX;   2. Find the built-in function first, use it! Take advantage of the features provided within MySQL (performance requirements are not high!) 5, index index is a table of contents, you can find the index location in the directory, so as to quickly locate the query data. For the index, it is saved in an extra file. Role:-constraint-Accelerated lookup index:-primary key index: Acceleration lookup + cannot be empty + cannot be duplicated-normal index: Accelerated Find-Unique index: Accelerated find + cannot repeat-federated index (multiple columns):-Federated primary Key index-Federated unique index-federated Normal Index acceleration lookup: Fast: SELECT * F Rom TB where name= ' asdf ' select * from TB where id=999 assume: id name email .... No index: Look up the whole table from front to back index: ID create extra file (some format store) name create extra file (some format store)E-Mail Create additional files (some format storage) created index ix_name on Userinfo3 (email); Name email create extra file (some format store) index kind (stored in some format): Hash index: (store is the data in the table Row store, unordered) single-value speed fast range slow for continuous or range data because the data stored in the hash table is asymmetric with the data in the original Table Btree index: Two tree index pyramid structure, from top to bottom, left branch is smaller than current number, right branch is bigger than current number ======== " Result: Fast ======== index: three points to note:-A. Extra files save special data structures (creating an index will recreate a file to save the corresponding relationship)-B. query fast; But slow-c. Hit index (query with the created index) Established not to use is wasted) SELECT * from Userinfo3 where email= ' [email protected] '; select * from Userinfo3 where email like ' alex% '; Slow (fuzzy match, index useless, or one in the original data) primary key index: PRIMARY key has two functions: Accelerated query and UNIQUE constraint normal index:-Create INDEX name on table name (column name)-Drop index name on table name unique index:-creat E unique index name on table name (column name)-Drop unique index name on table name combination index: (leftmost prefix match) the combined index is the combination of n columns into one index. The application scenario is: Frequent simultaneous use of n columns for query adherence: The leftmost prefix matching rule, which is related to the index order, the first judgment in the condition must be the first value of the composite index. -Create unique index name on table name (column name 1, column name 2)-Drop unique index name on table name Example:-Create INDEX Ix_name_email on test (Name,email,) -The leftmost prefix matches the select * from Test where name= ' alex666 '; Will select * from Test where name= ' alex666 ' and email= ' [Email protectEd] '; Will select * from Test where email= ' [email protected] '; Do not combine index efficiency > Index Merge (or use environment-specific) to combine indexes (when query information requires multiple conditions)-(Name,email,) SELECT * from Test where name= ' alex666 ' and email= ' [EMA Il protected] '; Quick SELECT * from test where name= ' alex666 '; Index merging: (the usual query is judged by a condition, multiple conditions are not commonly used)-name-emailselect * from test where name= ' alex666 '; Quick SELECT * from test where email= ' [email protected] '; Quick SELECT * from test where name= ' alex666 ' and email= ' [email protected] '; Relatively fast noun: Virtual presence Index Overwrite index:-get data directly in index file (not real index) the data column of select is available only from the index and does not have to read the data row, in other words, the query column to be built by the index cover example: Select email from test where email= "[email protected]" Index merging:-Merging multiple single-column indexes using example: Select email from test where email= "[email protected]" and tid=2222222 Text index: Indexing the entire data table, not commonly used, is generally used with third-party software to call because the index is specifically used to accelerate the search, so after the index, the query efficiency will be fast to fly up. When you add an index to a database table, it does make the query take off, but the premise must be the correct query using the index, and if used in an incorrect way, even indexing will not work. 2. Create an index for frequently found columns but some grammars do not speed up the index. If you want to quickly query, here's what we do:-CREATE INDEX-hit Index * * * * Big Data volume note, small data ok! However, the following methods do not hit the index:-like '%xx ' Fuzzy Lookup select * from Test where e-mail like ' alex% '; available thirdSquare Tools to accelerate!  -Use the function to take the order of the SELECT * from test where reverse (email) = ' [email protected] '; If you want to flip the lookup, in Python first to query the data to reverse, and then to match the database content-or Index or non-index cannot hit index operation SELECT * FROM Test where tid = 1 or name = ' [email protected] '; Special: When the OR condition has an unindexed columns invalidated, the following will go index select * FROM Test where tid = 1 or name = ' alex2666666 ' and email = ' [email protected] ' #会忽略非索引-find condition, fill in inconsistent data type if the column is a string type, pass The condition must be quoted in quotation marks, if written as a number type, where the side involved in the process of transcoding, will certainly slow down the query speed select * FROM test where email = 999;-! = does not mean to take the reverse select * from Test where Email! = ' Alex ' Special: If the primary key is still going to go index select * from Test where tid! = 123-> Greater than select * from test where email > ' [email&nb Sp;protected] ' Special: If the primary key or index is an integer type, then the index select * from Test where the Tid > 123select * from Test where num > 123-order b Y sort Note: When sorting by index, select the mapping (the column to display) if it is not an index, then do not go to index select name from test order by email desc; Special: If the primary key is sorted, then the index: SELECT * FROM Test O Rder by nid desc; -Combined index leftmost prefix if the combined index is: (name,email) name and email--Use index name--use index email--Do not use index 3. Time  Explain + query SQL-used to display SQL execution information parameters, based on reference information can be SQL optimized MySQL has an execution plan: let MySQL estimate the time-consuming and type types before actually performing the operation (generally correct)! Good judgment! Syntax: Explain SQL statement SQL estimated operation time (for reference only) type: All < index < range < Index_merge < Ref_or_null < ref < Eq_r EF < System/constall is a global scan ref is index scan id,email slow: SELECT * from Test where name= ' alex2666666 ' explain select * from Test wher E name= ' alex2666666 ' type:all (full table Scan) SELECT * from test limit 1; #limit page fetching is an exception, this kind of operation to get the previous data will be very fast (because the operation of the data is small AH), if the start is to find a good millions of behind the data row, still can't fly up! Fast: SELECT * from Test where email= ' alex2666666 ' type:const (walk index) 4. DBA work slow Log-Execution time > 10-Miss Index-log file path configuration: (before modifying a profile, be sure to back it up!) Or you have to run!!! )-Program execution persists in memory, and configuration information is also in! -View Current configuration information: (In-memory variables) show variables like '%query% ' #查看有 query variables (mainly look at configuration information, file path) show variables like '%queri  es% ' #查看有 queries variable (mostly look at index)-modifies the current configuration: Set global variable name = value Set Slow_query_log = On turn on slow logging set slow_query_log_file = D:\mysql\setup\mysql-5.7.18-winx64\data\ZYJ-PC-slow.log log file default in the data directory set Long_query_time = 2 time limit, more than this, the record set log_queries_not_using_indexes = On is used to search records using the index-configuration file to write the configuration file, and then open the database together with the execution of the time, so that the province after each open, then configure! Open execution: mysqld--defaults-file= ' configuration file absolute path + profile name ' Create profile---> Free profile contents: Slow_query_log = On turn on slow logging slow_query_log_f ile = D:\mysql\setup\mysql-5.7.18-winx64\data\ZYJ-PC-slow.log log file is default in the data directory Long_query_time = 2 time limit, log is logged when the time is exceeded _queries_not_using_indexes = On for search records using index Note: After modifying the configuration file, it is necessary to restart the service to take effect (modify only the files within the hard disk, the contents of the files are not modified!) ) 5. Paging *******a. Limit the number of pages, only view how many pages SELECT * from test limit 20,10;b. Three methods-direct not to see-index Table sweep: (Overwrite index) SELECT * from Test WHERE tid in (S Elect N.tid from (SELECT * from Test LIMIT 2900000,10) as N)-Scenario: The relative lookup is primarily------> records the maximum or minimum ID1 of the current page.  Page only previous page, next page # max_id# min_id Next: Tid > Max_idselect * from Test where tid > 2900000 limit 10; previous: Tid < Min_idselect * FROM (SELECT * To test where Tid < 2899991 order by Tid DESC limit) as N ORDER by N.tid ASC; 2. Skip Page prev 192 193 [196] 197 198 199 Next SELECT * FROM (SELECT * from Test WHERE tid > 2800000 limit) as N order by N.tid DESC LIMIT, as S order by S.tid ASCc. Why not between and * * * *: The most deadly reason: If the ID is not contiguous, can not directly use the ID range to find, the data obtained is not a fixed value!

Python full stack database (ii) MySQL database advanced