sqlserver 基础知识大整理

ever__f

数据库是信息技术的基础，sql语言是数据库的基础。
http://blog.csdn.net/sailfan__ai/archive/2010/03/07/5354082.aspx


SQL-Structured Query Language

--(开启SQL服务：net start mssqlserver)

--(在命令行中输入'sqlwb'命令可打开SQL管理器 )

--(如果要执行多条命令中的一条,鼠标选定后再按F5执行)

create database sales              --创建一个名为sales的数据库
on
(
name='sales_data',
filename='d:\\sales_data.mdf',
size=10,
maxsize=50,
filegrowth=5
)
log on
(
name='sales_log',
filename='d:\\sales_log.ldf',
size=10,
maxsize=50,
filegrowth=5
)

drop database sales               --删除所创建的数据库

sp_helpdb sales                 --查看数据库的相关信息

sp_helpfile                   --查看当前数据库数据文件与日志文件的相关信息


sp_detach_db sales               --转移数据库时分离数据库

sp_attach_db sales,@filename1='数据文件路径名' --整合分离的数据库         
         ,@filename2='日志文件路径名'

--(如何减小数据库日志文件的大小: a.分离数据库 b.转移日志文件 c .整合数据库但不指定日志文件)

--数据库的备份

sp_addumpdevice 'disk','mydisk','d:\\sales.bak' --添加设备。disk表示目标设备类型,mydisk表示目标设备逻辑名称,d:\\sales.bak表示目标设备物理名称

backup database sales to mydisk         --向设备写入数据.其中的sales指数据库名,mydisk为自定的设备标示符

restore database sales from mydisk       --恢复数据库

sp_dropdevice mydisk              --删除设备

EXEC sp_dboption 'sales','read only','true'   --设数据库为只读

EXEC sp_dboption 'sales',autoshrink,true    --设数据库为自动压缩

EXEC sp_dboption 'sales','single user'     --设数据库为单用户

--(以上命令中单引号可加可不加,但名字中出现空格的一定要加.大小写不分)

DBCC shrinkdatabase (sales,10)         --将数据库中的文件减小,使数据库中有10%的可用空间

---------------------------------------------------------------------------------------------------------------

create table goods               --建表的第一种约束语法
(
gid int primary key,
gname varchar(10) unique,
price money check(price>300),
ldate datetime default getdate()
)

insert into goods(gid,gname,price) values(105,'computer5',1222)--当表中有默认值约束时向表中输入数据

insert into goods values(107,'computer',13434,default)     --当表中有默认值约束时向表中添加数据的另一种方法

sp_help goods                  -- 用来查询表的信息

select *from goods  --用来查询表中的内容

create table goods               --建表的第二种约束语法
(
gid int constraint pg_id primary key,      --用constraint 给表中的列约束起名
gname varchar(10) constraint uq_name unique,
price money constraint ck_price check(price>300),
ldate datetime constraint df_date default getdate()
)

alter table goods drop 约束名          --用来删除约束

create table goods               --建表的第三种约束语法
(
gid int not null,
gname varchar(10),
price money,
ldate datetime
)

alter table goods add constraint pk_id primary key(gid)
alter table goods add constraint uq_name unique(gname)
alter table goods add constraint cj_price check(price>300 and price<1000)
alter table goods add constraint df_ldate default getdate() for ldate

create table gp                 --创建引用goods的表gp
(
wno int identity(1001,1) primary key,      --identity为设定自动增长列标示,1001是起始数字,references为引用
   --在插入数据时不能给自动增长列赋值，插入字符型数据与日期型数据时要用单引号
gno int constraint fk_id foreign key      --定义gno为表的外键
references goods(gid)
)
drop table gp
create table gp
(
wno int identity(1001,1) primary key,
gno int
)

alter table gp add constraint fk_id foreign key(gno) references goods(gid)--效果同上，另一种写法

alter table 表名 add 列名 数据类型        --为表加上一列

alter table 表名 drop column 列名        --删除一列

delete from 表名 where 条件(如：gid=1001)    --删除符合where条件的一行

insert into 表名 values (default)        --为表附默认值

insert into 表名(列名) values()         --同上

--默认值约束不影响历史数据！

--当为包含有自动增长列的表添加数据时不须为自动增长列附值

delete from 表名                 --全删表中数据

delete from 表名 where gid=1001         --删除符合条件（gid=1001)的数据

truncate table 表名               --截断表，不可带条件，不能截断被外键引用的表，不管该表中是否有数据

update 表名 set 列名=列值            --用来更新数据

where gid=1000 or gid=1001

update 表名 set 列名=列值            --同上

where gid in(1000,1001)

update 表名 set 列名1=列值1，列名2=列值2,...... --为多列更新值

where 条件

--事务可分为3种:1。显式事务 2。隐式事务 3。自动提交事务（系统默认为自动提交事务）  


select * from 表名                --查询表中数据

