sql基本语句代码总结.docx

1、sql基本语句代码总结1. 创建sql 语句Create database mydbon( Name=mydb_dat, Filename=f:datamydb_dat.mdf, Size=5mb, Maxsize=20mb, Filegrowth=5%)Log on( Name=mydb_log, Filename=f:datamydb_log.ldf, Size=5mb, Maxsize=20mb, Filegrowth=1mb)2. 可以创建同一个文件组，组名默认为PRIMARY，数据文件可以存放在不同磁盘上，数据库中的数组是分布在所有的次要文件中create database MyDB

2、 on primary( name=mydb_dat, filename=f:datamydb_dat.mdf, size=6MB, maxsize=20mb, filegrowth=5%),filegroup dbfg ( name=dbfg_dat1, filename=d:datamydb_dat1.ndf, size=5mb, maxsize=20mb, filegrowth=5%), ( name=dbfg_dat2, filename=e:datamydb_dat2.ndf, size=5mb, maxsize=20mb, filegrowth=1mb)log on( name=m

3、ydb_log, filename=f:datamydb_log.ldf, size=5MB, maxsize=20mb, filegrowth=5%) 3. 修改数据库名 alter database mydb modify name=youdb 存储过程修改数据库名 exec sp_dboption youdb,single,true exec sp_renamedb youdb,mydbexec sp_dboption mydb,single,false 4. 修改数据库文件大小Use masterAlter database mydb Modify file( Name=mydb_da

4、t, Size=6mb, Filegrowth=1%)5. 增加数据库文件组Use masterAlter database mydbAdd file( Filename=f:datamydb_data1.ndf, Name=mydb_dat1, Size=6mb, Filegrowth=1%)6. 删除数据库 drop database mydb7. 从sql 中删除，但保持其数据和事务日志文件（相当于分离数据库）Exec sp_detach_db mydb 8. 附加数据库 Exec sp_attach_db mydb,f:datamydb_dat.mdf9. 备份数据库Backup da

5、tabase mydb toDisk=f:datadatabase_bak.dat with name=backup10. 还原数据库restore database mydb from disk=f:datadatabase_bak.dat with replace11. 创建数据库快照create database mydb001On( name=mydb_dat, Filename=f:datamydb001.mdf),(name=ddd, -这是次要文件可以没有Filename=f:ddd.ndf)As snapshot of mydb12. 恢复数据库快照restore databa

6、se mydb from database snapshot=mydb00113. 查看日志 dbcc log (数据库名,n) N的值 0 - 最少信息(operation, context, transaction id) 1 - 更多信息(plus flags, tags, row length) 2 - 非常详细的信息(plus object name, index name,page id, slot id) 3 - 每种操作的全部信息 4 - 每种操作的全部信息加上该事务的16进制信息 默认 type = 01. 创建用户自定义类型sp_addtypetype_name,base_

7、type,null|not null,拥有者删除 sp_droptype type_name2. 在MMM数据库中创建自定义数据类型exec sp_addtype nametype,varchar(20),not null删除 sp_droptype nametype3. 创建临时表 l临时表在退出其作用域是由系统自动删除create table #mytemptable 一个#的为本地临时表（ Cola int)create table #mytemp 两个#的为全局临时表( Cola int) 在tempdb 库中查看临时表信息4. 创建、删除表创建表sales 包含以下字段 order_

