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的货物资料，并只显示第一条记录