数据库中的一些基本用法

1.SQL语言的动词

功能	动词
创建定义	creat
删除定义	drop
修改定义	alter
数据查询	select
数据插入	insert
数据更新	update
数据删除	delete
授予权限	grant
回收权限	revoke

2.SQL语言的基本数据类型

数据类型	含义
number(p,s)	数字类型；其中“p”是精度（数字总位数），最大38位；“s”是刻度范围（小数点的位数），可在84-127间取值。
char(n)	可存储字母符号，数字符号，以及特殊符号；n是字符长度（1-2000）缺省值为1，每个字符占用一个字节，如果实际输入字符不足n个，则用空格补充
varchar2(n)	变长字符型数据；n是字符长度（1-4000）,没有缺省值，必须设值，存储大小为输入数据的字节实际长度，而不是n个字节。一般情况下可以节省空间但仍然有不足之处，不能过度使用。一个汉字占两个字节。
text	最大长度为65535个字符
date	日期，默认格式：DD-MON-YY,如“01-1-04”表示2004年1月1日
boolean	布尔类型，仅仅可以表示true或false或null

3.代码演示

create table SC
	(Son char(5)/* primary key*/,
	Con char(2),
	Grade number(3),
	constraint SC_pk primary key(Son,Con),
	/*constraint Son_fk foreign key (Son) References S(Son),*/
	constraint Ck1 check(Grade>=0 and Grade<=100)
	);

4.修改基本表：
(1)、add:为表增加一新列，新列必须允许为空（除非有默认值）。

alter table S add Birthday date;

(2)、drop column:在表中删除一个原有的列。

alter table S drop column Birthday;

(3)、modify:修改表中原有列的定义。

alter table S modify (Age unmber);

(4)、add constraint:增加新的表级约束。
(5)、drop constraint:删除原有表级约束。

alter table SC drop constraint Ck1;

(6)、rename:重新命名，表名，视图序列或同义词名。

rename SC to Detail_SC;

(7)、删除表：只有表的创建者或具有drop any table权限的用户才能删除表格。

drop table S;

(8)、truncate table：截断基本表，删除表中的所有行并释放存储空间，只有表格的所有者或有delete table系统权限的用户才能截断表，虽然delete语句也可以删除表中的所有行，但它不能释放存储空间，且truncate更快。

truncate table S;

5.索引的创建和撤销
索引的使用是有代价的，维护表的同时要更新索引，所以有索引的表格的维护过程比较费时间。
索引的建立包括自动建立和手动建立：自动方式，在表的定义中，当定义一个primary key 或 unique 约束时，一个唯一索引被建立。

手动方式：
(1)、unique表示：规定索引的每一个索引的值只对应于表中唯一的记录。

create unique index S_idx on S(Sno);

(2)、每个列名后都可以指定ASC（升序）或DESC（降序），默认ASC。若索引有多个列决定，则首先以第一个列的值排序，该列值相同的记录，按下一列值排序，以此类推。

create unique index SC_idx on SC(Sno ASC,Cno DESC);

(3)、删除索引

drop index S_idx;

6、SELECT语句的基本格式

(1)、语句格式
select [all|distinct] <目标列表达式> [as <别名>][,<目标列表达式>[[as <别名>],…] from <关系名>[<关系别名>][,<关系名>[关系别名],…] [where <条件表达式>] [group by <用于分组的别名>] [having<对组再选择的条件表达式>] [order by <用于分组的列名> [ASC|DESC] [,<用于排序的列名>[ASC|DESC]],… ]

说明：
<1> all | distinct
all：查询出所有符合条件的分组（默认为all），distinct：查询出的分组中每组重复元组只输出一条。

<2> <目标列表达式>的一般格式

a) [<关系名>.]<属性列名>
即为 表名.列名， 若被查询各关系中，只有一个关系有该属性，则关系名可省略。

b) [<关系名>.] *
即为 表名. * ，若结果关系的各列正好是被查询的关系的所有属性时，则可用此格式。若被查询的关系只有一个，则上述关系名可省略。

