1、创建表和数据插入sql
我们在开始创建数据表和向表中插入演示数据之前，我想给大家解释一下实时数据表的设计理念，这样也许能帮助大家能更好的理解sql查询。
在数据库设计中，有一条非常重要的规则就是要正确建立主键和外键的关系。
现在我们来创建几个餐厅订单管理的数据表，一共用到3张数据表，item master表、order master表和order detail表。
创建表：
创建item master表：
create table [dbo].[itemmasters]( [item_code] [varchar](20) not null, [item_name] [varchar](100) not null, [price] int not null, [tax1] int not null, [discount] int not null, [description] [varchar](200) not null, [in_date] [datetime] not null, [in_usr_id] [varchar](20) not null, [up_date] [datetime] not null, [up_usr_id] [varchar](20) not null, constraint [pk_itemmasters] primary key clustered ( [item_code] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary]
向item master表插入数据：
insert into [itemmasters] ([item_code],[item_name],[price],[tax1],[discount],[description],[in_date] ,[in_usr_id],[up_date],[up_usr_id]) values ('item001','coke',55,1,0,'coke which need to be cold',getdate(),'shanu' ,getdate(),'shanu') insert into [itemmasters] ([item_code],[item_name],[price],[tax1],[discount],[description],[in_date] ,[in_usr_id],[up_date],[up_usr_id]) values ('item002','coffee',40,0,2,'coffe might be hot or cold user choice',getdate(),'shanu' ,getdate(),'shanu') insert into [itemmasters] ([item_code],[item_name],[price],[tax1],[discount],[description],[in_date] ,[in_usr_id],[up_date],[up_usr_id]) values ('item003','chiken burger',125,2,5,'spicy',getdate(),'shanu' ,getdate(),'shanu') insert into [itemmasters] ([item_code],[item_name],[price],[tax1],[discount],[description],[in_date] ,[in_usr_id],[up_date],[up_usr_id]) values ('item004','potato fry',15,0,0,'no comments',getdate(),'shanu' ,getdate(),'shanu')
创建order master表：
create table [dbo].[ordermasters]( [order_no] [varchar](20) not null, [table_id] [varchar](20) not null, [description] [varchar](200) not null, [in_date] [datetime] not null, [in_usr_id] [varchar](20) not null, [up_date] [datetime] not null, [up_usr_id] [varchar](20) not null, constraint [pk_ordermasters] primary key clustered ( [order_no] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary]
向order master表插入数据：
insert into [ordermasters] ([order_no],[table_id] ,[description],[in_date],[in_usr_id],[up_date],[up_usr_id]) values ('ord_001','t1','',getdate(),'shanu' ,getdate(),'shanu') insert into [ordermasters] ([order_no],[table_id] ,[description],[in_date],[in_usr_id],[up_date],[up_usr_id]) values ('ord_002','t2','',getdate(),'mak' ,getdate(),'mak') insert into [ordermasters] ([order_no],[table_id] ,[description],[in_date],[in_usr_id],[up_date],[up_usr_id]) values ('ord_003','t3','',getdate(),'raj' ,getdate(),'raj')
创建order detail表：
create table [dbo].[orderdetails]( [order_detail_no] [varchar](20) not null, [order_no] [varchar](20) constraint fk_ordermasters foreign key references ordermasters(order_no), [item_code] [varchar](20) constraint fk_itemmasters foreign key references itemmasters(item_code), [notes] [varchar](200) not null, [qty] int not null, [in_date] [datetime] not null, [in_usr_id] [varchar](20) not null, [up_date] [datetime] not null, [up_usr_id] [varchar](20) not null, constraint [pk_orderdetails] primary key clustered ( [order_detail_no] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary] ) on [primary] --now let’s insert the 3 items for the above order no 'ord_001'. insert into [orderdetails] ([order_detail_no],[order_no],[item_code],[notes],[qty] ,[in_date],[in_usr_id],[up_date],[up_usr_id]) values ('or_dt_001','ord_001','item001','need very cold',3 ,getdate(),'shanu' ,getdate(),'shanu') insert into [orderdetails] ([order_detail_no],[order_no],[item_code],[notes],[qty] ,[in_date],[in_usr_id],[up_date],[up_usr_id]) values ('or_dt_002','ord_001','item004','very hot ',2 ,getdate(),'shanu' ,getdate(),'shanu') insert into [orderdetails] ([order_detail_no],[order_no],[item_code],[notes],[qty] ,[in_date],[in_usr_id],[up_date],[up_usr_id]) values ('or_dt_003','ord_001','item003','very spicy',4 ,getdate(),'shanu' ,getdate(),'shanu')
向order detail表插入数据：
insert into [orderdetails] ([order_detail_no],[order_no],[item_code],[notes],[qty] ,[in_date],[in_usr_id],[up_date],[up_usr_id]) values ('or_dt_004','ord_002','item002','need very hot',2 ,getdate(),'shanu' ,getdate(),'shanu') insert into [orderdetails] ([order_detail_no],[order_no],[item_code],[notes],[qty] ,[in_date],[in_usr_id],[up_date],[up_usr_id]) values ('or_dt_005','ord_002','item003','very hot ',2 ,getdate(),'shanu' ,getdate(),'shanu') insert into [orderdetails] ([order_detail_no],[order_no],[item_code],[notes],[qty] ,[in_date],[in_usr_id],[up_date],[up_usr_id]) values ('or_dt_006','ord_003','item003','very spicy',4 ,getdate(),'shanu' ,getdate(),'shanu')
2、简单的select查询语句
select查询语句是sql中最基本也是最重要的dml语句之一。那么什么是dml？dml全称data manipulation language（数据操纵语言命令），它可以使用户能够查询数据库以及操作已有数据库中的数据。
下面我们在sql server中用select语句来查询我的姓名(name)：
select 'my name is syed shanu' -- with column name using 'as' select 'my name is syed shanu' as 'my name' -- with more then the one column select 'my name' as 'column1', 'is' as 'column2', 'syed shanu' as 'column3'
在数据表中使用select查询：
-- to display all the columns from the table we use * operator in select statement. select * from itemmasters -- if we need to select only few fields from a table we can use the column name in select statement. select item_code ,item_name as item ,price ,description ,in_date from itemmasters
3、合计和标量函数
合计函数和标量函数都是sql server的内置函数，我们可以在select查询语句中使用它们，比如count(), max(), sum(), upper(), lower(), round()等等。下面我们用sql代码来解释这些函数的用法：
select * from itemmasters -- aggregate -- count() -> returns the total no of records from table , avg() returns the average value from colum,max() returns max value from column -- ,min() returns min value from column,sum() sum of total from column select count(*) totalrows,avg(price) avgprice ,max(price) maxprice,min(price) minprice,sum(price) pricetotal from itemmasters -- scalar -- ucase() -> convert to upper case ,lcase() -> convert to lower case, -- substring() ->display selected char from column ->substring(columnname,startindex,lenthofchartodisplay) --,len() -> lenth of column date, -- round() -> which will round the value select upper(item_name) uppers,lower(item_name) lowers, substring(item_name,2,3) midvalue,len(item_name) lenths ,substring(item_name,2,len(item_name)) midvaluewithlenfunction, round(price,0) as rounded from itemmasters
4、日期函数
在我们的项目数据表中基本都会使用到日期列，因此日期函数在项目中扮演着非常重要的角色。有时候我们对日期函数要非常的小心，它随时可以给你带来巨大的麻烦。在项目中，我们要选择合适的日期函数和日期格式，下面是一些sql日期函数的例子：
-- getdate() -> to display the current date and time -- format() -> used to display our date in our requested format select getdate() currentdatetime, format(getdate(),'yyyy-mm-dd') as dateformats, format(getdate(),'hh-mm-ss')timeformats, convert(varchar(10),getdate(),10) converts1, convert(varchar(24),getdate(),113), convert(nvarchar, getdate(), 106) converts2 ,-- here we used convert function replace(convert(nvarchar, getdate(), 106), ' ', '/') formats-- here we used replace and --convert functions. --first we convert the date to nvarchar and then we replace the '' with '/' select * from itemmasters select item_name,in_date currentdatetime, format(in_date,'yyyy-mm-dd') as dateformats, format(in_date,'hh-mm-ss')timeformats, convert(varchar(10),in_date,10) converts1, convert(varchar(24),in_date,113), convert(nvarchar, in_date, 106) converts2 ,-- here we used convert function replace(convert(nvarchar,in_date, 106), ' ', '/') formats from itemmasters
datepart –> 该函数可以获取年、月、日的信息。
dateadd –> 该函数可以对当前的日期进行加减。
datediff –> 该函数可以比较2个日期。
--datepart datepart(dateparttype,yourdate) select datepart(yyyy,getdate()) as years , datepart(mm,getdate()) as months, datepart(dd,getdate()) as days, datepart(week,getdate()) as weeks, datepart(hour,getdate()) as hours --days add to add or subdtract date from a selected date. select getdate()currentdate,dateadd(day,12,getdate()) as adddays , dateadd(day,-4,getdate()) as fourdaysbeforedate -- datediff() -> to display the days between 2 dates select datediff(year,'2003-08-05',getdate()) yeardifferance , datediff(day,dateadd(day,-24,getdate()),getdate()) daysdifferent, datediff(month,getdate(),dateadd(month,6,getdate())) monthdifferance
5、其他select函数
top —— 结合select语句，top函数可以查询头几条和末几条的数据记录。
order by —— 结合select语句，order by可以让查询结果按某个字段正序和逆序输出数据记录。
-
-top to select top first and last records using select statement. select * from itemmasters --> first display top 2 records select top 2 item_code ,item_name as item ,price ,description ,in_date from itemmasters --> to display the last to records we need to use the order by clause -- order by to display records in assending or desending order by the columns select top 2 item_code ,item_name as item ,price ,description ,in_date from itemmasters order by item_code desc
distinct —— distinct关键字可以过滤重复的数据记录。
select * from itemmasters --distinct -> to avoid the duplicate records we use the distinct in select statement -- for example in this table we can see here we have the duplicate record 'chiken burger' -- but with different item_code when i use the below select statement see what happen select item_name as item ,price ,description ,in_usr_id from itemmasters -- here we can see the row no 3 and 5 have the duplicate record to avoid this we use the distinct keyword in select statement. select distinct item_name as item ,price ,description ,in_usr_id from itemmasters
6、where子句
where子句在sql select查询语句中非常重要，为什么要使用where子句？什么时候使用where子句？where子句是利用一些条件来过滤数据结果集。
下面我们从10000条数据记录中查询order_no为某个值或者某个区间的数据记录，另外还有其他的条件。
select * from itemmasters select * from orderdetails --where -> to display the data with certain conditions -- now below example which will display all the records which has item_name='coke' select * from itemmasters where item_name='coke' -- if we want display all the records iten_name which starts with 'c' then we use like in where clause. select * from itemmasters where item_name like 'c%' --> here we display the itemmasters where the price will be greater then or equal to 40. --> to use more then one condition we can use and or or operator. --if we want to check the data between to date range then we can use between operator in where clause. select item_name as item ,price ,description ,in_usr_id from itemmasters where item_name like 'c%' and price >=40 --> here we display the orderdetails where the qty will be greater 3 select * from orderdetails where qty>3
where – in 子句
-- in clause -> used to display the data which is in the condition select * from itemmasters where item_name in ('coffee','chiken burger') -- in clause with order by - here we display the in descending order. select * from itemmasters where item_name in ('coffee','chiken burger') order by item_code desc
where – between子句
-- between -> now if we want to display the data between to date range then we use betweeen keyword select * from itemmasters select * from itemmasters where in_date between '2014-09-22 15:59:02.853' and '2014-09-22 15:59:02.853' select * from itemmasters where item_name like 'c%' and in_date between '2014-09-22 15:59:02.853' and '2014-09-22 15:59:02.853'
查询某个条件区间的数据，我们常常使用between子句。
　7、group by 子句
group by子句可以对查询的结果集按指定字段分组：
--group by -> to display the data with group result.here we can see we display all the aqggregate result by item name select item_name,count(*) totalrows,avg(price) avgprice ,max(price) maxprice,min(price) minprice,sum(price) pricetotal from itemmasters group by item_name -- here this group by will combine all the same order_no result and make the total or each order_no select order_no,sum(qty) as totalqty from orderdetails where qty>=2 group by order_no -- here the total will be created by order_no and item_code select order_no,item_code,sum(qty) as totalqty from orderdetails where qty>=2 group by order_no,item_code order by order_no desc,item_code
group by & having 子句
--group by clause -- here this will display all the order_no select order_no,sum(qty) as totalqty from orderdetails group by order_no -- having clause-- this will avoid the the sum(qty) less then 4 select order_no,sum(qty) as totalqty from orderdetails group by order_no having sum(qty) >4
8、子查询
子查询一般出现在where内连接查询和嵌套查询中，select、update和delete语句中均可以使用。
--sub query -- here we used the sub query in where clause to get all the item_code where the price>40 now this sub --query reslut we used in our main query to filter all the records which item_code from subquery result select * from itemmasters where item_code in (select item_code from itemmasters where price > 40) -- sub query with insert statement insert into itemmasters ([item_code] ,[item_name],[price],[tax1],[discount],[description],[in_date] ,[in_usr_id],[up_date] ,[up_usr_id]) select 'item006' ,item_name,price+4,tax1,discount,description ,getdate(),'shanu',getdate(),'shanu' from itemmasters where item_code='item002' --after insert we can see the result as select * from itemmasters
9、连接查询
到目前为止我们接触了不少单表的查询语句，现在我们来使用连接查询获取多个表的数据。
简单的join语句：
--now we have used the simple join with out any condition this will display all the -- records with duplicate data to avaoid this we see our next example with condition select * from ordermasters,orderdetails -- simple join with condition now here we can see the duplicate records now has been avoided by using the where checing with both table primarykey field select * from ordermasters as m, orderdetails as d where m.order_no=d.order_no and m.order_no='ord_001' -- now to make more better understanding we need to select the need fields from both --table insted of displaying all column. select m.order_no,m.table_id,d.order_detail_no,item_code,notes,qty from ordermasters as m, orderdetails as d where m.order_no=d.order_no -- now lets join 3 table select m.order_no,m.table_id,d.order_detail_no,i.item_name,d.notes,d.qty,i.price, i.price*d.qty as totalprice from ordermasters as m, orderdetails as d,itemmasters as i where m.order_no=d.order_no and d.item_code=i.item_code
inner join,left outer join,right outer join and full outer join
下面是各种类型的连接查询代码：
--inner join --this will display the records which in both table satisfy here i have used like in where class which display the select m.order_no,m.table_id,d.order_detail_no,i.item_name,d.notes,d.qty,i.price,i.price*d.qty as totalprice from ordermasters as m inner join orderdetails as d on m.order_no=d.order_no inner join itemmasters as i on d.item_code=i.item_code where m.table_id like 't%' --left outer join --this will display the records which left side table satisfy select m.order_no,m.table_id,d.order_detail_no,i.item_name,d.notes,d.qty,i.price,i.price*d.qty as totalprice from ordermasters as m left outer join orderdetails as d on m.order_no=d.order_no left outer join itemmasters as i on d.item_code=i.item_code where m.table_id like 't%' --right outer join --this will display the records which left side table satisfy select m.order_no,m.table_id,d.order_detail_no,i.item_name,d.notes,d.qty,i.price,i.price*d.qty as totalprice from ordermasters as m right outer join orderdetails as d on m.order_no=d.order_no right outer join itemmasters as i on d.item_code=i.item_code where m.table_id like 't%' --full outer join --this will display the records which left side table satisfy select m.order_no,m.table_id,d.order_detail_no,i.item_name,d.notes,d.qty,i.price,i.price*d.qty as totalprice from ordermasters as m full outer join orderdetails as d on m.order_no=d.order_no full outer join itemmasters as i on d.item_code=i.item_code where m.table_id like 't%'
10、union合并查询
union查询可以把多张表的数据合并起来，union只会把唯一的数据查询出来，而union all则会把重复的数据也查询出来。
select column1,colum2 from table1 union select column1,column2 from table2 select column1,colum2 from table1 union all select column1,column2 from table2
具体的例子如下：
--select with different where condition which display the result as 2 table result select item_code,item_name,price,description from itemmasters where price <=44 select item_code,item_name,price,description from itemmasters where price >44 -- union with same table but with different where condition now which result as one table which combine both the result. select item_code,item_name,price,description from itemmasters where price <=44 union select item_code,item_name,price,description from itemmasters where price >44 -- union all with join sample select m.order_no,m.table_id,d.order_detail_no,i.item_name,d.notes,d.qty,i.price,i.price*d.qty as totalprice from ordermasters as m (nolock) inner join orderdetails as d on m.order_no=d.order_no inner join itemmasters as i on d.item_code=i.item_code wherei.price <=44 union all select m.order_no,m.table_id,d.order_detail_no,i.item_name,d.notes,d.qty,i.price,i.price*d.qty as totalprice from ordermasters as m (nolock) inner join orderdetails as d on m.order_no=d.order_noinner join itemmasters as i on d.item_code=i.item_codewherei.price>44
11、公用表表达式（cte）——with语句
cte可以看作是一个临时的结果集，可以在接下来的一个select,insert,update,delete,merge语句中被多次引用。使用公用表达式可以让语句更加清晰简练。
declare @sdate datetime, @edate datetime; select @sdate = getdate()-5, @edate = getdate()+16; --select @sdate startdate,@edate enddate ;with cte as ( select @sdate startdate,'w'+convert(varchar(2), datepart( wk, @sdate))+'('+convert(varchar(2),@sdate,106)+')' as 'sdt' union all select dateadd(day, 1, startdate) , 'w'+convert(varchar(2),datepart( wk, startdate))+'('+convert(varchar(2), dateadd(day, 1, startdate),106)+')' as 'sdt' from cte where dateadd(day, 1, startdate)<= @edate ) select * from cte option (maxrecursion 0)
12、视图
很多人对视图view感到很沮丧，因为它看起来跟select语句没什么区别。在视图中我们同样可以使用select查询语句，但是视图对我们来说依然非常重要。
假设我们要联合查询4张表中的20几个字段，那么这个select查询语句会非常复杂。但是这样的语句我们在很多地方都需要用到，如果将它编写成视图，那么使用起来会方便很多。利用视图查询有以下几个优点：
一定程度上提高查询速度
可以对一些字段根据不同的权限进行屏蔽，因此提高了安全性
对多表的连接查询会非常方便
下面是一个视图的代码例子：
create view viewname as select columnames from yourtable example : -- here we create view for our union all example create view myunionview as select m.order_no,m.table_id,d.order_detail_no,i.item_name,d.notes,d.qty,i.price, i.price*d.qty as totalprice from ordermasters as m inner join orderdetails as d on m.order_no=d.order_no inner join itemmasters as i on d.item_code=i.item_code wherei.price <=44 union all select m.order_no,m.table_id,d.order_detail_no,i.item_name,d.notes,d.qty,i.price, i.price*d.qty as totalprice from ordermasters as m inner join orderdetails as d on m.order_no=d.order_no inner join itemmasters as i on d.item_code=i.item_codewherei.price>44 -- view select query select * from myunionview -- we can also use the view to display with where condition and with selected fields select order_detail_no,table_id,item_name,price from myunionview where price >40
13、pivot行转列
pivot可以帮助你实现数据行转换成数据列，具体用法如下：
-- simple pivot example select * from itemmasters pivot(sum(price) for item_name in ([chiken burger], coffee,coke)) as pvttable -- pivot with detail example select * from ( select item_name, price as totamount from itemmasters ) as s pivot ( sum(totamount) for [item_name] in ([chiken burger], [coffee],[coke]) )as mypivot
14、存储过程
我经常看到有人提问如何在sql server中编写多条查询的sql语句，然后将它们使用到c#程序中去。存储过程就可以完成这样的功能，存储过程可以将多个sql查询聚集在一起，创建存储过程的基本结构是这样的：
create procedure [procedurename] as begin -- select or update or insert query. end to execute sp we use exec procedurename
创建一个没有参数的存储过程：
-- ============================================= -- author : shanu -- create date : 2014-09-15 -- description : to display pivot data -- latest -- modifier : shanu -- modify date : 2014-09-15 -- ============================================= -- exec usp_selectpivot -- ============================================= create procedure [dbo].[usp_selectpivot] as begin declare @mycolumns as nvarchar(max), @sqlquery as nvarchar(max) -- here first we get all the itemname which should be display in columns we use this in our necxt pivot query select @mycolumns = stuff((select ',' + quotename(item_name) from itemmasters group by item_name order by item_name for xml path(''), type ).value('.', 'nvarchar(max)') ,1,1,'') -- here we use the above all item name to disoplay its price as column and row display set @sqlquery = n'select ' + @mycolumns + n' from ( select item_name, price as totamount from itemmasters ) x pivot ( sum(totamount) for item_name in (' + @mycolumns + n') ) p ' exec sp_executesql @sqlquery; return end
15、函数function
之前我们介绍了max(),sum(), getdate()等最基本的sql函数，现在我们来看看如何创建自定义sql函数。创建函数的格式如下：
create function functionname as begin end
下面是一个简单的函数示例：
-- ============================================= -- author : shanu -- create date : 2014-09-15 -- description : to display pivot data -- latest -- modifier : shanu -- modify date : 2014-09-15 alter function [dbo].[ufnselectitemmaster]() returns int as -- returns total row count of item master. begin declare @rowscount as int; select @rowscount= count(*)+1 from itemmasters return @rowscount; end -- to view function we use select and fucntion name select [dbo].[ufnselectitemmaster]()
下面的一个函数可以实现从给定的日期中得到当前月的最后一天：
-- ============================================= -- author : shanu -- create date : 2014-09-15 -- description : to display pivot data -- latest -- modifier : shanu -- modify date : 2014-09-15 alter function [dbo].[ufn_lastdayofmonth] ( @date nvarchar(10) ) returns nvarchar(10) as begin return convert(nvarchar(10), dateadd(d, -1, dateadd(m, 1, cast(substring(@date,1,7) + '-01' as datetime))), 120) end select dbo.ufn_lastdayofmonth('2014-09-01')as lastday