The 27th day of Java smallholder cultivation

day27

Today's content

1. multi-table query
2. thing
3. DCL

multi-table query

# 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, associated 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),
('Zhu Bajie','male',3600,'2010-12-02',2),
('Tang Monk','male',9000,'2008-08-08',2),
('Baigujing','female',5000,'2015-10-07',3),
('spider goblin','female',4500,'2011-03-14',1);

select Column name list from Table name list where Condition list;

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

-- Cartesian product 
SELECT * FROM emp, dept;

Classification of multi table query:

Internal connection query:

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

-- 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`, t1.`gender`, t2.`name`
FROM
	emp t1, dept t2
WHERE 
	t1.`dept_id` = t2.`id`;
  1. Show internal connections:
Syntax: select Field list from Table name 1 [inner] join Table name 2 on condition;
-- Query all employee information and corresponding department information
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.id;
  1. Internal connection query:
    1. From which tables do you query data
    2. What are the conditions
    3. Which fields to query

External connection query:

  1. Left outer connection:
Syntax: select Field list from Table 1 left [outer] join Table 2 on condition;
All data in the left table and its intersection are queried.

-- 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

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.

Subquery:

Concept: nested query in query. Nested query is a sub query.
-- Query the information of the highest paid employee
-- 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 That's it. 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 employees whose salary is less than the average salary

SELECT * FROM emp WHERE emp.`salary` < (SELECT AVG(salary) FROM emp);
  1. The result of subquery is multi row and single column:
Subqueries can be used as conditions and operators in To judge
-- query'Finance Department'and'Marketing Department'All employee information

SELECT id FROM dept WHERE NAME = 'Finance Department' OR NAME = 'Marketing Department';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = 'Finance Department' OR NAME = 'Marketing Department');
  1. The result of subquery is multi row and multi column:
Subqueries can be queried as a virtual table
-- The entry date of the employee is 2011-11-11 Employee information and department information after

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';

affair

Basic introduction to transaction

  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.
  1. Operation:
  • Start transaction: start transaction;
  • Rollback: rollback;
  • Submit: commit;
-- 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
UPDATE account SET balance = balance + 500 WHERE NAME = 'list';
-- If there is no problem in execution, commit the transaction
COMMIT;
-- A problem is found. Roll back the transaction
ROLLBACK;
  • 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;

Four characteristics of transactions:

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

Isolation level of transactions (understand)

  • Concept: multiple things are isolated and independent of each other. However, if multiple things operate on the same batch of data, it will cause some problems. Setting different isolation levels can solve these problems.
  • Existing problems:
    • Dirty read: a transaction reads uncommitted data from another transaction.
    • Non repeatable read (virtual read): in the same transaction, the data read twice is different.
    • 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:

      • Read uncommitted: read uncommitted

        • Problems: dirty reading, unrepeatable reading and unreal reading

      • Read committed: read committed (Oracle default)

        • Problems: unrepeatable reading and unreal reading

      • Repeatable read: repeatable read (MySQL default)

        • The problem: unreal reading

      • 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;

DCL

DCL(Data control Language) is used to define the access rights and security levels of the database and create users. Keywords: GRANT,REVOKE, etc

DBL: Database Administrator

DCL:

  1. Manage users
Add user:
Syntax: CREATE USER 'user name'@'host name' IDENTIFIED BY 'password';

CREATE USER 'zhangsan'@'localhost' IDENTIFIED BY '123'; 

CREATE USER 'lisi'@'%' IDENTIFIED BY '123'; 

Delete user:
Syntax: DROP USER 'user name'@'host name';

DROP USER 'zhangsan'@'localhost';

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');.

*mysql I forgot root User's password?
	1.cmd-->net stop mysql stop it mysql service
		* Need administrator to run cmd
	2.Start with no authentication mysql Services: mysql --skip-grant-tables
	3.Open new cmd Window, direct input mysql Command, hit enter. You can log in successfully
	4.use mysql;
	5.update user set password = password('New password') where user = 'root';
	6.Close both windows
	7.Open the task manager and end it manually mysql.exe Process of
	8.start-up mysql service
	9.Log in with a new password
 Query user:
-- 1. Switch to mysql database
USE mysql;
-- 2. query user surface
SELECT * FROM USER;
  • Wildcard character:% indicates that users can log in to the database on any host
  1. Permission management:
1.Query authority:
-- Query authority
SHOW GRANTS FOR 'user name'@'host name';
SHOW GRANTS FOR 'lisi'@'%';
2.Grant permissions:
-- Grant permission
GRANT Permission list ON Database name.Table name TO 'user name'@'host name';

GRANT SELECT,DELETE,UPDATE ON db3.`account` TO 'lisi'@'%';

-- to zhangsan The user grants all permissions on any database or any table
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
3.Revoke permissions:

Keywords: Java Database MySQL

Added by philicious on Wed, 02 Feb 2022 21:50:18 +0200

Popular Keywords

©2024 Programming VIP