SQL语句

数据库SQL语句的思维导图，主要内容有SQL语句的数据查询功能(DQL)、SQL语句的操纵功能(DML)、SQL的数据查询功能(DQL)等。

SQL语句

• SQL语句的数据查询功能（DQL）
  • 聚合函数:对一组值进行分析或汇总并返回单个值。 （COUNT(),SUM(), AVG(), MAX(),MIN()）
    • 1. COUNT( )----返回某列的行数 语法格式如下： SELECT COUNT（[DISTINCT] * |字段名）FROM 表名 COUNT（*）不会忽略空值 ：统计一个表的总行数； COUNT（字段名）会忽略空值 ：统计一列中值的个数； COUNT（[DISTINCT] 字段名）会忽略空值且剔除重复：统计一列中值的个数；
    • 2. SUM()----返回某列值的和 语法格式如下： SELECT SUM（[DISTINCT] 字段名 ）FROM 表名 注：sum()里面的参数必须是数值型数据，sum()忽略列值为NULL的列。
    • 3.AVG()----返回某列的平均值（参数必须是数值型列或数据） 语法格式如下：SELECT AVG（[DISTINCT] 字段名 ）FROM 表名 注：1.Avg()只能获得单个列的平均值，如果需要获得多个列的平均值必须使用多个avg() 2.avg()忽略列值为空的行
    • 4.MAX()-----返回某列的最大值 语法格式如下： SELECT MAX（[DISTINCT] 字段名 ）FROM 表名
    • 5.MIN()----返回某列的最小值 语法格式如下： SELECT MIN（[DISTINCT] 字段名 ）FROM 表名 注：max()和min()里面的参数可以是数值型，也可以是文本型或日期时间型。 max()和min()都忽略列值为NULL的列。
  • 带GROUP的分组查询
    • 数据分组（ Group By）：字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。
    • 语法格式如下：SELECT 字段名1，字段名2…[，聚合函数] FROM 表名 GROUP BY 分组字段名1，分组字段名2…. [HAVING 条件表达式] 与聚合函数使用：SELECT 分组字段名，聚合函数 FROM 数据源 GROUP BY 分组字段名
    • GROUP BY 和HAVING 关键字一起使用--------过滤分组 过滤分组：即对分组的结果进行过滤。对分组进行过滤的关键字是HAVING，语句格式如下： SELECT 分组字段[，聚合函数] FROM 表名 GROUP BY 分组字段 HAVING <过滤条件>
    • GROUP BY与ORDER BY子句一起使用，ORDER BY子句要放在GROUP BY子句之后。 SELECT 分组字段[，聚合函数] FROM 数据源 GROUP BY 分组字段 HAVING <过滤条件> ORDER BY 排序字段
  • 小结：SELECT语句中所有子句的顺序，使用SELECT语句完成数据检索时必须遵循如下的子句顺序。SELECT [ALL|TOP|DISTINCT]字段列表|* FROM 数据源 [WHERE 条件表达式 ] [GROUP BY 分组字段] [HAVING条件表达式] [ORDER BY 排序字段 [ asc | desc ]]
  • 连接查询 （使用SELECT语句将多个表拼接起来，生成一个虚拟的表的过程就叫做表的连接，生成的虚拟表就叫做连接表，从连接表中进行的查询就是连接查询。连接查询可以找出多个表之间蕴涵的有用信息。）
    • 交叉连接查询的语句结构 SELECT * FROM 数据源列表； 交叉连接查询的特点：查询结果集数据量是k1 × k2行，存在很多无效数据。
    • 等值连接查询 (一般在创建连接时使用表关联的字段进行比较做连接条件。在连接条件中比较运算使用“=” 的查询叫做等值连接查询（最常用的多表查询）) (1)SELECT *|字段名列表 FROM 数据源列表 WHERE 连接条件 等值连接查询语法格式如下: (2)SELECT *|字段名列表 FROM 表1 ，表2 Where 表1.关联字段=表2.关联字段 (3)SELECT *|字段名列表 FROM 数据源列表 WHERE 连接条件 AND 业务条件
      • 内连接(INNER JOIN…ON)的语法格式如下： SELECT *|字段名列表 FROM 表1 INNER JOIN 表2 ON 连接条件 （1）等值连接内连接： SELECT *|字段名列表 FROM 表1 INNER JOIN 表2 ON 表1关联字段=表2关联字段
    • 自连接查询 查询语句如下： SELECT b.s_no 学号, b.s_name 姓名, b.s_sex 性别, b.s_speciality 专业, b.s_avgrade 平均成绩 FROM student AS a -- 为student创建别名a JOIN student AS b -- 为student创建别名b ON (a.s_name='刘洋' AND a.s_speciality = b.s_speciality);
    • 外连接查询(分左外连接和右外连接。)外连接查询的语法如下： SELECT 所查字段 FROM 表1 LEFT|RIGHT [OUTER] JOIN 表2 ON 表1.关系字段 = 表2.关系字段 WHERE 连接条件 如果在FROM子句中选择关键字LEFT，则该查询称为左外连接查询；如果选择关键字RIGHT，则该查询称为右外连接查询。
    • 复合条件连接查询 通过添加过滤条件来或其他子句（GROUP BY,ORDER BY,LIMIT）来限制查询结果的显示，使查询结果更加精确。
  • 嵌套查询:指一个查询语句嵌套在另一个查询语句的WHERE子句或HAVING的条件中的查询。嵌套查询把内层查询结果当作外层查询的条件
    • 嵌套查询的执行过程是由里向外。嵌套查询的优点是，每一层的查询都是一条简单的SELECT语句，其结构清晰，易于理解。
    • (1)带有IN谓词的子查询
    • （2）使用比较运算符的嵌套查询询 当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。
    • （3） 带ANY谓词的子查询 (ANY表示满足任意条件，ANY关键字允许使用比较运算符对子查询的返回结果进行比较，只要内层子查询结果中的任一个值满足比较条件就说明条件成立。) SELECT *|字段名列表 FROM 表名 WHERE 比较字段名 比较运算符 ANY (SELECT 比较字段名 FROM 表名);
    • （4 ）带ALL关键字的查询 (ALL表示满足所有条件，ALL关键字后面允许使用表达式对子查询的返回结果进行比较，要求同时满足内层子查询结果中的所有比较条件才说明条件成立。)语法格式如下: SELECT *|字段名列表 FROM 表名 WHERE 比较字段 ALL 比较运算符 (SELECT *|字段 FROM 表名);
    • （5）带有EXISTS谓词的子查询（相关子查询)带EXISTS的嵌套查询是先执行外层，后执行内层，再回到外层。
