简介
面向SQL入门者,快速掌握SQL语句
基本
查询
SELECT
*
FROM
table_name;
'*': 所有列
新增
INSERT
INTO
table_name
VALUES
(1, 'A', 'aa', '2017-09-22 00:00:00');
'VALUES'后面括号里的字段,需要对应表定义的列,从左到右依次填入
更新
UPDATE
table_name
SET
col_name1 = value1,
col_name2 = value2
WHERE
id = 1
'WHERE‘后面跟着指定更新条目所需的条件。
删除
DELETE
FROM
table_name
WHERE
条件
查询细化
AND (且)
SELECT
*
FROM
table_name
WHERE
条件
AND 条件
OR (或)
SELECT
*
FROM
table_name
WHERE
条件
OR 条件
比较计算
运算符 |
说明 |
= |
左右相等 |
< |
左边小于右边 |
> |
左边大于右边 |
<= |
左边小于等于右边 |
>= |
左边小于等于右边 |
<> |
左右不相等 |
SELECT
*
FROM
table_name
WHERE
价格 >= 1000
NULL判断
SELECT
*
FROM
table_name
WHERE
col_name IS NULL
SELECT
*
FROM
table_name
WHERE
col_name IS NOT NULL
LIKE
匹配符号 |
说明 |
% |
任意文字组成的任意长度的字符串 |
_ |
任意的1个文字 |
- 从备忘列的值里,选出满足’1月‘前后任意文字长度大于0的任意长度的条目。
SELECT
*
FROM
table_name
WHERE
memo LIKE '%1月%'
- 从备忘列的值里,选出’1月’为文字开始,其后只跟着一个文字的条目
SELECT
*
FROM
table_name
WHERE
memo LIKE '1月_'
BETWEEN
SELECT
*
FROM
table_name
WHERE
价格 BETWEEN 50 AND 100;
IN/NOT IN
- 匹配出指定字段符合给定集合里的的任意一个值的所有条目
SELECT
*
FROM
table_name
WHERE
col_name IN (
value1, value2,value3, ...
);
- 匹配出指定字段不符合给定集合里的的任意一个值的所有条目
SELECT
*
FROM
table_name
WHERE
col_name NOT IN (
value1, value2, value3, ...
);
ANY/ALL
- 指定字段与给定的值列表比对,筛选出只要其中一个为真就为真的所有条目
SELECT
*
FROM
table_name
WHERE
col_name =< ANY(
value1, value2, value3
);
- 指定字段与给定的值列表比对,筛选出所有值比对都为真的所有条目
SELECT
*
FROM
table_name
WHERE
col_name =< ALL(
value1, value2, value3, ...
);
查询结果加工
DISITINCT
SELECT
DISITINCT user_name
FROM
table_name;
ORDER BY
SELECT
*
FROM
table_name
ORDER BY
价格 DESC;
升序-> ASC, 降序-> DESC
LIMIT + (OFFSET)
SELECT
*
FROM
table_name
ORDER BY
价格 DESC LIMIT 3 OFFSET 2;
UNION
SELECT
user_name,
age,
address
FROM
table_1
UNION
SELECT
user_name,
age,
address
FROM
table_2;
EXCEPT (mysql不支持)
SELECT
user_name,
age,
address
FROM
table_1
EXCEPT
SELECT
user_name,
age,
address
FROM
table_2;
INTERSECT (mysql不支持)
SELECT
user_name,
age,
address
FROM
table_1
INTERSECT
SELECT
user_name,
age,
address
FROM
table_2;
操作符与函数
代表性的操作符
操作符 |
使用方法 |
说明 |
+ |
数值 + 数值 |
数值相加 |
|
日期+数值 |
日期加天数 |
– |
数值 – 数值 |
数值相减 |
|
日期 – 数值 |
日期减天数 |
|
日期 – 日期 |
两日期相差天数 |
* |
数值 * 数值 |
数值相乘 |
/ |
数值 / 数值 |
数值相除 |
CASE
SELECT
user_name,
CASE age
WHEN age < 20 THEN '未成年'
WHEN age >= 20
AND age <= 65 THEN '成年'
ELSE '高龄'
END AS 分类
FROM
table_name;
LENGTH
SELECT
user_name,
LENGTH(user_name) as '名字长度'
FROM
table_name;
TRIM
SELECT
user_name,
TRIM(user_name) as '去左右空白后名字'
FROM
table_name;
REPLACE
UPDATE
table_name
SET
user_name = REPLACE(user_name,
'李',
'张');
将用户名里带’李‘的转为’张‘
SUBSTRING
SELECT
*
FROM
table_name
WHERE
SUBSTRING(user_name, 1, 3) LIKE '%德%'
ROUND
SELECT
支出金额, ROUND(支出金额, -2) AS '以百计支出金额'
FROM
table_name;
SELECT 12345.67, ROUND(12345.67, -1) => '12345.67', '12350'
TRUNC(mysql命令为TRUNCATE)
SELECT
支出金额, TRUNCATE(支出金额, -2) AS '以百计支出金额'
FROM
table_name;
SELECT 12345.67, TRUNCATE(12345.67, -1) => '12345.67', '12340'
POWER
SELECT
支出金额,
POWER(支出金额,
2) AS '指数乘后的支出金额'
FROM
table_name;
汇总和GROUP化
分类 |
方法名 |
说明 |
汇总 |
SUM |
各行的值合计 |
|
MAX |
各行的值取最大值 |
|
MIN |
各行的值取是小值 |
|
AVG |
各行的值取平均值 |
计数 |
COUNT |
行数计算 |
SELECT
SUM(支出金额) AS '支出金额合计'
from
table_name;
SELECT
COUNT(*) AS '合计行数'
FROM
table_name;
COUNT(*) NULL行也会计算
GROUP化
SELECT
支出项目,
SUM(支出金额) AS '以支出项目合计金额'
FROM
table_name
GROUP BY
支出项目;
子查询
SELECT
MAX(以支出项目合计金额)
from
(SELECT
支出项目,
SUM(支出金额) AS '以支出项目合计金额'
FROM
table_name
GROUP BY
支出项目);
表结合
SELECT
to.id,
to.price,
tu.user_name,
tu.address
FROM
table_order AS to
JOIN
table_user AS tu
ON to.user_id = tu.id;