注意:本篇博文主要讲解oracle上的sql知识,然后是在mysql做测试。
sql语法是不区分大小写的。
当在数据库的表中存入数据后,就可以查询这些已经存入的数据。在开发过程中,我们绝大部分的工作就是在做查询操作。查询数据需要用到select语句,可以使用不同复杂程度的查询语句来检索需要的数据。
1 数据库
在讲解今天的知识前我们先创建测试数据。这里我们创建一个user表,字段结构如下。
字段名 | 中文释义 | 数据类型 |
---|---|---|
id | 用户的id、自增、主键 | int(20) |
name | 用户名 | varchar(20) |
QQ号码 | varchar(15) | |
age | 年龄 | int |
createTime | 创建时间 | timestamp |
使用mysql创建表:
-- 创建测试库
create database test;
-- 使用测试表
use test;
-- 创建user表
create table user (
id int(20) auto_increment,
name varchar(20),
qq varchar(15),
age int,
createTime timestamp,
primary key (id)
);
-- 查看表结构
describe user;
-- 增加数据
insert into user(name, qq, age, createTime) values('阳君', '937447974', 20, '2015-11-12');
insert into user(name, qq, age, createTime) values('阳君', '937447974', 21, '2015-11-13');
insert into user(name, qq, age, createTime) values('阳君', '937447974', 22, '2015-11-14');
insert into user(name, qq, age, createTime) values('阳君', '937447974', 23, '2015-11-15');
insert into user(name, qq, age, createTime) values('阳君', '937447974', 24, '2015-11-16');
insert into user(name, qq, age, createTime) values('阳君', '937447974', 25, '2015-11-17');
-- 查询所有数据
select * from user;
2 查询数据必备SELECT
Select关键字表示数据的检索,它由一系列的子句组成,最终检索出来的数据由子句决定的。也就是说,检索出来的数据必须满足所有子句的限制。select语句按照复杂程度可以分为简单查询、where条件查询、多表查询、子查询等。
2.1 SELECT语句语法
select语句是日常使用最多的语句,select语句的主要语法结构如下:
select [distinct|all] select_list
from table_name
[where_clause]
[group_by_clause]
[having condition]
[order_by_clause]
- select:查询关键字;
- [distinct|all]:描述列表字段中的数据是否取出重复记录;
- select_list:需要查询的字段列表;
- from:数据的来源;
[group_by_clause]
:查询的where条件语句;[having condition]
:group by子句部分;[order_by_clause]
:having子句部分;[order_by_clause]
:排序
2.2 获取指定字段的数据
获取表中指定字段的数据,就是指定表中的某几个字段,然后使用select语句查询。这里获取name字段。
-- 查询name字段
select name from user;
2.3 获取所有字段的数据
要想查看表中所有字段的数据,最简单的方法就是利用“*”来查询。
select * from user;
你还可以写出所有字段名查询,效果一样。
select id,name,qq,age,createTime from user;
2.4 使用别名替代表中的字段名
表中的字段名称都是英文的,这会给英文不好的客户查看数据带来不便。在select语句中允许我们指定别名,指定别名可以利用as关键字。
查询用户名和qq号码并加上别名。
select name as 用户名, qq as QQ from user;
2.5 使用表达式操作查询的字段
可以针对某个列使用表达式,这样查询出来的结果就是修改后的数据,但是数据库中的数据不会被修改。
所有用户的岁数增加10岁。
select age+10 as newAge from user;
2.6 使用函数操作查询的字段
查询过程中检索的列允许使用函数对其操作,如果仅仅是查询,那么更多的是利用函数对数据进行类型转换。
查询qq号码的前5位。
select qq,substr(qq,1,6) as 截取后的qq号码
from user;
2.7 去除检索数据中的重复记录
当查询数据时有可能遇到重复的记录,这给统计有效数据造成了一定的影响,利用distinct关键字可以去除重复的数据。
去除重复的用户name和qq
select distinct name, qq
from user;
3 检索出来的数据排序
要从海量的数据中找出我们模糊记忆中的数据,使用排序是个不错的选择,可以考虑让查询出来的记录根据某个字段进行排序操作。
3.1 使用排序的语法
排序需要放置在select语句的后面,不管有什么条件限制,排序关键字只能在最后一项。下面是排序的语法,对应前面基本语法中的order_by_clause
项。
order by
{expr | position | c_alias}
{asc | desc}
{nulls first | nulls last}
[, {expr | position | c_alias}
{asc | desc}
{nulls first | nulls last}
]...
- order by:排序关键字。
- expr:表达式。
- position:表中列的位置。
- c_alias:别名。
- [asc | desc]:升序或降序。
- nulls first | nulls last:对空字段的处理方式。
- 可以根据多个字段排序。
3.2 使用升序和降序来处理数据
可以使用升序方式或降序方式对查询出来的数据进行排序,如果对某个字段使用了order by子句而不指定排序方式,那么它将默认以升序的方式排序,即默认asc模式。
查询所有数据,以年龄降序排列。
select * from user
order by age desc;
3.3 排序时对NULL值的处理
null值在排序过程中是个比较特殊的值类型,默认情况把它看成最大值。也就是,当排序的记录中出现null值时,默认情况下,升序排列它在最后,降序排列它在首位。mysql和oracle相反,null默认是最小值。
插入空数据。
insert into user(name, age) values('阳君', 26);
insert into user(name, qq) values('阳君', '937447974');
以qq字段做升序排列,null的放在首位。
select * from user
order by qq nulls first;
在mysql中写法如下:
select * from user
order by (1- isnull(qq)), qq;
3.4 使用别名作为排序字段
为user表中的age指定别名,并使用该别名做降序排列。
select age as 年龄
from user
order by 年龄 desc;
3.5 使用表达式作为排序字段
查询中允许使用表达式处理字段数据,其实也允许使用表达式作为排序字段。
user表中的age增加10,然后做排序。
select age, age+10
from user
order by age+10;
3.6 使用字段的位置作为排序字段
排序时允许使用观察下列表中字段的位置来作为排序手段,这么做有两个好处:查询方便和防止使用union时出现错误。
查询id、name和age,并利用age所在位置做排序。
select id,name,age
from user
order by 3 desc;
3.7 使用多个字段排序
排序非常灵活,它不仅可以利用单一字段进行排序,还能利用多个字段进行排序,最后查询出来的数据是综合排序后的数据。多个字段排序的操作过程如下:
- 按照第一个字段进行排序。
- 在此基础上按照第二个字段排序。
- 如果后面还有排序字段,那么这个过程会不断重复。
查询所有数据,并利用qq降序和age降序排序。
select * from user
order by qq desc, age desc;
4 使用WHERE子句设置检索条件
select…from是一个基本的查询语句,它会无差别地返回所有的值,但我们希望检索出来的数据满足某个甚至多个条件,而利用where子句可以达到我们的目的。
where子句就像一个筛选器,它对from子句返回的结果进行筛选,每条记录都会按照条件进行判断,如果符合条件,则该记录作为查询结果的一部分,如果不符合条件则不会返回。
where条件子句中可以使用的操作符,主要有关系操作符、比较操作符和逻辑操作符。
- 关系操作符包括:<、<=、>、>=、=、!=、<>.
- 比较操作符包括:
- is null:如果操作符为null返回true。
- like 模糊比较字符串值。
- between…and…:验证值是否在范围之内。
- in:验证操作数在设定的一系列值中。
- 逻辑操作符:
- and:两个条件都必须满足。
- or:只要满足两个条件中的其中一个。
- not:与某个逻辑值取反。
简单的where条件语句一般只有一个限制条件,如果一个条件不行可以使用多个条件用逻辑操作符相连接。
4.1 查询中使用单一条件
4.1.1 查询中使用“>”
查询age>25的用户
select * from user
where age > 25;
4.1.2 查询中使用“<>”
查询age不为25的用户
select * from user
where age <> 25;
利用“<>”作为查询条件时,可以使用“!=”替换;字符串类型的数据需要使用单引号括起。
4.1.3 查询中使用函数
查询qq为“93744”开头的用户
select * from user
where substr(qq,1,5) = '93744';
4.2 查询中使用多个条件限制
查询条件中除了单一的条件也可以设置多个条件,但是这些条件需要使用逻辑操作符连接起来。例如,and表示多个条件需要同时满足,其中一个条件不满足,那么该记录就不会返回到查询结果中。
查询qq为“93744”开头以及年龄在22到25岁之间的用户。
select * from user
where substr(qq,1,5) = '93744' and (age between 22 and 25);
4.3 模糊查询数据
当并不能确切地了解查询条件,而是只了解查询条件的一部分时,或者想检索出包含特定字符的数据时,可以利用模糊查询。
使用模糊查询的关键字是like,它和两个通配符一起使用,才能实现模糊查询的功能。用这两个通配符可以代替模糊的部分,它们分别是:
- _:可以代替一个字符。
- %:可以代替多个字符。
检索qq为“93744”开头的用户。
select * from user
where qq like '93744%';
4.4 查询条件限制在某个列表范围之内
某种情况下,要求查询条件从给定的值中选取,这时就可以利用in关键字来实现这个功能。它的语法是in(list),其中list是值列表。
查询age为22和23的用户。
select * from user
where age in ('22','23');
利用in检索出来的数据同分别利用“22”和“23”查询出来的数据是一样的。如果in前面使用not关键字,那么检索出来的数据将为age不为22且不为23的用户。
4.5 专门针对NULL值的查询
数据库中的数据不会是完美的,更多的时候,由于种种原因,会存在垃圾数据和null数据。如果要检索null数据,将如何操作呢?利用 “is null”就可以达到目的,而利用”is not null“就可以检索非null的数据。
查询qq为null的用户
select * from user
where qq is null;
5 GROUP BY和HAVING子句
group by子句和having子句同where不一样,它们两个都用于组的查询。使用分组可以统计数据,例如利用group by配合分组函数,可以查询相同年龄的用户人数。
5.1 GROUP BY子句语法及使用
group by用于归纳汇总相关数据,它不属于where子句。也就是说,group by子句可以直接在from后面,也可以在where条件的后面。
插入数据
insert user(name, age, createTime) values('阳君', 25, '2015-11-12');
insert user(name, age, createTime) values('阳君', 23, '2015-11-12');
insert user(name, age, createTime) values('阳君', 24, '2015-11-12');
insert user(name, age, createTime) values('阳君', 25, '2015-11-12');
insert user(name, qq, age, createTime) values('阳君', '937447974', 24,' 2015-11-12');
insert user(name, qq, age, createTime) values('阳君', '937447974', 25, '2015-11-12');
5.1.1 单一字段分组
查询相同年龄的人的人数
select age, count(*) from user
group by age;
5.1.2 根据多个字段分组
查询相同年龄及创建时间相同的用户人数
select age, createTime, count(*) from user
group by age, createTime;
5.1.3 where和分组混用
查询相同年龄及创建时间相同且qq号码存在的用户人数
select age, createTime, count(*) from user
where qq is not null
group by age, createTime;
5.2 HAVING子句的使用
having子句通常和group by子句一起使用,限制搜索条件。它和where子句不一样,having子句与组有关,而不与单个的值有关。在group by子句中,它会作用于group by创建的组。
查询不同创建时间的用户的平局年龄,并列出平均年龄高于23的用户。
select createTime, avg(age) from user
group by createTime
having avg(age) > 23;
从语句上可以看出having与where的区别,having对group by子句负责,而where对from负责。
6 使用子查询
什么是子查询?子查询就是嵌套查询,它是嵌套在另外一个语句中的select语句。where后面的条件不是一个确切的值或表达式,而是另外一个查询语句的查询结果。子查询不仅仅出现在select语句中,也会出现在delete和update语句中,它本质上是where后的一个条件表达式。
在前面的user表中,不知道你发现没有,其实会出现冗余数据,因为一个人可能有一个或多个qq号码。
下面重新设计数据库,生成两张表,一张user表,一张qq表。
user表的结构如下。
字段名 | 中文释义 | 数据类型 |
---|---|---|
id | 用户的id、唯一、自增、主键 | int |
name | 用户名 | varchar(20) |
age | 年龄 | int |
qq表的结构如下。
字段名 | 中文释义 | 数据类型 |
---|---|---|
id | 表id、唯一、自增、主键 | int |
user_id | 用户id,外键,指向user表的id字段 | int |
QQ号码 | varchar(15) |
-- 删除表
drop table if exists qq;
drop table if exists user;
-- 创建user表
create table user (
id int auto_increment,
name varchar(20),
age int,
PRIMARY KEY (id),
constraint unique(id)
);
-- 创建qq表
create table qq (
id int auto_increment,
user_id int not null,
qq varchar(15),
primary key(id),
constraint foreign key(user_id) references user(id) on delete cascade
);
-- 插入数据
insert into user(name, age) values('阳君', 24);
insert into user(name, age) values('阳君1', 25);
insert into user(name, age) values('阳君2', 25);
insert into user(name, age) values('阳君3', 26);
insert into user(name, age) values('yangjun', 25);
insert into user(name, age) values('yangjun2', 26);
insert into user(name, age) values('yangjun3', 22);
insert into qq(user_id, qq) values('1', '937447974');
insert into qq(user_id, qq) values('1', '937447975');
insert into qq(user_id, qq) values('2', '937447976');
现在你发现冗余相对减少了,而且表之间的关系也很清楚。
6.1 子查询
子查询允许返回单行数据,也允许返回多行数据。
如果返回的是单行数据(不管是普通查询还是分组查询),那么这是逻辑上最简单的子查询嵌套查询语句。它和在where条件中使用单一或多个条件限制的操作方法一致。
查询用户名为“阳君”的qq号码。
select * from qq
where user_id = (select id from user where name = '阳君');
如果子查询返回的值为多行值,那么需要用到IN关键字,此时IN的用户和前面介绍的方式一致。除此之外,也可以使用量化比较关键字some、any、all,这些需要配合<、<=、=、>、>=使用。它们所表示的含义如下:
- any:表示满足子查询结果的任何一个即可。
- some:可以认为和any含义相同。
- all: 表示满足子查询结果的所有条件。
6.1.1 in示例
查询所有存在qq号码的用户。
select * from user
where id in (select user_id from qq);
6.1.2 any示例
select * from user
where age < any (select age from user);
会显示部分数据
6.1.3 some示例
some的用法和any一样,只不过any多用于在非’=’的环境中。
select * from user
where age < some (select age from user);
6.1.4 all示例
select * from user
where age < all (select age from user);
没有数据显示。
7 连接查询
关系型数据库中允许表和表之间存在关系,这种关系可以把两个甚至多个表的数据联系在一起。
利用这种关系,可以查询符合条件的数据,这些数据将是一套符合实际业务逻辑的数据,而数据中这些表和表之间的关系将不存在。换句话说,获取真实世界的原始数据后,根据某种规则把它们拆分成了各种独立的数据,假如想从数据库中再次获取原始数据,那么需要依靠当初拆分时的规则。而这种规则也可以看成表和表之间的联系,要再次实现这种联系,需要用到连接查询。连接分为内连接、外连接和全连接,还有一种叫做自连接,其中最常用的是内连接和外连接。
7.1 最简单的连接查询
最简单的连接查询时利用逗号完成的,它利用逗号把form后的表名隔开,这就构成了最简单的连接查询。
7.1.1 最简单的连接查询
连接user表和qq表。
select * from user, qq;
利用这种方式查询数据将得到两个表的笛卡尔积,也就是说得到两个表中记录数的乘积。
7.2 内连接
内连接也称为简单连接,它会把两个或多个表进行连接,只能查询出匹配的记录,不匹配的记录将无法查询出来。内连接中最常用的就是等值连接和不等值连接。
7.2.1 等值连接
连接条件中使用“=”连接两个条件的表。
查询user表和qq表中,userID一致的数据。
select * from user u, qq q
where u.id = q.user_id;
-- 或
select * from user u
inner join qq q on u.id = q.user_id;
内连接中inner可以不写,sql会自动判断。
7.2.2 不等值连接
不等值连接就是之连接条件中使用>、>=、<=、<、!=、<>、between…and…、in等连接两个条件的列表,但这种方式通常需要和其他等值运算一起使用,否则检索出来的数据很可能没有实际意义。
查询低于平均年龄的用户
select * from user u
join (select avg(age) as avg_age from user) uv
on u.age < uv.avg_age;
7.3 自连接
所谓自连接,就是把自身表的一个引用作为另一个表来处理,这样就能获取一些特殊的处境。
获取小于平均年龄的用户。
select u.name,u.age from user u, (select avg(age) as avg_age from user) uc
where u.age < uc.avg_age;
-- 或
select u.name,u.age from user u
join (select avg(age) as avg_age from user) uc
on u.age < uc.avg_age;
7.4 外连接
外连接分为左外连接、右外连接、全外连接。它们所表示的含义如下:
- 左外连接:又称为左向外连接。使用左外连接的查询,返回的结果不仅仅是符合连接条件的行记录,还包含了左边表中的全部记录。
- 右外连接:又称为右向外连接。它与左外连接相反,将右边的表中所有数据与左表进行匹配,返回的结果除了匹配成功的记录,还包含了右表中未匹配成功的记录,并在其左表对应的列补空值。
- 全外连接:返回所有匹配成功的记录,并返回左表未匹配成功的记录,也返回右表未匹配成功的记录。
7.4.1 左外连接
左外连接使用关键字 left join … on …
select * from user u
left join qq q
on u.id = q.user_id;
7.4.2 右外连接
右外连接和左外连接是相反的,它一右边的表为主表。右外连接使用关键字right join … on …
select * from user u
right join qq q
on u.id = q.user_id;
7.4.3 全外连接
全外连接就是左外连接和右外连接的综合,它除了返回内连接匹配的数据外,也将返回两个表中不匹配的数据。全外连接使用的关键字是full join … on …
select * from user u
full join qq q
on u.id = q.user_id;
在mysql中没有全外连接,但是有另一种方式实现。
select * from user left join qq on user.id = qq.user_id
union
select * from user right join qq on user.id = qq.user_id;
union关键字的作用是连接两个结果集,并去除重复的数据。
7.5 多表连接
多表连接常用的关键字是union和union all。
7.5.1 union
union:连接两个结果集的同时去除重复数据。
select * from user
union
select * from user;
7.5.1 union all
union all:连接两个结果集的同时不去除重复数据。
select * from user
union all
select * from user;
union all比union查询的速度快。
7.6 (+)的使用
在oracle中使用外连接,有一种比较特殊的表示方法,利用“(+)”表示外连接。虽然这种方式可以是实现外连接,当是oracle还是不建议使用。”(+)”主要在非主表的一方,并且使用where语句不能存在join关键字。
7.6.1 左外连接使用(+)
select * from user u, qq q
where u.id = q.user_id(+);
7.6.2 右外连接使用(+)
select * from user u, qq q
where u.id(+) = q.user_id;
8 小结
本篇主要介绍了select语句的相关内容,包括select的语法、如何制定字段别名、如何在查询中使用函数等。为了使数据更加有条理,可以对检索出来的数据进行排序,利用order by子句可以根据某个字段或多个字段对查询数据进行排序。如果想检索出来特定的数据,那么可以利用where子句设置检索条件,检索条件可以是一个也可以是多个,只需利用and和or连接起来即可。关于子查询,它可以作为另一个select语句的查询条件,为动态地查询数据提供了条件。
其他
参考资料
文档修改记录
时间 | 描述 |
---|---|
2015-11-12 | 完成到“6 使用子查询”章节前 |
2015-11-13 | 完成文章 |
2015-11-14 | 增加7.5章节关于多表连接的介绍;增加其他文章的链接。 |