SQL主要内容(一)

一、SQL Server 2000常见的版本
企业版（Enterprise Edition）
标准版（Standard Edition）
个人版（Personal Edition）
开发者版（Developer Edition）

二、SQL Server 系统数据库简介
SQL Server 2000有4个系统数据库
1. Master数据库是SQL Server系统最重要的数据库，它记录了SQL Server系统的所有系统信息。这些系统信息包括所有的登录信息、系统设置信息、SQL Server的初始化信息和其它系统数据库及用户数据库的相关信息。
2. Model数据库是所有用户数据库和Tempdb数据库的模板数据库，它含有Master数据库所有系统表的子集，这些系统数据库是每个用户定义数据库需要的。
3. Msdb数据库是代理服务数据库，为其警报、任务调度和记录操作员的操作提供存储空间。
4. Tempdb是一个临时数据库，它为所有的临时表、临时存储过程及其它临时操作提供存储空间。

三、SQL Server 系统表简介
1. Sysobjects表
   SQL Server 的主系统表sysobjects出现在每个数据库中，它对每个数据库对象含有一行记录。
   如：检查mydb数据库是否存在student表
  use mydb
   select * from sysobjects where name='student' --表枚举的系统表

2. Syscolumns表
   系统表syscolumns出现在master数据库和每个用户自定义的数据库中，它对基表或者视图的每个列和存储过程中的每个参数含有一行记录。
   如：列出student表中的所有的字段信息
  select * from syscolumns where id=object_id('student') --列枚举的系统表
  --name字段：列名
   --id字段：所属的表代号
   --xusertype字段：外键，对应systypes表的xusertype字段，该列的类型
   --length字段：列的长度
   --colorder字段：字段的显示顺序
   --isnullable字段：是否允许为空 0-不允许 1-允许
  
3. Sysdatabases表
   系统表sysdatabases对SQL Server 系统上的每个系统数据库和用户自定义的数据库含有一行记录，它只出现在master数据库中。
   如：检查数据库中是否已存在mydb数据库
  use master
   select * from sysdatabases where name='mydb' --数据库枚举的系统表
4. systypes表
   保存有SQLServer数据库中所有的数据类型
   如：列出数据库中所有的类型
  select * from systypes --类型枚举系统表
   --name字段：类型名称
   --xusertype字段：类型代号
  
   --综合示例：获取SqlServer中表结构
  SELECT syscolumns.name,systypes.name,syscolumns.isnullable,
   syscolumns.length
   FROM syscolumns, systypes
   WHERE syscolumns.xusertype = systypes.xusertype
   AND syscolumns.id = object_id('student')

四、数据类型：
整数数据类型 INT SMALLINT TINYINT BIGINT
浮点数据类型 REAL FLOAT DECIMAL NUMERIC
二进制数据类型 BINARY VARBINARY
逻辑数据类型 BIT
字符数据类型 CHAR NCHAR VARCHAR NVARCHAR
文本和图形数据类型 TEXT NTEXT IMAGE
日期和时间数据类型 DATETIME SMALLDATETIME
货币数据类型 MONEY SMALLMONEY

五、完整性与约束
完整性分为：实体完整性与引用完整性
列属性
  NULL-允许在插入数据时省略该列的值
  NOT NULL-不允许在插入数据时省略该列的值
  IDENTITY
    属性可以使表的列包含系统自动生成的数字。IDENTITY 属性适用于INT SMALLINT TINYINT DECIMAL列。
约束：在SQL Server 中有5 种约束
  主关键字约束Primary Key Constraint
   外关键字约束Foreign Key Constraint
   惟一性约束Unique Constraint
   检查约束CheckConstraint
   缺省约束Default Constraint
  
