DROP:DROP TABLE test;删除表test，并释放空间，将test删除的一干二净。TRUNCATE:TRUNCATE test;删除表test里的内容，并释放空间，但不删除表的定义，表的结构还在。DELETE:1、删除指定数据删除表test中年龄等于30的且国家为US的数据DELETE FROM test WHERE age=30 AND country='US';2、删除整个表仅删除表test内的所有内容，保留表的定义，不释放空间。 DELETE FROM test 或者 TRUNCATE test; truncate table 命令将快速删除数据表中的所有记录，但保留数据表结构。这种快速删除与 delete from 数据表的删除全部数据表记录不一样， delete 命令删除的数据将存储在系统回滚段中，需要的时候，数据可以回滚恢复，而truncate 命令删除的数据是不可以恢复的。相同点truncate 和不带 where 子句的 delete, 以及 drop 都会删除表内的数据。不同点:1. truncate 和 delete 只删除数据不删除表的结构(定义) ，drop 语句将删除表的结构被依赖的约束(constrain), 触发器(trigger), 索引(index); 依赖于该表的存储过程/函数将保留, 但是变为 invalid 状态。2.delete 语句是 dml, 这个操作会放到 rollback segement 中, 事务提交之后才生效; 如果有相应的 trigger, 执行的时候将被触发。 truncate, drop 是 ddl, 操作立即生效, 原数据不放到 rollback segment 中, 不能回滚。 操作不触发 trigger。3.delete 语句不影响表所占用的 extent, 高水线(high watermark)保持原位置不动。 显然 drop 语句将表所占用的空间全部释放 。 truncate 语句缺省情况下见空间释放到 minextents 个 extent, 除非使用 reuse storage; truncate会将高水线复位(回到最开始)。4.速度：一般来说: drop > truncate > delete 。5.安全性: 小心使用 drop 和 truncate, 尤其没有备份的时候。否则哭都来不及。最后使用上, 想删除部分数据行用 delete, 注意带上 where 子句。 回滚段要足够大。想删除表, 当然用 drop。想保留表而将所有数据删除。如果和事务无关, 用 truncate 即可。 如果和事务有关, 或者想触发 trigger, 还是用 delete。如果是整理表内部的碎片, 可以用 truncate 跟上 reuse stroage, 再重新导入/插入数据。

当我们需要查询包含某个特定字段的数据时，往往会使用 ‘%关键字%' 查询的方式。例如：SELECT ... FROM 表名 WHERE 字段名 LIKE '%关键字%'这应该可以算是一种典型的”包含XXX”的方式，但如果我们需要查询字段被包含于特定字符的数据时呢？比如，我有一张联系人数据表ConnectName，其中有个字段用于记录姓名name。我想获取名为小兰和灰原的人的联系资料。正常情况下，我们首先能想到的做法应该是：SELECT * FROM ConnectName  WHERE   name = '小兰'   OR name = '灰原'这样的做法是可以实现这种目的的。如果这时候，我突然想，再查一个人，比如说“柯南”，那么我们就要修改SQL的结构，添加一个Where条件句：SELECT * FROM ConnectName  WHERE   name = '小兰'   OR name = '灰原'   OR name = '柯南'我们知道，OR条件查询本身是属于效率较低的，而且结构变动的语句在MyBatis实现稍微麻烦些（当然也是可以实现的，遍历插入字段就行了）。能不能简单一些呢？我可以把所有关键字放在一起，只用一个Where条件去实现吗？CHARINDEX登场这时候，我们就可以用 CHARINDEX 关键字了，CHARINDEX可以返回某个字段在一串文字中出现的位置，跟String的indexOf用法类似，不多废话，我们来举个栗子：CHARINDEX('李白','曹操很帅') =0在上面的栗子中，因为曹操很帅不包含李白关键字，所以找不到，返回0.SELECT * FROM ConnectName  WHERE   <!--[CDATA[ AND CHARINDEX(name ,#{传入的参数}) --> 0 ]]>如果后期我们想要加入一个新的人，比如毛利小五郎，只需要在传入的参数中加入 ‘小兰灰原柯南毛利小五郎' 就可以了。

一些在学习数据库收集的入门基础语句还不错分享一波（显示文章文字太多，只能分俩个帖子（开心））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 -- 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 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 4411、公用表表达式（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 -- 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 >4013、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 MyPivot14、存储过程我经常看到有人提问如何在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                          END15、函数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

