MySQL查询

原文：http://yanqiaosun.blog.163.com/blog/static/216133133201302342224106/

一、MySQL中select的基本语法形式：

select 属性列表

from 表名和视图列表

[where 条件表达式1]

[group by 属性名1[having 条件表达式2]]

[order by 属性名2[asc|desc]]

说明：

1、如果有where子句，就按照“条件表达式1”指定的条件进行查询。如果没有where子句，就查询所有记录；

2、如果有group by子句，就按照“属性名1”指定的字段 进行分组。如果group by子句后带着having关键字，那么只有满足“条件表达式2”中指定的条件的才能够输出。group by子句通常和count()、sum()等聚合函数一起使用；

3、如果有order by子句，就按照“属性名2”指定的字段进行排序。排序方式由“asc”和“desc”两个参数指出，默认的情况时“asc”。

 

二、单表查询

    单表查询是指从一张表中查询所需要的数据。查询数据时，可以从一张表中查询数据，也可以从多张表中查询数据。两者的查询方式上有一定的区别。因为单表查询只在一张表上进行操作，所以查询比较简单。

1、查询所有字段：

（1）列出表的所有字段

（2）使用“*”查询所有字段

2、查询指定字段：

在属性列表中列出所要查询的字段，查询结果显示顺序与select属性列表中设定顺序相同。

3、查询指定记录：

where子句可以用来指定查询条件

4、带in关键字的查询：

语法规则 [not]in（元素1,元素2,…,元素n）

5、带between and 的范围查询

select * from employee where age not between 15 and 25

6、带like的字符匹配查询

（1）完整字符串匹配：

select * from employee where name like 'Aric';select * from employee where name = 'Aric';

like跟=相同。

（2）不完整字符串匹配(模糊查询)，使用通配符的时候就不能用等号了：

借助于通配符%（%为任意字符或字符串）

select * from employee where homeaddr like '北京%';匹配homeaddr前两个汉字为'北京'的记录，此时用=查询结果为空。

借助于通配符_(_的位置为一个任意字符)

select * from employee where name like 'Ar_c'

not like 与like 用法类同。

7、查询空值（is null）：

如果字段的值是空值，则满足查询条件，该记录被查询出来。如果字段的值不是空值，则不满足查询条件。

其语法规则如下：

is [not] null

其中，“not”是可选参数，加上not表示字段不是空值满足条件。

8、带and的多条件查询：

and关键字可以用来联合多个条件进行查询。使用and关键字时，只有同时满足所有查询条件的记录才会被查询出来，如果不满足这些查询条件的其中一个，这样的记录将被排除。

and关键字的语法规则如下：

条件表达式1 and 条件表达式2 […and 条件表达式n]

其中，and可以连接两个条件表达式。而且，可以同时使用多个and关键字，这样可以连接更多的条件表达式。

9、带or的多条件查询

只要满足这几个查询条件中的一个，这样的记录将会被查询出来。如果不满足这些查询条件的任何一个，这样的记录将被排除掉。or关键字的语法规则跟and 类同。

10、查询结果不重复

如果表中某些字段上没有唯一性约束，这些字段可能存在着重复的值。可以使用distinct关键字来消除字段中的重复记录。

例子：select distinct d_id from employee;

11、对查询结果排序

从表中查询出来的数据可能是无序的，或者其排列顺序不是用户所期望的顺序，为了是查询结果的顺序满足用户的要求，可以使用order by对记录进行排序。

语法规则：

order by 属性名[asc|desc]

例子：select * from employee order by age;select * from employee order by d_id asc,age desc;

12、分组查询

group by关键字可以将查询结果按某个字段或者多个字段进行分组。字段中值相等的为一组。

其语法规则：

group by 属性名[having 条件表达式][with rollup]

有以下几种情况：

单独使用group by关键字来分组

group by关键字与group_concat()函数一起使用

group by关键字与集合函数一起使用

group by关键字与having一起使用

按多个字段进行分组

group by 关键字与with rollup一起使用

例子：

 select * from employee

 group by d_id；

当有两条记录的d_id相同时，只能输出第一条。

用group by对多个字段进行分组的时候，首先按照第一个字段分组，在第一个字段相同时再按第二个字段进行分组。

select sex,count(sex) from employee

group by sex with rollup;

with rollup在最后插入一条求和记录。

 

例子：

mysql> select sex,group_concat(name) from employee

    -> group by sex with rollup;

+------+---------------------+

| sex  | group_concat(name)  |

+------+---------------------+

| 男   | 张三,王五,Aric      |

| 女   | 李四                |

| NULL | 张三,王五,Aric,李四 |

+------+---------------------+

3 rows in set (0.00 sec)

13、用limit限制查询结果的数量：

