又是周六了-MySQL特训，-MySQL_PHP教程-php教程-PHP中文網

首頁

後端開發

php教程

又是周六了-MySQL特训，-MySQL_PHP教程

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jul 12, 2016 am 09:05 AM

mysql資料庫

又是周六了-MySQL特训，-MySQL

又是周六，又是磨蹭个一上午~午饭后开始吧

1、MySQL

-----子查询与连接（三）-----

----使用INSERT...SELECT插入记录

--数据库内容的英文版本

由于我的WAMP中的MYSQL控制台显示中文无力，在更改过程中心力交瘁，遂改变战术，把数据改为了全英文的，现分享出来

-- 创建数据表

CREATE TABLE IF NOT EXISTS goods(
goods_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
goods_name VARCHAR(150) NOT NULL,
goods_cate VARCHAR(40) NOT NULL,
brand_name VARCHAR(40) NOT NULL,
goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0,
is_show BOOLEAN NOT NULL DEFAULT 1,
is_saleoff BOOLEAN NOT NULL DEFAULT 0
);

-- 写入记录

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6Inches Laptop','Laptop','ASUS','3399',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0Inches Laptop','Laptop','Lenovo','4899',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6Inches Gamebook','Gamebook','ThunderGod','8499',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6Inches Laptop','Laptop','ASUS','2799',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5InchesUltrabook','Ultrabook','Lenovo','4999',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3Inches Ultrabook','Ultrabook','Lenovo','4299',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3Inches Touch Ultrabook','Ultrabook','Sony','7999',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9Inches Tablet','Tablet','Apple','1998',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7Inches Tablet （16G WiFi Vision）','Tablet','Apple','3388',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A with Retina screen 7.9InchesTablet （16G WiFi Vision）','Tablet','Apple','2788',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20Inches All-in-one ','Desktop','Lenovo','3499',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 desktop','Desktop','Dell','2899',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5Inches All-in-one','Desktop','Apple','9188',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP Desktop （i5-3450 4G 500G 2GIndependent graphics card DVD Keyboard&Mouse Linux ）','Desktop','Acer','3699',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PAWork station','server/Work station','HP','4288',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 IIserver','server/Work station','Dell','5388',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A Professional Desktop','server/Work station','Apple','28888',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W Headset Display Device','Laptop accessory','Sony','6999',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Business Backpage','Laptopaccessory','Sony','99',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4 server 2583i14','server/Work station','IBM','6888',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Xuanlong Laptop Radiator','Laptop accessory','Windgod','',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W Headset Display Device','Laptop accessory','Sony','6999',DEFAULT,DEFAULT);

INSERT goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Business Backpage','Laptop accessory','Sony','99',DEFAULT,DEFAULT);

---INSERT...SELECT

INSERT [INTO] tbl_name [(col_name)] SELECT

使用的时候就这样，创建一个新的表

CREATE TABLE IF NOT EXISTS cates(

cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

cate_name VARCHAR(40) NOT NULL

);

然后把原数据表中的东西有选择性的插入进来

INSERT INTO CATES(CATE_NAME) SELECT GOODS_CATE FROM GOODS GROUP BY GOODS_CATE;

如果需要根据目录表cates，来更新我们的参数表——多表更新

----多表更新

--多步

UPDATE table_ref SET col_name=... ...

其中，ref是表的参照关系

这里有连接关系，INNER JOIN LEFT JOIN 等

最简单的是INNER，内连接

mysql> UPDATE GOODS INNER JOIN CATES ON GOODS_CATE=CATE_NAME
-> SET GOODS_CATE=CATE_ID;

（请原谅我不想打小写的了。。）

解释一下：更新goods这张表，内连接cates这张表，在什么条件呢——goods_cate=cate_name，设定的值是什么呢，是cates这张表中的cate_id

mysql> SELECT * FROM GOODS\G; 验证一下

--一步

CREATE...SELECT...

即创建表的同时写入连接的数据

这里的例子是：对品牌做连接更新

mysql> SELECT BRAND_NAME FROM GOODS GROUP BY BRAND_NAME;

