一、Sqlite简介&＃xff1a;
SQLite (http://www.sqlite.org/)&＃xff0c;是一款轻型的数据库&＃xff0c;是遵守ACID的关联式数据库管理系统&＃xff0c;它的设计目标是嵌入式的&＃xff0c;而且目前已经在很多嵌入式产品中使用了它&＃xff0c;它占用资源非常的低&＃xff0c;在嵌入式设备中&＃xff0c;可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统&＃xff0c;同时能够跟很多程序语言相结合&＃xff0c;比如 Tcl、C#、PHP、Java等&＃xff0c;还有ODBC接口&＃xff0c;同样比起Mysql、PostgreSQL这两款开源世界著名的数据库管理系统来讲&＃xff0c;它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月. 至今已经有10个年头&＃xff0c;SQLite也迎来了一个版本 SQLite 3已经发布。
二、Sqlite作者赠言&＃xff1a;
o May you do good and not evil. 愿你行善莫行恶
o May you find forgiveness for yourself and forgive others. 愿你原谅自己宽恕他人
o May you share freely, never taking more than you give. 愿你宽心与人分享&＃xff0c;所取不多于你所施予
三、Sqlite的命令&＃xff1a;
sqlite3 too.db 创建名为too的数据库&＃xff0c;其后缀不一定用db
.Help 求助
.quit 离开
四、Sqlite的客户端工具&＃xff1a;
SQLiteExpertPers
六、Sqlite的sql语句&＃xff1a;
建表&＃xff1a;create table table_name(field1, field2, field3, ...);
例子&＃xff1a;创建名为film的数据库表
create table film(_id Integer primaray key autoincrement , title, length, year, starring);
注意&＃xff1a;语句要以分号结尾&＃xff0c;字段不用指定类型&＃xff0c;它会适时的自动转换
可以存储文字、数字、大文本(blub)
创建索引&＃xff1a;create index index_name on table_name(field_to_be_indexed);
例子&＃xff1a;针对表film的title字段创建名为film_title_index的索引
create index film_title_index on film(title);
注意&＃xff1a;当表的数据较多时&＃xff0c;索引能加快查询速度(前提是根据建有索引的字段查询)
添加数据&＃xff1a;insert into table_name(field1,field2,field3,...) values(data1, data2, data3, ...);
例子&＃xff1a;向表film中添加一条记录
insert into film(title, length, year, starring) values(&＃39;Contact&＃39;,153,1997,&＃39;Jodie Foster&＃39;);
注意&＃xff1a;可以省略语句中的字段名部分&＃xff0c;前提是数据个数与字段个数一样
如果某个字段没有添加值则其值为null,也可手动添加null值
查询数据&＃xff1a;select columns from table_name where expression;
例子&＃xff1a;从表film中查询数据
1 显示表里所有字段的所有数据
select * from film;
2 如果资料太多了&＃xff0c;我们或许会想限制笔数&＃xff1a;
select * from film limit 10;
3 照着电影年份来排列&＃xff1a;
select * from film order by year limit 10;
4 年份比较近的电影先列出来&＃xff1a;
select * from film order by year desc limit 10;
5 我们只想看电影名称跟年份&＃xff1a;
select title, year from film order by year desc limit 10;
6 查所有茱蒂佛斯特演过的电影&＃xff1a;
select * from film where starring&＃61;&＃39;Jodie Foster&＃39;;
7 查所有演员名字开头叫茱蒂的电影(&＃39;%&＃39; 符号便是 SQL 的万用字符&＃xff09;&＃xff1a;
select * from film where starring like &＃39;Jodie%&＃39;;
8 查所有演员名字以茱蒂开头、年份晚于1985年、年份晚的优先列出、最多十笔&＃xff0c;只列出电影名称和年份&＃xff1a;
select title, year from film where starring like &＃39;Jodie%&＃39; and year >&＃61; 1985
order by year desc limit 10;
9 查看数据库一共有多少条记录&＃xff1a;
select count(*) from film;
10 查看1985年以后的电影有几部&＃xff1a;
select count(*) from film where year >&＃61; 1985;
更新数据&＃xff1a;update film set starring&＃61;&＃39;Jodie Foster&＃39; where starring&＃61;&＃39;Jodee Foster&＃39;;
把主角字段为&＃39;Jodee Foster&＃39;的所有记录改成Jodie Foster。
删除数据&＃xff1a;delete from film where year <1970;
删除所有年代早于1970 年&＃xff08;不含&＃xff09;的电影记录
注释&＃xff1a;注释单行&＃xff1a;--
注释多行&＃xff1a;/* */
创建视图&＃xff1a;CREATE VIEW view-name AS select-statement
模糊匹配&＃xff1a;like %
sqlite日间日期函数&＃xff1a;
datetime() 产生日期和时间
date() 产生日期
time() 产生时间
strftime() 对以上三个函数产生的日期和时间格式化
可用的字符串参数&＃xff1a;
now 产生现在的时间
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
例子&＃xff1a;
select datetime(&＃39;now&＃39;);
select datetime(&＃39;2011-06-12&＃39;);
select datetime(&＃39;2006-10-17 00:20:00&＃39;,&＃39;&＃43;1 hour&＃39;,&＃39;-12 minute&＃39;);
select date(&＃39;2006-10-17&＃39;,&＃39;&＃43;1 day&＃39;,&＃39;&＃43;1 year&＃39;);
select datetime(&＃39;now&＃39;, &＃39;localtime&＃39;);
--修改表结构
--添加一个字段
alter table film add column director2;
--删除一个字段 不行
alter table film drop (column director2);
--删除一个表
drop table test;
sqlite的特别用法
sqlite可以在shell底下直接执行命令&＃xff1a;
输出 HTML 表格&＃xff1a; sqlite3 -html film.db "select * from film;"
将数据库「倒出来」&＃xff1a; sqlite3 film.db ".dump" > output.sql
利用输出的资料&＃xff0c;建立一个一模一样的数据库&＃xff08;加上以上指令&＃xff0c;就是标准的SQL数据库备份了&＃xff09;&＃xff1a;
sqlite3 film.db
插入完资料后要记得打这个指令&＃xff0c;资料才会写进数据库中&＃xff1a; commit;
七、练习&＃xff1a;
--创建雇员表
CREATE TABLE EMPLOYEES(
employee_id Integer PRIMARY KEY,
department_id Integer,
location_id Integer,
first_name,
last_name,
salary,
hire_date date
);
--创建部门表
CREATE TABLE DEPARTMENT(
department_id Integer primary key,
name
);
创建职位表
CREATE TABLE LOCATION (
location_id Integer PRIMARY KEY,
name
);
添加测试数据
insert into [employees](department_id,location_id , first_name, last_name, salary,hire_date)
values (1,1, &＃39;A&＃39;, &＃39;z&＃39;, 50000, &＃39;2005-02-21&＃39;);
insert into [employees](department_id,location_id, first_name, last_name, salary,hire_date)
values (1,2, &＃39;B&＃39;, &＃39;x&＃39;, 20000, &＃39;2009-03-21&＃39;);
insert into [employees](department_id,location_id , first_name, last_name, salary,hire_date)
values (2,3, &＃39;C&＃39;, &＃39;v&＃39;, 10000, &＃39;2009-08-23&＃39;);
insert into [employees](department_id,location_id, first_name, last_name, salary,hire_date)
values (4,2, &＃39;D&＃39;, &＃39;n&＃39;, 30000, &＃39;2004-09-28&＃39;);
insert into [employees](department_id,location_id, first_name, last_name, salary,hire_date)
values (3,5, &＃39;E&＃39;, &＃39;m&＃39;, 3000, &＃39;2009-04-11&＃39;);
insert into [employees](department_id,location_id, first_name, last_name, salary,hire_date)
values (5,5, &＃39;F&＃39;, &＃39;l&＃39;, 5000, &＃39;2008-03-11&＃39;);
insert into [employees](department_id,location_id, first_name, last_name, salary,hire_date)
values (5,3, &＃39;G&＃39;, &＃39;p&＃39;, 20000, &＃39;2005-05-09&＃39;);
insert into [employees](department_id,location_id, first_name, last_name, salary,hire_date)
values (5,4, &＃39;H&＃39;, &＃39;o&＃39;, 8000, &＃39;2006-07-21&＃39;);
insert into [employees](department_id,location_id, first_name, last_name, salary,hire_date)
values (5,5, &＃39;I&＃39;, &＃39;u&＃39;, 6000, &＃39;2006-09-21&＃39;);
insert into [employees](department_id,location_id, first_name, last_name, salary,hire_date)
values (5,5, &＃39;J&＃39;, &＃39;y&＃39;, 5500, &＃39;2007-08-21&＃39;);
insert into [employees](department_id,location_id, first_name, last_name, salary,hire_date)
values (5,5, &＃39;K&＃39;, &＃39;t&＃39;, 6500, &＃39;2006-12-21&＃39;);
insert into [employees](department_id,location_id, first_name, last_name, salary,hire_date)
values (5,1, &＃39;L&＃39;, &＃39;r&＃39;, 100000, &＃39;2001-05-21&＃39;);
insert into department(name) values (&＃39;人事&＃39;);
insert into department(name) values (&＃39;财务&＃39;);
insert into department(name) values (&＃39;后勤&＃39;);
insert into department(name) values (&＃39;公关&＃39;);
insert into department(name) values (&＃39;研发&＃39;);
insert into location(name) values (&＃39;总经理&＃39;);
insert into location(name) values (&＃39;经理&＃39;);
insert into location(name) values (&＃39;主管&＃39;);
insert into location(name) values (&＃39;组长&＃39;);
insert into location(name) values (&＃39;职员&＃39;);
--查研发部的职员的员工信息
select * from employees e where e.location_id &＃61;(select l.location_id from location l where l.name&＃61;&＃39;职员&＃39;)
and
e.[department_id]&＃61;(select d.department_id from department d where d.name&＃61;&＃39;研发&＃39;);
--根据查询结果创建一个表
create table TEMP_EMPLOYEES AS select employee_id, first_name, last_name from EMPLOYEES where salary>6000;
--查询可以进行计算
select salary*13 年薪 from employees where 年薪 !&＃61;260000;
select salary*13 年薪 from employees where 年薪 between 50000 and 100000;
--first_name 是A, B, C的职员信息
select * from employees where first_name in (&＃39;A&＃39;, &＃39;B&＃39;, &＃39;C&＃39;);
--测试is null
select * from film where title is null;
--查询月薪大于10000的主管
select * from employees where salary>10000 and location_id&＃61;3;
--查询月薪大于10000的主管或者理解
select * from employees where salary>10000 and (location_id&＃61;3 or location_id&＃61;2);
select * from employees where salary>10000 and location_id not in(4,5,1);
--测试order by
select * from employees order by location_id,salary;
京公网安备 11010802041100号