MySQL管理之SQL语句实例

程序员文章站 2022-04-05 10:49:10

...

MySQL 管理之SQL语句实例首先，我们来导入world库，这个world库中的表是mysql ocp考试专用表，在网上有下 mysql source/root/world_innodb.sql 表结构如下：本文来自http://yijiu.blog.51cto.com 转载请经博主允许 ,盗帖可耻！ CREATETABLE`Country`( `Cod

MySQL管理之SQL语句实例

首先，我们来导入world库，这个world库中的表是mysql ocp考试专用表，在网上有下

mysql> source/root/world_innodb.sql

表结构如下：本文来自http://yijiu.blog.51cto.com 转载请经博主允许 ,盗帖可耻！

CREATE TABLE `Country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
`LifeExpectancy` float(3,1) DEFAULT NULL,
`GNP` float(10,2) DEFAULT NULL,
`GNPOld` float(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

使用show table status查看表状态如下所示：

mysql> show tablestatus;

+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+

| City | InnoDB | 10 | Compact | 4321 | 94 | 409600 | 0 | 131072 | 0 | 4080 | 2014-10-02 15:35:18 |NULL | NULL | latin1_swedish_ci | NULL | | |

| Country | InnoDB | 10 | Compact | 241 | 407 | 98304 | 0 | 0 | 0 | NULL | 2014-10-02 15:35:18 |NULL | NULL | latin1_swedish_ci | NULL | | |

| CountryLanguage |InnoDB | 10 | Compact | 856 | 114 | 98304 | 0 | 65536 | 0 | NULL | 2014-10-02 15:35:18 |NULL | NULL | latin1_swedish_ci | NULL | | |

3 rows in set (0.00sec)

mysql> select *from Country where Name="China"\G

***************************1. row ***************************

Code: CHN

Name: China

Continent: Asia

Region: Eastern Asia

SurfaceArea: 9572900.00

IndepYear: -1523

Population: 1277558000

LifeExpectancy: 71.4

GNP: 982268.00

GNPOld: 917719.00

LocalName: Zhongquo

GovernmentForm:People'sRepublic

HeadOfState: Jiang *

Capital: 1891

Code2: CN

1 row in set (0.00sec)

本文来自http://yijiu.blog.51cto.com 转载请经博主允许 ,盗帖可耻！

由此可以看到此表与City表关联，如下所示

因为city表存在城市ID编号，也就是刚才与Capital= 1891 相关的信息

mysql> select *from City where id = 1891;

+------+--------+-------------+----------+------------+

+------+--------+-------------+----------+------------+

| 1891 | Peking | CHN | Peking | 7472000 |

+------+--------+-------------+----------+------------+

1 row in set (0.00sec)

而CountryLanguage表里是将CountryCode做关联

看到编号为CHN，那查一下关于CHN相关的信息

可看到CountryLanguage用到以CHN作为关联可以查到相关城市

mysql> select *from CountryLanguage where CountryCode = 'CHN';

+-------------+-----------+------------+------------+

+-------------+-----------+------------+------------+

| CHN | Chinese | T | 92.0 |

| CHN | Dong | F | 0.2 |

| CHN | Hui | F | 0.8 |

| CHN | Mantu | F | 0.9 |

| CHN | Miao |F | 0.7 |

| CHN | *n | F | 0.4 |

| CHN | Puyi | F | 0.2 |

| CHN | * | F | 0.4 |

| CHN | Tujia | F | 0.5 |

| CHN | Uighur | F | 0.6 |

| CHN | Yi | F | 0.6 |

| CHN | Zhuang | F | 1.4 |

+-------------+-----------+------------+------------+

12 rows in set (0.00sec)

接下来就进入主题

查询语句初识本文来自http://yijiu.blog.51cto.com 转载请经博主允许 ,盗帖可耻！

来个例子先，查找City表中的前10行

mysql> select Id,Name, Population From City limit 10;

+----+----------------+------------+

| Id | Name | Population |

+----+----------------+------------+

| 1 | Kabul | 1780000 |

| 2 | Qandahar | 237500 |

| 3 | Herat | 186800 |

| 4 | Mazar-e-Sharif | 127800 |

| 5 | Amsterdam | 731200 |

| 6 | Rotterdam | 593321 |

| 7 | Haag | 440900 |

| 8 | Utrecht | 234323 |

| 9 | Eindhoven | 201843 |

| 10 | Tilburg | 193238 |

+----+----------------+------------+

10 rows in set (0.00sec)

查找从第10行到20行,中间相差10行

limit 10,10;意思为从第几行开始并从这行开始向下显示多少行

mysql> select Id,Name, Population From City limit 10,10;

+----+-------------------+------------+

| Id | Name | Population |

+----+-------------------+------------+

| 11 | Groningen | 172701 |

| 12 | Breda | 160398 |

| 13 | Apeldoorn | 153491 |

| 14 | Nijmegen | 152463 |

| 15 | Enschede | 149544 |

| 16 | Haarlem | 148772 |

| 17 | Almere | 142465 |

| 18 | Arnhem | 138020 |

| 19 | Zaanstad | 135621 |

| 20 |s-Hertogenbosch | 129170 |

+----+-------------------+------------+

10 rows in set (0.00sec)

错误的sql：如下所示

select * from tb where xxxx limit 537793977, 20;

如果出现这样的sql，意味着先要扫描表里面的537793977行后再取20行返回，这样成本就会很高

LIMIT的一个原则：

在生产环境中使用LIMIT后只跟一个数，而且最好不大于500，如果是连续的，包括上面的sql，利用上面的SQL得到一个ID的最大值，那么这时候我们就会用到份页

如下所示：

优化前：

mysql> select Id,Name, Population From City limit 10;

+----+----------------+------------+

| Id | Name | Population |

+----+----------------+------------+

| 1 | Kabul | 1780000 |

| 2 | Qandahar | 237500 |

| 3 | Herat | 186800 |

| 4 | Mazar-e-Sharif | 127800 |

| 5 | Amsterdam | 731200 |

| 6 | Rotterdam | 593321 |

| 7 | Haag | 440900 |

| 8 | Utrecht | 234323 |

| 9 | Eindhoven | 201843 |

| 10 | Tilburg | 193238 |

+----+----------------+------------+

10 rows in set (0.00sec)

优化后如下：

使用last_max_id通过程序进行计算得到的

语法：

mysql>select Id, Name, Population From City where id > limit 10;

mysql> select Id,Name, Population From City where id >10 limit 10;

+----+-------------------+------------+

| Id | Name | Population |

+----+-------------------+------------+

| 11 | Groningen | 172701 |

| 12 | Breda | 160398 |

| 13 | Apeldoorn | 153491 |

| 14 | Nijmegen | 152463 |

| 15 | Enschede | 149544 |

| 16 | Haarlem | 148772 |

| 17 | Almere | 142465 |

| 18 | Arnhem | 138020 |

| 19 | Zaanstad | 135621 |

| 20 |s-Hertogenbosch | 129170 |

+----+-------------------+------------+

10 rows in set (0.00sec)

本文来自http://yijiu.blog.51cto.com 转载请经博主允许 ,盗帖可耻！

参数解释：

where id> 10 limit 10 就是取前10行到第20行

如果取前10的话，将数据放进去，得到的就是last_max_id然后传到下一页，进行对比

这就是所谓的分页

#这并不是完全在sql里实现的，如果想特别精准的分页，这种访问有可能有问题的。

#跳页的话有种方法：一般粗略的估计一下即可，不会让其非常精准的显示出来，只求速度足够快

比如跳页，如果对于整个系统进行搜索的话，非查不可的情况，我们可以使用专属的搜索系统进行查看，互联网领域中能不进行写则不写，以提高速度

count, max(), min()使用

count

count(*)在早版本会走主键的，最新版本会走普通索引

mysql> selectcount(*), count(id) from City;

+----------+-----------+

| count(*) | count(id)|

+----------+-----------+

| 4079 | 4079 |

+----------+-----------+

1 row in set (0.00sec)

那么问题来了：主建为何没有第二索引快？

因为Innodb主建就表本身里的数据，如果count主建，需要将整个表扫描一遍，这样建带数据读的块更大

走主建相当于把整个表都要读（全表扫描）本文来自http://yijiu.blog.51cto.com 转载请经博主允许 ,盗帖可耻！

而在Innodb里是索引列+主建值存储的结构体系，这么做的话会更快

如果直接使用Secondary index的话会快一点

如今mysql对count(*)做了优化，默认会走Secondary index，所以在以后计算总数的时候，不要总计算列数，直接写count(*)就可以了

列出人数最多的城市

我们现在有需求，我们知道City表中有城市的总人数，我们现在想统计一下最多人数的城市

那么我们先来看一下表结构本文来自http://yijiu.blog.51cto.com 转载请经博主允许 ,盗帖可耻！

mysql> desc City ;

+-------------+----------+------+-----+---------+----------------+

+-------------+----------+------+-----+---------+----------------+

| ID | int(11) | NO | PRI | NULL | auto_increment |

| CountryCode |char(3) | NO | MUL | | |

| Population | int(11) | NO | | 0 | |

+-------------+----------+------+-----+---------+----------------+

5 rows in set (0.00sec)

由此我们可知条件，Population为每个城市的总人数，我们只要筛出Population最大的值即可，如下所示：

mysql> select * from City where Population = (selectmax(Population) from City);

+------+-----------------+-------------+-------------+------------+

+------+-----------------+-------------+-------------+------------+

| 1024 | Mumbai(Bombay) | IND | Maharashtra| 10500000 |

+------+-----------------+-------------+-------------+------------+

1 row in set (0.00sec)

括号中内的内容为子句查询；

而select max(Population) from City 表示查找这个表中人数最多的行

先来看一个例子，执行上面的sql子句：

mysql> selectmax(Population) from City;

+-----------------+

| max(Population) |

+-----------------+

| 10500000 |

+-----------------+

1 row in set (0.00sec)

max是内置函数，表示将取最大数值的行

那么将其封装到子句里面，再进行对比，即 Population= 最大的字段

找到城市人数最少的城市

mysql> select *from City where Population = (select min(Population) from City);

+------+-----------+-------------+----------+------------+

+------+-----------+-------------+----------+------------+

| 2912 | Adamstown |PCN | – | 42 |

+------+-----------+-------------+----------+------------+

1 row in set (0.01sec)

使用oder by进行排序

mysql> select *from City order by Population desc limit1;

+------+-----------------+-------------+-------------+------------+

+------+-----------------+-------------+-------------+------------+

| 1024 | Mumbai(Bombay) | IND | Maharashtra| 10500000 |

+------+-----------------+-------------+-------------+------------+

1 row in set (0.00sec)

本文来自http://yijiu.blog.51cto.com 转载请经博主允许 ,盗帖可耻！

查找大于人员数大于100W的城市有有哪些

使用count(*) 统计出总数

mysql> selectcount(*) from City where Population > 1000000;

+----------+

| count(*) |

+----------+

| 237 |

+----------+

1 row in set (0.00sec)

可看到一共有237个城市

那么再来查找人口大于100W的城市是否一共有237个

mysql> select *from City where Population >1000000;

#拉到最后可看到如下的数值

+------+--------------------------+-------------+----------------------+------------+

237 rows in set (0.00sec)

我们还可以使用函数，如果什么参数都没有加的情况下就使用以下函数，会得到上一个sql的执行所得到的行数

mysql> selectfound_rows();

+--------------+

| found_rows() |

+--------------+

| 237 |

+--------------+

1 row in set (0.00sec)

本文来自http://yijiu.blog.51cto.com 转载请经博主允许 ,盗帖可耻！

那么使用count(*)并执行函数来查看效果又会是什么样

mysql> selectcount(*) from City where Population > 1000000;

+----------+

| count(*) |

+----------+

| 237 |

+----------+

1 row in set (0.00sec)

mysql> selectfound_rows();

+--------------+

| found_rows() |

+--------------+

| 1 |

+--------------+

1 row in set (0.00sec)

FOUND_ROWS函数

比如取前10行，但表中总共有多少行是未知的，这里在比其他数据中多了一个函数:

SQL_CALC_FOUND_ROWS

先来看一下yw表有多少行

mysql> selectcount(*) from yw;

+----------+

| count(*) |

+----------+

| 6000000 |

+----------+

1 row in set (15.64sec)

再执行打印前10行内容

mysql> select SQL_CALC_FOUND_ROWS * fromCity limit 10;

ERROR 1146 (42S02):Table 'test1.City' doesn't exist

mysql> select SQL_CALC_FOUND_ROWS* from yw limit 10;

+----+---------+---------+---------+---------+---------------------+------------------------------------------------------+

| id | c1 | c2 | c3 | c4 | c5 | c6 |

+----+---------+---------+---------+---------+---------------------+------------------------------------------------------+

| 1 | 463681 | 1098981 | 1817518 | 2222359 | 2014-09-24 15:38:29 |wubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubx |

| 2 | 2333997 | 269341 | 2459005 | 915557 |2014-09-24 15:38:29 | wubxwubxwubx |

| 3 | 2971523 | 1226698 | 842469 | 414525 | 2014-09-24 15:38:29 | wubxwubxwubxwubxwubxwubxwubxwubxwubx |

| 4 | 2835700 | 930937 | 2835332 | 1945110 | 2014-09-24 15:38:29 | wubx |

| 5 | 1578655 | 1044887 | 2649255 | 2307696 |2014-09-24 15:38:29 | wubxwubxwubxwubxwubxwubxwubx |

| 6 | 1442242 | 992011 | 1740281 | 190626 |2014-09-24 15:38:29 | wubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubxwubx |

| 7 | 693798 | 309586 |&nb

声明：本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn核实处理。

相关标签： MySQL 管理 SQL 语句实例 MySQL 管理 SQ

上一篇： php怎么判断指定值是不是数组键名

下一篇： mysql null注意事项

MySQL管理之SQL语句实例

Mysql中SQL语句不使用索引的情况

PHP处理SQL脚本文件导入到MySQL的代码实例

MySQL中间件之ProxySQL(3)：Admin管理接口

mysql的sql语句特殊处理语句总结(必看)

PHP优化之批量操作MySQL实例分析

Shell脚本中执行sql语句操作mysql的5种方法

【呕心总结】python如何与mysql实现交互及常用sql语句

详解JSP中的语句对象Statement操作MySQL的使用实例

mysql sql语句隐藏手机号码中间四位的方法

sql语句like多个条件的写法实例