2011年8月27日 星期六 16时14分
sqlite 基本语法
sqlite的命令
sqlite3 too.db 创建名为too的
数据库，其后缀不一定用db .Help 求助 .quit 离开 建表：create table table_name(field1,field2,field3,...); 例子：创建名为film的
数据库表 create table film(title,length,year,starring); 注意：语句要以分号结尾，字段不用指定类型，它会适时的
自动转换 可以存储
文字、数字、大文本(blub) 创建索引：create index index_name on table_name(field_to_be_indexed); 例子：针对表film的title字段创建名为film_title_index的索引 create index film_title_index on film(title); 注意：当表的数据较多时，索引能加快
查询速度(前提是根据建有索引的字段
查询)
添加数据：insert into table_name(field1,...) values(data1,data2,data3,...); 例子：向表film中
添加一条记录 insert into film(title,starring) values('Contact',153,1997,'Jodie Foster'); 注意：可以省略语句中的字段名部分，前提是数据个数与字段个数一样 如果某个字段没有
添加值则其值为null,也可手动
添加null值
查询数据：select columns from table_name where expression; 例子：从表film中
查询数据 1
显示表里所有字段的所有数据 select * from film; 2 如果资料太多了，我们或许会想限制笔数： select * from film limit 10; 3 照着电影年份来排列： select * from film order by year limit 10; 4 年份比较近的电影先列出来： select * from film order by year desc limit 10; 5 我们只想看电影
名称跟年份： select title,year from film order by year desc limit 10; 6 查所有茱蒂佛斯特演过的电影： select * from film where starring='Jodie Foster'; 7 查所有演员名字开头叫茱蒂的电影('%' 符号便是
sql 的万用字符）： select * from film where starring like 'Jodie%'; 8 查所有演员名字以茱蒂开头、年份晚于1985年、年份晚的优先列出、最多十笔，只列出电影
名称和年份： select title,year from film where starring like 'Jodie%' and year >= 1985 order by year desc limit 10; 9 查看
数据库一共有多少条记录： select count(*) from film; 10 查看1985年以后的电影有几部： select count(*) from film where year >= 1985; 更新数据：update film set starring='Jodie Foster' where starring='Jodee Foster'; 把主角字段为'Jodee Foster'的所有记录改成Jodie Foster。
删除数据：delete from film where year < 1970;
删除所有年代早于1970 年（不含）的电影记录 注释：注释单行：-- 注释多行：/* */ 创建视图：CREATE VIEW view-name AS select-statement 模糊匹配：like %
sqlite的特别
用法 sqlite可以在shell底下直接执行命令：
输出 HTML 表格：
sqlite3 -html film.db "select * from film;" 将
数据库「倒出来」：
sqlite3 film.db ".dump" > output.
sql 利用
输出的资料，建立一个一模一样的
数据库（
加上以上指令，就是标准的
sql数据库备份了）：
sqlite3 film.db < output.
sql 在大量插入资料时，你可能会需要先打这个指令： begin; 插入完资料后要记得打这个指令，资料才会写进
数据库中： commit;
sqlite日间日期
函数： datetime() 产生日期和时间 date() 产生日期 time() 产生时间 strftime() 对以上三个
函数产生的日期和时间格式化 可用的字符串参数： 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 例子： select datetime('now'); select datetime('2011-06-12'); select datetime('2006-10-17 00:20:00','+1 hour','-12 minute'); select date('2006-10-17','+1 day','+1 year'); select datetime('now','localtime'); 部门实例： --创建雇员表 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 ); select * from employees; insert into [employees](department_id,location_id,hire_date) values (1,1,'A','z',50000,'2005-02-21'); insert into [employees](department_id,2,'B','x',20000,'2009-03-21'); insert into [employees](department_id,hire_date) values (2,3,'C','v',10000,'2009-08-23'); insert into [employees](department_id,hire_date) values (4,'D','n',30000,'2004-09-28'); insert into [employees](department_id,hire_date) values (3,5,'E','m',3000,'2009-04-11'); insert into [employees](department_id,hire_date) values (5,'F','l',5000,'2008-03-11'); insert into [employees](department_id,'G','p','2005-05-09'); insert into [employees](department_id,4,'H','o',8000,'2006-07-21'); insert into [employees](department_id,'I','u',6000,'2006-09-21'); insert into [employees](department_id,'J','y',5500,'2007-08-21'); insert into [employees](department_id,'K','t',6500,'2006-12-21'); insert into [employees](department_id,'L','r',100000,'2001-05-21'); --插入部门信息 insert into DEPARTMENT(name) values ('人事'); insert into DEPARTMENT(name) values ('财务'); insert into DEPARTMENT(name) values ('后勤'); insert into DEPARTMENT(name) values ('公关'); insert into DEPARTMENT(name) values ('研发'); --插入职位信息 insert into LOCATION(name) values ('总经理'); insert into LOCATION(name) values ('经理'); insert into LOCATION(name) values ('主管'); insert into LOCATION(name) values ('组长'); insert into LOCATION(name) values ('职员'); --查研发部的职员的员工信息 select * from employees e where e.location_id =(select l.location_id from location l where l.name='职员') and e.[department_id]=(select d.department_id from department d where d.name='研发'); 分析：查看平均工资最高的部门ID 对题目分解： 1、首先
查询平均工资最高的第一个部门 select department_id,avg(salary) from employees group by department_id order by avg(salary) desc limit 1; 注：(1)在
sqlite中，向上面的
查询理论上可以使用这个
查询方式： select department_id,max（avg(salary)) from employees group by department_id order by avg(salary); 但是在实际使用是会出现
错误，貌似
不支持max(avg(salary))这种书写格式。没关系，可以换一个思路，就是想得到平均工资最高的那个元素，可以将
查询出来的平均工资按降序排列，然后取第一个值：order by avg(salary) desc limit 1; (2)当然使用max()也是很好的
方法，需要临时使用一个别名，如下面的avs： select department_id,max(avs) from (select department_id,avg(salary) avs from employees group by department_id order by avg(salary) desc) 2、继续，直接从上面
查询的表中
查询部门ID即可 select department_id from (select department_id,avg(salary) from employees group by department_id order by avg(salary) desc limit 1); 总结：
查询就是：按一定限制条件 多中取少 --查研发部的职员的员工信息 select * from employees e where e.location_id =(select l.location_id from location l where l.name='职员') and e.[department_id]=(select d.department_id from department d where d.name='研发'); --根据
查询结果创建一个表 关键字：as create table TEMP_EMPLOYEES AS select employee_id,last_name from EMPLOYEES where salary>6000; --
修改表结构 --
添加一个字段 关键字：alter add alter table film add column director2; --
删除一个字段 不行 alter table film drop (column director2); --
删除一个表 关键字：drop drop table test; --
查询可以进行计算 关键字：!= between...and select salary*13 年薪 from employees where 年薪 !=260000; select salary*13 年薪 from employees where 年薪 between 50000 and 100000; --first_name 是A,B,C的职员信息 关键字：in select * from employees where first_name in ('A','C'); --
查询所有有片名的电影信息 关键字：is null select * from film where title is null; --
查询月薪大于10000的主管 关键字：and select * from employees where salary>10000 and location_id=3; --
查询月薪大于10000的主管或者经理 关键字：or select * from employees where salary>10000 and (location_id=3 or location_id=2); --
查询月薪大于10000的员工信息，但他们不能是主管或经理 关键字：not in select * from employees where salary>10000 and location_id not in(4,1); --
查询员工信息并根据职位及工资排序 关键字：order by select * from employees order by location_id,salary; --求各部门的平均工资 --
查询各部门中最高工资大于20000的工资 --
查询平均工资最高的部门的id --查出所有工资大于平均工资的员工信息 --查出所有所有姓z的部门
名称 --查出姓z的员工的所有信息(
包括其职位和部门) --查出工资高于姓z的并与他不在同一个部门的员工信息
