sqlite筆記（akaedu）

標籤：

1.建立sql表
create table student(id integer primary key, name text, score integer)；

2.插入一條記錄
insert into student(score, id, name) values(100, 1, ‘XiaoMing‘);
insert into student values(2, "XiaoZhang", 90);
//主鍵沒有的給的話，會自動分配一個給你的記錄，其他沒有not null約束的欄位你沒有提供的話，預設是可以為空白(null)的
insert into student(name) values("XiaoLiu");

3.簡單的查詢語句
select id, name from student;
select * from student;

4.修改一條記錄（where子句很重要，沒有where則修改所有記錄）
update student set score=80, name="XiaoWu" where id=3;

5.刪除一條記錄
delete from student; //沒有where子句刪除所有記錄
delete from student where id=3;

6.資料的大量匯入
這不是SQL語句，是sqlite3工具的一條命令
.import 1.txt student

7.修改表的結構
alter table student add score2 integer;
可以使用命令.schema student查看student表結構。
alter table student rename to newstudent;修改表名
但是不支援修改某一個現有欄位。（沒有modify操作)

8.備份一張表的內容(備份表內容，但是表結構可能有差異）
備份student表的所有內容到新的表newstudent
create table newstudent as select * from student;
備份student表的頭三列到新的表newstudent中
create table newstudent as select id, name, score from student;

9.刪除表
drop table student;刪除student表

10.複雜的查詢語句
select * from student where score>80;查詢成績大於80分的同學
select * from student where score>87 and score<100;
select * from student where score between 87 and 100;
where score between 87 and 100;
等價於 where score>=87 and score<=100;

模糊查詢
select * from student where score like "9%";
select * from student where name like "%g";
select * from student where score like "87";等價於select * from student where score=87;

排序輸出
select * from student order by score desc; 降序
select * from student order by score asc;升序
order by預設是升序排列

找80分以上成績最低的兩位學員：
select * from student where score>=80 order by score asc limit 2;

找班上成績第三名的同學:
select * from student order by score desc limit 1 offset 2;

找班上成績最高的一位或幾位同學：
select * from student where score=(select score from student order by score desc limit 1);

group by子句(having是group by的條件子句）
select dep, sum(salory) from employee where salory>4000 group by dep; //按部門列出每個月每個部門所發薪水總和
select name from employee group by name, salory, dep having count(*)>1;//求出出現重複錄入的資料的人的姓名

串連兩張表的內容：
sqlite> select * from student;
1|XiaoMing|21
2|XiaoZhang|22
3|XiaoWu|19
sqlite> select * from score;
1|100
2|96

1.where子句串連兩張表
select a.id, a.name, a.age, b.score from student a, score b where a.id=b.id;
1|XiaoMing|21|100
2|XiaoZhang|22|96

2.自然串連（要求兩張表中要有相同名字的欄位,欄位值相同的記錄被串連到一起輸出）
select id, name, age, score from student natural join score;
1|XiaoMing|21|100
2|XiaoZhang|22|96
如果兩張表中沒有相同名字的欄位（student的id，score的id名字相同),串連不能成功，輸出兩張表的笛卡爾積
select id, name, age, nid, score from student natural join newscore;
1|XiaoMing|21|1|100
1|XiaoMing|21|2|96
2|XiaoZhang|22|1|100
2|XiaoZhang|22|2|96
3|XiaoWu|19|1|100
3|XiaoWu|19|2|96

左外串連（左邊的表中，即使在右邊表內沒有串連成功的項也會輸出。）
select a.id, name, age, score from student a left outer join score b on a.id=b.id;
1|XiaoMing|21|100
2|XiaoZhang|22|96
3|XiaoWu|19|     =>這一項因為左外串連而輸出,注意和下面的比較

select a.id, name, age, score from score b left outer join student a on a.id=b.id;
1|XiaoMing|21|100
2|XiaoZhang|22|96

sqlite筆記（akaedu）