cgb2109-day04-有解無憂

文章目錄

- 一,SQL練習
- - --1,測驗
- 二,欄位約束
- - --1,默認約束
  - --2,檢查約束
  - ! --3,外鍵約束
- 三,多表聯查
- - --1,準備表和資料
  - --2,方式1:笛卡爾積
  - --3,方式2:連接查詢
  - --4,方式3:子查詢
- SQL的作業:
- - 用兩種多表聯查的方式完成以下練習:

一,SQL練習

–1,測驗

#練習1:依次按照多個條件排序,asc升序,desc降序
SELECT * FROM emp ORDER BY sal DESC,ename ASC
SELECT * FROM emp ORDER BY deptno DESC,ename ASC
#練習2:統計每個崗位的員工人數
SELECT job,COUNT(1) FROM emp
#什么時候必須需要分組?查詢時出現了混合列
#按照啥分組?按照非聚合列分組
GROUP BY job 
#練習3:統計每個部門包含re的部門編號
SELECT deptno FROM dept 
WHERE dname LIKE 're%' #以re開始的
GROUP BY loc #合理的分組
#練習4:統計每年入職的員工的最高薪資,只要>5000的
SELECT MAX(sal) a,YEAR(hiredate) b FROM emp
GROUP BY b #分組,除了where里不能用別名其他位置都行
#having里可用聚合函式where不能
HAVING a>5000 #分組后過濾
#練習5:查詢所有員工的最高薪-用分頁
SELECT MAX(sal) FROM emp
SELECT sal FROM emp ORDER BY sal DESC LIMIT 1
#創建user表(id,name,pwd),并插入資料
CREATE TABLE USER(
 id INT PRIMARY KEY AUTO_INCREMENT,#主鍵自增
 NAME VARCHAR(20),
 pwd VARCHAR(10)
)
INSERT INTO USER VALUES(NULL,'jack',"123")
INSERT INTO USER VALUES(NULL,'rose',"456")
#對上面SQL中使用的事務進行優化,因為上面的SQL需要兩個事務的開啟和關閉
#下面這種SQL也叫作批量插入的功能,高效,多次插入使用同一個事務
INSERT INTO USER VALUES
	(NULL,'tony',"123"),(NULL,'jerry',"456")

二,欄位約束

–1,默認約束

哪個欄位添加了默認約束,從此欄位值的就有了默認值

#默認約束:給欄位設定默認值而不是默認的null,使用default
CREATE TABLE a(
 id INT PRIMARY KEY AUTO_INCREMENT,
 sex VARCHAR(10) DEFAULT '男'#默認約束
)

–2,檢查約束

哪個欄位添加了檢查約束,從此,欄位值必須符合檢查的條件才可以

#檢查約束:使用check,了解
CREATE TABLE b(
 id INT PRIMARY KEY AUTO_INCREMENT,
 age INT,
 CHECK(age<200 AND age>0)#檢查約束
 #資料要滿足檢查條件才可以
)

! --3,外鍵約束

使用明確的一段代碼表示,兩個表之間的關系

#外鍵約束:先創建外鍵+再使用外鍵
CREATE TABLE tb_user(
 id INT PRIMARY KEY AUTO_INCREMENT,
 NAME VARCHAR(20),
 age INT,
 sex CHAR(10) DEFAULT '男'#默認約束
)
CREATE TABLE tb_user_address(
 user_id INT PRIMARY KEY,
 address VARCHAR(200),
 #foreign key(當前表的主鍵名) references 對方表(對方的主鍵)
 FOREIGN KEY(user_id) REFERENCES tb_user(id)#1.創建外鍵
)
#2.使用外鍵
#約束的情況1:子表的主鍵值  必須 取自 主表的主鍵值
#約束的情況2:主表的記錄不能隨便刪,要保證沒有被子表使用才可以刪

三,多表聯查

–1,準備表和資料

