Oracle 11g学习--2.基本操作查询

一、插入数据

insert into Student values (201215122, '刘晨', '女', 19, 'CS');
insert into Student values (201215123, '王敏', '女', 18, 'MA');
insert into Student values (201215125, '张立', '男', 19, 'IS');
insert into Course (Cno,Cname,Ccredit) values (2, '数学', 2); 
insert into Course (Cno,Cname,Cpno,Ccredit) values (3, '信息系统', 1, 4); 
insert into Course (Cno,Cname,Cpno,Ccredit) values (4, '操作系统', 6, 3); 
insert into Course (Cno,Cname,Cpno,Ccredit) values (5, '数据结构', 7, 4); 
insert into Course (Cno,Cname,Ccredit) values (6, '数据处理', 2); 
insert into Course (Cno,Cname,Cpno,Ccredit) values (7, 'PASLCAL语言', 6, 4); 
insert into SC (Sno,Cpno,Grade) values (201215121, 2, 85); 
insert into SC (Sno,Cpno,Grade) values (201215121, 3, 88); 
insert into SC (Sno,Cpno,Grade) values (201215122, 2, 90); 
insert into SC (Sno,Cpno,Grade) values (201215122, 3, 80); 

二、表的增、删、改

增：

create table tuser(
    name varchar2(10) primary key,
    age number(10),
    sex varchar2(10)
);

加"user_date"列：

alter table tuser add user_date date;

改：

# 添加grade分数字段
alter table tuser add grade varchar2(10);
# 将grade分数字段从char改为int
alter table tuser modify grade int;
删：

```sql
drop table tuser;

三、表的查询

1. 查询全体学生的姓名以及出生年份

select Sname, 2014-Sage from Student;

2. 查询全体学生的姓名，出生年份和所在的院系，小写字母表示系名

select Sname,'Year of Birth:',2014-Sage,lower(Sdept) from Student;

3. 查询选修了课程的学生学号，消除重复行

select distinct Sno from SC;

4. 查询所有刘姓学生的姓名、学号、性别

select Sname,Sno,Ssex from Student where Sname like '刘%';
#查询所有不是刘姓学生的姓名、学号、性别
select Sname,Sno,Ssex from Student where Sname not like '刘%';

5. 查询名字中第二个字为’晨’的学生的姓名、学号、性别

select Sname,Sno,Ssex from Student where Sname like '_晨%';

6. 查询选修了3号课程的学生成绩，降序排序

# 升序asc,降序desc
select Sno,Grade from SC where CPno=3 order by Grade desc;

7. 求各个课程号以及选课人数

select CPno,count(Sno) from SC group by CPno;

8. 查询选修了三门课及以上的学生学号

#group by 按某一列或多列的值分组
#having指定筛选条件
#where子句不能用聚集函数做表达式
select Sno from SC group by Sno having count(*)>2;