SQL基础_创建一个学生表student

数据定义

SQL的数据定义功能: 表定义、视图和索引的定义

基本表的定义、删除、修改

定义基本表&约束

create table <表名>(
	<列名> <数据类型> [列级完整性约束],
	...
	<列名> <数据类型> [列级完整性约束],
	[表级完整性约束]，
	[表级完整性约束]
)
1
2
3
4
5
6
7

主键约束

例3-1 建立一个课程表Course，由课程号Cno、课程名Cname、先行课程号Ccpno、学分Ccredit四个属性组成。

create table course(
  //列级约束
  cno number(4) constraint pk_course primary key,
  cname varchar(20),
  cpno number(4),
  ccredit number(4)
)
1
2
3
4
5
6
7

例3-2 对例3.1改为表级约束

create table course(
  cno number(4),
  cname varchar(20),
  cpno number(4),
  ccredit number(4)，
  constraint pk_course primary key(con)
)
1
2
3
4
5
6
7

外码约束

例3-3 创建表的同时创建表级外码约束

create table sc(
    sno number(12),
    cno number(4),
    grade number(3)
    constraint pk_sc primary key(sno, cno),
    constraint fk_c foreign key(cno) references course(cno)
)
1
2
3
4
5
6
7

例3-4 对例3-3的表级外码约束改为列级

create table sc(
    sno number(12),
    cno number(4) constraint fk_c foreign key references course(cno),
    grade number(3) 
    constraint pk_sc primary key(sno, cno), 
)
1
2
3
4
5
6

CHECK约束

例3-7 添加一个CHECK约束来保证每个学生的考试成绩在0到100之间。

create table sc(
    sno number(12),
    cno number(4) 
    grade number(3) constraint ck_g check(grade>=0 and grade<=100),
    constraint pk_sc primary key(sno, cno),
    constraint fk_c foreign key(cno) references course(cno),
    constraint fk_s foreign key(sno) references student(sno)
)
1
2
3
4
5
6
7
8

create table test(
    id int,
    name varchar(10),
    sex varchar(10) check (sex in ('男','女')) //constraint <约束名>用不到时可省略
)
1
2
3
4
5

NOT NULL 约束

例3-8 指定Course表Cname取值非空

create table course(
    cno number(4) primary key,
    cname char(20) not null,
    cpno number(4),
    ccredit number(4)
)
1
2
3
4
5
6

UNIQUE 约束

例3-9 指定Course表Cname取值唯一

create table course(
    cno number(4) primary key,
    cname char(20) constraint u_cname unique,
    cpno number(4),
    ccredit number(4)
    //多列唯一可以设置全局约束，这里省去了 constraint <约束名>
    //unique(列名1，列名2)
)
1
2
3
4
5
6
7
8

例3-9 指定Course表Cname取值唯一而且非空

create table course(
    cno number(4) primary key,
    cname char(20) not null unique,
    cpno number(4),
    ccredit number(4)
)
1
2
3
4
5
6

DEFAULT 约束

建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，要求性别的默认值为男

create table course(
    sno number(12),
    sname char(20),
    ssex char(2) default('男'),
    sage number(3),
    sdept char(10)
)
1
2
3
4
5
6
7

修改基本表

alter table <表名>

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

//删除列
[drop column 列名]	

//添加约束
[add constraint <完整性约束名> <完整性约束>]	

//删除约束
[drop constraint <完整性约束名>]

//修改列
[modify <列名> <数据类型> [完整性约束]]	
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

添加和删除表中的列

例3-12 向Student表增加“入学时间”列，其数据类型为日期型。

alter table student
add s_entrance date
1
2

例3-13 向Student表中加入“入学时间”、“生源地”两列。

alter table student add(
	s_entrance date,
	s_sourse char(20)
)
1
2
3
4

例3-14 删除Student表中的“入学时间”列

alter table student 
drop columns s_entrance
1
2

例3-15 删除Student表中“入学时间”、“生源地”两列

alter table student drop(
	s_entrance,
	s_sourse
) 
1
2
3
4

修改列

例3-16 将Stuent表中性别Ssex这一列由原来的char(2)修改为char(8)，并赋默认值为‘女’

alter table student 
modefy ssex char(8) default('女')
1
2

删除约束

例3-17 将例3-7中定义的SC表中的检查约束ck_g删除

alter table sc 
drop constraint ck_g
1
2

例3-18 删除关于学生姓名必须取唯一值的约束

alter table sc 
drop unique(sname)
1
2

删除基本表

例3-19** 删除Student表

drop table student
1

索引的建立与删除

建立索引

  要想创建一个索引，首先需要一个表。在CREATE INDEX语句中，告诉数据库创建的新索引的名称是什么，要在哪个表上建立索引，以及包含哪些列。一般格式为：

create [unique][cluster] index <索引名> on <表名>(
    <列名> [<次序>],
    ...
	<列名> [<次序>]
)
1
2
3
4
5

  其中，<表名>指定要建索引的基本表的名字。索引可以建在该表的一列或多列上，各列名之间用逗号分隔。每个<列名>后面还可以用<次序>指定索引值的排列次序，包括ASC(升序)和DESC(降序)两种，缺省值为ASC。

  UNIQUE表明此索引的每一个索引值只对应唯一的数据记录。

  CLUSTER表示要建立的索引是聚簇索引。所谓聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织，聚簇索引在Oracle中只能在定义表的同时定义。用户可以在最常查询的列上建立聚簇索引以提高查询效率。在一个基本表上最多只能建立一个聚簇索引。建立聚簇索引后，更新索引列数据时，往往导致表中记录的物理顺序的变更，代价较大，因此对于经常更新的列不宜建立聚簇索引。

