MySQL查询

select 高级查询

select基本用法

DML（data manipulation language）是数据操纵语言：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。

DDL（data definition language）是数据定义语言：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DCL（DataControlLanguage）是数据库控制语言：是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。

DML命令

1.1、增加

-- insert into 表名[（具体字段）] VALUES(具体值)；
INSERT into goods VALUES(3,'苹果',22,2);

1.2、修改

-- UPDATE 表名 set 列=值 where 表达式；
UPDATE student3 set name='马云3' where id=13;

1.3、删除

-- delete from 表名 where 表达式;
DELETE from student3 where name='马云3';

1、检索所用列： select * from 表名;
2、检索制定列： select 列字段 from 表名；
别名： select 列字段1 as 别名1,列字段2 as 别名2….from 表名；

*按条件过滤：**select from 表名 where 条件表达式；

数据汇总

1、聚合函数

max   计算字段最大值
min   计算字段最小值
avg   计算字段平均值
sum   计算字段合计值
count 计算字段总个数值

2、用法

select max(s_age)as ' 高龄少年' from student；
select count(s_id)as '学生总数' from student；

mysql中的五种统计函数：

max：求最大值

select max(goods_price) from goods
这里会取出最大的价格的值，只有值
查询每个栏目下价格最高的
select cat_id,max(goods_price) from goos group by cat_id;
查出价格最高的商品编号
select goods_id,max(goods_price) from goods group by goods_id;

min：求最小值
sum：求总数和

求商品库存总和
select sum(goods_number) from goods;

avg：求平均值

求每个栏目的商品平均价格
select cat_id,avg(goods_price) from goods group by cat_id;

count：求总行数

求每个栏目下商品种类
select cat_id,count(*) from goods group by cat_id;

要把每个字段名当成变量来理解，它可以进行运算###
例：查询本店每个商品价格比市场价低多少；
select goods_id,goods_name,goods_price-market_price from goods;
查询每个栏目下面积压的货款
select cat_id,sum(goods_price*goods_number) from goods group by cat_id;

可以用as来给计算结果取个别名###
select cat_id,sum(goods_price * goods_number)  as hk from goods group by cat_id
不仅列名可以取别名，表单也可以取别名

mysql查询的五种子句

where(条件查询)、having（筛选）、group by（分组）、order by（排序）、limit（限制结果数）

1、where常用运算符：

比较运算符

> ,  < ,=  , != （< >）, >= , <=  
in(v1,v2..vn)  
between v1 and v2    //在v1至v2之间（包含v1,v2）

逻辑运算符

not ( ! )  逻辑非
or ( || )    逻辑或
and ( && )  逻辑与

where price>=3000 and price <= 5000 or price >=500 and price <=1000     //取500-1000或者3000-5000的值
where price not between 3000 and 5000   //不在3000与5000之间的值

语法格式：

SELECT * FROM  表名  order by 排序的字段 ASC|desc

默认升序asc从小到大，desc从大到小
eg:select * from  student order by s_age；

如果有条件判断一定要注意：
不能颠倒 ORDER BY 和 WHERE 子句的位置（条件在前）

1、通配符过滤

如果我要查询学生姓名有王字的同学一般的查询语句已经不能够满足我
们的需求了                  
单字符匹配     _  (英文半角状态) 可以匹配任意一个字符
多字符匹配     %  (可以匹配任意多个字符)                   
格式：  select * from 表名    where  字段名  like  '王%' | '__东'；

2、空值检测

没有添加非空约束列是可以为空值的（也就是 NULL） ， 有时我们需要对空值进行检测
is not null；不为空，is null；为空

3、多值检测

如果我们要为学生年龄为24岁、 25岁和28岁的人发国家励志奖学金我们该怎么做呢？
in(24,25,28);   in(v1,v2,...vn);

4、范围值检测

between    and（前后包括）；如果我们要为学生年龄为24-28岁的人发国家励志奖学金我们该怎么做呢？
select * from student   where s_age between 24 and 28;

5、数据分组

select s_age count (s_age)  from student group by s_age;

注意 ：

1、需要注意的是GROUP BY子句的位置，GROUP BY子句必须放到SELECT语句的之后，如果SELECT语句有WHERE子句，则GROUP BY子句必须放到WHERE语句的之后。
2、需要分组的所有列都必须位于GROUP BY子句的列名列表中，也就是没有出现在GROUP BY子句中的列（聚合函数除外）是不能放到SELECT语句后的列名列表中的。 因为采用分组以后的查询结果集是以分组形式出现。

一般情况下group需与统计函数（聚合函数）一起使用才有意义
如：

select goods_id,goods_name,cat_id,max(shop_price) from goods group by cat_id;

这里取出来的结果中的good_name是错误的！因为shop_price使用了max函数，那么它是取最大的，而语句中使用了group by 分组，那么goods_name并没有使用聚合函数，它只是cat_id下的第一个商品，并不会因为shop_price改变而改变。

6、模糊查询

模糊查询 like '%字%'或者 '_字_';
SELECT * from student3 where name like '可%';
SELECT * from student3 where name like '__';

