与MySQL的零距离接触(二.实例：记录操作)

---

道德三皇五帝，功名夏侯商周，五霸七雄闹春秋，顷刻兴亡过手，
青石几行名姓，北邙无数荒丘，前人播种后人收，说甚龙争虎斗。

---

一、操作数据表中的记录

1、MySQL插入记录INSERT

创建新表，并添加记录。

2、MySQL插入记录INSERT SET-SELECT

INSERT users3 SET username='Ben',password='456';

3、MySQL单表更新记录UPDATE

//表示原有年龄的基础上加5 更新一列
UPDATE users3 set age=age+5;

//更新多列（年龄改变为当前年龄减去id，sex值都为0）
UPDATE users3 SET age =age-id,sex=0;

//使用条件更新记录
UPDATE users SET age = age+10 WHERE id%2=0;

4、MySQL单表删除记录DELETE

DELETE FROM users3 WHERE id=2;

删除一条记录，在添加一条记录，添加的id为删除前最大的id号加1

5、MySQL查询表达式解析

SELECT id,username FROM users3;

//使用别名查找
SELECT id AS userId,username AS uname FROM users3;

6、MySQL where语句进行条件查询

7、MySQL group by 语句对查询结果分组

ASC升序，DESC降序

SELECT sex FROM users3 GROUP BY sex;

8、having语句设置分组条件

对GROUP BY的查询结果进行过滤
在HAVING字句中，查询对象必须出现在查询表达式中或使用聚合函数

 SELECT sex FROM users3 GROUP BY 1 HAVING count(id)>=2;

9、order by语句对查询结果排序

SELECT * FROM users3 ORDER BY id DESC;

10、limit语句限制查询数量

SELECT*FROM users LIMIT 2;    表示记两条记录，第0条开始SELECT*FROM users LIMIT 3,2;  表示在第3条开始记录，共2条记录

//把查询到的结果的username插入到另外一张表
INSERT test(username) SELECT username FROM users3 WHERE age>=20;

二、子查询与连接

1、数据准备

1》创建tdb_goods数据表

