正在玩命加载中 . . .

MySQL(三)

数据库 MySQL
数据库
发布日期:   2020-10-18
更新日期:   2021-02-21
文章字数:   5.8k
阅读时长:   26 分
阅读次数:  

1.连接查询

  • 在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。
  • 在实际开发中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。
stuno stuname classno classname
1 zs 1 第二中学高三1班
2 ls 1 第二中学高三1班

因此，学生和班级信息存储到一张表中，结果就像上面一样，数据会存在大量的重复，导致数据的冗余。

2.连接查询分类

  • 根据语法出现的年代
    • SQL92（一些老的DBA可能还在使用这种语法。DBA：DataBase Administrator，数据库管理员）
    • SQL99（比较新的语法）
  • 根据表的连接方式
    • 内连接(inner)
      • 等值连接
      • 非等值连接
      • 自连接
    • 外连接(outter)
      • 左外连接（左连接）
      • 右外连接（右连接）
    • 全连接（这个不讲，很少用）

3.笛卡尔积现象

在表的连接查询方面有一种现象被称为：笛卡尔积现象（笛卡尔乘积现象）

案例：找出每一个员工的部门名称，要求显示员工名和部门名。

EMP表：

ename deptno
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10

DEPT表：

DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON 
select e.ename,d.dname from emp e,dept d;

此处，我们为EMP表起了别名e，DEPT表别名d，这样的好处：

  • 执行效率高
  • 可读性好

显示结果：

+——–+————+
| ename | dname |
+——–+————+
| SMITH | ACCOUNTING |
| SMITH | RESEARCH |
| SMITH | SALES |
| SMITH | OPERATIONS |
| ALLEN | ACCOUNTING |
| ALLEN | RESEARCH |
| ALLEN | SALES |
| ALLEN | OPERATIONS |
…………

最后一行显示：

56 rows in set (0.00 sec) //总共56次，显示56行

这就是笛卡尔积现象：

当两张表进行连接查询的时候，没有任何条件进行限制，最终的查询结果条数是两张表记录条数的乘积。

如何避免该现象

想要避免，很显然，就是加入过滤条件。

思考：当我们加入条件进行过滤后，避免了笛卡尔积现象，会减少记录的匹配次数吗？

答：不会，次数还是56次，只是显示的是有效记录。

案例：找出每一个员工的部门名称，要求显示员工名和部门名。

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno

这个语句可以帮我们避免笛卡尔积现象，但是，这是SQL92的写法，现在不用了。（表格连接条件与之前的条件查询都放在了where里面，不好）

4.内连接(inner)：等值连接

特点：条件是等量关系

还是上面的案例：找出每一个员工的部门名称，要求显示员工名和部门名。

（SQL92的写法就在上面，就不再写了，而且现在也不用了）下面是SQL99的写法，被普遍采用。

select
    e.ename,d.dname
from
    emp e
/*inner*/join
    dept.d
on
    e.deptno = d.deptno;
-- where
-- ...

因为此处是等值连接，正如小标题所述一样，等值连接属于内连接（inner)，所以inner被省略了，但是，带着inner目的是可读性更好一些。

SQL99语法结构更清晰一些：表的连接条件和后来的where条件分离了。同样达到避免笛卡尔积现象的目的。

5.内连接(inner)：非等值查询

特点：连接条件中的关系是非等量关系。

案例：找出每个员工的工资等级，要求显示员工名、工资、工资等级。

select ename,sal from emp;//将此表取别名：e

+——–+———+
| ename | sal |
+——–+———+
| SMITH | 800.00 |
| ALLEN | 1600.00 |
| WARD | 1250.00 |
| JONES | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING | 5000.00 |
| TURNER | 1500.00 |
| ADAMS | 1100.00 |
| JAMES | 950.00 |
| FORD | 3000.00 |
| MILLER | 1300.00 |
+——–+———+

select * from salgrade;//将此表区别名：s

+——-+——-+——-+
| GRADE | LOSAL | HISAL |
+——-+——-+——-+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+——-+——-+——-+

工资等级是按照工资在LOSAL和HISAL里哪个区间进行等级评判的，这就是连接条件。

select
    e.ename,e.sal,s.grade
from 
    emp e
/*inner*/join
    salgrade s
on 
    e.sal between s.losal and s.hisal;

显示结果：

+——–+———+——-+
| ename | sal | grade |
+——–+———+——-+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+——–+———+——-+

6.自连接

特点：一张表看做两张表。自己连接自己。

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。

