【MySQL】入门基础(二)

去除重复记录

把查询结果去除重复记录。(注意:不会改变原表数据)

去重需要使用一个关键字:distinct

select distinct job from emp;

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

select ename,distinct job from emp; //这样编写语法错误

distinc只能出现在所有字段的最前方

select distinct job,deptno from emp;

distinct 出现在job和deptno两个之前,表示两个字段联合起来去重。

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

统计工作岗位的数量:

select count(distinct job) from emp;

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

!!!连接查询

什么是连接查询?

从一张表中单独查询,称为单表查询。

emp表和dept表联合起来查询数据:从emp表中取员工名字,从dept表中取部门名字。

这种跨表查询,多张表联合起来查询数据,称为连接查询

连接查询的分类

  • 根据语法的年代分类:

    SQL92 ,SQL99(重点学习SQL99)

  • 根据表连接方式分类:

    • 内连接
      • 等值连接
      • 非等值连接
      • 自连接
    • 外连接
      • 左外连接(左连接)
      • 右外连接(右连接)
    • 全连接(几乎不用)

当两张表进行连接查询时,没有任何条件的限制会发生什么现象?

案例:查询每个员工所在部门名称?

mysql> select ename,deptno from 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 |
+--------+--------+

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

select ename,dname from emp,dept; //两张表没有连接限制

+--------+------------+
| 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行记录  14*4=56

当两张表进行连接查询没有任何连接限制的时候最终查询结果条数,是两张表条数的乘积,这种现象被称为:笛卡尔现象。

如何避免笛卡尔现象?

连接时加条件,满足这个条件的记录被筛选出来!

select ename,dname from emp,dept where emp.deptno = dept.deptno;

更高效率的查法:

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno; //给表emp、dept起别名 SQL92语法

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

思考:最终查询的结果是14条,但是匹配的过程中,匹配的次数减少了吗?

​ 没有减少匹配次数,只是进行了四选一。

注意:通过笛卡尔现象得知,表的连接次数越多效率越低,所以要尽量减少表的连接。

内连接之等值连接

案例:查询每个员工所在的部门名称,显示员工名和部门名:

emp e 和dept d表进行连接,条件是:e.deptno = d.deptno

select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno; //SQL92语法

SQL92语法 缺点:结构不清晰,表的连接条件和后期进一步筛选的条件,都放到了where后面。

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno; //SQL99语法 jion... on

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

SQL99语法:

select
  ...
from
  a
join
  b
on
  a和b的连接条件
where
  筛选条件

优点:表连接的条件是独立的,连接之后,如果还需要进一步筛选,往后添加where即可。

select 
e.ename,d.dname
from
emp e
inner join	#内连接
dept d
on
e.deptno = d.deptno

inner join表示内连接,其中的inner可以省略,带着inner可读性更好。

e.deptno = d.deptno 条件是等量关系,所以被称为等值连接

内连接之非等值连接

案例:找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级:

mysql> select * from emp; e
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> select * from salgrade; s
+-------+-------+-------+
| GRADE | LOSAL | HISAL |
+-------+-------+-------+
|     1 |   700 |  1200 |
|     2 |  1201 |  1400 |
|     3 |  1401 |  2000 |
|     4 |  2001 |  3000 |
|     5 |  3001 |  9999 |
+-------+-------+-------+

SQL语句:

select 
e.ename,e.sal,s.grade
from
emp e
inner join  #inner可以省略
salgrade s
on
e.sal between s.losal and s.hisal; #e.sal >= s.losal and e.sal <= 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 |
+--------+---------+-------+

内连接之自连接

案例:查询员工的上级领导,要求显示员工名和对应的领导名:

mysql> select empno,ename,mgr from emp;
+-------+--------+------+
| 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 e 员工表

emp m 领导表

select e.ename epname,m.ename mgname from emp e inner join emp m on e.mgr = m.empno;

e.mgr = m.empno员工的领导编号 = 领导的员工编号

+--------+--------+
| epname | mgname |
+--------+--------+
| 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  |
+--------+--------+
13 rows in set (0.00 sec) #King 是老板没有领导

外连接

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

内连接(A和B连接,AB两张表没有主次关系,平等的)

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

外连接(右外连接):

right:表示把join关键字右边的表当做主表,主要是为了将这张表的所有数据全部查询出来,捎带关联查询左边的表。

在外连接中,两张表连接,产生了主次关系

select 
e.ename,d.dname
from
emp e 
right outer join 
dept d  #right join dept d 把join右边表的所有数据都查询出来
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 |
| NULL   | OPERATIONS |
+--------+------------+

外连接(左外连接):

select 
e.ename,d.dname
from
dept d 
left outer join  #outer可以省
emp e
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 |
| NULL   | OPERATIONS |
+--------+------------+

带有right的是右外连接,又称为右连接

带有left的是左外连接,又称为左连接

任何一个右连接都有一个左连接的写法,任何一个左连接都有一个右连接的写法。

left(right) outer join,其中outer可以省略,带着可读性强。

注意:内连接和外连接的主要区别在于 join 右边是否有 left/right关键字。

思考:外连接的查询结果条数一定是 >= 内连接的查询结果条数?

