MySQL基本操作

发表于 更新于 分类于 ， Waline： 阅读次数： 本文字数： 24k 阅读时长 ≈ 22 分钟

内容摘要：

  • 使用cmd操作
  • 认识和创建数据库表
  • 数据库表列类型
  • SQL的五大部分
  • 表的完整性约束
  • 数据库对象

使用cmd操作

登录

1
mysql -hlocalhost -uroot -p

-h：主机

-u：用户名

-p：密码，这里输入密码是会被看见的，在后续输入

展示数据库目录

1
show databases

使用某个数据库

1
use myDB

展示数据库中的所有表

1
show tables;

退出的三种方式

1
2
3
quit;
exit;
\q;

并发模拟

可以同时使用Navicat和cmd，来实现对数据库的并发操作

数据库表

认识数据库表

表 Table

数据库将复杂的数据结构用较为简单的二维表来表示

二维表有行和列

行：记录

列：字段

创建数据库表

注释

1
2
3
4
5
6
7
8
## 单行注释

/*
多行注释
多行注释
*/

-- 也是注释

建表语句

1
2
3
4
5
6
7
8
9
create table t_student(
        student_no int(6), -- 6显示长度 
        student_name varchar(5), -- 5个字符
        sex char(1),
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);

类型很多但是大差不差

int(6) 指显示宽度为6，超过后系统会自动增大宽度

查看表的结构

1
desc t_student; ## desc describe

查看建表语句

1
show create table t_student;

查看表中的数据

1
select * from t_student;

数据库表列类型

整数类型

MySQL支持选择在该关键字后面的括号内指定整数值的显示宽度

显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示

主键自增：不使用序列，通过auto_increment，要求是整数类型

浮点数类型

浮点数类型的宽度不会自动扩充，与整数类型不一样

score double(4,1)5

说明总宽度为4位，小数部分为1位，并且不会自动扩充

字符串类型

char和varchar类型相似，均用于存于较短的字符串

区别：

char类型长度固定

varchar类型的长度可变

因为varchar类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时，推荐使用varchar类型，这样可以大大节约磁盘空间、提高存储效率

char和varchar类型表示的是字符个数，而不是字节个数

日期和时间类型

timestamp类型的数据指定方式与datetime基本相同

区别

  1. 数据的取值范围不同，timestamp取值范围更小
  2. 如果对timestamp没有明确赋值，或是被赋予了null值，MySQL会自动将该字段赋值为系统当前的日期与时间
  3. timestamp类型还可以使用current_timestamp来获取系统当前时间
  4. timestamp类型有一个很大特定，时间根据时区来显示

SQL的五大部分

DQL 数据查询语言

主要用于数据的查询，基本结构是使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据

准备四张表，进行学习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
create table DEPT(  
  DEPTNO int(2) not null,  
  DNAME  VARCHAR(14),  
  LOC    VARCHAR(13)  
);  
alter table DEPT  
  add constraint PK_DEPT primary key (DEPTNO); 
        
create table EMP  
(  
  EMPNO    int(4) primary key,  
  ENAME    VARCHAR(10),  
  JOB      VARCHAR(9),  
  MGR      int(4),  
  HIREDATE DATE,  
  SAL      double(7,2),  
  COMM     double(7,2),  
  DEPTNO   int(2)  
);  
alter table EMP  
  add constraint FK_DEPTNO foreign key (DEPTNO)  
  references DEPT (DEPTNO);  
        
