结构化查询语言SQL

最新推荐文章于 2023-01-12 00:03:14 发布

CJJ_jingjie

最新推荐文章于 2023-01-12 00:03:14 发布

阅读量501

点赞数

分类专栏： SQL 文章标签： SQL

本文链接：https://blog.csdn.net/CJJ_jingjie/article/details/79845950

版权

SQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

结构化查询语言SQL

本例中使用的数据库为MySQL，数据库为sql_learning，数据表分别有websites、access_log和apps。

SQL-SELECT

SELECT语句用于从数据库中选取数据。

语法

select column_name, column_name from table_name;
select * from table_name;

示例

例1：查看“Websites”表的数据，即从“Websites”表中选取所有列

mysql> select * from 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     |
+----+----------+---------------------------+-------+---------+
5 rows in set (0.02 sec)

例2：从“WebSites”表中选取“name”和“url”列

mysql> select name, url from Websites;
+----------+---------------------------+
| name     | url                       |
+----------+---------------------------+
| Google   | https://www.google.cm/    |
| 淘宝     | https://www.taobao.com/   |
| 菜鸟教程 | http://www.runoob.com/    |
| 微博     | http://weibo.com/         |
| Facebook | https://www.facebook.com/ |
+----------+---------------------------+
5 rows in set (0.00 sec)

SQL-SELECT DISTINCT

SELECT DISTINCT语句用于返回唯一不同的值

语法

select distinct column_name, column_name from table_name;

示例

例1：查找“Websites”表中“country”列中唯一不同的值

mysql> select distinct country from Websites;
+---------+
| country |
+---------+
| USA     |
| CN      |
+---------+
2 rows in set (0.00 sec)

SQL-WHERE

WHERE字句用于过滤记录。

语法

select column_name, column_name from table_name where column_name operator value;

示例

例1：文本字段（需要加引号）
从“Websites”表中选取名字为“淘宝”的网站

mysql> select * from Websites where name="淘宝";
+----+------+-------------------------+-------+---------+
| id | name | url                     | alexa | country |
+----+------+-------------------------+-------+---------+
|  2 | 淘宝 | https://www.taobao.com/ |    13 | CN      |
+----+------+-------------------------+-------+---------+
1 row in set (0.00 sec)

例2：数值字段（可以不加引号）
从“WebSites”表中选取id为“2”的网站

mysql> select * from Websites where id = 2;
+----+------+-------------------------+-------+---------+
| id | name | url                     | alexa | country |
+----+------+-------------------------+-------+---------+
|  2 | 淘宝 | https://www.taobao.com/ |    13 | CN      |
+----+------+-------------------------+-------+---------+
1 row in set (0.00 sec)

WHERE子句中的运算符

常用运算符：=、<>、>、<、>=、<=
特殊运算符

运算符	含义
BETWEEN	在某个范围内
LIKE	搜索某种模式
IN	指定针对某个列的多个可能值

SQL-AND & OR

如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

mysql> select * from Websites where country = 'CN' and alexa > 100;
+----+----------+------------------------+-------+---------+
| id | name     | url                    | alexa | country |
+----+----------+------------------------+-------+---------+
|  3 | 菜鸟教程 | http://www.runoob.com/ |  4689 | CN      |
+----+----------+------------------------+-------+---------+
1 row in set (0.00 sec)

mysql> select * from Websites where country = 'CN' or alexa > 100;
+----+----------+-------------------------+-------+---------+
| id | name     | url                     | alexa | country |
+----+----------+-------------------------+-------+---------+
|  2 | 淘宝     | https://www.taobao.com/ |    13 | CN      |
|  3 | 菜鸟教程 | http://www.runoob.com/  |  4689 | CN      |
|  4 | 微博     | http://weibo.com/       |    20 | CN      |
|  6 | 百度     | http://www.baidu.com    |   100 | CN      |
+----+----------+-------------------------+-------+---------+
4 rows in set (0.00 sec)

SQL-ORDER BY

mysql> select name, alexa from Websites order by alexa asc;
+----------+-------+
| name     | alexa |
+----------+-------+
| Google   |     1 |
| Facebook |     3 |
| 淘宝     |    13 |
| 微博     |    20 |
| 百度     |   100 |
| 菜鸟教程 |  4689 |
+----------+-------+
6 rows in set (0.00 sec)