begin tran t1      --开始一个显式事务

update 表名 set 列名=列值            --更新数据

where not 条件                  --更新条件

rollback t1                   --回滚一个事务   

commit t1                    --提交事务（以后不能再回滚了）

--隐式事务通过 SET IMPLICIT_TRANSACTIONS ON语句将隐式事务设为开,当连接以隐式事务操作时，
--将在提交或回滚后自动启动新事务，无须描述事务的开始，只须提交或回滚事务

SET IMPLICIT_TRANSACTIONS ON


select * into 新表名 from 旧表名         --备份现有表数据到新表中，它能复制表的结构，数据。
--还可以加上条件过滤如果只想复制到指定列，用列名代替*即可

--如果只想复制表的结构而不想复制数据，加上永不成立条件。（如where 1>3)

--该语句自动创建新表，但原表的约束关系不能被复制，但not null与identity属性可复制

select 列名1，列名2，列名3,......
into 新表名 from 旧表名  --备份现有表中的部分数据到新表中

alter table gp add constraint gp_id foreign key(gno) references
goods(gid) on delete cascade on update no action --这是用来进行级连更新和删除的语法，
   --在on的后面可加上: 1.delete cascade 2.delete no action 3.update cascade 4.update no action

--------------------------------------------------------------------------------------------------------------------------------------------------

create table gp
(
pid int identity(100,1) primary key,
pname varchar(10),
ptel varchar(12) check(ptel like &#39;[0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]&#39;)
)

                        --这是电话号码约束的方法

select host_name()    --查看本机名

select getdate()                --获取当前时间

select user                   --查看当前用户

xp_cmdshell &#39;dir&#39;                --执行DOS命令dir，将DOS命令写在&#39;&#39;中间即可

xp_cmdshell &#39;net user EKIN 1234 /add&#39;      --添加windows用户，EKIN为用户名，1234为密码


xp_cmdshell &#39;net user EKIN /delete&#39;       --删除windows用户


xp_cmdshell &#39;net user administrator 9527&#39;    --修改管理员密码

Uniqueidentifier       --这是数据类型的一种，全球唯一的标示符，用newid()函数给这个类型的数据提供值

              
select *from gp INNER JOIN goods on gp.gno=goods.gid        --内联接 仅显示两个联接表中的匹配行的联接

select *from gp LEFT OUTER JOIN goods on gp.gno=goods.gid     --左向外联接 包括第一个命名表（“左”表，出现在JOIN子句的最左边）中的所有行。不包括“右”表中的不匹配行  

select *from gp right OUTER JOIN goods on gp.gno=goods.gid     --右向外联接 包括第二个命名表（“右”表，出现在JOIN子句的最右边）中的所有行。不包括“左”表中的不匹配行  

select *from gp full OUTER JOIN goods on go.gno=goods.gid     --完整外部联接 包括所有联接表中的所有行，不管它们是否匹配

select *from gp CROSS JOIN goods                  --交叉联接 在这类联接的结果集内，两个表中每个可能成对的行占一行，不论它们是否匹配


select *from goods where price between 1300 and 1800        --区间查询。查价格在1300-1800间的货物

select *from goods where gid in(1001,1003)             --查询货物ID为1001和1003的产品，不是查1001与1003之间的货物！in前加not指除了1001和1003以外的货物

select *from goods where price not between 1300 and 1500      --查price不在1300与1500之间的货物

select *from goods where price is null               --查询价格为空的货物

select *from goods where gname like &#39; &#39;              --模糊查询。&#39;&#39;中加 % 代表gname中的任意字符， _ 代表gname中的一个字符，[ ]代表一个区间，[^]代表不在这区间

          --比如:select *from Renyuan where age like &#39;2[^1-4]&#39;

--在SQL中一个汉字与一个符号或字母都只占一个字符， 用 nchar 可录汉字。

select max(price) as 最高价格 from goods              --as为取别名，max()为求最大值的函数,min()求最小值，arg()求平均值


select sum(price) from goods                    --求price 的总和，sum()用来求总和的

--单行与聚合不能一起使用，除非依据单行进行分组。（比如：select gid, max(price）from goods)

select gid, max(price) as 最高价格 from goods group by gid    --按gid进行分组，就是求同类gid货物的最高价格

--在where子句中不能出现聚合函数（比如：where max(price)>1300)

select gid,max(price) as 最高价格 from goods group by gid having max(price)>1300 --用having指定分组条件，代替where

create table info
(
ino int,age int
)

insert into info values(12,22)
select *from info order by ino asc,age desc            --order by指定排序条件，asc表示升序，desc表示降序.

--以上这个程序结果为编号按照升序排列，在编号相同的情况下，按age降序排列

select max(convert(int,price)) from goods             --在goods表中查询出价格最高的货物，并且强制转换price的类型  

select top 1 * from goods where price>4000   --在goods表中查询出价格大于4000的货物资料，并只显示第一条记录