1.主关键字约束指定表的一列或几列的组合的值在表中具有惟一性，每个表中只能有一列被指定为主关键字。
   IMAGE 和TEXT 类型的列不能被指定为主关键字，也不允许指定主关键字列有NULL 属性
   语法如下：
  CONSTRAINT constraint_name PRIMARY KEY [CLUSTERED | NONCLUSTERED] column_name1[, column_name2,…,column_name16
   如：
    create table products (
    p_id char(8) not null,
    p_name char(10) not null ,
    price money default 0.01 ,
    quantity smallint null ,
   constraint pk_p_id primary key (p_id, p_name)
    )

2.外关键字约束：外关键字约束定义了表之间的关系，确保表间的引用完整性。也可以设置主从表之间的级联更新与级联删除
   不能使用一个定义为TEXT 或IMAGE 数据类型的列创建外关键字
   外关键字最多由16 个列组成
   语法如下
   CONSTRAINT constraint_name
   FOREIGN KEY (column_name1[, column_name2,…,column_name16])
   REFERENCES ref_table [( ref_column1)[,(ref_column2),…, (ref_column16)] ]
   [ ON DELETE { CASCADE | NO ACTION } ]
   [ ON UPDATE { CASCADE | NO ACTION } ] ]
   如：
   create table orders(
    order_id char(8),
    p_id char(8),
    p_name char(10) ,
   constraint pk_order_id primary key (order_id) ,
    foreign key(p_id, p_name) references products(p_id, p_name)
    )

3.惟一性约束：用以防止在列中输入重复的值。惟一性约束指定的列可以有NULL 属性，主关键字列不能再设定惟一性约束。
   语法如下
   CONSTRAINT constraint_name UNIQUE [CLUSTERED | NONCLUSTERED] column_name1[, column_name2,…,column_name16]
   如：
    create table employees (
    emp_id char(8),
    emp_name char(10) ,
    emp_cardid char(18),
   constraint pk_emp_id primary key (emp_id),
    constraint uk_emp_cardid unique (emp_cardid)
    )

4.检查约束：检查约束对输入列或整个表中的值设置检查条件，以限制输入值保证数据库的数据完整性。
语法如下
  CONSTRAINT constraint_name CHECK [NOT FOR REPLICATION] (logical_expression)
如：创建一个订货表其中定货量必须不小于10
create table orders(
   order_id char(8),
   p_id char(8),
   p_name char(10) ,
   quantity smallint,
  constraint pk_order_id primary key (order_id),
   constraint chk_quantity check (quantity>=10) ,
   )

5.缺省约束
   语法如下
   CONSTRAINT constraint_name DEFAULT constant_expression [FOR column_name]
