MySQL-使用教程
2016-07-02
本博客所有文章采用的授权方式为 自由转载-非商用-非衍生-保持署名 ,转载请务必注明出处,谢谢。
声明: 本博客欢迎转发,但请保留原作者信息!
github地址:atanx
新浪微博:@蜀山掌门V
QQ:365039667
博客地址:江斌的博客
内容仅供学习参考,如有不当引用,请告知博主。
[toc]
基础操作
CURD
增: create
drop table if exists address_book;
删:delete
,drop
, truncate
-- 删除记录
delete from address_book where id=12;
-- 删除表
drop table if exists address_book;
--清空表
truncate table address_book;
改: update
, alter
-- 简单update语句、通过关联update
update theTable set name='hello' where name='world';
update tableA, tableB set tableA.name=tableB.name where tableA.tableB_id=tableB.id;
-- 新增、删除一列
alter table `tableName` Add gateWayId int not null default 0 after `RegionId`;
alter table `tableName` Drop column gateWayId;
查: select
, show
show databases;
show tables;
select * from theTable;
常用函数
concat
GROUP_CONCAT
CONCAT
CONCAT_WS
group_concat示例
-- 查询指定项目重复的号码
SELECT
phone,
count(*) as repeatTime,
group_concat(project_name SEPARATOR ';') as project
FROM (select d.*, p.project_name from dianxiao_detail d
LEFT JOIN dianxiao_project p
on d.project_id = p.id) t
WHERE phone IN (SELECT phone
FROM dianxiao_detail
WHERE project_id = 1002730)
GROUP BY phone
HAVING COUNT(*) > 1;
临时表
CREATE TEMPORARY TABLE repeatToday (
SELECT *
FROM testTable);
高级操作
变量
/*
计算上周一和上周日两天分别是第几周
@sd 起始日期
@ed 结束日期
*/
set @sd = CURDATE() + INTERVAL -7-(DAYOFWEEK(CURDATE())-2) DAY;
set @ed = CURDATE() + INTERVAL -1-(DAYOFWEEK(CURDATE())-2) DAY;
select date_format(@sd , '%Yw%u'), date_format(@ed, '%Yw%u')
定时任务/事件
DELIMITER的作用是将 | | 之间的代码块作为一块代码。 ```sql DROP EVENT IF EXISTS create_tmp_diujuzi_t;
DELIMITER | CREATE EVENT create_tmp_diujuzi_t ON SCHEDULE EVERY 30 minute STARTS TIMESTAMP ‘2018-09-18 01:00:00’ ON COMPLETION PRESERVE DO BEGIN drop table if EXISTS tmp_diujuzi_t; create table tmp_diujuzi_t select * from mp_diujuzi_v; drop table if EXISTS tmp_diubang_t; create table tmp_diubang_t select * from mp_diubang_v; END | delimiter ;
## 分表
## 分区
### 新增分区
四种分区方式:`Range`, `List`, `Hash`, `Key`
```SQL
-- Range
CREATE TABLE `range` (
id INT(11),
money INT(11) UNSIGNED NOT NULL,
date DATETIME
)PARTITION BY RANGE (year(date)) (
PARTITION p2007 VALUES LESS THAN (2008),
PARTITION p2008 VALUES LESS THAN (2009),
PARTITION p2009 VALUES LESS THAN (2010),
PARTITION p2010 VALUES LESS THAN MAXVALUE
);
-- List
CREATE TABLE list (
a INT(11),
b INT(11)
)PARTITION BY LIST (b) (
PARTITION p0 VALUES IN (1, 3, 5, 7, 9),
PARTITION p1 VALUES IN (2, 4, 6, 8, 0)
);
-- Hash
CREATE TABLE hash (
a INT(11),
b DATETIME
)PARTITION BY HASH (YEAR(b))
PARTITIONS 4;
-- Key
CREATE TABLE t_key (
a INT(11),
b DATETIME
)
PARTITION BY KEY (b)
PARTITIONS 4;
增删分区
ALTER TABLE sale_data
ADD PARTITION (PARTITION p201010 VALUES LESS THAN (201011));
合并分区
ALTER TABLE sale_data
REORGANIZE PARTITION p201001, p201002, p201003,
p201004, p201005, p201006,
p201007, p201008, p201009 INTO
(
PARTITION p2010Q1 VALUES LESS THAN (201004),
PARTITION p2010Q2 VALUES LESS THAN (201007),
PARTITION p2010Q3 VALUES LESS THAN (201010)
);
自定义函数
DROP FUNCTION IF EXISTS dmp.mysin;
CREATE DEFINER=`root`@`%` FUNCTION `mysin`(`getCount` double) RETURNS double
DETERMINISTIC
BEGIN
DECLARE result DOUBLE;
set result = sin(getCount);
RETURN result;
END;
存储过程
show,kill
# 查询数据库中的表
show tables from db_name;
select tables from information_schema where db = "db_name";
# 查询线程
show processlist from db_name;
select * from infomation_schema.processlist where db = 'db_name';
实例-数据去重只保留一条
delete from slogan_t
where id in
(
select id from(
select id,
title,
@title:=s.title,
if(@title = s.title, @rank:=@rank+1, @rank:=1) as rank
from (select *
from slogan_t
order by title
) s,
(select @rank:=0,
@title:=null
)p
)t
where rank > 1
);