SQL2005数据库笔记

                                     SQL2005数据库

数据库SQL Server2005设计与应用

1.数据库:数据存放的仓库 DataBase DB

 层次模型

 网状模型

 关系模型:二维表格 sql

2.应用：

 数据库设计原理***

 得到一个数据库原形(理论)(E-R图)

3.数据库软件：数据库管理系统

 DataBase Management System （DBMS）

 FoxBase(dos)

 FoxPro

 MS Visual FoxPro5.0

 Access

 SQL Server***

 Oracle

 DB2

 mysql

 ...

4.学习SQL Server2005(程序类)

 1.安装 环境介绍

 2.数据库管理

 3.表的创建与管理

 4.其他对象(视图 索引 过程 函数)

 5.标准sql语法

 Structured Query Language 结构化查询语言

 DDL:数据定义语言

  创建 Create 

  修改 Alter

  删除 Drop  

 DML:数据操作语言

  读 Select*********

  写 Insert

  改 Update

  删 Delete

 DCL:数据控制语言

  流程控制语句:

  条件if

  选择case

  循环while

  用户权限角色管理:

   grant 授权

   deny 禁用

   revoke 收回

 6.数据库维护

  备份恢复

  导入 导出

面向过程开发程序 c语言

面向对象开发程序

数据表table

4行:记录 record   3个记录

6列:字段 field    6个字段

表结构：0条记录的表就是表结构

设计创建表的过程就是设计创建表结构的过程

数据类型：限制字段的取值范围

主键:主要字段(取值不能重复)

00000000000000000000000000

教学数据库模型

 学生表:学生编号pk/姓名/.....

 教师表:教师编号pk/姓名/.....

 课程表:课程编号pk/课程名称/课程描述/教师编号fk

 学生课程关系表:学生编号fk/课程编号fk/考试成绩

Developer Edition 开发版

Standard Edition 标准版

Enterprise Edition 企业版

Evaluation Edition 测试版

SQL Server 2005 Express Edition  简化版

Visual Studio.net2005

 VB.NET

 C++

 ASP.NET

 C#

 ADO.NET

 SQL Server 2005 Express Edition  简化版

 .netFramework公共组建类库

print 1+2

print '张三'

f5

print getdate()

--代码该变当前数据库:

--use 数据库名称

--

--sdfgsdfgsdf

--gs

--df

--sdfhsdfh

--单行注释

/*

块注释

gd

gsdf

sdfh

dsfhsdf

*/

use master

--文本结果

select getdate()

--网格结果

select getdate(),1+2,'张三'

print getdate(),1+2,'张三'

use ABC

--查询ｉｎｆｏ表中的所有信息

select * from info

select * from test

--sql server的命令窗口： sqlcmd

sqlcmd -U用户名 -P口令 -S服务器

--代码创建数据库语法

--1.简单格式

create database 数据库名称

create database db001

--2.完整格式

on primary 

(

 --定义主数据文件信息

)

,

 --定义其他数据文件

log on

 --定义日志文件信息

----

 name='定义主数据文件的逻辑名称(数据库名称_data)',

 filename='定义主数据文件的物理路径和文件名称和扩展名.mdf',

 size=定义文件大小 5mb,

 filegrowth=2 | 15%,

 maxsize=100mb | unlimited

 name='定义其他数据文件的逻辑名称(数据库名称_data2)',

 filename='定义其他数据文件的物理路径和文件名称和扩展名.ndf',

 name='定义日志文件的逻辑名称(数据库名称_log)',

 filename='定义日志文件的物理路径和文件名称和扩展名.ldf',

create database mydb

 name=mydb_data,

 filename='K:\DatabaseFile\mydb\mydb_data.mdf',

 size=4,

 filegrowth=1,

 maxsize=unlimited

 name=mydb_data2,

 filename='K:\DatabaseFile\mydb\mydb_data2.ndf',

 size=5,

 filegrowth=10%,

 maxsize=100mb

 name='mydb_log',

 filename='K:\DatabaseFile\mydb\mydb_log.ldf', 

 size=2,

 filegrowth=2,

