当前位置：Gxlcms > 数据库问题 > MySQL初步

MySQL初步

时间：2021-07-01 10:21:17 帮助过：7人阅读


1.1 本节内容
本节的主要内容是MySQL的基本操作（来自MySQL 5.7官方文档）。

1.2 工具准备
一台装好了mysql的ubuntu 16.04 LTS机器。

二 MySQL的连接与断开
2.1 连接与断开MySQL服务
MySQL提供了一个Linux命令行程序mysql，利用它我们可以连接到MySQL并执行SQL操作。想要查看mysql自带帮助文档，请使用以下命令。

  1. mysql --help

2.2.1 使用以下命令连接到MySQL

  1. mysql -h localhost -u root -p

命令详解：
-h后面参数表示主机名。如果MySQL安装在了本地，后面的参数可以使用localhost表示本地。也可以省略-h参数。
-u后面的参数表示使用的账户名。可以使用root用户，前提是你得记得安装MySQL时输入的root用户密码。
-p参数表示需要输入密码。一般来说-p参数是必须的。

2.1.2 使用quit命令退出mysql命令行

  1. quit


三 键入查询语句
3.1 查询MySQL版本和当前时间

  1. <span style="color: #0000ff">SELECT</span> VERSION(), <span style="color: #0000ff">CURRENT_DATE</span><span style="color: #000000">();
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+----------------+</span>
  3. <span style="color: #808080">|</span> VERSION() <span style="color: #808080">|</span> <span style="color: #0000ff">CURRENT_DATE</span>() <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+----------------+</span>
  5. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">5.7</span>.<span style="color: #800000; font-weight: bold">21</span>    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">2018</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">02</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">14</span>     <span style="color: #808080">|</span>
  6. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+----------------+</span>
  7. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.06</span> sec)

 

注意：SQL语句后面有个分号，quit命令后没有分号。

3.2 使用MySQL做简单计算

  1. <span style="color: #0000ff">SELECT</span> <span style="color: #ff00ff">SIN</span>(<span style="color: #ff00ff">PI</span>()<span style="color: #808080">/</span><span style="color: #800000; font-weight: bold">2</span>), (<span style="color: #800000; font-weight: bold">4</span><span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">1</span>)<span style="color: #808080">*</span><span style="color: #800000; font-weight: bold">5</span><span style="color: #000000">;
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----------+---------+</span>
  3. <span style="color: #808080">|</span> <span style="color: #ff00ff">SIN</span>(<span style="color: #ff00ff">PI</span>()<span style="color: #808080">/</span><span style="color: #800000; font-weight: bold">2</span>) <span style="color: #808080">|</span> (<span style="color: #800000; font-weight: bold">4</span><span style="color: #808080">+</span><span style="color: #800000; font-weight: bold">1</span>)<span style="color: #808080">*</span><span style="color: #800000; font-weight: bold">5</span> <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----------+---------+</span>
  5. <span style="color: #808080">|</span>           <span style="color: #800000; font-weight: bold">1</span> <span style="color: #808080">|</span>      <span style="color: #800000; font-weight: bold">25</span> <span style="color: #808080">|</span>
  6. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----------+---------+</span>
  7. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.05</span> sec)


3.3 将多条命令写在同一行

  1. <span style="color: #0000ff">SELECT</span> VERSION(); <span style="color: #0000ff">SELECT</span><span style="color: #000000"> NOW();
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+</span>
  3. <span style="color: #808080">|</span> VERSION() <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+</span>
  5. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">5.7</span>.<span style="color: #800000; font-weight: bold">21</span>    <span style="color: #808080">|</span>
  6. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">---------+</span>
  7. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
  8. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------------------+</span>
  9. <span style="color: #808080">|</span> NOW()               <span style="color: #808080">|</span>
  10. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------------------+</span>
  11. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">2018</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">02</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">14</span> <span style="color: #800000; font-weight: bold">15</span>:<span style="color: #800000; font-weight: bold">11</span>:<span style="color: #800000; font-weight: bold">56</span> <span style="color: #808080">|</span>
  12. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------------------+</span>
  13. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)