c) 不含有任何被查询关系中属性名
最极端时，该表达式只由一个常量组成，则该列的列名和各元组的分量都为此常量。

d) [<关系名>.]<目标列表达式>
表达式中可以对目标列施加算术运算和函数运算：count()，max()，min()，sum()，avg()等。

<3> <别名>
a) 当目标列中含有特殊字符（如：±*/）或函数时，会使列变得模糊，为此取别名很有必要。查询结果关系中该列的列名就是此别名。如果别名中包含特殊字符（如#）时，或大小写敏感时，应该将别名放在双引号（“ ”）中。

b) 使用连字运算符
使用连字运算符“||”，可以进行列与列之间，列与算术表达式之间或者列与常数之间的连接，来创建一个字符表达式。
例如：

select Sname||Sdept as "student" from S;

此例中，Sname与Sdept 被连接合并到一个单元输出列，并且被指定为列别名student。

c) 使用文字字符串
文字字符串可以是一个数字或一个日期，对每个返回行打印一次，任意格式文本的文字字符串能够包含在查询结果中。
例如：

select Sname||'is long to'||Sdept as "student" from S;

(4)常用的查询条件

查询条件	谓词
比较	=,>,<,>=,<=,<>,NOT+上述比较运算方符
确定范围	between…and , not between … and…,
确定集合	in , not in
字符匹配	like , not like
空值	is null , is not null
多重条件	and , or

7、查询语句举例

select Sname, Spec, Age, from S where Age between 20 and 25;
select Sname from S where Sname not like '王%'；
select Sno, Cno, from SC where grade is null;
select Sno from SC where Grade in (80,85,90);

/*查询全体学生情况，查询结果按所在系名升序排列，
若是同一系的则按专业名升序排列，若又是同一专业的，按照年龄降序排列*/
select * from S order by Sdept, Spec, Age DESC;

（1）笛卡尔积：

select S.* , C.* from S,C;

（2）自身连接
连接操作不仅可以在两个表之间进行，也可以是一个表与其自身进行连接。

select X.Sno , X.Grade from SC X, SC Y where X.Grade>Y.Grade 
and X.Cno='1' and Y.Cno='1' and Y.Sno='00101';

（3）内连接

select * from a_table a inner join b_table bon a.a_id = b.b_id;

（4）左连接
查询每个学生以及他们的学习课程的情况，若某个学生没有选课，则只输出其基本信息，其选修信息为空。

select S.Sno, Sname, Sex, Age, Spec, Sdept, Cno, Grade
from S left outer join SC on S.Sno=SC.Sno;

（5）右连接

select * from a_table a right /*outer*/ join b_table b on a.a_id = b.b_id;

（6）分组查询
a) 集函数
为了增强检索功能，SQL提供了许多集函数，主要有：

集函数	功能
count( [ distinct / all ] * )	统计元组个数
count( [ distinct / all ] * <列名> )	统计一列中值的个数
sum( [ distinct / all ] * <列名> )	计算一列值的总和（此列必须是数值型）
avg( [ distinct / all ] * <列名> )	计算一列值的平均值（此列必须是数值型）
max( [ distinct / all ] * <列名> )	求一列值中的最大值
min( [ distinct / all ] * <列名> )	求一列值中的最小值

/*查询选修"2"号课程的学生人数和平均成绩*/
select count( Sno ), avg( Grade ) from SC where SC.Cno='2';

/*查询各门课程号以及相应的学生人数和平均成绩*/
select Sno , count( distinct sno), avg( Grade ) from SC group by Cno;

/*查询开设专业少于3个的系名以及专业数*/
select Sdept, count( distinct Spec) from S 
group by Sdept having count( distinct Spec)<3  ;

下面的网址写出了where与 having之间的区别用法
https://blog.csdn.net/jdjh1024/article/details/76647866

（7）嵌套查询
嵌套查询中先处理子查询后处理父查询，特别指出的是：子查询的select语句中不能使用order by子句，order by字句只能对最终的查询结果排序。

