潇的MySQL自学日记-汇总(一)

记载于2021.6.18

-- 2021/6/14 START

4.5 去重以检索不同的行（返回的匹配列无重复项）

SELECT DISTINCT 列 FROM FROM 表 

4.6 使用limit以限制结果

SELECT 列 FROM 表 LIMIT 3 ：返回的列不多于五行

SELECT 列 FROM 表 LIMIT 3，5 ：返回从第三行开始的5行 = SELECT 列 FROM 表 LIMIT 5 OFFSET 3

4.7 使用完全限定的表明以 跨表格 跨数据库调用准确的列或表

SELECT 表.列 FROM 表 ：跨表格调用准确列

SELECT 表.列 FROM 数据库.表 ：跨数据库调用准确表以调用准确列

 

5.1 排序数据

SELECT 列 FROM 表 ORDER BY 列 ：对列以字母顺序排列

5.2 按多个列排列数据

SELECT 列1，列2，列3 FROM 表 ORDER BY 列1，列2

5.3 指定排序方向

SELECT 列1，列2，列3 FROM 表 ORDER BY 列1，列2 （ ASC ）：升序（默认）

SELECT 列1，列2，列3 FROM 表 ORDER BY 列1，列2 DESC ：降序

5.3.2 指定某列排序

SELECT 列1，列2，列3 FROM 表 ORDER BY 列1 DESC ，列2 仅对列1降序

5.3.3 利用 LIMIT 及 ORDER BY 找出一个列最高最低值

SELECT 列 FROM 表 ORDER BY 列 LIMIT 1 ：列表数字最大（商品最贵）

 

6.1 WHERE 句

SELECT 列1，列2 FROM 表 WHERE 列1=520

6.2 WHERE 子句操作符

=等于 <>不等于 !=不等于 BETWEEN a AND b 指定两个值ab之间 >=大于等于 WHERE 。。 IS NULL 查询空值

7.1 AND 操作符

SELECT 列1，列2，列3 FROM 表 WHERE 列1 = 520 AND 列2 > 999

7.2 OR 操作符

SELECT 列1，列2，列3 FROM 表 WHERE 列1 = 520 OR 列2 > 999

7.3 AND 与 OR 的优先级及组合用法

SELECT 列1，列2，列3 FROM 表 WHERE 列1 = 520 OR ※列3 = 520 AND 列2 > 999 ：这样就先处理了※后面的东西 因为and优先级更高

SELECT 列1，列2，列3 FROM 表 WHERE （列1 = 520 OR 列3 = 520） AND 列2 > 999 ：这样就先处理了括号里面的内容

7.4 IN 操作符

SELECT 列1，列2 FROM 表 WHERE vend_id IN （a，b） ORDER BY 列1 : 查询列1列2所对应的行中 vend_id 为a 或者为b的列并以列1的字母排序以返回。

7.5 NOT 操作符（否定where后的条件 达到除了。。。以外的效果）

SELECT 列1，列2 FROM 表 WHERE vend_id  NOT IN （a，b） ORDER BY 列1：查询列1列2所对应的行中 除了vend_id 为a 或者为b的所有列并以列1的字母排序以返回。

 

8.1 通配符之like操作符及百分号 %通配符

SELECT 列1，列2 FROM 表 WHERE 列2 LIKE '%jet%' ：查询列2中任意数量字符后含jet的所有字符

SELECT 列1，列2 FROM 表 WHERE 列2 LIKE '_jet' ：查询列2中单个字符后含jet的所有字符

 

10.1 计算字段 以执行算术运算

SELECT 列1，列2，列3，列2*列3 AS 商品总价 FROM 表 WHERE 字符=20005

例如教材page66 orders表收到的所有订单 orderitems表每个订单中的各项商品

SELECT prod_id,quantity,item_price FROM oderitems WHERE order_num=20005

10.1.2 常见算术操作符

+ - * / 加 减 乘 除

10.2 拼接字段 CONCAT

SELECT CONCAT (列1,空格'(',列2,')') FROM 表1 ORDER BY 列1 ：返回的列中值格式就是'商品（国家）'即'列1（列2）' 列名为 CONCAT (列1,空格'(',列2,')')

10.2.2 RTRIM 去掉值右边的所有空格以整理各个列 LTRIM 去掉左边 TRIM 去掉左右两边的空格

SELECT CONCAT (RTRIM 列1,空格'(',RTRIM 列2,')') FROM 表1 ORDER BY 列1

10.2.3 使用 AS 以用别名替换字段或值

