sqlserver 基础知识大整理
sqlserver 基础知识大整理（强烈推荐之一）
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 '[0-9][0-9][0-9][-][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
)
--这是电话号码约束的方法
select host_name() --查看本机名
select getdate() --获取当前时间
select user --查看当前用户
xp_cmdshell 'dir' --执行DOS命令dir，将DOS命令写在''中间即可
xp_cmdshell 'net user EKIN 1234 /add' --添加windows用户，EKIN为用户名，1234为密码
xp_cmdshell 'net user EKIN /delete' --删除windows用户
xp_cmdshell 'net user administrator 9527' --修改管理员密码
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 ' ' --模糊查询。''中加 % 代表gname中的任意字符， _ 代表gname中的一个字符，[ ]代表一个区间，[^]代表不在这区间
--比如:select *from Renyuan where age like '2[^1-4]'
--在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的货物资料，并只显示第一条记录
