专业学习

数据库复习笔记

Posted by Mr.Be1ieVe on Monday, January 13, 2020

使用的是sql server，操作可能跟mysql有区别

新建数据库

create database stuhyy
on 
(
 name = stu, 
 filename='C:\stu_data.mdf',
 size = 3mb,
 maxsize = 500mb,
 filegrowth = 10%

)
log on
(
 name = stu_log,
 filename = 'D:\stu_log.ldf',
 size=3mb,
 maxsize = unlimited,
 filegrowth = 1mb
)

修改数据库

Alter database Stuhyy
Modify file
(
    name = Stuhyy_log,
    filename = 'H:\Stuhyy_log.ldf',
    size = 10MB,
    maxsize = 20MB,
    fileGrowth = 5%
)

添加数据库文件

Alter database Stuhyy
add file
(
    name = dataname,
    filename = 'H:\dataname.ndf',
    size = 20MB,
    maxsize = 30MB,
    filegrowth = 1MB
)

新建&修改数据表

Create Table Student
(
    Num int primary key,
    Nam char(6) not null,
    Birthday datetime
)
Create Table ChoseClass
(
    Num int primary key,
    CNum int not null,
    Score int not null.
    check (100>= Score and Score >= 0)
)
Alter Table Student
add 学分 int
ALTER TABLE table_name 
DROP COLUMN column_name

添加外键约束

Alter Table Student
Add Constraint fk_stuNum
Foreign key(Num) references ChoseClass(Num)

插入信息

Insert Into studinfo
Values('hyy','11'，'广东')

删除信息

Delete From sc
where xxx = xxx

备份数据库

Backup database Stuhyy to disk = 'H:\StuhyyBackup.bak'
///Backup database Stuhyy to StuhyyBak
Backup log Stuhyy_log to disk = 'H:\Stuhyy_logBackup.bak'
///Backup log Stuhyy_log to Stuhyy_log

恢复

Restore Database Stuhyy from disk = 'H:\StuhyyBackup.bak'

Select Distinct 学号 from sc

对成绩进行降序,相同则按学号升序排序

Select 学号,成绩 From sc
Where 课程号 = '4'
Order by 成绩 desc,学号 asc

查询姓黄同学的信息

Select 姓名, 系部名称 From Studinfo
Where 姓名 like '咩%' and 系部名称 in ('国传','媒体工程')

查询选修人数超过10人的课程号

Select 课程号,Count(*) as 选课人数  From sc
Group By 课程号
Having Count (*) > 10

==查询每门课最高分，最低分==

查询选修了课程的学生姓名及选修课程的最高分

Select Studinfo.姓名,Max(sc.成绩) as 最高成绩 From sc
Right Outer Join Studinfo on Studinfo.学号 = sc.学号
Group by Studinfo.姓名

==right outer join left inner join==

　　left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 以左表为主 　　right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录 以右表为主 　　inner join(等值连接) 只返回两个表中联结字段相等的行

outer join(外连接) 可分为左外连接left outer join和右外连接right outer join

left join 是 left outer join 的简写，两者含义一样的。 right join 是 right outer join 的简写，两者含义一样的

T-sql创建学生视图

Create View AS
Select Studinfo.学号，Studinfo.系部名称,Course.课程名，Sc.成绩 From Studinfo,sc,Course
Where Studinfo.学号 = sc.学号 And sc.课程号 = course.课程号 
And Studinfo.系部名称 = '媒体工程'
With Check Option

对视图进行update或者insert操作时，保证更新或者插入的行满足图中定义的谓词条件 假如一张表里有个字段是专业的;你创建视图的时候 create view stu as select 学生 from table where 专业=‘计算机’ with check option 这样where后面就实现了对专业的限定 以后你如果对视图添加记录的时候专业如果不是计算机的话不让添加进去的 ———————————————— 版权声明：本文为CSDN博主「ClearLoveQ」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/ClearLoveQ/article/details/84285060

声明局部变量

Declare @max date , @min date
set @max = (select Max(出生日期) from studinfo)
set @min = (select Min(出生日期) from studinfo)
select @max as 最大出生日期 , @min as 最小出生日期

小知识

①date类型可用于需要一个日期值而不需要时间部分时；

②datetime类型:可用于需要同时包含日期和时间信息的值。

Case的使用

Select 姓名,课程名,成绩 =
Case
 when 成绩 > 90 then 'A'
 when 成绩 > 80 then 'B'
 when 成绩 > 70 then 'C'
 when 成绩 > 60 then 'D'
 else 'E'
End
From sc
inner join studinfo on sc.学号 = studinfo.学号
inner join course on sc.课程号 = course.课程号

///如果存在
If Exists(select * from studinfo)
 select * from studinfo

声明游标，更新字段

