mysql总结

基础

1. sql, DB, DBMS 分别是什么, 他们之间的关系?

DB:
DataBase (数据库, 实际上在硬盘上以文件的形似存在)
DBMS:
Database Management System(数据库管理系统,常见的有:MySQL, Oracle DB2 Sybase, SqlServer...)
SQL:
结构化查询语言, 是一门标准通用语言, 标准的sql语句适用于所有的数据库产偶

DBMS 负责执行 sql 语句, 通过执行 sql 语句来操作 db 中的数据 DBMS -(执行)-> SQL 语句 -(操作)-> DB

2. 什么是表?

表是数据库的基本组成单元, 所有的数据都以表格的格式组织 一个表包括行和列: 行: 被称为数据/记录 列:被称为字段

  • 每个字段名包含哪些信息: 字段名, 数据类型, 相关约束

3. 学习通用的 SQL 语句,sql 语句分类:

DQL(数据库查询语言): 查询语句, 凡是 select 语句都是 DQL DML(数据操作语言):insert /delete /update 对表当中的数据进行增删改 DDL(数据定义语言):create drop alter , 对表结构的增删改 TCL(事务控制语言):commit 提交事务, rollback 回滚事务 DCL(数据控制语言):grant 授权, revoke 撤销权限等

4.数据库导入数据

  • 第 1 步. 登录 mysql 管理系统 mysql -u 用户名 -p 密码
  • 第 2 步. 查看有哪些数据库 show databases;(这个不是 SQL 语句,属于 mysql 的命令)

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | mysql             |
    | performance_schema |
    | sakila             |
    | sys               |
    | world             |
    +--------------------+

  • 第 3 步. 创建属于我们自己的数据库 create database bjpowernode(数据库名)

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | bjpowernode       |
    | mysql             |
    | performance_schema |
    | sakila             |
    | sys               |
    | world             |
    +--------------------+

  • 第 4 步. 使用数据库 use bjpowernode;
  • 第 5 步. 查看当前使用的数据库中有哪些表? show tables;
  • 第 6 步. 初始化数据 source C:\Users\qin\Desktop\bjpowernode.sql
  • 第 7 步. 初始化之后数据后,查看该数据库

    mysql> show tables;
    +-----------------------+
    | Tables_in_bjpowernode |
    +-----------------------+
    | dept                 |
    | emp                   |
    | salgrade             |
    +-----------------------+
    3 rows in set (0.00 sec)

5. 什么是 sql 脚本?

当一个文件的扩展名是 .sql, 并且该问渐渐编写了大量的 sql 语句, 我们称这样的文件为 sql 脚本

注意:可以直接使用 source 某路径下的 sql 脚本执行某个 sql 文件,进行导入数据到某个表,导入之前一定要先创建表

6. 删除数据库

drop database bjpowernode;

mysql> drop database bjpowernode;
Query OK, 3 rows affected (0.02 sec)

7.查看表结构

desc 表名;

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO   | int(4)     | NO   | PRI | NULL   |       |
| ENAME   | varchar(10) | YES |     | NULL   |       |
| JOB     | varchar(9) | YES |     | NULL   |       |
| MGR     | int(4)     | YES |     | NULL   |       |
| HIREDATE | date       | YES |     | NULL   |       |
| SAL     | double(7,2) | YES |     | NULL   |       |
| COMM     | double(7,2) | YES |     | NULL   |       |
| DEPTNO   | int(2)     | YES |     | NULL   |       |
+----------+-------------+------+-----+---------+-------+

8. 常用命令?

  • 查看当前使用的哪个数据库

    mysql> select database();

  • 查看 mysql 版本

    mysql> select version();

  • \c 命令, 结束一条 sql 语句
  • exit 命令, 退出 mysql

9. 查看创建表时的 Sql 语句:

show create table 表名

<!-- sql 语句查看 -->

sql 语句开始

简单的查询语句(DQL)

语法格式: select 字段名 1,字段名 2,... from 表名;

提示:

  1. 任意一条 sql 语句以 ';' 结尾。
  2. sql 语句不区分大小写。
  • 查询员工的年新?(字段可以参与数学运算)

