MsSQL编程入门-待补充

[TOC]

– 数据库版本 select @@version

–查询数据库大小

exec sp_spaceused;

–查询数据库中指定表大小

exec sp_spaceused ‘表名’;

https://www.cnblogs.com/rainman/p/6203065.html

数据库操作:

代码语言：javascript复制

CREATE DATABASE [WeiyiGeek]
ON
PRIMARY
(
  NAME = N'WeiyiGeek',
  FILENAME = N'D:ZSBWeiyiGeek.ndf',
  SIZE = 2304KB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 1MB
)
LOG ON
(
  NAME = N'WeiyiGeek_log',
  FILENAME = N'D:ZSBCZWeiyiGeek_log.ldf',
  SIZE = 768KB,
  MAXSIZE = UNLIMITED,
  FILEGROWTH = 10MB
)

GO
ALTER DATABASE [WeiyiGeek] COLLATE Chinese_PRC_CI_AS

GO
ALTER DATABASE [WeiyiGeek] SET RECOVERY FULL

用户操作:

1.新增

代码语言：javascript复制

--创建登录用户及默认管理数据库
CREATE LOGIN weiyigeek WITH PASSWORD='Weiyi.Geek*123456',DEFAULT_DATABASE=weiyigeek

--创建数据库用户
CREATE USER weiyigeek FOR LOGIN weiyigeek WITH DEFAULT_SCHEMA=dbo

2.修改

代码语言：javascript复制

--禁用登录用户
ALTER LOGIN weiyigeek DISABLE
--启用登录账户
ALTER LOGIN weiyigeek ENABLE
--登录账户改密码
ALTER LOGIN weiyigeek WITH PASSWORD='复杂密码要求'
--登录账户改名
ALTER LOGIN weiyigeek WITH NAME=Weiyi
--数据库用户改名
ALTER USER weiyigeek WITH NAME=Weiyi
--更改数据库用户 DEFAULT_SCHEMA
ALTER USER weiyigeek WITH DEFAULT_SCHEMA=stg
--删除数据库用户
DROP USER weiyigeek
--删除登录账户
DROP LOGIN weiyigeek

权限操作:

代码语言：javascript复制

--通过加入数据库角色，赋予数据库用户“db_owner”权限
exec sp_addrolemember 'db_owner', 'weiyigeek'

-- 数据库所有者权限
-- GO
-- EXEC [WeiyiGeek].dbo.sp_changedbowner N'Sa'



--授予用户建表权限
GRANT CREATE TABLE to weiyigeek
--授予角色dba对HR.Employees表的所有权限
GRANT ALL ON HR.Employees to weiyigeek


--收回权限
REVOKE all ON HR.Employees FROM weiyigeek


--授予角色dba对HR.Employees表的SELECT权限
GRANT SELECT ON HR.Employees TO weiyigeek
--拒绝安全账户dba对HR.Employees表的select权限(下面则相反)
DENY SELECT ON HR.Employees to weiyigeek



--从数据库中删除安全账户,failed
EXEC sp_revokedbaccess 'jira'
--删除角色 r_test,failed
EXEC sp_droprole 'jira'
--删除登录 l_test,success
EXEC sp_droplogin 'jira'

Q:recoke和deny的区别?

• revoke 收回之前被授予的权限,(收回权限后，可以从其他角色中重新继承权限)
• deny 拒绝给当前数据库内的安全账户授予权限并防止安全账户通过其组或角色成员资格继承权限,(拒绝权限后，不可以从其他角色继承权限)

基础示例:

代码语言：javascript复制

-- Revoke
GRANT INSERT ON TableA TO RoleA      -- 授予角色RoleA对TableA插入权限
EXEC sp_addrolemember RoleA, 'UserA' -- 用户UserA将有TableA的INSERT权限
REVOKE INSERT ON TableA FROM RoleA   -- 用户UserA将没有TableA的INSERT权限(收回权限)
GRANT INSERT ON TableA TO RoleA       -- 重新给RoleA以TableA的INSERT权限


