SQL

前言：这里将我编程实践中遇到的有价值的sql语句一路记下来，一方面方便自己查用，一方面也夯实下即将遗忘的回忆。整个过程中我会不断更新，直到不能再加为止，同时，这里只记录最实用的咚咚，不效仿学院派的那一套。

　

一、常用SQL语句荟萃

1，查询：

1.1，简单查询：select * from table where

 

1.2，连接查询：

什么是连接查询？顾名释义，就是查询时涉及多个表的查询。是以说到连接，废话一下，要知道连接还是关系数据库的主要特点呢。

连接查询分为三种：外连接(OUTER JOIN)，内连接(INNER JOIN)，交叉连接(CROSS JOIN)。

1.2.1，内连接(INNER JOIN)使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行。根据所使用的比较方式不同，内连接又分为等值连接、自然连接和不等连接三种。

1.2.2，外连接分为左外连接(LEFT OUTER JOIN或LEFT JOIN)、右外连接(RIGHT OUTER JOIN或RIGHT JOIN)和全外连接(FULL OUTER JOIN或FULL JOIN)三种。与内连接不同的是，外连接不只列出与连接条件相匹配的行，而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行。

1.2.3，交叉连接(CROSS JOIN)没有WHERE子句，它返回连接表中所有数据行的笛卡尔积，其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。连接操作中的ON (join_condition)子句指出连接条件，它由被连接表中的列和比较运算符、逻辑运算符等构成。

1.2.4，无论哪种连接都不能对text、ntext和image数据类型列进行直接连接，但可以对这三种列进行间接连接。例如：

SELECT p1.pub_id,p2.pub_id,p1.pr_info
FROM pub_info AS p1 INNER JOIN pub_info AS p2
ON DATALENGTH(p1.pr_info)=DATALENGTH(p2.pr_info)

 

1.2.5，使用WHERE子句设置查询条件

WHERE子句设置查询条件，过滤掉不需要的数据行。例如下面语句查询年龄大于20的数据：

SELECT *

FROM usertable

WHERE age>20

WHERE子句可包括各种条件运算符：

比较运算符(大小比较)：>、>=、=、<、<=、<>、!>、!<

范围运算符(表达式值是否在指定的范围)：BETWEEN…AND…

NOT BETWEEN…AND…

列表运算符(判断表达式是否为列表中的指定项)：IN (项1,项2……)

NOT IN (项1,项2……)

模式匹配符(判断值是否与指定的字符通配格式相符):LIKE、NOT LIKE

空值判断符(判断表达式是否为空)：IS NULL、NOT IS NULL

逻辑运算符(用于多条件的逻辑连接)：NOT、AND、OR

1、范围运算符例：age BETWEEN 10 AND 30相当于age>=10 AND age<=30

2、列表运算符例：country IN ('Germany','China')

3、模式匹配符例：常用于模糊查找，它判断列值是否与指定的字符串格式相匹配。可用于char、varchar、text、ntext、datetime和smalldatetime等类型查询。

可使用以下通配字符：

百分号%：可匹配任意类型和长度的字符，如果是中文，请使用两个百分号即%%。

下划线_：匹配单个任意字符，它常用来限制表达式的字符长度。

方括号[]：指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。

[^]：其取值也[]相同，但它要求所匹配对象为指定字符以外的任一个字符。

例如：

限制以Publishing结尾，使用LIKE '%Publishing'

限制以A开头：LIKE '[A]%'

限制以A开头外：LIKE '[^A]%'

空值判断符例：WHERE age IS NULL

2，更新：update table

　

3，插入：

3.1，一般插入：

INSERT INTOpublishers
(pub_id, pub_name, city, state)
VALUES
('9001', 'Acme Publishing', 'New York', 'NY')

3.2，插入多行

使用 INSERT语句可以向表添加多行数据。这些多行数据是从已经存有这些数据的另一个表中选取的。本例中，向 pubhold表中添加有关在加利福尼亚和德克萨斯州的出版商的数据。这些数据可从 publishers表中获得。

INSERT pubhpold SELECT * FROM publishers
WHERE state = 'CA' OR state = 'TX'

　

4，删除：

4.1，Delete语句联合删除：

DELETE FROM uu_SuiteToMinClassroomSect

