1. Johngo学长首页
  2. 技术点详细复盘专题
  3. 数据库

MySQL学习笔记-day02

• 数据库 • 阅读 85 
select distinct job from emp;# distinct关键字去除重复记录。

结果：

+-----------+
| job       |
+-----------+
| CLERK     |
| SALESMAN  |
| MANAGER   |
| ANALYST   |
| PRESIDENT |
+-----------+

select ename,distinct job from emp;# 错误

记住： distinct只能出现在所有字段的最前面。

distinct name1，name2 # 联合去重

select distinct deptno,job from emp;

结果：

+--------+-----------+
| deptno | job       |
+--------+-----------+
|     20 | CLERK     |
|     30 | SALESMAN  |
|     20 | MANAGER   |
|     30 | MANAGER   |
|     10 | MANAGER   |
|     20 | ANALYST   |
|     10 | PRESIDENT |
|     30 | CLERK     |
|     10 | CLERK     |
+--------+-----------+

案例：统计岗位的数量？

+---------------------+
| count(distinct job) |
+---------------------+
|                   5 |
+---------------------+

在实际开发中，大部分的情况下都不是从单表中查询数据，一般都是多张表联合查询取出最终的结果。

在实际业务中，一般一个业务都会对应多张表，比如：学生和班级，起码两张表。

显示每个员工信息，并显示所属的部门名称。

select ename,dname from emp,dept;

根据语法出现的年代来划分的话，包括：

SQL92(一些老的 DBA可能还在使用这种语法。 DBA:DataBase Adminstrator。数据库管理员)

SQL99(比较新的语法)

根据表的连接方式来划分，包括：

  • 等值连接

  • 非等值连接

  • 左外连接(左连接)

  • 右外连接(右连接)
  • 全连接(这个很少用)

笛卡尔积现象：当两张表进行连接查询的时候，如果没有任何条件限制，查询结果是两张表中的记录条数的乘积。
案例：找出每一个员工的部门信息，要求显示员工名和部门名。

select ename,dname from emp,dept order by ename;# 发生笛卡尔积现象

+--------+-------------+
| ename  | dname       |
+--------+-------------+
| ADAMS  | SALES       |
| ADAMS  | OPERATIONS  |
| ADAMS  | ACCOUNTING  |
| ADAMS  | RESEARCHING |
| ALLEN  | OPERATIONS  |
| ALLEN  | ACCOUNTING  |
| ALLEN  | RESEARCHING |
| ALLEN  | SALES       |
| BLAKE  | OPERATIONS  |
| BLAKE  | ACCOUNTING  |
| BLAKE  | RESEARCHING |
| BLAKE  | SALES       |
| CLARK  | SALES       |
| CLARK  | OPERATIONS  |
| CLARK  | ACCOUNTING  |
| CLARK  | RESEARCHING |
| FORD   | ACCOUNTING  |
| FORD   | RESEARCHING |
| FORD   | SALES       |
| FORD   | OPERATIONS  |
| JAMES  | RESEARCHING |
| JAMES  | SALES       |
| JAMES  | OPERATIONS  |
| JAMES  | ACCOUNTING  |
| JONES  | RESEARCHING |
| JONES  | SALES       |
| JONES  | OPERATIONS  |
| JONES  | ACCOUNTING  |
| KING   | ACCOUNTING  |
| KING   | RESEARCHING |
| KING   | SALES       |
| KING   | OPERATIONS  |
| MARTIN | ACCOUNTING  |
| MARTIN | RESEARCHING |
| MARTIN | SALES       |
| MARTIN | OPERATIONS  |
| MILLER | OPERATIONS  |
| MILLER | ACCOUNTING  |
| MILLER | RESEARCHING |
| MILLER | SALES       |
| SCOTT  | RESEARCHING |
| SCOTT  | SALES       |
| SCOTT  | OPERATIONS  |
| SCOTT  | ACCOUNTING  |
| SIMITH | ACCOUNTING  |
| SIMITH | RESEARCHING |
| SIMITH | SALES       |
| SIMITH | OPERATIONS  |
| TURNER | OPERATIONS  |
| TURNER | ACCOUNTING  |
| TURNER | RESEARCHING |
| TURNER | SALES       |
| WARD   | SALES       |
| WARD   | OPERATIONS  |
| WARD   | ACCOUNTING  |
| WARD   | RESEARCHING |
+--------+-------------+
56 rows in set (0.00 sec)

