mysql成长之路_MySQL成长之路(002)

一、select 语句完整格式：

select [选项]列的列表from 数据源where 条件 group by 分组 having 条件 order by 排序limit 限制;

1.select 列的列表

select不一定 并有一定非得有后面的字句，可以单独使用

例如：select 1 =2;

select 1+2;

select选项：

all:默认情况下是显示所有记录

distinct:去重

2.select * from  stu;

要求：

from数据源；from后不一定是真实存在的表；任何符合二维表格式的数据源都是可以的！

1.真实的数据表

select * from stu8;

2.多个表

select * from stu7,stu8//stu 7 stu8之间用的笛卡尔积，笛卡尔积没有任何意义，列的拼接

3.from 后接虚拟的表(不真实存在的表)

select * from(select * from stu8) as temp where id <3;

如果虚拟表(select * from stu8)，需要给第二个赋变量名temp，(select * from stu8) as temp算个子查询。

列上也可以起别名，alias → as

如：select num as '手机号' from stu8;

as关键字可以省略；

如：select num '手机号' from stu8;

3.from后五子句

where :主要是对记录的行级过滤：

where后主要使用的是条件表达式(算术运算付，逻辑运算符，in.....)

二、聚合函数：

把多条记录编程一条记录的函数：

求和：多对一的关系

求最值：多对一的关系

求平均值：多对一的关系

sum(列名)：对某列求和

max(列名)：求最大值

min(列名)：求最小值

avg(列名)：求平均值

coun t(列名)：计数

只有sum时候不用关系ifnull，其他都需要写上

统计总数：

select count(*) as '人数' from stu;//统计有多少条记录，null值不参与count计数，因为任何一列都可能出现null值；null不参与count计数。

统计语文成绩之和：

select sum(ch) '语文成绩汇总' from stu;

如果用null+1的话，任何数字加的结果都是null，其实是用函数方式，null此时变成0进行运算。

select sum(ch) '语文成绩汇总'，sum(en) '英语成绩汇总' from stu;

求出整个班级的所有成绩汇总

先求每个人的成绩汇总

这种方法不对，select sum(ch + en) '全班总成绩' from stu;//但如果有成绩是null之后容易变成null。

有两种方式：

可以使用ifnull,

1.select sum(ifnull(ch,0) +ifnull(en,0)) '全班总成绩' from stu;;

2.select sum(ch) + sum(en) '全班总成绩' from stu;

统计平均值：

select avg(en) '英语平均分' from stu;//这种就是不对的，因为avg不统计null值，个数也不算。

当值中有null的情况：

select avg(ifnull(ch,0)) '语文平均成绩' from stu;

统计最值：

select max(ch) '语文最高分',max(en)'英语最高分',min(ifnull(ch,0))'语文最低分',min(ifnll(en,0)) '英语最低分'  from stu;

三、分组

group by：

将整张表根据某些列进行归类，然后在小组上进行统计汇总(通常使用的是聚合函数！)

select * from t1 where 条件 group by(列名)

例如：select gender,sum(ifnull(ch,0)+ifnull(en,0)) from stu group by gender;

select * from orders group by product;默认情况下，非分组列只显示第一条记录，一般情况下，非分组的记录会有多条，和分组列的条数不匹配！

因此需要对分组的列使用聚合函数，计算出一个值，才能对应到分组列的属性，

统计每种产品的销售总额

select product ,sum(price) '销售总额' from orders group by product;//一般要把分组的列在select 后面显示出来，显示分组的标准。

多分组：

概念：在一个分组条件下，进行二次分组

案例：统计住校和非住校男女各多少人；

select  zhuxiao,gender,count(*) '人数 ' from stu2 group by zhuxiao,gender;

分组顺序：默认情况下，分组是有顺序的，使用关键字改变分组的排序，asc(升序)，desc(降序)；

select  zhuxiao,gender,count(*) '人数 ' from stu2 group by zhuxiao desc,gender desc;

四、having条件 ：

where指的是从数据中取数据时进行的行级过滤，having是指对分组统计之后的数据进行的再次过滤！！

统计在销售额在1000以上的产品信息。

select product,sum(price) '销售总额' from orders group by product having sum(price) >1000;//如果用where会报错，where是读磁盘中真实存在的表。

也可以：

select product,sum(price) '销售总额' from orders group by product having 销售总额 >1000;//可以使用临时列的别名

五、order by

根据某列进行排序：

asc：升序(默认的)

desc:降序

案例：按英语成绩降序

select  * from  stu oder by en desc;

按英语成绩升序

select  * from  stu oder;

根据多列排序：

当第一个列发生重复值时，按照第二个列排序，

按照英语成绩降序，英语成绩相同，按照年龄升序。

select * from stu order by en desc,age;

六、limit，显示多少条

对已经符合条件的记录进行数量上的限制！

两种格式：

①limit数字：本次显示的记录数，只限制指定条数的记录

select * from stu limit 2;

②起始值，显示的记录数，可以实现类分页的功能；

imit  n:m；

例如：

select * from stu limit 0,3;//输出第1，第2，第3个数，从0开始，就是不包含0；

select * from stu limit 3,3;//从3开始不包含3，输出4，5，6；

要求：

1.对满足条件的数据进行数量上的限制

2.多表联合查询时,有些语法要求必须使用limit!!一般使用limit 1。

例如：

显示某个表中1000 - 1030条记录的两种方式:

select * from stu where id > 1000 limit 30;(推荐，数据量大一些比较好用)

select * from stu limit 1000,30;

总结：

select 列的列表from 数据源

where 条件

group by 分组

having 条件

order by 排序

limit 限制;

------------------------------------------------------------------------------------------------

