SQL关系型数据库&结构化查询语言

数据库：用来存储数据的仓库，英文表示为database，简称DB

数据：数字、文字、图片、视频、音频、文本等等，在计算机科中能输入到计算机并能被计算处理的符号的总称

数据库的管理系统：简称DBMS

【介于用户和操作系统之间一层管理软件.主要负责用户与数据库之间沟通】

现今流行的的数据库管理系统

1、小型

Access【Office软件产品之一】、VF…

2、中型

SQLServer【Microsoft微软】、MySQL【瑞典一个小公司出品】、DB2【IBM公司】

3、大型

Oracle【甲骨文公司】…

数据库管理包括

1、数据的定义部分

【包括创建数据库、修改数据库、删除数据库、创建数据表、修改表结构、删除数据表、添加/删除约束条件】

2、数据的操纵部分【对数据的处理(添加数据、修改数据、删除数据、查询数据等)】

3、数据库的运行和管理

4、数据库的建立和维护

数据库系统：用户【后台开发人员】----数据库管理系统----数据库管理员----数据库

注意：用户通过数据库管理系统操作数据库，数据库管理员可以通过数据库管理系统操作数据库，也可以直接操作数据库

通常目前使用的数据库管理系统都是关系型数据库

关系型数据库中存放一个或者多个关系，每一个关系就是一张二维表

不同的关系表存在一些关系

1、一对一关系

2、一对多关系

3、多对多关系

关系表：以二维表形式存在，不同的数据表之间存在关系

元组【记录】：存放在二维表中的一行记录，任何一张表中没有完全相同的记录

属性【字段】：存放在二维表中的一列信息，同一字段的属性名称不能相同

域【字段值】：存放在二维表中的属性值，同一列的数据值类型必须一致

主码【主键】：用来唯一标识某一行记录的属性，主键尽可能不参与任何业务逻辑，跟实际功能无关，任何数据表中都要有主键

############################################################################### sql server 2005版本

1)sql server 2005企业版：只能安装windows server操作系统.如:windows server 2003

2)sql server 2005个人版: 可以安装win xp,windows 2000/2003,windows vista

3)sql server 2005简装版：随Microsoft Visual Studio 2005安装的.

4)sql server 2005开发版:

5)sql server 2005工作组版

############################################################################### SQL语言：

标准的结构化查询语言【所有数据库管理系统通用的标准语言】

使用数据库管理系统【SQLServer为例】

1、安装SQLServer数据库【SQLServer2005开发板的安装】

对于32位操作系统安装X86，对于64位系统安装X64，由于SQLServer2005自带客户端管理界面，先安装Tools文件夹中的客户端管理界面，再安装Server文件夹中的服务器2、开启服务

注意：安装SQLServer数据库会生成相关服务，默认情况下服务是自动并且开启，为了保证系统的顺畅运行，习惯将服务都设置成手动，不需要的服务可以暂时关闭

服务启动类型

1)自动:windows运行时服务会自动开启

2)手动:当需要时，用户手动去点击服务才会开启

3)禁用:服务不允许使用.

注:最好将sql server服务设置为手动方式.

其中：

SQL Server(MSSQLServer)服务是运行数据库必须要开启的，其中小括号中的内容可能在家用电脑中名字有所区别(命名实例SQLServer)

我的电脑右键----管理---服务和应用程序---服务---找到SQL Server(MSSQLServer)服务双击----点击启动-----确定

3、打开SQLServer进入登录界面

开始---程序---SQLServer---SQLServer Management

注意：

安装数据库过程中如果选择登录模式为【混合模式SQLServer身份登录】通过用户名sa和密码可以直接登录

如果数据库过程中如果选择登录模式为【Windows模式登录】不需要用户名密码直接登录进入数据库后再自行设置登录名和密码以及所属权限

设置登录名和密码以及所属权限：

以Windows模式登录---安全性---登录名---右键新建登录名---设置名字和密码---服务器角色处选择sysadmin---确定---断开连接

重新进入登录界面---选择SQLServer身份登录---输入登录名和密码---确定

自带的系统数据库:

1)master: 存放数据库的启动和配置信息

2)model: 是一个模版数据库，新建的数据库都以它为模版.

3)tempdb: 存放临时数据的.

4)msdb: 存放任务的周期性执行

数据库:主要由数据文件，日志文件组成.

