mysql联合主键是联合索引 联合主键sql语句

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