十六、mysql中对于数据表中记录/数据的一些中级操作二

1、数据源

数据源定义及分类

数据源即指数据的来源，在select语句中体现的就是from后面的部分；在关系型数据库中，其数据源就是数据表，本质上只要保证数据类似二维表，最终都可以作为数据源；数据源分为多种：单表数据源、多表数据源、查询语句。

各种数据源的示例及语法

1、单表数据源：select * from 表名;
2、多表数据源：select * from 表名1,表名2,....,表名n;
注： 从一张表中取出一条记录，去另一张表中匹配所有记录，而且全部保留(记录和字段数)，将这种结果称为笛卡尔积(交叉连接)：笛卡尔积没有什么用处，应尽量避免。
3、查询语句，具有这种数据源的查询语句也被称为子查询；基本语法：select * from (select 语句)as 表名;

实例

1、单表数据源
select * from stu; select * from stu2;

2、多表数据源
select * from stu,stu2;

3、查询语句
select * from (select * from stu limit 3) as stud;

2、where子句

where子句定义

where子句用来判断数据、删选数据，其返回结果为0或者1；其中0代表false，1代表true，后面跟着的判断条件分为运算符和逻辑运算符。

判断条件分类

运算符：>、<、 <=、>=、<>、!=、=、like、between、and、in/not、in
逻辑运算符：： &&(and)、||(or)、!(not)
附： like和=的区别
Like是用于模糊查询的，而=是用于精确查询的。

where子句执行过程

where子句的执行过程(原理)：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件，从磁盘取出一条记录，开始进行where判断，判断的结果如果成立，保存到内存，如果失败则直接放弃。

实例

//向table表中添加height字段
alter table student add height unsigned tinyint;
//更新age和height字段的值
update  my_student set age=floor(rand()*20+20),height=floor(rand()*20+170);
/**
   解析：
      1、rand()：产生0-1之间的随机数
      2、floor()：向下取整
*/

示例：
条件查询1：找学生id为1,3,5的学生

方法一：select * from student where id in (1,3,5);
方法二：select * from student where id=1 or id=3 or id=5;

条件查询2：查出身高落在180到190之间的学生

方法一：select * from student where height <=180 and height<=190;
方法二：select * from student where height between 180 and 190;
注： between本身是闭区间，between左边的值必须小于或者等于右边的值

3、group by子句

基本使用方法及基本含义

group by：分组的意思，根据某个字段进行分组(相同的数一组，不同的分到不同的组)。
基本语法：group by 字段名;对分组的结果合并之后的整个结果进行排序。
注： 分组后会自动排序，默认升序，修改的基本语法：group by 字段名 [asc|desc];，并且这个命令后可以跟多个字段，最终会先按照字段1分组，然后将分组的结果再按照字段2分组，以此类推，直到按照指定的所有字段进行分组。

实例：

//按照性别分组
select * from student group by sex;
/*
上述语句显示的结果只是每个分组中多条记录的第一条记录；但分组的意义是为了统计数据(如计算每组中的人数等)。
 */

SQL中提供的统计函数

1、count()：计数，null不进行计数
2、max()：最大值
3、min()：最小值
4、avg()：求平均
5、sum()：求和

综合(分组&统计函数)运用实例：

1、分组(按性别)统计最大身高和最矮身高、年龄平均和总年龄
select max(height),min(height),avg(age),sum(age) from student group by sex;
2、多字段分组：现根据一个字段分组，然后对分组后的结果再次按照其他字段进行分组。
如先按班级分组，再按男女分组，实现如下：
select c_id,sex,count(*) from student group by c_id,sex;
函数group_concat(字段名) 可以对分组的结果中的某个字段进行字符串连接(保留某个字段的数据)；如select c_id,group_concat(sname) from student group by c_id;

回溯统计

1、回溯统计(with rollup)：任何一个分组后的结果都会有一个小组，最后都需要向上级分组汇报统计结果(根据当前分组的字段)，这就是回溯统计；回溯统计的时候，会将分组字段置空。
–实例如下：

//建表语句
create table if not exists student(
     sno int primary key auto_increment,
     sname varchar(4),
     c_id  tinyint comment '班级'
)charset utf8;
//插入数据
insert into student values(null,'刘飞',1),(null,'六子',1),(null,'天一',2),(null,'黄花',2),(null,'留一手',1),(null,'天子',2);
//分组统计
select c_id,count(*) from student group by c_id;
//再进行回溯统计
select c_id,count(*) from student group by c_id with rollup;

解析： 从最后的回溯统计(with rollup)语句可以看出，最后回溯统计时，统计字段c_id被置空，每组的统计结果为3，最后分别汇报1和2的统计结果，再运用统计函数统计1和2的结果，得到6。
2、多字段回溯统计
考虑第一层分组会有一次回溯，第二次分组要看第一次分组的组数，组数是多少，回溯就是多少，然后再加上第一层的回溯即可。
–实例：
修改student表至如下(c_x列为整数)：

进行回溯统计：

解析： 从中可以看到，第1次依照c_id进行分组，组数为2个，所以第2次回溯了2次(即两次null)，然后再加上第1次回溯1次(即最后一条记录)，总共回溯了3次。

4、having子句

having子句和where子句一样，是进行条件判断的，后接判断条件；两者的区别在于where是针对磁盘数据进行判断的，一旦数据进入到内存里，会进行分组操作，分组结果就需要having来处理，除此之外，having与where还有如下区别：

1. 分组统计的结果或者说统计函数都只有having能够使用，即如果判断条件中含有统计函数，只能用where。
2. having能够使用字段别名，而where不能，因为where是从磁盘取数据，而名字只可能是字段进入到内存后才会产生，也就是说，如果在判断条件中含有别名时，必须用having。

从上面来看，having比where的功能要强大的多，但是在应用中，如果能够使用where条件，尽量是要用where条件，这样可以节省内存空间。
实例：找出所有班级人数大于等于2的学生的人数及班级(即:if 班级人数>=2，print 班级,班级人数)

5、order by子句

这是一个排序的子句，可以根据某个(或多个)字段将记录进行升序或者降序排列，其依赖于数据表的校对集；基本语法为：order by 字段名1 [asc|desc][,...,字段名n [asc|desc]]
基本语法解析： 如果后面接了多个字段名，则先按第一个字段排序，然后再对排好的结果中第一个字段值一样的记录按第二个字段排序，依次类推，直到排完所有结果；当只有一个字段时，按此字段排序即可；所有不管哪种形式，此子句都是默认升序。
实例：按照学生号(sno)升序排列，并且按照名字降序排列，语句如下：
select * from student order by sno,c_id desc;