15条SQLite3语句

转自：http://www.thegeekstuff.com/2012/09/sqlite-command-examples/

SQLite3 is very lightweight SQL database which focuses on simplicity more than anything else. This is a self-contained serverless database engine,which is very simple to install and use.

While most of the commands in the SQLite are similar to SQL commands of other datbases like MySQL and ORACLE,there are some SQLite SQL commands that are different.

This article explains all the basic SQL commands that you need to know to use the SQLite database effectively.

If you don’t have sqlite installed,execute “yum install sqlite” to install it. You can alsoinstall SQLite database from sourceto get the latest version.

1. Create a SQLite Database (and a Table)

First,let us understand how create a SQLite database with couple of tables,populate some data,and view those records.

The following example creates a database called employee.db. This also creates an employee table with 3 columns (id,name and title),and a department table in the company.db database. We’ve purposefully missed the deptid column in the employee table. We’ll see how to add that later.

#sqlite3company.db
sqlite>createtableemployee(empidinteger,namevarchar(20),titlevarchar(10));
sqlite>createtabledepartment(deptidinteger,locationvarchar(10));
sqlite>.quit

Note: To exit from the SQLite commandline “sqlite>” prompt,type “.quit” as shown above.

A SQLite database is nothing but a file that gets created under your current directory as shown below.

#ls-lcompany.db-rw-r--r--.1rootroot3072Sep1911:21company.db

2. Insert Records

The following example populates both employee and department table with some sample records.

You can execute all the insert statements from the sqlite command line,or you can add those commands into a file and execute the file as shown below.

#viinsert-data.sql insertintoemployeevalues(101,'JohnSmith','CEO'); insertintoemployeevalues(102,'RajReddy','Sysadmin'); insertintoemployeevalues(103,'JasonBourne','Developer'); insertintoemployeevalues(104,'JaneSmith','SaleManager'); insertintoemployeevalues(105,'RitaPatel','DBA'); insertintodepartmentvalues(1,'Sales','LosAngeles'); insertintodepartmentvalues(2,'Technology','SanJose'); insertintodepartmentvalues(3,'Marketing','LosAngeles');

The following will execute all the commands from the insert-data.sql in the company.db database

#sqlite3company.db<insert-data.sql

3. View Records

Once you’ve inserted the records,view it using select command as shown below.

#sqlite3company.db
sqlite>select*fromemployee;
101|JohnSmith|CEO
102|RajReddy|Sysadmin
103|JasonBourne|Developer
104|JaneSmith|SaleManager
105|RitaPatel|DBA
sqlite>select*fromdepartment;
1|Sales|LosAngeles
2|Technology|SanJose
3|Marketing|LosAngeles

4. Rename a Table

The following example renames department table to dept using the alter table command.

sqlite>altertabledepartmentrenametodept;

5. Add a Column to an Existing Table

The following examples adds deptid column to the existing employee table;

sqlite>altertableemployeeaddcolumndeptidinteger;

Update the department id for the employees using update command as shown below.

updateemployeesetdeptid=3whereempid=101;
updateemployeesetdeptid=2whereempid=102;
updateemployeesetdeptid=2whereempid=103;
updateemployeesetdeptid=1whereempid=104;
updateemployeesetdeptid=2whereempid=105;

Verify that the deptid is updated properly in the employee table.

sqlite>select*fromemployee;
101|JohnSmith|CEO|3
102|RajReddy|Sysadmin|2
103|JasonBourne|Developer|2
104|JaneSmith|SaleManager|1
105|RitaPatel|DBA|2

6. View all Tables in a Database

Execute the following command to view all the tables in the current database. The folowing example shows that there are two tables in the current database.

sqlite>.tablesdeptemployee

7. Create an Index

The following example creates an unique index called empidx on the empid field of employee table.

sqlite>createuniqueindexempidxonemployee(empid);

Once an unique index is created,if you try to add another record with an empid that already exists,you’ll get an error as shown below.

sqlite>insertintoemployeevalues(101,'JamesBond','SecretAgent',1);
Error:constraintfailed

8. Create a Trigger

For this example,first add a date column called “updatedon” on employee table.

sqlite>altertableemployeeaddcolumnupdatedondate;

Next,create a file that has the trigger definition. The following trigger will update the “updatedon” date column with the current timestamp whenever you perform an update on this table.

#viemployee_update_trg.sql
createtriggeremployee_update_trgafterupdateonemployee
begin
updateemployeesetupdatedon=datetime('NOW')whererowid=new.rowid;
end;

Create the trigger on the company.db database as shown below.

#sqlite3company.db<employee_update_trg.sql

Now anytime you update any record in the employee table,the “updatedon” date column will be updated with the current timestamp as shown below. The following example updates the “updatedon” timestamp for empid 104 through trigger.

#sqlite3company.db
sqlite>updateemployeesettitle='SalesManager'whereempid=104;
sqlite>select*fromemployee;
101|JohnSmith|CEO|3|
102|RajReddy|Sysadmin|2|
103|JasonBourne|Developer|2|
104|JaneSmith|SalesManager|1|2012-09-1518:29:28105|RitaPatel|DBA|2|

9. Create a View

The following example creates a view called “empdept”,which combines fields from both employee and dept table.

sqlite>createviewempdeptasselectempid,e.name,title,d.name,locationfromemployeee,deptdwheree.deptid=d.deptid;

Now you can execute select command on this view just like a regular table.