create table SALGRADE  
(  
  GRADE int primary key,  
  LOSAL double(7,2),  
  HISAL double(7,2)  
);  
create table BONUS  
(  
  ENAME VARCHAR(10),  
  JOB   VARCHAR(9),  
  SAL   double(7,2),  
  COMM  double(7,2)  
);  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (10, 'ACCOUNTING', 'NEW YORK');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (20, 'RESEARCH', 'DALLAS');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (30, 'SALES', 'CHICAGO');  
insert into DEPT (DEPTNO, DNAME, LOC)  
values (40, 'OPERATIONS', 'BOSTON');  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);  
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)  
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (1, 700, 1200);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (2, 1201, 1400);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (3, 1401, 2000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (4, 2001, 3000);  
insert into SALGRADE (GRADE, LOSAL, HISAL)  
values (5, 3001, 9999);

select * from dept;

select * from emp;

select * from salgrade;

select * from bonus;

四张表

  • 部门表（dept）
  • 员工表（emp）
  • 薪资等级表（salgrade）
  • 奖金表（bonus）

DQL依靠上面的四张表介绍

单表查询

内容摘要：

  1. select语句：

    select column，group_function(column)

    from table

    [where condition]

    [group by group_by_expression]

    [having group_condition]

    [order by column]

    顺序固定，不要改变

  2. select语句的执行顺序

    from--where-- group by-- select -- having -- order by

    从哪个表查，where过滤信息，按需分组，在筛选目标列，having二次过滤，排序

基础查询
显示所有数据
1
select * from emp;

* 代表所有数据

显示部分列
1
select empno,ename,sal from emp;

显示需要的列

显示部分行：where子句
1
select * from emp where sal > 2000;

相当于加设条件句，筛除不合要求的行

显示部分列，部分行
1
select empno,ename,job,mgr from emp where sal > 1000;

混合使用即可

一些操作
起别名

对于字段的英文名字，起中文名字方便看

1
2
3
4
select empno 员工编号,ename 姓名,sal 工资 from emp;
select empno as 员工编号,ename as 姓名,sal as 工资 from emp;
select empno as '员工编号',ename as "姓名",sal as 工资 from emp;
select empno as '员 工编号',ename as " 姓名",sal as 工资 from emp;

as 可以省略，是alias 别名的缩写

别名中的引号可以省略，对结果没有影响

但是，如果需要在别名里添加特殊字符，则引号不可省略

算数运算符
1
2
select empno,ename,sal,sal+1000 涨薪,deptno from emp where sal < 2500;
select empno,ename,sal,comm,sal+comm from emp;

原数据不会改变

如果相加的两数中一个为null，那么结果也为空

去重操作
1
2
3
select distinct job from emp;
select distinct job,deptno from emp;

多列去重是对他们的组合进行去重

排序
1
2
3
4
select * from emp order by sal;
select * from emp order by sal asc;
select * from emp order by sal desc;
select * from emp order by sal asc, deptno desc;

默认情况下按照升序排列

asc 升序

desc 降序

不同列的排序方式可以单独设置

where子句
where 子句 + 关系运算符
1
2
3
4
5
6
7
select * from emp where deptno = 10;
select * from emp where deptno < 10;
select * from emp where deptno > 10;
select * from emp where deptno <= 10;
select * from emp where deptno >= 10;
select * from emp where deptno <> 10;
select * from emp where deptno != 10;

where子句：将过滤条件放在where子句的后面，可以筛选/过滤出我们想要的符合条件的数据

1
2
3
4
select * from emp where job = 'CLERK';
select * from emp where job = 'clerk';
select * from emp where binary job = 'CLERK';
select * from emp where hiredate < '1981-12-25';

关系运算符不止可以用于数字，对于字符，日期有同样效果

默认情况下不区分大小写

添加 binary 关键字区分大小写

and 和 && 两种表示
1
2
select * from emp where sal > 1500 and sal < 3000;
select * from emp where sal > 1500 && sal < 3000;
筛选完后，排序，语句加在后面即可
1
select * from emp where sal > 1500 and sal < 3000 order by sal;
between 范围关键字
1
select * from emp where sal between 1500 and 3000;

between A and B = [A,B]

or 和 || 两种表示
1
2
select * from emp where deptno = 10 or deptno = 20;
select * from emp where deptno = 10 || deptno = 20;
另一种集合表示
1
2
select * from emp where deptno in (10,20);
select * from emp where job in ('MANAGER','CLERK','ANALYST');
模糊查询

例如查询名字中带A的员工

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

模糊查询，使用 like

% 代表任意多的字符 0，1，2，...

查询名字中第二个字母为A的员工

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

_ 代表一个字符

关于 null 的判断
1
2
select * from emp where comm is null;
select * from emp where comm is not null;

不能用 =

is null 是空

is not null 不是空

小括号的使用
1
2
3
select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500;
select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500); 
select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500;

改变优先级

函数

通过封装一些特定功能，需要时直接使用，实现对应功能

作用：提高select的能力

函数并不会改变数据，只是对真实数据进行加工，展示新的结果

单行函数

对每一条记录进行计算，得到结果并返回

改变每一条数据，每一条数据对应一条结果