mysql> select ename, sal * 12 from emp;
+--------+----------+
| ename | sal * 12 |
+--------+----------+
| SMITH |  9600.00 |
| ALLEN | 19200.00 |
| WARD   | 15000.00 |
| JONES | 35700.00 |
| MARTIN | 15000.00 |
| BLAKE | 34200.00 |
| CLARK | 29400.00 |
| SCOTT | 36000.00 |
| KING   | 60000.00 |
| TURNER | 18000.00 |
| ADAMS | 13200.00 |
| JAMES | 11400.00 |
| FORD   | 36000.00 |
| MILLER | 15600.00 |
+--------+----------+

  • 给查询结果的列重名

    select ename, sal * 12 as sal from emp;

mysql> select ename, sal * 12 as sal from emp;

  • 别名中有中文?

mysql> select ename, sal * 12 as '年薪' from emp;

注意:标准的 sql 语句,要求以单引号括起来,不要使用双引号

  • 查询所有字段? <!-- 实际开发中不要这样写 --> 运行时, 会先将 * 转成所有的字段名, 速度比较慢

    select * from emp;

条件查询

语法格式:

select 字段 1,字段 2, .... from 表名 where 条件

执行顺序: 先 from , 然后 where, 最后 select

  1. 查询工资等于 5000 的员工姓名?

select ename from emp where sal=5000;

  1. 查询 SMITH 的工资?

select sal from emp where ename='SMITH';

  1. 找出工资 大于,大于等于,小于, 小于等于 3000 的员工?

select ename from emp where sal>3000;
select ename from emp where sal>=3000;
select ename from emp where sal<3000;
select ename from emp where sal<=3000;

  1. 找出工资不等于 3000 的员工?

select ename from emp where sal<>3000;
select ename from emp where sal!=3000;
​

  1. 找出工资在 1100 和 3000 之间的员工, 包括 1100 和 3000 and between....and(闭区间, 左小右大)

select ename,sal from emp where sal>=1100 and sal<=3000;
select ename,sal from emp where sal between 1100 and 3000;

注意: between....and 不但可以用在数字上,还可以用在字符串上(几乎用不到)

select ename,sal from emp where ename between 'A' and 'B';

  1. 找出哪些人没有津贴? 在数据库当中 NULL 不是一个值, 代表什么都没有, 为空。 空不是一个值, 不能用等号衡量 必须使用 is null 或者 is not nul

-- 找出哪些人津贴为NULl?
select ename,sal,comm from emp where comm is null;
-- 找出哪些人津贴不为NUll?
select ename,sal,comm from emp where comm is not null;
​

  1. 找出哪些人没有津贴(NUll 和 0)

select ename,sal,comm from emp where comm is  null or comm=0;

  1. 找出员工岗位是 MANAGER 或者 SALESMAN 的员工

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

  1. and 和 or 联合使用:找出薪资大于 1000 的员工并且部门编号是 20 或者 30 的部门员工

select ename, sal, deptno from emp where sal>1000 and (deptno=20 or deptno=30);

当运算符的优先级不确定的时候加小括号

  1. in 操作符(等同于 or), 找出工作岗位是 MANAGER 和 SALESMAN 的员工?

select ename, job from emp where job in('MANAGER', 'SALESMAN');
-- 等价于下边的or
select ename, job from emp where job='MANAGER' or job='SALESMAN';

注意:in 后边的两个值表示的是具体的值, 而不是一个区间

  1. not in 不在这几个值当中, 与 in 的用法相反
  2. like 模糊查询 (在模糊查询中, 必须掌握两个符号, 一个是 %, 一个是 _) % 代表多个字符, _代表任意一个字符
  • 找出名字当中含有 o 的?

select ename from emp where ename like '%o%';

  • 找出名字当中第二个字母是 A 的?

select ename from emp where ename like '_A%';

  • 找出名字中有下划线的(使用转义符\)?

select ename from emp where ename like '%\_%';

  • 找出最后一个字母是 T 的

select ename from emp where ename like '%T';

排序(升序, 降序)

语法格式:

  • select 字段 from 表名 order by 某个字段 asc(升序)|desc(降序)
注意: 数据库默认是升序
  1. 按照工资升序排, 找出员工和薪资

select ename, sal from emp order by sal;

  1. 指定是升序还是降序? asc 表示升序, desc 降序。

select ename, sal from emp order by sal asc;
select ename, sal from emp order by sal desc;

  1. 案例:按照工资的降序排列, 当工资相同的时候再按照名字的升序排列? 注意: 按多个字段排序的时候, 谁在前边, 谁的优先级越高,后边的字段排序,只有当前边的无法完成具体的排序时,才会执行后边的排序条件