数据文件:分为主数据文件和次数据文件

主数据文件:它的扩展名为.mdf,存放数据库的配置信息和一般数据

次数据文件:它的扩展名为.ndf,用来辅助主文件不足的.

日志文件:它的扩展名为.ldf.用来存放日志信息，利用日志文件可以进行数据恢复。

注:主数据文件有且仅有一个、次文件可以没有、也可以有多个，日志文件至少有一个，也可以有多个。

注:登录服务名称中可以输入计算机名或者计算机ip或者.表示登录本地sql server 数据库常用的数据类型

int 整型--4字节=4*8=32位范围:-2^31~2^31-1

char 字符型--静态长度

varchar 字符型--动态长度

numeric(m,n) m表示整数和小数位数,n表示小数的位数.如:numeric(4,1)

datetime 时间日期型

text 存放大量文本

image 存放二进制文件,如:声音，图像等.

约束:保证数据的完整型和一致性

主键(primary key):

唯一标识表中一条记录.一个表只有一个主键，不能为空，也不能重复.

唯一(unique):

一个表可以有多个唯一,可以为空，但只能空一次,不能重复.

检查(check):

检查字段的取值范围

如: 年龄>=18 and 年龄<=60,年龄是表中字段

性别='男' or 性别='女',性别是表中字段,字符型用单引号引上。

空(null)：

表示字段的内容可以不填写.

非空(not null)：

表示字段的内容必须要填写

默认(default):

事先给字段设置好的一个值，当用户不填写时，会自动填入设好的值。如果填写就会复盖设好的值.

标识列(identity):

一个表只有一个标识列,它可以产生连续且唯一的编号，并且递增产生。某字段设置标识列之后，用户给该字段插入值或修改值.字段类型必须是整型

外键(foreign key):

需要2个表,用一个表字段参考于另一个表另一个字段.建外键的表为子表(外键表),被参考的表为主表(主键表),要求主表被参考的字段为主键或唯一约束,子表的字段类型要与

主表的字段类型要相同。

注意：SQLServer注释分2种：--注释内容单行/*注释内容*/多行

数据的定义语言：

对创建数据库、使用数据库、数据表的创建、修改数据表的结构、添加约束条件

通过命令对数据库操作：

打开并登录数据库管理系统----工具栏新建查询---为SQL文件创建目录---起名---确定1、创建数据库

create database 数据库的名称;

例如：create database myDB;

数据文件(主文件.mdf，次文件.ndf)和日志文件(.ldf)

create database 数据库名

on

(

name=逻辑名1,

filename='路径+文件名.mdf',

size=初始值MB,

maxsize=最大值MB,

filegrowth=x%|xMB

),

(

name=逻辑名2,

filename='路径+文件名.ndf',

size=初始值MB,

maxsize=最大值MB,

filegrowth=x%|XMB

),

...

log on

(

name=逻辑名3,

filename='路径+文件名.ldf',

size=初始值MB,

maxsize=最大值MBM,

filegrowth=x%|XMB

)

注：

name,filename不能省略,可以省略size=1MB,maxsize=不受限制,filegrowth=10% name逻辑名不能重复.name和filename 一一对应的

例如：

create database MyDB

on

(

name=MyDB_MDF_data,

filename='F:\MyDB_MDF_data.mdf',

size=5MB,

maxsize=100MB,

filegrowth=20%

),

(

name=MyDB_NDF _data,

filename='F:\MyDB_NDF_data.ndf',

size=5MB,

maxsize=100MB,

filegrowth=20%

)

log on

(

name=MyDB_log,

filename='F:\MyDB_log.ldf',

size=5MB,

maxsize=100MB,

filegrowth=20%

)

如图【F盘目录下生成如下文件】

############################################################################### 2、使用数据库

use 数据库的名称;

例如：use myDB;

3、删除数据库

drop database 数据库名;

例如：drop database myDB;

数据库在使用状态下，无法删除，先使用其它数据库，然后再删除

4、修改数据库

给现有的数据库的新增一个数据文件

alter database 数据库名

add file

(

name=逻辑名,

filename=’路径+文件名.ndf',

size=初始值MB,

maxsize=最大值MB,

filegrowth=X%|XMB

)

例如：

alter database MyDB

add file

(

name=MyDB_NDF_data,

filename='F:\MyDB_NDF_data.ndf',

size=10MB,

maxsize=200MB,

filegrowth=10MB

)

