SQL 基本语法

  SQL数据库设计和高级查询
----------------------------------------------------------------------------------------------------------------------------
2009.2.27  阴
   第一章 数据库设计
----------------------------------------------------------------------------------------------------------------------------
一部份：新单词 
            *****************************************
            * E—R（Entity-Relationship）:实体关系图*
            * Account：账户                         *
            * BBS：论坛                             *
            * customer：客户                        *
            * Normal Formate：正规范                *
           ******************************************
二部分: 主体
1.数据库设计的步骤：
* 需求分析阶段
* 概要设计阶段
* 详细设计阶段

2.系统分析阶段
（1）收集信息
（2）标识对象
（3）标识每个对象需要存储的信息。
（4）标识对象之间的关系

3.绘制E-R(实体-关系图)

1.实体 2.属性 3. 关系 4.映射基数  * 一 对一
                                  * 一对多
                                  * 多对多
                                  * 多对一
5. 实体关系图

* 矩形表示实体集
* 椭圆型表示属性
* 菱形表示关系图
* 直线用来连接属性和实体集，也用来连接实体集和关系图

*****规范设计******
1 第一范式
目标: 确保每列的原子性
即 将每列拆分到最小列
例如：
  顾客表 可以将地址分为 国家 、省、 市
2.第二范式
目标：确保每列都和主见有关系
例如：
  订单表（订单标号、产品标号、订购日期、价格、）
主见为订单标号 但 产品标号和主见并没有关系
确保该表只描述一件事 --订单信息
3. 第三范式
目标：确保每列都和主见列直接相关 而不是间接相关
列如：
订单表（订单标号、顾客标号、订购日期、顾客姓名、）
“顾客姓名”与“顾客标号”冲突

---------------------------------------------------------------------------------------------------------------------------
200-3-2  
      第二章数据库的实现
---------------------------------------------------------------------------------------------------------------------------
一部份：新单词
 
            **********************************
            * insert : 插入                  *
            * delete：删除                   *
            * update ：更新                  *
            * select :查询                   *
            * create : 创建                  *
            * drop:删除                      *
            **********************************

第二部分：
１、创建数据库的语法                               ２ 创建多个数据文件和多个日志文件

create database 数据库名                create database 数据库名
    on [primary]                    on [primary]
     (                          (
       /**数据库文件参数**/                /**数据库文件参数**/
      name='',--文件逻辑名                name='',--文件逻辑名
      filename='',--物理文件名              filename='',--物理文件名
      size=,--文件初始大小                size=,--文件初始大小
      maxsize=,--文件最大容量（一般不用设）        maxsize=,--文件最大容量（一般不用设）
      filegrowth=,--文件增长量               filegrowth=,--文件增长量
      
     )                          ),--注意逗号(
     [log on ]                            
    (                                                       /**另一个文件参数的设定**/
     /**日志文件参数**/                                        )
      name='',--文件逻辑名
      filename='',--物理文件名                              [log on ]  
      size=,--文件初始大小                                   ( /**一个日志文件的参数**/      ),---注意逗号
      maxsize=,--文件最大容量（一般不用设）                   (/**另一个日志文件的参数**/     )
      filegrowth=,--文件增长量
      
    )
  go--批处理关键字
 
2、删除数据库文件
  drop datebase 数据库名
  --------要想创建一个新数据库就要先查询源文件中是否存在相同的数据库，如果有就删除语法如下------
use master --设置当前的数据库为master，以便访问sysdatabase表
go
if exists(select * from sys.sysdatabase where name='库名')
drop database 库名
3、使用SQL语句创建表                          *    删除表
语法                                          *    语法
                                              *   删除表  drop table 表名
creat table 表名                              *   删除表中的数据  delete from 表名 不可删除表示列                 
   (                                          *    删除表中的数据   truncate table 表名  可使表示列重新开始
   字段1 数据类型 列的特征               
   字段2 数据类型 列的特征
   字段3 数据类型 列的特征
    ......
    )
---------------------------------------------------------------------------------------
数据类型分类
类型      * 数据类型 ********  描述 
整型        int                在数据库中占有4个字节
            smallint           存储长限定在一定范围的数据类型有效，占用两个字节
            tinyint             可以存储0~255之间的数字 占用1个字节 存储有限的数字效率高