常用的单行函数主要包括字符串函数，数值函数，日期与时间函数，流程函数以及其他函数

  1. 字符串函数

    • length()

      1
      select ename,length(ename) from emp;

      获取字符串长度

    • substring()

      1
      select ename,substring(ename,2,3) from emp;

      截取子串

      三个参数依次：处理对象，开始位置，截取长度（ename，从第二个字符开始，截取长度为3的子串）（下标从1开始）

      如果指定截取长度超过处理对象长度，就截取到对象末尾

  2. 数值函数

    1
    select abs(-5) 绝对值,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.74) 四舍五入,10/3 '10除以3', 10%3 '10%3',mod(10,3) 'mod(10,3)' from dual;

    from dual 取处理对象为 dual，是默认的伪表，这样不会打印特别多行

    abs()：绝对值

    ceil()：向上取整

    floor()：向下取整

    round()：四舍五入

    /：除法

    mod()或%：取模

    当然改名字也是同样可以的

  3. 日期与时间函数

    1
    select curdate(),curtime() from dual;

    curdate()：年月日

    curtime()：时分秒

    1
    select now(),sysdate(),sleep(3),now(),sysdate() from dual;

    同时表示年月日时分秒

    now()：当前时间

    sysdate()：函数执行时的时间

    虽然这两个可以表示年月日时分秒，但是插入数据时还是参照表的结构

  4. 流程函数

    if 相关

    1
    2
    3
    select empno,ename,sal,if(sal >= 2500,'高薪','底薪') '薪资等级' from emp;
    select empno,ename,sal,comm,sal+ifnull(comm,0) '工资' from emp;
    select nullif(1,1),nullif(1,2) from dual;

    • if(条件，满足执行，不满足执行)，很类似于三位运算符X ？A ：B

    • ifnull(对象，满足执行)，不为空就执行原数据

    • nullif(A，B)

      A = B ：null

      A != B ：返回第一个值

    case 相关

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    select empno,ename,job,
    case job
    	when 'CLERK' then '店员'
    	when 'SALESMAN' then '销售'
    	when 'MANAGER' then '经理'
    	else '其他'
    end 岗位,
    sal
    from emp;

    select empno,ename,sal,
    case
    	when sal<=1000 then 'A'
    	when sal<=2000 then 'B'
    	when sal<=3000 then 'C'
    	else 'D'
    end 薪资等级,
    deptno
    from emp;

    太像VHDL了，格式记住，就相当于把if换成了when

    同样，可以改名字，可以加别的操作

  5. JSON函数

    json不会，所以先欠着

  6. 其他函数

    1
    select database(),user(),version() from dual;

    database()：当前数据库名

    user()：当前登录的用户名

    version()：当前MySQL版本号

多行函数（分组函数）

对一组数据进行计算，返回一个结果

多条数据展示一个结果

除了多行函数（max，min，count，sum，avg），都是单行函数

1
select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp;

max()：最大

min()：最小

count()：计数

sum()：求和

avg()：求平均值

自动忽略null值

sum，avg对字符一类没有意义

分组
group by

如果我想要查员工的平均工资可以使用avg（sal）实现，但是实际情况下，我们更倾向于分部门，看员工的平均工资

可以先进行分组，在计算平均工资

1
2
select deptno,avg(sal) from emp group by deptno;
select deptno,avg(sal) from emp group by deptno order by deptno desc;

字段和多行函数不可同时使用，除非这个字段属于分组

同样，分组后也可以进行改名，排序，改变大小写

1
2
select job,avg(sal) from emp group by job;
select job,lower(job) 职位,avg(sal) 平均工资 from emp group by job;
分组后筛选 having

在计算完平均工资后，想要看看平均工资高于2000的部门

1
2
3
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000 order by 平均工资 desc;

having 加上条件即可

同样可以进行改名，改名也可用于约束条件

也可以排序

所以我们就有两种办法筛除掉管理层的平均工资

1
2
select job,avg(sal) 平均工资 from emp where job != 'MANAGER' group by job;
select job,avg(sal) 平均工资 from emp group by job having job != 'MANAGER';

一种使用where，先筛去管理层，再分组计算平均工资

一种使用having，先计算平均工资，再筛去管理层

所以

where 在分组前过滤，having 在分组后过滤

多表查询

在多张数据库表中查询数据，使用连接查询来查询多张表中满足要求的数据记录。

join关键字

提供的连接查询类型：cross、natural、using、on

语法
交叉连接
1
2
3
select *
from emp
cross join dept;

MySQL可以省略cross

cross join 交叉连接：会出现笛卡尔乘积，没有实际意义，有理论意义

自然连接
1
2
3
select *
from emp
natural join dept;

natural join 自然连接：自动匹配所有的同名列，同名列只展示一次，简单

缺点：查询字段的时候，没有指定字段所属的数据库表，效率低

解决：指定表名

1
2
3
select emp.empno,emp.ename,emp.sal,dept.dname,dept.loc
from emp
natural join dept;

缺点：表名过长有麻烦

解决：改名字

1
2
3
select e.empno,e.ename,e.sal,d.dname,d.loc
from emp e
natural join dept d;

缺点：因为自然连接会自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列

