MySQL 查询数据
MySQL 数据库使用SQL SELECT语句来查询数据。
你可以通过 mysql> 命令提示窗口中在数据库中查询数据,或者通过PHP脚本来查询数据。
语法
以下为在MySQL数据库中查询数据通用的 SELECT 语法:
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N][ OFFSET M]
查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
SELECT 命令可以读取一条或者多条记录。
你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
你可以使用 WHERE 语句来包含任何条件。
你可以使用 LIMIT 属性来设定返回的记录数。
你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
通过命令提示符获取数据
以下实例我们将通过 SQL SELECT 命令来获取 MySQL 数据表 runoob_tbl 的数据:
实例
以下实例将返回数据表 runoob_tbl 的所有记录:
读取数据表:
select * from runoob_tbl;
输出结果:
使用PHP脚本来获取数据
使用 PHP 函数的 mysqli_query()
及 SQL SELECT 命令来获取数据。
该函数用于执行 SQL 命令,然后通过 PHP 函数 mysqli_fetch_array() 来使用或输出所有查询的数据。
mysqli_fetch_array() 函数从结果集中取得一行作为关联数组,或数字数组,或二者兼有 返回根据从结果集取得的行生成的数组,如果没有更多行则返回 false。
以下实例为从数据表 runoob_tbl 中读取所有记录。
实例
尝试以下实例来显示数据表 runoob_tbl 的所有记录。
使用 mysqli_fetch_array MYSQLI_ASSOC 参数获取数据:
<?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 );
if(! $retval )
{
die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>
mysqli_fetch_array 测试';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_array($retval, 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);
?>
输出结果如下所示:
以上实例中,读取的每行记录赋值给变量 $row,然后再打印出每个值。
注意:记住如果你需要在字符串中使用变量,请将变量置于花括号。
在上面的例子中,PHP mysqli_fetch_array() 函数第二个参数为 MYSQLI_ASSOC, 设置该参数查询结果返回关联数组,你可以使用字段名称来作为数组的索引。
PHP 提供了另外一个函数 mysqli_fetch_assoc(), 该函数从结果集中取得一行作为关联数组。 返回根据从结果集取得的行生成的关联数组,如果没有更多行,则返回 false。
实例
尝试以下实例,该实例使用了 mysqli_fetch_assoc() 函数来输出数据表 runoob_tbl 的所有记录:
使用 mysqli_fetch_assoc 获取数据:
<?php
$dbhost = 'localhost:3306'; // 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 );
if(! $retval )
{
die('无法读取数据: ' . mysqli_error($conn));
}
echo '<h2>菜鸟教程 mysqli_fetch_assoc 测试</h2>';
echo '<table border="1"><tr><td>教程 ID</td><td>标题</td><td>作者</td><td>提交日期</td></tr>';
while($row = mysqli_fetch_assoc($retval))
{
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);
?>
输出结果如下所示:
你也可以使用常量 MYSQLI_NUM 作为 PHP mysqli_fetch_array() 函数的第二个参数,返回数字数组。
实例
以下实例使用 MYSQLI_NUM 参数显示数据表 runoob_tbl 的所有记录:
使用 mysqli_fetch_array MYSQLI_NUM 参数获取数据:
<?php
$dbhost = 'localhost:3306'; // 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 );
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_NUM))
{
echo "<tr><td> {$row[0]}</td> ".
"<td>{$row[1]} </td> ".
"<td>{$row[2]} </td> ".
"<td>{$row[3]} </td> ".
"</tr>";
}
echo '</table>';
mysqli_close($conn);
?>
输出结果如下所示:
以上三个实例输出结果都一样。
内存释放
在我们执行完 SELECT 语句后,释放游标内存是一个很好的习惯。
可以通过 PHP 函数 mysqli_free_result()
来实现内存的释放。
select _column,_column from _table [where Clause] [limit N][offset M]
select * : 返回所有记录
limit N : 返回 N 条记录
offset M : 跳过 M 条记录, 默认 M=0, 单独使用似乎不起作用
limit N,M : 相当于 limit M offset N , 从第 N 条记录开始, 返回 M 条记录
实现分页:
select * from _table limit (page_number-1)*lines_perpage, lines_perpage
或
select * from _table limit lines_perpage offset (page_number-1)*lines_perpage
zhaojjiang
zhaojjiang
MySQL limit 应用的一些例子
语法格式:
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
解析:LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。
mysql> SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
//如果只给定一个参数,它表示返回最大的记录行数目:
mysql> SELECT * FROM table LIMIT 5; //检索前 5 个记录行
//换句话说,LIMIT n 等价于 LIMIT 0,n。
Mysql 的分页查询语句的性能分析
MySql 分页 sql 语句,如果和 MSSQL 的 TOP 语法相比,那么 MySQL 的 LIMIT 语法要显得优雅了许多。使用它来分页是再自然不过的事情了。
最基本的分页方式:
SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT
…
在中小数据量的情况下,这样的 SQL 足够用了,唯一需要注意的问题就是确保使用了索引。
举例来说,如果实际 SQL 类似下面语句,那么在 category_id, id 两列上建立复合索引比较好。
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 50, 10
子查询的分页方式
随着数据量的增加,页数会越来越多,查看后几页的 SQL 就可能类似:
SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10
一言以蔽之,就是越往后分页,LIMIT 语句的偏移量就会越大,速度也会明显变慢。
此时,我们可以通过子查询的方式来提高分页效率,大致如下:
SELECT * FROM articles WHERE id >=
(SELECT id FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10
JOIN 分页方式
SELECT * FROM `content` AS t1
JOIN (SELECT id FROM `content` ORDER BY id desc LIMIT ".($page-1)*$pagesize.", 1) AS t2
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;
经过我的测试,join 分页和子查询分页的效率基本在一个等级上,消耗的时间也基本一致。
explain SQL语句
:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1 PRIMARY t1 range PRIMARY PRIMARY 4 NULL 6264 Using where
2 DERIVED content index NULL PRIMARY 4 NULL 27085 Using index
为什么会这样呢?因为子查询是在索引上完成的,而普通的查询时在数据文件上完成的,通常来说,索引文件要比数据文件小得多,所以操作起来也会更有效率。
实际可以利用类似策略模式的方式去处理分页,比如判断如果是一百页以内
,就使用最基本的分页方式
,大于一百页
,则使用子查询的分页方式
。
Mysql 简单查询语句
可以通过不同的查询语句进行套用。
/*websites 表名 NAME alexa url country 字段*/
SELECT * FROM websites; /* 查询表所有数据 */
SELECT NAME FROM websites; /* 查询表字段数据 */
SELECT * FROM websites where name = "广西"; /* 查询表字段下条件数据 */
SELECT * from websites where name like "_o%"; /* 模糊查询表下数据 */
SELECT * FROM websites where id BETWEEN "1" AND "5"; /* 查询表下字段范围数据 */
SELECT * FROM websites WHERE name in ("广西","百度"); /* 查询表字段下固定条件数据 */
SELECT DISTINCT country FROM Websites; /* 查询去重值 */
SELECT * FROM Websites WHERE country = "CN" AND alexa > 50; /*查询表下范围条件数据*/
SELECT * FROM Websites WHERE country = "USA" OR country="sh"; /* 查询表下条件不同值 */
SELECT * FROM Websites ORDER BY alexa; /* 查询表下值排序结果 */
SELECT * FROM Websites ORDER BY alexa DESC; /* 查询表下排序结果降序 */
SELECT * FROM Websites LIMIT 2; /* 查询表下范围数据 */
SELECT name as zzz from websites; /*别名查询表下数据*/
sql 语句的关联查询
左关联: left join ... on ...
右关联: right join... on ...
格式:
select 字段 from 表1 left join 表2 on 条件 (一般为表1与表2的关联条件)
查询用户的所有订单信息 :
user 用户表
orders 订单表
select * from user left join orders on user.id = orders.user_id
稍微复杂点 统计用户的订单数量 (需要分组,通过用户的id)
select user.username,orders.id,count(*) from user right join orders on user.id = orders.user_id GROUP BY user.id;
mysql> select user.username,orders.id,count(*) from user right join orders on user.id = orders.user_id GROUP BY user.id;
+----------+----+----------+
| username | id | count(*) |
+----------+----+----------+
| 王五 | 3 | 2 |
| 张三 | 5 | 1 |
+----------+----+----------+
2 rows in set (0.07 sec)
这里显示名为王五(id=3)的用户有2个订单 张三(id=5)1个订单
这里是右关联查询,用右关联查询是有道理的, 因为左关联和有关联 是有差别的查询,区别:left join on 左边的表为主表 right join on 右边的表为主表
这个统计订单的查询有一个问题 就是 用户表中有用户新信息,但是这个用户没有订单信息
请看下面的查询;
---------------------
单表查询
---------------------
mysql> select * from user;
+----+----------+------------+------+----------+
| id | username | birthday | sex | address |
+----+----------+------------+------+----------+
| 1 | 王五 | 2017-11-25 | 3 | 南阳 |
| 10 | 张三 | 2014-07-10 | 1 | 北京市 |
| 16 | 张小明 | NULL | 1 | 河南郑州 |
| 22 | 陈小明 | NULL | 1 | 河南郑州 |
| 24 | 张三丰 | NULL | 1 | 河南郑州 |
| 25 | 陈小明 | NULL | 1 | 河南郑州 |
| 26 | 王五 | NULL | NULL | NULL |
| 29 | 小黑 | 2017-11-26 | NULL | NULL |
| 30 | 抖森 | 2017-11-25 | 1 | 山村 |
+----+----------+------------+------+----------+
9 rows in set (0.03 sec)
mysql> select * from orders;
+----+---------+---------+---------------------+------+
| id | user_id | number | createtime | note |
+----+---------+---------+---------------------+------+
| 3 | 1 | 1000010 | 2015-02-04 13:22:35 | NULL |
| 4 | 1 | 1000011 | 2015-02-03 13:22:41 | NULL |
| 5 | 10 | 1000012 | 2015-02-12 16:13:23 | NULL |
+----+---------+---------+---------------------+------+
3 rows in set (0.03 sec)
--------------------------
关联查询
左关联
mysql> select user.* ,orders.number from user left join orders on user.id = orders.user_id;
+----+----------+------------+------+----------+---------+
| id | username | birthday | sex | address | number |
+----+----------+------------+------+----------+---------+
| 1 | 王五 | 2017-11-25 | 3 | 南阳 | 1000010 |
| 1 | 王五 | 2017-11-25 | 3 | 南阳 | 1000011 |
| 10 | 张三 | 2014-07-10 | 1 | 北京市 | 1000012 |
| 16 | 张小明 | NULL | 1 | 河南郑州 | NULL |
| 22 | 陈小明 | NULL | 1 | 河南郑州 | NULL |
| 24 | 张三丰 | NULL | 1 | 河南郑州 | NULL |
| 25 | 陈小明 | NULL | 1 | 河南郑州 | NULL |
| 26 | 王五 | NULL | NULL | NULL | NULL |
| 29 | 小黑 | 2017-11-26 | NULL | NULL | NULL |
| 30 | 抖森 | 2017-11-25 | 1 | 山村 | NULL |
+----+----------+------------+------+----------+---------+
10 rows in set (0.04 sec)
-----------------------------
右关联
-----------------------------
mysql> select user.* ,orders.number from user right join orders on user.id = orders.user_id;
+----+----------+------------+-----+---------+---------+
| id | username | birthday | sex | address | number |
+----+----------+------------+-----+---------+---------+
| 1 | 王五 | 2017-11-25 | 3 | 南阳 | 1000010 |
| 1 | 王五 | 2017-11-25 | 3 | 南阳 | 1000011 |
| 10 | 张三 | 2014-07-10 | 1 | 北京市 | 1000012 |
+----+----------+------------+-----+---------+---------+
3 rows in set (0.05 sec)
很明显此处错误的选择left会导致查出不必要的数据,可以说是垃圾信息,因为是要查出订单信息(携带用户信息)没有订单的用户就不必要查询出来。
多级关联查询以及聚合函数的使用
没事多尝试下 最多报错, 不会损坏数据库(最多也是测试环境数据库,难道你还是在生产环境学习sql?)
多级关联和两个表关联查询没什么区别 大胆的去尝试就知道了,直接在后面加 left jion on
mysql> select * from user right join orders on user.id = orders.user_id right join orderdetail on orders.id = orderdetail.orders_id left join items on items.id = orderdetail.items_id;
+----+----------+------------+-----+---------+----+---------+---------+---------------------+------+----+-----------+----------+-----------+----+---------+--------+--------+------------------------------------------+---------------------+
| id | username | birthday | sex | address | id | user_id | number | createtime | note | id | orders_id | items_id | items_num | id | name | price | detail | pic | createtime |
+----+----------+------------+-----+---------+----+---------+---------+---------------------+------+----+-----------+----------+-----------+----+---------+--------+--------+------------------------------------------+---------------------+
| 1 | 王五 | 2017-11-25 | 3 | 南阳 | 3 | 1 | 1000010 | 2015-02-04 13:22:35 | NULL | 1 | 3 | 1 | 1 | 1 | 台式机1 | 3000.0 | | e2269631-2190-45ed-a215-b8a8dc0dc3d7.png | 2015-02-03 13:22:50 |
| 1 | 王五 | 2017-11-25 | 3 | 南阳 | 3 | 1 | 1000010 | 2015-02-04 13:22:35 | NULL | 2 | 3 | 2 | 3 | 2 | 笔记本 | 6000.0 | | fffb17d9-0280-461a-b9db-9927ffe5c1b7.png | 2015-02-09 13:22:57 |
| 1 | 王五 | 2017-11-25 | 3 | 南阳 | 4 | 1 | 1000011 | 2015-02-03 13:22:41 | NULL | 3 | 4 | 3 | 4 | 3 | 背包 | 200.0 | | NULL | 2015-02-06 13:23:02 |
| 1 | 王五 | 2017-11-25 | 3 | 南阳 | 4 | 1 | 1000011 | 2015-02-03 13:22:41 | NULL | 4 | 4 | 2 | 3 | 2 | 笔记本 | 6000.0 | | fffb17d9-0280-461a-b9db-9927ffe5c1b7.png | 2015-02-09 13:22:57 |
+----+----------+------------+-----+---------+----+---------+---------+---------------------+------+----+-----------+----------+-----------+----+---------+--------+--------+------------------------------------------+---------------------+
4 rows in set (0.08 sec)
复杂的sql
要求: 统计用户的各订单价格
(注意,这里每个订单中可能有几个订单项,比如一个订单里面有书和鞋,这里面的订单价格就是 书的价格+鞋的价格)
mysql> select user.id,user.username,user.address,orders.number,sum(items.price) from user right join orders on user.id = orders.user_id right join orderdetail on orders.id = orderdetail.orders_id left join items on items.id = orderdetail.items_id GROUP BY orders.number;
+----+----------+---------+---------+------------------+
| id | username | address | number | sum(items.price) |
+----+----------+---------+---------+------------------+
| 1 | 王五 | 南阳 | 1000010 | 9000.0 |
| 1 | 王五 | 南阳 | 1000011 | 6200.0 |
+----+----------+---------+---------+------------------+
2 rows in set (0.11 sec)
mysql>
select user.id,user.username,user.address,orders.number,items.name, items.price from user right join orders on user.id = orders.user_id right join orderdetail on orders.id = orderdetail.orders_id left join items on items.id = orderdetail.items_id;
+----+----------+---------+---------+---------+--------+
| id | username | address | number | name | price |
+----+----------+---------+---------+---------+--------+
| 1 | 王五 | 南阳 | 1000010 | 台式机1 | 3000.0 |
| 1 | 王五 | 南阳 | 1000010 | 笔记本 | 6000.0 |
| 1 | 王五 | 南阳 | 1000011 | 背包 | 200.0 |
| 1 | 王五 | 南阳 | 1000011 | 笔记本 | 6000.0 |
+----+----------+---------+---------+---------+--------+
4 rows in set (0.08 sec)
第一个查询统计价格用到了 sum() 函数 用来累加求和 属于聚合函数
数据聚合函数的还有 avg()求平均值 max()最大值 min()最小值 count()统计数据条数。
数据库查询数据还可以选择查询哪一部分的数据。
SQL 语句:
select*from 表名 order by 字段 (倒序/升序) limit start,count;
查询数据库中学生表逆序的 5 条数据:
select*from student order by id desc limit 0,5;
order by id: 通过id来查询
desc: 表示倒序,可替换成 asc ,表示升序
start: 开始(升序第一条是0,降序最后一条是0)
count: 查询的个数
mysql> select*from student;
+----+--------+------+
| id | name | age |
+----+--------+------+
| 16 | gege | 50 |
| 18 | didi | 23 |
| 19 | gege | 23 |
| 20 | yeye | 23 |
| 21 | jiujiu | 101 |
| 25 | gege | 13 |
| 29 | kaka | 2 |
| 32 | haha | 20 |
| 33 | ghg | 20 |
| 34 | dijia | 20 |
| 38 | daina | 20 |
+----+--------+------+
11 rows in set (0.00 sec)
mysql> select*from student order by id desc limit 0,5;
+----+-------+------+
| id | name | age |
+----+-------+------+
| 38 | daina | 20 |
| 34 | dijia | 20 |
| 33 | ghg | 20 |
| 32 | haha | 20 |
| 29 | kaka | 2 |
+----+-------+------+
5 rows in set (0.00 sec)
MySQL 分页查询
后端在写 mysql 语句来处理前端的分页查询请求其实非常简单,用 limit 就行。
例:
select * from foo limit 100,200
100 是指偏移,200 是指查询条数。
所以后端代码需要接收两个参数:偏移(offset),查询条数(rows)。
前端传递给后端的参数可以是page_num(第几页),page_size(每一页显示多少条数据),后端接收到 page_num 和 page_size 之后可以通过 (page_num - 1) * page_size 表达式换算 offset,查询条数(rows)就是 page_size
整个后端代码:
def paging(page_count, page_size):
table_name = 'Test'
offset = (page_count - 1) * page_size
sql = 'select * from %s limit %d, %d'%(table_name, offset, page_size)
print(sql)
conn.execute(sql)
conn.commit()