#SQL1242错误
---------------------
- 子查询更新数据时遇到多条数据时,可以使用SUM&MIN等函数解决:如下:
//正确的方法一对多 UPDATE `yd_draw_prize_order` SET `adopt_update_time` = (SELECT MIN(`update_time`) FROM `yd_draw_prize_order_number` WHERE `d_p_order_id` = `yd_draw_prize_order`.`d_p_order_id`) WHERE `adopt_update_time` IS NULL //报1242错误方式一对多 UPDATE `yd_draw_prize_order` SET `adopt_update_time` = (SELECT `update_time` FROM `yd_draw_prize_order_number` WHERE `d_p_order_id` = `yd_draw_prize_order`.`d_p_order_id`) WHERE `adopt_update_time` IS NULL
- 时间查询 简单举例: SELECT COUNT('$column') FROM `%table` WHERE `$column` BETWEEN UNIX_TIMESTAMP('2018-05-03 00:00:01') AND UNIX_TIMESTAMP('2018-05-03 23:59:59')
更多时间查询案例;
案例分享:123456789101112131415161718192021222324//FROM_UNIXTIME 将时间戳转换为字符串日期;
//UNIX_TIMESTAMP 将其他时间转换为时间戳;
SELECT
`yd_draw_prize_order`.`d_p_order_id`,
yd_draw_prize_order_log.`rand_num`,
FROM_UNIXTIME(yd_draw_prize_order.adopt_time)
AS
adopt_time,
FROM_UNIXTIME(
yd_draw_prize_order_log.draw_time
)
AS
'抽奖时间'
,
yd_draw_prize_order_log.`d_p_order_activity_id`,
yd_draw_prize_order_log.`d_p_order_activity_name`,
yd_draw_prize_order_log.`draw_num`,
yd_draw_prize_order_log.`winning`
FROM
`yd_draw_prize_order`
INNER
JOIN
`yd_draw_prize_order_number`
ON
yd_draw_prize_order.d_p_order_id = yd_draw_prize_order_number.d_p_order_id
INNER
JOIN
`yd_draw_prize_order_log`
ON
yd_draw_prize_order.d_p_order_id = yd_draw_prize_order_log.d_p_order_id
WHERE
`customer_phone` =
'15512816085'
AND
`adopt` = 1
AND
yd_draw_prize_order_number.`reward_num` = 04028
AND
yd_draw_prize_order_log.draw_time > UNIX_TIMESTAMP(NOW())
AND
yd_draw_prize_order_log.`d_p_order_activity_id` = 2
案例2
123456789101112131415//日期子查询;
SELECT
*
FROM
`yd_extension_data_1`
WHERE
`distrbutor_id`
IN
(
SELECT
`distrbutor_id`
FROM
`yd_distrbutor`
WHERE
`distrbutor_provinceid` = 19)
AND
`create_time`
BETWEEN
'2017-01-01 00:00:01'
AND
'2018-01-01 00:00:00'
AND
`distrbutor_id`
NOT
IN
(1,23)
ORDER
BY
`create_time`
- 综合查询&&综合更新
综合查询案例1:
1. SELECT b.id AS '用户id', user_name AS '用户名', `active_points` AS '积分', COUNT(bet_state) AS '猜中数', GROUP_CONCAT(bet_state) AS '统计情况', GROUP_CONCAT(match_id) AS '赛事ID', b.share AS '1为己分享', `use_points` AS '消耗积分' FROM yd_draw_worldcup_betting AS a INNER JOIN yd_draw_worldcup_user AS b WHERE a.`worldcup_user_id` = b.`id` AND a.`bet_state` IN (20, 21, 22) AND b.`use_points` <> 0 GROUP BY id; 2. SELECT b.id AS '用户id', user_name AS '用户名', `active_points` AS '积分', b.share + COUNT(bet_state) AS '自定义', COUNT(bet_state) AS '猜中数', b.share AS '1为己分享', `use_points` AS '消耗积分' FROM yd_draw_worldcup_betting AS a INNER JOIN yd_draw_worldcup_user AS b WHERE a.`worldcup_user_id` = b.`id` AND a.`bet_state` IN (20, 21, 22) AND b.`use_points` = 0 GROUP BY id;
1. UPDATE `yd_draw_worldcup_user` SET `active_points` = `active_points` + 1 WHERE `id` IN (SELECT `worldcup_user_id` FROM `yd_draw_worldcup_betting` WHERE `match_id` = 2 AND `bet_state` IN (20, 21, 22))
综合链表更新2:
1. update yd_draw_worldcup_user as a,yd_draw_worldcup_betting as b set a.active_points = 0, b.bet_state = 10, b.draw_number = '' where b.match_id in (1,2,3,4,5) and b.worldcup_user_id = a.id;
- 判断语句:
1234UPDATE
yd_draw_worldcup_user
AS
a
SET
a.active_points =
(
CASE
WHEN
(a.active_points + a.`share` < a.use_points)
THEN
0
ELSE
(a.active_points + a.`share` - a.use_points)
END
)
WHERE
1;
- 事务语句:
123456789101112131415#mysql使用事务的关键字
#
begin
//打开一个事务
#
commit
//提交到数据库
#
rollback
//取消操作
#savepoint //保存,部分取消,部分提交
#
alter
table
person type=INNODB //修改数据引擎
begin
;
update
tags
set
tagid = 133
where
docid = 1;
SAVEPOINT tags1;
update
tags
set
tagid = 530
where
docid =2;
SAVEPOINT tags2;
ROLLBACK
TO
SAVEPOINT tags2;
SELECT
*
from
tags
where
docid
in
(1,2);
commit
;
- 自定义序列号字段:
12345678SET
@rownum=0;
SELECT
@rownum:=@rownum+1
AS
'序列'
,
a.<You_TableName_Field>
AS
'用户名称'
FROM
(
SELECT
@rownum:=0) r,<You_TableName>
AS
a
WHERE
1
ORDER
BY
<You_TableName>.<You_TableName_Field>
ASC
- mysql update You can't specify target table 'yd_qr_code' for update in FROM clause .
原:update `yd_qr_code` set winning_description = (select a.`winning_description` from `yd_qr_code` as a where a.`qr_id` = 1) where qr_id = 2
原理:mysql 不能在同表操作更新,我们要用一个 中间表 来让数据库认为不是同表操作;
后:update `yd_qr_code` set winning_description = (select b.`winning_description` from (select a.`winning_description` from `yd_qr_code` as a where a.`qr_id` = 1)b) where qr_id = 2 - 【查询】数据库有哪些表;
select table_schema as database_name, table_name from information_schema.tables where table_type = 'BASE TABLE' and table_name like '%__value%' order by table_schema, table_name;
--------------------------------
权限:
- 为用户 xuguo 添加"xu_gms" 数据库操作权限:GRANT ALL PRIVILEGES ON xu_gms.* to 'xuguo'@'%';
- 数据库文件导出: mysqldump -u username -p dbname > filename.sql
- 查看字符集:show variables like '%char%';
修改数据字符集:set character_set_database=utf8;
set character_set_server=utf8; - 修改全局变量max_allowed_packet:global max_allowed_packet = 2*1024*1024*10 ; 查看全局变量max_allowed_packet:show VARIABLES like '%max_allowed_packet%';
- --
---------------------
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek 解答了困扰我五年的技术问题
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· 用 C# 插值字符串处理器写一个 sscanf
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· 为什么说在企业级应用开发中,后端往往是效率杀手?
· DeepSeek 解答了困扰我五年的技术问题。时代确实变了!
· 本地部署DeepSeek后,没有好看的交互界面怎么行!
· 趁着过年的时候手搓了一个低代码框架
· 推荐一个DeepSeek 大模型的免费 API 项目!兼容OpenAI接口!