sql的常用操作

基本操作

1.select

选取数据

select * from table_name;

表示从table_name这个表中获取所有的列

select name,country from table_name;

表示从table_name这个表中获取“name"和"country"列

2.select distinct

返回唯一不同的值

select distinct country from table_name;

从table_name表中的”country"列选取唯一不同的值，也就是去除重复值

3.where

用于过滤记录

SELECT * FROM Websites WHERE country='CN';

从websites表中选取国家为“CN”的所有网站

文本字段用单引号（这里的CN），数值字段不用引号

4.and&or

用于基于一个以上的条件对记录进行过滤

SELECT * FROM Websites
WHERE country='CN'
AND alexa > 50;

从 "Websites" 表中选取国家为 "CN" 且alexa排名大于 "50" 的所有网站

SELECT * FROM Websites
WHERE country='USA'
OR country='CN';

从 "Websites" 表中选取国家为 "USA" 或者 "CN" 的所有客户

5.oder by

用于对结果集进行排序

SELECT * FROM Websites
ORDER BY alexa;

从 "Websites" 表中选取所有网站，并按照 "alexa" 列排序（默认是升序）

SELECT * FROM Websites
ORDER BY alexa DESC;

按照 "alexa" 列排序，降序

SELECT * FROM Websites
ORDER BY country,alexa;

从 "Websites" 表中选取所有网站，并按照 "country" 和 "alexa" 列排序

6.select limit/select top percent

用于规定要返回的记录的数目

SELECT * FROM Websites LIMIT 2;

从 "Websites" 表中选取头两条记录

SELECT TOP 50 PERCENT * FROM Websites;

从 websites 表中选取前面百分之 50 的记录

7.like

用于在 WHERE 子句中搜索列中的指定模式

SELECT * FROM Websites
WHERE name LIKE 'G%';

选取 name 以字母 "G" 开始的所有客户

SELECT * FROM Websites
WHERE name LIKE '%k';

选取 name 以字母 "k" 结尾的所有客户

SELECT * FROM Websites
WHERE name LIKE '%oo%';

选取 name 包含模式 "oo" 的所有客户

8.通配符

9.in

IN 操作符允许您在 where 子句中规定多个值。

SELECT * FROM Websites
WHERE name IN ('Google','菜鸟教程');

选取 name 为 "Google" 或 "菜鸟教程" 的所有网站

10.between

选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20;

选取 alexa 介于 1 和 20 之间的所有网站

SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H';

选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站

SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

选取 date 介于 '2016-05-10' 和 '2016-05-14' 之间的所有访问记录

11.as取别名

列的别名

SELECT name AS n, country AS c
FROM Websites;

指定了两个别名，一个是 name 列的别名，一个是 country 列的别名

SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;

把三个列（url、alexa 和 country）结合在一起，并创建一个名为 "site_info" 的别名

表的别名

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="菜鸟教程";

我们使用 "Websites" 和 "access_log" 表，并分别为它们指定表别名 "w" 和 "a"

12.join

用于把来自两个或多个表的行结合起来,基于这些表之间的共同字段

inner join

在表中存在至少一个匹配时返回行,如果 "Websites" 表中的行在 "access_log" 中没有匹配，则不会列出这些行。

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id;

通过websites表的id和access_log表的site_id将两个表关联起来

left join

从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

right join

从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

SELECT websites.name, access_log.count, access_log.date
FROM websites
RIGHT JOIN access_log
ON access_log.site_id=websites.id
ORDER BY access_log.count DESC;

13.union

union

合并两个或多个 SELECT 语句的结果集，选取不同的值

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

从 "Websites" 和 "apps" 表中选取所有不同的country

union all

合并的结果集可以有重复值

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

从 "Websites" 和 "apps" 表中选取所有的country

UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名

14.update

更新表中的记录

UPDATE Websites 
SET alexa='5000', country='USA' 
WHERE name='菜鸟教程';

把 "菜鸟教程" 的 alexa 排名更新为 5000，country 改为 USA。

在更新记录时要格外小心！如果省略了where子句，就会将所有数据的alexa和country都更新了

15.delete

删除表中的行。

DELETE FROM Websites
WHERE name='Facebook' AND country='USA';

从 "Websites" 表中删除网站名为 "Facebook" 且国家为 USA 的网站。

16.insert into

向表中插入新记录

INSERT INTO Websites (name, url, alexa, country)
VALUES ('百度','https://www.baidu.com/','4','CN');

在指定列插入数据

INSERT INTO Websites (name, url, country)
VALUES ('stackoverflow', 'http://stackoverflow.com/', 'IND');

将插入一个新行，但是只在 "name"、"url" 和 "country" 列插入数据（id 字段会自动更新）

17.group by

结合聚合函数，根据一个或多个列对结果集进行分组

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

统计 access_log 各个 site_id 的访问量

18.sum()函数

返回数值列的总数。

SELECT SUM(count) AS nums FROM access_log;

查找 "access_log" 表的 "count" 字段的总数

19.count()函数

返回匹配指定条件的行数

1.COUNT(column_name)

SELECT COUNT(count) AS nums FROM access_log
WHERE site_id=3;

nums:3

不统计列值为空的记录

2.count(*)

计算 "access_log" 表中总记录数

SELECT COUNT(*) AS nums FROM access_log;

nums:9

3.COUNT(DISTINCT column_name)

SELECT COUNT(DISTINCT site_id) AS nums FROM access_log;

计算 "access_log" 表中不同 site_id 的记录数

nums:5