SELECT CONCAT (列1,空格'(',列2,')') AS 商品（国家） FROM 表1 ORDER BY 列1，则返回的列名为商品（国家）

 

11.1 文本处理函数

11.1.1 文本转换大写 UPPER （）

SELECT 列1， UPPER （列1），列3，列1*列3 AS 商品总价 FROM 表 :列1 返回文本全部大写

11.1.2 返回子串的字符 SUBSTRING()

11.1.3 返回串的soundex值 SOUNDEX （）

SELECT 列1， 列2， FROM 表 WHERE soundex（列1）= SOUNDEX （'王潇'） ：找出列1 和王潇同发音的字符

11.1.4 去掉右边的空格 RTRIM （）

11.2 使用日期和时间处理函数 -- （暑假学）

11.3 数据处理函数

ABS （） COS （） EXP （） MOD （） PI （） RAND （） SIN （） SQRT （） TAN （）

返回一个数的绝对值 角度的余弦 数的指数值 除操作的余数 圆周率 随机数 角度的正弦 平方根 角度的正切

 

12.1 聚集函数 运行在行组上 以计算和返回单个值的函数

AVG () 返回某列的平均值

COUNT () 返回某列的行数

MAX () 返回某列的最大值

MIN () 返回某列的最小值

SUM () 返回某列值之和

 

12.1

SELECT AVG (prod_price) AS avg_price FROM products

SELECT AVG (prod_price) AS avg_price FROM products WHERE vend_id=10086 确定特定列或行的平均值

12.2

a.使用 COUNT (*)函数 对表中行的数目进行计数，不管是否空值 NULL

b.使用 COUNT (COLUMN)对特定列中的值进行计数 忽略 NULL

a.select COUNT(*) AS num_cust FROM customers;

b.select COUNT(cust_email) AS num_cust FROM customers;

12.3

max忽略 NULL 行

SELECT MAX(prod_price) AS max_price FROM products

12.4

SELECT MIN(prod_price) AS min_price FROM products

12.5

SELECT SUM(prod_price) AS items_ordered FROM orderitems WHERE order_num=20005

也可合计计算值

SELECT SUM(quantity*item_price) AS total_price FROM orderitems WHERE order_num=20005

12.6以上所有的五个聚集函数都可使用 ALL 参数（默认）来指定所有行 或者 DISTINCT 不同的值

12.7 组合聚集函数

SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min,MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROM products;

 

13 分组数据

13.1 数据的分组：分组允许将数据分为多个逻辑组，以更能对每个组进行聚集计算

13.2 创建分组 GROUP BY : SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id

其中 有两个列 ：vend_id 及 num_prods(这个列实质是count(*)返回的行 只是用as把列改名了)，用 GROUP BY 返回分

组后的 vend_id，配合分组后的 vend_id对应的count(*) 返回值即(num_prods) 来返回两个列 只不过分组且对应了。

13.3 GROUP BY 说明：

1.哪怕嵌套了分组，最后也会将数据按照规定分类汇总。即建立分组时，指定的所有的列都必须一起计算（所以不能从个别的列取回数据）

2. GROUP BY 子句中列出的每个列必须是检索列或有效的列，不可是聚集函数，如果在select中用表达式，则group by的子句中也必须用表达式，不可用别名

3.关于null：若有null则会单独分组，多个null也会分为一组

4. GROUP BY 必须在 WHERE 后 ORDER BY 前

13.4 过滤分组

WHERE 过滤指定的是行 不是分组 关于分组必须用 HAVING 其中 HAVING 包括 WHERE 所有功能 区别只是过滤行和组

SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2:过滤两个及以上的订单

WHERE 与 HAVING 的组合：

查询过去12个月有2单以上的顾客，使用 WHERE 过滤12月内下过的订单 HAVING 过滤订单

例: WHERE 过滤prod_price至少为10的行，然后按vend_id分组,HAVING 过滤计数为大于等于2的分组

SELECT cust_id,COUNT(*) AS orders FROM orders WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2

13.5分组和排序

ORDER BY 1排序产生的输出 2不管选没选择 任何列可使用 3不一定需要

GROUP BY 1分组行，但输出可能不是分组的顺序 2只可能使用选择列和表达式列 而且必须使用每个选择列表达式 3如果与聚集函数一起使用列或表达式，则必须使用

2者组合使用的实例：

SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal

GROUP BY 子句用以按订单号order_num列分组数据 便于 SUM (*)函数能够返回总计订单价格，having 子句 用以过滤数据，用以只返回总计价格订单大于等于50的订单

，最后 ORDER BY 排序

 