例3-20 为Course、SC表建立索引。其中Course表按课程名升序建唯一索引，Sno、Cno表按学号升序和课程号降序建唯一索引。

create unique index coucname on course(cname);
create unique index scno on sc(
	sno ASC,
    cno DESC
)
1
2
3
4
5

删除索引

例3-21 删除Course表的Cname索引。

drop index coucname;
1

数据查询

select [all|distinct]<目标表达式> [,<目标表达式>]
from <表名或视图名>
[where <条件表达式>]
[group by <列名1> [having <条件表达式>]]
[order by <列名2> [ASC | DESC]]
1
2
3
4
5

Select字句的<目标表达式>可以为：

• 算数表达式
• 字符串常量
• 函数
• 列别名

select 的基本应用

查询指定列

例3-21 查询全体学生的学号与姓名。

select sno, snmae
from student
1
2

查询全部列

例3-23 查询全体学生的详细记录。

select * from student
1

查询经过计算的值

例3-24从Student表中查找学生姓名，出生日期

select name, 2020-sage 
from student
1
2

例3-25查询学生的姓名和出生日期，出生日期用别名stu birthday显示。

select sname 2020-sage "stu birthday"
from student
1
2

制定列别名

例3-25使用列别名改变查询结果的列标题

select sname NAME, 'Year of Birth:' Brith, 2020-sage BIRTHDAY, LOWER(sdept) DEPARTMENT	
from student
1
2

关键字DISTINCT

DISTINCT关键字可从SELECT语句的结果中除去重复的行。如果没有指定DISTINCT,则默认为ALL，那么将返回所有行，包括重复的行。

例3-26查询所有课程的学分

select ccredit
from course;

//或者为
select all ccredit 
from course;
1
2
3
4
5
6

例3-27查询所有课程一共哪几种学分

select distinct credit
from course
1
2

使用where子句

WHERE子句后面跟的是条件的布尔组合，其结果是查询指定条件的元组。WHERE子句常用的查询条件如表所示。

比较

例3-30 查询所有年龄不等于20岁的学生姓名及其年龄。

select sname, sage
from student
where sage!=20
1
2
3

确定范围

例3-31 查询年龄在20至23岁之间的学生的姓名和年龄。

select sname, sage
from student
where sage between 20 and 30;
1
2
3

例3-32 查询年龄不在20至23岁之间的学生的姓名和年龄。

select sname, sage
from student
where sage not between 20 and 30;
1
2
3

确定集合

例3-33 查询年龄为18或者20的学生的姓名和年龄

select sname, sage
from student
where sage in (18,20)
1
2
3

字符匹配

• %表示多个字符
• _表示一个字符
• ESCAPE 将字符转意

例3-34 查所有姓张的学生的姓名、学号

select sname, sno
from student
where sname like '张%'
1
2
3

例3-35 查学号中倒数第二个数字为1的学生姓名和学号。

select sname, sno
from student
where sno like '%1_'
1
2
3

例3-36 查DB_Design开头课程的课程号和学分

select cno, credit
from curse
where cname like 'DB\_Design%' ESCAPE '\'
1
2
3

涉及空值的查询

例3-37 某些学生选修某门课程后没有参加考试，所以有选课记录，但没有考试成绩，下面来查一下缺少成绩的学生的学号和相应的课程号。

select sno, cno
from sc
where grade is null
1
2
3

多重条件查询

例3-39 查年龄在20岁以下的男同学姓名

select sname
from student
where ssex='男' and sage < 20
1
2
3

order by语句

例3-40 查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列。

select sno, cno
from sc
where cno=3
order by grade desc
1
2
3
4

聚集函数

• COUNT([DISTINCT|ALL] *) 统计元组个数；
• COUNT([DISTINCT|ALL] <列名>) 统计一列中值的个数；
• SUM([DISTINCT|ALL] <列名>) 计算一列值的总和(此列必须是数值型) ；
• AVG([DISTINCT|ALL] <列名>) 计算一列值的平均值(此列必须是数值型)；
• MAX([DISTINCT|ALL] <列名>) 求一列值中的最大值；
• MIN([DISTINCT|ALL] <列名>) 求一列值中的最小值。
  如果指定DISTINCT短语，则表示在计算时要取消指定列中的重复值。如果不指定DISTINCT短语或指定ALL短语(ALL为缺省值)，则表示不取消重复值。
  聚集函数遇到空值时，除count（*）外，都跳过空值而只处理非空值。
  where子句中不能用聚集函数作为条件表达式

例3-43 查询学生的平均年龄

select AVG(sage)
from student
1
2

group by和having

例3-44 查询每个学生选修的课程数。

select sno, count(cno)
form sc
group by sno
1
2
3

例3-45 查询平均分在80分以上的学生的学号及其选课数。

select sno, count(cno)
from sc
group by sno
	having avg(grade)>80
1
2
3
4