8、no 不能空 Ordr_date 不能为空 Ship_date 不能为空use projects Create table sales( Order_no int not null, order_date datetime not null, Ship_date datetime not null)更改表名Exec sp_rename sales ssaless删除表drop table sales5. .创建函数列的表Create table testtable(Indate as getdate(),Id int,Usename as user_name() . 用户定义类型列创建计算列c

9、reate table testable( id int not null, Col1 nametype). 表变量 declare t table ( col1 int, col2 varchar(10) Insert into t values(1,很好)查询 select * from t6. 修改表employee添加列：电话号码 telephone_no 添加列：字符列email为char修改列类型：email为varchar 修改列空为非空列 修改字段名： 删除列：删除email列alter table employee Add telephone int null,Email c

10、har(29) null Alter table eemployee alter column email varchar(20) null alter table employee alter column email varchar(20) not nullexec sp_rename “构架名.表名.旧字段名,新字段名,columnalter table employeedrop column email7. 约束. 主键约束 创建是加主键 create table customers( customerno int identity not null primary key, Cust

11、omername varchar(30) not null) 在现有表中添加主键 alter table customers Add constraint pk_customerno Primary key (customerno)外键约束 使用外键床架一个表 Create table orders ( Ordered int identity not null primary key , Customerno int not null foreign key references customers(customerno)在已存在的表中添加外键alter table orders add c

12、onstraint fk_employeeCreatesorders Foreign key (customerno) references customers (customerno)级联动作 create table OrderDetails ( orderid int not null, partno varchar(10) not null constraint PKOrderDetails primary key (orderid,partno), constraint FKOrderContrainsDetails Foreign key (OrderID) References

13、orders(orderid) on update no action on delete cascade) 唯一约束 在创建表时创建唯一约束 create talbe shippers ( Shipperid int identity not null primary key,Phoneno varchar(14) not null unique)在已存在的表中创建唯一约束alter table shippersAdd constraint ak_shippersphoneno unique(phoneno)Check 约束alter table customers Add constrai

14、nt cn_customerdateinsystem check(dateinsystem=getdate()删除check 约束alter table testableDrop constraint cn_customerdateinsystem. Default 约束 创建默认表 create table shippers( Shipperid int identity not null primary key, Dateinsystem smalldatetime not null default getdate () 在已存在的表上加上默认值 alter table customers

15、 add constraint cn_customerdefaultdateinsystemDefault getdate() for dateinsystem1. 表数据插入使用Insert 插入USE northwindINSERT into customers(customerid, companyname, contactname, contacttitle,address, city, region, postalcode, country, phone,fax)VALUES (PECOF, Pecos Coffee Company,Michael Dunn,Owner, 1900

16、Oak Street, Vancouver, BC,V3F 2K1, Canada,(604) 555-3392,(604) 555-7293) 使用 insert 和select 插入行 USE northwindINSERT customersSELECT substring(firstname, 1, 3) + substring (lastname, 1,2),lastname, firstname, title, address, city,region, postalcode, country, homephone, NULLFROM employees 使用select into

17、 插入 USE northwindSELECT productname AS products,unitprice AS price, (unitprice * 0.1) AS taxINTO #pricetableFROM products 使用top 插入USE northwindSELECT top 10 productname AS products,unitprice AS price, (unitprice * 1.1) AS taxINTO #pricetableFROM products Select * from #pricetable 2. 插入部分数据 USE north

18、windINSERT shippers (companyname)VALUES (Fitch & Mather) SELECT * FROM shippersWHERE companyname = Fitch & Mather3. 使用delete 语句USE northwinddelete shippers WHERE companyname = Fitch & Mather4. 使用truncate table 删除表中所有的行，但保留表的结构和与之相关的对象 TRUNCATE TABLE 语句比 DELETE 语句执行速度快 如果表中有 IDENTITY 列，TRUNCATE TABLE

19、 语句会重新设置原始数据USE northwind TRUNCATE TABLE orders5. 删除基于其他表的行DELETE FROM 表名 | 视图名FROM ,nWHERE 搜索条件 6. 根据表中数据更新use northwind Update products Set unitprice =(unitprice*1.5),列名=新值7. 表操作 Use AdventureWorks Select * from productUse AdventureWorks Select name,productnumber,reorderpoint from product8. 使用字符串比

20、较符通配符描述%0或多个字符串_任何单个的字符在指定区域或集合内的任何单个字符 不在指定区域或集合内的任何单个字符USE northwindSELECT companynameFROM customersWHERE companyname LIKE %Restaurant% 其中包含restrurant9. 使用逻辑运算符优先级 not and orUSE northwind SELECT productid, productname, supplierid, unitprice FROM products WHERE (productname LIKE T% OR productid = 1

21、6) AND (unitprice 16.00) 10. 检索一定范围内的值USE northwindSELECT productname, unitpriceFROM productsWHERE unitprice BETWEEN 10 AND 20 11. 使用值列表作为搜索条件 USE northwindSELECT companyname, countryFROM suppliersWHERE country IN (Japan, Italy) 12. 检索未知值 USE northwindSELECT companyname, faxFROM suppliersWHERE fax I

22、S NULL 13. 对数据进行排序 asc 为正序 desc 为倒序 use northwind select * from employees order by firstname asc(desc) 14. 消除重复行 USE northwindSELECT DISTINCT country FROM suppliers ORDER BY country 15. 使用top NUSE northwindSELECT TOP 5 productname, unitpriceFROM products SELECT TOP 5 PERCENT productname, unitpriceFR

23、OM products 16. 使用聚合函数例子：USE northwindSELECT productid, SUM(quantity) AS QuantitySum from order details GROUP BY productidORDER BY productid 17. 使用 HAVING 子句时，应注意 只在使用 GROUP BY 子句的同时，使用 HAVING子句来限制分组 可以引用任何出现在选择列表中的字段不要联合使用关键字 ALL 和 HAVING 子句。 因为 HAVING 子句会忽略 ALL 关键字，并返回只符合 HAVING 条件的组group by 分组 US

24、E northwindSELECT productid, SUM(quantity) AS QuantitySum from order details GROUP BY productidHAVING SUM(quantity)500ORDER BY SUM(quantity) 18. 连接内部连接是连接类型中最普通的一种，与大多数连接一样，内部连接根据一个或几个相同的字段将记录匹配在一起，但是内部连接仅仅返回那些存在的字段匹配的记录Use northwind select products.*,suppliers.supplierid from products inner join su

25、ppliers on products.supplierid=suppliers.supplieriduse pubsselect a.au_lname+, +a.au_fname as author,t.title from authors ajoin titleauthor ta on a.au_id=ta.au_id join titles t on t.title_id=ta.title_id 外部连接时必需跟上左侧连接还是右侧连接Use pubsselect discounttype,discount,s.stor_name from discounts d left outer j

26、oin stores s on d.stor_id=s.stor_idselect discounttype,discount,s.stor_name from discounts d right join stores s on d.stor_id=s.stor_id 完全连接Use pubsselect discounttype,discount,s.stor_name from discounts d full join stores s on d.stor_id=s.stor_id 交叉连接Use pubsselect discounttype,discount,s.stor_name

27、 from discounts d cross join stores s Union 连接use northwind select companyname ,address,cityfrom customersunionselect companyname,address,cityfrom suppliers IN use northwind select * from customers where customerid in (select distinct customerid from orders) Exists use northwind select * from orders where Exists(select * from customers where customerid=alfki )