select ename, sal from emp order by sal asc;
select ename, sal from emp order by sal desc,ename asc;

  1. 也可以按列排序(直接使用列的序号, 不推荐使用)

select ename, sal from emp order by 1;
​

  1. where 混合 排序使用
  • 找出工作岗位是 SALESMAN 的员工,要求按薪资的降序排

select ename, sal from emp where job='SALESMAN' order by sal desc;

执行顺序:from ---- where --- select --- order by

分组函数(多行处理)

  • count 计数
  • sum 求和
  • avg 平均数
  • max 最大值
  • min 最小值 所有的分组函数, 都是对一组数据进行操作的 分组函数一共有 5 个。 它的另一个名字:多行处理函数 多行处理函数的特点: 处理多行, 最终输出的结果是一行
  1. 对某个字段求和

-- 求工资总和?
select sum(sal) from emp;
-- 找出最高工资
select max(sal) from emp;
-- 找出最低工资
select min(sal) from emp;
-- 找出平均工资
select avg(sal) from emp;
-- 找出总人数
select count(*) from emp;

注意:

  1. 分组函数自动忽略字段为 NUll 的数据

问题: 找出工资高于平均工资的员工?

select ename,sal from emp where sal>avg(sal);

<!-- 上述语法报错:ERROR 1111 (HY000): Invalid use of group function -->

思考为啥报错:无效的使用了分组函数 原因是:SQL 语句当中,分组函数不可直接使用在 where 字句中。 为什么? 怎么解释: 因为 group by 是在 where 执行之后才会执行

<!-- 执行顺序是 -->

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

那么怎么处理这个需求: 找出工资高于平均工资的员工?

-- 第1步:找出平均工资
select avg(sal) from emp;
-- 第二步:找出高于平均工资的员工
select ename,sal from emp where sal > 上一步执行的结果

合并上边两步的操作,使用子查询

select ename,sal from emp where sal > (select avg(sal) from emp);

<!-- ======== 分割线 ====== -->

  • count(*) 和 count(某个具体的字段) 他们有什么区别?

    • count(*):是统计的总记录条数
    • count(某个具体的字段):是统计当前这个字段不为NUll的数据总条数
  • 分组函数也能组合使用

select count(*),sum(sal),avg(sal) from emp;

分组查询(group by 和 having)

  • group by: 按照某个字段或者某些字段进行分组。
  • having:是对分组之后的数据进行再次过滤。
案例 1. 找出每个工作岗位的最高薪资

select max(sal),job from emp group by job
+----------+-----------+
| max(sal) | job       |
+----------+-----------+
|  3000.00 | ANALYST   |
|  1300.00 | CLERK     |
|  2975.00 | MANAGER   |
|  5000.00 | PRESIDENT |

 1600.00SALESMAN
案例 2. 找出每个工作岗位的最高薪资,进行排序

mysql> select max(sal),job from emp group by job order by max(sal) desc;
+----------+-----------+
| max(sal) | job       |
+----------+-----------+
|  5000.00 | PRESIDENT |
|  3000.00 | ANALYST   |
|  2975.00 | MANAGER   |
|  1600.00 | SALESMAN |

 1300.00CLERK    

注意:分组函数一般都会和 group by 联合使用,这也是为什么会被称为分组函数的原因, 并且任何分组函数(min,avg, max, sum, count)都是再 group by 语句执行结束之后才会执行的 当一条 sql 语句中没有 group by 的话, 整张表的数据会自成一组

注意: select ename,max(sal),job group by job; select 和 group by 一块使用时, 搜索的字段必须是 group by 分组后跟的字段 和 分组函数,不然上述语法再 Oracle 会报错

规则:当一条查询语句中有 group by的话, select后面只能跟分组函数和参与分组的字段

  1. 案例 3:每个岗位的平均工资

mysql> select job,avg(sal) from emp group by job;
+-----------+-------------+
| job       | avg(sal)   |
+-----------+-------------+
| ANALYST   | 3000.000000 |
| CLERK     | 1037.500000 |
| MANAGER   | 2758.333333 |
| PRESIDENT | 5000.000000 |

SALESMAN1400.000000
  1. 多个字段能不能联合起来一块分组?
