sqlite创建表联合主键的sql写法、执行sql文件、不支持右连接、获取年份、case when 的使用

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' , '');