查询所有字段

第一种方式：加上所有字段名

第二种方式：使用 *，缺点：效率低，可读性差。实际开发中不建议

列起别名

1
2
3
4
5
6
7
8
9
# 可以省略as
select dname as deptname from dept

# 解决别名含有空格的情况，单引号双引号都行
select dname as 'dept name' from dept

# 别名为中文同样需要用单引号包起来

# 注意：在所有数据库中，单引号是标准，双引号在oracle数据库中用不了

条件查询

=、<>或!=、<、<=、>、>=

between ... and ... 等同于 >= and <=

is null 为空 (is not null) 不为空

and、or、in（not in）

and 和 or 同时出现时，and 优先级比 or 高。可用 () 解决。

1
select * from emp where job in ('manager','salesman')

not not 可以取非

like 模糊查询，支持%或下划线匹配

% 匹配任意个字符

_ 一个下划线只匹配任意一个字符

1
2
3
4
5
6
7
select * from emp where ename like '%o%' 
select * from emp where ename like '%T' 
select * from emp where ename like 'K%'
select * from emp where ename like '_A%' 

# 特别：找出名字带有“_”的，使用转义字符“\”
select * from emp where ename like '%\_%'

多字段排序

1
2
# 查询名字和薪资，按薪资升序，如果薪资一样，则按名字升序，否则不启用第二个字段排序
select ename,sal from emp order by sal asc,ename asc

根据字段位置排序：

1
2
# 按照查询结果的第二列sal排序
select ename,sal from emp order by 2 # 不建议