select empno,ename,mgr from emp;

EMP a 员工表

+——-+——–+——+
| empno | ename | mgr |
+——-+——–+——+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+——-+——–+——+

EMP b 领导表

+——-+——–+
| empno | ename |
+——-+——–+
| 7566 | JONES |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7902 | FORD |
+——-+——–+

select
    a.ename as '员工',b.ename as '领导'
from 
    emp a
/*inner*/join
    emp b
on
    a.mgr = b.empno;-- 员工的领导编号 = 领导的员工编号

7.外连接

7-1.与内连接区别

  • 内连接
    • 假设A和B表使用内(inner)连接的话，凡是A表和B表能够匹配上的记录查询出来，这就是内连接。AB两张表没有主副之分，两张表是平等的。
  • 外连接
    • 假设A和B表使用外(outer)连接的话，AB两张表中有一张表是主表，一张表是副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配。

7-2.外连接分类

  • 左外连接（左连接）：表示左边的这张表是主表。
  • 右外连接（右连接）：表示右边的这张表是主表。

左连接有右连接的写法，右连接也会有对应的左连接的写法。

案例：找出每个员工的上级领导？（所有员工必须全部查询出来。）

EMP a 员工表

+——-+——–+——+
| empno | ename | mgr |
+——-+——–+——+
| 7369 | SMITH | 7902 |
| 7499 | ALLEN | 7698 |
| 7521 | WARD | 7698 |
| 7566 | JONES | 7839 |
| 7654 | MARTIN | 7698 |
| 7698 | BLAKE | 7839 |
| 7782 | CLARK | 7839 |
| 7788 | SCOTT | 7566 |
| 7839 | KING | NULL |
| 7844 | TURNER | 7698 |
| 7876 | ADAMS | 7788 |
| 7900 | JAMES | 7698 |
| 7902 | FORD | 7566 |
| 7934 | MILLER | 7782 |
+——-+——–+——+

EMP b 领导表

+——-+——–+
| empno | ename |
+——-+——–+
| 7566 | JONES |
| 7698 | BLAKE |
| 7782 | CLARK |
| 7788 | SCOTT |
| 7839 | KING |
| 7902 | FORD |
+——-+——–+

内连接：

select
    a.ename as '员工',b.ename as '领导'
from 
    emp a
/*inner*/join
    emp b
on
    a.mgr = b.empno;

左外连接：

select 
    a.ename '员工', b.ename '领导'
from
    emp a
left /*outer*/ join
    emp b
on
    a.mgr = b.empno;

右外连接：

select 
    a.ename '员工', b.ename '领导'
from
    emp b
right /*outer*/ join
    emp a
on
    a.mgr = b.empno;

外连接最重要的特点：主表的数据无条件的全部查询出来。

案例：找出哪个部门没有员工？

EMP表：

+——-+——–+———–+——+————+———+———+——–+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+——-+——–+———–+——+————+———+———+——–+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+——-+——–+———–+——+————+———+———+——–+

DEPT表：

+——–+————+———-+
| DEPTNO | DNAME | LOC |
+——–+————+———-+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+——–+————+———-+

select
    d.* 
from 
    emp e 
right join 
    dept d 
on 
    e.deptno = d.deptno 
where 
    e.empno is null;

8.三张表的连接查询

EMP e：

+——-+——–+———+——–+
| empno | ename | sal | deptno |
+——-+——–+———+——–+
| 7369 | SMITH | 800.00 | 20 |
| 7499 | ALLEN | 1600.00 | 30 |
| 7521 | WARD | 1250.00 | 30 |
| 7566 | JONES | 2975.00 | 20 |
| 7654 | MARTIN | 1250.00 | 30 |
| 7698 | BLAKE | 2850.00 | 30 |
| 7782 | CLARK | 2450.00 | 10 |
| 7788 | SCOTT | 3000.00 | 20 |
| 7839 | KING | 5000.00 | 10 |
| 7844 | TURNER | 1500.00 | 30 |
| 7876 | ADAMS | 1100.00 | 20 |
| 7900 | JAMES | 950.00 | 30 |
| 7902 | FORD | 3000.00 | 20 |
| 7934 | MILLER | 1300.00 | 10 |
+——-+——–+———+——–+

DEPT d：

+——–+————+———-+
| DEPTNO | DNAME | LOC |
+——–+————+———-+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+——–+————+———-+

SALGRADE s：

