mysql的查询、子查询及连接查询

一、mysql查询的五种子句

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

1、where常用运算符：

比较运算符

> , < ,= , != （< >）,>= , <=

in(v1,v2..vn)

mysql> select id,name from user where id in(7,9,11,100);
+----+-----------+
| id | name      |
+----+-----------+
|  7 | 娲�紵楣?   |
|  9 | CCC       |
| 11 | 闄堥摥閽?   |
+----+-----------+
3 rows in set (0.02 sec)

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之间的值

模糊查询

like 像

通配符:

% 任意字符

_ 单个字符

where goods_name like '诺基亚%'

where goods_name like '诺基亚N__' (必须要两个字符 ‘诺基亚N85’ ,诺基亚N9 就不行) 要匹配一个、两个字符的话不嫌麻烦的要OR把

2、group by 分组

例子  aa表       a          b
                  123        10
                  123        12
                   1234       11
                   1234         14
首先 group 是用来分组的  不是过滤重复项的。重复项删除语句 DISTINCT用这个 。 select  DISTINCT（a） from aa
结果就是     a
                  123
                    1234

group by用来分组的
select a, sum(b) from aa group by a 
sum意思是总和。结果就是
     a         b
    123      22
    1234    25
语句的目的是以a为目标 需要知道 相同名字的物品   在b列一共有多少数量总和
select a,count(b)  from aa group by a 
count 意思行数总和   结果就是
      a         b
    123      2
    1234    2
语句目的是  相同名字的物品 一共有几行

（1）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;

（2）min：求最小值

（3）sum：求总数和

#求商品库存总和

select sum(goods_number) from goods;

（4）avg：求平均值

#求每个栏目的商品平均价格

select cat_id,avg(goods_price) from goods group by cat_id;

（5）count：求总行数

#求每个栏目下商品种类

select cat_id,count(*) from goods group by cat_id;

以ustuid为目标统计apply表里每个ustuid有多少记录(用group by 结果有排序的效果(如下ustuid))

mysql> select ustuid,count(*) from apply  group by ustuid;
+-----------+----------+
| ustuid    | count(*) |
+-----------+----------+
| 031302305 |        3 |
| 031302331 |       11 |
| 031302605 |        3 |
| 031302625 |        1 |
| 031302642 |        2 |
| 123123123 |        3 |
| 221300300 |        1 |
| 221300313 |        2 |
| 221300410 |        1 |
| 313       |        2 |
| 321321321 |        3 |
+-----------+----------+
11 rows in set (0.09 sec)

###要把每个字段名当成变量来理解，它可以进行运算###

例：查询本店每个商品价格比市场价低多少；

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

不仅列名可以取别名，表单也可以取别名

3、having 与where 的异同点

having与where类似，可以筛选数据，where后的表达式怎么写，having后就怎么写

where针对表中的列发挥作用，查询数据

having对查询结果中的列发挥作用，筛选数据

mysql> select id,stuid,score as s from user;
+----+-----------+------+
| id | stuid     | s    |
+----+-----------+------+
|  5 | 031302331 |  111 |
|  7 | 221200420 |    0 |
|  8 | 221300300 |    0 |
|  9 | 031302305 |    8 |
| 11 | 031302605 |    7 |
| 12 | 313       |    0 |
| 13 | 031302625 |    0 |
| 14 | 221300313 |    0 |
| 15 | 031302642 |    0 |
| 17 | 031202303 |    0 |
| 19 | 221200238 |    0 |
| 20 | 221300410 |    0 |
| 21 | 123123123 |    0 |
| 22 | 321321321 |    0 |
+----+-----------+------+
14 rows in set (0.00 sec)

感觉就像是score 换了一个名字

所以大概明白 having 就是把前面一个表达式换了一个代号有时候where 后面不能用表达式的时候

//这里不能用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

#查询两门及两门以上科目不及格的学生的平均分(stu表)

思路：

#先计算所有学生的平均分

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;

4、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;

如果是多个排序条件的：

mysql> select id,stuid,score from user order by score order by stuid;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
mysql> select id,stuid,score from user order by score,stuid;
+----+-----------+-------+
| id | stuid     | score |
+----+-----------+-------+
| 17 | 031202303 |     0 |
| 13 | 031302625 |     0 |
| 15 | 031302642 |     0 |
| 21 | 123123123 |     0 |
| 19 | 221200238 |     0 |
|  7 | 221200420 |     0 |
|  8 | 221300300 |     0 |
| 14 | 221300313 |     0 |
| 20 | 221300410 |     0 |
| 12 | 313       |     0 |
| 22 | 321321321 |     0 |
| 11 | 031302605 |     7 |
|  9 | 031302305 |     8 |
|  5 | 031302331 |   111 |
+----+-----------+-------+
14 rows in set (0.00 sec)