• SQL语句的操纵功能（DML）
  • 数据插入（INSERT语句）INSERT语句的语法格式为： INSERT [INTO] <table>[(<column1>[, <column2>…])] VALUES(<value1>[, <value2>…]); INSERT 语句的3个组成部分： 1、所要插入数据的表的名称； 2、表中需要使用的列的名称（可选）； 3、需要插入到列的值。
    • 1.为表中所有字段添加数据的INSERT语句有两种：方式一：INSERT语句中指定所有字段名 INSERT INTO 表名(字段名1，字段名2，……) VALUES(值1，值2，……); 字段名1表示数据表中的字段名称，此处必须列出表所有字段的名称;值1表示每个字段的值：每个值的顺序、类型必须与对应的字段相匹配 方式二：INSERT语句中不指定字段名 INSERT INTO 表名 VALUES(值1，值2，……); 值1：用于指定要添加的数据。需要注意的是，由于INSERT语句中没有指定字段名，添加的值的顺序必须和字段在表中定义的顺序相同。注意事项：1.如果表名后面没写字段名，则默认是向所有的字段添加值。使用该方式向所有字段插入数据时值的顺序必须与表定义时字段的顺序相同；2.使用省略列名插入一行数据，不能只插入半行数据。
    • 2.为表的指定字段添加数据 INSERT INTO 表名 [(字段名1，字段名2…字段名n)] VALUES (值1，值2…值n) 使用关键字INSERT插入数据时，可以允许用户省略部分字段，即只插入行的一部分。省略的字段必须满足如下某一个条件： 1.该列为自增字段；2.省略的列允许为NULL值；3.省略的列在表定义中已经给出默认值，不插入该列的数据时使用默认值填充。若不满足以上条件而省略字段时系统会报错。
    • 3. 同时添加多条记录 INSERT INTO 表名 [(字段名1，字段名2，……) ] VALUES(值1，值2，……)， (值1，值2，……)， … … (值1，值2，……);
  • 数据更新
    • 在SQL语句中，UPDATE语句提供了数据修改功能。 其语法格式如下： UPDATE <table> SET <column1> = <value1>[,<column2> = <value2>…] [WHERE <condition_expression>]; <table>表示要修改数据的表；关键字SET后面的column1, column2, …表示要修改的字段，value1, value2, …对应字段修改后的新值；condition_expression为一逻辑表达式，此处表示修改条件。 如果UPDATE语句不包含WHERE子句，则表示无条件对表中所有记录都进行修改（无条件修改）；如果包含了WHERE子句，那么只有对满足修改条件的记录进行修改（有条件修改）。