mysql> CREATE TABLE IF NOT EXISTS brands(
-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> brand_name VARCHAR(40) NOT NULL
-> )
-> SELECT BRAND_NAME FROM GOODS GROUP BY BRAND_NAME;

就可以省去INSERT的操作了

但是这时候更新要出错

mysql> UPDATE GOODS INNER JOIN BRANDS ON BRAND_NAME=BRAND_NAME
-> SET BRAND_NAME=BRAND_ID;
ERROR 1052 (23000): Champ: 'BRAND_NAME' dans field list est ambigu

因为名字一样，要改字段，或者起别名AS

mysql> UPDATE GOODS AS G INNER JOIN BRANDS AS B ON G.BRAND_NAME=B.BRAND_NAME
-> SET G.BRAND_NAME=BRAND_ID;

虽然实现了外表的更新，但是，比如说goods_cate的类型还是varchar，而我们其实希望主表中，它是int类型的。这时候由于需要修改字段名以及数据类型，用ALTER CHANGE

mysql> ALTER TABLE GOODS
-> CHANGE GOODS_CATE CATE_ID SMALLINT UNSIGNED NOT NULL,
-> CHANGE BRAND_NAME BRAND_ID SMALLINT UNSIGNED NOT NULL;

这种外键称为事实外键，比foreign key的物理外键还要多

----连接

由于我们实现了多表的存储，但是由于显示等需求，要求多个表联动，需要用到连接

数据表参照ref：tbl_name [[AS] alias] | table_subquery [AS] alias

---INNER JOIN

使用ON来设定连接条件，WHERE一般做进一步的筛选

内连接，仅显示符合连接条件的记录

SELECT GOODS_ID,GOODS_NAME,CATE_NAME FROM GOODS INNER JOIN CATES ON GOODS.CATE_ID=CATES.CATE_ID;

翻译一下：选取goods表中的goods_id,goods_name,cate_name字段，但是显示的时候，需要内连接cates表（因为我们不想显示的是类型的数字表示），条件是当两者的cate_id字段相等；这里由于cate_id没有给别名，所以写成表名.cate_id

会发现，这个内连接的显示，只显示了on条件的结果

---外连接

外连接又分为左外和右外

LEFT JOIN显示左表的全部记录和右表符合链接条件的记录，例子如下

注意最后一个记录的类名是null

RIGHT JOIN是这样的

----多表连接

mysql> SELECT GOODS_ID,GOODS_NAME,CATE_NAME,BRAND_NAME,GOODS_PRICE FROM GOODS AS G
-> INNER JOIN CATES AS C ON G.CATE_ID=C.CATE_ID
-> INNER JOIN BRANDS AS B ON G.BRAND_ID=B.BRAND_ID;

每连接一个表就需要一个JOIN，注意起别名给数据表！

此时的显示虽然跟一开始一样，但实现不同，意义大不一样

----无限级分类表设计

一般实际中会出现无限级数据表，不断往下分

举个例子

CREATE TABLE goods_types(
type_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
type_name VARCHAR(20) NOT NULL,
parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0
);

INSERT goods_types(type_name,parent_id) VALUES('Home Electronics',DEFAULT);
INSERT goods_types(type_name,parent_id) VALUES('PC、Office',DEFAULT);
INSERT goods_types(type_name,parent_id) VALUES('Appliances',1);
INSERT goods_types(type_name,parent_id) VALUES('Life Appliances',1);
INSERT goods_types(type_name,parent_id) VALUES('Tablet TV',3);
INSERT goods_types(type_name,parent_id) VALUES('Air Conditioner',3);
INSERT goods_types(type_name,parent_id) VALUES('Electrical Fan',4);
INSERT goods_types(type_name,parent_id) VALUES('Water Dispenser',4);
INSERT goods_types(type_name,parent_id) VALUES('Complete Machine',2);
INSERT goods_types(type_name,parent_id) VALUES('Accessory',2);
INSERT goods_types(type_name,parent_id) VALUES('Laptop',9);
INSERT goods_types(type_name,parent_id) VALUES('Ultrabook',9);
INSERT goods_types(type_name,parent_id) VALUES('Gamebook',9);
INSERT goods_types(type_name,parent_id) VALUES('CPU',10);
INSERT goods_types(type_name,parent_id) VALUES('Host Computer',10);

