sqlserver基础

标签：ysh database sqlserver 基础 server --- sql test

说明：此次笔记针对sqlserver2019版本

启停

右击服务名称，可选择停服/起服/重启服务

sql server(MSSQLSERVER) -- 开启

代理--开启

sqlserver 网络配置-- mssqlserver的协议--TCP/IP -- 开启

SQL Native Client 11.0配置-- 客户端协议--TCP/IP -- 开启

客户端

SQL Server Management Studio（ssms）

安装地址：https://aka.ms/ssmsfullsetup

点击连接--数据库引擎

库、表、视图、用户

1.库操作

#建库
create database db_test;
#查询库
SELECT name, database_id  FROM sys.databases;
#指定使用数据库
use db_test;

2.表操作

#建表
create table test_tb (i int,j char);
#插入数据
insert into test_tb(i,pri)  values(124,111)
#查询数据
select * from test_tb;
#删表
drop table test_tb;
#清除表数据
truncate table test_tb;
delete from test_tb;
#删除某条数据
delete from test_tb where i=122;
#添加表字段
alter table test_tb add pri_new char not null ;
#添加主键约束
alter  table test_tb add constraint pk_pri  primary key (pri_new)
#查看表结构
EXEC sp_help test_tb

3.视图

#查看视图
SELECT name AS ViewName  FROM sys.views;
#查看视图的定义
SELECT v.name AS ViewName, m.definition  FROM sys.views v  JOIN sys.sql_modules m ON v.object_id = m.object_id;

4.用户管理

登录名（Login）：登录名是用户连接到SQL Server实例的凭证。它可以是SQL Server身份验证（使用用户名和密码）或Windows身份验证（使用Windows用户名和密码或组）的一部分。登录名用于验证用户的身份，允许用户访问SQL Server实例。
用户（User）：用户是与特定数据库相关联的安全主体。每个登录名在尝试连接到数据库时都需要一个相应的数据库用户。这个数据库用户可以是基于登录名的，也可以是基于其他安全主体的（例如其他数据库用户或Windows组）。用户决定了在特定数据库范围内可以执行哪些操作。
角色（Role）：角色是一组用户的集合，用于简化权限管理。通过将一组相关的权限分配给一个角色，然后将用户添加到该角色，可以轻松地管理一组用户的权限，而不必单独为每个用户分配权限

---创建登录名
use master
create login ysh with password = N'bb123456??'
---创建数据库用户
USE db_test; 
CREATE USER ysh_user FOR LOGIN ysh;

---删除一个用户
use db_test
drop user ysh_user;
--- 删除登录名
use db_test
drop user ysh;

--授予权限
---授予操作表的权限
use db_test
GRANT SELECT, INSERT, UPDATE ON test_tb TO ysh_user;
---授权数据库SELECT, INSERT, UPDATE权限
GRANT SELECT, INSERT, UPDATE ON database::db_test TO ysh_user;
---授权建表权限（实测没效果）
GRANT CREATE TABLE TO    ysh_user;
--- 授予目标用户对特定数据库的所有权限（此条命令，可以对db_test执行增删改查、建表等操作了）
GRANT CONTROL ON DATABASE::db_test TO ysh_user;
---回收权限
REVOKE SELECT, INSERT, UPDATE ON database::db_test FROM ysh_user;
---查看权限
SELECT * FROM sys.database_permissions WHERE grantee_principal_id = USER_ID('ysh_user');

参考官网：https://learn.microsoft.com/zh-cn/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver16

或者直接通过ssms选择数据库--属性，修改

--角色
---数据库级别查看角色
SELECT   
    DP1.name AS DatabaseRoleName,   
    isnull(DP2.name, 'No members') AS DatabaseUserName  
FROM   
    sys.database_role_members AS DRM  
RIGHT OUTER JOIN   
    sys.database_principals AS DP1 ON DRM.role_principal_id = DP1.principal_id  
LEFT OUTER JOIN   
    sys.database_principals AS DP2 ON DRM.member_principal_id = DP2.principal_id  
WHERE   
    DP1.type = 'R'  
ORDER BY   
    DP1.name;
  
--- 服务器级别
SELECT   
    name,   
    type_desc,   
    is_fixed_role  
FROM   
    sys.server_principals  
WHERE   
    type = 'R';
    
---授予用户read的角色
use db_test
exec sp_addrolemember 'db_datareader' , ysh_user;
或者：ALTER  ROLE db_datareader ADD MEMBER ysh_user ;  

---查看用户所属角色
SELECT   
    r.name AS RoleName  
FROM   
    sys.database_role_members rm  
INNER JOIN   
    sys.database_principals r ON rm.role_principal_id = r.principal_id  
INNER JOIN   
    sys.database_principals u ON rm.member_principal_id = u.principal_id  
WHERE   
    u.name = 'ysh_user'; -- 替换'UserName'为你需要查询的用户名
    
--- 从角色删除用户
ALTER ROLE db_datareader DROP MEMBER  ysh_user;

角色参考：https://learn.microsoft.com/zh-cn/sql/relational-databases/security/authentication-access/join-a-role?view=sql-server-ver16&viewFallbackFrom=sql-server-2014&redirectedfrom=MSDN