mysql> select name, alexa from Websites order by alexa desc;
+----------+-------+
| name     | alexa |
+----------+-------+
| 菜鸟教程 |  4689 |
| 百度     |   100 |
| 微博     |    20 |
| 淘宝     |    13 |
| Facebook |     3 |
| Google   |     1 |
+----------+-------+
6 rows in set (0.00 sec)

SQL-INSERT INTO

无需指定要插入数据的列名，只需提供被插入的值
INSERT INTO table_name
VALUES (value1,value2,value3,...);

需要指定列名及被插入的值
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

SQL-UPDATE

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

注意：如果不加where，更新所有行

SQL-DELETE

DELETE FROM table_name
WHERE some_column=some_value;

SQL-LIKE

语法：

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

LIKE pattern
% 表示0、1或多个字符
_ 表示1个字符

pattern	含义
M%	首字符为M
%MM%	包含字符MM
%M	倒数第一个字符为M
_M%	第二个字符为M
%M_	倒数第二个字符为M

mysql> select name, url, country from Websites where name like '%oo%';
+----------+---------------------------+---------+
| name     | url                       | country |
+----------+---------------------------+---------+
| Google   | https://www.google.cm/    | USA     |
| Facebook | https://www.facebook.com/ | USA     |
+----------+---------------------------+---------+
2 rows in set (0.02 sec)

SQL-通配符

MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。
regexp

mysql> select name, url from Websites where name regexp '[GFs]';
+----------+---------------------------+
| name     | url                       |
+----------+---------------------------+
| Google   | https://www.google.cm/    |
| Facebook | https://www.facebook.com/ |
+----------+---------------------------+
2 rows in set (0.02 sec)

mysql> select name, url from Websites where name regexp '^[GFs]';
+----------+---------------------------+
| name     | url                       |
+----------+---------------------------+
| Google   | https://www.google.cm/    |
| Facebook | https://www.facebook.com/ |
+----------+---------------------------+
2 rows in set (0.00 sec)

not regexp

mysql> select name, url from Websites where name not regexp '^[GFs]';
+----------+-------------------------+
| name     | url                     |
+----------+-------------------------+
| 淘宝     | https://www.taobao.com/ |
| 菜鸟教程 | http://www.runoob.com/  |
| 微博     | http://weibo.com/       |
| 百度     | http://www.baidu.com    |
+----------+-------------------------+
4 rows in set (0.00 sec)

mysql> select name, url from Websites where name regexp '^[^GFs]';
+----------+-------------------------+
| name     | url                     |
+----------+-------------------------+
| 淘宝     | https://www.taobao.com/ |
| 菜鸟教程 | http://www.runoob.com/  |
| 微博     | http://weibo.com/       |
| 百度     | http://www.baidu.com    |
+----------+-------------------------+
4 rows in set (0.00 sec)

SQL-IN

语法：

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

mysql> select name, url from Websites where name in ('Google','淘宝');
+--------+-------------------------+
| name   | url                     |
+--------+-------------------------+
| Google | https://www.google.cm/  |
| 淘宝   | https://www.taobao.com/ |
+--------+-------------------------+
2 rows in set (0.01 sec)

SQL-BETWEEN

between…and…

mysql> select id, name, url from Websites where name between 'Google'and '淘宝';
+----+--------+-------------------------+
| id | name   | url                     |
+----+--------+-------------------------+
|  1 | Google | https://www.google.cm/  |
|  2 | 淘宝   | https://www.taobao.com/ |
|  4 | 微博   | http://weibo.com/       |
+----+--------+-------------------------+
3 rows in set (0.00 sec)

SQL-JOIN

JOIN用于把来自两个或多个表的行结合起来

Join类型	含义
inner join	如果表中至少有一个匹配行，则返回行
left join	即使右表中没有匹配，也从左表所有的行
right join	即使左表中没有匹配，也从右表返回所有的行
full 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     |
|  6 | 百度     | http://www.baidu.com      |   100 | CN      |
+----+----------+---------------------------+-------+---------+

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 |
|  10 |      10 |   200 | 2018-04-08 |
|  11 |       8 |   100 | 2018-04-08 |
+-----+---------+-------+------------+

INNER JOIN（交集）

