在前面介绍了数据的记录的操作,主要包括两类:
记录的写操作:增加记录,修改记录,删除记录
记录的读取操作:记录的查找
首先在 test 数据库 中创建一个数据表 goods
root@localhost test>CREATE TABLE goods(
-> goods_id SMALLINT(5) UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> goods_name varchar(150),
-> goods_cate varchar(40),
-> brand_name varchar(40),
-> goods_price decimal(15,3) unsigned DEFAULT 0.000,
-> is_show tinyint(1) DEFAULT 1,
-> is_saleoff tinyint(1) DEFAULT 0
-> );
使用命令查看创建情况,该命令用于查看表的结构:
root@localhost test>show columns from goods;
现在插入若干条记录
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostr','lapPC','lennovo',3990,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('honorX6','phone','honor',990,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('lingyue','lapPC','HP',3990,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('back-2','backpack','sony',690,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('mi-9','phone','xiaomi',2990,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('sdkf','Trolley-bag','xuan',290,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('x-3','TV','TCL',1890,default,default);
insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('A-6','camera','sony',4990,default,default);
子查询(SubQuery)是指出现在其他SQL语句内的SELECT 子句
SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2)
其中SELECT * FROM t1 称之为 Outer Query /Outer Statement 为外层查询,SELECT col2 FROM t2 称之为SubQuery
子查询是指嵌套在查询内部,且必须始终出现在()内,子查询可以包括多个关键字和条件,如DISTINCT,GROUP BY,ORDER BY,LIMIT,函数等;子查询的外层查询可以是:SELECT,INSERT,UPDATE,DET,DO.
子查询的结果可以是一个标量,一行,一列或者子查询
1、使用比较运算符的子查询
使用比较运算符的子查询,其中比较运算符有 =、>、 <、>=、<=、<>、!=、<=> ,其语法结构为:
operand comparision_operator subquery
首先对表中的所有商品的价格进行平均值求解:
root@localhost test>SELECT AVG(goods_price) FROM goods;
小数点的位数太多,这里进行指定为为小数点后取两位
root@localhost test>SELECT ROUND(AVG(goods_price),2) FROM goods;
现在查询哪些商品的价格大于平均值呢
root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price >= 2477.50;
这里使用的 2477.50 就是上一个查询结果的值,能否将两个语句“合并”,就利用子查询进行实现
root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM goods);
这样就实现了子查询的功能。
现在来查询一下,分类中goods_cate为phone的有哪些商品
这里查询的时候对表达式goods_cate=phone中没有对phone进行使用引号报错了,加上引号之后:
root@localhost test>SELECT goods_price FROM goods WHERE goods_cate='phone';
现在想知道哪些商品的价格大于这些 phone
root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > (SELECT goods_price FROM goods WHERE goods_cate='phone');
此时系统会提示 ERROR 1242 (21000):Subquery returns maore than 1 row 因为在上述查询中知道满足goods_cate='phone'的结果有两个,子查询返回了三条结果,所以应该指定到底是大于990还是2990,此时会用到以下语法
operand comparsion_operator ANY (subquery)
operand comparsion_operator SOME (subquery)
operand comparsion_operator ALL (subquery)
当子查询返回多个结果时,可以使用以上的语法进行修饰
root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > ANY (SELECT goods_price FROM goods WHERE goods_cate='phone');
root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > ALL (SELECT goods_price FROM goods WHERE goods_cate='phone');
2、使用[NOT] IN的子查询
使用IN 或者 NOT IN 的子查询和上述的方法基本相同,其语法结构如下
operand comparsion_operator [NOT] IN (subquery)
而该语法又与ALL,ANY又一定的联系,其中 =ANY与IN与算符等价,!=ALL或者<>ALL运算符与NOT IN运算符等价
root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price NOT IN (SELECT goods_price FROM goods WHERE goods_cate='phone');
查找产品类型不为phone 的记录
3、使用[NOT] EXISTS的子查询
这种情况使用的较少,如果子查询返回任何行,EXISTS将返回TRUE否则返回FALSE
4、多表更新
在数据表中可以发现,存在很多重复的字段,利用命令查看,如产品类型goods_cate中的LapPC,phone和品牌brand_name中的sony等,目前因为数据表只涉及了8条记录,但是如果有越来越多的数据记录,这将造成 查找时候效率降低 ,想着可以用外键来实现:
root@localhost test>select * from goods\G;
而外键的实现是利用两张数据表,现在只有一张数据表,因此需要创建一张数据表来存储分类和品牌,那如何才能得到这些商品的不同分类,以方便实现存储呢?就是在查找的时候按照分组就可以
root@localhost test> CREATE TABLE IF NOT EXISTS goods_cates(
-> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> cate_name VARCHAR(40) NOT NULL
-> );
注意这里的数据表的名称为goods_cates和数据表goods中的记录goods_cate不要混淆
root@localhost test>SELECT goods_cate FROM goods GROUP BY goods_cate;
利用分类进行查看,这里共有6类,8条记录共有6类,随着商品记录不断增加,在类别上可能会大大的减少
将查找的类别写入到数据表中,可以一条一条的写入,当然这种方法面对很多类别的时候,操作相当麻烦,因此可以是如下语句
INSERT [INFO] table_name [(col_name,...)] SELECT...
将查询结果写入到数据表中
root@localhost test>select * from goods_cates;
数据表goods_cates为空,数据表的结结构为:
root@localhost test>DESC goods_cates;
![](https://i-blog.csdnimg.cn/blog_migrate/38a47f1eaf063a8744e6c451361323e7.png)
这张数据表有两个字段,现在写入记录
root@localhost test>INSERT goods_cates(cate_name) SELECT goods_cate FROM goods GROUP BY goods_cate;
这里在INSERT的时候省略了VALUES,要注意
这6条记录已经成功写入到数据表中了,但是没有使用外键实现,而在数据表goods中依然存储的是lapPC,phone,而根据设计目的,在goods_cates数据表中应该将lapPC存储为3,将phone存储为 4等等,因此现在需要参照分类表goods_cates来更新商品表goods,就是参照别的表来更新当前的表,因此需要用到多表更新。其语法结构为
UPDATE table_references SET col_name1={expr1|DEFAULT} [,col_name2=expr2|DEFAULT}]...[WHERE where_condition]
table_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr
root@localhost test>UPDATE goods INNER JOIN goods_cates ON goods_cate = cate_name
-> SET goods_cate = cate_id;
这里需要更新的是表goods中的goods_cate使用内连接的方式,将goods表中的goods_cate修改为表goods_cates表中的cate_id,(如将goods中的goods_cate 为lapPC修为为3,因为goods_cates表的lapPC为3,goods_cate和cate_name中的值是相等的,)因此上述语句中连接条件为ON goods_cate = cate_name,条件有了,那么更新的值是什么呢,那就是SET goods_cate = cate_id
因此多表更新可以总结为:
1、创建新表,这里是goods_cates
2、插入记录,这里是将goods_cate中的值插入到表goods_cates中的cate_name
3、多表的更新
发现上述步骤较为繁琐,可以实行CREATE...SELECT来实现
即是创建数据表同时将查询结果写入到数据表中,其语法结构为:
CREATE TABLE [IF NOT EXISTS] table_name [(create_defination,...)] select_statement
将上述的中1、2步骤合成一个命令,首先查看一下有多少种品牌
root@localhost test>SELECT brand_name FROM goods GROUP BY brand_name;
![](https://i-blog.csdnimg.cn/blog_migrate/6a21b9cf2f8d90a1a5df192baf46f580.png)
root@localhost test>CREATE TABLE table_goods_brands(
-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
-> brand_name VARCHAR(40) NOT NULL
-> )
-> SELECT brand_name FROM goods GROUP BY brand_name;
注意这里在创建数据结束的时候没有加";",而是在SELECT语句之后加上的“;”
此时需要参照品牌表table_goods_brands更新商品表goods,分别查询两个表中的结构情况
root@localhost test>UPDATE goods INNER JOIN table_goods_brands ON brand_name = brand_name
-> SET brand_name = brand_id;
提示错误!因为两个表中均有brand_name,此时利用起别名进行区分是哪个表的grand_name
root@localhost test>UPDATE goods AS A INNER JOIN table_goods_brands AS B ON A.brand_name = B.brand_name
-> SET A.brand_name = B.brand_id;
再查询一下结构
发现goods_cate和brand_name依然是字符型,因此需要尽量的去修改结构
root@localhost test>alter table goods
-> change goods_cate cate_id smallint unsigned not null,
-> change brand_name brand_id smallint unsigned not null;
再次查找发现现在才更改为smallint unsigned进行了“瘦身”操作。目前将数据进行分开存储了,那要不要做外键呢?其实外键不一定非要物理的外键,就是非要有FOREIGN KEY ,也可以是使用上述形式的外键,这种外键往往称之为事实的外键,也是常用的一种形式,而真正的物理的外键就是有 FOREIGN KEY用的不是特别多.
使用命令 select * from goods\G 进行查看
数字所占的字节数要远远小于字节占的字节数。现在再在goods和table_goods_brand 插入记录
root@localhost test>INSERT goods_cates VALUES (default,'route'),(default,'switch'),(default,'ethnet');
root@localhost test>INSERT table_goods_brands VALUES (default,'Huawei'),(default,'Huawei'),(default,'H3C');
再在goods数据表中写入一条记录
root@localhost test>INSERT goods(goods_name,cate_id,brand_id,goods_price) VALUES ('Laserjet Pro P16','12','4','2998');
注意这里的cate_id为12,用于并不存在为12的分类,但是有外键存在,所以也是成功的