sqlserver常用操作

公司进行数据存储分析时用到了sqlserver,本文对工作中常用的sqlserver操作进行记录。

  1. 在线数据库
    公司数据库连接不方便,使用线上数据库做测试。地址为:
    https://sqliteonline.com/
  2. sqlserver对象命名方式
    SQL Server 2000及以前:对象命名是“服务器.数据库.用户名.对象” SQL Server 2005及以后:对象命名改为“服务器.数据库.Schema.对象” 对象包括表、视图等内容

  3. 预定义schema
    SQL Server 预定义了一些架构,他们与内置数据库用户和角色具有相同的名称。这些架构主要用于向后兼容性。如果没必要保留这些与固定数据库角色具有相同名称的架构,则可以删除它们。
不能修改或删除下列 4 个预定义的架构。
【sqlserver常用操作】(1)dbo
dbo 是新创建的数据库的默认架构。dbo 架构由 dbo 用户帐户拥有。默认情况下,使用 CREATE USER 语句创建的用户的默认架构为 dbo。dbo 用户与 dbo 架构是不同的概念,分配了 dbo 架构的用户不继承 dbo 用户帐户的权限。
(2)sys
sys 是包含了系统对象的默认架构。系统的元数据、视图、函数等都包含于该架构中。
(3)guest
(4)INFORMATION_SCHEMA
SQL Server 提供了独立于系统表的元数据库视图,INFORMATION_SCHEMA 架构中包含的元数据视图符合 ISO 标准中的信息架构定义。
  1. 默认schema
    从 SQL Server 2005 开始,每个用户都拥有一个默认架构。可以使用 CREATE USER 或 ALTER USER 的 DEFAULT_SCHEMA 选项设置和更改默认架构。如果未定义 DEFAULT_SCHEMA,则数据库用户将使用 dbo 作为默认架构。
    创建用户时,可以使用 WITH DEFAULT_SCHEMA 子句为其指定一个默认的架构。
如果用户具有默认架构,则将使用默认架构。如果用户不具有默认架构,但该用户是具有默认架构的组的成员,则将使用该组的默认架构。如果用户不具有默认架构而且是多个组的成员,则该用户的默认架构将是具有最低 principle_id 的 Windows 组的架构和一个显式设置的默认架构。(不可能将可用的默认架构之一显式选作首选架构。)如果不能为用户确定默认架构,则将使用 dbo 架构。
当使用部分名称来引用数据库对象时,SQL Server 首先在用户的默认架构中查找。如果在此处未找到该对象,则 SQL Server 其次将在 dbo 架构中查找。如果对象不在 dbo 架构中,则会返回一个错误。为了防止缺少架构名称而影响到查询的执行过程,推荐在引用对象时同时指定架构和对象名称。例如:
SELECT ProductID FROM Production.Product

如果在引用对象时未指定schema名称,则使用dbo为默认schema。
  1. 查看sqlserver版本
    select @@VERSION

  2. 查看sqlserver登录用户
    sp_who

  3. 创建schema与view
    CREATE SCHEMA test_schema; GRANT ALTER ON SCHEMA::test_schema TO admin CREATE VIEW test_view as select * from demo

  4. schema/表/view信息查询
    --查询所有表名 SELECT name as TABLE_NAME,xtype as TABLE_TYPE FROM sysobjects where xtype = 'U' orxtype = 'V'--查询所有表名及对应架构 select t.[name] as tablename, s.[name] as [schema] from sys.tables as t,sys.schemas as s where t.schema_id = s.schema_id---查看view及对应架构 select v.[name] as viewname, s.[name] as [schema] from sys.views as v,sys.schemas as s where v.schema_id = s.schema_id

    推荐阅读