001oracle基本语法_oracle查询姓王的学生-CSDN博客

本文链接：https://blog.csdn.net/qq_55860753/article/details/128132612

1.创建表，插入，修改，删除

-- 数据类型
-- 数值类型： NUMBER(X[,Y]) : X代表整个数据的长度,  [,y]可省略代表小数的位数,如果超出范围会四舍五入  
-- 时间/日期两类型: DATE / TIMESTAMP  : 如果需要展示时分秒、上午、下午 必须用 TIMESTAMP
-- 字符串类型： varchar2(n) 变长/  char(n) 定长 
-- 数据库中，每条指定以分号为结束  ， 运行快捷键 F9
-- 查询当前系统的时间 sysdate     
-- 系统默认展示日期格式：   09-11月-22  day-motn月-year
SELECT sysdate from dual;
-- 修改默认日期格式，此sql只在一次session生效
ALTER session SET NLS_DATE_FORMAT = 'yyyy-mm-dd';
-- 创建表 AA ,字段 b 类型date 
CREATE TABLE BB(b DATE);
-- 添加数据
-- 注：添加数据时，先执行格式转换
INSERT INTO BB VALUES('2022-10-11');
INSERT INTO BB VALUES(sysdate);
INSERT INTO BB VALUES('20220505');


-- 创建表语法：
-- 建表语句中最后一行语句不要加逗号
-- CREATE TABLE 表名
--（
--  字段名1 数据类型 [列级约束] ,
--  字段名2 数据类型 [列级约束] ,
--  字段名3 数据类型 [列级约束] ,
--  ...
--  [表级约束]
-- );
CREATE TABLE student(
  sunm number(10),
  snmae char(20),
  ssex char(4),
  sbirth date
);
-- 查看表结构信息
desc student;
-- 1.修改表名称
-- ALTER TABLE 原表名 rename to 新表名;
ALTER TABLE student RENAME to student_a;
-- 2.添加表字段
-- ALTER TABLE 表名 ADD(字段名1 类型,字段名2 类型....);
ALTER TABLE student_a ADD(sage number(3),saddress varchar2(100));
-- 3.修改字段类型
-- ALTER TABLE 表名 MODIFY (字段名 新数据类型);
ALTER TABLE student_a modify (snmae varchar2(20));
-- 4.修改字段名称
-- ALTER TABLE 表名 RENAME COLUMN 原字段名 TO 新字段名;
ALTER TABLE student_a RENAME COLUMN SUNM TO SNUMID;
-- 5.删除字段
ALTER TABLE student_a DROP COLUMN B;
ALTER TABLE student_a DROP COLUMN C;
-- 6.删除数据表
DROP TABLE student_a;

-- 字段信息
-- 1.删除某一行
DELETE FROM STUDENT WHERE ID=1;
-- 2.修改表信息
UPDATE STUDENT SET NAME='A' WHERE ID=1;

2.约束（非空，默认，外键，唯一）

-- 完整性约束
-- 1.NOT NULL : 非空约束,定义 该列的值不允许为空
-- 2.DEFAULT : 默认值约束,当该列未填入数据时,会使用默认值
-- 3.PRIMARY KEY: 主键约束, 该列的值不能为空且不能重复,一张表有且仅有一个主键,一般都是给数值类型且长度较短
-- 4.UNIQUE : 唯一约束, 该列的值最多有一个空值且不能重复

-- 对于已经创建好的表添加非空/默认约束：   ALTER TABLE 表名 MODIFY 字段 约束类型;
-- 对于已经创建好的表添加主键/唯一约束：   ALTER TABLE 表名 ADD constraint 约束名  约束类型(字段名);

ALTER TABLE test_b  
add CONSTRAINT fk_testId 
FOREIGN KEY (test_aId) REFERENCES test_a(ID);
--其中 fk_testId 是外键名称

-- 学生表中添加ac（sno）外键约束
ALTER TABLE STUDENT 
ADD CONSTRAINT AC 
FOREIGN KEY (SNO) REFERENCES AC(SNO);


-- 使用
CREATE TABLE ac(
  sno number(11) primary key,
  name varchar2(20) not null,
  age number(10) default 18,
  sex varchar2(5) default '男',
  identicard number(18) unique
  -- constraint 约束名 约束类型(字段名) 约束名默认规则：pk_字段名 fk_字段名
);