可以用来指定查询结果从哪条记录开始显示。还可以指定一共显示多少条记录。有两种使用方式。

（1）不指定初始位置

 select * from employee limit 2;

不指定位置时，从第一条开始，按limit指定数量显示。

如果limit值大于表中记录数，会显示表中所有记录。

（2）指定初始位置

 select * from employee limit 0,2;

0指初始位置，2表示记录数。

从第一条记录开始，查询数量为2条。

mysql中0表示第一条，1表示第二条。

三、使用集合函数查询（group by关键字通常需要与集合函数一起使用）

1、count()用来统计记录的条数

select count(*) from employee;

查询employee表中的所有记录数

select d_id,count(*)from employee

group by d_id;

统计employee表中不同d_id值的记录数

2、sum()用来计算字段的值的综合

select num,sum(score) from grade where num=1001;

统计学号为1001的学生课程成绩的总和

select num,sum(score) from grade group by num;

按学号统计每个同学课程成绩的总和

3、avg()用来计算字段的值的平均值

select num,avg(score) from grade group by num;

求每个学生的平均分数

select course,avg(score) from grade group by course;

求每个科目的平均分数

4、max()用来查询字段的最大值

select num,course,max(score) from grade group by course;

例子：

mysql> select * from work;

+------+------+------+---------+

| id   | name | sex  | info    |

+------+------+------+---------+

| 1001 | hjh  | NULL | NULL    |

| 1002 | cch  | NULL | NULL    |

| 1003 | zk   | NULL | student |

+------+------+------+---------+

3 rows in set (0.00 sec)

mysql> select max(name) from work;

+-----------+

| max(name) |

+-----------+

| zk        |

+-----------+

1 row in set (0.00 sec)

字符串比较，先比较第一个字符的asi码，相等时再比较后面的字符。

求出每一科目的最高分的学号、科目和成绩。

5、min()用来查询字段的最小值（类同于max()）

四、连接查询

    连接查询是将两个或两个以上的表按某个条件连接起来，从中选择选取需要的数据，连接查询是同时查询两个或两个以上的表时使用的。当不同的表中存在表示相同意义的字段时，可以通过该字段来连接这几个表。

1、内连接查询

    当两个表中存在表示相同意义的字段时，可以通过该字段来连接这两张表。当该字段的值相等时，就查询出该记录

例子：

mysql> select num,name,employee.d_id,age,sex,d_name,function

    -> from employee,department

    -> where employee.d_id=department.d_id;

+-----+------+------+------+------+--------+----------+

| num | name | d_id | age  | sex  | d_name | function |

+-----+------+------+------+------+--------+----------+

|   1 | 张三 | 1001 |   26 | 男   | 科研部 | 研发产品 |

|   2 | 李四 | 1001 |   24 | 女   | 科研部 | 研发产品 |

|   3 | 王五 | 1002 |   25 | 男   | 生产部 | 生产产品 |

+-----+------+------+------+------+--------+----------+

3 rows in set (0.00 sec)

当表中存在两个字段名相同，查询时必须指定是哪张表的哪个字段。

2、外连接查询

    外连接查询可以查询两个或两个以上的表。外连接查询也需要通过指定字段来进行连接。当该字段取值相等时可以查询出该记录。而且，该字段取值不相等的记录也可以查询出来。外连接包括左连接查询和右连接查询。其基本语法如下：

select 属性名列表

from 表名1 left|right join 表名2

on 表名1.属性1=表名2.属性2；

左连接查询中，可以显示表名1中的所有记录，表名2中只有匹配的记录可以显示呢出来。右连接类同。

左连接右连接实例：

mysql> select * from department;

+------+--------+----------+---------------+

| d_id | d_name | function | address       |

+------+--------+----------+---------------+

| 1001 | 科研部 | 研发产品 | 3号楼5层      |

| 1002 | 生产部 | 生产产品 | 5号楼1层      |

| 1003 | 销售部 | 策划销售 | 1号楼销售大厅 |

+------+--------+----------+---------------+

3 rows in set (0.00 sec)

mysql> select * from employee;

+-----+------+------+------+------+--------------+

| num | d_id | name | age  | sex  | homeaddr     |

+-----+------+------+------+------+--------------+

|   1 | 1001 | 张三 |   26 | 男   | 北京市海淀区 |

|   2 | 1001 | 李四 |   24 | 女   | 北京市昌平区 |

|   3 | 1002 | 王五 |   25 | 男   | 湖南省长沙市 |

|   4 | 1004 | Aric |   15 | 男   | England      |

+-----+------+------+------+------+--------------+

4 rows in set (0.00 sec)

mysql>  select num,name,employee.d_id,age,sex,d_name,function

    ->  from employee left join department

    ->  on employee.d_id=department.d_id;

