《深圳华为SQL总结》由会员分享，可在线阅读，更多相关《深圳华为SQL总结（21页珍藏版）》请在金锄头文库上搜索。
1、深圳华为SQL总结 1-更改列标题的三种方法select au_id作者编号,作者姓名=au_lname,phone as电话from authors-where条件查询select*from authors where state=ca-查询住在加州的作者-between.and.条件查询select*from titles where pricebetween10and20-查找图书价格在10至20元间的图书-逻辑运算符、关系运算符的应用select*from titles where price=10and price(select avg(qty)from sales)select a
2、u_id,au_fname from authors where au_id in(select au_id from titleauthor group by au_id havingcount(au_id)=2)select au_id,au_fname from authors where au_id notin(select au_id from titleauthor)select au_id,au_fname from authors wherenot exists(select au_id from titleauthor where authors.au_id=titleaut
3、hor.au_id)select*from salesselect*from storesselect title_id,title,price from titleswhere price250)4create databasemydata on primary(name=mydata,filename=c:aaamydata.mdf,size=5mb,maxsize=50,filegrowth=5)log on(name=mydatalog,filename=c:aaamydatalog.ldf,size=5mb,filegrowth=5)alter databasemydata addf
4、ile(name=mydata2,filename=c:aaamydata2.ndf,size=5mb,maxsize=50,filegrowth=5)select*from sysfilesselect*from sysfiles1select*from sysfilegroupsalter databasemydata addfilegroup group1alter databasemydata removefilegroup group1alter databasemydata modifyname=mydata2alter databasemydata modifyfile(name
5、=mydata,maxsize=150)create databasemydata1onprimary(name=mydata1,filename=c:aaamydata1.mdf,size=5mb,maxsize=50,filegrowth=5),(name=mydata11,filename=c:aaamydata11.ndf,size=5mb,maxsize=50,filegrowth=5),filegroup group1(name=mydata12,filename=c:aaamydata12.ndf,size=5mb,maxsize=50,filegrowth=5),(name=m
6、ydata13,filename=c:aaamydata13.ndf,size=5mb,maxsize=50,filegrowth=5)log on(name=mydatalog1,filename=c:aaamydatalog1.ldf,size=5mb,filegrowth=5),(name=mydatalog2,filename=c:aaamydatalog2.ldf,size=5mb,filegrowth=5)alter databasemydata1modify filegroupgroup1default dbshrinkdatabase(mydata1,80,truncateon
7、ly)db shrinkfile(mydata1,3,truncateonly)drop databasemydata1sp_helpdb mydata2sp_databases sp_renamemydata2,mydata3,databasesp_dboptionmydata3,select into/bulkcopy,true5create table students(sno intidentity(1000,1)primary key,sname varchar (20)not null,age int not null,address varchar (30)select*from
8、 studentscreate table#tt(sno intidentity(1000,1)primary key,sname varchar (20)not null,age intnot null,address varchar (30)select*from#tt create table#ttt(sno intidentity(1000,1)primary key,sname varchar (20)not null,age intnot null,address varchar (30)select*into#tt1from studentsselect*into tt1from studentsselect*from#tt1select au_id,state from(select*from authors)sselect*from#tt alter tablestudentsadd phonevarchar (11)alter tablestu
