MySql基本查询、连接查询、子查询、正则表达查询解说

林炳文Evankaka原创作品。转载请注明出处http://blog.csdn.net/evankaka

查询数据指从数据库中获取所须要的数据。查询数据是数据库操作中最经常使用，也是最重要的操作。用户能够依据自己对数据的需求，使用不同的查询方式。通过不同的查询方式，能够获得不同的数据。

MySQL中是使用SELECT语句来查询数据的。

在这一章中将解说的内容包括。
1、查询语句的基本的语法
2、在单表上查询数据
3、使用聚合函数查询数据
4、多表上联合查询
5、子查询
6、合并查询结果
7、为表和字段取别名
8、使用正則表達式查询

什么是查询？

怎么查的？

数据的准备例如以下：

create table STUDENT(
STU_ID int primary KEY,
STU_NAME char(10) not null,
STU_AGE smallint unsigned  not null,
STU_SEX char(2) not null
);
insert  into STUDENT values(2001,'小王',13,'男');
insert  into STUDENT values(2002,'明明',12,'男');
insert  into STUDENT values(2003,'红红',14,'女');
insert  into STUDENT values(2004,'小花',13,'女');
insert  into STUDENT values(2005,'天儿',15,'男');
insert  into STUDENT values(2006,'阿猎',13,'女');
insert  into STUDENT values(2007,'阿猫',16,'男');
insert  into STUDENT values(2008,'阿狗',17,'男');
insert  into STUDENT values(2009,'黑子',14,'男');
insert  into STUDENT values(2010,'小玉',13,'女');
insert  into STUDENT values(2011,'头头',13,'女');
insert  into STUDENT values(2012,'冰冰',14,'女');
insert  into STUDENT values(2013,'漂亮',13,'女');
insert  into STUDENT values(2014,'神乐',12,'男');
insert  into STUDENT values(2015,'天五',13,'男');
insert  into STUDENT values(2016,'小三',11,'男');
insert  into STUDENT values(2017,'阿张',13,'男');
insert  into STUDENT values(2018,'阿杰',13,'男');
insert  into STUDENT values(2019,'阿宝',13,'女');
insert  into STUDENT values(2020,'大王',14,'男');

然后这是学生成绩表，当中定义了外键约束

create table GRADE(
STU_ID INT NOT NULL,
STU_SCORE INT,
foreign key(STU_ID) references STUDENT(STU_ID)
);
insert  into GRADE values(2001,90);
insert  into GRADE values(2002,89);
insert  into GRADE values(2003,67);
insert  into GRADE values(2004,78);
insert  into GRADE values(2005,89);
insert  into GRADE values(2006,78);
insert  into GRADE values(2007,99);
insert  into GRADE values(2008,87);
insert  into GRADE values(2009,70);
insert  into GRADE values(2010,71);
insert  into GRADE values(2011,56);
insert  into GRADE values(2012,85);
insert  into GRADE values(2013,65);
insert  into GRADE values(2014,66);
insert  into GRADE values(2015,77);
insert  into GRADE values(2016,79);
insert  into GRADE values(2017,82);
insert  into GRADE values(2018,88);
insert  into GRADE values(2019,NULL);
insert  into GRADE values(2020,NULL);

一、查询语句的基本的语法

查询数据是指从数据库中的数据表或视图中获取所须要的数据，在MySQL中，能够使用SELECT语句来查询数据。

依据查询条件的不同，数据库系统会找到不同的数据。

SELECT语句的基本的语法格式例如以下：

SELECT 属性列表
       FROM 表名或视图列表
       [WHERE 条件表达式1]
       [GROUP BY 属性名1 [HAVING 条件表达式2]]
       [ORDER BY 属性名2 [ASC|DESC]]

属性列表：表示须要查询的字段名。
表名或视图列表：表示即将进行数据查询的数据表或者视图，表或视图能够有多个。
条件表达式1：设置查询的条件。
属性名1：表示按该字段中的数据进行分组。
条件表达式2：表示满足该表达式的数据才干输出。
属性2：表示按该字段中的数据进行排序，排序方式由ASC或DESC參数指定。
ASC：表示按升序的顺序进行排序。
即表示值依照从小到大的顺序排列。这是默认參数。
DESC：表示按降序的顺序进行排序。即表示值依照从大到小的顺序排列。