+——-+——-+——-+
| GRADE | LOSAL | HISAL |
+——-+——-+——-+
| 1 | 700 | 1200 |
| 2 | 1201 | 1400 |
| 3 | 1401 | 2000 |
| 4 | 2001 | 3000 |
| 5 | 3001 | 9999 |
+——-+——-+——-+

案例：找出每一个员工的部门名称以及工资等级。

select 
    e.ename,d.dname,s.grade
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
join
    salgrade s
on
    e.sal between s.losal and s.hisal;

表示：emp表和dept表先进行表连接，连接之后emp表继续和salgrade表进行连接。

案例：找出每一个员工的部门名称，工资等级，以及上级领导。

PS：这个时候必须使用外连接，KING是员工，有部门名称，工资等级，但是KING没有上司，所以KING 必须查出，若是使用内连接，则会导致KING丢失。

select
    e.ename '员工',d.dname,s.grade,e1.ename '领导'
from
    emp e
join
    dept d
on
    e.deptno = d.deptno
join
    salgrade s
on
    e.sal between s.losal and s.hisal
left join
    emp e1
on
    e.mgr = e1.empno;

结果：

+——–+————+——-+——-+
| 员工 | dname | grade | 领导 |
+——–+————+——-+——-+
| SMITH | RESEARCH | 1 | FORD |
| ALLEN | SALES | 3 | BLAKE |
| WARD | SALES | 2 | BLAKE |
| JONES | RESEARCH | 4 | KING |
| MARTIN | SALES | 2 | BLAKE |
| BLAKE | SALES | 4 | KING |
| CLARK | ACCOUNTING | 4 | KING |
| SCOTT | RESEARCH | 4 | JONES |
| KING | ACCOUNTING | 5 | NULL |
| TURNER | SALES | 3 | BLAKE |
| ADAMS | RESEARCH | 1 | SCOTT |
| JAMES | SALES | 1 | BLAKE |
| FORD | RESEARCH | 4 | JONES |
| MILLER | ACCOUNTING | 2 | CLARK |
+——–+————+——-+——-+

9.子查询

9-1.什么是子查询

出现在其他语句当中的select语句，成为子查询或内查询。外部的查询语句，成为主查询或外查询。

select语句当中嵌套select语句，被嵌套的select语句是子查询。

子查询可以出现在以下位置：

select
     ..(select).
from
     ..(select).
where
     ..(select).

子查询按结果集的行列数的不同：

  • 标量子查询（单行子查询）：结果集只有一行一列
  • 列子查询（多行子查询）：结果集只有一列多行
  • 行子查询：结果集有一行多列
  • 表子查询：结果集一般多行多列

按子查询出现的位置：

  • select后面

    仅仅支持标量子查询

  • from后面

    支持表子查询

  • where或having后面

    标量子查询（单行子查询）

    列子查询（多行子查询）

    行子查询（较少）

  • exists后面（相关子查询）

    表子查询

特点：

  1. 子查询放在小括号里
  2. 子查询一般放在条件的右侧
  3. 标量子查询，一般搭配着单行操作符使用，“>，<，>=，<=，=，<>”
  4. 列子查询一般搭配着多行操作符使用，“in，any / some，all”

9-2.where子句中使用子查询

案例：找出高于平均薪资的员工信息。

select * from emp where sal > avg(sal);

注意：错误的写法，where后面不能直接使用分组函数。

分为两步：

  1. 找出平均薪资

    select avg(sal) from emp;

  2. where过滤

    select * from emp where sal > 2073;

将以上两步合为一步：

select * from emp where sal > (select avg(sal) from emp);

标量子查询 / 单行子查询（尚硅谷）

案例1：谁的工资比 Abel 高?

-- ①查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';
-- ②查询员工的信息，满足 salary>①结果
SELECT *
FROM employees
WHERE salary>(
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
);

案例2：返回job_id与141号员工相同，salary比143号员工多的员工 姓名，job_id 和工资？

-- ①查询141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
-- ②查询143号员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
-- ③查询员工的姓名，job_id 和工资，要求job_id=①并且salary>②
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
);

案例3：返回公司工资最少的员工的last_name,job_id和salary？

-- ①查询公司的 最低工资
SELECT MIN(salary)
FROM employees;
-- ②查询last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
    SELECT MIN(salary)
    FROM employees
);

案例4：查询最低工资大于50号部门最低工资的部门id和其最低工资？

-- ①查询50号部门的最低工资
SELECT  MIN(salary)
FROM employees
WHERE department_id = 50;
-- ②查询每个部门的最低工资
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id;
-- 在②基础上筛选，满足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
);