浮点型      numeric             与decimal相同   numeric(4,1) 表示的是共四位其中小数占一位
            real              
            float
            decimal            
字符型      char               用于存储制定字符串长度  列宽最大长度为8000
            varchar            存储非定长度的字符串
            text               存储较长的字符
unicode 型 nchar             用双字节来存储字符串
           nvarchar         存储可变字节的字符串
           ntext            使用的字符串的字节增加了一倍
是/否类型  bit              其值有 0、1 其中0 返回 false 1 返回true
二进制型   binary            存储最长8000字节长度的制定长度的二进制数
           varbinary          存储最长8000字节长度的非制定长度的二进制数
           image               可变长的二进制字节 最长20亿个字节
货币型     money             用来存储钱数
           smallmoney         
日期型     datetime            精确到毫秒
           smalldatetime       精确到秒
特殊类型    timestamp        可变的时间列 标示唯一
            uniquwidentifie   存储一个列的唯一标识
------------------------------------------------------------------------------------------------
4、创建约束 和删除约束
  * 主键约束 （primary key constranit）:  alter table 表名  add constraint 约束名  primary key (列名)      约束名规范 PK_列名
  * 外键约束 （foreign key constranit）: alter table 表名 add constraint 约束名 foreign key (字表列名) references 主表（列名） 命名规范  FK_列名
* 唯一约束 （unique constraint）: alter table 表名 add  constraint 约束名 unique(列名) 命名规范UQ_列名
* 检查约束（check constraint）: alter table 表名 add constraint 约束名 chack (具体约束)
* 默认约束 （default constraint）:alter table 表名 add constraint 约束名 default () for 列名
 
   删除约束
* alter table 表名 drop constraint 约束名
-----------------------------------------------------------------------------------------------------     
5、创建登陆帐户

1、创建登陆帐户（进入小区）
  * SQL 身份验证 ：适合于非window用户 和Internet 用户 需要提供帐户和密码
exec sp_addlogin '帐户' ,'密码'
* window 身份验证
exec sp_grantlogin 'window域名\帐户'
2  创建数据库用户（进入楼内）
*向数据库中添加用户
exec sp_grantdbaccess '登陆帐户'，'数据库用户'--一般时候不用改名字
3 给数据库用户授权
  grant 权限 on  表名 to 数据库用户
6、删除帐号
--取消对数据库用户的授权
revoke create table to ssh
--当在查询中创建的用户，会自动拥有架构信息，删除用户时会报错
/*
消息 15138，级别 16，状态 1，第 1 行
数据库主体在该数据库中拥有 架构，无法删除。
*/
drop user ssh
--解决办法：首先删除架构，然后删除用户
--删除架构
drop schema ssh
--删除用户
drop user ssh
--删除登录名
--删除windows登录
drop login [MICROSOF-41EECF\ssh]
--删除SQL登录
drop login Song
----------------------------------------------------------------------------------------------------------------------------
第三章 T-SQL编程
1、掌握给变量赋值 2、掌握如何输出显示数据 3、掌握逻辑控制语句 4、理解T-SQL中批处理的概念
----------------------------------------------------------------------------------------------------------------------------
  声明变量语法
   Declare @变量名 数据类型
  
1、使用变量
* 局部变量
  给变量赋值
  set @变量名=值
或 select @变量名=值
*全局变量
  @@error   最后一个T-SQL语句的错误号
  @@identity  最后插入的标识列
  @@language  当前使用的语言的名称
  @@max_connections  可以创建同时连接的最大数目
  @@rowcount      受一个T-SQL语句影响的行数
  @@servername   本地服务器的名称
  @@servicename   该服务器上的SQL服务的名称
  @@timeticks   当前计算机上每刻度的微妙数
  @@transcount 当前打开的事务数
  @@version   SQL Server版本信息
2、输出语句
print '语句'
3、逻辑控制语句
1 if-else 语句
语法
if （条件）
    begin
      语句1
      语句2
       ....
     end
  else
    ...
2、WHILE 循环语句 多与if 套欠使用
while (条件)
  begin
     语句1
    [break]
  end