总结：select子句排序

SELECT 返回的列或表达式 必须使用

FROM 从中检索数据的表 仅在从表中选择数据时使用

WHERE 行级过滤 不必须使用

HAVING 组级过滤 不必须使用

GROUP BY 分组说明 尽在按组计算聚集时使用

ORDER BY 输出排序顺序 不必须使用

LIMIT 要检索的函数 不必须使用

 

14.1 使用子查询 就是select中的select无限嵌套

SELECT cust_name,cust_contact FROM customers WHERE cust_id IN(

SELECT cust_id FROM orders WHERE order_num IN (

SELECT order_num FROM orderitems WHERE prod_id='tnt2' ));

先执行tnt那个语句 从内而外 执行

 

14.2 使用子查询的另一个方法 创建计算字段

假如需要显示customers表中每一个客户的订单总数，订单与相应的客户id存储在orders表中

所以需要1.从customers表中检索客户列表 2.对于检索出的每个用户，统计其在orders表中的订单数目

可用select(*)来对表中的所有行进行计数，并且通过提供一条where子句来过滤某个特定的客户id，可仅对该客户的id进行计数

对每个客户进行count(*)计算

SELECT COUNT(*) AS orders FROM orders WHERE cust_id=10001;

为了对每个客户进行count(*)计算，应该将count作为一个子查询

SELECT cust_name.cust_state,(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_od)AS orders FROM customers

ORDER BY cust_name（其中，必须完全限定列名）

这条select语句对customers表中的每个客户返回三列：cust_name.cust_state.orders这三个，orders是一个计算字段，它是由圆括号中的子查询建立的。该子查询对

检索出的每一个客户执行一次，在此例子中，共执行了次（因为检索结果是个5个用户）

 

15 联结表

15.1创建联结:规定要联结的表以及他们如何关联

SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id=products.vend_id ORDER BY vend_name,prod_name;需要完全限定列名(联结条件),如果不限定列名

就会造成笛卡尔积:返回的结果为笛卡尔积即返回的行数为表1的行数乘以表二的行数.

15.2内部联结:等值联结

SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id

15.3联结多个表:列出所有表,然后定义表之间的关系

SELECT 列1,列2,列3,列4 FROM 表1,表2,表3 WHERE 表2.vend_id=表3.vend_id AND 表1.prod_id=表2.prod_id AND order_num=20005;

15.4 引用14.1 嵌套子查询这一复杂方法和使用多个联结的方法之比较

-引用-14.1 使用子查询 就是select中的select无限嵌套

SELECT cust_name,cust_contact FROM customers WHERE cust_id IN(

SELECT cust_id FROM orders WHERE order_num IN (

SELECT order_num FROM orderitems WHERE prod_id='tnt2' ));

先执行tnt那个语句 从内而外 执行

在15.3中,可以这样写

SELECT cust_name,cust_contact

FROM customers,orders,orderitems

WHERE customers.cust_id=orders.cust_id

AND orderitems.order_num=orders.order_num

AND prod_id='tnt2';

 

16 创建高级联结

16.1 使用表别名

-引用第10章-

SELECT CONCAT (RTRIM (列1),空格'(',RTRIM (列2),')') AS bieming FROM 表1 ORDER BY 列1

-引用15章例子以进行别名例子改造-

SELECT cust_name,cust_contact

FROM customers AS c,orders AS o,orderitems AS oi

WHERE c.cust_id=o.cust_id

AND oi.order_num=o.order_num

AND prod_id='tnt2';

 

16.2使用不同类型的联结

16.2.1自联结

比较子查询与自联结

子查询:SELECT prod_id,prod_name FROM products WHERE vend_id=(SELECT vend_id FROM products WHERE prod_id='dtntr')

自联结:SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id=p2.vend_id AND p2.prod_id='dtntr';

 

16.2.2 自然联结:无论何时对表进行联结,应该至少有一个列出现在不止一个表中(被联结的列).标准的联结(前一章介绍的内部联结)返回所有的数据,甚至相同的列多次出现.

而我们的自然联结,就是用来排除多次出现这一情况的,使每一个列只返回一次. 使用通配符(SELECT*)来完成这一工作以对其他所有的表的列明确的子集来完成的.

SELECT c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price

FROM customers AS c,orders AS o,orderitems AS oi WHERE c.cust_id=o.cust_id AND oi.order_num=o.order_num AND prod_id='fb':在这个例子中,通配符只对第一个表使用,

所有其他列明确列出,所以没有重复的列被检索出来.

 