假设有WHERE子句。就依照“条件表达式1”指定的条件进行查询；假设没有WHERE子句，就查询全部记录。

假设有GROUP BY子句，就依照“属性名1”指定的字段进行分组。假设GROUP BY子句后面带着HAVINGkeyword。那么仅仅有满足“条件表达式2”中指定的条件的记录才干够输出。

GROUP BY子句通常和COUNT()、SUM()等聚合函数一起使用。

假设有ORDER BY子句，就依照“属性名2”指定的字段进行排序。排序方式由ASC或DESC參数指定。

默认的排序方式为ASC。

二、在单表上查询数据

2.1、查询全部字段

select * from STUDENT;

2.2、按条件查询

（1）比較运算符

> , < ,= , != （< >）,>= , <=

select * from STUDENT where STU_AGE>13;

in(v1,v2..vn) ，符合v1,v2,,,vn才干被查出
INkeyword能够推断某个字段的值是否在指定的集合中。

假设字段的值在集合中，则满足查询条件。该纪录将被查询出来。假设不在集合中。则不满足查询条件。其语法规则例如以下：[ NOT ] IN ( 元素1, 元素2, …, 元素n )

select * from STUDENT where STU_AGE in(11,12);

between v1 and v2 在v1至v2之间（包括v1,v2）
BETWEEN ANDkeyword能够判读某个字段的值是否在指定的范围内。假设字段的值在指定范围内，则满足查询条件。该纪录将被查询出来。假设不在指定范围内，则不满足查询条件。其语法规则例如以下：
[ NOT ] BETWEEN 取值1 AND 取值2

select * from STUDENT where STU_AGE between 13 and 15;

（2）逻辑运算符

not ( ! ) 逻辑非

select * from STUDENT where STU_AGE NOT IN(13,14,16);

or ( || ) 逻辑或
ORkeyword也能够用来联合多个条件进行查询，可是与ANDkeyword不同。

使用ORkeyword时，仅仅要满足这几个查询条件的当中一个，这种记录将会被查询出来。假设不满足这些查询条件中的不论什么一个，这种记录将被排除掉。ORkeyword的语法规则例如以下：
条件表达式1 OR 条件表达式2 [ …OR 条件表达式n ]
当中。OR能够用来连接两个条件表达式。并且，能够同一时候使用多个ORkeyword，这样能够连接很多其它的条件表达式。

select * from STUDENT where STU_ID<2005 OR STU_ID>2015;

and ( && ) 逻辑与
ANDkeyword能够用来联合多个条件进行查询。
使用ANDkeyword时，仅仅有同一时候满足全部查询条件的记录会被查询出来。
假设不满足这些查询条件的当中一个，这种记录将被排除掉。

ANDkeyword的语法规则例如以下：
条件表达式1 AND 条件表达式2 [ … AND 条件表达式n ]
当中，AND能够连接两个条件表达式。并且，能够同一时候使用多个ANDkeyword，这样能够连接很多其它的条件表达式。

（3）模糊查询

like 像
LIKEkeyword能够匹配字符串是否相等。假设字段的值与指定的字符串相匹配，则满足查询条件，该纪录将被查询出来。

假设与指定的字符串不匹配。则不满足查询条件。

其语法规则例如以下：[ NOT ] LIKE '字符串'
“NOT”可选參数，加上 NOT表示与指定的字符串不匹配时满足条件。“字符串”表示指定用来匹配的字符串，该字符串必须加单引號或双引號。

通配符:

% 随意字符

select * from STUDENT where STU_NAME LIKE '%王';

表示匹配不论什么以王结尾的

select * from STUDENT where STU_NAME LIKE '阿%';

表示匹配不论什么以阿开头的

_ 单个字符

比方说插入

insert  into STUDENT values(2021,'天下无镜',14,'男');

然后

select * from STUDENT where STU_NAME LIKE '_下_';

查询的结果为空

可是假设下后面加两个_符号

select * from STUDENT where STU_NAME LIKE '_下__';

查询结果不为空