mysql> SELECT * FROM GOODS_TYPES;
+---------+------------------+-----------+
| type_id | type_name | parent_id |
+---------+------------------+-----------+
| 1 | Home Electronics | 0 |
| 2 | PC?Office | 0 |
| 3 | Appliances | 1 |
| 4 | Life Appliances | 1 |
| 5 | Tablet TV | 3 |
| 6 | Air Conditioner | 3 |
| 7 | Electrical Fan | 4 |
| 8 | Water Dispenser | 4 |
| 9 | Complete Machine | 2 |
| 10 | Accessory | 2 |
| 11 | Laptop | 9 |
| 12 | Ultrabook | 9 |
| 13 | Gamebook | 9 |
| 14 | CPU | 10 |
| 15 | Host Computer | 10 |
+---------+------------------+-----------+

注意parent_id，是指改节点的级别，比如0，表示home electronics没有父节点，是个顶级节点

查找的实现，是通过自身连接来完成的

---自身连接

举个例子，显示所有类别的父类是什么

这里，自身的连接，可以想象右边有一张一模一样的表（左边也可以，有些东西稍微变一下就行）

翻译一下，就是，可以先从条件说起，当子表s中的parent_id=父表中的type_id时，选取子表的type_id,type_name,以及父表的type_name显示，这里的别名可以在语句的末尾处显示

要把这个逻辑关系先说说/想想清楚，再翻译为sql语句

反过来，查找所有类别的子类

先考虑逻辑关系：当父表的type_id=子表的parent_id时，取 P.TYPE_ID,P.TYPE_NAME,S.TYPE_NAME ——再写出语句

再复杂一点，且留给你们自己翻译

mysql> SELECT P.TYPE_ID,P.TYPE_NAME,COUNT(S.TYPE_NAME) CHILD_COUNT FROM GOODS_TYPES AS P LEFT JOIN GOODS_TYPES AS S
-> ON P.TYPE_ID=S.PARENT_ID GROUP BY P.TYPE_NAME ORDER BY P.TYPE_ID;
+---------+------------------+-------------+
| TYPE_ID | TYPE_NAME | CHILD_COUNT |
+---------+------------------+-------------+
| 1 | Home Electronics | 2 |
| 2 | PC?Office | 2 |
| 3 | Appliances | 2 |
| 4 | Life Appliances | 2 |
| 5 | Tablet TV | 0 |
| 6 | Air Conditioner | 0 |
| 7 | Electrical Fan | 0 |
| 8 | Water Dispenser | 0 |
| 9 | Complete Machine | 3 |
| 10 | Accessory | 2 |
| 11 | Laptop | 0 |
| 12 | Ultrabook | 0 |
| 13 | Gamebook | 0 |
| 14 | CPU | 0 |
| 15 | Host Computer | 0 |
+---------+------------------+-------------+

----多表删除

基本思路也是通过一张表模拟多张表的操作

懒了，直接复制看吧，需要了再去做

-- INSERT ... SELECT实现复制

INSERT goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM goods WHERE goods_id IN (19,20);

-- 查找重复记录

SELECT goods_id,goods_name FROM goods GROUP BY goods_name HAVING count(goods_name) >= 2;

-- 删除重复记录

DELETE t1 FROM goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM goods GROUP BY goods_name HAVING count(goods_name) >= 2 ) AS t2 ON t1.goods_name = t2.goods_name WHERE t1.goods_id > t2.goods_id;

陳述

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

Go语言和MySQL数据库：如何进行数据冷热分离处理？Jun 18, 2023 am 08:26 AM

随着数据量的不断增加，数据库的性能成为了一个越来越重要的问题。数据冷热分离处理是一种有效的解决方案，它可以将热点数据和冷数据进行分离，从而提高系统的性能和效率。本文将介绍如何使用Go语言和MySQL数据库进行数据冷热分离处理。一、什么是数据冷热分离处理数据冷热分离处理是一种将热点数据和冷数据进行分类处理的方式。热点数据是指访问频率高、对性能要求高的数据，冷数

