【软件测试】Oracle基础_DML篇_oracle问题软件测试-CSDN博客

本文链接：https://blog.csdn.net/Y1013768371/article/details/89071330

DML(data manipulation language)

范围：主要用来对数据库的数据进行一些操作，经常用到的有select、insert、delete、update等。

语法：select/insert/delete/update [...] [from] 表名;
SELECT 列名称 FROM 表名称
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
INSERT INTO 表名称(列1, 列2,...) VALUES (值1, 值2,....)
DELETE FROM 表名称 WHERE 列名称 = 值

操作数据(insert into/update/delete)

--删除已存在的表course、course_1、stu,若没有就忽略
drop table course;
drop table course_1;
drop table stu;

--学生信息表stu
create table stu(
  sid varchar(100) primary key,
  sname varchar2(32) not null,
  sex char(8),
  age integer,
  score number(5,2),
  admission_date date,
  habby clob 
);
 
--课程表(关联stu表)course
create table course(
cid varchar(100) primary key,
cname varchar(50) not null,
sid varchar(200),
foreign key(sid) references stu(sid)
);

--1.insert into 表名(列名1,列明2...) values (值1,值2,...);
	--全部列插入数据
	insert into stu values('201901','hg','男',18,99,to_date('2019-01-01','yyyy-mm-dd'),'music');
	insert into stu values('201902','llj','男',28,59,to_date('2019-01-02','yyyy-mm-dd'),'movie');
	insert into course values('201','network','201901');
	insert into course values('202','software','201902');
	--指定列插入数据
	insert into stu(sid,sname,score) values('201903','zly',20);
	insert into stu(sid,sname,sex,admission_date) values('201904','fbb','女',to_date('2019-01-04','yyyy-mm-dd'));
	insert into course(cid,cname) values('203','media');
	insert into course(cid,cname,sid) values('204',' ','201904');

--2.update 表名 set 列名= 值,列2 = 值2,... [where 条件];
	--单列更新数据
	update stu set sex = '女' where sid = '201901';
	--多列更新数据
	update stu set sname = 'lyl',age = 22 where sid = '201902';
	--全表更新数据
	update stu set sex = '男';
	update stu set age = score;

--3.delete from 表名 [where 条件];
	--复制数据结构创建表stu_1
	create table stu_1 as select * from stu;
	--删除单行数据
	delete from stu_1 where sid = '201901';
	--删除全表数据,效果等同于truncate table stu_1
	delete from stu_1;

查询

语法：

        select selection_list /*要查询的列名称*/
        from table_list      /*要查询的表名称*/
        [
         where condition       /*行条件*/
         group by grouping_columns /*对结果分组*/
         having condition        /*分组后的条件*/
         order by sorting_columns    /*对结果分组*/
        ]

1.select -查询

--通配符*
select * from stu;--查询所有
select sid from stu;--查询某列

--别名
select sid as 学号,sname 姓名 from stu;--列别名,可以加as
select s.sid,sname from stu s;--表别名,oracle不能加关键字as,表的别名只在当前select语句中有效

--列运算+ - * /
select sname 姓名,age+5 年龄,score*1.5 分数 from stu;

--拼接字段 || concat()
select sid||''||sname 拼接 from stu;
select concat(sid,sname) 拼接 from stu;

--去除重复行distinct
select distinct(sex) 性别 from stu;

--行号rownum(伪列),rownum放在最底层效率最高，注意点详见：https://blog.csdn.net/Y1013768371/article/details/89189788
select rownum,stu.* from stu;--为stu各行加上行号,stu.必须加上
select * from stu where rownum <= 3;--查询前三行数据,带条件,后面详述
select * from (select rownum rn,stu.* from stu where rownum <= 3) s where s.rn >= 2;--查询第二到三行的数据,子查询后面详述

--伪表dual
select 1/2 from dual;

1/2
---
0.5

2.from -目标

--1)单表查询
	select * from stu;

--2)多表联合查询:子查询的效率没有内连接或外连接效率高,原因是子查询内部表全表查完再查外表全表，而连接查询只查两个表中具有相同条件的数据

	--2.1)子查询(嵌套查询):由内而外,先执行子查询再到外层查询
	/*
	语法：
	select column_name1,column_name2
	from table1,table2
	where column_name operator(in...)
	(select column_name1,column_name2
	from table1,table2
	[where])
	*/		
	select * from stu where sid in(select sid from course where cname = 'network');--查询课程名为network的学生信息

	--2.2)内连接[inner join on],inner可以不加
	/*
	语法:
	select column_name1,column_name2... from table1 [inner] join table2
	on table1.column_name=table2.column_name [where];
	*/
	select stu.sid,sname,score from stu join course 
	on stu.sid = course.sid;--返回course表中与stu表的sid一样的sid、sname、score列的数据

	--2.3)等值连接：所有的等值连接都可以用内连接,从集合的角度来说,等值连接是内连接的子集,因为内连接还可以有不等的连接条件
	/*
	语法:
	select column_name1,column_name2... from table1,table2
	where table1.column_name=table2.column_name;
	*/
	select stu.sid,sname,score from stu,course 
	where stu.sid = course.sid;--返回course表中与stu表的sid一样的sid、sname、score列的数据

	--2.4)外连接
		--2.41)左链接：[left outer join on],outer可以不加,返回左表中所有的行,右表没有与之匹配的行用null表示
		/*
		语法:
		select column_name1,column_name2... from table1 left join table2
		on table1.column_name=table2.column_name [where];
		*/
		select * from stu left join course on stu.sid = course.sid;--查询所有学生的选课情况 

		--2.42)右连接：[right outer join on],outer可以不加,返回右表中所有的行,左表没有与之匹配的行用null表示
		/*
		语法:
		select column_name1,column_name2... from table1 right join table2
		on table1.column_name=table2.column_name [where];
		*/
		select * from stu right join course on stu.sid = course.sid;--查询课程对应的学生情况 

		--2.43)满外连接(全连接)：[full outer join on],outer可以不加,返回两张表所有行,除返回满足条件的行之外还返回不满足条件的行
		/*
		语法:
		select column_name1,column_name2... from table1 full join table2
		on table1.column_name=table2.column_name [where];
		*/
		select * from stu full join course on stu.sid = course.sid;--查询所有学生的信息和选课情况 

	--2.5)组合查询:将多个查询结果合并起来,即多个SQL语句所产生结果在同一列
	--union:对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
	select score from stu where score = 59
	union
	select age from stu;
	--union all:对两个结果集进行并集操作，包括重复行，不进行排序
	select score from stu where score = 59
	union all
	select age from stu;
	/*
	注：
	1，Union可以对字段名不同但数据类型相同(兼容也可以)的结果集进行合并；
	2，如果字段名不同的结果集进行Union,那么对此字段的Order by子句将失效。
	*/

