id game qq
1 a 123
2 b 123
3 c 234
4 e 123
SELECT qq, GROUP_CONCAT(DISTINCT game ORDER BY game DESC SEPARATOR '_') AS game FROM tenqq GROUP BY qq;
qq game
123 e_b_a
234 c
CREATE TABLE tenqq_group_concat LIKE tenqq;
INSERT INTO tenqq_group_concat
SELECT id, GROUP_CONCAT(DISTINCT game ORDER BY game DESC SEPARATOR '_'), qq FROM tenqq GROUP BY qq;
w
select count(*) from db_mining.miner_movers_shakers_us where top_count =
union all
select count(*) from db_mining.miner_movers_shakers_us where top_count =
union all
select count(*) from db_mining.miner_movers_shakers_us where top_count not in (,)
update
movers_shakers
set created_at = DATE_SUB(created_at,INTERVAL 1 day),
updated_at = DATE_SUB(updated_at,INTERVAL 1 day)
where id>0; 建表字符集 CREATE TABLE `answers_jp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`question_id` VARCHAR(25) NOT NULL DEFAULT '' COMMENT '提问的id' ,
`question_author` VARCHAR(100) NOT NULL DEFAULT '' COMMENT '提问人的姓名' ,
`post_content_id` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '回答的id' ,
`answer_content` VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '回答内容' ,
`is_seller` TINYINT(1) DEFAULT 0 COMMENT '0买家回复1卖家回复',
`post_date` VARCHAR(50) NOT NULL DEFAULT '' COMMENT '回答的发表日期' ,
PRIMARY KEY (`auto_id`),
UNIQUE INDEX `post_content_id` (`post_content_id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_unicode_ci
COMMENT='Questions & Answers 回答页面'; SHOW PROCESSLIST;
KILL 123;
w
ALTER TABLE questions_grab_us MODIFY question TEXT CHARSET utf8 COLLATE utf8_unicode_ci ; ALTER TABLE questions_grab_uk MODIFY question TEXT CHARSET utf8 COLLATE utf8_unicode_ci ; ALTER TABLE questions_grab_jp MODIFY question TEXT CHARSET utf8 COLLATE utf8_unicode_ci ; ALTER TABLE questions_grab_de MODIFY question TEXT CHARSET utf8 COLLATE utf8_unicode_ci ; ALTER TABLE questions_grab_fr MODIFY question TEXT CHARSET utf8 COLLATE utf8_unicode_ci ;
ALTER TABLE answers_grab_us MODIFY ask_author VARCHAR(64) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY answer_author VARCHAR(64) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY answer_content TEXT CHARSET utf8 COLLATE utf8_unicode_ci, MODIFY post_date VARCHAR(32) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY comment_count TEXT CHARSET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE answers_grab_uk MODIFY ask_author VARCHAR(64) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY answer_author VARCHAR(64) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY answer_content TEXT CHARSET utf8 COLLATE utf8_unicode_ci, MODIFY post_date VARCHAR(32) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY comment_count TEXT CHARSET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE answers_grab_de MODIFY ask_author VARCHAR(64) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY answer_author VARCHAR(64) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY answer_content TEXT CHARSET utf8 COLLATE utf8_unicode_ci, MODIFY post_date VARCHAR(32) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY comment_count TEXT CHARSET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE answers_grab_fr MODIFY ask_author VARCHAR(64) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY answer_author VARCHAR(64) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY answer_content TEXT CHARSET utf8 COLLATE utf8_unicode_ci, MODIFY post_date VARCHAR(32) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY comment_count TEXT CHARSET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE answers_grab_jp MODIFY ask_author VARCHAR(64) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY answer_author VARCHAR(64) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY answer_content TEXT CHARSET utf8 COLLATE utf8_unicode_ci, MODIFY post_date VARCHAR(32) CHARSET utf8 COLLATE utf8_unicode_ci DEFAULT ' ', MODIFY comment_count TEXT CHARSET utf8 COLLATE utf8_unicode_ci;
BLOB, TEXT, GEOMETRY or JSON column 'answer_content' can't have a default value 字符串截取 substr trim
SELECT SUBSTR(TRIM(question_id),10,LENGTH(TRIM(question_id))-9) AS question_id FROM questions_grab_us;
快速添加字段
CREATE TABLE v2_1_add_quota LIKE v2_1;
INSERT INTO v2_1_add_quota
SELECT * FROM v2_1;
ALTER TABLE v2_1_add_quota
ADD ask varchar(10) COLLATE utf8_unicode_ci DEFAULT ' ',
ADD five_star varchar(5) COLLATE utf8_unicode_ci DEFAULT ' ',
ADD four_star varchar(5) COLLATE utf8_unicode_ci DEFAULT ' ',
ADD three_star varchar(5) COLLATE utf8_unicode_ci DEFAULT ' ',
ADD two_star varchar(5) COLLATE utf8_unicode_ci DEFAULT ' ',
ADD one_star varchar(5) COLLATE utf8_unicode_ci DEFAULT ' ',
ADD offer_listing varchar(50) COLLATE utf8_unicode_ci DEFAULT ' ',
ADD soldby varchar(100) COLLATE utf8_unicode_ci DEFAULT ' ',
ADD bsr1path varchar(200) COLLATE utf8_unicode_ci DEFAULT ' ' ;
跨表UNION ALL
SELECT COUNT(*) FROM v2 WHERE LENGTH(price)=0
UNION ALL
SELECT COUNT(*) FROM v2_1 WHERE LENGTH(price)=0;
w
SELECT CONCAT(COUNT(*),'all') FROM amz_listing
UNION ALL
SELECT CONCAT(COUNT(*),'LENGTH(coin) = 0') FROM amz_listing
WHERE LENGTH(coin) = 0
UNION ALL
SELECT CONCAT(COUNT(*),'LENGTH(coin_mc) = 0') FROM amz_listing
WHERE LENGTH(coin_mc) = 0
UNION ALL
SELECT CONCAT(COUNT(*),'LENGTH(list_coin) = 0') FROM amz_listing
WHERE LENGTH(list_coin) = 0
UNION ALL
SELECT CONCAT(COUNT(*),'LENGTH(deal_coin) = 0') FROM amz_listing
WHERE LENGTH(deal_coin) = 0
UNION ALL
SELECT CONCAT(COUNT(*),'coin_mc = 0') FROM amz_listing
WHERE coin_mc = 0
UNION ALL
SELECT CONCAT(COUNT(*),'$coin_mc=deal_coin') FROM amz_listing
WHERE CONCAT('$',coin_mc) = deal_coin
UNION ALL
SELECT CONCAT(COUNT(*),'LENGTH(deal_coin) != 0') FROM amz_listing
WHERE LENGTH(deal_coin) != 0
874697all
574356LENGTH(price) = 0
0LENGTH(price_mc) = 0
435336LENGTH(list_price) = 0
847931LENGTH(deal_price) = 0
182858price_mc = 0
26762$price_mc=deal_price
26766LENGTH(deal_price) != 0
缺失值的比率
SELECT COUNT(*) FROM amazon_listing_daily_us
UNION ALL
SELECT COUNT(*) FROM amazon_listing_daily_us
WHERE LENGTH(price) = 0
选出同表2列的较大值
UPDATE amazon_deal_us_todo_origin_missingval_add_field_before_after_la1
SET effective_date = GREATEST(erp_db_date,datacenter_website_local_date)
SELECT GREATEST(erp_db_date,datacenter_website_local_date) FROM amazon_deal_us_todo_origin_missingval_add_field_before_after_las
w 处理时差
UPDATE amazon_listing_fr_copy SET add_date_time = DATE_SUB(add_date_time, INTERVAL 12 HOUR)
SELECT cp.add_date_time,a.add_date_time,cp.ASIN_ID FROM amazon_listing_fr_copy cp LEFT JOIN amazon_listing_fr a ON cp.ASIN_ID = a.ASIN_ID LIMIT 2
https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html
建库
设置字符集
设置排序规则
CREATE DATABASE amzapi
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_unicode_ci;
SET default_storage_engine=InnoDB;
建库
设置存储引擎
CREATE DATABASE apiamz; SET default_storage_engine=InnoDB;
mysql> CREATE DATABASE apiamz; SET default_storage_engine=InnoDB;
Query OK, row affected (0.01 sec) Query OK, rows affected (0.00 sec) mysql>
表名大小写
mysql> INSERT INTO ListOrderItems (AmazonOrderId,ASIN,SellerSKU,OrderItemId,Title,QuantityOrdered,QuantityShipped,ItemPriceCurrencyCode,ItemPriceAmount,ItemTaxCurrencyCode,ItemTaxAmount,PromotionDiscountCurrencyCode,PromotionDiscountAmount) VALUES ("test","","")
->
-> ;
ERROR (42S02): Table 'apiamz.ListOrderItems' doesn't exist
mysql> INSERT INTO ListOrderItems (AmazonOrderId,ASIN,SellerSKU,OrderItemId,Title,QuantityOrdered,QuantityShipped,ItemPriceCurrencyCode,ItemPriceAmount,ItemTaxCurrencyCode,ItemTaxAmount,PromotionDiscountCurrencyCode,PromotionDiscountAmount) VALUES ("test","","");
ERROR (42S02): Table 'apiamz.ListOrderItems' doesn't exist
mysql> INSERT INTO listorderitems (AmazonOrderId,ASIN,SellerSKU,OrderItemId,Title,QuantityOrdered,QuantityShipped,ItemPriceCurrencyCode,ItemPriceAmount,ItemTaxCurrencyCode,ItemTaxAmount,PromotionDiscountCurrencyCode,PromotionDiscountAmount) VALUES ("test","","");
Query OK, row affected (0.00 sec)
mysql>
tar -xvf apiamz.tar
mysql -uroot -p123
create database apiamz
use apiamz
source home/etc/project/apilinux/Samples/apiamz.sql
linux 导入数据库
w
mysql拷贝表的几种方式 - 51CTO.COM
http://database.51cto.com/art/201011/234776.htm
"
mysql拷贝表操作我们会常常用到，下面就为您详细介绍几种mysql拷贝表的方式，希望对您学习mysql拷贝表方面能够有所帮助。
假如我们有以下这样一个表：
id username password
-----------------------------------
1 admin *************
2 sameer *************
3 stewart *************
CREATE TABLE IF NOT EXISTS `admin` (
`id` int(6) unsigned NOT NULL auto_increment,
`username` varchar(50) NOT NULL default '',
`password` varchar(100) default NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
1. 下面这个语句会拷贝表结构到新表newadmin中。 （不会拷贝表中的数据）
CREATE TABLE newadmin LIKE admin
2. 下面这个语句会拷贝数据到新表中。 注意：这个语句其实只是把select语句的结果建一个表。所以newadmin这个表不会有主键，索引。
CREATE TABLE newadmin AS
(
SELECT *
FROM admin
)
3. 如果你要真正的复制一个表。可以用下面的语句。
CREATE TABLE newadmin LIKE admin;
INSERT INTO newadmin SELECT * FROM admin;
4. 我们可以操作不同的数据库。
CREATE TABLE newadmin LIKE shop.admin;
CREATE TABLE newshop.newadmin LIKE shop.admin;
5. 我们也可以拷贝一个表中其中的一些字段。
CREATE TABLE newadmin AS
(
SELECT username, password FROM admin
)
6. 我们也可以讲新建的表的字段改名。
CREATE TABLE newadmin AS
(
SELECT id, username AS uname, password AS pass FROM admin
)
7. 我们也可以拷贝一部分数据。
CREATE TABLE newadmin AS
(
SELECT * FROM admin WHERE LEFT(username,1) = 's'
)
8. 我们也可以在创建表的同时定义表中的字段信息。
CREATE TABLE newadmin
(
id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
AS
(
SELECT * FROM admin
)
"
去重
由于进程并发而未加锁，导致入库表的数据存在重复行
方案:
0-复制GROUP BY后的原表
w
w wb
123 0
12993 0
1 1
5 1
2 2
解决办法；复制表去重
全部复制
CREATE TABLE wb LIKE w;
INSERT INTO wb SELECT * FROM w;
通过GROUP BY 去重
CREATE TABLE wd LIKE w;
INSERT INTO wd SELECT * FROM w GROUP BY wb;
w
获取亚马逊订单列表接口入库数据的订单总条数、下单时间的最值。
SELECT MAX(PurchaseDate),MIN(PurchaseDate),COUNT(*) FROM listorders;
w
timestamp
CURRENT_TIMESTAMP
对单列每一个数据加权去求和，任意种权重
SELECT
SUM(CASE WHEN w0>0 THEN 1 ELSE 0 END ),
SUM(CASE WHEN w1>3 THEN 100 WHEN w1>1 THEN 10 ELSE 0 END ),
SUM(IF(w1>1,1,0))
FROM
w
对单列每一个数据加权去求和，至多2种权重
SELECT
(SELECT 100*COUNT(*) FROM w WHERE w0>0 ),
(SELECT COUNT(*) FROM w WHERE w1>1 ),
(SELECT COUNT(*) FROM w WHERE w2>2 )
支持跨表、跨库
SELECT
(SELECT COUNT(*) FROM w WHERE w0>0 ),
(SELECT COUNT(*) FROM w WHERE w1>1 ),
(SELECT COUNT(*) FROM w WHERE w2>2 )
支持给不同case以不同权重
SELECT
SUM(CASE WHEN w0>0 THEN 1 ELSE 0 END ),
SUM(IF(w1>1,1,0))
FROM
w
w0 w1 w2
0 2 1
1 0 3
4 2 4
w
http://php.net/manual/en/mysqli.construct.php
mysqli::__construct ([ string $host = ini_get("mysqli.default_host") [, string $username = ini_get("mysqli.default_user") [, string $passwd = ini_get("mysqli.default_pw") [, string$dbname = "" [, int $port = ini_get("mysqli.default_port") [, string $socket = ini_get("mysqli.default_socket") ]]]]]] )
$link = mysqli_connect("192.168.11.22", "u_write", "123", "APIamz",3302);
w
w
字段属性更改。
更改int default 0 为 smallint default 1;
alter table gbcart modify gbnum smallint DEFAULT 1 ;
显示注释
添加新字段
alter table wcart add wstatus tinyint(1) default 0;
字段重命名
ALTER TABLE w MODIFY fkgid wid INT(11) DEFAULT 0;
CREATE TABLE w SELECT * FROM existing_table
日期x idm valuexm
日期x idn valuexn
日期y idm valueym
日期y idp valueyp
日期z idm valueym
日期z idn valueyn
日期z idq valueyq
求id-value的最近两日期增长量
ALTER TABLE w MODIFY wf TINYINT(1) DEFAULT '';
SELECT
DATE_FORMAT(reportedate, '%Y') AS year,
DATE_FORMAT(reportedate, '%m') AS month,
DATE_FORMAT(reportedate, '%V') AS week,
SUM(mount) AS week_mount,
SUM(coin) AS week_coin
FROM
sale
WHERE
DATE_FORMAT(reportedate, '%Y') = 2016
GROUP BY
week; SELECT
DATE_FORMAT(NOW(), '%Y');
SELECT
"1997-11-01" + INTERVAL 1 MONTH + INTERVAL - 1 DAY; CREATE VIEW view_tab AS
SELECT
DATE_FORMAT(reportedate, '%Y') AS year,
DATE_FORMAT(reportedate, '%m') AS month,
DATE_FORMAT(reportedate, '%V') AS week,
SUM(mount) AS week_mount,
SUM(coin) AS week_coin
FROM
sale
WHERE
DATE_FORMAT(reportedate, '%Y') = 2016
GROUP BY
week; SELECT
DATE_FORMAT(reportedate, '%Y') AS year,
DATE_FORMAT(reportedate, '%m') AS month,
DATE_FORMAT(reportedate, '%V') AS week,
SUM(mount) AS week_mount,
SUM(coin) AS week_coin
FROM
sale
WHERE
DATE_FORMAT(reportedate, '%Y') = (SELECT
DATE_FORMAT(NOW(), '%Y') )
GROUP BY
week;
统计不同类别的总数
SELECT COUNT(1) FROM tab_child WHERE tab_parent_id!=0
UNION ALL
SELECT COUNT(1) FROM tab_child WHERE tab_parent_id=0;
SELECT COUNT(1)
FROM (
SELECT
CASE tab_parent_id
WHEN 0 THEN 0
ELSE -1
END AS id__
FROM tab_child
)
AS tmp
GROUP BY id__
;
统计每小时的数据
SELECT COUNT(1),FROM_UNIXTIME(create_time,'%Y-%m-%d %H') AS p FROM order GROUP BY p;
1 2017-12-02 00
134 2017-12-02 09
81 2017-12-02 10
68 2017-12-02 11
56 2017-12-02 12
4 2017-12-02 13
2 2017-12-02 14
166 2017-12-02 15
71 2017-12-02 16
211 2017-12-02 17
209 2017-12-02 18
334 2017-12-02 19
309 2017-12-02 20
334 2017-12-02 21
205 2017-12-02 22
21 2017-12-02 23
4 2017-12-03 04
4 2017-12-03 05
1 2017-12-03 06
4 2017-12-03 07
15 2017-12-03 08
6 2017-12-03 09
5 2017-12-03 10
11 2017-12-03 11
23 2017-12-03 12
8 2017-12-03 13
9 2017-12-03 14
8 2017-12-03 15
18 2017-12-03 16
12 2017-12-03 17
复制某表字段到另一表
INSERT INTO test_temp(uid,url,remarks,title) SELECT uid,url,remarks,title FROM test;
更新字符串字段
UPDATE test_temp SET remarks=CONCAT('_____',remarks,'______________________') WHERE id<100;
按字段和分组统计
SELECT COUNT(1) FROM test_temp GROUP BY no_open_times+no_ad_times+no_open_times;
SELECT COUNT(1) ,no_open_times+no_ad_times+no_open_times as a FROM test_temp GROUP BY a;
sql连表通过WHERE过滤数据
SELECT url,no_open_times,no_ad_times,ok_times,script_need_run_times,uid,title,remarks,create_time,update_time FROM test_error_temp
WHERE no_ad_times+no_open_times+ok_times>=script_need_run_times AND url NOT IN (SELECT DISTINCT url FROM test_error) ORDER BY id DESC;
连表更新
UPDATE `a`
INNER JOIN `b` ON a.user_id=b.user_id
SET a.rate=b.rate;
更新数字描述为数字
2.3万
5113
7.6万
403
2.5万
1089
23
504
1.3万
8.3万
2915
1.4万
1613
3058
3565
1712
10万
UPDATE xmt_star_helper_toutiao_uid_targeted SET num_followed=REPLACE(num_followed, '万', '' )*10000 WHERE id=186980;
UPDATE xmt_star_helper_toutiao_uid_targeted SET num_followed=REPLACE(num_followed, '万', '' )*10000 WHERE INSTR(num_followed,'万')>0;
`num_followed` varchar(11) DEFAULT NULL,
DELETE FROM xmt_star_helper_toutiao_uid_targeted WHERE LENGTH(REPLACE(num_followed, ' ', '' ))=0;
ALTER TABLE `xmt_star_helper_toutiao_uid_targeted`
MODIFY COLUMN `num_following` int(11) NULL DEFAULT NULL COMMENT 'following关注数followed粉丝数' AFTER `selfintroduction`,
MODIFY COLUMN `num_followed` int(11) NULL DEFAULT NULL AFTER `num_following`;
全表复制至另外一张表
INSERT INTO test SELECT * FROM v_video_test_udp_plusold WHERE id>19;
GROUP 更新
UPDATE v_video_test_copy SET title='d333ssd' WHERE article_id=5079 ORDER BY id DESC LIMIT 1;
去除新生成的重复数据
DELETE FROM v_video WHERE id IN (
SELECT id FROM (
SELECT MAX(id) AS id ,COUNT(1) AS c FROM v_video GROUP BY article_id
) AS t WHERE c>1
) ;
借助临时表查询 借助临时表复制表
DROP TEMPORARY TABLE IF EXISTS xl_tmp_pn ;
CREATE TEMPORARY TABLE xl_tmp_pn AS SELECT COUNT(1) AS used ,uid FROM tab_paid GROUP BY uid ;
SELECT v.total,v.uid,t.used FROM (
SELECT COUNT(1) AS total ,uid FROM tab_produced WHERE id IN (
SELECT MAX(id) FROM tab_produced WHERE status = 0 GROUP BY article_id
) GROUP BY uid
) AS v
LEFT JOIN xl_tmp_pn t ON v.uid=t.uid
;
DROP TEMPORARY TABLE IF EXISTS xl_tmp_pn ;
CREATE TEMPORARY TABLE xl_tmp_pn AS SELECT COUNT(1) AS used ,uid FROM tab_paid GROUP BY uid ;
INSERT INTO tab_test (total,uid,used,modify_time,create_time) SELECT v.total,v.uid,t.used,UNIX_TIMESTAMP(),UNIX_TIMESTAMP() FROM (
SELECT COUNT(1) AS total ,uid FROM tab_produced WHERE id IN (
SELECT MAX(id) FROM tab_produced WHERE status = 0 GROUP BY article_id
) GROUP BY uid
) AS v
LEFT JOIN xl_tmp_pn t ON v.uid=t.uid
;
查询结果加序号
SELECT (@i := @i + 1) rownum, FROM_UNIXTIME( create_time,'%Y-%m-%d %H:%i:%S') ,v.* FROM tab_test v , (SELECT @i := 0) AS a WHERE status=0 ORDER BY create_time DESC LIMIT 30;
每个用户每天的消费金额
SELECT SUM(money),uid,FROM_UNIXTIME(buy_time,'%Y-%m-%d') AS d FROM shopping_history GROUP BY uid,d;
产品每天的销售额
SELECT SUM(money) AS t_m,uid FROM shopping_history GROUP BY uid ORDER BY t_m DESC;
消费用户计数
SELECT (@i := @i + 1) rownum,SUM(money) AS t_m,uid FROM shopping_history,(SELECT @i := 0) AS a GROUP BY uid ORDER BY t_m DESC;
销售总额
SELECT SUM(money) FROM shopping_history ;
天销售总额
SELECT FROM_UNIXTIME(buy_time,'%Y-%m-%d') AS d, SUM(money) FROM shopping_history GROUP BY d ORDER BY d DESC ;
字段唯一性 约束
ALTER TABLE namepwd ADD UNIQUE KEY (mobile);
数据恢复
INSERT INTO t_pro SELECT * FROM t_test;
注意 id 过滤，交集为空，限制条件不冲突
每次写记录当前时间 insert update
`action_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
创建新表并导入数据；
create table targer_table as select * from source_table;
https://www.cnblogs.com/zzzy0828/p/7531601.html
MYSQL自动获取时间日期
实现方式：
1、将字段类型设为 TIMESTAMP
2、将默认值设为 CURRENT_TIMESTAMP
举例应用：
1、MySQL 脚本实现用例
--添加CreateTime 设置默认时间 CURRENT_TIMESTAMP
ALTER TABLE `table_name`
ADD COLUMN `CreateTime` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;
--修改CreateTime 设置默认时间 CURRENT_TIMESTAMP
ALTER TABLE `table_name`
MODIFY COLUMN `CreateTime` datetime NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间' ;
--添加UpdateTime 设置 默认时间 CURRENT_TIMESTAMP 设置更新时间为 ON UPDATE CURRENT_TIMESTAMP
ALTER TABLE `table_name`
ADD COLUMN `UpdateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间' ;
--修改 UpdateTime 设置 默认时间 CURRENT_TIMESTAMP 设置更新时间为 ON UPDATE CURRENT_TIMESTAMP
ALTER TABLE `table_name`
MODIFY COLUMN `UpdateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间' ;
将一张表的某行值更新到全表
update test_consumption_detail set consumption_detail=(SELECT consumption_detail FROM
(select consumption_detail from test_consumption_detail where id=73) as t)