create database [test_DB]

--修改数据库

--1)修改数据库名称

alter database 数据库名称 modify name=新名称

alter database mydb modify name=mydatabase

--2)删除其它数据文件、日志文件

alter database 数据库名称 remove file 逻辑名称

alter database mydatabase remove file mydb_data2

alter database mydatabase remove file mydb_data

alter database mydatabase remove file mydb_log

--不能从数据库中删除主数据文件或主日志文件。

--3)添加其它数据文件、日志文件

alter database 数据库名称 add [log] file

(详细定义)

alter database mydatabase add file

 name='mydb_data2', 

 size=1,

alter database mydatabase add log file

 name='mydb_log2',

 filename='K:\DatabaseFile\mydb\mydb_log2.ldf',

 filegrowth=4%,

--4)修改数据文件、日志文件的基本信息

--(不能修改各个文件的物理路径)

alter database 数据库名称  modify file 

(name=要修改的文件的逻辑名称,修改的信息)

alter database mydatabase modify file

(name=mydb_log2,size=5)

--5)修改数据文件、日志文件的逻辑名称

(name=要修改的文件的逻辑名称,newname=修改后的名称)

(name=mydb_log2,newname=mydb_log22)

--删除数据库语法

drop database 数据库1,数据库2,...

drop database abc,abcd,db,db001,dddd,ganbu,hhhh

本节总结；《细心加重视》

create database  代码创建数据库语法

alter database     修改数据库语法

drop database         -删除数据库语法

create table   代码创建表语法

alter table    修改表

drop table     删除表

create view

alter view

drop view

...

--变量类型

全局变量:系统定义的变量 @@name   关键字

局部变量:用户定义的变量 @name

declare @str char

declare @str char(1)'1'表示set 后字符窜的最多值，

而不包括print的值

set @str='abc'

print @str

declare @str char(10)'10表示set 后字符窜的最多值，

print @str+'efg'

declare @str varchar(10)

declare @str varchar(1)

/****** 对象:  Database [mydb]    脚本日期: 10/28/2009 15:19:56 ******/

create database [mydb] 

on  primary 

( 

name = 'mydb_data', 

filename = 'k:\databasefile\mydb\mydb_data.mdf' , 

size = 4096kb , 

maxsize = unlimited, 

filegrowth = 1024kb 

),

name = 'mydb_data2', 

filename = 'k:\databasefile\mydb\mydb_data2.ndf' , 

size = 5120kb , 

maxsize = 102400kb , 

filegrowth = 10%

 log on 

name = 'mydb_log', 

filename = 'k:\databasefile\mydb\mydb_log.ldf' , 

size = 2048kb , 

maxsize = 2048gb , 

filegrowth = 2048kb 

----字符编码标准

--ansi :标准编码格式

--unicode :扩展编码格式

char  1~8000

varchar     1~8000

--sql变量:局部变量(用户定义的变量) @name

--char 固定长度

declare @str char(10) --定义声明一个局部变量

set @str='abcd'   --设置变量的值

declare @str char(8000) --定义声明一个局部变量

declare @str char(8001) --定义声明一个局部变量

set @str='abcd'   --设置变量的值                                                   

print 1+2     --加法运算 

print '1'+'2' --字符串连接

--varchar 可变长度

declare @str varchar(10) --定义声明一个局部变量

declare @str varchar(8000) --定义声明一个局部变量

declare @str varchar(8001) --定义声明一个局部变量

--日期时间类型 datetime

declare @dt datetime

set @dt='2005-8-18'

--print @dt

select @dt

set @dt='2005-8-18 15:34:59'

set @dt='2005 8 18'

set @dt='2005/8/18'

set @dt='2005.8.18'

set @dt='8.18.2005'

set @dt='8/18/2005'

select getdate()   设置当前时间语法

--整数类型： 

tinyint smallint int bigint 范围依次增大

declare @n tinyint 

set @n=23

print @n

declare @n tinyint    --0~255

