MySQL二

最新推荐文章于 2023-02-17 11:32:23 发布

panxuej_shenx

最新推荐文章于 2023-02-17 11:32:23 发布

阅读量194

点赞数

本文链接：https://blog.csdn.net/xwcj3/article/details/103268042

版权

三种连接

/*
Navicat MySQL Data Transfer
Source Server         : pxj31
Source Server Version : 50711
Source Host           : 192.168.25.31:3306
Source Database       : pxjdata
Target Server Type    : MYSQL
Target Server Version : 50711
File Encoding         : 65001
Date: 2019-11-26 20:16:27
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for a
-- ----------------------------
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `aname` varchar(120) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of a
-- ----------------------------
INSERT INTO `a` VALUES ('1', 'pxj1', '20');
INSERT INTO `a` VALUES ('2', 'pxj2', '22');
INSERT INTO `a` VALUES ('3', 'pxj3', '23');
-- ----------------------------
-- Table structure for b
-- ----------------------------
DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bname` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
-- ----------------------------
-- Records of b
-- ----------------------------
INSERT INTO `b` VALUES ('1', 'pxj1', 'gz');
INSERT INTO `b` VALUES ('2', 'pxj2', 'gs');
内连接：
mysql> select a.*,b.address
    -> from a
    -> join b
    -> on a.id=b.id;
+----+-------+------+---------+
| id | aname | age  | address |
+----+-------+------+---------+
|  1 | pxj1  |   20 | gz      |
|  2 | pxj2  |   22 | gs      |
+----+-------+------+---------+
2 rows in set (0.00 sec)
左连接
mysql> select a.*,b.address
    -> from a
    -> left join b
    -> on a.id=b.id;
+----+-------+------+---------+
| id | aname | age  | address |
+----+-------+------+---------+
|  1 | pxj1  |   20 | gz      |
|  2 | pxj2  |   22 | gs      |
|  3 | pxj3  |   23 | NULL    |
+----+-------+------+---------+
3 rows in set (0.00 sec)
右连接
mysql> select a.*,b.address
    -> from a
    -> right join b
    -> on a.id=b.id;
+------+-------+------+---------+
| id   | aname | age  | address |
+------+-------+------+---------+
|    1 | pxj1  |   20 | gz      |
|    2 | pxj2  |   22 | gs      |
+------+-------+------+---------+
2 rows in set (0.00 sec)
总结：
左连接，以左边的表为主，为全；右表是匹配的，匹配到就显示，匹配不到就null
右连接，以右边的表为主，为全；左表是匹配的，匹配到就显示，匹配不到就null
内连接，根据id关联，两张表数据都存在的 显示，不存在的不显示
sum
mysql> select id,sum(age)
    -> from a
    -> group by id;
+----+----------+
| id | sum(age) |
+----+----------+
|  1 |       20 |
|  2 |       22 |
|  3 |       23 |
+----+----------+
3 rows in set (0.00 sec)
count
mysql> select count(*)
    -> from a
    -> ;
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

SQL示例

部门表
dept部门表(deptno部门编号/dname部门名称/loc地点)
create table dept (
    deptno numeric(2),
    dname varchar(14),
    loc varchar(13)
);
insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');
--工资等级表
salgrade工资等级表(grade 等级/losal此等级的最低/hisal此等级的最高)
create table salgrade (
    grade numeric,
    losal numeric,
    hisal numeric
);
insert into salgrade values (1, 700, 1200);
insert into salgrade values (2, 1201, 1400);
insert into salgrade values (3, 1401, 2000);
insert into salgrade values (4, 2001, 3000);
insert into salgrade values (5, 3001, 9999);
-- 员工表
-- emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
-- 工资 ＝ 薪金 ＋ 佣金
create table emp (
    empno numeric(4) not null,
    ename varchar(10),
    job varchar(9),
    mgr numeric(4),
    hiredate datetime,
    sal numeric(7, 2),
    comm numeric(7, 2),
    deptno numeric(2)
);
insert into emp values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
mysql> -- 1. 查询出部门编号为30的所有员工的编号和姓名
mysql> select deptno,empno,ename 
    -> from emp 
    -> where deptno=30;
+--------+-------+--------+
| deptno | empno | ename |
+--------+-------+--------+
| 30 | 7499 | ALLEN |
| 30 | 7521 | WARD |
| 30 | 7654 | MARTIN |
| 30 | 7698 | BLAKE |
| 30 | 7844 | TURNER |
| 30 | 7900 | JAMES |
+--------+-------+--------+
6 rows in set (0.00 sec)
-- 2.找出部门编号为10中所有经理，和部门编号为20中所有销售员的详细资料。
mysql> select *
    -> from emp e
    -> where (e.deptno=10 and e.job='MANAGER') or (e.deptno=20 and e.job='SALESMAN');
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL |     10 |
+-------+-------+---------+------+---------------------+---------+------+--------+
1 row in set (0.00 sec)
3.查询所有员工详细信息，用工资降序排序，如果工资相同使用入职日期升序排序
mysql> select * from emp e order by sal desc ,hiredate asc;
+-------+--------+-----------+------+---------------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate            | sal     | comm    | deptno |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 |    NULL |     10 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |    NULL |     20 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 00:00:00 | 3000.00 |    NULL |     20 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |    NULL |     20 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |    NULL |     10 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |    NULL |     10 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 00:00:00 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |    NULL |     30 |
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |    NULL |     20 |
+-------+--------+-----------+------+---------------------+---------+---------+--------+
14 rows in set (0.00 sec)
--4.列出薪金大于1500的各种工作及从事此工作的员工人数。
mysql> select job ,count(1) 
    -> from emp 
    -> where sal+ifnull(comm,0)>1500
    -> group by job;
+-----------+----------+
| job       | count(1) |
+-----------+----------+
| ANALYST   |        2 |
| MANAGER   |        3 |
| PRESIDENT |        1 |
| SALESMAN  |        3 |
+-----------+----------+
4 rows in set (0.00 sec)
5.列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
mysql> select ename
    -> from emp
    -> where deptno in (select deptno from dept  d where d.dname='SALES' );
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.00 sec)
6.查询姓名以S开头的\以S结尾\包含S字符\第二个字母为L  __
mysql> select *
    -> from emp
    -> where ename like 'S%' or ename like '%S' or ename like '%S%' or ename like '__L';
+-------+-------+---------+------+---------------------+---------+------+--------+
| empno | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+-------+-------+---------+------+---------------------+---------+------+--------+
|  7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
|  7788 | SCOTT | ANALYST | 7566 | 1982-12-09 00:00:00 | 3000.00 | NULL |     20 |
|  7876 | ADAMS | CLERK   | 7788 | 1983-01-12 00:00:00 | 1100.00 | NULL |     20 |
|  7900 | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
|  7369 | SMITH | CLERK   | 7902 | 1980-12-17 00:00:00 |  800.00 | NULL |     20 |
+-------+-------+---------+------+---------------------+---------+------+--------+
7.查询每种工作的最高工资、最低工资、人数
mysql> select 
    -> max(sal+ifnull(comm,0)) as maxsal,
    -> min(sal+ifnull(comm,0)) as minsal,
    -> count(empno) cno 
    -> from emp
    -> group by job;
+---------+---------+-----+
| maxsal  | minsal  | cno |
+---------+---------+-----+
| 3000.00 | 3000.00 |   2 |
| 1300.00 |  800.00 |   4 |
| 2975.00 | 2450.00 |   3 |
| 5000.00 | 5000.00 |   1 |
| 2650.00 | 1500.00 |   4 |
+---------+---------+-----+
5 rows in set (0.01 sec)
8.列出薪金 高于 公司平均薪金的所有员工号，员工姓名，所在部门名称，上级领导，工资，工资等级
mysql> select
    -> e.ename,
    -> e.deptno,d.dname,
    -> e.mgr,
    -> e.sal+ifnull(e.comm,0) as sal
    -> 
    -> from emp e
    -> left join dept d on e.deptno=d.deptno
    -> where (sal+ifnull(comm,0)) > (select avg(sal+ifnull(comm,0)) from emp);
+--------+--------+------------+------+---------+
| ename  | deptno | dname      | mgr  | sal     |
+--------+--------+------------+------+---------+
| CLARK  |     10 | ACCOUNTING | 7839 | 2450.00 |
| KING   |     10 | ACCOUNTING | NULL | 5000.00 |
| JONES  |     20 | RESEARCH   | 7839 | 2975.00 |
| SCOTT  |     20 | RESEARCH   | 7566 | 3000.00 |
| FORD   |     20 | RESEARCH   | 7566 | 3000.00 |
| MARTIN |     30 | SALES      | 7698 | 2650.00 |
| BLAKE  |     30 | SALES      | 7839 | 2850.00 |
+--------+--------+------------+------+---------+
7 rows in set (0.00 sec)
9.列出薪金  高于  在部门30工作的  所有/任何一个员工的薪金的员工姓名和薪金、部门名称。
mysql> select 
    -> e.ename,
    -> e.sal+ifnull(e.comm,0),
    -> e.deptno,
    -> d.dname 
    -> from emp e, dept  d
    -> where e.deptno =d.deptno 
    -> and sal+ifnull(comm,0) > any(select sal+ifnull(comm,0) from emp where deptno=30);
+--------+------------------------+--------+------------+
| ename  | e.sal+ifnull(e.comm,0) | deptno | dname      |
+--------+------------------------+--------+------------+
| ALLEN  |                1900.00 |     30 | SALES      |
| WARD   |                1750.00 |     30 | SALES      |
| JONES  |                2975.00 |     20 | RESEARCH   |
| MARTIN |                2650.00 |     30 | SALES      |
| BLAKE  |                2850.00 |     30 | SALES      |
| CLARK  |                2450.00 |     10 | ACCOUNTING |
| SCOTT  |                3000.00 |     20 | RESEARCH   |
| KING   |                5000.00 |     10 | ACCOUNTING |
| TURNER |                1500.00 |     30 | SALES      |
| ADAMS  |                1100.00 |     20 | RESEARCH   |
| FORD   |                3000.00 |     20 | RESEARCH   |
| MILLER |                1300.00 |     10 | ACCOUNTING |
+--------+------------------------+--------+------------+
12 rows in set (0.00 sec)
总结：
1.null字段 计算 比如相加  null+任何数值=null，所以相加的时候注意使用ifnull
2. max min 
select xxx,yyyy,count(job) from emp
group by xxx,yyyy;
#注意点
a.count(*)少用  count(id) count(0)
b.算age数据多少  sum(age)
c.count(distinct id)  去重

空值造成的数据倾斜案例

假如
A表有1000W数据，其中id(800W 有值，剩余的是null
B表有10W的数据，其中id(5W有数据，5W为null)
当A与B通过id关联时会出现 200W*5W的null数据，有可能存在数据倾斜
eg:
select * from 
(select * from A where A.id is not null)AA
left join (
select * from B where B.id is not null)BB
)
on BB.id=AA.id
mysql实现topN的排序
选出每个部门工资最高的前1名
create view e
as (
select deptno,job,
sum(sal+ifnull(comm,0)) as sal
from emp e
group by job,deptno
)
mysql> SELECT * from e
    -> where (
    -> SELECT COUNT(sal) from e g
    -> where e.deptno=g.deptno
    -> and e.sal<g.sal
    -> )=0;
+--------+-----------+---------+
| deptno | job       | sal     |
+--------+-----------+---------+
|     20 | ANALYST   | 6000.00 |
|     10 | PRESIDENT | 5000.00 |
|     30 | SALESMAN  | 7800.00 |
+--------+-----------+---------+
3 rows in set (0.00 sec)

mysql实现topN的排序

选出每个部门工资最高的前1名
create view e
as (

select deptno,job,
sum(sal+ifnull(comm,0)) as sal
from emp e
group by job,deptno
)
mysql> SELECT * from e
    -> where (
    -> SELECT COUNT(sal) from e g
    -> where e.deptno=g.deptno
    -> and e.sal<g.sal
    -> )=0;
+--------+-----------+---------+
| deptno | job       | sal     |
+--------+-----------+---------+
|     20 | ANALYST   | 6000.00 |
|     10 | PRESIDENT | 5000.00 |
|     30 | SALESMAN  | 7800.00 |
+--------+-----------+---------+
3 rows in set (0.00 sec)