mysql语句使用实例_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;