3.4 将一条命令写在多行中

  1. <span style="color: #0000ff">SELECT</span> 
  2. <span style="color: #ff00ff">USER</span><span style="color: #000000">()
  3. ,
  4. </span><span style="color: #0000ff">CURRENT_DATE</span><span style="color: #000000">();
  5. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------------+----------------+</span>
  6. <span style="color: #808080">|</span> <span style="color: #ff00ff">USER</span>()         <span style="color: #808080">|</span> <span style="color: #0000ff">CURRENT_DATE</span>() <span style="color: #808080">|</span>
  7. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------------+----------------+</span>
  8. <span style="color: #808080">|</span> root<span style="color: #008000">@localhost</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">2018</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">02</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">14</span>     <span style="color: #808080">|</span>
  9. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------------+----------------+</span>
  10. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.05</span> sec)

注意：在MySQL中，识别一条SQL命令的标志是分号。所以我们可以把一条命令写在多行中。

3.5 撤销当前输入

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span>
  2. <span style="color: #808080">-></span> <span style="color: #ff00ff">USER</span><span style="color: #000000">()
  3. </span><span style="color: #808080">-></span><span style="color: #000000"> \c
  4. mysql</span><span style="color: #808080">></span>

 

注意：c是小写的。

四 数据库的创建和使用
4.1 查看当前系统中的数据库

  1. <span style="color: #000000">SHOW DATABASES;
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------------------+</span>
  3. <span style="color: #808080">|</span> <span style="color: #0000ff">Database</span>           <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------------------+</span>
  5. <span style="color: #808080">|</span> information_schema <span style="color: #808080">|</span>
  6. <span style="color: #808080">|</span> mysql              <span style="color: #808080">|</span>
  7. <span style="color: #808080">|</span> performance_schema <span style="color: #808080">|</span>
  8. <span style="color: #808080">|</span> sys                <span style="color: #808080">|</span>
  9. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------------------+</span>
  10. <span style="color: #800000; font-weight: bold">4</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.15</span> sec)

注意：SHOW DATEBASES;只会将当前用户具有的SHOW DATABASES命令权限的数据库打印出来。

4.2 使用（进入)数据库

  1. <span style="color: #0000ff">USE</span><span style="color: #000000"> sys
  2. Reading </span><span style="color: #0000ff">table</span> information <span style="color: #0000ff">for</span> completion <span style="color: #0000ff">of</span> <span style="color: #0000ff">table</span> <span style="color: #808080">and</span> <span style="color: #0000ff">column</span><span style="color: #000000"> names
  3. You can turn </span><span style="color: #0000ff">off</span> this feature <span style="color: #0000ff">to</span> get a quicker startup <span style="color: #0000ff">with</span> <span style="color: #808080">-</span><span style="color: #000000">A
  4. </span><span style="color: #0000ff">Database</span> changed

 

注意：USE和前面的QUIT一样，后面是不接分号的。与之前命令不一样的是USE命令必须写在单独一行中，不能跨越多行。

4.3 创建一个数据库

  1. <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">DATABASE</span><span style="color: #000000"> menagerie;
  2. Query OK, </span><span style="color: #800000; font-weight: bold">1</span> row affected (<span style="color: #800000; font-weight: bold">0.07</span> sec)

注意：在Linux下数据库名是大小写敏感的。

 

4.4 在连接MySQL时就指定使用menagerie数据库

  1. mysql -u root -p menagerie


4.5 查看当前使用的是哪个数据库

  1. <span style="color: #0000ff">SELECT</span> <span style="color: #0000ff">DATABASE</span><span style="color: #000000">();
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+</span>
  3. <span style="color: #808080">|</span> <span style="color: #0000ff">DATABASE</span>() <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+</span>
  5. <span style="color: #808080">|</span> menagerie  <span style="color: #808080">|</span>
  6. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+</span>
  7. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)