Declare curor_mark Cursor
For select 成绩 from sc
For Update of 成绩
Go

///删除游标
Deallocate curor_mark

游标定位操作

定义基于sc的游标，分别将第三条记录，从当前记录开始向前第二条记录和最后一条记录显示在屏幕上

Declare cursor_mark Cursor Scroll
For
 Select * from sc
 inner join studinfo on sc.学号 = Studinfo.学号
 inner join course on course.课程号 = sc.课程号
Open course_mark
fetch absolute 3 from cursor_mark
fetch relative -2 from cursor_mark
fetch last from cursor_mark
Close cursor_mark
Deallocate cursor_mark

游标遍历sc表，将学号为“1001”的学生的所有课程成绩加5分

Declare @name char(20), @num int
Declare cursor_mark Cursor Scroll
For
 Select 姓名,sc.学号 from sc
 inner join studinfo on sc.学号 = studinfo.学号
 inner join course on course.课程号 = sc.课程号
For Update of 成绩
Open cursor_mark
Fetch Next from cursor_mark Into @name , @num
while @@fetch_status = 0
 Begin
 if @name = '女朋友'
  Update sc
  set 成绩 = 成绩 + 5
  where current of cursor_mark
  Fetch Next from cursor_mark Into @name , @num
 End
Close cursor_mark
Deallocate cursor_mark

给sc表增加一列字段：等级，char（2），利用游标操作机制给等级字段赋值，如果score>60则为“P”，否则为“F”

 Alter Table sc
 Add 等级 char(2) null
 Go
 Begin
  Update sc
  set 等级 = 
   Case
     when 成绩 >= 60 then 'P'
     when 成绩 < 60 then 'F'
     End
 End

==dateadd(year,1,出生日期) 出生日期的year + 1==

==month(出生日期) 取出生日期的month出来==

==定义标量函数f_stud1,统计某学院的学生人数==

If Exists(select * from sysobjects where name = 'f_stud1')
 drop function f_stud1
Go
Create Function f_stud1(@nam char(10))
return int
AS
 Begin
  Declare @n int
  set @n = (select count(*) from studinfo
                 where 系部名称 = @nam)
        return (@n)
    End
Go

Select dbo.f_stud1('媒体工程') as 人数

定义一==内嵌表值函数==f_stud3，返回某学生选修课情况，包含姓名、课程名，分数等

Create Function f_stud3(@name nchar(10))
return Table
As
 return (select 姓名 ,课程名,成绩 from sc s
           inner join studinfo on s.学号 = studinfo.学号
           inner join course on s.学号 = course.学号
           where 姓名 = @nam)
Go

selet * from dbo.f_stud3('Mie')

创建一个==带输入的存储过程==，通过调用上例的f_stud3，查询某学生的选修课程情况

Create Procedure use_f_stud3(@nam nchar(10))
AS
 select * from dbo.f_stud3(@nam)
Go

Exec use_f_stud3('Mie')

==datediff(year,出生日期,getdate()) 获取年龄==

==定义多语句表值函数，根据输入的表名，返回该表所包含的字段名称，类型，类型长度以及是否为空==

create function f_tableinfo
(@tableName nvarchar(200))
returns @result table (ColName nvarchar(200),ColType nvarchar(200),ColLength int,ColIsNull bit)
as
 begin
  insert into @result(ColName,ColType,ColLength,ColIsNull)
 select
  c.[name] as ColumnName,
  t.[name] as ColumnType,
  c.max_length as MaxLength,
  c.is_nullable as [IsNull]
 from sys.columns c
 inner join sys.types t on c.system_type_id=t.system_type_id
 Where c.[object_id]=object_id(@tableName) and t.[name]<>'sysname'
 Order by c.column_id
 Return
 End
Go
select * from f_tableinfo('Studinfo')

使用存储过程新建department(系部人数，总人数，男生人数，女生人数)，用SQL语句从学生表中采集相应信息添加到系部表中。

Create Procedure p_stuXibu
As
 select 系部名称 as 系部名称,count(*) as 总人数,
  sum(case 姓名 when '女' then 1 else 0 End) as 女生人数
  sum(case 姓名 when '男' then 1 else 0 End) as 男生人数
 into 系部表 From studinfo
 Group by 系部名称
Go
p_stuXiBu

创建触发器tr_stud1，当在课程表中新增一条记录时，在屏幕上显示该新增记录信息，同时激活该激活器，验证该触发器的执行结果

Create trigger tr_stud1 on course
For Insert
As
 select * from inserted

建立一个命名事务trans_credit，当学号为@sid的学生所选的课程（课程编号为@cid）的总学分没有超过10时，将学号和课程编号值（@sid,@cid）添加到grade表中，同时修改stu_credit表中的总学分，使总学分为当前总学分+所选课程的学分值（@cre），否则，取消该事务，实现回滚。

