sql安装及使用方法

sqlite3官网地址：www.sqlite.org
源上安装：
sudo apt-get install libsqlite3-dev sqlite3

安装过程可能遇到的问题：
1.找不到软件包。
    sudo apt-get update   然后再次安装。

2.依赖关系不满足。
    sudo apt-get install aptitude
    sudo  aptitude  install  libsqlite3-dev  sqlite3

使用:
sqlite3 -V

进入:sqlite3
退出:.quit
查看:.help

sudo apt-get install xchm    (安装查看chm文件的工具)

sqlite3 +文件名    (创建表)    
例如:create table student(id integer primary key, name text, age integer, score integer);
(注意后面的分号不能丢)
或create table stu(id integer primary key, name text NOT NULL, age integer, score integer);
(指定姓名字段不能为空)
    
.table    (查看该数据库中的表)
drop table student;  --(删除表)

insert into +表名(字段)    (插入记录)
例如:insert into student(name,id,score,age) values('小明', 1, 100, 21);
或insert into student values(3,'小亮', 79, 20);      (按默认格式插入)
或insert into student(id, name, age) values(4, '小吴', 19);    (按指定格式输入)

1.select * from +表名;    (查询所有记录)
2.select id,name from +表名;    (查询指定记录id,name)
3.select * from +表名(student) where id>2;    (查询学号大于2的记录)
4.select * from student where id=2;
  select * from student where id like 2;
5.select * from student where id=2 and name='小李';
6.select * from student where id=2 or name='小明';
7.select * from student where id>=2 and id<=6;
8.select * from student where id between 2 and 6;
9.select * from student where score like "9%";    (模糊查询)
10.select * from student where name like "小%李";
11.select distinct name from student where name = "小李";
(加上distinct,重复的只显示一次)
12.select all name from student where name = "小李";
(显示全部:默认)
13.select * from student order by score asc;    (升序)
14.select * from student order by score desc;    (降序)
15.select * from student order by score desc limit 2;  (只显示前两个)
16.select * from newstu where score < 70 order by score desc limit 3;
17.select * from newstu order by score asc limit 1 offset 2;  (取第三个,offset 2:跳过两个)
18.select * from newstu where score<70 order by score desc limit 1 offset 1;
(取70分以下分数最高的第二名)
19.select * from newstu where score=(select score from newstu order by score desc limit 1);
   select * from newstu order by score desc limit 1;


select sum(salary) from employee;    (求和)
select depart, sum(salary) from employee group by depart;    (按组求和)
select depart,sum(salary),max(salary),min(salary),avg(salary),count(*) from employee group by depart;
select depart,sum(salary),count(*) from employee where salary>=5000 group by depart;
select depart,sum(salary),count(*) from employee where salary>=5000 group by depart order by sum(salary) desc;
select depart,sum(salary),count(*) from employee where salary>=5000 group by depart having sum(salary)>8000 order by sum(salary) desc;
select * from employee group by depart,salary,name having count(*)>1;
select id from employee where depart='市场部' and salary=4000 and name='小周';

select * from employee where id>6 union select * from employee where id<3;
(求并集 union)
select * from employee where id>6 intersect select * from employee where id<3;
(求交集 intersect)
select * from employee where id<6 except select * from employee where id>3;



update 表名 set score = 90,age = 21;
(修改所有记录)
update 表名 set score = 90,age = 21 where id = 4;
(修改指定的记录)

delete from +表名;
(删除所有记录)
delete from +表名 where id = 4;
(删除指定记录)

.import +文件名 +表名
(将文件中的信息按对应格式导入表中)

.schema +表名
(查看表的结构)

alter table student add score2 integer;
(修改表的结构,增加字段score2)
alter table +表名 rename to +新表名;
(修改表名)

create table +新表名 as select * from +旧表名;
(备份表,两张表的结构不一样,只能保证记录是一样的)
create table newstu as select id,name,age,score from student;
(备份指定记录到第二张表)

insert into stu(id,name,age) select id,name,age from student;
insert into score(id,score) select id,score from student;
(从student表中插入对应记录到stu表中)

insert into score values(5,90);

select score.id,name,age,score from stu inner join score on stu.id=score.id;
(内链接inner)<=>select score.id,name,age,score from stu,score where stu.id=score.id;

select nu,name,age,score from stu inner join testscore on stu.id = testscore.nu;
(不能用自然链接)

select score.id,name,age,score from stu natural join score;
(自然链接natural)

select score.id,name,age,score from stu left join score on stu.id=score.id;
(左外链接left join)