SQL学习教程(补充)
参考资料
https://www.runoob.com/sql/sql-tutorial.html-SQL菜鸟教程
https://www.runoob.com/mysql/-MySQL菜鸟教程
union操作符
SQL UNION 操作符合并两个或多个 SELECT 语句的结果。
注意,UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 SELECT 语句中的列的顺序必须相同。
语法
- SQL UNION 语法
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
默认地,UNION 操作符选取不同的值。如果允许重复的值,请使用 UNION ALL。
- SQL UNION ALL 语法
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
演示数据库
在本教程中,我们将使用 RUNOOB 样本数据库。
- 下面是选自 “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 |
| 7 | stackoverflow | http://stackoverflow.com/ | 0 | IND |
±—±--------------±--------------------------±------±--------+ - 下面是 “apps” APP 的数据:
mysql> SELECT * FROM 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 |
±—±-----------±------------------------±--------+
3 rows in set (0.00 sec)
SQL UNION 实例操作
下面的 SQL 语句从 “Websites” 和 “apps” 表中选取所有不同的country(只有不同的值):
SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
注释:UNION 不能用于列出两个表中所有的country。如果一些网站和APP来自同一个国家,每个国家只会列出一次。UNION 只会选取不同的值。请使用 UNION ALL 来选取重复的值!
- SQL UNION ALL 实例操作
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;
- 带有 WHERE 的 SQL UNION ALL
下面的 SQL 语句使用 UNION ALL 从 “Websites” 和 “apps” 表中选取所有的中国(CN)的数据(也有重复的值):
SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'
ORDER BY country;
注意:使用UNION命令时需要注意,只能在最后使用一个ORDER BY命令,是将两个查询结果合在一起之后,再进行排序!绝对不能写两个ORDER BY命令。
concat函数
concat函数用于将两个字符串连接在一起
比如
SELECT CONCAT('FIRST ', 'SECOND');//得到 FIRST SECOND
SELECT CONCAT(id, name, work_date)
-> FROM employee_tbl;//就可以将表中id,name,work_date3项连接在一起
GROUP BY 语句
GROUP BY 语句可结合一些聚合函数来使用,根据一个或多个列对结果集进行分组。
语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
- 下面是 “access_log” 网站访问记录表的数据:
mysql> SELECT * FROM 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 |
+-----+---------+-------+------------+
9 rows in set (0.00 sec)
- 实际应用
统计 access_log 各个 site_id 的访问量:
SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id; //把access_log.count字段下同size_id的聚合为一个新的字段nums,并根据site_id排序
结果如下
SQL GROUP BY 多表连接
下面是选自 “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 |
+----+---------------+---------------------------+-------+---------+
下面的 SQL 语句统计有记录的网站的记录数量:
SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;
查询结果:
group_concat函数
语法
group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
实例
有表aa:
+------+------+
| id| name |
+------+------+
|1 | 10|
|1 | 20|
|1 | 20|
|2 | 20|
|3 | 200 |
|3 | 500 |
+------+------+
以id分组,把name字段的值打印在一行,分号分隔
select id,group_concat(name separator ';') from aa group by id;
+------+----------------------------------+
| id| group_concat(name separator ';') |
+------+----------------------------------+
|1 | 10;20;20 |
|2 | 20|
|3 | 200;500 |
+------+----------------------------------+
以id分组,把去冗余的name字段的值打印在一行,
select id,group_concat(distinct name) from aa group by id;
以id分组,把name字段的值打印在一行,逗号分隔,以name排倒序
select id,group_concat(name order by name desc) from aa group by id;
LEFT JOIN 关键字
LEFT JOIN 关键字从左表(table1)返回所有的行,即使右表(table2)中没有匹配如果右表中没有匹配,则结果为 NULL。
语法
SELECT column_name(s)
FROM table1//FROM紧跟返回的表
LEFT JOIN table2//LEFT JOIN紧跟连接的表
ON table1.column_name=table2.column_name;//表1与表2连接的关联条件
或:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
注释:在某些数据库中,LEFT JOIN 称为 LEFT OUTER JOIN。
实例
- 下面的 SQL 语句将返回所有网站及他们的访问量(如果有的话)。以下实例中我们把 Websites 作为左表,access_log 作为右表:
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;
MySQL 查询数据
MySQL 数据库使用SQL SELECT语句来查询数据。
你可以通过 mysql> 命令提示窗口中在数据库中查询数据,或者通过PHP脚本来查询数据。
语法
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
- 查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。limit N : 返回 N 条记录
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。即从第一条开始查询。offset M : 跳过 M 条记录。
重点介绍LIMIT N M:
limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录。如LIMIT 5,10; // 检索记录行 6-15
为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1:LIMIT 95,-1; // 检索记录行 96-last.
如果只给定一个参数,它表示返回最大的记录行数目:LIMIT 5; //检索前 5 个记录行 。换句话说,LIMIT n 等价于 LIMIT 0,n。
- 使用PHP脚本来获取数据
使用 PHP 函数的mysqli_query()
及SQL SELECT
命令来获取数据。
该函数用于执行 SQL 命令,然后通过 PHP 函数mysqli_fetch_array()
来使用或输出所有查询的数据。
mysqli_fetch_array()
函数从结果集中取得一行作为关联数组,或数字数组,或二者兼有 返回根据从结果集取得的行生成的数组,如果没有更多行则返回 false。
以下实例为从数据表 runoob_tbl 中读取所有记录。
实例:
尝试以下实例来显示数据表 runoob_tbl 的所有记录。
<?php
$dbhost = 'localhost'; // mysql服务器主机地址
$dbuser = 'root'; // mysql用户名
$dbpass = '123456'; // mysql用户名密码
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);//连接函数,主机加用户名加密码作为参数
if(! $conn )
{
die('连接失败: ' . mysqli_error($conn));
}
// 设置编码,防止中文乱码
mysqli_query($conn , "set names utf8");
$sql = 'SELECT runoob_id, runoob_title,
runoob_author, submission_date
FROM runoob_tbl';
mysqli_select_db( $conn, 'RUNOOB' );
$retval = mysqli_query( $conn, $sql );//用mysqli_query函数进行查询
if(! $retval )
{
die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>菜鸟教程 mysqli_fetch_array 测试</h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))//读取的每行记录赋值给变量 $row,然后再打印出每个值。注意此处PHP mysqli_fetch_array() 函数第二个参数为 MYSQLI_ASSOC,设置该参数查询结果返回关联数组,你可以使用字段名称来作为数组的索引。
{
echo "<tr><td> {$row['runoob_id']}</td> ".
"<td>{$row['runoob_title']} </td> ".
"<td>{$row['runoob_author']} </td> ".
"<td>{$row['submission_date']} </td> ".
"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>
- 内存释放
在我们执行完 SELECT 语句后,释放游标内存是一个很好的习惯。
可以通过 PHP 函数mysqli_free_result()
来实现内存的释放。
以下实例演示了该函数的使用方法。