CREATE TABLE tdb_goods(-> goods_id SMALLINT(5) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,//商品id-> 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,//商品价格-> is_show TINYINT(1) NOT NULL DEFAULT 1,//商品是否上架-> is_saleoff TINYINT(1) NOT NULL DEFAULT 0//商品是否销售一空-> );

查看数据表：SHOW TABLES;
查看数据表结构：SHOW COLUMNS FROM tdb_goods;

修改数据表属性：

ALTER TABLE tdb_goods MODIFY goods_price DECIMAL(15,3) UNSIGNED NOT NULL DEFAULT 0.000;

2》写入记录

 INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('R510VC 15.6英寸笔记本','笔记本','华硕','3399',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Y400N 14.0英寸笔记本电脑','笔记本','联想','4899',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('G150TH 15.6英寸游戏本','游戏本','雷神','8499',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X550CC 15.6英寸笔记本','笔记本','华硕','2799',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X240(20ALA0EYCD) 12.5英寸超极本','超级本','联想','4999',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('U330P 13.3英寸超极本','超级本','联想','4299',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('SVP13226SCB 13.3英寸触控超极本','超级本','索尼','7999',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad mini MD531CH/A 7.9英寸平板电脑','平板电脑','苹果','1998',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iPad Air MD788CH/A 9.7英寸平板电脑 （16G WiFi版）','平板电脑','苹果','3388',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' iPad mini ME279CH/A 配备 Retina 显示屏 7.9英寸平板电脑 （16G WiFi版）','平板电脑','苹果','2788',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('IdeaCentre C340 20英寸一体电脑 ','台式机','联想','3499',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostro 3800-R1206 台式电脑','台式机','戴尔','2899',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('iMac ME086CH/A 21.5英寸一体电脑','台式机','苹果','9188',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('AT7-7414LP 台式电脑 （i5-3450四核 4G 500G 2G独显 DVD 键鼠 Linux ）','台式机','宏碁','3699',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Z220SFF F4F06PA工作站','服务器/工作站','惠普','4288',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('PowerEdge T110 II服务器','服务器/工作站','戴尔','5388',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Mac Pro MD878CH/A 专业级台式电脑','服务器/工作站','苹果','28888',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('X3250 M4机架式服务器 2583i14','服务器/工作站','IBM','6888',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('玄龙精英版 笔记本散热器','笔记本配件','九州风神','',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES(' HMZ-T3W 头戴显示设备','笔记本配件','索尼','6999',DEFAULT,DEFAULT);INSERT tdb_goods (goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('商务双肩背包','笔记本配件','索尼','99',DEFAULT,DEFAULT);

3》以网格线的形式查看插入的数据

ELECT * FROM tdb_goods\G;

如果显示乱码则修改客户端的显示编码格式

SET NAMES gbk;

2、MySQL子查询简介

子查询是一种常用计算机语言SELECT-SQL语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时，称之为子查询。
例句：

SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);

其中，SELECT * FROM t1 …称为Outer Query[外查询](或者Outer Statement),
SELECT column1 FROM t2 称为Sub Query[子查询]。

所以，我们说子查询是嵌套在外查询内部。而事实上它有可能在子查询内部再嵌套子查询。
子查询必须出现在圆括号之间。

3、由比较运算符引发的子查询

查找所有商品的平均价格

//AVG为求平均值的聚合函数
SELECT AVG(goods_price) FROM tdb_goods;

//计算价格平均值，四舍五入保留小数点后2位
SELECT ROUND(AVG(goods_price),2)FROM tdb_goods;

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= 5636.36;

通过子查询的方式实现

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2)FROM tdb_goods);

寻找超极本的价格

SELECT goods_price FROM tdb_goods WHERE goods_cate='超级本';

查询哪些商品的价格大于超级本

由于查询到多个结果，所以报错，需要使用下面的方式进行修饰

SELECT goods_id,goods_name,goods_price FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate ='超级本');

由此看出都是大于4299

4、由[NOT]IN/EXISTS引发的子查询

5、使用INSERT…SELECT插入记录

在上面的数据中，goods_cate和goods_name字段有很多是重复的，且汉字所占字节数要比数字要多，如果数据量比较大的话，查询起来会越来越慢。我们可以通过外键的方式进行更改数据表。
新创建一张数据表 tdb_goods_cates

 CREATE TABLE IF NOT EXISTS tdb_goods_cates(-> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> cate_name VARCHAR(40) NOT NULL-> );

//查看分类
SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

-- 将分组结果写入到tdb_goods_cates数据表INSERT tdb_goods_cates (cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;

6、多表更新

在mysql，join,cross join和inner join 是等价的。
left [outer] join,左外连接
right [outer] join,右外连接

//INNER JOIN 内连接UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name-> SET goods_cate = cate_id;

7、多表更新之一步到位

 CREATE TABLE tdb_goods_brands-> (-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> brand_name VARCHAR(40) NOT NULL-> )-> SELECT brand_name FROM tdb_goods GROUP BY brand_name;

更新tbd_goods数据

mysql> UPDATE tdb_goods INNER JOIN tdb_goods_brands ON brand_name = brand_name-> SET brand_name = brand_id;
ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous

错误信息：brand_name含义不明确

我们使用别名的方式进行更改

UPDATE tdb_goods AS g INNER JOIN tdb_goods_brands AS b ON g.brand_name = b.brand_name SET g.brand_name = b.brand_id;

但是并不能更改表结构

因此我们要记得修改表结构

 ALTER TABLE tdb_goodsCHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL, CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;

我们分别在tdb_goods_cates和tdb_goods_brands表插入记录

INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');

-- 在tdb_goods数据表写入任意记录INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');

平时，我们需要将多个表的数据展示给用户观看，就需要使用连接来完成。

8、连接的语法结构

9、内连接INNER JOIN

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

SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

并没有查找出我们新更新的数据内容，因为它们不满足查询的条件

INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交换机'),('网卡');
INSERT tdb_goods_brands(brand_name) VALUES('海尔'),('清华同方'),('神舟');
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印机','12','4','1849');

10、外连接OUTER JOIN

内连接：显示左表及右表符合连接条件的记录，A ∩ B
左外连接：显示左表中的全部和右表符合连接条件的记录，A ∪ (A ∩ B)
右外连接：显示右表中的全部和左表符合连接条件的记录，B ∪ (A ∩ B)

11、多表连接

SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods AS g -> INNER JOIN tdb_goods_cates AS c ON g.cate_id = c.cate_id-> INNER JOIN tdb_goods_brands AS b ON g.brand_id = b.brand_id\G;

12、关于连接的几点说明

13、无限级分类表设计

 CREATE TABLE tdb_goods_types(type_id   SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,//分类idtype_name VARCHAR(20) NOT NULL,//分类名称parent_id SMALLINT UNSIGNED NOT NULL DEFAULT 0//父类id); 

INSERT tdb_goods_types(type_name,parent_id) VALUES('家用电器',DEFAULT);INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑、办公',DEFAULT);INSERT tdb_goods_types(type_name,parent_id) VALUES('大家电',1);INSERT tdb_goods_types(type_name,parent_id) VALUES('生活电器',1);INSERT tdb_goods_types(type_name,parent_id) VALUES('平板电视',3);INSERT tdb_goods_types(type_name,parent_id) VALUES('空调',3);INSERT tdb_goods_types(type_name,parent_id) VALUES('电风扇',4);INSERT tdb_goods_types(type_name,parent_id) VALUES('饮水机',4);INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑整机',2);INSERT tdb_goods_types(type_name,parent_id) VALUES('电脑配件',2);INSERT tdb_goods_types(type_name,parent_id) VALUES('笔记本',9);INSERT tdb_goods_types(type_name,parent_id) VALUES('超级本',9);INSERT tdb_goods_types(type_name,parent_id) VALUES('游戏本',9);INSERT tdb_goods_types(type_name,parent_id) VALUES('CPU',10);INSERT tdb_goods_types(type_name,parent_id) VALUES('主机',10);

自身连接：同一个数据表对其自身进行连接。

SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;

SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN  tdb_goods_types s ON s.parent_id = p.type_id;

-- 查找所有分类及其子类的数目SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;-- 为tdb_goods_types添加child_count字段ALTER TABLE tdb_goods_types ADD child_count MEDIUMINT UNSIGNED NOT NULL DEFAULT 0;-- 将刚才查询到的子类数量更新到tdb_goods_types数据表UPDATE tdb_goods_types AS t1 INNER JOIN ( SELECT p.type_id,p.type_name,count(s.type_name) AS children_count FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id ) AS t2 ON  t1.type_id = t2.type_id SET t1.child_count = t2.children_count;-- 复制编号为12,20的两条记录SELECT * FROM tdb_goods WHERE goods_id IN (19,20);-- INSERT ... SELECT实现复制INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);

14、多表删除

-- 查找重复记录SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name HAVING count(goods_name) >= 2;-- 删除重复记录DELETE t1 FROM tdb_goods AS t1 LEFT JOIN (SELECT goods_id,goods_name FROM tdb_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;