再读sql server 2008入门经典

  • 系统元数据的特定访问路径保存在information_schema, 所以不必使用系统表

select * from information.schema.tables;

  • 注意：ORDER BY子句可使用查询使用的任何表的任何列来排序，而不管其是否在SELECT列表中
  • 注意: 除了COUNT(*)函数之外, 所有的聚合函数都忽略NULL值, 特别注意AVG函数, 在这点上
  • 系统存储过程sp_help, 功能是给出任意数据库对象, 用户自定义数据类型或sql server数据类型的信息

例如: EXEC sp_help Sales

  • 内部连接(INNER JOIN)中的INNER关键字, 不使用的情况更为常见, 所以
  • 外部连接如LEFT OUTER JOIN中的OUTER关键字, 实际也很少使用, 直接LEFT JOIN
  • 完全连接FULL JOIN很少使用, 可以返回两侧表的所有记录, 不匹配值用NULL填充
  • 而交叉连接CROSS JOIN则返回两侧表记录的笛卡尔积, 且不使用ON运算符
  • 联合UNION默认删除重复记录, 即默认使用distinct, 如果需要所有使用UNION ALL
  • 仔细阅读了2遍, 我所理解的SQL Server2008模式是指所有者, 2008之前的版本确实也是这么叫的, 但模式可以为多个登录名所共享, 一个登录名也可以拥有多个模式, 是为解决数据库设计中的组织问题, 但是这些问题通常可以采用其他方法解决;  dbo是默认模式, 相当于透明
  • 创建数据库的一般写法示例:

  • create database Accounting
    on
    (name = 'Accounting',
    filename = 'c:\sql2008\AccountingData.mdf',
    size = 10,
    maxsize = 50,
    filegrowth = 5)
    log on
    (name = 'AccountingLog',
    filename = 'c:\sql2008\AccountingLog.ldf',
    size = 5,
    maxsize = 25,
    filegrowth = 5);
    go

  • exec sp_helpdb 'Accounting'　　--查看数据库结构信息
  • date, time datetime2是SQL Server 2008提供的新数据类型
  • alter database Accounting　　--修改数据库初始大小代码示例

          modify file
          (name = Accounting, size = 20mb);

  • alter table Employees　　--修改表, 增加列

          add PreviousEmployer varchar(30) null,
          DateOfBirth date null,
          LastRaiseDate date not null default '2008-01-01'

  • 表在创建后移动列非常麻烦: 删除引用本表的外键约束再恢复; 索引会自动删除需要恢复; 构建的视图需要重新创建解析

  • use Accounting --删除表
    drop table Customers, Employees

    use master --删除数据库
    drop database Accounting

  • 百度得来如果设置数据库的排序规则Collation=Chinese_PRC_CS_AS, 则会区分大小写
  • 在谈到主键时, 听说只有在处理重复的和其他分布式数据的数据库环境下, 将GUID作为主键才是非常有用的

  • alter table Employees
    add constraint PK_EmployeeID
    primary key (EmployeeID);　　--添加主键约束, 如果创建表时, 直接列后写primary key

  • create table Orders
    (
    OrderID int identity not null primary key,
    CustomerNo int not null foreign key references Customers(CustomerNo),    --添加外键约束
    OrderDate date not null,
    EmployeeID int not null);

  • exec sp_helpconstraint Orders    --查看约束详情

  • alter table Orders
    add constraint FK_EmployeeCreatesOrder
    foreign key (EmployeeID) references Employees(EmployeeID)    --修改表添加外键约束

  • create table OrderDetails
    (
    OrderID int not null,
    PartNo varchar(10) not null,
    Description varchar(25) not null,
    UnitPrice money not null,
    Qty int not null, --下面单独声明主键外键约束， 使语句整洁
    constraint PKOrderDetails primary key (OrderID, PartNo),
    constraint FKOrderContainsDetails foreign key (OrderID)
    references Orders(OrderID)
    on update no action    --注意 NO ACTION是默认动作
    on delete cascade
    );

  • create table Shippers
    (
    ShipperID int identity not null primary key,
    ShipperName varchar (30) not null,
    Address varchar(30) not null,
    City varchar(25) not null,
    State char(2) not null,
    Zip varchar(10) not null,
    PhoneNo varchar(14) not null unique); --创建唯一约束

  • alter table Employees --修改表创建唯一约束
    add constraint AK_EmployeeSSN
    unique(SSN);

  • 唯一约束也可叫替换键(Alternate Key), 想想应该是与主键名称相对应的

  • alter table Customers
    add constraint CN_CustomerDateInSystem
    check (DateInSystem <= getdate()); --check约束

  • Create table Shippers
    (
    ShipperID int identity not null primary key,
    ShipperName varchar(30) not null,
    DateInSystem smalldatetime not null
    default getdate()); --创建DEFAULT约束

  • 增加default的写法是alter table tableName add DF_name default someValue for columnName
  • 添加约束在已有数据表上, 可以使用WITH NOCHECK, 不会检查已有数据

  • alter table Customers
    with nocheck
    add constraint CN_CustomerPhoneNo
    check (Phone like '([0-9][0-9][0-9])[0-9]

  • 禁用约束 alter table Customers nocheck constraint CN_CustomerPhoneNo 只能禁用外键约束与check约束, 不能禁用主键约束与unique约束, default约束也不能禁用
  • 子查询与关联子查询, 更像是SQL语句的艺术升级, 优雅的表达方式, 还有ISNULL()函数
  • 所谓的派生表, 听起来比较陌生, 感觉就是一个查询后跟一个as 表名, 然后当成一个表, 进行连接使用什么的, 但派生表没有索引, 如果数据量很大, 则应使用临时表, 但在上面建立索引
  • 有助于提高性能的EXISTS关键字, 也用于脚本中判断一个对象是否已经存在
  • CAST和CONVERT, 后者提供一些日期格式转换, 但前者与ANSI/ISO兼容, 所以两者都存在的原因
  •  merge语句, 比如应用在统计月份的销售报表, 注意, 语句结束必须以分号结尾, 尽管大部分SQL语句中保留分号是为了向后兼容; 语句中by target是默认, 也可以写 by source,  源指"派生表"

  • merge Sales.MonthlyRollup as smr
    using
    (
    select soh.OrderDate, sod.ProductID, sum(sod.OrderQty) as QtySold
    from Sales.SalesOrderHeader soh
    join Sales.SalesOrderDetail sod
    on soh.SalesOrderID = sod.SalesOrderID
    where soh.OrderDate >= '2003-08-02'
    and soh.OrderDate < '2003-08-03'
    group by soh.OrderDate, sod.ProductID
    ) as s
    on (s.ProductID = smr.ProductID)
    when matched then
    update set smr.QtySold = smr.QtySold + s.QtySold
    when not matched then
    insert (Year, Month, ProductID, QtySold)
    values (datepart(yy, s.OrderDate),
    datepart(m, s.OrderDate),
    s.ProductID,
    s.QtySold)
    output $action,            --还可以带OUTPUT关键字输出数据
    inserted.Year,
    inserted.Month,
    inserted.ProductID,
    inserted.QtySold,
    deleted.Year,
    deleted.Month,
    deleted.ProductID,
    deleted.QtySold;

  • truncate table Sales.MonthlyRollup    --删除表中所有行, 而不记录单个行删除操作, 比 delete语句更快
  • 第一范式: 没有重复数据组, 所有列是原子的
  • 第二范式: 符合第一范式, 每列必须依赖于整个键
  • 第三范式: 符合第二范式, 任何列都不能依赖于非键列; 不可以有派生的数据
  • 今天偶然写T-SQL语句, 给变量赋值一定要前面加个SET, 切记, 如果没有错误揭示, 结果可能是致命的!
  • 堆表是没有群集索引的任意表
  •  
  •  
  •  

  • 表值函数dm_db_index_physical_stats

  • declare @db_id smallint
    declare @object_id int
    set @db_id = DB_ID(N'AdventureWorks2008')
    set @object_id = OBJECT_ID(N'AdventureWorks2008.Sales.SalesOrderDetail')
    select database_id,
    object_id,
    index_id,
    index_depth,
    avg_fragmentation_in_percent,
    page_count
    from sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL, NULL)

  • with check option创建的视图, 可以限制插入/更新到视图中的内容
  • 有两种得到实际视图定义的方法: sp_helptext 和 sys.sql_modules 元数据函数(通过object_id( ) 函数)
  • with encryption选项可以用来加密视图源代码, 务必在别处储存代码, 用于修改!
  • @@identity系统函数提供在当前连接中分配的最近标识值
  • @@rowcount 通过编程来知道影响了多少行
  • 存储过程的输出参数(使用output关键字)和返回值, 应该使用哪种还是有章可循的, 一般来说返回值指示程序运行的状态(结果)
  • 可以利用存储过程返回值来传递消息, 比如返回-1000指示出现的问题是什么, 程序员自己约定的错误(结果)
  • 相比于输出参数, 用户自定义函数返回值更为健壮, 相比较存储过程来说, 可返回标量值和表(可直接用于连接或其他, 表可以开头定义, 函数体各种处理, 最后填充返回)
  • 视图和用户自定函数中添加with schemabinding, 可以防止依赖对象被删除, 目的简单来说就是为了健壮
  • 存储过程和用户自定义函数, 后者似乎更"新"一些, 甚至参数都用小括号了, 前者不用, 初学时会迷糊
  • 有时为反复处理表数据, 比如做包码单处理一包内花型号颜色号的串联问题, 可以定义一个表TYPE, 然后使用用户自定义函数对这个表类型进行处理, 封装功能　　
  • 相比delete表, truncate table算得上是批量操作, 释放表空间而已, 不记录单个行删除操作, 也不会激活触发器
  • 虽然没有人明说, 但THROW仍然是RAISERROR()的替代, throw的语法示例: throw 50000, '错误消息', 1 这其实是抛出自定义的错误消息, 直接用throw也好!
  • 触发器的其他常见用途中提到更新汇总信息, 想起merge来, 这两个应该可以协同工作
  • 看到XML概述章节，完全看不下去，书中跳跃转大，前面还好，中间部分就不知道在讲什么，只看到大串的网址字符的，恶补XML基础，了解了DTD和XML SCHEMA
  • 艰难的第16章，初识XML，能记住的是可以把关系数据SELECT到XML，还可以利用openxml函数把XML数据转换为XML，其它细节都不记得:(
posted @ 2024-03-11 08:37  Captain_Amazing  阅读(4)  评论(0)  编辑  收藏  举报