mysql------查询数据

1、基本查询语句

语法：select    [字段1，字段2，... ]    from     [表或视图]     where      [查询条件]  ；

2、单表查询

（1）带in关键字的查询，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围中的一个值即可。

如：从tb_emp表查询id是1或2的name和saraly字段。

select   name,salary   from  tb_emp  where  id  in(1,2);

相反的，用not in 来检索不在条件范围内的记录

如：

select   name,salary from  tb_emp   where   id   not   in(1,2);

（2）between...and....的范围查询，包含端点值。

如：从tb_emp表中查询工资在1000到3000的name和salary字段。

select name,salary from tb_empwhere salary between 1000 and 3000;

相反的，用not  between  and查询范围之外

如：

select name,salary from tb_emp where salary not between 1000 and3000;

（3）带like的字符匹配查询

a、百分号通配符%，匹配任意长度的字符，甚至是零字符，%可以放在不同的位置

如：从tb_emp表中查询名字以林开头的信息

select * from tb_emp where namelike ‘林%’;

b、下划线通配符_，一次只能匹配任意一个字符，如果要匹配多个字符，则需要使用多个下划线_。

如：从tb_emp表中查询名字为林某烽的人的信息

select * from tb_emp where namelike ‘林_烽’;

（4）查询空值，空值不同于0，也不同于空字符串，空值一般表示数据未知，不适用或将在以后添加数据，

在select语句中使用is null可以查询字段内容为空记录。

如：从tb_emp中查询名字为空的人的信息。相反的，not is null查找字段不为空的记录

select * from tb_emp where name isnull;

（5）带and的多条件查询，需要满足所有查询条件

如：

select * from tb_emp where name like ‘林%’and salary>=4000;

（6）带or的多条件查询，满足其中一个条件即可

如：

select * from tb_emp where salary=1000 or salary >=2000;

（7）查询结果不重复：selectdistinct 字段名 from 表名；

如：查询tb_emp的s_id值，返回的s_id字段值不得重复,值返回了不同的s_id的字段

selectdistinct s_id from tb_emp;

（8）对查询结果排序order by，默认是升序排列asc

如：按照name字母表的顺序对查询结果进行了升序的排序。

select * from tb_emp order by name;

当按多列进行排序时，第一列必须有相同的列值，才会对第二列进行排序，如果第一列数据的所有列值都是唯一的，将不在对第二列进行排序。

如：

select * from tb_emp order by name,salary;

在语句末尾加上desc时进行降序排列

如：select * from tb_emp order by name desc;            //按名字降序排列

       select * from tb_emporder by name desc,salary;   //先按名字降序排列然后按工资升序排列

 

（9）分组查询group by

如:按deptid分组查询

select * from tb_emp group by deptid;

用having过滤分组,在数据分组之后进行过滤来选择分组，和group by 一起限制显示记录所有满足的条件

如:

select * from tb_emp group by salary having salary>=1000;

多字段分组，如：

select *from tb_emp group by deptid,salary;

先按deptid进行分组，再按salary分组，只有当deptid和salary都相同时

才合并为一组数据

(10)Limit[位置偏移量],行数：限制查询结果的数量

如：显示查询结果的前4行

select * from tb_emp limit 4;

Limit 4即等价于limit 0,4

Limit 4,3即表示从第5个记录开始之后的3条记录

Mysql5.6中可以使用limit 4 offset 3等价于limit4,3

当limit和order by 一起使用时，limit要位于order by后面

3、使用集合函数查询

(1)count()函数

  count（*）计算表中总的行数，不管某列有数值或者为空值

  count（字段名）计算指定列下的总的行数，计算时将忽略空值的行

如：查询deptid列的行数，并起别名为deptid_total

select count(deptid) as deptid_total from tb_emp;

（2）sun()函数,返回指定列的总和

如：

select sum(salary) from tb_emp;

（3）avg()函数，通过计算返回的行数和每一行数据的和，求得指定列数据的平均值

如：

select avg(salary) from tb_emp;

（4）max()函数，返回指定列的最大值，当对字符类型数据进行比较时，按字符的ASCII码值大小进行比较，从a-z,从小到大

如：

SELECT MAX(f_price) AS max_price FROM fruits;

（5）min()函数，返回指定列的最小值

如：

SELECT MIN(f_price) AS min_price FROM fruits;

4、连接查询

（1）内连接查询：只列出匹配的记录
   语法：
          SELECT … FROM    join_table
        

          [INNER] JOIN join_table2
        

          [ON join_condition]

          WHERE    where_definition

解释：只列出这些连接表中与连接条件相匹配的数据行。

INNER可以不写，则默认为内连接；

[ON join_condition]里面写的是连接的条件。

举例：

select    e.name,  d.name    from   tb_employee  e     inner    join    tb_department  d    on     e.deptid=d.id; 

等价于：

select   e.name,d.name    from     tb_employee  e  , tb_department  d   where      e.deptid=d.id; 

或者不用起别名，直接

select    tb_employe.name, tb_department.name     from   tb_employee   inner   join    tb_department     on    tb_employee.deptid= tb_department.id; 

和

select    tb_employee.name, tb_department.name     from    tb_employee,tb_department     where     tb_employee.dept_id= tb_department.id; 

特殊的内连接(自连接)：参与连接的表都是同一张表。(通过给表取别名虚拟出两张表)
如：

select e1.name 名字,e1.id 编号from tb_emp e1,tb_emp e2 where e1.deptid=e2.deptid;

（2）外连接查询

