1.简单查询

• 日常查询中，最常用的就是通过from子句实现的查询。
• 语法格式：

select [,...] from table_reference [,...]

• 使用方法：
  • select关键字之后和from子句之前出现的表达式称为select项。select项用于指定要查询的列，from指定要从哪个表中查询。如果要查询所有列，可以在select后面使用*号，如果只查询指定的列，可以直接在select后面制定列名，列名之间用逗号隔开。
• 示例：创建一个training表，并向表中插入三行数据后。查看training表的所有列。

-- 创建training表
create table `training`(staff_id int not null,course_name char(50),exam_date datetime,score int);
-- 向表中插入三行数据
insert into `training`(staff_id, course_name, exam_date, score) values(10,'class1','2022-08-04 06:36:00',90);
insert into `training`(staff_id, course_name, exam_date, score) values(10,'class2','2022-08-04 06:36:00',95);
insert into `training`(staff_id, course_name, exam_date, score) values(10,'class3','2022-08-04 06:36:00',97);

• select后面使用*号查询training表中的所有列

select * from training;

10,class1,2022-08-04 06:36:00,90
10,class2,2022-08-04 06:36:00,95
10,class3,2022-08-04 06:36:00,97

2.去除重复值

• distinct关键字

  • 从select的结果集中删除所有重复的行，使结果集中的每行都是唯一的。取值范围为：已存在的字段名，或字段表达式。

• 语法格式

  •   select distinct[,...] from table_reference[,...]
    

  • 如果在distinct关键字后只有一个列，则使用该列来计算重复，如果有两列或者多列，则将使用这些列的组合来进行重复检查。

• 示例：下表中是一个部门的员工信息，利用distinct关键字来查询员工的岗位和奖金，去除岗位和奖金相同的记录。

  drop table if exists `sections`;
  create table `sections`(
      `staff_id` int,
      `name` varchar(50),
      `job` varchar(50),
      `bonus` int
  )engine =InnoDB,charset =utf8mb4;
  
  insert into `sections`
  values (30,'wangxin','developer',9000),
      (31,'xufeng','tester',7000),
      (34,'denggui','tester',7000),
      (35,'caoming','developer',10000),
      (37,'lixue','developer',9000);
  

  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

  select distinct job,bonus from sections;
  
  developer,9000
  tester,7000
  developer,10000
  

3.查询列的选择

• 在选择查询列时，列名可以用下面几种形式表达：
  • 手动输入列名，多个列之间用英文逗号（，）隔开。

    select a,b,f1,f2 from t1,t2;
    -- 列a、b是表t1中的列，f1、f2是表t2中的列。
    

  • 可以是计算出来的字段。

    select a+b from t1;
    

  • 如果某两个或某几个表正好有一些共同的列名，推荐使用表名限定列名。不限定列名可以得到查询结果，但使用完全限定的表和列名称，可以减少数据库内部的处理工作量，从而提升查询的返回性能。例如：

    select t1.f1,t2.f2 from t1,t2;
    

• 示例：查看training表中参与培训的员工编号及培训课程名。

  select staff_id,course_name from training;
  
  10,class1
  10,class2
  10,class3
  

• 示例：表名限定列名，查询学号sid为10的学生的数学成绩和英语成绩。

  drop table if exists `math`;
  create table `math`(
      `sid` int,
      `score` int
  )engine=InnoDB,charset=utf8mb4;
  
  insert into `math` values (10,95),(11,87),(12,99);
  
  drop table if exists `english`;
  create table `english`(
      `sid` int,
      `score` int
  )engine=InnoDB,charset=utf8mb4;
  
  insert into `english` values (10,82),(11,87),(12,93);
  

  select math.sid as sid,math.score as math,english.score as english
  from math,english
  where math.sid=10 and english.sid=10;
  

4.别名

• 通过使用子句as some_name，可以为表名称或列名称指定另一个标题名显示，一般创建别名是为了让列名称的可读性更强。
• 语法格式：
  • 列和表的SQL别名分别跟在相应的列名和表名后面，中间可以加或不加一个as关键字。
• 示例：别名使用：

  select `staff_id` as empno,`course_name` from training;
  

  select a.sid,a.score math,b.score english from math a,english b where a.sid=10 and b.sid=10;
  

5.条件查询