set @n=23234

declare @n int   

int

declare @a int,@b int

set @a=5 

set @b=2

print @a/@b

set @a=5.2 

set @b=2.2

--Bit 二进制位类型

declare @x bit

set @x=0

print @x

set @x=1

set @x=18976

--bit 0就是0,非0就是1

set @x=-324

--两种状态  逻辑类型 true 1/   false 0

--boolean  表示逻辑类型

或

declare @ss real

set @ss=234.678676786876

print @ss

set @ss=4678676786876

说明：数字特别大时，执行采用科学计数法 4.67868e+012：012表示10的12次方。

declare @n numeric(5,2)

set @n=234

说明：括号5为字符窜总长，值最大38，表示字符窜长度，2表示小数部分保留位，

整数部分字符窜最长为两者作差。

set @n=234.987

set @n=34.987

set @n=34.9

set @n=934.9

set @n=4934.9

set @n=999.99

set @n=999.999

declare @n numeric(38,2)

set @n=-999.99

decimal与numeric的用法一样

总结数据类型：

char

varchar

datetime

bit

real/float

decimal/numeric

money

text

varchar(max)

--字符编码标准

--ansi

char  1-8000

varchar  1-8000

text  2gb

--unicode

nchar  1-4000

nvarchar 1-4000

ntext  2gb

declare @ss nchar(5)

set @ss='abc'

print @ss+'sdfa'

--教学数据库模型LearnDB

 学生表student:

学生编号pk/ stu_no  200501001 char(9)

姓名/  name     varchar(20)

性别/  sex      char(2)

生日/  birthday    datetime

城市/  city     varchar(20)

电话/  tele     varchar(20)

班别/  class_type    varchar(20)

报名费用/ fee      money

 课程表course:

课程编号pk/ c_no c_001   char(5)   

课程名称/ c_name     varchar(20)

课程描述/ c_desc     varchar(200)

授课教师/ teacher     varchar(20)

 学生课程关系表relation:

学生编号fk/ stu_no     char(9)

课程编号fk/ c_no     char(5)

考试成绩/ mark     numeric(5,2)

create database LearnDB

 name=LearnDB_data,

 filename='K:\DatabaseFile\LearnDB\LearnDB_data.mdf',

 filegrowth=15%,

 name=LearnDB_log,

 filename='K:\DatabaseFile\LearnDB\LearnDB_log.ldf',

--查询、添加、修改、删除语法的简单介绍

--Select 字段列表 from 表名 where 条件

--Insert into 表名(字段列表) values(对应字段结果)

--Update 表名 set 字段=结果 where 条件

--Delete from 表名 where 条件

use LearnDB

--查询

Select 字段列表 from 表名 where 条件

select name,sex,city from student

select stu_no,name,sex,birthday,city,tele,class_type,fee from student

select * from student------》打开学生表

select * from course------》打开课程表

select * from relation------》打开关系表

select * from table_1------》打开table_1表

select * from student where city='北京'

select * from student where sex='男'

select * from student where class_type='信息网络工程师'

select * from relation

select * from relation where mark<60

--查询select

select * from student

select name,sex,city,birthday from student where sex='男'

select name,sex,city,birthday from student where sex<>'男'

select name,sex,city,birthday from student where sex!='男'

--运算符号的分类

--算术运算符号

+

-

*

/

print 5/2 --整除

print 2/5 --整除

% 取余数      

print 5 % 2 

print 2 % 5   ——————————》短除式计算法

--关系运算符号

=

<> 等价写法   !=

>

<

>=等价写法  !<

<= 等价写法  !>

select * from student where fee!>3000

select * from student where fee<=3000

--逻辑运算符号

not 非 

and 与

or 或

print 1+2*3

print (1+2)*3

not and or 优先级依次降低

选中局部边表格的语法格式：

select * from student 

where sex='男' 

and city='北京' 

and class_type='信息网络工程师'

and fee<2000

列举使用逻辑符号：

select * from student  where not sex='男'

select * from student  where sex!='男'