从多个表中返回满足join条件的所有行。
返回结果和join相同

语法

select column_name(s) from table1 inner join / join table2 on table1.column_name = table2.column_name;

示例

例1：查找所有网站的访问记录
inner join关键字表示表中存在至少一个匹配时返回行。示例中返回多个“Google”或“菜鸟教程”则表示存在多个匹配。
如果“Websites”表中存在的行在“access_log”中没有匹配，则不会列出这些行。示例中“Websites”表中的“百度”在“access_log”表中没有匹配，所以未返回。

inner join 查询结果
mysql> select * from Websites inner join access_log on Websites.id = access_log.site_id;
+----+----------+---------------------------+-------+---------+-----+---------+-------+------------+
| id | name     | url                       | alexa | country | aid | site_id | count | date       |
+----+----------+---------------------------+-------+---------+-----+---------+-------+------------+
|  1 | Google   | https://www.google.cm/    |     1 | USA     |   1 |       1 |    45 | 2016-05-10 |
|  1 | Google   | https://www.google.cm/    |     1 | USA     |   3 |       1 |   230 | 2016-05-14 |
|  2 | 淘宝     | https://www.taobao.com/   |    13 | CN      |   4 |       2 |    10 | 2016-05-14 |
|  3 | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      |   2 |       3 |   100 | 2016-05-13 |
|  3 | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      |   7 |       3 |   220 | 2016-05-15 |
|  3 | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      |   9 |       3 |   201 | 2016-05-17 |
|  4 | 微博     | http://weibo.com/         |    20 | CN      |   6 |       4 |    13 | 2016-05-15 |
|  5 | Facebook | https://www.facebook.com/ |     3 | USA     |   5 |       5 |   205 | 2016-05-14 |
|  5 | Facebook | https://www.facebook.com/ |     3 | USA     |   8 |       5 |   545 | 2016-05-16 |
+----+----------+---------------------------+-------+---------+-----+---------+-------+------------+
9 rows in set (0.00 sec)

LEFT JOIN（左表）

left join从左表中返回所有的行，即使右表没有匹配。
如果右表中没有匹配，右表对应列的结果为NULL

语法

select column_name(s) from table1 left join / (left outer join) table2 on table1.column_name = table2.column_name;

示例

例1：查找所有网站的访问记录
left join关键字表示从左表返回所有的行，即使右表没有匹配。示例中返回“百度”，由于“access_log”表中没有匹配，所以“access_log”表中的所有列的值为NULL。

mysql> select * from Websites left join access_log on Websites.id = access_log.site_id;
+----+----------+---------------------------+-------+---------+------+---------+-------+------------+
| id | name     | url                       | alexa | country | aid  | site_id | count | date       |
+----+----------+---------------------------+-------+---------+------+---------+-------+------------+
|  1 | Google   | https://www.google.cm/    |     1 | USA     |    1 |       1 |    45 | 2016-05-10 |
|  1 | Google   | https://www.google.cm/    |     1 | USA     |    3 |       1 |   230 | 2016-05-14 |
|  2 | 淘宝     | https://www.taobao.com/   |    13 | CN      |    4 |       2 |    10 | 2016-05-14 |
|  3 | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      |    2 |       3 |   100 | 2016-05-13 |
|  3 | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      |    7 |       3 |   220 | 2016-05-15 |
|  3 | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      |    9 |       3 |   201 | 2016-05-17 |
|  4 | 微博     | http://weibo.com/         |    20 | CN      |    6 |       4 |    13 | 2016-05-15 |
|  5 | Facebook | https://www.facebook.com/ |     3 | USA     |    5 |       5 |   205 | 2016-05-14 |
|  5 | Facebook | https://www.facebook.com/ |     3 | USA     |    8 |       5 |   545 | 2016-05-16 |
|  6 | 百度     | http://www.baidu.com      |   100 | CN      | NULL |    NULL |  NULL | NULL       |
+----+----------+---------------------------+-------+---------+------+---------+-------+------------+
10 rows in set (0.00 sec)

RIGHT JOIN（右表）

right join从右表中返回所有的行，即使左表没有匹配。
如果左表中没有匹配，左表对应列的结果为NULL

语法

select column_name(s) from table1 right join / (right outer join) table2 on table1.column_name = table2.column_name;

示例

