sql server 杂记

sql server 杂记

sql server 中也具有标准SQL的三层结构：

• 数据库(DATABASE)——模式/框架(SCHEMA)——表(TABLE)。

但是在对某个表进行操作时，会有下面这三条语句：

SELECT * FROM relevantDB.relevantSchema.someTable;
SELECT * FROM relevantSchema.someTable;
SELECT * FROM someTable;

• 第一条语句指明表的完整结构；
• 第二条语句当用户目前使用的 DB是表的relevantDB时，则等价于第一条；
• 第三条语句仅当既满足前诉条件，又满足用户的默认框架(DEFAULT_SCHEMA)是relevantSchema时，才等价于第一条。

---

什么是用户的默认框架？用户是指登录用户(LOGIN)还是用户(USER)？

1. 登录用户(LOGIN)是面向“登录”这个动作而言的，它可以设置有无登录所需的密码，登录后默认的数据库。
2. 用户(USER)是面向登录后的“数据库系统”而言的，也就是一个登录用户完成登录后，切换到了它相应的USER身份。USER可以设置操作数据库系统的各种权限。所以提到用户的各种数据库属性时，用户便是指USER。一个DB的所有USER保存在各自的数据库中（someDB->Security->Users）。
3. 用户的默认框架，是指一个用户在一个DB中的缺省框架（不同DB中有不同的缺省框架）。sql server在不指明用户的默认框架时，设置为dbo。
4. dbo是sql server为每一个DB的管理员USER(在该DB的db_owner中) ，同时也创建了一个名为：dbo的框架。
5. 框架是针对于数据库而言的（符合标准的三层结构），它保存在各自的数据库中（someDB->Security->Schemas）。

---

实验一

1.1

• 主要设置为让新建的用户默认数据库为TPCH，默认模式为Sales，以及在TPCH数据库中可以显示中文。

USE master
GO

CREATE DATABASE TPCH;

-- set the default db of login user as TPCH
CREATE LOGIN experiment 
    WITH PASSWORD = 'Strong_password', 
    DEFAULT_DATABASE = TPCH;
GO

USE TPCH;
GO

CREATE SCHEMA Sales;
GO

-- set the default schema of user as 'Sales'
CREATE USER yang for LOGIN experiment 
    WITH DEFAULT_SCHEMA = Sales;

-- grant the administrator right of cur db to yang
exec sp_addrolemember 'db_owner', 'yang';   
GO

-- set the default language of cur db as 'Simplified Chinese'
DECLARE @tmp int;
SELECT @tmp = (
    SELECT langid 
    FROM master.dbo.syslanguages 
    WHERE alias = 'Simplified Chinese'); 
EXEC sp_configure 'default language', @tmp; 
GO  

RECONFIGURE ;  
GO

-- set the database sorting as Chinese when the default setting is latin language
-- it can really help to display Chinese
-- but this setting only works for the new created table 
ALTER DATABASE TPCH COLLATE Chinese_PRC_90_CI_AS
GO

• 前面的配置，只是让在数据库中进行 insert语句时可以插入带中文的元组，以及select语句时可以正常显示而不至于乱码。
• 在sql server中，字符char是单字节字符，而一个中文字是占用的两个字符，这就是说，如果声明name CHAR(2)，实际上只能插入一个中文字符。但是sql server中有一种新字符类型，NCHAR或者NVARCHAR，其表示一个字符占用两个字节，即name NCHAR(1)，可以插入一个中文字符。

---

1.2

• 主要设置是，书中的声明字符长度不够，需要自行更改各表中列的属性（或者建表时改表属性）。

  -- change Sales.Part.mfgr to NCHAR(25)
  alter table Sales.Part
  alter column mfgr NCHAR(25); 
  

• 批量导入数据，批量导入需要一个用户需要具备ADMINISTER BULK OPERATIONS权限，但是由于我不会授予自己的实验用户该权限，所以都是用SA用户来进行数据导入的。

• 数据文本编码格式：在一般windows（简体中文）下的数据文本采用的是GB 2312，不幸的是自己尝试用linux 各种编码打开实验的数据集，都始终有些编码错误。最后找到一个方法，用wps 表格打开各个数据集，再保存一下（不保留原有功能），文本编码便成了utf-8，再借助vscode或者mssql IDE，将其保存为utf-16，数据集中便没有任何乱码。（听说windows下不需要进行文本编码的转换，请自行尝试）

  -- normal bulk insert using absolute path
  -- only works when date file has the same colcumns with table
  BULK INSERT Sales.Region
      FROM "/home/yang/Desktop/book/database/experiment/dbtestdata/region.csv" 
      WITH (FORMAT = 'CSV');
  GO
  

