mysql 查询

联合查询

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’;
* 出现多次
+ 最少出现一次