今日任務
完成對MYSQL數(shù)據(jù)庫的多表查詢及建表的操作
教學目標
掌握MYSQL中多表的創(chuàng)建及多表的查詢
掌握MYSQL中的表關系分析并能正確建表
昨天內(nèi)容回顧:
? 數(shù)據(jù)庫的創(chuàng)建 : create database 數(shù)據(jù)庫的名 character set 字符集 collate 校對規(guī)則
? 數(shù)據(jù)庫的刪除: drop database 數(shù)據(jù)庫名
? 修改: alter database 數(shù)據(jù)庫 character set 字符集(utf8)
? 查詢: show databases;
? show create database 數(shù)據(jù)庫的名字
? select database();
? 切換數(shù)據(jù)庫 :
? use 數(shù)據(jù)庫的名字
?
? 表結(jié)構(gòu)的操作:
? 創(chuàng)建: create table 表名(
? 列名 列的類型 列的約束,
? 列名 列的類型 列的約
? )
? 列的類型: char / varchar
? 列的約束:
? primary key 主鍵約束
? unique : 唯一約束
? not null 非空約束
? 自動增長 : auto_increment
? 刪除 : drop table 表名
? 修改: alter table 表名 (add, modify, change , drop)
? rename table 舊表名 to 新表名
? alter table 表名 character set 字符集
? 查詢表結(jié)構(gòu):
? show tables; 查詢出所有的表
? show create table 表名: 表的創(chuàng)建語句, 表的定義
? desc 表名: 表的結(jié)構(gòu)
? 表中數(shù)據(jù)的操作
? 插入： insert into 表名(列名,列名) values(值1,值2);
? 刪除: delete from 表名 [where 條件]
? 修改: update 表名 set 列名='值' ,列名='值' [where 條件];
? 查詢: select [distinct] * [列名1,列名2] from 表名 [where 條件]
? as關鍵字: 別名
? where條件后面:
?
? 關系運算符: > >= < <= != <>
? --判斷某一列是否為空: is null is not null
? in 在某范圍內(nèi)
? between...and
? 邏輯運算符: and or not
? 模糊查詢: like
? _ : 代表單個字符
? %: 代表的是多個字符
? 分組: group by
? 分組之后條件過濾: having
? 聚合函數(shù): sum() ,avg() , count() ,max(), min()
? 排序: order by (asc 升序, desc 降序)
?
?
?
?
?
SQL 會創(chuàng)建多表及多表的關系
需求:
分類表和商品表之間是不是有關系? 如果有關系,在數(shù)據(jù)庫中如何表示這種關系
create table category(
cid int primary key auto_increment,
cname varchar(10),
cdesc varchar(31)
);
insert into category values(null,'手機數(shù)碼','電子產(chǎn)品,黑馬生產(chǎn)');
insert into category values(null,'鞋靴箱包','江南皮鞋廠傾情打造');
insert into category values(null,'香煙酒水','黃鶴樓,茅臺,二鍋頭');
insert into category values(null,'酸奶餅干','娃哈哈,蒙牛酸酸乳');
insert into category values(null,'饞嘴零食','瓜子花生,八寶粥,辣條');
select * from category;
select cname,cdesc from category;
--所有商品
1.商品ID
2.商品名稱
3.商品的價格
4.生產(chǎn)日期
5.商品分類ID
商品和商品分類 : 所屬關系
create table product(
pid int primary key auto_increment,
pname varchar(10),
price double,
pdate timestamp,
cno int
);
insert into product values(null,'小米mix4',998,null,1);
insert into product values(null,'錘子',2888,null,1);
insert into product values(null,'阿迪王',99,null,2);
insert into product values(null,'老村長',88,null,3);
insert into product values(null,'勁酒',35,null,3);
insert into product values(null,'小熊餅干',1,null,4);
insert into product values(null,'衛(wèi)龍辣條',1,null,5);
insert into product values(null,'旺旺大餅',1,null,5);
//插入數(shù)據(jù)會失敗
insert into product values(null,'充氣的',1,null,12);
技術分析:
-
多表之間的關系如何來維護
外鍵約束: foreign key
-
給product中的這個cno 添加一個外鍵約束
alter table product add foreign key(cno) references category(cid);
自己挖坑
-
從分類表中,刪除分類為5信息,
- delete from category where cid =5; //刪除失敗
- 首先得去product表, 刪除所有分類ID5 商品
-
-
建數(shù)據(jù)庫原則:
- 通常情況下,一個項目/應用建一個數(shù)據(jù)庫
-
多表之間的建表原則
-
一對多 : 商品和分類
-
建表原則: 在多的一方添加一個外鍵,指向一的一方的主鍵
?
-
-
多對多: 老師和學生, 學生和課程
建表原則: 建立一張中間表,將多對多的關系,拆分成一對多的關系,中間表至少要有兩個外鍵,分別指向原來的那兩張表
?
-
一對一: 班級和班長, 公民和身份證, 國家和國旗
-
建表原則:
- 將一對一的情況,當作是一對多情況處理,在任意一張表添加一個外鍵,并且這個外鍵要唯一,指向另外一張表
- 直接將兩張表合并成一張表
- 將兩張表的主鍵建立起連接,讓兩張表里面主鍵相等
-
實際用途: 用的不是很多. (拆表操作 )
- 相親網(wǎng)站:
- 個人信息 : 姓名,性別,年齡,身高,體重,三圍,興趣愛好,(年收入, 特長,學歷, 職業(yè), 擇偶目標,要求)
- 拆表操作 : 將個人的常用信息和不常用信息,減少表的臃腫,
?
- 相親網(wǎng)站:
-
-
?
-
網(wǎng)上商城表實例的分析: 用戶購物流程
?
-
用戶表 (用戶的ID,用戶名,密碼,手機)
create table user( uid int primary key auto_increment, username varchar(31), password varchar(31), phone varchar(11) ); insert into user values(1,'zhangsan','123','13811118888'); -
訂單表 (訂單編號,總價,訂單時間 ,地址,外鍵用戶的ID)
create table orders( oid int primary key auto_increment, sum int not null, otime timestamp, address varchar(100), uno int, foreign key(uno) references user(uid) ); insert into orders values(1,200,null,'黑馬前臺旁邊小黑屋',1); insert into orders values(2,250,null,'黑馬后臺旁邊1702',1);
-
商品表 (商品ID, 商品名稱,商品價格,外鍵cno)
create table product( pid int primary key auto_increment, pname varchar(10), price double, cno int, foreign key(cno) references category(cid) ); insert into product values(null,'小米mix4',998,1); insert into product values(null,'錘子',2888,1); insert into product values(null,'阿迪王',99,2); insert into product values(null,'老村長',88,3); insert into product values(null,'勁酒',35,3); insert into product values(null,'小熊餅干',1,4); insert into product values(null,'衛(wèi)龍辣條',1,5); insert into product values(null,'旺旺大餅',1,5);?
-
訂單項: 中間表(訂單ID,商品ID,商品數(shù)量,訂單項總價)
create table orderitem( ono int, pno int, foreign key(ono) references orders(oid), foreign key(pno) references product(pid), ocount int, subsum double ); --給1號訂單添加商品 200塊錢的商品 insert into orderitem values(1,7,100,100); insert into orderitem values(1,8,101,100); --給2號訂單添加商品 250塊錢的商品 () insert into orderitem values(2,5,1,35); insert into orderitem values(2,3,3,99);?
?
-
商品分類表(分類ID,分類名稱,分類描述)
create table category( cid int primary key auto_increment, cname varchar(15), cdesc varchar(100) ); insert into category values(null,'手機數(shù)碼','電子產(chǎn)品,黑馬生產(chǎn)'); insert into category values(null,'鞋靴箱包','江南皮鞋廠傾情打造'); insert into category values(null,'香煙酒水','黃鶴樓,茅臺,二鍋頭'); insert into category values(null,'酸奶餅干','娃哈哈,蒙牛酸酸乳'); insert into category values(null,'饞嘴零食','瓜子花生,八寶粥,辣條');
- 多表之間的關系如何維護: 外鍵約束 : foreign key
- 添加一個外鍵: alter table product add foreign key(cno) references category(cid);
- ? foreign key(cno) references category(cid)
- 刪除的時候, 先刪除外鍵關聯(lián)的所有數(shù)據(jù),再才能刪除分類的數(shù)據(jù)
- 建表原則:
- 一對多:
- 建表原則: 在多的一方增加一個外鍵,指向一的一方
- 多對多:
- 建表原則: 將多對多轉(zhuǎn)成一對多的關系,創(chuàng)建一張中間表
- 一對一: 不常用, 拆表操作
- 建表原則: 將兩張表合并成一張表
- 將兩張表的主鍵建立起關系
- 將一對一的關系當作一對多的關系去處理
- 建表原則: 將兩張表合并成一張表
- 一對多:
主鍵約束: 默認就是不能為空, 唯一
- 外鍵都是指向另外一張表的主鍵
- 主鍵一張表只能有一個
唯一約束: 列面的內(nèi)容, 必須是唯一, 不能出現(xiàn)重復情況, 為空
- 唯一約束不可以作為其它表的外鍵
- 可以有多個唯一約束
一對多 : 建表原則: 在多的一方添加一個外鍵,指向一的一方
多對多: 建表原則:
? 拆成一對多
? 創(chuàng)建一張中間表, 至少要有兩個外鍵, 指向原來的表
一對一: 建表原則: 合并一張表, 將主鍵建立關系 , 將它當作一對多的情況來處理
-
數(shù)據(jù)庫客戶端軟件
?
使用商城表完成對商品信息的多表查詢
需求分析:
在我們的商城案例中,我的訂單中包含很多信息.打開我的訂單需要去查詢表
技術分析:
多表查詢
- 交叉連接查詢 笛卡爾積
- 內(nèi)連接查詢
- 左外連接
- 右外連接
分頁查詢
每頁數(shù)據(jù)數(shù)據(jù)3
起始索引從0
第1頁: 0
-
第2頁: 3
起始索引: index 代表顯示第幾頁 頁數(shù)從1開始
每頁顯示3條數(shù)據(jù)
startIndex = (index-1)*3
?
第一個參數(shù)是索引
第二個參數(shù)顯示的個數(shù)
select * from product limit 0,3;
select * from product limit 3,3;
子查詢(了解的內(nèi)容,非常重要)
查詢出(商品名稱,商品分類名稱)信息
查詢分類名稱為手機數(shù)碼的所有商品
select * from product where cname ='手機數(shù)碼';
練習題
- 按照商品分類的名稱統(tǒng)計商品的個數(shù):
- 查詢1號訂單的訂單項信息和商品信息
多表查詢練習數(shù)據(jù)
- 員工信息表
--員工信息表
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT
) ;
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
INSERT INTO emp values(7981,'MILLER','CLERK',7788,'1992-01-23',2600,500,20);
- 部門信息表
CREATE TABLE dept(
deptno INT,
dname varchar(14),
loc varchar(13)
);
INSERT INTO dept values(10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO dept values(20, 'RESEARCH', 'DALLAS');
INSERT INTO dept values(30, 'SALES', 'CHICAGO');
INSERT INTO dept values(40, 'OPERATIONS', 'BOSTON');
- 基本查詢
--所有員工的信息
--薪資大于等于1000并且小于等于2000的員工信息
--從員工表中查詢出所有的部門編號
--查詢出名字以A開頭的員工的信息
--查詢出名字第二個字母是L的員工信息
--查詢出沒有獎金的員工信息
--所有員工的平均工資
--所有員工的工資總和
--所有員工的數(shù)量
--最高工資
--最少工資
--最高工資的員工信息
--最低工資的員工信息
- 分組查詢
--每個部門的平均工資
- 子查詢
-- 單行子查詢(> < >= <= = <>)
-- 查詢出高于10號部門的平均工資的員工信息
-- 多行子查詢(in not in any all) >any >all
-- 查詢出比10號部門任何員工薪資高的員工信息
-- 多列子查詢(實際使用較少) in
-- 和10號部門同名同工作的員工信息
-- Select接子查詢
-- 獲取員工的名字和部門的名字
-- from后面接子查詢
-- 查詢emp表中經(jīng)理信息
-- where 接子查詢
-- 薪資高于10號部門平均工資的所有員工信息
-- having后面接子查詢
-- 有哪些部門的平均工資高于30號部門的平均工資
-- 工資>JONES工資
-- 查詢與SCOTT同一個部門的員工
-- 工資高于30號部門所有人的員工信息
-- 查詢工作和工資與MARTIN完全相同的員工信息
-- 有兩個以上直接下屬的員工信息
-- 查詢員工編號為7788的員工名稱,員工工資,部門名稱,部門地址
- SQL查詢的綜合案例
- 查詢出高于本部門平均工資的員工信息
- 列出達拉斯加工作的人中,比紐約平均工資高的人
- 查詢7369員工編號,姓名,經(jīng)理編號和經(jīng)理姓名
- 查詢出各個部門薪水最高的員工所有信息
面試題
CREATE TABLE test(
name CHAR(20),
kecheng CHAR(20),
fenshu CHAR(20)
);
INSERT INTO test VALUES('張三','語文',81),
('張三','數(shù)學',75),
('李四','語文',76),
('李四','數(shù)學',90),
('王五','語文',81),
('王五','數(shù)學',82);
--請用一條Sql語句查處分數(shù)大于80的學生
