一、实验环境
自行创建一个员工信息表info，里面记录着员工姓名，年龄，地址以及工资。
|
1 2 3 4 5 6 7 8 9 10 |
create database testdb charset utf8; use testdb; create table info(ID int not null auto_increment primary key,NAME CHAR(8) NOT NULL,AGE INT NOT NULL,ADDRESS VARCHAR(20) NOT NULL,SALARY decimal(10,2) not null) ENGINE=innodb; INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('ZHANG',32,'Beijing',2000.00); INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('LI',25,'Shanghai',1500.00); INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('PENG',23,'Hangzhou',2000.00); INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('LIN',25,'Henan',6500.00); INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('WANG',27,'Hunan',8500.00); INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('WANG',22,'Hunan',4500.00); INSERT INTO info(NAME,AGE,ADDRESS,SALARY) VALUES('GAO',24,'Hebei',10000.00); |
二、投影查询
|
1 |
select colname1,col_name2 from table_name; |
实例：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 |
# 查看表的所有行和列(遍历整张数据表资源消耗大); mysql> select * from info; +----+-------+-----+----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+-------+-----+----------+----------+ | 1 | ZHANG | 32 | Beijing | 2000.00 | | 2 | LI | 25 | Shanghai | 1500.00 | | 3 | PENG | 23 | Hangzhou | 2000.00 | | 4 | LIN | 25 | Henan | 6500.00 | | 5 | WANG | 27 | Hunan | 8500.00 | | 6 | WANG | 22 | Hunan | 4500.00 | | 7 | GAO | 24 | Hebei | 10000.00 | +----+-------+-----+----------+----------+ 7 rows in set (0.00 sec) # 查看表的指定列; mysql> select ID,NAME from info; +----+-------+ | ID | NAME | +----+-------+ | 1 | ZHANG | | 2 | LI | | 3 | PENG | | 4 | LIN | | 5 | WANG | | 6 | WANG | | 7 | GAO | +----+-------+ 7 rows in set (0.00 sec) # 查看表的指定列（distinct表示相同的结果只显示一次，保证查询结果不重复）; mysql> select distinct AGE from info; +-----+ | AGE | +-----+ | 32 | | 25 | | 23 | | 27 | | 22 | | 24 | +-----+ 6 rows in set (0.00 sec) # 查看表时添加一个字段,limit 1表示限制输出; mysql> select *,'Level' from info limit 1; +----+-------+-----+---------+---------+-------+ | ID | NAME | AGE | ADDRESS | SALARY | Level | +----+-------+-----+---------+---------+-------+ | 1 | ZHANG | 32 | Beijing | 2000.00 | Level | +----+-------+-----+---------+---------+-------+ 1 row in set (0.00 sec) # 查看表时添加一个排名字段; mysql> set @level=0; select *,@level:=@level+1 as level from info; +----+-------+-----+----------+----------+-------+ | ID | NAME | AGE | ADDRESS | SALARY | level | +----+-------+-----+----------+----------+-------+ | 1 | ZHANG | 32 | Beijing | 2000.00 | 1 | | 2 | LI | 25 | Shanghai | 1500.00 | 2 | | 3 | PENG | 23 | Hangzhou | 2000.00 | 3 | | 4 | LIN | 25 | Henan | 6500.00 | 4 | | 5 | WANG | 27 | Hunan | 8500.00 | 5 | | 6 | WANG | 22 | Hunan | 4500.00 | 6 | | 7 | GAO | 24 | Hebei | 10000.00 | 7 | +----+-------+-----+----------+----------+-------+ 7 rows in set (0.00 sec) # 查看表时给工资大于6500的一个标记'level-1',小于6500的标记'level-2'; mysql> select *,(case when SALARY>6500 then 'level-1' else 'level-2' end) as mark from info; +----+-------+-----+----------+----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | mark | +----+-------+-----+----------+----------+---------+ | 1 | ZHANG | 32 | Beijing | 2000.00 | level-2 | | 2 | LI | 25 | Shanghai | 1500.00 | level-2 | | 3 | PENG | 23 | Hangzhou | 2000.00 | level-2 | | 4 | LIN | 25 | Henan | 6500.00 | level-2 | | 5 | WANG | 27 | Hunan | 8500.00 | level-1 | | 6 | WANG | 22 | Hunan | 4500.00 | level-2 | | 7 | GAO | 24 | Hebei | 10000.00 | level-1 | +----+-------+-----+----------+----------+---------+ 7 rows in set (0.00 sec) |
三、条件查询
在使用WHERE语句可以做条件查询，但需要结合SQL操作符一起运用，如SQL算术、比较、逻辑操作符。
SQL比较操作符
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
# 查看NAME字段名称等于GAO的记录; mysql> select * from info where NAME='GAO'; +----+------+-----+---------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+------+-----+---------+----------+ | 7 | GAO | 24 | Hebei | 10000.00 | +----+------+-----+---------+----------+ 1 row in set (0.00 sec) # 查看年龄大于等于25的记录; mysql> select * from info where AGE>=25; +----+-------+-----+----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+-------+-----+----------+---------+ | 1 | ZHANG | 32 | Beijing | 2000.00 | | 2 | LI | 25 | Shanghai | 1500.00 | | 4 | LIN | 25 | Henan | 6500.00 | | 5 | WANG | 27 | Hunan | 8500.00 | +----+-------+-----+----------+---------+ 4 rows in set (0.00 sec) |
SQL算术操作符
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# 查看AGE字段加SALARY字段的结果; mysql> select AGE+SALARY from info; +------------+ | AGE+SALARY | +------------+ | 2032.00 | | 1525.00 | | 2023.00 | | 6525.00 | | 8527.00 | | 4522.00 | | 10024.00 | +------------+ 7 rows in set (0.00 sec) |
SQL逻辑操作符
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 |
# 查看AGE字段年龄大于等于25小于等于30的记录; mysql> select * from info where AGE between 25 AND 30; +----+------+-----+----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+------+-----+----------+---------+ | 2 | LI | 25 | Shanghai | 1500.00 | | 4 | LIN | 25 | Henan | 6500.00 | | 5 | WANG | 27 | Hunan | 8500.00 | +----+------+-----+----------+---------+ 3 rows in set (0.00 sec) # 查看NAME字段不等于GAO,或者AGE大于等于25的的记录; mysql> select * from info where NOT(NAME='GAO' OR AGE>=25); +----+------+-----+----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+------+-----+----------+---------+ | 3 | PENG | 23 | Hangzhou | 2000.00 | | 6 | WANG | 22 | Hunan | 4500.00 | +----+------+-----+----------+---------+ 2 rows in set (0.00 sec) # 查看AGE字段中有18,20,25年龄的记录; mysql> select * from info where AGE in (18,20,25); +----+------+-----+----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+------+-----+----------+---------+ | 2 | LI | 25 | Shanghai | 1500.00 | | 4 | LIN | 25 | Henan | 6500.00 | +----+------+-----+----------+---------+ 2 rows in set (0.00 sec) # 查看NAME字段为NULL的记录; mysql> select * from info where NAME is NULL; Empty set (0.00 sec) # 查看NAME字段不为NULL的记录; mysql> select * from info where NAME is not NULL; +----+-------+-----+----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+-------+-----+----------+----------+ | 1 | ZHANG | 32 | Beijing | 2000.00 | | 2 | LI | 25 | Shanghai | 1500.00 | | 3 | PENG | 23 | Hangzhou | 2000.00 | | 4 | LIN | 25 | Henan | 6500.00 | | 5 | WANG | 27 | Hunan | 8500.00 | | 6 | WANG | 22 | Hunan | 4500.00 | | 7 | GAO | 24 | Hebei | 10000.00 | +----+-------+-----+----------+----------+ 7 rows in set (0.00 sec) # 使用like模糊查询,%：表示任意字符,_：表示单个字符; mysql> select * from info where NAME like 'Z%'; +----+-------+-----+---------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+-------+-----+---------+---------+ | 1 | ZHANG | 32 | Beijing | 2000.00 | +----+-------+-----+---------+---------+ 1 row in set (0.00 sec) # RLIKE使用正则表达式查询; mysql> select * from info where NAME rlike 'G$'; +----+-------+-----+----------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+-------+-----+----------+---------+ | 1 | ZHANG | 32 | Beijing | 2000.00 | | 3 | PENG | 23 | Hangzhou | 2000.00 | | 5 | WANG | 27 | Hunan | 8500.00 | | 6 | WANG | 22 | Hunan | 4500.00 | +----+-------+-----+----------+---------+ 4 rows in set (0.00 sec) |
我们也可以把AND和OR结合起来（使用圆括号来组成复杂的表达式）:
|
1 2 3 4 5 6 7 |
mysql> select * from info where AGE > 25 AND (SALARY > 2000.00 OR ADDRESS like '%Shanghai%'); +----+------+-----+---------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+------+-----+---------+---------+ | 5 | WANG | 27 | Hunan | 8500.00 | +----+------+-----+---------+---------+ 1 row in set (0.00 sec) |
PS：逻辑操作符的执行优先级为NOT>ADN>OR，一般当有多个逻辑操作符时要使用括号括起来。
四、聚合查询
AVG()
|
1 2 3 4 5 6 7 8 |
# 取工资平均数; mysql> select AVG(SALARY) from info; +-------------+ | AVG(SALARY) | +-------------+ | 5000.000000 | +-------------+ 1 row in set (0.00 sec) |
MAX()
|
1 2 3 4 5 6 7 8 |
# 取年龄最大值; mysql> select MAX(AGE) from info; +----------+ | MAX(AGE) | +----------+ | 32 | +----------+ 1 row in set (0.00 sec) |
MIN()
|
1 2 3 4 5 6 7 8 |
# 取年龄最小值; mysql> select MIN(AGE) from info; +----------+ | MIN(AGE) | +----------+ | 22 | +----------+ 1 row in set (0.00 sec) |
SUM()
|
1 2 3 4 5 6 7 8 |
# 取工资总和; mysql> select SUM(SALARY) from info; +-------------+ | SUM(SALARY) | +-------------+ | 35000.00 | +-------------+ 1 row in set (0.00 sec) |
COUNT()
|
1 2 3 4 5 6 7 8 |
# 取表的行数; mysql> select COUNT(*) from info; +----------+ | COUNT(*) | +----------+ | 7 | +----------+ 1 row in set (0.00 sec) |
RIGHT()
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# 取向右移动字符数; mysql> select RIGHT(NAME,2) from info; +---------------+ | RIGHT(NAME,2) | +---------------+ | NG | | LI | | NG | | IN | | NG | | NG | | AO | +---------------+ 7 rows in set (0.01 sec) |
LEFT()
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# 取向左移动字符数; mysql> select LEFT(NAME,2) from info; +--------------+ | LEFT(NAME,2) | +--------------+ | ZH | | LI | | PE | | LI | | WA | | WA | | GA | +--------------+ 7 rows in set (0.00 sec) |
五、关键字查询
AS（给字段或表取别名）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# AS关键字给字段取别名; mysql> select NAME as MINGZI from info limit 1; +--------+ | MINGZI | +--------+ | ZHANG | +--------+ 1 row in set (0.00 sec) # AS关键字给表取别名; mysql> select dd.ID from info as dd limit 1; +----+ | ID | +----+ | 1 | +----+ 1 row in set (0.00 sec) |
GROUP BY（分组查询）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 |
# GROUP BY子句,根据ADDRESS字段进行分组并统计; mysql> select ADDRESS,count(*) from info group by ADDRESS; +----------+----------+ | ADDRESS | count(*) | +----------+----------+ | Beijing | 1 | | Hangzhou | 1 | | Hebei | 1 | | Henan | 1 | | Hunan | 2 | | Shanghai | 1 | +----------+----------+ 6 rows in set (0.00 sec) # GROUP By子句,分组查看每一个人工资总和（同一个员工有多行记录才有效果）; mysql> select NAME,SUM(SALARY) as Gongzi from info group by NAME; +-------+----------+ | NAME | Gongzi | +-------+----------+ | GAO | 10000.00 | | LI | 1500.00 | | LIN | 6500.00 | | PENG | 2000.00 | | WANG | 13000.00 | | ZHANG | 2000.00 | +-------+----------+ 6 rows in set (0.01 sec) # GROUYP BY … HAVING子句,分组查看每一个人工资总和并且看看大于6500的人有哪些; mysql> select SUM(SALARY) as GONGZI from info group by NAME having GONGZI > 6500; +----------+ | GONGZI | +----------+ | 10000.00 | | 13000.00 | +----------+ 2 rows in set (0.00 sec) # GROUP By子句,分组查询同龄人工资最高的人; mysql> select AGE,MAX(SALARY) from info group by AGE; +-----+-------------+ | AGE | MAX(SALARY) | +-----+-------------+ | 22 | 4500.00 | | 23 | 2000.00 | | 24 | 10000.00 | | 25 | 6500.00 | | 27 | 8500.00 | | 32 | 2000.00 | +-----+-------------+ 6 rows in set (0.00 sec) # GROUP BY多字段分组,查询同一个名字的人分布在哪些城市; mysql> select name,address,count(1) from info group by NAME,address; +-------+----------+----------+ | name | address | count(1) | +-------+----------+----------+ | GAO | Hebei | 1 | | LI | Shanghai | 1 | | LIN | Henan | 1 | | PENG | Hangzhou | 1 | | WANG | Hunan | 2 | | ZHANG | Beijing | 1 | +-------+----------+----------+ 6 rows in set (0.00 sec) # GROUP BY多字段分组,显示出同一个城市中有重复人名的名字; mysql> select name,address,count(1) from info group by NAME,address having count(1) > 1; +------+---------+----------+ | name | address | count(1) | +------+---------+----------+ | WANG | Hunan | 2 | +------+---------+----------+ 1 row in set (0.00 sec) |
ORDER BY（对查询结果进行排序）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
# ORDER BY子句根据SALARY工资进行排序（desc降序（大到小），asc升序（小到大））; mysql> select * from info order by SALARY desc; +----+-------+-----+----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+-------+-----+----------+----------+ | 7 | GAO | 24 | Hebei | 10000.00 | | 5 | WANG | 27 | Hunan | 8500.00 | | 4 | LIN | 25 | Henan | 6500.00 | | 6 | WANG | 22 | Hunan | 4500.00 | | 1 | ZHANG | 32 | Beijing | 2000.00 | | 3 | PENG | 23 | Hangzhou | 2000.00 | | 2 | LI | 25 | Shanghai | 1500.00 | +----+-------+-----+----------+----------+ 7 rows in set (0.00 sec) |
LIMIT（用于限制由 SELECT 语句返回的数据数量）
|
1 2 3 4 5 6 7 8 |
# LIMIT子句只显示规定的前2行数据; mysql> select * from info limit 2; # LIMIT字句只显示规定的前2行数据，但是从第3行开始偏移,也就是显示第3行的下2行数据; mysql> select * from info limit 2 offset 3; # 跟上面一句的含义是相同的，也是表示从第3行开始偏移，显示两行数据（分页就是这么做的）; mysql> select * from info limit 3,2; |
DISTINCT（使字段查询结果不重复）
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
# 查看表的指定列（distinct表示相同的结果只显示一次，保证查询结果不重复）; mysql> select distinct AGE from info; +-----+ | AGE | +-----+ | 32 | | 25 | | 23 | | 27 | | 22 | | 24 | +-----+ 6 rows in set (0.00 sec) |
六、正则表达式
正则表达式也称默认查询（REGEXP关键字可以使用LIKE代替）通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串。例如从一个文本文件中提取电话号码，查找一篇文件中重复的单词或替换用户输入的某些敏感词语等等，这些地方都可以使用正则表达式。正则表达式强大且灵活，可以应用与非常复杂的查询，比如用在
MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式，下面给出一些常用的字符匹配列表（具体正则表达式可以看本博客“Linux文本检索和处理中有介绍”）。
^：匹配文本的开始字符，如“^b”匹配以字符b开头的字符串。
$：匹配文本的结束字符，如“b$”匹配以b结尾的字符串。
.：匹配任意单个字符，如“b.t”匹配任何b和t之间有一个字符的字符串。
*：匹配零个或多个在它前面的字符，如“f*n”匹配字符n掐面有任意个字符f的字符串。
[]：匹配字符集和中的任何一个字符，如[xz]匹配x或者z。
REGEXP举例
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# 查看NAME字段显示匹配指定字符串"ang"的记录; mysql> select * from info where NAME REGEXP 'ang'; # 查看NAME字段显示以字母"b"开头的记录; mysql> select * from info where NAME REGEXP '^Z'; # 查看NAME字段显示以字母"G"结尾的记录; mysql> select * from info where NAME REGEXP 'g$'; # 查看NAME字段显示包含字母"H"与"N"且两个字母之间只有一个字母的记录; mysql> select * from info where NAME REGEXP 'H.N'; # 查看SALARY字段显示包含6789任意一个数字的记录; mysql> select * from info where SALARY REGEXP '[6789]'; # 查看SALARY字段显示包含6789任意一个数字以外的记录; mysql> select * from info where SALARY REGEXP '[^6789]'; |
除了使用正则表达式来匹配查询外，也还可以使用LIKE运算符匹配指定的字符串，但与REGEXP不同，LIKE匹配的字符串如果在文本中间出现，则找不到它，相应的行也不会返回。而REGEXP在本内进行匹配，如果被匹配的字符串在文本中出现，REGEXP将会找到它，相应的行也会被返回。下面给一些例子：
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> select * from info where NAME LIKE 'ANG'; Empty set (0.00 sec) mysql> select * from info where NAME LIKE '%ANG'; +----+-------+-----+---------+---------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+-------+-----+---------+---------+ | 1 | ZHANG | 32 | Beijing | 2000.00 | | 5 | WANG | 27 | Hunan | 8500.00 | | 6 | WANG | 22 | Hunan | 4500.00 | +----+-------+-----+---------+---------+ |
连接查询
连接是关系数据库模型的主要特点，连接查询时关系数据库中最主要的查询，主要包括内连接、外连接（左外连接、右外连接）等。通过连接运算符可以实现多个表查询。
创建两张表student（学生姓名，出生时间，性别），course（课程名称，课程编号，课程导师，学生姓名）
|
1 2 3 4 5 6 7 8 9 10 |
create table student(ID int primary key auto_increment,NAME char(10) not null,BIRTHDAY date not null,SEX enum('男','女') not null); insert into student(NAME,BIRTHDAY,SEX) value('ZHANG','1993-10-06','男'); insert into student(NAME,BIRTHDAY,SEX) value('LI','1994-10-06','女'); insert into student(NAME,BIRTHDAY,SEX) value('PENG','1993-2-06','男'); insert into student(NAME,BIRTHDAY,SEX) value('WANG','1993-9-06','男'); create table course(ID int primary key auto_increment,C_NAME char(10) not null,NUM int not null,T_NAME char(10) not null,NAME char(10)); insert into course(C_NAME,NUM,T_NAME,NAME) value('Shuxue','1001','ERIC','ZHANG'); insert into course(C_NAME,NUM,T_NAME,NAME) value('Yuwen','1002','MARK','LI'); insert into course(C_NAME,NUM,T_NAME,NAME) value('Wuli','7002','JERRY','LI'); insert into course(C_NAME,NUM,T_NAME,NAME) value('Huaxue','7003','LILI','WANG'); |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
mysql> SELECT * FROM student; +----+-------+------------+-----+ | ID | NAME | BIRTHDAY | SEX | +----+-------+------------+-----+ | 1 | ZHANG | 1993-10-06 | 男 | | 2 | LI | 1994-10-06 | 女 | | 3 | PENG | 1993-02-06 | 男 | | 4 | WANG | 1993-09-06 | 男 | +----+-------+------------+-----+ mysql> SELECT * FROM course; +----+--------+------+--------+-------+ | ID | C_NAME | NUM | T_NAME | NAME | +----+--------+------+--------+-------+ | 1 | Shuxue | 1001 | ERIC | ZHANG | | 2 | Yuwen | 1002 | MARK | LI | | 3 | Wuli | 7002 | JERRY | LI | | 4 | Huaxue | 7003 | LILI | WANG | +----+--------+------+--------+-------+ |
交叉连接
返回左右表中每个记录一一对应，没有值的以“Null”显示
|
1 |
mysql> select * from student,course; |
内连接（INNER JOIN）
内连接使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现结果关系中。使用语法如下：
|
1 2 |
# 查看学生ZHANG选择了什么课程以及导师是谁; mysql> select student.NAME,course.C_NAME,course.T_NAME from student inner join course on student.NAME=course.NAME where student.NAME='ZHANG'; |
或
|
1 |
mysql> select s.NAME,c.C_NAME,c.T_NAME from student as s inner join course as c on s.NAME=c.NAME where s.NAME='ZHANG'; |
或
还可以使用WHERE字句定义连接条件，比较简单明了。而INNER JOIN语法是ANSI SQL的标准规范，使用INNER JOIN连接语法能够确保不会忘记连接条件，而且，WHERE字句在某些时候会影响查询的性能。具体操作语法如下
|
1 |
mysql> select s.NAME,c.C_NAME,c.T_NAME from student,course where s.NAME=c.NAME AND s.NAME='ZHANG'; |
左外连接（LEFT JOIN）
返回包括左表中的所有记录和右表中连接字段相等的记录，右边无值则显示“NULL”，语法如下：
|
1 2 |
# 查看没有选择课程的学生; mysql> select student.NAME,course.C_NAME from student left join course on student.NAME=course.NAME; |
右外连接（RIGHT JOIN）
返回包括右表中的所有记录和右表中连接字段相等的记录，左边没有值则显示“NULL”，语法如下：
|
1 2 |
# 查看没有选择的课程; mysql> select student.NAME,course.C_NAME from student right join course on student.NAME=course.NAME; |
完全连接（FULL JOIN）
返回包括左右表中的所有记录，左右两边没有值都显示为“NULL”，语法如下：
|
1 2 |
# 查看没有选择课程的学生和没有选择的课程; mysql> select course.T_NAME,course.NAME from student full join course on student.NAME=course.NAME; |
三表内连接
|
1 2 |
mysql> select user.aa.id,user.aa.name,storage1.dol_chara_store.id,user.user.account_id,user.user.Id from storage1.dol_chara_store inner join user.user inner join user.aa on storage1.dol_chara_store.account_id=user.user.account_id and storage1.dol_chara_store.id=user.aa.rid; |
子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从MySQL4.1开始引入。在SELECT子句中先计算子查询，子查询结果作为为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。子查询中常用的操作符有ANY（SOME）、ALL、IN、EXISTS。子查询可以添加到SELECT、UPDATE和DELETE语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符。
1）带ANY关键字的子查询
ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
示例:
|
1 2 |
# 把student表的id列与course表中NUM的值与之进行比较，只要id大于NUM-1000的任何一个值，即为符合查询条件的结果; mysql> select * from student where id > ANY(select NUM-1000 from course); |
2）带ALL关键字的子查询
ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。例如，修改前面的例子，用ALL关键字替换ANY。ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE。
示例:
|
1 |
mysql> select * from student where id > ALL(select NUM-1000 from course); |
3）带EXISTS关键字的子查询
EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。
示例:
|
1 |
mysql> select * from student where EXISTS (select NUM from course where NUM=1001); |
由上述查看可以看到，如果子查询有NUM=1000的记录，因此EXISTS表达式返回true；那么外层查询语句接收true之后对表student进行查询，返回所有的记录。如果使用NOT EXISTS关键字的话就是对EXISTS取反。代码如下：
|
1 |
mysql> select * from student where NOT EXISTS (select NUM from course where NUM=1001); |
4）带IN关键字的子查询
使用IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。
示例:
|
1 |
mysql> select * from student where ID IN(select NUM-1000 from course); |
5）带比较运算符的子查询
在使用比较运算符时，内层查询语句必须返回一个准确的值给外层查询用来做比较使用，而上面介绍的ANY、ALL、IN等关键字就不需要内层查询给一个准确的返回值。
示例:
|
1 |
mysql> select * from student where ID > (select NUM-1000 from course where NUM=1002); |
PS：子查询的功能也可以通过连接查询来完成，但是子查询使得MySQL代码更容易阅读和编写。
合并查询结果
利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。UNION不适用关键字ALL，执行的时候删除重复的记录，所有返回的行都是唯一的；使用关键字ALL的作用是不删除重复行也不对结果进行自动排序。基本语法格式如下：
|
1 |
SELECT column FROM table1 UNION [ALL] SELECT column FROM table2; |
示例:
|
1 2 3 4 5 6 7 8 9 |
mysql> select * from course where ID > 2 UNION ALL select * from course where NUM > 7000; +----+--------+------+--------+------+ | ID | C_NAME | NUM | T_NAME | NAME | +----+--------+------+--------+------+ | 3 | Wuli | 7002 | JERRY | LI | | 4 | Huaxue | 7003 | LILI | WANG | | 3 | Wuli | 7002 | JERRY | LI | | 4 | Huaxue | 7003 | LILI | WANG | +----+--------+------+--------+------+ |
查询course表中ID大于2的记录和查询course表中NUM小于7000的记录，然后使用UNION ALL把这两个查询结果合并到一起去。如果光使用UNION关键字就是合并重复的行，代码如下：
|
1 2 3 4 5 6 7 |
mysql> select * from course where ID > 2 UNION select * from course where NUM > 7000; +----+--------+------+--------+------+ | ID | C_NAME | NUM | T_NAME | NAME | +----+--------+------+--------+------+ | 3 | Wuli | 7002 | JERRY | LI | | 4 | Huaxue | 7003 | LILI | WANG | +----+--------+------+--------+------+ |
如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。
