首页 > 海文新闻 > Java初学者必看的基础SQL查询语句

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

2017年10月12日14:57:08来源:海文国际         92
分享到:

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

Java是一种面向对象的跨平台编程语言,下面石家庄java培训小编整理了Java初学者必看的基础SQL查询语句,希望对大家有帮助!

1、创建表和数据插入SQL

我们在开始创建数据表和向表中插入演示数据之前,我想给大家解释一下实时数据表的设计理念,这样也许能帮助大家能更好的理解SQL查询。

在数据库设计中,有一条非常重要的规则就是要正确建立主键和外键的关系。

现在我们来创建几个餐厅订单管理的数据表,一共用到3张数据表,ItemMaster表、OrderMaster表和OrderDetail表。

创建表:

创建ItemMaster表:

CREATETABLE[dbo].[ItemMasters](

[Item_Code][varchar](20)NOTNULL,

[Item_Name][varchar](100)NOTNULL,

[Price]IntNOTNULL,

[TAX1]IntNOTNULL,

[Discount]IntNOTNULL,

[Description][varchar](200)NOTNULL,

[IN_DATE][datetime]NOTNULL,

[IN_USR_ID][varchar](20)NOTNULL,

[UP_DATE][datetime]NOTNULL,

[UP_USR_ID][varchar](20)NOTNULL,

CONSTRAINT[PK_ItemMasters]PRIMARYKEYCLUSTERED

(

[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]

向ItemMaster表插入数据:

INSERTINTO[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,'Cokewhichneedtobecold',GETDATE(),'SHANU'

,GETDATE(),'SHANU')

INSERTINTO[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,'CoffeMightbeHotorColduserchoice',GETDATE(),'SHANU'

,GETDATE(),'SHANU')

INSERTINTO[ItemMasters]([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]

,[IN_USR_ID],[UP_DATE],[UP_USR_ID])

VALUES

('Item003','ChikenBurger',125,2,5,'Spicy',GETDATE(),'SHANU'

,GETDATE(),'SHANU')

INSERTINTO[ItemMasters]([Item_Code],[Item_Name],[Price],[TAX1],[Discount],[Description],[IN_DATE]

,[IN_USR_ID],[UP_DATE],[UP_USR_ID])

VALUES

('Item004','PotatoFry',15,0,0,'NoComments',GETDATE(),'SHANU'

,GETDATE(),'SHANU')

创建OrderMaster表:

CREATETABLE[dbo].[OrderMasters](

[Order_No][varchar](20)NOTNULL,

[Table_ID][varchar](20)NOTNULL,

[Description][varchar](200)NOTNULL,

[IN_DATE][datetime]NOTNULL,

[IN_USR_ID][varchar](20)NOTNULL,

[UP_DATE][datetime]NOTNULL,

[UP_USR_ID][varchar](20)NOTNULL,

CONSTRAINT[PK_OrderMasters]PRIMARYKEYCLUSTERED

(

[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]

向OrderMaster表插入数据:

INSERTINTO[OrderMasters]

([Order_No],[Table_ID],[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])

VALUES

('Ord_001','T1','',GETDATE(),'SHANU',GETDATE(),'SHANU')

INSERTINTO[OrderMasters]

([Order_No],[Table_ID],[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])

VALUES

('Ord_002','T2','',GETDATE(),'Mak',GETDATE(),'MAK')

INSERTINTO[OrderMasters]

([Order_No],[Table_ID],[Description],[IN_DATE],[IN_USR_ID],[UP_DATE],[UP_USR_ID])

VALUES

('Ord_003','T3','',GETDATE(),'RAJ',GETDATE(),'RAJ')

创建OrderDetail表:

CREATETABLE[dbo].[OrderDetails](

[Order_Detail_No][varchar](20)NOTNULL,

[Order_No][varchar](20)CONSTRAINTfk_OrderMastersFOREIGNKEYREFERENCESOrderMasters(Order_No),

[Item_Code][varchar](20)CONSTRAINTfk_ItemMastersFOREIGNKEYREFERENCESItemMasters(Item_Code),

[Notes][varchar](200)NOTNULL,

[QTY]INTNOTNULL,

[IN_DATE][datetime]NOTNULL,

[IN_USR_ID][varchar](20)NOTNULL,

[UP_DATE][datetime]NOTNULL,

[UP_USR_ID][varchar](20)NOTNULL,

CONSTRAINT[PK_OrderDetails]PRIMARYKEYCLUSTERED

(

[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]

--Nowlet’sthe3itemsfortheaboveOrderNo'Ord_001'.

INSERTINTO[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','NeedveryCold',3

,GETDATE(),'SHANU',GETDATE(),'SHANU')

INSERTINTO[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','veryHot',2

,GETDATE(),'SHANU',GETDATE(),'SHANU')

INSERTINTO[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','VerySpicy',4

,GETDATE(),'SHANU',GETDATE(),'SHANU')

向OrderDetail表插入数据:

INSERTINTO[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','NeedveryHot',2

,GETDATE(),'SHANU',GETDATE(),'SHANU')

INSERTINTO[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','veryHot',2

,GETDATE(),'SHANU',GETDATE(),'SHANU')

INSERTINTO[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','VerySpicy',4

,GETDATE(),'SHANU',GETDATE(),'SHANU')

2、简单的Select查询语句

Select查询语句是SQL中最基本也是最重要的DML语句之一。那么什么是DML?DML全称DataManipulationLanguage(数据操纵语言命令),它可以使用户能够查询数据库以及操作已有数据库中的数据。

下面我们在SQLServer中用select语句来查询我的姓名(Name):

SELECT'MyNameIsSYEDSHANU'

--WithColumnNameusing'AS'

SELECT'MyNameIsSYEDSHANU'as'MYNAME'

--WithmorethentheoneColumn

SELECT'MyName'as'Column1','Is'as'Column2','SYEDSHANU'as'Column3'

3、合计和标量函数

合计函数和标量函数都是SQLServer的内置函数,我们可以在select查询语句中使用它们,比如Count(),Max(),Sum(),Upper(),lower(),Round()等等。下面我们用SQL代码来解释这些函数的用法:

select*fromItemMasters

--Aggregate

--COUNT()->returnstheTotalnoofrecordsfromtable,AVG()returnstheAverageValuefromColum,MAX()ReturnsMaXValuefromColumn

--,MIN()returnsMinValuefromColumn,SUM()sumoftotalfromColumn

SelectCount(*)TotalRows,AVG(Price)AVGPrice

,MAX(Price)MAXPrice,MIN(Price)MinPrice,Sum(price)PriceTotal

FROMItemMasters

--Scalar

--UCASE()->ConverttoUpperCase,LCASE()->ConverttoLowerCase,

--SUBSTRING()->Displayselectedcharfromcolumn->SUBSTRING(ColumnName,StartIndex,LenthofChartoDisplay)

--,LEN()->lenthofcolumndate,

--ROUND()->Whichwillroundthevalue

SELECTUPPER(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)asRounded

FROMItemMasters

4、日期函数

在我们的项目数据表中基本都会使用到日期列,因此日期函数在项目中扮演着非常重要的角色。有时候我们对日期函数要非常的小心,它随时可以给你带来巨大的麻烦。在项目中,我们要选择合适的日期函数和日期格式,下面是一些SQL日期函数的例子:

--GETDATE()->toDisplaytheCurrentDateandTime

--Format()->usedtodisplayourdateinourrequestedformat

SelectGETDATE()CurrentDateTime,FORMAT(GETDATE(),'yyyy-MM-dd')ASDateFormats,

FORMAT(GETDATE(),'HH-mm-ss')TimeFormats,

CONVERT(VARCHAR(10),GETDATE(),10)Converts1,

CONVERT(VARCHAR(24),GETDATE(),113),

CONVERT(NVARCHAR,getdate(),106)Converts2,--hereweusedConvertFunction

REPLACE(convert(NVARCHAR,getdate(),106),'','/')Formats--Hereweusedreplaceand--convertfunctions.

--firstweconvertthedatetonvarcharandthenwereplacethe''with'/'

select*fromItemmasters

SelectITEM_NAME,IN_DATECurrentDateTime,FORMAT(IN_DATE,'yyyy-MM-dd')ASDateFormats,

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,--hereweusedConvertFunction

REPLACE(convert(NVARCHAR,IN_DATE,106),'','/')Formats

FROMItemmasters

DatePart–>该函数可以获取年、月、日的信息。

DateADD–>该函数可以对当前的日期进行加减。

DateDiff–>该函数可以比较2个日期。

5、其他Select函数

Top——结合select语句,Top函数可以查询头几条和末几条的数据记录。

OrderBy——结合select语句,OrderBy可以让查询结果按某个字段正序和逆序输出数据记录。

Distinct——distinct关键字可以过滤重复的数据记录。

6、Where子句

Where子句在SQLSelect查询语句中非常重要,为什么要使用where子句?什么时候使用where子句?where子句是利用一些条件来过滤数据结果集。

下面我们从10000条数据记录中查询Order_No为某个值或者某个区间的数据记录,另外还有其他的条件。

Select*fromItemMasters

Select*fromOrderDetails

--Where->Todisplaythedatawithcertainconditions

--NowbelowexamplewhichwilldisplayalltherecordswhichhasItem_Name='Coke'

select*FROMItemMastersWHEREITEM_NAME='COKE'

--IfwewantdisplayalltherecordsIten_NamewhichStartswith'C'thenweuseLikeinwhereclause.

SELECT*FROMItemMastersWHEREITEM_NAMELike'C%'

-->herewedisplaytheItemMasterswherethepricewillbegreaterthenorequalto40.

-->tousemorethenoneconditionwecanUseAndorOroperator.

--IfwewanttocheckthedatabetweentodaterangethenwecanuseBetweenOperatorinWhereClause.

selectItem_nameasItem

,Price

,Description

,IN_USR_ID

FROMItemMasters

WHERE

ITEM_NAMELike'C%'

AND

price>=40

-->herewedisplaytheOrderDetailswheretheQtywillbegreater3

Select*FROMOrderDetailsWHEREqty>3

Where–In子句

--Inclause->usedtodisplaythedatawhichisinthecondition

select*

FROMItemMasters

WHERE

Item_nameIN('Coffee','ChikenBurger')

--InclausewithOrderBy-Herewedisplaytheindescendingorder.

select*

FROMItemMasters

WHERE

Item_nameIN('Coffee','ChikenBurger')

ORDERBYItem_CodeDesc

Where–Between子句

--between->Nowifwewanttodisplaythedatabetweentodaterangethenweusebetweeenkeyword

select*FROMItemMasters

select*FROMItemMasters

WHERE

In_DateBETWEEN'2014-09-2215:59:02.853'AND'2014-09-2215:59:02.853'

select*FROMItemMasters

WHERE

ITEM_NAMELike'C%'

AND

In_DateBETWEEN'2014-09-2215:59:02.853'AND'2014-09-2215:59:02.853'

查询某个条件区间的数据,我们常常使用between子句。

7、GroupBy子句

GroupBy子句可以对查询的结果集按指定字段分组:

--GroupBy->Todisplaythedatawithgroupresult.HerewecanseewedisplayalltheAQggregateresultbyItemName

SelectITEM_NAME,Count(*)TotalRows,AVG(Price)AVGPrice

,MAX(Price)MAXPrice,MIN(Price)MinPrice,Sum(price)PriceTotal

FROM

ItemMasters

GROUPBYITEM_NAME

--HerethisgroupbywillcombineallthesameOrder_Noresultandmakethetotaloreachorder_NO

SelectOrder_NO,Sum(QTy)asTotalQTY

FROMOrderDetails

whereqty>=2

GROUPBYOrder_NO

--HeretheTotalwillbecreatedbyorder_NoandItem_Code

SelectOrder_NO,Item_Code,Sum(QTy)asTotalQTY

FROMOrderDetails

whereqty>=2

GROUPBYOrder_NO,Item_Code

OrderByOrder_NODesc,Item_Code

GroupBy&Having子句

--GroupByClause--herethiswilldisplayalltheOrder_no

SelectOrder_NO,Sum(QTy)asTotalQTY

FROMOrderDetails

GROUPBYOrder_NO

--HavingClause--Thiswillavoidthethesum(qty)lessthen4

SelectOrder_NO,Sum(QTy)asTotalQTY

FROMOrderDetails

GROUPBYOrder_NO

HAVINGSum(QTy)>4

8、子查询

子查询一般出现在where内连接查询和嵌套查询中,select、update和语句中均可以使用。

9、公用表表达式(CTE)----With语句

CTE可以看作是一个临时的结果集,可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练。


10、视图

很多人对视图View感到很沮丧,因为它看起来跟select语句没什么区别。在视图中我们同样可以使用select查询语句,但是视图对我们来说依然非常重要。

假设我们要联合查询4张表中的20几个字段,那么这个select查询语句会非常复杂。但是这样的语句我们在很多地方都需要用到,如果将它编写成视图,那么使用起来会方便很多。利用视图查询有以下几个优点:

一定程度上提高查询速度

可以对一些字段根据不同的权限进行屏蔽,因此提高了安全性

对多表的连接查询会非常方便