I moved from my own csdn blog, and found that many blog posts have become vip posts after posting on csdn. I can go to your bar csdn. I can't read my own blog. I have to log in to see it. It's really disgusting
Just at the weekend, we talked about finance and big data in class, and asked to write SQL, and take out the notes we have written before
Review notes of the national MySql Level II exam (experience update after the exam on March 30)
Introduction to examination environment
Operation problem solving skills
-
Remember to use draft paper to copy the multiple choice questions Sql statement Avoid forgetting grammar in operation questions, -
If you really forget, remember to use mysql help The specific rules of the command are as follows: If I want to search Create Index Syntax, enter it in MySQL help + Search content mysql> help CREATE index; Then the syntax and detailed explanation will appear, which is very convenient. Other searches are similar. If the statement is incorrect, the following prompts will be given -
Alternatively, you can manually generate sql statements in phpmyadmin, and then copy, edit, and paste them to get the answer. -
In addition, once an error statement cannot be executed, it will report an error. If it is executed in branches, it will be convenient for us to locate and check the error, -
You may encounter the problem of saving sql to txt. Remember to open Notepad first, do it again, and then save it. You must select the utf-8 encoding format. Ansi is afraid that it will lose points if it does not support Chinese (if you create Notepad first, you cannot select the encoding format!)
Because the local question bank mysql can't run at all, you can only manually type the notepad to compare the answers I have done several sets of questions and found some rules. The question bank always examines several kinds of things So take notes and review SQL
Key review objects
-
view -
event -
trigger -
Storage process -
Storage function -
User creation and permission -
Backup and restore with MySql -
PHP and MySql Process oriented interactive
view
Create View
CREATE [OR REPLACE] VIEW view_name[(column_list)] AS SELECT_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
-
Cannot contain subquery containing from -
In addition to the create view permission, the user also needs to have the relevant permissions inside select_statement -
Cannot reference system variable or user variable -
Preprocessing statement parameters cannot be referenced -
The referenced table or view must exist -
If the select statement does not refer to the basic table or source view of the current database, you need to add a database prefix -
The ORDER BY clause is allowed in view definitions constructed by SELECT statements. However, if you view the BY statement from a specific view, the ORDER BY clause in the view definition will be ignored -
For other options or clauses in the SELECT statement, if the created view also contains these options, the statement execution effect is undefined. For example, if the view definition includes a LIMIT clause and the SELECT statement also uses its own LIMIT clause, MySQL does not define which LIMIT statement to use.
Modify View
ALTER VIEW view_name[(column_list)] AS SELECT_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
Delete View
DROP VIEW [IF EXISTS] view_name[,view_name] event
Create Event
CREATE [DEFINER = { user | CURRENT_USER }] EVENT [IF NOT EXISTS] event_name ON SCHEDULE schedule [ON COMPLETION [NOT] PRESERVE] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] DO event_body; schedule: AT timestamp [+ INTERVAL interval] ... | EVERY interval [STARTS timestamp [+ INTERVAL interval] ...] [ENDS timestamp [+ INTERVAL interval] ...] interval: quantity {YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE | WEEK | SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE | DAY_SECOND | HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}
Modify Event
ALTER [DEFINER = { user | CURRENT_USER }] EVENT event_name [ON SCHEDULE schedule] [ON COMPLETION [NOT] PRESERVE] [RENAME TO new_event_name] [ENABLE | DISABLE | DISABLE ON SLAVE] [COMMENT 'comment'] [DO event_body] Delete Event
DROP EVENT [IF EXISTS] event_name trigger
Create Trigger
CREATE TRIGGER Trigger Name After | before (trigger time) Insert | update | delete (trigger event) On table name (monitoring address) FOR EACH ROW -- This sentence is dead in MySQL. There are only row triggers and table triggers in Oracle BEGIN sql1 ... sqlN END;
-
Before Complete triggering first , and then add, delete and modify data If you want to verify whether the new data meets the use limit, use before ， -
After Yes Complete data addition, deletion and modification first , re trigger If you want to complete several or more changes after the trigger statement executes, you usually use the after option. Trigger event: -
INSERT inserts a new row to activate the trigger. -
The trigger is activated when UPDATE changes a row in the table. -
The trigger is activated when DELETE deletes a row from the table.
-
-
about insert For the new row new To represent; The value of each column in the row, using New. Column name To represent. -
about delete For the deleted lines, use old To represent the value of each column in the row, use Old. Column name To represent. -
about update For the modified row, the data before modification is used old To indicate that, Old. Column name Reference the value in the row before modification; The modified data is used new To indicate that, New. Column name Reference the value in the modified row.
Delete trigger
DROP TRIGGER [IF EXISTS] [database name.] trigger name
Storage process
Semicolon; Escape
DELIMITER $$
Create Stored Procedure
DELIMITER $$ CREATE PROCEDURE sp_name([[IN|OUT|INOUT] param_name type][,...]]) //This is similar to function sp_name (a as int, b as int) BEGIN SQL1; SQL2; ... SQLN; END $$
DECLARE Declare Local Variables
DECLARE var_name[,...]type[DEFAULT value]
SET statement
SET var_name=expr[,var_name=expr]
Process judgment statement
-
Conditional judgement -
if-else
IF condition THEN statement [ELSEIF condition THEN statement] [ELSE statement] END IF -
case
CASE case_value WHEN when_value THEN statement WHEN when_value THEN statement END CASE This method is to use case_value and when_value for comparison. If it is true, execute The second method CASE WHEN condition THEN statement WHEN condition THEN statement ELSE statement END CASE This method is more convenient -
-
Loop statement -
WHILE cycle
WHILE search_condition DO statement END WHILE -
REPEAT cycle
REPEAT statement UNTIL search_condition END REPEAT -
LOOP cycle
LOOP statement END LOOP I don't know how to use this -
cursor
-
declare cursor DECLARE cursor_name CURSOR FOR select_statement -
Open Cursor DECLARE cursor_name CURSOR FOR select_statement -
Read data FETCH cursor_name INTO var_name[,...] -
Close Cursor CLOSE cursor_name
Call stored procedure
CALL sp_name([parameter[,...]]) Delete Stored Procedure
DROP PROCEDURE FUNCTION[IF EXISTS]sp_name Storage function
| |
|
|
|---|---|---|
| |
|
|
| |
|
|
Create storage function
DELIMITER $$; CREATE FUNCTION sp_name([param_name type[,...]]) RETURNS type BEGIN sql1; sql2; ... sqln; END $$ Call storage function
SELECT sp_name(param) Delete Stored Function
DROP FUNCTION sp_name; User creation and permission
Create User
CREATE USER 'name'@'localhost' [IDENTIFIED BY 'password'] Permission settings
Grant permission GRANT
GRANT permission name ON table name TO name@localhost [WITH with_statement]
Revoke permission REVOKE
REVOKE permission name ON table name FROM name@localhost MySql data backup and recovery
MySQL dump backup data
-
Data Table Export
Mysqldump - u username - p Password Database Data Table>Export File Address File Name.sql -
Backup database system
Mysqldump - u username - p password -- all databases>export filename.sql -
Back up table structure and data separately
mysqldump -u root -p**** --tab=filename.sql Table data and structure can be backed up separately
MySQL recovery data
-
Restore the structure and data of the database
mysql -u root -p **** db_database< database.sql -
Recover data only
mysqlimport -u root -p **** database.sql PHP and MySql Process oriented interactive
Connect Database
<? php header("Content-type:text/html;charset=utf-8"); $link = mysqli_connect( 'localhost' , 'root' , '' , 'test' ) or die ('Connect Error:'.mysqli_connect_error()); //This is a general procedure oriented php using mysqli connection method $con=mysql_connect("localhost:3306","root","") Or die ("Database server connection failed!<br>"); Mysql_select_db ('test ', $con) or die ("Database selection failed!<br>"); //This is a foolishly connected method, which examines the statements used to select the database ?> Set Character Set
mysqli_set_charset($link,'UTF8'); //Mysqli process oriented mysql_query("set column 'uft8"); //Set properties for a column Execute sql statement (insert, read, update, delete)
insert data
$sql = "INSERT INTO MyGuests (firstname, lastname, email) VALUES ('John', 'Doe', ' john@example.com ')"; if (mysqli_query($conn, $sql)) { Echo "New record inserted successfully"; } else { echo "Error: " . $ sql . "<br>" . mysqli_error($conn); } Read data
$sql = "SELECT id, firstname, lastname FROM MyGuests"; $result = mysqli_query($conn, $sql); if (mysqli_num_rows($result) > 0) { //Output Data while($row = mysqli_fetch_assoc($result)) { echo "id: " . $ row["id"]. " - Name: " . $ row["firstname"]. " " . $ row["lastname"]. "<br>"; } } else { Echo "0 result"; }
-
MYSQL_NUM numeric array -
MYSQL_ASSOC associative array (key value pair) -
The default value of MYSQL_BOTH, which generates both numbers and associative arrays mysql_fetch_row(data) Generate numeric array mysql_fetch_assoc(data) Generate associative array (key value pair)