解决：内连接——using子句

using 子句
1
2
3
4
select * 
from emp e
inner join dept d
using(deptno);

inner 可以不写

属于内连接，不能写natural join

缺点：using 关联的字段，必须是同名字段

解决：内连接——on子句

1
2
3
4
select * 
from emp e
inner join dept d
on (e.deptno = d.deptno);

多表连接查询

  • cross join 交叉连接
  • natural join 自然连接
  • 内连接——using子句
  • 内连接——on子句（最常用）

条件：

  1. 筛选条件 where having
  2. 连接条件 on using natural

SQL99语法：筛选条件和连接条件是分开的

内连接会导致冲突信息丢失，出现在查询结果中不显示的问题

外连接：除了显示匹配的数据之外，还可以显示不匹配的数据

1
2
3
4
select * 
from emp e
left outer join dept d
on (e.deptno = d.deptno);

left outer join 左外连接：左面的表的信息，即使不匹配也可以查看出效果

同理

1
2
3
4
select * 
from emp e
right outer join dept d
on (e.deptno = d.deptno);

right outer join 右外连接：右面的表的信息，即使不匹配也可以查看出效果

1
2
3
4
select * 
from emp e
full outer join dept d
on (e.deptno = d.deptno);

MySQL不支持full outer join（全外连接）

1
2
3
4
5
6
7
8
9
select * 
from emp e
left outer join dept d
on (e.deptno = d.deptno);
union
select * 
from emp e
right outer join dept d
on (e.deptno = d.deptno);

将左外和右外取并集

union 并集 去重 效率低

1
2
3
4
5
6
7
8
9
select * 
from emp e
left outer join dept d
on (e.deptno = d.deptno);
union all
select * 
from emp e
right outer join dept d
on (e.deptno = d.deptno);

union all 并集 不去重 效率高

outer 可以省略

MySQL对集合操作支持较弱，只支持并集，不支持交集，差集

三表连接查询
1
2
3
4
5
6
select e.ename,e.sal,e.empno,e.deptno,d.dname,s.*
from emp e
right outer join dept d
on e.deptno = d.deptno
inner join salgrade s
on e.sal between s.losal and s.hisal

多表连接的要点

相当于两张表连接后成为一张表，然后再连接另一张表

从而实现多张表的连接

外连接和内连接灵活使用实现不同功能

自连接查询
1
2
3
4
select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 领导姓名 
from emp e1
left outer join emp e2
on e1.mgr = e2.empno;

一张表和自己连接查询

把一张表想象成两张表

92语法的多表查询

92-cross join

1
2
select e.empno,e.ename,e.sal,e.deptno,d.dname
from emp e,dept d

92-natural join

1
2
3
select e.empno,e.ename,e.sal,e.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno;

筛选

1
2
3
select e.empno,e.ename,e.sal,e.deptno,d.dname
from emp e,dept d
where e.deptno = d.deptno and e.sal > 2000;

自连接

1
2
3
select e1.ename,e1.job,e1.mgr,e2.ename
from emp e1,emp e2
where e1.mgr = e2.empno

多表连接

1
2
3
select e.empno,e.ename,e.sal,e.deptno,d.dname,s.grade
from emp e,dept d,salgrade s
where e.deptno = d.deptno and e.sal between s.losal and s.hisal;

缺点：

  1. 92 语法麻烦
  2. 表的连接条件和筛选条件放在一起没有分开
  3. 99语法中提供了更多的查询连接类型：cross，natural，inner，outer

子查询

有时查询操作一次不能完成，分步骤虽然能解决问题，但是效率低，第二个命令依托于第一个命令，第一个命令的结果给第二个命令使用，但是第一个命令结果改变后，第二个命令也会导致修改

所以将步骤合并——子查询

1
select * from emp where sal > (select sal from emp where ename = 'CLARK');

一个命令解决问题，效率高

不相关子查询

可以独立运行的子查询，称为不相关子查询

执行顺序：先执行子查询，再执行外查询

优点：功能强大，简单

缺点：较难理解

根据子查询的结果行数，可以分为单行子查询和多行子查询

单行子查询

几个单行子查询练习

  1. 查询工资高于平均工资的雇员名字和工资
  2. 查询和CLARK同一部门且比他工资低的雇员名字和工资
  3. 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
1
2
3
4
5
6
7
8
9
10
11
12
13
select ename,sal
from emp
where sal > (select avg(sal) from emp);

select ename,sal
from emp
where deptno = (select deptno from emp where ename = 'CLARK')
	and sal < (select sal from emp where ename = 'CLARK');