列子查询 / 多行子查询（尚硅谷）

操作符 含义
in，not in 等于列表中的任意值
any，some 和子查询返回的某一个值比较
all 和子查询返回的所有值比较

案例1：返回location_id是1400或1700的部门中的所有员工姓名？

-- ①查询location_id是1400或1700的部门编号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700);
-- ②查询员工姓名，要求部门号是①列表中的某一个
SELECT last_name
FROM employees
WHERE department_id  <>ALL(
    SELECT DISTINCT department_id
    FROM departments
    WHERE location_id IN(1400,1700)
);

案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary？

-- ①查询job_id为‘IT_PROG’部门任一工资
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG';
-- ②查询员工号、姓名、job_id 以及salary，salary<(①)的任意一个
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary < ANY(
    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 MAX(salary)
    FROM employees
    WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

行子查询 （结果集一行多列或多行多列）

案例：查询员工编号最小并且工资最高的员工信息？

-- ①查询最小的员工编号
SELECT MIN(employee_id)
FROM employees;
-- ②查询最高工资
SELECT MAX(salary)
FROM employees;
-- ③查询员工信息
SELECT *
FROM employees
WHERE employee_id=(
    SELECT MIN(employee_id)
    FROM employees
)AND salary=(
    SELECT MAX(salary)
    FROM employees
);

9-3.from后面嵌套子查询

案例：找出每个部门平均薪水的等级。

第一步：找出每个部门平均薪水（按照部门编号分组，求sal的平均值）

select deptno,avg(sal) from emp group by deptno;

结果：

+——–+————-+
| deptno | avgsal |
+——–+————-+
| 10 | 2916.666667 |
| 20 | 2175.000000 |
| 30 | 1566.666667 |
+——–+————-+

第二步：将以上的查询结果当做临时表t，让t表和salgrade s表连接，条件是：t.avgsal between s.losal and s.hisal

select
    t.*,s.grade
from
    (select deptno,avg(sal) from emp group by deptno) t
join
    salgrade s
on
    t.avgsal between s.losal and s.hisal

结果：

+——–+————-+——-+
| deptno | avgsal | grade |
+——–+————-+——-+
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
+——–+————-+——-+

案例：找出每个部门平均的薪水等级。

第一步：找出每个员工的薪水等级。

select
    e.ename,e.sal,e.deptno,s.grade
from
    emp e
join 
    salgrade s
on
    e.sal between s.losal and hisal;

结果：

+——–+———+——–+——-+
| ename | sal | deptno | grade |
+——–+———+——–+——-+
| SMITH | 800.00 | 20 | 1 |
| ALLEN | 1600.00 | 30 | 3 |
| WARD | 1250.00 | 30 | 2 |
| JONES | 2975.00 | 20 | 4 |
| MARTIN | 1250.00 | 30 | 2 |
| BLAKE | 2850.00 | 30 | 4 |
| CLARK | 2450.00 | 10 | 4 |
| SCOTT | 3000.00 | 20 | 4 |
| KING | 5000.00 | 10 | 5 |
| TURNER | 1500.00 | 30 | 3 |
| ADAMS | 1100.00 | 20 | 1 |
| JAMES | 950.00 | 30 | 1 |
| FORD | 3000.00 | 20 | 4 |
| MILLER | 1300.00 | 10 | 2 |
+——–+———+——–+——-+

第二步：基于以上结果，继续按照deptno分组，求grade平均值。

select 
    e.deptno,avg(s.grade)
from 
    emp e 
join 
    salgrade s 
on 
    e.sal between s.losal and s.hisal
group by
    e.deptno;

结果：

+——–+————–+
| deptno | avg(s.grade) |
+——–+————–+
| 10 | 3.6667 |
| 20 | 2.8000 |
| 30 | 2.5000 |
+——–+————–+

9-4.select后面嵌套子查询

案例：找出每个员工所在的部门名称，要求显示员工名和部门名。

原始版本：

select
    e.ename,d.dname
from
    emp e
join 
    dept d
on 
    e.deptno = d.deptno;

select嵌套子查询版本：

select
    e.ename
    (select d.dname from dept where e.deptno = d.deptno) as dname
from
    emp e;

结果：

+——–+————+
| ename | dname |
+——–+————+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+——–+————+

仅仅支持标量子查询（尚硅谷）

案例1：查询每个部门的员工个数？

SELECT d.*,(
    SELECT COUNT(*)
    FROM employees e
    WHERE e.department_id = d.`department_id`
 ) 个数
 FROM departments d;

案例2：查询员工号=102的部门名？

SELECT (
    SELECT department_name,e.department_id
    FROM departments d
    INNER JOIN employees e
    ON d.department_id=e.department_id
    WHERE e.employee_id=102
) 部门名;

10.union的使用

union可以将查询结果集相加

案例：找出工作岗位是SALESMAN和MANAGER的员工？

第一种(or)：

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

第二种(in)：

select ename,job from emp where job in('MANAGER','SALESMAN');

第三种(union)：

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

union 可以用于两张不相干的表中的数据拼接在一起显示：

select ename from emp
union
select dname from dept;

结果：

+————+
| ename |
+————+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
| ACCOUNTING |
| RESEARCH |
| SALES |
| OPERATIONS |
+————+

注意：使用union时，前后查询的字段的数量必须一样。

11.limit的使用(重中之重)

limit用于以后的分页查询。

limit是mysql特有的，其他数据库中没有，不通用。（Oracle中有一个相同的机制，叫做rownum）

作用：取结果集中的部分数据

语法：

limit startindex,length

startIndex表示起始位置，从0开始，0表示第一条数据.

length表示取几个

案例：取出工资前5名的员工（思路：降序取前5个）

select ename,sal from emp order by sal desc limit 0, 5;
select ename,sal from emp order by sal desc limit 5;

执行顺序：

select            5
        ...
from            1
        ...        
where            2
        ...    
group by        3
        ...
having            4
        ...
order by        6
        ...
limit            7
        ...;

通用的标准分页sql

比如：

每页显示3条记录：
第1页：0, 3
第2页：3, 3
第3页：6, 3
第4页：9, 3
第5页：12, 3

每页显示pageSize条记录：
第pageNo页：(pageNo - 1) * pageSize, pageSize

java代码：

int pageNo = 2; // 页码是2
int pageSize = 10; // 每页显示10条

limit (pageNo - 1) * pageSize, pageSize

12.表的创建

12-1.语法格式

create table 表名(
    字段名1 数据类型,
    字段名2 数据类型,
    字段名3 数据类型,
    ....
);

12-2.MySQL中字段的数据类型(常见的)

  • int——整数型(java中的int)
  • bigint——长整型(java中的long)
  • float——浮点型(java中的float double)
  • char——定长字符串(String)
  • varchar——可变长字符串(StringBuffer/StringBuilder)
  • date——日期类型 （对应Java中的java.sql.Date类型）
  • BLOB——二进制大对象（存储图片、视频等流媒体信息），Binary Large OBject ，（对应java中的Object）
  • CLOB——字符大对象（存储较大文本，比如，可以存储4G的字符），Character Large OBject，（对应java中的Object）

注意：

  1. char和varchar怎么选择？

    答：在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char；当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

  2. BLOB和CLOB类型的使用？

    假设有一个电影表: t_movie

    id(int) name(varchar) playtime(date/char) haibao(BLOB) history(CLOB)
    1 蜘蛛侠 2020-10-10 图片型海报 电影简介
    2

    海报用insert语句插不进去，必须使用java中的IO流，才能将图片放到表当中。但是，用的不多，数据库太珍贵了，一般将图片，声音放到硬盘上，再将硬盘上的地址存入数据库中。但是，图片只有1k，或更小，比如qq头像。

  3. 表名在数据库当中一般建议以：t_或者tbl_开始。

  4. char类型的效率高于varchar，char类型不需要做运算，不需要判断传过来多少长度的字符串。底层不会做if语句判断，而varchar这种智能型字符串在底层会做if语句判断。

12-3.创建一个学生表

学生信息包括：
学号、姓名、性别、班级编号、生日
学号：bigint
姓名：varchar
性别：char
班级编号：int
生日：char

create table t_student(
    no bigint,
    name varchar(255),
    sex char(1),
    classno varchar(255),
    birth char(10)
);

13.insert语句插入数据

13-1.语法格式

insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)

