校园网站建设合同百度文库,开发网站的流程步骤,广告设计公司宁波,网站建设管理工作总结数据库管理系统的一个最重要的功能就是数据查询#xff0c;数据查询不应只是简单查询数据库中存储的数据#xff0c;还应该根据需要对数据进行筛选#xff0c;以及确定数据以什么样的格式显示。MySQL提供了功能强大、灵活的语句来实现这些操作。下面是通过help帮助查看到的s… 数据库管理系统的一个最重要的功能就是数据查询数据查询不应只是简单查询数据库中存储的数据还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示。MySQL提供了功能强大、灵活的语句来实现这些操作。 下面是通过help帮助查看到的select语法格式Name: SELECT
Description:
Syntax:
SELECTselect_expr [, select_expr] ...[FROM table_references][WHERE where_condition][GROUP BY {col_name | expr | position}[ASC | DESC], ... [WITH ROLLUP]][HAVING where_condition][ORDER BY {col_name | expr | position}[ASC | DESC], ...][LIMIT {[offset,] row_count | row_count OFFSET offset}]基本查询 mysql从数据表中查询数据的基本语句为select语句。select语句的基本格式如下SELECT {* | 字段列表} [ FROM 表1, 表2.... [ where 表达式 ] [ group by ] [ having ] [ order by.. ] [ limit ... ]{*|字段列表}包含星号通配符选择字段列表表示查询的字段其中字段列至少包含一个字段名称如果要查询多个字段多个字段之间用逗号隔开最后一个字段后不要加逗号。FROM表1,表2...表1和表2表示查询数据的来源可以是单个或多个。WHERE子句是可选项如果选择该项将限定查询必须满足的查询条件。GROUP BY字段该子句告诉MySQL按什么样的顺序显示查询出来的数据可以进行的排序有升序asc、降序desc。[limit]该子句告诉mysql每次显示查询出来的数据条款。mysql create table fruits - (- f_id char(10) not null,- s_id int not null,- f_name char(255) not null,- f_price decimal(8,2) not null,- primary key(f_id)- );
Query OK, 0 rows affected (0.02 sec)
mysql insert into fruits(f_id,s_id,f_name,f_price)- values(a1,101,apple,5.2),- (b1,101,blackberry,10.2),- (bs1,102,orange,11.2),- (bs2,105,melon,8.2),- (t1,102,banana,10.3),- (t2,102,grape,5.3),- (o2,103,coconut,9.2),- (c0,101,cherry,3.2),- (a2,103,apricot,2.2),- (l2,104,lemon,6.4),- (b2,104,berry,7.6),- (m1,106,mango,15.7),- (m2,105,xbabay,2.6),- (t4,107,xbababa,3.6),- (m3,105,xxtt,11.6),- (b5,107,xxxx,3.6);
Query OK, 16 rows affected (0.02 sec)
Records: 16 Duplicates: 0 Warnings: 0单表查询 单表查询是指从一张表数据中查询所需的数据。主要有查询所有字段、查询指定字段、查询指定记录、查询空值、多条件的查询、对查询结果进行排序等方式。查询所有字段1、在select语句中使用星号通配符查询所有字段。 select查询记录最简单的形式是从一个表中检索所有记录实现的方法是使用星号通配符指定查找所有列的名称。mysql select * from fruits;
---------------------------------
| f_id | s_id | f_name | f_price |
---------------------------------
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
---------------------------------
16 rows in set (0.00 sec)2、在select语句中指定所有字段 根据前面select语句的格式select关键字后面的字段名为将要查询的数据因此可以将表中所有字段的名称跟在select子句后面如果忘记了字段名称可以使用DESC命令查看表的结构。有时候由于表中的字段多不一定能记住所有的字段名称。因此很不方便不建议使用。语法格式mysql select f_id,s_id,f_name,f_price from fruits;
---------------------------------
| f_id | s_id | f_name | f_price |
---------------------------------
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
---------------------------------
16 rows in set (0.00 sec)查询指定字段语法格式select 字段名 from 表名mysql select f_name from fruits;
------------
| f_name |
------------
| apple |
| apricot |
| blackberry |
| berry |
| xxxx |
| orange |
| melon |
| cherry |
| lemon |
| mango |
| xbabay |
| xxtt |
| coconut |
| banana |
| grape |
| xbababa |
------------
16 rows in set (0.00 sec) 查询多个字段 使用select声明可以获取多个字段下的数据只需要在关键字select后面指定要查询的字段的名称不同字段名称之间用逗号分隔最后一个字段后面不需要加逗号 语法格式select 字段1字段2字段3 ....字段n from 表名mysql select f_name,f_price from fruits;
---------------------
| f_name | f_price |
---------------------
| apple | 5.20 |
| apricot | 2.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| orange | 11.20 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| mango | 15.70 |
| xbabay | 2.60 |
| xxtt | 11.60 |
| coconut | 9.20 |
| banana | 10.30 |
| grape | 5.30 |
| xbababa | 3.60 |
---------------------
16 rows in set (0.00 sec)查询指定记录 数据库中包含大量的数据根据特殊要求可能只需要查询表中的指定数据相当于对数据的过滤。在select语句中通过where子句可以对数据进行过滤。 语法格式select 字段1字段2....字段n from 表名 where 查询条件;操作符说明相等,!不相等小于小于等于大于大于等于BETWEEN位于两端之间mysql select f_name,f_price from fruits where f_price 10.2;
---------------------
| f_name | f_price |
---------------------
| blackberry | 10.20 |
---------------------
1 row in set (0.00 sec)带in关键字的查询 in操作符用来查询满足指定范围内的条件的记录使用IN操作符将所有检索条件用括号括起来检索条件之间用逗号分隔开只要满足条件范围内的一个值即为匹配项。mysql select s_id,f_name,f_price - from fruits- where s_id in (101,102)- order by f_name;
---------------------------
| s_id | f_name | f_price |
---------------------------
| 101 | apple | 5.20 |
| 102 | banana | 10.30 |
| 101 | blackberry | 10.20 |
| 101 | cherry | 3.20 |
| 102 | grape | 5.30 |
| 102 | orange | 11.20 |
---------------------------
6 rows in set (0.00 sec)带between and的范围查询 Between and用来查询某个范围内的值该操作符需要两个参数即范围的开始值和结束值如果字段值满足指定的范围查询条件则这些记录被返回。mysql select f_name,f_price- from fruits- where f_price between 2.00 and 10.20;
---------------------
| f_name | f_price |
---------------------
| apple | 5.20 |
| apricot | 2.20 |
| blackberry | 10.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| melon | 8.20 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| coconut | 9.20 |
| grape | 5.30 |
| xbababa | 3.60 |
---------------------
12 rows in set (0.00 sec)带like的字符匹配查询 通配符是一种在SQL的where条件子句中拥有特殊意思的字符SQL语句中支持多种通配符可以和like一起使用的通配符有‘%’和‘_’。百分号%通配符匹配任意长度的字符甚至包括零字符mysql select f_id,f_name- from fruits- where f_name like b%;
------------------
| f_id | f_name |
------------------
| b1 | blackberry |
| b2 | berry |
| t1 | banana |
------------------
3 rows in set (0.00 sec)下划线__)通配符一次只能匹配任意一个字符mysql select f_id,f_name- from fruits- where f_name like ____y;
--------------
| f_id | f_name |
--------------
| b2 | berry |
--------------
1 row in set (0.00 sec)查询空值 数据表创建的时候设计者可以指定某列中是否可以包含空值NULL)。空值不同于0也不同于空字符串。空值一般表示数据未知、不适用或将在以后添加数据。 创建customers表并插入数据mysql create table customers- (- c_id int not null auto_increment,- c_name char(50) not null,- c_address char(50) null,- c_city char(50) null,- c_zip char(50) null,- c_contact char(50) null,- c_email char(50) null,- primary key(c_id)- );
Query OK, 0 rows affected (0.03 sec)
mysql insert into customers(c_id,c_name,c_address,c_city,c_zip,c_contact,c_email)- values(10001,RedHook,200Street,Tianjin,300000,LiMing,LMing163.com),- (10002,Stars,333 FromageLane,Dalian,116000,Zhangbo,Jerryhotmail.com),- (10003,Netbhood,1 Sunny Place,Qingdao,266000,LuoCong,NULL),- (10004,JOTO,829 Riverside Drive, Haikou,570000,YangShan,samhotmail.com);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0 在select语句中使用IS NULL子句可以查询某字段内容为空的记录。mysql select c_id,c_name,c_email from customers where c_email is null;
--------------------------
| c_id | c_name | c_email |
--------------------------
| 10003 | Netbhood | NULL |
--------------------------
1 row in set (0.00 sec)带and的多条件查询 使用select查询时可以增加查询的限制条件这样可以使查询的结果更加精确。MySQL在where子句中使用and操作符限定只有满足所有查询条件的记录才会被返回。可以使用and连接两个甚至多个查询条件多个条件表达式之间用and分开。mysql select f_id,f_price,f_name- from fruits- where s_id 101 and f_price 5;
---------------------------
| f_id | f_price | f_name |
---------------------------
| a1 | 5.20 | apple |
| b1 | 10.20 | blackberry |
---------------------------
2 rows in set (0.00 sec)带or的多条件查询 与and相反在where声明中使用or操作符表示只需要满足其中一个条件的记录即可返回。or也可以连接两个甚至多个查询条件多个条件表达式之间用or分开。mysql select s_id,f_name,f_price- from fruits- where s_id 101 or s_id 102;
---------------------------
| s_id | f_name | f_price |
---------------------------
| 101 | apple | 5.20 |
| 101 | blackberry | 10.20 |
| 102 | orange | 11.20 |
| 101 | cherry | 3.20 |
| 102 | banana | 10.30 |
| 102 | grape | 5.30 |
---------------------------
6 rows in set (0.00 sec)查询结果不重复语法格式select distinct 列名 from 表名; 现在查看fruits表s_id列可以看到很多重复的行mysql select s_id from fruits;
------
| s_id |
------
| 101 |
| 103 |
| 101 |
| 104 |
| 107 |
| 102 |
| 105 |
| 101 |
| 104 |
| 106 |
| 105 |
| 105 |
| 103 |
| 102 |
| 102 |
| 107 |
------
16 rows in set (0.00 sec) 添加关键字再次查看s_id列mysql select distinct s_id from fruits;
------
| s_id |
------
| 101 |
| 103 |
| 104 |
| 107 |
| 102 |
| 105 |
| 106 |
------
7 rows in set (0.00 sec)对查询结果排序1、单列排序mysql select f_name from fruits;
------------
| f_name |
------------
| apple |
| apricot |
| blackberry |
| berry |
| xxxx |
| orange |
| melon |
| cherry |
| lemon |
| mango |
| xbabay |
| xxtt |
| coconut |
| banana |
| grape |
| xbababa |
------------
16 rows in set (0.00 sec)
mysql select f_name from fruits order by f_name;
------------
| f_name |
------------
| apple |
| apricot |
| banana |
| berry |
| blackberry |
| cherry |
| coconut |
| grape |
| lemon |
| mango |
| melon |
| orange |
| xbababa |
| xbabay |
| xxtt |
| xxxx |
------------
16 rows in set (0.01 sec)2、多列排序 在多列进行排序的时候首先排序的第一列必须有相同的列值才会对第二列进行排序。如果第一列数据中所有值都是唯一的将不再对第二列进行排序。mysql select f_name,f_price from fruits order by f_name,f_price;
---------------------
| f_name | f_price |
---------------------
| apple | 5.20 |
| apricot | 2.20 |
| banana | 10.30 |
| berry | 7.60 |
| blackberry | 10.20 |
| cherry | 3.20 |
| coconut | 9.20 |
| grape | 5.30 |
| lemon | 6.40 |
| mango | 15.70 |
| melon | 8.20 |
| orange | 11.20 |
| xbababa | 3.60 |
| xbabay | 2.60 |
| xxtt | 11.60 |
| xxxx | 3.60 |
---------------------
16 rows in set (0.00 sec)3、指定排序方向 默认情况下查询数据按字母升序进行排序从A~Z)但数据的排序并不仅限于此还可以使用order by对查询结果进行降序排序从Z~A)这可以通过关键字DESC实现。mysql select f_name,f_price from fruits order by f_name,f_price DESC;
---------------------
| f_name | f_price |
---------------------
| apple | 5.20 |
| apricot | 2.20 |
| banana | 10.30 |
| berry | 7.60 |
| blackberry | 10.20 |
| cherry | 3.20 |
| coconut | 9.20 |
| grape | 5.30 |
| lemon | 6.40 |
| mango | 15.70 |
| melon | 8.20 |
| orange | 11.20 |
| xbababa | 3.60 |
| xbabay | 2.60 |
| xxtt | 11.60 |
| xxxx | 3.60 |
---------------------
16 rows in set (0.00 sec) 注与DESC相反ASC是升序分组查询 分组插叙是对数据按照某个或多个字段进行分组MySQL中使用group by关键字对数据进行分组 基本语法形式为group by 字段 1、创建分组Group by 关键字通常和集合函数一起使用。函数说明AVG求平均值COUNT统计行的数量MAX求最大值MIN求最小值SUM求累加和1、根据s_id对fruits表中的数据进行分组mysql select s_id,count(*) as total from fruits group by s_id;
-------------
| s_id | total |
-------------
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
-------------
7 rows in set (0.01 sec) 根据s_id对fruits表中的数据进行分组将每个供应商的水果名称显示出来mysql select s_id,group_concat(f_name) as name from fruits group by s_id;
-------------------------------
| s_id | name |
-------------------------------
| 101 | apple,blackberry,cherry |
| 102 | orange,banana,grape |
| 103 | apricot,coconut |
| 104 | berry,lemon |
| 105 | melon,xbabay,xxtt |
| 106 | mango |
| 107 | xxxx,xbababa |
-------------------------------
7 rows in set (0.00 sec)2、使用having过滤分组 根据s_id对fruits表中的数据进行分组并显示水果种类大于1的分组信息mysql select s_id,group_concat(f_name) as name from fruits group by s_id having count(f_name) 1;
-------------------------------
| s_id | name |
-------------------------------
| 101 | apple,blackberry,cherry |
| 102 | orange,banana,grape |
| 103 | apricot,coconut |
| 104 | berry,lemon |
| 105 | melon,xbabay,xxtt |
| 107 | xxxx,xbababa |
-------------------------------
6 rows in set (0.00 sec)3、在group by 子句中使用with rollup 使用with rollup关键字之后在所有查询出的分组记录之后增加一条记录该记录计算查询出的所有记录的总和即统计记录数量。mysql select s_id,count(*) as total- from fruits- group by s_id with rollup;
-------------
| s_id | total |
-------------
| 101 | 3 |
| 102 | 3 |
| 103 | 2 |
| 104 | 2 |
| 105 | 3 |
| 106 | 1 |
| 107 | 2 |
| NULL | 16 |
-------------
8 rows in set (0.00 sec)4、多字段分组 使用group by可以对多个字段进行分组group by关键字后面跟需要分组的字段MySQL根据多字段的值来进行层次分组分组层次从左到右即先按第1个字段分组然后在第1个字段值相同的记录中再根据第2个字段的值进行分组以此类推。mysql select * from fruits group by f_id,f_name;
---------------------------------
| f_id | s_id | f_name | f_price |
---------------------------------
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
---------------------------------
16 rows in set (0.00 sec)5、group by和order by一起使用 某些情况下需要对分组进行排序mysql create table orderitems- (- o_num int not null,- o_item int not null,- f_id char(10) not null,- quantity int not null,- item_price decimal(8,2) not null,- primary key(o_num,o_item)- );
Query OK, 0 rows affected (0.03 sec)
mysql insert into orderitems(o_num,o_item,f_id,quantity,item_price)- values(30001,1,a1,10,5.2),- (30001,2,b2,3,7.6),- (30001,3,bs1,5,11.2),- (30001,4,bs2,15,9.2),- (30002,1,b3,2,20.0),- (30003,1,c0,100,10),- (30004,1,o2,50,2.50),- (30005,1,c0,5,10),- (30005,2,b1,10,8.99),- (30005,3,a2,10,2.2),- (30005,4,m1,5,14.99);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0 查询价格大于100的订单号和总价订单价格mysql select o_num,sum(quantity*item_price) as ordertotal- from orderitems- group by o_num- having sum(quantity*item_price) 100;
-------------------
| o_num | ordertotal |
-------------------
| 30001 | 268.80 |
| 30003 | 1000.00 |
| 30004 | 125.00 |
| 30005 | 236.85 |
-------------------
4 rows in set (0.00 sec)使用limit限查询结果的数量 select返回所有匹配的行有可能是表中所有的行如仅仅需要返回第一行或者前几行使用limit关键字语法格式如下limit [位置偏移量] 行数mysql select * from fruits limit 4;
---------------------------------
| f_id | s_id | f_name | f_price |
---------------------------------
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
---------------------------------
4 rows in set (0.00 sec) 查看从第四行开始到第三行结束前面的4为第几行后面的3为数量mysql select * from fruits limit 4,3;
-----------------------------
| f_id | s_id | f_name | f_price |
-----------------------------
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
-----------------------------
3 rows in set (0.00 sec)聚合函数查询函数作用AVG()返回某列的平均值COUNT()返回某列的行数MAX()返回某列的最大值MIN()返回某列的最小值SUM()返回某列的和count()函数 Count()函数统计数据表中包含的记录行的总数或者根据查询结果返回列中包含的数据行数。 Count(*)计算表中总的函数不管某列有数列或者为空值 Count(字段名)计算指定列下总的行数计算时将忽略空值的行mysql select count(*) from customers;
----------
| count(*) |
----------
| 4 |
----------
1 row in set (0.01 sec)
mysql select count(c_email) from customers;
----------------
| count(c_email) |
----------------
| 3 |
----------------
1 row in set (0.00 sec)
mysql select o_num,count(f_id) from orderitems group by o_num;
--------------------
| o_num | count(f_id) |
--------------------
| 30001 | 4 |
| 30002 | 1 |
| 30003 | 1 |
| 30004 | 1 |
| 30005 | 4 |
--------------------
5 rows in set (0.00 sec)sum()函数sum()是一个求总和的函数返回指定列值得总和。mysql select sum(quantity) from orderitems where o_num 30005;
---------------
| sum(quantity) |
---------------
| 30 |
---------------
1 row in set (0.00 sec)
mysql select o_num,sum(quantity)- from orderitems- group by o_num;
----------------------
| o_num | sum(quantity) |
----------------------
| 30001 | 33 |
| 30002 | 2 |
| 30003 | 100 |
| 30004 | 50 |
| 30005 | 30 |
----------------------
5 rows in set (0.01 sec) 注意sum()函数在计算时忽略列值为NULL的行。avg()函数 avg()函数通过计算返回的行数和每一行数据的和求得指定列数据的平均值。mysql select avg(f_price)- from fruits- where s_id103;
--------------
| avg(f_price) |
--------------
| 5.700000 |
--------------
1 row in set (0.02 sec)
mysql select s_id,avg(f_price)- from fruits- group by s_id;
--------------------
| s_id | avg(f_price) |
--------------------
| 101 | 6.200000 |
| 102 | 8.933333 |
| 103 | 5.700000 |
| 104 | 7.000000 |
| 105 | 7.466667 |
| 106 | 15.700000 |
| 107 | 3.600000 |
--------------------
7 rows in set (0.01 sec)max()函数 max()返回指定列中的最大值mysql select max(f_price) from fruits;
--------------
| max(f_price) |
--------------
| 15.70 |
--------------
1 row in set (0.00 sec)
mysql select s_id,max(f_price) - from fruits- group by s_id;
--------------------
| s_id | max(f_price) |
--------------------
| 101 | 10.20 |
| 102 | 11.20 |
| 103 | 9.20 |
| 104 | 7.60 |
| 105 | 11.60 |
| 106 | 15.70 |
| 107 | 3.60 |
--------------------
7 rows in set (0.01 sec)min()函数min()返回查询列中的最小值mysql select min(f_price) from fruits;
--------------
| min(f_price) |
--------------
| 2.20 |
--------------
1 row in set (0.00 sec)
mysql select s_id,min(f_price) from fruits group by s_id;
--------------------
| s_id | min(f_price) |
--------------------
| 101 | 3.20 |
| 102 | 5.30 |
| 103 | 2.20 |
| 104 | 6.40 |
| 105 | 2.60 |
| 106 | 15.70 |
| 107 | 3.60 |
--------------------
7 rows in set (0.00 sec)连接查询 连接是关系数据库模型的主要特点。连接查询是关系数据库中最主要的查询主要包括内连接、外连接。通过连接运算符可以实现多个表查询。在关系数据库管理系统中表建立时各数据之间的关系不必确定常把一个实体的所有信息存放在一个表中。当查询数据时通过连接操作查询出存放在多个表中的不同实体的信息。当两个或多个表现中存在相同意义的字段时便可以通过这些字段对不同的表进行连接查询。内连接查询 内连接inner join使用比较运算符进行表间某些列数据的比较操作并列出这些表中与连接条件相匹配的数据行组合成新纪录也就是说在内连接查询中只有满足条件的记录才能出现在结果关系中。 创建表并插入数据mysql create table suppliers- (- s_id int not null auto_increment,- s_name char(50) not null,- s_city char(50) null,- s_zip char(10) null,- s_call char(50) not null,- primary key(s_id) - );
Query OK, 0 rows affected (0.02 sec)
mysql insert into suppliers(s_id,s_name,s_city,s_zip,s_call)- values(101,FastFruit Inc.,tianjin,300000,48075),- (102,LT Supplies,chongqing,400000,44333),- (103,acme,shanghai,200000,90046),- (104,fnk inc.,zhongshan,528437,11111),- (105,good set,taiyuang,030000,22222),- (106,just eat ours,beijing,010,45678),- (107,dk inc.,zhengzhou,450000,33332);
Query OK, 7 rows affected (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 0mysql select suppliers.s_id,s_name,f_name,f_price- from fruits,suppliers- where fruits.s_id suppliers.s_id;
-------------------------------------------
| s_id | s_name | f_name | f_price |
-------------------------------------------
| 101 | FastFruit Inc. | apple | 5.20 |
| 103 | acme | apricot | 2.20 |
| 101 | FastFruit Inc. | blackberry | 10.20 |
| 104 | fnk inc. | berry | 7.60 |
| 107 | dk inc. | xxxx | 3.60 |
| 102 | LT Supplies | orange | 11.20 |
| 105 | good set | melon | 8.20 |
| 101 | FastFruit Inc. | cherry | 3.20 |
| 104 | fnk inc. | lemon | 6.40 |
| 106 | just eat ours | mango | 15.70 |
| 105 | good set | xbabay | 2.60 |
| 105 | good set | xxtt | 11.60 |
| 103 | acme | coconut | 9.20 |
| 102 | LT Supplies | banana | 10.30 |
| 102 | LT Supplies | grape | 5.30 |
| 107 | dk inc. | xbababa | 3.60 |
-------------------------------------------
16 rows in set (0.04 sec) 如果在一个连接查询中涉及的两个表都是同一个表这种查询称为自连接查询。自连接是一种特殊的内连接它是指相互连接的表在物理上为同一张表但可以在逻辑上分为两张表。mysql select f1.f_id,f1.f_name- from fruits as f1, fruits as f2- where f1.s_id f2.s_id and f2.f_id a1;
------------------
| f_id | f_name |
------------------
| a1 | apple |
| b1 | blackberry |
| c0 | cherry |
------------------
3 rows in set (0.00 sec)外连接查询 外连接查询将将查询多个表中相关联的行内连接时返回查询结果集合中的仅是符合查询条件和连接条件的行。但有时候需要包含没有关联的行中数据即返回查询结果集合中的不仅包含符合连接条件的行而且还包含左表左外连接或左连接、右表右外连接或右连接或两个连接表全外连接中的所有数据行。外连接分为左外连接或左连接和右外连接或右连接。 Left join左连接返回包括左表中的所有记录和右表中连接字段相等的记录。 Right join右连接返回包括右表中的所有记录和左表中连接字段相等的记录。mysql create table orders- (- o_num int not null auto_increment,- o_date datetime not null,- c_id int not null,- primary key(o_num)- ) ;
Query OK, 0 rows affected (0.01 sec)
mysql insert into orders(o_num,o_date,c_id)- values(30001,2008-09-01,10001),- (30002,2008-09-12,10003),- (30003,2008-09-30,10004),- (30004,2008-10-03,10005),- (30005,2008-10-08,10001);
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 01、左连接mysql select customers.c_id,orders.o_num- from customers left outer join orders- on- customers.c_id orders.c_id;
--------------
| c_id | o_num |
--------------
| 10001 | 30001 |
| 10003 | 30002 |
| 10004 | 30003 |
| 10001 | 30005 |
| 10002 | NULL |
--------------
5 rows in set (0.00 sec)2、右连接mysql select customers.c_id,orders.o_num- from customers right outer join orders- on- customers.c_id orders.c_id;
--------------
| c_id | o_num |
--------------
| 10001 | 30001 |
| 10003 | 30002 |
| 10004 | 30003 |
| NULL | 30004 |
| 10001 | 30005 |
--------------
5 rows in set (0.00 sec)复合条件连接查询 复合条件连接查询是在连接查询的过程中通过添加过滤条件限制查询的结果使查询的结果更加准确。mysql select customers.c_id,orders.o_num- from customers inner join orders- on- customers.c_id orders.c_id and customers.c_id 10001;
--------------
| c_id | o_num |
--------------
| 10001 | 30001 |
| 10001 | 30005 |
--------------
2 rows in set (0.00 sec)
mysql select suppliers.s_id,s_name,f_name,f_price- from fruits inner join suppliers- on- fruits.s_id suppliers.s_id- order by fruits.s_id;
-------------------------------------------
| s_id | s_name | f_name | f_price |
-------------------------------------------
| 101 | FastFruit Inc. | apple | 5.20 |
| 101 | FastFruit Inc. | blackberry | 10.20 |
| 101 | FastFruit Inc. | cherry | 3.20 |
| 102 | LT Supplies | orange | 11.20 |
| 102 | LT Supplies | banana | 10.30 |
| 102 | LT Supplies | grape | 5.30 |
| 103 | acme | apricot | 2.20 |
| 103 | acme | coconut | 9.20 |
| 104 | fnk inc. | berry | 7.60 |
| 104 | fnk inc. | lemon | 6.40 |
| 105 | good set | melon | 8.20 |
| 105 | good set | xbabay | 2.60 |
| 105 | good set | xxtt | 11.60 |
| 106 | just eat ours | mango | 15.70 |
| 107 | dk inc. | xxxx | 3.60 |
| 107 | dk inc. | xbababa | 3.60 |
-------------------------------------------
16 rows in set (0.01 sec)子查询 子查询指一个查询语句嵌套在另一个查询语句内部的查询这个特性从mysql4.1开始引入。在select子句中先计算子查询子查询结果作为外层另一个查询的过滤条件查询可以基于一个表或者多个表。 子查询中常用的操作符有anysome、all、in、exists。子查询可以添加到select、update和delete语句中而且可以进行多层嵌套。子查询中也可以使用比较运算符如“”,“”,“”,“”和“!”等。带any、some关键字的子查询 any和some关键字是同义词表示满足其中任一条件它们允许创建一个表达式对子查询的返回值列进行比较只要满足内层子查询中的任何一个比较条件就返回一个结果作为外层查询的条件。 创建两个表并插入数据mysql create table tb1(num1 int not null);
Query OK, 0 rows affected (0.01 sec)
mysql create table tb2(num2 int not null);
Query OK, 0 rows affected (0.01 sec)
mysql insert into tb1 values(1),(5),(13),(27);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql insert into tb2 values(6),(14),(11),(20);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0 查看满足条件的行mysql select num1 from tb1 where num1 any (select num2 from tb2);
------
| num1 |
------
| 13 |
| 27 |
------
2 rows in set (0.00 sec)带all关键字的子查询 all关键字与any和some不同使用all时需要同时满足所有内层查询的条件。mysql select num1 from tb1 where num1 all (select num2 from tb2);
------
| num1 |
------
| 27 |
------
1 row in set (0.00 sec)带exists关键字的子查询 exists关键字后面的参数是一个任意的子查询系统对子查询进行运算以判断它是否返回行如果至少返回一行那么exists的结果为true此时外层查询语句将进行查询如果子查询没有返回任何行那么exists返回的结果是false此时外层语句将不进行查询。mysql select * from fruits where exists (select s_name from suppliers where s_id 107);
---------------------------------
| f_id | s_id | f_name | f_price |
---------------------------------
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
---------------------------------
16 rows in set (0.00 sec)带in关键字的子查询 in关键字进行子查询时内层查询语句仅仅返回一个数据列这个数据列里的值将提供给外层查询语句进行比较操作。mysql select c_id from orders where o_num in- (select o_num from orderitems where f_id c0);
-------
| c_id |
-------
| 10004 |
| 10001 |
-------
2 rows in set (0.00 sec)带比较运算符的子查询mysql select s_id,f_name from fruits- where s_id - (select s1.s_id from suppliers as s1 where s1.s_citytianjin);
------------------
| s_id | f_name |
------------------
| 101 | apple |
| 101 | blackberry |
| 101 | cherry |
------------------
3 rows in set (0.00 sec)合并查询结果 利用union关键字可以给出多条select语句并将它们的结果组合成单个结果集。合并时两个表对应的列数和数据类型必须相同。各个select语句之间使用union或union all关键字分隔。union不使用关键字all执行的时候删除重复的记录所有返回的行都是唯一的使用关键字all的作用是不删除重复行也不对结果进行自动排序。mysql select s_id,f_name,f_price- from fruits- where f_price 9.0- union all- select s_id,f_name,f_price- from fruits- where s_id in(101,103);
---------------------------
| s_id | f_name | f_price |
---------------------------
| 101 | apple | 5.20 |
| 103 | apricot | 2.20 |
| 104 | berry | 7.60 |
| 107 | xxxx | 3.60 |
| 105 | melon | 8.20 |
| 101 | cherry | 3.20 |
| 104 | lemon | 6.40 |
| 105 | xbabay | 2.60 |
| 102 | grape | 5.30 |
| 107 | xbababa | 3.60 |
| 101 | apple | 5.20 |
| 103 | apricot | 2.20 |
| 101 | blackberry | 10.20 |
| 101 | cherry | 3.20 |
| 103 | coconut | 9.20 |
---------------------------
15 rows in set (0.00 sec) union和union all的区别使用union all的功能是不删除重复行加上all关键字语句执行时所需要的资源少所以尽可能地使用它因此知道有重复行但是想保留这些行确定查询结果中不会有重复数据或者不需要去掉重复数据的时候应当使用union all以提高查询效率。mysql select s_id,f_name,f_price- from fruits- where f_price 9.0- union- select s_id,f_name,f_price- from fruits- where s_id in(101,103);
---------------------------
| s_id | f_name | f_price |
---------------------------
| 101 | apple | 5.20 |
| 103 | apricot | 2.20 |
| 104 | berry | 7.60 |
| 107 | xxxx | 3.60 |
| 105 | melon | 8.20 |
| 101 | cherry | 3.20 |
| 104 | lemon | 6.40 |
| 105 | xbabay | 2.60 |
| 102 | grape | 5.30 |
| 107 | xbababa | 3.60 |
| 101 | blackberry | 10.20 |
| 103 | coconut | 9.20 |
---------------------------
12 rows in set (0.01 sec)为表和字段取别名 前面介绍了分组查询、聚合函数查询和嵌套子查询取别名使用关键字as为查询结果中的某一列指定一个特别的名字。可以为字段或者表分别取别名在查询时使用别名替代指定的内容。为表取别名mysql select * from orders as abc where abc.o_num 30001;
-----------------------------------
| o_num | o_date | c_id |
-----------------------------------
| 30001 | 2008-09-01 00:00:00 | 10001 |
-----------------------------------
1 row in set (0.00 sec)为字段取别名 先给fruits表指定别名为faf_name列名指定为fruits_namef_price列名指定为fruits_price并且价格小于8mysql select fa.f_name as fruits_name,fa.f_price as fruits_price- from fruits as fa- where fa.f_price 8;
---------------------------
| fruits_name | fruits_price |
---------------------------
| apple | 5.20 |
| apricot | 2.20 |
| berry | 7.60 |
| xxxx | 3.60 |
| cherry | 3.20 |
| lemon | 6.40 |
| xbabay | 2.60 |
| grape | 5.30 |
| xbababa | 3.60 |
---------------------------
9 rows in set (0.00 sec)使用正则表达式查询 正则表达式通常被用来检索或替换那些符合某个模式的文本内容根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如从一个文本文件中提取电话号码查找一篇文章中重复的单词或者替换用户输入的某些敏感词语等等这些地方都可以使用正则表达式。正则表达式强大且灵活可以应用于非常复杂的查询。mysql中使用regexp关键字指定正则表达式的字符匹配模式。选项说明例子匹配值实例^匹配文本的开始字符^b匹配以字母b开头的字符串bookbigbike$匹配文本的结束字符st$匹配以st结尾的字符串testresist.匹配任何单个字符b.t匹配任何b和t之间有一个字符bitbatbut*匹配零个或者多个在它前面的字符f*n匹配字符n前面有任意个字符fnfanfaaan匹配前面的字符1次或多次ba匹配以b开头后面紧跟至少有一个ababaybarebattle字符串匹配包含指定的字符串的文本fa fan,afa,faad[字符集合]匹配字符集合中的任何一个字符[xz]匹配x或者zdizzyzabraextra[^]匹配不在括号中的任何字符[^abc]匹配任何不包括a、b或c的字符串deskfoxf8ke字符串{n,}匹配前面的字符串至少n次b{2}匹配2个或者更多的bbbb,bbbbb字符串{n,m}匹配前面的字符串至少n次至多m次。如果n为0则省略b{2,4}匹配最少2个最多4个bbb,bbb,bbbb查询以特定字符或字符串开头的记录mysql select * from fruits where f_name regexp ^b;
---------------------------------
| f_id | s_id | f_name | f_price |
---------------------------------
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| t1 | 102 | banana | 10.30 |
---------------------------------
3 rows in set (0.00 sec)查询以特定字符或字符串结尾的记录mysql select * from fruits where f_name regexp y$;
---------------------------------
| f_id | s_id | f_name | f_price |
---------------------------------
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| c0 | 101 | cherry | 3.20 |
| m2 | 105 | xbabay | 2.60 |
---------------------------------
4 rows in set (0.05 sec)用符合‘.’来代替字符串中的任意一个字符mysql select * from fruits where f_name regexp a.g;
-----------------------------
| f_id | s_id | f_name | f_price |
-----------------------------
| bs1 | 102 | orange | 11.20 |
| m1 | 106 | mango | 15.70 |
-----------------------------
2 rows in set (0.00 sec)使用“*”和“”来匹配多个字符mysql select * from fruits where f_name regexp ^ba*;
---------------------------------
| f_id | s_id | f_name | f_price |
---------------------------------
| b1 | 101 | blackberry | 10.20 |
| b2 | 104 | berry | 7.60 |
| t1 | 102 | banana | 10.30 |
---------------------------------
3 rows in set (0.00 sec)
mysql select * from fruits where f_name regexp ^ba;
-----------------------------
| f_id | s_id | f_name | f_price |
-----------------------------
| t1 | 102 | banana | 10.30 |
-----------------------------
1 row in set (0.00 sec)匹配指定字符串mysql select * from fruits where f_name regexp on|ap;
------------------------------
| f_id | s_id | f_name | f_price |
------------------------------
| a1 | 101 | apple | 5.20 |
| a2 | 103 | apricot | 2.20 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| o2 | 103 | coconut | 9.20 |
| t2 | 102 | grape | 5.30 |
------------------------------
6 rows in set (0.00 sec)
mysql select * from fruits where f_name like on;
Empty set (0.00 sec)匹配指定字符中的任意一个mysql select * from fruits where f_name regexp [ot];
------------------------------
| f_id | s_id | f_name | f_price |
------------------------------
| a2 | 103 | apricot | 2.20 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
------------------------------
7 rows in set (0.00 sec)mysql select * from fruits where s_id regexp [456];
-----------------------------
| f_id | s_id | f_name | f_price |
-----------------------------
| b2 | 104 | berry | 7.60 |
| bs2 | 105 | melon | 8.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
-----------------------------
6 rows in set (0.00 sec)匹配指定字符以外的字符mysql select * from fruits where f_id regexp [^a-e1-2];
------------------------------
| f_id | s_id | f_name | f_price |
------------------------------
| b5 | 107 | xxxx | 3.60 |
| bs1 | 102 | orange | 11.20 |
| bs2 | 105 | melon | 8.20 |
| c0 | 101 | cherry | 3.20 |
| l2 | 104 | lemon | 6.40 |
| m1 | 106 | mango | 15.70 |
| m2 | 105 | xbabay | 2.60 |
| m3 | 105 | xxtt | 11.60 |
| o2 | 103 | coconut | 9.20 |
| t1 | 102 | banana | 10.30 |
| t2 | 102 | grape | 5.30 |
| t4 | 107 | xbababa | 3.60 |
------------------------------
12 rows in set (0.00 sec)使用{n,}或者{n,m}来指定字符串连续出现的次数mysql select * from fruits where f_name regexp x{2,};
-----------------------------
| f_id | s_id | f_name | f_price |
-----------------------------
| b5 | 107 | xxxx | 3.60 |
| m3 | 105 | xxtt | 11.60 |
-----------------------------
2 rows in set (0.00 sec)
mysql select * from fruits where f_name regexp ba{1,3};
------------------------------
| f_id | s_id | f_name | f_price |
------------------------------
| m2 | 105 | xbabay | 2.60 |
| t1 | 102 | banana | 10.30 |
| t4 | 107 | xbababa | 3.60 |
------------------------------
3 rows in set (0.00 sec)
