1. Multi table query:

1. Query syntax:

select
	Column name list
from
	Table name list
where....

2. Prepare sql

Create department table

CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('Development Department'),('Marketing Department'),('Finance Department');

Create employee table

CREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	gender CHAR(1), -- Gender
	salary DOUBLE, -- wages
	join_date DATE, -- Entry date
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES dept(id) -- Foreign key, related department table(Primary key of department table)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Sun WuKong','male',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Zhu Bajie','male',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Tang Monk','male',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('Baigujing','female',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('spider goblin','female',4500,'2011-03-14',1);

3. Cartesian product:

• There are two sets a and B Take all the components of these two sets.

• To complete multi table query, we need to eliminate useless data

4. Classification of multi table query:

1. Internal connection query:

1. Implicit implicit connection: use the where condition to eliminate useless data

• example:
  – query all employee information and corresponding department information

SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

– query the name and gender of the employee table. Name of department table

SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

SELECT 
	t1.name, -- Name of employee table
	t1.gender,-- Employee gender table
	t2.name -- Name of department table
FROM
	emp t1,
	dept t2
WHERE 
	t1.`dept_id` = t2.`id`;

1. Explicit inner connection:
   Syntax: select field list from table name 1 [inner] join table name 2 on condition

   • For example:

    SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;	
    SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;	
   

2. Internal connection query:

• From which tables do you query data
• What are the conditions
• Which fields to query

2. External link query:

		1. Left outer connection:
			* Syntax:`select Field list from Table 1 left [outer] join Table 2 on Conditions;`
			* All data in the left table and its intersection are queried.
			* example:
				-- Query all employee information. If an employee has a department, the Department name will be queried. If there is no department, the Department name will not be displayed
			
			```sql
				SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
			```

1. Right outer connection:

• Syntax: select field list from table 1 right [outer] join table 2 on condition;

• All the data in the right table and its intersection are queried.

• example:

  SELECT 	* FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
  

3. Sub query:

• Concept: nested query in query, which is called sub query.
  – query the information of the highest paid employees
  – 1 what is the highest salary

  SELECT MAX(salary) FROM emp;
  

  – 2 query employee information, and the salary is equal to 9000

  SELECT * FROM emp WHERE emp.`salary` = 9000;
  

  – one sql will complete this operation. Subquery

  SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);
  

• Different sub queries

  1. The result of subquery is single row and single column:

     • Subqueries can be used as conditions and operators can be used to judge. Operator: > > = < < ==

     • – query the person whose salary is less than the average salary

       SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
       

  2. The result of subquery is multi row and single column:

     • The sub query can be used as a condition and judged by the operator in
       – query all employee information of 'finance department' and 'marketing department'

       SELECT id FROM dept WHERE NAME = 'Finance Department' OR NAME = 'Marketing Department';
       SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
       -- Subquery
       SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = 'Finance Department' OR NAME = 'Marketing Department');
       

  3. The result of subquery is multi row and multi column:

     • Subqueries can be queried as a virtual table
       – query employee information and department information of employees whose employment date is after November 11, 2011
       – subquery

       SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2
       WHERE t1.id = t2.dept_id;
       -- Common internal connection
       SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'
       

5. Multi table query exercise