• 在select语句中，可以通过设置条件以达到更精确的查询。条件由表达式与操作符共同指定，且条件返回的值是true，false或者null。查询条件可以应用于where子句，having子句。

• 语法格式：

  • condition子句

    select_statement {predicate} [ {AND | OR} condition ] [,...n]
    

  • predicate子句

    {
        expression { = | <> | != | > | >= | < | <= } {ALL | ANY} expression | (select)
        | string_expression [NOT] LIKE string_expression
        | expression [NOT] between expression and expression
        | expression is [not] null
        | expression [not] in (select | expression [,...n])
        | [not] exists (select)
    }
    

• 查询条件由表达式和操作符共同定义。常用的条件定义方式如下：

  • 比较操作符">,<,>=,<=,!=,<>,="指定的比较查询条件。当查询条件中和数字比较，可以使用单引号引起，也可以不用，当和字符及日期类型的数据比较，则必须用单引号引起来。
  • 测试运算符指定的范围查询条件。如果希望返回的结果必须满足多个条件，可以使用and逻辑操作符连接这些条件；如果希望返回的结果满足多个条件之一即可，可以使用or逻辑操作符连接这些条件。

• 示例：使用比较操作符来指定查询条件，例如查询学习课程class1的人员信息。

  select * from training where course_name='class1';
  
  10,class1,2022-08-04 06:36:00,90
  

• 逻辑操作符

  • 常用的逻辑操作符有and、or和not，他们的运算结果有三个值，分别为true、false和null，其中null代表未知。他们运算优先级顺序为：not>and>or。

    a	b	a and b	a or b	not a
    true	true	true	true	false
    true	false	false	true	false
    true	null	null	true	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	字符串与正则表达式匹配

  staff_id	name	job	bonus
  30	wangxin	developer	9000
  31	xufeng	document developer	7000
  37	liming	developer	8000
  39	wanghua	tester	8000

• 示例：从上表bonuses_depa中查询岗位为developer，且奖金>8000的职员信息。

  drop table if exists `bonuses_depa`;
  
  create table `bonuses_depa`(
      `staff_id` int,
      `name` varchar(50),
      `job` varchar(50),
      `bonus` int
  )engine=InnoDB,charset=utf8mb4;
  

  insert into `bonuses_depa` values (30,'wangxin','developer',9000),
                                (31,'xufeng','docuemnt developer',7000),
                                (37,'liming','developer',8000),
                                (39,'wanghua','tester',8000);
  

  select *
  from bonuses_depa
  where job='developer' and bonus>8000;
  
  30,wangxin,developer,9000
  

• 示例：从上表bonuses_depa中查询姓wang，且奖金8500-9500的职员信息。

  select *
  from bonuses_depa
  where name like 'wang%' and bonus between 8500 and 9500;
  
  30,wangxin,developer,9000
  

6.join连接查询

• 实际应用中所需要的数据，经常会需要查询两个或两个以上的表。这种查询两个或两个以上数据表或视图的查询叫做连接查询。连接查询通常建立在存在相互关系的父子表之间。
• 语法格式：

  select [,...] from table_reference
      [left [outer] | right [outer] | inner]
      join table_reference
      [on {predicate} [{and | or} condition [,...n]]]
  

  table_reference子句

  {
      table_name [[as] alias]
      | view_name [[as] alias]
      | (select query) [[as] alias]
  }
  

• 当查询的from子句中出现多个表时，数据库就会执行连接。
  • 查询的select列表可以是这些表中任意一些列。

    select table1.column,table2.column from table1,table2;
    

  • 大多数连接查询包含至少一个连接条件，连接条件可以在from子句中也可以在where子句中。

    select table1.column,table2.column from table1 join table2 on (table1.column1=table2.column2);
    select table1.column,table2.column from table1,table2 where table1.column1=table2.column2;
    

• 内连接：内连接的关键字为inner join，其中inner可以省略。使用内连接，连接执行顺序必须遵循语句中所写的表的顺序。
• 示例：查询员工ID、最高学历和考试分数。使用training和education两个相关的列（staff_id）做查询操作。

  select * from training;
  
  10,class1,2022-08-04 06:36:00,90
  10,class2,2022-08-04 06:36:00,95
  10,class3,2022-08-04 06:36:00,97
  

  drop table if exists `education`;
  create table `education`(
      `staff_id` int,
      `highest_degree` varchar(50),
      `graduate_school` varchar(50),
      `education_note` varchar(20)
  )engine=InnoDB,charset=utf8mb4;
  
  insert into education values (11,'master','northwestern polytechnical university','211&985'),
                           (12,'doctor','peking university','211&985'),
                           (13,'scholar','peking unversity','211&985');
  

  select * from education;
  
  11,master,northwestern polytechnical university,211&985
  12,doctor,peking university,211&985
  13,scholar,peking unversity,211&985
  

  select e.staff_id,e.highest_degree,t.score from education e join training t on (e.staff_id=t.staff_id);
  
  11,master,95
  12,doctor,97
  

