sqlite数据库只用一个文件就ok,小巧方便,所以是一个非常不错的嵌入式数据库,SQLite大量的被用于手机,PDA,MP3播放器以及机顶盒设备。 新建数据库 sqlite3 databasefilename 检查databasefilename是否存在,如果不存在就创建并进入数据库(如果直接退出,数据库文件不会创建) 如果已经存在直接进入数据库 对数据库进行操作 sqlite中命令: 以.开头,大小写敏感(数据库对象名称是大小写不敏感的) .exit .help 查看帮助 针对命令 .database 显示数据库信息;包含当前数据库的位置 .tables 或者 .table 显示表名称 没有表则不显示 .schema 命令可以查看创建数据对象时的SQL命令; .schema databaSEObjectname查看创建该数据库对象时的SQL的命令;如果没有这个数据库对象就不显示内容,不会有错误提示 .read FILENAME 执行指定文件中的SQL语句 .headers on/off 显示表头 默认off .mode list|column|insert|line|tabs|tcl|csv 改变输出格式,具体如下 sqlite> .mode list sqlite> select * from emp; 7369|SMITH|CLERK|7902|17-12-1980|800||20 7499|ALLEN|SALESMAN|7698|20-02-1981|1600|300|30 如果字段值为NULL 默认不显示 也就是显示空字符串 sqlite> .mode column sqlite> select * from emp; 7369 SMITH CLERK 7902 17-12-1980 800 20 7499 ALLEN SALESMAN 7698 20-02-1981 1600 300 30 7521 WARD SALESMAN 7698 22-02-1981 1250 500 30 sqlite> .mode insert sqlite> select * from dept; INSERT INTO table VALUES(10,'ACCOUNTING','NEW YORK'); INSERT INTO table VALUES(20,'RESEARCH','DALLAS'); INSERT INTO table VALUES(30,'SALES','CHICAGO'); INSERT INTO table VALUES(40,'OPERATIONS','BOSTON'); sqlite> .mode line sqlite> select * from dept; DEPTNO = 10 DNAME = ACCOUNTING LOC = NEW YORK DEPTNO = 20 DNAME = RESEARCH LOC = DALLAS DEPTNO = 30 DNAME = SALES LOC = CHICAGO DEPTNO = 40 DNAME = OPERATIONS LOC = BOSTON sqlite> .mode tabs sqlite> select * from dept; 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON sqlite> .mode tcl sqlite> select * from dept; "10" "ACCOUNTING""NEW YORK" "20" "RESEARCH""DALLAS" "30" "SALES" "CHICAGO" "40" "OPERATIONS""BOSTON" sqlite> .mode csv sqlite> select * from dept; 10,ACCOUNTING,"NEW YORK" 20,RESEARCH,DALLAS 30,SALES,CHICAGO 40,OPERATIONS,BOSTON .separator "X" 更改分界符号为X sqlite> .separator '**' sqlite> select * from dept; 10**ACCOUNTING**"NEW YORK" 20**RESEARCH**DALLAS 30**SALES**CHICAGO 40**OPERATIONS**BOSTON .dump ?TABLE? 生成形成数据库表的SQL脚本 .dump 生成整个数据库的脚本在终端显示 .output stdout 将输出打印到屏幕 默认 .output filename 将输出打印到文件(.dump .output 结合可将数据库以sql语句的形式导出到文件中) .nullvalue STRING 查询时用指定的串代替输出的NULL串 默认为.nullvalue '' 字段类型: 数据库中存储的每个值都有一个类型,都属于下面所列类型中的一种,(被数据库引擎所控制) NULL: 这个值为空值 INTEGER: 值被标识为整数,依据值的大小可以依次被存储为1,2,3,4,5,6,7,8个字节 REAL: 所有值都是浮动的数值,被存储为8字节的IEEE浮动标记序号. TEXT: 文本. 值为文本字符串,使用数据库编码存储(TUTF-8,UTF-16BE or UTF-16-LE). BLOB: 值是BLOB数据,如何输入就如何存储,不改变格式. 值被定义为什么类型只和值自身有关,和列没有关系,和变量也没有关系.所以sqlite被称作 弱类型 数据库 数据库引擎将在执行时检查、解析类型,并进行数字存储类型(整数和实数)和文本类型之间的转换. SQL语句中部分的带双引号或单引号的文字被定义为文本, 如果文字没带引号并没有小数点或指数则被定义为整数, 如果文字没带引号但有小数点或指数则被定义为实数, 如果值是空则被定义为空值. BLOB数据使用符号X'ABCD'来标识. 但实际上,sqlite3也接受如下的数据类型: smallint 16位的整数。 interger 32位的整数。 decimal(p,s) 精确值p是指全部有几个十进制数,s是指小数点后可以有几位小数。如果没有特别指定,则系统会默认为p=5 s=0 。 float 32位元的实数。 double 64位元的实数。 char(n) n 长度的字串,n不能超过 254。 varchar(n) 长度不固定且其最大长度为 n 的字串,n不能超过 4000。 graphic(n) 和 char(n) 一样,不过其单位是两个字节, n不能超过127。这个形态是为了支持两个字节长度的字体,如中文字。 vargraphic(n) 可变长度且其最大长度为n的双字元字串,n不能超过2000 date 包含了 年份、月份、日期。 time 包含了 小时、分钟、秒。 timestamp 包含了 年、月、日、时、分、秒、千分之一秒。 SQLite包含了如下时间/日期函数: datetime() 产生日期和时间 无参数表示获得当前时间和日期 sqlite> select datetime(); 2012-01-07 12:01:32 有字符串参数则把字符串转换成日期 sqlite> select datetime('2012-01-07 12:01:30'); 2012-01-07 12:01:30 select date('2012-01-08','+1 day','+1 year'); 2013-01-09 select datetime('2012-01-08 00:20:00','+1 hour','-12 minute'); 2012-01-08 01:08:00 select datetime('now','start of year'); 2012-01-01 00:00:00 select datetime('now','start of month'); 2012-01-01 00:00:00 select datetime('now','start of day'); 2012-01-08 00:00:00 select datetime('now','start of week');错误 select datetime('now','localtime'); 结果:2006-10-17 21:21:47 date()产生日期 sqlite> select date('2012-01-07 12:01:30'); 2012-01-07 同理 有参和无参 select date('now','start of year'); 2012-01-01 select date('2012-01-08','+1 month'); 2012-02-08 time() 产生时间 select time(); 03:14:30 select time('23:18:59'); 23:18:59 select time('23:18:59','start of day'); 00:00:00 select time('23:18:59','end of day');错误 在时间/日期函数里可以使用如下格式的字符串作为参数: YYYY-MM-DD YYYY-MM-DD HH:MM YYYY-MM-DD HH:MM:SS YYYY-MM-DD HH:MM:SS.SSS HH:MM HH:MM:SS HH:MM:SS.SSS now 其中now是产生现在的时间。 日期不能正确比较大小,会按字符串比较,日期默认格式 dd-mm-yyyy select hiredate from emp order by hiredate; 17-11-1981 17-12-1980 19-04-1987 20-02-1981 22-02-1981 strftime() 对以上三个函数产生的日期和时间进行格式化 strftime()函数可以把YYYY-MM-DD HH:MM:SS格式的日期字符串转换成其它形式的字符串。 strftime(格式,日期/时间,修正符,…) select strftime('%d',datetime()); 它可以用以下的符号对日期和时间进行格式化: %d 在该月中的第几天,01-31 %f 小数形式的秒,SS.SSS %H 小时,00-23 %j 算出某一天是该年的第几天,001-366 %m 月份,00-12 %M 分钟,00-59 %s 从1970年1月1日到现在的秒数 %S 秒,00-59 %w 星期,0-6 (0是星期天) %W 算出某一天属于该年的第几周,01-53 %Y 年,YYYY %% 百分号 select strftime('%Y.%m.%d %H:%M:%S','now'); select strftime('%Y.%m.%d %H:%M:%S','now','localtime'); 结果:2006.10.17 21:41:09 select hiredate from emp order by strftime('%Y.%m.%d %H:%M:%S',hiredate); 正确 select strftime('%Y.%m.%d %H:%M:%S',hiredate) from emp order by strftime('%Y.%m.%d %H:%M:%S',hiredate); 错误 算术函数 abs(X) 返回给定数字表达式的绝对值。 max(X,Y[,...]) 返回表达式的最大值。 组函数 max(列名) sqlite> select max(2,12); 12 min(X,...]) 返回表达式的最小值。 random() 返回随机数。 sqlite> select random(); 3224224213599993831 round(X[,Y]) 返回数字表达式并四舍五入为指定的长度或精度。 字符处理函数 length(X) 返回给定字符串表达式的字符个数。 lower(X) 将大写字符数据转换为小写字符数据后返回字符表达式。 upper(X) 返回将小写字符数据转换为大写的字符表达式。 substr(X,Y,Z) 返回表达式的一部分。 从Y开始读Z个字符 Y最小值1 sqlite> select substr('abcdef',3); cde quote(A) 给字符串加引号 sqlite> select quote('aaa'); 'aaa' 条件判断函数 ifnull(X,Y) 如果X为null 返回Y select ifnull(comm,0) from emp; 0 300 500 0 1400 集合函数 avg(X) 返回组中值的平均值。 count(X) 返回组中项目的数量。 max(X) 返回组中值的最大值。 min(X) 返回组中值的最小值。 sum(X) 返回表达式中所有值的和。 其他函数 typeof(X) 返回数据的类型。 sqlite> select typeof(111); integer sqlite> select typeof('233'); text sqlite> select typeof('2012-12-12'); text sqlite> select typeof('223.44'); text sqlite> select typeof(223.44); real last_insert_rowid() 返回最后插入的数据的ID。 sqlite_version() 返回SQLite的版本。 sqlite> select sqlite_version(); 3.7.9 change_count() 返回受上一语句影响的行数。 last_statement_change_count() create table emp_bak select * from EMP;不能在sqlite中使用 插入记录 insert into table_name values (field1,field2,field3...); 查询 select * from table_name;查看table_name表中所有记录; select * from table_name where field1='xxxxx'; 查询符合指定条件的记录; select ..... from table_name[,table_name2,...] where ..... group by.... having .... order by ... select ..... from table_name inner join | left outer join | right outer join table_name2 on ... where ..... group by.... having .... order by ... 子查询: select * from EMP m where SAL> (select avg(SAL) from EMP where DEPTNO=m.DEPTNO); 支持case when then 语法 update EMP set SAL= ( case when DEPTNO=10 and JOB='MANAGER' then SAL*1.1 when DEPTNO=20 and JOB='CLERK' then SAL*1.2 when DEPTNO=30 then SAL*1.1 when DEPTNO=40 then SAL*1.2 else SAL END ); select ENAME, case DEPTNO when 10 then '后勤部' when 20 then '财务部' when 30 then '内务部门' else '其他部门' end as dept from EMP; 支持关联子查询 in后面的语法中可以有limit(mysql不可以) select * from emp e where e.EMPNO in ( select empno from EMP where deptno=e.DEPTNO order by SAL desc limit 0,2 ); 支持表和表之间的数据合并等操作 union 去重复 union all 不去掉重复 select deptno from emp union select deptno from dept; select deptno from emp union all select deptno from dept; 在列名前加distinct也是去重复 sqlite> select distinct deptno from emp; 删除 delete from table_name where ... 删除表 drop table_name; 删除表; drop index_name; 删除索引; 修改 update table_name set xxx=value[,xxx=value,...] where ... 建立索引 如果资料表有相当多的资料,我们便会建立索引来加快速度。好比说: create index film_title_index on film(title); 意思是针对film资料表的name字段,建立一个名叫film_name_index的索引。这个指令的语法为 CREATE [ UNIQUE ] NONCLUSTERED INDEX index_name ON { table | view } ( column [ ASC | DESC ] [,...n ] ) create index index_name on table_name(field_to_be_indexed); 一旦建立了索引,sqlite3会在针对该字段作查询时,自动使用该索引。这一切的操作都是在幕后自动发生的,无须使用者特别指令。 其他sqlite的特别用法 sqlite可以在shell底下直接执行命令: sqlite3 film.db "select * from emp;" 输出 HTML 表格: sqlite3 -html film.db "select * from film;" 将数据库「倒出来」: sqlite3 film.db ".dump" > output.sql 利用输出的资料,建立一个一模一样的数据库(加上以上指令,就是标准的SQL数据库备份了): sqlite3 film.db < output.sql 在大量插入资料时,你可能会需要先打这个指令: begin; 插入完资料后要记得打这个指令,资料才会写进数据库中: commit; sqlite> begin; sqlite> insert into aaaa values('aaa','333'); sqlite> select * from aaaa; 2|sdfds sdfsd|9 2012-12-12|13:13:13 aaa|333 sqlite> rollback; sqlite> select * from aaaa; 2|sdfds sdfsd|9 2012-12-12|13:13:13 创建和删除视图 CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition DROP VIEW view_name create view e as select avg(SAL) avgsal,DEPTNO from EMP group by DEPTNO; select ENAME,EMP.DEPTNO,SAL,avgsal from EMP inner join e on EMP.DEPTNO=e.DEPTNO where SAL>avgsal;
练习员工表:
PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE DEPT ( DEPTNO int(2) not null, DNAME varchar(14), LOC varchar(13) ); INSERT INTO "DEPT" VALUES(10,'NEW YORK'); INSERT INTO "DEPT" VALUES(20,'DALLAS'); INSERT INTO "DEPT" VALUES(30,'CHICAGO'); INSERT INTO "DEPT" VALUES(40,'BOSTON'); CREATE TABLE EMP ( EMPNO int(4) not null, ENAME varchar(10), JOB varchar(9), MGR int(4), HIREDATE date, SAL int(7 ), COMM int(7 ), DEPTNO int(2) ); INSERT INTO "EMP" VALUES(7369,'SMITH','CLERK',7902,'17-12-1980',800,NULL,20); INSERT INTO "EMP" VALUES(7499,'ALLEN','SALESMAN',7698,'20-02-1981',1600,300,30); INSERT INTO "EMP" VALUES(7521,'WARD','22-02-1981',1250,500,30); INSERT INTO "EMP" VALUES(7566,'JONES','MANAGER',7839,'02-04-1981',2975,20); INSERT INTO "EMP" VALUES(7654,'MARTIN','28-09-1981',1400,30); INSERT INTO "EMP" VALUES(7698,'BLAKE','01-05-1981',2850,30); INSERT INTO "EMP" VALUES(7782,'CLARK','09-06-1981',2450,10); INSERT INTO "EMP" VALUES(7788,'SCOTT','ANALYST',7566,'19-04-1987',3000,20); INSERT INTO "EMP" VALUES(7839,'KING','PRESIDENT','17-11-1981',5000,10); INSERT INTO "EMP" VALUES(7844,'TURNER','08-09-1981',1500,30); INSERT INTO "EMP" VALUES(7876,'ADAMS',7788,'23-05-1987',1100,20); INSERT INTO "EMP" VALUES(7900,'JAMES','03-12-1981',950,30); INSERT INTO "EMP" VALUES(7902,'FORD',20); INSERT INTO "EMP" VALUES(7934,'MILLER',7782,'23-01-1982',1300,10); CREATE TABLE SALGRADE ( GRADE int, LOSAL int, HISAL int ); INSERT INTO "SALGRADE" VALUES(1,700,1200); INSERT INTO "SALGRADE" VALUES(2,1201,1400); INSERT INTO "SALGRADE" VALUES(3,1401,2000); INSERT INTO "SALGRADE" VALUES(4,2001,3000); INSERT INTO "SALGRADE" VALUES(5,3001,9999); COMMIT;
SQL的指令格式 所以的SQL指令都是以分号(;)结尾的。如果遇到两个减号(--)则代表注解,sqlite3会略过去。
建立资料表 假设我们要建一个名叫film的资料表,只要键入以下指令就可以了:
create table film(title,length,year,starring);
这样我们就建立了一个名叫film的资料表,里面有name、length、year、starring四个字段。
这个create table指令的语法为:
create table table_name(field1,field3,...);
table_name是资料表的名称,fieldx则是字段的名字。sqlite3与许多SQL数据库软件不同的是,它不在乎字段属于哪一种资料型态:sqlite3的字段可以储存任何东西:文字、数字、大量文字(blub),它会在适时自动转换。
建立索引 如果资料表有相当多的资料,我们便会建立索引来加快速度。好比说:
create index film_title_index on film(title);
意思是针对film资料表的name字段,建立一个名叫film_name_index的索引。这个指令的语法为
create index index_name on table_name(field_to_be_indexed);
一旦建立了索引,sqlite3会在针对该字段作查询时,自动使用该索引。这一切的操作都是在幕后自动发生的,无须使用者特别指令。
加入一笔资料 接下来我们要加入资料了,加入的方法为使用insert into指令,语法为:
insert into table_name values(data1,data2,data3,109); padding-top:0px"> 例如我们可以加入
insert into film values ('Silence of the Lambs,The',118,1991,'Jodie Foster');
insert into film values ('Contact',153,1997,109); padding-top:0px"> insert into film values ('Crouching Tiger,Hidden Dragon',120,2000,'Yun-Fat Chow');
insert into film values ('Hours,114,2002,'Nicole Kidman');
如果该字段没有资料,我们可以填NULL。
查询资料 讲到这里,我们终于要开始介绍SQL最强大的select指令了。我们首先简单介绍select的基本句型:
select columns from table_name where expression;
最常见的用法,当然是倒出所有数据库的内容:
select * from film;
如果资料太多了,我们或许会想限制笔数:
select * from film limit 10;
或是照着电影年份来排列:
select * from film order by year limit 10;
或是年份比较近的电影先列出来:
select * from film order by year desc limit 10;
或是我们只想看电影名称跟年份:
select title,year from film order by year desc limit 10;
查所有茱蒂佛斯特演过的电影:
select * from film where starring='Jodie Foster';
查所有演员名字开头叫茱蒂的电影('%' 符号便是 SQL 的万用字符):
select * from film where starring like 'Jodie%';
查所有演员名字以茱蒂开头、年份晚于1985年、年份晚的优先列出、最多十笔,只列出电影名称和年份:
有时候我们只想知道数据库一共有多少笔资料:
select count(*) from film;
有时候我们只想知道1985年以后的电影有几部:
select count(*) from film where year >= 1985;
(进一步的各种组合,要去看SQL专书,不过你大概已经知道SQL为什么这么流行了:这种语言允许你将各种查询条件组合在一起──而我们还没提到「跨数据库的联合查询」呢!)
如何更改或删除资料 了解select的用法非常重要,因为要在sqlite更改或删除一笔资料,也是靠同样的语法。
例如有一笔资料的名字打错了:
update film set starring='Jodie Foster' where starring='Jodee Foster';
就会把主角字段里,被打成'Jodee Foster'的那笔(或多笔)资料,改回成Jodie Foster。
delete from film where year < 1970;
就会删除所有年代早于1970年(不含)的电影了。
将结果写到文件
默认情况下,sqlite3会将结果发送到标准输出,你可以使用 ".output" 来改变,只是将输出到的文件名作为参数传递给 .output,所有后面的查询结果都会写到文件里。开头使用 ".output stdout" 会再次写到标准输出,例如:
sqlite> .mode list sqlite> .separator | sqlite> .output test_file_1.txt sqlite> select * from tb1; sqlite> .exit # cat test_file_1.txt hello|10 goodbye|20
其他sqlite的特别用法 sqlite可以在shell底下直接执行命令:
sqlite3 film.db "select * from film;"
输出 HTML 表格:
sqlite3 -html film.db "select * from film;"
将数据库「倒出来」:
sqlite3 film.db ".dump" > output.sql
利用输出的资料,建立一个一模一样的数据库(加上以上指令,就是标准的SQL数据库备份了):
sqlite3 film.db < output.sql
在大量插入资料时,你可能会需要先打这个指令:
begin;
简介 sqlite3一款主要用于嵌入式的轻量级数据库,本文旨在为熟悉sqlite3基本命令提供技术文档。 备注:本文所有操作均在root用户下进行。
1、安装sqlite3 ubuntu下安装sqlite3直接在终端运行命令: #apt-get install sqlite3 查看版本信息: #sqlite3 -version 2 、sqlite3常用命令 当前目录下建立或打开test.db数据库文件,并进入sqlite命令终端,以sqlite>前缀标识: #sqlite3 test.db 查看数据库文件信息命令(注意命令前带字符'.'): sqlite>.database
查看所有表的创建语句: sqlite>.schema 查看指定表的创建语句: sqlite>.schema table_name 以sql语句的形式列出表内容: sqlite>.dump table_name 设置显示信息的分隔符: sqlite>.separator symble Example:设置显示信息以‘:’分隔 sqlite>.separator : 设置显示模式: sqlite>.mode mode_name Example:默认为list,设置为column,其他模式可通过.help查看mode相关内容 sqlite>.mode column 输出帮助信息: sqlite>.help 设置每一列的显示宽度: sqlite>.width width_value Example:设置宽度为2 sqlite>.width 2 列出当前显示格式的配置: sqlite>.show 退出sqlite终端命令: sqlite>.quit 或 sqlite>.exit 3、sqlite3指令 sql的指令格式:所有sql指令都是以分号(;)结尾,两个减号(--)则表示注释。 如: sqlite>create studen_table(Stu_no interger PRIMARY KEY,Name text NOT NULL,Id interger UNIQUE,Age interger CHECK(Age>6),School text DEFAULT 'xx小学); 该语句创建一个记录学生信息的数据表。 3.1 sqlite3存储数据的类型 NULL:标识一个NULL值 INTERGER:整数类型 REAL:浮点数 TEXT:字符串 BLOB:二进制数
3.2 sqlite3存储数据的约束条件 Sqlite常用约束条件如下: PRIMARY KEY - 主键: 1)主键的值必须唯一,用于标识每一条记录,如学生的学号 2)主键同时也是一个索引,通过主键查找记录速度较快 3)主键如果是整数类型,该列的值可以自动增长 NOT NULL - 非空: 约束列记录不能为空,否则报错 UNIQUE - 唯一: 除主键外,约束其他列的数据的值唯一 CHECK - 条件检查: 约束该列的值必须符合条件才可存入 DEFAULT - 默认值: 列数据中的值基本都是一样的,这样的字段列可设为默认值
3.3 sqlite3常用指令 1)建立数据表 create table table_name(field1 type1,field2 type1,...); table_name是要创建数据表名称,fieldx是数据表内字段名称,typex则是字段类型。 例,建立一个简单的学生信息表,它包含学号与姓名等学生信息: create table student_info(stu_no interger primary key,name text); 2)添加数据记录 insert into table_name(field1,...) values(val1,val2,...); valx为需要存入字段的值。 例,往学生信息表添加数据: Insert into student_info(stu_no,name) values(0001,alex); 3)修改数据记录 update table_name set field1=val1,field2=val2 where expression; where是sql语句中用于条件判断的命令,expression为判断表达式 例,修改学生信息表学号为0001的数据记录: update student_info set stu_no=0001,name=hence where stu_no=0001; 4)删除数据记录 delete from table_name [where expression]; 不加判断条件则清空表所有数据记录。 例,删除学生信息表学号为0001的数据记录: delete from student_info where stu_no=0001; 5)查询数据记录 select指令基本格式: select columns from table_name [where expression]; a查询输出所有数据记录 select * from table_name; b限制输出数据记录数量 select * from table_name limit val; c升序输出数据记录 select * from table_name order by field asc; d降序输出数据记录 select * from table_name order by field desc; e条件查询 select * from table_name where expression; select * from table_name where field in ('val1','val2','val3'); select * from table_name where field between val1 and val2; f查询记录数目 select count (*) from table_name; g区分列数据 select distinct field from table_name; 有一些字段的值可能会重复出现,distinct去掉重复项,将列中各字段值单个列出。 6)建立索引 当说数据表存在大量记录,索引有助于加快查找数据表速度。 create index index_name on table_name(field); 例,针对学生表stu_no字段,建立一个索引: create index student_index on student_table(stu_no); 建立完成后,sqlite3在对该字段查询时,会自动使用该索引。 7)删除数据表或索引 drop table table_name; drop index (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|