MySQL 基本操作
查询
最简单的查询语句： 查询 dcuser 表中所有字段。
简单查询
SELECT * FROM dcuser;
以上语句在代码中禁止使用，因为在数据库扩展时，SELECT * 语句对应的结构体如果没有增加字段，则会出现未知错误。
指定字段查询
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)的记录。
SELECT UserID, Name, DCType, DepotID FROM dcuser WHERE DCType = 1;
查询 groupcallinfo 表中 DCUserID 为 13003 或 13004
SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo WHERE DCUserID IN (13003, 13004);
BETWEEN AND
查询 groupcallinfo 表中 DCUserID 介于 13003 到 13005
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 开头的内容。 % 代替任意数量字符。
SELECT UserID, Name, DCType, DepotID FROM dcuser WHERE Name LIKE 'dis%'
查询 dcuser 表中所有以 Name 字段以 dis0 + 任意一个字符的内容。 _ 代替一个任意字符。
SELECT UserID, Name, DCType, DepotID FROM dcuser WHERE Name LIKE 'dis0_'
查询结果排序
按时间降序查询 groupcallinfo 表中的数据。
升序(ASC): 数值小的记录在前。
降序(DESC): 数值大的记录在前。
如果不写关键字, 则默认使用 升序ASC
SELECT SeqID, DCUserID, GroupID, Time FROM groupcallinfo ORDER BY Time DESC;
也可以使用多字段排序。 按照 Time 字段降序， ASC 字段升序排列。
SELECT SeqID, DCUserID, GroupID, Time FROM groupcallinfo ORDER BY Time DESC, SeqID ASC;
查询总数量
查询 dcuser 表中有几个全功能调度员的账号。
SELECT COUNT(*) AS '记录数' FROM dcuser WHERE DCType = 65535;
输出:
| 记录数 |
|---|
| 2 |
限制查询记录条数
查询组呼记录，只显示100条。
SELECT DCUserID, GroupID, Time, CallType FROM groupcallinfo LIMIT 100;
子查询
查询组呼记录表中所有具有行车调度权限调度台处理的记录。
翻译为 SQL 语句:
查询 groupcallinfo 表中， DCUserID 等于 dcuser 表中 DCType 等于 1 记录的 UserID 字段的值
三句话等价:
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);
SELECT * FROM contacts WHERE (surname, firstname) IN (SELECT surname, firstname FROM customer);
ALL 与 ANY 关键字:
找出 class1 中比 class2 所有 source 都高的学生信息。
SELECT * FROM class1 WHERE source > ALL(SELECT source FROM class2);
找出 class1 中 second_name 与 class2 中的重名的学生信息。
SELECT * FROM class1 WHERE second_name = ANY(SELECT second_name FROM class2);
查询最大、最小、平均值
SELECT MAX(EndTime-StartTime) AS '最大通话时长', MIN(EndTime-StartTime) AS '最小通话时长', AVG(EndTime-StartTime) AS '平均通话时长' FROM trainposcallinfo;
输出:
| 最大通话时长 | 最小通话时长 | 平均通话时长 |
|---|---|---|
| 48 | 2 | 14.667 |
查询数据分组
统计不同的 DCUserID 都有多少条组呼记录。
SELECT DCUserID, COUNT(*) AS "总数" FROM groupcallinfo GROUP BY DCUserID;
输出:
| DCUserID | 总数 |
|---|---|
| 13001 | 13 |
| 13003 | 662 |
| 13005 | 53 |
| 13006 | 131 |
WITH ROLLUP
WITH ROLLUP 用来在 GROUP BY 统计的基础上再加一行总数的统计行。
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的记录才能进行聚合语句
查询过滤重复数据
SELECT DISTINCT DCType FROM dcuser;
输出:
| DCType |
|---|
| 65535 |
| 2 |
说明: DISTINCT 关键词修饰的是语句整体，不能对单独字段修饰，并查询其他字段内容。
如:
SELECT DISTINCT DCType, UserID FROM dcuser;
如上语句意义为查询 DCType 且 UserID 同时不重复的列。
输出:
| DCType | UserID |
|---|---|
| 65535 | 3000 |
| 65535 | 3001 |
| 65535 | 3002 |
| 65535 | 3003 |
| 2 | 9999 |
替换特定字段查询结果
查询 dcuser 表中所有数据， DCType 字段等于 65535 的显示全功能调度员, 等于 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
RENAME TABLE dcuser TO dcuser_new;
删除表
删除整个 dcuser 表。
DROP TABLE dcuser;
清空表
删除表信息的方式有两种 :
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
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");
参考资料
插入主键重复的数据
插入 dcuser 表中一条数据，如果主键重复则更新原数据的 StationList 字段。
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";
按天查询数量
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循环、游标