a) 不相关子查询

子查询的查询条件不依赖父查询，子查询一般写在谓语动词之后，有以下形式：

i) <属性名>[ not ] in ( select 子查询)；

查询与“李敏浩”选修同一专业的学生的姓名和学号：

select Sno ,Sname from S where Spec in
(select Spec from S where Sname ='李敏浩');

ii) <属性名>￥( select 子查询)；当用户确切知道查询结果为单值，用该形式，其中￥为比较运算符。

查询与“李敏浩”同系，切=且年龄大于“李勇”的学生信息：

select Sname, Age, Spec from S where Spec=
(select Spec from S where Sname='李敏浩') and 
Age>(select Age from S where Sname='李勇');

iii) <属性名>￥[ any | all ]( select 子查询) ；

查询其他系中比计算机系某一学生年龄小的学生学号，姓名和年龄：

select Sno,  Sname, Age from S where Age < any
(select Age from S where Sdept='计算机系') and Sdept<>'计算机系';

iiii) [ not ] between <下限> and<上限>；其中<下限>和<上限>也可以是子查询。

b) 相关子查询

查询条件表达格式：[ not ] exists ( select 子查询)
在此格式中，子查询不返回任何数据，只产生逻辑值，子查询查到元组，条件表达式为真，否则为假。子查询中的 <目标表达式> 一般为 *，因为没有实际意义。

查询没有选择任何课程的学生学号和姓名：

select Sno, Sname from S where not exists
( select * from SC where S.Sno=SC.Sno);

8、插入数据
SQL的数据插入语句 insert 通常有两种形式。一种是插入一个元组，另一种是插入子查询结果。后者可以一次插入多元组。

（1）插入单个元组
插入单个元组，按顺序在表后给出表中每个列名，在values后给出对应的每个列值。插入完整的新元组时，可省略表的列名，插入部分列值，必须在表名后给出要输入的列名。

例如：

insert into S values('02001','李强','男',21,'信息管理','信息系');
insert into SC(Sno,Cno) values ('01302','2');

（2）插入子查询结果
例如：

insert into Courseavg(Cno,Cavg)
	select Cno,avg(Grade) from SC group by Cno;

9、修改数据

update S set Spec ='计算机应用' where Sno = '2';
update SC set Grade = Grade+5 where Cno='2';
update SC set Grade = 0 where '数据库'=(select Cname from C where Cno=SC.Cno);

10、删除数据
删除数据要注意为了维护数据库的一致性，一般应用参照完整性约束定义或者触发器来完成删除数据工作，以下命令不建议使用：

delete from SC where '王丹'=(select Teacher from C where Cno=SC.Cno);

11、视图

视图的优缺点：https://blog.csdn.net/helloxiaozhe/article/details/80171793

12、授予权限与收回权限
（1）系统权限：all 或create database 或create table 或create view

grant create database to U1 with grant option;

with grant option指该用户可以将这种权限赋予其他用户的权限。

（2）对象权限：all 或 select 或 update

grant all on SC to U2;

13、SQL事务处理与游标概念
事务提交：commit 或 commit work;
事务回退：rollback 或 rollback work;事务一旦提交就不能再rollback了。

commit之前，只有当前被操作的记录被锁定，其他用户看不到更改，只有当前用户能看到更改，commit之后则更改完成。
若数据库突然故障或断开连接，数据库自动执行rollback。

设置保存点：savepoint <保存点的名称>
回退到保存点：rollback to savepoint <保存点的名称>
如果事务过长，设置保存点再commit，可以rollback 到保存点的位置。

游标概念略。

14、存储过程与触发器

存储过程例子：

create or replace procedure student_del( p1 in student.sno%type )
as
begin
	delete from student where Sno = p1;
end;

触发器例子：

create or replace trigger sc_upd
	after update on student
	referencing new as new old as for each row
begin
	if updating then 
		update sc set sno =:new.sno where sno=old.sno;
	end if;
end;