It's Saturday again-MySQL special training,-MySQL_PHP tutorial-PHP Tutorial-php.cn

Home

Backend Development

PHP Tutorial

It's Saturday again-MySQL special training,-MySQL_PHP tutorial

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jul 12, 2016 am 09:05 AM

mysql database

It’s Saturday again - MySQL special training, -MySQL

It’s Saturday again, another long morning~ Let’s start after lunch

1. MySQL

-----Subquery and Connection (3)-----

----Use INSERT...SELECT to insert records

--English version of database content

Because the MYSQL console in my WAMP showed that the Chinese language was weak, and I was exhausted during the change process, I changed my tactics and changed the data to all English. I am sharing it now

--Create data table

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
);

-- Write record

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

Just like this when using it, create a new table

CREATE TABLE IF NOT EXISTS cates(

cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

cate_name VARCHAR(40) NOT NULL

);

Then selectively insert the things in the original data table

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

If we need to update our parameter table based on the catalog table categories - multi-table update

----Multiple table update

--Multiple steps

UPDATE table_ref SET col_name=... ...

Among them, ref is the reference relationship of the table

There are connections here, INNER JOIN LEFT JOIN, etc.

The simplest one is INNER, inner connection

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

(Please forgive me for not typing in lowercase...)

Explain: update the goods table, internally connect the categories table, under what conditions - goods_cate=cate_name, what is the set value, it is the cate_id in the categories table

mysql> SELECT * FROM GOODSG; Verify it

--One step

CREATE...SELECT...

That is, creating the table and writing the connected data at the same time

The example here is: making a connection update to the brand

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;

You can save the INSERT operation

But the update will go wrong at this time

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

Because the names are the same, we need to change the field or use an alias AS

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

Although the appearance has been updated, for example, the type of goods_cate is still varchar, and we actually want it to be of type int in the main table. At this time, because you need to modify the field name and data type, use ALTER CHANGE

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

This kind of foreign key is called a fact foreign key, and it has more physical foreign keys than the foreign key

----Connect

Since we have implemented multi-table storage, but due to display and other requirements, multiple tables are required to be linked, and a connection is required

Data table reference ref: tbl_name [[AS] alias] | table_subquery [AS] alias

---INNER JOIN

Use ON to set connection conditions, WHERE generally performs further filtering

Inner connection, only display records that meet the connection conditions

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

Translation: Select the goods_id, goods_name, cate_name fields in the goods table, but when displaying, you need to connect the cates table internally (because we don’t want to display the numerical representation of the type), the condition is that the cate_id fields of the two are equal ;Here, since cate_id is not given an alias, it is written as table name.cate_id

You will find that the display of this inner connection only displays the results of the on condition

---Outer join

Outer joins are divided into left outer and right outer joins

LEFT JOIN displays all the records in the left table and the records in the right table that meet the link conditions. The example is as follows

注意最后一个记录的类名是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;

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact 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.第三方库

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

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

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

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

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