-- Deny
DENY INSERT ON TableA TO UserA  -- 虽然用户UserA所在RoleA有TableA的INSERT权限，但UserA本身被DENY了，所以用户UserA将没有TableA的INSERT权限。

章节总结: 1.MsSQL用户与角色(MsSQL 2008):

用户:

登录连接用户名: 个人建立用户与Windows用户组或用户账户(SYSTEM / MSSQLSERVER / SQLSERVERAGENT / Administrator), 默认最高权限系统管理员sa;

代码语言：javascript复制

数据库用户

角色:

服务器角色:在服务器层次上定义的，因此它们位于从属于数据库服务器的数据库外面;

代码语言：javascript复制

#sysadmin: 执行SQL Server中的任何动作比如sa，只有这个角色中的成员(或一个被这个角色中的成员赋予了CREATE DATABASE权限的用户)才能够创建数据库;
#serveradmin: 配置服务器设置,比如(使表常驻于主内存中,显示或更改系统选项,更新系统过程所做的所有改动,关掉数据库服务器,用户自定义表设置选项的值)
#setupadmin: 安装复制和管理扩展过程，向该服务器角色中添加其他登录以及添加、删除或配置链接的服务器，和指向一些系统过程;
#securityadmin: 执行关于服务器访问和安全的所有动作,管理登录和CREATE DATABASE的权限以及阅读审计,读取SQL Server的错误日志
#processadmin: 管理SQL Server进程,如中止用户正在运行的查询以及执行KILL命令(以取消用户进程);
#dbcreator: 管理与数据库创建和修改有关的所有动作,比如(运行CREATE DATABASE和ALTER DATABASE语句)
#diskadmin: 用来存储数据库对象的文件和文件组有关的动作(管理磁盘文件)
#bulkadmin: 批量插入管理员，以前的SQL版本称为bulk - insert administrators

数据库角色:固定数据库角色在数据库层上进行定义，因此它们存在于属于数据库服务器的每个数据库中

代码语言：javascript复制

#db_owner : 可以执行数据库中技术所有动作的用户
  - 1.向其他固定数据库角色中添加成员或从其中删除成员
  - 2.运行所有的DDL语句
  - 3.运行BACKUP DATABASE和BACKUP LOG语句
  - 4.使用CHECKPOINT语句显式地启动检查点进程
  - 5.授予、取消或剥夺每一个数据库对象上的下列权限
  - 6.执行特定的系统过程进行添加用户和角色以及对任意数据库对象重新命名

#db_accessadmin : 执行与数据库访问有关的所有动作，比如可以添加、删除用户的用户
  - 1.为Windows用户账户、Windows组和SQL Server登录添加或删除访问

#db_datareader : 对数据库中的数据库对象(表或视图)具有SELECT权限，比如可以查看所有数据库中用户表内数据的用户

#db_datawriter : 对数据库中的数据库对象(表或视图)具有INSERT、UPDATE和DELETE权限，可以添加、修改或删除所有数据库中用户表内数据的用户
  
#db_ddladmin ：可以在数据库中执行所有DDL操作的用户
  - 1.运行所有DDL语句
  - 2.对任何表上授予REFERENCESE权限

#db_securityadmin : 管理数据库中的安全，可以管理数据库中与安全权限有关所有动作的用户
  - 1.运行与安全有关的所有Transact-SQL语句(GRANT、DENY和REVOKE)

#db_backoperator ：可以备份数据库的用户(并可以发布DBCC和CHECKPOINT语句，这两个语句一般在备份前都会被执行)
  - 1.运行BACKUP DATABASE和BACKUP LOG语句
  - 2.用CHECKPOINT语句显式地启动检查点进程

#db_denydatareader:不能看到数据库中任何数据的用户，对数据库中的数据库对象(表或视图)没有SELECT权限
#db_denydatawriter:不能改变数据库中任何数据的用户，对数据库中的任何数据库对象(表或视图)没有INSERT、UPDATE和DELETE权限。


