我是在大二的时候学的数据库,没有专心听过。最近辞职了,想捡起来。
前段时间去一个面试,问到我多表联查相关的,我发现自己进度不行了,最基础的都不会~弱爆了
今天总结多表联查!!!耶耶耶
排版希望后期整理
【重点】:找准表与表之间的联系
【方法】
笛卡尔积:
内连接: 查询两个表中符合连接条件的记录。内用得多!!!
语法:SELECT 字段名称,...FROM tbl_name1
INNER JOIN tbl_name2
ON 连接条件
【例子】
SELECT e.id,e.username,e.age,d.depname
FROM emp AS emp
INNER JOIN dep AS d
ON e.depid=d.id;
外连接: 左外连接/右连接(左连接:左边为主,右边为从)
(左连接)先显示左表的全部记录,再去右表查询符合条件的记录,不符合的以null代替
语法:SELECT 字段名称,...FROM tbl_name1
RIGHT/LEFT OUTER JOIN tbl_name2
ON 连接条件
【例子】
SELECT e.id,e.username,e.age,d.depname
FROM emp AS e
RIGHT OUTER JOIN dep AS d
ON e.depid=d.id;
【建表】省份表,管理员表,商品分类表,商品表
省份表,管理员表,关系是管理员的地域和省份的id有关系
商品分类表,商品表,商品分类的id在商品表中的分类id有联系
管理员表,商品表,管理员id和商品中的adminid有联系
–省份表
CREATE TABLE shengfen(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
proname VARCHAR(20) NOT NULL UNIQUE
);
INSERT shengfen(proname) VALUES(‘北京’),(‘武汉’),(‘成都’),(‘深圳’),(‘重庆’);
–管理员表(id,名字,邮箱,地区,)
CREATE TABLE admin(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
name VARCHAR(10) NOT NULL,
email VARCHAR(20) NOT NULL DEFAULT’575656786qq.com’,
proid TINYINT UNSIGNED NOT NULL
);
INSERT admin(name,proid)
VALUES(‘张三’,2),(‘李四’,5),(‘王二’,3),(‘麻子’,1),(‘隔壁老王’,5),(‘滚蛋’,4),(‘小芳’,2);
–商品分类(id,分类名称,分类描述)
CREATE TABLE cate(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
catename VARCHAR(10) NOT NULL,
catedesc VARCHAR(50) NOT NULL DEFAULT ”
);
INSERT cate(catename) VALUES(‘母婴’);
INSERT cate(catename) VALUES(‘电器’);
INSERT cate(catename) VALUES(‘食品’);
INSERT cate(catename) VALUES(‘男装’);
INSERT cate(catename) VALUES(‘女装’);
INSERT cate(catename) VALUES(‘药品’);
–商品表(id,价格,名字,cateid、)
CREATE TABLE products(
id TINYINT UNSIGNED AUTO_INCREMENT KEY ,
name VARCHAR(10) NOT NULL,
price FLOAT(8,2) NOT NULL ,
cateid TINYINT UNSIGNED NOT NULL,
adminid TINYINT UNSIGNED NOT NULL
);
INSERT products(name,price,cateid) VALUES(‘感康’,15,6),(‘尿不湿’,88,1),
(‘巧克力’,20,3),(‘牛仔裤’,255,4),(‘超短裙’,600,5),(‘电视机’,5001,2);
【实例】
–修改省份的名字
mysql> UPDATE shengfen SET proname=’帝都’ WHERE id =1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
回显
mysql> SELECT * FROM shengfen;
+—-+———+
| id | proname |
+—-+———+
| 1 | 帝都 |
| 2 | 武汉 |
| 3 | 成都 |
| 4 | 深圳 |
| 5 | 重庆 |
+—-+———+
5 rows in set (0.00 sec)
–查询省份表中管理员的信息
mysql> SELECT s.id,s.proname,a.name,a.email,a.proid
-> FROM shengfen AS s
-> JOIN admin AS a
-> ON s.id=a.proid;
回显
+—-+———+————–+—————–+——-+
| id | proname | name | email | proid |
+—-+———+————–+—————–+——-+
| 2 | 武汉 | 张三 | 575656786qq.com | 2 |
| 5 | 重庆 | 李四 | 575656786qq.com | 5 |
| 3 | 成都 | 王二 | 575656786qq.com | 3 |
| 1 | 帝都 | 麻子 | 575656786qq.com | 1 |
| 5 | 重庆 | 隔壁老王 | 575656786qq.com | 5 |
| 4 | 深圳 | 滚蛋 | 575656786qq.com | 4 |
| 2 | 武汉 | 小芳 | 575656786qq.com | 2 |
+—-+———+————–+—————–+——-+
–查询管理员中a.username,a.email;商品p.id,p.name,p.price;
商品分类c.catename,并且按照商品价格降序排列
SELECT a.name,a.email,p.id,p.name,p.price,c.catename
FROM admin AS a
JOIN products AS p
ON a.id=p.adminid
JOIN cate AS c
ON p.cateid =c.id
ORDER BY p.price DESC;
+————–+—————–+—-+———-+———+———-+
| name | email | id | name | price | catename |
+————–+—————–+—-+———-+———+———-+
| 张三 | 575656786qq.com | 6 | 电视æœ | 5001.00 | 电器 |
| 李四 | 575656786qq.com | 5 | 超短è£ | 600.00 | 女装 |
| 滚蛋 | 575656786qq.com | 4 | 牛仔è£ | 255.00 | 男装 |
| 隔壁老王 | 575656786qq.com | 2 | 尿不æ¹ | 88.00 | 母婴 |
| 麻子 | 575656786qq.com | 3 | 巧克åŠ | 20.00 | 食品 |
| 小芳 | 575656786qq.com | 1 | 感康 | 15.00 | 药品 |
+————–+—————–+—-+———-+———+———-+
只显示(2,3)
SELECT a.name,a.email,p.id,p.name,p.price,c.catename
FROM admin AS a
JOIN products AS p
ON a.id=p.adminid
JOIN cate AS c
ON p.cateid =c.id
ORDER BY p.price DESC
LIMIT 2,3;
+————–+—————–+—-+———-+——–+———-+
| name | email | id | name | price | catename |
+————–+—————–+—-+———-+——–+———-+
| 滚蛋 | 575656786qq.com | 4 | 牛仔è£ | 255.00 | 男装 |
| 隔壁老王 | 575656786qq.com | 2 | 尿不æ¹ | 88.00 | 母婴 |
| 麻子 | 575656786qq.com | 3 | 巧克åŠ | 20.00 | 食品 |
+————–+—————–+—-+———-+——–+———-+