use Stuhyy
go

if exists(select * from sys.all_objects where name = 'trans_credit')
 drop trigger trans_credit
go

create trigger trans_credit on stu_credit
for insert
as
 BEGIN TRAN 选课
  --save tran 选课
  declare @sid char(10),@cid char(10),@mark int,@totalmark int
  set @sid = (select 学号 from inserted)
  set @cid = (select 课程号 from inserted)
  set @mark = (select 学分 from inserted)
  set @totalmark = (select 总学分 from stu_credit where 学号 = @sid) + @mark
  if @totalmark >10
   BEGIN
    print '超出总学分!选课失败！'
    rollback tran 选课
   END
  else
   BEGIN
   update stu_credit set 总学分 = @totalmark where 学号 = @sid
   if OBJECT_ID(N'grade',N'U') is not null
    insert into grade 
    Values(@sid,@cid)
   else 
    create table student  --创建表student
    (学号 char(10) ,
    课程号 char(10),
    )
    insert into grade 
    Values(@sid,@cid) 
   END
 commit TRAN 选课
 GO
GO

用T-Sql语句创建sql server 登录1_test_w1,登录密码为12345,默认访问pubs

sp_addlogin
@loginame = 'Mie'
@passwd = '12345'
@dfdb = 'pubs'

授予Windows用户连接到sql server的权利

EXEC sp_grantloging ‘CSUSQL\1_test_w2’
Sp_addlogin
@loginame = '1_test_s2'
@defdb = 'Stuhyy'

添加数据库用户

EXEC sp_grantdbaccess 'u_test_w1'

禁止用户u_test_s1 对course表的插入和删除权限，撤销对studinfo的更新权限

GRANT
 DENY Insert,Delete On course
 Revoke Update on Studinfo

对用户u_test_s1添加成员r_test_s1

EXEC Sp_addrolemember 'u_test_s1' , 'r_test_s1'

删除账户

Sp_revokelogin 'u_test_w1'
Sp_revokelogin 'u_test_s1'

删除数据库中用户

Sp_revokedbaccess 'r_test_s1'

删除数据库角色r_test_s1

EXEC sp_droprolemember 'u_test_w1','r_test_s1'

2019/12/4上课笔记

use employee
Go 
if exists(select * from sys.all_objects where
  name = 'trigger_welcome' and type = "TR")
  Drop trigger trigger_welcome
go

Create trigger tr_employye on employee
for delete
as
declare @msg varchar(50)
select @msg = str(@@rowcount)+"个员工被删除"
select @msg
return

Create Trigger test_tr
on employee from insert,update,delete
as
 select * from inserted
 select * from deleted

create trigger tri_del_sales2
on sotrebak
for delete as
delete sales_bak from deleted
where deleted.sotr_id = sales_bak.stor_id

2019/12/11 上课笔记

BEGIN TRAN[SACTION]
 
COMMIT TRAN[SACTION]

ROLLBACK TRAN[SACTION]

2019/12/25复习

判断5分，单选20分，填空20分，简答题10分，综合设计45分

E-R图什么标志表示实体，属性，关系？ P9

数据库三级模式体系结构，有利于==数据独立性==

三级模式结构又是什么，为什么可以有利 P6

模式，内模式，外模式

模式:对数据库全部数据的数据模式和特征的总体描述

内模式：存储模式，数据库最低一级逻辑描述

外模式:用户视图（来源一个或多个表组成的查询表

数据库系统中，用户使用的数据视图用==外模式==描述，该模式是用户与数据库系统之间的接口

一对一，一对多，多对多举例

数据库特点：数据共享，数据完整性和数据独立性

范式分解:减少冗余 范式P27

是什么范式，判断不符合更高范式即可

1NF：所有属性都不能分解为更基本的数据元素

2NF：不存在部分函数依赖（某列也能函数决定另一列属性）

3NF：非传递函数依赖

数据模型三要素：数据结构（二维表），关系操作（选择投影链接），完整性约束

DBMS、DB、DBS（system，包含人）、DBA

Create Insert
Alter update
Drop Delete

自然连接：消除重复列

sql server数据库文件后置：.mdf .ldf .ndf

windows身份验证，混合身份验证

存储过程：主动调用

触发器：根据条件触发

综合设计：E-R图转换，35分写代码（查询，存储过程，事务，每题6-8分）

以633开头的7位电话怎么写

「真诚赞赏，手留余香」

Mr.Be1ieVe's Treasure

真诚赞赏，手留余香

使用微信扫描二维码完成支付

CATALOG
    FEATURED TAGS
    awd buu ctf hackthebox linux使用 xray 专业学习 基础知识 学习笔记