关于表的别名：

select  e.ename,d.dname from emp as e,dept as d;# 可省略as
select  e.ename,d.dname from emp e,dept d;

表的别名有什么好处？

  • 第一：执行效率高。
  • 第二：可读性好。

找出每一个员工的部门信息，要求显示员工名和部门名。

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;# SQL92，以后不用。

避免笛卡尔积现象，匹配次数不会减少，只不过显示的是有效记录！

案例：查询每个员工的部门名称，要求显示员工名和部门名。

  • SQL92:(太老了，不用了)
select
    e.ename,d.dname
from
    emp e,dept d
where
    e.deptno = d.deptno;
  • SQL99:(常用的)
select
    e.ename,d.dname
from
    emp e
inner join  # inner可以省略，带着inner目的是可读性好一些。
    dept d
on
    e.deptno = d.deptno;

on后面跟表连接的条件。

结果：

+--------+-------------+
| ename  | dname       |
+--------+-------------+
| CLARK  | ACCOUNTING  |
| KING   | ACCOUNTING  |
| MILLER | ACCOUNTING  |
| SIMITH | RESEARCHING |
| JONES  | RESEARCHING |
| SCOTT  | RESEARCHING |
| ADAMS  | RESEARCHING |
| FORD   | RESEARCHING |
| ALLEN  | SALES       |
| WARD   | SALES       |
| MARTIN | SALES       |
| BLAKE  | SALES       |
| TURNER | SALES       |
| JAMES  | SALES       |
+--------+-------------+

语法：

...
    A
join
    B
on
    连接条件
where
    ...

SQL99语法结构更清晰一些：表的连接条件和后来的 where条件分离了。

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

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 |
+--------+---------+-------+
| SIMITH |  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 |
+--------+---------+-------+

案例：找出每个员工的上级领导，要求显示员工名和对应的领导名。(所有员工必须全部查询出来。)

empno 员工编号
ename 员工名称
mgr 领导编号

select empno,ename,mgr from emp a;

emp a 员工表

+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SIMITH | 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 |
+-------+--------+------+

FORD既是员工(empno = 7902,即 FORD的员工编号为 7902)，也是(员工 SIMITH的)领导(SIMITH:mrg = 7902,即 SIMITH的=领导编号为 7902,也就是 FORD)

emp b 领导表

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

员工的领导编号 = 领导的员工编号

即: a.mgr = b.empno;

内连接方式：

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

结果：

+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SIMITH | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

什么是外连接，和内连接有什么区别？

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

外连接的分类？

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

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

案例：找出每个员工的上级领导？

emp a 员工表

+-------+--------+------+
| empno | ename  | mgr  |
+-------+--------+------+
|  7369 | SIMITH | 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   |
+-------+--------+

案例：找出每个员工的上级领导？

  • 外连接方式之左连接： join前面的 outer可以省略
select
    a.ename as '员工名',b.ename as '领导名'
from
    emp a
left outer join
    emp b
on
    a.mgr = b.empno;

结果：

+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SIMITH | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+
  • 外连接方式之右连接： join前面的 outer可以省略
select
    a.ename as '员工名',b.ename as '领导名'
from
    emp b
right outer join
    emp a
on
    a.mgr = b.empno;

结果：

+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SIMITH | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | NULL   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

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

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

emp员工表

+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SIMITH | 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 |    NULL |     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 | RESEARCHING | 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;

结果：

+--------+------------+--------+
| deptno | dname      | loc    |
+--------+------------+--------+
|     40 | OPERATIONS | BOSTON |
+--------+------------+--------+

ifnull()空处理函数

ifnull(可能为NULL的数据，被当做什么处理)：属于单行处理函数。

eg：

设置津贴为NULL的数据为0。

select ename,ifnull(comm,0) as comm from emp;

案例：设置没有上级领导的员工名为BOSS。

select a.ename as '员工名',ifnull(b.ename,'BOSS') as '领导名' from emp a left join emp b on a.mgr = b.empno;

