数据库查询进阶

基础查询数据：

select 字段名1,字段名2,...字段名n from 表名
select id,name,age from sn_info;
select id from sn_info;
--查询这张表所有信息--
select * from sn_info;
--带条件查询--
select * from sn_info where name='yoyo';
select * from sn_info where name='yoyo'and age=28;
1
2
3
4
5
6
7
8

两表查询：

create table sn_info(
	id number not null,
    stuno number not null,
    stu_name nvarchar2(50) not null,
    sex nvarchar2(50) not null
);
create table sn_score(
	id number not null,
    subject_id nvarchar2(50) not null,
    sub_name nvarchar2(50) not null,
    score number not null,
    stuno number not null
);
--此处省略添加语句--
--查询参加了考试的学生成绩--
select sn_info.stuno,sn_info.stu_name,sn_score.sub_name,sn_score.score
from sn_info,sn_score
where sn_score.stuno=sn_info.stuno
--Oracle中起别名 直接在字段名后面 加上别名就可以了--
select sn_info.stuno 学号,sn_info.stu_name  姓名,sn_score.sub_name 科目,sn_score.score 成绩
 --查询某一个人的考试成绩--
select sn_info.stuno 学号,sn_info.stu_name  姓名,sn_score.sub_name 科目,sn_score.score 成绩
from sn_info,sn_score
where sn_score.stuno=sn_info.stuno
and sn_info.stuno.stuno=1001
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

10/12数据库学习

三表查询

select sn_info.stuno,sn_stuinfo.username,sn_subject.sub_name,sn_score.score
from sn_info,sn_subject,sn_score
where sn_info.stuno=sn_score.stuno and sn_score.sub_id=sn_subject.sub_id
1
2
3

where只能从原始数据中筛选数据放到结果集中。

--like 模糊查询--
select * from sn_info where name like '%y%';
--between...and...--
select * from sn_info where stuno between 1002 and 1004;
select * from sn_info where stuno not between 1002 and 1004;
1
2
3
4
5

常用函数

• 分组函数

  • count

    select count(stuno) from sn_info;//统计个数
    select count(*) from sn_info where sex='女';//女的个数
    --及格人数有几个--
    select count(*) 及格人数
    from sn_info,sn_subject,sn_score
    whre sn_info.stuno=sn_score.stuno and sn_score.sub_id=sn_subject.sub_id and sn_score>=60
    1
    2
    3
    4
    5
    6

  • SUM

  • AVG

  • MAX

    select MAX(sn_score) 最高分
    from sn_info,sn_subject,sn_score
    whre sn_info.stuno=sn_score.stuno and sn_score.sub_id=sn_subject.sub_id
    1
    2
    3

  • MIN

• 其他函数

  • 字符串
  • 日期和时间

--数据排序  ASC 升序 DESC 降序--
select * from sn_info order by sn_score;//排序，默认升序排列
select * from sn_info order by sn_score desc;//降序排列
1
2
3

--内连接 Inner Join...on...--
select * from A inner join B on A.A_ID=B.B_ID;
select * from A.B where A.A_ID=B.B_ID;
--使用内连接 进行三表查询——-
select sn_info.stuno,sn_stuinfo.username,sn_subject.sub_name,sn_score.score
from sn_info inner join sn_score on sn_info.stuno=sn_score.stuno
inner join sn_subject on sn_subject.stuno=sn_score.stuno;
1
2
3
4
5
6
7

DDL 数据定义语言（Data Definition Language）用来定义数据库的对象 例如：数据表、视图、索引

DQL数据查询语句

DML数据处理语言（Data Manipulation Language） 如 增加 修改 删除

DCL 数据控制语言（Data Control Language）用于设置用户权限和控制事务语句

10/13数据库学习

Having

• 对汇总查询后的结果在此进行过滤
• having只能跟随group by 使用
• 注意和where的区别

select leibie from sn_test group by leibie;//需要数据中有重复的
--分组并将数量整合在一起--
select leibie SUM(shuliang) from sn_test group by leibie;
select leibie SUM(shuliang) from sn_test group by leibie order by SUM(shuliang) desc;//降序排列
--进行筛选后，再进行排序--
select leibie SUM(shuliang) from sn_test group by leibie having SUM(shuliang) order by SUM(shuliang) desc;
--序列(Oracle特有的)--
create sequence sn_id(序列名称) minvalue 1 maxvalue 9999
increment by 1//每增加一次，+1
start with 1;//从1开始
insert into sn_info(id,stuno,name,sex) values(sn_id.nextval,1001,'yoyo','女');//sn_id=1
insert into sn_info(id,stuno,name,sex) values(sn_id.nextval,1002,'coco','女');//sn_id=2
--向使用中的表追加字段进去--
alter table 表名 add 新字段的名称 字段类型;
1
2
3
4
5
6
7
8
9
10
11
12
13
14