mysql查询cp值数据_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 tgroup 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个，这样就不会提示参数数量不同的错误了。

其实稍稍修改一下就可以了

对于 'select id,createDate,desc,hasCode from table1' 可以任意选择4个field

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

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

SELECTCOUNT(c.a)FROM(

(SELECTUID a,ID,SERIAL,ParkName,CardNO,ScoreRealPay,PayFlag,PayType,CreatetimeFROMcp_consumption_recordWHEREUID=761ANDPayFlag=1ORDERBYCreatetimeDESC)UNIONALL(SELECTUID a,CpResID,CpParkID,ParkSERIAL,CarCode,Price,BusinessType,CardNO,CreateDateFROMcp_reservationWHEREUID=761ANDBusinessTypeIN(1,2,3)ORDERBYCreateDateDESC)

) c

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

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

SELECTc.UID,c.ScoreRealPay,c.PayFlag,c.PayTypeFROM(

(SELECTUIDASUID,IDASID,SERIALASSERIAL ,ParkNameASParkName,CardNO CardNO,ScoreRealPay ScoreRealPay,PayFlag PayFlag,PayType PayType,Createtime CreatetimeFROMcp_consumption_recordWHEREUID=761ANDPayFlag=1ORDERBYCreatetimeDESC)UNIONALL(SELECTUID a,CpResID,CpParkID,ParkSERIAL,CarCode,Price,BusinessType,CardNO,CreateDateFROMcp_reservationWHEREUID=761ANDBusinessTypeIN(1,2,3)ORDERBYCreateDateDESC)

) c ORDERBYCreatetimeDESC/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后面的表达式，不一定为=，也可以>，

下面是例子分析

表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 数据类型数据的字段，将会发生错误。

版权声明：本文为博主原创文章，未经博主允许不得转载。