要求：字段的数量和值的数量相同，并且数据类型要对应相同。

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban','1950-10-12')

mysql> select * from t_student;
+——+———-+——+————+————+
| no | name | sex | classno | birth |
+——+———-+——+————+————+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
+——+———-+——+————+————+

insert into t_student(name) values('wangwu'); /*除name字段之外，剩下的所有字段自动插入NULL。*/

mysql> select * from t_student;
+——+———-+——+————+————+
| no | name | sex | classno | birth |
+——+———-+——+————+————+
| 1 | zhangsan | 1 | gaosan1ban | 1950-10-12 |
| 2 | lisi | 1 | gaosan1ban | 1950-10-12 |
| NULL | wangwu | NULL | NULL | NULL |
+——+———-+——+————+————+

注意：

当一条insert语句执行成功之后，表格当中必然会多一行记录。即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行insert语句插入数据了，只能使用update进行更新。

13-2.删表

drop table if exists t_student; /当这个表存在的话删除/

13-3.默认值default

create table t_student(
    no bigint,
    name varchar(255),
    sex char(1) default 1,/*给性别默认值'1'*/
    classno varchar(255),
    birth char(10)
);
insert into t_student(name) values('zhangsan');

mysql> select * from t_student;
+——+———-+——+———+——-+
| no | name | sex | classno | birth |
+——+———-+——+———+——-+
| NULL | zhangsan | 1 | NULL | NULL |
+——+———-+——+———+——-+

