Linux云計算訓練營筆記day10（MySQL數據庫）
目錄
- Linux云計算訓練營筆記day10（MySQL數據庫）
- ifnull
- 別名
- 聚合函數
- group by
- HAVING
- 子查詢
- 關聯查詢
ifnull
在DQL語句中可以使用函數或表達式
函數
IFNULL(arg1,arg2)
如果arg1為NULL,函數返回arg2的值
如果arg1不為NULL，函數返回arg1的值
作用:將一個NULL值替換成一個非NULL值
NULL和任何數據運算，結果都是NULL
-- 查看老師的工資和年薪分別是多少
SELECT name,salary,salary*12
from teacher;
-- 查看每個老師的工資，獎金，工資+獎金分別是多少？
SELECT name,salary,IFNULL(comm,0),salary+IFNULL(comm,0)
from teacher;
-- 查看每個老師的獎金，以及年獎金
SELECT name,IFNULL(comm,0),IFNULL(comm,0)*12
from teacher;
別名
別名
字段 as 別名
字段 as “別名”
-- 查看哪些老師的年薪高于60000,并按照工資從高到低排序
SELECT name,salary*12,salary
from teacher
WHERE salary*12>60000
ORDER BY salary DESC;
SELECT name as 姓名,salary as 工資,salary*12 as 年薪
from teacher;-- 1)當別名中有空格,必須加引號， 2)當別名中有關鍵字,必須加引號
SELECT name as "姓 名",salary as "from"
from teacher;
-- 1.查詢所有10歲學生的生日,按生日對應的年紀從大到小排序
SELECT name,birth,age
from student
WHERE age=10
ORDER BY birth ASC;-- 2.查詢8歲同學中名字含有 苗 的學生信息
SELECT name,age
from student
WHERE age=8 and name LIKE "%苗%";-- 3.查詢負責課程編號1和2號且工資高于6000的老師信息
SELECT name,subject_id,salary
from teacher
WHERE subject_id in (1,2) and salary>6000;-- 4.查詢10歲以上的語文課代表和數學課代表
SELECT name,age,job
from student
WHERE age>10 and job in ("語文課代表","數學課代表");-- 5.查詢不教 課程編號1的 老師信息，按照工資降序排序
SELECT name,subject_id,salary
from teacher
WHERE subject_id <> 1
ORDER BY salary DESC;-- 6.查詢沒有獎金的老師信息 (0也屬于沒有獎金)
SELECT name,comm
from teacher
WHERE IFNULL(comm,0)=0;-- 7.查詢所有老師的獎金，并按照獎金降序排序
SELECT name,comm
FROM teacher
ORDER BY comm DESC;-- 8.查看工資高于8000的老師負責的課程編號都有哪些
SELECT DISTINCT subject_id
from teacher
WHERE salary>8000;-- 9.查看全校年齡最小學生的第6-10名 M: (頁碼-1)*x5 M:5 N:5
SELECT name,birth,age
from student
ORDER BY birth DESC,id
LIMIT 5,5;
聚合函數
聚合函數 將多條記錄按照指定的字段進行統計并得出一個結果
MIN: 統計指定字段的最小值
MAX: 統計指定字段的最大值
AVG: 統計指定字段的平均值
SUM: 統計指定字段的總和
COUNT: 不是對字段的統計，是對記錄的統計
注意事項:
聚合函數忽略NULL值
MIN MAX AVG SUM 是對值的統計，COUNT是對記錄的統計
-- 查看老師的平均工資是多少 (先統計老師的工資 ; 在添加聚合函數)
SELECT AVG(salary)
from teacher;-- 查看老師的最高工資，最低工資，平均工資和工資總和是多少 (先統計參與的數據 ; 在添加聚合函數)
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
from teacher;-- 查看負責課程編號1的老師平均工資是多少 (先統計1號老師的工資 ; 在添加聚合函數)
SELECT AVG(salary)
from teacher
WHERE subject_id=1;-- 查看一共有多少位老師
SELECT COUNT(name)
FROM teacher;
SELECT COUNT(id)
FROM teacher;-- 統計字段共有多少條數據 (用的最多)
SELECT COUNT(*)
FROM teacher;
SELECT * from teacher;
-- 1.查看所有老師的平均獎金和獎金總和是多少
SELECT AVG(IFNULL(comm,0)), SUM(comm)
from teacher;-- 2.查看負責課程編號2的老師共有多少人
SELECT COUNT(*)
from teacher
WHERE subject_id=2;-- 3.查看班級編號為 1 的學生有多少人
SELECT COUNT(*)
FROM student
WHERE class_id=1;-- 4.查看全校學生 生日 最大的是哪天
SELECT MIN(birth)
from student;-- 5.查看11歲的課代表總共有多少人
SELECT COUNT(*)
from student
WHERE age=11 and job LIKE "%課代表";-- 6.姓 張 的學生有多少人
SELECT COUNT(*)
from student
WHERE name LIKE "張%";-- 7.工資高于5000的老師中最低的工資是多少
SELECT MIN(salary)
from teacher
WHERE salary>5000;-- 8.4層有幾個班
SELECT COUNT(*)
from class
WHERE floor=4;-- 9.老師中 總監 的平均工資是多少
SELECT AVG(salary)
from teacher
WHERE title="總監";
group by
group by
對結果集按照指定字段相同的記錄進行分組,配合聚合函數做到組內統計
group by一定是配合聚合函數的，如果select子句沒有聚合函數，不會使用group by
在select語句中凡是不在聚合函數中的字段都應出現在group by子句中
group by可以按照多組字段分組，那些字段值相同的記錄被看做一組
把結果集按照聚合函數的統計結果排序
-- 查看每種職位 title 的老師平均工資是多少 (title字段值相同的記錄會被劃分為一組)
SELECT AVG(salary),title
from teacher
GROUP BY title;-- 查看每個班級各有多少人 (class_id字段值相同的學生是一個班)
SELECT COUNT(*),class_id
from student
GROUP BY class_id;-- 查看學生每種 職位各有多少人，以及最大生日和最小生日
SELECT COUNT(*) as 人數, MIN(birth) as 最大生日,MAX(birth) as 最小生日, job as 職位
from student
GROUP BY job;-- 查看同班級 同性別的學生有多少人
SELECT COUNT(*),class_id,gender
FROM student
GROUP BY class_id,gender;-- 查看每個班級 每種職位各有多少人
SELECT COUNT(*) as 人數,class_id,job
from student
GROUP BY class_id,job;-- 查看每個科目老師的平均工資排名
SELECT AVG(salary) as 平均工資,subject_id
from teacher
GROUP BY subject_id
ORDER BY AVG(salary) DESC;
HAVING
HAVING
在group by之后，用于添加條件過濾分組的
聚合函數不能出現在where子句中
HAVING和WHERE
1)過濾時機不同,WHERE先過濾,HAVING后過濾
2)WHERE用于確定結果集的記錄
3)HAVING用于確定保留哪些分組
4)WHERE不可以使用聚合函數作為過濾條件，HAVING可以
- 查詢每個科目老師的平均工資,但是只看平均工資高于6000的那些
SELECT AVG(salary),subject_id
from teacher
GROUP BY subject_id
HAVING AVG(salary) >6000;-- 查詢每個科目老師的平均工資,前提是該科目老師最高工資要超過9000
SELECT AVG(salary),MAX(salary),subject_id
from teacher
GROUP BY subject_id
HAVING MAX(salary)>9000;
-- 1.查看科目老師的工資總和是多少，前提是該科老師的平均獎金要高于4000
SELECT SUM(salary),subject_id
from teacher
GROUP BY subject_id
HAVING AVG(comm)>4000;-- 2.查看各科目 男老師 的平均工資是多少，前提是該科目老師最低工資高于4000
SELECT AVG(salary),subject_id
from teacher
WHERE gender="男"
GROUP BY subject_id
HAVING MIN(salary)>4000;-- 3.查看班級編號小于6的每個班 各有多少人
SELECT COUNT(*), class_id
from student
WHERE class_id<6
GROUP BY class_id;-- 4.查看3層一共多少個班
SELECT COUNT(*)
from class
WHERE floor=3;-- 5.查看工資低于8000的老師的平均工資是多少
SELECT AVG(salary)
from teacher
WHERE salary<8000;-- 6.查看班級人數超過60人的班級中年級最大的同學生日是多少
SELECT MIN(birth), class_id
from student
GROUP BY class_id
HAVING COUNT(*)>60;-- 7.查看教課程編號1 的老師平均年齡是多少
SELECT AVG(age)
from teacher
WHERE subject_id=1;-- 8.查看同一科目平均年齡超過35歲的 老師中最小年齡是多少
SELECT MIN(age),subject_id
from teacher
GROUP BY subject_id
HAVING AVG(age)>35;-- 9.查看同職稱人數超過 4人的老師 的平均工資是多少
SELECT AVG(salary),title
from teacher
GROUP BY title
HAVING COUNT(*)>4;
子查詢
子查詢
嵌套在其他SQL語句中的一條DQL語句
在DQL中使用
在select子句中使用,將該查詢結果當做一個字段列在外層查詢的結果集中
在DML中使用
在增刪改中，基于該查詢結果集對表中數據進行操作
子查詢的分類
1)單行單列子查詢
2)單行多列子查詢
可以同時查詢出多個值
如果進行等值判斷 in,not in
如果進行關系運算
>ANY(列表) 大于列表中最小的值
<ANY(列表) 小于列表中最大的值
>ALL(列表) 大于列表中所有值 >最大的
<ALL(列表) 小于列表中所有值 <最小的
3)多列子查詢
-- 查看比范傳奇工資高的老師都有誰
-- 1)范傳奇的工資是多少 - 3000
SELECT salary from teacher WHERE name="范傳奇";
-- 2)查看誰的工資高于3000
SELECT name,salary from teacher where salary>3000;
SELECT name,salary
from teacher
WHERE salary>(SELECT salary from teacher WHERE name="范傳奇");-- 查看哪些老師的工資是高于平均工資的
-- 平均工資是多少 - 6355.050SELECT AVG(salary) from teacher;
-- 查看高于平均工資的
SELECT name,salary from teacher WHERE salary>6355.050;
SELECT name,salary
from teacher
WHERE salary>(SELECT AVG(salary) from teacher);-- 查看和 李費水 在同一個班級的學生都有誰
-- 李費水的班級編號
SELECT class_id from student WHERE name="李費水";
SELECT name,class_id
from student
WHERE class_id=(SELECT class_id from student WHERE name="李費水");-- 查看 3年級2班 在同一層的班級都有哪些，列出班級編號,名字,樓層
SELECT floor from class WHERE name="3年級2班";
SELECT id,name,floor
from class
WHERE floor=(SELECT floor from class WHERE name="3年級2班");-- 查看3年級2班的學生都有誰
SELECT id from class WHERE name="3年級2班";
SELECT name,class_id
from student
WHERE class_id=(SELECT id from class WHERE name="3年級2班");-- 查看教語文的老師都有誰
select id from subject WHERE name="語文";
SELECT name,subject_id
from teacher
WHERE subject_id=(select id from subject WHERE name="語文");-- 查看工資最高的老師的工資和獎金是多少
select MAX(salary) from teacher;
SELECT name,salary,comm
from teacher
WHERE salary=(select MAX(salary) from teacher);-- 查看和 祝雷 李費水 在同一個班的學生都有誰
SELECT class_id from student WHERE name in ("祝雷","李費水");
SELECT name,class_id
from student
WHERE class_id in (SELECT class_id from student WHERE name in ("祝雷","李費水"));-- 查看不與 范傳奇 和 王克晶 教同一課的老師都有誰
SELECT name,subject_id
from teacher
WHERE subject_id not in (SELECT subject_id from teacher WHERE name in ("范傳奇","王克晶"));-- 查看比教 科目2 和 科目4 老師工資都高的老師都有誰
SELECT salary from teacher WHERE subject_id in (2,4);
SELECT name,salary
from teacher
WHERE salary>ALL(SELECT salary from teacher WHERE subject_id in (2,4));-- 給與 范傳奇 負責同一科目的所有老師工資漲1000
UPDATE teacher set salary=salary+1000
WHERE subject_id=(SELECT subject_id from teacher WHERE name="范傳奇");-- 將王克晶 的工資改成與 范傳奇一樣
UPDATE teacher
SET salary=(SELECT salary from teacher WHERE name="范傳奇")
WHERE name="王克晶";
關聯查詢
關聯查詢
聯合多張表查詢數據,結果來自于多張表
關系的分類
1)一對一關系: 一個表的記錄對應另一個表的記錄
用戶表 用戶詳情表
create table user(
id int primary key auto_increment,
name varchar(10)
);
create table user_detail(
user_id int primary key auto_increment,
address varchar(10),
foreign key(user_id) references user(id);
)
2) 一對多關系
主表的一條記錄 對應另外一張表的多條數據
部門表 員工表
create table dept(
id int primary key auto_increment,
name varchar(10)
);
create table emp(
id int primary key auto_increment,
name varchar(10),
deptId int,
foreign key(deptId) references dept(id);
)
3)多對多關系
兩個表的記錄可以相互對應多條數據
學生表 課程表 (通過中間表實現)
create table student(
id int primary key auto_increment,
name varchar(10)
);
create table courses(
id int primary key auto_increment,
name varchar(10)
);
create table student_courses(
student_id int,
couses_id int,
primary key(student_id,couses_id),
foreign key(student_id) references student(id),
foreign key(couses_id) references courses(id);
);
連接條件 用于關聯兩張表中數據對應關系
在關聯查詢中通常兩張表要指定連接條件，否則會產生笛卡爾積.
笛卡爾積的查詢結果是兩張表的 乘積
-- 查看每個老師的名字和其所教科目的名字
SELECT teacher.name,teacher.salary,teacher.age,subject.name
from teacher,subject
WHERE teacher.subject_id=subject.id;SELECT t.name,t.salary,t.age,s.name
from teacher t,subject s
WHERE t.subject_id=s.id;SELECT * from teacher;
SELECT * from subject;
show tables;
desc teacher;
desc subject;
SELECT t.name,t.salary,t.age,s.name
from teacher t,subject s;-- 查看班級的名稱和對應的班主任老師是誰
SELECT c.name,c.teacher_id,t.name,t.id
from class c,teacher t
WHERE c.teacher_id=t.id;-- 查看每個學生的名字 年齡 以及其所在的班級名稱和所在樓層
SELECT s.name,s.age,c.name,c.floor
from student s,class c
WHERE s.class_id=c.id;-- 查看每個學生來自哪座城市的名字，列出學生的名字，性別，年齡和所在城市的名字
select s.name,s.gender,s.age,l.name
from student s, location l
WHERE s.location_id=l.id;-- 王克晶 是哪個班的班主任?列出 班級名稱 樓層 老師名稱 工資
SELECT c.name,c.floor,t.name,t.salary
from class c,teacher t
WHERE t.id=c.teacher_id and t.name="王克晶";-- 查看三年級的班主任都是誰，要列出 班級名稱，所在樓層，班主任名字 工資
SELECT c.name,c.floor,t.name,t.salary
from class c,teacher t
WHERE c.teacher_id=t.id and c.name LIKE "3年級%";
