您的位置: 首页  >  文章  >  Mysql杂记（二）查询基础

Mysql杂记（二）查询基础

分类: 文章 • 2024-06-14 18:53:40

去除重复DISTINCT

SELECT DISTINCT NAME FROM emp；

如何显示工资高于3000的员工
SELECT * FROM emp WHERE sal>=3000;

如何查找1980.1.1后入职的员工？
SELECT * FROM emp WHERE hiredate>'1981-01-01';
--在oracle中需要将字符串转换成日期to_date()，to_char()

如何查询1980年入职的员工？
mysql中的三个日期函数：year(),MONTH(),DAY()
SELECT * FROM emp WHERE YEAR(hiredate) = '1980';

如何显示工资在2000到2500的员工情况？
关系运算符:>,>=,<,<=,!=,<>(不等于)
BETWEEN

SELECT * FROM emp WHERE sal>=2000 AND sal<=2500;
SELECT * FROM emp WHERE sal BETWEEN 2000 AND 2500;

模糊查询like
_:字符字符
%:一个或多个字符
在模糊查询中，严格区分大小写

如何显示首字符为S的员工姓名和工资？
SELECT ename,sal FROM emp WHERE ename LIKE 'S%';

如何显示第三个字符为大写O的所有员工的姓名和工资？
SELECT ename,sal FROM emp WHERE ename LIKE '__O%';

在where条件中使用in
如何显示empno为7844, 7839,123,456 的雇员情况？
SELECT * FROM emp WHERE empno IN (7844, 7839,123,456);


使用is null的操作符，如何显示没有上级的雇员的情况？
SELECT * FROM emp WHERE mgr IS NULL;

使用逻辑逻辑运算符
1、查询工资高于500或是岗位是MANAGER的雇员，
同时还要满足他们的姓名首字母为大写的J？
SELECT * FROM emp WHERE (sal>500 OR job='MANAGER') AND ename LIKE 'J%';


使用order BY 字句，默认asc
1、问题：如何按照工资的从低到高的顺序显示雇员的信息？
SELECT * FROM emp ORDER BY sal;
SELECT * FROM emp ORDER BY sal ASC;
降序 DESC
SELECT * FROM emp ORDER BY sal DESC;

2、问题：按照部门号升序而雇员的工资降序排列
SELECT * FROM emp ORDER BY deptno,sal DESC;

使用列的别名排序
1、问题：按年薪排序
SELECT *,sal*12 年薪 FROM emp ORDER BY 年薪;

表的复杂查询

数据分组 ——max，min， avg， sum， COUNT
SELECT * FROM emp;
1、问题：如何显示所有员工中最高工资和最低工资？
SELECT MAX(sal),MIN(sal) FROM emp;
2、最高工资那个人是谁？
首先查询最高工资，不要管是谁
然后将5000作为查询条件，查出最高工资的是谁
SELECT ename,sal FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

3、问题：如何显示所有员工的平均工资和工资总和？

SELECT AVG(sal),SUM(sal) FROM emp;


4、问题：如何计算总共有多少员工问题
SELECT COUNT(*) FROM emp;


扩展：
查询最高工资员工的名字，工作岗位
SELECT ename,sal,job FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);
显示工资高于平均工资的员工信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);


GROUP BY 和 having子句
GROUP BY:分组
HAVING:限制分组之后的显示结果，和where非常相似

1、问题：如何显示每个部门的平均工资和最高工资？
SELECT deptno,AVG(sal),MAX(sal) FROM emp GROUP BY deptno;
2、问题：显示每个部门的每种岗位的平均工资和最低工资？
SELECT deptno,job,AVG(sal),MIN(sal) FROM emp GROUP BY deptno,job;
3、问题：显示平均工资低于2000的部门号和它的平均工资？
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)<2000;

多表查询
一）内连接（等值连接）：查询客户姓名，订单编号，订单价格
    ---------------------------------------------------
    select c.name,o.isbn,o.price
    from customers c inner join orders o
    where c.id = o.customers_id;
    ---------------------------------------------------
    select c.name,o.isbn,o.price
    from customers c join orders o
    where c.id = o.customers_id;
    ---------------------------------------------------
    select c.name,o.isbn,o.price
    from customers c,orders o
    where c.id = o.customers_id;
    ---------------------------------------------------
    select c.name,o.isbn,o.price
    from customers c join orders o
    on c.id = o.customers_id;
    ---------------------------------------------------
    注意：内连接（等值连接）只能查询出多张表中，连接字段相同的记录