4.6 查看当前数据库中有哪些表

  1. <span style="color: #0000ff">SELECT</span><span style="color: #000000"> TABLES;
  2. Empty </span><span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)

 

注意：刚刚创建的数据库中是没有表的。

4.7 创建表格

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">CREATE</span> <span style="color: #0000ff">TABLE</span><span style="color: #000000"> pet
  2.     </span><span style="color: #808080">-></span><span style="color: #000000"> (
  3.     </span><span style="color: #808080">-></span> name <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">),
  4.     </span><span style="color: #808080">-></span> owner <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">),
  5.     </span><span style="color: #808080">-></span> species <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">20</span><span style="color: #000000">),
  6.     </span><span style="color: #808080">-></span> sex <span style="color: #0000ff">CHAR</span>(<span style="color: #800000; font-weight: bold">1</span><span style="color: #000000">),
  7.     </span><span style="color: #808080">-></span><span style="color: #000000"> birth DATE,
  8.     </span><span style="color: #808080">-></span><span style="color: #000000"> death DATE
  9.     </span><span style="color: #808080">-></span><span style="color: #000000"> );
  10. Query OK, </span><span style="color: #800000; font-weight: bold">0</span> rows affected (<span style="color: #800000; font-weight: bold">0.15</span><span style="color: #000000"> sec)
  11. mysql</span><span style="color: #808080">></span><span style="color: #000000"> SHOW TABLES;
  12. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------------------+</span>
  13. <span style="color: #808080">|</span> Tables_in_menagerie <span style="color: #808080">|</span>
  14. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------------------+</span>
  15. <span style="color: #808080">|</span> pet                 <span style="color: #808080">|</span>
  16. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------------------+</span>
  17. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.01</span> sec)

 


4.8 查看表的信息

  1. mysql<span style="color: #808080">></span><span style="color: #000000"> DESCRIBE pet;
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------+-------------+------+-----+---------+-------+</span>
  3. <span style="color: #808080">|</span> Field   <span style="color: #808080">|</span> Type        <span style="color: #808080">|</span> <span style="color: #0000ff">Null</span> <span style="color: #808080">|</span> <span style="color: #0000ff">Key</span> <span style="color: #808080">|</span> <span style="color: #0000ff">Default</span> <span style="color: #808080">|</span> Extra <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------+-------------+------+-----+---------+-------+</span>
  5. <span style="color: #808080">|</span> name    <span style="color: #808080">|</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">20</span>) <span style="color: #808080">|</span> YES  <span style="color: #808080">|</span>     <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>    <span style="color: #808080">|</span>       <span style="color: #808080">|</span>
  6. <span style="color: #808080">|</span> owner   <span style="color: #808080">|</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">20</span>) <span style="color: #808080">|</span> YES  <span style="color: #808080">|</span>     <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>    <span style="color: #808080">|</span>       <span style="color: #808080">|</span>
  7. <span style="color: #808080">|</span> species <span style="color: #808080">|</span> <span style="color: #0000ff">varchar</span>(<span style="color: #800000; font-weight: bold">20</span>) <span style="color: #808080">|</span> YES  <span style="color: #808080">|</span>     <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>    <span style="color: #808080">|</span>       <span style="color: #808080">|</span>
  8. <span style="color: #808080">|</span> sex     <span style="color: #808080">|</span> <span style="color: #0000ff">char</span>(<span style="color: #800000; font-weight: bold">1</span>)     <span style="color: #808080">|</span> YES  <span style="color: #808080">|</span>     <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>    <span style="color: #808080">|</span>       <span style="color: #808080">|</span>
  9. <span style="color: #808080">|</span> birth   <span style="color: #808080">|</span> date        <span style="color: #808080">|</span> YES  <span style="color: #808080">|</span>     <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>    <span style="color: #808080">|</span>       <span style="color: #808080">|</span>
  10. <span style="color: #808080">|</span> death   <span style="color: #808080">|</span> date        <span style="color: #808080">|</span> YES  <span style="color: #808080">|</span>     <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>    <span style="color: #808080">|</span>       <span style="color: #808080">|</span>
  11. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-------+-------------+------+-----+---------+-------+</span>
  12. <span style="color: #800000; font-weight: bold">6</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)