3、case 多分枝语句

  case
    when 条件1 then 结果1
    when 条件2 then 结果2
    .........
     end
----------------------------------------------------------------------------------------------------------------------------
   第四章  高级查询
1、掌握简单字查询的用法  2、掌握IN字查询的用法 3、掌握EXISTS子查询的用法  4、使用T-SQL语句进行综合查询
----------------------------------------------------------------------------------------------------------------------------
1、简单子查询
语法
select ...from 表 where 字段一>(子查询)
2、IN 和 NOT IN 子查询
使用=、〉、〈，等符号只能返回一条记录  用于返回多条只有用IN了
3、Exists 和 NOT EXISTS 子查询
if Exists(子查询)
   语句
if not EXISTS （子查询）
语句
4。用T-SQL语句进行综合查询
-----------------------------------------------------------------------------------------------------------------------------
第五章  事务索引和试图
1、掌握如何创建事务 、索引、视图
-----------------------------------------------------------------------------------------------------------------------------
1、事务的概念
事务（transaction）是单个的工作单元
事务的四个属性 ＡＣＩＤ
* 原子性（atomicity）
* 一致性（consistency）
* 隔离性（Isolation）
* 持久性（Durability）
2、创建事务

2.1
*开始事务 begin transaction
*提交事务 commit transaction
*会滚事务 rollback transaction
2.2
事务的分类
*显式事务 begin transaction
*隐式事务 set implicit_transaction on 语句
*自动提交 SQL默认每条语句是一个事务

3、索引
3.1 什么是事务索引
* 索引是编排数据的内部方法
* 索引页 类似于汉语字典的目录页
* 唯一索引 不允许有相同值
* 主见索引 按主见查询
* 聚集索引 按物理顺序查询
3.2 创建索引
  使用T—SQL创建语法
create [unique] [clustered|nonclustered] index_name
  on table_name (column_name [,column_name ...])
[with
  fillfactor=x
  ]
* unique 指定唯一索引，可选
* clustered 、nonclustered 指定是聚集索引还是非聚集索引，可选
* fillfactor 表示填充因子

3.3 建立索引的条件
* 该列用于频繁搜索。
* 该列用于对数据进行排序

请不要将下列项设置索引
*列中仅包含几个不同的值
*列中仅包含几行，为小型表创建索引可能不太划算，因为SQL server 在索引中所艘的时间要比在表中进行搜索所花的时间长

4、视图

4.1 什么是视图
   * 视图是查看数据库中一个或多个表中的数据的方法。视图是虚拟表 。
   视图通常要进行一下3项操作
   * 筛选表中的行
   * 防止未经许可的用户访问敏感数据
   * 将多个物理表抽象成一个虚拟表
如何创建

   使用T—SQL创建视图
create view view_name as <select 语句>
  查询视图  select * from 视图名

 
----------------------------------------------------------------------------------------------------------------------------
第六章  存储过程
1、了解存储过程的好处   2、掌握常用的系统存储过程 3、 掌握如何创建存储过程  4 、掌握如何调用存储过程
----------------------------------------------------------------------------------------------------------------------------  1、什么是存储过程

存储过程类似于 java 中的方法
   它有以下的优点
   * 允许模式化程序设计
   * 更快的执行
   * 减少网络流量
  分类
  * 系统存储过程
  * 用户自定义的
2、常用系统存储过程
     exec  sp_database     列出服务器上的所有数据库
     exec sp_helpdb       报告有关制定数据库或所有数据库信息
     exec  sp_renamedb     重命名制定数据库
     exec  sp-tables       返回当前环境下可查询的对象列表
     exec  sp-columns       返回某个列表的信息
     exec  sp_help          查某个表的所有信息
     exec  sp_helpconstraint察看某个表的约束
     exec  sp-helpindex     察看某个表的索引
     exec  sp_stored_procedure 列出当前环境中所有存储过程
     exec  sp-password       添加或修改登陆帐户的密码
     exec  sp-helptext   显示默认值 为加密的存储过程用户自定义的存储过程、触发器、或试图和实际的文本
  
    
3、 用户自定义
   create procedure 存储过程
    @参数1
    @参数2
    ....
    as
  < sql 语句 >