使用一个表来填充另一个表

您可以通过在一个有一组字段的表上使用 select 语句，填充数据到另一个表中。下面是语法：

<span class="pln">INSERT INTO first_table_name <span class="pun">[(<span class="pln">column1<span class="pun">,<span class="pln"> column2<span class="pun">,<span class="pln"> <span class="pun">...<span class="pln"> columnN<span class="pun">)]<span class="pln"> 
   SELECT column1<span class="pun">,<span class="pln"> column2<span class="pun">,<span class="pln"> <span class="pun">...<span class="pln">columnN 
   FROM second_table_name
   <span class="pun">[<span class="pln">WHERE condition<span class="pun">];</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

您暂时可以先跳过上面的语句，可以先学习后面章节中介绍的 SELECT 和 WHERE 子句。

sqlite 运算符

定义/例子详见 : http://www.runoob.com/sqlite/sqlite-operators.html

• 算术运算符

• 比较运算符

• 逻辑运算符

• 位运算符

SQLite Update 语句

实例

假设 COMPANY 表有以下记录：

<span class="pln">ID          NAME        AGE         ADDRESS     SALARY
<span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">
<span class="lit">1<span class="pln">           <span class="typ">Paul<span class="pln">        <span class="lit">32<span class="pln">          <span class="typ">California<span class="pln">  <span class="lit">20000.0<span class="pln">
<span class="lit">2<span class="pln">           <span class="typ">Allen<span class="pln">       <span class="lit">25<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">15000.0<span class="pln">
<span class="lit">3<span class="pln">           <span class="typ">Teddy<span class="pln">       <span class="lit">23<span class="pln">          <span class="typ">Norway<span class="pln">      <span class="lit">20000.0<span class="pln">
<span class="lit">4<span class="pln">           <span class="typ">Mark<span class="pln">        <span class="lit">25<span class="pln">          <span class="typ">Rich<span class="pun">-<span class="typ">Mond<span class="pln">   <span class="lit">65000.0<span class="pln">
<span class="lit">5<span class="pln">           <span class="typ">David<span class="pln">       <span class="lit">27<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">85000.0<span class="pln">
<span class="lit">6<span class="pln">           <span class="typ">Kim<span class="pln">         <span class="lit">22<span class="pln">          <span class="typ">South<span class="pun">-<span class="typ">Hall<span class="pln">  <span class="lit">45000.0<span class="pln">
<span class="lit">7<span class="pln">           <span class="typ">James<span class="pln">       <span class="lit">24<span class="pln">          <span class="typ">Houston<span class="pln">     <span class="lit">10000.0</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

下面是一个实例，它会更新 ID 为 6 的客户地址：

<span class="pln">sqlite<span class="pun">><span class="pln"> UPDATE COMPANY SET ADDRESS <span class="pun">=<span class="pln"> <span class="str">‘Texas‘<span class="pln"> WHERE ID <span class="pun">=<span class="pln"> <span class="lit">6<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span>

现在，COMPANY 表有以下记录：

<span class="pln">ID          NAME        AGE         ADDRESS     SALARY
<span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">
<span class="lit">1<span class="pln">           <span class="typ">Paul<span class="pln">        <span class="lit">32<span class="pln">          <span class="typ">California<span class="pln">  <span class="lit">20000.0<span class="pln">
<span class="lit">2<span class="pln">           <span class="typ">Allen<span class="pln">       <span class="lit">25<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">15000.0<span class="pln">
<span class="lit">3<span class="pln">           <span class="typ">Teddy<span class="pln">       <span class="lit">23<span class="pln">          <span class="typ">Norway<span class="pln">      <span class="lit">20000.0<span class="pln">
<span class="lit">4<span class="pln">           <span class="typ">Mark<span class="pln">        <span class="lit">25<span class="pln">          <span class="typ">Rich<span class="pun">-<span class="typ">Mond<span class="pln">   <span class="lit">65000.0<span class="pln">
<span class="lit">5<span class="pln">           <span class="typ">David<span class="pln">       <span class="lit">27<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">85000.0<span class="pln">
<span class="lit">6<span class="pln">           <span class="typ">Kim<span class="pln">         <span class="lit">22<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">45000.0<span class="pln">
<span class="lit">7<span class="pln">           <span class="typ">James<span class="pln">       <span class="lit">24<span class="pln">          <span class="typ">Houston<span class="pln">     <span class="lit">10000.0</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span><br><br>

如果您想修改 COMPANY 表中 ADDRESS 和 SALARY 列的所有值，则不需要使用 WHERE 子句，UPDATE 查询如下：

<span class="pln">sqlite<span class="pun">><span class="pln"> UPDATE COMPANY SET ADDRESS <span class="pun">=<span class="pln"> <span class="str">‘Texas‘<span class="pun">,<span class="pln"> SALARY <span class="pun">=<span class="pln"> <span class="lit">20000.00<span class="pun">;</span></span></span></span></span></span></span></span></span></span></span></span>

现在，COMPANY 表有以下记录：