#Public: 特殊的固定数据库角色，数据库的每个合法用户都属于该角色（它为数据库中的用户提供了所有默认权限。）
  - 1.作用:提供一种机制即给予那些没有适当权限的所有用户以一定的(通常是有限的)权限，所以public角色不能被删除；
  - 2.允许的操作：使用某些系统过程查看并显示master数据库中的信息，执行一些不需要一些权限的语句

注意事项:

• 您不能添加、修改或删除固定服务器角色。另外，只有固定服务器角色的成员才能执行上述两个系统过程来从角色中添加或删除登录账户。
• 在以前的SQL Server版本中不存在角色，sa登录具有所有可能的关于系统管理工作的权限。在SQL Server 2005中sa登录保持了向后兼容性是固定服务器角色sysadmin中的成员，并且不能从该角色中删除。

EXEC 命令

代码语言：javascript复制

--检查数据库中孤立用户
EXEC sp_change_users_login @action='Report'

--对孤立用户连接到现有的登录名
EXEC sp_change_users_login   
    @action='update_one',   
    @usernamepattern='UserName', --数据库孤立用户  
    @loginname='LoginName'; --关联到sql server登录名  
GO

-- #系统过程添加或删除固定服务器角色成员
sp_addsrvrolemember
sp_dropsrvrolemember

sp_srvrolepermission - 浏览每个固定服务器角色的权限

代码语言：javascript复制

-- 语法
sp_srvrolepermission [[@srvrolename =] 'role']

EXEC sp_srvrolepermission --当前用户角色权限
-- ServerRole	Permission
-- bulkadmin	Add member to bulkadmin

EXEC sp_srvrolepermission @srvrolename = 'bulkadmin'  -- 查看指定固定服务器角色用户的权限
-- ServerRole	Permission
-- bulkadmin	Add member to bulkadmin
-- bulkadmin	BULK INSERT

sp_dbfixedrolepermission - 查看每个固定数据库角色的权限

代码语言：javascript复制

-- 语法
sp_db.xedrolepermission [[@rolename =] 'role']

EXEC sp_dbfixedrolepermission -- 当前固定数据库角色权限
-- DbFixedRole	Permission
-- db_accessadmin	sp_revokedbaccess
-- db_backupoperator	BACKUP DATABASE

EXEC sp_dbfixedrolepermission @rolename = 'public' -- 查指定数据库角色的权限
-- DbFixedRole	Permission
-- db_owner	Add/drop to/from db_accessadmin
-- db_owner	Add/drop to/from db_backupoperator

代码语言：javascript复制

运 行如下的系统过程：如sp_addlinkedsrvlogin、sp_addlogin、sp_defaultdb、 sp_defaultlanguage、sp_denylogin、sp_droplinkedsrvlogin、sp_droplogin、 sp_grantlogin、sp_helplogins、sp_remoteoption和sp_revokelogin(所有这些系统过程都与系统安 全相关。)

--db_owner
使 用下列系统过程向数据库中添加用户或角色：sp_addapprole、sp_addrole、sp_addrolemember、 sp_approlepassword、sp_changeobjectowner、sp_dropapprole、sp_droprole、 sp_droprolemember、sp_dropuser、sp_grantdbaccess

使用系统过程sp_renamedb来修改数据库的名称

使用系统过程sp_procoption和sp_recompile来修改任何存储过程的结构

使用系统过程sp_rename为任何数据库对象重命名

使用系统过程sp_tableoption和sp_changeobjectowner分别修改表的选项和任何数据库对象的拥有者

---

IN 字句

代码语言：javascript复制

#示例1.显示姓李**的100位学生及其学校
SELECT top 100 XM,KDMC FROM [cj] WHERE BMH IN (SELECT BMH FROM [CJ] WHERE XM LIKE '李__')

行转列 需求分析:当一个数据表中每一行代表了学生的某一科成绩，如何将在一行显示一位考生所有科的成绩，这是我们就需要进行列转行; 实现需求:

WeiyiGeek.

测试示例:

代码语言：javascript复制

