3.4 Getting Information About Databases and Tables
获取数据库和表格的信息
如果你忘记了数据库或者表格的名字怎么办?或者给定的表格的结构怎么办?(例如你知道都有哪些列,叫什么名字吗)
mysql 有几种方法能解决这个问题
从前文你已经知道,可以通过Show databases;语句查看数据库,通过 Database()函数能知道当前选择的数据库
mysql> SELECT DATABASE();
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAOEAAAB3CAIAAAB67rBzAAAIBklEQVR4nO2dy5mrOBCFSQrSwavJBFhNIEAqmF1nMFv8OYRZYINeJSS5ZJfN+b/e3G5Zj9KhJAE+t/j7+/vn3/8KAMQCjQLpvE2jZTPflvttud/6yxua+3i7mana6T7Ub2mq7K5TV2pNz21Jl2fnzXm0bOaXtVK10/0hu+V+bap3tVvUfWhzRdldl72TgU1H1P8+jV6GxWqoHm/L+J4LpCgkaLQeb9pl6qdqp/CJ9LYbT6RGt1ms2ukeMkaBGqW6xBLPUB4arcdb3635aagvw3K/LXNbrrFWErv2Ty2fqfGqeyWF6HPjHFvd34OvS9dlbbdrCYJu11W+Ho08vW8YAlOjplEzjEp8HmX89dvlnxrdpkBbfF3li6D5MuJgjELjMiz2ih96Ncaxa3S5D/Xa3bktt8tUy1vqTFNXUt0r8bVyJPWpsplvjjE7WANqy1RtV+sD0S5Zvh6pnqTm0UINY9nMW+frXptRZ/1E+eqZULY+73J31h8yX0bc/MnS1dvcGp26cu+WIs1dZ9r6UjazK/npl1ewRh+F6Rxpl9STjd6ulQCsdsnyHiGyaNQcyJFGifLGWu/Malr9QfNlbVE8/SmbmV+OTo41ug3DPN+5jszG3ERpdD1nRAz7MmzljTOKtfyZ7ZLlffs8Ho0aTR9q1F2e1ihdPzFfVNyOtrxxB4kXCNDoI3ZlMxMzVLXTc9j63NiX2utrvf6xbZ9HJBKyXaq8L3mka3Tvp17/cR6lylMa9dWv1vmMhiduAXn0PcemEI2uIb5O5LFG6a6+SeA/M1ntPuu396BED5VGyavF3ZmI1U3T6GVYVE1s2lLWAbJ+qrymUWUsvvq1gdB7d2cxG3I/yn5bKkij2pWn9sZ1HtyWj6kr9evefW6NWjLohcxsQrlmiPOys3xR6KdsfRqUjxyf692HaOWmwdzW9u1xV3wc5cnOB5U/jts2iqhzPXmifYmw+6Opd+PetmUBeYi9P+pZhdIJ0Wj6CQ4a/XqCnzM9lo4Mz5/8Gn2ueqkNQ6M/wNme1wMQDTQKpAONAulAo0A60CiQDjQKpGNoNOY1W7CDuGUEGmUBccsINMoC4pYR9T184+fxBEx7PcJ43+z0v/fEDXCBPMoC4pYRaJQFxC0j0CgLiFtGcH8USAcaBdKBRsEXAI0C0SCPAukYGqW/RA98IG55EanRL/PXFBO3XyQxj8JfUwcazcgLGoW/plYeGs0Fh0bP469JxwMazQqDRs/ir0kCjWaEJ4+e3l8TGs0Iv0ZP6a8JjWaEez96Un9NaDQjHBo9j78mCTSaEZb7o+fx1yTjAY3m4+TPQnF/9As4uUbxnOkLOL1G8bxePHg3D0gHGgXSgUaBdKBRIB1oFEgHGgXSgU8JC4hbRqBRFhC3jECjLCBuGYH/KPxHpYM8ygLilhFolAXELSPQKAuIW0ZwfxRIBxoF0oFGgXSgUSAdaBRIB98VYQFxy8g5NBrq7JfMj8ZNBtAoCz8aNxmcQ6PZQdwykqjRerz13dN+8TLoFqEuv8/VG2ctmeInqr6r4fQTXXu+2ZJto3AYldntvpxiodGMpGt0uQ/1Os1zW+72TITf5yq4hzRVFxDKT9T47NYryk/0ocX147pnr9Giv540oNGMpGt06sp97jcZUX6fug8eZZtI+OYpCiP9RHXDM9OtydKox5c0BWg0I8waJf0+aY0SfqLuPEr7ifodmsy/+nxJU4BGM5I3j+5QGiX9RJVNqroQk3Z2cRqNtMU7BBrNCLdGqb0dpVHKT/QyEIsvtXeM1CjDHtSoHxrNBbtGCb9Peq0n/ErN/4pk15PbT5TQqHlz4LCeNKDRjAi9P2oezF0OjJKQErefRKhG6/Fmnrs5t4/sSInbTyJUo+ZaL1qghaS4/SB4Nw9IBxoF0oFGgXSgUSAdaBRIBxoF0oFPCQuIW0agURYQt4xAoywgbhmB/yj8R6WDPMoC4pYRaJQFxC0j0CgLiFtGcH8USAcaBdKBRoF0oFEgHWgUSAcaBdKRrdHsnoyclPsXr3+Cqp1kfI1M1+j6lfOXnI9Y+YxGk252Or5dHevLR5Vn9PeLox5FiGHX6Pp14UZGtz5KgkbtjygegFU7HXtMUOVj6+HF7/7yJp4ardpprIuIS4fy+9T9PxRTO8vKxq8DwjfU52NKoPmRqKnOzk+mOUqgf4nDds/25fN3lSofW0/ceJ9/2N+MsSaF2RgrBXM/GqNRl9+n5jG2+y1aZo51H/R+kCVu0sc0uIa9A5T/aGwedTShhvH5wpRvvFT52HoSxmvYbbg++OHHvC9p1Pb7NDOK8vunP9S1qUz7J38rjjyq9PZoi1Y2s2tEPv/RyFnRrf/Ujj1UpXkIu6HKx9YTP96gBe2zZ8GXNGpfr6ZotpiuPnhV249D35XBZ8bXNVo4tw1e/9EEjZq6eayeW50BGnWWj60nerwBFYYFOSPMGqXy6KrRshuaqu7ntiStG49aSdGo+lm3H7QOQx4tu6u6KB/uI6nysfVYHTsar6vzRtc+fmxi1qjtXb+fSeehn9fccO3HwFuJrBrVavP4j8aubs5QEN7+K+uZT4szVd5bT0THfL6t3hkXtB/VfJOtCNrQl9d26NbOj3W/1XkZjs/LlG9orEb1eqx9CHF+V/6UeK73V+LQKF0+qjMp41XvZnzDuR4k8flkk4fPL/QFNMqGdBffFMQ9ZwIvguf1mYBGgXSgUSAdaBRIBxoF0oFGgXSgUSAdaBRIBxoFwvkfBbYo76M5bDMAAAAASUVORK5CYII=)
如果你什么数据库也没选择,这条语句执行的结果是Null,如下图
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAANUAAAB0CAIAAACdYuCfAAAHZ0lEQVR4nO2dybmjOhBGSUqkI686E/DqBgKkgonibvHnEHrBpJJUIJWwZV//5+vN65Y1FEcTg17x7+e3ACAX8A/kBP6BnMA/kJPX+Keq4T4+7uPj3lxeUFz2cp9MWfePVr+kKHW99VdFih5qxaeP5ZXjn6qGZA/Kun/MSo2PW1W+qtxCN6HFFep6G7dKBhYdkf/r/Lu0o1OQ7u5jd1rhmf3T3Z10r33Kug+/SLvlxhPp33qFyrp/hLTxDf3jqnRKPGf+/fwWurs312lcafWlHR/3cajVFEdjsCX/ScYhMxa6Mbo+jbu33rp5BPcnX3d0y3UuNl+uL73urPF1m8QDhzTinx1GIz5zmv383fSLf+slIBOiL30RdL2sOFitIFza0Z2FQ3saYfZvfLR6qspQq7V7kfHGvIpcD9CNETtnbON+parh7mmPhylYroJmuaQOTLlset1xNZGOf4UZRlUNa+V1Q66WN38mfbkMFmudN5W9+YdcLytu+4Ocr7Yp/vVXtRVpaLc5RMZ8VQ2+QYt2i2D/5sT82OampIMELdfpuE65bPodyU7xz27IkX9Memv+9Y5GJP+g6+UsG3bqo6ohWjUvB/6tVbT3Qb6tpRX3KP+mNXtEky7tmt5a7ztTkl0um35vXXWOf1bRh/750/P+8fkz14uL29ESM27hznPk3xwXVQ1M9Mu6X5pE4+52kfT5l/5sXVcxAwBbLpd+r9PL/dvqSfM/Hv+49Jx/e/mbeS7R2IlbwPh3yhbk0L8pfLee3SIYVaET9/n7D6fcJX93zcfU0CiU7Qn+ykTMOMS/Szua13v1xhi/2fy59MQ/oy17+ZOG8GtlbzIXdv0XeymP/SM9xizJt29ah/T+qmh/9e/vooZxfnKxizD6A7Ov9KYvCrobpSE2fnK8//VvNo3N9VBr99auLz6e9Gzlg9Ifx21tRdT+l90d7hFw/096t+msJQLIROz9v53Zg+XQP/lOB/59PMHPP+YhP34dtePfMhNJH7bAvz/AX3r+C4AN/AM5gX8gJ/AP5AT+gZzAP5AT07+YVyDBBuImB/6lg7jJgX/pIG5y1vefrT/zUxfyKN16p+jr/34nbiAQjH/pIG5y4F86iJsc+JcO4iYH9/9ATuAfyAn8AzmBfyAn8A/kxPSP/8gX7IG4yXlL/z7sfLu3idsHIvEP59tR4J8cqX84346kh39Ckv37nvPt+HjAPzEn+Pct59uxwD855/pH+Jrz7eCfnJP9+8rz7eCfnFPXf196vh38k5Ps3/ecb8cC/+Sk3//7nvPt2HjAPzFv+fzjReD+X36+2T88/8jPd/uH57+5wftXICfwD+QE/oGcwD+QE/gHcgL/QE5w/kE6iJsc+JcO4iYH/qWDuMnB+X84/y8nGP/SQdzkwL90EDc58C8dxE0O7v+BnMA/kBP4B3IC/0BO4B/Iybe/f38GiJsc+JcO4iZH/v3v9oXi8rWs/UWt8RUtd5xFzDEXbwv8kyP1b7ht34nBP/gnRX7+hu6WxPAP/klJOP/l0s62wT/4JyXl/KElPfyDf1LSzr+6tP1VwT/4Jybx/DXdPFoN/+CflNTz/9T11nSLZ5d23N5EIgf+wT/g5Zz//8Jo3ovxncNnvapuesmcp/tBwD85eP6RDuImB/6lg7jJgX/pIG5y8P4VyAn8AzmBfyAn8A/kBP6BnMA/kBOcf5AO4iYH/qWDuMmBf+kgbnJw/h/O/8sJxr90EDc58C8dxE0O/EsHcZOD+38gJ/AP5AT+gZzAP5AT+AdyAv9ATt7Yv7LuP+eLTHW9fUpVgyjr7XyzZ2L4N33J273NA6Q8/olu5l1a58mbbuI+aubSx+ZzGrp7hQyzf7q7j0NdvaTI90bgn/sT3ay6lHVvntXJ5uBNH5vPuahqeHqJ/35+i6Ks+04XEcpPXxyqapi6phH9aRAlz+ndZhxe4zVn+sOy7qfjZqb8QyYIsz5kiHLHla3Q9U9I9O1Tbwpy8M2cYL+qXPrYfOLau/zD9haFc1FoBZ4BWf/F+Le1ZItLWfe+81+cQ150E/SeiCMuOesjpHdyaXRDxxgjTez45ynCDOPy4sxee7n0sfkI2jvNe7xhT3+0KPdvU+ro/Ktlbb58pz0NY2GleMY/o7ZHSyJVDb4WuePKliYy4qTLkYrNxgy1Omovlz42n/j2Bk1ET113yv1z+5ktxBqv6aTUsm66trmq4L1Vun+FdyonL/PZU7nAP9uJeUZb8wzwz5s+Np/o9gZk+OwDys70jz3/79KOnVbXtip1M9RqPbg3thSJf+Zvl9z2ljUnjH/qejMnysN1G5c+Nh+nYkft9VXeqtqztyBn+kfbQ/duQ9sMU5++NV3grbJT/SO5WWs+O5k02xWj7W5Z0/6JxJlLv5tPRMW43zLzNanYK9Z/2+bIffXcB98t/Of/6WbN89Ie7yvJJs6YSmL9o/k4awNmn2v8k3D/u5+Jxz8+fVRlJO01d/25979AxF99//Rl9/9AIp7nHx/PS59/gETw/FcG/AM5gX8gJ/APZOQ/wKeli3+VYSoAAAAASUVORK5CYII=)
通过下面的语句,可以查看当前数据库有哪些列表
mysql> SHOW TABLES;
请注意,是Tables,而不是Tables(),没有这个函数
如果想查看数据库的结果,那么就可以用Describe语句,它会将数据库的每一列都展示出来
mysql> DESCRIBE pet;
注意,它展示的是结构,数据类型等信息,并不是表的完整信息
Field展示列的名字,Type是数据类型。NULL说明这一列可以插入Null值,key说明这一列是否被索引,default说明它的默认值,最后Extra说明特殊信息。如果某一列被 设成AUTO_INCRMENT,它的值会自动递增
如果某一列被索引了,那么可以通过Show INdex From Tbl_name查看相应信息。
3.5 Using mysql in Batch Mode
用mysql批处理模式。
在前面的章节中,你通过mysql的交互模式输入语句,并查看结果。事实上,你可以使用mysql的批处理模式。
要做到这点,你需要将要运行的语句写到文件中,然后告诉Mysql去读取,并运行它。
shell> mysql < batch-file
如果你的系统是window,而批处理文件里面包含一些特殊字符,那么,恭喜你,可能会发生一些错误,你可以这样做
C:\> mysql -e "source batch-file
"
如果你需要通过命令行指定连接的参数,那么命令可以是这样的
shell>mysql -h
Enter password:host
-uuser
-p <batch-file
********
如果你这样做,最好写一个脚本,然后执行这个脚本。
如果你想即使执行批处理文件发生了一些错误也要继续执行,那么你应该使用--force命令行参数(霸王硬上弓模式)
为什么要写脚本呢?
1.例如,有些语句,每天或者每周都要运行一次,写成脚本就能避免一次又一次的重复造轮子。
2.你可以从已经写好的脚本相似中复制,然后修改下就行了
3.批处理模式同样适用于你在开发多行语句时,如果你不小心弄错了,你不用重新输入,只需要修改正确,然后让mysql再次执行就可以 了
4.如果你的语句执行结果有茫茫多,你可以一页一页的输出,而不是看着它在你的屏幕上翻滚。
shell>mysql <
batch-file
| more
5.你也可以将结果输出到文件,以做进一步的处理
shell> mysql < batch-file
> mysql.out
6.你可以将你的脚本展示给你的小伙伴,让它们也可以用它。
如果你说,我要用批处理模式,但是我也要交互模式的那种结果,那么你就应该使用:mysql -t.如果你想回显执行结果:mysql -V
7.还有一些情况不能使用交互模式,如果在运行一个计划任务时,这种情况下,你必须使用批处理模式
当然,交互模式与批处理模式输出的结果形式可能有些不同
批处理的如下图
下面两种形式都会执行文件
mysql> source filename
;
mysql> \. filename
3.6 Examples of Common Queries
常见查询实例
这里以shop表 为例(本例使用test数据库)
首先进入数据库
shell> mysql your-database-name
创建shop表:
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAhcAAABuCAIAAAAbCaY/AAASc0lEQVR4nO2dS3rqOBCFtSmyHbKZwAKyCmCeVQCD7CP36yX0wNjWo05JJQtj4/N/PehLjFyWjY71quN+f76d+zhc//v35/13+nTO6Z+f9+7B/vLvetw9/hF+5XE8Ijj49vWhHkwIIWR5/P58vzoEQgghq4UqQgghpB6qCCGEkHqoIoQQQuqhihBCCKmHKkIIIaSeeVRk93UvW/u7TcKV0x5Prjd43np2x9u47JsQsgHm7Ivsvu4NWsP95albTPanycXujre/cJ/N3/2gtayZ1rxNvdnPa+fz/NdalgghC+fFKhLsWCwrwWuRd18XtXWuoYGKmIt6ExVpWHWEkNXw+/Pt9pd/p2O3jfy8/zwP7867481/iQ7+GWw791ui/cl7Bw8VQmwN96f//v1dypoy+Kq7+7rfvj6G8R+/LRvjCYPx4nycfRw+kvbeo3IUxFY1PW/fmg9VGndccL2VxyPeL+W8/vFRnN0TksS5O95K7yMh5I14qMjff+d91zDdD7vhFfXjcB3bQb8tQ2/H+5PX8ib9DPStqIcBwe3UQwC603lq58fj///u6z6o0f4UNMSw6ZfK0UmLAuf96CW8+8MlktW03qzxgJoPzusdk9x3KU7vc+UUhJB356Ei1+NubAi8RmRUgmD0Y/d1lzoQn2dfDIpV5HFwdkg9aV6DksdzDWGE8SARCuOUVKSsnLRgfYQH1G18OqHezPGA+wXyocUFDqdT4gyEhxCyITIqMrQUydobYflQ1PqYVKSbkdYHZ3QVSUtWZrmjP+kqYp4tLy6qTkWq4pGWewEViW9c0DdVVITz6oRskZyKPJrCbuJBKuDjcO0bplBFohEPN31ES50XkUqOm2M/5vFyrH2RYpKi0Hmn9UVsePdrYl8kOIx9EUK2SlZFummG2xWOmcjj6d163Maz68PMzVDgY41W0TzNiK9Gn+cwzlT8cDn5aMOGFZ03aJ3Tc+nzIlai+yVl+I9fI0ri5LwIIRslryLB26v3ibgQaxhsuR53Yaskr30yrvR14UoqceY/BPidjJtO7od9NFjnfSUc/Cn2TelPknZr5POiwnG9TfBxicxggE/MsBArPh6elGu0CNkmBftFaoe87QpBlk3mSeB+EUK2SFZFxBGeIqgi70b2fYJ71wnZHoqK9GNHtcMUVJF3o6BXyjxahGwN5vQlhBBSD1WEEEJIPVQRQggh9WxYRT4O19LUik19PgznzVM6D/FxuFZvVJxI0+tVWM48nJdcIF4xn00AoX5eRHk9yHEaWO7vYgXnfSPeXkXw8lPz09NoY53tvOryWbAmSkwng/PHPJmtqQhaqAbrQcvp8EQVabagDu0mtiqTuJc2n5N72ufovK0w1EP3sx0j8bJPybkk8L2e+bfwYhV5/tU23MTwku3ZSvzgTx+H63/nk1Cxb769fCEqYt99+RoVabdL9HkqEue99vNYt/gcnbchRhW538YxA6pIMaYMKM5/pwhzdqX+IopfiNYTx16K030+wHll3w7d7wS1Ao/9PfJjVJiAS8uMAvxFZL8ZXM/xtnwv26PsX1LgE5O9BeJJtfKxb43ov9J9R7gv+shPExVB9QCfT/D8YP+b4YhMltXMc2sgzV2NcnXXfW6l8Pkv8isS6O7I/iJkwqWKZCnOxgh9QZC/iFPfBWTvRRzJdJ8PUE73CN7FnGAofpzG+H7Ywceo7M1IUxExTr29U+vNT7cDy0f1rPvZaOwvsQUZ8KGRnivZf0VoNXI+MXWfC1cD6kG8LiVOxf+mXEWGr08dA/DHYPspnPO+3edm0POJ/HicM/dFLnv3eX5ESxWxUuIvkn7FVxHBX8Q5o4rot3y6zwcoR/uVKsNWoptWYgyTnDr/eOl9ESFO4F/inRRfb0F2SFTPGT8bzPBDHf4p30f5uVJ+1U57DJ6mIqge1MCyj2vBfVfibKMi1+Pu0e577nmtPjdjzYFtrIe+nD6BOlUkIrDEkJ7dEn+RuJywLwJ+bxYVMfqf1/qOtFKROFT/i5MeL7OKOHXQxtQXAb9SqZ5zfjaI/SmsOnwf5ecK+q8Mpc2rIqge9OdTjLPE/2bWvogfs6cKTT43Y/Xjca5KRZz7PF+PO6qIieIRLegL0lBF6vsixTypLxKMjEtD/9KpRWpUxP9uUWZ7YbC4rC8ylpvxs5EJx7KcUr42bLikvgisB/X5FOIs87+ZTUV2x9tfJM9Dq9riczMz9UXc40WHKlKMZXYd+oIovzelcUm/ZRyZqVzdYVIRFH+miZk0L+JdV9BHLFERUKux+oqVDMsH9ZzxsxFQppQN7TjyX9FOgUubPqIF60F7PoU44e8LPA9anPVZXD38uo3neFp8bsTqx+OcqR78O7I73k4Xz/kbzLvUqEi3OqDpov+VrfQFviDq7y31C1F8O0T/kuf4fITx4NZZ9DvJjWtPWqPlj2xc9rvABwXPuqdrgWC9xRO8+XoA9Sz72SDM9xE/V6L/yhCS8I6PrtfyOQbWA34+xecH+e7g5wHHCZ5bG6iQVp+bg7H48RhPnZiHeiM0oHxvQWnwJ/R598fQ668BL++LkGqsgwZL2S8SDykwnzwh86GPuNRAFVkzlvb3ZXvXE6Ll1PXj1IQQC48+Zet2gCqyblaQR0sgHNFaUGCEEDNUEUIIIfVQRQghhNRDFSGEEFLPilVkws6apr4I5VRvmsVM9TmfMF/ybj4W9JkgpIptqkjH7Ctfm6tIukZrXFcOd7ZHlVa9duu9fCyUz5uwCZ8Jsk1Wtuuw1Xedc+tXkbRhGjNEJYmNgx2CuQwlZad/Hx8L/fMmbMJngmyTl/dFJviLFPkoiH4kHeV+IdhnQgk09SnpVGTYywr8M3K+Kf3f9NbZz8qQ9VcwJAR7Rx8LK/SZIMTj5SriDNkYbT4KXcmiH8l43jK/EMW/BAQq+pQ8mpgupMifw+qbkutAeONIBf4K5vxaLl//q/GxMEOfCUI8lqAizrkyf5EqHwWXHt8fWOwXoviXyFdTks694JePz5tJPxxIZoG/QlnOuDf1sTBDnwlCPNbkL1LhowD8SB7lFfuFGGcO0PwHbiXNvinaFEvctyvxVyhpbt7Vx8IMfSYI8VhCX6R0RMvsowD9SMYSyvxCKlTE0hep8E2BfRGhMgv8FYou8F19LMzQZ4IQj5eriGV23eqjAP0SOsr9QqyrmEDWTMWFqco3RbThkxr0rL9CuR/7W/pYGKHPBCEeK1vpa/VRkP0SGvpM4EiBT4n8rl3hmwJaZ3E2O2dyYPcdeTcfCxP0mSDE4+V9EVJNq8GcpfiOkHlp7zNBtglVZM202Ba+HN8RMhtP8pkg24Qqsm5emEeLEEIcVYQQQsgUqCKEEELqoYoQQgipZwMqsnzfCLgRvd5XY+p8iRX6lMzG8p9nsjGoIvVEaTzqG6ZMxnj7Slxx7Va380DeC1ncNEP/EvqUjB/K+0VwIXAzh1zOkjLY0weFvFxFVv30pLvDKq+lsYoIDUG3p/0raeij5CgFJYv+JXVxjrG9i0+J7uMilyAdr5ezJBWhDwp5uYrYM6CIvg7Ih0MbwRD8P4Z4yt4lheS1QcZDcQM5ypyBfEecxQdFiOpR/mXvhO7ChH2LYiKvjfuUZH1cCo+3loOgDwqZhZeriDP4iyT+HAU+HE5shZV8U5K/CIg7+rUMDSvymdBU5J9oBwLiV+LUOgRxyZ/nRwdFVi8VeXxp0z4lBT4uRcdby4HQB4XMwhJUxLlCf5Fo5Gd8OnX/D7EVln4qZT4lDh3Q/zjhr0jvi4AwsrmHk4E12AoIKhLlHCsdMkotv8ZQN+tTUuDjUnS8tRwIfVDILKzJX0RXEdxvSP4Kfpa6T4Ycs6Qi0GeikYpocaotjtgX2aF/4utW+o5b9ikp8XEpOd5aDoQ+KGQWltAXMY1ohT4Z3pCuUUXyfZF83GBeZOa+SIC5L6KcV0S/WZv2KSnwcSk63loOhD4oZBZeriL22XXvi2WZ29O/omymttUvwa/FHx2CPhPAPwNeF4pfidMyL5J4vAe/ZMl/Aswn+QVu2adE9XER6hMdn/WDKYM+KGQWVrXSF5pYgKZT86WQ/D+0UwiEwynhmAPwgSjxz0BmJ8qfcn4ewYKoZDmTti8hHvJC5QwHbNynRClEbA3R8c2CoQ8KeTov74tYqB4g3hYtfUcs74D0KSHPgD4oS4cq8o5M3uZd4T9BnxLSHPqgrAKqyHuyojxahJBVsyoVIYQQsjCoIoQQQuqhihBCCKlnAyqyID+GVqunRp44/2Got6a+HT0TdqK1iwfN93AeiJAeqsictFaRBpYbOCRzvTVe6Tt5P3ObeNDaM65JI6RjVbsOV09bFWlSWsOQ3lNFcDncH0OIcy9XkZf4i+wv/07HPhl7V1qQRyjdmC2XHyU4Ksh35DXZ0V5fyVdDL9/gtyHHr/hJVPiyOJyfCu2NR1Uk7mHOewImtVEeD3p+etCefPHzBfV9CZmDl6uIm99fpM9C32dWGLehAL8KVH7i01DgbTcYkPgHA78QrXyUDMoSvx+SQLkvCzre5tdi9B1x+Hqt8ejPj8O1JH1OFSEbYwkq4ty8/iJ989R/DvLgeq0YLB/m5QVXefrv9vWZeKBiXw1Yvpq7tzx+o4row18WHxSRKt8Rlx5vjkd/fpID8p8TsiG26C+iqIjsV6GU72XDLmhN+uGUsJHSfDVQ+WUWKfn4TSpi9Ief6NdS4jsCrtcaT74fyZTmhCCW0BeZ218Eqgjyq8i3wr3dW4ZHkx2PC2mpcEH5Yl+kPn7xT8W+LOj4SX4tBb4j8Hqt8WRVhBPshEBeriIv8BeBKgL9KrTyd8fb3/12LbqEocmOhDPnqyGUL4VUE78yJlPuy4KOV+dC0kzpVt8ReL3WeLJiYJ4X4SJgsh1WtdK3kb+IMqIF/CrUViaeKtcu9xTOlo+NoOJrAsq3+G3k4w/Oa/VlqfJBEX0jrL4j8vWa48mpiGmN1mPckmlDyVZ4eV/EwjJz+j47Klh++53w87IW3wjrcNZarouQNlBFJvLsVTpq+Q32rr+GFflGmPaur+i6CGkFVaSafmznWU1GUflz+4hsDebRIiTHqlSEEELIwqCKEEIIqYcqQgghpB6qyIrxNmMHi7XQ5/NgWr39bvM6nC8h26NXEX8d/RJWmIjx+LPrU3PeWXPNLg+0QKvNwq36ZcQGFRFCNd6XIcHzYnb/0XeEbI3fn+9oX5tlM/mTCOLxlroGyXenSoi/Q22NSSxQUsJ80sMiZlCR9BTW+7I/9SIUJeLtcsx8vaZBZ1oUsi1+f76Tt6fhDTFcWRu2DgafBqsPRxzPEEb/P1MbyjS3a2YUAvtPCH0mNRVusHe6pMcA/TOMKgLf8RO/EMV3RCkH+YJAhDjN98XDz6n1cegSxhi6BfC+lNcbvBBC3pvfn+/0vb5PNwRVxOjTYPPhwK1wF8/kXSN+49JPIegFll5Xd0DyMj68MlvfUkX/DKGV/7vs8edO8fnAfiFiXwSVg3xB9EuTzEts98VDGscrVhF0XyrqbTh41WkFCDHwUBE5aR1SEbtPg8WHI02ZF5g7TZ8x7oJ5tFOBSxVCvq74bTrO4t4n4g0H4qp7UVHrbOiLwPulNHbSn1A5yBdEQUoPbL8vfrTCSU0qIhxZU29jgWudbyPECOqLRM2fc0V+D018OHJ9kcmzx4+xiKGBKFMR0TtW9vz4PP9d9u7jcLqcT8dduG5HM6MVToz9M8pVBN4v7cKFhhKVg3xBNKRT2+/LIy7kLGCZ6BbuS1W9jaemipCNUDUvUuPTYPDhiH+BSTy58YQMu+PN16GyeZGCrLpDtXye/y773fH89bE/3Q+7TlQiSmaPVf+M6r4IKj8k3xcBZyx7DZdObb8vTjenqVku5d+Xmnobo+IEO9kIvz/fj+WS/vhvOv8RehHW+DQYfDg+z96wVbRuBw8NpX4VEPkaMUrmeRDn/Xy6d4J3O13E7ldBQ6P6Z1hm17X7BWpMFANQDvQFURAvX70v0v3V3yeqFt36gVXU2/BFzouQrdDvFxnX3SdNVT9PGwxGVfg0mNbUivEk+0XCX7LgVwFJTTU01JE6od681dK+QoeVVjLiAfxCnLOu0VKtWeS1RmIVgXKgLwgmG6fkwZV2muFqAulzgHJfKurNcY0W2Rb+3vUnd8OfnpGXvg7r4l1f2DmcRbZFmAHFe/NqPTf43FUr9HVYJav1R1Hg3nWyNWbIo/VsHw6yYphHi5C1w2yMhBBC6qGKEEIIqYcqQgghpB6qCCGEkHqoIoQQQuqhihBCCKmHKkIIIaQeqgghhJB6qCKEEELqoYoQQgip5n8BTvFQV1PWbwAAAABJRU5ErkJggg==)
article 是指物品,dealer指商人。
插入数据:
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAgEAAABYCAIAAADjmrMgAAAPdElEQVR4nO2dXZbCIAyFu6m6nboZdS3qVtSdOGeWMA/Vlp8EEgi1de535mHGqUBTIEDhpusAAAAAAAAAAAAAADCiPzx+nr8/z9+f8/7TZfmH7I6338vw6VI4oD4A8B/pD4/KNj+cf199x+I9yHD+vR928ou15VSlr6StDxjO4Q3Gn5DU1wcAwJYg2vxw/bmdemU6LbtLy0y1bmOjPqAbrj/Pq5P8/vIUZQcfAMBa+Dmfjrffn+fvZdhfnr8/z8ex77r+dB9/GfH+3I3Xjz9ug/eGwH7/Trb54fzr9yB5ou5y7OPGkr8L7/wrU06nkP3hcT/sppWKMZd54UI5/xCWM5m+W36Jlcj7HfOd/sXZZ0o/YU8Sv9PvT3enqE59CMsf1QffV/njA/J5JZ4vAEDB2H6G89jgp6a4O97mXsxtsdwIzlsEiMb43Lf6w0PQ0Xi5xH2r16Vqyun+/uqOx2L7LtBiHsCWk0k/sn9uzsTc7+7t4INruPSj63P5hjZ0fp/65eGcHROwPiD1vDCTAKCesbG9W5TTNczt0Guf/eFBDUv3F7crF/uA18XicRwzvibyFZXTGbf6/Z13mZUPSIxzw/T9AXVYbArmfpl82fSDtaN8vs5yEL/ulK8PnH2Sz0s5iQQAELA+YGp+/ekeN+Bg4SLoU1Q+oD/do7UjDlXfypTTX3thxubJTI3LSaQfGlC0rE9tuWHyZdPX+4BpOSioA4Gpy3wA/7yY+wUAKOF9wKtvGhfKqa/ujrd38/Pbf7yG0HQtiOtbiXLyndq6fIB+HhDk5a75lM8DwstoxiUa34BeRTKbB+TvFwCgJOEDxmXx+43tBej15eG6+Dvh6d/0XiM3d27zYsIHSJbFa8pJpR86Y1UBgudC5cul710v3OjZDdef5+MeTiCmP/cXQX2Y8/LnhZIy4N0AAOWkfAAxwvI2Y3gNe5q2306939fQ+16Ue0OZffepPo5ZiPD/JXrH6Hwl19coy5lIf9qcI3EA3P0m8iXTp42TY0zKd+fjUGBcvRmm5US+PszLPtfBW34ki5R4vgAAM0p3lxft/QcrYHXnigEAH6NgDeQFfMBWgQ8AAMw7Lkr33sEHbBX4AAAAAAAAAAAAAAAAAAArITq3rGd3vKk2FGqvb8rueFOcqpuB3b7Wbquyc8eU54vt35RSu30vpDrxuFtc8QabPnnEq1ZIT0v55wMUp4ujrANJUS+pULFZQiO7Zfbpt7Qbd2iDP8xhY7f5XIUuqkSb+pa8nn6+jA4rh97OXHks7F/WvtrVWyc1za01b++bwCp+wHD9eT6OB52ZyGfcHx73w570uto22XWdXEz/tRvnHB4eTlRx7cnYZnZz9xERmg3N7OYeZpT8/qLebsP5XbZAXz2XTtP6RlzPPN/gTHgul0I7F5zG5+6LbwXy9tWy3pa0o+btfTOotSII+ZrdcZSyMHCVr2qRfhgqhEJDr3MYkYBE8rsaBaGGdos0ZS1qat5unEZhTruw2m4uRN+nwb6++YmTzzfWYU1ao9zOHIb2rwm+ZFVvS9pR8/a+LVSacTkB6jofMFU4q8MNMsG1uR3q6oSiAbS0WzieMjhbILFbUGz32ZGfu181sVvXqcahVOrW9Y0kMIj751ucI3ULFXZmk7SyvybTCOt6K29H7dv7BpHGD0gOu6p9gFPhal3utGQpeWbzo+WEr5l1W/Ep7qZ2K9MXYsqisJvb/7qrsdznMzZ20wv5BRjWtxSxD7id+lfv78aM4ii3M0e9/VXtK5G4Vb3tOkU7at/eV4gnAU+ZSRo/IFlfTdc0rFxuflXRrQf8eJDubqTjx+XsZlVNRauxc716HAdnSMh9PqduYDet5nk68YZDPGIe4MZgEAyBi+2cKFJ9vR1TKnnbRCRuUG+F7WiJ9r41NG2p5Xg2jlViYu7cYwtFRrm5OXV34kXM5vMAbbyBPNrqPlxpU1Cf19ut2gE0q28xxKJN8Lw0N6KxM4dNvZ3yLbSbdb2VtaNF2vum0L4T1q9rU5rG3NdDHWxJ20inH+83SF7PVmh6/wY1eKTTb2i3wvGUtd3IfzGf19pt3AdC3+Rn6xuTEbsvKB5HW9qZLZFJve06dT2JvmtZbwvaUbP2vh0KfDjzis/3q1GNF7QuMuSLcDXfT99bZ9Q9M88g6XQ6bhGZvt9mdivW8a+3G7fJPbf5vdJuTtCFVdS39JVkORP7+g3tzFFp/0y7WLzeptvRh9r7d6NdOW0defxTkc2TsTmFg99c+rAb7GYN7F+er8Zu3414Q95rybWZgVqnn4CceGbKA7vBbp8G9i+jxG7fjYF+yKb5oO7KpoHdPgvsXwb0ggAAAAAAAAAAAADAfwZ64tBhLwF2swXr1OAzNNITHz+9ckk11hOfdwc7+bbVwbexW3DeVagjz6KyG63jv0jcBbnAyTvnD9gtpe/PJxJe/y917cEyWMUPmKnREw8FAPrDNTwJ0khPPDi+/6K5Dr5Dhd28UzzE6LWd3Tgd/+ZxF7qO1H/PprOs3TL1J4K9/p/q2oNlsIgfECZYqr1VpwM8o9YTp8u8qA5+hd2ClLX6MzM18QwUfZaJ3Uj9dx2t7abV9+evL7LnJtaywDowix/Q1Qk/WYmdTUj7sv3lNYj79QQdl9TBt7RbZVgVg+Wa5nEXGP13Ha3tptX3568vsid8AFBRHT/AQE/cfGVT4QOcWf/8rSV08A3stgIfEAjtto67wOm/62huN62+P3v9V+vag2VYJn5A13XqN2nuNz/oA9xpkPPnIjr4r5SK7fZpH5CeR9rHXZDpv+dZwG5afX/B9f9L1x4sg1n8gJHy6mj1PsApiXge4OTLrFa30cH30i+zm9m6tlMSsQ/IVx7ruAtS/fd80Ze1m0bfP3X9/9G1B8tgGT+g6+r0xEMV1mBfEIOBnrg7tqKn1Y108Gcq7Ob1Zf66VgoLHfaUjv9Iy7gLHfeiflV24/5VFCdAYU/t3mjwP7GJH2ClJ951/tKncGJuoSfu3IJjkMY6+EZ28/e5y0fE1XbjdPybx11wvkNU4PXYLVl/5HECauxpOrEGYAR64nO+0GEvyxd2swO69mB5oCcOHfZSYDdboGsPPgP0xKHDXgLsZgv0ggAAAAAAAAAAAAAAAGABnM2F3sYV7vNlUO33/bZ1dqyPg63ibgFew9t8sjzuwf0KRapAt10jAb8muM0qNqedy2VEFT6AKGruSESUG3s9GY+hPdDTB1vEPfunFnO2xzuL6JybnX2AoUZVuU7OZ+HOLhmpny7gA+IsnPPS9HnUOAX6ejoew0JAOAFsD0Zu0BfM8ts2Of4aNWano7avFh4IoWR1UcKR1FSM9y+WIs8iKXn6vt5lC+YrcRfgdHbekUtJJ+UcAY3lwBQ+gB0vR3HTgqBRQUgpLh3vqKrEBxDl9J9FXj+HvV7vwNjnIrcbWzAA1g8TwIj1Ae7Y2f391X2Ml81tMozvkR4l8X3oWJ68BKYc4ZBNel/jBdFAeAp3pR0h9ofHdKfTmJfoo5/Xgf+8459XQm+H7Ea5dMI0BT6AMIXr+9/rdakHzV7PxGNQFSa6L6Hdpos/8CYGgGLiPjfpA+LxlzMEntu/c9n83RIRY9cHmC7vSsdr9H2FI1nn83dn7biudzrFM5igb1XMA9jnleiqGFU1Mh3/c5EPoHRMxy++evPHsc9VFfZ6Lh4DC/NcSuw2J7jJl0zgvxKPW4POq+ucth2/U303FX6cG/aPCXLzADOFZ3lDpe8r7OzcPug6dLvj+Xo5n3p/r8g8WpdMCAJTl/kA9nmlOlmim+PS0cYs7JisX6srk60EPoC+no/HwEM8lyK7zWWDDwAbQvk+gG1UibWOsU/JBqXzM2LKk1cMlqDwJXxAQWoeMPqA/nQ57Ibz49iPLiFA8s7THyxbzQO49H3y8wAmR5lnpbIOXuRm3wew18viMfAFez+XEru9yoLXwmBbuHNnV8t+Xpbx43xxe2lSVb8/3Z+P+00aU2VqY8HeD35RRREnoFPO1pn78voCv5yPy/kxuqv7+UpOfQTdhNuX7S/F84D082IsRtqHScexwzg2L3sfwNW9F5m4C+HafToeg6xgBXabvoj3AWBbOFLsYUfzfrvoLeN4+yi8d8Jsvyba7ZcsT3Q+gFA/l434lHsHk2tchN2c/bXu2rRvNEnHNDgvNgd/GU23L4h+Xp3/JtnvtpyvzNcz6Uz15Hbqhcsg2XKGBs/FXfCup+MxMCSeS4HdOuwLAlul8QTWcj8PCfTKt8W3DpaxEAS2i2r0pKPtTgnolW8S6wDOawDnhAEIeM+a0TBABPSCAAAAAAAAAAAAAAAA4N/xD45WJg8JV6hhAwWwMwAR6n3rLTD3AVZxAoJ0qrf9JU6c1VvgUxsutbr/ZApBydtsslyXD6i3GwAGOOddVSe5TGk8DyiPE+CdZjLoQZruH/+ID9Dq/pPEZvn6jfYmdgPAAFoDues6tX46HY+M19/ndOcTOvtlvXDF0c3gRKuvHU3pKc3adqR2Md3ZcZMMRqc+jitQpPtvYmet7j8NaRZPikNar1j7M3ZOPC//nPAzOKa+CrsBYIDjAyKtHoV+OqOnz+rvs7rzec0JpQ+oGlGGqgbTbaZ8wNQ1xIfjuMIwkvpEv0DGFZj+lOv+29hZq/ufSOe875yuf7oX7n4TcR0SWrbRJ+z1jq3kMTAWtxsA9TCDGqV+OqujycUVYHXnrVUf6vRbinxAQrdS7gNECztRfDex7r+RnbW6/+l0ut3x/BgPjk2xd6jL3uUn6lUq/l3H+IDsc3S/tS67AVDPNMbxBuZa/XRWT1+mvex/Xaezn6RWpuJjPoDvFPi4Agrd/ynTSjtrdf8T6Yx94nk/nB/H3vEBzP0m3q5b+AB2HtCtym4A1DPXb09QU6mfnp4H5HwA01PXvyurFqUpfB/AfF3pA6h5QCqugEL3P0qz4p25Rvc/lc51GK4vze3D/q27wN7vEj4gsx9sBXYDoB5GB1+rn87p6Qv093ndeXr1ViX9X7ndyOvE92GsBSK+gtenxDaUrwXxMQ7ZuAIa3f907go7S+pMHi/CxP7yfNxvcUwY734b+wAy+E9U6o/bDYB6vHGWNzzR6qfTevrsOzRadz6ts6/xAco4AYlE6E0gdHwFzmjhJhM6roDEzom4Agrdfzs7p3T/5bj9u/eelrtfpQ9I2Z/0GeFWq5XaDQCwLrCw+xWEizPfqHQNAGgAfMBXEGzMxXo9AEAGfMCX4K8FwQEAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAACA1fIHKigHdVGLSDQAAAAASUVORK5CYII=)
现在来查看下刚才插入的数据:
SELECT * FROM shop;
3.6.1 The Maximum Value for a Column
求某一列的极大值:
这里求哪件商品的序号最大
SELECT MAX(article) AS article FROM shop;
3.6.2 The Row Holding the Maximum of a Certain Column
找出最贵的那件物品的商品序号,及商人
可以用一个子句:
SELECT article, dealer, price
FROM shop
WHERE price=(SELECT MAX(price) FROM shop);
这是相当于先把最大值求出,再将这个最大值作为查询条件使用。
另一种方法是用"Left Join" 或者用mysql的 LIMIT 子句将所有列降序排序,然后取第一行。
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;
注:如果表中有多处19.95的商品,也就是说最高价商品有多个,那么这里由于Limit 1,只会显示其中一个。
3.6.3 Maximum of Column per Group
对每一组的列取最大值
这里求每一物品的最高价(因为不同商人价格不同,同一商品为一组)
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article;
3.6.4 The Rows Holding the Group-wise Maximum of a Certain Column
对具体的列,有最大值的那个组
对每一商品,求出价最高的商人(组,多人)
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
这里也是将 Select Max(s2.price From shop s2 Where s1.articel = s2.article )作为一个子句的条件。
前面种方法利用了有相互关联的查询子句,可能导致效率低下。其他可能的能解决这个问题的方法是在From子句中使用相互不关联的子句。或者用Left Join
相互不关联的子句:
SELECT s1.article, dealer, s1.price
FROM shop s1
JOIN (
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article) AS s2
ON s1.article = s2.article AND s1.price = s2.price;
LEFT JOIN
:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price
WHERE s2.article IS NULL;
Left Join 只有当s1.price是最大值时,且s2.price也没有更大值,s2这行的值为NULL时才有效。
(发现没怎么理解,暂停......待续)