正确。

案例:查询每个员工的上级领导,要求显示所有的员工名字和领导名

select empno,ename,mgr from emp;
+-------+--------+------+
| 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 |
+-------+--------+------+
select e.ename '员工名',m.ename '领导名' from emp e left join emp m on e.mgr = m.empno;
+--------+--------+
| 员工名  | 领导名  |
+--------+--------+
| SMITH  | 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  |
+--------+--------+
14 rows in set (0.00 sec)

三张表、四张表如何连接?

语法:

select
  ...
from
  a
join
  b
on
  a和b的连接条件
join
  c
on
  a和c的连接条件
right join
  d
on
  a和d的连接条件

一条SQL中内连接和外连接可以混合。

案例:找出每个员工的部门名称以及工资等级,要求显示员工名、部门名、薪资和薪资等级?

select e.ename,d.dname,e.sal,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;

select 
	e.ename,d.dname,e.sal,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      | sal     | grade |
+--------+------------+---------+-------+
| SMITH  | RESEARCH   |  800.00 |     1 |
| ALLEN  | SALES      | 1600.00 |     3 |
| WARD   | SALES      | 1250.00 |     2 |
| JONES  | RESEARCH   | 2975.00 |     4 |
| MARTIN | SALES      | 1250.00 |     2 |
| BLAKE  | SALES      | 2850.00 |     4 |
| CLARK  | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | RESEARCH   | 3000.00 |     4 |
| KING   | ACCOUNTING | 5000.00 |     5 |
| TURNER | SALES      | 1500.00 |     3 |
| ADAMS  | RESEARCH   | 1100.00 |     1 |
| JAMES  | SALES      |  950.00 |     1 |
| FORD   | RESEARCH   | 3000.00 |     4 |
| MILLER | ACCOUNTING | 1300.00 |     2 |
+--------+------------+---------+-------+

案例:找出每个员工的部门名称以及工资等级,还要上级领导,要求显示员工名、领导名,部门名、薪资和薪资等级?

select e.ename as ename,m.ename as lname,d.dname,e.sal,s.grade from emp e left join emp m on e.mgr = m.empno join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

select 
	e.ename as ename,m.ename as lname,d.dname,e.sal,s.grade 
from 
	emp e 
left join 
	emp m 
on 
	e.mgr = m.empno 
join 
	dept d 
on 
	e.deptno = d.deptno 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal;
	
+--------+-------+------------+---------+-------+
| ename  | lname | dname      | sal     | grade |
+--------+-------+------------+---------+-------+
| SMITH  | FORD  | RESEARCH   |  800.00 |     1 |
| ADAMS  | SCOTT | RESEARCH   | 1100.00 |     1 |
| JAMES  | BLAKE | SALES      |  950.00 |     1 |
| WARD   | BLAKE | SALES      | 1250.00 |     2 |
| MARTIN | BLAKE | SALES      | 1250.00 |     2 |
| MILLER | CLARK | ACCOUNTING | 1300.00 |     2 |
| ALLEN  | BLAKE | SALES      | 1600.00 |     3 |
| TURNER | BLAKE | SALES      | 1500.00 |     3 |
| JONES  | KING  | RESEARCH   | 2975.00 |     4 |
| BLAKE  | KING  | SALES      | 2850.00 |     4 |
| CLARK  | KING  | ACCOUNTING | 2450.00 |     4 |
| SCOTT  | JONES | RESEARCH   | 3000.00 |     4 |
| FORD   | JONES | RESEARCH   | 3000.00 |     4 |
| KING   | NULL  | ACCOUNTING | 5000.00 |     5 |
+--------+-------+------------+---------+-------+

子查询

什么是子查询?

select语句中嵌套select语句,被嵌套的select语句称为子查询。

子查询可以出现在哪里?

语法格式:

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

where子句中出现子查询

案例:找出比最低工资高的员工姓名和工资?

select ename,sal from emp where sal>min(sal); //错误的,where不能直接使用分组函数

实现思路:

  • 第一步:查询最低工资是多少?

    select min(sal) from emp;

    +----------+
    | min(sal) |
    +----------+
    |   800.00 |
    +----------+
    
  • 第二步:找出大于800的

    select ename,sal from emp where sal > 800;

    +--------+---------+
    | ename  | sal     |
    +--------+---------+
    | 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 ename,sal from emp where sal > (select min(sal) from emp);

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

from子句中的子查询

注意:from后面的子查询,可以将子查询的查询结果当做一张临时表。(技巧)