mysql> select id,stuid,score from user order by stuid,score;
+----+-----------+-------+
| id | stuid     | score |
+----+-----------+-------+
| 17 | 031202303 |     0 |
|  9 | 031302305 |     8 |
|  5 | 031302331 |   111 |
| 11 | 031302605 |     7 |
| 13 | 031302625 |     0 |
| 15 | 031302642 |     0 |
| 21 | 123123123 |     0 |
| 19 | 221200238 |     0 |
|  7 | 221200420 |     0 |
|  8 | 221300300 |     0 |
| 14 | 221300313 |     0 |
| 20 | 221300410 |     0 |
| 12 | 313       |     0 |
| 22 | 321321321 |     0 |
+----+-----------+-------+
14 rows in set (0.00 sec)

5、limit

在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)：为了与 PostgreSQL 兼容，MySQL 也支持句法： LIMIT # OFFSET #。

mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：

mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

//如果只给定一个参数，它表示返回最大的记录行数目：

mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行

//换句话说，LIMIT n 等价于 LIMIT 0,n。

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

思路：

#先对每个栏目下的商品价格排序

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; as t 作为临时表这个语句不能缺

#这里使用group by cat_id是因为临时表中每个栏目的第一个商品就是最贵的商品，而group by前面没有使用聚合函数，所以默认就取每个分组的第一行数据，这里以cat_id分组

良好的理解模型：

1、where后面的表达式，把表达式放在每一行中，看是否成立

2、字段(列)，理解为变量，可以进行运算（算术运算和逻辑运算）

3、取出结果可以理解成一张临时表

二、mysql子查询

1、where型子查询

（把内层查询结果当作外层查询的比较条件）

#不用order by 来查询最新的商品

select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

#取出每个栏目下最新的产品(goods_id唯一)

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

2、from型子查询

(把内层的查询结果供外层再次查询)

#用子查询查出挂科两门及以上的同学的平均成绩

思路：

#先查出哪些同学挂科两门以上

select name,count(*) as gk from stu where score < 60 having gk >=2;

#以上查询结果，我们只要名字就可以了，所以再取一次名字

select name from (select name,count(*) as gk from stu having gk >=2) as t;

#找出这些同学了，那么再计算他们的平均分

select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

3、exists型子查询

（把外层查询结果拿到内层，看内层的查询是否成立）

#查询哪些栏目下有商品，栏目表category,商品表goods

select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

查询哪些用户(user)有申请记录(apply)

注：可以用【表名.字段名】来表示

如下面的sql语句也可以这样下

mysql> select id,stuid,name from user where exists(select * from apply where apply.ustuid =user.stuid);

mysql> select id,stuid,name from user where exists(select * from apply where ustuid =stuid);
+----+-----------+-----------+
| id | stuid     | name      |
+----+-----------+-----------+
|  5 | 031302331 | 閮戝�鍏?   |
|  8 | 221300300 | 鏋?       |
|  9 | 031302305 | CCC       |
| 11 | 031302605 | 闄堥摥閽?   |
| 12 | 313       | 钀у畤      |
| 13 | 031302625 | 鍚村厛婊?   |
| 14 | 221300313 | 钀у畤      |
| 15 | 031302642 | 鍚撮敠绉?   |
| 20 | 221300410 | 鏉庣懚姹?   |
| 21 | 123123123 | zjx       |
| 22 | 321321321 | 123123    |
+----+-----------+-----------+
11 rows in set (0.02 sec)

三、union的用法

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。

举例说明：

select * from table1 union select * from table2

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。

MySQL中的UNION ALL
UNION ALL只是简单的将两个结果合并后就返回。这样，如果返回的两个结果集中有重复的数据，那么返回的结果集就会包含重复的数据了。

举例说明：

select * from table1 union all select * from table2

注: 使用 UNION 时前一个 select column的个数要等于后一个select column的个数
如:

table1: (id,createDate,lastUpdateDate,desc,num,hashCode),  
table2: (id,createDate,lastUpdateDate,desc)

如果现在使用:

select * from table1 UNION ALL select * from table2

则是不会成功的, 数据库为报:

Error
The used SELECT statements have a different number of columns

这是提示查询的两张表的字段不统一，如果table1比table2的字段内容多，可以使用空字符串来代替

select id,createDate,lastUpdateDate,desc,num,hashCode from table1 UNION ALL select id,createDate,lastUpdateDate,desc,'','' from table2

如果里面有不想要的，千万要记住前面查询内容要和后面查询内容的字段个数要一样，前面你查询4个，后面也要相应的放4个，这样就不会提示参数数量不同的错误了。 mysql中UNION ALL用法 - 小桃子 - 小桃子

其实稍稍修改一下就可以了
对于 'select id,createDate,desc,hasCode from table1' 可以任意选择4个field

从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复的数据的话，那么就使用UNION ALL。

如果遇到两张表数据不同来集合查询，可以使用union all这个函数进行操作

[sql]view
 plaincopyprint?

