sqlite3 常用命令
maxos下启动sqlite数据库直接启动终端输入sqlite3即可1. 创建数据库、表的语句:
to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:
$ sqlite3 ex1SQLite version 3.6.11Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite> create table tbl1(one varchar(10), two smallint);sqlite> insert into tbl1 values('hello!',10);sqlite> insert into tbl1 values('goodbye', 20);sqlite> select * from tbl1;hello!|10goodbye|20sqlite>
注意每个sql命令的结尾处务必加上分号！
2. 接上面的注意事项：
Make sure you type a semicolon at the end of each SQL command! The sqlite3 program looks for a semicolon to know when your SQL command is complete. If you omit the semicolon, sqlite3 will give you a continuation prompt and wait for you to enter more text to be added to the current SQL command. This feature allows you to enter SQL commands that span multiple lines. For example:
sqlite> CREATE TABLE tbl2 ( ...> f1 varchar(30) primary key, ...> f2 text, ...> f3 real ...> );sqlite>
3. sqlite特殊命令：
For a listing of the available dot commands, you can enter ".help" at any time. For example:
sqlite> .help.backup ?DB? FILE Backup DB (default "main") to FILE.bail ON|OFF Stop after hitting an error.Default OFF.databases List names and files of attached databases.dump ?TABLE? ... Dump the database in an SQL text format.echo ON|OFF Turn command echo on or off.exit Exit this program.explain ON|OFF Turn output mode suitable for EXPLAIN on or off..genfkey ?OPTIONS? Options are: --no-drop: Do not drop old fkey triggers. --ignore-errors: Ignore tables with fkey errors --exec: Execute generated SQL immediately See file tool/genfkey.README in the source distribution for further information..header(s) ON|OFF Turn display of headers on or off.help Show this message.import FILE TABLE Import data from FILE into TABLE.indices TABLE Show names of all indices on TABLE.iotrace FILE Enable I/O diagnostic logging to FILE.load FILE ?ENTRY? Load an extension library.mode MODE ?TABLE? Set output mode where MODE is one of: csv Comma-separated values column Left-aligned columns.(See .width) html HTML <table> code insert SQL insert statements for TABLE line One value per line list Values delimited by .separator string tabs Tab-separated values tcl TCL list elements.nullvalue STRING Print STRING in place of NULL values.output FILENAME Send output to FILENAME.output stdout Send output to the screen.prompt MAIN CONTINUEReplace the standard prompts.quit Exit this program.read FILENAME Execute SQL in FILENAME.restore ?DB? FILE Restore content of DB (default "main") from FILE.schema ?TABLE? Show the CREATE statements.separator STRING Change separator used by output mode and .import.show Show the current values for various settings.tables ?PATTERN? List names of tables matching a LIKE pattern.timeout MS Try opening locked tables for MS milliseconds.timer ON|OFF Turn the CPU timer measurement on or off.width NUM NUM ... Set column widths for "column" modesqlite>
4. 格式化输出结果的写法就不说了，什么时候要用直接查官网文档！
5. Querying the database schema,这个比较常用：
to see a list of the tables in the database, you can enter ".tables".
sqlite> .tablestbl1tbl2sqlite>
查看索引是类似的，不多说，使用 ".indices" command
the ".schema" command shows the original CREATE TABLE and CREATE INDEX statements that were used to build the current database. If you give the name of a table to ".schema", it shows the original CREATE statement used to make that table and all if its indices.
sqlite> .schemacreate table tbl1(one varchar(10), two smallint)CREATE TABLE tbl2 (f1 varchar(30) primary key,f2 text,f3 real)sqlite> .schema tbl2CREATE TABLE tbl2 (f1 varchar(30) primary key,f2 text,f3 real)sqlite>
The ".databases" command shows a list of all databases open in the current connection. There will always be at least 2. The first one is "main", the original database opened. The second is "temp", the database used for temporary tables. There may be additional databases listed for databases attached using the ATTACH statement. The first output column is the name the database is attached with, and the second column is the filename of the external file.
sqlite> .databases
6. 重命名表名
alter table current_table_name rename to new_table_name;
7. 修改表结构，添加一个字段
alter table table_name add column column_name column_type;
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
That's All!
页:
[1]