结果：

+--------+--------+
| 员工名 | 领导名 |
+--------+--------+
| SIMITH | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| KING   | BOSS   |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

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

拆解――:

select empno,ename,deptno from emp;

emp e

+-------+--------+---------+--------+
| empno | ename  | sal     | deptno |
+-------+--------+---------+--------+
|  7369 | SIMITH |  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 | RESEARCHING | 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 |  5000 |
+-------+-------+-------+

注意，这里解释一下：

....
    A
join
    B
join
    C
on
...

表示A表和B表先进行连接，连接之后A表继续和C表进行连接。

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;

结果：

+--------+-------------+-------+
| ename  | dname       | grade |
+--------+-------------+-------+
| SIMITH | RESEARCHING |     1 |
| ALLEN  | SALES       |     3 |
| WARD   | SALES       |     2 |
| JONES  | RESEARCHING |     4 |
| MARTIN | SALES       |     2 |
| BLAKE  | SALES       |     4 |
| CLARK  | ACCOUNTING  |     4 |
| SCOTT  | RESEARCHING |     4 |
| KING   | ACCOUNTING  |     5 |
| TURNER | SALES       |     3 |
| ADAMS  | RESEARCHING |     1 |
| JAMES  | SALES       |     1 |
| FORD   | RESEARCHING |     4 |
| MILLER | ACCOUNTING  |     2 |
+--------+-------------+-------+

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

员工对应的领导

select e.ename '员工', e1.ename '领导' from emp e left outer join emp e1 on e.mgr = e1.empno;

结果：

+--------+--------+
| 员工   | 领导   |
+--------+--------+
| SMITH  | FORD   |
| ALLEN  | BLAKE  |
| WARD   | BLAKE  |
| JONES  | KING   |
| MARTIN | BLAKE  |
| BLAKE  | KING   |
| CLARK  | KING   |
| SCOTT  | JONES  |
| TURNER | BLAKE  |
| ADAMS  | SCOTT  |
| JAMES  | BLAKE  |
| FORD   | JONES  |
| MILLER | CLARK  |
+--------+--------+

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;

什么是子查询？

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

子查询都可以出现在哪里？

select
    ..(select).

from
    ..(select).

where
    ..(select).

比如：查询员工信息，查询哪些人是管理者，要求显示其员工编号和员工姓名。

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

select * from emp where sal > avg(sal);# 错误的写法。where后面不能直接使用分组函数。

注意：分组函数不能直接使用在 where关键字后面。因为 group by是在 where执行之后才会执行的。必须先分组，再执行分组函数。

任何一个分组函数(count sum avg max min)都是在 group by语句执行结束之后才会执行。

第一步：找出平均薪资

select avg(sal) from emp;

第二步： where过滤

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

结果：

+-------+-------+-----------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
+-------+-------+-----------+------+------------+---------+------+--------+
|  7566 | JONES | MANAGER   | 7839 | 1981-04-02 | 2975.00 | NULL |     20 |
|  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 |
|  7902 | FORD  | ANALYST   | 7566 | 1981-12-03 | 3000.00 | NULL |     20 |
+-------+-------+-----------+------+------------+---------+------+--------+

案例：找出每个部门平均薪资等级。(按照部门编号分组，求 sal的平均值) avg(sal)

第一步：找出每个部门平均薪水

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

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

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

select
    t.*,s.grade