7、having

1. 聚合函数不能在where中使用
2. 需要特别注意，在having语句中不能包含未分组的列名
3. HAVING语句中也可以像WHERE语句一样使用复杂的过滤条件
4. having 语句能够使用的语法和WHERE几乎是一样的，不过使用WHERE 的时候GROUPBY子句要位于WHERE子句之后，而使用having子句的时候GROUP
5. BY子句要位于having子句之前。 having 是和group by 语句配对使用的。

SELECT avg(score) from student3  GROUP BY name HAVING avg(score)>60

having 与where 的异同点

1. having与where类似，可以筛选数据，where后的表达式怎么写，having后就怎么写
2. where针对表中的列发挥作用，查询数据
3. having对查询结果中的列发挥作用，筛选数据

查询本店商品价格比市场价低多少钱，输出低200元以上的商品
select goods_id,good_name,market_price - shop_price as s from goods having s>200 ;

//这里不能用where因为s是查询结果，而where只能对表中的字段名筛选

如果用where的话则是：
select goods_id,goods_name from goods where market_price - shop_price > 200;

同时使用where与having

select cat_id,goods_name,market_price - shop_price as s from goods where cat_id = 3 having s > 200;
查询积压货款超过2万元的栏目，以及该栏目积压的货款
select cat_id,sum(shop_price * goods_number) as t from goods group by cat_id having s > 20000

查询两门及两门以上科目不及格的学生的平均分

思路：
先计算所有学生的平均分
select name,avg(score) as pj from stu group by name;
查出所有学生的挂科情况
select name,score<60 from stu;
这里score<60是判断语句，所以结果为真或假，mysql中真为1假为0
查出两门及两门以上不及格的学生
select name,sum(score<60) as gk from stu group by name having gk > 1;
综合结果
select name,sum(score<60) as gk,avg(score) as pj from stu group by name having gk >1;

8、order by数据排序
（1） order by price //默认升序排列
（2）order by price desc //降序排列
（3）order by price asc //升序排列，与默认一样
（4）order by rand() //随机排列，效率不高

按栏目号升序排列，每个栏目下的商品价格降序排列
select * from goods where cat_id !=2 order by cat_id,price desc;

9、LIMIT 开始，条数（前不包括）

limit [offset,] N
offset 偏移量，可选，不写则相当于limit 0,N N 取出条目

取价格第4-6高的商品
select good_id,goods_name,goods_price from goods order by good_price desc limit 3,3;

查询每个栏目下最贵的商品

思路：
//先对每个栏目下的商品价格排序
select cat_id,goods_id,goods_name,shop_price from goods order by cat_id,shop_price desc;
//上面的查询结果中每个栏目的第一行的商品就是最贵的商品
//把上面的查询结果理解为一个临时表[存在于内存中]【子查询】
//再从临时表中选出每个栏目最贵的商品
select * from (select goods_id,goods_name,cat_id,shop_price from goods order by cat_id,shop_price desc) as t group by cat_id;
//这里使用group by cat_id是因为临时表中每个栏目的第一个商品就是最贵的商品，而group by前面没
//有使用聚合函数，所以默认就取每个分组的第一行数据，这里以cat_id分组

良好的理解模型：
1、where后面的表达式，把表达式放在每一行中，看是否成立
2、字段(列)，理解为变量，可以进行运算（算术运算和逻辑运算）
3、 取出结果可以理解成一张临时表

Where , group by, having, order by，limit 按顺序写命令，语法

栗子

//查询挂科2门及2门以上学生的平均成绩（这个学生所有科目的平均成绩）
SELECT name,sum(score<60) from student3 GROUP BY name;
SELECT name,avg(score) from student3 GROUP BY name having sum(score<60)>=2;

10、union联合查询

UNION表示“并”，当用的时候，系统会自动将重复的元组去掉，如果要保留重复元组则就用UNION ALL。
SELECT name,score from student3 union SELECT * from sub;

11、内连接
INNER JOIN on

查询结果是左右连接的交集，【即左右连接的结果去除null项后的并集（去除了重复项）】
SELECT name,score,sub_ from student3 INNER JOIN sub;  -- 笛卡尔积
SELECT name,score,sub_ from student3 INNER JOIN sub on d_id=sub.id ;

12、左连接和右连接
1、左连接
以左表为准，去右表找数据，如果没有匹配的数据，则以null补空位，所以输出结果数>=左表原数据数

语法：select n1,n2,n3 from ta left join tb on ta.n1= ta.n2 [这里on后面的表达式，不一定为=，也可以>，<等算术、逻辑运算符]【连接完成后，可以当成一张新表来看待，运用where等查询】
取出价格最高的五个商品，并显示商品的分类名称
select goods_id,goods_name,goods.cat_id,cat_name,shop_price from goods left join category on goods.cat_id = category.cat_id order by  shop_price desc limit 5;

2、右连接

a left join b 等价于 b right join a
//推荐使用左连接代替右连接
语法：select n1,n2,n3 from ta right join tb on ta.n1= ta.n2