15个初学者必看的基础SQL查询语句

本文将分享15个初学者必看的基础sql查询语句，都很基础，但是你不一定都会，所以好好看看吧。

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 where i.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_no inner join itemmasters as i
 on  d.item_code=i.item_code where i.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 where i.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_code where i.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

下面是一个简单的函数示例：                                                 

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]()

下面的一个函数可以实现从给定的日期中得到当前月的最后一天：

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

以上就是适合初学者学习的基础sql查询语句，希望对大家学习sql查询语句有所帮助。