4.9 向表中输入信息
第一步：创建一个pet.sql文件，文件内容如下。

  1. <span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> pet
  2. </span><span style="color: #0000ff">VALUES</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">Fluffy</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">Harold</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">cat</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">f</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">1993-02-04</span><span style="color: #ff0000">‘</span>, <span style="color: #0000ff">NULL</span><span style="color: #000000">);
  3. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> pet
  4. </span><span style="color: #0000ff">VALUES</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">Claws</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">Gwen</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">cat</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">m</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">1994-03-17</span><span style="color: #ff0000">‘</span>, <span style="color: #0000ff">NULL</span><span style="color: #000000">);
  5. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> pet
  6. </span><span style="color: #0000ff">VALUES</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">Buffy</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">Harold</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">dog</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">f</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">1989-05-13</span><span style="color: #ff0000">‘</span>, <span style="color: #0000ff">NULL</span><span style="color: #000000">);
  7. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> pet
  8. </span><span style="color: #0000ff">VALUES</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">Fang</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">Benny</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">dog</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">m</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">1990-08-27</span><span style="color: #ff0000">‘</span>, <span style="color: #0000ff">NULL</span><span style="color: #000000">);
  9. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> pet
  10. </span><span style="color: #0000ff">VALUES</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">Bowser</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">Diane</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">dog</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">m</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">1979-08-31</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">1995-07-29</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  11. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> pet
  12. </span><span style="color: #0000ff">VALUES</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">Chirpy</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">Gwen</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">bird</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">f</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">1998-09-11</span><span style="color: #ff0000">‘</span>, <span style="color: #0000ff">NULL</span><span style="color: #000000">);
  13. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> pet
  14. </span><span style="color: #0000ff">VALUES</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">Whistler</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">Gwen</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">bird</span><span style="color: #ff0000">‘</span>, <span style="color: #0000ff">NULL</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">1997-12-09</span><span style="color: #ff0000">‘</span>, <span style="color: #0000ff">NULL</span><span style="color: #000000">);
  15. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> pet
  16. </span><span style="color: #0000ff">VALUES</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">Slim</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">Benny</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">snake</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">m</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">1996-04-29</span><span style="color: #ff0000">‘</span>, <span style="color: #0000ff">NULL</span><span style="color: #000000">);
  17. </span><span style="color: #0000ff">INSERT</span> <span style="color: #0000ff">INTO</span><span style="color: #000000"> pet
  18. </span><span style="color: #0000ff">VALUES</span> (<span style="color: #ff0000">‘</span><span style="color: #ff0000">Puffball</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">Diane</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">hamster</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">f</span><span style="color: #ff0000">‘</span>, <span style="color: #ff0000">‘</span><span style="color: #ff0000">1999-03-30</span><span style="color: #ff0000">‘</span>, <span style="color: #0000ff">NULL</span>);

第二步：使用下列命令执行sql文件，随后查看表的内容。

  1. mysql<span style="color: #808080">></span> source <span style="color: #808080">~/</span><span style="color: #000000">pet.sql
  2. Query OK, </span><span style="color: #800000; font-weight: bold">1</span> row affected (<span style="color: #800000; font-weight: bold">0.13</span><span style="color: #000000"> sec)
  3. Query OK, </span><span style="color: #800000; font-weight: bold">1</span> row affected (<span style="color: #800000; font-weight: bold">0.01</span><span style="color: #000000"> sec)
  4. Query OK, </span><span style="color: #800000; font-weight: bold">1</span> row affected (<span style="color: #800000; font-weight: bold">0.02</span><span style="color: #000000"> sec)
  5. Query OK, </span><span style="color: #800000; font-weight: bold">1</span> row affected (<span style="color: #800000; font-weight: bold">0.01</span><span style="color: #000000"> sec)
  6. Query OK, </span><span style="color: #800000; font-weight: bold">1</span> row affected (<span style="color: #800000; font-weight: bold">0.01</span><span style="color: #000000"> sec)
  7. Query OK, </span><span style="color: #800000; font-weight: bold">1</span> row affected (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
  8. Query OK, </span><span style="color: #800000; font-weight: bold">1</span> row affected (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
  9. Query OK, </span><span style="color: #800000; font-weight: bold">1</span> row affected (<span style="color: #800000; font-weight: bold">0.00</span><span style="color: #000000"> sec)
  10. Query OK, </span><span style="color: #800000; font-weight: bold">1</span> row affected (<span style="color: #800000; font-weight: bold">0.01</span><span style="color: #000000"> sec)
  11. mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> pet;
  12. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+--------+---------+------+------------+------------+</span>
  13. <span style="color: #808080">|</span> name     <span style="color: #808080">|</span> owner  <span style="color: #808080">|</span> species <span style="color: #808080">|</span> sex  <span style="color: #808080">|</span> birth      <span style="color: #808080">|</span> death      <span style="color: #808080">|</span>
  14. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+--------+---------+------+------------+------------+</span>
  15. <span style="color: #808080">|</span> Fluffy   <span style="color: #808080">|</span> Harold <span style="color: #808080">|</span> cat     <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1993</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">02</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">04</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  16. <span style="color: #808080">|</span> Claws    <span style="color: #808080">|</span> Gwen   <span style="color: #808080">|</span> cat     <span style="color: #808080">|</span> m    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1994</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">03</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">17</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  17. <span style="color: #808080">|</span> Buffy    <span style="color: #808080">|</span> Harold <span style="color: #808080">|</span> dog     <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1989</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">05</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">13</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  18. <span style="color: #808080">|</span> Fang     <span style="color: #808080">|</span> Benny  <span style="color: #808080">|</span> dog     <span style="color: #808080">|</span> m    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1990</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">08</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">27</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  19. <span style="color: #808080">|</span> Bowser   <span style="color: #808080">|</span> Diane  <span style="color: #808080">|</span> dog     <span style="color: #808080">|</span> m    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1979</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">08</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">31</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1995</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">07</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">29</span> <span style="color: #808080">|</span>
  20. <span style="color: #808080">|</span> Chirpy   <span style="color: #808080">|</span> Gwen   <span style="color: #808080">|</span> bird    <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1998</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">09</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">11</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  21. <span style="color: #808080">|</span> Whistler <span style="color: #808080">|</span> Gwen   <span style="color: #808080">|</span> bird    <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1997</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">12</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">09</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  22. <span style="color: #808080">|</span> Slim     <span style="color: #808080">|</span> Benny  <span style="color: #808080">|</span> snake   <span style="color: #808080">|</span> m    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1996</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">04</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">29</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  23. <span style="color: #808080">|</span> Puffball <span style="color: #808080">|</span> Diane  <span style="color: #808080">|</span> hamster <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1999</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">03</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">30</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  24. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+--------+---------+------+------------+------------+</span>
  25. <span style="color: #800000; font-weight: bold">9</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)


五 SELECT语句
5.1 选择所有的列

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> pet;
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+--------+---------+------+------------+------------+</span>
  3. <span style="color: #808080">|</span> name     <span style="color: #808080">|</span> owner  <span style="color: #808080">|</span> species <span style="color: #808080">|</span> sex  <span style="color: #808080">|</span> birth      <span style="color: #808080">|</span> death      <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+--------+---------+------+------------+------------+</span>
  5. <span style="color: #808080">|</span> Fluffy   <span style="color: #808080">|</span> Harold <span style="color: #808080">|</span> cat     <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1993</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">02</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">04</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  6. <span style="color: #808080">|</span> Claws    <span style="color: #808080">|</span> Gwen   <span style="color: #808080">|</span> cat     <span style="color: #808080">|</span> m    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1994</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">03</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">17</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  7. <span style="color: #808080">|</span> Buffy    <span style="color: #808080">|</span> Harold <span style="color: #808080">|</span> dog     <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1989</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">05</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">13</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  8. <span style="color: #808080">|</span> Fang     <span style="color: #808080">|</span> Benny  <span style="color: #808080">|</span> dog     <span style="color: #808080">|</span> m    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1990</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">08</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">27</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  9. <span style="color: #808080">|</span> Bowser   <span style="color: #808080">|</span> Diane  <span style="color: #808080">|</span> dog     <span style="color: #808080">|</span> m    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1979</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">08</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">31</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1995</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">07</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">29</span> <span style="color: #808080">|</span>
  10. <span style="color: #808080">|</span> Chirpy   <span style="color: #808080">|</span> Gwen   <span style="color: #808080">|</span> bird    <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1998</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">09</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">11</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  11. <span style="color: #808080">|</span> Whistler <span style="color: #808080">|</span> Gwen   <span style="color: #808080">|</span> bird    <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1997</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">12</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">09</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  12. <span style="color: #808080">|</span> Slim     <span style="color: #808080">|</span> Benny  <span style="color: #808080">|</span> snake   <span style="color: #808080">|</span> m    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1996</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">04</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">29</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  13. <span style="color: #808080">|</span> Puffball <span style="color: #808080">|</span> Diane  <span style="color: #808080">|</span> hamster <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1999</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">03</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">30</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>       <span style="color: #808080">|</span>
  14. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+--------+---------+------+------------+------------+</span>
  15. <span style="color: #800000; font-weight: bold">9</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)


5.2 修改数据

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">UPDATE</span> pet <span style="color: #0000ff">SET</span> birth <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">1989-08-31</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">WHERE</span> name <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">Bowser</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  2. Query OK, </span><span style="color: #800000; font-weight: bold">1</span> row affected (<span style="color: #800000; font-weight: bold">0.17</span><span style="color: #000000"> sec)
  3. Rows matched: </span><span style="color: #800000; font-weight: bold">1</span>  Changed: <span style="color: #800000; font-weight: bold">1</span>  Warnings: <span style="color: #800000; font-weight: bold">0</span><span style="color: #000000">
  4. mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> birth <span style="color: #0000ff">FROM</span> pet <span style="color: #0000ff">WHERE</span> name <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">Bowser</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  5. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+</span>
  6. <span style="color: #808080">|</span> birth      <span style="color: #808080">|</span>
  7. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+</span>
  8. <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1989</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">08</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">31</span> <span style="color: #808080">|</span>
  9. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">----------+</span>
  10. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)


