国产xxxx99真实实拍_久久不雅视频_高清韩国a级特黄毛片_嗯老师别我我受不了了小说

資訊專欄INFORMATION COLUMN

mysql記錄

zsirfs / 1368人閱讀

摘要：富士康小心，前方有坑返回的結果默認是無序的，即不會按照距離遠近進行排序。需要手動加上進行排序。

1、查找表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷

select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)

2、刪除表中多余的重復記錄，重復記錄是根據單個字段（peopleId）來判斷，只留有一個記錄

delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and min(id) not in (select id from people group by peopleId having count(peopleId )>1)

3、查找表中多余的重復記錄（多個字段）

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)

4、刪除表中多余的重復記錄（多個字段），只留有rowid最小的記錄

delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

5、查找表中多余的重復記錄（多個字段），不包含rowid最小的記錄

select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)

6.mysql delete刪除關聯多表數據

DELETE reviews,reviews_description FROM reviews LEFT JOIN reviews_description ON reviews.reviews_id=reviews_description.reviews_id WHERE reviews.status="0"

7.mysql delete in操作

delete from sns_hits where id not in （select id from another_table）
delete from sns_hits where id not in (select id from sns_hits)# error
CREATE TEMPORARY TABLE tmp_sns_hits ( `id` BIGINT(20) )
DELETE   FROM t_tag
WHERE id NOT IN (SELECT * FROM (SELECT MAX(t .id) AS id FROM t_tag t GROUP BY tagname)t)

8.一對多查詢

