sqlite創建表時，聯合主鍵，要寫在建表語句最后一行，primary key ()，括號里面；

執行sql文件；使用 .read xxx.sql 命令；

下圖執行錯誤，應該是字段名含有中文，不能讀取sql文件；

?

case shen的使用示例；查詢出每門課程的及格人數和不及格人數；

使用分段[100-85],[85-70],[70-60],[<60]來統計各科成績，分別統計：各分數段人數，課程號和課程名稱；

查詢結果沒有顯示字段名，可能命令行的sqlite不支持中文；

看一下第二個查詢語句，提示錯誤：右和全外連接當前不支持；

?

獲取年份；不支持year()函數；使用strftime函數；

CMD操作過程如下；

Microsoft Windows [版本 6.1.7601]
Copyright (c) 2010 Microsoft Corporation. ?All rights reserved.

S:\sqlite3>sqlite3 test1.db
SQLite version 3.17.0 2017-02-13 16:02:40
Enter ".help" for usage hints.
sqlite> .database
main: S:\sqlite3\test1.db
sqlite> use test1.db
? ?...> ;
Error: near "use": syntax error
sqlite> create table score (
? ?...> 學號 int primary key not null,
? ?...> 課程號 int primary key not null,
? ?...> ;
Error: table "score" has more than one primary key
sqlite> create table score (
? ?...> 學號 varchar(50) primary key not null,
? ?...> 課程號 varchar(50) primary key not null,
? ?...> 成績 real not null
? ?...> );
Error: table "score" has more than one primary key
sqlite> create table score (
? ?...> 學號 varchar(50) not null,
? ?...> 課程號 varchar(50) not null,
? ?...> 成績 real not null,
? ?...> primary key (學號,課程號));
sqlite> create table course (
? ?...> 課程號 varchar(50) primary key not null,
? ?...> 課程名稱 varchar(50) not null,
? ?...> 教師號 varchar(50) not null);
sqlite> create table student (
? ?...> 學號 varchar(50) primary key not null,
? ?...> 姓名 varchar(50) not null,
? ?...> 出生日期 date not null,
? ?...> 性別 char(4) not null);
sqlite> create table teacher (
? ?...> 教師號 varchar(20) primary key not null,
? ?...> 教師姓名 varchar(20));
sqlite> .tables;
Error: unknown command or invalid arguments: ?"tables;". Enter ".help" for help
sqlite> .tables
course ? score ? ?student ?teacher
sqlite> .read test1.sql
Error: near line 1: table student has no column named ???
Error: near line 4: table student has no column named ???
Error: near line 7: table student has no column named ???
Error: near line 10: table student has no column named ???
Error: near line 13: table score has no column named ???
Error: near line 16: table score has no column named ???
Error: near line 19: table score has no column named ???
Error: near line 22: table score has no column named ???
Error: near line 25: table score has no column named ???
Error: near line 28: table score has no column named ???
Error: near line 31: table score has no column named ???
Error: near line 34: table score has no column named ???
Error: near line 37: table course has no column named ?γ??
Error: near line 40: table course has no column named ?γ??
Error: near line 43: table course has no column named ?γ??
Error: near line 46: table teacher has no column named ?????
Error: near line 49: table teacher has no column named ?????
Error: near line 52: table teacher has no column named ?????
Error: near line 55: table teacher has no column named ?????
sqlite> .schema student
CREATE TABLE student (
學號 varchar(50) primary key not null,
姓名 varchar(50) not null,
出生日期 date not null,
性別 char(4) not null);
sqlite> insert into student(學號,姓名,出生日期,性別) values('0001' , '猴子' , '1
989-01-01' , '男');
sqlite> delete from student;
sqlite> .read test1.sql
Error: near line 1: table student has no column named ???
Error: near line 3: table student has no column named ???
Error: near line 5: table student has no column named ???
Error: near line 7: table student has no column named ???
Error: near line 9: table score has no column named ???
Error: near line 11: table score has no column named ???
Error: near line 13: table score has no column named ???
Error: near line 15: table score has no column named ???
Error: near line 17: table score has no column named ???
Error: near line 19: table score has no column named ???
Error: near line 21: table score has no column named ???
Error: near line 23: table score has no column named ???
Error: near line 25: table course has no column named ?γ??
Error: near line 27: table course has no column named ?γ??
Error: near line 29: table course has no column named ?γ??
Error: near line 31: table teacher has no column named ?????
Error: near line 33: table teacher has no column named ?????
Error: near line 35: table teacher has no column named ?????
Error: near line 37: table teacher has no column named ?????
sqlite> insert into student(學號,姓名,出生日期,性別) values('0001' , '猴子' , '1
989-01-01' , '男');
sqlite>
sqlite> insert into student(學號,姓名,出生日期,性別) values('0002' , '猴子' , '1
990-12-21' , '女');
sqlite>
sqlite> insert into student(學號,姓名,出生日期,性別) values('0003' , '馬云' , '1
991-12-21' , '男');
sqlite>
sqlite> insert into student(學號,姓名,出生日期,性別) values('0004' , '王不二' ,
'1990-05-20' , '男');
sqlite>
sqlite> insert into score(學號,課程號,成績) values('0001' , '0001' , 80);
sqlite>
sqlite> insert into score(學號,課程號,成績) values('0001' , '0002' , 90);
sqlite>
sqlite> insert into score(學號,課程號,成績) values('0001' , '0003' , 99);
sqlite>
sqlite> insert into score(學號,課程號,成績) values('0002' , '0002' , 60);
sqlite>
sqlite> insert into score(學號,課程號,成績) values('0002' , '0003' , 80);
sqlite>
sqlite> insert into score(學號,課程號,成績) values('0003' , '0001' , 80);
sqlite>
sqlite> insert into score(學號,課程號,成績) values('0003' , '0002' , 80);
sqlite>
sqlite> insert into score(學號,課程號,成績) values('0003' , '0003' , 80);
sqlite>
sqlite> insert into course(課程號,課程名稱,教師號) values ('0001' , '語文' , '00
02');
sqlite>
sqlite> insert into course(課程號,課程名稱,教師號) values ('0002' , '數學' , '00
01');
sqlite>
sqlite> insert into course(課程號,課程名稱,教師號) values ('0003' , '英語' , '00
03');
sqlite>
sqlite> insert into teacher(教師號,教師姓名) values ('0001' , '孟扎扎');
sqlite>
sqlite> insert into teacher(教師號,教師姓名) values ('0002' , '馬騰騰');
sqlite>
sqlite> insert into teacher(教師號,教師姓名) values ('0003' , null);
sqlite>
sqlite> insert into teacher(教師號,教師姓名) values ('0004' , '');
sqlite> select * from score;
0001|0001|80.0
0001|0002|90.0
0001|0003|99.0
0002|0002|60.0
0002|0003|80.0
0003|0001|80.0
0003|0002|80.0
0003|0003|80.0
sqlite> select 課程號,
? ?...> sum(case when 成績>=60 then 1
? ?...> ? ? ? ? ?else 0
? ?...> ? ? end) as 及格人數,
? ?...> sum(case when 成績 < ?60 then 1
? ?...> ? ? ? ? ?else 0
? ?...> ? ? end) as 不及格人數
? ?...> from score
? ?...> group by 課程號;
0001|2|0
0002|3|0
0003|3|0
sqlite> select a.課程號,b.課程名稱,
? ?...> sum(case when 成績 between 85 and 100
? ?...> ? ? ? ? ?then 1 else 0 end) as '[100-85]',
? ?...> sum(case when 成績 >=70 and 成績<85
? ?...> ? ? ? ? ?then 1 else 0 end) as '[85-70]',
? ?...> sum(case when 成績>=60 and 成績<70
? ?...> ? ? ? ? ?then 1 else 0 end) as '[70-60]',
? ?...> sum(case when 成績<60 then 1 else 0 end) as '[<60]'
? ?...> from score as a right join course as b
? ?...> on a.課程號=b.課程號
? ?...> group by a.課程號,b.課程名稱;
Error: RIGHT and FULL OUTER JOINs are not currently supported
sqlite> select 學號,姓名 from student where year(出生日期)=1990;
Error: no such function: year
sqlite> select 學號,姓名 from student where strftime('%Y', 出生日期)='1990';
0002|猴子
0004|王不二
sqlite>

?

test1.sq;

insert into student(學號,姓名,出生日期,性別) values('0001' , '猴子' , '1989-01-01' , '男');insert into student(學號,姓名,出生日期,性別) values('0002' , '猴子' , '1990-12-21' , '女');insert into student(學號,姓名,出生日期,性別) values('0003' , '馬云' , '1991-12-21' , '男');insert into student(學號,姓名,出生日期,性別) values('0004' , '王不二' , '1990-05-20' , '男');insert into score(學號,課程號,成績) values('0001' , '0001' , 80);insert into score(學號,課程號,成績) values('0001' , '0002' , 90);insert into score(學號,課程號,成績) values('0001' , '0003' , 99);insert into score(學號,課程號,成績) values('0002' , '0002' , 60);insert into score(學號,課程號,成績) values('0002' , '0003' , 80);insert into score(學號,課程號,成績) values('0003' , '0001' , 80);insert into score(學號,課程號,成績) values('0003' , '0002' , 80);insert into score(學號,課程號,成績) values('0003' , '0003' , 80);insert into course(課程號,課程名稱,教師號) values ('0001' , '語文' , '0002');insert into course(課程號,課程名稱,教師號) values ('0002' , '數學' , '0001');insert into course(課程號,課程名稱,教師號) values ('0003' , '英語' , '0003');insert into teacher(教師號,教師姓名) values ('0001' , '孟扎扎');insert into teacher(教師號,教師姓名) values ('0002' , '馬騰騰');insert into teacher(教師號,教師姓名) values ('0003' , null);insert into teacher(教師號,教師姓名) values ('0004' , '');

?

《新程序員》：云原生和全面數字化實踐50位技術專家共同創作，文字、視頻、音頻交互閱讀

總結

以上是生活随笔為你收集整理的sqlite创建表联合主键的sql写法、执行sql文件、不支持右连接、获取年份、case when 的使用的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。