六、常用函数
CEILING(numeric) 返回>=表达式的最小整数,返回的数据类型与表达式相同,可为INTEGER MONEY,REAL FLOAT 类型
FLOOR(numeric) 返回<=表达式的最小整数,返回的数据类型与表达式相同.可为INTEGER MONEYREAL FLOAT 类型
ROUND(integer) 返回以integer_expression 为精度的四舍五入值,返回的数据类型与表达式相同,可为INTEGER MONEY REAL FLOAT 类型
PI          返回值为即3.1415926535897936
RAND(integer)   用任选的[integer_expression]做种子值得出0-1 间的随机浮点数
ASCII()函数返回最左端字符的ASCII码值 ASCII(<character_expression>)
CHAR()函数用于将ASCII 码转换为字符 CHAR(<integer_expression>)
LOWER()函数把字符串全部转换为小写   LOWER(<character _expression>)
UPPER()函数把字符串全部转换为大写   UPPER(<character _expression>)
STR()函数把数值型数据转换为字符型数据 STR(<float _expression>[ length[ <decimal>]])
LTRIM()函数把字符串头部的空格去掉 LTRIM(<character _expression>)
RTRIM()函数把字符串尾部的空格去掉 RTRIM(<character _expression>)
LEFT()函数返回部分字符串 LEFT(<character_expression> <integer_expression>)
RIGHT()函数返回部分字符串 RIGHT(<character_expression> <integer_expression>)
SUBSTRING()函数返回部分字符串 SUBSTRING(<expression> <starting_ position> length)
CHARINDEX()函数返回字符串中某个指定的子串出现的开始位置 CHARINDEX <’substring_expression’> <expression>
REPLICATE()函数返回一个重复character_expression 指定次数的字符串 REPLICATE(character_expression integer_expression)
REVERSE()函数将指定的字符串的字符排列顺序颠倒 REVERSE(<character_expression>)
REPLACE()函数返回被替换了指定子串的字符串 REPLACE (<string_expression1> ,<string_expression2>, <string_expression3>)
SPACE()函数返回一个有指定长度的空白字符串 SPACE(<integer_expression>)
CAST()函数语法如下 CAST(<expression> AS <data_ type>[ length ])
DAY() 返回date_expression 中的日期值 DAY(<date_expression>)
MONTH()返回date_expression 中的月份值 MONTH(<date_expression>)
YEAR()返回date_expression 中的年份值 YEAR(<date_expression>)
DATEADD 函数返回指定日期date 加上指定的额外日期间隔number 产生的新日期 DATEADD(<datepart>,<number>,<date>)
DATEDIFF()返回date2 超过date1的差距值,其结果值是一个带有正负号的整数值 DATEDIFF(<datepart>,<date1>,<date2>)
DATEPART() 以整数值的形式返回日期的指定部分 DATEPART(<datepart>,<date>)
  DATEPART(dd,date)等同于DAY(date)
   DATEPART(mm,date)等同于MONTH(date)
   DATEPART(yy,date)等同于YEAR(date)
GETDATE()以DATETIME 的缺省格式返回系统当前的日期和时间
ISDATE ()判断所给定的表达式是否为合理日期,如果是则返回1,不是则返回0. ISDATE(<expression>)

七、Transact-SQL语言
主要组成部分
   数据定义语言（DDL，Data Definition Language）
   数据操纵语言（DML，Data Manipularion Language）
   数据控制语言（DCL，Data Control Language）
   系统存储过程（System Stored Procedure）
   一些附加的语言元素
1.数据定义语言（DDL）：数据定义语言是指用来定义和管理数据库以及数据库中的各种对象的语句。包括CREATE、ALTER和DROP等语句。
创建表：
    CREATE TABLE publishers
    (
      pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756') OR pub_id LIKE '99[0-9][0-9]'),
      pub_name varchar(40) NULL,
      city varchar(20) NULL,
      state char(2) NULL,
      country varchar(30) NULL DEFAULT('USA')
    )
修改表：
    A. 更改表以添加新列
    ALTER TABLE doc_exa ADD column_b VARCHAR(20) NULL
    B. 更改表以除去列
     ALTER TABLE doc_exb DROP COLUMN column_b
    C. 更改表以添加具有约束的列
    ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE
    D.更改表以添加未验证的约束
    ALTER TABLE doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a > 1)
    F. 添加具有默认值的可为空的列
    ALTER TABLE MyTable ADD AddDate smalldatetime NULL CONSTRAINT AddDateDflt DEFAULT getdate() WITH VALUES

综合示例：
    创建一个定货表再插入一个定货商编号列
   create table orders
    (
    order_id char(8) ,
    p_id char(8) foreign key references products(p_id),
    order_quantity smallint check (order_quantity>=10),
    constraint pk_order_id primary key (order_id),
    )
    alter table orders add order_firm_id char(8) null
    constraint fk_order_firm_id foreign key references order_firm(order_firm_id)
    更改上例中的检查约束并删除一个外关键字约束
   alter table orders add constraint chk_order_quantity check (order_quantity>=100)
    drop constraint chk_order_quantity
   
