Shulou(Shulou.com)05/31 Report--

This article mainly introduces the relevant knowledge of mysql connection case analysis, the content is detailed and easy to understand, the operation is simple and fast, and has a certain reference value. I believe you will gain something after reading this mysql connection case analysis article. Let's take a look at it.

Description

1. The internal join is similar to the natural join, but the internal join does not require comparing the attribute columns of the same name of the two tables. You can use on or using to specify the fields to compare between the two tables.

(the compared field does not require the same name).

2. The condition of internal connection can not be written, at this time, the query is Cartesian product.

Example

# the inner join of the table, which means that only the records that meet the connection conditions are kept in the result set-- method-- SELECT e.empnoree.enamestatement d.dnamecopyright-connection target FROM t_emp e JOIN t_dept dlib-connection condition ON e.deptnogend.deptno.deptno;-- method II. SELECT e.empnopary e.enamescene d.dnamewright-connection target FROM t_emp efocus dept dlib-join condition WHERE e.deptno=d.deptno -- inline exercise 1 # Internal connection of different tables # query each employee's job number, name, department name, basic salary, position, salary grade SELECT e.empnoree.ename.dnamememe.salmeme.jobdyers.gradeFROM t_emp e JOIN t_dept d ON e.deptno=d.deptnoJOIN t_salgrade s ON e.sal BETWEEN s.losal and s.hisal # Inner join of the same table 1 # query who employees in the same department of Scott have SELECT e2.enameFROM t_emp E1 JOIN t_emp e2 ON e1.deptno=e2.deptnoWHERE e1.enameame = "SCOTT" AND e2.enameplate = "SCOTT"; # Internal join II of the same table # query employee information whose base salary exceeds the company's average base salary SELECT e.empnodepartment e.deptnoree.jobFROM t_emp e JOIN (SELECT avg (sal) as avgs FROM t_emp) tON e.sal > = t.avgs # query the number of people in the RESEARCH department, maximum basic salary, minimum basic salary, average basic salary, average length of service SELECT COUNT (*), MAX (e.sal), MIN (e.sal), AVG (e.sal), FLOOR ((DATEDIFF (NOW (), e.hiredate) / 365)) FROM t_emp e JOIN t_dept d ON e.deptno=d.deptnoWHERE d.dname = "RESEARCH" SELECT FLOOR (29.9) # 29SELECT CEIL (1.1) # 1.2 # query the maximum wage, the minimum wage, the average wage, the maximum wage grade and the minimum wage grade of each occupation: SELECT e.jobj Max (e.sal+IFNULL (e.commPower0)), MIN (e.sal+IFNULL (e.commPower0)), AVG (e.sal+IFNULL (e.commco0)), MAX (s.grade) MIN (s.grade) FROM t_emp e JOIN t_salgrade sON (e.sal+IFNULL (e.commmem0)) BETWEEN s.losal AND s.hisalGROUP BY e.job # query the information of each employee whose basic salary exceeds the average basic salary of the department, SELECT e.empnoree.enamedepartment e.SalFROM t_emp e JOIN (SELECT deptno,AVG (sal) as depsal FROM t_emp GROUP BY deptno) tON e.deptno=t.deptnoAND e.sal > depsal;, that's all for the article "connection instance Analysis within mysql". Thank you for reading! I believe you all have a certain understanding of the knowledge of "connection case Analysis in mysql". If you want to learn more, you are welcome to follow the industry information channel.

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.