select * from student  where sex<>'男'

select * from student where city='上海'

--北京和上海

select * from student where city='北京' or city='上海'

--查询北京、上海两个地区的全部女生信息

where (city='北京' or city='上海') and sex='女'

where city='北京' and sex='女' or city='上海' and sex='女'

where (city='北京' and sex='女') or (city='上海' and sex='女')

--统计除北京地区全部女生以外的其他地区的女生信息

where not (city='北京' and sex='女') and sex='女'

where (city!='北京' or sex!='女') and sex='女'

where (city!='北京' and sex='女')

--统计除北京地区以外的其他地区的女生信息

where city!='北京' and sex='女'

where city<>'北京' and sex='女'

where not city='北京' and sex='女'

where sex='女' and not city='北京'

--添加语法insert

insert into 表名(字段1,字段2,...) values(值1,值2,...)

insert into student(stu_no,name,sex,birthday,city,tele,class_type,fee) 

values('900','张力刚','男','1985-6-14','广州','675648787','java开发工程师',13500)

values('9014235','高茜','女','1985-1-14','广州','675648764887','java开发工程师',13500)

select * from course

INSERT into course(c_no,c_name,c_desc,teacher) 

values('c_14','语文','asdf','张老师')

insert into relation(stu_no,c_no,mark) values('901','c_014',100)

--更新update

update 表名 set 字段1=结果1,字段2=结果2,...where 条件

update course set c_desc='描述信息'

update course set c_desc=c_name+'描述信息'

update course set c_desc='《'+c_name+'》课程描述信息'

values('c_15','aaa','asdf','张老师')

--公式字段(计算列)

'《'+c_name+'》课程描述信息'

values('c_16','bbb','asddadf','张老师')

--不能修改列 "c_desc"，因为它是计算列，或者是 UNION 运算符的结果。

INSERT into course(c_no,c_name,teacher) 

values('c_16','bbb','张老师')

print getdate()---------->执行当前时间

print year(getdate())

print month(getdate())

print day(getdate())

print year('1998-6-20')----------》指定时间

print month('1998-6-20')

print day('1998-6-20')

--int----------》必须是整数格式

print year(getdate()) - year('1980-6-20')

--age

year(getdate()) -year(birthday)——-——————》年龄公式代码------》

select name,sex,birthday,city from student

select name,sex,year(getdate()) -year(birthday),city from student

select name,sex,age,city from student

year(getdate()) -year(birthday)

对比修改表代码变化：

1，

datepart(参数,日期)   --year/ month/ day

2，

(datepart(year,getdate())-datepart(year,[birthday]))

2是1的系统变化前身。举例如下；

f(x)=2x

g(x)=f(x)+56

g(x)=2x+56

更新数据库。注意更新后不能恢复原态。代码如下：

update

update student set fee=fee-500  where sex='男'

在学生表中举例：思路：

高级网络工程师     信息网络工程师

网站设计师    6+4

影视动画设计师  6+2

Java软件工程师    6+3   java开发工程师

.Net软件工程师    c++  .net开发工程师

update student set class_type='高级网络工程师' where class_type='信息网络工程师'

update student set class_type='网站设计师' where class_type='6+4'

update student set class_type='影视动画设计师' where class_type='6+2'

update student set class_type='Java软件工程师' where class_type='6+3' or class_type='java开发工程师'

update student set class_type='.Net软件工程师' where class_type='c++' or class_type='.net开发工程师'

update student

set birthday='1960-9-4',tele='3838839898',class_type='.Net软件工程师',fee=130000

where name='刘德华'

--删除delete

delete from 表名 where 条件  --删除指定表的满足条件的纪录

delete from 表名    --清空所有纪录

select * from table_1

delete from table_1

delete from course where c_name='abcdef' or c_name='bbb'

总结四种公式

--select insert update delete

select id,name,sex from student where id<8

identitycol 别名   

select identitycol,name,j

select * from relation where identitycol<45

--公式字段和标识列字段都不可以显式指定值

