Shulou(Shulou.com)06/03 Report--

This article focuses on "MySql multi-table query syntax and transaction characteristics", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn the syntax and transaction characteristics of MySql multi-table query.

Catalogue

1. Multi-table query

1. Query syntax

2. Prepare sql

3. Cartesian product

4. Classification of multi-table queries

4.1 Internal join query

4.2 external link query

4.3 subquery

4.4 subqueries for different situations

5. Multi-table query exercise

II. Affairs

1. A basic introduction to transactions

two。 Operation

2. Automatic commit of transactions in MySQL database by default

3. Four characteristics of transaction

1. Multi-table query 1. Query syntax

Select

List of column names

From

List of table names

Where....

2. Prepare sql # to 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,-- salary 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); INSERT INTO emp (NAME,gender,salary,join_date,dept_id) VALUES ('Zhu Bajie, male', 3600); INSERT INTO emp (NAME,gender,salary,join_date,dept_id) VALUES ('Tang monk', 'male', 9000) INSERT INTO emp (NAME,gender,salary,join_date,dept_id) VALUES ('Baigujing', 'female', 5000); INSERT INTO emp (NAME,gender,salary,join_date,dept_id) VALUES ('spider essence', 'female', 4500); 3, Cartesian product

There are two sets An and B. Take all the composition of the two sets.

To complete a multi-table query, you need to eliminate useless data

4. Join query in category 4.1 of multi-table query

Implicit internal join: using where conditions 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. The name of the department table SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept _ id` = dept.`id`; SELECT t1.name,-- the name of the employee table t1.genderLay-the gender t2.name of the employee table-- the name of the department table FROM emp T1, dept t2WHERE t1.`dept _ id` = t2.`id`

Explicit internal 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`; 4.2 external link query 4.2.1 left outer link

* Syntax: select field list from Table 1 left [outer] join Table 2 on condition

* query all the data in the left table and its intersection.

Example:

-- query the information of all employees. If the employee has a department, query the department name. If there is no department, the department name SELECT t 1, t 2.name` FROM emp t 1 LEFT JOIN dept t 2 ON t 1.`dept _ id` = t 2.`id` is not displayed; 4.2.2 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 intersecting parts are queried.

Example:

SELECT * FROM dept T2 RIGHT JOIN emp T1 ON T1 1.`dept _ id` = t2.`id`; 4.3 subquery

Concept: a nested query in a query is called a subquery.

-- query the information of the employees with the highest salary

-- 1 inquire about the maximum salary of 9000

SELECT MAX (salary) FROM emp

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

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

This is done with a sql. Subquery

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

4.4 subqueries for different situations

The results of the subquery are single-row and single-column:

Subqueries can be used as conditions, using operators to determine. Operator: > > =

2011-11-11 / 5, Multi-table query exercise-- Department table CREATE TABLE dept (id INT PRIMARY KEY PRIMARY KEY,-- Department id dname VARCHAR (50),-- Department name loc VARCHAR (50)-- Department location) -- add 4 departments INSERT INTO dept (id,dname,loc) VALUES (10dint 'Department of Teaching and Research', 'Beijing'), ('Department of Learning and work', 'Shanghai'), ('Department of sales', 'Guangzhou'), ('Department of Finance', 'Shenzhen') -Job sheet, job title, job description CREATE TABLE job (id INT PRIMARY KEY, jname VARCHAR (20), description VARCHAR (50)) -add 4 positions INSERT INTO job (id, jname, description) VALUES (1, 'chairman', 'manage the whole company, take orders'), (2, 'manager', 'management staff'), (3, 'salesperson', 'sell products to customers'), (4, 'clerk', 'use office software') -- employee form CREATE TABLE emp (id INT PRIMARY KEY,-- employee id ename VARCHAR (50),-- employee name job_id INT,-- Job id mgr INT,-- superior leader joindate DATE,-- entry date salary DECIMAL (7jue 2),-- salary bonus DECIMAL (7pc2),-- bonus dept_id INT. -- Department code 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 staff INSERT INTO emp (id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES (1001 recorder 'Sun WuKong', 4 pyrrors 1004 miners 2000-12-17), (1002 minus'Lu Junyi', 3 pyrrons 1006) (2001-02-20), (1003) Lin Chong, 3pr 1006 (2001-02-22), 12500.00' (1005, Li Kui, 4, 1006, 12500.00, 14000.00, 30), (1006, 1006, Song Jiang, 2, 1009, 2001-05, 01, 01, 01, respectively), (1007, 007, Liu Bei, 2, 1009, 2001, 01, and 24500.001) NULL,10), (1008 miners' Zhu Bajie', 4pyrrors 1004 miners), (1009 pence 'Luo Guanzhong', 1), (1009 pencils' Luo Guanzhong', 1), (1010 'Wuyong', 3 miners 1006 pencils' 2001-09-08'), (1011 mins' Sha Wujing', 414 1004 pens' 2007-05-23 pas 11000.00') NULL,20), (1012 mins'Li Kui', 4 pencils 1006 pencils 2001-12-03 parallels), (1013 'little white dragons', 4 pencils 1004 parallels (2001-12-03)), (1014 pencils' Guan Yu', 4 penges 1007 parcels 2002-01-23 parcels 13000.005) -- wage scale CREATE TABLE salarygrade (grade INT PRIMARY KEY,-- level losalary INT,-- minimum wage hisalary INT-- maximum wage);-- add 5 wage grades INSERT INTO salarygrade (grade,losalary,hisalary) VALUES (1jinger 7000 and 12000), (2pint 12010 14000), (3pm 14010 20000), (4jing 20010ju 30000), (5je 30010pc99990).

Demand:

-1. Query all employee information. Query employee number, employee name, salary, job name, job description

Analysis:

1. Employee number, employee name, salary, need to query emp table job name, job description needs to query job table

two。 Query condition emp.job_id = job.id

SELECT t1.`id`,-- employee number t1.`ename`,-- employee name t1.`salary`,-- salary t2.`jname`,-- job name t2.`accountion`-- Job description FROM emp T1, job T2 WHERE t1.`job _ id` = t2.`id`

-- 2. Query employee number, employee name, salary, job name, job description, department name, department location

Analysis:

1. Employee number, employee name, salary emp Job name, Job description job Department name, Department location dept

two。 Condition: emp.job_id = job.id and emp.dept_id = dept.id

SELECT t1.`id`,-- employee number t1.`ename`,-- employee name t1.`salary`,-- salary t2.`jname`,-- job name t2.`accountion`,-- job description t3.`dname`,-- department name t3.`loc`-- department location FROM emp T1, job t2Powerdept3 WHERE t1.``job id` = t2.`id` AND t1.dept _ id` = t3.`id`

-3. Inquire about employee's name, salary, salary grade

Analysis:

1. Employee name, salary emp, salary grade salarygrade

two。 Condition emp.salary > = salarygrade.losalary and emp.salary

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.