MySQL子查询子查询是将一个查询语句嵌套在另一个查询语句中,内层查询语句的查询结果，可以为外层查询语句提供查询条件
mysql> select * fromemployee where d_id in (select d_id from department);
+-----+------+--------+------+------+--------------------+
| num | d_id |name | age | sex | homeaddr |
+-----+------+--------+------+------+--------------------+
| 1 | 1001 | 张三 | 26 | 男 | 北京市海淀区 |
| 2 | 1001 | 李四 | 24 | 女 | 北京市昌平区 |
| 3 | 1002 | 王五 | 25 | 男 | 湖南长沙市 |
+-----+------+--------+------+------+--------------------+
3 rows in set (0.00sec)
mysql> select *from employee where d_id not in (select d_id from department);
+-----+------+------+------+------+----------+
| num | d_id | name |age | sex | homeaddr |
+-----+------+------+------+------+----------+
| 4 | 1004 | Aric | 15 | 男 | England |
+-----+------+------+------+------+----------+
1 row in set (0.00 sec)
带比较运算符的子查询
=,!=,>,>=,<,<=,<> <>与!=等价
mysql> select * fromscholarship;
+-------+-------+
| level | score |
+-------+-------+
| 1 | 90 |
| 2 | 80 |
| 3 | 70 |
+-------+-------+
3 rows in set (0.00sec)
mysql> select * fromcomputer_stu;
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1001 | lily | 85 |
| 1002 | tom | 91 |
| 1003 | jim | 87 |
| 1004 | aric | 77 |
| 1005 | lucy | 65 |
| 1006 | andy | 99 |
| 1007 | ada | 85 |
| 1008 | jeck |70 |
+------+------+-------+
8 rows in set (0.00sec)
mysql> selectid,name,score from computer_stu
-> where score >=(select score from scholarship where level=1);
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1002 | tom | 91 |
| 1006 | andy |99 |
+------+------+-------+
2 rows in set (0.01sec)
mysql> selectd_id,d_name from department where d_id <>(select d_id from employee whereage=24);
+------+-----------+
| d_id | d_name |
+------+-----------+
| 1002 | 生产部 |
| 1003 | 销售部 |
+------+-----------+
2 rows in set (0.00sec)
带EXISTS关键字的子查询
exists关键字表示存在,使用此关键字时，内层查询语句不返回查询的记录，而是返回一个真假值
mysql> select *from employee where exists (select d_namefrom department where d_id=1003);//语句如果返回真，则执行select语句
+-----+------+--------+------+------+--------------------+
| 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.00sec)
mysql> select *from employee where exists (select d_name from department where d_id=1009);//如果返回的结果为假，则不执行select语句
Empty set (0.00 sec)
与and和or来连接
mysql> select *from employee where age>24 and exists(select d_name from department whered_id=1003);
+-----+------+--------+------+------+--------------------+
| num | d_id |name | age | sex | homeaddr |
+-----+------+--------+------+------+--------------------+
| 1 | 1001 | 张三 | 26 | 男 | 北京市海淀区 |
| 3 | 1002 | 王五 | 25 | 男 | 湖南长沙市 |
+-----+------+--------+------+------+--------------------+
2 rows in set (0.00sec)
带ANY关键字的子查询
any关键字表示满足其中任一条件,只要满足内层查询语句返回的结果中的任何一个，就可以通过该条件来执行外层查询语句
mysql> select *from computer_stu where score>=any(select score from scholarship);
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1001 | lily |85 |
| 1002 | tom | 91 |
| 1003 | jim | 87 |
| 1004 | aric |77 |
| 1006 | andy |99 |
| 1007 | ada | 85 |
| 1008 | jeck |70 |
+------+------+-------+
7 rows in set (0.00sec)
带ALL关键字的子查询
表示满足所有条件，只有满足内层查询语句返回的所有结果时，才可以执行外层查询语句
mysql> select *from computer_stu where score>=all(select score from scholarship);
+------+------+-------+
| id | name | score |
+------+------+-------+
| 1002 | tom | 91 |
| 1006 | andy |99 |
+------+------+-------+
2 rows in set (0.01sec)
合并查询结果
union：合并所有的查询结果，然后去除相同的记录
union all:只是简单的合并到一起
select 语句1
union|union all
select语句2
union|union all
mysql> select d_idfrom department;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
+------+
3 rows in set (0.00sec)
mysql> select d_idfrom employee;
+------+
| d_id |
+------+
| 1001 |
| 1001 |
| 1002 |
| 1004 |
+------+
4 rows in set (0.01sec)
mysql> select d_idfrom department union select d_id from employee;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1004 |
+------+
4 rows in set (0.00sec)
mysql> select d_idfrom department union all select d_id from employee;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
| 1001 |
| 1001 |
| 1002 |
| 1004 |
+------+
7 rows in set (0.00sec)
为表和这字段取别名
表名 表的别名 //为表取别名必须保证该数据库中没有其他表与该别名相同
mysql> select *from department d where d.d_id=1001;
+------+-----------+--------------+-------------+
| d_id | d_name | function | address |
+------+-----------+--------------+-------------+
| 1001 | 科研部 | 研发产品 | 3号楼5层 |
+------+-----------+--------------+-------------+
1 row in set (0.31sec)
属性名 [AS] 别名
mysql> select d_idas department_id,d_name as department_name from department;
+---------------+-----------------+
| department_id |department_name |
+---------------+-----------------+
| 1001 | 科研部 |
| 1002 | 生产部 |
| 1003 | 销售部 |
+---------------+-----------------+
3 rows in set (0.00sec)
分别对表和字段取别名
mysql> selectd.d_id as department_id,d.d_name as department_name,d.function,d.address
-> from department d where d.d_id=1001;
+---------------+-----------------+--------------+-------------+
| department_id |department_name | function |address |
+---------------+-----------------+--------------+-------------+
| 1001 | 科研部 | 研发产品 | 3号楼5层 |
+---------------+-----------------+--------------+-------------+
1 row in set (0.00sec)
使用正则表达式查询
用某种模式去匹配一类字符串的一个方式
属性名 regexp '匹配方式'
^ 匹配字符串开始的部分
$ 匹配字符串结束的部分
. 代表字符串中的任意一个字符，包括回车和换行
[字符集合] 匹配“字符集合”中的任何一个字符
[^字符集合] 匹配除了“字符集合”以外的任何一个字符
S1|S2|S3匹配S1，S2和S3中的任意一个字符串
* 代表多个该符号之前的字符，包括0和1个
+ 代表多个该符号之前的字符，包括1个
字符串{N} 字符串出现N次
字符串{M,N} 字符串出现至少M次，最多N次
查询以特定字符或字符串开头的记录
mysql> select *from department where d_name regexp '^q';
+------+------------+-----------------------+---------------+
| d_id | d_name | function | address |
+------+------------+-----------------------+---------------+
| 18 | qwewwesdae | sethis isa good idea | 3th,west road |
+------+------------+-----------------------+---------------+
1 row in set (0.00sec)
查询以特定字符结尾的记录
mysql> select *from department where d_name regexp 'e$';
+------+------------+-----------------------+---------------+
| d_id | d_name | function | address |
+------+------------+-----------------------+---------------+
| 14 | dae | this is a good idea | 3th,west road |
| 15 | sdae | sethis is a good idea | 3th,west road|
| 16 | sesdae | sethis is a good idea | 3th,west road |
| 17 | awwesdae | sethis is a good idea | 3th,west road |
| 18 | qwewwesdae | sethis is a goodidea | 3th,west road |
| 22 | adee | NULL | 10th |
+------+------------+-----------------------+---------------+
6 rows in set (0.00sec)
用符号"."来替代字符串中的任意一个字符
mysql> select *from department where d_name regexp '^s..e$';
+------+--------+-----------------------+---------------+
| d_id | d_name |function | address |
+------+--------+-----------------------+---------------+
| 15 | sdae | sethis is a good idea | 3th,west road |
+------+--------+-----------------------+---------------+
1 row in set (0.00sec)
匹配指定字符中的任意一个
[]
mysql> select *from department where d_name regexp '[dsa]';
+------+------------+---------------------------+---------------+
| d_id | d_name | function | address |
+------+------------+---------------------------+---------------+
| 13 | deo | this is a good idea | 3th,west road |
| 12 | kid | i a ied | wth |
| 14 | dae | this is a good idea | 3th,west road |
| 15 | sdae | sethis is a good idea | 3th,west road |
| 16 | sesdae | sethis is a good idea | 3th,west road |
| 17 | awwesdae | sethis is a good idea | 3th,west road |
| 18 | qwewwesdae | sethis is a good idea | 3th,west road |
| 19 | jack | sethis is a gadsfood idea | 3th,westroad |
| 20 | linsco | sethis is a gadsfood idea | 3th,westroad |
| 22 | adee | NULL | 10th |
| 23 | deww | dess | NULL |
+------+------------+---------------------------+---------------+
11 rows in set (0.01sec)
[a-z]表示从a~z的所有字母
[0-9]表示从0~9的所有数字
[a-z0-9]表示所有小写字母和数字
mysql> select *from department where d_name regexp'[h-k]';
+------+--------+---------------------------+---------------+
| d_id | d_name |function | address |
+------+--------+---------------------------+---------------+
| 12 | kid | i a ied |wth |
| 19 | jack | sethis is a gadsfood idea | 3th,west road |
| 20 | linsco | sethis is a gadsfood idea |3th,west road |
+------+--------+---------------------------+---------------+
3 rows in set (0.01sec)
匹配指定字符以外的字符
[^字符集合]
mysql> select *from department where d_name regexp '[^a-b]';
+------+------------+---------------------------+---------------+
| d_id | d_name | function | address |
+------+------------+---------------------------+---------------+
| 13 | deo | this is a good idea | 3th,west road |
| 12 | kid | i a ied | wth |
| 14 | dae | this is a good idea | 3th,west road |
| 15 | sdae | sethis is a good idea | 3th,west road |
| 16 | sesdae | sethis is a good idea | 3th,west road |
| 17 |awwesdae | sethis is a good idea | 3th,west road |
| 18 | qwewwesdae | sethis is a good idea | 3th,west road |
| 19 | jack | sethis is a gadsfood idea | 3th,westroad |
| 20 | linsco | sethis is a gadsfood idea | 3th,westroad |
| 22 | adee | NULL | 10th |
| 23 | deww | dess | NULL |
+------+------------+---------------------------+---------------+
11 rows in set (0.00sec)
匹配指定的字符串