sqlite>select*fromempdept;
101|JohnSmith|CEO|Marketing|LosAngeles
102|RajReddy|Sysadmin|Technology|SanJose
103|JasonBourne|Developer|Technology|SanJose
104|JaneSmith|SalesManager|Sales|LosAngeles
105|RitaPatel|DBA|Technology|SanJose

After creating a view,if you execute .tables,you’ll also see the view name along with the tables.

sqlite>.tables
deptempdeptemployee

10. SQLite Savepoint,Rollback,Commit

Currently dept table has the following 3 records.

sqlite>select*fromdept;
1|Sales|LosAngeles
2|Technology|SanJose
3|Marketing|LosAngeles

Now,create a savepoint called “major”,and perform some transactions on the dept table. As you see below,we’ve added two records,deleted one record,after creating a savepoint called “major”.

sqlite>savepointmajor;
sqlite>insertintodeptvalues(4,'HR','LosAngeles');
sqlite>insertintodeptvalues(5,'Finance','SanJose');
sqlite>deletefromdeptwheredeptid=1;
sqlite>select*fromdept;
2|Technology|SanJose
3|Marketing|LosAngeles
4|HR|LosAngeles
5|Finance|SanJose

Now for some reason,if we don’t want the above transactions,we can rollback the changes to a particular savepoint. In this example,we are rolling back all the changes we’ve made after the “major” savepoint.

sqlite>rollbacktosavepointmajor;
sqlite>select*fromdept;
1|Sales|LosAngeles
2|Technology|SanJose
3|Marketing|LosAngeles

If you don’t want your savepoints anymore,you can erase it using release command.

sqlite>releasesavepointmajor;

11. Additional Date Functions

By default,the date columns values displayed in UTC time. To display in the local time,use the datetime command on the date column as shown below.

sqlite>selectempid,datetime(updatedon,'localtime')fromemployee;
104|2012-09-1511:29:28

You can also use strftime to display the date column in various output.

The following are the possible modifers you can use in the strftime function.
 
 
 
%d day of month: 00
 
 
%f fractional seconds: SS.SSS
 
 
%H hour: 00-24
 
 
%j day of year: 001-366
 
 
%J Julian day number
 
 
%m month: 01-12
 
 
%M minute: 00-59
 
 
%s seconds since 1970-01-01
 
 
%S seconds: 00-59
 
 
%w day of week 0-6 with Sunday==0
 
 
%W week of year: 00-53
 
 
%Y year: 0000-9999
 
 
%% %
 
 
12. Dropping Objects
 
You can drop all the above created objects using the appropriate drop command as shown below.
 
Since we are dropping objects for testing purpose,copy the company.db to a test.db and try these commands on the test.db
 
#cpcompany.dbtest.db#
sqlite3test.db
sqlite>.tablesdeptempdeptemployee
sqlite>dropindexempidx;
sqlite>droptriggeremployee_update_trg;
sqlite>dropviewempdept;sqlite>
droptableemployee;
sqlite>droptabledept;
 
All the tables and views from the test.db are now deleted.
 
sqlite>.tables
sqlite>
 
Note: When you drop a table all the indexes and triggers for that table are also dropped.
 
13. Operators
 
The following are the possible operators you can use in SQL statements.
 
 
 
||
 
 
* / %
 
 
+ -
 
 
<< >> & |
 
 
< >=
 
 
= == != <> IS IS NOT IN LIKE GLOB MATCH REGEXP
 
 
AND OR
 
 
For example:
 
sqlite>select*fromemployeewhereempid>=102andempidselect*fromdeptwherelocationlike'Los%';
1|Sales|LosAngeles
3|Marketing|LosAngeles
 
14. Explain Query Plan
 
Execute “explain query plan”,to get information about the table that is getting used in a query or view. This is very helpful when you are debugging a complex query with multiple joins on several tables.
 
sqlite>explainqueryplanselect*fromempdept;0|0|TABLEemployeeASe1|1|TABLEdeptASd
 
For a detailed trace,just execute “explain” followed by the query to get more performance data on the query. This is helpful for debugging purpose when the query is slow.
 
sqlite>explainselectempid,updatedon)fromemployee;
0|Trace|0|0|0||00|
1|Goto|0|12|0||00|
2|OpenRead|0|2|0|4|00|
3|Rewind|0|10|0||00|
4|Column|0|0|1||00|
5|String8|0|3|0|%d-%m-%Y%w%W|00|
6|Column|0|3|4||00|
7|Function|1|3|2|strftime(-1)|02|
8|ResultRow|1|2|0||00|
9|Next|0|4|0||01|
10|Close|0|0|0||00|
11|Halt|0|0|0||00|
12|Transaction|0|0|0||00|
13|VerifyCookie|0|19|0||00|
14|TableLock|0|2|0|employee|00|
15|Goto|0|2|0||00|
 
15. Attach and Detach Database
 
When you have multiple database,you can use attach command to execute queries across database.
 
For example,if you have two database that has the same table name with different data,you can create a union query across the database to view the combined records as explained below.
 
In this example,we have two company database (company1.db and company2.db). From the sqlite prompt,attach both these database by giving alias as c1 and c2 as shown below.
 
#sqlite3sqlite>attachdatabase'company1.db'asc1;
sqlite>attachdatabase'company2.db'asc2;
 
Execute “.database” command which will display all the attached databases.
 
sqlite>.database
seqnamefile
------------------------------------
0main
2c1/root/company1.db
3c2/root/company2.db
 
 
After attaching a database,from the current sqlite session,if you want to detach it,use detach command as shown below.
 
sqlite> detach c1; sqlite> .databaseseq name file --- --------------- ----------------- 0 main 2 c2 /root/company2.db
                        
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!