3. where -过滤

--比较条件= != <>(不等于) < <= > >=  
select * from stu where score <> 99;--分数不为99的所有人的数据

--连接范围[not]between and,包含边界值，等价于 大于等于...小于等于...
select * from stu where age between 60 and 100;--年龄在60-100的所有人的数据
select * from stu where score not between 60 and 100;--分数不在60-100的所有人的数据

--离散查询[not]in
select * from stu where sname in('lyl','zly');--查询出姓名为lyl和zly的所有信息 
select * from stu where age not in(select age from stu where score < 60);--根据年龄查询出分数大于60的人的所有信息 

--模糊查询[not]like,%表示0个或多个字符,_代表一个字符
select * from stu where sname like 'z%';--查询出姓名首字母为z的所有信息
select * from stu where sname like '_y%';--查询出姓名第二个字母为y的所有信息
select * from stu where sname like '%b%';--查询出姓名含有b的所有信息

--空值查询is [not] null,为空就是不存在,不确定,为0或空格都不是为空
select * from course where sid is null;--查询没有学生选修的课程信息
select * from course where sid is not null;--查询选修了课程的学生信息

--算数运算查询优先级:比较符(= != <>(不等于) < <= > >=)->is[not]null->like->[not]in->[not]between->not->and->or(最低)

4.group by -分组

语法：select column_name, function(column_name) from table_name group by column_name;

--用于结合合计函数，根据一个或多个列对结果集进行分组,having对分组之后的数据进行筛选
--特征：出现每个，各个，所有等词汇时
select cname,count(sid) from course group by cname;--查询各课程的选修人数,count()后面详述
select cname from course group by cname having count(sid) = 0;--查询无人选修的课程名

5.order by -排序

--语句用于根据指定的列对结果集进行排序,默认升序，必须放在select语句结尾
select * from stu order by sid asc;--按学号对表进行升序排序
select * from stu order by score desc;--按成绩对表进行降序排序
insert into stu(sid,sname,score) values('201905','lyf',59);--插入一条记录，用于验证
select * from stu order by score desc,sid asc;--按成绩对表进行降序排序,成绩相同则按学号升序排序

6.聚合函数(合计函数)

--count()返回记录总数
select count(*) from stu;--查询学生的个数
select count(sid) from course;--查询已选修课程的学生数
--查询被选修的课程名及学生数
select cname,count(sid) from course group by cname having count(sid) > 0;

--sum()返回相应列值的总和
select sum(score) from stu;--查询班级学生总分

--avg()返回数值列的平均值,null值不参与计算
select avg(score) from stu;--查询班级学生平均分

--max()返回数值列的最大值,null值不参与计算
select max(score) from stu;--查询班级最高分
--查询班级最高分的学生学号姓名,分数
select sid,sname,score from stu where score = (select max(score) from stu);

--min()返回数值列的最小值,null值不参与计算
select min(age) from stu;--查询班级最小年纪
--查询班级最小年纪的学生学号姓名,分数
select sid,sname,score from stu where score = (select min(age) from stu);

注：在使用group by分组时
        1)在select子句后面只可以出现两种表达方式,即聚合函数和进行分组的列名
        2)在select子句中出现的列名必须是要进行分组的列名,除此之外
           出现的其他列名都是错误的,但是group by后面出现的列名可以不出现在select子句中
        3)默认情况下,将按照group by子句指定的分组列进行升序排序,
           如果需要重新排序,可以使用order by子句指定新的排列顺序

补充

--注释：
    1)单行注释：--内容
    2)多行注释：/* 内容 */
--Oracle中语句的执行顺序(详见：https://blog.csdn.net/Y1013768371/article/details/89306814)：
    from->on->join->where->group by->having->select->distinct->order by(递减)
--where后面不能用聚合函数作为筛选条件！！！
--查询语句分解
    查询课程表中科目是高数的学生的学号
    --第一,查的内容   学号
    --第二,从哪查     课程表
    --第三,查询条件   科目='高数'
    --select 后跟的是查询内容,from后跟查询地点
    --查询出的结果都是一个表
    3）oracle与mysql的区别之一
    --mysql是直接在sql语句中写"select... from ...where...limit  m, n",关键字limit，limit m,n 其中m表示起始位置的下标，下标从0开始。n表示要显示的条数，比如要查询一个表的第2到5条数据。
    select * from emp limit 1,4;
    --而oracle则是需要用到伪列ROWNUM和嵌套查询

参考原文：