SELECT COUNT(c.a)  FROM (  

(SELECT UID a,ID,SERIAL,ParkName,CardNO,ScoreRealPay,PayFlag,PayType,Createtime FROM cp_consumption_record WHERE UID=761 AND PayFlag=1  ORDER BY Createtime DESC) UNION ALL (SELECT UID a,CpResID,CpParkID,ParkSERIAL,CarCode,Price,BusinessType,CardNO,CreateDate FROM cp_reservation WHERE UID=761 AND BusinessType IN(1,2,3) ORDER BY CreateDate DESC)  

) c  

这是查询结果集共有多少条数据，

如果还有查询条件，直接在c后面添加就可以，比如按照时间进行查询

[sql]view
 plaincopyprint?

SELECT c.UID,c.ScoreRealPay,c.PayFlag,c.PayType  FROM (  

(SELECT UID AS UID,ID AS ID,SERIAL AS SERIAL ,ParkName AS ParkName,CardNO CardNO,ScoreRealPay ScoreRealPay,PayFlag PayFlag,PayType PayType,Createtime Createtime FROM cp_consumption_record WHERE UID=761 AND PayFlag=1  ORDER BY Createtime DESC) UNION ALL (SELECT UID a,CpResID,CpParkID,ParkSERIAL,CarCode,Price,BusinessType,CardNO,CreateDate FROM cp_reservation WHERE UID=761 AND BusinessType IN(1,2,3) ORDER BY CreateDate DESC)  

) c ORDER BY Createtime DESC/ASC  

这里强调一下，你要按照什么样的条件进行查询时，要分别在select子查询中添加上条件，最后在按照统一的时间倒序或者正序

注:
缺省的情况下,UNION 子句不返回重复的记录.如果想显示所有记录,可以加ALL选项
UNION运算要求查询具有相同数目的字段.但是,字段数据类型不必相同.

四、左连接，右连接，内连接

现有表a有10条数据，表b有8条数据，那么表a与表b的笛尔卡积是多少？

select * from ta,tb //输出结果为8*10=80条

1、左连接

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

语法：select n1,n2,n3 from ta left join tb on ta.n1= ta.n2 [这里on后面的表达式，不一定为=，也可以>，<等算术、逻辑运算符]【连接完成后，可以当成一张新表来看待，运用where等查询】

  下面是例子分析
表A记录如下：
aID        aNum
1           a20050111
2           a20050112
3           a20050113
4           a20050114
5           a20050115

表B记录如下:
bID        bName
1            2006032401
2           2006032402
3           2006032403
4           2006032404
8           2006032408

创建这两个表SQL语句如下：
CREATE TABLE  a
aID int( 1 ) AUTO_INCREMENT PRIMARY KEY ,
aNum char( 20 )
)
CREATE TABLE b(
bID int( 1 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
bName char( 20 )
)

INSERT INTO a
VALUES ( 1, 'a20050111' ) , ( 2, 'a20050112' ) , ( 3, 'a20050113' ) , ( 4, 'a20050114' ) , ( 5, 'a20050115' ) ;

INSERT INTO b
VALUES ( 1, ' 2006032401' ) , ( 2, '2006032402' ) , ( 3, '2006032403' ) , ( 4, '2006032404' ) , ( 8, '2006032408' ) ;

实验如下:
1.left join(左联接)

sql语句如下:
SELECT * FROM a
LEFT JOIN  b
ON a.aID =b.bID

结果如下:
aID        aNum                   bID           bName
1            a20050111         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404
5            a20050115         NULL       NULL
（所影响的行数为 5 行）

结果说明:
        left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.

2、右连接

a left join b 等价于 b right join a

推荐使用左连接代替右连接

语法：select n1,n2,n3 from ta right join tb on ta.n1= ta.n2

3.inner join(相等联接或内联接)

sql语句如下:
SELECT * FROM  a
INNER JOIN  b
ON a.aID =b.bID

等同于以下SQL句:
SELECT *
FROM a,b
WHERE a.aID = b.bID

结果如下:
aID        aNum                   bID           bName
1            a20050111         1               2006032401
2            a20050112         2              2006032402
3            a20050113         3              2006032403
4            a20050114         4              2006032404

结果说明:
        很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
LEFT JOIN操作用于在任何的 FROM 子句中，

组合来源表的记录。使用 LEFT JOIN 运算来创建一个左边外部联接。左边外部联接将包含了从第一个（左边）开始的两个表中的全部记录，即
使在第二个（右边）表中并没有相符值的记录。

语法：FROM table1 LEFT JOIN table2 ON table1.field1 compopr table2.field2
说明：table1, table2参数用于指定要将记录组合的表的名称。
field1, field2参数指定被联接的字段的名称。且这些字段必须有相同的数据类型及包含相同类型的数据，但它们不需要有相同的
名称。
compopr参数指定关系比较运算符："="， "<"， ">"， "<="， ">=" 或 "<>"。
如果在INNER JOIN操作中要联接包含Memo 数据类型或 OLE Object 数据类型数据的字段，将会发生错误。

today lazy . tomorrow die .