2.数据操纵语言：用来查询、添加、修改和删除数据库中数据的语句，这些语句包括SELECT、INSERT、UPDATE、DELETE等。
  
  简单查询：
    选择列
    a .用SELECT 子句来指定查询所需的列多个列之间用逗号分开
   select p_id, p_name, cost from products
    b .可以使用符号* 来选取表的全部列
   select * from employee
    c. 在查询结果中添加列
    查询产品的编号、名称、库存数量和成本，并计算每种产品的总成本价值
   select p_id, p_name, quantity, cost, cost*quantity as sum_cost from products
   选择行
    a .使用WHERE 子句
    用户在查询数据库时，往往并不需要了解全部信息，而只需要其中一部分满足某些条件的信息。在这种情况下，就需要在SELECT 语句中加入条件以选择数据行这时就用到WHERE 子句。WHERE 子句中的条件是由表达式以及逻辑联结词AND、OR、NOT 等组成。
    如：查询工资介于2000 元和3000 元之间的员工姓名
   select e_name
    from employee
    where e_wage between 2000 and 3000
    b .使用DICTINCT 关键字
    用户在对数据库进行查询时，会出现重复结果，这时就需要使用DISTINCT 关键字消除重复部分。
    如：列出工资大于7000 的员工所属的部门编号
   select distinct dept_id
    from employee
    where e_wage > 7000
    c .使用IN 关键字
    在使用WHERE 子句进行查询时，若条件表达式中出现若干条件相同的情况，就会使表达式显得冗长，不便于用户使用，这时可用IN 关键字来简化。
    查询在编号为1001 和1002 的部门中工作的员工姓名
   select e_name
    from employee
    where dept_id in (’1001’,’1002’)
    d .使用通配符
    在WHERE 子句中可以使用谓词LIKE 来进行字符串的匹配检查，其中将大量使用通配符
    如：查找公司中所有姓王且全名为两个字的员工的姓名所在部门编号
   select e_name, dept_id
    from employee
    where e_name like ’王__’
    % 任意多个字符
     _ 一个任意字符
     [] 其中的一个字符
     {^} 非其中的一个字符
  对查询结果排序
    a .使用ORDER 子句
    当用户要对查询结果进行排序时，就需要在SELECT 语句中加入ORDER BY 子句。在ORDER BY 子句中可以使用一个或多个排序要求，其优先级次序为从左到右。
    如：查询工作级别为2 的员工姓名查询结果按工资排序
   select e_name
    from employee
    where job_level = ’2’
    order by e_wage
     order by可以实现以多个字段的排序，主排序字段、次排序字段......
    对查询结果排序
    b .选取前几行数据
    在SELECT 语句中使用TOP n 或TOP n PERCENT，来选取查询结果的前n 行或前百分之n 的数据。此语句经常和ORDER 子句一起使用。
    如：查询工资最高的三名员工的姓名和工资
   select top 3 e_name, e_wage
    from employee
    order by e_wage desc
    top n 查询前n条数据
     top n percent 查询前百份之n的数据
  统计函数
   AVG()函数返回有关列值的算术平均值此。函数只适用数值型的列。
   COUNT()函数返回与选择表达式匹配的列中不为NULL 值的数据个数。
   MAX()函数返回某一列的最大值，此函数适用于数值型、字符型和日期型的列。
   MIN()函数返回某一列的最小值，此函数适用于数值型、字符型和日期型的列。
   SUM()函数用来返回诸如列值这样的实体的总和。此函数只适用于数值型的列。
   
