MySQL8 CRM Schema Design Expert
下载
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
本文记录了mysql的一些常用操作，不多说了，直接一一列出：
/*
Author: liuyazhuang
Date:2016-04-12
*/
use test;
DROP table if exists equipment;
create table equipment(
assetTag INTEGER primary key,
description varchar(20),
acquired Date
);
/*数据表插入语句*/
INSERT INTO equipment values (50431,'21寸监视器', DATE '2013-01-23');
INSERT INTO equipment values (50532,'pad', DATE '2013-01-26');
/*数据表增加新的列，并初始化*/
ALTER Table equipment ADD COLUMN office VARCHAR(20);
UPDATE equipment SET office='Headquarters';
/*向修改后的表中插入记录*/
INSERT INTO equipment(assetTag,description,acquired) values (50432,'IBM-PC',DATE '2013-01-01');
/*创建代替表的新表（复制）*/
DROP TABLE if EXISTS equipmentMultiSite;
CREATE TABLE equipmentMultiSite(
assetTag INTEGER PRIMARY KEY,
office varchar(20) DEFAULT 'Headquarters',
description varchar(20),
acquired DATE
);
INSERT INTO equipmentMultiSite
SELECT assetTag,'Headquarters',description,acquired FROM equipment;
/*删除现有equipment用视图代替*/
DROP TABLE equipment;
CREATE VIEW equipment AS
SELECT assetTag,description,acquired 'dateAcquired',
FROM equipment WHERE office='Headquarters';
/*基础的查询*/
select sum(amount) from payment where customer_id=1; /*某顾客从开户以来的消费总额*/
select count(*) from payment where customer_id=1; /*某顾客从开户以来的消费次数*/
/*查询某顾客在2005年5月份和6月份消费总额,用"between”或者""来建立条件*/
select sum(amount) from payment where customer_id=1 and extract(year from payment_date)=2005 and extract(month from payment_date) between 05 and 06;
select sum(amount) from payment where customer_id=1 and payment_date >= '2005-05-01 00:00:00' and payment_date UNIX_TIMESTAMP('2005-05-01 00:00:00') and payment_date
create view v_tmp_user as select * from tmp_user;
ERROR 1352 (HY000): View's SELECT refers to a temporary table 'tmp_user'
*/
/*创建预处理语句*/
create view pay_view as
select sum(amount) from payment where extract(year from payment_date)=2005;
/*mysql中连接字符串用concat函数，||仅作逻辑运算用*/
create view pay_view as
select concat(c.first_name,' ',c.last_name) as name, sum(p.amount) as amount from payment_copy As p,customer As c
where extract(year from p.payment_date)=2005 and p.customer_id=c.customer_id group by p.customer_id;
select * from pay_view;
/*输出前10条数据*/
select * from pay_view limit 10;
/*输出第11条到20条数据*/
select * from pay_view limit 11,20;
/*随机抽取5条数据,order by rand()*/
select * from pay_view order by rand() limit 5;
drop view pay_view;
/*不能给视图添加索引，只能在基本表上添加索引*/
/*create index pay_view_index on pay_view(amount);*/
/*ERROR 1347 (HY000): 'test.pay_view' is not BASE TABLE*/
/*特别注意：如果视图和基本表一一对应，视图的更新可以达到同步修改基本表的目的；如果进行抽取，运算等操作得到视图，对视图的操作不能同步到
基本表，视图中数据和基本表中的数据不一致，视图中的数据在内存中，做临时显示使用，有必要时才将数据同步到基本表*/
/*事务，mysql中默认每个sql语句是一个事务，就自动提交一次。考虑到性能，多个语句放在一个事务块中*/
begin
drop view pay_view;
create view pay_view as
select concat(c.first_name,' ',c.last_name) as name, sum(p.amount) as amount from payment_copy As p,customer As c
where extract(year from p.payment_date)=2005 and p.customer_id=c.customer_id group by p.customer_id;
select * from pay_view limit 10;
end
/*更改表的存储引擎*/
alert table payment_copy engine=InnoDB;
/*创建mysql定时执行的事件*/
set global event_scheduler=1;
create table testduty(
time varchar(20) default null
)engine=myisam default charset=latin1;
create event if not exists test_event on schedule every 10 second do insert into testduty(time) values(now());
/*删除定时任务*/
drop event test_event;
/*优化数据表*/
optimize table payment;
/*测试加入索引的性能*/
/*建立两张表：一个建立索引，另一个不建立*/
create table if not exists test_has_index(
id integer not null auto_increment,
num integer not null default 0,
d_num varchar(30) not null default '0',
primary key(id)
)engine=MyISAM default charset=utf8 auto_increment=1;
create table if not exists test_no_index(
id integer not null auto_increment,
num integer not null default 0,
primary key(id)
)engine=MyISAM default charset=utf8 auto_increment=1;
/*创建存储过程，用于初始化数据表*/
delimiter |
create procedure i_test(pa integer,tab varchar(30))
begin
declare max_num integer default 10000;
declare i integer default 0;
declare rand_num integer;
declare double_num char;
if tab!='test_no_index' then
select count(id) into max_num from test_has_index;
while i (select count(*) from t1_t2
where user_id=t.user_id and comment_counts>t.comment_counts
order by t.user_id,t.comment_counts)
order by t.user_id;
select t1.user_id,t1.blog_id from t1 inner join t2 on t1.blog_id=t2.blog_id order by counts desc;
group by t1.blog_id ;
select t1.user_id,t1.blog_id,count(t2.comment_id) as counts
from t1 inner join t2 on t1.blog_id=t2.blog_id
group by t1.blog_id
having count(t2.comment_id) where子句 联结条件和查询条件 > group by子句分组 > having子句搜索 >order by子句结果排序 > limit显示某些记录
*/
/*连接查询常用模式*/
/*1、select * from table1, table2 where table1.id=table2.id
2、select * from table1 left join table2 on table1.id = table2.id
3. select * from table1 left join table2 using(id)
4. select * from table1 left join table2 on table1.id=table2.id left join table3 on table2.id=table3.id
5. select * from table1 use index(key1,key2) where key1=1 and key2=2 and key3=3
6. slect * from table1 ignore index(key1) where key1=1 and key2=2 and key3=3
*/
/*验证各种连接结果,以film, film_actor, actor为例*/
select f.title,fa.actor from film f left join film_actor fa on f.film_id = fa.film_id limit 10;
select film.*,film_actor.* from film left join film_actor on film.film_id = film_actor.film_id limit 10;
select film.title,film_actor.actor_id from film left join film_actor on film.film_id = film_actor.film_id limit 10;
select film.title,film_actor.actor_id from film left join film_actor using(film_id) limit 10;
select film.title,film_actor.actor_id from film left join film_actor using(film_id) group by film.film_id limit 10;
select film.title,film_actor.actor_id from film inner join film_actor on film.film_id = film_actor.film_id limit 10;
/**/
/**/
/**/
/*查询中去掉重复字段*/
/*建立测试数据表school_report*/
drop table if exists school_report;
create table school_report(
id int(10) not null auto_increment comment '表ID',
u_id int(10) not null comment '学生ID',
name varchar(20) not null default '' comment '学生姓名',
score varchar(4) not null default 0 comment '学生成绩',
message varchar(50) not null default '',
dateline timestamp not null default current_timestamp,
primary key(id)
)engine=innodb default charset=utf8 auto_increment=1;
/*插入测试数据*/
insert into school_report(u_id,name,score,message)
values(1,'张三',89,'helloworld'),(1,'张三',90,'hello'),(2,'李四',92,'helloworld'),
(3,'王五',93,'world');
/*查询，去掉重复*/
select distinct name,score from school_report;
select *, count(distinct name) from school_report group by name;
/**/
select *
from school_report a inner join(
select max(dateline) as dateline
from school_report group by u_id) b
on a.dateline = b.dateline
group by id order by a.dateline desc;
/*记录和分析 花费时间较多的select*/
/*首先进行如下设置，设置long_query_time时间限*/
show variables like "%long%";
set global long_query_time=2;
/*检查并开启慢查询,会显示mysql-slow.log文件的路径*/
show variables like "%slow%";
set global slow_query_log='ON';
/*这样超过long_query_time的查询会记录到mysql日志中*/
/*分组后前n条数据: http://www.php.cn/*/
drop table if exists tb;
create table tb (
name varchar(10),
val int,
memo varchar(20)
);
insert into tb values('a', 2, 'a2(a的第二个值)'),('a',1,'a1--a第一个值'),
('a',3,'a3--a第三个值'),('b',1,'b1--b第一个值'),('b',3,'b3--b第三个值'),
('b',2,'b3--b2b2b2'),('b',4,'b4b4b4'),('b',5,'b5b5b5b5');
/*按name分组取value 最大的值的记录*/
--方法1：select a.* from tb a where val = (select max(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val > a.val);
--方法3：
select a.* from tb a,(select name,max(val) val from tb group by name) b where a.name = b.name and a.val = b.val order by a.name;
--方法4：
select a.* from tb a inner join (select name , max(val) val from tb group by name) b on a.name = b.name and a.val = b.val order by a.name ;
--方法5
select a.* from tb a where 1 > (select count(*) from tb where name = a.name and val > a.val ) order by a.name ;
/*
name val memo
---------- ----------- --------------------
a 3 a3:a的第三个值
b 5 b5b5b5b5b5
方法三、四效率比较高
*/
/*按name分组取val最小的值的记录*/
--方法1：select a.* from tb a where val = (select min(val) from tb where name = a.name) order by a.name
--方法2：
select a.* from tb a where not exists(select 1 from tb where name = a.name and val (select count(*) from tb where name = a.name and val (select count(*) from tb where name = a.name and val (select count(*) from tb where name = a.name and val > a.val ) order by a.name,a.val
select a.* from tb a where val in (select top 2 val from tb where name=a.name order by val desc) order by a.name,a.val
select a.* from tb a where exists (select count(*) from tb where name = a.name and val > a.val having Count(*)