select *
from emp
where job = (select job from emp where ename = 'SCOTT')
	and hiredate < (select hiredate from emp where ename = 'SCOTT');
多行子查询

几个多行子查询练习

  1. 查询部门20中职务同部门10的雇员一样的雇员信息
  2. 查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资
  3. 查询工资低于任意一个“CLERK”的工资的雇员信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- 1
select * from emp
where deptno = 20
and job in (select job from emp where deptno = 10);

select * from emp
where deptno = 20
and job = any(select job from emp where deptno = 10);

-- 2
-- 多行子查询
select empno,ename,sal
from emp
where sal > all(select sal from emp where job = 'SALESMAN');
-- 单行也能实现
select empno,ename,sal
from emp
where sal > (select max(sal) from emp where job = 'SALESMAN');

-- 3
-- 多行子查询
select *
from emp
where sal < all(select sal from emp where job = 'CLERK')
	and job != 'CLERK';
-- 单行也能实现
select * 
from emp
where sal < (select min(sal) from emp where job = 'CLERK')
	and job != 'CLERK';

多行的几个

any()：逻辑中满足子查询其中之一

all()：逻辑中满足所有子查询

相关子查询

子查询不可以独立运行，称为相关子查询

执行顺序：先运行外查询，再运行子查询

  1. 查询本部门最高工资的员工

通过不相关子查询实现：

1
2
3
4
5
6
7
select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)
union
select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)
union
select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30)
union
select * from emp where deptno = 40 and sal = (select max(sal) from emp where deptno = 40)

缺点：语句太多，具体几个部门不好枚举

通过相关子查询实现：

1
select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno
  1. 查询工资高于其所在岗位的平均工资的那些员工

相关子查询：

1
select * from emp e where sal >= (select avg(sal) from emp e2 where e2.job = e.job)

DML 数据操作语言

对数据库中数据进行增、删、改的操作

  • INSERT：增加数据
  • UPDATE：修改数据
  • DELETE：删除数据

添加数据

两种方式：

根据设定的格式依次填写信息，全字段插入数据

1
insert into t_student values(1,'张三','男',18,'2022-5-8','软件1班','123@126.com');

有些信息暂时不知道，只插入特定字段的数据，就需要加入字段的名字

1
insert into t_student (student_no,student_name,enterdate) values (10,'李四','2023-7-5')

添加多条数据时可以合并添加

1
2
3
4
5
insert into t_class values (null,'java001','r803'); -- 1
insert into t_class values (null,'java002','r416');
insert into t_class values (null,'大数据001','r103');

insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103'); -- 2

上述两种办法效果一致

注意事项：
时间

类型中时间有很多的表示方式

1
2
3
“2023/1/11”	## 1
“2023-1-11” ## 2
“2023.1.11” ## 3

如果想要调用当前时间

1
2
3
now()
sysdate()
CURRENT_DATE()
char varchar

是字符的个数，不是字节的个数，

如果需要控制字节个数

1
2
binary		## 定长
varbinary	## 不定长

修改、删除数据

修改表中数据

1
2
3
4
5
6
update t_student set sex = '女';
update t_student set sex = '男' where student_no = 10;
UPDATE T_STUDENT SET AGE = 21 WHERE STUDENT_NO = 10;
update t_student set CLASSNAME = 'java01' where student_no = 10 ;
update t_student set CLASSNAME = 'JAVA01' where student_no = 9 ;
update t_student set age = 29 where classname = 'java01';

删除操作

1
delete from t_student where student_no = 2;

修改（更新）：update

删除：delete

注意事项：

关键字，表名，字段名不区分大小写

默认情况下，内容不区分大小写

删除操作from关键字不可缺少

修改，删除数据别忘记加限制条件

清空表内数据

1
2
delete from t_student;
truncate table t_student;

结果上都是清空数据库表中数据

truncate 截断

delete和truncate的区别
  1. delete 属于DML；truncate属于DDL
  2. delete操作是将表中所有记录逐条删完；truncate操作是保留结构，就像字面意思的截断，相当于将原表部分与新表部分截断，重新编号记录，truncate操作效率更高
  3. delete操作可以回滚；truncate操作会导致隐式提交，因此不能回滚
  4. delete操作成功后会返回已删除的行数（删除4行记录，“Affected rows：4”；截断操作不会返回已删除的行数，通常是“Affected rows：0”）
  5. delete操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；truncate操作则会重新从1开始自增

DDL 数据定义语言

对数据库对象的创建，修改，删除操作

  • CREATE：创建数据库对象
  • ALTER：修改数据库对象
  • DROP：删除数据库对象

创建数据库表

修改删除数据库表

修改表的结构