例1：查找所有网站的访问记录
示例中倒数两行，表示右表在左表没有匹配时的查找结果。

mysql> select * from Websites right join access_log on Websites.id = access_log.site_id;
+------+----------+---------------------------+-------+---------+-----+---------+-------+------------+
| id   | name     | url                       | alexa | country | aid | site_id | count | date       |
+------+----------+---------------------------+-------+---------+-----+---------+-------+------------+
|    1 | Google   | https://www.google.cm/    |     1 | USA     |   1 |       1 |    45 | 2016-05-10 |
|    3 | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      |   2 |       3 |   100 | 2016-05-13 |
|    1 | Google   | https://www.google.cm/    |     1 | USA     |   3 |       1 |   230 | 2016-05-14 |
|    2 | 淘宝     | https://www.taobao.com/   |    13 | CN      |   4 |       2 |    10 | 2016-05-14 |
|    5 | Facebook | https://www.facebook.com/ |     3 | USA     |   5 |       5 |   205 | 2016-05-14 |
|    4 | 微博     | http://weibo.com/         |    20 | CN      |   6 |       4 |    13 | 2016-05-15 |
|    3 | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      |   7 |       3 |   220 | 2016-05-15 |
|    5 | Facebook | https://www.facebook.com/ |     3 | USA     |   8 |       5 |   545 | 2016-05-16 |
|    3 | 菜鸟教程 | http://www.runoob.com/    |  4689 | CN      |   9 |       3 |   201 | 2016-05-17 |
| NULL | NULL     | NULL                      |  NULL | NULL    |  10 |      10 |   200 | 2018-04-08 |
| NULL | NULL     | NULL                      |  NULL | NULL    |  11 |       8 |   100 | 2018-04-08 |
+------+----------+---------------------------+-------+---------+-----+---------+-------+------------+
11 rows in set (0.00 sec)

FULL JOIN（左表+右表）

full join结合了left join 和 right join 的结果，只要左表和右表其中一个表存在匹配，则返回行。

语法

select column_name(s) from table1 full join / (full outer join) table2 on table1.column_name = table2.column_name;

MySQL不支持 FULL JOIN

SQL-UNION

union：合并两个或多个select语句的结果
UNION内部每个SELECT语句必须拥有相同数量的列，列也必须拥有相似的数据类型。同时，每个SELECT语句中的列的顺序必须相同。

语法

union（去重）
select column_name(s) from table1
union
select column_name(s) from table2

union all(不去重)
select column_name(s) from table1
union all
select column_name(s) from table2

示例

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      |
|  4 | FacebookAPP | https://www.facebook.com | US      |
+----+-------------+--------------------------+---------+

例1：从“Websites”表和“apps”表中选取不同的country值。

mysql> select country from Websites union select country from apps;
+---------+
| country |
+---------+
| USA     |
| CN      |
| US      |
+---------+
3 rows in set (0.00 sec)

例2：从“Websites”表和“apps”表中选取所有的country值。

mysql> select country from Websites union all select country from apps;
+---------+
| country |
+---------+
| USA     |
| CN      |
| CN      |
| CN      |
| USA     |
| CN      |
| CN      |
| CN      |
| CN      |
| US      |
+---------+
10 rows in set (0.00 sec)

例3：带有where的union all
从 “Websites” 和 “apps” 表中选取所有的中国(CN)的数据（也有重复的值）

mysql> select country, name from Websites where country = 'CN' union all select country, app_name from apps where country = 'CN' order by country;
+---------+----------+
| country | name     |
+---------+----------+
| CN      | 微博     |
| CN      | 微博 APP |
| CN      | 淘宝     |
| CN      | 百度     |
| CN      | 淘宝 APP |
| CN      | 菜鸟教程 |
| CN      | QQ APP   |
+---------+----------+
7 rows in set (0.00 sec)

SQL-SELECT INTO

select into 语句从一个表复制数据，再把数据插入到另一个新表中

select * into newtable from oldtable

MySQL不支持 select…into，但支持insert into…select
或者使用：create table (new table) select * from (old table)

select...into 出错
mysql> select * into newwebsites from Websites;
ERROR 1327 (42000): Undeclared variable: newwebsites

