/************************************** 創(chuàng)建表 *********************************************/
//創(chuàng)建表-定義主鍵
CREATE TABLE T_Person(
FId INTEGER,
FName VARCHAR(20),
FAge INTEGER DEFAULT '18',
PRIMARY KEY (FId)
)
/************************************** 表關(guān)聯(lián)-外鍵 *********************************************/
//公司表
CREATE TABLE T_FIRM(
FId INTEGER,
FName VARCHAR(20),
FAddress VARCHAR(10),
PRIMARY KEY (FId)
)
//員工表-外鍵CId 關(guān)聯(lián)T_FIRM表FId
CREATE TABLE T_STAFF(
PId INTEGER,
PName VARCHAR(20),
CId INTEGER,
PRIMARY KEY (FId),
FOREIGN KEY (CId) REFERENCES T_FIRM (FId)
)
/************************************** 修改數(shù)據(jù)庫表 *********************************************/
//添加列字段
ALTER TABLE T_STAFF ADD FSex VARCHAR(10)
//去除列字段
ALTER TABLE T_STAFF DROP Column FSex
/************************************** 數(shù)據(jù)的增改刪 *********************************************/
//建表
create table T_PERSON(PId INTEGER ,PName VARCHAR ,PSex VARCHAR,PAge integer ,PRIMARY KEY (PId))
//插入數(shù)據(jù)
insert into T_PERSON (PId , PName , PSex , PAge ) values (1,'Jack','男',13)
//修改表中的數(shù)據(jù)
update T_PERSON set PAge='18',PSex='男' where PName='Juice'
//刪除表中數(shù)據(jù)
delete frome T_PERSON where PAge>17
/************************************** 數(shù)據(jù)的簡(jiǎn)單查詢 ******************************************/
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---------聚合計(jì)算
/************************************** 數(shù)據(jù)分組 **********************************************/
//使用GROUP BY子句來完成數(shù)據(jù)的分組!分組的目的也是為了進(jìn)行聚合運(yùn)算~可以把這個(gè)分組看成一個(gè)臨時(shí)的結(jié)果集
//數(shù)據(jù)分組~將表中的數(shù)據(jù),按表中的字段來進(jìn)行分組,然后再對(duì)這些分組進(jìn)行聚合運(yùn)算,MIN,MAX,SUM,AVG,COUNT的計(jì)算
SELECT PCity FROM T_Product GROUP BY PCity
SELECT PCity,COUNT(PCity) AS '數(shù)量' FROM T_Product where PKind='日用品類' GROUP BY PCity
select PCity,max(PPrice) from T_Product group by PCity
select PCity,PKind,count(*) as '數(shù)量' from T_Product group by PCity,PKind
select PCity,max(PPrice) as '價(jià)格最大' ,min(PPrice) as '價(jià)格最小' from T_Product group by PPrice
/*********************************** HAVING語句分組進(jìn)行過濾 ***********************************/
//聚合函數(shù)不能在WHERE語句中使用
//先把表中數(shù)據(jù)按PCity來分組,然后篩選出記錄條數(shù)大于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條數(shù)據(jù)開始取3條
/************************************** 抑制數(shù)據(jù)重復(fù) ****************************************/
//DISTINCT是針對(duì)整個(gè)結(jié)果集來進(jìn)行數(shù)據(jù)重復(fù)抑制的,而不是針對(duì)每一個(gè)列!!!
SELECT DISTINCT PCity FROM T_Product
SELECT DISTINCT PKind,PCity FROM T_Product
/************************************** 字段間計(jì)算 ****************************************/
select PName,FNum,PPrice,PPriceFNum as '商品總價(jià)' from T_Product
select ,PPriceFNum as '總價(jià)' from T_Product where PPriceFNum>5000
/************************************** 聯(lián)合結(jié)果集 ****************************************/
//UNION運(yùn)算符-----把多個(gè)查詢結(jié)果集放到一個(gè)結(jié)果集
SELECT '商品最高單價(jià)',MAX(PPrice) FROM T_Product
UNION
SELECT '商品最低單價(jià)',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)
/********************************** 表連接來操作多個(gè)表 *************************************/
1.內(nèi)連接(INNER JOIN) ------返回到結(jié)果集中僅包含符合條件的行
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) ------返回到結(jié)果集中的不僅包含符合條件的行,還包括左表(左外連接)、右表(右外連接)或者兩個(gè)連接表(完全連接)中的所有數(shù)據(jù)行!
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)---即連接關(guān)鍵字的兩邊都是同一個(gè)表
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