增加一列

1
2
3
alter table t_student add score double(5,2);
alter table t_student add score double(5,2) first;		## 加到表最前面
alter table t_student add score double(5,2) after sex;	## 放到sex列后面

增加成绩一列，数据类型为double，总长度为5，小数点后保留2位

删除一列

1
alter table t_student drop score;

修改一列

1
2
alter table t_student modify score float(4,1);
alter table t_student change score scorel double(5,1);

modify：修改列的类型定义，不会修改列的名字

change：修改列的名字和列的类型定义

删除表

1
drop table t_student;

快速添加数据库表

结构和数据跟原表一致
1
2
3
create table t_student2
as
select * from t_student;

简简单单复制一张数据库表

只保留原表结构，不包括数据
1
2
3
create table t_student3
as
select * from t_student where 0=1;

where 0=1，任何时候都不成立，所以，所有元素都不会被复制

复制一张具有结构的空表

只复制部分列，部分数据
1
2
3
create table t_student4
as
select sno,sname,age from t_student where sno = 2;

复制sno，sname，age列，其中sno=2的信息

已经不只是复制的能力了，属于筛选信息了

DCL 数据控制语言

用来授予或回收访问数据库的权限

  • GRANT：授予用户某种权限
  • REVOKE：回收授予的某种权限

TCL 事务控制语言

用于数据库的事物管理

  • START TRANSACTION：开启事务
  • COMMIT：提交事务
  • ROLLBACK：回滚事务
  • SET TRANSACTION：设置事务的属性

表的完整性约束

为了防止不符合规范的数据存入数据库，MySQL提供了一种机制，检查增、删、改过程中的数据是否符号规范，来保证数据库中数据的准确性和一致性，这种机制就是完整性约束

非外键约束

  • PRIMARY KEY：主键约束，约束字段的值可唯一地标识对应的记录
  • NOT NULL：非空约束，约束字段的值不能为空
  • UNIQUE：唯一约束，约束字段的值是唯一的
  • CHECK：检查约束，限制某个字段的取值范围
  • DEFAULT：默认值约束，约束字段的默认值
  • AUTO_INCREMENT：自动增加约束，约束字段的值自动递增

外键约束

  • FOREIGN KEY：外键约束，约束表与表之间的关系

非外键约束

主键

性质
  • 不能为空 + 唯一
  • 如果主键是整数类型，需要自增
作用

通过主键能够查到唯一的一条记录

就像输入身份证就能且只能查到一个人

注意事项

在自增时sql报错，主键会被浪费导致不连号，但是没有要求主键必须连号

主键没有设定值，或者用null和default都可以完成主键自增的效果

约束

  • 表级约束：

    约束表中的任意一个或者多个字段，与列定义相互独立，不包含在列定义中

    与定义用逗号隔开，必须指出要约束的列的名称

    可以给约束起名，在出错时容易找错

    部分约束没有表级约束，只有列级约束：默认值约束，自增约束

    部分约束没有列级约束，只有表级约束：外键约束

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    -- 表级约束
    create table t_student(
    	sno int(6) auto_increment,
    	sname varchar(5) not null,
    	sex char(1) default '男',
    	age int(3),
    	enterdate date,
    	classname varchar(10),
    	email varchar(15),
    	constraint pk_stu primary key (sno),
    	constraint ck_stu_sex check(sex = '男' || sex = '女'),
    	constraint ck_stu_age check(age >= 18 and age <= 50),
    	constraint uq_stu_email unique (email)
    );

  • 列级约束：

    包含在列定义中，直接跟在该列的其他定义之后，用空格分隔

    不必指定列名

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    -- 列级约束
    create table t_student(
    	sno int(6) primary key auto_increment,
    	sname varchar(5) not null,
    	sex char(1) default '男' check(sex='男' || sex='女'),
    	age int(3) check(age>=18 and age<=50),
    	enterdate date,
    	classname varchar(10),
    	email varchar(15) unique
    );
在创建表以后添加约束

后续使用SQL语言添加约束

1
2
3
4
5
alter table t_student add constraint pk_stu primary key (sno);	-- 主键约束
alter table t_student modify sno int(6) auto_increment; -- 自增
alter table t_student add constraint ck_stu_sex check(sex = '男' || sex = '女'); -- 检查约束
alter table t_student add constraint ck_stu_age check(age >= 18 and age <= 50);
alter table t_student add constraint uq_stu_email unique (email); -- 唯一约束

外键约束

将多张表之间通过约束，实现关联

建表时添加外键约束

