sql数据定义和数据查询

数据定义

1.定义基本表

create table <表名>

(<列名> <数据类型> [列级完整性约束],

[<列名> <数据类型> [列级完整性约束],]

[表级完整性约束])；

test1 创建学生S表

create table s

(Sno varchar2(10) primary key,

Sname varchar2(20) unique,

Ssex varchar2(10),

Sbirthdate date,

Smajor varchar2(40));

test2 创建课程C表

create course C

(Cno varchar2(10) primary key,

Cname varchar2(15) not null,

Cpno varchar2(10),  /*课程的选修课*/    

foreign key(Spno) references Course(Cno));  /*外键参考本表的Cno*/ [表级约束]

---

create course C

(Cno varchar2(10) primary key,

Cname varchar2(15) not null,

Cpno varchar2(10) references Course(Cno));  /*课程的选修课*//*外键参考本表的Cno*/[列级约束]

test3 创建选课SC表

creat table SC

(Sno varchar2(10),

Cno varchar2(10),

Grade varchar2(5),

primary key(Sno,Cno),

foreign key(Sno) references S(Sno),

foreign key(Cno) references C(Cno)); 

/*SC表主码是Sno和Cno共同组成，写为表级完整性约束；外码可表级/列级完整性约束*/

2.修改基本表

alter table <表名>

[add [column] <新列名> <数据类型>[完整性约束]]

[add <表级完整性约束>]

[drop [column] <列名> [cascade|restrict]]

[drop constraint <完整性约束> [restrict|cascade]]

[rename column <列名> to <新列名>]

[alter column <列名> type <数据类型>]

[modify <列名> <数据类型>];

示例:

①将S表中的Sbirthdate的数据类型由date改为字符型；

alter table S modify Sbirthdate varchar2(10);

②增加课程名称必须取唯一值的约束

alter table C add unique(cname);

3.删除基本表

drop table <表名> [cascade constraints]

//若不加[cascade constraints]即默认是受限删除(不能删除外键)；加上cascade constraints可以删除表上建立的索引、约束以及触发器和视图；

4.建立索引

create [unique][bitmap] index <索引名> on <表名> [<列名>次序asc升序(默认)/desc降序]

若[unique]和[bitmap]均不写就是normal索引； 

示例:

S表中为学生姓名建立唯一索引，C表中按课程名升序建唯一索引，SC表中按学号升序和课程号降序建唯一索引；

create unique index ind_sname on S(Sname);

create unique index ind_cname on C(Cname);  /*默认升序*/

create unique index ind_scno on SC(Sno asc,Cno desc); 

5.修改索引

alter index <原索引名> rename to <新索引名>

6.删除索引

drop index <索引名>

//删除索引时，系统会从数据字典中删去有关该索引的描述；

数据查询

1.SELECT语句

select <列名1> [<,列名2>]...from <表名/视图名>[,<表名/视图名>]...

[where <条件表达式>] 

[group by <列名1> [having <条件表达式>]]

[order by <列名2> [asc|desc]]

select子句中指定需要选择的列，可以是列名，可以是*，表达式或者函数

示例：

select * from s; //从s表中投影出所有列

select sno,sname,ssex,sbirth,college from s; //等同于第一条语句

select avg(grade) from sc; //sc表中所有成绩的平均值

select sno,sbirth,sbrith-365 from student;  //学生出生日期和年龄

select sno,sbirth,sbrith-365,sbirth as age from student;  //学生出生日期和年龄

2.单表查询 

1）选择表中的若干列

a.类型转换函数

to_char(x[,format])  to_date(x[,format])  to_number(x[,format])

①to_char测试

--dual表内永远有一条记录，可以用来调用函数，可以用来做计算器； 

②to_number测试

③to_date测试

(我莎姐亚运会女单夺冠的大日子!)

④extract函数测试

用来提取日期中的年月日--通常用来计算年龄

extract (year from current_date);  //提取年

extract (month from current_date);  //提取月

extract (day from current_date);  //提取日

⑤查询student中在1994年1月1号之前出生的学生

select * 
from student 
where sbirth < 1994/01/01;   //因为比较的左侧是date型右边是number型

方法一

select * 
from student 
where sbirth < to_date('1994/01/01','yyyy/mm/dd');

方法二

select *

from student

where to_char(sbirth,'yyyy')<'1994';

进行类型转换后的查询结果：

2）选择表中的若干元组

a.where子句查询条件

比较：= > < >= <= != <> !> !< not+比较运算符

//<>是不等于的意思

确定范围：between and【闭区间】; not between and

确定集合：in ; not in

字符匹配：like ; not like

空值：is null ; is not null

多重条件：and ; or ; not

where grade in (60,70,80);   //in语句

where grade not between 80 and 90;  //not between and语句 

 where not grade between 80 and 90;  //not+表达式语句 

b.字符匹配

属性列名 [not] like '<匹配串>' [escape <换码字符>]

%(百分号)：任意长度(长度可为0)的字符串

_(下划线):任意单个字符

示例:

①查询学生姓名是‘张三的学生’

select * from student

where Sname like '张三'；

②查询姓‘张’的学生

select * from student

where Sname like '张%'；

 ③查询姓‘张’的二字学生

select * from student

where Sname like '张__'；

④查询课程名以‘DB_'开头，且倒数第3个字符为i的详细情况

select * from course

where cname like 'db\_%i__';   //错误的，因为字符串内的信息区分大小写

where cname like 'DB\_%i__'escape '\';  //正确的

c.order by子句查询条件

