sql_samples.htm

来自「Absolute Database 5.12 src. Absolute Da」· HTM 代码 · 共 620 行 · 第 1/3 页

        insert into MyTable (val1, val2) values (7,'Hello');<br>
        alter table MyTable add (val3 float);<br>
        select * from MyTable order by ID val2 desc;</td>
      <td> This sample illustrates use of ALTER TABLE statement with ADD option.<br>
        This SQL script contains eight queries. First one deletes table named 
        &quot;MyTable&quot;. The second creates this table with 3 fields. Queries 
        3 - 6 insert some data into the table.<br>
        The seventh query adds a new column named &quot;val3&quot; of type float 
        to the table. Query #8 retrieves all rows from this table.</td>
    </tr>
    <tr> 
      <td> Alter_Table_Memory.sql</td>
      <td> drop table memory MyTable;<br>
        create table memory MyTable (ID AutoInc, val1 Integer, val2 string(10));<br>
        insert into memory MyTable (val1, val2) values (2,'2');<br>
        insert into memory MyTable (val1, val2) values (4,'3');<br>
        insert into memory MyTable (val1, val2) values (5,'9');<br>
        insert into memory MyTable (val1, val2) values (7,'Hello');<br>
        alter table memory MyTable add (val3 float);<br>
        select * from memory MyTable order by ID val2 desc</td>
      <td> This sample illustrates use of ALTER TABLE statement with MEMORY option.<br>
        This SQL script contains eight queries. First one deletes table named 
        &quot;MyTable&quot;. The second creates this table IN-MEMORY with 3 fields. 
        Queries 3 - 6 insert some data into the table. The seventh query adds 
        a new column named &quot;val3&quot; of type float to the table. Query 
        #8 retrieves all rows from this table.</td>
    </tr>
    <tr> 
      <td> Alter_Table_Modify.sql</td>
      <td> drop table MyTable;<br>
        create table MyTable (ID AutoInc, val1 Integer, val2 string(10));<br>
        insert into MyTable (val1, val2) values (2,'2');<br>
        insert into MyTable (val1, val2) values (4,'3');<br>
        insert into MyTable (val1, val2) values (5,'9');<br>
        insert into MyTable (val1, val2) values (7,'Hello');<br>
        alter table MyTable mofify (val1 string(5));<br>
        select * from MyTable order by ID val2 desc;</td>
      <td> This sample illustrates use of ALTER TABLE statement with MODIFY option.<br>
        This SQL script contains eight queries. First one deletes table named 
        &quot;MyTable&quot;. The second creates this table with 2 fields. Queries 
        3 - 6 insert some data into the table.<br>
        The seventh query modifies column named &quot;val1&quot; so that it turns 
        to be string. Query #8 retrieves all rows from this table.</td>
    </tr>
    <tr> 
      <td> Create_Drop_Table.sql</td>
      <td> drop table developers; <br>
        create table developers<br>
        ( id AutoInc, Code Integer, Name varchar(20), Birthday Date, Photo Graphic 
        <br>
        BlobCompressionMode 9 <br>
        BlobBlockSize 1024 <br>
        BlobCompressionAlgorithm ZLib );<br>
        select * from developers; </td>
      <td> This sample demonstrates use of CREATE TABLE and DROP TABLE statements. 
        This SQL script contains three queries. First one deletes table named 
        "developers". The second creates this table with 5 fields and 3 database 
        options. The third query retrieves all rows from this table (of course, 
        the table is empty at this point).</td>
    </tr>
    <tr> 
      <td> Create_Index.sql</td>
      <td> drop table developers; <br>
        create table developers ( id AutoInc, Code Integer, Name varchar(20) );<br>
        insert into developers (code, name) values (2, 'Janet'); <br>
        create unique index UniqueName ON developers (name desc nocase, code); 
        select * from developers; </td>
      <td> This sample illustrates use of CREATE INDEX statement. This SQL script 
        contains five queries. First one deletes table named "developers". The 
        second creates this table with 3 fields. The third query inserts some 
        data into the table. The fourth creates index so that "name"+"code" combination 
        must be unique, for field "name" sorting order is descending and is case-insensitive. 
        The new index is given name "UniqueName". The fifth query retrieves all 
        rows from this table.</td>
    </tr>
    <tr> 
      <td> Create_InMemory_Table.sql</td>
      <td> drop table memory developers; <br>
        create table memory developers (id AutoInc, code Integer, name varchar(20));<br>
        insert into memory developers (code, name) values (5, 'Bob');<br>
        insert into memory developers (code, name) values (7, 'Jeck');<br>
        insert into memory developers (code, name) values (2, 'Carol');<br>
        insert into memory developers (code, name) values (5, 'Harry');<br>
        insert into memory developers (code, name) values (4, 'Sam');<br>
        insert into memory developers (code, name) values (9, 'Chris');<br>
        select * from memory developers order by name desc, code, id; </td>
      <td> This sample illustrates use of CREATE TABLE statement with MEMORY option. 
        This SQL script contains nine queries. First one deletes table named "developers". 
        The second creates this table in-memory with 3 fields. Queries 3-8 insert 
        some data into the table. The ninth query retrieves all rows from this 
        table.</td>
    </tr>
    <tr> 
      <td> Delete.sql</td>
      <td> drop table developers;<br>
        create table developers (id AutoInc, code Integer, name varchar(20));<br>
        insert into developers (code, name) values (5, 'Bob');<br>
        insert into developers (code, name) values (7, 'Jeck'); <br>
        delete from developers where name='Bob'; <br>
        select * from developers order by name desc, code, id </td>
      <td> This sample illustrates use of DELETE keyword. This SQL script contains 
        six queries. First one deletes table named "developers". The second creates 
        this table with 3 fields. Queries 3 and 4 insert some data into the table. 
        The next query deletes all strings in the table that have 'Bob' as value 
        of "name" field. The sixth query retrieves all rows from this table.</td>
    </tr>
    <tr> 
      <td> Drop_Index.sql</td>
      <td> drop table developers;<br>
        create table developers ( id AutoInc, Code Integer, Name varchar(20) );<br>
        insert into developers (code, name) values (2, 'Janet');<br>
        create unique index UniqueName ON developers (name desc nocase, code); 
        drop index developers.UniqueName;<br>
        select * from developers; </td>
      <td> This sample illustrates use of DROP INDEX statement. This SQL script 
        contains six queries. First one deletes table named "developers". The 
        second creates this table with 3 fields. Query #3 inserts some data into 
        the table. The fourth creates index so that "name"+"code" combination 
        must be unique, for field "name" sorting order is descending and is case-insensitive. 
        The new index is given name "UniqueName". The fifth query deletes index 
        "UniqueName" from table "developers". The sixth query retrieves all rows 
        from this table.</td>
    </tr>
    <tr> 
      <td> Insert_Into.sql</td>
      <td> drop table developers; <br>
        create table developers (id AutoInc, code Integer, name varchar(20));<br>
        insert into developers (code, name) values (5, 'Bob'); <br>
        insert into developers (code, name) values (7, 'Jeck');<br>
        insert into developers (code, name) values (2, 'Carol'); <br>
        insert into developers values (10, 5, 'Harry');<br>
        insert into developers (code, name) values (4, 'Sam'); <br>
        insert into developers (code, name) values (9, 'Chris');<br>
        select * from developers order by name desc, code, id; </td>
      <td> This sample illustrates use of INSERT statement. This SQL script contains 
        nine queries. First one deletes table named "developers". The second creates 
        this table with 3 fields. Queries 3-8 insert some data into the table. 
        The ninth query retrieves all rows from this table. Notice that you don't 
        have to specify column names as long as you let parameters follow the 
        order of columns in the table (line 6).</td>
    </tr>
    <tr> 
      <td> Select_Into.sql</td>
      <td> select distinct FirstName into NewTable from employee </td>
      <td> This sample illustrates use of SELECT INTO statement. The query returns 
        all names from the field "FirstName" excluding duplicates and places it 
        inside a new table giving it name "NewTable".</td>
    </tr>
    <tr> 
      <td> Transactions_Flush.sql</td>
      <td> drop table MyTable;<br>
        create table MyTable (ID AutoInc, val1 Integer, val2 string(10));<br>
        insert into MyTable (val1, val2) values (2,'2');<br>
        insert into MyTable (val1, val2) values (4,'3');<br>
        start transaction;<br>
        insert into MyTable (val1, val2) values (5,'Flush1');<br>
        insert into MyTable (val1, val2) values (7,'Flush2');<br>
        commit flush;<br>
        select * from MyTable;</td>
      <td> <p>This sample illustrates use of FLUSH option in COMMIT statement.<br>
          This SQL script contains 9 queries. Table &quot;test&quot; is deleted, 
          then recreated and filled with data. A transaction is started, data 
          is inserted, then changes are comitted and immediately flushed to disk. 
          At last you can see what data is contained in the table after all queries.</p></td>
    </tr>
    <tr> 
      <td> Transactions_SQL.sql</td>
      <td> drop table MyTable;<br>
        create table MyTable (ID AutoInc, val1 Integer, val2 string(10));<br>
        insert into MyTable (val1, val2) values (2,'Start');<br>
        start transaction;<br>
        insert into MyTable (val1, val2) values (4,'Rollback');<br>
        rollback;<br>
        start transaction;<br>
        insert into MyTable (val1, val2) values (5,'Commit');<br>
        commit;<br>
        select * from MyTable;<br> </td>
      <td> This sample illustrates use of TRANSACTIONS via SQL. Notice that TAbsDatabase 
        supports transactions as class methods so you might find it better to 
        use another way.<br>
        This SQL script contains 14 queries. Table &quot;MyTable&quot; is deleted, 
        then recreated and filled with data. A transaction is started, data is 
        inserted, then this last action is cancelled.<br>
        Another transaction is started. data is inserted, then changes are comitted. 
        At last you can see what data is contained in the table after all queries.</td>
    </tr>
    <tr> 
      <td> Update.sql</td>
      <td> drop table MyTable;<br>
        create table MyTable (ID AutoInc, val1 Integer, val2 string(10));<br>
        insert into MyTable (val1, val2) values (2,'2');<br>
        insert into MyTable (val1, val2) values (4,'3');<br>
        insert into MyTable (val1, val2) values (5,'9');<br>
        insert into MyTable (val1, val2) values (7,'Hello');<br>
        update MyTable set val2='Many' where val1&gt;2;<br>
        select * from MyTable;</td>
      <td> This sample illustrates use of UPDATE statement.<br>
        This SQL script contains 8 queries. Table &quot;MyTable&quot; is deleted, 
        then recreated and filled with data. The seventh query sets field &quot;val2&quot; 
        to string 'Many' where &quot;val1&quot; is more than 2. At last you can 
        see what data is contained in the table after all queries.</td>
    </tr>
</table>
</body>
</html>