select ID from 表2 where (select CONCAT(",", ksort , ",")AS ksort from 表1 ) LIKE CONCAT("%,", ID , ",%");
from [Mysql刪除重復記錄](http://blog.baitongda.cn/article/119)

9.group by先排序后分組，group by默認的是第一條記錄，相同的iID的記錄不會先進行排序再group by
select from (select from t where 你的查詢條件 order by 你的排序字段) group by 你的分組字段
10.mysql case when

SELECT count(enq_id) AS total, sum(purchase_amount) AS purchase
FROM temp_stock
WHERE purchase_date <> "0000-00-00"
AND purchase_date < "2012-08-01"
AND (    STATUS = "Sold"
      OR STATUS = "In Stock"
      OR STATUS = "Ref")
AND CASE STATUS 
         WHEN "Sold" 
         THEN delivery_date >= "2012-08-01"
         ELSE 1=1
    END
SELECT *
FROM logs
WHERE pw="correct"
  AND CASE
          WHEN id<800 THEN success=1
          ELSE 1=1
      END
  AND YEAR(TIMESTAMP)=2011
WHERE
  pw="correct"
  AND (id>=800 OR success=1) 
  AND YEAR(timestamp)=2011

WHERE
  pw="correct"
  AND CASE WHEN id<800 THEN success=1 ELSE TRUE END 
  AND YEAR(timestamp)=2011

mysql where case when

--簡單Case函數 
CASE sex 
         WHEN "1" THEN "男" 
         WHEN "2" THEN "女" 
ELSE "其他" END 
--Case搜索函數 
CASE WHEN sex = "1" THEN "男" 
         WHEN sex = "2" THEN "女" 
ELSE "其他" END

mysql 排序去重 sql 寫法

| data_id | user_id | data_name |hits 
| 1 | 2 | test1 | 140 
| 2 | 2 | test2 | 200 
| 3 | 3 | test2 | 110 
| 4 | 3 | test2 | 10 
| 5 | 1 | test2 | 130 
| 6 | 4 | test2 | 10 
| 7 | 4 | test2 | 100 

查詢出來下面的 3 條結果，也就是前 3 條點擊量最多的數據， user_id 不能重復
SELECT
    t.*
FROM
    (
        SELECT
            user_id,
            max(hits) AS max_hits
        FROM
            t
        GROUP BY
            user_id
    ) t2
LEFT JOIN t ON t.user_id = t2.user_id
AND t.hits = t2.max_hits
ORDER BY
    t2.max_hits DESC
LIMIT 3

| data_id | user_id | data_name |hits 
| 2 | 2 | test2 | 200 
| 5 | 1 | test2 | 130 
| 3 | 3 | test2 | 110

order by 出現數據錯誤問題

select `stock`.*, `stock1`.`price_current` as `price_current1`, `stock1`.`rank` as `rank1`, `stock`.`price_current` - `stock1`.`price_current` as `percent1` from `stock` inner join `stock` as `stock1` on `stock`.`code` = `stock1`.`code` and `stock`.`date` = "2016-10-10" and `stock1`.`date` = "2016-09-30" and `stock`.`code` = "600817" order by `percent1` descG

price_current 這個字段設置了 UNSIGNED ，用 order by 會生成臨時表，而臨時表也就會同樣是 UNSIGNED ，這就導致所有percent1為負值的都變成 0.00 了

MYsql 去重復語句

delete from `xxx` where id in (select * from (SELECT min(id) FROM `xxx` group by mail having count(mail)>1) as a); 

create table tmp_xxx select min(id) as id,mail,password from xxx group by mail,password; 

只保留一條不重復數據
DELETE T FROM MYTABLE T LEFT JOIN (SELECT MAX(A.ID) AS ID FROM MYTABLE A GROUP BY A.MAIL) TT ON T.ID = TT.ID WHERE TT.ID IS NULL
刪除重復數據中的一條
DELETE T FROM MYTABLE T LEFT JOIN (SELECT MIN(A.ID) AS ID FROM MYTABLE A GROUP BY A.MAIL HAVING COUNT(A.MAIL) > 1) TT ON T.ID = TT.ID WHERE TT.ID IS NOT NULL 
MySQL 唯一索引和插入重復自動更新

INSERT INTO table (id, user_id, token) VALUES (NULL, "2479031", "232") ON DUPLICATE KEY UPDATE user_id = VALUES(user_id), token = VALUES(token), online = VALUES(online)
 user_id 是唯一索引字段，如果 insert 的時候該 user_id 已經存在，那么就將觸發更新而不是插入，此時相當于執行了
update table set user_id = 2479031 token = 232 where user_id = 2479031
mysql導入大批量數據出現MySQL server has gone away的解決方法
http://blog.csdn.net/fdipzone/article/details/51974165 
source /tmp/user.sql
mysql> show global variables like "max_allowed_packet";
+--------------------+---------+
| Variable_name      | Value   |
+--------------------+---------+
| max_allowed_packet | 4194304 |
+--------------------+---------+
mysql> set global max_allowed_packet=268435456;
使用set global命令修改 max_allowed_packet 的值，重啟mysql后會失效，還原為默認值。

如果想重啟后不還原，可以打開 my.cnf 文件，添加 max_allowed_packet = 256M 即可

查看當前使用的配置文件my.cnf的方法
locate my.cnf
mysql啟動時加載這個配置文件
ps aux|grep mysql|grep "my.cnf"
如果沒有設置使用指定目錄的my.cnf，mysql啟動時會讀取安裝目錄根目錄及默認目錄下的my.cnf文件。
mysql --help|grep "my.cnf"
在mysql默認讀取的目錄中，創建一個my.cnf文件(例如:/etc/my.cnf)，把需要修改的配置內容寫入，重啟mysql后即可生效
You can"t specify target table for update in FROM clause錯誤的解決方法 不能在同一個sql語句中，先select同一個表的某些值，然后再update這個表

mysql> update message set content="Hello World" where id in(select min(id) from message group by uid);
解決方法：select的結果再通過一個中間表select多一次，就可以避免這個錯誤


update message set content="Hello World" where id in( select min_id from ( select min(id) as min_id from message group by uid) as a );

Mysql select 中嵌套帶子句https://www.v2ex.com/t/318573
SELECT
    tt.*,u.name,(SELECT group_concat(title) FROM tag WHERE id IN (tt.tag)) as tag
FROM content tt
LEFT JOIN user u ON tt.owner=u.id
ORDER BY ts_created DESC

SELECT
    tt.*,u.name,(SELECT group_concat(title) FROM tag WHERE FIND_IN_SET(id,tt.tag)) as tag
FROM content tt
LEFT JOIN user u ON tt.owner=u.id
ORDER BY ts_created DESC

分組
select p.id,p.productname,p.productcode,p.price,l.num from p 
left join (select pid, sum(num) as num from wlms_stock group by pid) l on p.id = l.pid 從表2中獲得每個pid的總數sum(num),然后用pid跟表1去連接,這樣就既能獲得表1的所有數據也能獲得表2中的總數了https://segmentfault.com/q/1010000005941808

獲取 用戶最近10條文章 和 回復的文章https://segmentfault.com/q/1010000007169518
(SELECT id,title,create_time,"article" as type FROM fb_articles WHERE user_id = 4)
UNION
(SELECT a.id,a.title,b.create_time,"reply" as type FROM fb_articles as a,fb_replys as b WHERE a.id = b.article_id AND b.user_id = 4)
ORDER BY
create_time
DESC
LIMIT 10
SELECT * FROM(SELECT user_id,title,create_time FROM article UNION ALL SELECT user_id,article_id,create_time FROM reply) ar WHERE ar.user_id=xxx ORDER BY create_time desc LIMIT 10

MySQL 中將 varchar 字段轉換成數字進行排序
將 attr_price 轉換為數字再進行排序, *1 即可

"ORDER BY a.sort_order, g.attr_price * 1, g.goods_attr_id"; 
 order by cast(col as unsigned)這會把小數位去掉，不合理

查詢并更新到另個表的字段update 表 a inner join   
表1 b  set a.Nums =  b.rcmd_count 
where a.id = b.id
數據庫返回的整型數據被偷換成了字符串類型

問題出在了 PHP 的 MySQL 驅動上 http://stackoverflow.com/ques...
http://stackoverflow.com/ques...
更新 PHP 的 MySQL 驅動
sudo apt-get update
sudo apt-get install php5-mysqlnd

MySQL 慢查詢定位 
編輯 my.cnf, 注意，需置于 mysqld 的 section 下

log_slow_queries = /tmp/mysql-slow.log
long_query_time = 2
重啟 mysql.
只有 insert, update, delete 慢
set profiling=1;
SHOW PROFILES;
show profile for query 1;
使用 Redis GeoHash 實現附近的XXX 
這里 sicily 類似山東省，而 Palermo, Catania 類似煙臺、威海，即 Palermo、Catania 是 sicily 轄區內的城市。
redis> GEOADD Sicily 13.361389 38.115556 "Palermo" 15.087269 37.502669 "Catania"
(integer) 2
redis> GEODIST Sicily Palermo Catania
"166274.15156960039"
redis> GEORADIUS Sicily 15 37 100 km
1) "Catania"
redis> GEORADIUS Sicily 15 37 200 km
1) "Palermo"