insert into course(c_no,c_name,teacher) values('c_099','aa','bb')

delete from test where id=5 or id=25 or id=35

删除表中部分标识列内容

查询标识列内容的初始值

初始值 

ident_seed('表名')

增量

ident_incr('表名')

select ident_seed('test'),ident_incr('test')

print @@identity（显示标识列所有的值）

@@identity : 代表最后一次标识列插入的值

print @@identity(只显示最后一次标识列插入的值）

insert into test values('dd','dd','dd')

--标识列值的重用    （例如删去了标识列为25，恢复语法如下：）

insert into test(name,sex,city,id)  values('a','aa','aa',25)

--当 IDENTITY_INSERT 设置为 OFF 时，由student表到test表进行标识列恢复时，

不能为表 'test' 中的标识列插入显式值。

 insert 

set inserty_insert  test on

set identity_insert test off

set identity_insert student on

--表 'LearnDB.dbo.test' 的 IDENTITY_INSERT 已经为 ON。无法为表 'student' 执行 SET 操作。

set identity_insert student off

ascii

0~9 :  48 49 ... 57

A~Z :  65 66 ... 90

a~z :  97 98 ... 122

200802040

900     

update student set name='0'+name

where id=1

PK_表名_字段名称1_字段名称2

update student set tele=null where id=2

update student set tele=null where id=3

where tele is null   (查询无电话的学生）

where tele is not null（查询有电话的学生）

--删除relation表中的stu_no在student中不存在的纪录

————》理解条件是：一，stu_no（学生号码）在relation表中

              二，stu_no（学生号码）不在student表中

delete from relation where stu_no not in(

select stu_no from student)

--删除relation表中的c_no在course中不存在的纪录

delete from relation where c_no not in(

select c_no from course)

insert into course select c_no,c_name,teacher from course where c_no='c_099'

delete from test where name='dd'

删除

insert into test(name,sex,city) values('aa','aa','aa')

主键约束与唯一性约束区别如下 ：

一个表上只能定义一个主键约束，但是可以定义多个唯一性约束；

定义了唯一性约束列上的数据可以为null值而且只能有一个null值，

但定义了主键约束上的列不能为null值。

老师,@@identity的作用是，

查找最后一次插入的值。是一个全局变量，

语法格式不需要表名。即使该列已经删除，

但值依然存在。后一句如何解?

insert into test(name,sex,city) values('xx','xx','xx')

print @@identity

delete from test where id=7

update student set stu_no='199802001' where id=3

update student set stu_no='110000000' where id=2

delete from student where id=3

delete from student where id=2

update student set stu_no='199801001' where id=7

--更新层叠 <====> 级联更新

--删除层叠<====>   级联删除

update student set sex='ab' where id=7

select * from student where sex!='男' and sex!='女'

update student set sex='男' where sex!='男' and sex!='女'

--约束性别取男或者女

sex='男' or sex='女'

--约束生日取值不能大于当前日期

birthday!>getdate()

--约束费用必须大于０

fee>0

--约束成绩必须在0~100

mark>=0 and mark<=100

insert into student(stu_no,sex,city,tele,class_type,fee) 

values('902','男','深圳','6775666','Java软件工程师',12000)

--null就是一种特殊的默认值

values('903','男','深圳','9885666','Java软件工程师',12000)

update student set name='abcd' where name='无名'

select * from news

insert into news(title,content) values('aaaaa','sss')

--SQL语法代码创建表

create table 表名

 字段名称1 定义1,

 字段名称2 定义2,

create table users

 id   int identity(1,2),

 username varchar(50),

 pwd   varchar(50),

 name  varchar(50),

 birthday datetime,

 age   as year(getdate())-year(birthday)

insert into users(username,pwd,name,birthday) 

values('admin','1234','管理员','1986-9-10')

values('abcde','1234','操作员','1988-1-1')

select * from users

create database userDB

go

use userDB

     本文转自shenleigang 51CTO博客，原文链接：http://blog.51cto.com/shenleigang/138978，如需转载请自行联系原作者