数据库复习之（三）-- 查询

查询

1. 单表查询

1.1基本数据查询

• 查询所有字段
  select * from 表名; #效率低，尽量减少使用
• 查询指定字段数据
  select 指定的字段名, 指定的字段名，…… from 表名;
• 避免重复数据查询
  select distinct 字段名, 字段名，…… from 表名;
• 设置显示格式数据查询
  select concat(name,'学生的总分是:',chinese+english+math+chinese+physics) from s_score; #concat() 是系统函数，连接字符串

1.2 条件查询

Mysql中，通过关键字WHERE对所查询到的数据进行过滤，条件查询语法如下：
select field1,field2,…… from tablename where condition;
condition 查询条件种类：

查询条件	符号或关键字
比较	=,<,<=<>,>=,!=,<>,!>,!<
指定范围	between and，not between and
指定集合	in，not in
匹配字符	like,not like
是否为空	is null,is not null
多个查询条件	and，or

• 对查询结果进行排序(ORDER BY)
  select field1,field2,…… from tablename order by fieldm [ASC|DESC] [, order by fieldm [ASC|DESC]];

1.3 统计函数和分组查询

mysql支持的统计函数：

• count() :统计表中记录的条数 count(*)：表中记录进行统计，不管字段中包含的是null还不是null；count(field)：对指定字段记录进行统计，忽略null值
• AVG() :计算字段值的平均值
• SUM() :计算字段值的总和
• MAX()：查询字段的最大值
• MIN()：查询字段的最小值
• 简单的分组查询
  select * from tablename group by field; # 按照field属性进行分组，且默认查询出每个分组中的随机一条记录，具有很大不确定性，因此常于统计函数一起使用
    - group_concat() ：显示每个分组中的指定字段
      select subject，group_concat(name) name，count(name) number from tablename group by subject; # 会根据 sudject 分组，然后显示每个sudject 下所有教师的名字，count(name) 会统计显示有几个教师
• 多个字段分组查询
  select * from tablename group by field1,field2,……,fieldn; # 先按照field1 进行分组，在对每个分组按照filed2分组，以此类推
• HAVING 子句限定分组查询
  通过HAVING关键字来指定分组后的条件。
  select * from tablename where condition group by field1,field2,……,fieldn having condition ;
• limit 限制数据记录的查询数量

   select field1,field2,……,fieldn from tablename
   where condition limit [offset_start,]row_count;

    关键字limit来限制查询结果数量，其中offset_start表示查询的起始偏移，默认为0，row_count表示显示行数。

• 正则表达式查询
  • REGEXP 运算符
    正则表达式通过模式去匹配一类字符串，MySQL支持的模式字符如下：

模式字符	含义
^	匹配字符串的开始部分
$	匹配字符串的结束部分
.	匹配字符串的任意一个字符
[字符集合]	匹配字符集合中的任意一个字符
[^字符集合]	匹配字符集合外的任意一个字符
str1 |str2 |str3	匹配str1、str2、str3中的任意一个字符串
*	零个或多个前面字符
+	至少一次匹配前面的字符
字符串{N}	字符串出现n次
字符串{M,N}	字符串至少出现M次，最多N次v

2. 多表查询

2.1 内连接查询（INNER JOIN …ON）

• 自连接
• 等值连接
• 不等连接

1. 语法格式

   select field1,field2,...,fieldn from tablename1
    	inner join tablename2  on condition [ ,inner join tablenamen on conidition];
   