• SQL的数据查询功能（DQL）
  • 基本查询（不带任何子句的查询）指基于单表（一个数据表或视图）的仅仅由SELECT子句和FROM子句构成的SELECT语句。其一般格式如下： SELECT【ALL|TOP| DISTINCT 】 *|表名.*|字段名|... FROM 表名; SELECT语句的2个重要组成部分： 1、要显示的字段；2、数据源；3、查询条件
    • 1.选择所有字段 SELECT * FROM 表名
    • 2. 选择指定的若干字段 SELECT *|{字段名1, 字段名2, 字段名3,……} FROM 表名
    • 3. 构造计算字段 查询结果中的计算字段（列）是指根据数据表中的某一个或者若干个字段进行计算而得到的新字段，并把它放在查询结果集中，实际上在数据表中并不存在此字段。 例如：SELECT s_name 姓名, Year( getdate() )-Year(s_birthday) 年龄 FROM student;
    • 使用带AS关键字给字段取别名，AS后面跟由用户指定的字段标题（关键字AS可以省略）。 如：SELECT s_avgrade AS 平均成绩, s_name AS 姓名 FROM student;
  • 带DISTINCT的查询 使用SELECT查询时，SELECT后面可加上下字段关键字（ALL|TOP| DISTINCT ），以满足不同的查询要求。语法格式如下：SELECT DISTINCT 字段名 FROM 表名;
    • 注意：DISTINCT关键字可作用于多个字段 语法格式如下： SELECT DISTINCT 字段名1,字段名2,…… FROM 表名; 注：DISTINCT的作用范围是跟在它后面的所有的列， 即只有DISTINCT关键字后指定的多个字段值都相同，才会被认作是重复记录。
  • 带条件的查询 使用WHERE子句指定查询条件，语法格式如下：SELECT *|{字段名1, 字段名2, 字段名3,……} FROM 表名 WHERE 查询条件表达式
    • 注意：查询条件是一种逻辑表达式，就可以用一些逻辑联结联结词来构建这种表达式。常用联结词包括NOT、OR、AND等，分别表示逻辑意义上的“非”、“或”和“与”。 组合条件WHERE子句语法格式： SELECT 字段名 FROM 数据源 WHERE <条件1> AND/OR <条件2>[…..AND/OR <条件n>]
    • AND和OR一起使用计算次序时注意：小括号（）的优先级比AND、OR操作符高，执行WHERE子句时，先执行小括号的内容，任何使用AND和OR混合条件的语句都应该加上（）。
  • 带IN关键字的查询（集合过滤） 集合过滤即给定一个集合作为过滤条件，查询字段依次与集合内的元素进行比较，如果相等则查询成功，加入结果集。 使用的集合过滤关键字是IN / NOT IN。NOT是否定条件，用于即否定跟在它后面的条件，NOT IN表示不在某个集合范围内的。 SELECT *|字段名1,字段名2,…… FROM 表名 WHERE 字段名 [NOT] IN (元素1,元素2,……)
  • 带BETWEEN AND关键字的查询 BETWEEN AND关键字：用于判断某个字段的值是否在指定的范围之内，如果在则该字段所在记录将被查询出来，反之则不会被查询出来。语法格式如下: SELECT *|{字段名1,字段名2,……} FROM 表名 WHERE 字段名 [NOT] BETWEEN 值1 AND 值2
  • 空值查询 指查找指定字段的字段值为null的记录。 查找关键字是 IS NULL / IS NOT NULL,语法格式为SELECT *|字段名1,字段名2,…… FROM 表名 WHERE 字段名 IS [NOT] NULL 注意：IS NULL 不能用=等号代替！
  • 字符模糊--匹配带LIKE关键字的查询
    • LIKE+字段值 LIKE+字面值作为条件表示“查询字段=字面值” SELECT * FROM course WHERE cname LIKE（可以写为“="） “数据库原理”;
    • LIKE+字段值+通配符
      • 1.“%”：匹配任意数目字符（包括零个字符） 格式如：a%：a开头的所有字符串； %a：a结尾的所有字符串 a%b：a开头b结尾的字符串； %ab%：包含ab的字符串 例：查询读者名字最后一个字是‘志’字的读者信息。 SELECT * FROM student WHERE s_name LIKE “%志";
      • 2.”_”：匹配任何单个字符。格式如： a_：a开头的两个字符； _a：a结尾的两个字符 a_b：a开头b结尾的三个字符； _ab：ab结尾的三个字符等例：查询名字是三个字，且最后一个字为“华”的学生信息。SELECT * FROM readers WHERE readername LIKE ‘__华’;（注意：可以使用多个_下划线匹配多个连续的字符，但是下划线之间不能有空格）
  • 使用ORDER排序查询结果
    • 带ORDER BY子句的SELECT语句就可以实现对查询结果进行排序。其一般语法格式如下： SELECT column_expression FROM table_name表名 ORDER BY column_name字段名 [ASC|DESC][,…] column_name表示排序的依据字段，ASC表示按依据字段进行升序排列，DESC表示按依据字段进行降序排列。如果ASC和DESC都没有选择，则按依据字段进行升序排列，即ASC为默认值。