1
2
3
4
5
6
create table t_student(
	sno int(6) primary key auto_increment,
	sname varchar(5) not null,
	classno int(4),
	constraint fk_stu_classno foreign key (classno) references t_class (cno)
);

建表后添加外键约束

1
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno);

外键策略

删除主表数据时，如果从表存在与主表被删除数据相关联的数据时，直接删除不了，为了能够删除，有三种外键策略

  • no action 不允许操作

    为了删除，只能通过SQL先将对应数据关联项修改，然后再删除

  • cascade 级联操作：操作主表的时候影响从表的外键信息

    先删除之前的外键约束，重新添加外键约束

    1
    2
    alter table t_student drop foreign key fk_stu_classno;
    alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;

    对修改（update）和删除（delete）分别设置策略

  • set null 置空操作：将受影响部分直接置空

    先删除之前的外键约束，重新添加外键约束

    1
    2
    alter table t_student drop foreign key fk_stu_classno;
    alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;

上述策略可以混合使用，达到不同效果

1
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete set null;
应用场合

朋友圈删除：留言，点赞也删除，cascade

班级删除：对应学生 班级置空，稍后处理，set null

数据库对象

事务 Transaction

用来维护数据库完整性，保证一系列的MySQL操作要么全部执行，要么全不执行

概念

事务指一个操作序列

这个序列中的多个操作，要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位

由DBMS（数据库管理系统）中的事物管理子系统负责事物的处理

目前常用的存储引擎有InnoDB（MySQL5.5以后默认的存储引擎）和MyISAM（MySQL5.5之前默认的存储引擎），其中InnoDB支持事务处理机制，MyISAM不支持

特性

事务处理可以确保除了非事务性序列内的所有操作都能够成功完成，否则不会永久更新面向数据的资源

通过将一组相关操作组合为一个要么全部成功要么全部失败的序列，可以简化错误恢复，并使应用程序更加可靠

满足事务的四个特性（ACID特性）的操作序列，才可以称为事务

原子性 Atomicity

原子不可再分（其实可拆乐），事务中的所有操作可以看做一个原子，事务是应用中不可再分的最小逻辑执行体，要么全执行，要么都不执行

一致性 Consistency

事务执行结果，必须使数据库从一个一致性状态，变到另一个一致性状态

一致性通过原子性保证

隔离性 Isolation

各个事务的执行互不干扰

持久性 Durability

事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中

一个演示

账户转账

1
2
3
4
5
6
7
8
create table account(
	id int primary key auto_increment,
	uname varchar(10) not null,
	balance double
);
insert into account values(null,'丽丽',2000),(null,'小刚',2000);

select * from account;

丽丽给小刚转账200元

1
2
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;

但是这样是两个事务（默认一个DML语句是一个事务），所以上面的操作执行了两个事务

所以一旦出现意外，会导致错误

1
2
update account set balance = balance - 200 where id = 1;
update account set balance = balance2 + 200 where id = 2;

第一条语句成功执行，丽丽账户减少200，而第二条语句报错，小刚账户不变，这样200就凭空消失了

为了解决这样的问题，我们需要控制这两个操作为一个事务

1
2
3
4
5
6
7
start transaction;
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;

rollback;

commit;

start transaction 手动开启事物

然后执行各条语句

如果不想要当前的修改可以进行回滚

rollback； 回滚操作

如果确认修改，则进行提交

commit； 提交操作

在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据

相当于拿着样板修改，确定后再真正修改

并发问题

脏读 Dirty read

读取到了未提交的数据

不可重复读 Unrepeatableread

多次读取同一个数据，但是在事务中，另一个事务更改了这个数据，并且提交，导致读取的数据没有更新

解决：通过锁定读取的数据，防止其他事务更改

幻读 Phantom read

事务A读取了几行数据后，另一个事务B插入了一些数据，在随后查询中，事务A发现多了一些数据

解决：锁定全表，防止其他事务修改

事务隔离级别

事务的隔离级别用于决定如何控制并发用户读写数据的操作。

数据库允许多用户并发访问，多个用户同时对数据库操作会产生并发问题，MySQL提供四种隔离级别来解决上述问题。

事务的隔离级别从低到高：

READ UNCOMMITTED

READ COMMITTED

REPEATABLE READ

SERIALIZABLE

下面是他们解决并发问题的能力

隔离级别 脏读 不可重复读 幻读
Read Uncommitted 不解决 不解决 不解决
Read Committed 解决 不解决 不解决
Repeatable read 解决 解决 不解决
Serializable 解决 解决 解决

并不是隔离级别越高越好

隔离级别越低，越能支持高并发的数据库操作

但同时，级别越低，会出现的并发问题也会更多

