Java初學者必看的基礎SQL查詢語句
Java是一種面向對象的跨平臺編程語言,下面小編整理了Java初學者必看的基礎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 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 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和語句中均可以使用。
--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 we can see the result as
Select * from ItemMasters
9、公用表表達式(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(var2),
DATEPART( wk, @sDate))+'('+convert(var2),@sDate,106)+')' as 'SDT'
union all
select dateadd(DAY, 1, StartDate) ,
'W'+convert(var2),DATEPART( wk, StartDate))+'('+convert(var2),
dateadd(DAY, 1, StartDate),106)+')' as 'SDT'
FROM cte
WHERE dateadd(DAY, 1, StartDate)<= @eDate
)
select * from cte
option (maxrecursion 0)
10、視圖
很多人對視圖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 r_NO,e_ID,r_detail_no,_Name,s,,e,
e* as TotalPrice
FROM
Ordermasters as M Inner JOIN OrderDetails as D
ON r_NO=r_NO INNER JOIN ItemMasters as I
ON _Code=_Code WHERE e <=44
Union ALL
SELECT r_NO,e_ID,r_detail_no,_Name,s,,e,
e* as TotalPrice
FROM
Ordermasters as M Inner JOIN OrderDetails as D
ON r_NO=r_NO INNER JOIN ItemMasters as I
ON _Code=_Code WHERE e>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
相關文章
-
JAVA認證開源技術:關於Java的對象equals方法
在學習JAVA認證過程中,每個人會遇到每個人不同的問題,或小或大,下面yjbys小編爲大家分享的是關於Java的對象equals方法,希望對大家學習JAVA有幫助!不是所有的equals都是比較整個對象的。 #1.普通Object類:先看源碼:publi -
Java初學者必備基礎知識
Java是一門面向對象編程語言,下面小編整理了Java初學者必備基礎知識,希望對大家有幫助! 1先明白瞭解什麼是Java的四個方面初學者先弄清這些Java的基本概念也是必不可少的,死記硬背肯定是不行的,重在理解,理解它們之間的區 -
java的String=a; a==null和a.equals(null)這兩個判斷有什麼區別
1、String 的==與equal()在對字符串的相等判斷,==判斷的是地址是否相同,equal()判斷的是字符值是否相同。大多數時候==跟equal()的結果都是相同的。這是因爲String對象是不變模式的,如果你不是明確地new一個String對象,Ja -
Java初學者必備4大核心基礎知識
很多Java編程初學者在剛接觸Java語言程序的時候,不知道該學習掌握哪些必要的基礎知識。本文達妹總結了零基礎學習Java編程語言的幾個基礎知識要點。希望能夠對剛入門的Java新手有幫助。 Java初學者必備4大核心基礎 -
關於基於Java字符串==與 equals
雖然關於Java字符串“==”和“equals”的問題是Java學習中的最基礎的一個問題,但是仍然有不少剛剛學習Java的同學搞不清楚裏面的原理,最近和朋友討論到這個問題,所以寫篇文章給大家分享一下我自己的理解。首先讓我們看一 -
Java零基礎如何學好Java語言
作爲Java零基礎的人,你知道該如何學好Java語言嗎,下面我們一起來看看吧,歡迎大家閱讀! Java零基礎如何學好Java語言 把Java基礎打牢大學裏學過c語言的同學學習起來Java編程很快,因爲像條件判斷語句ifelseswitch什麼 -
JavaWeb基礎教程之Java基礎加強版參考
1、myeclipse的安裝和使用* eclipse:是一個免費的開發工具* myeclipse:是一個收費的插件,破解myeclipse,** 安裝目錄的要求: 不能有中文和空格** 安裝完成之後,選擇一個工作空間 ,這個工作空間不能有中文和空格* 破解myeclip -
SQL語言查詢基礎:連接查詢和聯合查詢
SQL語言查詢基礎:連接查詢通過連接運算符可以實現多個表查詢。連接是關係數據庫模型的主要特點,也是它區別於其它類型數據庫管理系統的一個標誌。在關係數據庫管理系統中,表建立時各數據之間的關係不必確定,常把一個實 -
JAVA認證基礎知識:Java獲取當前的系統時間
在不斷注重大學聯考能力提高的同時,尤其是JAVA認證備考的後階段,我們選擇的是求準求穩求規範。此複習輔導不等於題海戰術,而是要積累實戰經驗,解決掉一些考場失誤等問題。 一、獲取當前系統時間和日期並格式化輸出:import ;im -
Java中的== 和equals()方法詳解與實例教程
這個教程有2個作品喔〜步驟有點多請慢慢看😊收藏之餘請勿忘點👍!主要材料:不織布羊羔絨珍珠鐵絲玉線小裝飾所需工具:剪刀針線熱膠槍捲尺直尺保麗龍膠製作步驟:第1步:耳罩成品圖~第2步:第3步:耳機成品圖~第4步:第5步:首先做耳罩,量