SQLITE 笔记

r囧r小猫 2022-08-14 02:46 246阅读 0赞

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  
  
  
SQL语言：  
一、创建表：  
create table student(id integer primary key, name text, age integer, score integer);  
  
  
sqlite3的命令，不是SQL语句，不需要分号结尾，以点开头：  
.table 查看当前数据库文件里有哪些表。  
.schema 表名     查看表的结构有哪几列。  
.quit  退出sqlite3程序。  
  
  
二、插入新数据：  
insert into student(id, name, age, score) values(1, '小明', 21, 100);  
insert into student values(3, "小张", 23, 98);  
  
  
三、查询记录：  
select \* from student;  
  
  
四、更改记录：  
update student set score=100 where id=4;更改id是4的学生分数是100分  
update student set score=100;更改所有记录的分数都是100分  
update student set score=99,age=20 where id=4;  
  
  
五、删除记录：  
delete from student where id=4; 删除一条记录  
delete from student; 删除所有记录  
  
  
六、备份表中数据：  
create table studentbak as select \* from student;  
  
  
七、删除表：  
drop table studentbak;  
  
  
八、修改表的结构：  
a.增加一个字段：  
alter table student add score2 integer;  
b.修改表的名字：  
alter table student rename to student1;  
但是不支持修改现有的一个字段。  
  
  
九、复杂查询：  
select id, name, gender, age, scoreA, scoreB, scoreC, scoreA+scoreB+scoreC from student;  
可以查询由字段名字组合成的表达式的值。  
  
  
select \* from student where score>95;  
  
  
select \* from student where score>95 and score<99;  
也支持用or把几个条件连起来。  
  
  
select \* from student where score between 95 and 99; 包含边界。  
  
  
select \* from student where score like '9%';显示90多分的同学  
  
  
select \* from student where name like '%李';  
select \* from student where name like 's%n';  
  
  
select \* from student where score like 90; 没有%则like此时相当于=号。  
  
  
select distinct name from fruit;  显示结果去掉重复值  
  
  
select all name from fruit; 显示结果有重复值  
select name from fruit; 显示结果有重复值， 默认是all  
  
  
select \* from fruit order by price desc; 按价格降序排列  
  
  
select \* from fruit order by price; 默认是升序  
select \* from fruit order by price asc;按价格升序排列  
  
  
select \* from fruit where price>1000 order by price asc; 把价格大于1000的交易记录按升序排列  
  
  
select \* from fruit order by price desc limit 2;  
显示交易记录价格最高的两笔记录  
  
  
select \* from fruit order by price desc limit 2 offset 1;跳过一笔记录以后，显示最高的两笔  
  
  
select \* from student order by score asc limit 1 offset 2;  
找班上成绩倒数第三的一位同学。  
  
  
select \* from student where score=(select score from student order by score asc limit 1 offset 2);  
找班上成绩倒数第三的所有同学。  
  
  
select \* from student where score<60 order by score desc limit 3;找班上不及格的同学中成绩最高的3位。  
  
  
group by子句：  
create table employee(id integer primary key, name text, dep text, salary float);  
  
  
select dep,sum(salary) from employee group by dep;  
打印公司每月各部门薪资总数  
  
  
select dep,sum(salary),count(\*) from employee group by dep;  
打印公司每月各部门薪资总数及各部门人数  
  
  
select id, name from employee group by name,dep,salary having count(\*)>1;  
打印输入重复的人的名字  
  
  
select id, name from employee where id>5 group by name,dep,salary having count(\*)>1;  
打印id>5的员工中，数据输入重复的人名。  
  
  
连接：  
定义：  
把两张表中的数据，按某一个字段进行匹配，一起输出，这就是连接。  
连接主要有：  
1.内连接(inner join),匹配成功的内容才输出，不匹配的不输出  
a.等值连接 （匹配条件用on子句加上=号给出）  
b.自然连接(natural join) （不显式给出匹配条件，不写on子句，默认匹配两张表中同名字段），实际上就是等值内连接，只不过不需要写on子句。如果两张表中没有同名字段，返回两张表的笛卡尔积。  
c.不等值连接（匹配条件用on子句加上!=、<、<=、>、>=号给出）  
2.外连接(outer join),不管匹配成功不成功，都输出  
a.左连接(left outer join)  
左边表中不匹配的项也会输出。  
b.右连接（right outer join)  
右边表中不匹配的项也会输出。sqlite3暂不支持。  
c.全连接(full outer join)  
左、右两边表中不匹配的项都会输出。sqlite3暂不支持。  
3.交叉连接(cross join)。  
指定连接条件，类似于inner join  
不指定连接条件，返回笛卡尔积。  
  
  
举例如下：  
sqlite> create table stu(id integer primary key, name text, age integer);  
sqlite> create table score(id integer primary key, score integer);  
sqlite> select \* from student;  
1|小明|21|100|  
2|小李|23|90|  
3|小张|20|96|  
4|小李|26|100|90  
sqlite> insert into stu(id,name,age) select id, name, age from student;  
sqlite> select \* from stu;  
1|小明|21  
2|小李|23  
3|小张|20  
4|小李|26  
sqlite> insert into score(id, score) select id, score from student;  
sqlite> select \* from score;  
1|100  
2|90  
3|96  
4|100  
  
  
创建两张表stuinfo，score:  
create table stuinfo(id integer primary key, name text, age integer);  
create table score(id integer primary key, score integer);  
sqlite> insert into stuinfo values(1, "小明", 21);  
sqlite> insert into stuinfo values(2, "小李", 23);  
sqlite> insert into score values(1, 100);  
sqlite> insert into score values(2, 97);  
sqlite> insert into score values(3, 63);  
sqlite> insert into score values(4, 82);  
sqlite> select \* from stuinfo;  
1|小明|21  
2|小李|23  
sqlite> select \* from score;  
1|100  
2|97  
3|63  
4|82  
sqlite> insert into stuinfo values(1, "小明", 21);  
sqlite> insert into stuinfo values(2, "小李", 23);  
sqlite> insert into score values(1, 100);  
sqlite> insert into score values(2, 97);  
sqlite> insert into score values(3, 63);  
sqlite> insert into score values(4, 82);  
sqlite> select \* from stuinfo;  
1|小明|21  
2|小李|23  
sqlite> select \* from score;  
1|100  
2|97  
3|63  
4|82  
sqlite> select score.id, stuinfo.name, stuinfo.age, score.score from stuinfo, score where stuinfo.id=score.id;  
1|小明|21|100  
2|小李|23|97  
sqlite> select b.id, a.name, a.age, b.score from stuinfo a, score b where a.id=b.id;  
1|小明|21|100  
2|小李|23|97  
sqlite> select \* from stuinfo a natural join newscore b;  
1|小明|21|1|100  
1|小明|21|2|97  
1|小明|21|3|63  
1|小明|21|4|82  
2|小李|23|1|100  
2|小李|23|2|97  
2|小李|23|3|63  
2|小李|23|4|82  
sqlite> select \* from stuinfo a inner join newscore b on a.id=b.no;  
1|小明|21|1|100  
2|小李|23|2|97  
select \* from stuinfo a left outer join score b on a.id=b.id;  
1|小明|21|1|100  
2|小李|23|2|97  
sqlite> select \* from stuinfo a left outer join score b on a.id=b.id;  
1|小明|21|1|100  
2|小李|23|2|97  
sqlite> select \* from score a left outer join stuinfo b on a.id=b.id;  
1|100|1|小明|21  
2|97|2|小李|23  
3|63|||  
4|82|||  
sqlite> select \* from score a right join stuinfo b on a.id=b.id;  
Error: RIGHT and FULL OUTER JOINs are not currently supported  
sqlite> select \* from score a right outer join stuinfo b on a.id=b.id;  
Error: RIGHT and FULL OUTER JOINs are not currently supported  
sqlite> select \* from stuinfo a cross join score b;  
1|小明|21|1|100  
1|小明|21|2|97  
1|小明|21|3|63  
1|小明|21|4|82  
2|小李|23|1|100  
2|小李|23|2|97  
2|小李|23|3|63  
2|小李|23|4|82  
sqlite> select \* from score a cross join stuinfo b on a.id=b.id;  
1|100|1|小明|21  
2|97|2|小李|23  
sqlite> select \* from stuinfo a cross join score b on a.id=b.id;  
1|小明|21|1|100  
2|小李|23|2|97