MySQL 基本操作

查询

最简单的查询语句：查询 dcuser 表中所有字段。

简单查询

1	SELECT * FROM dcuser;

以上语句在代码中禁止使用，因为在数据库扩展时，SELECT * 语句对应的结构体如果没有增加字段，则会出现未知错误。

指定字段查询

1	SELECT UserID, Name, DCType, DepotID FROM dcuser;

输出:

UserID	Name	DCType	DepotID
3000	dis01	65535	1
3001	dis02	65535	1
3002	dis03	65535	1
3003	dis04	65535	1
9999	test	2	1

查询添加过滤条件

查询 dcuser 表中具有行车调度权限(DCType = 1)的记录。

1	SELECT UserID, Name, DCType, DepotID FROM dcuser WHERE DCType = 1;

查询 groupcallinfo 表中 DCUserID 为 13003 或 13004

1	SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo WHERE DCUserID IN (13003, 13004);

BETWEEN AND

查询 groupcallinfo 表中 DCUserID 介于 13003 到 13005

1
2

SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo WHERE DCUserID BETWEEN 13003 AND 13005;
SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo WHERE DCUserID >= 13003 AND DCUserID <= 13005;

模糊查询

查询 dcuser 表中所有以 Name 字段以 dis 开头的内容。 % 代替任意数量字符。

1	SELECT UserID, Name, DCType, DepotID FROM dcuser WHERE Name LIKE 'dis%'

查询 dcuser 表中所有以 Name 字段以 dis0 + 任意一个字符的内容。 _ 代替一个任意字符。

1	SELECT UserID, Name, DCType, DepotID FROM dcuser WHERE Name LIKE 'dis0_'

查询结果排序

按时间降序查询 groupcallinfo 表中的数据。

升序(ASC): 数值小的记录在前。
降序(DESC): 数值大的记录在前。

如果不写关键字, 则默认使用升序ASC

1	SELECT SeqID, DCUserID, GroupID, Time FROM groupcallinfo ORDER BY Time DESC;

也可以使用多字段排序。按照 Time 字段降序， ASC 字段升序排列。

1	SELECT SeqID, DCUserID, GroupID, Time FROM groupcallinfo ORDER BY Time DESC, SeqID ASC;

查询总数量

查询 dcuser 表中有几个全功能调度员的账号。

1	SELECT COUNT(*) AS '记录数' FROM dcuser WHERE DCType = 65535;

输出:

记录数
2

限制查询记录条数

查询组呼记录，只显示100条。

1	SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo LIMIT 100;

子查询

查询组呼记录表中所有具有行车调度权限调度台处理的记录。

翻译为 SQL 语句:

查询 groupcallinfo 表中， DCUserID 等于 dcuser 表中 DCType 等于 1 记录的 UserID 字段的值

三句话等价:

1
2
3

SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo WHERE DCUserID IN (SELECT UserID FROM dcuser WHERE DCType = 1);
SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo WHERE DCUserID = ANY(SELECT UserID FROM dcuser WHERE DCType = 1);
SELECT A.DCUserID, A.GroupID, A.Time, A.CallType FROM groupcallinfo A WHERE A.DCUserID IN (SELECT B.UserID FROM dcuser B WHERE B.DCType = 1);

1	SELECT * FROM contacts WHERE (surname, firstname) IN (SELECT surname, firstname FROM customer);

ALL 与 ANY 关键字:

找出 class1 中比 class2 所有 source 都高的学生信息。

1	SELECT * FROM class1 WHERE source > ALL(SELECT source FROM class2);

找出 class1 中 second_name 与 class2 中的重名的学生信息。

1	SELECT * FROM class1 WHERE second_name = ANY(SELECT second_name FROM class2);

查询最大、最小、平均值

1	SELECT MAX(EndTime-StartTime) AS '最大通话时长', MIN(EndTime-StartTime) AS '最小通话时长', AVG(EndTime-StartTime) AS '平均通话时长' FROM trainposcallinfo;

输出:

最大通话时长	最小通话时长	平均通话时长
48	2	14.667

查询数据分组

统计不同的 DCUserID 都有多少条组呼记录。

1	SELECT DCUserID, COUNT(*) AS "总数" FROM groupcallinfo GROUP BY DCUserID;

输出:

DCUserID	总数
13001	13
13003	662
13005	53
13006	131

WITH ROLLUP

WITH ROLLUP 用来在 GROUP BY 统计的基础上再加一行总数的统计行。

1	SELECT DCUserID, COUNT(*) AS "总数" FROM groupcallinfo GROUP BY DCUserID WITH ROLLUP;

输出:

DCUserID	总数
13001	13
13003	662
13005	53
13006	131
	859

HAVING 与 WHERE 区别

having子句与where都是设定条件筛选的语句，有相似之处也有区别。

