MSSQL数据库

MSSQL数据库 (复习资料) 1.创建数据库xan, 并且创建一个主数据库文件, 一个辅数据库文件和一个日志文件. create database xan -----------创建数据库xan on (name=‘xan_data’, ----------主数据文件名为xan_data filename=‘g:\xan\xan_date.mdf’, ----------主数据文件存储路径(主数据文件后缀名为.mdf) size= 1mb, ---------设置起始文件大小为1MB maxsize=untimited, --------设置最大文件大小为不受限制 filegrowth=10%), --------设置文件增长为10%(也可按MB设置) (name=‘xan_data1’, ----------辅数据文件名为xan_data1 filename=‘g:\xan\xan_date1.ndf’, ----------辅数据文件存储路径(辅数据文件后缀名为.ndf) size=1mb, ----------设置起始文件大小为1MB maxsize=50mb, -----------设置最大文件大小为50MB filegrowth=10%), ----------设置文件增长为10% log on (name=‘xan_log’, ----------日志文件名为log_data filename=‘g:\xan\xan_log.ldf’, ---------日志文件存储路径(日志文件后缀名为.ldf) size=2mb, --------设置起始文件大小为2MB maxsize=50mb, --------设置最大文件大小为50MB filegrowth=2mb) ----------设置文件增长为2MB go ---------执行 2.添加一个文件组xangroup alter database xan -----指定更改的数据库xan add filegroup xangroup -----添加文件组为xangroup Go 3.向文件组xangroup添加一个文件xan_love alter database xan -------指定更改的数据库xan add file(name=xan_love, --------添加文件xan_love内容 filename=’g:\xan\xan_love.ndf’, size=1mb, maxsize=100mb, filegrowth=10%) to filegroup xangroup -----添加到指定的文件组xangroup go 4.修改数据库xan的名为nax alter database xan ----指定更改的数据库xan modify name=nax ----更改名为nax go exec sp_renamedb xan,nax 5.修改数据库文件xan_data起始大小为5MB alter database xan ----指定更改的数据库xan modify file(name=xan_data, ---指定更改的数据库文件名xan_data size=5mb ) ---更改新值为5MB go -----注意: 每次只能更改一个文件的属性 6.删除文件组xangroup和其中的文件xan_love alter database xan ----指定更改的数据库xan remove file xan_love ----删除文件xan_love go remove filegroup xangroup ----删除文件组xangroup go ---注意: 删除文件组必须先删除其中的文件 7.删除数据库xan drop database xan ----删除指定的数据库xan go 数据库表 8.在数据库xan创建表为xantable use xan ----指定数据库xan create table xantable ----创建表为xantable (学号 int not null, ----添加列名为学号, 数据类型为int型, 不允许为空 姓名 char(20), ----添加列名为姓名, 数据类型为字符型(长度为20), 默认允许为空 性别 bit, 年龄 smaint, 专业 char(20) default ‘计算机’,) ------添加列名为专业, 默认值为’计算机’ go 9. 为表xantable中添加数据信息 use xan ----指定数据库xan insert into xantable(学号,姓名,性别,年龄) ----向表xantable中添加你所需要填写的列名 values(00001,’竹子’,0,21) ----填写对应的数据信息 go 10. 给表xantable添加标识列xuhao(标识列相当于一个序列, 可以自动添加数据) use xan ----指定数据库xan alter table xantable ----指定更改的表为xantable add xuhao int identity(1,1) not null ----添加列为xuhao, 标识列的起始值为1, 增量系数为1, 不允许为空 go 11. 添加唯一标识符aa use xan -----指定数据库xan alter table xantable -----指定修改的表为xantable add aa unique default newid() -----标识列为aa, 默认为newid() [函数] go 12.修改xantable中的竹子的年龄改为20 use xan ----指定数据库为xan updata xantable ----修改表xantable中的数据信息 set 年龄=20 ----选择年龄的列,并赋予新值 where 姓名=’竹子’ ----选择需改数据信息的目标列姓名=’竹子’ go 13.alter table –修改表结构 use ugv alter table tach add 专业名 char(10)----添加一列 --alter column 姓名 char(10)---修改一列 --drop column 部门---删除一列 go 14. 删除xantable的所有 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 , 删除表xantable use xan delete from xantable ------------------默认删除所有,有日志可恢复 go truncate table xantable -------------表示删除表中全部记录，不会被日志文件所记录，是无法恢复，慎用 drop table xantable go 查询 15.查询xan库中xantable表所有内容 use xan select * ------* 号代表所有 from xantable go 16.查询northwind库products表中指定列(productid、supplierid、birthdate、city )。 use northwind select productid,supplierid,birthdate,city -------select后面跟多个列时用逗号隔开 from products go 17.查询northwind库products表中指定列(productid、supplierid、birthdate、city ) 中supplierid>10的数据 use northwind select productid,supplierid,birthdate,city from products Where supplierid>10 -----------------指定条件为supplierid>10 go 18.查询products表中productname为tofu的记录 use northwind select * from products where productname=’tofu’ go 19.查询products表中birthdate比1960-10-1大的 use northwind select * from products where birthdate>’1960-10-1’ go 20.模糊查询 like use northwind select * from customers where city like ‘c%’ -----查询customers表中city中以c开头的数据 ----where city not like ‘c%’ -----查询customers表中city中不含以c开头的数据 ----where city like ‘a_’ -----查询customers表中city中以c开头并且只有两个字符的数据 Go -----“%”号的使用:%C%，表示包含C;C%，表示C开头; %C，表示C结尾 -----“_”的用法: a_, 表示以a开头并只有两个字符; “_”能有多个, 一个代表一个字符 21.and/or/not use northwind select * from products where(productname like ‘%e’ and unitprice<40) or product=73-----列出(以e结尾且unitprice<40)或者product=73的数据 Go 22.between„and 在„范围之内 use northwind select * from products where unitprice between 10 and 20 --(大于10小于20) -----包含10和20 ------- where unitprice not between 10 and 20 --(小于10大于20)----不包含10和20 go 23.In ==or 确定给定的值是否与查询或列表中的值相匹配 use northwind select * from customers where city in('berlin','london') -----查询city中’berlin’和’london’的数据 -------where city not in('berlin','london')---- 查询city中除了’berlin’和’london’的数据 go 24. is null查询为空的数据 use northwind select companyname,fax from suppliers where fax is null ------查询fax为空的数据 -----where fax is not null----查询fax不为空的数据 Go 25.order by 排序: asc升; desc 降 use northwind Select productname,categoryid, unitprice, supplierid from products order by categoryid asc ,unitprice desc ----第一列categoryid为主序, 默认为asc, 第二列unitprice为desc Go 26.distinct 消除重复的记录 use northwind select distinct country ------查询suppliers中消除country重复的记录 from suppliers order by country Go 27.AS 别名(修改列名) use northwind select lastname as a, firstname b,city, lastname+firstname as c—修改lastname为a, firstname为b, lastname+firstname为c from employees Go ----------注意as 可用空格代替, 就如firstname b一样. use northwind select lastname as a, firstname b, city, ‘我来补一列’ as nokown ------添加一列nokown并填充”我来补一列” from employees go 28.分组排列top限制返回到结果集中的行数 use northwind select top 5 orderid, productid, quantity----查询前5个orderid的数据(只有5行, 超 过的并列一样的舍去) -- select top 5 with ties orderid, productid, quantity ---查询前5个orderid的数 据(只要前5个orderid的全部列出来) from [order details] --order by quantity desc ----排序(优先级最高, 先做排序),将quantity排成降序 go ---注意: 用with ties 时必须要排序(也就是必须要与order by连用) 聚集(合)函数 29. Avg 平均值 use northwind select avg(unitprice) --------求unitprice的平均值 from products go 30. count use northwind select count(*) ------- count(*)统计表中有多少行 ----select count(region) ------- count(列名)统计列中非空 的值行 from employees go 31. max( )/min( ) 最大/最小 use northwind select max(productid) --------求productid的最大值 --select mix(productid)------- 求productid的最小值 from products go 32. Sum( ) 求和 use northwind select sum(productid) -----求productid的和 from products go 33. insert 表名 select ------插入结果集 use northwind insert customers select substring (firstname, 1, 3)+ substring (lastname, 1, 2),lastname, firstname, title, address, city,region, postalcode, country, homephone, null from employees ------注意: 字符类型要一一对应, 不足的用null补齐 go 34. group by 分组汇总排列 use northwind select productid,sum(quantity) as 'quantity的总和' from [order details] where productid<10 group by productid ----分组汇总排列必须要有group by ---order by productid ----可以和group by连用, 但要放在group by下面 ---having sum(quantity) >=30 -----having必须与group by 联用; go ---注意:group by与聚集函数连用，但子句不能包含聚集函数 . ----------注意:group by, order by, having连用时顺序为group by, having, order by -----having与where的区别在于having后面可以跟聚合函数, 而where不能 35. compute [--by] 生成统计栏 use northwind select orderid,productid,quantity from [order details] ----order by productid desc ----对productid进行降序排列 compute sum(quantity) ----compute sum(quantity) by productid ----必须与order by 连用 go ------将quantity的所有的值合计出来, 分栏显示出来 36. 向customers插入结果集到employees中 use northwind insert customers select substring (firstname, 1, 3)+ substring (lastname, 1, 2), lastname, firstname, title, address, city,region, postalcode, country, homephone, null ---插入的列要与customers的数据类 型一致,不够用null补齐 from employees go ----substring(取子符串，起始位，取几位) 37. select„into 表名 ----将结果集生成新表(无日志操作) use northwind select orderid as products,freight as price,shipcity as tax into #pree ---#pree 代表临时表pree from orders go 38. 将上下两张表中两列的内容合并到一起 use northwind select employeeid from employees union -----union消除重复值 ----union all -----union all不消除重复值 select employeeid ----注意: 列名一定要相同 from employeeterritories Go --将两个结果集合并起来 表 联 接 联 接 查 询 40. 内 联 接 inner join (交集) use northwind select productid, suppliers.supplierid, suppliers.fax -----suppliers.supplierid 代表suppliers表中的supplierid from products[inner] join suppliers ----inner join代表联接products和suppliers这两张表(inner可以省略不写) on products.supplierid=suppliers.supplierid -----指定两张表中的supplierid要一样 where suppliers.fax is not null ----指定suppliers中的fax不为空 go 41.外联接left outer join (左外连接)/ right outer join(右外连接) / full outer join(全外连接) use pubs select a.au_fname, a.au_lname,p.pub_name from authors a left outer join publishers p --left outer为左外连接(outer可以不写), 包括满足连接条件的行外，还包括左表的所有行 ---- right outer join ----right outer为右外连接, 包括满足连接条件的行外，还包括右表的所有行; ---- full outer join ----full outer 为全外连接, 结果集中除了包括满足连接条件的行外，还包括两个表的所有行; on a.city = p.city Go 注意: 只连接两个表, 因为显示的全部信息, 所以不与where连用 42. 多表查询(联接) ------用逗号相隔 use northwind select productid,suppliers.supplierid,suppliers.fax from products , suppliers --------查询两张表, 中间用逗号隔开 where products.supplierid=suppliers.supplierid -----指定条件 Go -----注意: select查询语句，不能算联接语句(不推荐) 43. cross join交叉连接(笛卡尔集) use northwind select suppliers.companyname,shippers.companyname from suppliers cross join shippers go ----也就是将suppliers.companyname和shippers.companyname的所有能组合的全部排列出来 44.自 联 接(特殊的内联接) use pubs select distinct a.type from titles a join titles b ------连接自己本身 on a.type=b.type where a.pub_id<>b.pub_id go --------显示的是表中pub_id不同的type 45.子表查询 (扩展) use northwind select t.orderid, t.customerid from ( select orderid, customerid from orders ) as t go ---------也就是将括号里面的内容生成了一张新表, 然后在新表中查询 46.子列查询(扩展) use pubs select title, price, (select avg(price) from titles) as average, price-(select avg(price) from titles) as difference from titles go ------也就是将括号里面的内容生成一个新列 47.条件where子查询 use northwind select orderid, customerid from orders or1 where 20 < (select quantity from [order details] od where or1.orderid = od.orderid and od.productid = 23) go ----查询的是order details中的quantity>20 并且orders和order details两 张表中的orderid一样并且order details中的productid=23 48.条件 Exists 子查询 use northwind select orderid,productid,unitprice from [order details] where exists (select * from orders where orders.orderid=[order details].orderid and productid = 23 and quantity>20) go ----查询的是order details中的quantity>20 并且orders和order details两张表中的orderid一样并且order details中的productid=23 49.条件 In 子查询 use northwind select orderid,productid,unitprice from [order details] where orderid in(select orderid from orders) and productid = 23 and quantity>20 go ----查询的是order details中的quantity>20 并且orders和order details两张表中的orderid一样并且order details中的productid=23 数据完整性 50.域完整性(列)default(默认值) / check(检查条件) (1).创建表时就给这一列设置默认值为xan, constraint是约束的意思, df_1为约束名 name char(10) constraint df_1 default ‘xan’ (2).添加一列并给这一列设置默认值为xan add name char(10) constraint df_1 default ‘xan’ (3).给表中的一列添加一个默认值 add constraint df_1 default ‘xan’ for name ------for后面接指定的列名 或者 alter column name char(10) constraint df_1 default ‘xan’ (4)创建一个默认规则 Create default df_1 as ‘xan’ -----------这是直接创建在数据库中的 (5)将创建好的默认规则绑定到指定的列 Exec sp_bindefault df_1, [表名.列名] (6)解除绑定 Exec sp_unbindefault [表名.列名] (7) 创建一个规则(必须是临时变量, 它比default更灵活) create rule ru_1 as @name in(‘学校’,’医院’,’商场’) (8)绑定这个规则 exec sp_bindrule ru_1, [表名.列名] (9) 解除这个规则绑定 Exec sp_unbindrule [表名.列名] (10) 创建表时就给这一列设置检查约束条件为18= 1 ---------括号指的是统计出deleted表中的总行数 begin raiserror('你删除了多条数据', 16, 1) ---- 16显示红色字和黑色字信息, 如是10就只显示黑色字信息 rollback transaction -----回滚的意思(也就是回到上一步操作,所做的删除操作无效) end go (7)添加一个订单, 库存相应减少 如果删除过期订单, 则库存要相应增加 use northwind go create trigger trg_1 on [order details] for insert, delete as if (select count(*) from inserted) >0 --判断当前操作是不是添加 begin update p set unitsinstock=(p.unitsinstock-i.quantity) from products as p join inserted as i on p.productid=i.productid end else if(select count(*) from deleted) > 0 ----判断当前操作是不是删除 begin update p set unitsinstock=(p.unitsinstock+d.quantity) from products as p join deleted as d on p.productid=d.productid end go 游标 ? 声明游标。使用T-SQL语句生成一个结果集，并且定义游标的特征，如游标中的记录是否可以修改。 DECLARE 游标名 [scroll]CURSOR FOR [for read only|for update of 列名] ? 打开游标。 OPEN 游标名 ? 从游标的结果集中读取数据。从游标中检索一行或多行数据称为取数据。 FETCH [ NEXT | PRIOR | FIRST | LAST|ABSOLUTE|RELATIVE] FROM 游标名 ? 对游标中的数据逐行操作。 /*检查@@FETCH_STATUS以确定是否还可以继续取数*/ WHILE @@FETCH_STATUS = 0 BEGIN FETCH NEXT FROM authors_cur END ? 关闭和释放游标。 关闭游标: CLOSE 游标名 释放游标: DEALLOCATE 游标名