第四章 SQL语法分类
文章目录
一、数据查询
1、简单查询
-
日常查询中,最常用的是通过FROM子句实现的查询
-
语法格式:
SELECT[,…] FROM table_reference[,…]
-
使用方法:
- SELECT关键字之后和FROM子句之前出现的表达式称为SELECT项。SELECT项用于指定要查询的列,FROM指定要从哪个表中查询。如果要查询所有列,可以在SELECT后面使用*号,如果只查询特定的列,可以直接在SELECT后面指定列名,列名之间用逗号隔开。
-
示例:创建一个training表,并向表中插入三行数据后。查看training表的所有列
-
SELECT后面使用*号查询training表中的所有列
2、去除重复值
-
DISTINCT关键字
- 从SELECT的结果集中删除所有重复的行,使结果集中的每一行都是唯一的。取值范围:已存在的字段名,或字段表达式。
-
语法格式:
SELECT DISTINCT[,…] FROM table_reference[,…]
- 如果在DISTINCT关键字后只有一个列,则使用该列来计算重复,如果有两列或者多列,则将使用这些列的组合来进行重复检查。
-
示例:下表中是一个部门的员工信息,利用distinct关键字来查询员工的岗位和奖金,去除岗位和奖金相同的记录。
staff_id | name | job | bonus |
---|---|---|---|
30 | wangxin | developer | 9000 |
31 | xufeng | tester | 7000 |
34 | denggui | tester | 7000 |
35 | caoming | developer | 10000 |
37 | lixue | developer | 9000 |
3、查询列的选择
-
在选择查询列时,列名可以用下面几种形式表达:
-
手动输入列名,多个列之间用英文逗号隔开(,)分隔。
SELECT a, b, f1, f2 FROM t1, t2;
- 其中,列a、b是表t1中的列,f1、f2是表t2中的列。
-
可以是计算出来的字段
SELECT a+b FROM t1;
-
如果某两个或某几个表正好有一些共同的列名,推荐使用表名限定列名。不限定列名可以得到查询结果,但使用完全限定的表和列名称,可以减少数据库内部的处理工作量,从而提升查询的返回性能。例如:
SELLECT t1.f1 t2.f2 from t1, t2;
-
-
示例:查看training表中参与培训的员工编号及培训课程名。
-
示例:表名限定列名,查询学号sid为10的学生的数学成绩和英语成绩。
数学成绩表 math
sid score 10 95 11 87 12 99 英语成绩表 english
sid score 10 82 11 87 12 93
4、别名
- 通过使用子句AS some_name,可以为表名称或列名称指定另一个标题名显示,一般创建别名是为了让列名称的可读性更强。
- 语法格式
- 列和表的SQL别名分别跟在相应的列名和表名后面,中间可以加或不加一个“AS”关键字。
5、条件查询
-
在SELECT语句中,可以通过设置条件以达到更精确的查询。条件由表达式与操作符共同指定,且条件返回的值是TURE,FALSE或NULL。查询条件可以应用于WHERE句子,HAVING子句。
-
语法格式:
-
condition 子句
select_statement{predicate}[{AND|OR}condition] [,…n]
-
predicate 子句
-
-
查询条件由表达式和操作符共同定义。常用的条件定义方式如下:
- 比较操作符">, <, >=, <=, !=, <>, ="指定的比较查询条件。当查询条件中和数字比较,可以使用单引号引起,也可以不用,当和字符及日期类型的数据比较,则必须用单引号引起。
- 测试运算符指定的范围查询条件。如果希望返回的结果必须满足多个条件,可以使用AND逻辑操作符连接这些条件;如果希望返回的结果满足多个条件之一即可,可以使用OR逻辑操作符连接这些条件。
-
示例:使用比较操作符来指定查询条件,例如查询学习课程SQL majorization的人员信息。
-
逻辑操作符
-
常用的逻辑操作符有AND、OR和NOT,他们的运算结果有三个值,分别为TURE、FALSE和NULL,其中NULL代表未知。他们运算的优先级顺序为:NOT>AND>OR。
运算规则表
a b a AND b a OR b NOT a TURE TURE TURE TURE FALSE TURE FALSE FALSE TURE FALSE TURE NULL NULL TURE FALSE FALSE FALSE FALSE FALSE TRUE FALSE NULL FALSE NULL TRUE NULL NULL NULL NULL NULL
-
-
GaussDB(for MySQL)支持如下表的测试运算符:
运算符 描述 IN/NOT IN 元素在/不在指定的集合中 EXISTS/NOT EXISTS 存在/不存在符合条件的元素 ANY/SOME 存在一个满足条件。SOME是ANY的同义词 ALL 全部满足条件 BETWEEN…AND… 在两者之间,例如a BETWEEN x AND y等效于a>= x and a<= y。 IS NULL/IS NOT NULL 等于/不等于NULL LIKE/NOT LIKE 字符串模式匹配/不匹配 REGEXP 字符串与正则表达式相匹配 REGEXP_LIKE 字符串与正则表达式相匹配
6、join连接查询
-
实际应用中所需要的数据,经常会需要查询两个或两个以上的表。这种查询两个或两个以上数据表或视图的查询叫做连接查询。连接查询通常建立在存在相互关系的父子表之间
-
语法格式
-
当查询的FROM子句中出现多个表时,数据库就会执行连接
-
查询的SELECT列表可以是这些表中任意一些列。
SELECT table1.column,table2.column FROM table,table2;
-
大多数连接查询包含至少一个连接条件,连接条件可以在FROM子句中,也可以在WHERE子句中。
-
-
内连接:内连接的关键字为inner join,其中inner可以省略。使用内连接,连接执行顺序必然遵循语句中所写的表的顺序。
-
示例:查询员工ID、最高学历和考试分数。使用training和education两个相关的例(staff_id)做查询操作。
7、子查询
-
WITH AS子句
- 定义一个SQL片段,该SQL片段会被整个SQL语句用到。
-
语法格式
WITH {table_name AS select_statement1}[,…] select_statement2
- table_name
- 用户自定义的存储SQL片段的表名称
- select_statement1
- 从基本表中查询数据的SELECT语句
- select_statement2
- 从用户自定义的存储SQL片段的表中查询数据SELECT语句。
- table_name
-
子查询是指在查询、建表或插入语句的内部嵌入查询,以获得临时结果集。
- 子查询可以分为相关子查询和非相关子查询
- 子查询语法格式与普通查询相同
-
使用方法
- 子查询可以出现在FROM子句、WHERE子句、以及WITH AS子句中
- FROM子句中的子查询也称为内联视图
- WHERE子句中的子查询也称为嵌套子查询
-
示例:
8、合并结果集
-
除子查询外,还可以使用集合运算符处理多个查询的结果集,输出最终结果。
-
Union运算符:将多个查询块的结果集合并为一个结果集输出
select_statement UNION[ALL] select_subquery
-
-
使用方法
- 每个查询块的查询列数目必须相同。
- 每个查询块对应的查询列必须为相同数据类型或同一数据类型组
- 关键字ALL的意思是保持所有重复数据,而没有ALL的情况下表示删除所有重复数据
-
图解
9、差异结果集
10、数据分组
-
数据库查询中,分组是一个非常重要的应用。分组是指将数据表中的记录以某个或者某些列为标准,值相等的划分为一组。
-
语法格式
GROUP BY{ column_name } { ,…}
-
使用方法
- GROUP BY子句中的表达式可以包含FROM子句中表,视图的任何列,无论是这些列是否出现在SELECT列表中
- GROUP BY子句对行进行分组,但不保证结果集的顺序。要对分组进行排序,请使用ORDER BY子句
- GROUP BY后的表达式可以使用括号,如:group by(expr1, expr2),或者group by(expr1),(expr2)。但不支持group by(expr1, expr2),expr3 格式。
11、数据排序
-
ORDER BY子句
- 使用ORDER BY子句对查询语句返回的行根据指定的列进行排序。如果没有ORDER BY子句,则多次执行的同一查询将不一定以相同的顺序进行行的检索。
-
语法格式
ORDER BY{ column_name|number|expression} [ASC|DESC] [ ,…]
-
使用方法
- ORDER BY语句默认按照升序对记录进行排序。如果希望按照降序对记录进行排序,请使用DESC关键字。
- ASC默认为NULLS FIRST即默认NULL值为最小,NULL值在最前,DESC默认为NULLS LAST。
-
示例:
12、数据限制
-
数据限制功能包括两个独立的子句,LIMIT子句和OFFSET子句。
-
LIMIT子句允许限制查询返回的行。可以指定偏移量,以及要返回的行数或行百分比。可以使用此子句实现top—N报表,要获得一致的结果,请指定ORDER BY子句以确保确定性排序顺序。
LIMIT {count|ALL}
-
OFFSET 子句设置开始返回的位置。
OFFSET start
-
-
使用方法
- start:指定在返回行之前要跳过的行数
- count:指定要返回的最大行数
- start和count都被指定时,在开始计算要返回的count行之前会跳过start行
- LIMIT 5,20与LIMIT 20 OFFSET 5及OFFSET 5 LIMIT 20等效。
-
示例:
二、数据操作
1、数据插入
- 功能描述
- 在表中插入新的数据
- 注意事项
- 只有拥有表INSERT权限的用户,才可以向表中插入数据
- 如果使用RETURNING子句,用户必须要有该表的SELECT权限
- 如果使用query子句插入来自查询里的数据行,用户还需要拥有在查询里使用的表的SELECT权限。
- INSERT事务提交是默认开启的
- 语法格式
- INSERT语句有三种形式
- 值插入,构造一行记录并插入到表中
- 查询插入,通过SELECT子句返回的结果集构造一行或多行记录插入到表中
- 先插入记录,如果报主键冲突错误则执行UPDATE操作,更新指定字段值
- 示例:
2、数据修改
3、数据删除
三、数据定义
1、数据库对象
- 什么是数据库对象?
- 数据库对象是数据库的组成部分,数据库对象主要包含:表,索引,视图,存储过程,缺省值,规则,触发器,用户,函数等。
- 表
- 表是数据库中的一种特殊结构,用于存储数据对象以及对象之间的关系,由行和列组成的。
- 索引
- 索引是对数据库中一列或多列的值进行排序的一种结构,使用索引可以快速访问数据库表中的特定信息。
- 视图
- 视图是从一个或几个基本表中导出的虚表,可用于控制用户对数据的访问
- 存储过程
- 存储过程是一组为了完成特定功能的SQL语句的集合。一搬用于报表统计、数据迁移等
- 缺省值
- 缺省值是当前表中创建列或插入数据时,对没有指定其具体值的列或列数据项赋予事先设定好的值
- 规则
- 规则是对数据库表中数据的限制。它限定的是表的列
- 触发器
- 触发器是一种特殊类型的存储过程,通过指定的事件触发执行。一般用于数据审计、数据备份等
- 函数
- 函数是对一些业务逻辑的封装,以完成特定的功能。函数执行完成后会返回执行的结果
2、DDL分类
- DDL(Data Definition Language数据定义语言),用于定义或修改数据库中的对象,主要分为三种类型语句:CREATE、ALTER和DROP。
- CREATE用来创建数据库对象
- ALTER用来修改数据库对象的属性
- DROP则是用来删除数据库对象
3、定义表
-
表是数据库中的一种特殊结构,用于存储数据对象以及对象之间的关系。所涉及的SQL语句如下表所示
表的SQL
功能 | 相关SQL |
---|---|
创建表 | CREATE TABLE |
修改表属性 | ALTER TABLE |
删除表 | DROP TABLE |
删除表中所有数据 | TRUNCATE TABLE |
4、创建表
-
语法格式:
-
注意
- 创建当前用户的表,用户需要被授予CREATE TABLE系统权限
- 其中,表名、列名(数据类型)在创建表时必须指定
-
参数说明
-
创建临时表
-
示例:
5、修改表属性
-
ALTER TABLE功能指通过更改、添加、删除列和约束来更改表的定义,功能包括:
- 列的添加、删除、修改、重命名
- 约束的添加、删除
- 约束的启动和禁用
- 修改分区的名称
-
注意
- 增加表中的列属性时,保证表中无记录
- 修改表中的列属性时,保证表中数据类型不冲突,如有冲突需要将该列的值至NULL
-
语法格式
-
示例:
-
training表中添加列full_masks
ALTER TABLE training ADD full_masks INT;
-
删除course_period
ALTER TABLE training DROP course_period;
-
修改列的数据类型
ALTER TABLE training MODIFY course_name VARCHAR(60);
-
添加约束
-
6、删除表
-
注意
-
用户需要DROP TABLE权限,普通用户不可以删除系统用户对象
-
语法格式
DROP [TEMPORARY] TABLE [ IF EXISTS] [ database_name. ]table_name[RESTRICT|CASCADE]
-
-
参数说明
- CASCADE
- RESTRICT
- 此参数暂时无其他意义,为了移植语法。
-
示例
-
删除表Training
DROP TABLE IF EXISTS training;
-
删除临时表tmp_training
DROP TABLE IF EXISTS tmp_training;
-
7、定义索引
-
索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可以可快速访问数据库表中的特定信息。所涉及的SQL语句,如下表所示
索引的SQL
功能 相关SQL 创建索引 CREATE INDEX 修改索引属性 ALTER INDEX 删除索引 DROP INDEX -
索引按照索引列数分为单列索引和多列索引,按照索引使用方法可以分为普通索引、唯一索引、函数索引、分区索引、全文索引。
8、创建索引
- 功能描述
- 在指定的表上创建一索引。索引可以用来提高数据库查询性能,但是不恰当的使用将导致性能下降
- 注意
- 执行该语句的用户需要有INDEX权限,普通用户不可以创建系统用户对象
- 语法格式:
9、修改索引属性
- 语法格式:
- TLTER INDEX index_name{VISIBLE|INVISIBLE}
- VISIBLE|INVISIBLE
- 默认创建之后为可用状态。使用命令查看:show index from posts;
- 索引状态至为可用和不可用的状态
- RENAME INDEX old_name TO new_name
- 对索引进行重命名
- 示例:
- 创建索引
- 将索引状态置为可用
- 重命名索引
10、删除索引
- 语法格式
- 参数说明
- 待删除索引名
- 删除索引时指定表明
- 示例
11、定义视图
-
视图是从一个或几个基本表中导出的需表,可用于控制用户对数据访问,所涉及的SQL语句,如下表所示。
视图的SQL
功能 相关SQL 创建视图 CREATE VIEW 删除视图 DROP VIEW -
视图与基本表不同,数据库中仅存视图的定义,而不存放视图所对应的数据,这些数据仍存放在原来的基本表中。若基本表中的数据发生变化,从视图中查询的数据也随之改变。从这个意义上讲,视图就像一个窗口,透过它可以看到数据库中用户感兴趣的数据及变化。
12、创建视图
-
语法格式
CREATE[ OR REPLACE ] VIEW view_name AS subquery
-
参数说明
- [OR REPLACE]
- 创建视图时,若视图存在则更新
- view_name
- 视图名
- AS subquery
- 子查询
- [OR REPLACE]
-
示例:
13、删除视图
-
语法格式
- DROP VIEW [ IF EXISTS ] view_name
-
参数说明
- IF EXISTS
- 视图存在则执行删除
- view_name
- 待删除的视图名
- IF EXISTS
-
示例:
DRPOP VIEW IF EXISTS training_view;
四、数据控制
1、事务控制
-
事务是用户定义的一个数据库操作系列,这些操作要么全做,要么全不做,是一个不可分割的工作单位。
-
事务控制提供了事务的启动、提交、两阶段提交准备、回滚、设置隔离级别操作,并支持在事务中创建保存点。
事务控制
功能 相关SQL 提交事务 COMMIT 回滚事务 ROLLBACK -
GaussDB(for MySQL)没有提供显示定义事务开始的语句,第一个可执行SQL(除登录语句外)隐含事务的开始。
2、提交事务
-
功能描述
- 该语句使用当前事务工作单元中的所有操作"永久化",并结束该事务
-
语法格式
COMMIT;
-
示例:
3、回滚事务
-
功能描述
- 该语句回滚(废除)当前事务工作单元中的所有操作,并结束该事务。
-
语法格式:
ROLLBACK[TO SAVEPOINT savepoint_name]
-
示例:创建表posts,插入数据,回滚所有操作并结束事务。
4、事务保存点
-
功能描述
- SAVEPOINT语句用于在事务中设置保存点
- 保存点提供了一种灵活的回滚,事务在执行中可以回滚到某个保存点。在该保存点以前的操作有效,而以后的操作被回滚掉。一个事务中可以设置多个保存点。
-
语法格式:
SAVEPOINT savepoint_name
-
示例:回滚事务到保存点
- 创建表bonus_2019
- 向表bonus_2019中插入记录1
- 设置保存点s1
- 向bonus_2019中插入记录2
- 设置保存点s2
-
示例:
五、其他
1、SHOW命令
-
功能描述
- 该语句有许多形式,可以提供有关数据库、表、列和有关服务器状态等信息
-
语法格式
-
示例:显示实例下的数据库
SHOW DATABASSES;
-
示例:显示数据库中的表
- 创建表bonus_2019
- 查看数据库中的表
- 结果为:
-
示例:查看bonus_2019表的建表语句
SHOW CREATE bonus_2019;
- 结果为:
2、SET命令
- 功能描述
- 该语句使用户可以将值分配给不同的变量、服务器或客户端的操作
- 语法格式
- 示例
- 将变量name值设置为43
- 将全局参数max_connections设置为1000
- 将当前会话的sql_mode值设置为TRADITIONAL(只影响当前会话)