CREATE TABLE `TEST_TB_GRADE` (
  `ID` int(10) NOT NULL AUTO_INCREMENT,
  `USER_NAME` varchar(20) DEFAULT NULL,
  `COURSE` varchar(20) DEFAULT NULL,
  `SCORE` float DEFAULT '0',
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
 
insert into TEST_TB_GRADE(USER_NAME, COURSE, SCORE)  values
("张三", "数学", 34),
("张三", "语文", 58),
("张三", "英语", 58),
("李四", "数学", 45),
("李四", "语文", 87),
("李四", "英语", 45),
("王五", "数学", 76),
("王五", "语文", 34),
("王五", "英语", 89);

实际示例:

代码语言：javascript复制

-- 知识点:group by when-case语句 聚合函数
-- 以姓名为分组进行显示考生各科成绩以及是否考了高等数学或者大学语文
SELECT TOP 10 XM 姓名, 
max(case KM1MC WHEN '高等数学' THEN CJ1 else null end) 高等数学,
max(case KM1MC WHEN '大学语文' THEN CJ1 else null end) 大学语文,
max(case KM2MC WHEN '计算机基础' THEN CJ2 else 0.0 end) 计算机基础,
max(case KM2MC WHEN '大学英语' THEN CJ3 else 0.0 end) 大学英语
FROM [cj]
GROUP BY xm;

--结果
姓名	高等数学	大学语文	计算机基础	大学英语
WeiyiGeek	91	0	77	0

列转行 需求分析:将上述图中进行逆向转换，即将一行学生包括的各门成绩进行一行一行显示 测试实例:

代码语言：javascript复制

select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2
union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2
union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2
order by user_name,COURSE;

实际实例：

代码语言：javascript复制

-- union 联合查询
-- 以学生姓名排序每一行显示一门课程成绩
SELECT top 1 XM, '高等数学' '科目' ,CJ1 '成绩'from [2019cj]
UNION SELECT top 1 XM, '大学语文' '科目' ,CJ1 '成绩' from [2019cj]
UNION SELECT top 1 XM, '计算机基础' '科目',CJ2 '成绩' from [2019cj]
UNION SELECT top 1 XM, '大学英语'  '科目',CJ3 '成绩' from [2019cj]
ORDER BY XM;


--执行结果
-- 姓名	      科目     成绩
-- WeiyiGeek		高等数学  91
-- WeiyiGeek	 	大学语文   0
-- WeiyiGeek	  计算机基础 77
-- WeiyiGeek	 	大学英语		0

声明了三个变量：typeid、parentid、name，数据类型分别为：int、int、varchar(50)

declare @typeid int,@parentid int,@name varchar(50) ;

设置变量：name 的值为：成都市

set @name=’成都市’;

使用变量，变量查询赋值：使用 @name 变量，将查询出来的 typeid 字段的数据赋值给 @typeid 变量

select @typeid = typeid from [logging].[dbo].[SYS_CODE] where name = @name;

使用下面的代码直接输出变量：

print ‘typeid：‘ @typeid ’ name：’ @name

代码语言：javascript复制

--创建登录名
USE master
GO
--创建pbmasser,密码123456
create login weiyigeek with password='weiyigeek123456.'


GO



--创建数据库用户
USE E_Market
GO
--创建MasterDBUser用户名
create user MasterDBUser for login PbMaster   --为PbMaster数据库登录名创建
GO


--为使用者MasterDBUser赋予查看，新增，修改的操作权限
use E_Market
go
grant select,insert,update on UserInfo to MasterDBUser   --赋予查看，新增，修改.sderinfo表名
go





--将updata权限进行回收
use E_Market
GO
revoke update on userinfo to MasterDBUser
GO

• : 该运算符用于字符串拼接,例如select "Name" ":WeiyiGeek"

基础示例:

代码语言：javascript复制

-- 示例1.Mssql 数据库字符串字段拼接、截取后四位和指定字符串替换
-- cmd & copy (标4文5)
SELECT 'mkdir ' RIGHT(yxdm,4) from t_user_info where xxok = '1' GROUP BY yxdm; 
SELECT REPLACE('echo F | copy .' photo ' ./use/' RIGHT(yxdm, 4) '/' ksh2 '.jpg', '/', '') ' /Y' FROM [dbo].[t_student_info] where xxok = '1'

-- powershell & copy-item
SELECT REPLACE('copy-Item -Path .' photo ' -Destination ./use/' RIGHT(yxdm, 4) '/' ksh2 '.jpg', '/', '') FROM [dbo].[t_student_info] where xxok = '1'

-- 指定ks复制到指定目录
SELECT REPLACE('copy-Item -Path ./' sfzh '.jpg -Destination ./' RIGHT(yxdm, 4) '/' ksh2 '.jpg', '/', '') FROM [dbo].[t_student_info] where  sfzh in ('xxxxxx')

Tips : 当我们将两个char、nchar、varchar、nvarchar 、binary、varbinary表达式拼接时，所生成新字符串的长度是两个表达式长度之和。

SQL 内置函数之字符串操作

官方参考地址: https://docs.microsoft.com/en-us/sql/t-sql/functions

LEFT 函数 - 从左边截取字符串

RIGHT 函数 - 从右边截取字符串

SUBSTRING 函数 - 从指定下标开始截取多个字符

描述说明: 在sql server提供了3个常用截取字符串方法它是LEFT()、RIGHT()、SUBSTRING() 基础语法:

代码语言：javascript复制

LEFT(character,integer)  
RIGHT(character,integer)
SUBSTRING(character,start,length)

参数说明:

代码语言：javascript复制

- LEFT : 参数1：要截取的字符串，参数2：截取字符个数
- RIGHT : 参数1：要截取的字符串，参数2：截取字符个数
- SUBSTRING : 参数1：要截取的字符串，参数2：开始截取的下标，参数3：截取的字符长度

基础示例:

代码语言：javascript复制

-- 截取字符串左边7个字符--
select LEFT('Welcome to China!',7) as 结果1
-- 截取字符串右边6个字符--
select RIGHT('Welcome to China!',6) as 结果2
-- 截取字符串中间2个字符（从9号下标开始）
select SUBSTRING('Welcome to China!',9,2) as 结果3

Translate 函数 - 实现指定字符串的批量替换

描述说明: SQL Server 2017 新增 Translate 函数可以实现批量替换。 简单语法: TRANSLATE ( inputString, characters, translations ) 参数说明: 要替换的源字符串,被替换字符,替换成为字符 基础示例:

代码语言：javascript复制

# - A.用普通大括号替换方形大括号和花括号
SELECT TRANSLATE('2*[3 4]/{7-2}', '[]{}', '()()');

# - B.将GeoJSON点转换为WKT
SELECT TRANSLATE('[137.4, 72.3]' , '[,]', '( )') AS Point, TRANSLATE('(137.4 72.3)' , '( )', '[,]') AS Coordinates;

Replace 函数 - 实现指定字符串的替换

简单语法: Replace(被替换的数据库字段名称或字符串,'被替换字符串','被替换的字符') 参数说明: 要替换的数据库字段名或源字符串,被替换字符,替换成为字符

代码语言：javascript复制

SELECT REPLACE(body,'<span>weiyigeek</span>','<span>唯一极客</span>') from blog.content;

SQL 服务器信息

代码语言：javascript复制

select SERVERPROPERTY(N'edition') as Edition --数据版本，如企业版、开发版等
,SERVERPROPERTY(N'collation') as Collation --数据库字符集
,SERVERPROPERTY(N'servername') as ServerName --服务名
,@@VERSION as Version --数据库版本号
,@@LANGUAGE AS Language --数据库使用的语言，如us_english等

SELECT COLLATIONPROPERTY( 'chinese_prc_ci_as', 'codepage' )

执行几个:

代码语言：javascript复制

Enterprise Edition (64-bit)       
Chinese_PRC_CI_AS 
WIN-2605UB613IT        
Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr  2 2010 15:48:46 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
简体中文
936

编程算法 数据库 sql sqlserver javascript

0 人点赞