/************************************** 创建表 *********************************************/
//创建表-定义主键
CREATE TABLE T_Person(
FId INTEGER,
FName VARCHAR(20),
FAge INTEGER DEFAULT '18',
PRIMARY KEY (FId)
)
/************************************** 表关联-外键 *********************************************/
//公司表
CREATE TABLE T_FIRM(
FId INTEGER,
FName VARCHAR(20),
FAddress VARCHAR(10),
PRIMARY KEY (FId)
)
//员工表-外键CId 关联T_FIRM表FId
CREATE TABLE T_STAFF(
PId INTEGER,
PName VARCHAR(20),
CId INTEGER,
PRIMARY KEY (FId),
FOREIGN KEY (CId) REFERENCES T_FIRM (FId)
)
/************************************** 修改数据库表 *********************************************/
//添加列字段
ALTER TABLE T_STAFF ADD FSex VARCHAR(10)
//去除列字段
ALTER TABLE T_STAFF DROP Column FSex
/************************************** 数据的增改删 *********************************************/
//建表
create table T_PERSON(PId INTEGER ,PName VARCHAR ,PSex VARCHAR,PAge integer ,PRIMARY KEY (PId))
//插入数据
insert into T_PERSON (PId , PName , PSex , PAge ) values (1,'Jack','男',13)
//修改表中的数据
update T_PERSON set PAge='18',PSex='男' where PName='Juice'
//删除表中数据
delete frome T_PERSON where PAge>17
/************************************** 数据的简单查询 ******************************************/
select FName,FAge as '年龄',FSalary from T_Worker
select FName from T_Worker where FAge>30 and FSalary>5000
select * from T_Worker order by FAge asc
/************************************** 过滤查询 **********************************************/
SELECT * FROM T_Worker WHERE (FAge BETWEEN 18 AND 20) OR ( FAge BETWEEN 30 AND 50 )
//MIN,MAX,SUM,AVG,COUNT---------聚合计算
/************************************** 数据分组 **********************************************/
//使用GROUP BY子句来完成数据的分组!分组的目的也是为了进行聚合运算~可以把这个分组看成一个临时的结果集
//数据分组~将表中的数据,按表中的字段来进行分组,然后再对这些分组进行聚合运算,MIN,MAX,SUM,AVG,COUNT的计算
SELECT PCity FROM T_Product GROUP BY PCity
SELECT PCity,COUNT(PCity) AS '数量' FROM T_Product where PKind='日用品类' GROUP BY PCity
select PCity,max(PPrice) from T_Product group by PCity
select PCity,PKind,count(*) as '数量' from T_Product group by PCity,PKind
select PCity,max(PPrice) as '价格最大' ,min(PPrice) as '价格最小' from T_Product group by PPrice
/*********************************** HAVING语句分组进行过滤 ***********************************/
//聚合函数不能在WHERE语句中使用
//先把表中数据按PCity来分组,然后筛选出记录条数大于1的部分!
select PCity from T_Product group by PCity HAVING count(PCity)>1
select PCity,max(PPrice) from T_Product group by PCity HAVING count(PCity)>1
select PCity,min(PPrice) from T_Product group by PCity HAVING count(PCity)>1
//1.HAVING子句需要放在GROUP BY子句之后!
//2.HAVING子句不能包含未分组的列名,就是没写在GROUP BY后面的~
/**************************************** limit分页 *****************************************/
select * from T_Product limit 5 , 3
//5 = startIndex, 3 = size 第5条数据开始取3条
/************************************** 抑制数据重复 ****************************************/
//DISTINCT是针对整个结果集来进行数据重复抑制的,而不是针对每一个列!!!
SELECT DISTINCT PCity FROM T_Product
SELECT DISTINCT PKind,PCity FROM T_Product
/************************************** 字段间计算 ****************************************/
select PName,FNum,PPrice,PPriceFNum as '商品总价' from T_Product
select ,PPriceFNum as '总价' from T_Product where PPriceFNum>5000
/************************************** 联合结果集 ****************************************/
//UNION运算符-----把多个查询结果集放到一个结果集
SELECT '商品最高单价',MAX(PPrice) FROM T_Product
UNION
SELECT '商品最低单价',MIN(PPrice) FROM T_Product
UNION
SELECT '员工最高薪资',MAX(FSalary) FROM T_Worker
UNION
SELECT '员工最低薪资',MIN(FSalary) FROM T_Worker
/************************************** 索引与约束 ****************************************/
CREATE INDEX idx_person_union ON T_Worker(FName,FAge,FSex)
ALTER TABLE T_Person ADD PRIMARY KEY(FId)
ALTER TABLE T_Person ADD CONSTRAINT FOREIGN KEY(PId) REFERENCES T_FIRM(FId)
/********************************** 表连接来操作多个表 *************************************/
1.内连接(INNER JOIN) ------返回到结果集中仅包含符合条件的行
select * from T_Student inner join T_Class on T_Student.Class_id=T_Class.Class_id
select SId,SName,Class_name from T_Student inner join T_Class on T_Student.Class_id=T_Class.Class_id
select SId,SName,Class_name,Dorm_name from T_Student inner join T_Class on T_Student.Class_id=T_Class.Class_id inner join T_Dorm on T_Student.Dorm_id=T_Dorm.Dorm_id
2.外连接(LEFT OUTER JOIN === RIGHT OUT JOIN) ------返回到结果集中的不仅包含符合条件的行,还包括左表(左外连接)、右表(右外连接)或者两个连接表(完全连接)中的所有数据行!
select * from T_Student left outer join T_Class on T_Student.Class_id=T_Class.Class_id
select * from T_Student right outer join T_Class on T_Student.Class_id=T_Class.Class_id //不支持sqlite
select * from T_Student full outer join T_Class on T_Student.Class_id=T_Class.Class_id //不支持sqlite
3.交叉连接(cross join)
SELECT * FROM T_Student cross join T_Class
4.自连接(INNER JOIN)---即连接关键字的两边都是同一个表
select a.C_Name,b.C_Name from T_City as a inner join T_City as b on a.C_Id=b.B_Id