“字符串”參数的值能够是一个完整的字符串。也能够是包括百分号(%)或者下划线(_)的通配字符。二者有非常大差别
“%”能够代表随意长度的字符串，长度能够为0;
“_”仅仅能表示单个字符。
假设要匹配姓张且名字仅仅有两个字的人的记录，“张”字后面必须要有两个“_”符号。由于一个汉字是两个字符。而一个“_”符号仅仅能代表一个字符。
（4）空值查询

IS NULLkeyword能够用来推断字段的值是否为空值（NULL）。

假设字段的值是空值。则满足查询条件，该记录将被查询出来。假设字段的值不是空值，则不满足查询条件。其语法规则例如以下：
IS [ NOT ] NULL
当中，“NOT”是可选參数。加上NOT表示字段不是空值时满足条件。

IS NULL是一个总体，不能将IS换成”=”.

三、使用聚合函数查询数据

3.1、group by 分组

例如以下：

select * from STUDENT group by STU_SEX;

不加条件，那么就仅仅取每一个分组的第一条。

假设想看分组的内容，能够加groub_concat

select STU_SEX,group_concat(STU_NAME) from STUDENT group by STU_SEX;

3.2、普通情况下group需与统计函数（聚合函数）一起使用才有意义

先准备一些数据：

create table EMPLOYEES(
EMP_NAME CHAR(10) NOT NULL,
EMP_SALARY INT unsigned NOT NULL,
EMP_DEP CHAR(10) NOT NULL
);
insert into EMPLOYEES values('小王',5000,'销售部');
insert into EMPLOYEES values('阿小王',6000,'销售部');
insert into EMPLOYEES values('工是不',7000,'销售部');
insert into EMPLOYEES values('人人乐',3000,'资源部');
insert into EMPLOYEES values('满头大',4000,'资源部');
insert into EMPLOYEES values('天生一家',5500,'资源部');
insert into EMPLOYEES values('小花',14500,'资源部');
insert into EMPLOYEES values('大玉',15000,'研发部');
insert into EMPLOYEES values('条条',12000,'研发部');
insert into EMPLOYEES values('笨笨',13000,'研发部');
insert into EMPLOYEES values('我是天才',15000,'研发部');
insert into EMPLOYEES values('无语了',6000,'审计部');
insert into EMPLOYEES values('什么人',5000,'审计部');
insert into EMPLOYEES values('不知道',4000,'审计部');

mysql中的五种统计函数：
（1）max：求最大值
求每一个部门的最高工资：

select EMP_NAME,EMP_DEP,max(EMP_SALARY) from EMPLOYEES group by EMP_DEP;

（2）min：求最小值

求每一个部门的最仰工资：

select EMP_NAME,EMP_DEP,min(EMP_SALARY) from EMPLOYEES group by EMP_DEP;

（3）sum：求总数和

求每一个部门的工资总和：

select EMP_DEP,sum(EMP_SALARY) from EMPLOYEES group by EMP_DEP

（4）avg：求平均值

求每一个部门的工资平均值

select EMP_DEP,avg(EMP_SALARY) from EMPLOYEES group by EMP_DEP;

（5）count：求总行数
求每一个部门工资大于一定金额的人数

select EMP_DEP,count(*) from EMPLOYEES where EMP_SALARY>=500 group by EMP_DEP;

3.3、带条件的groub by 字段 having。利用HAVING语句过滤分组数据

having 子句的作用是筛选满足条件的组。即在分组之后过滤数据，条件中经常包括聚组函数，使用having 条件显示特定的组。也能够使用多个分组标准进行分组。

having 子句被限制子已经在SELECT语句中定义的列和聚合表达式上。

通常，你须要通过在HAVING子句中反复聚合函数表达式来引用聚合值，就如你在SELECT语句中做的那样。

select EMP_DEP,avg(EMP_SALARY),group_concat(EMP_NAME)from EMPLOYEES  group by EMP_DEP HAVING  avg(EMP_SALARY) >=6000;

查找平均工资大于6000的部门，并把部门里的人全部列出来

四、多表上联合查询

多表上联合查询分为内连接查询和外连接查询

(1)隐式内连接查询

select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT,GRADE WHERE STUDENT.STU_ID=GRADE.STU_ID AND GRADE.STU_SCORE >=90;

