MySQL语句实例

字符串条件查询要加引号

导入数据库或数据表 source /root/admin.sql 或mysql -uroot databasefoo < foo.sql
导出数据库或数据表 mysqldump -u 用户名 -p 数据库名 数据表名 > 导出的文件名和路径
如:mysqldump -u root -p gctdb > F:\apache-tomcat-6.0.20\logs\news.sql (输入后会让你输入进入MySQL的密码)

select UNIX_TIMESTAMP('2016-12-04 16:30:28') -- 把时间转换成时间戳
select FROM_UNIXTIME('1480581161',"%Y/%m/%d") -- 时间戳转换成时间

select time_to_sec('01:00:05'); -- 时间转换成秒 3605
select sec_to_time(3605); -- 秒转换成时间 '01:00:05'

select to_days('2008-08-08'); -- 日期转换成天数 733627
select from_days(733627); -- 天数转换成日期 '2008-08-08'

select date_format(now(),'%Y/%m/%d') -- 日期转化为字符串
select time_format('22:23:01', '%H.%i.%s'); -- 字符串转化为时间
select str_to_date('12.12.2016 14:09:30', '%m.%d.%Y %H:%i:%s'); 字符串转换为日期 2016-12-12 14:09:30

查看sql使用情况
cat /data/web/app/runtime/os/logs/web_sql.log|grep SELECT

触发器
创建触发器，users表每添加一条数据后logs表也添加一条数据：
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函数可以拼接字符串
set s3 = NEW.add_time;
insert into logs(log_content,add_time) values(s2,s3);
end

image.png

删除触发器：drop trigger user_log;

外键

为已经添加好的数据表添加外键：

语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名)；

例: 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;

把外键约束增加事件触发限制（就是主表删，外键表跟着删）：alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;

group_by无法使用的解决办法

进入mysql

select @@sql_mode

复制 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为条件查询 where is null

设置主键自增初始值

alter table tableName auto_increment=2

新增字段

ALTER TABLE jh_promotion_coupons ADD category_ids varchar(125) COMMENT '商品分类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 '活动条件 1没有条件 2当订单总金额达到';

更新字段

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);

加主关键字的索引

alter table tablename add primary key(id);

加唯一限制条件的索引

alter table tablename add unique emp_name2(cardnumber);

删除某个索引

alter table tablename drop index emp_name;

多条件查询
SELECT * FROM jh_country
WHERE pid = '7'
AND level = '3'
AND (
zh_name LIKE '%西%' ESCAPE '!'
OR name LIKE '%西%' ESCAPE '!'
)
LIMIT 10

清空数据表 truncate tablename

in查询里面不用加单引号

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字段长度倒叙排列查询100条

SELECT * FROM jh_store ORDER BY LENGTH(title) DESC LIMIT 100;

按title字段最左边的三个字符查询10条

SELECT LEFT(title,3) FROM jh_store LIMIT 10;

去掉重复的

SELECT DISTINCT LEFT(title,10) FROM jh_store LIMIT 10;

去重再统计

SELECT COUNT(DISTINCT LEFT(title,10)) FROM jh_store;

两个语句相除

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)
);

两表联查的另一种写法

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 查询
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);

强制使用索引
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;

问题：goods表有1000 0000条数据，统计id>=1000的有多少条怎么查最快？？
select((select count() from jh_store_goods)-(select count() from jh_store_goods where store_goods_id<=1000));

UNION 操作符用于合并两个或多个 SELECT 语句的结果集
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

分页优化
原则：
1 不允许翻过太高的页码数
2 不用limit,用条件查、

SELECT * FROM jh_store_goods LIMIT 8000, 5;

改成
SELECT * FROM jh_store_goods where store_goods_id>8000 LIMIT 5;
缺点，id不能缺（不能断，否则查的不准）办法，不删除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;

显示查询记录和时间
set profiling=1;开启
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

子查询
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一直替代查询，比直接join更好
goods = "select good_id,goods_name from goods where store_id=".$store_id;

查询时按每天的日期去重再统计
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;