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;
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format |Rows | Avg_row_length | Data_length | Max_data_length | Index_length |Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment|
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| 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 Zemin
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;
+------+--------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------+-------------+----------+------------+
| 1891 | Peking | CHN | Peking | 7472000 |
+------+--------+-------------+----------+------------+
1 row in set (0.00sec)
而CountryLanguage表里是将CountryCode做关联
看到编号为CHN，那查一下关于CHN相关的信息
可看到CountryLanguage用到以CHN作为关联可以查到相关城市
mysql> select *from CountryLanguage where CountryCode = 'CHN';
+-------------+-----------+------------+------------+
| CountryCode |Language | IsOfficial | Percentage |
+-------------+-----------+------------+------------+
| 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 | Mongolian | F | 0.4 |
| CHN | Puyi | F | 0.2 |
| CHN | Tibetan | 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 >
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 ;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode |char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00sec)
由此我们可知条件，Population为每个城市的总人数，我们只要筛出Population最大的值即可，如下所示：
mysql> select * from City where Population = (selectmax(Population) from City);
+------+-----------------+-------------+-------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------------+-------------+-------------+------------+
| 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);
+------+-----------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 2912 | Adamstown |PCN | – | 42 |
+------+-----------+-------------+----------+------------+
1 row in set (0.01sec)
使用oder by进行排序
mysql> select *from City order by Population desc limit1;
+------+-----------------+-------------+-------------+------------+
| ID | Name | CountryCode | District | Population |
+------+-----------------+-------------+-------------+------------+
| 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
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
