SQLite之SQL语言

--==========================--

-- 如果存在Classes表,则删除该表

DROP TABLE IF EXISTS [Classes];

-- 创建Classes表

CREATE TABLE [Classes]

(

    [ID] INTEGER PRIMARY KEY,        -- 班级编号,主键

    [Name] STRING NULL DEFAULT ''   -- 班级名称

);

-- 添加数据到班级表

INSERT into Classes (ID, Name) values (1, "iPhone Class1");

INSERT into Classes (ID, Name) values (2, "iPhone Class2"); 

INSERT into Classes values (3, "iPhone Class3"); 

INSERT into Classes(Name, ID) values ("iPhone Class4", 4);

--==========================--

DROP TABLE IF EXISTS [Students];

CREATE TABLE [Students]

(

    [ID] INTEGER PRIMARY KEY,           -- 学号,主键

    [Name] TEXT NULL DEFAULT '',     -- 姓名

    [Gender] INTEGER NULL DEFAULT 0,    -- 0: 男, 1: 女 

    [Age] INTEGER NULL DEFAULT 0,       -- 年龄

    [ClassID] [INTEGER] NULL DEFAULT ''    -- 班级编号

);

-- 一班同学

INSERT into Students (Name, Gender, Age, ClassID) values ("刘德华", 0, 25, 1);

INSERT into Students (Name, Gender, Age, ClassID) values ("那英", 1, 23, 1);

INSERT into Students (Name, Gender, Age, ClassID) values ("周杰伦", 0, 19, 1);

-- 二班同学

INSERT into Students (Name, Gender, Age, ClassID) values ("刘欢", 0, 22, 2);

INSERT into Students (Name, Gender, Age, ClassID) values ("张韶涵", 1, 22, 2);

INSERT into Students (Name, Gender, Age, ClassID) values ("刘谦", 0, 23, 2);

INSERT into Students (Name, Gender, Age, ClassID) values ("戴安娜", 1, 24, 2);

--==========================--

-- 任务: 年龄为22的同学的所有信息?

Select * from Students where Age = 22

-- 任务: 年龄为22的同学的姓名

Select Name from Students where Age = 22

-- 任务: Students表中刘欢的所有信息

Select * from Students where Name like "刘欢"

-- 任务: 刘欢的学号是?

Select ID from Students where Name like "刘欢"

-- 任务: 按照年龄从小到大排列学生

Select * from Students order by Age

-- 任务: 从大到小排列学生

Select * from Students order by Age desc

-- 任务: 一共有几个班?

Select count(*) from Classes;   -- Count 关键字

-- 任务: 每个班有几个学生?

Select ClassID, count(*) from Students group by ClassID

Select 

     ClassID, 

     Count(ID) as StudentCount

from 

     Students 

group by ClassID 

order by StudentCount

-- 任务: 二班新增加学生 唐伯虎

INSERT into Students (Name, Gender, ClassID) values ("唐伯虎", 0, 2);

-- Error insert order

INSERT into Students (ClassID, Name, Gender) values ("唐小虎", 0, 1);

INSERT into Students (ClassID, Name, Gender) values (1, "唐小虎", 0);

Update 

    Students

Set

    Name = '唐小虎',

    Gender = 0,

    Age = 22,

    ClassID = 1

Where

    ID = 8;

-- 任务: 戴安娜同学退学了

Delete from Students where Name like "戴安娜"

-- 任务: 学生的最大年纪是?

Select Max(Age) from Students

-- 任务: 学生的最小年纪是?

Select Min(Age) from Students

-- 任务: 学生年纪的总和是?

Select Sum(Age) as TotalAge from Students

-- 任务: 学生的平均年龄?

-- Average

Select Avg(Age) as TotalAge from Students 

-- 其他任务

Select distinct Name from Classes;

Select count(distinct Name) from Classes;

--Select ClassID, Count(*) from Students group by ClassID;

--Select Students.ID, Students.Name, Classes.Name from Students, Classes

--where Classes.ID = Students.ClassID;

--Select distinct ClassID as AttendClassID, Classes.Name from Students, Classes

--Where Classes.ID = AttendClassID;

Select distinct ClassID, Classes.Name from Students, Classes

Where Classes.ID = Students.ClassID;

--Select distinct ClassID as AttendClassID  from Students

--Select count(distinct ClassID) from Students;

--Delete from Classes where ID >= 4;

 Data Type:     BULL    INTEGER    REAL    BLOB     TEXT

 create table [Students]

 {   [ID] integer primary key,

     [Name] text null default ' ',

     [Age] integer null default 0,

     [ClassID] text null default ' '

 }

 

 delete from classes;

 delete from classes where id = 4;

 delete from classes where name = "Tracy";

 select * from Students where Age = 22;

 select Name from Students where Age = 22

 

 select * from Students where Age = 22 order by ID desc;

 select count(*) from Classes;

 select max(*) from Students;

 select min(*) from Students;

 

 select ClassID, count(*) from Students group by ClassID;

 select ClassID, count(ID) as StudentCount group by ClassID order by  StudentCount;         --别名

 insert into Students (Name, Gender, ClassID) values ("Tracy", 0, 2);

 insert into Classes values (3, "iphone 8");

 

 update Students set

   Name = "Tracy",

   Gender = 0,

   Age = 22,

   ClassID = 7

 where ID = 11

 

 select sum(Age) as TotalAge from Students;

 select avg(Age) as AvgAge from Students;

 select distinct Name form Students;

 联合查询 (笛卡儿基)

 select Students.ID, Students.Name, Class.Name from Students, Classes where Classes.ID = Students.ClassID;