字符串條件查詢(xún)要加引號(hào)
導(dǎo)入數(shù)據(jù)庫(kù)或數(shù)據(jù)表 source /root/admin.sql 或mysql -uroot databasefoo < foo.sql
導(dǎo)出數(shù)據(jù)庫(kù)或數(shù)據(jù)表 mysqldump -u 用戶(hù)名 -p 數(shù)據(jù)庫(kù)名 數(shù)據(jù)表名 > 導(dǎo)出的文件名和路徑
如:mysqldump -u root -p gctdb > F:\apache-tomcat-6.0.20\logs\news.sql (輸入后會(huì)讓你輸入進(jìn)入MySQL的密碼)
select UNIX_TIMESTAMP('2016-12-04 16:30:28') -- 把時(shí)間轉(zhuǎn)換成時(shí)間戳
select FROM_UNIXTIME('1480581161',"%Y/%m/%d") -- 時(shí)間戳轉(zhuǎn)換成時(shí)間
select time_to_sec('01:00:05'); -- 時(shí)間轉(zhuǎn)換成秒 3605
select sec_to_time(3605); -- 秒轉(zhuǎn)換成時(shí)間 '01:00:05'
select to_days('2008-08-08'); -- 日期轉(zhuǎn)換成天數(shù) 733627
select from_days(733627); -- 天數(shù)轉(zhuǎn)換成日期 '2008-08-08'
select date_format(now(),'%Y/%m/%d') -- 日期轉(zhuǎn)化為字符串
select time_format('22:23:01', '%H.%i.%s'); -- 字符串轉(zhuǎn)化為時(shí)間
select str_to_date('12.12.2016 14:09:30', '%m.%d.%Y %H:%i:%s'); 字符串轉(zhuǎn)換為日期 2016-12-12 14:09:30
查看sql使用情況
cat /data/web/app/runtime/os/logs/web_sql.log|grep SELECT
觸發(fā)器
創(chuàng)建觸發(fā)器络它，users表每添加一條數(shù)據(jù)后logs表也添加一條數(shù)據(jù)：
create trigger user_log after insert on users for each row
begin
declare s1 varchar(40) character set utf8;
declare s2 varchar(20) character set utf8;
declare s3 varchar(20) character set utf8;
set s1 = ' is created';
set s2 = concat(NEW.name,s1);#concat函數(shù)可以拼接字符串
set s3 = NEW.add_time;
insert into logs(log_content,add_time) values(s2,s3);
end
刪除觸發(fā)器：drop trigger user_log;
外鍵
為已經(jīng)添加好的數(shù)據(jù)表添加外鍵：
語(yǔ)法:alter table 表名 add constraint FK_ID foreign key(你的外鍵字段名) REFERENCES 外表表名(對(duì)應(yīng)的表的主鍵字段名)洋腮；
例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id)
刪除外鍵：alter table xiaodi drop foreign key xiaodi_ibfk_1;
把外鍵約束增加事件觸發(fā)限制（就是主表刪，外鍵表跟著刪）：alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;
group_by無(wú)法使用的解決辦法
進(jìn)入mysql
select @@sql_mode
復(fù)制 STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
vim /etc/my.cnf
加入一行
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
重啟 service mysqld restart
以null為條件查詢(xún) where is null
設(shè)置主鍵自增初始值
alter table tableName auto_increment=2
新增字段
ALTER TABLE jh_promotion_coupons ADD category_ids varchar(125) COMMENT '商品分類(lèi)id';
ALTER TABLE jh_promotion_coupons ADD ratio decimal(5,2) COMMENT '折扣值';
ALTER TABLE jh_promotion_freight ADD co_id INT(1) NOT NULL DEFAULT 1 COMMENT '活動(dòng)條件 1沒(méi)有條件 2當(dāng)訂單總金額達(dá)到';
更新字段
ALTER TABLE jh_promotion_coupons MODIFY COLUMN ratio decimal(5,2) NULL DEFAULT NULL COMMENT '折扣值' AFTER category_ids;
重命名表
rename table jh_email_backip to jh_email_backipv2;
加索引
alter table tablename add index 索引名 (字段名1[沛婴，字段名2 …]);
alter table tablename add index emp_name (name);
加主關(guān)鍵字的索引
alter table tablename add primary key(id);
加唯一限制條件的索引
alter table tablename add unique emp_name2(cardnumber);
刪除某個(gè)索引
alter table tablename drop index emp_name;
多條件查詢(xún)
SELECT * FROM jh_country
WHERE pid = '7'
AND level = '3'
AND (
zh_name LIKE '%西%' ESCAPE '!'
OR name LIKE '%西%' ESCAPE '!'
)
LIMIT 10
清空數(shù)據(jù)表 truncate tablename
in查詢(xún)里面不用加單引號(hào)
SELECT * FROM jh_country_copy WHERE id in(223,222,38,81,195,73,105,221,188,129,150,171,220,99,13,138,209);
按title字段長(zhǎng)度倒敘排列查詢(xún)100條
SELECT * FROM jh_store ORDER BY LENGTH(title) DESC LIMIT 100;
按title字段最左邊的三個(gè)字符查詢(xún)10條
SELECT LEFT(title,3) FROM jh_store LIMIT 10;
去掉重復(fù)的
SELECT DISTINCT LEFT(title,10) FROM jh_store LIMIT 10;
去重再統(tǒng)計(jì)
SELECT COUNT(DISTINCT LEFT(title,10)) FROM jh_store;
兩個(gè)語(yǔ)句相除
SELECT ((SELECT COUNT(DISTINCT LEFT(title,3)) FROM jh_store) / (SELECT COUNT(*) FROM jh_store));
SELECT(
(SELECT COUNT(DISTINCT LEFT(title, 3)) FROM jh_store) / (SELECT COUNT(*) FROM jh_store)
);
兩表聯(lián)查的另一種寫(xiě)法
SELECT store_goods_id,store_id,shop_goods_name FROM jh_store_goods
INNER JOIN jh_store USING (store_id) WHERE store_id = 127;
SELECT store_goods_id,shop_goods_name,tmp.store_id FROM jh_store_goods
INNER JOIN
(SELECT store_id FROM jh_store WHERE store_id > 150) as tmp
ON jh_store_goods.store_id=tmp.store_id;（√）
兩表where in 查詢(xún)
SELECT store_goods_id,store_id,shop_goods_name FROM jh_store_goods
WHERE store_id IN
(SELECT store_id FROM jh_store WHERE store_id > 150);
強(qiáng)制使用索引
SELECT store_goods_id from jh_store_goods use index(PRIMARY) where store_id=150 order by store_goods_id desc limit 1;
等于
SELECT max(store_goods_id) from jh_store_goods where store_id=150 limit 1;
問(wèn)題：goods表有1000 0000條數(shù)據(jù)醉锄，統(tǒng)計(jì)id>=1000的有多少條怎么查最快祝谚？悠瞬？
select((select count() from jh_store_goods)-(select count() from jh_store_goods where store_goods_id<=1000));
UNION 操作符用于合并兩個(gè)或多個(gè) SELECT 語(yǔ)句的結(jié)果集
union all 不去重也不排序 盡量使用union all
SELECT store_goods_id,store_id FROM jh_store_goods where store_id=150
UNION
SELECT store_goods_id,store_id FROM jh_store_goods where store_id=151
分頁(yè)優(yōu)化
原則：
1 不允許翻過(guò)太高的頁(yè)碼數(shù)
2 不用limit,用條件查锨阿、
SELECT * FROM jh_store_goods LIMIT 8000, 5;
改成
SELECT * FROM jh_store_goods where store_goods_id>8000 LIMIT 5;
缺點(diǎn)宵睦，id不能缺（不能斷，否則查的不準(zhǔn)）辦法墅诡，不刪除id或邏輯刪除
更佳方案1壳嚎，id可以缺
SELECT * FROM jh_store_goods
INNER JOIN (SELECT store_goods_id FROM jh_store_goods LIMIT 8500,5) AS tmp
ON jh_store_goods.store_goods_id = tmp.store_goods_id;
更佳方案2
SELECT * FROM jh_store_goods
INNER JOIN (SELECT store_goods_id FROM jh_store_goods WHERE store_goods_id > 8500 LIMIT 0,5) AS tmp
ON jh_store_goods.store_goods_id = tmp.store_goods_id;
顯示查詢(xún)記錄和時(shí)間
set profiling=1;開(kāi)啟
show profiles;顯示記錄
show profile for query 5
CASE employee_color_type
when 1 then 'green'
when 2 then 'yellow'
when 3 then 'red'
ELSE 'no' END as employee_color,
count
SELECT
COUNT( CASE WHEN status =1 THEN 1 ELSE NULL END ) AS aa,
COUNT( CASE WHEN status =2 THEN 1 ELSE NULL END ) AS bb,
COUNT( CASE WHEN status =3 THEN 1 ELSE NULL END ) AS cc,
COUNT( CASE WHEN status =4 THEN 1 ELSE NULL END ) AS dd,
COUNT( CASE WHEN status =5 THEN 1 ELSE NULL END ) AS ee,
COUNT( CASE WHEN status =6 THEN 1 ELSE NULL END ) AS ff,
COUNT( CASE WHEN status =7 THEN 1 ELSE NULL END ) AS gg,
COUNT( CASE WHEN status =8 THEN 1 ELSE NULL END ) AS hh,
COUNT( CASE WHEN status =9 THEN 1 ELSE NULL END ) AS ii,
COUNT( CASE WHEN status =10 THEN 1 ELSE NULL END ) AS jj,
COUNT( CASE WHEN status =11 THEN 1 ELSE NULL END ) AS kk,
COUNT( CASE WHEN status =12 THEN 1 ELSE NULL END ) AS ll
FROM jh_order;
SELECT COUNT( CASE WHEN mother >24 THEN 1 ELSE NULL END ) AS digong, COUNT( CASE WHEN mother <=24 THEN 1 ELSE NULL END ) AS tiangong FROM prince
SELECT ( SELECT COUNT( * ) FROM prince WHERE mother >24 ) AS digong, ( SELECT COUNT( * ) FROM prince WHERE mother <=24 ) AS tiangong
FROM prince
子查詢(xún)
select sku,count(sku) as count from(
select DISTINCT order_id,sku from de_order_product where order_id in
(SELECT b.order_id FROM de_dispute a LEFT JOIN de_order b ON b.transaction_id = a.transaction_id
WHERE a.create_time >= 1533052800
AND a.create_time <= 1535731199
AND a.reason = 'MERCHANDISE_OR_SERVICE_NOT_RECEIVED'
AND b.order_id is not null)
) AS tmp
GROUP BY sku
ORDER BY count DESC
select DISTINCT order_id,DISTINCT sku,count(sku) as count from de_order_product
where order_id in
(SELECT b.order_id FROM de_dispute a LEFT JOIN de_order b ON b.transaction_id = a.transaction_id
WHERE a.create_time >= 1533052800
AND a.create_time <= 1535731199
AND a.reason = 'MERCHANDISE_OR_SERVICE_NOT_RECEIVED'
AND b.order_id is not null)
GROUP BY sku
ORDER BY count DESC
join一直替代查詢(xún)，比直接join更好
goods = "select good_id,goods_name from goods where store_id=".$store_id;
查詢(xún)時(shí)按每天的日期去重再統(tǒng)計(jì)
SELECT
count( DISTINCT tmp.shop_url) AS shop_count,
tmp.created_at AS create_time
FROM
(
SELECT
shop_url,
LEFT (created_at, 10) AS created_at
FROM
jh_shopify_order
)AS tmp
GROUP BY create_time;