• SQL的数据定义功能（DDL）
  • 数据定义：指对数据库对象的定义、删除和修改操作。 数据定义功能通过CREATE、ALTER、DROP语句来完成 概念
  • 创建表
    • CREATE TABLE语句
      • CREATE TABLE table_name表名 (字段名 数据类型 约束条件）
      • 约束条件
      • 如创建学生表：CREATE TABLE student( s_no char(8) PRIMARY KEY, s_name char(8) NOT NULL, s_sex char(2) NOT NULL CHECK(s_sex='男'or s_sex='女'), s_binthday date, s_speciality varchar(50) DEFAULT '数媒', s_avgrade decimal(3,1) CHECK(s_avgrade>=0 and s_avgrade<=100), s_dept varchar(50) DEFAULT '高博', );
  • 删除表
    • DROP TABLE student（表名）; 当一个数据表被删除时，其中的数据也将全部 被删除。在使用删除语句的时候要特别慎重。
  • 修改表
    • （1）增加字段 ALTER TABLE 表名 Add 字段名 数据类型;
    • （2）删除字段 ALTER TABLE 表名 DROP COLUMN 字段名
    • （3）修改字段的数据类型 ALTER TABLE 表名 ; ALTER COLUMN 字段名 新的数据类型
    • （4）修改表名及字段名 EXEC sp_rename '表名','新字段名'; EXEC sp_rename '表名.字段名','新字段名’
    • 修改约束条件
      • 添加主键约束 alter table 表名 add constraint [约束名] primary key(字段名1 ,… ) 添加外键约束 alter table 表名 add constraint [约束名] foreign key 外键表(外键字段) references 主键表(主键字段); 添加检查约束 alter table 表名 add check (逻辑表达式）; 添加/删除非空约束 alter table 表名 alter column字段名 set/drop not null; 设置或删除缺省值 alter table 表名 alter column 字段名 set/drop default;
• SQL语言的数据类型
  • 字符串型
    • 数据类型如下：char(n) varchar(n) text nchar(n) nvarchar(n) ntext
  • 数值型
    • 数据类型如下：bit tinyint smallint int（或integer） bigint numeric(m,n) decimal(m,n) 精确型
    • 数据类型如下： real float 近似型
  • 日期时间型
    • 数据类型如下：date time(n) smalldatetime datetime datetime2(n) datetimeoffset(n) 日期时间型常量也要用引号引起来
  • 货币型
    • 数据类型如下： smallmoney money
  • 二进制型
    • 数据类型如下： binary(n) varbinary(n) image
  • 自定义数据类型
    • SQL概述
      • SQL语言的发展
        • SQL是Structured Query Language的缩写，译为“结构化查询语言”，它是关系数据库的标准语言。SQL是迄今为止最受欢迎的关系数据库语言之一。 SQL语言集数据查询、数据操纵、数据定义和数据控制功能于一体，是一种通用的、功能强大而又简单易学的关系数据库语言。
        • 按功能划分，SQL语言可以分为4类： 数据查询语言（Data Query Language, DQL） 数据定义语言（Data Definition Language, DDL） 数据操纵语言（Data Manipulation Language, DML） 数据控制语言（Data Control Language, DCL）
      • SQL语言的特点
        • （1）高度非过程化语言 （2）统一的数据库操作语言 （3）关系数据库的标准语言 （4）面向集合的数据操纵语言 （5）可嵌入式的数据库语言（6）简单易学