+-----+------+------+------+------+--------+----------+

| num | name | d_id | age  | sex  | d_name | function |

+-----+------+------+------+------+--------+----------+

|   1 | 张三 | 1001 |   26 | 男   | 科研部 | 研发产品 |

|   2 | 李四 | 1001 |   24 | 女   | 科研部 | 研发产品 |

|   3 | 王五 | 1002 |   25 | 男   | 生产部 | 生产产品 |

|   4 | Aric | 1004 |   15 | 男   | NULL   | NULL     |

+-----+------+------+------+------+--------+----------+

4 rows in set (0.00 sec)

mysql>  select num,name,employee.d_id,age,sex,d_name,function

    ->  from employee right join department

    ->  on employee.d_id=department.d_id;

+------+------+------+------+------+--------+----------+

| num  | name | d_id | age  | sex  | d_name | function |

+------+------+------+------+------+--------+----------+

|    1 | 张三 | 1001 |   26 | 男   | 科研部 | 研发产品 |

|    2 | 李四 | 1001 |   24 | 女   | 科研部 | 研发产品 |

|    3 | 王五 | 1002 |   25 | 男   | 生产部 | 生产产品 |

| NULL | NULL | NULL | NULL | NULL | 销售部 | 策划销售 |

+------+------+------+------+------+--------+----------+

4 rows in set (0.00 sec)

4、复合条件连接查询

    在连接查询时。可以增加其他的限制条件。通过多个条件的复合查询，可以使查询结果更加准确。

例子：

内连接查询的方式查询employee表和department表，并且employee表中的age字段值必须大于24，select语句如下：

select num,name,employee.d_id,age,sex,d_name,function

from employee,department

where employee.d_id=department.d_id

and age>24;

内连接查询的方式查询employee表和department表。并且以age字段升序的方式显示查询结果。select语句如下：

select num,name,employee.d_id,age,sex,d_name,function

from employee,department

where employee.d_id=department.d_id

order by age asc;

五、子查询

   子查询是将一个查询语句嵌套到另一个查询语句中。内层查询语句的查询结果，可以为外层查询语句提供查询条件。在特定情况下，一个查询语句需要另一个查询语句来获取。

1、带in关键字的子查询:

 一个查询语句的条件可能落在另一个select语句的查询结果中，这可以通过in关键字来判断。

查询employee表中的记录，这些记录的d_id字段的值必须在department表中出现过：

select * from employee

where d_id in

(select d_id from department);

查询employee表中的记录，这些记录的d_id字段的值必须没有在department表中出现过；

select * from employee

where d_id not in

(select d_id from department);

2、带比较运算符的子查询：

 这些运算符包括=、!=、>、>=、<、<=、<>等，其中<>与!=是等价的。

从computer_stu表中查询获得一等奖学金的学生学号、姓名和分数，各个等级奖学金的最低分存储在scholarship表中。

select id,name,score from computer_stu

where score>=

(select score from scholarship

where level=1);

在department表中查询哪些部门没有年龄为24岁的员工

select d_id,d_name from department

where d_id!=

(select d_id from employee

where age=24);

用<>是一样的结果：

select d_id,d_name from department

where d_id<>

(select d_id from employee

where age=24);

3、带exists关键字的子查询：

    exists关键字表示存在。使用exists关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句查询到满足条件的记录，就返回一个真值（true）。否则，将返回一个假植（false）。当返回的值是真值时，外层查询语句将进行查询。当返回假值时，外层查询语句不进行查询或者查询不出任何记录。

如果department表中存在d_id取值为1003的记录，则查询employee表：

select * from employee

where exists

(select d_name from department

where d_id=1003);

如果department表中存在d_id取值为1003的记录，则查询employee表中age>24的记录：

select * from employee

where age>24 and exists

(select d_name from department

where d_id=1003);

如果department表中不存在d_id取值为1003的记录，则查询employee表：

select * from employee

where not exists

(select d_name from department

where d_id=1003);

4、带any关键字的子查询：

?    any关键字表示满足其中任一条件。使用any关键字时，只要满足内层查询语句返回的结果的任何一个，就可以通过该条件来执行外层查询语句。通常与比较运算符一起使用。

从computer_stu表中查询出哪些同学可以获得奖学金。奖学金的信息存储在scholarship表中：

select * from computer_stu

where score>=any

(select score from scholarship);

5、带all关键字的子查询：

    使用all关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。

从computer表中查询出哪些同学可以获得一等奖学金（先从scholarship中查询出各种奖学金的最低分，然后从computer_stu表中查询哪些人的分数高于所有奖学金的最低分）：

select * from computer_stu

where score>=all

(select score from sholarship);