• 外连接
  • 内连接所指定的两个数据源处于平等的地位。而外连接不同，外连接以一个数据源为基础，将另外一个数据源与之进行条件匹配。
  • 内连接返回两个表中所有满足连接条件的数据记录。外连接不仅返回满足连接条件的记录，还将返回不满足连接条件的记录。
  • 外连接又分为左外连接、右外连接和全外连接。
• 左外连接
  • 又称左连接，是指以左边的表为基础表进行查询。
  • 根据指定连接条件关联右表，获取基础表以及和条件匹配的右表数据，未匹配条件的右表对应的字段位置填上null。
  • 示例：

    select * from education;
    
    11,master,northwestern polytechnical university,211&985
    12,doctor,peking university,211&985
    13,scholar,peking unversity,211&985
    

    select * from training;
    
    10,class1,2022-08-04 06:36:00,90
    11,class2,2022-08-04 06:36:00,95
    12,class3,2022-08-04 06:36:00,97
    

    select e.staff_id,e.highest_degree,t.score from education e left join training t on e.staff_id = t.staff_id;
    
    11,master,95
    12,doctor,97
    13,scholar,
    

• 右外连接
  • 又称右连接，是指以右边的表为基础表，在内连接的基础上也查询右边表中所有记录，而左边的表中没有记录的数据（左边用null值填充）。
  • 示例：

    select e.staff_id,e.highest_degree,t.score from education e right join training t on e.staff_id = t.staff_id;
    
    ,,90
    11,master,95
    12,doctor,97
    

• 半连接
  • 半连接（Semi join）是一种特殊的连接类型，在SQL中没有指定的关键字，通过在where后面使用in或exists子查询实现。当in/exists右侧的多行满足子查询的条件时，著查询也只返回一行与in/exists子查询匹配的行，而不是复制左侧的行。
  • 示例：查询参加培训的员工的教育信息。即使training表中的许多行可能与子查询匹配（即同一个staff_id下有多个职员），也只需要从表training返回一行。

    select staff_id, highest_degree, graduate_school, education_note from education where exists(select * from training where education.staff_id=training.staff_id);
    
    11,master,northwestern polytechnical university,211&985
    12,doctor,peking university,211&985
    

• 反连接
  • 反连接（Anti join）是一种特殊的连接类型，在SQL中没有指定的关键字，通过在where后面使用not in或not exists子查询实现。返回所有不满足条件的行。这个关系的概念跟半连接相反。
  • 示例：查询没有参加培训的员工的教育信息。

    select staff_id, highest_degree, graduate_school, education_note from education where staff_id not in (select staff_id from training);
    
    13,scholar,peking unversity,211&985
    

7.子查询

• with as子句
  • 定义一个sql片段，该sql片段会被整个sql语句用到。
• 语法格式

  with { table_name as select_statement1 }[,...] select_statement2
  

  • table_name
    • 用户自定义的存储sql片段的表的名称。
  • select_statement1
    • 从基本表中查询数据的select语句。
  • select_statement2
    • 从用户自定义的存储sql片段的表中查询数据的select语句。
• 子查询是指在查询、建表或插入语句的内部嵌入查询，以获得临时结果集。
  • 子查询可以分为相关子查询和非相关子查询。
  • 子查询的语法格式与普通查询相同。
• 使用方法
  • 子查询可以出现的from子句、where子句、with as子句中。
  • from子句中的子查询也称为内联视图。
  • where子句中的子查询也称为嵌套子查询。
• 示例：通过相关子查询，查找每个部门中高出部门平均工资的人员。

  select s1.last_name,s1.section_id,s1.salary
  from staffs s1
  where salary > (select avg(salary) from staffs s2 where s1.section_id=s1.section_id)
  order by s1.section_id;
  