外连接分类：
      •     左外连接(LEFT [OUTER] JOIN)
      •     右外连接(RIGHT [OUTER] JOIN)
      •     全外连接(FULL [OUTER] JOIN) 注：MySQL5.1的版本暂不支持

语法：
      SELECT … FROM     join_table1

      (LEFT | RIGHT | FULL) [OUTER] JOIN join_table2

      ON    join_condition

      WHERE    where_definition

解释：
不仅列出与连接条件（on）相匹配的行，还列出左表table1(左外连接)、或右表table2(右外连接)、或两个表(全外连接)中所有符合WHERE过滤条件的数据行。一般都是用左连接或者外连接。
其中，[OUTER]部分可以不写，(LEFT | RIGHT | FULL)部分要写其中一个。

左外连接：返回左表的全部记录，右表只列出连接字段相等的记录。

如:

select   tb_emp.name ,tb_dept.location   from   tb_emp   left   join   tb_dept   on   tb_emp.deptid=tb_dept.id;

或用别名

select   e.name,d.location   from   tb_emp  e    left   join   tb_dept d    on   e.deptid=d.id;

列出了tb_emp表的全部name字段记录，和tb_dept表中与之匹配的location列的记录。如果左表的某行在右表中没有匹配的行，则在相关联的结果中，右表的所有选择列表列均为空值。

右外连接：返回右表的全部记录，左表只列出连接字段相等的记录。
如：

select   tb_emp.name ,tb_dept.location  from   tb_emp    right    join   tb_dept   on    tb_emp.deptid=tb_dept.id;

或用别名

select    e.name,d.location    from   tb_emp e    right   join   tb_dept d     on   e.deptid=d.id;

列出了tb_dept表的全部location字段记录，和tb_emp表中与之匹配的name列的记录。如果右表的某行在左表中没有匹配的行，则在相关联的结果中，左表的所有选择列表列均为空值。

（3）交叉连接：没有ON子句和WHERE子句，它返回的是连接表中所有数据行的笛卡尔积
语法：
SELECT … FROM    join_table1   CROSS JOIN   join_table2;

笛卡尔积举例：假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}
其结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数

等价于：（荐）
SELECT … FROM table1, table2;

如：

select    tb_emp.name,tb_dept.name    from    tb_emp,tb_dept;

或

select    e.name,d.name   from   tb_emp e,tb_dept d

5、子查询

指一个查询语句嵌套在另一个查询语句内部的查询

（1）带ANY、SOME关键字的子查询

ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

例1：返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何1个值，即为符合查询条件的结果

 SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);

（2） 带ALL关键字的子查询

ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。
例1：返回tbl1表中比tbl2表num2 列所有值都大的值

 SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);

（3）带EXISTS关键字的子查询

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

例1：查询suppliers表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的记录

 SELECT * FROM fruits     WHERE EXISTS     (SELECT s_name FROM suppliers WHERE s_id = 107);

例2：查询suppliers表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的f_price大于10.20的记录

SELECT * FROM fruits     WHERE f_price>10.20 AND EXISTS     (SELECT s_name FROM suppliers WHERE s_id = 107);

例3：查询suppliers表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录

 SELECT * FROM fruits     WHERE NOT EXISTS     (SELECT s_name FROM suppliers WHERE s_id = 107);

（4）带IN关键字的子查询

IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。

例1：在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id

SELECT c_id FROM orders WHERE o_num IN     (SELECT o_num  FROM orderitems WHERE f_id = 'c0');

（5） 带比较运算符的子查询

子查询时还可以使用其他的比较运算符，如<、<=、=、>=和!=等。

例1：在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有非该供应商提供的水果的种类

SELECT s_id, f_name FROM fruits     WHERE s_id <>     (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

6、使用正则表达式查询

（1）^ 匹配文本的开始字符,‘^b’匹配以字母b开头的字符串，如book,big

如：select * from tb_emp where name regexp ‘^b’;

（2）$匹配文本的结束字符，‘st$’匹配以st结尾的字符串，如test,resist

如：select * from tb_emp where name regexp ‘st$’;

（3）. 匹配任何单个字符，‘a.t’匹配任何a和t之间有一个字符，如ait

如：select * from tb_emp where name regexp ‘a.t’;

（4）*匹配零个或多个在它前面的字符，‘f*n’匹配字符n前面有任意个字符f,如fn,fan,faan,fabcn

如：select * from tb_emp where name regexp ‘ba*’;

（5）+匹配前面的字符一次或者多次，‘ba+’匹配以b开头后面紧跟至少有一个a,如ba,bay,bare,battle

如：select * from tb_emp where name regexp ‘^ba+’;

（6）匹配指定字符串，多个字符串之间使用‘|’隔开

如：select * from tb_emp where name regexp ‘on’;

       select * from tb_empwhere name regexp ‘on|ap’;查询名字带有on或ap的信息

（7）[ ]匹配字符集合中的任何一个字符，[xz]匹配x或者z，如dizzy,extra

如：select * from tb_emp where name regexp ‘[at]’;查询名字带有a或t的信息

（8）[^ ]匹配不在括号中的任何字符，[^abc]匹配任何不包含a、b或c的字符串，如desk,fox

如：select * from tb_emp where name regexp ‘[^at]’;

（9）字符串{n} 匹配前面的字符串至少n次，b{2}匹配2个或更多的b,如bb,bbb

如：select * from tb_emp where name regexp ‘x{2}’;查询name字段出现字符x至少2次的记录

(10)字符串{n,m}匹配前面的字符串至少n次，至多m次，b{2,4}匹配最少2个，最多4个b，如bb,bbb,bbbb

转载请注明出处：http://blog.csdn.net/linshuxin111/