1、count 使用
- 1.1 查詢employee表中記錄數(shù):
SELECT COUNT(*) FROM employee;
- 1.2 查詢員工表中有績(jī)效的人數(shù):
SELECT COUNT(performance) FROM employee;
- 1.3 查詢員工表中月薪大于2500的人數(shù):
SELECT COUNT(*) FROM employee WHERE salary > 2500;
- 1.4 統(tǒng)計(jì)月薪與績(jī)效之和大于5000元的人數(shù):
SELECT COUNT(*) FROM employee WHERE IFNULL(salary,0)+IFNULL(performance,0)>5000;
- 1.5 查詢有績(jī)效的人數(shù)片择，和有管理費(fèi)的人數(shù):
SELECT COUNT(performance),COUNT(manage) FROM employee;
2哀澈、SUM 和MAX和MIN 的使用
- 1召娜、查詢所有雇員月薪和
SELECT SUM(salary) FROM employee;
- 2性锭、查詢所有雇員月薪和,以及所有雇員績(jī)效和
SELECT SUM(salary),SUM(performance) FROM employee;
- 3启涯、 查詢所有雇員月薪+績(jī)效和
SELECT SUM(salary+IFNULL(performance,0)) FROM employee;
- 4整陌、統(tǒng)計(jì)所有員工平均工資
SELECT AVG(salary) FROM employee;
- 5、 查詢最高工資和最低工資
SELECT MAX(salary),MIN(salary) FROM employee;
3脆侮、分組查詢
- 1锌畸、 將查詢結(jié)果按照1個(gè)或者多個(gè)字段進(jìn)行分組,字段值相同的為1組
SELECT gender FROM employee GROUP BY gender;
- 2、把相同的數(shù)據(jù)去掉
SELECT gender,GROUP_CONCAT(name) FROM employee GROUP BY gender;
4靖避、分組查詢-使用
- 1潭枣、在一組里面所有男性名字和女性名字
SELECT gender,GROUP_CONCAT('name') FROM employee GROUP BY gender;
- 2、 所有男性工資和女性工資
SELECT gender,GROUP_CONCAT(salary) FROM employee GROUP BY gender;
- 3幻捏、對(duì)某一個(gè)部門(mén)進(jìn)行分組,查看每個(gè)部門(mén)薪資信息都羅列出來(lái)
SELECT department,GROUP_CONCAT(salary) FROM employee GROUP BY department;
5盆犁、GROUP BY + 聚合函數(shù)
- 1、查看部門(mén)平均薪資
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;
- 2篡九、查看每個(gè)部門(mén)的最高薪資
SELECT department,GROUP_CONCAT(salary),MAX(salary) FROM employee GROUP BY department;
- 3谐岁、查看每個(gè)部門(mén)的最低薪資
SELECT department,GROUP_CONCAT(salary),MIN(salary) FROM employee GROUP BY department;
- 4、查詢每個(gè)部門(mén)的部門(mén)名稱(chēng)和每個(gè)部門(mén)的工資和
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;
- 5瓮下、查詢每個(gè)部門(mén)的部門(mén)名稱(chēng)和每個(gè)部門(mén)的人數(shù)
SELECT department,GROUP_CONCAT(name),COUNT(*) FROM employee GROUP BY department;
- 6翰铡、查詢每個(gè)部門(mén)的部門(mén)名稱(chēng)以及每個(gè)部門(mén)工資大于1500的人數(shù)
SELECT name,salary FROM employee WHERE salary>1500;
SELECT department,GROUP_CONCAT(salary),COUNT(*) FROM employee WHERE salary>1500 GROUP BY department;
6、GROUP BY + having
- 1讽坏、把所有部門(mén)進(jìn)行分組
SELECT department FROM employee GROUP BY department;
- 2锭魔、每一組有哪些薪資,以及薪資總和
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;
- 3、完成
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department HAVING SUM(salary)>9000;
7路呜、HAVING和WHERE的區(qū)別
HAVING是在分組后對(duì)數(shù)據(jù)進(jìn)行過(guò)濾;
WHERE是在分組前對(duì)數(shù)據(jù)進(jìn)行過(guò)濾;
HAVING后面可以使用分組函數(shù)(統(tǒng)計(jì)函數(shù));
WHERE后面不可以使用分組函數(shù);
WHERE是對(duì)分組前記錄的條件,如果某行記錄沒(méi)有滿足where子語(yǔ)句的條件,那么這行記錄不參與分組;而HAVING是對(duì)分組后的數(shù)據(jù)約束;
- 1迷捧、查詢工資大于2000的,工資總和大于6000的部門(mén)名稱(chēng)以及工資和;
- 各部門(mén)工資
- 各部門(mén)工資總和
- 各部門(mén)工資總和大于6000
- 各部門(mén)工資總和大于6000降序排序
- 2.、查詢工資大于2000
SELECT salary FROM employee WHERE salary>2000;
- 3胀葱、工資總和
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee WHERE salary > 2000 GROUP BY department HAVING SUM(salary)>9000;
ORDER BY SUM(salary) DESC;
8漠秋、書(shū)寫(xiě)順序，執(zhí)行順序
書(shū)寫(xiě)順序SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> LIMIT
執(zhí)行順序FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY => LIMIT
分頁(yè)
-
1抵屿、 LIMIT
image.png
SELECT * FROM employee
LIMIT (curPage-1)*pageSize,pageSize;
數(shù)據(jù)完整性
- 1庆锦、完整性分類(lèi)：
實(shí)體完整性
域完整性
引用完整性
- 2、什么是實(shí)體完整性
表中的一行(一條記錄)代表一個(gè)實(shí)體(entity)
- 3轧葛、實(shí)體完整性的作用
標(biāo)識(shí)每一行數(shù)據(jù)不重復(fù).行級(jí)約束
- 4搂抒、約束類(lèi)型
主鍵約束(primary key)
唯一約束(unique)
自動(dòng)增長(zhǎng)(auto_increment)
1、 主鍵約束
- 1尿扯、特點(diǎn):
每個(gè)表中要有一個(gè)主鍵
數(shù)據(jù)唯一,且不能為null
- 2求晶、添加方式:
CREATE TABLE 表名(字段名1 數(shù)據(jù)類(lèi)型 primary key,字段名2 數(shù)據(jù)類(lèi)型);
- 3、舉例:創(chuàng)建person表
CREATE TABLE person(ID BIGINT primary key,NAME VARCHAR(50))
CREATE TABLE 表名(字段1 數(shù)據(jù)類(lèi)型,字段名2 數(shù)據(jù)類(lèi)型,primary key(要設(shè)置主鍵的字段));
- 4衷笋、舉例創(chuàng)建
CREATE TABLE person(
ID BIGINT,
name VARCHAR(50),
age INT,
primary key(ID)
);
2芳杏、聯(lián)合主鍵：兩個(gè)字段數(shù)據(jù)同時(shí)相同時(shí),才違反聯(lián)合主鍵約束
- 1、創(chuàng)建聯(lián)合主鍵
CREATE TABLE 表名(字段1 數(shù)據(jù)類(lèi)型,字段2 數(shù)據(jù)類(lèi)型,primary key(主鍵1,主鍵2));
- 2、舉例
CREATE TABLE student(
ID BIGINT,
snum BIGINT,
name VARCHAR(50),
primary key(ID,snum))
- 3爵赵、先創(chuàng)建表再添加主鍵
CREATE TABLE student(sid INT,name VARCHAR(50));
- 4吝秕、如果表中你之前已經(jīng)添加了數(shù)據(jù),數(shù)據(jù)不符合主鍵規(guī)則就會(huì)報(bào)錯(cuò)
ALTER TABLE student ADD CONSTRAINT primary key(sid);
3、唯一約束
- 1亚再、特點(diǎn)
指定列的數(shù)據(jù)不能重復(fù)
可以為空值
- 2郭膛、格式:
CREATE TABLE students(
id INT primary key,
name VARCHAR(50) UNIQUE
)
4、自動(dòng)增長(zhǎng)列
- 1氛悬、特點(diǎn)
指定列的數(shù)據(jù)自動(dòng)增長(zhǎng)
即使數(shù)據(jù)刪除,還是從刪除的序號(hào)繼續(xù)往下
- 2则剃、格式
CREATE TABLE 表名(
字段名1 數(shù)據(jù)類(lèi)型 primary key AUTO_INCREMENT,
字段2 數(shù)據(jù)類(lèi)型 UNIQUE);
- 3、舉例
CREATE TABLE 表名(
字段名1 數(shù)據(jù)類(lèi)型 primary key AUTO_INCREMENT,
字段2 數(shù)據(jù)類(lèi)型 UNIQUE);
5如捅、域完整性
- 1棍现、使用
限制此單元格的數(shù)據(jù)正確,不對(duì)照此列的其他單元格比較
域代表當(dāng)前的單元格
- 2、數(shù)據(jù)類(lèi)型
數(shù)值類(lèi)型
日期類(lèi)型
字符串類(lèi)型
- 3镜遣、非空約束(NOT NULL)
CREATE TABLE stu(
id int primary key auto_increment,
name varchar(20) unique not null,
gender char(1) default '男'
)
- 4默認(rèn)值約束(DEFAULT)
CREATE TABLE 表名(
字段名1 數(shù)據(jù)類(lèi)型 primary key AUTO_INCREMENT,
字段2 數(shù)據(jù)類(lèi)型 UNIQUE NOT NULL,
字段3 數(shù)據(jù)類(lèi)型 DEFAULT '默認(rèn)值');
- 5己肮、舉例:
CREATE TABLE students(
id INT primary key AUTO_INCREMENT,
name VARCHAR(20) UNIQUE NOT NULL,
gender CHAR(1) DEFAULT '男'
);
6、參照完整性
- 什么是參照完整性
(1)是指表與表之間的一種對(duì)應(yīng)關(guān)系
(2)通常情況下可以通過(guò)設(shè)置兩表之間的主鍵悲关、外鍵關(guān)系,或者編寫(xiě)兩表的觸發(fā)器來(lái)實(shí)現(xiàn)
(3)有對(duì)應(yīng)參照完整性的兩張表格,再對(duì)他們進(jìn)行數(shù)據(jù)插入,更新,刪除的過(guò)程中,系統(tǒng)都會(huì)將被修改表格與另外一張對(duì)應(yīng)表格進(jìn)行對(duì)照,從而阻止一些不正確的數(shù)據(jù)的操作
2谎僻、數(shù)據(jù)庫(kù)的主鍵和外鍵類(lèi)型一定要一致
-
3、參照完整性添加
（1）通過(guò)一張表里面的某個(gè)字段,關(guān)聯(lián)另外一張表
image.png
（2）對(duì)已有表添加外鍵關(guān)系
ALTER TABLE score ADD CONSTRAINT sc_st_fk FOREIGN KEY(sid) REFERENCES student(id);
7寓辱、多表查詢
- 1. 一對(duì)一
一夫一妻(開(kāi)發(fā)中使用比較少)
- 2艘绍、一對(duì)多關(guān)系
一個(gè)人可以擁有多輛汽車(chē),要求查詢某個(gè)人擁有的所有車(chē)輛
創(chuàng)建Person表
- 人
CREATE TABLE person(
p_id INT PRIMARY KEY AUTO_INCREMENT,
p_name VARCHAR(50),
p_age INT,
p_sex CHAR(1)
);
- 車(chē)
CREATE TABLE car(
c_id INT PRIMARY KEY AUTO_INCREMENT,
c_name VARCHAR(50),
c_color VARCHAR(25),
p_id INT,
CONSTRAINT fk_Person FOREIGE KEY(c_id) REFERENCES person(p_id)
);
- 3、多對(duì)多關(guān)系
例如:學(xué)生選課,一個(gè)學(xué)生可以選修多門(mén)課程,每門(mén)課程可以供多個(gè)學(xué)生選擇
再例如: 老師和學(xué)生的多對(duì)多關(guān)系(一個(gè)老師有多個(gè)學(xué)生,一個(gè)學(xué)生有多個(gè)老師)
image.png
1秫筏、創(chuàng)建老師表
CREATE TABLE teacher(
t_id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(50),
t_age INT,
t_genger CHAR(1) DEFAULT '男'
);
2诱鞠、創(chuàng)建學(xué)生表
CREATE TABLE students(
s_id INT PRIMARY KEY AUTO_INCREMENT,
s_name VARCHAR(50) NOT NULL,
s_age INT,
s_gender CHAR(1) DEFAULT '男'
);
3、中間表(創(chuàng)建學(xué)生與老師 關(guān)系表)
CREATE TABLE tea_stu_rel(
t_id INT,
s_id INT
);
4这敬、添加外鍵
ALTER TABLE tea_stu_rel
ADD CONSTRAINT fk_tid
FOREIGE KEY(t_id)
REFERENCES teacher(t_id);
ALTER TABLE tea_stu_rel
ADD CONSTRAINT fk_sid
FOREIGE KEY(sid)
REFERENCES students(sid);
8航夺、合并結(jié)果集UNION與UNION_ALL
1、UNION
合并時(shí)去除重復(fù)記錄2崔涂、 UNION ALL
合并時(shí)不去除重復(fù)記錄3阳掐、格式
SELECT * FROM 表1 UNION SELECT * FROM 表2;
SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;
- 4、創(chuàng)建表
CREATE TABLE A(name VARCHAR(10),score INT);
CREATE TABLE B(name VARCHAR(10),score INT);
INSERT INTO A VALUES('a',10),('b',20),('c',30);
INSERT INTO A VALUES('a',10),('b',20),('c',40);
- 4冷蚂、 UNION
SELECT * FROM A
UNION
SELECT * FROM B;
- 5缭保、UNION ALL
SELECT * FROM A
UNION ALL
SELECT * FROM B;
- 6、 注意事項(xiàng)
被合并的兩個(gè)結(jié)果集:列數(shù),列類(lèi)型必須相同
