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 join class 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 table student(
id int primary key auto_increment,
name varchar (10),
age int,
class_id int
);
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 table class(
id int primary key auto_increment,
name varchar(10)
);
insert into class values(null,'一班');
insert into class values(null,'二班');
insert into class values(null,'三班');
浙公网安备 33010602011771号