Day53、表连接、合并结果集、组函数和分组、子查询、表操作（创建、删除、更新）、事务控制语句

来源：互联网发布：webpack搭建php服务器编辑：程序博客网时间：2024/05/16 19:17

二、sq199中的表连接

内连接：

select字段

from表1 [inner]join 表2

on连接条件；

外连接：

左外连接：内连接的结果集+左表匹配不上的记录

select 字段

from表1 left [outer] join 表2

on连接条件；

右外连接：内连接的结果集+右表匹配不上的记录

select 字段

from表1 right [outer] join 表2

on连接条件；

全外连接：左外连接的结果集+左外连接的结果集-内连接的结果集

select 字段

from表1 full [outer] join 表2

on连接条件；

/*列出员工信息，包括id,.first_name和部门名称*/

selecte.id,e.first_name,d.name

froms_emp e join s_dept d

one.dept_id=d.id;

/*列出所有员工信息，包括id,.first_name和部门名称*/

selecte.id,e.first_name,d.name

froms_emp e left join s_dept d

one.dept_id=d.id;

或 select e.id,e.first_name,d.name

froms_dept d right join s_emp e

one.dept_id=d.id;

/*列出员工信息，包括id,.first_name和所有部门名称*/

selecte.id,e.first_name,d.name

froms_dept d right join s_emp e

one.dept_id=d.id;

或 select e.id,e.first_name,d.name

froms_dept d left join s_emp e

one.dept_id=d.id;

/*列出所有员工信息，包括id,.first_name和所有部门名称*/

selecte.id,e.first_name,d.name

froms_dept d full join s_emp e

one.dept_id=d.id;

三表查询（内连接）

select 字段

from 表1

join 表2 on 表1.字段=表2.字段

join 表3 on 表1（2）.字段=表3.字段

三、合并结果集

union 合并两个结果集排重重复数据

union all 合并两个结果集不排重

select id from s_emp union

select id from s_dept;

select id from s_emp union all

select id from s_dept;

两个select语句查询的字段的类型和数量要匹配

四、组函数和分组

1、组函数

count：统计一组数据的行数参数可以是任意类型还可以用*

max：获取最大值参数可以是任意类型

min：获取最小值参数可以是任意类型

sum：获取和参数只能是数字类型

avg：获取平均值参数只能是数字类型

1）/*统计s_emp表中工资>1000的人数*/

Select count(salary) from s_emp wheresalary>1000;

/*统计当前用户下有多少张表*/

Select count(*) from user_tables;

/*统计s_emp表中的工资信息*/

Selectmax(salary),min(salary),sum(salary),avg(salary) from s_emp;

/*列出入职最早和最晚的员工信息*/

Select to_char(min(start_date),’yyyy-mm-dd’),to_char(max(start_date),’yyyy-mm--dd’)from s_emp;

2）组函数可以进行排重统计

distinct

select count(title) from s_emp; 25个

select count(distinct title) froms_emp; 8个

3）组函数对NULL的处理：忽略

select count(commission_pct) froms_emp; 5个

select avg(commission_pct) from s_emp; 13个

select id,count(id) from s_emp；报错

2、分组

1)分组子句的语法

Group by 分组标准（字段或表达式）

2）按照部门分组，统计每个部门的人数

select dept_id,count(id) from s_emp

group by dept_id;

可以放到分组语句中的字段要么是分组标准，

要么是用对应的组函数处理过的

/* 按照title分组，统计每个岗位的人数 */

select title,count(title) from s_emp

group by title;

/* 按照部门分组，统计每个部门的最高工资和平均工资 */

select dept_id,max(salary),avg(salary) from s_emp

group by dept_id;

3）按照部门分组，统计部门的平均工资，并筛选出平均工资大于1500的部门

Having 子句：分组后筛选出符合条件的组

select dept_id,avg(salary) avg_sal

froms_emp

where1=1

groupby dept_id

havingavg(salary)>1500

orderby avg_sal;

执行顺序：from where group by having select order by （from最先 order by 最后）

4)思考：按照部门和岗位分组，统计每个部门每个岗位的人数

select dept_id.title,count(id)

froms_emp

goupby dept_id,title

select d.name,count(e.id)

froms_emp e,s_dept d

wheree.dept_id=d.id(+)

goupby d.id,d.name

五、子查询

一条查询语句嵌套在另一条sql语句之内，执行的时候先执行内部的查询语句，把内部查询语句的结果作为外层sql语句的条件或者一部分

1、 Where子句

1）子查询结果是单值可以使用> 、=等运算符

/*列出和’Ben’职位相同的员工的信息*/

第一步：先查到‘Ben’的职位

select titlefrom s_emp where first_name=’Ben’;

-------查询结果’Warehouse Manager’

第二步：根据上一步的结果查询这一职位的员工

selectid,first_name,title from s_emp where tile=’Warehouse Manager’;

第三步：把上边两条语句结合起来

Selectid,first_name,titile form s_emp where title=

(select title from s_emp wherefirst_name=’Ben’);