增加一个日志文件

alter database 数据库名

add log file

(

name=逻辑名,

filename='路径+文件名.ldf',

size =初值MB,

maxsize=最大值MB,

filegrowth=X%|XMB

)

例如：

alter database MyDB

add log file

(

name=MyDB_log,

filename='F:\MyDB_log.ldf'

)

修改数据库的参数

alter database 数据库名

modify file

(

name=逻辑名,

size=初值MB，

maxsize= 最大值MB,

filegrowth=X%|XMB

)

注：filename不能修改,一次最好修改一个参数例如：

alter database MyDB

modify file

(

name=MyDB_log,

size=10MB

)

删除数据中的文件

alter database 数据库名remove file 逻辑名

例如：alter database MyDB remove MyDB_log;

修改数据库名

exec sp_renamedb '旧数据库名','新数据库名'

例如：exec sp_renamedb'MyDB','MyDataBase';

查看数据库信息

exec sp_helpdb 数据库名

例如：exec sp_helpdb MyDataBase;

############################################################################### 通过数据库管理系统创建数据表

在所选数据库上右键---新建表---为表设置列名以及类型【常见的类型：varchar字符串、char 字符、int整数、float小数、datetime日期等】

注意：

创建表必须为表中设置一个唯一标识的列【主键】：

在要设置主键的列上右键---选择设置主键【主键是不能为空】---确定

设置主键自增长：

要想通过数据库自动为我们维护主键唯一：

前提主键必须为整数int类型，选择主键列在下面的窗口中找到标识规范---选择是否标识【是】，出现标识增量【每次添加记录主键会自增长的数】和标识种子【主键起始值】

通过命令行创建数据表

create table 表名(

列名1 列的类型1 primary key identity(1,2),

列名2 列的类型2,

列名3 列的类型3,

列名4 列的类型4

);

例如：

create table t_user(

userOID int identity(1,1)primary key,

userName varchar(22),

userAge int,

userSex char(2),

userBirth datetime,

userSary float

);

其中

primary key表示主键

identity(1,2)表示自增长【参数1：表示标识种子，参数2：表示标识增量】注意：主键如果想要设置自增长，主键列必须为整数类型

表结构如下

用户表

修改表的结构

1、修改表结构添加列【为表中添加列，实际上修改原有表的结构】

alter table 表名add 列名列的类型;

例如：alter table t_user add userContext char(2);

2、修改表结构列的类型【修改的是表中列的类型】

alter table 表名alter column 要修改的列名修改后列的类型;

例如：alter table t_user alter column userContext varchar(50);

3、修改表结构删除列

alter table 表名drop column 要删除的列名;

例如：alter table t_user drop column userContext;

如果想要修改表【列】名称

exec sp_rename '表名.表中要修改的列名','新修改的列名','column';

exec sp_rename '旧表名','新表名';

############################################################################### 删除数据表

drop table 表名;

例如：drop table t_user;

数据表的复制

1、复制整张表

select * into 新表名from 旧表名;

例如：select*into tt_user from t_user;

2、只复制表结构不要数据内容

select * into 新表名from 旧表名where 1 = 0;

例如：select*into ttt_user from t_user where 1 = 0;

注意：复制表只能复制表的结构以及数据，但是无法对表的约束进行复制，需要为新表重新添加约束条件

############################################################################### 数据表的约束条件

基本形式

alter table 表名add constraint 约束名约束条件;

注意：同一数据库下的表约束名不能相同

1、主键约束

alter table 表名add constraint 约束名primary key(要设置主键的字段名);

例如：alter table t_user add constraint pk primary key(userOID);

2、默认约束

alter table 表名add constraint 约束名default 默认值for 要设置默认的字段名;

例如：alter table t_user add constraint df default'女'for userSex;

注意：默认值的类型要和设置该字段的类型一致

3、唯一约束：属于业务逻辑功能，类似主键

alter table 表名add constraint 约束名unique(要设置唯一的字段名);

例如：alter table t_user add constraint un unique(userName);

4、检查约束

alter table 表名add constraint 约束名check(要设置检查的表达式);

例如：

alter table t_user add constraint ck check(userAge<=22 or userAge>=34); alter table t_user add constraint c_k check(userAge>=0 and userAge<=74);

5、外键约束