2. 自连接
   自连接是特殊的等值连接，所谓的自连接就是指表与其自身进行连接
   例：`查询学生‘Alicia Florric’所在班级的其他学生，SQL语句如下：

   # 方法1：where 设置匹配 条件
   select t.stuid,t.name,t.classno  
    	from student as  t ,student as t2
    	where t.classno = t2.classno and t2.name = 'Alicia Florric';
    	#方法2：ANSI连接语法
    	select t.stuid,t.name,t.classno  
    		from student as  t inner join student t2
    		on t.classno = t2.classno and t2.name = 'Alicia Florric';  
   

3. 等值连接
   内连接查询中的等值连接，就是在关键字ON后的匹配条件中通过等于关系运算符（=）来实现等值条件
   例：执行SQL语句‘INNER JOIN ...ON’，在数据库school中，查询每个学生的编号，姓名，年龄，班级号，班级名称，班级位置，和班主任信息。

   #方式1：where 设置匹配 条件
   select s.stuid,s.name,s.gender,s.age,s.classno,c.cname,c.loc,c.advisor
   	from student s,class c
   where s.classno = c.classno;
   #方法2：ANSI连接语法
   select s.stuid,s.name,s.gender,s.age,s.classno,c.cname,c.loc,c.advisor
   	from student s inner class  c
   	on s.classno = c.classno;
   

4. 不等连接
   内连接查询中的不等连接，就是在关键字ON后的匹配条件中除了等于关系运算符来实现的不等条件，可以使用的关系运算符包含“>,>=,<,<=,!=”等。

2.2 外连接查询（OUTER JOIN …ON）

Mysql 中外连接查询会返回所操作表中至少一个表的所有数据记录。语法如下：

select field1,field2,...fieldn 
	from tablename1 left|right|full [outer] join tablename2
		on condition;

• 左外连接
• 右外连接
• 全外连接

1. 左外连接
   外连接查询中的左外连接，就是指新关系中执行匹配条件时，以关键字left outer join左边的表为参考。左连接的结果包括left outer 子句中指定的左表的所有行，而不仅仅是连接列所匹配的行，如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表选择列表均为空值。
2. 右外连接
   外连接查询中的右外连接，就是指新关系中执行匹配条件时，以关键字right outer join右边的表为参考。右连接的结果包括right outer 子句中指定的右表的所有行，而不仅仅是连接列所匹配的行，如果右表的某行在左表中没有匹配行，则在相关联的结果行中，左表选择列表均为空值。

• 合并查询数据记录
  UNION来实现并操作，将多个select语句查询结果合并到一起，语法如下：

select field1,field2,...fieldn 
	from tablename1
union | union all                   #union 查询结果会去掉重复值，而union all 不去重
select field1,field2,...fieldn 
	from tablename2
union | union all
select field1,field2,...fieldn 
	from tablename1
union | union all
select field1,field2,...fieldn 
	from tablename3
...

2.3 子查询

子查询是将一个查询嵌套到另一个查询语句中，内层查询结果，可以作为外层查询语句提供查询条件。子查询可以分为以下几类：

• 带比较运算符的子查询
• 带关键字IN的子查询
• 带关键字EXISTS的子查询
• 带关键字ANY的子查询
• 带关键字ALL的子查询

2.3.1 为什么使用子查询

这个问题需要层多表查询数据记录来说，例如：需要查询部门表t_dept和员工表t_employee的数据记录。
直接执行如下的sql语句进行查询：
select * from t_dept dept,t_employee empl where dept.deptno = empl.deptno;
     上述SQL语句在执行过程中，首先会对两个表进行笛卡尔积操作，然后再选取符合匹配条件的数据记录。进行笛卡尔积操作时，会生成两个表数据记录的乘积的数据记录，如果这俩个表的数据记录比较大，则在进行笛卡尔积操作时会造成死机。
     对于有经验的用户，首先会通过统计函数查看操作笛卡尔积后的数据记录数，然后在进行多表查询，如果查询到笛卡尔积后的数据记录数远远大于MySql可以接受的范围，否则，使用子查询来实现多表查询。

2.3.2 带比较运算符的子查询

例： 在数据库compay 中，查询薪资水平为高级的员工的编号、姓名、性别、年龄和工资，具体步骤如下：

1. 创建数据库，并使用该数据库
   create database company;
use company;
2. 创建t_employee表和t_slevel表，并查看创建成功

   CREATE TABLE t_employee
   (
   id INT(4),
   name VARCHAR(20),
   gender VARCHAR(6),
   age INT(4),
   salary INT(6),
   deptno INT(4)
   );
   
   describe t_employee;
   
   CREATE TABLE t_slevel
   (         
   id INT(4),             
   salary INT(6),         
   level INT(4),          
   description VARCHAR(20)
   ); 
                          
   DESCRIBE t_slevel;
   

3. 插入数据

   INSERT INTO t_employee(id,name,gender,age,salary,deptno)
   VALUES(1001,'Alicia Florric','Female',33,10000,1),
        (1002,'Kalinda Sharma','Female',31,9000,1),
        (1003,'Cary Agos','Male',27,8000,1),
        (1004,'Eli Gold','Male',44,20000,2),
        (1005,'Peter Florric','Male',34,30000,2),
        (1006,'Diane Lockhart','Female',43,50000,3),
        (1007,'Maia Rindell','Female',43,9000,3),
        (1008,'Will Gardner','Male',36,50000,3),
        (1009,'Jacquiline Florric','Female',57,9000,4),
        (1010,'Zach Florric','Female',17,5000,5),
        (1011,'Grace Florric','Female',14,4000,5);
      
   INSERT INTO t_slevel(id,salary,level,description)
   VALUES(1,3000,1,'初级'),                    
        (2,7000,2,'中级'),                   
        (3,10000,3,'高级'),                  
        (4,20000,4,'特级'),                  
        (5,30000,5,'高管');            
   

4. 查询薪资水平为高级的所有员工的编号、姓名、年龄和工资。
   select * from t_employee where salary >= (select salary from t_slevel where id = 3) and salary < (select salary from t_slevel where id = 4)

2.3.3 带关键字IN的子查询

一个查询语句的条件可能落在另一个SELECT语句的查询结果中，这可以通过IN关键字来判断。

• 例1：
  select * from t_employee where deptno IN (select deptno from t_dept);
• 例2：
  select * from t_employee where deptno NOT IN (select deptno from t_dept);

2.3.4 带关键字EXISTS的子查询

关键字EXISTS表示存在，后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时进行外层语句进行查询；如果子查询没有返回任何行，那么EXISTS的返回结果为false，此时外层语句将不进行查询。

• 例1：查询数据库company的表t_dept中是否存在deptno为4的部门，如果存在，在查询t_employee的记录。

  select * from t_employee 
      where  EXISTS (select deptname from t_dept where deptno = 4);
  

• 例2：查询数据库company的表t_dept中是否存在deptno为4的部门，如果存在，在查询表t_employee中字段age大于40的数据记录。

  select * from t_employee 
      where  age>40 and EXISTS (select deptname from t_dept where deptno = 4);
  

2.3.5 带关键字ANY的子查询

关键字any表示满足其中任何一个条件。使用关键字ANY时，只要满足内层查询语句返回的结果中的任何一个，就可以通过该条件来执行外层查询语句。

2.3.5 带关键字ALL的子查询

关键字ALL表示满足所有条件。使用关键字ALL时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。

AS子句作用（别名显示）

1. 可以给数据列取一个新的别名
2. 可以给表取一个新别名
3. 可把经计算或总结的结果用另外一个新名称来代替

如：

1. 查询结果以别名显示
   select 列名1 [as] 别名1[,列名2 [as] 别名2,列名3 [as] 别名3 ,....] from 表名;
2. select a.studentno from student [as] a;
3. select Phone + 1 [as] tel from student;

注：

• AS可省略不写

DISTINCT关键字（过滤重复）

• 作用：
    去掉SELECT查询返回的记录结果中重复的记录
• 语法：
    SELECT DISTINCT 列名1，列名2，… FROM 表名
• 注：
    ALL关键字是默认的，返回所有的记录

查询中的表达式

select version();
select now() as ‘当前时间’；
select concat(‘每个同事的邮箱：’,email,’@163.com’) as Email from student;

查询中的符号

• ‘%’ 不限个数的字符
  select * from student where studentname like '刘%'; # 所有名字以刘开头的同学信息将被显示
• ‘_’ 单字符的通配符
  select * from student where studentname like '刘_'; # 所有名字以刘开头,且姓名为2个字的同学信息将被显示
select * from student where studentname like '刘__'; # 所有名字以刘开头,且姓名为3个字的同学信息将被显示
• in
  `select * from student where gradeid in (2,3); # gradeid是 2或3的
• between and 判断操作数是否在某个范围内
  select 27 between 8 and 30; #是返回1，否则返回0