1. Prepare SQL

			-- Department table
			CREATE TABLE dept (
			  id INT PRIMARY KEY PRIMARY KEY, -- department id
			  dname VARCHAR(50), -- Department name
			  loc VARCHAR(50) -- Location of Department
			);
			
			-- Add 4 departments
			INSERT INTO dept(id,dname,loc) VALUES 
			(10,'Teaching and Research Department','Beijing'),
			(20,'School Work Department','Shanghai'),
			(30,'Sales Department','Guangzhou'),
			(40,'Finance Department','Shenzhen');
 			-- Job table, job name, job description
			CREATE TABLE job (
			  id INT PRIMARY KEY,
			  jname VARCHAR(20),
			  description VARCHAR(50)
			);
			
			-- Add 4 jobs
			INSERT INTO job (id, jname, description) VALUES
			(1, 'chairman', 'Manage the whole company and receive orders'),
			(2, 'manager', 'Management staff'),
			(3, 'salesperson', 'Promote products to customers'),
			(4, 'Clerk', 'Using office software');
						-- Employee table
			CREATE TABLE emp (
			  id INT PRIMARY KEY, -- staff id
			  ename VARCHAR(50), -- Employee name
			  job_id INT, -- post id
			  mgr INT , -- Superior leaders
			  joindate DATE, -- Entry date
			  salary DECIMAL(7,2), -- wages
			  bonus DECIMAL(7,2), -- bonus
			  dept_id INT, -- Department No
			  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
			  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
			);
			
			-- Add employee
			INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
			(1001,'Sun WuKong',4,1004,'2000-12-17','8000.00',NULL,20),
			(1002,'Lu Junyi',3,1006,'2001-02-20','16000.00','3000.00',30),
			(1003,'Lin Chong',3,1006,'2001-02-22','12500.00','5000.00',30),
			(1004,'Tang Monk',2,1009,'2001-04-02','29750.00',NULL,20),
			(1005,'Li Kui',4,1006,'2001-09-28','12500.00','14000.00',30),
			(1006,'Song Jiang',2,1009,'2001-05-01','28500.00',NULL,30),
			(1007,'Liu Bei',2,1009,'2001-09-01','24500.00',NULL,10),
			(1008,'Zhu Bajie',4,1004,'2007-04-19','30000.00',NULL,20),
			(1009,'Luo Guanzhong',1,NULL,'2001-11-17','50000.00',NULL,10),
			(1010,'Wu Yong',3,1006,'2001-09-08','15000.00','0.00',30),
			(1011,'Monk Sha',4,1004,'2007-05-23','11000.00',NULL,20),
			(1012,'Li Kui',4,1006,'2001-12-03','9500.00',NULL,30),
			(1013,'Little white dragon',4,1004,'2001-12-03','30000.00',NULL,20),
			(1014,'Guan Yu',4,1007,'2002-01-23','13000.00',NULL,10);
			
						-- Wage scale
			CREATE TABLE salarygrade (
			  grade INT PRIMARY KEY,   -- level
			  losalary INT,  -- minimum wage
			  hisalary INT -- Maximum wage
			);
			
			-- Add 5 salary levels
			INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
			(1,7000,12000),
			(2,12010,14000),
			(3,14010,20000),
			(4,20010,30000),
			(5,30010,99990);

1. Exercise one

			-- Requirements:
			
			-- 1.Query all employee information. Query employee number, employee name, salary, job name and job description
			/*
				analysis:
					1.Employee number, employee name, salary, emp table, job name and job description need to be queried
					2.Query criteria EMP job_ id = job. id
			
			*/
			SELECT 
				t1.`id`, -- Employee number
				t1.`ename`, -- Employee name
				t1.`salary`,-- wages
				t2.`jname`, -- Title
				t2.`description` -- Job description
			FROM 
				emp t1, job t2
			WHERE 
				t1.`job_id` = t2.`id`;