查询结果分组
    a .使用GROUP 子句
    查询各部门中的最高工资数额
   select dept_id, max(e_wage) as max_wage
    /* 使用as 字符来指定统计列的名称 */
    from employee
    group by dept_id
    b.使用HAVING 子句
    HAVING 子句用来选择特殊的组，它将组的一些属性与常数值进行比较，如果一个组满足HAVING 子句中的逻辑表达式，它就可以包含在查询结果中。
    如：查询有多个员工的工资不低于6000 的部门编号
   select dept_id, count(*)
    from employee
    where e_wage >= 6000
    group by dept_id
    having count(*) > 1
     在select语句中where,group by,having子句和统计函数的执行次序如下：
    Where子句从数据源中去掉不符合其搜索条件的数据行;
     group by 子句搜集数据行到各个组中;统计函数为各个组计算统计值;
     having子句去掉不符合其组搜索条件的各组数据行。
  添加数据
   INSERT [INTO]
    { table_name| view_name}
    { [(column_list)]
    { VALUES ( { DEFAULT | NULL | expression }[,...n] )
    | derived_table
    | execute_statement } }
    | DEFAULT VALUES
    当插入varchar或text类型的数据时，其后的空格将被去掉，如果插入一个只含空格的字符串，则会被认为插入了一个长度为0的字符串。
   Identity列不能指定数据，在values列表中应跳过此列。
   DELETE语法：
   delete from 表名 [where 条件]
    如果省去了where子句则会删掉表中的所有数据，但表的结构依然存在。
  TRUNCATE TABLE 命令
    如果要删除表中的所有数据，那么使用TRUNCATE TABLE 命令比用DELETE 命令快得多。因为DELETE 命令除了删除数据外，还会对所删除的数据在事务处理日志中作记录。而TRUNCATE TABLE只做删除与表有关的所有数据页的操作。
    命令语法如下：
   TRUNCATE TABLE table_name
  更新数据
   UPDATE
    { table_name | view_name}
    SET
    {column_name = {expression | DEFAULT | NULL}
    | @variable = expression
    | @variable = column = expression } [,...n]
    {{[FROM {<table_source>} [,...n] ]
    [WHERE <search_condition>] }
  
连接查询
   可以用两种方式来指定连接条件
   用WHERE 子句指定连接条件
    在FROM 子句中用JOIN 指定连接条件
   笛卡尔积：
   用WHERE 子句指定连接条件
   select distinct orders.p_id, p_name from orders, products where orders.p_id = products.p_id
   在FROM 子句中用JOIN 指定连接条件
   select distinct orders.order_id, p_name from orders join products on orders.p_id = products.p_id
   自连接查询
  select a.emp_id, a.e_name, b.emp_id, b.e_name, a.hire_date from employee a join employee b on a.emp_id!=b.emp_id and a.hire_date = b.hire_date
   where a.e_name < b.e_name order by a.hire_date
  外部连接分为左外部连接和右外部连接
    主表在左边则称为左外部连接
    主表在右边则称为右外部连接
   select a.order_id, b.f_name,c.p_name from orders a left join firms b on a.firm_id = b.firm_id right join products c on c.p_id = a.p_id order by a.o_date
   
合并查询
   合并查询就是使用UNION 操作符，将来自不同查询的数据组合起来，形成一个具有综合信息的查询结果。
    UNION 操作会自动将重复的数据行剔除
    参加合并查询的各子查询的使用的表结构应该相同
    各子查询中的数据数目和对应的数据类型都必须相同
嵌套查询
   在一个SELECT 语句的WHERE 子句或HAVING 子句中嵌套另一个SELECT 语句的查询，称为嵌套查询又称子查询。
  无关子查询--外查询和内查询之间没有任何逻辑上的联系
   相关子查询--外查询和内查询彼此依赖
  语法：
    SELECT <目标表达式1>[,… ] FROM <表或视图名1>
    WHERE [表达式]
    （SELECT <目标表达式2>[,…] FROM <表或视图名2>）
    [GROUP BY <分组条件>
    HAVING [<表达式>比较运算符]
    （SELECT <目标表达式2>[,… ] FROM <表或视图名2> ]）
   (车延禄)
   无关子查询
   select f_name from firms where firm_id in (select firm_id from orders where orders.p_id = (select p_id from products where p_name = '光纤'))
   相关子查询
   select p_name from products where exists (select p_id from orders where orders.p_id = products.p_id and orders.o_quantity > 7000)