1、count 使用
- 1.1 查詢employee表中記錄數(shù):
SELECT COUNT(*) FROM employee;
- 1.2 查詢員工表中有績效的人數(shù):
SELECT COUNT(performance) FROM employee;
- 1.3 查詢員工表中月薪大于2500的人數(shù):
SELECT COUNT(*) FROM employee WHERE salary > 2500;
- 1.4 統(tǒng)計月薪與績效之和大于5000元的人數(shù):
SELECT COUNT(*) FROM employee WHERE IFNULL(salary,0)+IFNULL(performance,0)>5000;
- 1.5 查詢有績效的人數(shù)断部，和有管理費的人數(shù):
SELECT COUNT(performance),COUNT(manage) FROM employee;
2娇跟、SUM 和MAX和MIN 的使用
- 1眯牧、查詢所有雇員月薪和
SELECT SUM(salary) FROM employee;
- 2逸雹、查詢所有雇員月薪和,以及所有雇員績效和
SELECT SUM(salary),SUM(performance) FROM employee;
- 3、 查詢所有雇員月薪+績效和
SELECT SUM(salary+IFNULL(performance,0)) FROM employee;
- 4筷转、統(tǒng)計所有員工平均工資
SELECT AVG(salary) FROM employee;
- 5筐喳、 查詢最高工資和最低工資
SELECT MAX(salary),MIN(salary) FROM employee;
3催式、分組查詢
- 1、 將查詢結(jié)果按照1個或者多個字段進行分組,字段值相同的為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哺窄、對某一個部門進行分組,查看每個部門薪資信息都羅列出來
SELECT department,GROUP_CONCAT(salary) FROM employee GROUP BY department;
5、GROUP BY + 聚合函數(shù)
- 1账锹、查看部門平均薪資
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;
- 2萌业、查看每個部門的最高薪資
SELECT department,GROUP_CONCAT(salary),MAX(salary) FROM employee GROUP BY department;
- 3、查看每個部門的最低薪資
SELECT department,GROUP_CONCAT(salary),MIN(salary) FROM employee GROUP BY department;
- 4奸柬、查詢每個部門的部門名稱和每個部門的工資和
SELECT department,GROUP_CONCAT(salary),SUM(salary) FROM employee GROUP BY department;
- 5生年、查詢每個部門的部門名稱和每個部門的人數(shù)
SELECT department,GROUP_CONCAT(name),COUNT(*) FROM employee GROUP BY department;
- 6、查詢每個部門的部門名稱以及每個部門工資大于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抱婉、把所有部門進行分組
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是在分組后對數(shù)據(jù)進行過濾;
WHERE是在分組前對數(shù)據(jù)進行過濾;
HAVING后面可以使用分組函數(shù)(統(tǒng)計函數(shù));
WHERE后面不可以使用分組函數(shù);
WHERE是對分組前記錄的條件,如果某行記錄沒有滿足where子語句的條件,那么這行記錄不參與分組;而HAVING是對分組后的數(shù)據(jù)約束;
- 1蹲蒲、查詢工資大于2000的,工資總和大于6000的部門名稱以及工資和;
- 各部門工資
- 各部門工資總和
- 各部門工資總和大于6000
- 各部門工資總和大于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届搁、書寫順序缘薛，執(zhí)行順序
書寫順序SELECT --> FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> LIMIT
執(zhí)行順序FROM => WHERE => GROUP BY => HAVING => SELECT => ORDER BY => LIMIT
分頁
- 1、 LIMIT
SELECT * FROM employee
LIMIT (curPage-1)*pageSize,pageSize;
數(shù)據(jù)完整性
- 1卡睦、完整性分類：
實體完整性
域完整性
引用完整性
- 2宴胧、什么是實體完整性
表中的一行(一條記錄)代表一個實體(entity)
- 3、實體完整性的作用
標(biāo)識每一行數(shù)據(jù)不重復(fù).行級約束
- 4表锻、約束類型
主鍵約束(primary key)
唯一約束(unique)
自動增長(auto_increment)
1恕齐、 主鍵約束
- 1、特點:
每個表中要有一個主鍵
數(shù)據(jù)唯一,且不能為null
- 2瞬逊、添加方式:
CREATE TABLE 表名(字段名1 數(shù)據(jù)類型 primary key,字段名2 數(shù)據(jù)類型);
- 3显歧、舉例:創(chuàng)建person表
CREATE TABLE person(ID BIGINT primary key,NAME VARCHAR(50))
CREATE TABLE 表名(字段1 數(shù)據(jù)類型,字段名2 數(shù)據(jù)類型,primary key(要設(shè)置主鍵的字段));
- 4仪或、舉例創(chuàng)建
CREATE TABLE person(
ID BIGINT,
name VARCHAR(50),
age INT,
primary key(ID)
);
2、聯(lián)合主鍵：兩個字段數(shù)據(jù)同時相同時,才違反聯(lián)合主鍵約束
- 1士骤、創(chuàng)建聯(lián)合主鍵
CREATE TABLE 表名(字段1 數(shù)據(jù)類型,字段2 數(shù)據(jù)類型,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ī)則就會報錯
ALTER TABLE student ADD CONSTRAINT primary key(sid);
3到旦、唯一約束
- 1、特點
指定列的數(shù)據(jù)不能重復(fù)
可以為空值
- 2巨缘、格式:
CREATE TABLE students(
id INT primary key,
name VARCHAR(50) UNIQUE
)
4添忘、自動增長列
- 1、特點
指定列的數(shù)據(jù)自動增長
即使數(shù)據(jù)刪除,還是從刪除的序號繼續(xù)往下
- 2带猴、格式
CREATE TABLE 表名(
字段名1 數(shù)據(jù)類型 primary key AUTO_INCREMENT,
字段2 數(shù)據(jù)類型 UNIQUE);
- 3昔汉、舉例
CREATE TABLE 表名(
字段名1 數(shù)據(jù)類型 primary key AUTO_INCREMENT,
字段2 數(shù)據(jù)類型 UNIQUE);
5、域完整性
- 1拴清、使用
限制此單元格的數(shù)據(jù)正確,不對照此列的其他單元格比較
域代表當(dāng)前的單元格
- 2靶病、數(shù)據(jù)類型
數(shù)值類型
日期類型
字符串類型
- 3、非空約束(NOT NULL)
CREATE TABLE stu(
id int primary key auto_increment,
name varchar(20) unique not null,
gender char(1) default '男'
)
- 4默認值約束(DEFAULT)
CREATE TABLE 表名(
字段名1 數(shù)據(jù)類型 primary key AUTO_INCREMENT,
字段2 數(shù)據(jù)類型 UNIQUE NOT NULL,
字段3 數(shù)據(jù)類型 DEFAULT '默認值');
- 5口予、舉例:
CREATE TABLE students(
id INT primary key AUTO_INCREMENT,
name VARCHAR(20) UNIQUE NOT NULL,
gender CHAR(1) DEFAULT '男'
);
6娄周、參照完整性
- 什么是參照完整性
(1)是指表與表之間的一種對應(yīng)關(guān)系
(2)通常情況下可以通過設(shè)置兩表之間的主鍵、外鍵關(guān)系,或者編寫兩表的觸發(fā)器來實現(xiàn)
(3)有對應(yīng)參照完整性的兩張表格,再對他們進行數(shù)據(jù)插入,更新,刪除的過程中,系統(tǒng)都會將被修改表格與另外一張對應(yīng)表格進行對照,從而阻止一些不正確的數(shù)據(jù)的操作
2沪停、數(shù)據(jù)庫的主鍵和外鍵類型一定要一致
3煤辨、參照完整性添加
（1）通過一張表里面的某個字段,關(guān)聯(lián)另外一張表
（2）對已有表添加外鍵關(guān)系
ALTER TABLE score ADD CONSTRAINT sc_st_fk FOREIGN KEY(sid) REFERENCES student(id);
7、多表查詢
- 1. 一對一
一夫一妻(開發(fā)中使用比較少)
- 2木张、一對多關(guān)系
一個人可以擁有多輛汽車,要求查詢某個人擁有的所有車輛
創(chuàng)建Person表
- 人
CREATE TABLE person(
p_id INT PRIMARY KEY AUTO_INCREMENT,
p_name VARCHAR(50),
p_age INT,
p_sex CHAR(1)
);
- 車
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众辨、多對多關(guān)系
例如:學(xué)生選課,一個學(xué)生可以選修多門課程,每門課程可以供多個學(xué)生選擇
再例如: 老師和學(xué)生的多對多關(guān)系(一個老師有多個學(xué)生,一個學(xué)生有多個老師)
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
合并時去除重復(fù)記錄2、 UNION ALL
合并時不去除重復(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屈扎、 注意事項
被合并的兩個結(jié)果集:列數(shù),列類型必須相同
