Database strengthening chapter (3)

4 Correlation query ( Multi-table Query )

--************** second, related query (multi-table query)****************---- -- demand: Query employees and their departments ( Show employee name, department name ) --2.1 Cross connect query (not recommended.) Produce a Cartesian product:4 * 4=16, some repeating records) SELECT empname,deptname from Employee,dept; -- demand: Query employees and their departments ( Show employee name, department name ) -- multi-table query rule:1) determine which table (s) to query 2) determine which fields 3) The table joins the table between the conditions ( rule: The number of connection conditions is the number of tables -1) --2.2 Internal connection query: only results that meet the criteria are displayed ( most frequently used ) SELECT Empname,deptname--2) determine which fields From Employee,dept--1) determining which tables to query WHERE Employee.deptid=dept.id--3) join condition between table and table -- Another syntax for inner joins SELECT Empname,deptname From employee INNER JOIN Dept On Employee.deptid=dept.id; -- using aliases SELECT E.empname,d.deptname From Employee E INNER JOIN Dept D On E.deptid=d.id; -- Requirements: Query employees in each department - Expected results: -- Software development Department Zhang San -- Software Development Department John Doe -- application and maintenance department Harry -- Chen Liu, Secretary department -- total handling null --2.2 left [ outer ] connection query: Use the data on the left table to match the data on the right table, if the result of matching join condition is displayed, if the connection condition is not met NULL -- (note: Left outer connection: The data of the left table is sure to complete the display!) ) SELECT D.deptname,e.empname From Dept D Left OUTER JOIN employee E On D.id=e.deptid; --2.3 right [ outer ] connection Query : Use the data on the right table to match the data on the left table, if the results of matching join conditions are displayed, Show NULL if the join condition is not met -- (Note: Right outer connection: the data of the right table must be displayed!) ) SELECT D.deptname,e.empname From Employee E Right OUTER JOIN Dept D On D.id=e.deptid; --2.4 self-connection query -- demand: query employees and their superiors - Expected results: -- Zhang San null -- Li Shizhang -- Harry John Doe -- Chen Liu Harry SELECT E.empname,b.empname From Employee E Left OUTER JOIN Employee b On E.bossid=b.id;

5 stored Procedures 5.1 What is a stored procedure

stored procedures, SQL statements with logic

the previous SQL has no condition to judge, no loops

stored procedures with Process Control statements (if while)

5.2 Stored procedure features

1) execution efficiency very fast! The stored procedure is executed on the server side of the database!!!

2) Very poor transplant! Stored procedures for different databases are not portable.

5.3 Stored procedure syntax

-- Create a stored procedure DELIMITER $-- terminator of declaring stored procedures CREATE PROCEDURE pro_test ()-- stored procedure name ( parameter list ) Begin-- start -- can write multiple SQL statements ; --SQL statement + Process Control SELECT * from employee; End $-- end Terminator -- Execute stored procedure Call Pro_test (); --Call stored procedure name ( parameter ); Parameters: in: Indicates input parameters, can carry data with stored procedure out: Represents an output parameter that can be returned from a stored procedure INOUT: Indicates input and output parameters, can either input function or output function