添加時，經度在前，維度在后。

GEORADIUSBYMEMBER yeda "富士康" 1 km
小心，前方有坑! 返回的結果默認是無序的，即不會按照距離遠近進行排序。需要手動加上 ASC, DESC 進行排序。

GEORADIUSBYMEMBER yeda "富士康" 10 km WITHDIST ASC
如何在 Laravel 中使用

composer require predis/predis

$items = Redis::georadius("yeda", $lng, $lat, 20, "km", "WITHDIST", "ASC");
2) "Catania"
MySQL 崩潰導致 Discuz! Database Error (2002) notconnect 錯誤

innodb_buffer_pool_size 的默認值，在 5.5 之后就被調成了 128M, 參考最新的 mysql 5.5, 5.6 文檔
在 my.conf 中將這個 buffer 調小

innodb_buffer_pool_size=32M

Redis監控方法

redis-cli info
獲取Redis所有Key的方法：

1.終端

獲取所有Key命令：redis-cli keys ‘*’ ；

獲取指定前綴的key：redis-cli KEYS “edu:*”

如果需要導出，可以redis-cli keys ‘*’ > /data/redis_key.txt

刪除指定前綴的Key redis-cli KEYS “edu:*” | xargs redis-cli DEL

2.PHP獲取Redis所有Key

獲取Redis所有Key：$keys = $redis->keys(‘*’);

獲取指定前綴的Key：$keys = $redis->keys(‘edu*’);

刪除指定前綴的Key ：$redis->delete($redis->keys(‘image*’));

mysql遞歸查詢 
https://segmentfault.com/q/1010000007523914
select id,name,pid from (select *from user order by pid,id) a,(select @pv := "8") init where (find_in_set(pid,@pv)>0) and @pv := concat(@pv,",",id) or id = 8;
mysql> delimiter //
mysql>
mysql> CREATE FUNCTION `getChildLst`(rootId INT)
    -> RETURNS varchar(1000)
    -> BEGIN
    ->   DECLARE sTemp VARCHAR(1000);
    ->   DECLARE sTempChd VARCHAR(1000);
    ->
    ->   SET sTemp = "$";
    ->   SET sTempChd =cast(rootId as CHAR);
    ->
    ->   WHILE sTempChd is not null DO
    ->     SET sTemp = concat(sTemp,",",sTempChd);
    ->     SELECT group_concat(id) INTO sTempChd FROM treeNodes where FIND_IN_SET(pid,sTempChd)>0;
    ->   END WHILE;
    ->   RETURN sTemp;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> select * from treeNodes
    -> where FIND_IN_SET(id, getChildLst(1));