案例4:找出每个部门不同岗位的最高薪资。

mysql> select deptno,job, avg(sal) from emp group by deptno,job;
+--------+-----------+-------------+
| deptno | job       | avg(sal)   |
+--------+-----------+-------------+
|     10 | CLERK     | 1300.000000 |
|     10 | MANAGER   | 2450.000000 |
|     10 | PRESIDENT | 5000.000000 |
|     20 | ANALYST   | 3000.000000 |
|     20 | CLERK     |  950.000000 |
|     20 | MANAGER   | 2975.000000 |
|     30 | CLERK     |  950.000000 |
|     30 | MANAGER   | 2850.000000 |

    30SALESMAN1400.000000
案例5找出每个部门的最高薪资, 要求显示薪资大于 2900 的数据

-- 第一步:找出mei个部门的最高薪资
mysql> select max(sal),deptno from emp group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  5000.00 |     10 |
|  3000.00 |     20 |
|  2850.00 |     30 |
+----------+--------+
-- 第二步:找出薪资大于2900的(以下sql执行效率低)
mysql> select max(sal),deptno from emp group by deptno having max(sal)>2900 ;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  5000.00 |     10 |
|  3000.00 |     20 |
+----------+--------+
-- 解决第二步的效率低的sql,使用where先过滤掉,能用where解决的,不要用having
mysql> select max(sal),deptno from emp where sal>2900 group by deptno;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  5000.00 |     10 |

 3000.00    20
案例6找出每个部门的平均薪资, 要求显示薪资大于 2000 的数据

mysql> select max(sal),deptno from emp group by deptno having avg(sal)>2000;
+----------+--------+
| max(sal) | deptno |
+----------+--------+
|  5000.00 |     10 |

 3000.00    20

总结一个完整的 DQL 语句怎么写?

select
...
from
...
where
...
group by
....
having
....
order by
....

distinct去重

    1. 关于查询结果集的去重?

-- distinct去除重复记录,可以跟多个字段
select distinct job from emp;

  1. 统计岗位的数量

-- distinct去除重复记录,可以跟多个字段
select count(distinct job) from emp;
+---------------------+
| count(distinct job) |
+---------------------+

                  5

连接查询

<!-- 了解 -->

1 什么是连接查询?

实际开发中,大部分的情况下都不是从但表中查询数据,一般都是多张表联合查询取出最终的结果 在实际开发中, 一般一个业务都会对应多张表, 比如学生和班级,起码两张表。 也就是关系型数据库表

2 表连接方式划分:

内连接:
等值连接
等值连接
自连接
外连接
左外连接
右外连接

3 在表的连接查询方面有一种现象称为:笛卡尔积

笛卡尔积现象:当两张表进行连接查询的时候,没有任何条件进行限制,最后的查询结果就是两张表的乘积 案例:找出每一个员工的部门名称,要求显示员工名和部门 <!-- 表别名 --> 关于表的别名 表别名有什么好处? 第一: 执行效率高 第二:可读性好

select e.ename, d.dname from emp e,dept d;

4 怎么避免笛卡尔积现象? 当然是家条件进行过滤。

思考: 避免笛卡尔积现象, 会减少记录的匹配次数吗? 不会, 次数还是 56 次。 只不过显示的是有效记录。

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

mysql> select e.ename,d.dname from emp e,dept d where e.deptno=d.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   |

MILLERACCOUNTING

5 内连接之等值连接

最大特点是:条件是等量关系。

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

// inner可以省略的,带着inner目的是可读性好一些。

select 
    e.ename,d.dname
from
    emp e
\[inner\] join
    dept d
on
    e.deptno = d.deptno;

语法:
    ...
        A
    join
        B
    on
        连接条件
    where
        ...
        
+--------+------------+
| ename  | dname      |
+--------+------------+
| CLARK  | ACCOUNTING |
| KING   | ACCOUNTING |
| MILLER | ACCOUNTING |
| SMITH  | RESEARCH   |
| JONES  | RESEARCH   |
| SCOTT  | RESEARCH   |
| ADAMS  | RESEARCH |
| FORD   | RESEARCH   |
| ALLEN  | SALES          |
| WARD   | SALES         |
| MARTIN | SALES      |
| BLAKE  | SALES        |
| TURNER | SALES      |
| JAMES  | SALES      |
+--------+------------+