WHERE min_classroom_sect_id IN

  (SELECT min_classroom_sect_id

  FROM uu_MinClassroomSect

  WHERE min_classroom_id = '112')

二、视图使用细则

1，一个典型的视图

CREATE VIEW View_uuGroupTaxis

AS

SELECT uu_GroupInfo.group_id, uu_GroupInfo.group_name,

      uu_GroupType.main_type, uu_GroupType.group_type_name,

     uu_GroupInfo.group_icon_url, ISNULL

         ((SELECT COUNT(*)

         FROM uu_GroupUser

         WHERE uu_GroupInfo.group_id = uu_GroupUser.group_id), 0)

     * 50 + ISNULL(uu_GroupInfo.fundCount, 0) + ISNULL

         ((SELECT COUNT(*)

         FROM Dv_Topic

         WHERE Dv_Topic.BoardID = uu_GroupInfo.subforum_id), 0) * 5 + ISNULL

         ((SELECT COUNT(*)

         FROM uu_GroupPhotos

         WHERE uu_GroupPhotos.group_id = uu_GroupInfo.group_id), 0)

     * 10 + ISNULL(uu_GroupInfo.topic_account, 0)

     * 2 + ISNULL(uu_GroupInfo.hit_account, 0) AS group_ActiveDegree,

     ISNULL

         ((SELECT COUNT(*)

         FROM uu_GroupUser

         WHERE uu_GroupInfo.group_id = uu_GroupUser.group_id), 0)

     AS group_memberNum, ISNULL(uu_GroupInfo.fundCount, 0) AS fundCount,

         (SELECT COUNT(*)

        FROM Dv_Topic

        WHERE Dv_Topic.BoardID = uu_GroupInfo.subforum_id) AS group_articleNum,

         (SELECT COUNT(*)

        FROM uu_GroupPhotos

        WHERE uu_GroupPhotos.group_id = uu_GroupInfo.group_id) AS group_PhotoNum,

     uu_GroupInfo.topic_account, uu_GroupInfo.hit_account,

         (SELECT user_name

        FROM uu_RegisterUser

        WHERE uu_RegisterUser.user_id = uu_GroupInfo.creator_id)

     AS group_CreatorName, uu_GroupInfo.create_time

FROM uu_GroupInfo INNER JOIN

     uu_GroupType ON

     uu_GroupInfo.group_type_id = uu_GroupType.group_type_id

 

三，存储过程的创建和调用

1，存储过程的调用

Execute procedureName @param=’value’

2，一个典型的带参数存储过程

CREATE PROCEDURE P_delMiniclassProc

 @miniClassroom_id int

AS

declare @billtag varchar(4)

set nocount on

IF @miniClassroom_id is null

        begin

                  return(-1)

        end

else

                  BEGIN TRANSACTION

                  --删除套餐信息

                  DELETE FROM uu_SuiteToMinClassroomSect

                  WHERE min_classroom_sect_id IN

                     (SELECT min_classroom_sect_id

                     FROM uu_MinClassroomSect

                     WHERE min_classroom_id =@miniClassroom_id)

                  --删除小课堂段信息

                  delete from uu_MinClassroomSect

                  where min_classroom_id = @miniClassroom_id

                  --删除小课堂用户购买记录

                  delete from uu_UserBuyMinClassroom

                  where min_classroom_id = @miniClassroom_id

                  --删除对应小课堂年级学科信息

                  delete from uu_MinClassroomToGradeClass

                  where min_classroom_id = @miniClassroom_id

                  --删除小课堂发言

                  delete from uu_MinClassroomDiscuss

                  where min_classroom_id = @miniClassroom_id

                  --删除课程讨论

                  DELETE FROM uu_CourseDiscuss

                  WHERE course_id IN

                     (SELECT course_id

                     FROM uu_CourseInfo

                     WHERE min_classroom_id = @miniClassroom_id)

                  --删除用户课程收藏

                  DELETE FROM uu_UserCellectionCourse

                  WHERE course_id IN

                     (SELECT course_id

                     FROM uu_CourseInfo

                     WHERE min_classroom_id = @miniClassroom_id)

                  --删除小课堂小课程的用户购买信息

                  DELETE FROM uu_UserBuyCourse

                  WHERE course_id IN

                     (SELECT course_id