一些在学习数据库收集的入门基础语句还不错分享一波（显示文章文字太多，只能分俩个帖子（开心））1、创建表和数据插入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 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&rsquo;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  ItemMasters3、合计和标量函数合计函数和标量函数都是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 ItemMasters4、日期函数在我们的项目数据表中基本都会使用到日期列，因此日期函数在项目中扮演着非常重要的角色。有时候我们对日期函数要非常的小心，它随时可以给你带来巨大的麻烦。在项目中，我们要选择合适的日期函数和日期格式，下面是一些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 ItemmastersDatePart –>  该函数可以获取年、月、日的信息。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())) MonthDifferance5、其他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 DESCDistinct —— 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 ItemMasters6、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>3Where – 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 DescWhere – 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_CodeGroup 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) >48、子查询子查询一般出现在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 ItemMasters9、连接查询到目前为止我们接触了不少单表的查询语句，现在我们来使用连接查询获取多个表的数据。简单的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_CodeInner 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%'

   技巧　　1、1=1，1=2的使用，在SQL语句组合时用的较多　　“where 1=1” 是表示选择全部 “where 1=2”全部不选，　　如：　　if @strWhere !='' 　　begin 　　set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere 　　end 　　else 　　begin 　　set @strSQL = 'select count(*) as Total from [' + @tblName + ']' 　　end 　　我们可以直接写成 　　set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收缩数据库　--重建索引       DBCC REINDEX　　DBCC INDEXDEFRAG　　--收缩数据和日志　　DBCC SHRINKDB　　DBCC SHRINKFILE3、压缩数据库dbcc shrinkdatabase(dbname)4、转移数据库给新用户以已存在用户权限　exec sp_change_users_login 'update_one','newname','oldname' 　　go5、检查备份集RESTORE VERIFYONLY from disk='E:dvbbs.bak'6、修复数据库ALTER DATABASE [dvbbs] SET SINGLE_USER 　　GO 　　DBCC CHECKDB('dvbbs',repair_allow_data_loss) WITH TABLOCK 　　GO 　　ALTER DATABASE [dvbbs] SET MULTI_USER 　　GO7、日志清除SET NOCOUNT ON 　　DECLARE @LogicalFileName sysname, 　　@MaxMinutes INT, 　　@NewSize INTUSE tablename -- 要操作的数据库名 　　SELECT @LogicalFileName = 'tablename_log', -- 日志文件名 　　@MaxMinutes = 10, -- Limit on time allowed to wrap log. 　　@NewSize = 1 -- 你想设定的日志文件的大小(M) 　　Setup / initialize 　　DECLARE @OriginalSize int 　　SELECT @OriginalSize = size 　　FROM sysfiles 　　WHERE name = @LogicalFileName 　　SELECT 'Original Size of ' + db_name() + ' LOG is ' + 　　CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' + 　　CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB' 　　FROM sysfiles 　　WHERE name = @LogicalFileName 　　CREATE TABLE DummyTrans 　　(DummyColumn char (8000) not null) 　　 　　DECLARE @Counter INT, 　　@StartTime DATETIME, 　　@TruncLog VARCHAR(255) 　　SELECT @StartTime = GETDATE(), 　　@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY' 　　DBCC SHRINKFILE (@LogicalFileName, @NewSize) 　　EXEC (@TruncLog) 　　-- Wrap the log if necessary. 　　WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired 　　AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName) 　　AND (@OriginalSize * 8 /1024) > @NewSize 　　BEGIN -- Outer loop. 　　SELECT @Counter = 0 　　WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000)) 　　BEGIN -- update 　　INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans 　　SELECT @Counter = @Counter + 1 　　END 　　EXEC (@TruncLog) 　　END 　　SELECT 'Final Size of ' + db_name() + ' LOG is ' + 　　CONVERT(VARCHAR(30),size) + ' 8K pages or ' + 　　CONVERT(VARCHAR(30),(size*8/1024)) + 'MB' 　　FROM sysfiles 　　WHERE name = @LogicalFileName 　　DROP TABLE DummyTrans 　　SET NOCOUNT OFF8、说明：更改某个表exec sp_changeobjectowner 'tablename','dbo'9、存储更改全部表CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch 　　@OldOwner as NVARCHAR(128), 　　@NewOwner as NVARCHAR(128) 　　AS 　　DECLARE @Name as NVARCHAR(128) 　　DECLARE @Owner as NVARCHAR(128) 　　DECLARE @OwnerName as NVARCHAR(128) 　　DECLARE curObject CURSOR FOR 　　select 'Name' = name, 　　 'Owner' = user_name(uid) 　　from sysobjects 　　where user_name(uid)=@OldOwner 　　order by name 　　OPEN curObject 　　FETCH NEXT FROM curObject INTO @Name, @Owner 　　WHILE(@@FETCH_STATUS=0) 　　BEGIN 　　if @Owner=@OldOwner 　　begin 　　 set @OwnerName = @OldOwner + '.' + rtrim(@Name) 　　 exec sp_changeobjectowner @OwnerName, @NewOwner 　　end 　　-- select @name,@NewOwner,@OldOwner 　　FETCH NEXT FROM curObject INTO @Name, @Owner 　　END 　　close curObject 　　deallocate curObject 　　GO10、SQL SERVER中直接循环写入数据declare @i int 　　set @i=1 　　while @i<30 　　begin 　　 insert into test (userid) values(@i) 　　 set @i=@i+1 　　end案例：有如下表，要求就裱中所有沒有及格的成績，在每次增長0.1的基礎上，使他們剛好及格:Name score 　　 Zhangshan 80 　　 Lishi 59 　　 Wangwu 50 　　 Songquan 69 　　while((select min(score) from tb_table)<60) 　　begin 　　update tb_table set score =score*1.01 　　where score<60 　　if (select min(score) from tb_table)>60 　　 break 　　else 　　 continue 　　end1.按姓氏笔画排序:　Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多2.数据库加密:select encrypt('原始密码') 　　select pwdencrypt('原始密码') 　　select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同 encrypt('原始密码') 　　select pwdencrypt('原始密码') 　　select pwdcompare('原始密码','加密后密码') = 1--相同；否则不相同3.取回表中字段:declare @list varchar(1000), 　　@sql nvarchar(1000) 　　select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A' 　　set @sql='select '+right(@list,len(@list)-1)+' from 表A' 　　exec (@sql)4.查看硬盘分区:EXEC master..xp_fixeddrives5.比较A,B表是否相等:　if (select checksum_agg(binary_checksum(*)) from A) 　　 = 　　 (select checksum_agg(binary_checksum(*)) from B) 　　 　print '相等' 　　else 　　print '不相等'6.杀掉所有的事件探察器进程:DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid) FROM master.dbo.sysprocesses 　　WHERE program_name IN('SQL profiler',N'SQL 事件探查器') 　　EXEC sp_msforeach_worker '?'7.记录搜索:Select Top N * From 表 　　------------------------------- 　　N到M条记录(要有主索引ID) 　　Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc 　　---------------------------------- 　　N到结尾记录 　　Select Top N * From 表 Order by ID Desc案例例如1：一张表有一万多条记录，表的第一个字段 RecID 是自增长字段， 写一个SQL语句， 找出表的第31到第40个记录。　　select top 10 recid from A where recid not in(select top 30 recid from A)　　分析：如果这样写会产生某些问题，如果recid在表中存在逻辑索引。　　 select top 10 recid from A where……是从索引中查找，而后面的select top 30 recid from A则在数据表中查找，这样由于索引中的顺序有可能和数据表中的不一致，这样就导致查询到的不是本来的欲得到的数据。解决方案       1， 用order by select top 30 recid from A order by ricid 如果该字段不是自增长，就会出现问题　　2， 在那个子查询中也加条件：select top 30 recid from A where recid>-1　　例2：查询表中的最后以条记录，并不知道这个表共有多少数据,以及表结构。　　set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)'　　print @s exec sp_executesql @s　8：获取当前数据库中的所有用户表　select Name from sysobjects where xtype='u' and status>=0　9：获取某一个表的所有字段select name from syscolumns where id=object_id('表名') 　　select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名') 　　两种方式的效果相同10：查看与某一个表相关的视图、存储过程、函数　select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'11：查看当前数据库中所有存储过程select name as 存储过程名称 from sysobjects where xtype='P'12：查询用户创建的所有数据库select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa') 　　或者 　　select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x0113：查询某一个表的字段和数据类型　select column_name,data_type from information_schema.columns 　　where table_name = '表名'14：不同服务器数据库之间的数据操作--创建链接服务器 　　exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 　　exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 ' 　　--查询示例 　　select * from ITSV.数据库名.dbo.表名--导入示例 　　select * into 表 from ITSV.数据库名.dbo.表名 　　--以后不再使用时删除链接服务器 　　exec sp_dropserver 'ITSV ', 'droplogins ' 　　--连接远程/局域网数据(openrowset/openquery/opendatasource) 　　--1、openrowset 　　--查询示例 　　select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 　　--生成本地表 　　select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 　　--把本地表导入远程表 　　insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名) 　　select *from 本地表 　　--更新本地表 　　update b 　　set b.列A=a.列A 　　from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b 　　on a.column1=b.column1 　　--openquery用法需要创建一个连接 　　--首先创建一个连接创建链接服务器 　　exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 ' 　　--查询 　　select * 　　FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') 　　--把本地表导入远程表 　　insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') 　　select * from 本地表 　　--更新本地表 　　update b 　　set b.列B=a.列B 　　FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a 　　inner join 本地表 b on a.列A=b.列A 　　--3、opendatasource/openrowset 　　SELECT * 　　FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta 　　--把本地表导入远程表 　　insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名 　　select * from 本地表SQL Server基本函数　　1.字符串函数 长度与分析用　　1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格　　2,substring(expression,start,length) 取子串，字符串的下标是从“1”，start为起始位置，length为字符串长度，实际应用中以len(expression)取得其长度　　3,right(char_expr,int_expr) 返回字符串右边第int_expr个字符，还用left于之相反　　4,isnull( check_expression , replacement_value )如果check_expression為空，則返回replacement_value的值，不為空，就返回check_expression字符操作类　　5,Sp_addtype 自定義數據類型　　例如：EXEC sp_addtype birthday, datetime, 'NULL'　　6,set nocount {on|off}使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据，则该设置由于大量减少了网络流量，因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置，而不是在分析时设置。SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。　　SET NOCOUNT 为 OFF 时，返回计数

  SQL分类　   DDL—数据定义语言(CREATE，ALTER，DROP，DECLARE)　　DML—数据操纵语言(SELECT，DELETE，UPDATE，INSERT)　　DCL—数据控制语言(GRANT，REVOKE，COMMIT，ROLLBACK)一、基础1、说明：创建数据库CREATE DATABASE database-name2、说明：删除数据库drop database dbname3、说明：备份sql server  --- 创建 备份数据的 device  USE master  EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'  --- 开始 备份  BACKUP DATABASE pubs TO testBack4、说明：创建新表  create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)根据已有的表创建新表：A：create table tab_new like tab_old (使用旧表创建新表)B：create table tab_new as select col1,col2… from tab_old definition only5、说明：删除新表  drop table tabname6、说明：增加一个列  Alter table tabname add column col type注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。7、说明：添加主键：  Alter table tabname add primary key(col) 说明：删除主键：  Alter table tabname drop primary key(col) 8、说明：创建索引：  create [unique] index idxname on tabname(col….) 删除索引：  drop index idxname 注：索引是不可更改的，想更改必须删除重新建。9、说明：创建视图：      create view viewname as select statement  删除视图：  drop view viewname  [code]  10、说明：几个简单的基本的sql语句  [code]  选择：select * from table1 where 范围  插入：insert into table1(field1,field2) values(value1,value2)  删除：delete from table1 where 范围  更新：update table1 set field1=value1 where 范围  查找：select * from table1 where field1 like '%value1%' ---like的语法很精妙，查资料!  排序：select * from table1 order by field1,field2 [desc]  总数：select count as totalcount from table1  求和：select sum(field1) as sumvalue from table1  平均：select avg(field1) as avgvalue from table1  最大：select max(field1) as maxvalue from table1  最小：select min(field1) as minvalue from table111、说明：几个高级查询运算词A： UNION 运算符　　UNION 运算符通过组合其他两个结果表（例如 TABLE1 和 TABLE2）并消去表中任何重复行而派生出一个结果表。当 ALL 随 UNION 一起使用时（即 UNION ALL），不消除重复行。两种情况下，派生表的每一行不是来自 TABLE1 就是来自 TABLE2。　　B： EXCEPT 运算符　　EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。　　C： INTERSECT 运算符　　INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。　　注：使用运算词的几个查询结果行必须是一致的。12、说明：使用外连接　　A、left （outer） join：　　左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。　SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c 　　B：right （outer） join:　　右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。　　C：full/cross （outer） join：　　全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。13、分组:Group by: 一张表，一旦分组 完成后，查询后只能得到组相关的信息。　　 组相关的信息：（统计信息） count,sum,max,min,avg 分组的标准)　　 在SQLServer中分组时：不能以text,ntext,image类型的字段作为分组依据　　 在selecte统计函数中的字段，不能和普通的字段放在一起；14、对数据库进行操作：　分离数据库： sp_detach_db; 附加数据库：sp_attach_db 后接表明，附加需要完整的路径名15.如何修改数据库的名称:　sp_renamedb 'old_name', 'new_name'二、提升       1、说明：复制表(只复制结构,源表名：a 新表名：b) (Access可用)    法一：select * into b from a where 1<>1（仅用于SQlServer）       法二：select top 0 * into b from a　　2、说明：拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)　　insert into b(a, b, c) select d,e,f from b;　　3、说明：跨数据库之间表的拷贝(具体数据使用绝对路径) (Access可用)　　insert into b(a, b, c) select d,e,f from b in ‘具体数据库' where 条件 　　例子：..from b in '"&Server.MapPath(".")&"data.mdb" &"' where..　　4、说明：子查询(表名1：a 表名2：b)　　select a,b,c from a where a IN (select d from b ) 或者: select a,b,c from a where a IN (1,2,3)　　5、说明：显示文章、提交人和最后回复时间　　select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b　　6、说明：外连接查询(表名1：a 表名2：b)　select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c　　7、说明：在线视图查询(表名1：a )　select * from (SELECT a,b,c FROM a) T where t.a > 1;　　8、说明：between的用法,between限制查询数据范围时包括了边界值,not between不包括　　select * from table1 where time between time1 and time2 　　select a,b,c, from table1 where a not between 数值1 and 数值2　　9、说明：in 的使用方法　select * from table1 where a [not] in (‘值1','值2','值4','值6')　　10、说明：两张关联表，删除主表中已经在副表中没有的信息　　delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1 )　　11、说明：四表联查问题：　select * from a left inner join b on a.a=b.b right inner join c on a.a=c.c inner join d on a.a=d.d where .....　　12、说明：日程安排提前五分钟提醒　SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5　　13、说明：一条sql 语句搞定数据库分页　　select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段具体实现：　　关于数据库分页：declare @start int,@end int 　　@sql nvarchar(600) 　　set @sql='select top'+str(@end-@start+1)+'+from T where rid not in(select top'+str(@str-1)+'Rid from T where Rid>-1)' 　　exec sp_executesql @sql注意：在top后不能直接跟一个变量，所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列，如 果top后还有具体的字段，这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的，查询的结果后实际表中的不一致（逻辑索引中的数据有可能和数据表中的不一致，而查询时如果处在索引则首先查询索引）14、说明：前10条记录select top 10 * form table1 where 范围15、说明：选择在每一组b值相同的数据中对应的a最大的记录的所有信息(类似这样的用法可以用于论坛每月排行榜,每月热销产品分析,按科目成绩排名,等等.)　select a,b,c from tablename ta where a=(select max(a) from tablename tb where tb.b=ta.b)16、说明：包括所有在 TableA 中但不在 TableB和TableC 中的行并消除所有重复行而派生出一个结果表(select a from tableA ) except (select a from tableB) except (select a from tableC)17、说明：随机取出10条数据select top 10 * from tablename order by newid()18、说明：随机选择记录select newid()19、说明：删除重复记录1),delete from tablename where id not in (select max(id) from tablename group by col1,col2,...) 　　2),select distinct * into temp from tablename 　　delete from tablename 　　insert into tablename select * from temp评价： 这种操作牵连大量的数据的移动，这种做法不适合大容量但数据操作3),例如：在一个外部表中导入数据，由于某些原因第一次只导入了一部分，但很难判断具体位置，这样只有在下一次全部导入，这样也就产生好多重复的字段，怎样删除重复字段alter table tablename--添加一个自增列add column_b int identity(1,1) 　　delete from tablename where column_b not in( 　　select max(column_b) from tablename group by column1,column2,...) 　　alter table tablename drop column column_b20、说明：列出数据库里所有的表名　select name from sysobjects where type='U' // U代表用户21、说明：列出表里的所有的列名select name from syscolumns where id=object_id('TableName')22、说明：列示type、vender、pcs字段，以type字段排列，case可以方便地实现多重选择，类似select 中的case。　select type,sum(case vender when 'A' then pcs else 0 end),sum(case vender when 'C' then pcs else 0 end),sum(case vender when 'B' then pcs else 0 end) FROM tablename group by type　显示结果：　　type vender pcs　　电脑 A 1　　电脑 A 1　　光盘 B 2　　光盘 A 2　　手机 B 3　　手机 C 323、说明：初始化表table1TRUNCATE TABLE table124、说明：选择从10到15的记录select top 5 * from (select top 15 * from table order by id asc) table_别名 order by id desc

索引可以提高查询的速度，但并不是使用带有索引的字段查询时，索引都会起作用。使用索引有几种特殊情况，在这些情况下，有可能使用带有索引的字段查询时，索引并没有起作用，下面重点介绍这几种特殊情况。1. 查询语句中使用LIKE关键字在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。例 1为了便于理解，我们先查询 tb_student 表中的数据，SQL 语句和运行结果如下：mysql> SELECT * FROM tb_student; +----+------+------+------+ | id | name | age  | sex  | +----+------+------+------+ |  1 | 张三 |   12 | 男   | |  2 | 李四 |   12 | 男   | |  3 | 王五 |   13 | 女   | |  4 | 张四 |   13 | 女   | |  5 | 王四 |   15 | 男   | |  6 | 赵六 |   12 | 女   | +----+------+------+------+ 6 rows in set (0.03 sec)下面在查询语句中使用 LIKE 关键字，且匹配的字符串中含有“%”符号，使用 EXPLAIN 分析查询情况，SQL 语句和运行结果如下：mysql>  EXPLAIN SELECT * FROM tb_student WHERE name LIKE '%四'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 6      filtered: 16.67         Extra: Using where 1 row in set, 1 warning (0.01 sec) mysql> CREATE INDEX index_name ON tb_student(name); Query OK, 6 rows affected (0.13 sec) mysql>  EXPLAIN SELECT * FROM tb_student WHERE name LIKE '李%'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: range possible_keys: index_name           key: index_name       key_len: 77           ref: NULL          rows: 1      filtered: 100.00         Extra: Using index condition 1 row in set, 1 warning (0.00 sec)第一个查询语句执行后，rows 参数的值为 6，表示这次查询过程中查询了 6 条记录；第二个查询语句执行后，rows 参数的值为 1，表示这次查询过程只查询 1 条记录。同样是使用 name 字段进行查询，因为第一个查询语句的 LIKE 关键字后的字符串是以“%”开头的，所以第一个查询语句没有使用索引，而第二个查询语句使用了索引 index_name。2. 查询语句中使用多列索引多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。例 2在 name 和 age 两个字段上创建多列索引，并验证多列索引的使用情况，SQL 语句和运行结果如下：mysql> CREATE INDEX index_name_age ON tb_student(name,age); Query OK, 6 rows affected (0.11 sec) mysql> EXPLAIN SELECT * FROM tb_student WHERE name LIKE '李%'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: range possible_keys: index_name_age           key: index_name_age       key_len: 77           ref: NULL          rows: 1      filtered: 100.00         Extra: Using index condition 1 row in set, 1 warning (0.05 sec) mysql> EXPLAIN SELECT * FROM tb_student WHERE age LIKE '12'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: ALL possible_keys: NULL           key: NULL       key_len: NULL           ref: NULL          rows: 6      filtered: 16.67         Extra: Using where 1 row in set, 1 warning (0.00 sec)第一条查询语句的查询条件使用了 name 字段，分析结果显示 rows 参数的值为 1，且查询过程中使用了 index_name_age 索引。第二条查询语句的查询条件使用了 age 字段，结果显示 rows 参数的值为 6，且 key 参数的值为 NULL，这说明第二个查询语句没有使用索引。因为 name 字段是多列索引的第一个字段，所以只有查询条件中使用了 name 字段才会使 index_name_age 索引起作用。3. 查询语句中使用OR关键字查询语句只有 OR 关键字时，如果 OR 前后的两个条件的列都是索引，那么查询中将使用索引。如果 OR 前后有一个条件的列不是索引，那么查询中将不使用索引。例 3下面演示 OR 关键字的使用。mysql> EXPLAIN SELECT * FROM tb_student WHERE name='张三' or sex='男'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: ALL possible_keys: index_name,index_name_age           key: NULL       key_len: NULL           ref: NULL          rows: 6      filtered: 30.56         Extra: Using where 1 row in set, 1 warning (0.06 sec) mysql> EXPLAIN SELECT * FROM tb_student WHERE name='张三' or id='12'\G *************************** 1. row ***************************            id: 1   select_type: SIMPLE         table: tb_student    partitions: NULL          type: index_merge possible_keys: PRIMARY,index_name,index_name_age           key: index_name,PRIMARY       key_len: 77,4           ref: NULL          rows: 2      filtered: 100.00         Extra: Using union(index_name,PRIMARY); Using where 1 row in set, 1 warning (0.01 sec)由于 sex 字段没有索引，所以第一条查询语句没有使用索引；name 字段和 id 字段都有索引，所以第二条查询语句使用了 index_name 和 PRIMARY 索引 。总结使用索引查询记录时，一定要注意索引的使用情况。例如，LIKE 关键字配置的字符串不能以“%”开头；使用多列索引时，查询条件必须要使用这个索引的第一个字段；使用 OR 关键字时，OR 关键字连接的所有条件都必须使用索引。

MySQL Select语句的执行细节/、往下瞧上图：            先，我们可以看到，整个select语句包含三个模块，其中客户端和MySQL两个部分，MySQL又包含server端和存储引擎侧，server端包含连接器、查询缓存、分析器、优化器，执行器等多个部分，存储引擎侧同样包含innodb、Myisam，memory等多种存储引擎。每个模块的作用如下：连接器：连接器的任务是跟客户端建立连接，查询权限，维持和管理连接等等。当我们使用命令行登录时，如果密码或者账号错误，那么连接器会返回给我们Access Deny的报错，利用正确的账号密码登录到MySQL之后，连接器会查询当前账号的登录权限，之后的所有操作，都是依赖这个权限进行的。如果客户端连接进入MySQL后，一直没有进行任何操作，那么连接器将会在wait_timeout参数设置的时间后，将该连接断开。建议在程序中使用长连接，这样可以减小和客户端和MySQL建立连接的带来的性能损耗。查询缓存：当客户端输入一个SQL之后，如果命中查询缓存，那么MySQL将会直接返回结果，而不进行下面的一系列分析操作，如果没有命中，则开始进入分析器---优化器---执行器这个流程。关于查询缓存，还有2点需要注意：a、MySQL8.0中开始弃用查询缓存，因为查询缓存的命中率一般较低，弊大于利。b、在高并发场景中，建议将查询缓存关闭，关闭时使用query_cache_type=off或者query_cache_size=0。c、如果非要使用这个功能，可以将参数query_cache_type设置成DEMAND，这样，就可以使用select sql_cache  * from table这种方式强制使用查询缓存的功能了。分析器：如果我们的查询没有真正命中查询缓存，那么就要进入分析器这个环节了，分析器的主要功能是词法分析和语法分析，其中：词法分析指的是根据SQL分析出来select、update、alter、列名、表名、库名等；语法分析是指需要分析你写的SQL是否满足MySQL的语法。如果不满足，则会给出you have an error xxx的提示。优化器：优化器的作用是选出SQL执行的最佳路径，例如在多表join的时候，优化器会选择最高效的方案来执行连接查询。这里可以给出一个例子，例如我们建立了(a,b)的联合索引，select * from table where a=1 and b=1；select * from table where b=1 and a=1；这两条SQL是等价的，因为优化器会将下面的改造成上面的样子，从而应用联合索引进行搜索查询。 执行器：SQL经过优化器之后，就会进入执行器，执行SQL前，需要校验表的权限，如果有权限，会根据表的存储引擎定义，去使用这个存储引擎提供的接口，最终将数据返回给客户端，并开始等待新的连接。这里需要注意一点：执行器调用一次，在引擎内部则扫描了多行，因此存储引擎扫描行数跟rows_examined并不是完全相同的。

在MySQL中你可以使用SELECT...INTO OUTFILE语句来简单的导出数据到文本文件上。使用 SELECT ... INTO OUTFILE 语句导出数据以下实例中我们将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中:mysql> SELECT * FROM runoob_tbl      -> INTO OUTFILE '/tmp/runoob.txt';你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'     -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'     -> LINES TERMINATED BY '\r\n';在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。SELECT a,b,a+b INTO OUTFILE '/tmp/result.text' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;SELECT ... INTO OUTFILE 语句有以下属性:LOAD DATA INFILE是SELECT ... INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT ... INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。SELECT...INTO OUTFILE 'file_name'形式的SELECT可以把被选择的行写入一个文件中。该文件被创建到服务器主机上，因此您必须拥有FILE权限，才能使用此语法。输出不能是一个已存在的文件。防止文件数据被篡改。你需要有一个登陆服务器的账号来检索文件。否则 SELECT ... INTO OUTFILE 不会起任何作用。在UNIX中，该文件被创建后是可读的，权限由MySQL服务器所拥有。这意味着，虽然你就可以读取该文件，但可能无法将其删除。导出表作为原始数据mysqldump 是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。使用 mysqldump 导出数据需要使用 --tab 选项来指定导出文件指定的目录，该目标必须是可写的。以下实例将数据表 runoob_tbl 导出到 /tmp 目录中：$ mysqldump -u root -p --no-create-info \             --tab=/tmp RUNOOB runoob_tbl password ******导出 SQL 格式的数据导出 SQL 格式的数据到指定文件，如下所示：$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt password ******以上命令创建的文件内容如下：-- MySQL dump 8.23 -- -- Host: localhost    Database: RUNOOB --------------------------------------------------------- -- Server version       3.23.58 -- -- Table structure for table `runoob_tbl` -- CREATE TABLE runoob_tbl (   runoob_id int(11) NOT NULL auto_increment,   runoob_title varchar(100) NOT NULL default '',   runoob_author varchar(40) NOT NULL default '',   submission_date date default NULL,   PRIMARY KEY  (runoob_id),   UNIQUE KEY AUTHOR_INDEX (runoob_author) ) TYPE=MyISAM; -- -- Dumping data for table `runoob_tbl` -- INSERT INTO runoob_tbl         VALUES (1,'Learn PHP','John Poul','2007-05-24'); INSERT INTO runoob_tbl         VALUES (2,'Learn MySQL','Abdul S','2007-05-24'); INSERT INTO runoob_tbl         VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');如果你需要导出整个数据库的数据，可以使用以下命令：$ mysqldump -u root -p RUNOOB > database_dump.txt password ******如果需要备份所有数据库，可以使用以下命令：$ mysqldump -u root -p --all-databases > database_dump.txt password ******--all-databases 选项在 MySQL 3.23.12 及以后版本加入。该方法可用于实现数据库的备份策略。将数据表及数据库拷贝至其他主机如果你需要将数据拷贝至其他的 MySQL 服务器上, 你可以在 mysqldump 命令中指定数据库名及数据表。在源主机上执行以下命令，将数据备份至 dump.txt 文件中:$ mysqldump -u root -p database_name table_name > dump.txt password *****如果完整备份数据库，则无需使用特定的表名称。如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：$ mysql -u root -p database_name < dump.txt password *****你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：$ mysqldump -u root -p database_name \        | mysql -h other-host.com database_name以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

前言每一个好习惯都是一笔财富，本文分SQL后悔药， SQL性能优化，SQL规范优雅三个方向，分享写SQL的21个好习惯，谢谢阅读，加油哈~1. 写完SQL先explain查看执行计划（SQL性能优化）日常开发写SQL的时候，尽量养成这个好习惯呀：写完SQL后，用explain分析一下，尤其注意走不走索引。explain select * from user where userid =10086 or age =18;2、操作delete或者update语句，加个limit(SQL后悔药）在执行删除或者更新语句，尽量加上limit，以下面的这条 SQL 为例吧：delete from euser where age > 30 limit 200;因为加了limit 主要有这些好处：SQL效率很可能更高，你在SQL行中，加了limit 1，如果第一条就命中目标return， 没有limit的话，还会继续执行扫描表。数据量大的话，容易把CPU打满 ,如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢的。3. 设计表的时候，所有表和字段都添加相应的注释（SQL规范优雅）这个好习惯一定要养成啦，设计数据库表的时候，所有表和字段都添加相应的注释，后面更容易维护。正例：CREATE TABLE `account` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',  `name` varchar(255) DEFAULT NULL COMMENT '账户名',  `balance` int(11) DEFAULT NULL COMMENT '余额',  `create_time` datetime NOT NULL COMMENT '创建时间',  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  PRIMARY KEY (`id`),  KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';反例：CREATE TABLE `account` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `balance` int(11) DEFAULT NULL,  `create_time` datetime NOT NULL ,  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,  PRIMARY KEY (`id`),  KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8;4. SQL书写格式，关键字大小保持一致，使用缩进。（SQL规范优雅）正例：SELECT stu.name, sum(stu.score)FROM Student stuWHERE stu.classNo = '1班'GROUP BY stu.name反例：SELECT stu.name, sum(stu.score) from Student stu WHERE stu.classNo = '1班' group by stu.name.显然，统一关键字大小写一致，使用缩进对齐，会使你的SQL看起来更优雅~5. INSERT语句标明对应的字段名称（SQL规范优雅）反例：insert into Student values ('666','捡田螺的小男孩','100');正例：insert into Student(student_id,name,score) values ('666','捡田螺的小男孩','100');6. 变更SQL操作先在测试环境执行，写明详细的操作步骤以及回滚方案，并在上生产前review。（SQL后悔药）变更Sql操作需要写明详细操作步骤，尤其有依赖关系的时候，如：先修改表结构再补充对应的数据。主键一定要加上的，没有主键的表是没有灵魂的因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较，最后导致索引失效7.设计数据库表的时候，加上三个字段：主键，createtime,updatetime。（SQL规范优雅）反例：CREATE TABLE `account` (  `name` varchar(255) DEFAULT NULL COMMENT '账户名',  `balance` int(11) DEFAULT NULL COMMENT '余额',) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';正例：CREATE TABLE `account` (  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',  `name` varchar(255) DEFAULT NULL COMMENT '账户名',  `balance` int(11) DEFAULT NULL COMMENT '余额',  `create_time` datetime NOT NULL COMMENT '创建时间',  `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',  PRIMARY KEY (`id`),  KEY `idx_name` (`name`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';理由：主键一定要加上的，没有主键的表是没有灵魂的创建时间和更新时间的话，还是建议加上吧，详细审计、跟踪记录，都是有用的。阿里开发手册也提到这个点，如图8. 写完SQL语句，检查where,order by,group by后面的列，多表关联的列是否已加索引，优先考虑组合索引。（SQL性能优化）反例：select * from user where address ='深圳' order by age ;正例：添加索引alter table user add index idx_address_age (address,age)9.修改或删除重要数据前，要先备份，先备份，先备份（SQL后悔药）如果要修改或删除数据，在执行SQL前一定要先备份要修改的数据，万一误操作，还能吃口后悔药~10. where后面的字段，留意其数据类型的隐式转换（SQL性能优化）反例：//userid 是varchar字符串类型select * from user where userid =123;正例：select * from user where userid ='123';理由：因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较，最后导致索引失效11. 尽量把所有列定义为NOT NULL（SQL规范优雅）NULL列需要注意空指针问题，NULL列在计算和比较的时候，需要注意空指针问题。12.修改或者删除SQL，先写WHERE查一下，确认后再补充 delete 或 update（SQL后悔药）尤其在操作生产的数据时，遇到修改或者删除的SQL，先加个where查询一下，确认OK之后，再执行update或者delete操作13.减少不必要的字段返回，如使用select <具体字段> 代替 select * （SQL性能优化）反例：select * from employee;正例：select id，name from employee;理由：可能用到覆盖索引，减少回表，提高查询效率。14.所有表必须使用Innodb存储引擎（SQL规范优雅）Innodb 支持事务，支持行级锁，更好的恢复性，高并发下性能更好，所以呢，没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎15.数据库和表的字符集统一使用UTF8（SQL规范优雅）统一使用UTF8编码可以避免，不同字符集比较转换，导致的索引失效问题如果是存储表情的，可以考虑 utf8mb416. 尽量使用varchar代替 char。（SQL性能优化）反例：  `deptName` char(100) DEFAULT NULL COMMENT '部门名称'正例：`deptName` varchar(100) DEFAULT NULL COMMENT '部门名称'理由：其次对于查询来说，在一个相对较小的字段内搜索，效率更高。17. 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释。 （SQL规范优雅）这个点，是阿里开发手册中，Mysql的规约。你的字段，尤其是表示枚举状态时，如果含义被修改了，或者状态追加时，为了后面更好维护，需要即时更新字段的注释。18. SQL修改数据，养成begin + commit 事务的习惯(SQL后悔药)正例：begin;update account set balance =1000000where name ='捡田螺的小男孩';commit;反例：update account set balance =1000000where name ='捡田螺的小男孩';19. 索引命名要规范，主键索引名为 pk 字段名；唯一索引名为 uk 字段名 ； 普通索引名则为 idx _字段名。（SQL规范优雅）说明： pk  即 primary key；uk 即  unique key；idx _ 即 index 的简称。20. WHERE从句中不对列进行函数转换和表达式计算假设loginTime加了索引反例：select userId,loginTime from loginuser where Date_ADD(loginTime,Interval 7 DAY) >=now();正例：explain  select userId,loginTime from loginuser where  loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);理由：大批量操作会会造成主从延迟。大批量操作，数据量过大，会把cpu打满。21.如果修改/更新数据过多，考虑批量进行。反例：delete from account  limit 100000;正例：for each(200次){ delete from account  limit 500;}理由：大批量操作会会造成主从延迟。大批量操作会产生大事务，阻塞。大批量操作，数据量过大，会把cpu打满。参考与感谢《阿里开发手册》原文链接：https://xie.infoq.cn/article/0a20c423e8fd0d4da26c28dba

为实现数据库中表规范化设计的目的，有时候需要对之前已经创建的表进行结构修改或者调整。在 MySQL 中可以使用 ALTER TABLE 语句来改变原有表的结构，例如增加或删减列、创建或取消索引、更改原有列类型、重新命名列或表等。基本语法修改表指的是修改数据库中已经存在的数据表的结构。MySQL 使用 ALTER TABLE 语句修改表。常用的修改表的操作有修改表名、修改字段数据类型或字段名、增加和删除字段、修改字段的排列位置、更改表的存储引擎、删除表的外键约束等。常用的语法格式如下：ALTER TABLE <表名> [修改选项]修改选项的语法格式如下：{ ADD COLUMN <列名> <类型>| CHANGE COLUMN <旧列名> <新列名> <新列类型>| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }| MODIFY COLUMN <列名> <类型>| DROP COLUMN <列名>| RENAME TO <新表名> }添加字段随着业务的变化，可能需要在已经存在的表中添加新的字段，一个完整的字段包括字段名、数据类型、完整性约束。添加字段的语法格式如下：ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] [FIRST|AFTER 已存在的字段名]；新字段名为需要添加的字段的名称；FIRST 为可选参数，其作用是将新添加的字段设置为表的第一个字段；AFTER 为可选参数，其作用是将新添加的字段添加到指定的已存在的字段名的后面。【实例 1】使用 ALTER TABLE 修改表 tb_emp1 的结构，在表的第一列添加一个 int 类型的字段 col1，输入的 SQL 语句和运行结果如下所示。mysql> ALTER TABLE tb_emp1     -> ADD COLUMN col1 INT FIRST; Query OK, 0 rows affected (0.94 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> DESC tb_emp1; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | col1   | int(11)     | YES  |     | NULL    |       | | id     | int(11)     | YES  |     | NULL    |       | | name   | varchar(25) | YES  |     | NULL    |       | | deptId | int(11)     | YES  |     | NULL    |       | | salary | float       | YES  |     | NULL    |       | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)提示：“FIRST 或 AFTER 已存在的字段名”用于指定新增字段在表中的位置，如果 SQL 语句中没有这两个参数，则默认将新添加的字段设置为数据表的最后列。【实例 2】使用 ALTER TABLE 修改表 tb_emp1 的结构，在一列 name 后添加一个 int 类型的字段 col2，输入的 SQL 语句和运行结果如下所示。mysql> ALTER TABLE tb_emp1     -> ADD COLUMN col2 INT AFTER name; Query OK, 0 rows affected (0.50 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> DESC tb_emp1; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | col1   | int(11)     | YES  |     | NULL    |       | | id     | int(11)     | YES  |     | NULL    |       | | name   | varchar(25) | YES  |     | NULL    |       | | col2   | int(11)     | YES  |     | NULL    |       | | deptId | int(11)     | YES  |     | NULL    |       | | salary | float        | YES  |     | NULL    |       | +--------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)可以看到，表 tb_emp1 中增加了一个名称为 col2 的字段，其位置在指定的 name 字段后面，添加字段成功。修改字段数据类型修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下：ALTER TABLE <表名> MODIFY <字段名> <数据类型>其中，表名指要修改数据类型的字段所在表的名称，字段名指需要修改的字段，数据类型指修改后字段的新数据类型。【实例 3】使用 ALTER TABLE 修改表 tb_emp1 的结构，将 name 字段的数据类型由 VARCHAR(22) 修改成 VARCHAR(30)，输入的 SQL 语句和运行结果如下所示。mysql> ALTER TABLE tb_emp1     -> MODIFY name VARCHAR(30); Query OK, 0 rows affected (0.15 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> DESC tb_emp1; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | col1   | int(11)     | YES  |     | NULL    |       | | id     | int(11)     | YES  |     | NULL    |       | | name   | varchar(30) | YES  |     | NULL    |       | | col2   | int(11)     | YES  |     | NULL    |       | | deptId | int(11)     | YES  |     | NULL    |       | | salary | float        | YES  |     | NULL    |       | +--------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)语句执行后，发现表 tb_emp1 中 name 字段的数据类型已经修改成 VARCHAR(30)，修改成功。删除字段删除字段是将数据表中的某个字段从表中移除，语法格式如下：ALTER TABLE <表名> DROP <字段名>；其中，字段名指需要从表中删除的字段的名称。【实例 4】使用 ALTER TABLE 修改表 tb_emp1 的结构，删除 col2 字段，输入的 SQL 语句和运行结果如下所示。mysql> ALTER TABLE tb_emp1     -> DROP col2; Query OK, 0 rows affected (0.53 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> DESC tb_emp1; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | col1   | int(11)     | YES  |     | NULL    |       | | id     | int(11)     | YES  |     | NULL    |       | | name   | varchar(30) | YES  |     | NULL    |       | | deptId | int(11)     | YES  |     | NULL    |       | | salary | float        | YES  |     | NULL    |       | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.00 sec)修改字段名称MySQL 中修改表字段名的语法规则如下：ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；其中，旧字段名指修改前的字段名；新字段名指修改后的字段名；新数据类型指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。【实例 5】使用 ALTER TABLE 修改表 tb_emp1 的结构，将 col1 字段名称改为 col3，同时将数据类型变为 CHAR(30)，输入的 SQL 语句和运行结果如下所示。mysql> ALTER TABLE tb_emp1     -> CHANGE col1 col3 CHAR(30); Query OK, 0 rows affected (0.76 sec) Records: 0  Duplicates: 0  Warnings: 0 mysql> DESC tb_emp1; +--------+-------------+------+-----+---------+-------+ | Field  | Type        | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | col3   | char(30)    | YES  |     | NULL    |       | | id     | int(11)     | YES  |     | NULL    |       | | name   | varchar(30) | YES  |     | NULL    |       | | deptId | int(11)     | YES  |     | NULL    |       | | salary | float        | YES  |     | NULL    |       | +--------+-------------+------+-----+---------+-------+ 5 rows in set (0.01 sec)CHANGE 也可以只修改数据类型，实现和 MODIFY 同样的效果，方法是将 SQL 语句中的“新字段名”和“旧字段名”设置为相同的名称，只改变“数据类型”。提示：由于不同类型的数据在机器中的存储方式及长度并不相同，修改数据类型可能会影响数据表中已有的数据记录，因此，当数据表中已经有数据时，不要轻易修改数据类型。修改表名MySQL 通过 ALTER TABLE 语句来实现表名的修改，语法规则如下：ALTER TABLE <旧表名> RENAME [TO] <新表名>；其中，TO 为可选参数，使用与否均不影响结果。【实例 6】使用 ALTER TABLE 将数据表 tb_emp1 改名为 tb_emp2，输入的 SQL 语句和运行结果如下所示。mysql> ALTER TABLE tb_emp1     -> RENAME TO tb_emp2; mysql> SHOW TABLES; +--------------------+ | Tables_in_test_db  | +--------------------+ | tb_emp2            | +--------------------+ 1 rows in set (0.00 sec)提示：用户可以在修改表名称时使用 DESC 命令查看修改后两个表的结构，修改表名并不修改表的结构，因此修改名称后的表和修改名称前的表的结构是相同的。

在做开发测试环境和生产误操作导致数据库误删除/更新，对DBA而言，回滚数据着实是一件头疼的事情，凡涉及到恢复线上数据必然对应用带来一定的影响。较多的情况是开发误操作delete数据，update多数行，根据之前的操作经验，下面介绍常用的恢复方法。1 .利用备份恢复使用这种方式的前提必须有最近的备份集或者知道出现误操作起始的binlog 位点或者GTID,利用备份集恢复到中间的机器上，然后利用MySQL的slave 特性START SLAVE [SQL_THREAD] UNTIL MASTER_LOG_FILE = 'log_name', MASTER_LOG_POS = log_pos;until_option:UNTIL { {SQL_BEFORE_GTIDS | SQL_AFTER_GTIDS} = gtid_set | MASTER_LOG_FILE = 'log_name',  MASTER_LOG_POS = log_pos | RELAY_LOG_FILE = 'log_name', RELAY_LOG_POS = log_pos | SQL_AFTER_MTS_GAPS }恢复出到一个临时的实例，将误删除,更新的数据 dump 出来并恢复到老的实例里面。恢复数据期间的受影响的表最好不可写，否则将难以达到最想要的结果。例如 a=2 ，被误更新为 a=4，恢复的期间有被更新为a=7 ，结果恢复后又恢复为a=2 。 此种恢复方式 不适合恢复大量数据库，且需要临时实例。2.2 利用开源工具binlog2sql 恢复binlog2sql 是大众点评公司的DBA 开发的一款基于通过解析binlog将delete 恢复为insert,update 的值 set 字段和where条件做对调的原理来恢复数据的。 使用限制 MySQL的binlog format 必须是row 安装git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql pip install -r requirements.txt用法usage: binlog2sql.py [-h HOST] [-u USER]             [-p PASSWORD] [-P PORT]            [--start-file STARTFILE]             [--start-position STARTPOS]            [--stop-file ENDFILE]             [--stop-position ENDPOS]            [--start-datetime STARTTIME]             [--stop-datetime STOPTIME]            [--stop-never]             [-d [DATABASES [DATABASES ...]]]            [-t [TABLES [TABLES ...]]]             [-K] [-B]            [--help]例子create table flashback( id int(11) not null auto_increment primary key , stat int(11) not null default 1  ) engine=innodb default charset=utf8; insert into flashback(stat)  values (2),(3),(4),(7),(9),(22),(42),(33),(66),(88)误操作update flashback set stat=15恢复数据的步骤1.获取误操作的dml所在的binlog，不过一般开发可不知道具体binlog，他们只知道什么时间误操作了，binlog2sql支持按照时间范围恢复。mysql> show master logs; +------------------+-----------+ | Log_name     | File_size | +------------------+-----------+ | mysql-bin.000009 | 177 | | mysql-bin.000010 | 464 | | mysql-bin.000011 | 8209 | +------------------+-----------+ 3 rows in set (0.00 sec)例子中binlog为mysql-bin.0000112.利用binlog2sql 恢复数据，先解析binlog获取 update 语句的起始位点，本例中 start 5087 end 5428，执行命令python binlog2sql.py -h127.0.0.1 -P3307 -udba -p'dbadmin' -dyang -tflashback --start-file='mysql-bin.000011'使用binlog2sql -B 参数得到恢复的sql将获取到的sql 执行到数据库，假如生产环境中真的发生了问题，一定要和开发沟通并且确认需要恢复的确切记录。mysql> select * from flashback; +----+------+ | id | stat | +----+------+ | 1 | 2 | | 2 | 3 | | 3 | 4 | | 4 | 7 | | 5 | 9 | | 6 | 22 | | 7 | 42 | | 8 | 33 | | 9 | 66 | | 10 | 88 | +----+------+ 10 rows in set (0.00 sec)binlog2sql的特点:mysql server必须开启，离线模式下不能解析 优点（对比mysqlbinlog) 。纯Python开发，安装与使用都很简单。自带flashback、no-primary-key解析模式，无需再装补丁。flashback模式下，更适合闪回实战。解析为标准SQL，方便理解、调试。代码容易改造，可以支持更多个性化解析.其实MySQL 还提供了一个参数 sql_safe_updates，该参数将禁止 不带where 条件的delete和update语句。具体用法和介绍还请参考MySQL官方介绍。

 摘要：那些会导致执行效率低下的SQL语句及其执行方式，我们称之为SQL中的“坏味道”。◆ 什么是SQL中的坏味道SQL语言是关系型数据库（RDB）的标准语言，其作用是将使用者的意图翻译成数据库能够理解的语言来执行。人类之间进行交流时，同样的意思用不同的措辞会产生不同的效果。类似地，人类与数据库交流信息时，同样的操作用不同的SQL语句来表达，也会导致不同的效率。而有时同样的SQL语句，数据库采用不同的方式来执行，效率也会不同。那些会导致执行效率低下的SQL语句及其执行方式，我们称之为SQL中的“坏味道”。下面这个简单的例子，可以说明什么是SQL中的坏味道。图1-a 用union合并集合在上面的查询语句中，由于使用了union来合并两个结果集，在合并后需要排序和去重，增加了开销。实际上符合dept_id = 1和dept_id > 2的结果间不会有重叠，所以完全可以用union all来合并，如下图所示。图1-b 用union all合并集合而更高效的做法是用or条件，在扫描的时候直接过滤出所需的结果，不但节省了运算，也节省了保存中间结果所需的内存开销，如下图所示。图1-c 用or条件来过滤结果可见完成同样的操作，用不同的SQL语句，效率却大相径庭。前两条SQL语句都不同程度地存在着“坏味道”。对于这种简单的例子，用户可以很容易发现问题并选出最佳方案。但对于一些复杂的SQL语句，其性能缺陷可能很隐蔽，需要深入分析才有可能挖掘出来。这对数据库的使用者提出了很高的要求。即便是资深的数据库专家，有时也很难找出性能劣化的原因。GaussDB在执行SQL语句时，会对其性能表现进行分析和记录，通过视图和函数等手段呈现给用户。本文将简要介绍如何利用GaussDB提供的这些“第一手”数据，分析和定位SQL语句中存在的性能问题，识别和消除SQL中的“坏味道”。◆ 识别SQL坏味道之自诊断视图GaussDB在执行SQL时，会对执行计划以及执行过程中的资源消耗进行记录和分析，如果发现异常情况还会记录告警信息，用于对原因进行“自诊断”。用户可以通过下面的视图查询这些信息：• gs_wlm_session_info• pgxc_wlm_session_info• gs_wlm_session_history• pgxc_wlm_session_history其中gs_wlm_session_info是基本表，其余3个都是视图。gs_开头的用于查看当前CN节点上收集的信息，pgxc_开头的则包含集群中所有CN收集的信息。各表格和视图的定义基本相同，如下表所示。表1 自诊断表格&函数字段定义其中的query字段就是执行的SQL语句。通过分析每个query对应的各字段，例如执行时间，内存，IO，下盘量和倾斜率等等，可以发现疑似有问题的SQL语句，然后结合query_plan（执行计划）字段，进一步地加以分析。特别地，对于一些在执行过程中发现的异常情况，warning字段还会以human-readable的形式给出告警信息。目前能够提供的自诊断信息如下：◇多列/单列统计信息未收集优化器依赖于表的统计信息来生成合理的执行计划。如果没有及时对表中各列收集统计信息，可能会影响优化器的判断，从而生成较差的执行计划。如果生成计划时发现某个表的单列或多列统计信息未收集，warning字段会给出如下告警信息：Statistic Not Collect:schemaname.tablename(column name list)此外，如果表格的统计信息已收集过（执行过analyze），但是距离上次analyze时间较远，表格内容发生了很大变化，可能使优化器依赖的统计信息不准，无法生成最优的查询计划。针对这种情况，可以用pg_total_autovac_tuples系统函数查询表格中自从上次分析以来发生变化的元组的数量。如果数量较大，最好执行一下analyze以使优化器获得最新的统计信息。◇SQL未下推执行计划中的算子，如果能下推到DN节点执行，则只能在CN上执行。因为CN的数量远小于DN，大量操作堆积在CN上执行，会影响整体性能。如果遇到不能下推的函数或语法，warning字段会给出如下告警信息：SQL is not plan-shipping, reason : %s◇Hash连接大表做内表如果发现在进行Hash连接时使用了大表作为内表，会给出如下告警信息：PlanNode[%d] Large Table is INNER in HashJoin \"%s\"目前“大表”的标准是平均每个DN上的行数大于100，000，并且内表行数是外表行数的10倍以上。◇大表等值连接使用NestLoop如果发现对大表做等值连接时使用了NestLoop方式，会给出如下告警信息：PlanNode[%d] Large Table with Equal-Condition use Nestloop\"%s\"目前大表等值连接的判断标准是内表和外表中行数最大者大于DN的数量乘以100，000。◇数据倾斜数据在DN之间分布不均匀，可导致数据较多的节点成为性能瓶颈。如果发现数据倾斜严重，会给出如下告警信息：PlanNode[%d] DataSkew:\"%s\", min_dn_tuples:%.0f, max_dn_tuples:%.0f目前数据倾斜的判断标准是DN中行数最多者是最少者的10倍以上，且最多者大于100,000。◇代价估算不准确GaussDB在执行SQL语句过程中会统计实际付出的代价，并与之前估计的代价比较。如果优化器对代价的估算与实际的偏差很大，则很可能生成一个非最优化的计划。如果发现代价估计不准确，会给出如下告警信息："PlanNode[%d] Inaccurate Estimation-Rows: \"%s\" A-Rows:%.0f, E-Rows:%.0f目前的代价由计划节点返回行数来衡量，如果平均每个DN上实际/估计返回行数大于100,000，并且二者相差10倍以上，则认定为代价估算不准。◇Broadcast量过大Broadcast主要适合小表。对于大表来说，通常采用Hash+重分布（Redistribute）的方式效率更高。如果发现计划中有大表被广播的环节，会给出如下告警信息：PlanNode[%d] Large Table in Broadcast \"%s\"目前对大表广播的认定标准为平均广播到每个DN上的数据行数大于100,000。◇索引设置不合理如果对索引的使用不合理，比如应该采用索引扫描的地方却采用了顺序扫描，或者应该采用顺序扫描的地方却采用了索引扫描，可能会导致性能低下。索引扫描的价值在于减少数据读取量，因此认为索引扫描过滤掉的行数越多越好。如果采用索引扫描，但输出行数/扫描总行数>1/1000，并且输出行数>10000（对于行存表）或>100（对于列存表），则会给出如下告警信息：PlanNode[%d] Indexscan is not properly used:\"%s\", output:%.0f, filtered:%.0f, rate:%.5f顺序扫描适用于过滤的行数占总行数比例不大的情形。如果采用顺序扫描，但输出行数/扫描总行数<=1/1000，并且输出行数<=10000（对于行存表）或<=100（对于列存表），则会给出如下告警信息：PlanNode[%d] Indexscan is ought to be used:\"%s\", output:%.0f, filtered:%.0f, rate:%.5f◇下盘量过大或过早下盘SQL语句执行过程中，因为内存不足等原因，可能需要将中间结果的全部或一部分转储的磁盘上。下盘可能导致性能低下，应该尽量避免。如果监测到下盘量过大或过早下盘等情况，会给出如下告警信息：• Spill file size large than 256MB• Broadcast size large than 100MB• Early spill• Spill times is greater than 3• Spill on memory adaptive• Hash table conflict下盘可能是因为缓冲区设置得过小，也可能是因为表的连接顺序或连接方式不合理等原因，要结合具体的SQL进行分析。可以通过改写SQL语句，或者HINT指定连接方式等手段来解决。使用自诊断视图功能，需要将以下变量设成合适的值：▲ use_workload_manager（设成on，默认为on）▲ enable_resource_check（设成on，默认为on）▲ resource_track_level（如果设成query，则收集query级别的信息，如果设成operator，则收集所有信息，如果设成none，则以用户默认的log级别为准）▲ resource_track_cost（设成合适的正整数。为了不影响性能，只有执行代价大于resource_track_cost语句才会被收集。该值越大，收集的语句越少，对性能影响越小；反之越小，收集的语句越多，对性能的影响越大。）执行完一条代价大于resource_track_cost后，诊断信息会存放在内存hash表中，可通过pgxc_wlm_session_history或gs_wlm_session_history视图查看。视图中记录的有效期是3分钟，过期的记录会被系统清理。如果设置enable_resource_record=on，视图中的记录每隔3分钟会被转储到gs_wlm_session_info表中，因此3分钟之前的历史记录可以通过gs_wlm_session_info表或pgxc_wlm_session_info视图查看。◆ 发现正在运行的SQL的坏味道上一节提到的自诊断视图可以显示已完成SQL的信息。如果要查看正在运行的SQL的情况，可以使用下面的视图：• gs_wlm_session_statistics• pgxc_wlm_session_statistics类似地，gs_开头的用于查看当前CN节点上收集的信息，pgxc_开头的则包含集群中所有CN收集的信息。两个视图的定义与上一节的自诊断视图基本相同，使用方法也基本一致。 通过观察其中的字段，可以发现正在运行的SQL中存在的性能问题。例如，通过“select queryid, duration from gs_wlm_session_statistics order by duration desc limit 10;”可以查询当前运行的SQL中，已经执行时间最长的10个SQL。如果时间过长，可能有必要分析一下原因。图2-a 通过gs_wlm_session_statistics视图发现可能hang住SQL查到queryid后，可以通过query_plan字段查看该SQL的执行计划，分析其中可能存在的性能瓶颈和异常点。图2-b 通过gs_wlm_session_statistics视图查看当前SQL的执行计划再下一步，可以结合等待视图等其他手段定位性能劣化的原因。图2-c 通过gs_wlm_session_statistics视图结合等待视图定位性能问题另外，活动视图pg_stat_activity也能提供一些当前执行SQL的信息。◆ Top SQL——利用统计信息发现SQL坏味道除了针对逐条SQL进行分析，还可以利用统计信息发现SQL中的坏味道。另一篇文章“Unique SQL特性原理与应用”中提到的Unique SQL特性，能够针对执行计划相同的一类SQL进行了性能统计。与自诊断视图不同的是，如果同一个SQL被多次执行，或者多个SQL语句的结构相同，只有条件中的常量值不同。这些SQL在Unique SQL视图中会合并为一条记录。因此使用Unique SQL视图能更容易看出那些类型的SQL语句存在性能问题。利用这一特性，可以找出某一指标或者某一资源占用量最高/最差的那些SQL类型。这样的SQL被称为“Top SQL”。例如，查找占用CPU时间最长的SQL语句，可以用如下SQL：select unique_sql_id,query,cpu_time from pgxc_instr_unique_sql order by cpu_time desc limit 10。Unique SQL的使用方式详见https://bbs.huaweicloud.com/blogs/197299。◆ 结论发现SQL中的坏味道是性能调优的前提。GaussDB对数据库的运行状况进行了SQL级别的监控和记录。这些打点记录的数据可以帮助用户发现可能存在的异常情况，“嗅”出潜在的坏味道。从这些数据和提示信息出发，结合其他视图和工具，可以定位出坏味道的来源，进而有针对性地进行优化

（1）Mybatis和hibernate不同，它不完全是一个ORM框架，因为MyBatis需要程序员自己编写Sql语句。（2）Mybatis直接编写原生态sql，可以严格控制sql执行性能，灵活度高，非常适合对关系数据模型要求不高的软件开发，因为这类软件需求变化频繁，一但需求变化要求迅速输出成果。但是灵活的前提是mybatis无法做到数据库无关性，如果需要实现支持多种数据库的软件，则需要自定义多套sql映射文件，工作量大。（3）Hibernate对象/关系映射能力强，数据库无关性好，对于关系模型要求高的软件，如果用hibernate开发可以节省很多代码，提高效率。

 摘要： 性能调优是应用迁移或开发过程中的关键步骤，同时也在整个项目实施过程中占据很大的份量，本篇主要介绍数据库级别的性能调优思路和总体策略。性能调优是应用迁移或开发过程中的关键步骤，同时也在整个项目实施过程中占据很大的份量，在很多实施步骤中都需要进行考虑，从开始的数据建模，表定义的设计，到数据库硬件、集群部署的选择，再到数据库系统级调优、数据表结构设计，以及单个SQL语句的编写及调优，都要考虑对性能的影响。同时，性能调优通常没有明确的衡量标准，没有明确的对错之分，通常需要的隐式技能比较多，使得其的技术含金量得以提升。通常来看，要做到一个性能调优的高手，除了对于应用程序的逻辑做到游刃有余外，还需要对应用的数据库的基本实现原理有所了解，更甚者，还需要对操作系统、网络等基础知识有所涉猎，同时还要具备性能诊断和分析技巧。当然，性能调优是一个不断积累的过程，大家不用考虑一步到位，唯有进行实践的积累，才能在广阔的调优战场所向披靡。本篇博文作为《GaussDB(DWS)性能调优系列》的专题文章，主要介绍数据库级别的性能调优思路和总体策略，包括系统级和语句级调优。同时，《GaussDB(DWS)性能调优系列》文章分为基础篇和实战篇，各位读者在通过基础篇文章了解数据库的基本原理后，可以结合调优思路，对实战篇的各个调优技巧进行深入的学习。有关整个调优过程中的其它方面，后续论坛会推出其它博文进行介绍。1. GaussDB DWS执行架构及说明GaussDB DWS是典型的share-nothing架构，其计算组件的示意图如上图所示，主要由CN（Coordinator）和DN（DataNode）组成。CN是整个集群的协调者，是整个集群与应用进行连接处理的门户，用于接收客户的SQL语句并返回执行结果。DN是集群进行数据计算的主体，各个DN拥有独立的存储和计算资源，使得各个DN可以独立地进行计算。GaussDB DWS支持多CN架构，通常应用程序会通过LVS（负载均衡设备）将语句均匀分发到各个CN上，以减少单个CN的瓶颈作用。GaussDB DWS的SQL处理流程如上方右图所示，其包含以下几个主要步骤：（1）CN通过驱动或客户端接收到一条SQL后，会进行解析、优化，并最终返回执行结果；（2）CN进行优化后，生成相应的执行计划；（3）CN将相同的执行计划下发到各个DN进行执行；（4）如果DN之间需要进行数据交换，则执行计划中包含流操作算子Stream，DN之间同步通过Stream算子进行数据交换，共同完成计划的执行并向CN返回结果。同时，GaussDB DWS对于不需要DN间数据交换的语句，还支持语句下发到DN生成计划；对于部分不支持分布式查询的语句，生成不能下推的计划（此计划对于大数据量性能较差）。各种计划的对比可详见博文《GaussDB(DWS)性能调优系列基础篇三：衍化至繁之分布式计划详解》。本文后续的讨论均基于Stream计划。2. 整体调优思路通过前面对SQL语句执行流程的介绍，我们可以知道，性能瓶颈可能发生在CN端、DN端，以及结果集返回，驱动数据处理等环节，性能调优的第一步就是定位瓶颈点主要发生在哪个环节。由于GaussDB DWS大数据量处理时，大部分执行时间消耗在DN端，故本博文主要针对DN端语句执行进行总体调优思路的分享。谈到执行性能，其实从数据模型建模、集群部署、表结构设计，到最终的SQL语句优化，都与之紧密相关，如上图所示，我们使用金字塔来描述整个调优过程。越接近塔尖，其对于整个业务性的影响范围越广，需要调优时，调优成本也越高，所以在设计之初需要投入足够精力，从上至下，我们需要全面的设计，才能减少在最终SQL调优时返工的可能。整个调优过程其实是一个不断迭代的过程，如上图所示。即使设计再严密，也有可能出现SQL语句性能的优化需要导致数据建模更改、集群部署、表分布键调整的情况，这时一发动全身将引起较高的成本，同时会对其它已经调优完毕的SQL产生影响，导致重新调优，成本较高。我们统一将前三阶段归结为静态调优，将SQL语句级调优归结为执行态调优。下面重点来探讨执行态调优-SQL语句调优，从调优步骤来看可以分为性能瓶颈诊断、性能原因分析和调优项实施，从调优实施对象来看，可能包括前面提到的数据建模、集群部署、表结构设计方面的修改，SQL语句层调优可以分为系统级调优和语句级调优。当然，有一些调优项，例如系统调优项，可以作为经验固化下来，在集群部署的时候就一并设置好，减少这方面调优花费的成本。同时，SQL调优也是一个迭代的过程，在实施一次调优项后，需要继续重新进行调优分析，直至性能达到标准为止。后面的章节，将围绕调优步骤和SQL层的调优项来开展。3. 性能瓶颈诊断GaussDB DWS提供了丰富的计划信息显示工具Explain，以及动态执行信息分析工具Top SQL。Explain工具主要针对单个语句进行展示，可以使用explain命令显示CN生成的SQL语句的计划，也可以使用explain analyze/performance命令显示执行态信息。通过执行态信息，我们可以分析出算子为单位的性能，也可以分析出算子内部各步骤的性能，进一步为诊断性能的瓶颈打下了基础。Explain工具相关内容请参考博文《GaussDB(DWS)性能调优系列基础篇二：大道至简explain计划信息》。Top SQL工具则针对集群中运行的语句进行整体性能分析，其包含12个视图，可以将执行时间超过一定设置阈值的语句的执行状态、执行结果进行实时查询，同时可以设置将其转储用作后续分析。附加于该工具之上的SQL自诊断调优工具，则通过瓶颈点的分析，给出可能的性能原因分析。同时，我们还提供Unique SQL工具，进行一类SQL的性能持续跟踪，可以用于发现系统资源及硬件问题对SQL性能产生的影响。Top SQL工具相关内容请参考博文《GaussDB(DWS)性能调优系列实战篇二：十八般武艺之坏味道SQL识别》。4. 性能原因分析性能原因分析属于性能调优里的高阶知识了，通常要对数据库的执行实现原理有基本了解才能够逐步深入下去。本章节将深入浅出地介绍数据库执行实现原理的基本技术，帮助各位读者朋友能够有兴趣去主动查找性能产生的原因，从而自己找到性能调优的方法。前面已经对GaussDB DWS的执行流程进行了介绍，由CN生成执行计划下发到DN去执行。GaussDB DWS是基于代价来生成计划的，因此需要依据基表的统计信息，进行每一步结果集统计信息的估算，根据数据规模的场景从GaussDB DWS支持的备选算子中选择最优的算子组合成计划进行执行。因此，统计信息是计划准确的前提，在执行SQL语句前要确保收集最新的统计信息，有关统计信息的收集可以参见博文《GaussDB(DWS)性能调优系列基础篇一：万物之始analyze统计信息》。由于统计信息只包含基表的统计信息，表关联之后的统计信息只能通过估算得到，因此仍然可能存在估算不准的情况。GaussDB DWS针对不同的SQL语句中的操作，为每个操作内部实现了不同的算子。每个算子可能在部分场景下是占优的，但其它场景比较差。SQL优化时，根据具体的场景去自动匹配最优的算子。如果存在估算不准，将导致算子选择出现失误，从而计划较差，此时就需要根据计划的瓶颈来分析具体的原因了。通常情况下，GaussDB处理的操作类型主要分为：扫描算子（Scan）、关联算子（Join）、聚集算子（Agg）和网络传输算子（Stream）。下表列出了各算子类别的使用场景，以及各类别中可选的算子，及其适用范围，同时列出调优场景，供大家参考。（1）扫描算子（Scan）：主要用于处理从存储扫描数据，返回上层算子，包括：全表扫描算子、索引扫描算子，其中行列存均对应不同的全套扫描算子，索引扫描包括：IndexScan（普通索引扫描）、IndexOnlyScan（仅扫描索引即可获得结果）、BitmapScan（需要索引扫描获取位图后再到基表上扫描），BitmapScanAnd/Or（从多个索引扫描进行位图运算后再到基表上扫描），由于索引扫描的原理基本都相同，故一并探讨。（2）关联算子（Join）：主要用于处理表的关联操作。在数据库中，多表关联时，SQL优化会选择关联顺序进行两两关联。表关联时可以包含关联操作，也可以没有关联操作（笛卡尔积）。在GaussDB DWS中，主要包含NestLoop, HashJoin, MergeJoin三种关联算子。（3）聚集算子（Agg）：（4）网络传输算子（Stream）：（5）其它算子：同时GaussDB DWS还支持排序（Sort）、集合（SetOp）、物化（Materialize）、窗口聚集（WindowAgg）和输出限制（Limit）算子，由于调优基本不涉及，故此处略过。5. 调优项实施在知道导致性能问题的原因后，就可以制定调优项并开始实施了。前面已经提到，调优项实施的范围很广。本博文仅探讨数据库级的调优项，包括系统级调优和语句级调优两部分。a)系统级调优项系统级调优又细分为操作系统参数调优和数据库全局参数调优，通常涉及到的是系统CPU、IO、内存、网络资源的充分使用，避免资源冲突，提升整个系统查询的吞吐量。由于数据库是运行在操作系统之上的，因此操作系统资源的利用率对于数据库性能的提升起到基石的作用。对于操作系统参数的调优，主要集中在操作系统内存参数、IO参数以及网络参数的设置上，具体可参见GaussDB DWS产品文档。数据库级别的调优，主要也是集中在上述资源的使用上，在上述四维度有以下主要因素的考虑（具体设置方法可以参见GaussDB DWS产品文档）：b)语句级调优项语句级调优通常需要通过计划分析，找到性能瓶颈点，然后根据瓶颈点对应的扫描、关联、聚集、Stream等算子，分析是否属于算子适用场景，是否符合调优条件。如果是，我们有以下调优手段：i. 通过修改表定义，包括行列存、表的分布方式，达到减少IO和网络资源开销的目的，详见博文《GaussDB(DWS)性能调优系列实战篇三：十八般武艺之好味道表定义》。ii. 如果最终分析是由于估算不准导致，可以通过相关GUC参数调整来设置不同的结果集估算模型，或禁止生成某种类型的算子，通过改进估算值达到优化计划的目的，详见博文《GaussDB(DWS)性能调优系列实战篇五：十八般武艺之路径干预》。iii.如果在迁移或升级过程中出现计划劣化，也可以通过Plan Hint的调优方式干预优化器生成理想的计划，详见博文《GaussDB(DWS)性能调优系列实战篇六：十八般武艺Plan hint运用》。iv.对于上述调优手段都无法解决的问题，例如：下推问题，相关子查询提升，NOT IN等问题，或者SQL语句存在计算冗余等问题，需要根据瓶颈点选择灵活多变的SQL改写策略消除瓶颈点，具体可参见博文《GaussDB(DWS)性能调优系列实战篇四：十八般武艺之SQL改写》总的来说，性能调优是一项艰巨的工程，当然深入其中，学习到的知识以及获得的收获都是非常大的。后续论坛也会推出更多的博文对性能调优的方方面面进行介绍，帮助各位读者迅速积累调优的经验