6、合并查询结果：

    将多个select语句的查询结果合并到一起，因为某种情况下，需要将几个select语句查询出来的结果合并起来显示。

进行合并操作使用union和union all关键字使用union关节囊字时，数据库系统会将所有查询结果合并到一起，然后去除掉相同的记录。而union all关键字则只是简单的合并到一起。

语法规则：

select 语句1

union|union all

select 语句2

union|union all…

select 语句n

从department和employee 表中查询d_id字段的取值，然后通过union关键字将结果合并到一起：

select d_id from department

union

select d_id from employee;

7、为表和字段取别名：

(1)当表的名称特别长时。在查询中直接使用表名很不方便，这时可以为表取一个别名。

为department表取别名d，然后查询表中d_id字段取值为1001的记录。

select * from department d

where d.d_id=1001;

(2)为字段取别名

基本语法 属性名 [as] 别名

例子：

为department表中的d_id字段和d_name字段分别取名为department_id和department_name

 select d_id as department_id,d_name as department_name

 from department;

为表和字段取别名同时使用：

为department表中的d_id字段和d_name字段分别取名为department_id和department_name，为department表取别名d，然后查询department表中d_id字段取值为1001的记录：

select d.d_id as department_id,d.d_name as department_name,d.function,d.address

from department d

where d.d_id=1001;

8、使用正则表达式查询

    正则表达式是用某种模式去匹配一类字符串的一个方式。例如：使用正则表达式可以查询出包含A、B、C其中任一字母的字符串。正则表达式的查询能力比通配字符的查询能力更强大，而且更加的灵活。正则表达式可以应用于非常复杂的查询。

基本形式：

属性名 regexp ‘匹配方式'

例子代码：

 create table info(

 id int,

 name varchar(20)

 );

insert into info values(1,'Aric');

insert into info values(2,'Eric');

insert into info values(4,'Jack');

insert into info values(5,'Lucy');

insert into info values(6,'Lily');

insert into info values(7,'Tom');

insert into info values(8,'aaa');

insert into info values(9,'dadaaa');

insert into info values(10,'aaabd');

insert into info values(11,'abc12');

insert into info values(12,'ad321');

(1)查询以特定字符或字符串开头的记录

使用字符“^”可以匹配特定字符或字符串开头的记录

从info表name字段查询以字母“L”开头的记录：

select* from info where name regexp '^L';

(2)查询以特定字符或字符串结尾的记录

使用字符“$”可以匹配以特定字符或字符串结尾的记录

从info表name字段中查询以字母“c”结尾的记录

select* from info where name regexp 'c$';

(3)用符号“.”来替代字符串中的任意一个字符

从info表name字段中查询以字母“”开头，以字母“y”结尾，中间有两个任意字符的记录

select* from info where name regexp '^L..y$';

(4)匹配指定字符中的任意一个

使用([])可以将需要查询的字符组成一个字符集。只要记录中包含方括号中的任意字符，该记录将会被查询出来。

从info表name字段中查询包含“c”、“e”、“o”这三个字母中任意一个的记录：

select * from info where name regexp '[ceo]';

也可以是数字：

select * from info where name regexp '[0-9]';

从info表name字段中查询包含数字或者字母a、b、c的记录：

select * from info where name regexp '[0-9a-c]';

(5)匹配指定字符以外的字符

从info表name字段中查询包含a到w和数字以外的字符的记录：

select * from info where name regexp '[^a-w0-9]';

(6)匹配指定字符串

从info表name字段查询包含“ic”的记录：

select * from info where name regexp 'ic';

从info表name字段查询包含“ic”“uc”“ab”这三个字符串任意一个的记录：

select * from info where name regexp 'ic|uc|ab';

(7)使用“*”“+”来匹配多个字符

“*”“+”都可以来匹配多个该符号之前的字符，但是“+”至少表示一个字符，而“：*”可以表示零个字符。

从info表name字段查询字母“c”之前出现过a的记录：

select * from info where name regexp 'a*c';

例子：

mysql> select * from info where name regexp 'a*c';

+------+-------+

| id   | name  |

+------+-------+

|    1 | Aric  |

|    2 | Eric  |

|    4 | Jack  |

|    5 | Lucy  |

|   11 | abc12 |

+------+-------+

5 rows in set (0.00 sec)

mysql> select * from info where name regexp 'a+c';

+------+------+

| id   | name |

+------+------+

|    4 | Jack |

+------+------+

1 row in set (0.00 sec)

(8)使用{M}或者{M,N}来指定字符串连续出现的次数

从info表name字段中查询出现过“a”三次的记录：

select * from info where name regexp 'a{3}';

从info表name字段中查询出现过“ab”最少一次，最多三次的记录：

select * from info where name regexp 'ab{1,3}';