2023/7/20 关系型数据库+sql语句复习整理（1）

一、DDL（数据定义语言）

1、create创建对象

（1）命令格式

创建表

create table 表明(
	列明 列类型[(类型长度) 列级约束条件],
	...
	表级约束条件
);

创建视图

create view 视图名 as
DML语句

（2）pl/sql语句的延申

创建触发器

create or replace trigger 触发器名字
  before | affter | instead of
  具体操作关键字
  on  视图名或表明
  for each row [when (其他条件)]
declare
  全局变量声明
begin
  触发器处理内容
end 触发器名字;

创建存储过程（数据库中的方法）

create or replace procedure 存储过程名字(执行参数名字 in 执行参数类型,返回参数名字 out 返回参数类型, ...) is
begin
  存储过程内容
end ;

（3）代码示例

创建学生表，有学号、姓名、性别三个属性学号为主键，且学号自增。

create table student(
	id int PRIMARY key auto_increment,
	name varchar(20),
	age int,
	gender varchar(4)
);

创建班级课程的关系表，要求不需要其他属性。创建主外键依赖。

create table banji_course(
	banji_id int,
	course_id int,
	PRIMARY KEY (banji_id,course_id),
	foreign key (banji_id) REFERENCES banji(id),
	FOREIGN key (course_id) REFERENCES course(id)
);

创建触发器，监控用户对XS表的操作，要求：当XS表执行插入、更新和删除3种操作后在sql_info表中给出相应提示和执行时间

create or replace trigger filter_xs
before
delete or insert or update
on xs
for each row
declare
begin
	if inserting then
		dbms_output.put_line('insert at '||sysdate);
	elseif updating then
		dbms_output.put_line('update at '||sysdate);
	else
		dbms_output.put_line('delete at '||sysdate);
	end if;
end filter_xs;

创建一个存储过程，以部门号为参数，返回该部门的人数.

create or replace procedure count_dep(dep in number,num out number) is
begin
  select count(*) into num from emp where emp.deptno=dep group by deptno;
end count_dep;

（4）完整性约束条件

实体完整性（主键属性约束）：
针对基本关系而言，若属性A是基本关系R的主属性，则A不能为空。在关系模型中，主码作（主键）为唯一标识符。主码属性如果为空，就说明存在主码无法标识的个体，实体表中的记录存在不再唯一的可能性。
参照完整性（外键约束）：
参照完整性用来描述实体外码，它表示属性的外码要么为空，要么等于另一个实体的主码。
对于参照性约束来说外码属性为不为空并不重要，因为另一个实体的主属性必然不为空。
用户自定义完整性：
在不违反实体完整性约束和参照完整性约束的条件下，用户可以根据具体应用场景自定义关系型数据库的约束条件。比如学生必然有姓名。

2、alter修改对象

（1）修改表结构

向student学生表中添加家庭住址列。

alter table student add column address char(20);

删除student学生个表中的php列

alter table student drop php;

（2）修改列类型

将student表中java列的类型修改为int

alter table student modify column java int;

（3）修改表名字

将student表重命名为stu表

alter table student rename to stu;

3、truncate清空表格数据

清空student表中的数据（包括自增起始点）

truncate table student;

4、drop删除对象

删除student表。

drop table student;

二、DML（数据操作语言）

1、insert添加数据

（1）命令格式

insert table 表明[(字段1,字段2,...)] values(字段1的值,字段2的值...);

（2）具体案例

向student表中添加一条数据。

INSERT into student(name,age,gender) values('屈原',2407,'男');

向student表中添加两条数据

INSERT into student(`name`,age,gender) values('范雎',2350,'男'),('白起',2330,'男');

（3）特别说明

不要出现主键重复或为空的情况。

2、update修改数据

（1）命令格式

update 表名 set 字段名1=值1[,字段名2=值2,...][where 目标数据满足的条件];

（2）具体案例

将student表中的山东的同学的java成绩改为90。

update student set java=90 where address='山东';

（3）特别说明

修改属性数据注意加条件，避免修改所有数据。

3、delete删除数据