16.2.3 外部联结

许多联结将一个表中的行与另一个表中的行相关联.但有时候会需要包含没有关联行的那些行(such AS : 对每个客户下单数量进行统计,包括没下单的用户.联结包含

了那些在表中没有关联行的行,这种类型的联结被称之为外部联结)

SELECT customers.cust_id,orders.order_num FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id:INNER JOIN ON 型 检索所有客户及其下单情况.

SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id:LEFT OUTER JOIN ON 型 检索所有客户

包括没下单的 其中,LEFT 指的是 OUTER JOIN 左边的表,还可以用 RIGHT 用以指 OUTER JOIN 右边的表

如为了从右边的表选择所有行,应该使用 RIGHT OUTER JOIN 如:

SELECT customers.cust_id,orders.order_num FROM customers RIGHT OUTER JOIN orders ON orders.cust_id=customers.cust_id

 

16.3 使用带聚集函数的联结

聚集函数在12章的讲解中,它是用来汇总数据的,但聚集函数也可以跨表以联结一起使用汇总.

如果要检索所有客户和每个客户所下的订单数:

SELECT customers.cust_name,customers.cust_id,

COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders

ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id:

这个 SELECT 语句用 INNER JOIN ON 将customers和orders表相关联,GROUP BY 子句按客户分组数据

因此,函数调用count(orders.order_num)对每个客户的订单进行计数,将它作为num_ord来返回

SELECT customers.cust_name,customers.cust_id,

COUNT(orders.order_num) AS num_ord FROM customers LEFT OUTER JOIN orders

ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id:

使用左外部联结以查询所有客户包含没有下单的所有客户,结果也显示了 mouse house,其0个订单

 

-- 2021/6/17 START

17组合查询

多数sql查询只包含从一个或多个表返回的数据的单挑select语句,MySQL允许多个查询(多条select语句),并将结果作为单个结果集返回,

这些组合查询被称为并(UNION)或复合查询(compound QUERY)

用于:1单个查询中从不同的表返回类似结构的数据  2对单个表执行多个查询,返回单个查询数据

1组合查询和2多个where子句条件的单条查询完成的工作相同

组合查询用法:1各条语句前 放上union 2 4条select 则3个union 3union每个查询必须包括相同的列,表达式和聚集函数,只是次序可以不一样 4 列数据类型必须完全兼容:类

型不必完全相同,但必须是 dbms 可以隐含地转换的类型(例如不同的数值类型或不同的日期类型) 5 union默认去重,如果不需要去重,则 UNION ALL 6 不允许使用多个 ORDER by语句

SELECT vend_id,prod_id,prod_price

FROM products WHERE prod_price <=5

UNION

SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1002,1003);

多个where子句条件的单条查询:

SELECT vend_id,prod_id,prod_price

FROM products WHERE prod_price <=5

OR

vend_id IN (1002,1003);

 

-- 2021/6/17 END

-- 2021/6/18 START

 

 

18 全文本搜索

18.1 启动全文本搜索支持

再创建表 CREATE TABLE 时 在最后一列 括号)的前面写一个 FULLTEXT(需要全文本搜索的列)

18.2.1进行全文本搜索

MATCH(指定被搜索的列)AGAINST(指定要使用的搜索表达式) 指定多列则列出他们

SELECT 列1 FROM 表1 WHERE MATCH(列1) AGAINST('某文本如rabbit')

18.2.1也可用 LIKE 子句:

SELECT 列1 FROM 表1 WHERE 列1 LIKE '%rabbit%'

18.2.2

如果想搜索不止某文本如'rabbit'的句子,还想搜索关于他的补充 在 AGAINST(... WITH QUERY EXPANSION)则okay了

18.2.3 布尔文本搜索p128 p129

 

19 插入数据(增)

19.1 数据插入:插入完整的行,插入行的一部分,插入多行,插入某些查询结果.

添加新的客户到customers表:

INSERT INTO customers VALUES(NULL,'信息1','信息2','信息3','信息1','信息4',NULL,NULL):1如果某列没有值,则 NULL;2存储到每个表列中的数据需要

在 VALUES 子句中给出,每个列需要提供一个值

另外的:

INSERT INTO customers (列1,列2,列3,列4,列5) VALUES(NULL,'信息1','信息2','信息3','信息1','信息4')

插入多行:

INSERT INTO customers (列1,列2,列3,列4,列5) VALUES(NULL,'信息1','信息2','信息3','信息1','信息4');INSERT INTO customers (列1,列2,列3,列4,列5) 

