sql server 笔记

用SQL语句建立数据库:

create database hfitu
on
(
 name='hfitudata1',
 filename='c:/data/it1.mdf',
 size=1,
 maxsize=5,
 filegrowth=1
)log on
(
 name='hfitudate11',
 filename='c:/data/it11.ldf',
 size=1,
 maxsize=5,
 filegrowth=5
)

查看:
sp_helpdb it

用SQL语句建立数据库(2)
create database it
on
(
 name='it1',
 filename='c:/data/it1.mdb',
 size=1,
 maxsize=5,
 filegrowth=1
),
(
 name='it2',
 filename='c:/data/it2.mdb',
 size=1,
 maxsize=5,
 filegrowth=50%
)log on
(
 name='it11',
 filename='c:/data/it11.ldb',
 size=1,
 maxsize=5,
 filegrowth=120%
)

修改数据库:
1) 添加数据文件
alter database it
add file
(
 name='wxbing',
 filename='c:/data/wxbing.mdb',
 size=2,
 maxsize=10,
 filegrowth=50%
)
sp_helpdb it

2)添加日志文件
alter database hfitu
add log file
(
 name='itu',
 filename='c:/data/itu1.ldb',
 size=2,
 maxsize=15,
 filegrowth=120%

)
sp_helpdb it

3)文件删除 
alter database hfitu
remove file itu

4) 文件属性设置
alter database it
modify file
(
 name='wxbing',
 size=5,
 maxsize=5,
 filegrowth=1
)

5) 数据库名修改
sp_renamedb it,itu

删除数据库

use itu

drop database hfitu

sp_  存储过程

 

 

 

数据类型:

1)整型数据类型:  bit(1/0)   int   smallint   tinyint(0-255)   bigint
2)浮点型(实型):  real       float
3)字符型:        char(n)    varchar(n)       text
4)二进制型:      binary     varbinary        image
5)时间型:        datetime   smalldatetime
6)货币型:        money    

自定义数据类型:

sp_addtype age(自定义数据类型名),'varchr(n)'(数据类型),'not null'(是否为空值)

注:在"企业管理器"中查看自定义数据类型必须显示所有系统数据库和系统对象.

 

变量:

定义变量:   declare @变量名 数据类型 ,@变量名 数据类型  ....

赋值:
1)set @变量名=值   2) select @变量名=值
注:set 不可在一行中赋多个变量,而select可以.

输出(打印)变量名:
1)print @变量名    2)select @变量名
注:print 不可在一行中输出多个变量的值,而select可以.

例:(1) declare @it varchar(10),@ed char(10)
       set @it='sje'
       set @ed='hfit'
       print @it
       print @ed

   (2) declare @age  tinyint,@name varchar(10)
       select @age=12,@name='wxb'
       select @age,@name

 

 

 

给变量赋值后输出时字段命名：
1、 select @变量 as 字段名
2、 select @变量 字段名
3、 select 字段名=@变量

eg、
declare @myname varchar(10),@myage tinyint,@myaddr varchar(20)
select @myname='wxb',@myage=20,@myaddr='ahlj'
select @myname  as name,@myage as age,@myaddr as address

全局变量：是由系统给定的，不能由用户创建变量
一般用@@开头
eg.@@error 报告上次SQL 操作时是否错误 如果正确返回为0 如果错误则返回为非0的数（也就是错误编号）

@@rowcount  影响行数 

select @@rowcount 用来表达操作的行数

 

创建表（用T-SQL语句创建）

create table 表名
(
 字段名1 类型  是否为空值，字段名2 类型 是否为空值

)

修改表

利用企业管理器来修改：右击-设计表
添加字段:
     alter  table 表名
     add 字段名称 类型
删除字段:
     alter  table 表名
     drop colunm 字段名
修改字段:
     alter  table 表名
     alter colunm 字段名 类型  是否为空值

注：如果表中有主键字段，必须先删除主键才能修改表中的字段

表名修改：  sp_rename 旧表名，新表名

字段名修改：sp_rename '表名.字段名' ，新字段名

向表中添加记

insert [into] 表名 (字段列表) values （值）

注：如果要某表所有字段列表赋值，那么内容字段列表可以省略
    表中所有的not null的字段必须为其赋值.
    为表中的所有的字段赋值,可以依次写出所有所有字段,也可以省略不写.
eg.   insert into 表名  values(........)
insert into wxb values ('wdh' ,'男',19)

 

 

查看表中的字段:   sp_help 表名

