身边的科学手抄报,港独分子返港遭围剿,赵秉哲
c:\users\asus\appdata\roaming\microsoft\sql server management studio\14.0\sqlstudio.bin
select * from student s2 where substring(s2.sname,2,1)=substring(s2.sname,3,1)
1 创建用户
create login names with password='pwd' , default_database=test; create user names for login names with default_schema=dbo;
2 增删改查授权
grant select,insert,update,delete on 表 to names
3 创建表
grant create table to names grant alter on schema::dbo to names;
4 存储过程授权
grant execute on 存储过程名 to username
5 禁止对表授权
deny update on 表 to username cascade;
6 回收权限
revoke delete on 表 from username
7 删除表
truncate table a delete from dbo.a drop table a
8 修改列
alter table a add sa nvarchar(10) not null alter table a add constraint pk_course_cno primary key(id) alter table a alter column id int alter table a drop constraint pk_a alter table a drop column sa
9 创建一个简单的登录,登录名为:newlogin;登录密码:123456;默认数据库:master,默认数据库也可以不指定。
exec sp_addlogin 'newlogin','123456','master'
10 创建用户
exec sp_adduser 'newlogin'
exec sp_adduser 'newlogin','newuser'
exec sp_addrole 'newrole'
exec sp_adduser 'newlogin','newuser','newrole'
grant all on jobs to newrole
grant select,update on sales to newrole
deny insert on employees to newrole
exec sp_grantdbaccess 'newlogin','newuser'
exec sp_addrolemember 'newrole','newuser'
exec sp_revokedbaccess 'newuser';
exec sp_droplogin 'newlogin'
exec sp_droprole 'newrole'
exec sp_droprolemember 'newrole', 'newuser'
create login mylogin with password = '123456' must_change;
create login mylogin with password = '123456', credential = mycredential;
create login [jack\xiangzhao] from windows;
create user myuser for login mylogin
create role myrole authorization myuser;
create role myrole authorization db_role
1 创建雇佣日期规则 hire_date_rule
create rule hire_date_rule as @hire_date>='1980-01-01' and @hire_date<=getdate()
2 创建性别规则sex_rule
create rule sex_rule as @sex in ('男','女')
3 -创建评分规则grade_rule
create rule grade_rule as @value between 1 and 100
4 创建字符规则my_character_rule
create rule my_character_rule as @value like '[a-z]%[0-9]'
5 sp_helptext 查看规则,查看规则hire_date_rule的文本信息
execute sp_helptext hire_date_rule
6 sp_bindrule绑定规则
exec sp_bindrule hire_date_rule, 'employee.hire_date'
exec sp_addtype pat_char,'varchar(10)','not null' exec sp_bindrule my_character_rule, pat_char, 'futureonly';
7 sp_unbindrule 解除规则的绑定
exec sp_unbindrule 'employee.hire_date'; * drop rule语句删除当前数据库中的一个或多个规则 drop rule sex_rule,hire_date_rule
select * from ( select * ,row_number() over(order by sid) as rowcindex --增加索引 from student) as t where t.rowcindex between 1 and 3;
select s.sid,s.sname, max(case c.cname when '语文' then sc.score end) as '语文', min(case c.cname when '数学' then sc.score end) as '数学', sum(case c.cname when '英语' then sc.score end) as '英语', max(case c.cname when '化学' then sc.score else 0 end) as '化学' from (student as s inner join sc as sc on s.sid=sc.sid inner join course as c on c.cid=sc.cid) group by s.sid,s.sname;
1 全局变量
select @@version --数据库版本 insert into course values('',''); select @@identity --获取最近insert语句的标识 print @@servername --服务器名称 print @@rowcount--返回受影响的行数
2 选择语句
declare @id int set @id =10 if @id>5 begin print 'ok' end else begin print 'no' end
3 循环语句
declare @id int set @id=1 while @id<10 begin print @id set @id=@id+1 end
4 异常
begin try delete from sc--不能成功 end try begin catch print @@error --判断错误信息 0对 end catch
5 事务:回滚函数
begin try begin transaction --开启事务 --错误sql commit tran --提交事务 end try begin catch rollback tran --回退事务 end catch
6 锁:同步
begin tran --锁上 --修改操作 rollback tran --开锁 commit tran
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!! 点击进行留言回复
数据库SQL---数据库、基本表、视图、索引的定义、修改、删除
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (上)
在 Azure CentOS VM 中配置 SQL Server 2019 AG - (下)
网友评论