from
    (select deptno,avg(sal) as avgsal 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 |
+--------+-------------+-------+

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

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

select
    e.ename,e.sal,e.deptno,s.grade
from
    emp e
join
    salgrade s
on
    e.sal between s.losal and s.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
    deptno;

结果：

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

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

不嵌套方式：

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

+--------+------------+
| 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 |
+--------+------------+

嵌套方式：

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

+--------+--------+------------+
| ename  | deptno | dname      |
+--------+--------+------------+
| SMITH  |     20 | RESEARCH   |
| ALLEN  |     30 | SALES      |
| WARD   |     30 | SALES      |
| JONES  |     20 | RESEARCH   |
| MARTIN |     30 | SALES      |
| BLAKE  |     30 | SALES      |
| CLARK  |     10 | ACCOUNTING |
| SCOTT  |     20 | RESEARCH   |
| KING   |     10 | ACCOUNTING |
| TURNER |     30 | SALES      |
| ADAMS  |     20 | RESEARCH   |
| JAMES  |     30 | SALES      |
| FORD   |     20 | RESEARCH   |
| MILLER |     10 | ACCOUNTING |
+--------+--------+------------+

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

第一种： or

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

第二种： in

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

第三种： union

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

结果：

+--------+----------+
| ename  | job      |
+--------+----------+
| ALLEN  | SALESMAN |
| WARD   | SALESMAN |
| JONES  | MANAGER  |
| MARTIN | SALESMAN |
| BLAKE  | MANAGER  |
| CLARK  | MANAGER  |
| TURNER | SALESMAN |
+--------+----------+

两张不相干的表中的数据拼接在一起显示？

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 |
+------------+

select ename,sal from emp
union
select dname,loc from dept;# 列数要相同

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;

结果：

+-------+---------+
| ename | sal     |
+-------+---------+
| KING  | 5000.00 |
| SCOTT | 3000.00 |
| FORD  | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

select      5
    ...

from        1
    ...

where       2
    ...

group by    3
    ...

having      4
    ...

order by    6
    ...

limit       7
    ...;

select ename,sal from emp order by sal desc limit 3,6;

每页显示3条记录：

第1页：0,3
第2页：3,3
第3页：6,3
第4页：9,3
第5页：12,3

每页显示 pageSize条记录：

第 pageNo页： (pageSize - 1) * pageSize,pageSize

pageSize是什么？

  • 是每页显示多少条记录

pageNo是什么？

  • 显示第几页

java代码

{
    int pageNo = 2;// 页码是2
    int pageSize = 10;// 每页显示10条记录
    limit (pageSize - 1) * pageSize,pageSize
}

建表语句的语法格式：

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

);

创建表的时候，表中有字段：

  • 字段名
  • 字段数据类型
  • 字段长度限制
  • 字段约束

关于MySQL当中字段的数据类型？

常见的字段名数据类型：

int 整数型
bigint  长整型(对应java的long)
float   浮点型
double  浮点型 例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
char    定长字符串(对应java中的String)
varchar 可变长字符串(对应java中的StringBuffer/StringBuffer)
date    日期类型(对应Java中的java.sql.Date类型)
BLOB(Binary Large OBject)       二进制大对象  按二进制来存储 通常像图片、文件、音乐等信息就用BLOB字段来存储，先将文件转为二进制再存储进去。
CLOB(Character Large OBject)    字符大对象   可以直接存储文字 像文章或者是较长的文字，就用CLOB存储。

建立学生信息表，字段包括：学号、姓名、性别、班级标识、出生日期。

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

char和 varchar怎么选择？

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

当一个字段的数据长度不确定，例如：简介、姓名等都是采用 varchar。

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

语法格式：

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

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

eg：

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

select * from t_student;

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
+------+----------+------+------------+------------+

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

select * from t_student;

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
|    2 | lisi     | 1    | gaosan1ban | 1950-12-10 |
+------+----------+------+------------+------------+

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

select * from t_student;

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
|    2 | lisi     | 1    | gaosan1ban | 1950-12-10 |
| NULL | wangwu   | NULL | NULL       | NULL       |
+------+----------+------+------------+------------+

insert into t_student(no) values(3);

select * from t_student;

+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
|    2 | lisi     | 1    | gaosan1ban | 1950-12-10 |
| NULL | wangwu   | NULL | NULL       | NULL       |
|    3 | NULL     | NULL | NULL       | NULL       |
+------+----------+------+------------+------------+

创建表赋默认值/删除表

删除表

drop table if exists t_student;# 如果t_student表存在的话，就删除该表。

再建一张表：

create table t_student(
    no bigint,
    name varchar(255),
    sex char(1) default 1,  # default指定默认值
    classno varchar(255),
    birth char(10)
);

select * from t_student;

+------+----------+------+---------+-------+
| no   | name     | sex  | classno | birth |
+------+----------+------+---------+-------+
| NULL | zhangsan | 1    | NULL    | NULL  |
+------+----------+------+---------+-------+