-- check约束
CREATE TABLE course
(
Cnum CHAR(8) PRIMARY KEY,
Cname VARCHAR2(50) NOT NULL,
Cterm NUMBER(1),
Chour NUMBER(2) DEFAULT 48,
Cscore NUMBER(1) DEFAULT 3,
CONSTRAINT Course_hour_ck CHECK(Chour >=8 and Chour<=80),
CONSTRAINT Course_score_ck CHECK(Cscore>=1 and Cscore<=10)
);
-- 直接加
create table student(
id number(11),
name varchar2(10),
class varchar(20),
sex char(4) check(sex in ('woman','man'))
);


-- 复合主键，如果没有id，为了确定唯一学生，可以用多个字段来表示主键
CREATE TABLE SC
(
Snum CHAR(10),
Cnum CHAR(8),
Grade NUMBER(3),
CONSTRAINT SC_PK PRIMARY KEY(Snum,Cnum),
CONSTRAINT SC_FK1 FOREIGN KEY(Snum) REFERENCES student(Snum),
CONSTRAINT SC_FK2 FOREIGN KEY(Cnum) REFERENCES course(Cnum),
CONSTRAINT SC_Grade_ck CHECK(Grade>=0 and Grade<=100)
);

3.规范，查询

-- 注：规范

  SELECT    * | 列名
  FROM      表名 [别名]
  WHERE     条件表达式 (多个条件用and | or 拼接)
  GROUP BY  分组列    [HAVING  条件表达式]
  ORDER BY  排序列   [ASC 升/默认 | DESC 降序]
  ROWNUM    限制行数 ;

-- 1.查询年，月，日
-- 注：注意括号
-- yyyy MM DD hh24 mm ss
select
 
to_number(to_char(t.hiredate,'yyyy'))as 入职年,
 
to_number(to_char(t.hiredate,'MM'))as 入职月，
 
to_number(to_char(t.hiredate,'DD'))as 入职天
 
 from emp t;

-- 2. 获取年月日
select 

extract(year from date'2011-05-17') year,

extract(month from date'2011-05-17') year,

extract(day from date'2011-05-17') year 

from dual;

-- 基本查询语句
-- SELECT 列名      多个列名用逗号分隔,如果要查询所有列用 * 代替
-- FROM   表名;
-- 1. 查询学生表中所有的数据
select * from student;
-- 2. 指定查询部分列
select sname,ssex from student;
-- 3. 可以对查询的列进行运算
--  concat字符串的拼接函数    extract(year from 列名)  获取列的年份值
select concat('姓名:',sname), extract(year from sbirth) from student;
-- 4. 取别名 / 修改列标题
--  列名 [as] 别名
select sname as 姓名, extract(year from sbirth) 出生日期 from student;
-- 5. 去除重复数据行  DISTINCT 列名
select distinct ssex from student;

-- 条件查询
--  多个条件表达式之间用  and|or 拼接   not 对条件取相反答案
-- 1. 查询SC成绩表中分数高于90的学生学号,成绩
select snum 学号,grade 分数 from sc where grade > 90;
-- 2. 查询sc表中 科目编号为 '080106  '且成绩高于85的记录
select * from sc where cnum='080106' and grade > 85;
-- 3. 范围查询:     between 表达式1 and 表达式2  
--    查询sc表中成绩在70~90之间的学生编号和成绩
select snum,grade from sc where grade >=70 and grade <= 90;
select snum,grade from sc where  grade not between 70 and 90;
--    多个字符串数据匹配时,用    in
--    查询王斌 、白小梅、李淑慧、罗光军   学生信息
select * from student where sname='王斌' or sname='白小梅' or sname='李淑慧' or sname='罗光军';
select * from student where sname in('王斌' ,'白小梅','李淑慧','罗光军');

-- 4. 模糊查询：LIke 搭配通配符一起使用
--  % : 匹配0~N个任意字符
--  _ : 匹配1个任意字符
-- 查询学生表中姓王的学生信息
select * from student where sname like '王%';
-- 查询学生表中名字里含有小的学生信息
select * from student where sname like '%小%';
-- 查询学生表中姓王且只姓名共2个字的学生信息
select * from student where sname like '王_';


-- 5. 限制行数查询  ROWNUM
-- 查询学生表中前10条记录
select * from student where rownum<11;

