一、SQL简介
- SQL是用于访问 和处理数据库的标准的计算机语言;
- SQL(Structured Query Language:结构化查询语言)用于管理关系数据库管理系统(RDBMS:Relational Database Management System);
- SQL的范围包括数据插入、查询、更新和删除,数据库模式创建和修改,以及数据访问控制。
注意:RDMBS是所有现代数据库系统的基础,比如说MS SQL Server、DB2、Oracle、MySQL以及Microsoft Access。
1.1、常用的SQL命令
- select ---- 从数据库中提取数据
- update ---- 更新数据库中的数据
- delete ---- 从数据库中删除数据
- insert into ---- 向数据库中插入新数据
- create database ---- 创建新的数据库
- alter database ---- 修改数据库
- create table ---- 创建数据表
- alter table ---- 变更(改变)数据表
- drop table ---- 删除表
- create index ---- 创建索引
- drop index ---- 删除索引
注意:SQL的大小写不敏感,句末不用分号(但是习惯性加上)
二、Select 语句
##SQL select语法结构
select column_name from table_name;
以Websites表作为案例:
id | name | url | alexa | country |
---|---|---|---|---|
1 | https://www.google.cm/ | 1 | USA | |
2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
4 | 微博 | http://weibo.com/ | 20 | CN |
5 | https://www.facebook.com/ | 3 | USA |
从Websites表中选取“name”和“country”
select * from Websites; ## 查出Websites表中所有数据
select name,country from Websites; ## 查出name以及 country列的数据
select distinct country from Websites; ## 去重,查询country唯一值
2.1、where 关键字
where 子句设置条件用于过滤记录;
## SQL where关键字搭配 select
select column_name from table_name where column_name operator value;
注意:其中operator代表的是运算符(比如:>、<、==之类),value代表的是值;
案例:从“Websites”表中选取国家为“CN"的所有网站
select * from Websites where country = 'CN';
注意:SQL使用单引号来环绕文本值(大部分数据库系统也接收双引号值),如果是数值,那就不需要单引号来环绕(比如:where id = 1);
2.1.1、where子句中的运算符
运算符 | 描述 |
---|---|
= | 等于 |
<> | 不等于。**注释:**在 SQL 的一些版本中,该操作符可被写成 != |
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
BETWEEN…AND… | 在某个范围内 |
LIKE | 搜索某种模式 |
IN | 指定针对某个列的多个可能值 |
AND | 如果第一个条件和第二个条件都成立,则根据这些条件进行判断 |
OR | 如果第一个条件和第二个条件中只要有一个成立,则根据其中一个条件判断 |
2.1.1.1、like
##like 语法
select column_name(s)
from table_name
where column_name like pattern;
注意:pattern代表的是参数
实例:选取name以字母“G"开始的所有客户;
select * from Websites
where name like 'G%';
注意:通过使用 not 关键字,可以选取不匹配模式的记录;
实例:选取name不包含“oo”的所有客户;
select * from Websites
where name not like '%oo%';
注意:
- ‘%a’ 以a结尾的数据
- ‘a%’ 以a开头的数据
- ‘%a%’ 含有a的数据
- ‘a’ 三位且中间字母是a的
- ‘_a’ 两位且结尾字母是a的
- ‘a_’ 两位且开头字母是a的
2.1.1.2、in
-
in 操作符允许在where子句中规定多个值;
-
##in 语法 select column_name(s) from table_name where column_name (not) in (value1,value2....);
实例:选取name为“Google"或”菜鸟教程“的所有网站;
select * from Websites
where name in ('Google','菜鸟教程');
2.1.1.3、between
-
between操作符用于选取介于两个值之间的数据范围内的值;
-
这些值可以是数值、文本或者日期;
-
##between 语法 select column_name(s) from table_name where column_name (not) between value1 and value2;
实例:选取alexa介于1和20之间的所有网站;
select * from Websites
where alexa between 1 and 20;
实例:显示不在上面实例范围内的网站;
select * from Websites
where alexa not between 1 and 20;
实例:选取name不介于"A"和"H"之间字母开始的所有网站;
select * from Websites
where name not between `A` and `H`;
实例:选取date介于”2016-05-10“和”2016-05-14“之间的所有访问记录
##access_log表
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
select * from access_log
where date between '2016-05-10' and '2016-05-14';
注意:
- 请注意,在不同的数据库中,BETWEEN 操作符会产生不同的结果!
- 在某些数据库中,BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
- 在某些数据库中,BETWEEN 选取介于两个值之间且包括两个测试值的字段。
- 在某些数据库中,BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。
- 因此,请检查您的数据库是如何处理 BETWEEN 操作符!
2.1.2、where子句中嵌套语句
注意:where子句中可以嵌套条件查询语句
2.2、order by关键字
- order by 关键字用于对结果集进行排序;
- order by 关键字默认按照升序排列,如果需要按照降序排列,则需要添加 DESC 关键字;
## order by 语法
select column_name1,column_name2
from table_name
order by column_name1,column_name2 ASC|DESC;
注意:
order by A,B 这个时候都是默认按升序排列
order by A desc,B 这个时候 A 降序,B 升序排列
order by A ,B desc 这个时候 A 升序,B 降序排列
此处需要案例来显式结果:
2.3、select top、limit、rownum关键字
- select top 子句用于规定要返回的记录的数目(对于处理大量数据,比较有用);
- 注意:并非所有的数据库系统都支持select top 语句,
- 其中MySQL支持Limit语句来选取指定的条数数据(对大量数据分段)
- Oracle 可以使用rownum来分段处理数据
2.3.1、select top
##select top 语法
select top number|percent column_name(s)
from table_name;
-
在SQL Server中可以使用百分比作为参数
-
select top 50 percent * from Websites;
-
从Websites表中,选取前面百分之50的记录;
-
2.3.2、limit
## limit 语法
select column_name(s) from table_name
limit index,pagesize
从“Websites"表中选取头两条记录
select * from Websites limit 2,8
## 从Websites表中从 起始下标为2 的页面开始分页,每页数据8
2.3.3、rownum
## rownum 语法
select column_name(s) from table_name
where rownum <= number;
实例:
select * from Persons
where rownum <= 5;
2.4、别名
- 通过使用SQL,可以为表名称或列名称指定别名;
- 创建别名是为了让列名称的可读性更强;
- 如果列名称包含空格,要求使用双引号或方括号;
##列的SQL别名语法
select column_name as alias_name
from table_name;
##表的SQL别名语法
select column_name(s)
from table_name as alias_name;
注意:alias_name:别名
##Websites
+----+---------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+---------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 5000 | USA |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
##access_log
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
## aid:为自增 id。
## site_id:为对应 websites表的网站 id。
## count:访问次数。
## date:为访问日期。
实例:SQL语句指定两个别名,包括name以及country列;
select name as n,country as c from Websites;
实例:把三个列(url、alexa、country)结合在一起,并创建一个名为“site_info”的别名;(回去继续查看)
select name,concat(url,',',alexa,',',country) as site_info from Websites;
实例:对表“Websites"以及表”access_log"设置别名;
select w.name,w.url,a.count,a.date
from Websites as w,access_log as a
where a.site_id = w.id and w.name = "菜鸟教程";
注意:以下情况,使用别名很有用
- 在查询中涉及超过一个表;
- 在查询中使用了函数;
- 列名称很长或者可读性差;
- 需要把两个列或者多个列结合在一起;
2.5、JOIN连接
-
SQL 的join连接用于把两个或者多个表连接起来;
-
最常见的JOIN类型:inner join
##Websites
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
##access_log
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
实例:inner join
select w.id,w.name,a.count,a.date
from Website w
inner join access_log a
on w.id = a.site_id;
注意:不同的SQL Join
- INNER JOIN:如果表中有至少一个匹配,则返回行
- LEFT JOIN:即使右表中没有匹配,也从左表返回所有的行
- RIGHT JOIN:即使左表中没有匹配,也从右表返回所有的行
- FULL JOIN:只要其中一个表中存在匹配,则返回行
2.6、group by关键字
-
group by 根据一个或多个列对结果集进行分组;
-
group by可以结合聚合函数来使用;
-
##语法 select column_name,aggregate_function(column_name) from table_name where column_name operator value group by column_name; ##aggregate_function 聚合函数
##Websites表
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
##access_log
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
实例:统计access_log各个site_id的访问量;
select site_id,sum(a.count) as nums
from access_log as a
group by site_id;
实例:统计有记录的网站的记录数量;
select w.name,count(a.site_id) as nums
from access_log as a
left join Websites w
on w.id = a.site_id
group by w.name;
2.7、having子句
-
注意:在SQL中增加having子句原因是,where关键字无法与聚合函数一起使用;
-
having 子句可以帮助筛选分组后的各组数据;
-
##语法 select column_name, aggregate_function(column_name) from table_name where column_name operator value group by column_name having aggregate_function(column_name) operator value;
##Websites表
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
##access_log
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
实例:查询访问量大于200的网站;
## 有疑问
SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;
2.8、Select into
-
Select into 语句从一个表复制数据,然后把数据插入到另一个新表中;
-
注意:不需要创建这个表,在复制的时候自动创建;
-
注意:MySQL不支持Select into语句,但是支持insert into …select
-
MySQL也可以使用以下语句拷贝表结构以及数据;
-
create table 新表 as select * from 旧表
-
##SQL Select into语法
##把旧表中所有数据拷贝到新表中
select *
into new_table [in externaldb]
from old_table;
##只复制希望的列插入到新表中;
select column_name(s)
into new_table [in externaldb]
from old_table;
**注意:**新表将会使用 SELECT 语句中定义的列名称和类型进行创建。您可以使用 AS 子句来应用新名称。
实例:创建Websites表的备份复件;
select *
into WebsitesBackup2016
from Websites;
实例:只复制一些列插入到新表中;
select name,url
into WebsitesBackup2016
from Websites;
实例:只复制中国的网站插入到新表中;
select *
into WebsitesBackup2016
from Websites
where country = 'CN';
实例:复制多个表中的数据插入到新表中;
select w.name,a.count,a.date
into WebsitesBackuo2016
from Websites w
left join access_log a
on w.id = a.site_id;
注意:SELECT INTO 语句可用于通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句即可:
select *
into new_table
from old_table
where 1=0;
三、insert 语句
3.1、insert into基础语法
-
insert into语句用于向表中插入新记录;
-
##语法格式 ##无需指定插入数据的列名 insert into table_name values(value1,value2,....); ##需要指定列名以及被插入的值 insert into table_name(column_name1,column_name2,....) values(value1,value2,....);
注意:插入数据时,要与列名一一对应;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+----+--------------+---------------------------+-------+---------+
实例:向websites表中插入数据;
insert into Websites(name,url,alexa,country) values('百度','https://www.baidu.com/','4','CN');
实例:向websites表的指定列中插入数据;
insert into Websites(name,url,country)
VALUES ('stackoverflow', 'http://stackoverflow.com/', 'IND');
3.2、insert into select
-
insert into select 语句从一个保复制数据到另一个已存在的表;
-
目标表中任何已存在的行都不会受影响;
-
##语法 ##复制所有的数据到另一个已存在的表 insert into table2 select * from table1; ##复制一些列到另一个已存在的表; insert into table2(column_name(s)) select column_name(s) from table1;
##Websites表
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
##apps表
+----+------------+-------------------------+---------+
| id | app_name | url | country |
+----+------------+-------------------------+---------+
| 1 | QQ APP | http://im.qq.com/ | CN |
| 2 | 微博 APP | http://weibo.com/ | CN |
| 3 | 淘宝 APP | https://www.taobao.com/ | CN |
+----+------------+-------------------------+---------+
实例:复制“apps”中的数据插入到Websites中;
insert into Websites(name,country)
select app_name,country from apps;
实例:只复制 id = 1 的数据到Websites
insert into Websites(name,country)
select app_name,country from apps
where id=1;
3.3、Select into 与insert into select区别
书签:insert into select 之后回去查看;
四、update
-
update语句用于更新表中的记录;
-
##语法 update table_name set column1 = value1,column2 = value2 where column_name operator value;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+----+--------------+---------------------------+-------+---------+
实例:把“菜鸟教程”的alexa排名更新为5000,country改为USA
update Websites set alexa = '5000',country='USA'
where name = '菜鸟教程';
- 注意:如果不加上where语句进行判断的话,那么就相当于把所有的alexa改为5000,country改为USA;
- 在MySQL中,可以设置==sql_safe_updates== 这个自带的参数来解决,当该参数开启,则必须在update语句中加上where条件,否则就会报错。(回去更深层次地了解mysql)
update Websites set alexa = '5000',country='USA';
五、delete
5.1、delete介绍
-
delete语句用于删除表中的记录;
-
##语法 delete from table_name where some_column = some_value;
注意:如果不加上where子句用来判断,那么所有的记录都将被删除;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
+----+--------------+---------------------------+-------+---------+
实例:从Websites表中删除网站名为“Facebook”且国家为USA的网站;
delete from Websites
where name ='Facebook' and country = 'USA';
-
删除表中所有记录;
-
但是表结构、属性、索引将保持不变;
-
delete from table_name; ##或者 delete * from table_name;
-
5.2、drop、truncate、delete之间区别
drop table_name;
drop命令,删除表并且释放表空间(意味着将表彻底删除);
truncate table_name;
删除表的内容,并释放空间,但是不删除表的定义,即表的结构还在;
delete from table_name where some_column = some_value;
- delete命令可以删除指定数据;
- 同样,delete命令也可以仅删除表的所有内容,但是保留表的定义,不释放空间;
回去细究
六、create以及约束
6.1、创建数据库
##语法
create database db_name;
实例:创建一个名为 “my_db”的数据库;
create database my_db;
6.2、创建数据表
##语法
create table table_name (
column_name data_type(size),
column_name data_type(size),
column_name data_type(size),
....
);
- 注意:data_type:参数规定的数据类型(例如:varchar、integer、decimal、date等);
- size:参数规定表中列的最大长度;
各个数据库的数据类型:SQL MS Access、MySQL 和 SQL Server 数据类型 | 菜鸟教程 (runoob.com)
实例:创建一个名为“Persons”的表,包含:PersonID、LastName、FileName、Address和City;
create table Persons(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
不知道:int后面是不是需要加上长度
6.3、约束
-
SQL约束用于规定表中的数据规则;
-
如果存在违反约束的数据行为,行为就会被终止;
-
约束可以在创建表时规定(create table),或者表创建之后规定(alter table);
-
##语法 create table table_name( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, .... );
constraint_name:约束名
在SQL语句中,存在:
- not null----指示某列不能存储null值;
- unique----保持某列的每行必须有唯一的值;
- primary key----not null 和unique的结合,确保某列(或多列)有唯一标识,有助于快速找到表中记录;
- foreign key----保证列中的值符合指定的条件;
- check----保证列中的值符合指定的条件;
- default----规定没有给列赋值时的默认值;
6.3.1、not null
七、函数
- SQL函数由很多用于计算和计数的内建函数;
- 其中包含SQL Aggregate函数以及SQL Scalar函数;
7.1、Aggregate函数(聚合函数)
SQL Aggregate函数计算从列中取得的值,返回一个单一的值;
其中常用的Aggregate函数包含:
- AVG() ----返回平均值
- COUNT() ----返回行数
- FIRST() ----返回第一个记录的值
- LAST() ----返回最后一个记录的值
- MAX() ----返回最大值
- MIN() ----返回最小值
- SUM() ----返回总和
7.1.1、AVG() 函数
-
AVG() 函数返回数值列的平均值;
-
##AVG() 语法 SELECT AVG(column_name) from table_name;
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
实例:从access_log表的count 列获取平均值;
select avg(count) as count_ave from access_log;
实例:选择访问量盖雨平均访问量的“site_id”和“count”;
select site_id,count from access_log
where count > (select avg(count) from access_log);
7.1.2、COUNT() 函数
- COUNT()函数返回匹配指定条件的行数;
- count(column_name) 函数返回指定列的值的数目(null 不计入);
- count(*) 返回表中的所有记录数;
- count(distict column_name) 返回指定列的不同值得数目(减去重复性数据);
- 注意:count(distinct)适用于oracle和SQL Server,但是无法适用于 Access;
##access_log表
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
实例:计算access_log表中“site_id”=3 的总访问量;
select count(count) as nums from access_log where site_id = 3;
实例:计算access_log表中总记录数;
select count(*) as num from access_log;
实例:计算access_log表中不同“site_id"的记录数;
select count(distinct site_id) as nums from access_log;
拓展项:count(*)以及count(1)之间的区别;
7.1.3、FIRST() 函数
-
first() 函数返回指定列的第一个记录的值;
-
注意:只有Access支持first()函数,但是SQL Server、MySQL、Oracle都有相应的方法;
-
select first(column_name) from table_name;
-
7.1.3.1、SQL Server
##SQL Server语法
SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;
实例:
SELECT TOP 1 name FROM Websites
ORDER BY id ASC;
7.1.3.2、MySQL
##语法
SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 6 | 百度 | https://www.baidu.com/ | 4 | CN |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
实例:
SELECT name FROM Websites
ORDER BY id ASC
LIMIT 1;
实例:选取websites表中的name列中第一个记录的值;
select name from Wesites from Websites limit 1;
7.1.3.2、Oracle
##语法
SELECT column_name FROM table_name
ORDER BY column_name ASC
WHERE ROWNUM <=1;
实例:
SELECT name FROM Websites
ORDER BY id ASC
WHERE ROWNUM <=1;
7.1.4、LAST() 函数
-
last() 函数返回指定的列中最后一个记录的值;
-
同first() 函数一样,只有Access支持last()函数,但是各个数据库都有各自的方法;
-
select last(column_name) from table_name;
-
7.1.4.1、SQL Server
##语法
SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;
实例:
SELECT TOP 1 name FROM Websites
ORDER BY id DESC;
7.1.4.2、MySQL
##语法
SELECT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 6 | 百度 | https://www.baidu.com/ | 4 | CN |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
实例:
SELECT name FROM Websites
ORDER BY id DESC
LIMIT 1;
7.1.4.3、Oracle
##语法
SELECT column_name FROM table_name
ORDER BY column_name DESC
WHERE ROWNUM <=1;
实例:
SELECT name FROM Websites
ORDER BY id DESC
WHERE ROWNUM <=1;
7.1.5、MAX() 函数
-
max()函数返回指定列的最大值;
-
##语法 select max(column_name) from table_name;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 5000 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 6 | 百度 | https://www.baidu.com/ | 4 | CN |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
实例:从Websites表的alexa列获取最大值;
select max(alexa) as max_alexa from Websites;
7.1.6、MIN() 函数
-
min()函数返回指定列的最小值;
-
select min(column_name) from table_name;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 6 | 百度 | https://www.baidu.com/ | 4 | CN |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
实例:从Websites表中的alexa列获取最小值;
select min(alexa) as min_alexa from Websites;
7.1.7、SUM() 函数
-
sum() 函数返回数值列的总数;
-
select sum(column_name) from table_name;
+-----+---------+-------+------------+
| aid | site_id | count | date |
+-----+---------+-------+------------+
| 1 | 1 | 45 | 2016-05-10 |
| 2 | 3 | 100 | 2016-05-13 |
| 3 | 1 | 230 | 2016-05-14 |
| 4 | 2 | 10 | 2016-05-14 |
| 5 | 5 | 205 | 2016-05-14 |
| 6 | 4 | 13 | 2016-05-15 |
| 7 | 3 | 220 | 2016-05-15 |
| 8 | 5 | 545 | 2016-05-16 |
| 9 | 3 | 201 | 2016-05-17 |
+-----+---------+-------+------------+
实例:在access_log表中,查找count字段的总数;
select sum(count) as nums from access_log;
7.2、SQL Scalar 函数
SQL Scalar 函数基于输入值,返回一个单一的值。
有用的 Scalar 函数:
- UCASE() ---- 将某个字段转换为大写
- LCASE() ---- 将某个字段转换为小写
- MID() ---- 从某个文本字段提取字符,MySql 中使用
- SubString(字段,1,end) ---- 从某个文本字段提取字符
- LEN() ---- 返回某个文本字段的长度
- ROUND() ---- 对某个数值字段进行指定小数位数的四舍五入
- NOW() ---- 返回当前的系统日期和时间
- FORMAT() ---- 格式化某个字段的显示方式
7.2.1、UCASE()函数
-
UCASE()函数把字段的值转换为大写;
-
##SQL语法 select ucase(column_name) from table_name;
注意:SQL Server的转换大写函数是UPPER()函数;
##SQL Server
select upper(column_name) from table_name;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
实例:从Websites表中选取name和url列,并把name列的值转换为大写;
select ucase(name) as title,url from websites;
7.2.2、LCASE()函数
-
LCASE()函数把字段的值转换为 小写;
-
##SQL语法 select lcase(column_name) from table_name;
注意:SQL Server的转换小写函数是LOWER()函数;
##SQL Server语法
select lower(column_name) from table_name;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
实例:从websites表中选取name和url列,并且把name列的值转换为小写;
select lcase(name) as site_title,url from websites;
7.2.3、MID()函数
-
MID()函数用于从文本字段中提取字符;
-
##MID语法 select mid(column_name,start,length) from table_name;
- start:规定开始位置(起始位置是1);
- length:要返回的字符数(如果省略,则MID()函数 将会返回剩余文本);
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
实例:从websites表中的name列中提取4个字符;
select mid(name,1,4) as shorttitle from website;
注意:oracle中没有MID函数,有substr函数具有类似功能;
##oracle 语法
select substr(column_name,start,length) from table_name;
实例:
select substr(name,1,4) as shorttitle from websites;
7.2.4、LEN()函数
-
LEN()函数返回文本字段中值的长度;
-
##语法 select len(column_name) from table_name;
注意:MySQL中函数为LENGTH()
##语法
select length(column_name) from table_name;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
实例:从websites表中选取name和url列中值的长度;
select name,length(url) as url_length from websites;
7.2.5、ROUND()函数
-
round()函数用于把数值字段舍入(四舍五入)为指定的小数位数;
-
##语法 select round(column_name,decimals) from table_name;
- decimals:规定要返回的小数位数;
- 如果decimals 为0则没有小数点 或者小数部分;
实例:round(x),返回参数x的四舍五入的一个整数;
select round(-1.23); ##-1
select round(-1.58); ##-2
select round(1.58); ##2
实例:round(x,d),返回参数x的四舍五入的有d位小数的一个数字
select round(1.298,1); ##1.3
select round(1.298,0); ##1
注意:round返回值被变换为一个BIGINT;细究
7.2.6、NOW()函数
-
now()函数返回当前系统的日期和时间;
-
##语法 select now() from table_name;
实例:从websites表中选取name,url以及当天日期;
select name,url,now() as date from websites;
7.2.7、FORMAT()函数(使用较多)
-
format()函数用于对字段的显式进行格式化;
-
##语法 select format(column_name,format) from table_name;
- format:规定格式;
+----+--------------+---------------------------+-------+---------+
| id | name | url | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1 | Google | https://www.google.cm/ | 1 | USA |
| 2 | 淘宝 | https://www.taobao.com/ | 13 | CN |
| 3 | 菜鸟教程 | http://www.runoob.com/ | 4689 | CN |
| 4 | 微博 | http://weibo.com/ | 20 | CN |
| 5 | Facebook | https://www.facebook.com/ | 3 | USA |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
+----+---------------+---------------------------+-------+---------+
实例:从websites表中选取name,url以及格式为YYYY-MM-DD的日期;
select name,url,DATE_FORMAT(Now(),'%Y-%m-%d' as date from websites;
数的一个数字
select round(1.298,1); ##1.3
select round(1.298,0); ##1
注意:round返回值被变换为一个BIGINT;细究
版本说明
在学习oracle的时候,了解到DDL、DML、DCL、DQL的区别;所以此处,只能是1.0的版本,之后的基础SQL语句以及函数将会以1.1版本出现;