MySQL - 常用语法及函数

1
2
3

> INSERT INTO <TABLE_NAME>(id, FIELD1, FIELD2, ...) VALUES(NULL, VALUE1, VALUE2, ...), (NULL, VALUE1, VALUE2, ...), ...;
-- 插入多条数据
-- 主键ID为自增字段，直接NULL即可

1
2

> INSERT INTO <TABLE_NAME_1>(FIELD3, FIELD4, ...) SELECT FIELD1, FIELD2, ... FROM <TABLE_NAME_2>;
-- 从 <数据表2> 查询 <字段名1> <字段名2> <...> 的值插入 <数据表1> 的 <字段名3> <字段名4> <...>

1
2

> UPDATE <TABLE_NAME> SET FIELD=VALUE [WHERE CONDITIONS];
-- 不加条件则更新整个数据表数据

1
2
3
4

> DELETE FROM <TABLE_NAME> [WHERE CONDITIONS];
-- 不加条件则删除整张表的数据，但自增字段不会重新开始
> TRUNCATE TABLE <TABLE_NAME>;
-- 清空数据表，自增字段重新从1开始

1
2

> SELECT <FIELD> FROM <TABLE_NAME>;
> SELECT * FROM <TABLE_NAME>;

1
2

> SELECT * FROM <TABLE_NAME> WHERE id=3;
-- 从数据表查询id=3的所有字段数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

> SELECT * FROM <TABLE_NAME> GROUP BY <FIELD> HAVING <CONDITIONS>;
-- 根据 <字段名> 分组并筛选出达到 <条件> 的组

-- 举个例子：有数据表user
+----------------------+
|user_name| dept| score|
+----------------------+
|zhangsan |sales|    90|
|lisi     |sales|    90|
|wangfei  |IT   |    89|
|leifeng  |IT   |    95|
|gouzi    |hr   |    92|
+----------------------+
> SELECT dept, COUNT(user_name) count_tmp FROM user WHERE score>89 GROUP BY dept HAVING count_tmp>1;
-- 先查出 score>89 的员工纪录
-- 根据 dept 分组（GROUP BY dept)
-- 再用 count 聚合函数统计部门人数
-- 最后筛选出部门总人数大于1人的部门

1
2

> SELECT IF(1 > 0, 'YES', 'NO')
-- return: YES

1
2

> SELECT IFNULL(null, 'Hello Word')
-- return: Hello Word

1
2
3
4
5
6
7
8

> SELECT CASE
    WHEN e1
    THEN v1
    WHEN e2
    THEN v2
    ...
    ELSE vn
  END

1
2
3
4
5
6

> SELECT CASE expr
    WHEN e1 THEN v1
    WHEN e2 THEN v2
    ...
    ELSE vn
  END

1
2

> SELECT PASSWORD('123')
-- return: *23AE809DDACAF96AF0FD78ED04B6A265E05AA257

1
2

> SELECT MD5('123')
-- return: 202cb962ac59075b964b07152d234b70

1
2
3
4

> SELECT ENCODE('123', 'xxoo')
-- return: ;vx
> SELECT DECODE(';vx','xxoo')
-- return: 123

1
2

> SELECT FORMAT(3.1215926, 3)
-- return: 3.142

1
2
3
4

> SELECT INET_ATON('192.168.0.1')
-- return: 3232235521
> SELECT INET_NTOA(3232235521)
-- return: 192.168.0.1

1
2
3
4
5
6

> SELECT GET_LOCK('MySQL', 10)
-- return: 1 (持续 10 秒)
> SELECT IS_FREE_LOCK('MySQL')
-- return: 1
> SELECT RELEASE_LOCK('MySQL')
-- return: 1

1
2

> SELECT BENCHMARK(10000, NOW())
-- return: 0 (返回系统时间 1 万次)

1
2
3
4

> SELECT CHARSET('ABC')
-- return: utf-8
> SELECT CHARSET(CONVERT('ABC' USING gbk))
-- return: gbk

1
2
3
4
5
6

> SELECT CAST('123' AS UNSIGNED INTEGER) + 1
-- return: 124
> SELECT '123' + 1
-- return: 124 (其实 MySQL 能默认转换)
> SELECT CAST(NOW() AS DATE)
-- return: 2014-12-18