2）子查询的结果不是单值，使用in（not in）这种符号

/*列出所有领导的信息*/

第一步：先查询出领导的编号

Select distinctmanager_id from s_emp;

查询结果 ---1,2,3,6,7,8,9,10，null

第二步：根据编号列出领导的first_name

Selectfirst_name from s_emp where id in(1,2,3,6,7,8,9,10，null);

第三步：把上面两条语句合并

Selectfirst_name from s_emp

where id in(Selectdistinct manager_id from s_emp);

/*列出所有普通员工的信息*/

Selectid,first_name from s_emp

Whereid not in(select distinct manager_id from s_emp where manager_id is not null);

2、 having子句

/*列出平均工资高于编号为42的部门的平均工资的部门*/

第一步：查询编号为42的部门的平均工资

select avg(salary) from s_emp where dept_id=42; ---1081.67

第二步：平均工资高于1081.67的部门

select dept_id,avg(salary)

from s_emp group by dept_id

havingavg(salary)>1081.67;

第三步：合并

selectdept_id,avg(salary)

from s_emp group by dept_id

having avg(salary)>（select avg(salary) from s_emp wheredept_id=42）;

3、 from字句

一条select语句会产生一个临时的内存表，可以在临时的内存表的基础上进行进一步的查询

selectdept_id,avg_sal from

(select dept_id,avg(salary) avg_sal from s_emp group by dept_id)

where avg_sal>1500;

六、表操作

6.1、创建表和删除表（ddl 数据定义语句）

表名_姓名的缩写_座位号

表名_tzh_00

6.1.1 创建表的语法

Creat table 表名（

字段1 数据类型，

字段2 数据类型，

…..

字段n 数据类型

）；

案例：创建一个员工表，包括员工的编号、姓名、入职日期

Creattable emp_tzh_00(

idnumber(3),

namevarchar2(20),

start_datedate

);

6.1.2 删除表

droptable 表名

droptable emp_tzh_00;

6.2、数据操作语句（dml）

6.2.1 insert

1）语法：

insert into 表名[(字段列表)] values(值列表)；

2）写入全部数据

insert into emp_tzh_00(id,name,start_date)

values(1,’Tom’，’14-SEP-92’);

insert into emp_tzh_00(id,name,start_date)

values(2,’Jerry’，’20-AUG-91’);

3）写入部分数据（没有给值的字段必须是允许为null的）

insert into emp_tzh_00(id,name)

values(3,’Ben’);

或insert into emp_tzh_00(id,name)

values(4,’Mark’,null);

6.2.2 delete

deletefrom 表名 [where 条件]

deletefrom 表名； --- 删除表中全部数据

/*删除编号为4的记录*/

deletefrom emp_tzh_00 where id=4;

6.2.3 update：更新

update表名 set 字段=更新值[，字段=更新值,……] [where 条件]；

/*更新id=3的员工的入职日期*/

updateemp_tzh_00 set start_date=’01-MAR-02’ where id=3;

commit;

updateemp_tzh_00 set id=4,name=’Ada’where id=3;

操作后查看表 select * from emp_tzh_00;

6.3、事务控制语句（TCL）

6.3.1 事务控制语句的含义

commit; ---提交事务

rollback; ---撤销没有提交的操作只能撤销没有提交的操作，提交后就不能撤销

savepointa; ----保存点

6.3.2 事务的四大特性

1）原子性

一个事务是一个不可分割的整体

转账：

A ----- > B 转账2000

Updateaccount set balance =balance -2000 where id=A; a

Update accountset balance =balance +2000 where id=B; b

if a&&b

commit;

else

rollback;

要么一起成功，要么一起失败

2）隔离性

3）一致性

4）持久性

6.3.3 部分成功，部分失败

drop tableemp_tzh_00

/*用select 语句创建表*/

create tableemp_tzh_00 as

select id,first_name,salary from s_emp;

delete fromemp_tzh_00 where id=25;

savepoint a;

delete fromemp_tzh_00 where id=24;

savepoint b;

delete fromemp_tzh_00 where id=23;

savepoint c;

delete fromemp_tzh_00 where id=22;

select * fromemp_tzh__00;

rollback to b;

commit;

select * fromemp_tzh_00;

七、约束

对表中字段的值的限制

7.1约束的种类

1）主键约束 primary key

不允许重复

不允许为null

一个表中只能有一个主键

2）唯一约束 unique

不允许重复、

允许为null 可以有多个null

一个表中可以有多个唯一约束

3）检查约束 check（表达式）

例如：check(age>=15 and age<= 50)

4）非空约束 not null

字段的值不允许为null

5）外键约束 foreign key references

表之间的关系

外键的值必须来源于所对应的主表中主键的值或者为null

练习：

1、创建一张表，Student_xxx_NN

学号数字主键

姓名字符串非空

出生日期日期

Creat table student_tzh_00(

stunonumber(4) primary key,

bamevarchar2(20) not kull,

birthdaydate

);

测试：

向表中写入三行数据（正确写入、学号重复的、姓名为NULL）

更新

删除

0 0