MySQL-使用教程

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