mysql> create table NewWebsites select * from Websites;
Query OK, 6 rows affected (0.11 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from NewWebsites;
+----+----------+---------------------------+-------+---------+
| 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 | 百度     | http://www.baidu.com      |   100 | CN      |
+----+----------+---------------------------+-------+---------+
6 rows in set (0.00 sec)

SQL-INSERT INTO SELECT

功能同select into select
从一个表中复制数据，把数据插入到一个已经存在的表中，目标表中任何已存在的行都不会受影响

INSERT INTO table2
SELECT * FROM table1;
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

mysql> insert into NewWebsites select * from Websites;
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from NewWebsites;
+----+----------+---------------------------+-------+---------+
| 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 | 百度     | http://www.baidu.com      |   100 | CN      |
|  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 | 百度     | http://www.baidu.com      |   100 | CN      |
+----+----------+---------------------------+-------+---------+
12 rows in set (0.00 sec)

只插入某些列
mysql> insert into NewWebsites (name, url, country) select app_name, url, country from apps;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from NewWebsites;
+----+-------------+---------------------------+-------+---------+
| 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 | 百度        | http://www.baidu.com      |   100 | CN      |
|  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 | 百度        | http://www.baidu.com      |   100 | CN      |
|  0 | QQ APP      | http://im.qq.com/         |     0 | CN      |
|  0 | 微博 APP    | http://weibo.com/         |     0 | CN      |
|  0 | 淘宝 APP    | https://www.taobao.com/   |     0 | CN      |
|  0 | FacebookAPP | https://www.facebook.com  |     0 | US      |
+----+-------------+---------------------------+-------+---------+
16 rows in set (0.00 sec)

SQL-CREATE

创建数据库

create database dbname;

mysql> create database newdb;
Query OK, 1 row affected (0.00 sec)

创建数据表

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

mysql> use newdb;
Database changed
mysql> create table Person
    -> (
    -> PersonID int,
    -> LastName varchar(255),
    -> FirstName varchar(255),
    -> Address varchar(255),
    -> City varchar(255),
    -> Tel int
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> select * from Person;
Empty set (0.00 sec)

SQL-CREATE INDEX（索引）

用于更加快速高效地查询数据。
注释：更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。

CREATE INDEX index_name
ON table_name (column_name)

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

SQL-删除

drop：删除数据库、表、索引，既删除内容，又删除数据库（表、索引）本身。
truncate：只删除表内的内容，不删除表本身。

MySQL-Date函数

函数	含义
NOW()	返回当前日期和时间
CURDATE()	返回当前日期
CURTIME()	返回当前时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间单独部分
DATE_ADD()	向日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

SQL函数

SQL Aggregate函数

函数	含义
AVG()	返回平均值
COUNT()	返回行数
FIRST()	返回第一个记录的值
LAST()	返回最后一个记录的值
MAX()	返回最大值
MIN()	返回最小值
SUM()	返回总和

SQL Scalar函数

函数	含义
UCASE()	将某个字段转换为大写
LCASE()	将某个字段转换为小写
MID()	从某个文本字段提取字符，MySQL中使用
SubString(字段, 1, end)	从某个文本字段提取字符
LEN()	返回某个文本字段的长度
ROUND()	对某个数值字段进行指定小数位数的四舍五入
NOW()	返回当前系统的日期和时间
FORMAT()	格式化某个字段的显示方式

- AVG()

返回“NewWebsites”表中”alexa”列的平均值

mysql> select avg(alexa) from NewWebsites;
+------------+
| avg(alexa) |
+------------+
|   603.2500 |
+------------+
1 row in set (0.03 sec)

mysql> select * from NewWebsites where alexa > (select AVG(alexa) from NewWebsites);
+----+----------+------------------------+-------+---------+
| id | name     | url                    | alexa | country |
+----+----------+------------------------+-------+---------+
|  3 | 菜鸟教程 | http://www.runoob.com/ |  4689 | CN      |
|  3 | 菜鸟教程 | http://www.runoob.com/ |  4689 | CN      |
+----+----------+------------------------+-------+---------+
2 rows in set (0.01 sec)

COUNT()

mysql> select count(name) from NewWebsites;
+-------------+
| count(name) |
+-------------+
|          16 |
+-------------+
1 row in set (0.00 sec)

mysql> select count(*) as num from NewWebsites;
+-----+
| num |
+-----+
|  16 |
+-----+
1 row in set (0.02 sec)

返回不同的值的总数
mysql> select count(distinct name) as num from NewWebsites;
+-----+
| num |
+-----+
|  10 |
+-----+
1 row in set (0.02 sec)

FIRST() / LAST()
只有 MS Access 支持 FIRST() / LAST() 函数

SELECT FIRST(column_name) FROM table_name;

SQL Server
FIRST() ASC;
LAST() DESC;

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;

MySQL

select column_name from table_name order by asc/desc column_name limit 1;

mysql> select name, alexa from NewWebsites order by alexa limit 1;
+-------------+-------+
| name        | alexa |
+-------------+-------+
| FacebookAPP |     0 |
+-------------+-------+
1 row in set (0.00 sec)

mysql> select name, alexa from NewWebsites order by alexa desc limit 1;
+----------+-------+
| name     | alexa |
+----------+-------+
| 菜鸟教程 |  4689 |
+----------+-------+
1 row in set (0.00 sec)

Oracle

SELECT column_name FROM table_name
ORDER BY column_name ASC/DESC
WHERE ROWNUM <=1;

MAX()

mysql> select max(alexa) as maxnum from NewWebsites;
+--------+
| maxnum |
+--------+
|   4689 |
+--------+
1 row in set (0.00 sec)

MIN()

mysql> select min(alexa) as minnum from NewWebsites;
+--------+
| minnum |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

SUM()
返回数值列的总数

select sum(column_name) from table_name;

mysql> select sum(alexa) as sum_alexa from NewWebsites;
+-----------+
| sum_alexa |
+-----------+
|      9652 |
+-----------+
1 row in set (0.00 sec)

GROUP BY()
GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

mysql> select name, sum(NewWebsites.alexa) from NewWebsites group by name;
+-------------+------------------------+
| name        | sum(NewWebsites.alexa) |
+-------------+------------------------+
| Facebook    |                      6 |
| FacebookAPP |                      0 |
| Google      |                      2 |
| QQ APP      |                      0 |
| 微博        |                     40 |
| 微博 APP    |                      0 |
| 淘宝        |                     26 |
| 淘宝 APP    |                      0 |
| 百度        |                    200 |
| 菜鸟教程    |                   9378 |
+-------------+------------------------+
10 rows in set (0.00 sec)

mysql> select w.name, count(a.aid) as num from access_log as a join Websites as w on a.site_id = w.id group by w.name;
+----------+-----+
| name     | num |
+----------+-----+
| Facebook |   2 |
| Google   |   2 |
| 微博     |   1 |
| 淘宝     |   1 |
| 菜鸟教程 |   3 |
+----------+-----+
5 rows in set (0.00 sec)

left join：name中多了NULL
mysql> select w.name, count(a.aid) as num from access_log as a left join Websites as w on a.site_id = w.id group by w.name;
+----------+-----+
| name     | num |
+----------+-----+
| NULL     |   2 |
| Facebook |   2 |
| Google   |   2 |
| 微博     |   1 |
| 淘宝     |   1 |
| 菜鸟教程 |   3 |
+----------+-----+
6 rows in set (0.00 sec)

right join：name中保留了Websites表中的“百度”
mysql> select w.name, count(a.aid) as num from access_log as a right join Websites as w on a.site_id = w.id group by w.name;
+----------+-----+
| name     | num |
+----------+-----+
| Facebook |   2 |
| Google   |   2 |
| 微博     |   1 |
| 淘宝     |   1 |
| 百度     |   0 |
| 菜鸟教程 |   3 |
+----------+-----+
6 rows in set (0.00 sec)

HAVING
在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。

没有having

mysql> select w.name, w.url, sum(a.count) as num from access_log as a join Websites as w on a.site_id = w.id group by w.name;
+----------+---------------------------+------+
| name     | url                       | num  |
+----------+---------------------------+------+
| Facebook | https://www.facebook.com/ |  750 |
| Google   | https://www.google.cm/    |  275 |
| 微博     | http://weibo.com/         |   13 |
| 淘宝     | https://www.taobao.com/   |   10 |
| 菜鸟教程 | http://www.runoob.com/    |  521 |
+----------+---------------------------+------+
5 rows in set (0.00 sec)

加上having筛选 count>200

mysql> select w.name, w.url, sum(a.count) as num from access_log as a join Websites as w on a.site_id = w.id group by w.name having sum(a.count) > 200;
+----------+---------------------------+------+
| name     | url                       | num  |
+----------+---------------------------+------+
| Facebook | https://www.facebook.com/ |  750 |
| Google   | https://www.google.cm/    |  275 |
| 菜鸟教程 | http://www.runoob.com/    |  521 |
+----------+---------------------------+------+
3 rows in set (0.00 sec)

UCASE() / LCASE()

mysql> select ucase(name) as site_title, url, lcase(country) from Websites;
+------------+---------------------------+----------------+
| site_title | url                       | lcase(country) |
+------------+---------------------------+----------------+
| GOOGLE     | https://www.google.cm/    | usa            |
| 淘宝       | https://www.taobao.com/   | cn             |
| 菜鸟教程   | http://www.runoob.com/    | cn             |
| 微博       | http://weibo.com/         | cn             |
| FACEBOOK   | https://www.facebook.com/ | usa            |
| 百度       | http://www.baidu.com      | cn             |
+------------+---------------------------+----------------+
6 rows in set (0.02 sec)

MID()
语法：

SELECT MID(column_name,start[,length]) FROM table_name;

column_name：必需
start：必需，规定开始位置，起始值为1
length：可选。要返回的字符数。如果省略，则MID()函数返回剩余文本。

mysql> select mid(name, 2, 2) as mid_name from Websites;
+----------+
| mid_name |
+----------+
| oo       |
| 宝       |
| 鸟教     |
| 博       |
| ac       |
| 度       |
+----------+
6 rows in set (0.03 sec)

mysql> select mid(name, 2) as mid_name from Websites;
+----------+
| mid_name |
+----------+
| oogle    |
| 宝       |
| 鸟教程   |
| 博       |
| acebook  |
| 度       |
+----------+
6 rows in set (0.00 sec)

LEN()
MySQL中为length()

mysql> select name, length(url) from Websites;
+----------+-------------+
| name     | length(url) |
+----------+-------------+
| Google   |          22 |
| 淘宝     |          23 |
| 菜鸟教程 |          22 |
| 微博     |          17 |
| Facebook |          25 |
| 百度     |          20 |
+----------+-------------+
6 rows in set (0.02 sec)

ROUND()
ROUND(X)：返回参数X的四舍五入的一个整数。
ROUND(X,D)：返回参数X的四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分。

mysql> select round(1.88);
+-------------+
| round(1.88) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> select round(1.88,0);
+---------------+
| round(1.88,0) |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

mysql> select round(1.88,1);
+---------------+
| round(1.88,1) |
+---------------+
|           1.9 |
+---------------+
1 row in set (0.00 sec)

NOW()
语法：

select now() from table_name;

mysql> select name, now() from Websites;
+----------+---------------------+
| name     | now()               |
+----------+---------------------+
| Google   | 2018-04-08 19:55:29 |
| 淘宝     | 2018-04-08 19:55:29 |
| 菜鸟教程 | 2018-04-08 19:55:29 |
| 微博     | 2018-04-08 19:55:29 |
| Facebook | 2018-04-08 19:55:29 |
| 百度     | 2018-04-08 19:55:29 |
+----------+---------------------+
6 rows in set (0.00 sec)

FORMAT()
语法：

select format(column_name, format) from table_name;

mysql> select name, date_format(now(), '%Y-%m-%d') as date from Websites;
+----------+------------+
| name     | date       |
+----------+------------+
| Google   | 2018-04-08 |
| 淘宝     | 2018-04-08 |
| 菜鸟教程 | 2018-04-08 |
| 微博     | 2018-04-08 |
| Facebook | 2018-04-08 |
| 百度     | 2018-04-08 |
+----------+------------+
6 rows in set (0.01 sec)

CJJ_jingjie

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
结构化查询语言SQL

结构化查询语言SQL本例中使用的数据库为MySQL，数据库为sql_learning，数据表分别有websites、access_log和apps。目录结构化查询语言SQL目录SQL-SELECT语法示例SQL-SELECT DISTINCT语法示例SQL-WHERE语法示例WHERE子句中的运算符SQL-AND &amp; ORSQ...
复制链接

扫一扫

专栏目录