创建数据表
use 数据库名
--查找数据表：if exists(select * from sysobjects where name = '表名')
--删除数据表： drop table 表名 go
--创建数据表：create table 表名(
--设为主键：列名1 数据类型 constraint PK_列名1 primary key identity(1,1) not null,
--创建唯一约束：列名2 数据类型 constraint UQ_列名2 unique not null,
--创建主外键关系：列名3 数据类型 constraint KF_列3 foreign key references 主键表名(主键列) not null,
--设默认值,默认约束中的for省略：列名4 数据类型 constraint DF_列名4 default ('默认内容') not null,
--设置检查约束条件：列名5 数据类型 constraint CK_列名5 check (检查约束条件) not null,
--创建多个约束,在同一列创建多个约束时使由多个constraint来区分的
列名6 数据类型 constraint DF_列名6 default ('默认内容') constraint CK_列名6 check (检查约束条件) not null ) go
创建和删除约束
/*-创建约束-*/
--主键约束(Primary Key Constraint):要求主键列数据唯一,并且不允许为空.
alter table 表名 add constraint PK_列名 primary key (列名)
--唯一约束(Unique Constraint):要求该列唯一,允许为空,但是只能出现一格空值.
alter table 表名 add constraint UQ_列名 unique (列名)
--检查约束(Check Constraint):某列取值范围限制、格式限制等.
alter table 表名 add constraint CK_列名 check(约束条件)
--默认约束(Default Constraint):某列的默认值.
alter table 表名 add constraint DF_列名 default('默认内容') for 列名
--外键约束(Foreign Key Constraint):用于两表之间建立关系，需要制定应用主表的哪一列.
alter table 表名 add constraint FK_列名 foreign key(外键列名) references 主键表名(主键列名)
/*-删除约束-*/
alter table 表名 drop constraint 约束名
关于使用SQL Server 身份验证模式sa账户密码登录和取消只读数据库的设置
设置使用SQL Server 身份验证模式sa账户密码登陆 1、使用Windows 身份验证模式登录——选中服务器——单击右键——属性——在服务器属性对话框的选择页中选择安全——在服务器身份验证的单选按钮中选择 SQL Server 和 Windows 身份验证模式——单击确定 2、打开服务器——然后打开安全性——最后打开登录名——选中 sa ——单击右键——属性——在登录属性-sa对话框的选择页中选择常规——在登录名选项的密码和确认密码对话框中强制添加 sa 密码——然后再在登录属性-sa对话框的选择页中选择状态——在设置选项的是否允许连接到数据库引擎单选按钮选择授权——登录单选按钮选择启用——单击确定
设置取消只读数据库打开SQL server 配置工具里的SQL Server Configuration Manager——在SQL Server 配置管理器中选择SQL Server 2005 服务——在右边的对话框中选择——SQL Server (SQLEXPRESS)——单击右键——属性——在SQL Server (SQLEXPRESS)属性对话框中——登录身份为单选按钮选择内置账户——在内置账户的下拉框中选择本地系统——然后在服务状态里单击重新启动——单击确定
设置取消获得连接错误！com.microsoft.sqlserver.jdbc.SQLServerExeption: 到主机的 TPC/IP 连接失败。的方法 1、打开SQL server 配置工具里的SQL Server Configuration Manager——在SQL Server 配置管理器中选择SQL Server 2005 网络配置——SQLEXPRESS 的协议——单击或者单击右键打开——在右边的对话框中将所有协议的状态都启用——然后选择TCP/IP——单击右键——属性——在TCP/IP属性对话框中选择IP地址选项卡——在IP1的选项中的已启用选项选择是——再在IPALL选项中的TCP端口改为1433——单击确定 2、在SQL Server 配置管理器中选择SQL Native Client 配置——客户端协议——单击或者单击右键打开——在右边的对话框中选择TCP/IP——单击右键——属性——将在TCP/IP属性对话框中协议的General选项中的默认端口改为1433并且将已启用的选项改为是——单击确定
创建用户
/*-第一步登录用户-*/
--创建windows登录用户
--当创建windows登陆用户是必须在用户或组中存在用户名,创建方法是
右键打开"我的电脑"—管理—本地用户和组—用户(在此创建用户名)
exec sp_grantlogin '电脑名\windows登录用户名'
--创建SQL登录用户： exec sp_addlogin 'SQL登录用户名','密码'
/*-第二步数据库用户-*/
--数据库用户windows登录：exec sp_grantdbaccess '电脑名\windows登录用户名','据库用户名'
--数据库用户SQL登录： exec sp_grantdbaccess 'SQL登录用户名','据库用户名'
/*-第三步用户授权-*/
--授权SQL登录用户增、删、改、查的权限：grant select,insert,delete,update on stuInfo to 据库用户名
--授权windows登录用户创建表的权限： grant create table to 据库用户名
T-SQL 简单编程
/*-插入语句-*/
--插入一条： insert into 表名 (列名) values (列名值)
--插入多条
--通过inert select 语句将现有表中的数据添加到新表
insert into 新表名 (新表列名)
select 现有表列名
from 现有表名
--通过select into 语句将现有的表中的数据添加到新表
select 现有表名.现有表列名,
identity(数据类型,标识种子,标识增长量) as 新表名的ID列名
into 新表名
from 现有表名
--通过union关键字合并数据进行插入
insert into 表名 (列名)
select 列名值 union
select 列名值
/*-删除-*/
--delete删除数据
--delete...from...要删除就删除整条记录,不会只删除单个字段.
delete from 表名 where 条件
--truncate table删除数据
--truncate table用来删除表中所有行的命令,功能上和没有where条
件的delete类似;
truncate table用来删除表中有行,但是表的结构、列、约束、索引等不会被改动.
truncate table不能用于有外键约束引用的表,所以需要使用delete语句.
truncate table 表名
/*-更新-*/：update 表名 set 列名=更新值 where 条件
/*-查询-*/
--查询所有：select * from 表名
--条件查询： select 列名 from 表名 where 条件
--在查询中使用列名
select 列名 as 指定列名 from 表名 where 条件
select 指定列名 = 列名 from 表名 where 条件
--查询空行：select 列名 from 表名 where 列名 is null
--在查询中使用常量：select 指定列名 = 列名,常量 as 指定列名 from 表名 where 条件
--查询返回限制的行数：select top 行数列名 from 表名 where 条件
--查询排序
--当按照多列排序时先写哪一列就先排哪一列,order...by...放在最后
select 列名 from 表名 where 条件 order by 排序列名 asc --或默认为升序
select 列名 from 表名 where 条件 order by 排序列名 desc --降序
模糊查询
--通过like和通配符组合成条件来实现模糊查询： select * from 表名 where 列名 like '条件'
|
通配符 |
||
|
通配符 |
解释 |
示例 |
|
'_' |
一个字符 |
a like 'c_' |
|
% |
任意长度的字符串 |
b like 'co_%' |
|
[] |
括号内所指定范围内的一个字符 |
c like '9w0[1-2]' |
|
[^] |
不在括号内所指定范围内的任意一个字符 |
d like '9w0[^1-2]' |
|
--通过between来实现模糊查询 --可以使用not来对限制条件"取反" select 列名 from 表名 where 列名 between 条件1 and 条件2 --使用in在列举值内查询 --可以使用not来对限制条件"取反" select 列名 from 表名 where 列名 in (列举值) --使用聚合函数进行查询 --总和这种查询只返回一个数值,不能够直接与可能返回多行的列一起使用来进行查询, 一个查询可以使用多个聚合函数 select sum(列名) from 表名 where 条件 --平均数 select avg(列名) from 表名 where 条件 --最大值和最小值 select max(列名) from 表名 where 条件最大值 select min(列名) from 表名 where 条件最小值 --返回非空值的计数 select count(列名/*) from 表名 where 条件 --分组查询 --group...by...分组查询 select 列名 from 表名 group by 分组列名 --having 分组筛选 select 列名 from 表名 group by 分组列名 having 分组筛选条件 --where、group by、having在同一查询语句使用顺序:where—>group by—>having where 子句从数据源中去除不符合其搜索条件的数据;group...by... 子句搜集数据行到各个组中,统计函数为各个组计算统计值;having 子局去掉不符合其组搜索条件的各组数据行. select 列名 from 表名where 条件 sgroup by 分组列名 having 分组筛选条件 --多表连接查询 --内连接 select 表名1.列名,表名2.列名 from 表名1,表名2 where 相同的列名条件关系 select 表名1.列名,表名2.列名 from 表名1 inner join 表名2 on 相同的列名条件关系 where 条件 --外连接左外连接:left...join...on或left...outer...join...on 右外连接:right...join...on或right...outer...join...on 完整外连接:full...join...on或full...outer...join...on select 表名1.列名,表名2.列名 from 表名1 left outer join 表名2 on 相同的列名条件关系 select 表名1.列名,表名2.列名 from 表名1 right outer join 表名2 on 相同的列名条件关系 |
T-SQL 高级编程
/*-变量应用-*/
--局部变量以标记@为前缀 --全局变量以标记@@为前缀
--局部变量声明： declare @变量名数据类型
--局部变量赋值：使用set语句或select语句
--set 赋值语句一般赋给变量指定的数据常量：set @变量名 = 值
--select 赋值语句一般用于从表中查询数据,然后再赋给变量
--当用select赋值时,不进行筛选会将最后一条记录赋给变量： select @变量名 = 列名值 from 表名 where 条件
/*-输出语句-*/ print convert(varchar(大小),变量或字符串) --只适合打印字符和字符串 select 变量 as 自定义列名 --适合所有类型 /*-逻辑控制语句-*/ --if-else条件语句 if(条件) begin --语句块 end else begin --语句块 end --while循环语句 while(条件) begin --语句块 end --case多分支语句 case when 条件1 then 结果1 when 条件2 then 结果2 else 结果3 end 列名 = case when 列值 then 表达式 else 表达式 end /*-高级查询-*/ --简单子查询 select 列名 from 表名 where 条件列名 = (select 条件列名 from 表名 where 条件) --in和not in子查询 select 列名 from 表名 where 条件列名 in (select 条件列名 from 表名 where 条件) select 列名 from 表名 where 条件列名 not in (select 条件列名 from 表名 where 条件) --exists和not exists子查询 if exists(select * from 表名 where 条件) begin --语句块 end if not exists(select * from 表名 where 条件) begin --语句块 end /*-事务-*/ --管理事务开始事务:begin transaction 提交事务:commit transaction 回滚事务:rollback transaction --分类显示事务:用begin transaction 明确指定事务的开始隐式事务:通过设置set implicit_transaction on 语句,将隐式事务模式设置为开. 当隐式事务模式操作时,SQL Server将在提交或回滚事务后自动启动新事务.无法描述事务的开始,只需要提交或回滚每个事务. 自动提交事务:这是SQL Server 的默认模式,它将每条单独的T-SQL 语句视为一个事务. 如果成功执行,则自动提交.如果错误,则自动回滚. --开始事务 begin transaction --定义变量,用于累计错误 declare @errorSum int --初始化变量 set @errorSum=0 --语句 --累计错误 set @errorSum = @errorSum + @@error if @errorSum <>0 begin print --错误提示 --回滚事务 rollback transaction end else begin print --成功提示
/*-索引-*/ --索引类型唯一索引:unique 聚集索引:clustered 非聚集索引:nonclusered use 数据库名 go --查找索引：if exists (select * from sysindexes where name = '索引名') --删除索引：drop index 表名.索引名 --创建索引：create nonclustered index 索引名 on 表名(索引列名) where fillfactor = --大小为0-100之间的值 go --查询索引：select * from 表名 (index = 索引名) where 由索引列名组成的条件 /*-创建视图-*/ use 数据库名 go --查询视图：if exists (select * from sysobjects where name = '视图名') --删除视图：drop view 视图名 --创建视图：create view 视图名as--查询语句go --使用视图：select * from 视图名
--自定义存储过程 --不带参的存储过程 use 数据库名 go --查询存储过程：if exists (select * from sysobjects where name = '存储过程') --删除存储过程：drop procedure 存储过程名 go --创建存储过程：create procedure 存储过程名 as declare @变量名数据类型 --SQL语句(一般用于查询语句) go
--调用存储过程：exec 存储过程名
--创建带输入参数的存储过程 use 数据库名 go --查询存储过程：if exists (select * from sysobjects where name = '存储过程') --删除存储过程：drop procedure 存储过程名 go --创建存储过程：create procedure 存储过程名 @变量名1 数据类型 --输入参数(当多个参数时用,隔开) as declare @变量名2 数据类型 --SQL语句(一般用于插入、修改、删除语句) go
--调用存储过程：exec 存储过程名给变量名1赋值
--创建带输出参数的存储过程 use 数据库名 go --查询存储过程：if exists (select * from sysobjects where name = '存储过程') --删除存储过程：drop procedure 存储过程名 go --创建存储过程：create procedure 存储过程名 @变量名1 数据类型 output,--output关键字,否则视为输入参数 @变量名2 数据类型 --输入参数(当多个参数时用,隔开) as declare @变量名3 数据类型 --SQL语句(一般用于查询语句) go
--调用存储过程 declare @变量名4 数据类型 --定义变量，用于存放存储过程变量名1的返回的结果 exec 存储过程名 @变量名4 output,@变量名2 数据类型
/*-处理错误信息-*/ use 数据库名 go --查询存储过程：if exists (select * from sysobjects where name = '存储过程') --删除存储过程：drop procedure 存储过程名 go --创建存储过程：create procedure 存储过程名 @变量名1 数据类型, @变量名2 output as declare @变量名3 数据类型 --错误处理 msg_id:在sysmessages系统表中指定的用户定义错误信息 msg_str:用户定义的特定信息,最长为255个字符 serverity:与特定信息相关联,表示用户定义的严重级别.用户可以使用0~18级,19~25级 是sysadmin固定角色的成员预留的,并且需要指定with log选项,20~25级错 误被认为是致命的错误 state:表示错误的状态,是1~127的值
raiserror('msg_id | msg_str',severity,state); --SQL语句(一般用于查询语句) go
--调用存储过程 declare @变量名4 数据类型 --定义变量，用于存放存储过程d的返回的结果 exec 存储过程名 @变量名4 output,@变量名2 数据类型
函数应用SQL学习笔记
|
部分常用日期函数 |
||
|
函数名 |
描述 |
举例 |
|
getdate |
取得当前的系统日期 |
select getdate()返回:今天的日期 |
|
dateadd |
将指定的数值添加到指定的日期部分后的日期 |
select dateadd(mm,4,'01/01/99') 返回:以当前的日期格式返回05/01/99 |
|
datediff |
两个日期之间的指定日期部分的区别 |
select datediff(mm,'01/01/99','05/01/99')返回:4 |
|
datename |
日期中指定日期部分的字符串形式 |
select datename(dw,'01/01/2000')返回:Saturday |
|
datepart |
日期指定部分的整数形式 |
select datepart(day,'01/15/2000')返回:15 |
函数应用