通常存在不同关系的两张表，多方存放一方的唯一标识

alter table 外键所在表名add constraint 约束名foreign key(要设置约束列名)references 另一张表名(另一张表主键名);

例如：

alter table t_user add constraint fg foreign key(userGroupID)references t_userGroup (userGroupOID);

注：设置外键约束的表数据删除后才能删除另一张表对应主键删除约束

alter table 表名drop constraint 约束名;

例如：alter table t_user drop constraint pk;

alter table 表名nocheck constraint 约束名;

例如：alter table t_user nocheck constraint ck;

激活约束

alter table 表名check constraint 约束名;

例如：alter table t_user check constraint ck;

注：激活和关闭约束适合于外键和检查约束

############################################################################### 数据的操纵

1、添加数据

插入表中全部记录

【表中有多少列，就需要插入多少值，并且插入的记录要和定义表中列的类型一致】insert into 表名values(列的值1,列的值2,列的值3,列的值4,......);

例如：

insert into t_user values('张三',22,'女','2012-12-12',2222.222);

插入表中部分记录

【插入的记录要和定义表中列的类型一致】

insert into 表名(指定要插入的列名1，列名2，.....) values (列的值1,列的值2,......); 例如：

insert into t_user (userName,userAge,userSex)values('张三',22,'女'); 2、修改

修改表中所属列的所有记录

update 表名set 要修改的列名=要修改列的值;

例如：

update t_user set userName='李四';

根据条件修改表中所属列的记录

update 表名set 要修改的列名=要修改列的值where 条件;

update 表名set 要修改的列名=要修改列的值where 条件1 and 条件2,......;

update 表名set 要修改的列名=要修改列的值where 条件1 or 条件2,......;

例如：

update t_user set userSary=7777.74 where userOID=1;

update t_user set userSary=7777.74 where userAge>22 and userAge<44; update t_user set userSary=7777.74 where userAge<31 or userAge>47;

删除表中所有记录------初始化数据表

delete from 表名;truncate table 【删除速度快，不写入日志中，删除后不可恢复】

例如：

delete from t_user;

truncate table t_user;

根据条件删除表中所属的记录

delete from 员工表where 条件;

delete from 员工表where 条件1 and 条件2,......;

delete from 员工表where 条件1 or 条件2,......;

例如：

delete from t_user where userOID=1;

delete from t_user where userAge>22 and userAge<44;

delete from t_user where userAge<31 or userAge>47;

############################################################################### 4、查询【简单的基本查询】

查询表中所有记录

select * from 表名;

例如：

select*from t_user;

查询表中指定列的所有记录

select 列名1,列名2,..... from 表名;

例如：

select userName,userSex from t_user;

根据条件查询表中指定列的所有记录

select 列名1,列名2,..... from 表名where 条件;

select 列名1,列名2,..... from 表名where 条件1 and 条件2,......;

select 列名1,列名2,..... from 表名where 条件1 or 条件2,......;

例如：

select userName,userSex from t_user where userOID = 1;

select userName,userSex from t_user where userAge>22 and userAge<44; select userName,userSex from t_user where userAge<31 or userAge>47;

############################################################################### 关于查询

格式

select 字段1,...字段n from 表名

where 条件

group by 字段having 分组条件

order by 字段asc | desc

其中：

*表示所有字段

例如：select*from t_user;

只显示某几个字段【字段列表,字段之间用逗号分开】

例如：select userName,userSex from t_user;

运算符：注:sql server 2005:"+"不仅表示字符串连接，也表示加运算.

>、<、>=、<=、!=不等于、<>不等于、=等于

+、-、*、/

and 表示与相当于语言中&&

or 表示或相当于语言中||

not 表示非--相当于语言中!

例如：

select userName,userSex from t_user where userAge>22 and userAge<44; select userName,userSex from t_user where userAge<31 or userAge>47; select userName,userSex from t_user where not userAge=11;

select userName,userSex,userSary+subSidySary from t_user;

字段的别名

空格分开

例如：select *,userSary+subSidySary sumSary from t_user;

as 分开

例如：select *,userSary+subSidySary as sumSary from t_user;

'字段别名'=字段名

例如：select*,sumSary=userSary+subSidySary from t_user;

between and【表示范围】相当于字段>=值1 and 字段<=值2

例如：

select*from t_user where userAge between 11 and 22;

相当于