--************** Three, stored procedure *******************- -- declaration Terminator -- Create a stored procedure DELIMITER $ CREATE PROCEDURE pro_test () BEGIN -- can write multiple SQL statements ; SELECT * from employee; END $ -- Execute stored procedure Call Pro_test (); --3.1 stored procedure with input parameters -- requirements: Pass in an employee ID, query employee information DELIMITER $ CREATE PROCEDURE Pro_findbyid (in Eid Int.)--In: input parameters BEGIN SELECT * FROM employee WHERE Id=eid; END $ -- Call Call Pro_findbyid (4); --3.2 stored procedure with output parameters DELIMITER $ CREATE PROCEDURE pro_testout (out str VARCHAR) –out: Output parameters BEGIN -- assigning values to parameters SET str= ' Helljava '; END $ -- Delete stored procedures DROP PROCEDURE pro_testout; -- Call -- How to accept the value of the return parameter?? --***mysql variable ****** -- global variables (built-in variables):mysql database built-in variables (all connections work) -- View all global variables: Show Variables -- View a global variable: select @@ 变量 name -- Modify global variables: set variable name = New value -- encoding of the received data of the CHARACTER_SET_CLIENT:MYSQL server --Character_set_results: encoding of MySQL server output data -- Session variable: exists only in one connection between the current client and the database server side. If the connection is broken, then all session variables are lost! -- Define session Variables : SET @ variable = value -- View Session variables: SELECT @ variable -- Local variables: variables used in stored procedures are called local variables. Local variables are lost as long as the stored procedure is executed!! --1) define a Session variable name, 2) use the name Session variable to receive the return value of the stored procedure Call Pro_testout (@NAME); -- View variable values SELECT @NAME; --3.3 stored procedure with input and output parameters DELIMITER $ CREATE PROCEDURE pro_testinout (INOUT n INT)--INOUT: input and Output parameters BEGIN -- View variables SELECT N; SET n = 500; END $ -- Call SET @n=10; Call Pro_testinout (@n); SELECT @n; --3.4 a stored procedure with conditional judgment - -demand: Enter an integer, if 1, return "Monday" , if 2, return "Tuesday", if 3 , return to "Wednesday". Other numbers, return "error input"; DELIMITER $ CREATE PROCEDURE pro_testif (in num int,out str VARCHAR (20)) BEGIN IF Num=1 Then SET str= ' Monday '; ELSEIF num=2 Then SET str= ' Tuesday '; ELSEIF Num=3 Then SET str= ' Wednesday '; ELSE SET str= ' input error '; END IF; END $ Call Pro_testif (4, @str); SELECT @str; --3.5 stored procedure with cyclic function -- requirements: Enter an integer and sum. For example, the input of the 1-100 and the statistical DELIMITER $ CREATE PROCEDURE pro_testwhile (in num int,out result INT) BEGIN -- Define a local variable DECLARE i INT DEFAULT 1; DECLARE vsum INT DEFAULT 0; While I<=num do SET vsum = vsum+i; SET i=i+1; END while; SET result=vsum; END $ DROP PROCEDURE Pro_testwhile; Call Pro_testwhile (@result); SELECT @result; Use DAY16; --3.6 Using the result of a query to assign a value to a variable (into) DELIMITER $ CREATE PROCEDURE Pro_findbyid2 (in Eid int,out vname VARCHAR (20)) BEGIN SELECT EmpName to VName from employee WHERE Id=eid; END $ Call Pro_findbyid2 (1, @NAME); SELECT @NAME;

6 triggers 6.1 trigger action

when you manipulate a table, you want to trigger some action/behavior at the same time , you can use the trigger to complete!!

For example: When you insert a record into an employee table, you want to insert the data into the log table at the same time

-- requirements: When inserting a record into an employee table, I want MySQL to automatically insert data into the log table at the same time -- Create a trigger ( add ) CREATE TRIGGER Tri_empadd After insert on employee for each ROW--when a record is inserted into the employee table Insert into Test_log (content) VALUES (' employee table insert a record '); -- inserting data INSERT into employee (Id,empname,deptid) VALUES (7, ' zangus ', 1); INSERT into employee (Id,empname,deptid) VALUES (8, ' zangus 2 ', 1); -- Create a trigger ( Modify ) CREATE TRIGGER tri_empupd after UPDATE on employee for each ROW--when a record is modified to the employee table INSERT into Test_log (content) VALUES (' employee table modified a record '); -- Modification UPDATE employee SET empname= ' Eric ' WHERE id=7; -- CREATE Trigger ( Delete ) CREATE TRIGGER Tri_empdel After delete on employee for each ROW--when a record is deleted to the employee table INSERT into Test_log (content) VALUES (' employee table Delete a record '); -- Delete DELETE from employee WHERE id=7;

7 MySQL permissions issues

--*********** ,mysql permissions problem **************** --MySQL database permissions problem:root : Have all permissions (can do anything) -- rights account, only partial permissions (curd) For example, you can only manipulate a table of a database -- How do I change the mysql user password? --PASSWORD:MD5 encryption function ( One-way encryption ) SELECT PASSWORD (' root '); --*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B --MySQL database, User Configuration : users table Use MySQL; SELECT * from USER; -- Change Password UPDATE USER SET password=password (' 123456 ') WHERE user= ' root '; -- assigning rights to accounts GRANT SELECT on Day16.employee to ' Eric ' @ ' localhost ' identified by ' 123456 '; GRANT DELETE on Day16.employee to ' Eric ' @ ' localhost ' identified by ' 123456 ';

Database strengthening chapter (3)