查询表中的记录:     select * from 表名 (查询表中所有的字段)
查询表中指定的记录: select 字段名1,字段名2....from 表名 where 条件
指定条件查询:      select 字段名 from 表名 where 条件

修改表中的记录:  update 表名 set 字段=值,字段2=值2...where 条件  
                update 字段初值为0的 字段=字段+值不行;
删除记录:       delete [from] 表名  where 条件

删除表:  drop table 表名1,表名2...

数据库的附加和分离:
附加:数据文件载入到企业管理器中.
右击数据库->所有任务->附加数据库

用SQL语句:

sp_attach_db @dbname='数据库名',
@filenameN='路径.mdf',
@filenameN='路径.ldf'
N最大为16

 

分离:把数据库文件从企业管理器中分离出来.
右击数据库->所有任务->分离数据库

用SQL语句:

sp_detach_db '数据库名'

 

 

标识符: (企业管理器） 右击表--设计表--光标定位到要定义的字段,
        条件:
 1、该列的数据类型必须为整型
 2、该列的值不允许为空
注意： 标识符的列是不可编辑，且一个表中只允许有一个标识符。

SQL： 创建表的同时， create table 表名
      (
                       字段名 类型 identity(n,m)
                     )  
      创建表后:     1.删除原列 2.添加此列为标识符
                  
                   alter table 表名
     drop column 字段名
               
                   alter table 表名
                   add 字段名 类型 identity(n,m)

在SQL 中的注释用"--"

公式:   (企业管理器）右击表--设计表--公式
       

         --创建带有公式和默认值的表
          create table 销售表2
          (
  单价 int not null,成本 int not null,销售量 int not null,销售金额 as 销售量*单价,赢利 as 销售量*单价-销售量*成本,销售时间 datetime default getdate()
)
select * from 销售表2

默认值: 用户输入的仍然有效
        (企业管理器） 右击表--设计表--默认值
      

        在建表后:添加字段时:
        alter table table_name
        add
        constraint df_name default 值 for 字段
     
        在创建表时:

        create table table_name
        (
          字段名 类型  default 值
         )

函数:
数学函数:

abs()           求一个数的绝对值
ceiling(num) 返回大于或等于给的数的最小整数
floor(num) 返回小于或等于给的数的最大整数  
power(n,m) 求一个数的指数 
round(n,m) 求一个数的精确度,m为小数部分且按四舍五入的方法
square(num) 求一个数的平方
sqrt(num) 求一个数的平方根

字符串函数:
char(num)          把一个数转换为字符
ascii('')    把一个字符转换为数字
charindex('','')   第一个字符串在不在第二字符串中0为不在,非0为不在
left('',num)       从左边第一个字符开始截取num个字符
right('',num)      从右边第一个字符开始截取num个字符

在网页上应用查询:select * from itnews
                where charindex('放假',title)<>0

 

 

 

len(string) e.g select * from news where len(title)=4

ltrim() rtrim() 分别删除左右边的空格

substring(字符串,起始点,长度)

upper ('英文字符') 转换为大写
lower ('英文字符') 转换为小写

日期函数:

getdate()   当前系统时间
year(date)  month(date)  day(date)
时间因子:也就是时间单位
dateadd(时间因子,数字,日期)
datediff(时间因子,小日期,大日期)

datepart(时间因子,日期)

转换函数:
cast(被转换类型 as 要转换到的类型)

convert(要转换的类型,被转换的类型)

系统函数:
current_user as 返回当前用户名
system_user  as 返回当前登录名
host_name()  as 返回当前服务器名
dataleng()   as 字节长度

3.7  数据完整性

实体完整性:      避免数据冗余
值域完整性:      值的范围
参照完整性:      避免数据不合理
用户自定义完整性:

 

实现数据完整性
约束/规则/默认

约束(五大约束)
1、主键 (primary key)
2、外键(foreign key)
3、唯一 (unique)
4、检查 (check)
5、默认 (default)

pk:设计表/选择字段/点击钥匙图标

用SQL语句创建:
建表时:
 
create table table_name
(
 字段 类型 是否为空 primary key,
)

建表后(表中必须有这个字段的存在,不能同时添加字段):

alter table table_name
add
constraint pk_name primary key(字段)

注:一个表只能有一个主键

PK的作用:
1、值的唯一性
2、该字段必须为 not null
3、添加值时不能为空
4、系统会自动为该字段添加索引(index)
5、一个表中只能有一个主键(多列组合主键)

用SQL语句创建多列组合主键
alter table table_name
add
constraint 名称 primary key (字段1,字段2,...)
 

unique 数据唯一:
可在表中创建多个
可以为空值(只能有一个空值)
用SQL语句:
 
constraint 名称 unique (字段)

外键(foreign key)

创建外键的条件:
 被参照列必须设置为主键或unique
 两个字段必须在数据类型和长度保持一致
在要设外键（foreign key)的表中设定关系
用SQL语句创建：
alter table 外键表
add
constraint 名称 foreign key(字段） references 主键表（字段）
e.g:
alter table 销售表
add
constraint fk_sp foreign key(产品编号) references 产品表(产品编号)

检查约束(check)
在企业管理器中：
右击表/设计表/右击/check约束

用SQL语句:
alter table table_name
add
constraint 名称 check(条件)

e.g:
alter table wxb
add
constraint ck_ce check(id between 0 and 10)

 

默认值: 用户输入的仍然有效
        (企业管理器） 右击表--设计表--默认值
      

        在建表后:添加字段时:
        alter table table_name
        add
        constraint df_name default 值 for 字段
     
        在创建表时:

        create table table_name
        (
          字段名 类型  default 值
         )

规则(rule):值域完整性
在功能上和check约束一样
表删除，约束随之走  （约束）
表删除，规则仍然在  （规则）

@变量名 关系式（@变量名 between 0 and 100）

用SQL创建规则：

create rule rule_name
as
规则表达式

 
规则绑定：
sp_bindrule 规则名，'表名.字段'

解除绑定:
sp_unbindrule '表名.字段'
注：一个字段只能有一个规则（后来者居上）

删除规则：
drop rule 规则名

注：如果规则正在使用无法删除的

查看规则源代码：  sp_helptext 规则名

查看规则创建时间：sp_help 规则名

默认：

与规则一样

第十三节课:  2009-6-5

like用法:
e.g:
alter table table_name
add
constraint ck_stu check(学号 like 'STU[0-9][0-9][0-9][0-9][0-9][0-9]')

% 包含零个或更多字符的任意字符串。
WHERE title LIKE '%computer%' 将查找处于书名任意位置的包含单词 computer 的所有书名。

_（下划线） 任何单个字符。
WHERE au_fname LIKE '_ean' 将查找以 ean 结尾的所有 4 个字母的名字（Dean、Sean 等）。

[ ] 指定范围 ([a-f]) 或集合 ([abcdef]) 中的任何单个字符。
WHERE au_lname LIKE '[C-P]arsen' 将查找以arsen 结尾且以介于 C 与 P 之间的任何单个字符开始的作者姓氏，例如，Carsen、Larsen、Karsen 等。

[^] 不属于指定范围 ([a-f]) 或集合 ([abcdef]) 的任何单个字符。
WHERE au_lname LIKE 'de[^l]%' 将查找以 de 开始且其后的字母不为 l 的所有作者的姓氏。

distinct 除去重复的记录

IS NULL 用来查找 NULL 值。

top 子句
select top number [percent] column_name(s) from table_name

 

% 表示任意个任意字符串
_ 表示一个任意字符串

exists 检查某个内容是否存在

在排序查询中,空值最小
注:排序只是对查询结果进行输出的!并没有改变记录在表中的真实位置!!!!

第十五节课：  2009-6-10

select count(*) from 学生表

注：有空值的情况下不统计在内。
    非分组字段绝不能出现在查询中。

having 分组查询中的条件

group by 分组

连接查询：

笛卡尔积的出现：通过两张表的公共字段连接   公共字段连接匹配

e.g： select * from 表1，表2
      where 表1.字段=表2.字段

连接查询也就是多表连接查询:有两张表,三表,四表查询.

 

 

 

别名： select * from 表1 别名1，表2 别名2

注：有了别名就要用。

嵌套查询：一个条件需要另一个查询的结果给出。另一个查询用“（）”
          子查询含多个查询结果用“in”
          使用“in”都可以

合并查询：将查询多个结果进行合并，得到一个结果，自动除掉重复记录。

第一个结果    类型统一  union

保存查询结果： 
  
into 表    保存到永久的表中
into #表   保存到临时表（当服务停止以后此表消失）
此表结果只能保存变量

e.g:
declare @age int
select @age=年龄 from 表  
select @age

Go 表示上一程序结果
e.g:
declare @age int
set @age=(
 select .....
)

空复本：
让查询结果为空保存到另一张表中，发布到WEB上
工具-->向导-->管理-->web助手向导