注意：当一条 insert语句执行成功之后，表格当中必然会多出一行记录。不能使用 insert语句插入数据了，只能使用 update进行更新。

插入字段省略不写

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

一次插入多行数据

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

语法：

create table 表名 as select语句;# 将查询结果当做创建出来。

eg：

create table emp1 as select * from emp;

+-------+--------+-----------+------+------------+---------+---------+--------+
| empno | ename  | job       | mgr  | hiredate   | sal     | comm    | deptno |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SIMITH | 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 |    NULL |     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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

create table emp2 as select empno,ename from emp;

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

create table dept1 as select * from dept;

select * from dept1;

+--------+-------------+----------+
| deptno | dname       | loc      |
+--------+-------------+----------+
|     10 | ACCOUNTING  | NEW YORK |
|     20 | RESEARCHING | DALLAS   |
|     30 | SALES       | CHICAGO  |
|     40 | OPERATIONS  | BOSTON   |
+--------+-------------+----------+

insert into dept1 select * from dept;

select * from dept1;

+--------+-------------+----------+
| deptno | dname       | loc      |
+--------+-------------+----------+
|     10 | ACCOUNTING  | NEW YORK |
|     20 | RESEARCHING | DALLAS   |
|     30 | SALES       | CHICAGO  |
|     40 | OPERATIONS  | BOSTON   |
|     10 | ACCOUNTING  | NEW YORK |
|     20 | RESEARCHING | DALLAS   |
|     30 | SALES       | CHICAGO  |
|     40 | OPERATIONS  | BOSTON   |
+--------+-------------+----------+

语法格式：

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

注意：没有条件，整张表数据全部更新。

案例：将部门10的住址 loc修改为 SHANGHAI，将部门名称 dname修改为 RENSHIBU

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

更新所有记录

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

语法格式：

delete from 表名 where 条件;

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

删除10部门数据？

delete from dept1 where deptno = 10;

删除所有记录？

delete from dept1;

怎么删除大表中的数据？(重点)

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

删除表？

drop table 表名;# 这个通用

drop table if exists 表名;# Oracle不支持这种写法

DQL(select) DML(insert delete update) DDL(create drop alter)

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

增删改查有一个术语：CRUD操作

Create(增) Retrieve(检索) Update(修改) Delete(删除)

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

  • 非空约束(not null)：约束的字段不能为NULL
  • 唯一约束(unique)：约束的字段不能重复
  • 主键约束(primary key)：约束的字段既不能为NULL，也不能重复。(简称PK)
  • 外键约束(foreign key)：(简称FK)
  • 检查约束(check):注意：Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。
drop table if exists t_user;
create table t_user(
    id int,
    username  varchar(255) not null,
    password  varchar(255)
);

insert into t_user(id,password) values(1,'123');
错误：ERROR 1364 (HY000): Field 'username' doesn't have a default value

insert into t_user(id,username,password) values(1,'lisi','123');

结果：

+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | lisi     | 123      |
+------+----------+----------+

Original: https://www.cnblogs.com/zhllw/p/16090812.html
Author: 学者莱维
Title: MySQL学习笔记-day02

原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/599258/

转载文章受原作者版权保护。转载请注明原作者出处！

赞 (0)
0

【自取】最近整理的，有需要可以领取学习：



Linux核心资料大放送~

全栈面试题汇总（持续更新&可下载）

一个提高学习100%效率的工具！

【超详细】深度学习面试题目！

LeetCode Python刷题答案下载！

LeetCode Java版刷题答案下载！

LeetCode C++ 版本，抓紧保存！

LeetCode GO语言 刷题答案下载！

