SQL语句--sql server（DDL、DCL、DML）

2. 支持DDL语句的审计，数据库表创建表、删除表、修改表结构（DDL）

1）创建数据库

create database zyh1029

2）使用数据库

use zyh1029

3）删除数据库（先删除在使用，不然）

drop database zyh1029

4）创建表：

create table Student(

    Sno Char(7) primary key,--学号  

    Sname Char(10) not null,--学生姓名

    Ssex Char(2) not null,--性别

    Sage Smallint,--年龄

    Clno Char(5) not null--学生所在班级号

);

2）查看表定义：

sp_columns Student

3）增加表字段：

alter table student add address varchar(20);

5）字段改名：

sp_rename 'Student.Clno','Class';

alter table Student alter column Class char(10);

6）删除字段：

alter table student drop column address;

4）删除表：

drop table Student;

5）修改表名：

exec sp_rename 'Student','Student1'

5）清空表：

create TABLE tb_AW(

name varchar(10),

sex varchar(30)

);

insert into tb_AW values('Joe','男');

insert into tb_AW values('周欣红','女');

select * from tb_AW;

truncate table tb_AW;

3. 支持DCL语句的审计，授予、解除用户访问权限、拒绝用户访问（DCL）

1）添加用户并设置密码：

create login test with password='test',default_database=zyh1028

create user test for login test with default_schema=dbo

exec sp_addrolemember 'db_owner', 'test'

2）拒绝test用户对Student表执行insert语句

deny insert on Student to test

3）查询用户：

Select name FROM Sysusers where status='0' and islogin='1'

4）授予权限：

GRANT SELECT to test

5）修改用户密码:

alter login test with password='Sqlserver123456'

6）收回权限:

revoke SELECT from test

7）删除用户：

DROP USER test;

4. 支持DML语句的审计，检索、更新数据、添加行、删除行（DML）

CREATE TABLE Students(

s_id VARCHAR(20),

s_name VARCHAR(20) NOT NULL DEFAULT '',

s_birth VARCHAR(20) NOT NULL DEFAULT '',

s_sex VARCHAR(10) NOT NULL DEFAULT '',

s_age VARCHAR(10) NOT NULL DEFAULT '',

);

1）添加：

insert into Students values('01' , '赵雷' , '2021-1-1' , '男' , '22');

insert into Students values('02' , '钱电' , '2021-1-1' , '男' , '22');

insert into Students values('03' , '孙风' , '2021-1-1' , '男' , '22');

2）修改表数据：

update Students set s_name = '周生' where s_id = '01'

3）查找表数据：

SELECT * from Students

4）条件查询

select s_name as '姓名' from Students;

select * from Students where s_name = '周生';

select * from Students where s_name like '%生%';

select * from Students where s_id between 01 and 02

select * from Students where s_name in('周生','孙风');

select u.s_id,u.s_name from Students u order by u.s_id desc;

5）删除表数据：

delete from Students where s_name = '周生'

5. 支持数据库存储过程审计

1）新建存储过程：

create  procedure  proctest

@id INT=1

as  

begin  

update Students set s_name = '周生' where s_id = @id;

end;

2）调用：

exec proctest 01;

3）查看存储过程

SELECT TEXT FROM syscomments WHERE id=object_id('proctest');

SP_HELPTEXT 'proctest'

4)修改存储过程

alter procedure proctest

@id INT=1

as  

begin  

update Students set s_name = '赵雷' where s_id = @id;

end

5）删除：

drop procedure proctest

6. 函数：

1）创建函数：

Create  FUNCTION getsum

(@num1 int,@num2 INT

)

returns INT

as

BEGIN

DECLARE @return INT

SET @return=@num1+@num2

RETURN @return

END;

2）调用：

SELECT dbo.getsum(1,3);

3）删除：

DROP FUNCTION getsum;

7. 视图：

1）创建视图：

create view testview as select * from Students;

2）调用视图：

SELECT * FROM testview;

3）查看视图：

select * from syscomments testview;

4）删除视图：

DROP VIEW testview

8. 索引：

1）创建索引：

create unique index Stusno on Student1(Sno);

2）查看索引：

SELECT * FROM sys.sysindexes WHERE id=object_id('Student1')

3）重命名索引：

exec sp_rename 'Student1.Stusno','Stuno';

4）删除索引：

drop index Student1.Stuno;

9. 事务：

1）提交事务：

begin transaction

    begin

        update Students set s_age=s_age-1000 where s_id=01

            commit transaction

    end  

2）回滚事务：

begin transaction

    begin

        update Students set s_age=s_age-1000 where s_id=01

            rollback transaction

    end

3）使用savepoint部分回滚

savepoint-sql server.txt

10. 超长SQL语句审计：

超长sql语句-sql server.txt

11. 中文乱码审计：

create table student4

(

  stuid      varchar(11) not null,--学号：'S'+班号（7位数）+学生序号（3位数）(1)

  stuname    varchar(50) not null,--学生姓名

  sex        char(20) not null,--性别

  age        int not null,--年龄

  classno    varchar(20) not null,--班号：'C'+年级（4位数）+班级序号（2位数）

  idnumber   varchar(20) default '身份证未采集' not null--身份证

)

insert into student4 values('01' , '赵雷' , '男' , '22' , '11' , '41130319970513680X');

insert into student4 values('02' , '钱电' , '男' , '23' , '21' , '41130319980513680X');

12. 报表特权操作补充：

1）创建角色：

create role testRole;

2）授权角色：

grant select on student4 to testRole;

3）删除角色：

drop role testRole;

13. 超长执行时长语句的审计

SELECT GETDATE()

WAITFOR delay '00:05:00'

SELECT GETDATE();