七、运算符：

1.算数运算符：

+ - * / % 通常使用在select结果之后

例如： select 1+2,1-2,1*2,1/2,10%3;

select 1/0,null/3,2/null,null+3;

2.逻辑运算符：

and,or,not

3.比较运算符

, <= ,>= ,<>             其中<>是不等于，也可以用！=

4.“=”两个应用场景

1.赋值

2.判断

5.null 两种方式判断：

第一种是:is；is null

第二种是<=>

判断非null:

判断不是null，只能使用 is not null;

6.in 运算符：

查找指定范围内的值：

select * from stu where id in (1,3,5);

7.区间运算符：

between Start and End

查看en成绩在90-100之间的成绩(闭区间)的信息

select * from stu where en >= 90 and en <= 100;

select * from stu where en between 90 and 100;

8.is运算符：

主要用来对null进行判断，

select null is null;

9.like 运算符：

—：单个字符

%：任意字符不限

查找名字是5个字符的

select * from stu where name like '_____';//单引号里是五个下划线

查找姓张的人：

insert into stu values(null,'张三',30,'male',99,50);//先插入几条姓名有张的数据

insert into stu values(null,'张',30,'male',99,50);

insert into stu values(null,'张si',35,'female',99,50);

insert into stu values(null,'大张',30,'male',59,50);

select * from stu where name like '张%';//查找以张字开头的数据

八、联合查询:

将多个查询结果拼接在一起显示的查询，就是联合查询。

默认情况下,联合查询的结果是去重的!!

union后使用如下选项可以显示所有结果:

all:显示所有的记录!!

distinct:去重(默认的!)

select * from stu union  select * from stu;

select * from stu union all select * from stu;

all:情况下是显示所有记录

distinct:去重(默认)

通常情况下降不同的表做联合查询，没有意义！

-- select * from stu union select * from stu2;//列的数量不同,不能联合查询

联合查询只关心列的数量,不关心类型

select id,name,age from stu union select * from stu2;

通常情况下,将不同的表做联合查询,没有意义!

select id,name,age from stu order by age desc union select * from stu2;//此时不能用

联合查询中任何一张表使用到了排序就必须配合limit使用！此时需要给limit一个非常大的值：999999999

(select id,name,age from stu order by age desc limit 999999999 )union(select * from stu2);

//一旦联合查血中出现了ordr by子句，必须配合limit使用，同时union两侧的查询语句必须使用()，不论另一个语句是否有order by子句；

九、交叉连接:无意义

将两个表的列进行拼接，行进行相乘，效果等同笛卡尔积。

select * from t1 [cross] join t2;

select * from su cross  join stu2;//cross可以省略

select * from stu jion stu2;

以下语句等效：

select * from stu,stu2;

十、内连接:(数据在代码1中)

概念：拿一张表的记录去和另一张表的记录进行匹配,如果匹配上,就留下,否则就丢弃!不符合的数据不显示。

select * from student inner join class;

第一种方式：

select * from t1 [inner] join t2 on 条件；//inner可以省略

on 条件通常是等值条件判断；

查询学生的所有信息

select student.name,age,class.name from student inner join class Class on class_id = class.id;

//省略写为：select student.name,age,class.name from student join Class on class_id = class.id; //inner可以省略

第二种方式：使用where条件判断。隐式内连接:

select * from A join B where 条件;

select * from A , B where 条件;

select student.name,age,class.name from student  join Class where class_id = class.id;

给表起别名：

有的表名称太长，多出需要使用，需要起一个简单的别名。

将学生表起名为：

select s.name,age,c.name from student s joinclass c where class_id = c.id;//给student 起名s,给class起名c;

-- 验证不符合条件的数据是否能显示

insert into student values(null,'Eric',11,null);

insert into class values(null,'四班');

select s.name,age,c.name from student s join class c where class_id = c.id;

十一、外连接:

内连接是满足条件的数据才能被显示，不满足条件的不显示

外连接是让不满足条件的数据也能显示出来，但不是所有的数据都显示出来，否则就是笛卡尔积

主表：不论连接条件是否满足，都会被显示的表

从表：只有满足条件的数据被现实的表成为从表！

两种外连方式：

左外连接：

格式：

select * from 主表 left outer join 从表 on 条件; outer可省

例子：显示所有的学生信息，学生表为主表！！

select student .name ,age,class.name from student left outer join calss on student.class_id = class.id;

//简写

select s.name,age,c.name from student s left join class c on class_id = c.id;

右外连接：

格式:

select * from 从表right outer join主表on条件;

select student.name,age,class.name from class right outer join student on student.class_id = class.id;

select student.name,age,class.name from student right outer join class on student.class_id = class.id;

select s.name,age,c.name from student s right join class c on class_id = c.id;

内连接可以使用where作为条件判断的关键字!

外连接没有这种语法!!!

十二、using关键字:

在连接查询中,如果两张表的条件判断列是同名的.可以使用using(列名)

--

首先有个表student:

再有个表：class

(可以用alter........change..............语句将其中相同的部分改名称，

alter table class change id class_id int;//将class表中的id列表名改成class_id,而且是int型)

using使用场景有限:多张表中无法保证用于条件判断的两个列一定同名!!!

代码1：

create tablestudent(

idint primary keyauto_increment,

namevarchar (10),

ageint,

class_idint);insert into student values(null,'Andy',10,1);insert into student values(null,'Bob',15,2);insert into student values(null,'Cindy',12,3);insert into student values(null,'Eric',11,3);insert into student values(null,'David',10,1);create tableclass(

idint primary keyauto_increment,

namevarchar(10)

);insert into class values(null,'一班');insert into class values(null,'二班');insert into class values(null,'三班');