sql_action

 

查询10分钟前的数据

 

SELECT * FROM  tabWHERE  ( NOW()- INTERVAL 3 MINUTE)<gmt_create

3分钟内新建的数据

 

 

分组排序

-- 按 field2分组，取出组内field1最大值对应行的field3字段值
SELECT MAX(field1), field2, field3 FROM test WHERE field2="A201809" GROUP BY field2

 

 

浮点转整数

AVG(a.Val),CAST(AVG(a.Val) AS DECIMAL(10,0)) 

 

 

 

JSON查询

SELECT * FROM t WHERE JSON_SEARCH(JsonLocation,'all','%郑州%') IS NOT NULL;

`JsonLocation` JSON NULL DEFAULT NULL COMMENT '地理位置信息',

{"City": "郑州市", "Address": "高新路", "District": "中国大陆", "Latitude": 0, "Province": "河南省", "Longitude": 0}
{"City": "洛阳市", "Address": "高新路", "District": "中国大陆", "Latitude": 0, "Province": "河南省", "Longitude": 0}

 

第一行数据被过滤出

 {"City": "洛阳市", "Address": "郑州高新路", "District": "中国大陆", "Latitude": 0, "Province": "河南省", "Longitude": 0}

也会被过滤出

 

SELECT JSON_EXTRACT(JsonLocation,'$.City')
FROM t
WHERE 1;

返回City的值

SELECT *  FROM t WHERE JSON_EXTRACT(JsonLocation,'$.City') LIKE "%郑州%";

模糊过滤出City值包含“郑州”的。

 注意，如果某些行Json中不包含City的键不会报错。

 

 

SELECT *  FROM t  WHERE JSON_EXTRACT(JsonGroupSet,'$') LIKE "%36%";

第一行被过滤出

 `JsonGroupSet` JSON NULL DEFAULT NULL COMMENT '组',

[1, 2, 36, 4]

[1, 2, 3, 4]

精确查询

 [1, 2, 36, 41]

[1, 2, 36, 4]

JSON_CONTAINS(JsonGroupSet,JSON_ARRAY(36,41));

 

 

{

    "Device": {

        "Id": 1225,

        "SN": "ABC123"

    },

    "Creator": {

        "Id": 3,

        "Name": "test"

    },

    "CreateTs": 1675789289,

    "DeviceModel": {

        "Id": 1,

        "Name": "TEST-4000"

    }

}

 

        JSON_EXTRACT(SnapshotInfo,'$.Device.SN') = "ABC123"

 

 

清空数据库

 

SELECT CONCAT('TRUNCATE  TABLE',TABLE_NAME,';') AS a FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbx' ;

 

 

sql group 结果合并到一行

SELECT
( SELECT UNIX_TIMESTAMP(MAX(lastExeTime)) FROM device ),
( SELECT COUNT(1) FROM device WHERE online=1 ),
( SELECT COUNT(1) FROM device WHERE online=0 );

 

 

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 =1
union all
select count(*) from db_mining.miner_movers_shakers_us where top_count =2
union all
select count(*) from db_mining.miner_movers_shakers_us where top_count not in (1,2)

 

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, 1 row affected (0.01 sec)

Query OK, 0 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 1146 (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 1146 (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, 1 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  导入数据库

 

实践 跨库表复制

将db0的中tab1中的部分数据复制到db1中的tab1.

 

DROP TABLE IF EXISTS db0.tab1 ;
CREATE TABLE db0.tab1 LIKE db1.tab1;
INSERT INTO db0.tab1 SELECT * FROM db1.tab1 WHERE comments="x项目";

 

 

 

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 '4';

 

 

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)

 

 

 获取昨日最多点赞的文章  SELECT  UNIX_TIMESTAMP( date_sub(curdate(),interval 1 DAY))- UNIX_TIMESTAMP( date_add(curdate(),INTERVAL 0 DAY))  mysql日期运算 

SELECT id FROM pgc_article WHERE deleted=0 AND biz_index IN (
SELECT biz_index FROM pgc_customer_behavior WHERE deleted=0 AND create_ts>=UNIX_TIMESTAMP( date_sub(curdate(),interval 1 DAY)) AND create_ts<UNIX_TIMESTAMP( date_add(curdate(),INTERVAL 0 DAY))
)

 

 

SELECT  id FROM orders WHERE GoodsId=(SELECT id FROM goods WHERE BizID="biz123");

 

1.17 16：20 - 1.20 20：00区间 每日新增用户数

SELECT COUNT(1) AS c, date_format(date_time, '%y%m%d') AS t FROM user
WHERE LENGTH(weixin_unionid)>0 AND date_format(date_time, '%y%m%d%H%i')>=2101171620 AND date_format(date_time, '%y%m%d%H%i')<2101202000 GROUP BY t ORDER BY t DESC ;

 

同比每日22点有效执行任务数、比率

SELECT tz.tia,tz.ca,tz.cb,tz.ca/tz.cb FROM (

SELECT * FROM ( SELECT COUNT(1) AS ca , date_format(date_time, '%y%m%d%H') AS tia FROM job WHERE ok=1 AND date_format(date_time, '%H')='22' GROUP BY date_format(date_time, '%y%m%d%H') ) AS ta
LEFT JOIN
( SELECT COUNT(1) AS cb , date_format(date_time, '%y%m%d%H') AS tib FROM job WHERE 1 AND date_format(date_time, '%H')='22' GROUP BY date_format(date_time, '%y%m%d%H') ) AS tb
ON ta.tia=tb.tib

)

AS tz ORDER BY tz.tia DESC;

 

 

 删除所有表 全部表

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';')
FROM information_schema.tables
WHERE table_schema = 'testdb';

 

 

 

搜索

复制