SQL语句实战

 DQL查询：

USE wubai;

CREATE TABLE company (
company_number INT PRIMARY key AUTO_INCREMENT,
name VARCHAR(255) DEFAULT NULL,
hiredate DATE DEFAULT NULL,
salary DOUBLE DEFAULT NULL,
bonus DOUBLE DEFAULT NULL,
department VARCHAR(255) DEFAULT NULL
);
DROP TABLE company;
INSERT INTO wubai.company(company_number,name,hiredate,salary,bonus ,department)
VALUES(null,'hdl','2010-01-01',10000,5000,'研发部门');
INSERT INTO wubai.company(company_number,name,hiredate,salary,bonus ,department)
VALUES(null,'whx','2011-01-01',11000,6000,'财政部门');
INSERT INTO wubai.company(company_number,name,hiredate,salary,bonus ,department)
VALUES(null,'szz','2012-01-01',9000,2000,'UI部门');
INSERT INTO wubai.company(company_number,name,hiredate,salary,bonus ,department)
VALUES(null,'wdl','2015-01-01',8000,3000,'UI部门');
INSERT INTO wubai.company(company_number,name,hiredate,salary,bonus ,department)
VALUES(null,'wy','2019-01-01',10000,5000,'研发部门');

--1.员工工资降序排列
SELECT * FROM wubai.company ORDER BY salary desc;
--2.根据员工工资升序排列，且查询不到财务部
SELECT * FROM wubai.company  WHERE department != '财政部门' ORDER BY salary;
--3.查询姓名第二个字非d的且薪资大于8000的员工信息，按照薪资升序
SELECT * FROM wubai.company  WHERE name NOT LIKE '_d%' AND salary >8000 ORDER BY salary desc;
--4.查询每位员工的综合年薪，根据年薪降序排列
SELECT name as 姓名 ,(salary*12+bonus) AS 年薪 FROM wubai.company   ORDER BY (salary*12+bonus) desc;
--5.求每个不同部门的平均薪水
SELECT department as 部门, avg(salary) FROM wubai.company GROUP BY department;
--6.求每个不同部门的平均薪水且平均薪资大于9000的部门
SELECT department as 部门, avg(salary) FROM wubai.company GROUP BY department  HAVING avg(salary) >9000;
--7.求每个部门的最高薪水
SELECT department as 部门, MAX(salary) FROM wubai.company GROUP BY department ;
--8.求每个部门有多少员工
SELECT department as 部门, count(*) as 员工 FROM wubai.company GROUP BY department ;
--9.查询员工最高薪资和最低薪资员工信息
SELECT * FROM wubai.company ORDER BY salary asc LIMIT 1 ;
;SELECT * FROM wubai.company ORDER BY salary  desc LIMIT 1 ;
--10.最高薪资和最低薪资差距
SELECT max(salary)-min(salary) FROM wubai.company ;
--11.查询所有员工的平均薪资
SELECT avg(salary) FROM wubai.company ;
--12.根据入职时间排序(升序或者降序)
SELECT * FROM wubai.company ORDER BY hiredate;
--13.查询2012入职的员工
SELECT * FROM wubai.company WHERE hiredate >='2012-01-01';

联表查询：

CREATE TABLE department_connection (
id INT ,
class VARCHAR(255) DEFAULT NULL,
PRIMARY key(id)
);

CREATE TABLE company_connection (
company_number INT PRIMARY key AUTO_INCREMENT,
name VARCHAR(255) DEFAULT NULL,
hiredate DATE DEFAULT NULL,
salary DOUBLE DEFAULT NULL,
bonus DOUBLE DEFAULT NULL,
department INT DEFAULT NULL,
constraint depatment_id FOREIGN KEY (department) REFERENCES department_connection(id)
);

INSERT INTO wubai.company_connection(company_number,name,hiredate,salary,bonus ,department)
VALUES(null,'hdl','2010-01-01',10000,5000,1);
INSERT INTO wubai.company_connection(company_number,name,hiredate,salary,bonus ,department)
VALUES(null,'whx','2011-01-01',11000,6000,2);
INSERT INTO wubai.company_connection(company_number,name,hiredate,salary,bonus ,department)
VALUES(null,'szz','2012-01-01',9000,2000,3);
INSERT INTO wubai.company_connection(company_number,name,hiredate,salary,bonus ,department)
VALUES(null,'wdl','2015-01-01',8000,3000,3);
INSERT INTO wubai.company_connection(company_number,name,hiredate,salary,bonus ,department)
VALUES(null,'wy','2019-01-01',10000,5000,1);
--交叉关联
SELECT * FROM company_connection,department_connection ；

SELECT * FROM company_connection, department_connection WHERE company_connection.department = department_connection.id;

--查询人数大于2的班级，并且人数降序排列
SELECT department_connection.class ,count(*) FROM company_connection, department_connection WHERE company_connection.department = department_connection.id GROUP BY department_connection.class HAVING count(*)>1 ORDER BY  count(*)  ;
---左连接 左匹配
SELECT * FROM department_connection LEFT JOIN company_connection ON department_connection.id=company_connection.department;
---右连接 右匹配
SELECT * FROM department_connection RIGHT JOIN company_connection ON department_connection.id=company_connection.department;
---内连接 都匹配
SELECT * FROM department_connection  JOIN company_connection ON department_connection.id=company_connection.department;

子查询：

--查询学生表中第一期和第三期的学生
SELECT * FROM company_connection  WHERE company_connection.department IN (SELECT id FROM department_connection WHERE class ='研发部门' or class ='财政部门');
--查询所有大于研发部门的薪资
SELECT * FROM company_connection  WHERE company_connection.salary > all(SELECT salary from company_connection where department = 1);
--1.返回员工拥有部门的员工新消息含员工部门
SELECT * FROM  company_connection AS a JOIN department_connection as b on a.department =b.id;
--2.查询员工薪资大于szz的员工信息
SELECT * FROM  company_connection where salary > (SELECT salary  FROM company_connection  WHERE name = 'szz');
-