所以需要根据实际情况取舍

查看默认的事务隔离级别 MySQL默认repeatable read

1
select @@transaction_isolation;
设置事务的隔离级别（设置当前会话的隔离级别）
1
2
3
4
set session transaction isolation level read uncommitted;
set session transaction isolation level read committed;
set session transaction isolation level repeatable read;
set session transaction isolation level serializable;

可以利用cmd和Navicat模拟并发过程

视图 view

概念

是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表

过滤原表，查出需要的数据，重新整合成一张虚拟表

数据库中只存放视图的定义（动态检索数据的查询语句），并不存放视图中的数据

创建一个视图，相当于提供一个特定的画面，连接不同的数据库表，用户通过这个特定的画面能够看到自己需要的信息，不需要在接触数据库表

好处

简化用户操作：只注意所关心的数据，不需要关心数据表的结构，关联条件，查询条件等等

对机密数据提供安全保护：对不同的用户定义不同的视图，避免机密数据出现在不应该看到这些数据的用户视图上。这样视图就自动提供了对机密数据的安全保护功能。

创建视图

创建单表视图
1
2
3
4
5
6
7
create view myview01
as
select empno,ename,job,deptno 
from emp
where deptno = 20;

select * from myview01;

单独的创建视图，依靠表单的指定信息

重复执行创建视图语句会报错，添加 replace 关键字

实现创建/替换视图

1
2
3
4
5
create or replace view myview01
as
select empno,ename,job,deptno 
from emp
where deptno = 20;
创建/替换多表视图
1
2
3
4
5
6
7
create or replace view myview02
as
select e.empno,e.ename,e.sal,d.deptno,d.dname
from emp e
join dept d
on e.deptno = d.deptno
where sal > 2000;

相当于连接多表，然后对这个多表创建视图

创建统计视图
1
2
3
4
5
6
7
create or replace view myview03
as 
select e.deptno,d.dname,avg(sal),min(sal),count(1)
from emp e
join dept d
using(deptno)
group by e.deptno;
创建基于视图的视图
1
2
3
create or replace view myview04
as 
select * from myview03 where deptno = 20;

同样的，对视图也可以创建视图

插入数据

1
2
insert into myview01 (empno,ename,job,deptno) values (9999,'lili','clerk',20);
insert into myview01 (empno,ename,job,deptno) values (8888,'nana','clerk',30);

插入到视图中，也会插入到原数据库表

但是如果视图展示20部门的员工信息，但是插入30部门的员工，上述语言创建的视图，也会显示（正常是不应该显示的）

通过添加 with check option，进行检查

1
2
3
4
5
6
create or replace view myview01
as
select empno,ename,job,deptno 
from emp
where deptno = 20
with check option;

存储过程 Stored Procedure

SQL语言非常便利，简洁，一个命令实现一个处理（非程序语言）

如果不编写流程，所有的处理只能通过一个个命令来实现。

但是局限性是显而易见的

所以出现存储过程这个概念

存储过程：数据库中保存（Stored）的一系列SQL命令（Procedure）的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个小程序

优点

提高执行性能

存储过程执行效率之所以高，在于普通的SQL语句，每次都需要语法分析，编译，执行，但是存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用

可减轻网络负担

使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端（或应用程序）传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担

将数据库的处理黑盒化

应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用那个存储过程就可以了

展示存储过程

定义一个没有返回值的存储过程

实现模糊查询操作

1
2
3
4
5
6
7
8
create procedure mypro01(name varchar(10))
begin
	if name is null or name = "" then
		select * from emp;
	else 
		select * from emp where ename like concat('%',name,'%');
	end if;
end;

相当于编程语言中编写函数的过程，这个语法很类似于VHDL

定义一个有返回值的存储过程
1
2
3
4
5
6
7
8
9
create procedure mypro02(in name varchar(10),out num int(3))
begin
	if name is null or name = "" then
		select * from emp;
	else 
		select * from emp where ename like concat('%',name,'%');
	end if;
	select found_rows() into num;
end;

不同于编程语言的函数，存储过程的返回值同样写在括号中，用 in 来表示输入参数（in可以省略不写），用 out 表示返回值

存储过程中查看记录条数，使用found_rows()

调用存储过程
1
2
call mypro01(null);
call mypro01('R');

相当于编程语言中实现函数调用的过程，输入参数，获得对应的结果

对于有返回值的存储过程的调用，可以添加@来表示返回值，并且可以使用select查询该返回值

1
2
3
4
5
call mypro02(null,@num);
select @num;

call mypro02('R',@aaa);
select @aaa;
删除存储过程
1
drop procedure mypro01;