（1）命令格式

delete from 表名 [where 目标数据满足的条件];

（2）具体案例

删除student表中姓名为白起的记录。

delete from student where name='白起';

（3）特别说明

删除记录注意加条件，避免删除所有数据。
数据删除后自增字段的自增起始点不变。
数据清空truncate后自增字段的自增起始点归零。

4、select查询数据

（1）命令格式

select [ALL|DISTINCT][TOP n[PERCENT]]<目标列表达式>[,...n][INTO<新表名>]
from <表明>|<视图名>[,...n]
[WHERE <条件表达式>]
[GROUP BY<列名I>
[HAVING<条件表达式>]]
[ORDER BY <列明2>[ASC|DESC]];

（2）命令格式详解

ALL|DISTINCT:
ALL是查询所有数据并展示；DISTINCT是对查询结果去重并展示。
TOP n[PERCENT]：
限制返回行数n。在mysql和Oracle中无法使用，只能用在sql server中。在mysql中使用limit n；在Oracle中用where rownum<=n来实现相应功能。当mysql要实现PRECENT相应效果只能使用count(*)+存储过程，并通过pl/sql调用存储过程获得比例行数的赋值变量m，进而limit 0,m，来进行查找前n%行的数据。
[,…n]：
前一项可以重复n次。
[INTO<新表名>]：
在pl/sql语句中，[into 变量名]用于将查询的该列结果，向变量直接赋值，被赋值变量将参与下面的逻辑。
[ASC|DESC]：
asc表示升序，desc表示降序，可以跟在order by的每一个排序标准列后面。排序优先级按照从左到右的循序进行。
[GROUP BY<列名I>[HAVING<条件表达式>]]
在有group by的查询语句中，select后面必须为聚集函数或分组依赖。
having里面的属性也必须为分组依赖。

（3）语句执行顺序

from->where->group by->having->select->orderby

（4）具体案例

案例一
在xuesheng表、course表、banji表、course_banji表中查询学生的姓名，班级，所学的课程名称和课程学分。

select 
xuesheng.name xueshengName,banji.name as banjiName,
course.`name` as courseName,
course.credit as credit
from 
xuesheng,banji,banji_course,course 
where 
xuesheng.banji_id = banji.id and banji.id = banji_course.banji_id and banji_course.course_id = course.id;

案例2
在goods表(cat_id,cat_name,parent_id)、category表(goods_id,goods_name,cart_id)中查询父级分类的所有商品数量和分类名字。

select count(goods.goods_id) as count , c2.cat_name
from goods,category as c1,category as c2
where goods.cat_id = c1.cat_id
GROUP BY c1.parent_id,c2.cat_id,c2.cat_name
HAVING c1.parent_id=c2.cat_id;

三、DCL（数据控制语言）

1、grant授予权限

（1）命令格式

grant <权限>[,<权限>]...
on <对象类型> <对象名>[,<对象类型> <对象名>]...
to <用户名>[,<用户名>]...
[with grant option];

（2）具体案例

授予teacher用户查询、删除、修改student表的权限。

grant select,update,delete
on table student
to teacher;

（3）特别说明

grant能授予的权限有
属性列和视图：select(查询权限),insert(插入权限),update(修改权限),delete(删除权限),all privileges(所有权限)。
基本表：select(查询权限),insert(插入权限),update(修改权限),delete(删除权限),alter(修改表结构权限),index(建立索引权限),all privileges(所有权限)。
[with grant option]出现时，授予用户授予其他用户自己所拥有权限的权限。
尽量不要使用所有权限，由于revoke无法收回所有权限，所以收回时会很麻烦。

2、revoke回收权限

（1）命令格式

revoke <权限>[,<权限>]...
on <对象类型> <对象名>[,<对象类型> <对象名>]...
from <用户名>[,<用户名>]...
[cascade | restrict];

（2）具体案例

收回teacher用户查询student表的权限

revoke select
on table student
from teacher;

（3）特别说明

cascade的作用是一并收回该用户授予其他用户的指定权限。
from public 可以收回所有用户的指定权限。