-- 6. 连接查询
-- 查询学生表信息和对应的选课信息
-- select 表名.列名  ： 当多张表中出现了重复列名,通过表名.列名的方式区分
select student.*,sc.* from student,sc where student.snum = sc.snum and ssex = '男';
select sname,ssex,grade,student.snum from student,sc where student.snum = sc.snum;

4.JOIN关键字链表查询

-- 基本语法：FROM 表名1 JOIN 表名2 ON 表1.主键=表2.外键

-- 查询学生表信息和对应的选课信息  
-- [INNER] JOIN 
select student.*,sc.* 
from student 
inner join sc on student.snum = sc.snum
where ssex = '男';

-- 查找选修了“计算机导论”课程且成绩在80分以上的学生学号、姓名、课程号、课程名及成绩
-- sc是中间表
SELECT student.snum 学号,sname 姓名,course.cnum 课程号,cname 课程名,grade 成绩
FROM student 
INNER JOIN sc ON student.snum = sc.snum
INNER JOIN course ON course.cnum = sc.cnum
WHERE grade >= 80 and cname='计算机导论';


-- 注：group by 分组后，要在select后面加上分组后面的类容，不然会报错，不在分组后面的类容不能加在group by 后面
-- 三表连接： 查询'软件工程系'的学生参加'操作系统' 考试的 最高分
select MAX(grade) 最高分,sdept 系别 
from sc 
inner join student s  on sc.snum = s.snum 
inner join course c on c.cnum = sc.cnum 
where cname='操作系统' group by sdept HAVING sdept='软件工程系';

-- 两表连接： 查询各系的平均分并且按照平均分降序排列
-- rount(a,b)a:数值，b:小数点后面的位数范围
select ROUND(avg(grade),2) 
from sc 
inner join student on sc.snum = student.snum 
GROUP BY sdept order by avg(grade) DESC;

5.左外连接(left outer join)，右外连接(right out join)，完全外连接(full out join)，交叉连接（cross join)--笛卡尔积

-- 左外连接（ left outer join） : 以左表数据为主,左表数据完全展示;右表能匹配则展示不能匹配用NULL代替
-- 右外连接（ right outer join） : 以右表数据为主,右表数据完全展示;左表能匹配则展示不能匹配用NULL代替
-- 完全外连接（ full outer join） : 两张表的数据都能完全展示,不能匹配用NULL代替
select grade,s.snum,sname from student s left outer join sc
on s.snum=sc.snum;
select grade,s.snum,sname from sc,student s where s.snum=sc.snum;

-- 交叉连接（cross join ） : 结果为笛卡尔积,结果数据行为两张表的乘积
select student.*,sc.* from sc cross join student; -- 总数据行就是 31*98


-- 合并结果集（UNION ALL） ：  SELECT ....  UNION ALL SELECT ....  [UNION ALL SELECT .... ]
-- 多个SELECT语句要查询的列,名称、顺序、类型必须完全一致
select sname,ssex from student where snum = '1506107'
union all 
select sname,ssex from student where snum = '1506106';
-- 返回的结果，显示在一张表上

6.子查询

-- 1.查询选修了课程号为“080104”的课程的学生信息
-- 查询学生信息
-- select * from student where snum 选修080104课 ;
-- 查询了选修080104课的相关信息
-- select * from sc where cnum='080104';
-- 组合
-- 注：子查询右多调语句用in，=只用于单个值比较
select * from student where snum in(select snum from sc where cnum='080104');


-- 2.连接查询速度快，比子查询效果好
select * from student join sc on student.snum=sc.snum where cnum='080101';


-- eg.查询未选修离散数学的信息,sc是中间表
select * from student where snum in
(select snum from sc where cnum not in 
(select cnum from course where cname != '离散数学'));

<all(子查询)	<min()
>all(子查询)	>max()
<any(子查询)	<max()
>any(子查询)	>min()

-- 3.比较查询
-- 查询年龄比软件工程系都小的学生信息
SELECT *
FROM student
WHERE sbirth > ALL
  (SELECT sbirth FROM student WHERE sdept='软件工程系'
  );
  
-- 4.年龄比较
-- trunc:小数位取整
-- months_between():返回两个日期之间的月份数
SELECT TRUNC(months_between(sysdate,sbirth)/12) AS age
FROM student;