案例:找出每个岗位的平均工资的薪资等级:

  • 第一步:找出每个岗位的平均薪资(按照岗位分组求平均值)

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

    +-----------+-------------+
    | job       | avg(sal)    |
    +-----------+-------------+
    | ANALYST   | 3000.000000 |
    | CLERK     | 1037.500000 |
    | MANAGER   | 2758.333333 |
    | PRESIDENT | 5000.000000 |
    | SALESMAN  | 1400.000000 |
    +-----------+-------------+
    
  • 第二步:把第一步的查询结果当作一张真实存在的表t,查找t表的薪资等级:

    select t.job,round(t.avgsal,2) as avgsal,s.grade from (select job,avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;

    select
    	t.job,round(t.avgsal,2) as avgsal,s.grade
    from
    	(select job,avg(sal) as avgsal from emp group by job) t
    join
    	salgrade s
    on
    	t.avgsal between s.losal and s.hisal;
    	
    +-----------+---------+-------+
    | job       | avgsal  | grade |
    +-----------+---------+-------+
    | ANALYST   | 3000.00 |     4 |
    | CLERK     | 1037.50 |     1 |
    | MANAGER   | 2758.33 |     4 |
    | PRESIDENT | 5000.00 |     5 |
    | SALESMAN  | 1400.00 |     2 |
    +-----------+---------+-------+
    

    t表和s表进行表连接,条件:t.avg(sal) between s.losal and s.hisal;

    注意:t.avg(sal) 不能直接放到 on 后面,必须在子查询中起别名 select job,avg(sal) as avgsal from emp group by job

select子句出现子查询(了解)

案例:找出每个员工的部门名称,要求显示员工名、部门名:

select e.ename,(select d.dname from dept d 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 |
+--------+------------+

注意:对于select后面的子查询来说,这个子查询只能一次返回1条结果,多余1条就报错。

union合并查询结果集

案例:查询工作岗位是MANAGER和SALESMAN的员工?

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

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

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

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

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

union效率高一些。对于表连接来说,每连接一次新表,则匹配的次数满足笛卡尔积,成倍的翻。但union可以减少匹配的次数,在减少匹配次数的情况下,还能完成两个结果集的拼接。

a 连接 b 连接 c

a 10条记录

b 10条记录

c 10条记录

匹配次数是:1000

a 连接 b一个结果:10 * 10 --> 100次

a 连接 b一个结果:10 * 10 --> 100次

使用union的话是:100 + 100 = 200次,(union 把乘法变成加分运算)

union在使用的时候的注意事项:

select ename,job from emp where job = 'MANAGER'
union 
select ename from emp where job = 'SALESMAN';
#ERROR 1222 (21000): The used SELECT statements have a different number of columns

union在进行结果集合并的时候,要求两个结果集的列数相同

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

以上的SQL语句在MySQL中可以执行,但是在Oracle中无法执行,Oracle语法严格,要求:结果集合并时列和列的数据类型也要一致

!!! limit

limit作用:

limit是将查询结果集的一部分取出来,通常使用在分页查询当中。

分页的作用是为了提高用户的体验,因为一次全部显示出来,用户体验差,可以一页一页翻页看。

limit使用

完整用法:limit startIndex, length startIndex 是起始下标,默认从0开始;length是长度。

缺省用法:limit 5; 这是取前5;

按照薪资降序,取出排名在前5名的员工:

select ename,sal from emp order by sal desc limit 5; //取前5的记录

完整的写法:

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

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

注意:mysql 当中limit在 order by之后执行。

取出工资排名在[3-5]名的员工?

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

2表示起始下标从2开始,3表示长度。

+-------+---------+
| ename | sal     |
+-------+---------+
| SCOTT | 3000.00 |
| JONES | 2975.00 |
| BLAKE | 2850.00 |
+-------+---------+

取出工资排名在【5-9】名的员工:

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

+--------+---------+
| ename  | sal     |
+--------+---------+
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+

分页

每页显示3条记录

第1页:limit 0,3 [0,1,2]

第2页:limit 3,3 [3,4,5]

第3页:limit 6,3 [6,7,8]

第4页:limit 9,3 [9,10,11]

每页显示pageSize条记录

第pageNo页:limit (pageNo-1)*pageSize , pageSize

public static void main(String[] args){
    //用户提交过来的一个页码,以及每页显示的记录条数
    int pageNo = 5;//第五页
    int pageSize = 10;//每页显示10条
    int startIndex = (pageNo - 1) * pageSize;
    String sql = "select ... limit" + startIndex + "," + pageSize;
}

关于DQL语句的总结

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

执行顺序:

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

表(建表)

建表的语法格式:

建表属于DDL语句 DDL包括:create drop alter

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

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

表名:建议以t_或者tbl_开始,可读性强,见名知意

字段名:见名知意

表名和字段名都属于标识符。

关于mysql中的数据类型

MySQL中有很多数据类型,只需掌握一些常用的数据类型即可。

  • varchar(最长255)

    可变长度的字符串,比较智能,节省空间,会根据实际的数据长度动态分配空间。

    优点:节省空间

    缺点:需要动态分配空间,速度慢。

  • char(最长255)

    定长字符串,不管实际数据长度是多少,分配固定长度空间存储数据。使用不恰当的时候,可能会导致空间的浪费。

    优点:不需要动态分配空间,速度快。

    缺点:使用不当可能会导致空间的浪费。

    varchar 和 char 该如何选择?

    ​ 性别字段选char,因为性别是固定长度的字符串。

    ​ 姓名字段选择varchar,因为每一个人的名字长度不同,所以选择varchar

  • int(最长11)

    数字中的整数型,等同于java中的int

  • bigint

    数字中的长整型,等同于java中的long

  • float

    单精度浮点型数据

  • double

    双精度浮点型数据

  • date

    短日期类型

  • datetime

    长日期类型

  • clob

    字符大对象,最多可以存储4G的字符串。比如:存储一篇文章,存储一个说明。

    超过255个字符的都要采用CLOB字符大对象来存储。

    Character Large OBject :CLOB

  • blob

    二进制大对象,Binary Large OBject 专门用来存储图片、声音、视频等流媒体数据。

    往BLOB类型的字段上面插入数据的时候,例如插入一个图片、视频等,需要使用IO流

t_move 电影表(专门存储电影信息的)

编号名字描述信息上映日期时长海报类型
no(bigint)name(varchar)story(clob)playtime(date)time(double)img(blob)type(char)
10000哪吒2022-03-252.5‘1’
10001林正英之娘娘2022-04-011.5‘2’

创建一个学生表

学号姓名性别年龄邮箱地址
stuno(int)name(varchar)sex(char)age(int)email(varchar)
create table t_student(
	stuno int,
    name varchar(255),
    sex char(1),
    age int(3),
    email varchar(255)
);

drop table t_student; //当这张表不存在的时候会报错

drop table if exists t_student; //如果这张表存在就删除

插入数据insert(DML)

语法格式:

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

注意:字段名和值要一一对应,数量要对应、数据类型也要对应。

insert into t_student(stuno,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');

insert into t_student(email,name,sex,age,stuno) values('lisi@123.com','lisi','f',22,2);

mysql> select * from t_student;
+-------+----------+------+------+------------------+
| stuno | name     | sex  | age  | email            |
+-------+----------+------+------+------------------+
|     1 | zhangsan | m    |   20 | zhangsan@123.com |
|     2 | lisi     | f    |   22 | lisi@123.com     |
+-------+----------+------+------+------------------+

insert into t_student(stuno) values(3);

mysql> select * from t_student;
+-------+----------+------+------+------------------+
| stuno | name     | sex  | age  | email            |
+-------+----------+------+------+------------------+
|     1 | zhangsan | m    |   20 | zhangsan@123.com |
|     2 | lisi     | f    |   22 | lisi@123.com     |
|     3 | NULL     | NULL | NULL | NULL             |
+-------+----------+------+------+------------------+

insert into t_student(name) values('wangwu');

mysql> select * from t_student;
+-------+----------+------+------+------------------+
| stuno | name     | sex  | age  | email            |
+-------+----------+------+------+------------------+
|     1 | zhangsan | m    |   20 | zhangsan@123.com |
|     2 | lisi     | f    |   22 | lisi@123.com     |
|     3 | NULL     | NULL | NULL | NULL             |
|  NULL | wangwu   | NULL | NULL | NULL             |
+-------+----------+------+------+------------------+

结论insert语句一旦执行成功了,那么必然会多一条记录。没有给其它字段指定值的话,默认值是NULL。

drop table if exists t_student;
create table t_student(
	stuno int,
    name varchar(32),
    sex char(1) default 'm',
    age int(3),
    email varchar(255)
);

mysql> desc t_student;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| stuno | int(11)      | YES  |     | NULL    |       |
| name  | varchar(32)  | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | m       |       |
| age   | int(3)       | YES  |     | NULL    |       |
| email | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

insert into t_student(stuno) values(1);

mysql> select * from t_student;
+-------+------+------+------+-------+
| stuno | name | sex  | age  | email |
+-------+------+------+------+-------+
|     1 | NULL | m    | NULL | NULL  |
+-------+------+------+------+-------+

insert 语句中的"字段名"可以省略吗? 可以

insert into t_student values(2); //错误的

前面的字段如果都省略了,那么后面的值都得写上!!

insert into t_student values(2,'zhangsan','f',20,'zhangsan@123.com');

mysql> select * from t_student;
+-------+----------+------+------+------------------+
| stuno | name     | sex  | age  | email            |
+-------+----------+------+------+------------------+
|     1 | NULL     | m    | NULL | NULL             |
|     2 | zhangsan | f    |   20 | zhangsan@123.com |
+-------+----------+------+------+------------------+

insert插入日期

数字格式化:format

select ename,sal from emp;

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

格式化数字:format(数字,‘格式’)

select ename,format(sal,'$999,999') as sal from emp;

+--------+-------+
| ename  | sal   |
+--------+-------+
| SMITH  | 800   |
| ALLEN  | 1,600 |
| WARD   | 1,250 |
| JONES  | 2,975 |
| MARTIN | 1,250 |
| BLAKE  | 2,850 |
| CLARK  | 2,450 |
| SCOTT  | 3,000 |
| KING   | 5,000 |
| TURNER | 1,500 |
| ADAMS  | 1,100 |
| JAMES  | 950   |
| FORD   | 3,000 |
| MILLER | 1,300 |
+--------+-------+

str_to_date:将字符串varchar类型转换成date类型

date_format:将date类型转换成具有一定格式的varchar类型

drop table if exists t_user;
create table t_user(
	id int,
    name varchar(32),
    birth date #生日可以使用日期类型
);
#或者
create table t_user(
	id int,
    name varchar(32),
    birth char(10)	#生日可以使用字符串
);

mysql> desc t_user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
| birth | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

注意:数据库中有一条命名规范:所有的标识符都是全部小写,单词和单词之间使用下划线进行衔接。

插入数据:

insert into t_user(id,name,birth) values(1,'zhangsan','01-10-1990') //1990-10-01

​ 类型不匹配,可以使用str_to_date函数进行类型转换。

str_to_date 语法格式:str_to_date('字符串日期','日期格式')

MySQL的日期格式:

  • %Y 年
  • %m 月
  • %d 日
  • %h 时
  • %i 分
  • %s 秒

insert into t_user(id,name,birth) values(1,'zhangsan',str_to_date('01-10-1990','%d-%m-%Y'));

mysql> select * from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 1990-10-01 |
+------+----------+------------+

str_to_date()函数可以把字符串varchar转换成日期date类型的数据,通常使用在插入insert方面,因为插入的时候需要一个日期类型的数据,需要通过该函数将字符串转为date。

当然,如果你的日期字符串格式写法是%Y-%m-%d格式,str_to_date()函数就不需要了。

insert into t_user(id,name,birth) values(2,'lisi','1990-10-02');

mysql> select * from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 1990-10-01 |
|    2 | lisi     | 1990-10-02 |
+------+----------+------------+

查询的时候能够以特定的日期格式展示吗?

这个时候就得用到date_format()函数,可以将日期类型的数据转换成特定格式的字符串。

select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;

+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 10/01/1990 |
|    2 | lisi     | 10/02/1990 |
+------+----------+------------+

date_format()使用: date_format(日期类型数据,'日期格式'),通常使用在查询日期方面,设置展示的日期格式。

select id,name,birth from t_user;

+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 1990-10-01 |
|    2 | lisi     | 1990-10-02 |
+------+----------+------------+

以上的SQL语句实际上是进行了默认的日期格式化,自动将数据库中的类型转换成varchar类型,采用的是默认的日期格式:%Y-%m-%d

Java中的日期格式:yyyy-MM-dd HH:mm:ss SSS

date 和 datetime两个类型的区别

date是短日期:只包括年月日信息。

datetime是长日期:包括年月日时分秒信息。

drop table if exists t_user;
create table t_user(
	id int,
    name varchar(32),
    birth date,
    create_time datetime
);
#id是整型
#name是字符串
#birth是短日期
#create_time是这条记录的创建时间:长日期类型

mysql短日期的默认格式:%Y-%m-%d

mysql长日期的默认格式:%Y-%m-%d %h:%i:%s

insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-01-02','2022-03-25 21:56:50');

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-01-02 | 2022-03-25 21:56:50 |
+------+----------+------------+---------------------+

在mysql当中如何获取系统当前时间?

now()函数,并且获取的时间带有:时分秒信息,是datetime类型的。

insert into t_user(id,name,birth,create_time) values(2,'lisi','1990-10-02',now());

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-01-02 | 2022-03-25 21:56:50 |
|    2 | lisi     | 1990-10-02 | 2022-03-25 22:08:49 |
+------+----------+------------+---------------------+

insert一次插入多条记录【掌握】

mysql> desc t_user;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | YES  |     | NULL    |       |
| name        | varchar(32) | YES  |     | NULL    |       |
| birth       | date        | YES  |     | NULL    |       |
| create_time | datetime    | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

一次可以插入多条记录:

语法:

insert into 表名(字段名1,字段名2) values(),(),();
insert into t_user(id,name,birth,create_time) values
(1,'zs','1980-01-01',now()),
(2,'lisi','1999-10-01',now()),
(3,'wangwu','2000-10-22',now());

mysql> select * from t_user;
+------+--------+------------+---------------------+
| id   | name   | birth      | create_time         |
+------+--------+------------+---------------------+
|    1 | zs     | 1980-01-01 | 2022-03-26 20:34:49 |
|    2 | lisi   | 1999-10-01 | 2022-03-26 20:34:49 |
|    3 | wangwu | 2000-10-22 | 2022-03-26 20:34:49 |
+------+--------+------------+---------------------+

修改update(DML)

语法格式:

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

注意:没有条件限制会导致所有数据全部更新。

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-01-02 | 2022-03-25 21:56:50 |
|    2 | lisi     | 1990-10-02 | 2022-03-25 22:08:49 |
+------+----------+------------+---------------------+

把李四改为Jack、生日改为2000年:

update t_user set name = 'jack',birth = '2000-10-02' where id=2;

mysql> select * from t_user;
+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-01-02 | 2022-03-25 21:56:50 |
|    2 | jack     | 2000-10-02 | 2022-03-25 22:08:49 |
+------+----------+------------+---------------------+

update t_user set name = 'jack',birth = '2000-10-02',create_time = now() where id=2;

+------+----------+------------+---------------------+
| id   | name     | birth      | create_time         |
+------+----------+------------+---------------------+
|    1 | zhangsan | 1990-01-02 | 2022-03-25 21:56:50 |
|    2 | jack     | 2000-10-02 | 2022-03-26 20:06:14 |
+------+----------+------------+---------------------+

更新表中所有数据:

update t_user set name = 'abc';

+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | abc  | 1990-01-02 | 2022-03-25 21:56:50 |
|    2 | abc  | 2000-10-02 | 2022-03-26 20:06:14 |
+------+------+------------+---------------------+

不加where条件,表中所有的name数据都改成abc了。

删除数据 delete (DML)

语法格式:

delete from 表名 where 条件

注意:没有条件,整张表的数据会全被删除。

delete from t_user where id = 2;

mysql> select * from t_user;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | abc  | 1990-01-02 | 2022-03-25 21:56:50 |
+------+------+------------+---------------------+

insert into t_user(id) values(2);

mysql> select * from t_user;
+------+------+------------+---------------------+
| id   | name | birth      | create_time         |
+------+------+------------+---------------------+
|    1 | abc  | 1990-01-02 | 2022-03-25 21:56:50 |
|    2 | NULL | NULL       | NULL                |
+------+------+------------+---------------------+

delete from t_user; //删除整张表的数据

mysql> select * from t_user;
Empty set (0.00 sec)

快速删除表(DDL操作)

删除dept_bake表中的数据:

delete from dept_bake; //这种删除数据方式比较慢

delete删除语句的原理是:逻辑删除 属于==DML==语句

  • 表中的数据被删除了,但是这个数据在硬盘上的真是存储空间不会被释放。
  • 这种删除方法的缺点:删除效率比较低。
  • 这种删除方式的优点支持回滚,后悔了可以恢复数据。
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from dept_bake;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from dept_bake;
Empty set (0.00 sec)

mysql> rollback; #回滚数据
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dept_bake;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

truncate语句删除数据原理:物理删除属于DDL操作

这种删除效率比较高,表背一次截断,物理删除。

  • 这种删除的缺点不支持回滚

  • 这种删除的优点:快速。

truncate table dept_bake; // 属于DDL操作

mysql> select * from dept_bake;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> truncate table dept_bake;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from dept_bake;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from dept_bake;
Empty set (0.00 sec)

如果有上亿条记录的大表:

删除的时候,使用delete,也许需要执行1个小时才能删除完!效率较低

可以选择truncate删除表中的数据,只需要不到1s的时间,效率较高。但是使用truncate之前,必须仔细询问客户是否真的要删除,删除后无法恢复。

truncate只是删除表中的数据,表的结构还在。

删除表操作?

drop table 表名; //这不是删除表中的数据,而是把表删除。

快速创建表(快速复制表)

create table emp2 as select * from emp;

mysql> select * from emp2;
+-------+--------+-----------+------+------------+---------+---------+--------+
| 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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

原理:将一个查询结果当做一张表新建,可以完成表的快速复制,表创建出来,同时表的数据也存在。

create table mytable as select empno,ename from emp where job = 'MANAGER';

mysql> select * from mytable;
+-------+-------+
| empno | ename |
+-------+-------+
|  7566 | JONES |
|  7698 | BLAKE |
|  7782 | CLARK |
+-------+-------+

将查询结果插入到一张表当中?(insert相关的语句)

create table dept_bake as select * from dept;

mysql> select * from dept_bake;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

insert into dept_bake select * from dept; //很少用

mysql> select * from dept_bake;
+--------+------------+----------+
| 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   |
+--------+------------+----------+

对表结构的增删改

在实际开发中表一旦设计好之后,对表结构的修改操作是很少的,修改表结构就是对之前的设计进行了否定,即使需要修改表结构,我们也可以直接使用工具操作。修改表结构的语句不会出现在Java代码当中。开发进行中,修改表的结构,成本比较高。

什么是对表结构的修改?使用alter(属于DDL语句)

添加一个字段,删除一个字段,修改一个字段。

DDL包括:create,drop,alter

出现在java代码当中的sql包括:insert delete update select(这些都是表中的数据操作。)

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

!!!约束

什么是约束?常见的约束有哪些呢?

约束对应的英语单词:constraint

在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中数据的完整性、有效性

约束的作用就是为了保证表中的数据有效

约束包括哪些?

  • 非空约束:not null
  • 唯一性约束:unique
  • 主键约束:primary key (简称 PK)
  • 外键约束:foreign key(简称FK)
  • 检查约束:check (mysql不支持,Oracle支持)

非空约束(not null)

非空约束 not null 约束的字段不能为NULL。

drop table if exists t_vip;
create table t_vip(
	id int,
    name varchar(255) not null
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');

PS:xxx.sql 文件被称为sql脚本文件,sql脚本文件中编写了大量的sql语句。我们执行sql脚本文件的时候,该文件中所有sql语句全部执行!

批量执行sql语句,可以使用sql脚本文件。

在mysql当中执行sql脚本:soucre D:\..\..\xxx.sql

mysql> insert into t_vip(id,name) values(3);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

not null只有列级约束,没有表级约束

唯一性约束 unique

唯一性约束unique的字段不能重复,但是可以为NULL

drop table if exists t_vip;
create table t_vip(
	id int,
    name varchar(255) unique,
    email varchar(255)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
select * from t_vip;
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | zhangsan@123.com |
|    2 | lisi     | lisi@123.com     |
|    3 | wangwu   | wangwu@123.com   |
+------+----------+------------------+

insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');

mysql> insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'

insert into t_vip(id) values(4);

insert into t_vip(id) values(5);

mysql> insert into t_vip(id) values(4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t_vip(id) values(5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t_vip;
+------+----------+------------------+
| id   | name     | email            |
+------+----------+------------------+
|    1 | zhangsan | zhangsan@123.com |
|    2 | lisi     | lisi@123.com     |
|    3 | wangwu   | wangwu@123.com   |
|    4 | NULL     | NULL             |
|    5 | NULL     | NULL             |
+------+----------+------------------+

name字段虽然被unique约束了,但是可以为NULL。

新需求:name和email两个字段联合起来有唯一性

drop table if exists t_vip;
create table t_vip(
	id int,
    name varchar(255) unique,
    email varchar(255) unique
);

name varchar(255) unique, //约束直接添加到列后面的,叫做列级约束
email varchar(255) unique

以上的创建语句是不符合"新需求"的,这样name和email各自唯一了

以下的插入语句是符合"新需求"的。

insert into t_vip (id,name,email) values(1,'zhangsan','zhangsan@123.com');

insert into t_vip (id,name,email) values(2,'zhangsan','zhangsan@sina.com');

mysql> create table t_vip(
    -> id int,
    ->     name varchar(255) unique,
    ->     email varchar(255) unique
    -> );
mysql> insert into t_vip (id,name,email) values(1,'zhangsan','zhangsan@123.com');
mysql> insert into t_vip (id,name,email) values(2,'zhangsan','zhangsan@sina.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'name'

应该这样创建表:

drop table if exists t_vip;
create table t_vip(
	id int,
    name varchar(255),
    email varchar(255),
    unique(name,email)
);

name varchar(255),
email varchar(255),
unique(name,email) //约束没有添加到列后面,这种约束称为表级约束

name 和 email 两个字段联合起来唯一

insert into t_vip (id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip (id,name,email) values(2,'zhangsan','zhangsan@sina.com');

mysql> create table t_vip(
    -> id int,
    ->     name varchar(255),
    ->     email varchar(255),
    ->     unique(name,email)
    -> );
mysql> insert into t_vip (id,name,email) values(1,'zhangsan','zhangsan@123.com');
mysql> insert into t_vip (id,name,email) values(2,'zhangsan','zhangsan@sina.com');
mysql> select * from t_vip;
+------+----------+-------------------+
| id   | name     | email             |
+------+----------+-------------------+
|    1 | zhangsan | zhangsan@123.com  |
|    2 | zhangsan | zhangsan@sina.com |
+------+----------+-------------------+

如果再插入一个重复的值呢?

insert into t_vip (id,name,email) values(3,'zhangsan','zhangsan@sina.com');

ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'

Dupilcate:重复的 entry:条目

什么时候使用表级约束?

​ 需要给多个字段联合起来添加某一个约束的时候,需要使用表级约束

unique 和 not null 联合使用:

drop table if exists t_vip;
create table t_vip(
	id int,
    name varchar(255) not null unique
);

mysql> desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | NO   | PRI | NULL    |       |
+-------+--------------+------+-----+---------+-------+

在MySQL中,如果一个字段同时被 unique 和 not null 同时约束的话,该字段自动变成主键字段(PRI)。但是在Oracle中就不一样。

insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'zhangsan'); #不能重复,错误

ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'name'

insert into t_vip(id) values(2); #不能为空,错误
ERROR 1364 (HY000): Field 'name' doesn't have a default value

!!!主键约束(primary key,简称PK)

主键约束的相关术语:

  • 主键约束:就是一种约束。

  • 主键字段:该字段上添加了主键约束,这样的字段叫做:主键字段

  • 主键值:主键字段中的每个值都叫做:主键值。

    (有点废话文学了😄)

什么是主键?有什么作用?

  • 主键值是每一行记录的唯一标识
  • 主键值是每一行记录的身份证号。

记住:任何一张表都必须有主键,没有主键,表无效。

主键的特征: not null + unique (主键值不能是NULL,同时不能重复!)

主键值

如何给一张表添加主键约束呢?

drop table if exists t_vip;
create table t_vip(
	id int primary key, #列级约束
    name varchar(255)
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');

insert into t_vip(id,name) values(2,'wangwu'); //错误,不能重复

ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

insert into t_vip(name) values('zhaoliu'); //错误,不能为NULL

ERROR 1364 (HY000): Field 'id' doesn't have a default value

可以使用表级约束来添加主键吗?

drop table if exists t_vip;
create table t_vip(
	id int,
    name varchar(255),
    primary key(id) #表级约束
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
mysql> insert into t_vip(id,name) values(1,'lisi'); #error
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

上面一个字段作主键称为单一主键

表级约束主要是给多个字段联合起来添加约束。(复合主键)

drop table if exists t_vip;
create table t_vip(
	id int,
    name varchar(255),
    email varchar(255),
    primary key(id,name) #复合主键
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');

mysql> select * from t_vip;
+----+----------+-------------------+
| id | name     | email             |
+----+----------+-------------------+
|  1 | zhangsan | zhangsan@123.com  |
|  2 | zhangsan | zhangsan@sina.com |
+----+----------+-------------------+

insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com'); //错误的,不能重复

ERROR 1062 (23000): Duplicate entry '1-zhangsan' for key 'PRIMARY'

在实际开发中,不建议使用复合主键,建议使用单一主键,因为主键值存在的意义就是用来识别这行记录的,只要意义达到即可,单一主键可以做到,复合主键比较复杂不建议使用。

主键数量

一个表中主键的约束可以加两个吗?

drop table if exists t_vip;
create table t_vip(
	id int primary key,
    name varchar(255) primary key
);

ERROR 1068 (42000): Multiple primary key defined #主键重复定义

结论:一张表中主键约束只能添加一个

主键值建议类型

主键值建议使用:

  • int
  • bigint
  • char

不建议使用varchar来做主键,主键值一般都是数字,主键都是定长的

自然主键和业务主键

主键除了 单一主键 和 复合主键 之外 ,还可以这样进行分类:

  • 自然主键:主键值是一个自然数,和业务无关系。
  • 业务主键:主键值和业务紧密关联,例如拿银行卡号当主键值,这就是业务主键。

在实际开发当中,自然主键使用的比较多,主键只要做到不重复就行,不需要有意义。业务主键不好,因为主键一旦和业务挂钩,那么业务发生变动的时候,可能会影响到主键值,所以业务主键不建议使用,尽量使用自然主键。

在mysql中,有一种机制,可以用帮助我们自动维护一个主键值

drop table if exists t_vip;
create table t_vip(
	id int primary key auto_increment,
    name varchar(255)
);
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
insert into t_vip(name) values('zhangsan');
select * from t_vip;

+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
|  3 | zhangsan |
|  4 | zhangsan |
|  5 | zhangsan |
|  6 | zhangsan |
|  7 | zhangsan |
|  8 | zhangsan |
+----+----------+

auto_increment :表示自增,从1开始,以1递增。

!!!外键约束(foreign key,简称FK)

外键约束涉及到的相关术语:

  • 外键约束:一种约束(foreign key)
  • 外键字段:该字段上添加了外键约束
  • 外键值:外键字段当中的每一个值。

业务背景:

请设计数据库表,来描述“班级和学生”的信息?

第一种方案:班级和学生存储到一张表中

no(PK)nameclassnoclassname
1jack100高三1班
2lucy100高三1班
3lilei100高三1班
4hanmeimei100高三1班
5zhangsan101高三2班
6lisi101高三2班
7wangwu101高三2班
8zhaoliu101高三2班

以上方案的缺点:数据冗余,空间浪费!这种设计比较失败

第二种方案:班级一张表、学生一张表:

t_calss班级表

classno(PK)classname
100高三1班
101高三2班

t_student学生表

no(PK)namecno(FK --引用t_calss表中的classno)
1jack100
2lucy100
3lilei100
4hanmeimei100
5zhangsan101
6lisi101
7wangwu101
8zhaoliu101

当cno字段没有任何约束的时候,可能会导致数据无效,可能会出现一个102,但是102班级不存在,所以为了保证cno字段的值都是100和101,需要给cno字段添加外键约束。

那么:cno字段就是外键字段。cno字段中的每一个值都是外键值。

注意:t_calss 是父表,t_student是子表

删除表的顺序?

​ 先删子表,再删父表

创建表的顺序?

​ 先创建父表,再创建子表

删除数据的顺序?

​ 先删子表数据,再删父表数据

插入数据的顺序?

​ 先插入父,再插入子

drop table if exists t_student;
drop table if exists t_class;
create table t_class(
	classno int primary key,
    classname varchar(255)
);
create table t_student(
	no int primary key auto_increment,
    name varchar(255),
    cno int,
    foreign key(cno) references t_class(classno)
);
insert into t_class(classno,classname) values(100,'高三1班');
insert into t_class(classno,classname) values(101,'高三2班');

insert into t_student(name,cno) values('jack',100); 
insert into t_student(name,cno) values('lucy',100); 
insert into t_student(name,cno) values('lilei',100); 
insert into t_student(name,cno) values('hanmeimei',100); 
insert into t_student(name,cno) values('zhangsan',101); 
insert into t_student(name,cno) values('lisi',101); 
insert into t_student(name,cno) values('wangwu',101); 
insert into t_student(name,cno) values('zhaoliu',101); 

select * from t_student;
select * from t_class;

image-20220326233933424image-20220326233951461

思考:子表中的外键引用的父类中的某个字段,被引用的这个字段必须是主键吗?

不一定是主键,但至少具有唯一性 有unique约束。

测试:外键可以为空吗?

外键值可以为NULL

insert into t_student(name) values('wangliu');

mysql> select * from t_student;
+----+-----------+------+
| no | name      | cno  |
+----+-----------+------+
|  1 | jack      |  100 |
|  2 | lucy      |  100 |
|  3 | lilei     |  100 |
|  4 | hanmeimei |  100 |
|  5 | zhangsan  |  101 |
|  6 | lisi      |  101 |
|  7 | wangwu    |  101 |
|  8 | zhaoliu   |  101 |
|  9 | wangliu   | NULL |
+----+-----------+------+
  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值