查找大于90分的学生信息：

（2）显式内连接查询

select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT inner join GRADE on STUDENT.STU_ID=GRADE.STU_ID AND GRADE.STU_SCORE >=90;

使用方法：select .... from 表1 inner join 表2 on 条件表达式

（3）外连接查询

left join.左连接查询。

使用方法：select .... from 表1 left join 表2 on 条件表达式

意思是表1查出来的数据不能为null，可是其相应表2的数据能够为null

select STUDENT.STU_ID,STUDENT.STU_NAME,STUDENT.STU_AGE,STUDENT.STU_SEX,GRADE.STU_SCORE from STUDENT left join GRADE on STUDENT.STU_ID=GRADE.STU_ID;

right join就是相反的了，使用方法同样

用left join的时候，left join操作符左側表里的信息都会被查询出来。右側表里没有的记录会填空(NULL).right join亦然；inner join的时候则仅仅有条件合适的才会显示出来

full join()
完整外部联接返回左表和右表中的全部行。当某行在还有一个表中没有匹配行时，则还有一个表的选择列表列包括空值。

假设表之间有匹配行。则整个结果集行包括基表的数据
值。
仅当至少有一个同属于两表的行符合联接条件时，内联接才返回行。内联接消除与还有一个表中的不论什么行不匹配的行。而外联接会返回 FROM 子句中提到的至少一个表或
视图的全部行。仅仅要这些行符合不论什么 WHERE 或 HAVING 搜索条件。将检索通过左向外联接引用的左表的全部行，以及通过右向外联接引用的右表的全部行。

完整外
部联接中两个表的全部行都将返回。

五、子查询

以一个查询select的结果作为还有一个查询的条件

语法：select * from 表1 wher 条件1（select ..from 表2 where 条件2）

1、与In结合

select * from STUDENT where STU_ID IN(select STU_ID from GRADE where STU_SCORE>85);

查找大于85分的学生信息

2、与EXISTS结合

EXISTS和NOT EXISTS操作符仅仅測试某个子查询是否返回了数据行。假设是，EXISTS将是true，NOT EXISTS将是false。

select * from STUDENT where  EXISTS (select STU_ID from GRADE where STU_SCORE>=100);

假设有学生成绩大于100，才查询全部的学生信息

3、ALL、ANY和SOME子查询

any和all的操作符常见使用方法是结合一个相对照较操作符对一个数据列子查询的结果进行測试。它们測试比較值是否与子查询所返回的全部或一部分值匹配。

比方说，假设比較值小于或等于子查询所返回的每一个值，<=all将是true，仅仅要比較值小于或等于子查询所返回的不论什么一个值，<=any将是true。some是any的一个同义词。

select STU_ID from GRADE where STU_SCORE <67;

仅仅要学号大于上面的随意一个就显示出来：

select * from STUDENT where STU_ID >= any (select STU_ID from GRADE where STU_SCORE <67);

六、合并查询结果

合并查询结果是将多个SELECT语句的查询结果合并到一起。由于某种情况下，须要将几个SELECT语句查询出来的结果合并起来显示。

使用UNIONkeyword时，数据库系统会将全部的查询结果合并到一起，然后去除掉同样的记录。而UNION ALLkeyword则仅仅是简单的合并到一起。其语法规则例如以下：

SELECT语句1
UNION | UNION ALL
SELECT语句2
UNION | UNION ALL ….
SELECT语句n ;

七、排序与取数

7.1、order by

（1） order by price //默认升序排列
（2）order by price desc //降序排列
（3）order by price asc //升序排列，与默认一样
（4）order by rand() //随机排列。效率不高

select * from GRADE where STU_SCORE >80 order by STU_SCORE;

默认是按升序的，

也能够这么写

select * from GRADE where STU_SCORE >80 order by STU_SCORE ASC;

结果例如以下：

假设想换成降序的：

select * from GRADE where STU_SCORE >80 order by STU_SCORE desc;

7.2、limit

limit [offset,] N
offset 偏移量。可选，不写则相当于limit 0,N
N 取出条目

取分数最高的前5条

select * from GRADE order by STU_SCORE desc limit 5;

取分数最低的前5条

select * from GRADE order by STU_SCORE asc limit 5;