二）外连接：按客户分组，查询每个客户的姓名和订单数
    ---------------------------------------------------
    左外连接：
    select c.name,count(o.isbn)
    from  customers c left outer join orders o
    on c.id = o.customers_id
    group by c.name;
    ---------------------------------------------------
    右外连接：
    select c.name,count(o.isbn)
    from  orders o right outer join customers c   
    on c.id = o.customers_id
    group by c.name;
    ---------------------------------------------------
    注意：外连接既能查询出多张表中，连接字段相同的记录；又能根据一方，将另一方不符合相同记录强行查询出来


三）自连接：求出AA的老板是EE
    ---------------------------------------------------
    内自连接：
    select users.ename,boss.ename
    from emps users inner join emps boss
    on users.mgr = boss.empno;
    ---------------------------------------------------
    外自连接：
    select users.ename,boss.ename
    from emps users left outer join emps boss
    on users.mgr = boss.empno;
    ---------------------------------------------------
    注意：自连接是将一张表，通过别名的方式，看作多张表后，再进行连接。

      这时的连接即可以采用内连接，又可以采用外连接


笛卡尔集
1、问题：显示雇员名，雇员工资及所在部门的名字
SELECT ename,sal,dname FROM emp,dept WHERE emp.deptno=dept.deptno;
2、问题：显示部门号为10的部门名、员工名和工资？
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno AND emp.deptno=10;
3、问题：显示各个员工的姓名，工资及工资的级别？
SELECT ename,sal,grade FROM emp,salgrade
 WHERE
  sal BETWEEN salgrade.losal AND salgrade.hisal;
扩展
问题：显示雇员名，雇员工资及所在部门的名字，并按部门排序？
SELECT ename,sal,dname,emp.deptno FROM emp,dept
 WHERE emp.deptno=dept.deptno ORDER BY emp.deptno;


自连接

问题：显示某个员工的上级领导的姓名？
SELECT * FROM emp;

SELECT t.ename 员工姓名,t2.ename 上级姓名 FROM emp t,emp t2 WHERE t.mgr = t2.empno

扩展
显示所有员工的姓名，和上级领导的姓名

子查询
单行子查询
显示与SMITH同部门的所有员工？
SELECT ename,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='SMITH');

多行子查询
如何查询和部门10的工作相同的雇员的名字、岗位、工资、部门号
SELECT deptno,job FROM emp WHERE deptno=10;
SELECT deptno,job FROM emp WHERE job IN
(SELECT job FROM emp WHERE deptno=10);

在多行子查询中使用all操作符
如何显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号？
SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
SELECT * FROM emp WHERE sal> ALL(SELECT sal FROM emp WHERE deptno=30);
在多行子查询中使用any操作符
如何显示工资比部门30的任意一个员工的工资低的员工姓名、工资和部门号？
SELECT * FROM emp WHERE sal< ANY(SELECT sal FROM emp WHERE deptno=30);

多列子查询
如何查询与SMITH的部门和岗位完全相同的所有雇员。
SELECT job,deptno FROM emp WHERE ename='SMITH';
SELECT * FROM EMP WHERE (job,deptno)=(SELECT job,deptno FROM emp WHERE ename='SMITH');

在from子句中使用子查询
如何显示高于自己部门平均工资的员工的信息
SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;

SELECT * FROM emp t,
(SELECT deptno,AVG(sal) 平均工资 FROM emp GROUP BY deptno) t2
 WHERE t.deptno=t2.deptno AND t.sal>t2.平均工资;

分页查询
Limit分页关键字：限制
LIMIT a,b:a表示从那一条记录开始，b表示，每页显示多少条记录

SELECT * FROM emp LIMIT 10,10;

将一列赋值给另一列

UPDATE table set 列1 = 列2

分组排序

Mysql杂记（二）查询基础

结果

Mysql杂记（二）查询基础

SELECT a.shop_id,a.category_id, a.price, count(*) as rank
FROM testgrouporder a
JOIN testgrouporder b ON a.shop_id=b.shop_id and a.category_id = b.category_id AND a.price <= b.price
GROUP BY a.shop_id,a.category_id, a.price
ORDER BY a.shop_id,a.category_id asc,a.price desc;

相关推荐