/ mysql

mysql-basic

参考书目:《MySQL与MariaDB学习指南》
作者的网站:mysqlresources.com 已经挂了

数据处理基础

  • 插入数据

    • 插入数据的优先级
  • 查询数据

    • 有条件地查询,WHERE
    • 临时表进行排序,ORDER BY column
    • 限定结果集数量,LIMIT start,num
    • 结果集进行计数和分组
    • 表连接
  • 备份数据(更新和删除必需)

  • 更新数据

    • 更新指定行
    • 限定行数更新
    • 排序后再按行数更新
    • 同时更新多个表
  • 删除数据

  • 表连接和子查询

    • 表连接
      • 更新已连接的表
      • 从已连接的表中删除数据,DELETE FROM a,b USING a LEFT JOIN b ON(a.aid = b.aid) WHERE a.status=0;
    • 子查询

1 插入数据

1.1 插入数据的优先级

在一台繁忙的 MySQL 服务器上,可能经常会发生多人同时访问的情况。于是就会有来自不同客户端的 SQL 语句被同时输入。这使得服务器需要判断应该让那个先执行。

更改数据(INSERT、UPDATE 和 DELETE),会比查询语句(SELECT)具有更高的优先级。添加数据的人应该比读取数据的人更重要,因为考虑到插入数据可能占用较长的时间,在这期间客户端做不了其他事情。而相反,查询数据的人一般都愿意等待。就像在购物网站中,下单的用户会比浏览产品的用户,具有更高的优先级。

当 MyISAM 表执行一个 INSERT 语句时,它会锁住相关的表,以排斥其他客户端的访问,以阻止其他的并发插入,直到它执行完毕。对于一个有大量并发数据请求的繁忙服务器来说,锁表会导致其他用户延迟,尤其是在有人使用多行语法来插入大量数据的时候。

不过 InnoDB 倒不是这样:它只锁住行,而不是整个表。

2 查询数据

对临时表进行排序

默认情况下所得到的行的排序方式取决于它们在表中被找到的顺序,通常会如同插入时的顺序。如果需要排序可以使用 ORDER BY

SELECT common_name, scientific_name FROM birds
WHERE family_id = 103
ORDER BY common_name
LIMIT 3;

排序是在临时表中进行的,所以 ORDER BY 是放在 WHERE 之后、 LIMIT 之前的。

  1. MySQL 首先按 WHERE 子句获取所有行,并在幕后将此结果集存放在一个临时表中
  2. 然后根据 ORDER BY 子句对临时表排序;
  3. 最后根据 LIMIT 子句获取排序后的前 3 行。

这也正是这三个子句顺序的内在原因。

**注意:**在 ORDER BY 中不能使用列的别名,但表的别名则可以。事实上,如果在 FROM 中指定了表的别名,那么在 ORDER BY 中就必须使用这个别名。

对结果集进行计数和分组

  • 计数:
    • COUNT(*) ,统计所有行数
    • COUNT(列名),只统计有值的行,换句话说忽略列中是 NULL 的行。
  • 分组:GROUP BY 列名

统计数据表的行数:

SELECT COUNT(*) FROM birds;

分组显示统计结果(内部逻辑是,先分组,再统计):

SELECT 字段1,字段2,COUNT(*) AS '计数' FROM 表1,表2
WHERE 条件1 AND 条件2
GROUP BY 字段1

表连接

把拆分的表连接起来查询,大大优于建立一个含有所有列的大表。连接表有两种方法:

  1. 使用 WHERE 子句,缺点是将 筛选调教连接条件 写在一起,不易于分辨 筛选调教
  2. 使用 JOIN ON(a.x=b.y)JOIN USING(z) 子句,将 筛选调教连接条件 分开写,易于区分 筛选调教连接条件

使用 WHERE 子句将 3 张表串起来。

SELECT common_name AS 'Bird', 
bird_families.scientific_name AS 'Family'
orders.scientific_name AS 'Order'
FROM birds, bird_families AS families, bird_orders AS orders
WHERE birds.family_id = bird_families.family_id
AND families.order = orders.order_id
AND families.order_id = 102
AND common_name != ''
ORDER BY common_name 
LIMIT 10

备份数据

在执行 UPDATE 或 DELETE 之前,最好先对要操作的数据表进行备份。

更新数据

排序后再按行数进行更新

随机排序,更新前 2 行:

UPDATE prize_winners
SET winner_date = CURDATE()
WHERE winner_date IS NULL
ORDER BY RAND()
LIMIT 2;

RAND() 在这里的作用是,给符合 WHERE 条件的每一行都分配一个随机的浮点数。然后,再排序。

同时更新多个表

在使用 UPDATE 的多表语法时,不能带有 ORDERLIMIT,但是在 UPDATE 单表时就可以。所以,我们可以通过子查询来实现类似的需求:

抽奖,名额 2 个,筛选条件为没有中过奖的英国用户:

UPDATE prize_winners, humans
SET winner_date = CURDATE()
WHERE winner_date IS NULL
AND country_id = 'uk'
AND prize_winners.human_id = humans.human_id
ORDER BY RAND()
LIMIT 2

更改为:

UPDATE prize_winners
SET winner_date = CURDATE()
WHERE winner_date IS NULL
	AND prize_winners.human_id IN (
		SELECT human_id
		FROM humans
		WHERE country_id = 'uk'
		ORDER BY RAND()
	)
LIMIT 2

删除数据

通常你不应该删除数据,而应该使用 delete flag 来标记无效的数据。

如果确实需要删除,请先执行备份操作,再确保你的 DELETE 语句必须有 WHERE 子句

复制表:

CREATE TALBE birds_details
select bird_id, description
from birds;

连接表和子查询

连接表

LEFT JOIN ,查出左表的所有行,而不管其在右表有没有对应的行。如果没有对应的行,则会显示 NULL。

更新已连接的表

如果想用 UPDATE 一次更新多个表,或者,想以其他表来限制某个表更新哪些行,可以使用 JOIN 子句。在 UPDATE 中使用 JOIN 子句与在 SELECT 中相同。

从已连接的表中删除数据

在 UPDATE 中使用 JOIN 子句与在 SELECT 中并不相同。注意,这里使用的是 USING ... JOIN ,而不是 JOIN ... USING,要区分开来。

USING 子句列出要在 WHERE 字句中用于指定筛选条件的表。

实例:删除 birdwachers 库的 humans 表和 prize_winners 表中,拥有 yahoo.com 邮箱的会员 Elena Bokova。

DELETE FROM humans, prize_winners
USING humans
	LEFT JOIN prize_winners ON humans.human_id = prize_winners.human_id
WHERE name_first = 'Elena'
	AND name_last = 'Bokova'
	AND email_address LIKE '%yahoo.com';

虽然与之前的那些 JOIN 不太一样,但,这就是 JOIN 在 DELETE 中的写法:

  • FROM 子句需要列出需要删除数据的表,如果只想删除 prize_winners 的数据,则为 DELETE FROM prize_winners
  • USING 子句列出要在 WHERE 子句中用于指定筛选条件的表。USING humans LEFT JOIN prize_winners 的意思是,在 WHERE 子句中会使用 humans 和 prize_winners 这两个表的列,来筛选哪些行会被删除。
  • 这里使用 LEFT JOIN 是为了确保一定会删除 humans 中符合条件的行,如果在 prize_winners 中有匹配的行同时删除。