取分数排名在10-15之间的5条

select * from GRADE order by STU_SCORE desc limit 10,5

八、为表和字段取别名

使用AS来命名列

select STU_ID as '学号',STU_SCORE as '分数' from GRADE;

当表的名称特别长时，在查询中直接使用表名非常不方便。

这时能够为表取一个别名。用这个别名来取代表的名称。
MySQL中为表取别名的基本形式例如以下：
表名表的别名

select S.STU_ID,S.STU_NAME,S.STU_AGE,S.STU_SEX,G.STU_SCORE from STUDENT S,GRADE G WHERE S.STU_ID=G.STU_ID AND G.STU_SCORE >=90;

九、使用正則表達式查询

正則表達式是用某种模式去匹配一类字符串的一个方式。

比如，使用正則表達式能够查询出包括A、B、C当中任一字母的字符串。

正則表達式的查询能力比通配字符的查询能力更强大，并且更加的灵活。

正則表達式能够应用于非常复杂查询。
MySQL中，使用REGEXPkeyword来匹配查询正則表達式。其基本形式例如以下：
属性名 REGEXP '匹配方式'

在使用前先插入一些数据：

insert  into STUDENT values(2022,'12wef',13,'男');
insert  into STUDENT values(2023,'faf_23',13,'男');
insert  into STUDENT values(2024,'fafa',13,'女');
insert  into STUDENT values(2025,'ooop',14,'男');
insert  into STUDENT values(2026,'23oop',14,'男');
insert  into STUDENT values(2027,'woop89',14,'男');
insert  into STUDENT values(2028,'abcdd',11,'男');

（1）使用字符“^”能够匹配以特定字符或字符串开头的记录。

查询全部以阿头的

select * from STUDENT where STU_NAME REGEXP  '^阿';

以数字开头

select * from STUDENT where STU_NAME REGEXP '^[0-9]';

（2）使用字符“$”能够匹配以特定字符或字符串结尾的记录

以数字结尾

select * from STUDENT where STU_NAME REGEXP '[0-9]$';

（3）用正則表達式来查询时。能够用“.”来替代字符串中的随意一个字符。

select * from STUDENT where STU_NAME REGEXP '^w....[0-9]$';

以w开头,以数字结束。中间有4个

（4）使用方括号（[]）能够将须要查询字符组成一个字符集。仅仅要记录中包括方括号里的随意字符，该记录将会被查询出来。
比如，通过“[abc]”能够查询包括a、b、c这三个字母中不论什么一个的记录。

使用方括号能够指定集合的区间。
“[a-z]”表示从a-z的全部字母;
“[0-9]”表示从0-9的全部数字;
“[a-z0-9]”表示包括全部的小写字母和数字。
“[a-zA-Z]”表示匹配全部字母。

select * from STUDENT where STU_NAME REGEXP '[0-9a-z]';

查询全部包括有数字和小写字母的

使用“[^字符集合]”能够匹配指定字符以外的字符

（5）｛｝表示出现的次数

正則表達式中，“字符串{M}”表示字符串连续出现M次；“字符串{M,N}”表示字符串联连续出现至少M次。最多N次。比如，“ab{2}”表示字符串“ab”连续出现两次。

“ab{2,4}”表示字符串“ab”连续出现至少两次，最多四次。

o出现2次

select * from STUDENT where STU_NAME REGEXP 'o{2}';

（6）+表示到少出现一次

fa至少出现一次

select * from STUDENT where STU_NAME REGEXP '(fa)+';

注意：

正則表達式能够匹配字符串。当表中的记录包括这个字符串时，就能够将该记录查询出来。

假设指定多个字符串时。须要用符号“|”隔开。仅仅要匹配这些字符串中的随意一个就可以。每一个字符串与”|”之间不能有空格。

由于。查询过程中，数据库系统会将空格也当作一个字符。这样就查询不出想要的结果。

正則表達式中，“*”和“+”都能够匹配多个该符号之前的字符。可是，“+”至少表示一个字符，而“*”能够表示零个字符。

posted on 2017-08-20 13:53 slgkaifa 阅读(776) 评论(0) 编辑收藏举报

刷新页面返回顶部

slgkaifa