select <列名> from <表名> order by <属性列> asc/desc

示例：

全体学生选修课程先按照课程号升序排列，同一个课程中降序排列；

select * from sc order by cno,grade desc;   /*默认cno是升序asc*/

d.聚集函数

count(*) //统计元组个数

count([distinct|all] <列名>)  //统计一列中值的个数

sum([distinct|all] <列名>)   //此列必须是数值型

avg([distinct|all] <列名>)   //此列必须是数值型

max([distinct|all] <列名>)

min([distinct|all] <列名>)

//distinct是去重的作用 all不去重--默认

示例：

查询学号为’20180003‘的学生选修课程的总学分数

select sum(Ccredit) from sc,course

where Sno='20180003' and SC.Cno=course.Cno;

聚集函数只能用在select子句和group by子句中的having短语中；

e.group by 子句

示例：

1.查询每个人选了几门课？

select count(cno)

from sc

group by sno;

2. 查询所有不及格的选课信息，统计出学生学号以及不合格的课程门数；

select sno,count(cno) from sc

where grade<60

group by sno;

3.查询所有不合格的选课信息，统计出学生学号以及不及格课程的总门数，筛选出不及格课程门数在3门以上的信息；

select sno,count(cno) 
from sc
where grade < 60
group by sno
having count(cno)>3；

 4.求各个课程号和选修该课程的人数；

select cno,count(sno)
from sc
group by cno;

 5.求平均成绩大于80分的学生学号和平均成绩；

select sno,avg(grade)
from sc
group by sno
having avg(grade)>=80;

错误❌：

select sno,avg(grade)
from sc

where avg(grade)>=80     //where子句不能作为聚集函数的条件表达式
group by sno；

3.连接查询

1）等值查询【内连接】

select sname,cno,grade
from student,sc
where student.sno=sc.sno;  //这两个表的联系是通过公共属性sno实现的

where student.sno<sc.sno;   //非等值连接，无意义

select sname,cno,grade
from student inner join sc  //内连接
on student.sno=sc.sno; 

select student.*,sc.*
from student,sc
where student.sno=sc.sno;   //等值连接没有去掉重复的属性列

2）自然连接 

去掉重复列的等值查询为自然连接查询

select *

from sc natural join student;  //自然连接

select *

from student natural join sc;   //自然连接

 3) 复合条件查询

查询课程号是‘2092508’并且成绩在90分以上的学生学号和姓名

select student.sno,sname,cno,grade

from sc,student

where cno='2092508' and grade>90;  ❌

修正：

select student.sno,sname,cno,grade

from sc,student

where student.sno=sc.sno   //连接谓词

and cno='2092508' and grade>90; 

4）自身连接--将表中的每一属性列 列成2列

自身连接时需要给表起别名以示区别，其实自身连接即做广义笛卡尔乘积；

【和intersect运算的巧合之处】

示例：

同时选择课程号是‘003’和‘005’的学生信息

select *
from sc a,sc b
where a.sno=b.sno and a.cno='003' and b.cno='005';

 5）外连接

将自然连接舍弃的悬浮元组保存在结果关系中，并在其属性值上填上空值，那么这种连接叫做全外连；而只将左边关系R中要舍弃的元组保留就叫做左外连；

全外连-- full [outer] join ...  on

左外连--left [outer] join ... on

右外连--right [outer] join ... on

select *

from student left join sc

on student.sno=sc.sno;    //学生没选课的信息置为空值

4.嵌套查询 

①相比连接查询，效率较高；

②嵌套查询的子查询不能使用order by子句；

③嵌套查询分为相关子查询[子查询的查询条件依赖于父查询]和不相关子查询[子查询的查询条件不依赖于父查询]

1）带有in谓词的子查询

in谓词引出的查询结果是一个集合；

父查询的where子句的属性列和in谓词引出的属性列一致；

先子查询再父查询；

select sname

from student

where sno in

(select sno 

from sc

where cno in

(select cno 

from course

where cname='Linux'));   //3层嵌套

2）带有比较运算符的子查询--返回单值

3）带有any/all的子查询--可以用聚集函数代替

结合比较运算符才可以引出子查询

>any 代表大于子查询的任意一个--大于最小的

>all   代表大于子查询的所有--大于最大的

示例：

查询选课成绩比选修了006号课程中任意一个成绩低的选课信息

select *

from sc

where grade < any 

(select grade

from sc

where cno='006') and cno <> '006'; 

//或者

select *

from sc

where grade  

(select max(grade)

from sc

where cno='006') and cno <> '006'; 

4）带有exists的子查询--只返回真/假

基本上exists谓词查询都是相关子查询；

基本上以上1)-3)都是不相关子查询；//例外如下

示例:

查询选修了001号课程的学生姓名

select *

from s

where sno in 

(select sno from sc where cn0='001');

//或者

select *

from s

where exists

(select *

from sc

where sc.sno=s.sno and cno='001');

例外：

找出每个学生超过他选修课程平均成绩的学号和课程号

select *

from sc sc1

where grade>

(select avg(grade)

from sc sc2

where sc2.sno=sc1.sno);      //相关子查询

5.集合查询 

union  //并集

intersect //交集

minus //差集

6.基于派生表的查询

子查询不仅可以放置在where子句中也可以放置在from语句中，被称为基于派生表的查询；

示例：

找出每个学生超过他选修课程平均成绩的学号和课程号

select sno,cno

from sc,(select sno sno_t,avg(grade) avggrade_t from sc group by sno)avg_table

where sc.sno=avg_table.sno_t and grade>avggrade_t;