以上写法是SQL99,SQL99语法结构更清晰一些:表的连接条件和后来的where条件分离了。

6、内连接之非等值连接

最大的特点是:连接条件中的关系是非等量关系。

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

select

e.ename,e.sal,s.grade

from

emp e

join

salgrade s

on

e.sal between s.losal and s.hisal;
enamesalgrade
SMITH800.001
ALLEN1600.003
WARD1250.002
JONES2975.004
MARTIN1250.002
BLAKE2850.004
CLARK2450.004
SCOTT3000.004
KING5000.005
TURNER1500.003
ADAMS1100.001
JAMES950.001
FORD3000.004
MILLER1300.002

7 自连接

最大的特点是:一张表看做两张表,自己连接自己。

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

分析:下表是员工表

empnoenamemgr
7369SMITH7902
7499ALLEN7698
7521WARD7698
7566JONES7839
7654MARTIN7698
7698BLAKE7839
7782CLARK7839
7788SCOTT7566
7839KINGNULL
7844TURNER7698
7876ADAMS7788
7900JAMES7698
7902FORD7566
7934MILLER7782

mgr是员工的领导,通过工号表示,我们要找到工号对应的名字,这时候一张表看做两张表。

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

select

a.ename as '员工名',b.ename as '领导名'

from

emp a

inner join

emp b

on

a.mgr = b.empno;
员工名领导名
SMITHFORD
ALLENBLAKE
WARDBLAKE
JONESKING
MARTINBLAKE
BLAKEKING
CLARKKING
SCOTTJONES
TURNERBLAKE
ADAMSSCOTT
JAMESBLAKE
FORDJONES
MILLERCLARK

8 外连接

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

什么是外连接,和内连接有什么区别?

内连接:

假设A和B表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录查询出来,这就是内连接。 ​ AB两张表没有主副之分,两张表是平等的。

外连接:

假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表,主要查询主表中的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。

外连接的分类?

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

案例:找出每个员工的上级领导?(所有员工必须全部查询出来)

分析:对比上题,上题也是查询员工的领导,但是只是查询了13条记录,这是为什么呢?因为king的就是最高领导,上级领导为null,但是我们不想这样,King作为一个员工,我们也想让他显示出来,这是就需要用到外连接。

外连接:(左外连接/左连接)
select

a.ename '员工', b.ename '领导'

from

emp a

left join

emp b

on

a.mgr = b.empno;
员工领导
SMITHFORD
ALLENBLAKE
WARDBLAKE
JONESKING
MARTINBLAKE
BLAKEKING
CLARKKING
SCOTTJONES
KINGNULL
TURNERBLAKE
ADAMSSCOTT
JAMESBLAKE
FORDJONES
MILLERCLARK

我们注意到有14条记录,并且king的领导为null

9 三张表怎么连接查询?

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

EMP e
empnoenamesaldeptno
7369SMITH800.0020
7499ALLEN1600.0030
7521WARD1250.0030
7566JONES2975.0020
7654MARTIN1250.0030
7698BLAKE2850.0030
7782CLARK2450.0010
7788SCOTT3000.0020
7839KING5000.0010
7844TURNER1500.0030
7876ADAMS1100.0020
7900JAMES950.0030
7902FORD3000.0020
7934MILLER1300.0010
DEPT d
DEPTNODNAMELOC
10ACCOUNTINGNEW YORK
20RESEARCHDALLAS
30SALESCHICAGO
40OPERATIONSBOSTON
SALGRADE s
GRADELOSALHISAL
17001200
212011400
314012000
420013000
530019999

注意,解释一下: .... 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 |
+--------+------------+-------+
| SMITH  | RESEARCH   |     1 |
| ALLEN  | SALES      |     3 |
| WARD   | SALES      |     2 |
| JONES  | RESEARCH   |     4 |
| MARTIN | SALES      |     2 |
| BLAKE  | SALES      |     4 |
| CLARK  | ACCOUNTING |     4 |
| SCOTT  | RESEARCH   |     4 |
| KING   | ACCOUNTING |     5 |
| TURNER | SALES      |     3 |
| ADAMS  | RESEARCH   |     1 |
| JAMES  | SALES      |     1 |
| FORD   | RESEARCH   |     4 |
| MILLER | ACCOUNTING |     2 |
+--------+------------+-------+

子查询

1、什么是子查询?子查询都可以出现在哪里?

