Join queries
SQLyog Ultimate v10.00 Beta1 mysql-5.7.18-log: Database - girls ********************************************************************* */ /*! 40101 SET NAMES utf8 */; / *! 40101 SET SQL_MODE=''*/; / *! 40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; / *! 40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; / *! 40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; / *! 40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*! 32312 IF NOT EXISTS*/`girls` /*! 40100 DEFAULT CHARACTER SET gb2312 */; USE `girls`; /*Table structure for table `admin` */ DROP TABLE IF EXISTS `admin`; CREATE TABLE `admin` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(10) NOT NULL, `password` varchar(10) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; /*Data for the table `admin` */ insert into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666'); /*Table structure for table `beauty` */ DROP TABLE IF EXISTS `beauty`; CREATE TABLE 'beauty' (' id 'int(11) NOT NULL AUTO_INCREMENT,' name 'varchar(50) NOT NULL,' sex 'char(1) DEFAULT' female ', `borndate` datetime DEFAULT '1987-01-01 00:00:00', `phone` varchar(11) NOT NULL, `photo` blob, `boyfriend_id` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; /*Data for the table `beauty` */ insert into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) Values (1, 'Ada', 'female' and '1988-02-03 00:00:00', '18209876577', NULL, 8), (2, 'ms',' female 'and' 1987-12-30 s 00:00:00 ', '18219876577', NULL, 9), (3, 'Angelababy', 'female' and '1989-02-03 00:00:00', '18209876567', NULL, 3), (4, 'hot', 'female' and '1993-02-03 s 00:00:00 ', '18209876579', NULL, 2), (5, 'zhou', 'female' and '1992-02-03 00:00:00', '18209179577', NULL, 9), (6, 'week zhi if' and 'female' and '1988-02-03 s 00:00:00 ', '18209876577', NULL, 1), (7, 'lenient to', 'female' and '1987-12-30 00:00:00', '18219876577', NULL, 9), (8, 'small zhao', 'female' and '1989-02-03 s 00:00:00 ', '18209876567', NULL, 1), (9, 'double son', 'female' and '1993-02-03 00:00:00', '18209876579', NULL, 9), (10, 'Wang Yuyan', 'female' and '1992-02-03 s 00:00:00 ', '18209179577', NULL, 4), (11, 'summer snow', 'female' and '1993-02-03 00:00:00', '18209876579', NULL, 9), (12, 'zhao', 'female' and '1992-02-03 s 00:00:00','18209179577',NULL,1); /*Table structure for table `boys` */ DROP TABLE IF EXISTS `boys`; CREATE TABLE `boys` ( `id` int(11) NOT NULL AUTO_INCREMENT, `boyName` varchar(20) DEFAULT NULL, `userCP` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; /*Data for the table `boys` */ insert into `boys`(`id`,`boyName`,`userCP`) values (1, 'zhang mowgli, 100), (2,' Lu Han, 800), (3, 'huang xiaoming, 50), (4,' broken jade, 300); / *! 40101 SET SQL_MODE=@OLD_SQL_MODE */; / *! 40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; / *! 40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; / *! 40111 SET SQL_NOTES=@OLD_SQL_NOTES */;Copy the codeMeaning: also called multi-table query, when the query field comes from multiple tables, will be used to connect the query Cartesian product phenomenon: m lines in table 1, table 2 n lines, result = m * n line causes: the lack of an effective connection conditions How to avoid: add effective connection condition classification: according to the s classification: Sql92 standard: supports only internal connection SQL99 standard [recommended] : supports internal connection + external connection (left and right) + cross connection By function classification: Internal connection: Equivalent connection Non-equivalent connection Self-connection external connection: */ SELECT * FROM beauty; SELECT * FROM boys; SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id= boys.id; # 1, # 1 sql92 standard, contour connection / * (1) the results of the multi-table connection equivalent to table the intersection of part (2) n table joins, requires at least n - no more than 1 a join condition (3) table order requirements (4) general need for the table names (5) can match all the clauses mentioned above is used, such as sorting, grouping, filtering * / case # 1: SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id= boys.id; # Case 2: SELECT last_name,department_name FROM employees,departments WHERE employees.`department_id`=departments.`department_id`; Alter table alias (*); alter table alias (*); If the table is aliased, The query of the field will not be able to use the original name of the table to limit * / # query staff name, job number, type of work name SELECT e.l ast_name, e.j ob_id, j.j ob_title FROM employees, e jobs WHERE j e.`job_id`=j.`job_id`; # 3, whether the order of the two tables can switch # query staff name, job number, type of work name SELECT e.l ast_name, e.j ob_id, j.j ob_title FROM jobs j, WHERE employees of e e.`job_id`=j.`job_id`; #4, can add filter # case: SELECT last_name,department_name,commission_pct FROM employees e,departments D WHERE e.`department_id`=d.`department_id` AND e.`commission_pct` IS NOT NULL; # Case 2: SELECT department_name,city FROM departments D,locations L WHERE D. 'location_id' = L. 'location_id' FROM departments D,locations L WHERE D. 'location_id' = L. 'location_id AND city LIKE '_o%'; #5, Can add groups # Case 1: SELECT COUNT(*),city FROM departments D,locations l WHERE D. 'location_id' = L. 'location_id' GROUP BY city; # Case 2: SELECT department_name, D. 'manager_id',MIN(salary) FROM DEPARTMENTS D,employees e WHERE department_name, manager_id,MIN(salary) FROM departments D,employees e WHERE d.`department_id`=e.`department_id` AND commission_pct IS NOT NULL GROUP BY department_name,d.`manager_id`; #6, can add sort # example: Query the name of each job and the number of employees, SELECT job_title,COUNT(*) FROM employees e,jobs j WHERE e. 'job_id' = J. 'job_id' GROUP BY job_title ORDER BY COUNT(*) DESC; #7, can implement three table join? # case: SELECT last_name,department_name,city FROM employees e,departments D,locations l WHERE e.`department_id`=d.`department_id` AND d.`location_id`=l.`location_id` AND city LIKE 's%' ORDER BY department_name DESC; #2. Non-equivalent connection # case 1: SELECT salary,grade_level FROM employees e,job_grades G WHERE salary BETWEEN g. 'lowest_sal' AND g.`highest_sal` AND g.`grade_level`='A'; /* select salary,employee_id from employees; select * from job_grades; CREATE TABLE job_grades (grade_level VARCHAR(3), lowest_sal int, highest_sal int); INSERT INTO job_grades VALUES ('A', 1000, 2999); INSERT INTO job_grades VALUES ('B', 3000, 5999); INSERT INTO job_grades VALUES('C', 6000, 9999); INSERT INTO job_grades VALUES('D', 10000, 14999); INSERT INTO job_grades VALUES('E', 15000, 24999); INSERT INTO job_grades VALUES('F', 25000, 40000); */ SELECT e.employee_id, e.list_name,m.employee_id, m.list_name FROM employees e,employees m WHERE SELECT e.employee_id, e.list_name FROM employees e,employees m WHERE e.`manager_id`=m.`employee_id`;Copy the codecase
#1. Display all employees' names, department numbers, and department names. USE myemployees; SELECT last_name,d.department_id,department_name FROM employees e,departments d WHERE e.`department_id` = d.`department_id`; SELECT job_id,location_id FROM employees e,departments D WHERE employees_id, employees_id, employees_id, employees_id, employees_id, employees_id, employees_id, employees_id FROM employees e,departments D WHERE e.`department_id`=d.`department_id` AND e.`department_id`=90; SELECT last_name, department_name, location_id, city SELECT last_name, department_name, l.location_id, city FROM employees e,departments d,locations l WHERE e.department_id = d.department_id AND d.location_id=l.location_id AND e.commission_pct IS NOT NULL; SELECT last_name, job_id, department_id, department_name from city employees working in Toronto SELECT last_name, job_id, department_name d.department_id , department_name FROM employees e,departments d ,locations l WHERE e.department_id = d.department_id AND d.location_id=l.location_id AND city = 'Toronto'; SELECT department_name,job_title,MIN(salary) minimum salary FROM employees e,departments D,jobs J WHERE e.`department_id`=d.`department_id` AND e.`job_id`=j.`job_id` GROUP BY department_name,job_title; BCC SELECT country_id,COUNT(*) Number of departments FROM DEPARTMENTS D,locations l WHERE departments exist D. 'location_id' = L. 'location_id' GROUP BY country_id HAVING departments >2; #7 select the name and employee number of the designated employee and the name and employee number of his manager. The result is similar to the following format: employees Emp# manager GRR # kochhar 101 king 100 SELECT e.last_name employees,e.employee_id "Emp#",m.last_name manager,m.employee_id "Mgr#" FROM employees e,employees m WHERE e.manager_id = m.employee_id AND e.last_name='kochhar'; cCopy the codeSql99 syntax connection query
Join table 2 alias on join condition [WHERE filter condition] [group by group] [having filter condition] [order by sort list] category: Outside the inside connection (u) : inner connection left (u) : left right (u) : outer 】 【 right outside the whole outer 】 【 : full cross connection: outer 】 【 cross * / # 1) connection / * grammar: Select from table 1 inner join table 2 alias on join condition; Equivalent non-equivalent self-connection features: Select * from inner join where select * from inner join where select * from inner join where select * from inner join where select * from inner join where select * from inner join where select * from inner join where select * from inner join where select * from inner join where select * from inner join where select * from inner join SQL > query intersection of multiple tables */ #1; SELECT last_name,department_name FROM DEPARTMENTS D JOIN employees e ON E. 'department_id' = D. 'department_id'; SELECT last_name,job_title FROM employees e INNER JOIN jobs j ON e. 'job_id' = J. 'job_id' SELECT last_name,job_title FROM employees e INNER JOIN jobs j ON E. 'job_id' = J. 'job_id' WHERE e.`last_name` LIKE '%e%'; Select * from city where number of departments >3; SELECT * FROM departments where departments have been selected,COUNT(*) the number of departments FROM departments where departments have been selected d.`location_id`=l.`location_id` GROUP BY city HAVING COUNT(*)>3; Select * from department where number of employees >3; SELECT COUNT(*),department_name FROM employees e INNER JOIN departments D ON E. 'department_id' = D. 'department_id' GROUP BY department_name #② SELECT COUNT(*) FROM employees e INNER JOIN DEPARTMENTS D ON E. 'department_id' = D. 'department_id' GROUP BY department_name HAVING COUNT(*)>3 ORDER BY COUNT(*) DESC; #5. Query employee name, department name, job type name, SELECT last_name,department_name,job_title FROM employees e INNER JOIN DEPARTMENTS D ON e.`department_id`=d.`department_id` INNER JOIN jobs j ON e.`job_id` = j.`job_id` ORDER BY department_name DESC; SELECT salary,grade_level FROM employees e JOIN job_grades G ON E. 'salary' BETWEEN G. 'lowest_sal' AND g.`highest_sal`; Select * from salary level where number >20; SELECT COUNT(*),grade_level FROM employees e JOIN job_grades G ON E. 'salary' BETWEEN G. 'lowest_sal' AND g.`highest_sal` GROUP BY grade_level HAVING COUNT(*)>20 ORDER BY grade_level DESC; SELECT e. cast_name, m.cast_name FROM employees e JOIN employees m ON e. 'manager_id' = m.`employee_id`; SELECT e. ast_name,m. ast_name FROM employees e JOIN employees m ON e. 'manager_id' = m.`employee_id` WHERE e.`last_name` LIKE '%k%'; Mysql > select * from table_name where table_name = '1' and table_name = '1' 1, the outer join query results is given priority to the all records in the table If there is and it matches from the table, display the match value If not from the table and it matches, is displayed outside the null connection query results + = inside connection in the main table and 2 records in the table no, left outer join, left the join on the left is the main table Right connection, Right the join on the right is the main table 3, left and right to exchange the order of the two tables, can achieve the same effect. 4, the result of the connection within full outer join = + in table 1 have but there is no table 2 + in table 2 but no * / table 1 # introduction: SELECT * FROM beauty; SELECT * FROM boys; SELECT * FROM boys bo LEFT OUTER JOIN beauty b ON B. 'boyfriend_id' = bo. 'id' WHERE B. 'id' IS NULL; # Case 1: SELECT D.*, e.department_id FROM DEPARTMENTS D LEFT OUTER JOIN employees e ON D. 'department_id' = e.`department_id` WHERE e.`employee_id` IS NULL; SELECT D.*, E.departyee_id FROM employees e RIGHT departments DEPARTMENTS D ON D. 'department_id' = E. 'department_id' WHERE e.`employee_id` IS NULL; # USE girls; SELECT b.*,bo.* FROM beauty b FULL OUTER JOIN boys bo ON b.`boyfriend_id` = bo.id; SELECT b.*,bo.* FROM beauty b CROSS JOIN boys bo; Sql99 implements separation of connection conditions and filter conditions, which is more readable */Copy the codeMultiple table joins
Select * from user where id >3; select * from user where id >3; SELECT B.id, B.name,bo.* FROM beauty B LEFT OUTER JOIN boys bo ON B. 'boyfriend_id' = bo. 'id' WHERE B. 'id' >3; SELECT city FROM departments D RIGHT OUTER JOIN LOCATIONS L ON D. 'location_id' = L. 'location_id' WHERE departments 1 d.`department_id` IS NULL; SELECT E.*,d.department_name, D. 'department_id' FROM DEPARTMENTS D LEFT JOIN employees e ON d.`department_id` = e.`department_id` WHERE d.`department_name` IN('SAL','IT'); SELECT * FROM departments WHERE `department_name` IN('SAL','IT');Copy the codeJoin summary
The subquery
Select * from (select * from (select * from (select * from (select * from (select * from (select * from)))); Table subqueries are supported where or having: ★ Standard quantum query (single row) √ column subquery (multiple rows) √ row subquery Exists Table subqueries vary according to the number of columns and columns in the result set. Scalar subquery (only one line of a result set column) example query result set (only more than one column) muck query result set more than a row (column) table subquery (result sets general multiple lines more columns) * / # a, where or having behind / * 1, a scalar subquery (single subquery) 2, example query (how muck query) 3, row sub-query (multi-column multi-row) features: ① Subquery is placed in parentheses ② subquery is generally placed on the right side of the condition ③ standard quantum query, generally with the single line operator > <> = <= = <> subquery, generally with the multi-line operator using in, any/some, all ④ subquery execution precedence over the main query execution. The primary query criteria use the results of the subquery */ #1. ★ # Case 1: Who earns more than Abel? SELECT last_name FROM employees WHERE last_name = 'Abel' # SELECT last_name FROM employees WHERE last_name = 'Abel' # SELECT * FROM employees WHERE salary>(SELECT salary FROM employees WHERE last_name = 'Abel'); # Case 2: Select * from job_id where salary is the same as employee 141 and salary is higher than employee 143. SELECT job_id FROM employees WHERE employee_id = 141 # Select * from employees WHERE employee_id = 143 # Demanded job_id = 1) and salary > (2) SELECT last_name, job_id, salary FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141 ) AND salary>( SELECT salary FROM employees WHERE employee_id = 143 ); # Case 3: SELECT MIN(salary) FROM employees # SELECT last_name,job_id, salary FROM employees # SELECT last_name,job_id, salary FROM employees # SELECT last_name,job_id,salary FROM employees WHERE salary=(SELECT MIN(salary) FROM employees); # Case 4: SELECT MIN(salary) FROM employees WHERE department_id = 50 # SELECT MIN(salary) FROM employees WHERE department_id = 50 # SELECT MIN(salary),department_id FROM employees GROUP BY department_id #③ Satisfy min(salary)>① SELECT min(salary),department_id FROM employees GROUP BY department_id HAVING min(salary)> (SELECT MIN(salary) FROM employees WHERE department_id = 50 ); Department_id FROM employees GROUP BY department_id HAVING MIN(salary)>(SELECT salary FROM employees WHERE department_id = 250 ); ★ # Case 1: SELECT DISTINCT department_id FROM departments WHERE location_ID is 1400 or 1700 Location_id IN(1400,1700) # SELECT last_name FROM employees WHERE department_id <>ALL(SELECT DISTINCT department_id FROM departments) WHERE location_id, IN (1400170)); # Case 2: SELECT salary FROM employee where job_id is' IT_PROG 'SELECT salary FROM employee where job_id is' IT_PROG' SELECT salary FROM employee where job_id is' IT_PROG Select * from employees WHERE job_id = 'IT_PROG' #② SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ANY(SELECT DISTINCT salary FROM employees) SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ANY(SELECT DISTINCT salary FROM employees employees WHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG'; SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<(SELECT MAX(salary) FROM employees WHERE salary job_id = 'IT_PROG' ) AND job_id<>'IT_PROG'; # Case 3: SELECT last_name,employee_id,job_id,salary FROM employees where job_id is lower than 'IT_PROG'. SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<ALL( SELECT DISTINCT salary FROM employees WHERE job_id = 'IT_PROG' ) AND job_id<>'IT_PROG'; SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<(SELECT MIN(salary) FROM employees WHERE salary<(SELECT last_name,employee_id,job_id,salary FROM employees WHERE salary<(SELECT MIN(salary) FROM employees WHERE salary< job_id = 'IT_PROG' ) AND job_id<>'IT_PROG'; Select * from subquery (result set with multiple columns or rows); Query information about the employee with the smallest employee id and the highest salary. SELECT * FROM employees WHERE (employee_id,salary)=(SELECT MIN(employee_id),MAX(salary) FROM employees ); SELECT MIN(employee_id) FROM employees #② Query maximum salary SELECT MAX(salary) FROM employees #③ Query employee information SELECT * FROM employees # employees WHERE employee_id=( SELECT MIN(employee_id) FROM employees )AND salary=( SELECT MAX(salary) FROM employees ); Select * from (select * from (select * from)); Query the number of employees in each department SELECT D.*,(SELECT COUNT(*) FROM employees e WHERE e.department_id = D. 'department_id') number FROM departments d; # Case 2: Query department_name =102 SELECT (SELECT department_name,e.department_id FROM departments D INNER JOIN employees e ON D. detment_id = e.detment_id WHERE e.mployee_id =102) Department name; SQL > select * from table where subquery result = 'table'; SELECT AVG(salary),department_id FROM employees GROUP BY department_id SELECT * FROM department_id SELECT * FROM department_id SELECT * FROM department_id job_grades; Select * from job_grades; select * from job_grades; Between lowest_sal and highest_sal SELECT AG_depp.*,g. 'grade_level' FROM (SELECT AVG(salary) AG,department_id FROM employees GROUP BY department_id ) ag_dep INNER JOIN job_grades g ON ag_dep.ag BETWEEN lowest_sal AND highest_sal; A complete query statement exists in this case. 1 or 0 */ SELECT EXISTS(SELECT employee_id FROM employees WHERE salary=300000); # Case 1: Query department name #in SELECT department_name FROM departments D WHERE D. 'department_id' in (SELECT department_id FROM employees) ) #exists SELECT department_name FROM departments d WHERE EXISTS( SELECT * FROM employees e WHERE d.`department_id`=e.`department_id` ); # Case 2: #in SELECT bo.* FROM boys bo WHERE bo.id NOT in (SELECT boyfriend_id FROM beauty) #exists SELECT bo.* FROM boys bo boys bo WHERE NOT EXISTS( SELECT boyfriend_id FROM beauty b WHERE bo.`id`=b.`boyfriend_id` );Copy the codecase
SELECT department_id FROM employees WHERE last_name = 'Zlotkey' SELECT department_id FROM employees WHERE last_name = 'Zlotkey' SELECT last_name,salary FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE department_id = 1) SELECT last_name,salary FROM employees WHERE department_id = (SELECT department_id FROM employees WHERE department_id = 1 last_name = 'Zlotkey' ) #2. Query employee numbers, names, and salaries of employees whose salaries are higher than the company average. SELECT AVG(salary) FROM employees # SELECT AVG(salary) FROM employees # SELECT AVG(salary) FROM employees # SELECT last_name,employee_id,salary FROM employees WHERE salary>( SELECT AVG(salary) FROM employees ); #3. Select * from employees in each department whose salary is higher than the average salary of the department. SELECT AVG(salary),department_id FROM employees GROUP BY department_id #② join department_id SELECT employee_id,last_name,salary, e.partment_id FROM employees e INNER JOIN (SELECT AVG(salary) FROM employees e INNER JOIN (SELECT AVG(salary)) ag,department_id FROM employees GROUP BY department_id ) ag_dep ON e.department_id = ag_dep.department_id WHERE salary>ag_dep.ag ; SELECT DISTINCT department_id FROM employees WHERE last_name LIKE SELECT employee DISTINCT department_id FROM employees WHERE last_name LIKE SELECT employee DISTINCT department_id FROM employees WHERE last_name LIKE SELECT last_name,employee_id FROM employees WHERE department_id IN(SELECT DISTINCT) SELECT last_name,employee_id FROM employees WHERE department_id IN(SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%' ); SELECT DISTINCT department_id FROM departments WHERE departments location_ID = 1700 SELECT DISTINCT department_id FROM departments WHERE departments location_id = 1700 SELECT employee_id FROM employees WHERE department_id =ANY(SELECT DISTINCT FROM department_id WHERE department_id =ANY department_id FROM departments WHERE location_id = 1700 ); SELECT employee_id FROM employees WHERE last_name = 'K_ing' #② SELECT manager_id FROM employees WHERE last_name = 'K_ing SELECT last_name,salary FROM employees WHERE manager_id IN(SELECT employee_id FROM employees WHERE last_name = 'K_ing' ); Select last_name, last_name, first_name from last_name where last_name = last_name; SELECT MAX(salary) FROM employees #② SELECT MAX(salary) FROM employees #② Name SELECT CONCAT(first_name,last_name) "surname. Name "FROM employees WHERE salary=(SELECT MAX(salary) FROM employees);Copy the codeThe classic case of subqueries
# 1. Query information about the lowest paid employee: SELECT last_name,salary # FROM employees # SELECT last_name,salary # FROM employees # SELECT last_name,salary FROM employees WHERE salary=(SELECT MIN(salary) FROM employees); # 2. Select the sector with the lowest average wage SELECT AVG(salary),department_id FROM employees GROUP BY department_id #② SELECT MIN(ag) FROM (department_id) SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id) ag_dep #③ SELECT department_id FROM employees GROUP BY department_id AVG(salary),department_id FROM employees GROUP BY department_id HAVING AVG(salary)=( SELECT MIN(ag) FROM ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ) ag_dep ); SELECT D.* FROM DEPARTMENTS WHERE D. 'department_id' =(SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary)=( SELECT MIN(ag) FROM ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ) ag_dep ) ); # Method 2: SELECT AVG(salary),department_id FROM employees GROUP BY department_id # SELECT department_id FROM employees GROUP BY department_id # FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1; SELECT * FROM departments WHERE department_id=(SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1 ); SELECT AVG(salary),department_id FROM employees GROUP BY department_id SELECT AVG(salary),department_id FROM employees GROUP BY department_id SELECT AVG(salary),department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1; SELECT *, AG FROM departments D JOIN (SELECT AVG(salary) AG,department_id FROM employees GROUP BY departments department_id ORDER BY AVG(salary) LIMIT 1 ) ag_dep ON d.`department_id`=ag_dep.department_id; SELECT AVG(salary),job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC SELECT * FROM jobs WHERE job_id=(SELECT job_id FROM employees GROUP BY job_id ORDER BY AVG(salary)) DESC LIMIT 1 ); # 5. Where is the average salary higher than the average salary of the company? SELECT AVG(salary),department_id FROM employees GROUP BY SELECT AVG(salary) FROM employees GROUP BY Department_id #③ Screening ② result set, SELECT AVG(salary),department_id FROM employees GROUP BY department_id HAVING AVG(salary)>(SELECT AVG(salary) FROM department_id FROM employees ); SELECT DISTINCT Manager_id FROM employees SELECT DISTINCT Manager_id FROM employees Employee_id =① SELECT * FROM employees WHERE employee_id= ANY(SELECT DISTINCT manager_id FROM employees); SELECT department_id FROM employees GROUP BY department_id ORDER FROM department_id SELECT department_id FROM employees GROUP BY department_id ORDER FROM department_id BY MAX (salary) LIMIT 1 # 2) query (1) the results of the minimum wage for that department SELECT MIN (salary), department_id FROM employees WHERE department_id = (SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX(salary) LIMIT 1 ); # 8. Query the manager of the department with the highest average salary: last_name, department_id, email, SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1 # 2 Connect employees to departments, SELECT last_name, d.partment_id, email, salary FROM employees e INNER JOIN departments d ON d.manager_id = e.employee_id WHERE d.department_id = (SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1) ;Copy the codePaging query
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * Select query list from table [join type join table 2 ON JOIN criteria WHERE group by having criteria order by having criteria] limit 【 offset, size; Offset Indicates the start index of the items to be displayed (the start index starts from 0) size Indicates the number of items to be displayed. Size select query list from table limit (page-1)*size,size; SELECT * FROM employees LIMIT 0,5; SELECT * FROM employees LIMIT 0,5; SELECT * FROM employees LIMIT 5; SELECT * FROM employees LIMIT 10,15; SELECT * FROM employees WHERE commission_pct IS NOT NULL ORDER BY salary DESC LIMIT 10;Copy the codeThe joint query
/* union: combine the results of multiple query statements into one result. Syntax: query statement 1 union query statement 2 union... Application scenario: The results to be queried from multiple tables, and multiple tables have no direct connection, but the query information is consistent features: ★ 1, the number of query statements is consistent query columns! Select * from union all where */ # is a duplicate item. Select * from union all where */ # is duplicate. SELECT * FROM employees WHERE email LIKE '%a%' OR department_id>90; SELECT * FROM employees WHERE email LIKE '%a%' UNION SELECT * FROM employees WHERE department_id>90; # case: SELECT id,cname FROM t_ca WHERE csex=' male 'UNION ALL SELECT t_id,tname FROM t_ua WHERE csex=' male tGender='male';Copy the codeSelect statement summary
From 表名 where... The group by... . Having... Order by... order by... order by... . 1. Run the WHERE statement to filter raw data 2. Run the group by command to group data 3. Run the HAVING command to group data 4. Note: Rule: If you can filter the data in WHERE, try to filter the data in WHERE efficiently. The filtering function of HAVING is used to filter the data after grouping.Copy the codeData addition, deletion and modification
Insert into table name (table name, table name, table name, table name, table name, table name) Values (1,...). ; */ SELECT * FROM beauty; # 1. INSERT the type of the value to be consistent with the type of column or compatible INSERT INTO beauty (id, NAME, sex, borndate, phone, photo, boyfriend_id) VALUES (13, 'Tang Yixin', 'woman', '1990-4-23', '1898888888', NULL, 2); #2. A value must not be inserted for a column that is null. How do I insert a value into a null-capable column? # Method 1: INSERT INTO beauty (id, NAME, sex, borndate, phone, photo, boyfriend_id) VALUES (13, 'Tang Yixin', 'woman', '1990-4-23', '1898888888', NULL, 2); INSERT INTO beauty(id,NAME,sex,phone) VALUES(15,' nz ',' x ','13888888 '); INSERT INTO beauty(NAME,sex,id,phone) VALUES(' 男 ',' 男 ',16,'110'); INSERT INTO beauty(NAME,sex,id,phone) VALUES(' 男 ','女',17,'110'); INSERT INTO beauty VALUES(18,' zhang ',' male ',NULL,'119',NULL,NULL); Insert into table_name set table_name = table_name, table_name = table_name, table_name = table_name */ INSERT INTO beauty SET id=19,NAME=' phone ',phone='999'; INSERT INTO beauty VALUES(23,' tang Yixin 1',' female ','1990-4-23','189888888 ',NULL,2) Tang Yixin, 24, '2', 'woman', '1990-4-23', '1898888888', NULL, 2), (25, 'Tang Yixin 3', 'woman', '1990-4-23', '1898888888', NULL, 2); INSERT INTO beauty(id,NAME,phone) SELECT 26,' s1 ','11809866'; INSERT INTO beauty(id,NAME,phone) SELECT id,boyname,'1234567' FROM boys WHERE id<3; Update table name set column = new value, column = new value, update table name set column = new value... Where filter criteria; 2. Modify the multi-table record [supplementary] syntax: sql92 syntax: update the alias table 1, table 2 alias set column = value,... Where join condition and filter condition; Sql99 syntax: update the alias table 1 inner | left | right join table 2 alias on connection conditions set column = value,... Where filter criteria; UPDATE beauty SET phone = '13899888899' WHERE NAME LIKE '%'; UPDATE boys SET boyname=' boyname ',usercp=10 WHERE id=2; #2. Modify multiple table records # case 1: UPDATE boys bo INNER JOIN beauty B ON bo. 'id' = B. 'boyfriend_id' SET B. 'phone' ='119',bo. 'userCP' =1000 WHERE bo. 'boyName' =' boyName '; # Case 2: UPDATE boys bo RIGHT JOIN beauty b ON bo. 'id' =b. 'boyfriend_id' SET B. 'boyfriend_id' =2 WHERE bo. 'id IS NULL; SELECT * FROM boys; SQL > select * from table where SQL > select * from table where SQL > select * from table where SQL > select * from table where SQL > select * from table where SQL > select * from table where SQL > select * from table where SQL > delete Delete alias from table 1, alias from table 2 where join condition and filter condition; Sql99 syntax: delete the alias table 1, table 2 alias from table 1 alias inner | left | right alias on connection conditions where the join table 2 filter condition; Truncate Syntax: TRUNCate table table name. Delete FROM beauty WHERE phone LIKE '%9'; delete FROM beauty WHERE phone LIKE '%9'; SELECT * FROM beauty; #2. Delete multiple tables DELETE b FROM beauty B INNER JOIN boys bo ON B. 'boyfriend_id' = bo. 'id' WHERE bo. 'boyName' =' zhang '; # case: DELETE b,bo FROM beauty B INNER JOIN boys bo ON B. 'boyfriend_id' =bo. 'id' WHERE bo. 'boyName' =' xiaomin '; Truncate TABLE BOYS; truncate TABLE boys; Truncate 【 interview question ★】 /* 1. Delete where truncate cannot be added. If the table to be deleted has self-growing columns and data is inserted after the DELETE command is used, the self-growing column starts from the breakpoint. If the TRUNCate command is used to insert data, the self-growing column starts from 1. Truncate delete does not have a return value, while delete delete has a return value. DELETE FROM boys; TRUNCATE TABLE boys; INSERT INTO boys (boyname,usercp) VALUES(' f ',100),(' f ',100),(' F ',100);Copy the codeAddition, deletion and modification of case data
Create table my_employees USE myemployees; CREATE TABLE my_employees( Id INT(10), First_name VARCHAR(10), Last_name VARCHAR(10), Userid VARCHAR(10), Salary is a DOUBLE (10, 2)); CREATE TABLE users( id INT, userid VARCHAR(10), department_id INT ); #2. Display table my_employees' structure DESC my_employees; ID FIRST_NAME LAST_NAME USERID SALARY 1 patel Ralph Rpatel 895 2 Dancs Betty Bdancs 860 3 Biri Ben Bbiri 1100 4 Newman Chad Cnewman 750 5 Ropeburn Audrey Aropebur 1550 # INSERT INTO my_employees VALUES(1,'patel','Ralph','Rpatel',895), (2,'Dancs','Betty','Bdancs',860), (3,'Biri','Ben','Bbiri',1100), (4,'Newman','Chad','Cnewman',750), (5,'Ropeburn','Audrey','Aropebur',1550); DELETE FROM my_employees; # Method 2: INSERT INTO my_employees SELECT 1,'patel','Ralph','Rpatel',895 UNION SELECT 2,'Dancs','Betty','Bdancs',860 UNION SELECT 3,'Biri','Ben','Bbiri',1100 UNION SELECT 4,'Newman','Chad','Cnewman',750 UNION SELECT 5,'Ropeburn','Audrey','Aropebur',1550; Rpatel 10 2 Bdancs 10 3 Bbiri 20 4 Cnewman 30 5 Aropebur 40 INSERT INTO users VALUES(1,'Rpatel',10) (2,'Bdancs',10), (3,'Bbiri',20); UPDATE my_employees SET last_name='drelxer' WHERE id = 3; UPDATE my_employees SET salary=1000 WHERE salary<900; DELETE u,e FROM users u JOIN my_employees e ON U. 'userid' = E. 'userid' WHERE userid = Bbiri u.`userid`='Bbiri'; DELETE FROM my_employees; DELETE FROM users; SELECT * FROM my_employees; SELECT * FROM users; My_employees TRUNCATE TABLE my_employees;Copy the codeManagement of libraries and tables
Alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table Create database [if not exists] Database name; CREATE DATABASE IF NOT EXISTS Books; RENAME DATABASE books TO a new library name; ALTER DATABASE books CHARACTER SET GBK; DROP DATABASE IF EXISTS books; Management # 1, # 2, table. The table of the painted create / * grammar: create table table name (the column name column type (length) constraints, 】 the column column type (length) constraints, 】 the column column type (length) constraints, 】... */ # Example: Book CREATE TABLE Book (id INT,# id bName VARCHAR(20),# price DOUBLE,# price authorId INT,# authorId publishDate DATETIME# publication date); DESC book; Author CREATE TABLE IF NOT EXISTS author(id INT, au_name VARCHAR(20), nation VARCHAR(10)) DESC author; # 2. The table changes / * grammar alter table table name add | drop | the modify | change column column name "column type constraint"; ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME; ALTER TABLE book MODIFY COLUMN pubdate TIMESTAMP; ALTER TABLE author ADD COLUMN annual DOUBLE; ALTER TABLE book_author DROP COLUMN annual; ALTER TABLE author RENAME TO book_author; DESC book; DROP TABLE IF EXISTS book_author; SHOW TABLES; DROP DATABASE IF EXISTS; CREATE DATABASE New DATABASE name; DROP TABLE IF EXISTS Specifies the name of the existing TABLE. CREATE TABLE name (); # 4. Copy of table INSERT INTO the author VALUES (1, 'Mr. Murakami', 'Japan'), (2, 'mo yan', 'China'), (3, 'feng', 'China'), (4, 'jin yong', 'China'); SELECT * FROM Author; SELECT * FROM copy2; CREATE TABLE copy LIKE author; CREATE TABLE copy2 SELECT * FROM author; CREATE TABLE copy3 SELECT id,au_name FROM author WHERE nation='中国'; CREATE TABLE copy4 SELECT id,au_name FROM author WHERE 0;Copy the codeCase library and table management
Create table dept1 NAME NULL? TYPE id INT(7) NAME VARCHAR(25) USE test; CREATE TABLE dept1( id INT(7), NAME VARCHAR(25) ); CREATE TABLE DEPT2 SELECT department_ID,department_name FROM myemployees.departments; Create table emp5 NAME NULL? TYPE id INT(7) First_name VARCHAR (25) Last_name VARCHAR(25) Dept_id INT(7) CREATE TABLE emp5( id INT(7), first_name VARCHAR(25), last_name VARCHAR(25), dept_id INT(7) ); ALTER TABLE emp5 MODIFY COLUMN Last_name VARCHAR(50); CREATE TABLE employees2 LIKE myemployees.employees; CREATE TABLE employees2 LIKE myemployees.employees; Emp5 DROP TABLE IF EXISTS emp5; ALTER TABLE employees2 RENAME TO EMP5; ALTER TABLE emp5 ADD COLUMN test_column INT ALTER TABLE emp5 ADD COLUMN test_column INT; Select * from emp5 where dept_id DESC EMP5; ALTER TABLE emp5 DROP COLUMN test_column;Copy the codeThe data type
# Common data types /* Numeric type: integer decimal: fixed point number Floating point number character type: short text: char, varchar Long text: text, blob date type: */ # one, integer /* classification: Tinyint, SmallINT, mediumInt, int/ INTEGER, bigInt 1 2 3 4 8 If you want to set the value to unsigned, add the unsigned keyword. If you want to set the value to unsigned, add the unsigned keyword. If you want to set the value to unsigned, add the unsigned keyword. There will be a default length length representing the maximum width of the display, if not enough will be filled with 0 on the left, but must be used with zerofill! DROP TABLE IF EXISTS tab_int; CREATE TABLE tab_int( t1 INT(7) ZEROFILL, t2 INT(7) ZEROFILL ); DESC tab_int; INSERT INTO tab_int VALUES(-123456); INSERT INTO tab_int VALUES(-123456,-123456); INSERT INTO tab_int VALUES (2147483648429967, 296); INSERT INTO tab_int VALUES (123123); SELECT * FROM tab_int; Double (M,D) 2 float(M,D) double(M,D) 2 Decimal (M,D) decimal(M,D) If decimal places are out of range, insert critical values ② both M and D can be omitted. If decimal places are decimal, M defaults to 10 and D defaults to 0. If float and double, the precision is determined by the precision of the value being inserted. */ # test M and D DROP TABLE tab_float; CREATE TABLE tab_float( f1 FLOAT, f2 DOUBLE, f3 DECIMAL ); SELECT * FROM tab_float; DESC tab_float; INSERT INTO tab_float VALUES (123.4523, 123.4523, 123.4523); INSERT INTO tab_float VALUES (123.456, 123.456, 123.456); INSERT INTO tab_float VALUES (123.4, 123.4, 123.4); INSERT INTO tab_float VALUES (1523.4, 1523.4, 1523.4); /* Select as simple a type as possible, and as small a type that can hold values as possible */ Binary and varbinary are used to hold short binary enums are used to hold enumerations sets are used to hold sets long text: text blob(larger binary) features: The default value is 1. Fixed-length characters cost more. Varchar VARCHar (M) The maximum number of characters. */ CREATE TABLE tab_CHAR (C1 ENUM('a','b','c')); INSERT INTO tab_char VALUES('a'); INSERT INTO tab_char VALUES('b'); INSERT INTO tab_char VALUES('c'); INSERT INTO tab_char VALUES('m'); INSERT INTO tab_char VALUES('A'); SELECT * FROM tab_set; CREATE TABLE tab_set( s1 SET('a','b','c','d') ); INSERT INTO tab_set VALUES('a'); INSERT INTO tab_set VALUES('A,B'); INSERT INTO tab_set VALUES('a,c,d'); Timestamp save date + time timestamp save date + time Datetime 8 1000 -- 9999 timestamp 4 1970-2038 timestamp */ CREATE TABLE tab_date(T1 datetime, T2 timestamp); INSERT INTO tab_date VALUES(NOW(),NOW()); SELECT * FROM tab_date; SHOW VARIABLES LIKE 'time_zone'; SET time_zone='+9:00';Copy the codeCommon constraints
NOT NULL: NOT NULL: NOT NULL: NOT NULL; Non-empty, used to ensure that the value of the field cannot be empty, such as the name and student ID. DEFAULT: used to ensure that the field has DEFAULT values, such as gender. PRIMARY KEY: used to ensure that the value of the field is UNIQUE and non-empty, such as student ID and employee ID. FOREIGN KEY: used to restrict the relationship between two tables. Used to ensure that the value of this field must come from the associated column of the primary table. The value used to reference a column in the main table, such as the major number of the student table, the department number of the employee table, and the job number of the employee table. Alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table alter table Unique allowed empty How many primary keys can a table have combined primary keys √ × At most 1 √, but not recommended Unique √ √ Multiple √, but not recommended foreign keys: 1, requirements in setting foreign key relationship from table 2, from the table, the foreign key column connection types and the main table column type requirements consistent or compatible, no name 3, the link between the main table column must be a key (usually the primary key or unique) 4, insert data, first insert the main table, insert again from the table Delete the data, first remove from the table, */ CREATE TABLE name CREATE DATABASE students; Add a constraint type to the column name and type of the column. Only: default, non-empty, primary key, unique */ USE students; DROP TABLE stuinfo; CREATE TABLE stuinfo(id INT PRIMARY KEY,# stuName VARCHAR(20) NOT NULL UNIQUE,# gender CHAR(1) CHECK(gender=' male 'OR Gender =' female '),# check seat INT UNIQUE,# age INT DEFAULT 18,# DEFAULT constraint majorId INT REFERENCES Major (id)# foreign key); CREATE TABLE major( id INT PRIMARY KEY, majorName VARCHAR(20) ); # SHOW INDEX FROM stuinfo; # SHOW INDEX FROM stuinfo; [constraint name] */ DROP TABLE IF EXISTS stuinfo; CREATE TABLE stuinfo( id INT, stuname VARCHAR(20), gender CHAR(1), seat INT, age INT, majorid INT, CONSTRAINT PRIMARY KEY(ID),# CONSTRAINT uq UNIQUE(seat),# CONSTRAINT ck CHECK(gender =' male 'OR gender =' female '),# CHECK CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorID) REFERENCES Major (id)# SHOW INDEX FROM stuinfo; # Common usage: ★ CREATE TABLE IF NOT EXISTS stuinfo(id INT PRIMARY KEY, stuname VARCHAR(20), sex CHAR(1), age INT DEFAULT 18, seat INT UNIQUE, majorid INT, CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) ); Alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name alter table table_name Alter table alter table add [constraint 表名] add [constraint 表名] */ DROP TABLE IF EXISTS stuinfo; CREATE TABLE stuinfo( id INT, stuname VARCHAR(20), gender CHAR(1), seat INT, age INT, majorid INT ) DESC stuinfo; ALTER TABLE STUinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL; ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18; ALTER TABLE stuinfo MODIFY COLUMN ID INT PRIMARY KEY; ALTER TABLE stuinfo ADD PRIMARY KEY(id); ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE; ALTER TABLE stuinfo ADD UNIQUE(seat); ALTER TABLE STUinfo ADD CONSTRAINT fk_STUinfo_major FOREIGN KEY(majorID) REFERENCES Major (id); ALTER TABLE STUinfo MODIFY COLUMN stuname VARCHAR(20) NULL; ALTER TABLE stuinfo MODIFY COLUMN age INT; ALTER TABLE stuinfo DROP PRIMARY KEY; ALTER TABLE stuinfo DROP INDEX seat; ALTER TABLE STUinfo DROP FOREIGN KEY fk_stuinfo_major; SHOW INDEX FROM stuinfo; # Cascade delete ALTER TABLE STUinfo ADD CONSTRAINT fk_STU_major FOREIGN KEY(majorID) REFERENCES Major (ID) ON DELETE CASCADE; # Method 2: ALTER TABLE STUinfo ADD CONSTRAINT Fk_STU_major FOREIGN KEY(majorID) REFERENCES Major ON DELETE SET NULL; SQL > delete from main tableCopy the codeCommon constraints of cases
ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY; ALTER TABLE emp2 MODIFY COLUMN id INT PRIMARY KEY; ALTER TABLE emp2 ADD CONSTRAINT my_emp_id_pk PRIMARY KEY(id); Alter table dept2 add PRIMARY KEY (my_depT_ID_pk); Add column DEPt_ID to table EMP2 and define a FOREIGN KEY constraint with the id column associated with the DEPT2 table. ALTER TABLE emp2 ADD COLUMN dept_id INT; ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept2 FOREIGN KEY(dept_id) REFERENCES dept2(id); Table level constraint: all columns below default and non-empty are not supported, other support is possible (primary key has no effect).Copy the code1, Do id columns have to be matched with primary keys? 2. How many identifying columns can a table have? One at most! SET auto_increment_increment=3 increment_increment=3 DROP TABLE IF EXISTS tab_identity; DROP TABLE IF EXISTS tab_identity; CREATE TABLE tab_identity( id INT , NAME FLOAT UNIQUE AUTO_INCREMENT, seat INT ); TRUNCATE TABLE tab_identity; INSERT INTO tab_identity(id,NAME) VALUES(NULL,'john'); INSERT INTO tab_identity(NAME) VALUES('lucy'); SELECT * FROM tab_identity; SHOW VARIABLES LIKE '%auto_increment%'; SET auto_increment_increment=3; ALTER TABLE tab_identity MODIFY COLUMN ID INT PRIMARY KEY AUTO_INCREMENT; ALTER TABLE tab_identity MODIFY COLUMN ID INT;Copy the code