<span class="pln">ID          NAME        AGE         ADDRESS     SALARY
<span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">
<span class="lit">1<span class="pln">           <span class="typ">Paul<span class="pln">        <span class="lit">32<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">20000.0<span class="pln">
<span class="lit">2<span class="pln">           <span class="typ">Allen<span class="pln">       <span class="lit">25<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">20000.0<span class="pln">
<span class="lit">3<span class="pln">           <span class="typ">Teddy<span class="pln">       <span class="lit">23<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">20000.0<span class="pln">
<span class="lit">4<span class="pln">           <span class="typ">Mark<span class="pln">        <span class="lit">25<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">20000.0<span class="pln">
<span class="lit">5<span class="pln">           <span class="typ">David<span class="pln">       <span class="lit">27<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">20000.0<span class="pln">
<span class="lit">6<span class="pln">           <span class="typ">Kim<span class="pln">         <span class="lit">22<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">20000.0<span class="pln">
<span class="lit">7<span class="pln">           <span class="typ">James<span class="pln">       <span class="lit">24<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">20000.0</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

SQLite Delete 语句

实例

假设 COMPANY 表有以下记录：

<span class="pln">ID          NAME        AGE         ADDRESS     SALARY
<span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">
<span class="lit">1<span class="pln">           <span class="typ">Paul<span class="pln">        <span class="lit">32<span class="pln">          <span class="typ">California<span class="pln">  <span class="lit">20000.0<span class="pln">
<span class="lit">2<span class="pln">           <span class="typ">Allen<span class="pln">       <span class="lit">25<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">15000.0<span class="pln">
<span class="lit">3<span class="pln">           <span class="typ">Teddy<span class="pln">       <span class="lit">23<span class="pln">          <span class="typ">Norway<span class="pln">      <span class="lit">20000.0<span class="pln">
<span class="lit">4<span class="pln">           <span class="typ">Mark<span class="pln">        <span class="lit">25<span class="pln">          <span class="typ">Rich<span class="pun">-<span class="typ">Mond<span class="pln">   <span class="lit">65000.0<span class="pln">
<span class="lit">5<span class="pln">           <span class="typ">David<span class="pln">       <span class="lit">27<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">85000.0<span class="pln">
<span class="lit">6<span class="pln">           <span class="typ">Kim<span class="pln">         <span class="lit">22<span class="pln">          <span class="typ">South<span class="pun">-<span class="typ">Hall<span class="pln">  <span class="lit">45000.0<span class="pln">
<span class="lit">7<span class="pln">           <span class="typ">James<span class="pln">       <span class="lit">24<span class="pln">          <span class="typ">Houston<span class="pln">     <span class="lit">10000.0</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

下面是一个实例，它会删除 ID 为 7 的客户：

<span class="pln">sqlite<span class="pun">><span class="pln"> DELETE FROM COMPANY WHERE ID <span class="pun">=<span class="pln"> <span class="lit">7<span class="pun">;</span></span></span></span></span></span></span>

现在，COMPANY 表有以下记录：

<span class="pln">ID          NAME        AGE         ADDRESS     SALARY
<span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">  <span class="pun">----------<span class="pln">
<span class="lit">1<span class="pln">           <span class="typ">Paul<span class="pln">        <span class="lit">32<span class="pln">          <span class="typ">California<span class="pln">  <span class="lit">20000.0<span class="pln">
<span class="lit">2<span class="pln">           <span class="typ">Allen<span class="pln">       <span class="lit">25<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">15000.0<span class="pln">
<span class="lit">3<span class="pln">           <span class="typ">Teddy<span class="pln">       <span class="lit">23<span class="pln">          <span class="typ">Norway<span class="pln">      <span class="lit">20000.0<span class="pln">
<span class="lit">4<span class="pln">           <span class="typ">Mark<span class="pln">        <span class="lit">25<span class="pln">          <span class="typ">Rich<span class="pun">-<span class="typ">Mond<span class="pln">   <span class="lit">65000.0<span class="pln">
<span class="lit">5<span class="pln">           <span class="typ">David<span class="pln">       <span class="lit">27<span class="pln">          <span class="typ">Texas<span class="pln">       <span class="lit">85000.0<span class="pln">
<span class="lit">6<span class="pln">           <span class="typ">Kim<span class="pln">         <span class="lit">22<span class="pln">          <span class="typ">South<span class="pun">-<span class="typ">Hall<span class="pln">  <span class="lit">45000.0</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span>

如果您想要从 COMPANY 表中删除所有记录，则不需要使用 WHERE 子句，DELETE 查询如下：

<span class="pln">sqlite<span class="pun">><span class="pln"> DELETE FROM COMPANY<span class="pun">;</span></span></span></span>

现在，COMPANY 表中没有任何的记录，因为所有的记录已经通过 DELETE 语句删除。

SQLite Like 子句

实例

下面一些实例演示了 带有 ‘%‘ 和 ‘_‘ 运算符的 LIKE 子句不同的地方：

[QT][SQL]sql学习记录3_sqlite 使用

标签：.com   定义   header   通过   bsp   rom   学习记录   prim   res