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 语句 >