5.3 清空表内容

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">DELETE</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> pet;
  2. Query OK, </span><span style="color: #800000; font-weight: bold">9</span> rows affected (<span style="color: #800000; font-weight: bold">0.08</span><span style="color: #000000"> sec)
  3. mysql</span><span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span><span style="color: #000000"> pet;
  4. Empty </span><span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)

 


5.4 选择特定行

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> pet <span style="color: #0000ff">WHERE</span> name <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">Bowser</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------+-------+---------+------+------------+------------+</span>
  3. <span style="color: #808080">|</span> name   <span style="color: #808080">|</span> owner <span style="color: #808080">|</span> species <span style="color: #808080">|</span> sex  <span style="color: #808080">|</span> birth      <span style="color: #808080">|</span> death      <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------+-------+---------+------+------------+------------+</span>
  5. <span style="color: #808080">|</span> Bowser <span style="color: #808080">|</span> Diane <span style="color: #808080">|</span> dog     <span style="color: #808080">|</span> m    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1989</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">08</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">31</span> <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1995</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">07</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">29</span> <span style="color: #808080">|</span>
  6. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------+-------+---------+------+------------+------------+</span>
  7. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)

 

5.5 根据条件选择

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> pet <span style="color: #0000ff">WHERE</span> birth <span style="color: #808080">>=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">1998-1-1</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+-------+---------+------+------------+-------+</span>
  3. <span style="color: #808080">|</span> name     <span style="color: #808080">|</span> owner <span style="color: #808080">|</span> species <span style="color: #808080">|</span> sex  <span style="color: #808080">|</span> birth      <span style="color: #808080">|</span> death <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+-------+---------+------+------------+-------+</span>
  5. <span style="color: #808080">|</span> Chirpy   <span style="color: #808080">|</span> Gwen  <span style="color: #808080">|</span> bird    <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1998</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">09</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">11</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>  <span style="color: #808080">|</span>
  6. <span style="color: #808080">|</span> Puffball <span style="color: #808080">|</span> Diane <span style="color: #808080">|</span> hamster <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1999</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">03</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">30</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>  <span style="color: #808080">|</span>
  7. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+-------+---------+------+------------+-------+</span>
  8. <span style="color: #800000; font-weight: bold">2</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)

 