• 当数据集中的数据只有表中的某几个属性时，或者数据列不对应时，需要借助bcp以及非xml结构进行格式化导入，详情见sql server官方文档说明。

  // use shell to run
  // argument -c is for CHAR varians
  ~: bcp TPCH.Sales.Part format nul -S localhost -U SA -P yourPasswd -f Part_Default.fmt -c
  // argument -w is for NCHAR varians
  ~: bcp TPCH.Sales.Part format nul -S localhost -U SA -P yourPasswd -f Part_Default.fmt -w
  

• 下面是生成的非xml结构文件，我是用了-w参数生成，因为表中用的是NCHAR属性，当然也可以使用CHAR属性，当时字符长度要到相应长度，直到可以容纳数据集中的数据。

  14.0
  8
  1       SQLNCHAR            0       24      "\t\0"   1     partkey                                          ""
  2       SQLNCHAR            0       110     "\t\0"   2     name                                             Chinese_PRC_90_CI_AS
  3       SQLNCHAR            0       50      "\t\0"   3     mfgr                                             Chinese_PRC_90_CI_AS
  4       SQLNCHAR            0       50      "\t\0"   4     type                                             Chinese_PRC_90_CI_AS
  5       SQLNCHAR            0       24      "\t\0"   5     size                                             ""
  6       SQLNCHAR            0       20      "\t\0"   6     container                                        Chinese_PRC_90_CI_AS
  7       SQLNCHAR            0       60      "\t\0"   7     retailprice                                      ""
  8       SQLNCHAR            0       46      "\n\0"   8     comment                                          Chinese_PRC_90_CI_AS
  

  • 文件内容说明，图片来自官网文档。

• 由于数据集中只有partkey，name，mfgr，type，retailprice这几列，有两种修改方式：

• 一：

  14.0
  8
  1       SQLNCHAR            0       24      ",\0"   1     partkey                                          ""
  2       SQLNCHAR            0       110     ",\0"   2     name                                             Chinese_PRC_90_CI_AS
  3       SQLNCHAR            0       50      ",\0"   3     mfgr                                             Chinese_PRC_90_CI_AS
  4       SQLNCHAR            0       50      ",\0"   4     type                                             Chinese_PRC_90_CI_AS
  5       SQLNCHAR            0       0      ""   5     size                                             ""
  6       SQLNCHAR            0       0      ""   6     container                                        Chinese_PRC_90_CI_AS
  7       SQLNCHAR            0       60      "\n\0"   7     retailprice                                      ""
  8       SQLNCHAR            0       0      ""   8     comment                                          Chinese_PRC_90_CI_AS
  

  • 即没有的属性列长度改为0，且终止符改为空，而存在的属性列终止符即按相应的分割符修改（如果使用的是CHAR类型，这里的宿主文件数据类型显示的是：SQLCHAR；相应的，终止符也就不需要后面的"\0"，只要","即可）。

• 二：

  14.08
  5
  1       SQLNCHAR            0       24      ",\0"   1     partkey                                          ""
  2       SQLNCHAR            0       110     ",\0"   2     name                                             Chinese_PRC_90_CI_AS
  3       SQLNCHAR            0       50      ",\0"   3     mfgr                                             Chinese_PRC_90_CI_AS
  4       SQLNCHAR            0       50      ",\0"   4     type                                             Chinese_PRC_90_CI_AS
  5       SQLNCHAR            0       60      "\n\0"   7     retailprice                                      ""
  

  • 删除文件中不存在的属性列，修改相应列的终止符，修改宿主文件字段顺序以及列数。
  • PS：==列数指：数据文件总列数；宿主文件字符顺序指：数据文件的列顺序；服务器列顺序指：表中该属性的列顺序。==也就是将相应的数据文件的列，输入到表中相应属性的列。

• 利用修改后的非xml文件进行格式化插入：

  BULK INSERT Sales.Part
      FROM "/home/yang/Desktop/book/database/experiment/dbtestdata/part.csv"
      WITH (FORMATFILE = '/home/yang/Desktop/book/database/experiment/dbtestdata/Part_Default.fmt');
  GO
  

  • 如果报出下面的错误，表明表中name属性的长度容不下数据的长度，需要自行更改表中长度。

  Bulk load data conversion error (truncation) for row 1, column 2 (name).
  

  -- change Sales.Part.name to CHAR(100)
  alter table Sales.Part
  alter column name CHAR(100); 
  

  • 相应的非xml文件中，该属性的宿主文件数据长度也需要修改，CHAR(100)对应的是100，NCHAR(100)对应的是200，自行修改成正确的。

• 使用update语句更新相应表的属性（实验书中有提示，1.1每个表后面），使用的是实验账号，默认模式是Sales，故不需指明Sales。

  UPDATE Lineitem 
  SET extendedprice = quantity * Part.retailprice, tax = 0
      FROM Part
      WHERE Lineitem.partkey = Part.partkey;
  GO