having与where的区别:
having是在分组后对数据进行过滤
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合

在查询过程中执行顺序：from>where>group（含聚合）>having>order>select。

所以聚合语句(sum,min,max,avg,count)要比having子句优先执行，而where子句在查询过程中执行优先级别优先于聚合语句(sum,min,max,avg,count)。
where子句：
select sum(num) as rmb from order where id>10
//只有先查询出id大于10的记录才能进行聚合语句

Mysql中having和where的区别

查询过滤重复数据

1	SELECT DISTINCT DCType FROM dcuser;

输出:

DCType
65535
2

说明: DISTINCT 关键词修饰的是语句整体，不能对单独字段修饰，并查询其他字段内容。
如:

1	SELECT DISTINCT DCType, UserID FROM dcuser;

如上语句意义为查询 DCType 且 UserID 同时不重复的列。

输出:

DCType	UserID
65535	3000
65535	3001
65535	3002
65535	3003
2	9999

替换特定字段查询结果

查询 dcuser 表中所有数据， DCType 字段等于 65535 的显示全功能调度员, 等于 1 的显示行车调度员, 其他取值显示原本的值, 该字段结果显示为调度员类型

1	SELECT CASE DCType WHEN 65535 THEN "全功能调度员" WHEN 1 THEN "行车调度员" ELSE UserID END AS "调度员类型" FROM dcuser;

分段看:

SELECT

CASE DCType
WHEN 65535 THEN
	"全功能调度员"
WHEN 1 THEN
	"行车调度员"
ELSE
	UserID
END
AS "调度员类型"

FROM
	dcuser;

输出:

调度员类型
全功能调度员
14004
行车调度员

关于 CASE... WHEN... 更多用法见下面链接

SQL之CASE WHEN用法详解
 SQL 查询：SELECT CASE 条件赋值
 关于case when复杂sql语句查询

自连接

SQL SELECT(复杂查询)之自连接 & 子查询解析
 sql中自连接的使用
 010-MySQL：自连接查询
 算法工程师-SQL进阶：神奇的自连接与子查询

重命名表

改变 dcuser 表名称到 dcuser_new

1	RENAME TABLE dcuser TO dcuser_new;

删除表

删除整个 dcuser 表。

1	DROP TABLE dcuser;

清空表

删除表信息的方式有两种 :

1 2	TRUNCATE TABLE dcuser; DELETE FROM dcuser;

注 : truncate操作中的table可以省略，delete操作中的*可以省略

truncate、delete 清空表数据的区别 :
1> truncate 是整体删除 (速度较快)，delete是逐条删除 (速度较慢)
2> truncate 不写服务器 log，delete 写服务器 log，也就是 truncate 效率比 delete高的原因
3> truncate 不激活trigger (触发器)，但是会重置Identity (标识列、自增字段)，相当于自增列会被置为初始值，又重新从1开始记录，而不是接着原来的 ID数。而 delete 删除以后，identity 依旧是接着被删除的最近的那一条记录ID加1后进行记录。如果只需删除表中的部分记录，只能使用 DELETE语句配合 where条件

参考资料:
MySQL 清空表(truncate)与删除表中数据(delete) 详解

Update

1	UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;

过程、函数

DELIMITER //

DROP PROCEDURE IF EXISTS  Create10K//

CREATE PROCEDURE `Create10K`(IN `for_time` INT, IN `type_int` INT, IN `info_text` VARCHAR(255))
BEGIN
	DECLARE i INT DEFAULT 0;
    WHILE i < for_time * 10000 DO
    INSERT INTO test_table(Type, Info) VALUES(type_int, info_text);
    SET i = i + 1;
    END WHILE;
END//

DELIMITER ;

CALL Create10K(500, 1, "1");

参考资料

常用SQL查询语句

插入主键重复的数据

插入 dcuser 表中一条数据，如果主键重复则更新原数据的 StationList 字段。

1	INSERT INTO dcuser(UserID, Name, DCType, DepotID, StationList) VALUES(14005, '14005', 1, 1, "1,2,3,4") ON DUPLICATE KEY UPDATE StationList = "1,2,3,4";

按天查询数量

1	SELECT UNIX_TIMESTAMP(date_format(FROM_UNIXTIME(SendTime),'%y-%m-%d 0:0:0')), count(*) FROM sds_info GROUP BY date_format(FROM_UNIXTIME(SendTime),'%y-%m-%d');

参考资料

MySQL 8.0 Reference Manual
Chapter 7 Examples of Common Queries
13.2.9 REPLACE Statement

TODO: 合并查询、分页查询、空值判断、Contact 拼接查询结果、REPLACE INTO、多条插入、事务、存储过程、视图、批量插入、my.ini 的配置项的研究、中间表查询
SQL中的循环、for循环、游标