select*from t_user where userAge>=11 and userAge<=22;

in 表示集合,是否在集合中

字段in(值1,...值n)

例如：

select*from t_user where userAge=18 or userAge=11 or userAge=22; 相当于

select*from t_user where userAge in(18,11,22);

not in 与in相反.

例如：

select*from t_user where userAge not in(11,18,13);

模糊查询:

查询姓李的人的所有信息

select * from 表名where userName like '李%';

例如：select*from t_user where userName like'李%';

查询姓名最后一个字为'明'的人的所有信息

select * from 表名where userName like '%明';

例如：select*from t_user where userName like'%明';

查询姓名中间文字为'明'字的人的所有信息

select * from 表名where userName like '_明_';

例如：select*from t_user where userName like'_明_';

查询名字含有李字的人的所有信息

例如：select*from t_user where userName like'%李%';

排除姓李的人的所有信息

例如：select*from t_user where userName like'[^李]%%'

指定姓李的人的所有信息

例如：select*from t_user where userName like'[李]%%'

其中Like修饰符使用了简单的正则表达式，引用几种种通配符

% 匹配任何数目的字符，甚至零个字符

_ 精确匹配一个字符

[]指定单个字符的范围

[^]排除指定字符的范围

not like 与like 相反

查询名字第二个字不是李字的人的所有信息

例如：select*from t_user where userName not like'_李%'

查询名字中含有”_”的记录

例如：

select*from t_user where userName like'%_%'查询结果有误

select*from t_user where userName like'%[_]%'

[^]通配符

select*from t_user where userName like'_[李]_';

select*from t_user where userName like'_[^李]_';

逃逸字:

查询的内容中当包含通配时,将通配符做为一个普通字符处理。逃逸字后面的通配符是一个普通字符.

格式

where 字段like '%#%%' escape '#'

where 字段like '%#_%' escape '#'

例如：查询名字带”_”的人所有信息

select*from t_user where userName like'%[_]%'

等价

select*from t_user where userName like'%#_%'escape'#'

查询并且对指定字段排序order by 字段【asc | desc】

asc(默认方式): 升序【从小到大】

desc: 降序【从大到小】

多列排序:

首先第一列排序,当第一列值相同时，再按第二列排序.以此类推,多个字段之间用逗号分开例如：查询表中名字叫XXX的人所有信息，并且按照年龄的升【降】序排列

select*from t_user where userName='XXX'order by userAge desc; select*from t_user where userName='XXX'order by userAge asc;去掉重复记录

distinct 它放在select直接后，作用于所有列

例如：select distinct userAge from t_user

查询表的前N条记录

top n

例如：

select top 5 *from t_user

查询表的前%n记录

top n percent

例如：

select top 10 percent*from t_user

查询空和非空

is null 判断空

is not null 非空

where 字段=null- 错的

例如：

select*from t_user where subSidySary=null 查询结果错误select*from t_user where subSidySary is null

select*from t_user where subSidySary is not null

聚合函数【分组函数】

max(字段) 求最大值

min(字段) 求最小值

avg(字段) 求平均值

sum(字段) 求合

count(*)或count(字段) 求记录数

分组:字段的内容相同的为一组【group by 字段1,..字段n having 分组条件】where 与having 的区别

a)where 查询条件

b)having 放在group by中做分组的条件

c)分组函数不可以放在where 中,只能放在having中做条件

注:

字段的别名，不能用在where 条件中.也不能用在having 条件中,

只能用在order by 中.

注:

***分组时，除了分组函数之外，其它列要放在group by 中

例如：

查询表中所有用户的总工资

select userOID,sum(userSary+subSidySary)as sumSary

from t_user group by userOID

查询表中用户的总工资只显示工资大于2500的

select userOID,sum(userSary+subSidySary)as sumSary

from t_user group by userOID having sum(userSary+subSidySary)>2500查询表中用户的总工资只显示工资大于2500的，并按照降序排序

select userOID,sum(userSary+subSidySary)as sumSary

from t_user

group by userOID

having sum(userSary+subSidySary)>2500

order by sum(userSary+subSidySary)desc

############################################################################### 常见函数

字符串函数

len(表达式)

取表达式的长度

查询用户表中姓名长度

select len(userName)from t_user;

查询用户表中姓名长度为4的人的姓名

select userName,len(userName)

as namelength from t_user where len(userName)=4;