5.6 根据组合条件选择
5.6.1

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> pet <span style="color: #0000ff">WHERE</span> species <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">dog</span><span style="color: #ff0000">‘</span> <span style="color: #808080">AND</span> sex <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">f</span><span style="color: #ff0000">‘</span><span style="color: #000000">;
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+--------+---------+------+------------+-------+</span>
  3. <span style="color: #808080">|</span> name  <span style="color: #808080">|</span> owner  <span style="color: #808080">|</span> species <span style="color: #808080">|</span> sex  <span style="color: #808080">|</span> birth      <span style="color: #808080">|</span> death <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+--------+---------+------+------------+-------+</span>
  5. <span style="color: #808080">|</span> Buffy <span style="color: #808080">|</span> Harold <span style="color: #808080">|</span> dog     <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1989</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">05</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">13</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>  <span style="color: #808080">|</span>
  6. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+--------+---------+------+------------+-------+</span>
  7. <span style="color: #800000; font-weight: bold">1</span> row <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.04</span> sec)

 

 

5.6.2

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span> <span style="color: #0000ff">FROM</span> pet <span style="color: #0000ff">WHERE</span>
  2.     <span style="color: #808080">-></span> (species <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">cat</span><span style="color: #ff0000">‘</span> <span style="color: #808080">AND</span> sex <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">m</span><span style="color: #ff0000">‘</span><span style="color: #000000">)
  3.     </span><span style="color: #808080">-></span> <span style="color: #808080">OR</span> (species <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">dog</span><span style="color: #ff0000">‘</span> <span style="color: #808080">AND</span> sex <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">f</span><span style="color: #ff0000">‘</span><span style="color: #000000">);
  4. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+--------+---------+------+------------+-------+</span>
  5. <span style="color: #808080">|</span> name  <span style="color: #808080">|</span> owner  <span style="color: #808080">|</span> species <span style="color: #808080">|</span> sex  <span style="color: #808080">|</span> birth      <span style="color: #808080">|</span> death <span style="color: #808080">|</span>
  6. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+--------+---------+------+------------+-------+</span>
  7. <span style="color: #808080">|</span> Claws <span style="color: #808080">|</span> Gwen   <span style="color: #808080">|</span> cat     <span style="color: #808080">|</span> m    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1994</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">03</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">17</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>  <span style="color: #808080">|</span>
  8. <span style="color: #808080">|</span> Buffy <span style="color: #808080">|</span> Harold <span style="color: #808080">|</span> dog     <span style="color: #808080">|</span> f    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1989</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">05</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">13</span> <span style="color: #808080">|</span> <span style="color: #0000ff">NULL</span>  <span style="color: #808080">|</span>
  9. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">-----+--------+---------+------+------------+-------+</span>
  10. <span style="color: #800000; font-weight: bold">2</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)

注意：AND优先级高于OR。

5.7 选择特定列
5.7.1

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> name, birth <span style="color: #0000ff">FROM</span><span style="color: #000000"> pet;
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+------------+</span>
  3. <span style="color: #808080">|</span> name     <span style="color: #808080">|</span> birth      <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+------------+</span>
  5. <span style="color: #808080">|</span> Fluffy   <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1993</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">02</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">04</span> <span style="color: #808080">|</span>
  6. <span style="color: #808080">|</span> Claws    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1994</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">03</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">17</span> <span style="color: #808080">|</span>
  7. <span style="color: #808080">|</span> Buffy    <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1989</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">05</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">13</span> <span style="color: #808080">|</span>
  8. <span style="color: #808080">|</span> Fang     <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1990</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">08</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">27</span> <span style="color: #808080">|</span>
  9. <span style="color: #808080">|</span> Bowser   <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1989</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">08</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">31</span> <span style="color: #808080">|</span>
  10. <span style="color: #808080">|</span> Chirpy   <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1998</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">09</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">11</span> <span style="color: #808080">|</span>
  11. <span style="color: #808080">|</span> Whistler <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1997</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">12</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">09</span> <span style="color: #808080">|</span>
  12. <span style="color: #808080">|</span> Slim     <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1996</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">04</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">29</span> <span style="color: #808080">|</span>
  13. <span style="color: #808080">|</span> Puffball <span style="color: #808080">|</span> <span style="color: #800000; font-weight: bold">1999</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">03</span><span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">30</span> <span style="color: #808080">|</span>
  14. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">--------+------------+</span>
  15. <span style="color: #800000; font-weight: bold">9</span> rows <span style="color: #808080">in</span> <span style="color: #0000ff">set</span> (<span style="color: #800000; font-weight: bold">0.00</span> sec)


5.7.2

  1. mysql<span style="color: #808080">></span> <span style="color: #0000ff">SELECT</span> owner <span style="color: #0000ff">FROM</span><span style="color: #000000"> pet;
  2. </span><span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------+</span>
  3. <span style="color: #808080">|</span> owner  <span style="color: #808080">|</span>
  4. <span style="color: #808080">+</span><span style="color: #008080">--</span><span style="color: #008080">------+</span>
  5. <span style="color: #808080">|</span> Harold <span style="color: #808080">|</span>
  6. <span style="color: #808080">|</span> Gwen   <span style="color: #808080">|</span>
  7. <span style="color: #808080">|</span> Harold <span style="color: #808080">|</span>
  8. <span style="color: #808080">|</span> Benny  <span style="color: #808080">|</span>
  9. <span style="color: #808080">|</span> Diane  <span style="color: #808080">|</span>
  10. <span style="color: #808080">|</span> Gwen   <span style="co                    </div>
  11.                   
  12.  
  13.                     <div class=" "="">
  14.                         <ul class="m-news-opt fix">
  15.                             <li class="opt-item">
  16.                                 <a href="/sql_question-409320.html" target="_blank"><p>< 上一篇</p><p class="ellipsis">JDBC【PreparedStatment、批处理、处理二进制、自动主键、调用存储过程、函数】</p></a>
  17.                             </li>
  18.                             <li class="opt-item ta-r">
  19.                                  <a href="/sql_question-409322.html" target="_blank"><p>下一篇 ></p><p class="ellipsis">MySQL多表查询</p></a>
  20.                             </li>
  21.                         </ul>
  22.                     </span>

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！