• 对于staffs表的每一行，父查询使用相关子查询来计算同一部门成员的平均工资。相关子查询为staffs表的每一行执行以下步骤：
  • 确定行的section_id。
  • 然后使用section_id来评估父查询。
  • 如果此行中工资大于所在部门的平均工资，则返回该行。
  • 对于staffs表的每一行，子查询都将被计算一次。
• 示例：
  • with子查询：查询培训过big data课程的员工信息

    with bigdata_staffs as ( select staff_id,exam_date from training where course_name='big data')select * from bigdata_staffs;
    
    10,2022-08-04 06:36:00
    

  • 通过子查询建立一个和表training具有相同表结构的表。

    create table training_new as select * from training where 1<>1;
    

  • 通过子查询向表training_new表中插入training表的所有数据。

    insert training_new select * from training;
    

8.合并结果集

• 除子查询外，还可以使用集合运算符处理多个查询的结果集，输出最终结果。

  • union运算符：将多个查询块的结果集合并为一个结果集输出。

    select_statement union [all] select_subquery
    

• 使用方法

  • 每个查询块的查询列数目必须相同。
  • 每个查询块对应的查询列必须为相同数据类型或同一数据类型组。
  • 关键字all的意思是保持所有重复数据，而没有all的情况下表示删除所有重复数据。

• 图解
  

• 示例：现有两个部门的员工信息，查询获得奖金超过7000的员工信息。

  bonuses_depa1

  staff_id	name	job	bonus
  23	wangxia	developer	5000
  24	limingying	tester	7000
  25	liulili	quality control	8000
  29	liuxue	tester	9000

  bonuses_depa2

  staff_id	name	job	bonus
  30	wangxin	developer	9000
  31	xufeng	document developer	6000
  34	denggui	quality control	5000
  35	caoming	tester	10000

  select *
  from bonuses_depa1
  where bonus>7000
  union all
  select *
  from bonuses_depa2
  where bonus>7000;
  

9.差异结果集

• 图解
  

• 示例：使用not in进行查询数据。

  select * from education where staff_id not in (select staff_id from education_disable where staff_id=13);
  

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格式。

• 示例：该部门按照岗位和奖金分组，查询每组员工数，结果按人数升序排序。

  staff_id	name	job	bonus
  30	wangxin	developer	9000
  31	xufeng	tester	7000
  37	lixue	developer	9000
  39	chengjing	developer	9000
  34	denggui	tester	7000
  35	caoming	developer	10000

  select job,bonus,count(staff_id) sum
  from bonuses_depa
  group by job, bonus
  order by sum;
  

• having子句

  • 与group by子句配合用来选择特殊的组。having子句将组的一些属性与一个常数值比较，只有满足having子句中条件的组才会被提取出来。

• 语法格式

  having condition [,...]
  

• 示例：查询表bonuses_depa中岗位人数大于3的各岗位员工总数

  select job,count(staff_id) from bonuses_depa group by job having count(staff_id)>3;
  
  developer,4
  

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。

• 示例：查询下表bonuses_depa中各工种的奖金信息，查询结果先按bonus升序排列，然后按照name降序排列。

  staff_id	name	job	bonus
  30	wangxin	developer	9000
  31	xufeng	document developer	6000
  34	denggui	quality control	9000
  35	caoming	tester	10000

  select * from bonuses_depa order by bonus, name desc ;
  
  31,xufeng,document developer,6000
  30,wangxin,developer,9000
  34,denggui,quality control,9000
  35,caoming,tester,10000
  

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等效。

• 示例：查询下表bonuses_depa中的员工信息。通过增加limit 2 offset 1限定查询时跳过前1行后，查询总共2行的数据。

  staff_id	name	job	bonus
  30	wangxin	developer	9000
  31	xufeng	document developer	6000
  34	denggui	quality control	9000
  35	caoming	tester	10000

  select * from bonuses_depa limit 2 offset 1;
  
  31,xufeng,document developer,6000
  34,denggui,quality control,9000
  

13.思考题

• 查找岗位是工程师且薪水在6000以上的记录，逻辑表达式为：岗位=‘工程师’ and 薪水>6000
• where子句中，表达式"age between 20 and 30"等同于：age>=20 and age<=30
• 从student表中查询学生姓名、年龄和成绩，结果按照年龄降序排序，年龄相同的按照成绩升序排序：select name,age,score from student order by age desc,score;