select语句当中嵌套select语句,被嵌套的select语句是子查询。 子查询可以出现在哪里?

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

2、where子句中使用子查询

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

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

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

3、from后面嵌套子查询

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

第一步:找出每个部门平均薪水(按照部门编号分组,求sal的平均值)

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

deptnoavgsal
102916.666667
202175.000000
301566.666667

第二步:将以上的查询结果当做临时表t,让t表和salgrade s表连接,条件是: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;
deptnoavgsalgrade
301566.6666673
102916.6666674
202175.0000004

4、在select后面嵌套子查询。

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

select

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

from

emp e;
enamedname
SMITHRESEARCH
ALLENSALES
WARDSALES
JONESRESEARCH
MARTINSALES
BLAKESALES
CLARKACCOUNTING
SCOTTRESEARCH
KINGACCOUNTING
TURNERSALES
ADAMSRESEARCH
JAMESSALES
FORDRESEARCH
MILLERACCOUNTING

这题有一个更好的解法:

select

e.ename,d.dname

from

emp e

join

dept d

on

e.deptno = d.deptno;

union (可以将查询结果集相加)

案例:找出工作岗位是SALESMAN和MANAGER的员工?

第一种:

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

第二种:

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';

enamejob
JONESMANAGER
BLAKEMANAGER
CLARKMANAGER
ALLENSALESMAN
WARDSALESMAN
MARTINSALESMAN
TURNERSALESMAN

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

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

这里要提的是:如果查询的字段长度一样,那么他会分成两列,如果不一样,会合成一列,如上图所示。

mysql> select ename,sal from emp

-> union
-> select dname from dept;

ERROR 1222 (21000): The used SELECT statements have a different number of columns

若union合成的两个字段数量不一样,则直接报错。

limit (用于分页)

(重点中的重点,以后分页查询全靠它了)

5.1、limit的作用

取结果集中的部分数据

5.2、语法机制: limit startIndex, length startIndex表示起始位置,从0开始,0表示第一条数据。 length表示取几个

案例:取出工资前5名的员工(思路:降序取前5个)

select ename,sal from emp order by sal desc;
取前5个:
    select ename,sal from emp order by sal desc limit 0, 5;
    select ename,sal from emp order by sal desc limit 5;

5.3、limit是sql语句最后执行的一个环节:

select 5

...

from 1

...        

where 2

...    

group by 3

...

having 4

...

order by 6

...

limit 7

...;

5.4、案例:找出工资排名在第4到第9名的员工?

select ename,sal from emp order by sal desc limit 3,6;
+--------+---------+
| ename  | sal     |
+--------+---------+
| JONES  | 2975.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| ALLEN  | 1600.00 |
| TURNER | 1500.00 |
| MILLER | 1300.00 |
+--------+---------+

5.6、通用的标准分页sql?

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

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

pageSize是什么?是每页显示多少条记录
pageNo是什么?显示第几页

表的创建

1、建表语句的语法格式:

create table 表名(

        字段名1 数据类型,
        字段名2 数据类型,
        字段名3 数据类型,
        ....
    );

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)
    ......

char和varchar怎么选择?

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

案例:创建学生表: 学生信息包括: 学号、姓名、性别、班级编号、生日 学号:bigint 姓名:varchar 性别:char 班级编号:int 生日:char