VALUES(NULL,'信息5','信息6','信息7','信息8','信息9');

简单插入多行法:

INSERT INTO customers (列1,列2,列3,列4,列5) VALUES(NULL,'信息1','信息2','信息3','信息1','信息4'),(NULL,'信息5','信息6','信息7','信息8','信息9');

插入检索出来的数据:

INSERT INTO customers (列1,列2,列3,列4,列5) SELECT 列1,列2,列3,列4,列5 FROM custnew;插入多少行取决于custonew有多少行

插入检索出来的数据 使用 INSERT SELECT 语句中可以包含含有 WHERE 的子句以过滤插入的数据

 

20 更新与删除数据(改与删)

更:UPDATE :更新表中的特定行或所有行 特定行利用 WHERE 实现

更用法:要更新的表,列名和它的新值,确定要更新行的过滤条件

UPDATE 表 SET cust_Email = 'elmer@qqqq.com' WHERE cust_id = '10005':SET 子句设置cust_email列为指定的值 WHERE 子句设置过滤的特定行 如果没有则更新所有行

更新多个:UPDATE 表 SET cust_Email = 'elmer@qqqq.com',cust_name='wang' WHERE cust_id = '10005':更新多个行也只需要一个 set命

令,每个列=值对之间用逗号隔离,最后一列不需要

如果出错 不能更新 则:

IGNORE 关键字 UPDATE IGNORE 表:可继续更新,即使发生错误

 

删除数据:

方法一: 更新列值为 NULL ,UPDATE 表 SET 列=NULL WHERE 列2=1005

方法二:DELETE :DELETE FROM 表 WHERE 行=10006:删除整行

 

21 创建表:

CREATE TABLE customers(

cust_id INT NOT NULL AUTO_INCREMENT,

cust_name CHAR(50) NOT NULL,

cust_address CHAR(50) NOT NULL,

cust_city CHAR(50) NOT NULL,

cust_state CHAR(5) NOT NULL,

cust_zip CHAR(10) NOT NULL,

cust_country CHAR(50) NOT NULL,

cust_contact CHAR(50) NOT NULL,

cust_email CHAR(255) NOT NULL,

PRIMARY KEY (cust_id)

)ENGINE=INNODB:指定cust_id为主键 ' 空格 '也可插入 NOT NULL,null指的是没有值的列

 

何为 AUTO_INCREMENT ?

自动增量:只允许一个auto列,而且必须使它被索引(如设置它为主键)

每增加一行时自动增量,每次执行 INSERT 操作时 给下一列赋予可操作的值 从而给每行分配一个唯一的cust_id,从而用作主键.

增加列的时候,订单编号:SELECT LAST_INSERT_ID 返回最后一个 AUTO_INCREMENT 值.

 

DEFAULT 如果有 NOT NULL 出现 但又没有值 默认填入 DEFAULT 常量,则没有数据的会默认显示常量,但不可用函数,DEFAULT 只支持常量

 

关于主键及多列组合为主键:

主键值必须唯一,表中的每一行必须有唯一的主键值,如果主键使用单个列,则它的值必须唯一,如果使用多个值,则他们的组合值必须唯一

CREATE TABLE orderitems(

order_num INT NOT NULL,

order_item INT NOT NULL,

prod_id CHAR(10) NOT NULL,

quantity INT NOT NULL,

item_price DECIMAL(8,2) NOT NULL,

PRIMARY KEY (order_num,order_item)

)ENGINE=INNODB;

每个订单包含多个商品,但每个订单任何时候都只有1个第一项物品,1个第二项物品,如此等等,因此,订单号(order_num列)和订单物品(order_item列)的组合是唯一

的,所以可以作为主键.

 

 

引擎:

-- INNODB 事务处理引擎 不支持全文本搜索 可靠

-- MYISAM 不支持事物处理 支持全文本搜索 性能极高

-- MEMORY 数据存储在内存而不是磁盘 速度很快 适合临时表.

 

关于表的增删改

ALTER TABLE 之后给出要更改的表名 表必须存在

ALTER TABLE  vendors ADD vend_phone CHAR(20);

 

删除刚刚添加的列:

ALTER TABLE vendors DROP COLUMN vend_phone;

 

ALTER TABLE 定义外键

ALTER TABLE orderitems ADD CONSTRAINT fk_oderitems_orders FOREIGN KEY (order_num) REFERENCES orders (order_num)

 

删除表

DROP TABLE 表

 

重命名表:

RENAME TABLE 旧表1 TO 新表1,旧表2 TO 新表2;

 

 

-- 2021/6/18 END