SQL语言总结

浅浅的花香味﹌ 2022-01-07 23:45 197阅读 0赞

**常用sql语句大全：**  
定义模式：

Create schema 模式名 authorization 用户名

删除模式：

Drop schema 模式名 <cascade|restrict>

定义基本表：

Create table 表明 （列名 数据类型 约束条件,
    表级完整性约束）；
    主键：primarykey
    唯一：unique
    外键：foreign key(列名） references 表名（列名）
    修改基本表：
    Alter talbe 表明 操作
    插入新列：add 列名 数据类型 完整性约束
    改数据类型：alter column 列名 新数据类型
    加约束条件：add 约束条件(列名）
    删除基本表：
    Drop table 表名 <restrict|cascade>

建立索引：

Create (unique) (cluster) index 索引名 on 表名（列名 次序（ASC、DESC）...)
    Unique:每一个索引值对应唯一的数据记录
    Cluster:聚簇索引
    删除索引：
    Drop index 索引名

数据查询：

Select  (all/distinct) 列名 from 表名 where 条件 group by 列名 条件 
    order by 列名 顺序

通配符：

%：任意长度的字符串		_:任意单个字符		
    Like用法：
    例：找姓刘的学生姓名：
    Select sname from student where sname like ‘刘%’
    Order by：默认升序 DESC降序

聚集函数：

Count(*) 					统计元组个数
    Count((distinct/all) 列名）	统计一列中值得个数
    Sum((distinct/all)	列名）	计算一列值的总和
    AVG(约束  列名)			计算一列的平均值
    Max(列名）				计算最大值
    Min（列名）				计算最小值

连接查询：

外连接：
    左外连接：
    Select student.sno,sname,ssex,sage,sdept,cno,grade
    From student left outer join sc on(studnet.sno=sc.sno);
    保留student的悬浮元组，SC的属性上填null
    右外连接：与左外连接相反

嵌套查询：

带有in谓词的子查询：
    例：查询与‘刘晨’同在一个系的学生：
    1.确定刘晨所在系：
    Select sdept from student where sname=’刘晨’----->CS
    2.查找所有在CS系的学生：
    Select sno from student where sdept=’cs’
    使用嵌套查询：
    Select sno from student where sdept in (
    Select sdept from student where sname=‘刘晨’);

带有any或all或exists谓词：

>any			大于子查询结果的某个值
    >all				大于子查询结果的所有值
    Exists--->存在	返回true或false
    查询没有选修一号课程的学生姓名：
    Select sname from student where not exists(
    Select * from sc where sno=student.sno and cno=1);

集合查询：

并操作：union	交操作：intersect		差操作：except
    例：查找计算机的学生及年龄小于19岁的学生：
    Select * from student where sdept=’cs’ 
    Union(all) select * from student where sage < 19;
    Ps:不加all则自动去重
    基于派生表的查询：
    例：找出每个学生超过他自己选修课程平均成绩的课程号：
    Select sno,cno from sc,(select sno,avg(grade) from sc group by sno) 
    as avg_sc(avg_sno, avg_garde);

数据更新：  
插入数据：

Insert into 表名（列名1，列名2....） values(值1，值2....)

插入子查询结果：

Insert into 表名 子查询;
    Insert into dept_age(sdept,avg_age) select sdept,avg(sage)
    From student group by sdept;

修改数据：

Update 表名	set 列名=新值 where 条件

删除数据：

Delete from 表名 where 条件

空值处理：

Is null 和 is not null判断空值

视图：  
创建视图：

Create view 视图名 列名 as 子查询, with check option
    With check option:加上该句后如果进行插入、修改删除等操作会自动加上条件sdept=’is’
    Create view is_student as select sno,sname,sage from student where sdept=’is’

删除视图：

Drop view 视图名 cascade

查询视图：

Select 列名 from 视图名 where 条件

同表查询

更新视图：
    同表更新，但是如果创建视图是没有with check option则需要消解视图

**权限：**

授权和收回：

授权：grant

Grant 权限 on 对象类型 对象名 to 用户 with grant option
    With grant option是指获得该权限的用户可以传播该权限
    例： grant update(sno),select on table student to public with grant option

收回：revoke

Revoke 权限 on 对象类型 对象名 from 用户 （cascade/restrict）
    例： revoke update(sno),select on table student from public cascade;
    级联收回时将次用户传播出去的权限一同收回

创建数据库模式的权限：

Create user 用户名 with [dba|resource|connect]
    Ps:	dba--->超级用户
    Resource--->不能创建模式和新用户
    Connect---->只能连接数据库

数据库角色：  
1.创建角色：

Create role 角色名
    Eg:Create role r1

2.给角色授权

Grant 权限 on 对象类型 对象名 to 角色
    Eg:grant select,update,insert on table student to r1

3.将角色授予其他角色或用户

Grant 角色名 to 角色名/用户名 (with admid option)
    Ps:with admin option:可以传播权限
    Eg:grant r1 to wangping

4.角色权限收回

Revoke 权限 on 对象类型 对象名 from 角色
    Eg:revoke r1 from wangping

5.角色权限修改

Grant delete on table student to r1		使r1增加删除权限

审计：

Audit语句和noaudit语句
    Eg：对修改sc表结构或表数据的操作审计：
    Audit alter,update on sc
    取消审计：
    Noaudit alter,update on sc;

完整性约束：

1.Foreign key(sno) references student(sno) on delete cascade
    Ps:删除student的元组时同时删除本条表数据
    2.check短语指定条件：
    Create table student(ssex char(2) check(ssex in (‘男’,’女’));
    3.完整性约束命名句子
    Constraint 完整性约束条件名 完整性约束条件
    完整性约束条件包括 not null,unique,primary key,foreign key,check等短语
    Create talbe student(sno numeric(6) constraint c1 check(sno between 90000 and 10000))

断言：

1.创建断言(任何使断言不为真的操作都会被拒绝执行)
    Create assertion 断言名 check 句子
    Eg:限制数据库最多60名学生选修：
    Create assertion asse_sc_db_num check(60 >= (select count(*) from Course,sc
    Where sc.cno=course.cno and course.cname=数据库));

2.删除断言

Drop assertion 断言名

触发器：  
1.定义触发器

Create trigger 触发器名 (before|after) 触发时间 on 表名
    Referencing new|old row as 变量
    For each (row|statement)	定义触发器类型，表名动作执行频率
    When 触发条件 触发动作体
    Eg：定义一个before行级触发器，为教师表teacher定义完整性规则“教师的工资不能低于4000，若低于4000则自动改为4000”
    Create trigger insert_or_update_sal
    Before insert or update on teacher
    Referencing new row as newTuple
    For each row
    Begin	//触发动作体
    If (newtuple.job=教授) and (newtuple.sal<4000)
    Then newtuple.sal:=4000;
    End if;
    End

2.删除触发器

Drop trigger 触发器名 on 表名