+----+----------+------+
| id | nodename | pid  |
+----+----------+------+
|  1 | A        |    0 |
|  2 | B        |    1 |
|  3 | C        |    1 |
|  4 | D        |    2 |
|  5 | E        |    2 |
|  6 | F        |    3 |
|  7 | G        |    6 |
+----+----------+------+
7 rows in set (0.01 sec)
mysql分組 
$res = ["2016-11-18","2016-11-19"];

$s=implode("","",$res);//"2016-11-18","2016-11-19"
echo """.$s.""";//""2016-11-18","2016-11-19""
$sql="select date_format(created,"%Y-%m-%d") as date,count(*) from test where date_format(created,"%Y-%m-%d") in ("".$s."") group by date order by id desc limit 5;"
mysql優化查詢速度

select id from t where num=10 or num=20

select id from t where num=10
union all
select id from t where num=20
select num from a where num in(select num from b)
select num from a where exists(select 1 from b where num=a.num)

MySQL日志查詢分析工具pt-query-digest

直接分析慢查詢文件
pt-query-digest slow.log > slow_report.log
分析最近12小時內的查詢
pt-query-digest --since=12h slow.log > slow_report2.log

批量更新 
update table_name set field_name = CASE id 

WHEN id1 THEN  field_value,

WHEN id1 THEN  field_value

END
一個 MySQL 用戶名長度的坑

MySQL user names can be up to 32 characters long (16 characters before MySQL 5.7.8). http://dev.mysql.com/doc/refm...
grant all privileges on . to joe@10.163.225.87 identified by ‘123′;

Mysql FIND_IN_SET 語句原始排序

find_in_set單條記錄沒問題，比如唯一id的in。多條記錄order會失效select * from act_log where answer in ("B","C","CD") order by find_in_set( answer,""B","C","CD"")
排序錯誤：CD,C,BB,C
select * from tb1 order by a desc ,id desc limit 3; 用limit和order by 一個非唯一字段時，結果集并不總是確定的.已經確定為bughttp://www.codesec.net/view/2...
select SUM(fee) AS income, SUM(IF(type=5, fee, 0)) AS reward

mysql 如何實現先排序后分組

1、可以先把數據按照你的條件要求，使用order by排好序
2、在查詢sql外面，再包一層查詢，使用group by
例如： select from (select from t where 你的查詢條件 order by 你的排序字段) group by 你的分組字段

MySQL中varchar最大長度是多少

（1）單個字段如果大于65535，則轉換為TEXT 。

（2）單行最大限制為65535，這里不包括TEXT、BLOB

查詢一個月當中每個人每天的第一次和最后一次記錄

SELECT MIN(card_time) AS mintime, MAX(card_time) AS maxtime , user_sn, card_date FROM testtbl GROUP BY user_sn, card_date

數據庫去除重復記錄

insert into xxxx (select xxx from xxxx)
然后再 delete from xxx where id in (select xxx from xxxx)

alter table 鎖表

新建表，數據復制到新表然后 rename 切換 http://www.cnblogs.com/wangta...

按ID降序排序，如果用戶狀態為0(未激活)，則注冊時間升序，排在結果最后

select *from user order by status desc,case status when 1 then id end desc,case status when 0 then created_at end asc;

過濾 三個字段 有重復的記錄

select * FROM tt t1, tt t2 WHERE t1.userid = t2.userid and t1.pid = t2.pid and t1.tid = t2.tid and s1.id != s2.id

查詢total_time > 100

SELECT *
FROM tuanke_time
LEFT JOIN tuanke_student ON tuanke_student.Sid = tuanke_time.studentID
GROUP BY tuanke_time.studentID having SUM(tuanke_time.time) > 100;

查每個用戶第一次記錄

select user,num from (select * from tb order by time asc) as a group by a.user;

刪除重復記錄 
DELETE FROM price_monitor WHERE id NOT IN (
    SELECT * FROM (
        SELECT MAX(id) FROM price_monitor  GROUP BY domain
    )
    as tmp
)
limit sum

select uid, sum(skip) as sumskip, time from (select uid, skip, time from attendance where uid = 8499 order by time limit 3) as subt;

mysql刪除大量數據,直接delete會鎖表 
DELIMITER $$
CREATE PROCEDURE delete_temp_tab()
BEGIN
    REPEAT
        DELETE FROM test.`tab` WHERE id > 111352 LIMIT 1000;
    UNTIL ROW_COUNT()=0 END REPEAT;
END $$
DELIMITER ;
CALL delete_temp_tab();
DROP PROCEDURE IF EXISTS delete_temp_tab;
mysql 分頁查詢優化 
EXPLAIN
SELECT 
    `id` 
  FROM
    `table` 
  WHERE `node` = 2
  ORDER BY `create_at` DESC 
  LIMIT 12 OFFSET 69996

