mySql学习笔记（二）

1. 使用各种条件提取数据

1. 1基本函数

-- 平均值
SELECT AVG(age) FROM tb1

-- SUM总和
SELECT SUM(age) FROM tb1

-- COUNT计数
SELECT COUNT(0) FROM tb1

-- CONCAT连接字符串
SELECT CONCAT(empid,name,'先生') FROM tb1

-- 从右取出：RIGHT(str,len)函数
SELECT RIGHT(empid,3) FROM tb1

-- 从左取出：LEFT(str,len)函数
SELECT LEFT(empid,3) FROM tb1

-- SUBSTRING从列empid的第2个字符开始连续显示3个字符
SELECT SUBSTRING(empid,2,3) FROM tb1

-- REPEAT重复显示字符“.”，其重复次数为列age的值
SELECT REPEAT('.',age) FROM tb1;

-- REVERSE(str) 反转
SELECT REVERSE(empid) FROM tb1

1.2 条件提取(WHERE)

-- LIMIT 选取第一条显示
SELECT * FROM tb1 LIMIT 1

-- WHERE 显示满足条件的数据
SELECT * FROM tb1 WHERE age>18

1.3 比较运算符

 1.4 模糊查询(LIKE)

1.5 使用NULL作为条件

-- 查询empid为NULL数据
SELECT * FROM tb1 WHERE empid IS NULL

-- 查询empid不为NULL数据
SELECT * FROM tb1 WHERE empid IS NOT NULL

1.6 消除重复数据(DISTINCT)

SELECT DISTINCT empid FROM tb1

1.7 使用AND

SELECT * FROM tb1 WHERE empid= 'A101' AND age>18

1.8 使用OR

SELECT * FROM tb1 WHERE empid= 'A101' or age=18

1.9 使用CASE WHEN

SELECT empid,
CASE 
	WHEN age <18 THEN '未成年'
	WHEN age >=18 THEN '已成年'
	ELSE
		'未知'
END
FROM tb1

1.10 排序(ORDER BY)

-- 根据age升序（默认）排列
SELECT * FROM tb1 ORDER BY age ASC

-- 根据age降序排列
SELECT * FROM tb1 ORDER BY age DESC

1.11 指定记录的显示范围(LIMIT 1 OFFSET 1)

-- OFFSET 1表示从第二条数据开始提取，LIMIT 1表示提取一条数据
SELECT * FROM tb1 LIMIT 1 OFFSET 1

1.12 分组显示(GROUP BY)

-- 分组显示
SELECT empid FROM tb1 GROUP BY empid

-- 计算各组记录数
SELECT empid, COUNT(*) FROM tb1 GROUP BY empid

2.编辑数据

2.1 更新数据(UPDATE)

-- 修改数据
UPDATE tb1 SET remark='备注' 

2.2 复制数据(INSERT INTO)

INSERT INTO tb1 SELECT * FROM tb1a

2.3 删除数据(DELETE)

DELETE FROM tb1 WHERE age<16

3.使用多个表

3.1 表的数据合并(UNION)

-- 不允许重复
SELECT empid FROM tb1
UNION
SELECT empid FROM tb1a

-- 允许重复
SELECT empid FROM tb1
UNION ALL
SELECT empid FROM tb1a

3.2 内连接（INNER JOIN）

定义：将多个表通过连接键连接在一起，内连接仅显示相匹配的数据

-- 内连接
SELECT * FROM tb1 a
INNER JOIN
tb1a b
ON a.empid = b.empid

-- 如果连接键名称相同，可用USING简写
SELECT * FROM tb1 a
INNER JOIN
tb1a b
USING(empid)

3.3 外连接（LEFT/RIGHT JOIN）

定义：显示主表的所有数据和连接表与主表相匹配的数据；

注：避免左、右外连接混合使用（有可能发生错误）；

-- 左外连接
SELECT * FROM tb1 a
LEFT JOIN
tb1a b
ON a.empid = b.empid

-- 右外连接
SELECT * FROM tb1 a
RIGHT JOIN
tb1a b
ON a.empid = b.empid

3.4 子查询

定义：使用子查询可以完成两个阶段的处理：执行查询，然后使用检索到的记录进一步执行查询；

SELECT * FROM tb1 
WHERE age in (SELECT MAX(age) FROM tb1)

3.5 使用EXISTS

特点：仅以存在的记录为对象

-- 返回子查询中存在的数据
SELECT * FROM tb1 
WHERE EXISTS 
 (SELECT * FROM tb1a 
 WHERE tb1a.empid = tb1.empid)

-- 返回子查询中不存在的数据
SELECT * FROM tb1 
WHERE NOT EXISTS
 (SELECT * FROM tb1a 
 WHERE tb1a.empid = tb1.empid)