数据处理函数/单行处理函数

  • 常用

    lower：转小写

    upper：转大写

    substr：取子串 substr(被截取的字符串,起始下标,截取长度)，起始下标从1开始。

    1
    2
    3
    select substr(ename,1,2) from emp 
    # 查询名字首字符为A的员工，除了用模糊查询，也可以使用substr
    select ename from emp where substr(ename,1,1) = 'A'

    length：取长度

    concat：拼接成字符串

    1
    2
    # 不一定是字符串才能拼接
    select concat(empno,ename) from emp

    trim：去空格

    1
    select * from emp where ename = trim('   KING')

    str_to_date：varchar转date

    1
    2
    3
    # 格式：str_to_date('字符串日期','日期格式')
    # %Y 年	%m 月  %d 日  %h 时  %i 分  %s 秒
    select str_to_date('2022-01-01','%Y-%m-%d')

    date_format：date转varchar

    1
    # 格式：date_format(日期,'日期格式')

    format：设置千分位，格式化数字 format(数字,'格式')

    round：四舍五入

    1
    2
    3
    # 0 表示保留到整数位，1表示保留一位小数，以此类推
    # -1 结果为 130
    select round(126.123,0) from emp

    rand()：生成随机数（生成0-1之间的小数），不包含0和1。

    1
    2
    3
    select rand()
    # 生成[0,100]之间的整数，注意为闭区间
    select round(rand()*100)

    ifnull：将null转成具体值

    注意：在所有数据库中，只要有NULL参与的数学运算，最终结果都为NULL

    1
    2
    # 例如comom某一行存在NULL值，则计算结果也为NULL
    select ename,sal+comm as salcomm from emp
    1
    2
    # 使用ifnull解决
    select ename,sal+ifnull(comm,0) as salcomm from emp

    case..when..then..when..then..else..end：

    1
    2
    3
    4
    5
    # 当员工的工作岗位MANAGER时，工资上调10%，如果是SALESMAN时，工资上调50%
    # 注意：（不修改数据库，只是显示查询结果）
    select ename,job,sal,
    (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
    from emp

  • 综合

    1
    2
    # 查询员工姓名，首字母大写，其余小写
    select concat(upper(substr(ename,1,1)),lower(substr(ename,2,length(ename)-1))) as ename from emp

分组函数/聚合函数/多行处理函数

count：记录数

1
2
3
# count(*) 和 count(具体字段)区别
# * 代表记录，当一条记录每个字段都为NULL时，那个记录就不存在
# 所以count(*)在任何情况下都可以准确求出表的记录数，而当具体字段存在空值时count(具体字段)就不准确了

sum：求和

avg：取平均

max：取最大数

min：取最小数

注意：

1、分组函数会自动忽略空值NULL。

2、分组才能使用，不分组默认整张表为一组。

3、不能直接使用在where子句中

4、所有的分组函数可以结合起来用

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

分组查询

注意：在select语句中，如果有group by语句的话，select后面只能跟参加分组的字段以及分组函数

1
2
# 找出每个工作岗位的工资和
select job,sum(sal) from emp group by job
1
2
# 找出每个部门的最高薪资
select deptno,max(sal) from emp group by deptno
1
2
# 找出每个部门不同岗位的最高薪资
select deptno,job,max(sal) from emp group by deptno,job
1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 找出每个部门最高工资，要求显示最高工资大于3000的
# 方式一：使用having。注意：having必须和group by联合使用
select deptno,max(sal) as maxsal from emp 
group by deptno
having maxsal > 3000
# 方式二：
select * from (
	select deptno,max(sal) as maxsal from emp group by deptno
)as t
where maxsal > 3000 
# 以上两种方式执行效率低，原因是先将所有最高工资算出来再排除<=3000的
# 方式三：
select deptno,max(sal) as maxsal from emp where sal > 3000
group by deptno

优先策略：where和having，优先选择where，where实在完成不了了再选择having。

1
2
3
# where没办法解决的
# 找出每个部门平均薪资，要求显示平均薪资高于2500的
select deptno,avg(sal) as avgsal from emp group by deptno having avgsal > 2500

单表查询总结

  • 执行顺序

    1、from

    2、where

    3、group by

    4、having

    5、select

    6、order by

    7、limit

    可以看出执行where时还没有进行分组，所以不能在where子句里写分组函数

  • 综合练习

    1
    2
    3
    4
    5
    6
    # 找出每个岗位的平均薪资，除MANAGER外，要求显示平均薪资大于1500的，并且按照平均薪资降序排
    select job,avg(sal) as avgsal from emp 
    where job != 'MANAGER'
    group by job
    having avgsal > 1500
    order by avgsal desc

distinct关键字

作用：去重

1
select distinct job from emp

注意：只能出现在所有查询字段的最前方

两个字段联合起来去重：

1
select distinct job,deptno from emp

结果：

image-20220615220220660

连接查询

1）分类：

  • 内连接

    等值连接

    非等值连接

    自连接

  • 外连接

    左外连接（左连接）

    右外连接（右连接）

  • 全连接（不重要）

2）笛卡尔积现象：

1
select ename,dname from emp,dept

image-20220615222700366

避免：

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

image-20220615222940162

3）内连接值等值连接

1
2
3
4
5
6
7
8
9
# 查询每个员工所在部门，显示员工名和部门名

# (SQL92语法)  
select e.ename,d.dname from emp e,dept d
where e.deptno = d.deptno

# (SQL99语法) join前面可以省略inner
select e.ename,d.dname from emp e join dept d
on e.deptno = d.deptno

4）内连接之非等值连接

1
2
3
4
# 找出每个员工的薪资等级，要求显示员工名，薪资，薪资等级
# 同样再join前省略了inner
select e.ename,e.sal,s.grade from emp e join salgrade s
on e.sal between s.losal and s.hisal

5）内连接之自连接

1
2
3
# 查询员工的上级领导，要求显示员工名和对应的领导名
select e1.ename as '员工',e2.ename as '领导' from emp e1 join emp e2
on e1.mgr = e2.empno

6）外连接

1
2
3
4
5
6
7
8
9
10
# 右外连接
# 查询每个员工所在部门，显示员工名和部门名
# 意思是除了要将符合e.deptno = d.deptno的记录查出来还要将right右边表剩余数据也查出来
# 即将右边的表看成主表，捎带着关联查询左边的表
# outer同样可以省略
select e.ename,d.dname from emp e 
right join dept d
on e.deptno = d.deptno

# 左外连接同理，还要将left左边表剩余数据也查出来

注意：任何一个右连接都有左连接写法，左连接同样都有右连接写法。

image-20220615231159056

1
2
3
# 查询员工的上级领导，要求显示员工名和对应的领导名
select e1.ename as '员工',e2.ename as '领导' from emp e1 left join emp e2
on e1.mgr = e2.empno

image-20220615232144761

7）多张表连接

语法

1
2
3
4
5
6
7
8
select 
	...
from a
join b on a和b的连接条件
join c on a和c的连接条件
right join d on a和d的连接条件

# 一条sql中内连接和外连接可以混合使用

案例

1
2
3
4
5
6
# 找出每个员工的部门名称以及工资等级，还有上级领导，要求显示员工名、领导名、部门名、薪资、薪资等级
select e1.ename as '员工',e2.ename as '领导',d.dname,e1.sal,s.grade 
from emp e1
left join emp e2 on e1.mgr = e2.empno
join dept d on e1.deptno = d.deptno
join salgrade s on e1.sal between s.losal and s.hisal

子查询

1）语法

1
2
3
4
5
6
select 
	..(select)..
from 
	..(select)..
where
	..(select)..

2）where中子查询

1
2
3
# 找出比最低工资高的员工姓名和工资
select ename,sal from emp
where sal > (select min(sal) from emp)

3）from中的子查询

1
2
3
4
# 找出每个岗位的平均工资的薪资等级
select t.job,s.grade from salgrade as s
join (select job,avg(sal) as svgsal from emp group by job) as t
on t.svgsal between s.losal and s.hisal

4）select中的子查询

1
2
3
# 找出每个员工的部门名称，要求显示员工名、部门名
select e.ename,(select d.dname from dept d where d.deptno = e.deptno) dname
from emp e

union

1
2
3
# 查询工作岗位是MANAGER和SALESMAN的员工
select ename,job from emp where job='MANAGER' or job='SALESMAN'
select ename,job from emp where job in ('MANAGER','SALESMAN')
1
2
3
4
# 使用联合查询
select ename,job from emp where job='MANAGER'
union
select ename,job from emp where job='SALESMAN'

相比之下union的效率会高一些。对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，但是union可以减少匹配次数。

1
2
3
4
5
6
7
8
9
a 连接 b 连接 c
a 10条数据
b 10条数据
c 10条数据
匹配次数为：10*10*10=1000次

a连接b一个结果：10*10 --> 100次
a连接c一个结果：10*10 --> 100次
使用union为：100+100=200次

注意：结果集合并时列数必须相同并且数据类型也要一致

limit

取出查询结果的一部分，用法：

完整用法：limit startIndex,length 起始下标以及长度（起始下标从0开始）

缺省用法：limit 5 取前5

1
2
# 按照薪资降序，取出排名在前5名的员工
select ename,sal from emp order by sal desc limit 5

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

表操作及数据类型

1）数据类型

varchar：节省空间，会根据实际数据长度动态分配空间，缺点速度慢，最多（255）

char：分配固定的空间存储数据，有点速度快，最多（255）

int（11）、bigint、float、double、date、datetime

clob：字符大对象，最多可以存储4G的字符串，比如文章等等

blob：二进制大对象，用来存储图片、声音、视频等流媒体数据，需要使用io流才行

注意：类型后面数字代表可存储的长度，一个中文或字母都算一个长度，或者数字

2）建表

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

3）删表

1
2
drop table t_user;
drop table if exists t_user;

4）insert

1
2
3
4
insert into 表名(字段名,字段名,字段名...) values (值1,值2,值3...)
insert into 表名 values(...) # 一定要写所有字段
# 也可以一次插入多条数据
insert into 表名(字段名,字段名,字段名...) values (值1,值2,值3...),(值1,值2,值3...)...

5）update

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

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

6）delete

1
delete from 表名 where 条件

说明：数据被删除了，但是数据在硬盘的真实存储空间不会被释放，支持回滚，可以再恢复数据

注意：没有条件整张表的数据会全部删除

7）表复制

1
create table emp2 as select * from emp

8）表插入

1
insert into dept_bak select * from dept

约束

1）非空约束 not null

not null 只有列级约束，没有表级约束。

2）唯一性约束 unique

3）主键约束 primary key

4）外键约束 foreign key

5）检查约束 check (mysql不支持，oracle支持)

示例：

普通

1
2
3
4
create table t_test(
	id int,
	name varchar(20) not null
);

联合唯一

1
2
3
4
5
6
create table t_test(
	id int,
	name varchar(20),
    email varchar(20),
    unique(name,email)
);

not null 和 unique 联合

mysql中，如果一个字段同时被not null 以及 unique 约束，该字段自动变成主键字段（oracle中不一样）

1
2
3
4
create table t_test(
	id int,
	name varchar(20) not null unique 
);

复合主键

1
2
3
4
5
6
create table t_test(
	id int,
	name varchar(20),
    email varchar(20),
    primary key(id,name)
);

mysql中，主键自动递增

1
2
3
4
create table t_test(
	id int primary key auto_increment,
	name varchar(20)
);

外键约束

被引用的字段不一定得是主键，但至少具有unique约束。

1
2
3
4
5
6
7
8
9
10
11
12
# 班级表
create table t_class(
	classno int primary key auto_increment,
	classname varchar(20)
);
# 学生表
create table t_student(
	no int primary key auto_increment,
	name varchar(20),
    cno int,
    foreign key(cno) references t_class(classno)
);

事务

例如转账就要用到事务

提交与回滚

提交事务：commit;

回滚事务：rollback; (注意：只能回滚到上一次事务提交点。)

1
2
3
4
5
6
commit;
update ...;
insert ...;
insert ...;
...
rollback;

mysql默认自动提交事务，也就是说每执行一条语句，提交一次事务。

执行start transaction;取消自动提交事务。

事务特性

A：原子性

说明事务是最小的工作单元，不可再分。

B：一致性

在同一事务中，所有操作必须同时成功，或者同时失败。

C：隔离性

A事务和B事务具有一定的隔离。

eg：A事务操作一张表时，B事务也在操作同一张表，会怎样？

D：持久性

事务最终结束的保障，将没有保存到硬盘的数据保存到硬盘上。

隔离级别

1）read uncommitted：读未提交（最低隔离级别）

事务A可以读取事务B未提交的数据。存在脏读现象。一般不用。

2）read committed：读已提交（Oracle默认隔离级别）

事务A只能读取事务B提交之后的数据。解决了脏读现象。但是存在不可重复读取数据现象。

eg：A事务开启，读取的数据为3条，此时B提交了事务改变了数据，第二次读取的数据可能为4条，A事务结束。

3）repeatable read：可重复读（MySQL默认隔离级别）

事务A开启后，不管多久，每次事务A读取的数据都是一致的，即使事务B将数据修改了。

解决了不可重复读，但存在幻影读。也就是，读取的数据有可能是幻象，不一定是真实的。

4）serializable：序列化/串行化（最高隔离级别）

效率最低，但是解决了所有问题。表示事务排队，不能并发。

例子：

查看当前隔离级别：select @@tx_isolation;

设置隔离级别：set global transaction isolation level read committed;

1
2
3
4
5
6
# 事务A
start transaction;
select * from t_class;  # 1 结果为3条
select * from t_class;	# 3 结果为3条
select * from t_class;	# 5 结果为4条
commit;
1
2
3
4
# 事务B
start transaction;
insert into t_class values(...); # 2
commit; # 4

索引

概述

在表字段上添加的，为了提高查询效率。相当于目录，可以缩小扫描范围（会先扫描索引）。

  1. 一个字段可以添加一个索引，多个字段联合起来也可以添加索引。

  2. 索引在mysql中都是以树的形式存在，遵循左小右大原则存放。

  3. 在任何数据库中都会自动为主键添加索引。另外在mysql中，字段上如果有unique约束，也会自动创建索引

大致原理

image-20220701181612193

语法

创建：

1、单一索引create index emp_ename_index on emp(ename);

2、复合索引create index job_sal_index on emp(job,sal);

删除索引：drop index emp_ename_index on emp;

查看SQL语句是否使用了索引：explain select * from emp where ename = 'KING';

失效情况

示例：

1
2
# 模糊查询会失效
select * from emp where ename like '%T%';
1
2
# 使用or时，要求or两边的条件都有索引，才会走索引
select * from emp where ename = 'KING' or job = 'MANAGER';
1
2
# 使用复合索引时，如果没有使用左侧的索引，索引失效 create index job_sal_index on emp(job,sal);
select * from emp where sal = 800;
1
2
# 在where当中索引列参加了运算，索引失效
select * from emp where sal+1 = 800; # 注意不是800参加运算
1
2
# 在where中索引列使用了函数
select * from emp where lower(ename) = 'smith';

等等…

分类

单一索引、复合索引 、主键索引、唯一性索引(unique) …

唯一性弱的字段添加索引用处不大。

视图

站在不同的角度看同一份数据

创建与删除

只有查询语句才能以view形式创建。视图创建了除非主动删除，否则存在于库中。

1
2
3
4
# 创建
create view emp_view as select * from emp;
# 删除
drop view emp_view;

作用

可以面向视图对象进行增删改查，对视图对象的这些操作，会导致原表被操作。

例子：

1
2
select * from emp_view;
insert into emp_view( .. , .. ) values( .. , ..);

场景：

一个既复杂又长的查询需要在许多业务中使用，可以使用视图，业务直接查询视图就可以。

DBA命令

1
2
# 创建用户
create user swj identified by '123456';

授权、回收权限、导出导入等 …

三范式

数据库表的设计依据。每个范式都建立在前范式的基础之上。

1）第一范式：要求任何一张表必须有主键，并且每一个字段原子性不可再分。

1
2
3
4
id		联系方式
1001	aa@qq.com,15860123233
1000	bb@qq.com,32423432444
1001	cc@qq.com,23424828344

不满足第一范式。

2）第二范式：要求所有非主键字段完全依赖主键，不要产生部分依赖。

1
2
3
4
5
# 字段一和字段二联合主键
1001	001		张三		王老师
1002	002		张四		赵老师
1003	001		张五		王老师
1001	002		张三		赵老师

满足第一范式，但不满足第二范式。结果：数据冗余，空间浪费。

3）第三范式：要求所有非主键字段直接依赖主键，不要产生传递依赖。

1
2
3
4
				 班级编号 班级名称
1001	张三		001		1班
1002	张四		002		2班
1003	张五		003		3班

满足一二范式，不满足第三范式。