#多表聯查:
#1.課程表
CREATE TABLE courses( 
cno VARCHAR(5) NOT NULL, 
cname VARCHAR(10) NOT NULL,
tno VARCHAR(3) NOT NULL, 
PRIMARY KEY (cno) #單獨設定主鍵
);
#2.得分表
CREATE TABLE scores(
sno VARCHAR(3) NOT NULL,
cno VARCHAR(5) NOT NULL, 
degree NUMERIC(10,1) NOT NULL,
PRIMARY KEY (sno, cno) #聯合主鍵,了解
);
#3.學生表
CREATE TABLE students( 
sno VARCHAR(3) NOT NULL,
sname VARCHAR(4) NOT NULL,
ssex VARCHAR(2) NOT NULL,
sbirthday DATETIME, 
class VARCHAR(5), 
PRIMARY KEY (sno)#主鍵
);
#4.老師表
CREATE TABLE teachers( 
tno VARCHAR(3) NOT NULL,
tname VARCHAR(4),
tsex VARCHAR(2),
tbirthday DATETIME,
prof VARCHAR(6),
depart VARCHAR(10),
PRIMARY KEY (tno)
);
#5.插入資料
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (108 ,'曾華' ,'男' ,'1977-09-01',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (105 ,'匡明' ,'男' ,'1975-10-02',95031);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (107 ,'王麗' ,'女' ,'1976-01-23',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (101 ,'李軍' ,'男' ,'1976-02-20',95033);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (109 ,'王芳' ,'女' ,'1975-02-10',95031);
 
INSERT INTO STUDENTS (SNO,SNAME,SSEX,SBIRTHDAY,CLASS) VALUES (103 ,'陸君' ,'男' ,'1974-06-03',95031);
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (804,'易天','男','1958-12-02','副教授','計算機系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (856,'王旭','男','1969-03-12','講師','電子工程系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (825,'李萍','女','1972-05-05','助教','計算機系');
 
INSERT INTO TEACHERS(TNO,TNAME,TSEX,TBIRTHDAY,PROF,DEPART) VALUES (831,'陳冰','女','1977-08-14','助教','電子工程系');
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-105' ,'計算機導論',825);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('3-245' ,'作業系統' ,804);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-166' ,'模擬電路' ,856);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('6-106' ,'概率論' ,831);
 
INSERT INTO COURSES(CNO,CNAME,TNO)VALUES ('9-888' ,'高等數學' ,831);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-245',86);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-245',75);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-245',68);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (103,'3-105',92);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (105,'3-105',88);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (109,'3-105',76);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'3-105',64);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'3-105',91);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'3-105',78);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (101,'6-166',85);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (107,'6-106',79);
 
INSERT INTO SCORES(SNO,CNO,DEGREE)VALUES (108,'6-166',81);

–2,方式1:笛卡爾積

#多表聯查,三種方式:
#方式1:笛卡爾積,用逗號連接多張表
SELECT * FROM dept,emp #產生了大量的結果集
SELECT * FROM dept,emp #逗號隔開表
#描述兩張表的關系   表名.欄位名
WHERE dept.deptno = emp.deptno
#練習1:查詢部門名稱和員工表的所有資料
SELECT dept.dname,emp.* FROM dept,emp #逗號隔開表名
WHERE dept.deptno=emp.deptno #表關系
#練習2:查詢部門的所有和員工的名字,只要部門名稱叫accounting的
SELECT dept.*,emp.ename FROM dept,emp #逗號隔開表名 
WHERE dept.deptno=emp.deptno #表關系
AND dept.dname='accounting' #業務條件
#練習3:查詢所有部門和員工的資料,條件是部門編號>1的	
SELECT * FROM dept,emp 
WHERE dept.deptno=emp.deptno#表關系
AND emp.deptno>1 #業務條件
#練習4:查詢易天老師能講的課程名稱
SELECT courses.cname FROM teachers,courses 
WHERE courses.tno=teachers.tno #表關系
AND teachers.tname='易天' #業務條件
#練習5:查詢計算機導論課程的總分
SELECT SUM(degree) FROM courses,scores 
WHERE courses.cno=scores.cno #表關系
AND courses.cname='計算機導論' #業務條件

–3,方式2:連接查詢

#方式2:連接查詢,用join連接多張表
SELECT * FROM dept JOIN emp #用join連接多張表
SELECT * FROM dept JOIN emp
ON dept.deptno=emp.deptno #表關系
#練習1:查詢部門名稱和員工表的所有資料
SELECT dept.dname,emp.* FROM dept JOIN emp 
ON dept.deptno=emp.deptno#表關系
#練習2:查詢部門的所有和員工的名字,只要部門名稱叫accounting的
SELECT dept.*,emp.ename FROM emp JOIN dept
ON dept.deptno=emp.deptno #表關系
WHERE dept.dname='accounting' #業務條件
#練習3:查詢所有部門和員工的資料,條件是部門編號>1的
SELECT * FROM dept JOIN emp
ON dept.deptno=emp.deptno #表關系
WHERE emp.deptno>1 #業務條件	
#練習4:查詢易天老師能講的課程名稱
SELECT courses.cname FROM courses JOIN teachers
ON courses.tno=teachers.tno #表關系
WHERE teachers.tname='易天' #業務條件
#練習5:查詢計算機導論課程的總分
SELECT SUM(scores.degree) FROM courses JOIN scores
ON courses.cno=scores.cno #表關系
WHERE courses.cname='計算機導論' #業務條件
#測驗 三種連接的區別????
#有兩種連接查詢:內連接和外連接(左外連接,右外連接)
#inner join:取兩張表的交集
#left join:左邊表的所有和右邊滿足條件的
#right join:右邊表的所有和左邊滿足條件的
#內連接:取交集,取左右表都滿足條件的
SELECT * FROM dept INNER JOIN emp 
ON dept.deptno=emp.deptno
#左連接:取左表的所有和右表滿足條件的不滿足的都是null
SELECT * FROM dept LEFT JOIN emp
ON dept.deptno=emp.deptno
#右連接:取右表的所有和左表滿足條件的不滿足的都是null
SELECT * FROM emp RIGHT JOIN dept
ON dept.deptno=emp.deptno
#連接查詢的效率:小表驅動大表,
#把結構簡單或者資料量少的表放在前面作為左表
#因為左表會查所有資料,右表只查滿足了條件的那些資料

–4,方式3:子查詢

在這里插入代碼片

SQL的作業:

用兩種多表聯查的方式完成以下練習:

練習1:查詢research部門的所有員工姓名和工資
練習2:查詢jack所在的部門資訊
練習3:查詢總監的部門資訊
練習4:查詢李軍的平均分
練習5:查詢陳冰能講的課程名

轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/347184.html

標籤：其他

上一篇：docker虛擬化詳細步驟

下一篇：Facebook宣布關閉面部識別系統，洗掉超過10億用戶的資料