create table t_student(

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

表的增删改

insert语句插入数据:

语法格式:

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

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

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

字段可以省略不写,但是后面的value对数量和顺序都有要求。

insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23');

一次插入多行数据

insert into t_student

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

表的复制

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

修改数据:update

语法格式:

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

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

案例:将部门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   |
+--------+------------+----------+

删除数据

语法格式:

delete from 表名 where 条件;

注意:没有条件全部删除。

删除所有记录?

delete from dept1;

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

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

删表

drop table 表名; // 这个通用。

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

约束

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

在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的 ​ 合法性、有效性、完整性。 ​ 常见的约束有哪些呢? ​ 非空约束(not null):约束的字段不能为NULL ​ 唯一约束(unique):约束的字段不能重复 ​ 主键约束(primary key):约束的字段既不能为NULL,也不能重复(简称PK) ​ 外键约束(foreign key):...(简称FK) ​ 检查约束(check):注意Oracle数据库有check约束,但是mysql没有,目前mysql不支持该约束。

2、非空约束 (not null)

create table t_user(

    id int,
    username varchar(255) not null,
    password varchar(255)
);

3、唯一性约束(unique)

唯一约束修饰的字段具有唯一性,不能重复。但可以为NULL。

案例:给某一列添加unique

    drop table if exists t\_user;
    create table t\_user(
        id int,
        username varchar(255) unique  // 列级约束
    );
    insert into t\_user values(1,'zhangsan');
    insert into t\_user values(2,'zhangsan');
    ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'

案例:给两个列或者多个列添加unique

    drop table if exists t\_user;
    create table t\_user(
        id int, 
        usercode varchar(255),
        username varchar(255),
        unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】
    );

这样的话就是两个字段合起来不唯一。

注意:not null约束只有列级约束。没有表级约束。

4、主键约束(primary key)

create table t_user(

        id int primary key,  // 列级约束
        username varchar(255),
        email varchar(255)
    );
    insert into t\_user(id,username,email) values(1,'zs','zs@123.com');
    insert into t\_user(id,username,email) values(2,'ls','ls@123.com');
    insert into t\_user(id,username,email) values(3,'ww','ww@123.com');
    select \* from t\_user;
    +----+----------+------------+
    | id | username | email      |
    +----+----------+------------+
    |  1 | zs       | zs@123.com |
    |  2 | ls       | ls@123.com |
    |  3 | ww       | ww@123.com |
    +----+----------+------------+

主键相关的术语

主键约束 : primary key ​ 主键字段 : id字段添加primary key之后,id叫做主键字段 ​ 主键值 : id字段中的每一个值都是主键值。

主键有什么作用?

表的设计三范式中有要求,第一范式就要求任何一张表都应该有主键。

主键值是这行记录在这张表当中的唯一标识

主键的分类

根据主键字段的字段数量来划分

单一主键(推荐的,常用的) ​ 复合主键(多个字段联合起来添加一个主键约束)(复合主键不建议使用,因为复合主键违背三范式。)

根据主键性质来划分

自然主键:主键值最好就是一个和业务没有任何关系的自然数。(这种方式是推荐的) ​ 业务主键:主键值和系统的业务挂钩,例如:拿着银行卡的卡号做主键,拿着身份证号码作为主键。(不推荐用) 最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候,主键值可能也需要随着发生变化,但有的时候没有办法变化,因为变化可能会导致主键值重复。

主键自增

mysql提供主键值自增:(非常重要。)

    drop table if exists t\_user;
    create table t\_user(
        id int primary key auto\_increment, // id字段自动维护一个自增的数字,从1开始,以1递增。
        username varchar(255)
    );

外键约束(foreign key)

写法格式

foreign key(字段名) references 父表(字段名)

外键约束的相关术语

外键约束: foreign key ​ 外键字段:添加有外键约束的字段 ​ 外键值:外键字段中的每一个值。

业务背景: 请设计数据库表,用来维护学生和班级的信息? 第一种方案:一张表存储所有数据

no(pk)

name

classno

classname

1

zs1

101

北京大兴区经济技术开发区亦庄二中高三1班

2

zs2

101

北京大兴区经济技术开发区亦庄二中高三1班

3

zs3

102

北京大兴区经济技术开发区亦庄二中高三2班

4

zs4

102

北京大兴区经济技术开发区亦庄二中高三2班

缺点:冗余。【不推荐】

第二种方案:两张表(班级表和学生表) t_class 班级表 cno(pk) cname


101 北京大兴区经济技术开发区亦庄二中高三1班 ​ 102 北京大兴区经济技术开发区亦庄二中高三2班

t_student 学生表 ​ sno(pk) sname classno(该字段添加外键约束fk)


1 zs1 101 ​ 2 zs2 101 ​ 3 zs3 102 ​ 4 zs4 102 ​ 5 zs5 102

顺序要求: 删除数据的时候,先删除子表,再删除父表。 添加数据的时候,先添加父表,在添加子表。 创建表的时候,先创建父表,再创建子表。 删除表的时候,先删除子表,在删除父表。

drop table if exists t_student;

    drop table if exists t\_class;

    create table t\_class(
        cno int,
        cname varchar(255),
        primary key(cno)
    );

    create table t\_student(
        sno int,
        sname varchar(255),
        classno int,
        primary key(sno),
        foreign key(classno) references t\_class(cno)
    );

外键可以为NULL,被引用的字段不一定是主键,但至少具有unique约束。

存储引擎

<!--了解-->

MyISAM

MyISAM这种存储引擎不支持事务。 ​ MyISAM是mysql最常用的存储引擎,但是这种引擎不是默认的。 ​ MyISAM采用三个文件组织一张表: ​ xxx.frm(存储格式的文件) ​ xxx.MYD(存储表中数据的文件) ​ xxx.MYI(存储表中索引的文件) ​ 优点:可被压缩,节省存储空间。并且可以转换为只读表,提高检索效率。 ​ 缺点:不支持事务。

InnoDB

优点:支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。

表的结构存储在xxx.frm文件中 ​ 数据存储在tablespace这样的表空间中(逻辑概念),无法被压缩,无法转换成只读。 ​ 这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制。 ​ InnoDB支持级联删除和级联更新。

事务(Transaction)

什么是事务?

一个事务是一个完整的业务逻辑单元,不可再分。

要想保证几条DML语句同时成功或者同时失败,那么就需要使用数据库的“事务机制”。

和事务相关的语句

DML语句。(insert delete update)

为什么?因为它们这三个语句都是和数据库表当中的“数据”相关的。 事务的存在是为了保证数据的完整性,安全性。

事务的特性

事务包括四大特性:ACID A: 原子性:事务是最小的工作单元,不可再分。 C: 一致性:事务必须保证多条DML语句同时成功或者同时失败。 I:隔离性:事务A与事务B之间具有隔离。 D:持久性:持久性说的是最终数据必须持久化到硬盘文件中,事务才算成功的结束。

关于事务之间的隔离性

事务隔离性存在隔离级别,理论上隔离级别包括4个: ​ 第一级别:读未提交(read uncommitted) ​ 对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。 ​ 读未提交存在脏读(Dirty Read)现象:表示读到了脏的数据。 ​ 第二级别:读已提交(read committed) ​ 对方事务提交之后的数据我方可以读取到。 ​ 这种隔离级别解决了: 脏读现象没有了。 ​ 读已提交存在的问题是:不可重复读。 ​ 第三级别:可重复读(repeatable read) ​ 这种隔离级别解决了:不可重复读问题。 ​ 这种隔离级别存在的问题是:读取到的数据是幻象。 ​ 第四级别:序列化读/串行化读(serializable) ​ 解决了所有问题。 ​ 效率低。需要事务排队。

oracle数据库默认的隔离级别是:读已提交。 ​ mysql数据库默认的隔离级别是:可重复读。

索引

什么是索引

缩写扫描的范围,提高检索效率。

注意:索引虽然可以提高检索效率,但是不能随意的添加索引,因为索引也是数据库当中的对象,也需要数据库不断的维护。是有维护成本的。比如,表中的数据经常被修改这样就不适合添加索引,因为数据一旦修改,索引需要重新排序,进行维护。

怎么创建索引对象?怎么删除索引对象?

    创建索引对象:
        create index 索引名称 on 表名(字段名);
    删除索引对象:
        drop index 索引名称 on 表名;

什么时候考虑给字段添加索引?

  • 数据量庞大。(根据客户的需求,根据线上的环境)
  • 该字段很少的DML操作。(因为字段进行修改操作,索引也需要维护)
  • 该字段经常出现在where子句中。(经常根据哪个字段查询)

    主键和具有unique约束的字段自动会添加索引

索引的实现原理

通过B Tree缩小扫描范围,底层索引进行了排序,分区,索引会携带数据在表中的“物理地址”, 最终通过索引检索到数据之后,获取到关联的物理地址,通过物理地址定位表中的数据,效率是最高的。 select ename from emp where ename = 'SMITH'; 通过索引转换为: select ename from emp where 物理地址 = 0x3;

模糊查询的时候,第一个通配符使用的是%,这个时候索引是失效的。

比如:

select ename from emp where ename like '%A%';

数据库设计三范式

重点

第一范式:任何一张表都应该有主键,并且每一个字段原子性不可再分。

第二范式:建立在第一范式的基础之上,所有非主键字段完全依赖主键,不能产生部分依赖。

第三范式:建立在第二范式的基础之上,所有非主键字段直接依赖主键,不能产生传递依赖。

多对多?三张表,关系表两个外键。

一对多?两张表,多的表加外键。

评论

暂无评论