-- 4.2
SELECT TRUNC((TO_CHAR(sysdate,'yyyyMMdd')-TO_CHAR(sbirth,'yyyyMMdd'))/10000)AS age
FROM student;

例题--存在的问题

-- 查询课程号080101成绩不低于080102最低成绩学生的学号

-- 两个子查询比较，报错
-- 要以一个为基础，这样比较不能确定是哪个，如：2>1满足条件就行
SELECT snum
FROM sc
WHERE (SELECT grade FROM sc WHERE cnum=080101)>
  all(SELECT grade FROM sc WHERE cnum=080102
  );

-- 
select snum
from sc
where cnum=080101 and grade > all(SELECT grade FROM sc WHERE cnum=080102);

-- 查询参加"c语言程序设计"(course)" 最高分"(sc) 的学生(student)信息
SELECT *
FROM student
WHERE snum=
  (SELECT snum
  FROM course,
    sc
  WHERE course.cnum=sc.cnum
  AND course.cname ='C语言程序设计'
  AND sc.grade     =
    (SELECT MAX(grade)
    FROM course,
      sc
    WHERE course.cnum=sc.cnum
    AND course.cname ='C语言程序设计'
    )
  );

7. 视图（根据自己需求，把字段凑在一起，view改，或表改，都该）

-- 视图:
-- 视图是一张虚表,是一个查看数据的窗口,不是存储数据的表;
-- 对视图可以实现创建、查看、修改、添加、删除操作,此时数据会影响基表;
-- 对基表做增删改查数据,也会随之反应到视图

-- 1. 创建视图CREATE  或 修改视图[OR REPLACE] VIEW 视图名 AS Select 语句....
-- 创建一个视图,包括计算机系所有的学生姓名、学号
CREATE OR REPLACE VIEW vi_student 
AS
SELECT sname,snum,ssex from student where sdept = '计算机系';
-- 创建一个视图,包括计算机系所有的学生姓名、学号、课程编号、成绩
CREATE OR REPLACE VIEW vi_computer
AS
SELECT s.sname,s.snum,cnum,grade from student s join sc on s.snum = sc.snum
where sdept = '计算机系';
-- 源表可以是基表或视图
-- 创建一个视图,包括计算机系学号(num)和平均成绩(score_avg)
create view vi_avgscore(num,score_avg) AS
SELECT snum,AVG(grade) from vi_computer group by snum;

-- 2.查看视图
select * from vi_avgscore;
-- 查询计算机系80分以上的学生学号和平均成绩
select num,score_avg from vi_avgscore where score_avg>80;
-- 查看视图的定义
DESC vi_avgscore;

-- 3.视图插入数据
-- 给视图 vi_student 添加数据,要注意基表中非空列字段必须给值,如果不满足会报错
insert into vi_student values('张无忌','008','男');
select * from student;

-- 4.修改视图数据,只能修改视图所包含的表和数据
update vi_student set sname = '白眉鹰王' where snum = '1506109';

-- 5.删除视图数据
delete from vi_student where sname ='王程程';

-- 6.删除视图
DROP VIEW vi_student;

8.分页

-- select * from students limit (n-1)*m, m
-- 说明:
--  n表示显示第几页的数据
--    页数   12/3 = 4
--    n = 1, 2, 3, 4
--  m表示每页显示多少条数据
--    m = 3, 每页显示3条数据

-- 【注】后面分页显示直接加每页显示的条数即可

-- n = 1, m = 3
-- (1-1)*3 = 0
select * from students limit 0, 3;

-- n = 2, m = 3
-- (2-1)*3 = 3
select * from students limit 3, 3;


-- n = 3, m = 3
-- (3-1)*3 = 6
select * from students limit 6, 3;

-- n = 4, m = 3
-- (4-1)*3 = 9
select * from students limit 9, 3;


-- 1、 查询第4到第6行学生信息
select * from students limit 3, 3;
-- 2、 每页显示5条数据， 显示每一页的数据
select * from students limit 0, 5;
select * from students limit 5, 5;
select * from students limit 10, 5;
-- 3、 已知总记录数和每页显示条数， 求总页数？
-- 总数为12， 每页显示3个数据，12/3=4页
-- 总数为12， 每页显示5个数据，如果除不尽，12/5取商 + 1