大家都在看

  • 数据库

    建库操作 #创建数据库（默认字符集编码） create database test20210420 #创建数据库的时候指定字符集编码以及字符校验规则 create database…

    数据库 2023年5月24日
    055

  • 数据库概述

    MySQL的启动、停止 启动： net start mysql80 停止： net stop mysql80 (PS：mysql80为Win注册到MySQL中的系统服务名称)* M…

    数据库 2023年5月24日
    065

  • MySQL实战45讲 17

    17 | 如何正确地显示随机消息？ 场景：从一个单词表中随机选出三个单词。 表的建表语句和初始数据的命令如下，在这个表里面插入了 10000 行记录： CREATE TABLE w…

    数据库 2023年6月14日
    054

  • 关于.netcore6图片处理（生成缩略图、验证码、加水印）发布到docker(liunx)踩到的坑

    最近在开发一个项目，需要对图片进行处理，比如生成缩略图、生成图片验证码、图片添加水印等功能，项目使用.netcore6.0开发，开发系统使用的云桌面（win10系统），由于是云桌面…

    数据库 2023年6月9日
    088

  • Pisanix v0.2.0 发布｜新增动态读写分离支持

    1.动态读写分离介绍 1.1 介绍 读写分离是业界使用 MySQL 高可用最常用的方案之一，在实际场景中可以提高查询性能，降低服务器负载。本次版本在 v0.1.0 静态规则基础上增…

    数据库 2023年6月16日
    082

  • leetcode 669. Trim a Binary Search Tree 修剪二叉搜索树 (简单)

    一、题目大意 给你二叉搜索树的根节点 root ，同时给定最小边界low 和最大边界 high。通过修剪二叉搜索树，使得所有节点的值在[low, high]中。修剪树 不应该 改变…

    数据库 2023年6月16日
    083

  • python 学习笔记(十二)–Django 基本知识点小结

    构造函数格式： 作用： 向客户端浏览器返回相应，同时携带响应体内容。 参数： –content:表示返回的内容。 –status_code:返回的HTTP响…

    数据库 2023年6月16日
    065

  • 阿里巴巴编码规范-考试认证

    阿里巴巴编码规范-考试认证 雨打梨花深闭门，忘了青春，误了青春。 1、注册阿里云账号 2、购买认证 需要怒支付一顿早餐Q，可以用支付宝支付，选择支付宝支付然后直接输入支付密码就OK…

    数据库 2023年6月14日
    0170

  • Centos7 离线安装K3s

    1、安装前准备 github地址：https://github.com/k3s-io/k3s/releases k3s二进制文件：k3s下载地址：github地址 / 百度网盘地址…

    数据库 2023年6月14日
    099

  • 第十六章：接口

    本篇翻译自《Practical Go Lessons》 Chapter 16: Interfaces 1 你将在本章学到什么？ 什么是类型接口？ 如何定义接口。 “实现…

    数据库 2023年6月6日
    084

  • Java8Stream流

    Stream流呢，以前我也有所了解，像一些面试题中也出现过，Java8的新特性，有一块就是这个Stream操作集合，而且在看一些项目中也使用的比较多。但总感觉自己学的一知半解，所以…

    数据库 2023年6月11日
    072

  • 从源码分析 XtraBackup 的备份原理

    MySQL物理备份工具，常用的有两个：MySQL Enterprise Backup 和 XtraBackup。 前者常用于MySQL企业版，后者常用于MySQL社区版、Perco…

    数据库 2023年6月11日
    0110

  • MySQL实战45讲 19

    19 | 为什么我只查一行的语句，也执行这么慢？ 有些情况下，”查一行”，也会执行得特别慢。 需要说明的是，如果 MySQL 数据库本身就有很大的压力，导致…

    数据库 2023年5月24日
    045

  • MySQL设计表结构

    时间datetime 创建时间不能自动更新，更新时间需要自动更新 CURRENT_TIMESTAMP：创建时，会用当前时间自动填充该字段值 CURRENT_TIMESTAMP ON…

    数据库 2023年6月9日
    075

  • 23种设计模式之分类总结

    关于设计模式的学习要告一段落了，学习的这一路上，也收到了不少小伙伴的留言，以及点赞给了我莫大的鼓励，我在这里谢谢大家的鼓励。。。 我会再接再厉，嘿嘿。。。 以上的话虽是真心话，但是…

    数据库 2023年6月6日
    0271

  • innobackupex备份源码解析

    目前MySQL的物理备份大多数采用xtrabackupex进行，其备份过程如下图所示，这里通过解析 xtrabackup 的源码来详细看看其是如何进行备份的，xtrabackup …

    数据库 2023年6月9日
    098
亲爱的 Coder【最近整理，可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球