13-4.省略

insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23');/*字段可以省略不写，但是后面的value对数量和顺序都有要求*/

13-5.一次插入多行数据

insert into 
    t_student(no,name,sex,classno,birth) 
values
    (3,'rose','1','gaosi2ban','1952-12-14'),
    (4,'laotie','1','gaosi2ban','1955-12-14');

mysql> select * from t_student;
+——+———-+——+————+————+
| no | name | sex | classno | birth |
+——+———-+——+————+————+
| NULL | zhangsan | 1 | NULL | NULL |
| 1 | jack | 0 | gaosan2ban | 1986-10-23 |
| 3 | rose | 1 | gaosi2ban | 1952-12-14 |
| 4 | laotie | 1 | gaosi2ban | 1955-12-14 |
+——+———-+——+————+————+

14.表的复制

语法格式：

create table 表名 as select语句;
将查询结果当做表创建出来。
比如：
create table emp1 as select * from emp;
将后面的查询结果当做一张新表emp1，创建出来。

15.将查询结果插入表中

mysql> insert into dept1 select * from dept;
mysql> select * from dept1;
+——–+————+———-+
| DEPTNO | DNAME | LOC |
+——–+————+———-+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+——–+————+———-+

16.修改数据update

16-1.语法格式：

update 表名 set 字段名1=值1,字段名2=值2... where 条件;

注意：没有where条件，整张表数据全部更新；字段与字段之间用 ‘ , ‘ 连接，不用 ‘ and ‘ 。

案例：将部门10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU

update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;

mysql> select * from dept1;
+——–+————+———-+
| DEPTNO | DNAME | LOC |
+——–+————+———-+
| 10 | RENSHIBU | SHANGHAI |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 10 | RENSHIBU | SHANGHAI |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+——–+————+———-+

16-2.更新所有记录

update dept1 set loc = 'x', dname = 'y';

mysql> select * from dept1;
+——–+——-+——+
| DEPTNO | DNAME | LOC |
+——–+——-+——+
| 10 | y | x |
| 20 | y | x |
| 30 | y | x |
| 40 | y | x |
| 10 | y | x |
| 20 | y | x |
| 30 | y | x |
| 40 | y | x |
+——–+——-+——+

17.删除数据

语法格式：

delete from 表名 where 条件;

注意：没有where条件，则全部删除

删除10部门数据？

delete from dept1 where deptno = 10;

删除大表中的数据（重点）

truncate table 表名; /*表被截断，不可回滚。永久丢失。*/

删除表

drop table 表名; /*这个通用。*/ 
drop table if exists 表名; /*oracle不支持这种写法。*/

18.表的修改

18-1.修改列名

-- column可省略
alter table [表名] change column [旧列名] [新列名]  数据类型;

18-2.修改列的类型或约束

-- column可省略
alter table [表名] modify column [列名] [新数据类型];

18-3.添加新列

alter table [表名] add [column] [列名] 数据类型;

18-4.删除列

alter table [表名] drop [column] [列名];

18-5.修改表名

alter table [旧表名] rename to [新表名];

19.表的删除

drop table if exists [表名];

20.总结

对于表结构的修改，这里不讲了，大家使用工具完成即可，因为在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，即使需要修改表结构，我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。出现在java代码当中的sql包括：insert delete update select（这些都是表中的数据操作。）

增删改查有一个术语CRUD操作：Create(增)，Retrieve(检索)，Update(修改)，Delete(删除)

文章作者: LogicVan
文章链接: http://LogicVan.github.io/2020/10/18/mysql-san/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 LogicVan !
数据库 MySQL
赏
评论
  目录