mysql的九个常用语句_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')

简单的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_DATEFROMItemMasters

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 ColumnSelect 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 valueSELECT 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 RoundedFROM ItemMasters

4、日期函数

在我们的项目数据表中基本都会使用到日期列，因此日期函数在项目中扮演着非常重要的角色。有时候我们对日期函数要非常的小心，它随时可以给你带来巨大的麻烦。在项目中，我们要选择合适的日期函数和日期格式，下面是一些SQL日期函数的例子：-- GETDATE() -> to Display the Current Date and Time-- Format() -> used to display our date in our requested formatSelect 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 ItemmastersSelect 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), ' ', '/') FormatsFROM 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 RecordsSelect TOP 2 Item_Code ,Item_name as Item ,Price ,Description ,In_DATEFROM 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 columnsSelect TOP 2 Item_Code ,Item_name as Item ,Price ,Description ,In_DATEFROM ItemMastersORDER BY Item_Code DESCSelect * 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 happenSelect Item_name as Item,Price,Description,IN_USR_IDFROM 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 ItemMastersSelect * 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 WHEREITEM_NAME Like 'C%' AND price >=40--> here we display the OrderDetails where the Qty will be greater 3Select * FROM OrderDetails WHERE qty>3

Where – In 子句-- In clause -> used to display the data which is in the conditionselect *FROM ItemMastersWHEREItem_name IN ('Coffee','Chiken Burger')-- In clause with Order By - Here we display the in descending order.select *FROM ItemMastersWHEREItem_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 keywordselect * FROM ItemMastersselect * 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 NameSelect ITEM_NAME,Count(*) TotalRows,AVG(Price) AVGPrice,MAX(Price) MAXPrice,MIN(Price) MinPrice,Sum(price) PriceTotal FROMItemMastersGROUP BY ITEM_NAME-- Here this group by will combine all the same Order_No result and make the total or each order_NOSelect Order_NO,Sum(QTy) as TotalQTY FROM OrderDetailswhere qty>=2GROUP BY Order_NO-- Here the Total will be created by order_No and Item_CodeSelect Order_NO,Item_Code,Sum(QTy) as TotalQTY FROM OrderDetailswhere qty>=2GROUP BY Order_NO,Item_CodeOrder 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 OrderDetailsGROUP BY Order_NO-- Having Clause-- This will avoid the the sum(qty) less then 4Select Order_NO,Sum(QTy) as TotalQTY FROM OrderDetailsGROUP BY Order_NOHAVING 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 resultSELECT * FROM ItemMasters WHERE Item_Code IN (SELECT Item_Code FROM ItemMasters WHERE price > 40) -- Sub Query with Insert StatementINSERT 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