使用Go语言进行MySQL数据库的数据增量备份的方法Jun 17, 2023 pm 02:28 PM

随着数据量的增加，数据库的备份变得越来越重要。而对于MySQL数据库，我们可以借助Go语言实现自动化的增量备份。本篇文章将简单介绍如何使用Go语言进行MySQL数据库的数据增量备份。一、安装Go语言环境首先，我们需要在本地安装Go语言环境。可以前往官网下载相应的安装包并进行安装。二、安装相应的库Go语言提供了许多访问MySQL数据库的第三方库，其中较为常用的

如何使用MySQL数据库进行时间序列分析？Jul 12, 2023 am 08:39 AM

如何使用MySQL数据库进行时间序列分析？时间序列数据是指按照时间顺序排列的数据集合，它具有时间上的连续性和相关性。时间序列分析是一种重要的数据分析方法，可以用于预测未来趋势、发现周期性变化、检测异常值等。在本文中，我们将介绍如何使用MySQL数据库进行时间序列分析，并附上代码示例。创建数据表首先，我们需要创建一个数据表来存储时间序列数据。假设我们要分析的数

如何使用Go语言进行可靠的MySQL数据库连接？Jun 17, 2023 pm 07:18 PM

随着大量的数据需要存储和处理，MySQL已经成为了应用开发中最常用的关系型数据库之一。而Go语言由于其高效并发处理和简洁的语法，也越来越受到开发者的欢迎。本文就将带领读者通过Go语言实现可靠的MySQL数据库连接，让开发者能够更加高效地查询和存储数据。一、Go语言连接MySQL数据库的几种方式Go语言中连接MySQL数据库通常有3种方式，分别是：1.第三方库

使用Go语言进行MySQL数据库的数据迁移的方法Jun 17, 2023 am 09:01 AM

随着业务的增长和数据库版本升级等因素，数据库迁移变得越来越普遍。在进行数据迁移时，选择合适的工具和语言非常重要。本文将介绍如何使用Go语言进行MySQL数据库的数据迁移。安装MySQL驱动在使用Go语言进行MySQL数据库迁移前，需要首先安装MySQL驱动。在Go语言中，有很多MySQL驱动可供选择。在本文中，我们将选择最常用的官方MySQL驱动包-"dat

MySQL数据库和Go语言：如何进行数据缓存处理？Jun 17, 2023 am 10:05 AM

近年来，Go语言越来越受到开发人员的青睐，成为开发高性能Web应用程序的首选语言之一。MySQL也作为一种流行的数据库，使用广泛。在将这两个技术结合起来使用的过程中，缓存处理是非常重要的一环。下面将介绍如何使用Go语言来处理MySQL数据库的缓存。缓存的概念在Web应用程序中，缓存是为了加快数据的访问速度而创建的一种中间层。它主要用于存储经常被请求的数据，以

MySQL创建标签表实现文章标签功能的实现步骤Jul 02, 2023 pm 04:17 PM

MySQL创建标签表实现文章标签功能的实现步骤标签是一种常用的分类方式，它可以帮助我们更好地组织和检索文章。在许多网站和应用程序中，都会有文章标签的功能。本文将介绍如何使用MySQL创建标签表，并实现文章标签的功能。步骤1：创建标签表首先，我们需要创建一个用于存储标签的表。在MySQL中，可以使用以下命令创建标签表：CREATETABLEtags(

使用Go语言进行MySQL数据库的数据导入导出过滤的方法Jun 17, 2023 pm 04:04 PM

随着互联网和大数据时代的到来，数据处理成为了一项必备的技能。MySQL作为目前世界上最流行的关系型数据库管理系统，一直以来在数据处理领域受到了广泛的应用。MySQL具有性能高、易用性好、灵活性强等优点，但数据导入导出过程中可能会存在重复或无效数据，因此本文将介绍如何使用Go语言进行MySQL数据库的数据导入导出过滤的方法。一、环境搭建安装MySQL数据库在开

See all articles