1. Exercise 2

		
				-- 2.Query employee number, employee name, salary, job name, job description, department name and department location
				/*
					analysis:
						1. Employee number, employee name, salary, emp job name, job description, job department name, Department location, dept
						2. Condition: EMP job_ id = job. id and emp. dept_ id = dept.id
				*/
    SELECT 
				t1.`id`, -- Employee number
				t1.`ename`, -- Employee name
				t1.`salary`,-- wages
				t2.`jname`, -- Title
				t2.`description`, -- Job description
				t3.`dname`, -- Department name
				t3.`loc` -- Department location
			FROM 
				emp t1, job t2,dept t3
			WHERE 
				t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id`;
			   
		

1. Exercise three

	-- 3.Query employee name, salary and salary grade
			/*
				analysis:
					1.Employee name, salary emp salary grade salarygrade
					2.Condition EMP salary >= salarygrade. losalary and emp. salary <= salarygrade. hisalary
						emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
			*/
			SELECT 
				t1.ename ,
				t1.`salary`,
				t2.*
			FROM emp t1, salarygrade t2
			WHERE t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;

1. Exercise 4

-- 4.Query employee name, salary, job name, job description, department name, Department location and salary grade
/*
	analysis:
   1. Employee name, salary emp, job title, job description, job department name, Department location, dept salary grade, salarygrade
	 2. Condition: EMP job_ id = job. id and emp. dept_ id = dept.id and emp. salary BETWEEN  				   salarygrade.losalary and salarygrade.hisalary		
	 */
	   					
			SELECT 
				t1.`ename`,
				t1.`salary`,
				t2.`jname`,
				t2.`description`,
				t3.`dname`,
				t3.`loc`,
				t4.`grade`
			FROM 
				emp t1,job t2,dept t3,salarygrade t4
			WHERE 
				t1.`job_id` = t2.`id` 
				AND t1.`dept_id` = t3.`id`
				AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;		

1. Exercise five

-- 5.Find out the department number, department name, Department location and department number
    /*
				analysis:
					1.Department number, department name and department location dept table. Department headcount emp table
					2.Use grouped queries. According to EMP dept_ ID completes grouping and queries count(id)
					3.Use the sub query to associate the query result in step 2 with the dept table
					
			*/
			SELECT 
				t1.`id`,t1.`dname`,t1.`loc` , t2.total
			FROM 
				dept t1,
				(SELECT
					dept_id,COUNT(id) total
				FROM 
					emp
				GROUP BY dept_id) t2
			WHERE t1.`id` = t2.dept_id;

7. Exercise 6

-- 6.Query the names of all employees and their direct superiors,Employees without leaders also need to query
/*
		analysis:
			1.Name emp, name of direct supervisor emp
				* emp The id and mgr of the table are self related
			2.Condition EMP id = emp. mgr
			3.Query all data in the left table and intersection data
				* Use left outer join query
				
*/
			/*
			select
				t1.ename,
				t1.mgr,
				t2.`id`,
				t2.ename
			from emp t1, emp t2
			where t1.mgr = t2.`id`;
			
			*/
			
			SELECT 
				t1.ename,
				t1.mgr,
				t2.`id`,
				t2.`ename`
			FROM emp t1
			LEFT JOIN emp t2
			ON t1.`mgr` = t2.`id`;

2. Services

1. Basic introduction of affairs

1. Concept:

   • If a business operation with multiple steps is managed by a transaction, these operations will either succeed or fail at the same time.

2. Operation:

   1. Start transaction: start transaction;
   2. Rollback: rollback;
   3. Submit: commit;

3. example:

    CREATE TABLE account (
   		id INT PRIMARY KEY AUTO_INCREMENT,
   		NAME VARCHAR(10),
   		balance DOUBLE
   	);
   	-- Add data
   	INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
   
   
   
   SELECT * FROM account;
   UPDATE account SET balance = 1000;
   -- Zhang San transferred 500 yuan to Li Si
   		
   
   -- 0. Open transaction
   START TRANSACTION;
   -- 1. Zhang San account -500
   
   UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';
   -- 2. Li Si account +500
   -- Error ...
   UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';
   
   -- If there is no problem in execution, commit the transaction
   COMMIT;
   
   -- A problem is found. Roll back the transaction
   ROLLBACK;
   

​

1. Transactions in MySQL database are automatically committed by default
   • There are two ways to commit a transaction:
     • Auto submit:
       • mysql is submitted automatically
       • A DML statement will automatically commit a transaction.
     • Manual submission:
       • Oracle database is manually committed by default
       • You need to start the transaction before committing
   • Modify the default commit method of transaction:
     • View the default commit method of transactions: SELECT @@autocommit; – 1 for automatic submission, 0 for manual submission
     • Modify the default submission method: set @@autocommit = 0;

2. Four characteristics of affairs:

1. Atomicity: it is the smallest indivisible operation unit, which either succeeds or fails at the same time.
2. Persistence: when the transaction is committed or rolled back, the database will persist and save the data.
3. Isolation: between multiple transactions. Independent of each other.
4. Consistency: the total amount of data remains unchanged before and after the transaction operation

3. Isolation level of transactions (understand)

• Concept: multiple transactions are isolated and independent of each other. However, if multiple transactions operate on the same batch of data, some problems will be caused. Setting different isolation levels can solve these problems.

• Existing problems:

  1. Dirty read: a transaction reads uncommitted data from another transaction
  2. Non repeatable read (virtual read): in the same transaction, the data read twice is different.
  3. Phantom reading: if all records in one transaction operation (DML) data table and another transaction adds a piece of data, the first transaction cannot query its own modification.

• Isolation level:

  1. Read uncommitted: read uncommitted
     • Problems: dirty reading, unrepeatable reading and unreal reading
  2. Read committed: read committed (Oracle)
     • Problems: unrepeatable reading and unreal reading
  3. Repeatable read: repeatable read (MySQL default)
     • The problem: unreal reading
  4. serializable: serialization
     • Can solve all problems
  • Note: from small to large, the isolation level has higher and higher security, but lower and lower efficiency
  • Database query isolation level:
    • select @@tx_isolation;
  • Set isolation level for database:
    • set global transaction isolation level string;

• demonstration:

  set global transaction isolation level read uncommitted;
  start transaction;
  -- Transfer operation
  update account set balance = balance - 500 where id = 1;
  update account set balance = balance + 500 where id = 2;
  

3.DCL:

• SQL classification:

  1. DDL: operating databases and tables
  2. DML: adding, deleting and modifying data in the table
  3. DQL: query data in table
  4. DCL: management user, authorization

• DBA: Database Administrator

• DCL: management user, authorization

  1. Manage users

     1. Add user:

        • Syntax: CREATE USER 'username' @ 'hostname' IDENTIFIED BY 'password';

     2. Delete user:

        • Syntax: DROP USER 'username' @ 'hostname';

     3. Modify user password:

        UPDATE USER SET PASSWORD = PASSWORD('New password') WHERE USER = 'user name';
        UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';
        
        SET PASSWORD FOR 'user name'@'host name' = PASSWORD('New password');
        SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');
        

     4. Forgot the password of root user in mysql?

     5. CMD -- > net stop mysql stop mysql service

      * An administrator is required to run the`cmd`
     

     1. Start mysql service without authentication: mysqld -- skip grant tables

     2. Open a new cmd window, directly enter the mysql command and press enter. You can log in successfully

     3. use mysql;

     4. update user set password = password('your new password ') where user =' root ';

     5. Close both windows

     6. Open the task manager and manually end mysqld Exe process

     7. service mysql start

     8. Log in with a new password.

     9. Query user:
        – 1. Switch to mysql database
        USE myql;
        – 2. Query user table
        SELECT * FROM USER;

     • Wildcard character:% indicates that users can log in to the database on any host

  2. Permission management:

     1. Query authority:
        – query authority
        SHOW GRANTS FOR 'user name' @ 'host name';
        SHOW GRANTS FOR 'lisi'@'%';

     2. Grant permissions:
        – grant permissions
        grant permission list on database name Table name to 'user name' @ 'host name';
        – grant all permissions to user Zhang San on any database or table

        GRANT ALL ON *.* TO 'zhangsan'@'localhost';

     3. Revoke permissions:
        – revoke authority:
        revoke permission list on database name Table name from 'user name' @ 'host name';
        REVOKE UPDATE ON db3.account FROM 'lisi'@'%';