联合查询
union 去重
union all 不去重
select username from cms_user UNION all select username from cms_user;
子查询
定义:将一个查询语句嵌套在另外一个语句中
一个查询语句的结果是另外一个语句的查询条件
in exists 或者运算符
student表
CREATE TABLE student (
id SMALLINT UNSIGNED PRIMARY KEY auto_increment,
sname VARCHAR(20) NOT NULL,
score TINYINT UNSIGNED);
奖学金表
CREATE TABLE scholarship(
id TINYINT UNSIGNED PRIMARY KEY auto_increment,
level TINYINT UNSIGNED);
查询一等奖学金的学生信息
select id,username from student where score>(select level from scholarship where id=1);
exists
SELECT id,ename,depId FROM employee WHERE EXISTS
(SELECT * FROM department WHERE id=3);
any some all
当ALL和>或>=结合使用时,是获取最大值,和<或<=结合使用时,是获取最小值;
ANY正好和ALL相反;
SOME和ANY是相同的
#查询获得学金的学生的姓名和分数
SELECT sname,score FROM student WHERE score>=ANY
(SELECT level FROM scholarship);
#查询获得一等奖学金的学生的姓名和分数
SELECT sname,score FROM student WHERE score>=ALL
(SELECT level FROM scholarship);
#查询没有获得学金的学生的姓名和分数
SELECT sname,score FROM student WHERE score<ALL
(SELECT level FROM scholarship);
#查询没有获得一等奖学金的学生的姓名和分数
SELECT sname,score FROM student WHERE score<ANY
(SELECT level FROM scholarship);
正则表达式查询
^ 匹配字符开始的部分
select * from employee where username regexp ‘^k’;
$ 匹配结尾
select * from employee where username regexp ‘g$’;
. 任意字符
select * from employee where username regexp ‘q.’;
[] 字符集合
select * from employee where username regexp ‘[uo]’;
| 查看字符串中任意一个
select * from employee where username regexp ‘qu|ng’;
* 出现多次
+ 最少出现一次