MySQL管理之SQL语句实例

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 > 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 ;

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

| 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