#查詢1
SELECT 
    `id` 
  FROM
    `table` 
  WHERE `node` = 2
  ORDER BY `id` ASC
  LIMIT 10

查詢第二頁的SQL如下

SELECT 
    `id` 
  FROM
    `table` 
  WHERE `node` = 2 AND `id`>10
  ORDER BY `id` ASC
LIMIT 10
每次取10000 三次取完 
分塊
$sql = "SELECT xxx FROM table WHERE xxx LIMIT limit ：limit offset :offset";
$limit = 10000;
$offset = 0;
do{
    $stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
    $stmt->execute(array("：limit" => $limit, ":offset" => $offset));
    $data = $stmt->fetchAll();
    // 處理邏輯
    $offset += $limit;
}while(count($data));
游標，按行讀取返回
$sql = "SELECT xxx FROM table WHERE xxx";
$stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_LAST);
do {
    // 處理邏輯 $row
} while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_PRIOR));
正則匹配手機號前綴

SELECT name FROM users WHERE phone REGEXP "^15[1-4]";

redis setbit

setbit key offset，其中offset用userid取代，如果ID為1的用戶是男性就setbit key 1 1，
如果是女性就setbit key 1 0,獲取ID為1的性別就getbit key 1

數據遷移

在沒有索引的情況下，我會采用先delete后insert。
但在有索引的情況下，我會采用以下方式先update后insert

每半個小時的數據 
delimiter $$
CREATE PROCEDURE test()  
begin
    declare begintime int(10);
    set begintime =  unix_timestamp("2016-7-31 23:59:59");
    loop1:LOOP
    IF begintime > unix_timestamp("2016-9-30 23:59:59") then
         leave loop1;
     END IF;
        select * from tablename where timestamp between begintime and begintime+1800;
        set begintime = begintime + 1800;
    END LOOP loop1;
end;$$
子查詢不支持limit 
SELECT * FROM yi_user_joke WHERE id in (SELECT id FROM yi_user_joke WHERE status="2" ORDER BY audit_time desc LIMIT 499950,10 ) ORDER BY audit_time desc LIMIT 10 ;

ERROR 1235 (42000): This version of MySQL doesn"t yet support "LIMIT & IN/ALL/ANY/SOME subquery"

SELECT * FROM yi_user_joke a inner join (SELECT id FROM yi_user_joke WHERE status="2" ORDER BY audit_time desc LIMIT 499950,10 ) as b on a.id=b.id ORDER BY a.audit_time desc LIMIT 10 ;
SELECT * FROM yi_user_joke WHERE id in (select *from (SELECT id FROM yi_user_joke WHERE status="2" ORDER BY audit_time desc LIMIT 499950,10 ) as b) ORDER BY audit_time desc LIMIT 10 ;
分組取前五 
select * from table as a where (select count(distinct(total)) from table as b where a.stage_id = b.stage_id and b.total > a.total) < 5;where中的select是保證遍歷所有記錄，取每條記錄與當前記錄做比較，只有當table表中同一stage_id不超過5個人total當前選擇item的total高時，這個item就算是每組total排行的前5名
查詢每個部門工資前3名

select * from table d1 where (select count(*) from table d2 where d2.bumen=d1.bumen and d2.salary>d1.salary) < 3 order by d1.salary desc

從一個表中選出最后幾個元素 
SELECT * FROM (
    SELECT * FROM tmp_table ORDER BY id DESC LIMIT 50
    ) sub
    ORDER BY id ASC
選出分組求和的平均數SELECT AVG(sum_column1)
    FROM (SELECT SUM(column1) AS sum_column1)
        FROM t1 GROUP BY column1) AS t1;
group by 
group by 是用于分組統計的，會按照分組字段進行排序，如果 order by 和 group by 同時出現，order by 是對 group by 的結果排序，因此取的是根據 group by 排序后各組第一條，但這是有邏輯錯誤的，好像只有在 MySQL 中可行。 

這句 SQL 就好比一個班級的女生按照寢室分組，然后你想知道分組的結果屬于誰？ 其實誰都不屬于，但你可以得到每個組中屬性的最大值，最小值，或者具有分組統計意義的信息。 從 MySQL 5.7.5 起，SELECT 不在 GROUP BY 中的字段將會導致數據庫拒絕執行查詢
order by 只有后面跟著 limit 在子查詢才有意義 
select * from (select * from table order by <字段 2> limit 1000) as temp group by <字段 1>

文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。

轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/21907.html

相關文章

發表評論

0條評論

最新活動
閱讀需要支付1元查看
<