MySQL之——基本操作汇总

转载请注明出处:http://blog.csdn.net/l1028386804/article/details/51133169

本文记录了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 < '2005-07-01 00:00:00';
/*下面用法报警了*/
select sum(amount) from payment where customer_id=1 and payment_date > UNIX_TIMESTAMP('2005-05-01 00:00:00') and payment_date < UNIX_TIMESTAMP('2005-07-01 00:00:00');

/*查询某顾客在2005年一年的总消费*/
select sum(amount) from payment where customer_id=1 and extract(year from payment_date)=2005;
select sum(amount) from payment where customer_id=1 and extract(year from payment_date)='2005'; /*年份加单引号的写法也能通过，但不够简练*/  
/*针对公司，查询2005年总的销售额*/
select sum(amount) from payment where extract(year from payment_date)=2005;

/*针对公司，查询2005年总的销售额，payment_copy没有建立索引*/
select sum(amount) from payment_copy where extract(year from payment_date)=2005;

explain select sum(amount) from payment where extract(year from payment_date)=2005;

/*为payment_copy建立索引*/
create index cust_id on payment_copy(customer_id);
/*利用索引提高查询效率*/
select sum(amount) from payment_copy where customer_id=1 and extract(year from payment_date)=2005;
/*删除索引*/
drop index cust_id on payment_copy;

create index pay_date on payment(payment_date);
drop index pay_date on payment;


/*建立视图*/

/*利用视图查询*/
select title from film_list;


/*建立临时表*/
create temporary table if not exists tmp_user(
id integer not null auto_increment COMMENT '用户ID',
name varchar(20) not null default '' COMMENT '名称',
sex integer not null default 0 COMMENT '0为男，1为女',
primary key(id)
)engine=MyISAM default charset=utf8 auto_increment=1;


/*显示临时表的细节,show table显示包括table和view但不包括临时表*/
desc tmp_user;

/*mysql不支持在临时表上建立视图，会报错*/
/*create view v_tmp_user as select * from tmp_user;*/
/*
mysql> 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 < pa do
			if max_num < 10000 then
				select cast(rand()*100 as unsigned) into rand_num;
				select concat(rand_num,rand_num) into double_num;
				insert into test_has_index(num,d_num) values(rand_num,double_num);
			end if;
			set i=i+1;
		end while;
	else	
		select count(id) into max_num from test_no_index;
		while i < pa do
			if max_num < 10000 then
				select cast(rand()*100 as unsigned) into rand_num;
				insert into test_no_index(num) values(rand_num);
			end if;
			set i=i+1;
		end while;
	end if;
end |
	
delimiter ;

/*数据表中记录较少时，索引反而会影响性能*/
call i_test(10,'test_has_index'); /*call调用存储过程，并传入参数*/
select num from test_has_index where num!=0;
explain select num from test_has_index where num!=0;
/*Tips: where后的条件，order by ,group by 等这样过滤时，后面的字段最好加上索引。根据实际情况，选择PRIMARY KEY、UNIQUE、INDEX等索引，但是不是越多越好，要适度。*/

select a.num as num1, b.num as num2 
	from test_no_index as a 
		left join test_has_index as b 
			on a.num=b.num;

explain select a.num as num1, b.num as num2 
	from test_no_index as a 
		left join test_has_index as b 
			on a.num=b.num;
/*Tips:数据量特别大的时候，最好不要用联合查询，即使你做了索引??*/

/*mysql中使用外键，必须选用innodb存储引擎，myisam不支持外键*/

/*建立user、order表,二者相关联,以下也是两个表关联的典型实例*/
create table test_user(
id int(10) not null auto_increment COMMENT '用户ID',
name varchar(20) not null default '' COMMENT '名称',
sex integer not null default 0 COMMENT '0为男，1为女',
primary key(id)
)engine=innodb default charset=utf8 auto_increment=1;

insert into test_user(name,sex) values("Han",1),("Max",2);

/*建立表test_order,并同步设置了主键、索引、外键、存储引擎innodb*/
create table test_order(
order_id int(10) not null auto_increment comment '订单ID',
u_id int(10) not null default 0 comment '用户ID',
username varchar(20) not null default '' comment '用户名',
money int(10) not null default 0 comment '钱数',
datetime timestamp not null default current_timestamp comment '生成时间',
primary key(order_id),
index(u_id),
foreign key order_f_key(u_id) references test_user(id)
)engine=innodb default charset=utf8 auto_increment=1;


/*向order中插入数据*/
insert into test_order(u_id,username,money,datetime) values(1,'Han',223,current_timestamp);
insert into test_order(u_id,username,money,datetime) values(2,'Max',423,current_timestamp);
delete fromm user where id=1;

insert into test_order(u_id,username,money,datetime) values(5,Sophe,223,current_timestamp);

/*外键维护数据完整性的方式有5种
cascade 从父表删除或更新且自动删除或更新子表中匹配的行。ON DELETE CASCADE和ON UPDATE CASCADE都可用。
set null 从父表删除或更新行，并设置子表中的外键列为NULL。ON DELETE SET NULL和ON UPDATE SET NULL子句被支持。
no action InnoDB拒绝对父表的删除或更新操作。
restrict 拒绝对父表的删除或更新操作。NO ACTION和RESTRICT都一样，删除ON DELETE或ON UPDATE子句。
set default
默认情况下，外键模式是*/
/*查询test_order表中的外键名称*/
show create test_order;
/*删除外键*/
alter table test_order drop foreign key test_order_ibfk_1;

/*新增外键,增加了on delete cascade  on update cascade*/
alter table test_order add foreign key(u_id) references test_user(id) on delete cascade on update cascade;
/*此时更新主表，从表的u_id字段会自动更改*/
update test_user set id=11 where id=1;

/*下面学习几个比较使用的mysql函数*/

/*建立新的练习表*/
create table comment(
c_id int(10) not null auto_increment comment '评论ID',
u_id int(10) not null comment '用户ID',
name varchar(20) not null default '' comment '用户名',
content varchar(1000) not null default '' comment '评论内容',
datetime timestamp not null default current_timestamp,
num1 int(10) default null,
num2 int(10) default null,
primary key(c_id)
)engine=myisam default charset=utf8 auto_increment=1;

/*插入几条数据*/
insert into comment(u_id,name,content,num1,num2)
	values (1,'test1','3445212',4,23),(2,'test2','up!!',43,21),(3,'test3','a3235b',23,23);

/*greatest(),least()求最值*/
select c_id, greatest(num1,num2) as max, least(num1,num2) as min, num1,num2 from comment
	where num1!="" and num2!="";

/*concat(), concat_ws()用于连接多个字符串，CONCAT_WS() 代表 CONCAT With Separator ，是CONCAT()的特殊形式,第一个参数是其它参数的分隔符。间隔符可以自己指定*/
select concat_ws(',',name, content,datetime) from comment;
select concat(',',name, content,datetime) from comment; /*concat会将第一个，当作一个附加的字符*/
select concat(name, ',',content,',',datetime) from comment; /*concat默认不加分隔符，可以手动加入，但不如concat_ws()简练*/

/*interval（）查询10小时之前的评论*/
select * from comment where datetime <= (select now()-interval 10 hour as time_start);

/*last_insert_id()查询最后插入记录的id*/
select last_insert_id();

/*mysql中可以加入正则匹配查询*/
select * from comment where content regexp '[A-z][0-9]+';
select * from comment where content regexp '^u';

/*随机数使用rand()产生，cast（）用于转换类型*/
select cast(rand()*1000 as unsigned) as rand_num;

/*常用的时间处理函数extract(year from payment_date)=2005或者year(),month(),day(), hour(),minute(),second(),week(), */
select day(now()) as day;
/*上面day（）完全可以代替下面的substring，更简练*/
select substring(now(),9,2) as day;

/*mysql中分表，大表分成多个小表，提高查询性能*/
/*利用merge进行分表*/
drop  table if exists  user1;
create table if not exists user1(
id int(10) not null auto_increment,
name varchar(20) not null default '',
sex integer not null default 0,
primary key(id)
)engine=myisam default charset=utf8 auto_increment=1;

drop  table if exists  user2;
create table if not exists user2(
id int(10) not null auto_increment,
name varchar(20) not null default '',
sex integer not null default 0,
primary key(id)
)engine=myisam default charset=utf8 auto_increment=1;

insert into user1(name,sex) values('Alice',0),('Apple',1);
insert into user2(name,sex) values('Bob',1),('Band',0);

/*数据分析笔试题
*/
drop table if exists t1;
create table if not exists t1(
user_id int(10) not null,
blog_id int(10) not null
);
drop table if exists t2;
create table if not exists t2(
blog_id int(10) not null,
comment_id int(10) not null
);

insert into t1 values(1,1),(1,2),(1,3),(2,4),(2,5),(2,6),(2,7),(3,8);
insert into t2 values(2,1),(2,2),(2,3),(2,4),(3,5),(4,6),(4,7),(4,8),(5,9),(5,10);

alter t2 change user_id blog_id int(10);
alter t2 change blog_id comment_id int(10); 

insert into t1 values(1,9),(1,10);
insert into t2 values(9,11),(9,12),(10,13);

alter table t2 change user_id blog_id int(10);
select  t1.user_id,t1.blog_id,t2.comment_id from t1 inner join  t2 on t1.blog_id=t2.blog_id;
select  t1.user_id,t1.blog_id,count(t2.comment_id) from t1 inner join  t2 on t1.blog_id=t2.blog_id 
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 
	order by counts desc 
	limit 1;
	

/*将上述select的结果插入一个表中*/
drop table if exists t1_t2;
create table if not exists t1_t2(
user_id int(10),
blog_id int(10),
comment_counts int(10)
);

insert into t1_t2 values(2,5,2),(1,9,2),(1,3,1),(1,10,1),(1,2,4),(2,4,3);
/*注意insert into t1_t2表后不加values关键词*/
insert into t1_t2 (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 
	order by counts desc);
/*求按user_id分组取最大的2个comment_id对应的blog_id
*下面经过验证*/


select t.user_id,t.blog_id from t1_t2 t where 2>(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)<=2;
	order by counts desc;
	limit 1;
	
/*
按照blog_id分组取最大的值所在的行
*/



/*将2个表merge成一个表，注意最后一行是engine=merge，而不是type=merge
The older term TYPE is supported as a synonym for ENGINE for backward compatibility,
but ENGINE is the preferred term and TYPE is deprecated. 
*/
/*但是，如下方式直接建立alluser还是报错了1168：unable to open underlying table which is differently defined or of non-myiasm*/
/*drop  table if exists alluser;
create table if not exists alluser(
id int(10) not null auto_increment,
name varchar(20) default '',
sex integer not null default 0,
index(id)
)engine=MERGE union=(user1,user2) INSERT_METHOD=last AUTO_INCREMENT=1;
*/
/*最后决定采用曲线方式*/
create table alluser like user1;
alter table alluser engine=merge union(user1,user2);
alter table alluser insert_method=last;


desc alluser;
/*从合成的表中查询*/
select * from alluser;
/*总表中插入记录*/
insert into alluser(name,sex) values('Merry',1),('Han',0);
select * from user1;
select * from user2;
/*更新总表中记录*/
update alluser set sex=replace(sex,1,0) where id=2;




/*特别要明白各种关联查询,注重查询效率*/
/*在一个 INNER JOIN 之中，可以嵌套 LEFT JOIN 或 RIGHT JOIN，但是在 LEFT JOIN 或 RIGHT JOIN 中不能嵌套 INNER JOIN。*/
/*INNER JOIN 运算 组合两个表中的记录，只要在公共字段之中有相符的值*/
select a.actor_id,b.film_id from actor as a inner join film_actor as b 
	on a.actor_id = b.actor_id limit 10;

/*两个表普通连接*/
select a.actor_id,film_id from actor as a, film_actor as b 
	where a.actor_id = b.actor_id limit 10;

	
/*各种查询关键字的顺序:
join子句 联结条件 > 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.jb51.net/article/31590.htm*/
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 < a.val); 
--方法3： 
select a.* from tb a,(select name,min(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 , min(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 1 a1--a的第一个值 
b 1 b1--b的第一个值 

*/ 
/*按name分组取出第一次出现的记录*/

select a.* from tb a where val = (select top 1 val from tb where name = a.name) order by a.name 
/* 
name val memo 
---------- ----------- -------------------- 
a 2 a2(a的第二个值) 
b 1 b1--b的第一个值 
*/
 
/*按name分组随机取一条数据*/

select a.* from tb a where val = (select top 1 val from tb where name = a.name order by newid()) order by a.name/* 
name val memo 
---------- ----------- -------------------- 
a 1 a1--a的第一个值 
b 5 b5b5b5b5b5 

*/ 
/*按name分组取最小的2个（N个）val所在记录*/

select a.* from tb a where 2 > (select count(*) from tb where name = a.name and val < a.val ) order by a.name,a.valselect a.* from tb a where val in (select top 2 val from tb where name=a.name order by val) 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(*) < 2) order by a.name 
/* 
name val memo 
---------- ----------- -------------------- 
a 1 a1--a的第一个值 
a 2 a2(a的第二个值) 
b 1 b1--b的第一个值 
b 2 b2b2b2b2 

*/ 
/*按name分组取最大的2个（N个）val所在记录*/

select a.* from tb a where 2 > (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(*) < 2) order by a.name 
/* 
name val memo 
---------- ----------- -------------------- 
a 2 a2(a的第二个值) 
a 3 a3:a的第三个值 
b 4 b4b4 
b 5 b5b5b5b5b5 
*/ 
/*MySQL中序列有auto_increment，Oracle中使用sequence序列和**.NextVal*/
/*在Oracle中可以添加触发器，实现mysql形式的自增长；mysql也可以通过定义过程实现Oracle的序列语法*/
/*mysql实现currval()*/
drop table if exists sequence;
create table sequence(
name varchar(50) not null,
current_value int not null,
increment int not null default 1,
primary key(name)
)engine=InnoDB;

insert into sequence values('MovieSeq',3,5);

Drop function if exists currval;
delimiter $
create function currval(seq_name varchar(50))
returns integer
contains sql
begin
	declare value integer;
	set value=0;
	select current_value into value
	from sequence
	where name=seq_name;
	return value;
end$
delimiter ;
/*测试定义的currval*/
select currval('MovieSeq');

/*mysql实现nextval()*/
drop function if exists nextval;
delimiter $
create function nextval(seq_name varchar(50))
returns integer
contains sql
begin 
	update sequence
	set	current_value=current_value+increment
	where name=seq_name;
	return currval(seq_name);
end$
delimiter ;

/*测试定义的nextval()*/
select nextval('MovieSeq');
select nextval('MovieSeq');

/*mysql实现setval(). 报错了！！*/
drop function if exists setval;
delimiter $
create function setval(seq_name varchar(50),val integer)
returns integer
contains sql
begin
	update sequence
	set current_value=val;
	where name = seq_name;
	return currval(seq_name);
end$
delimiter ;

/*测试定义的setval()*/
select setval('MovieSeq',150);
select curval('MovieSeq');
select nextval('MovieSeq');

/*即作为主键同时又是外键的情况*/
drop table if exists Issues;
create table Issues (
issue_id integer auto_increment primary key
);

drop table if exists Bugs;
create table Bugs(
issue_id integer primary key,
foreign key(issue_id) references Issues(issue_id)
);

insert into Issues values();
insert into Issues values();
select * from Issues; /*1,2*/
select * from Bugs; /*null*/
insert into Bugs values(4); /*报外键引用错误*/
insert into Bugs values(2); /*正常插入，只要插入Issues中存在的数据就OK*/
select * from Bugs; /*2*/



/*浮点数的表示*/

/*浮点数不能比较，要用近似相等*/
drop table if exists Num;
create table Num(
id integer auto_increment primary key,
price float not null default '0.0',
sum float not null default '0.0',
);

/*float类型的数据存储时满足IEEE754二进制浮点数的标准，表达的范围很大，舍入方式不是四舍五入；
当存储的值在Integer和numeric类型所支持的范围内，就不必选择float类型。推荐用numeric*/

alter table Num add column price2 numeric(9,2) default '0.0';
/*numeric(9,2)定义的price2列存储的就有2位小数位，可以进行=精确比较，即使插入了3个小数位，会四舍五入*/


/*限定列的有效值：
如果，可选范围固定，使用Enum和Check约束,check约束使用范围更广，如检查start永远小于end；
否则，将可选数据建立一个检查表*/
drop table if exists PersonalContacts;
Create table PersonalContacts(
id integer auto_increment primary key,
salutation varchar(5) check (salutation in ('Mr.','Mrs.','Ms.','Dr.'))
);

drop table if exists PersonalContacts;
Create table PersonalContacts(
id integer auto_increment primary key,
salutation enum('Mr.','Mrs.','Ms.','Dr.')
);
/*用Enum限定列的取值范围，插入不属于其范围的值时，未报错但插入字段为空。插入范围内的值，一切正常*/
insert into PersonalContacts(salutation) values('Mz.');
select * from PersonalContacts;

insert into PersonalContacts(salutation) values('Ms.');
select * from PersonalContacts;
/*注：enum是mysql独有的特性*/

/*要想增加某列取值范围，需要更改数据表，ETL（抽取--》转换--》加载数据）,但麻烦*/
alter table PersonalContacts modify column salutation enum('Mr.','Mrs.','Ms.','Dr.','Mz.');
 
insert into PersonalContacts(salutation) values('Mz.');
select * from PersonalContacts;

/*可选数据建立一个检查表*/
drop table if exists Salutation;
create table Salutation(
status varchar(5) primary key
);

insert into Salutation(status) values('Mr.','Ms.');

drop table if exists PersonalContacts2;
/*定义外键时，加上on update cascade，重命名一个值就比较方便*/
create table PersonalContact2(
id integer auto_increment primary key,
status varchar(5),
foreign key(status) references Salutation(status) on update cascade  
);
/*方便查询、插入和更改*/
select status from Salutation order by status;
insert into Salutation(status) values('Mss.');
update Salutation set status='Dr.' where status='Mss'; 

/*使用检查表，支持废弃数据(保持历史数据的值，对新插入的数值加限制)*/
alter table Salutation add column active enum('inactive','active') not null default 'active';

/*使用update代替delete废弃一个值*/
update Salutation set active='inactive' where status='Dr.';
select status from Salutation where active='active';


/*mysql数据类型：
1.数值类型

MYSQL支持所有标准SQL,这其中包括:

    精确数值数据类型:INTERGER/INT,SMALLINT,DECIMAL/DEC,NUMERIC
    近似数值数据类型:FLOAT,REAL,DOCULE PRECISION
    BIT数据类型
作为对标准SQL的扩展,MySQL还支持TINYINT,MEDIUMINT及BIGINT


    如果ZEROFILL指定给数值列,则MYSQL会自动添加UNSIGNED属性
    整数或浮点类型均可指定AUTO_INCREMENT属性,当被赋值NULL或0时会自动设置成下一个序列值,AUTO_INCREMENT序列从1开始
    MYSQL将DOUBLE与DOUBLE PRECISION等同,将REAL也与DOUBLE PRECISION等同
    DECIMAL与NUMERIC用来存储精确数值数据,NUMERIC是DECIMAL的一种实现,MYSQL5.5将DECIMAL与NUMERIC存储为二进制格式
    BIT数据类型用来存储比特数值,BIT(M)中M允许从1到64,位数不足时会自动左侧补0
    SERIAL 等同于BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

 2.日期时间类型
     当使用DATE与TIME类型时通常需要注意的事项：

     MYSQL会尝试从各种格式的输入中解析日期与时间，但还是有格式限制的
    只有两位数的YEAR，会让MYSQL感到迷惑，所以MYSQL会尝试作下面的解析：在70～99之间的年会被解析成：1970～1999，在00～69之间的年会被解析成2000～2069
    MYSQL在解析日期时，日期的输入顺序必须为：year-month-day,否则无法正确解析
    默认情况下，MYSQL会将超出范围或不合法的日期与时间，转换成0，但对于超出范围的TIME，会将其归整到一个恰当的时间点上。
 
 3.字符串类型
    字符串类型包括:CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM和SET. 


	*/

/*图片等多媒体信息存储在数据库内，还是存储在数据库外（文件系统中）*/
/*存储在数据库外（仅用varchar存放图片文件路径）
缺点：
1. 不支持delete
2. 不支持事物隔离
3. 不支持回滚
4. 文件不支持数据库备份工具
5. 不支持访问权限限制
6. 文件不是SQL数据类型*/

/*存储在数据库内（使用Blob类型）
优点：解决 存储在数据外 的6个缺点
Blob 初始化 可以从文件中导入；
Blob 内容 也可以导入文件中。

缺点：需要数据表占用空间更大，备份更大
*/
drop table  if exists Bugs;
create table Bugs(
bug_id integer auto_increment primary key
);
/*image_id integer auto_increment not null,*/

drop table if exists Screenshots;
/*SERIAL 等同于BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE*/
create table Screenshots(
bug_id integer not null,
image_id serial not null,
screenshot_image BLOB,
caption varchar(100),
primary key (bug_id,image_id),
foreign key (bug_id) references Bugs(bug_id)
);

/*如果采用 记录路径名的 方式，存储在数据库之外的文件系统中*/
/*
create table Screenshots(
bug_id integer not null,
image_id serial not null,
screenshot_path varchar(100),
caption varchar(100),
primary key (bug_id,image_id),
foreign key (bug_id) references Bugs(bug_id)
);
*/

/*插入数据*/
insert into Bugs values();
insert into Bugs values();
insert into Bugs values();
insert into Bugs values();
insert into Bugs values();
insert into Bugs values();

/*初始化Blob数据类型*/
insert into Screenshots(bug_id,screenshot_image) values(1,load_file('f:\aaa.jpg')); 
/*这里插入时，必须指明bug_id*/

select bug_id,image_id from Screenshots;
/*验证发现image_id是自增的*/

select screen_image from Screenshots;
/*会显示出一大片的图片二进制字符*/

insert into Screenshots(bug_id,screenshot_image) values(1,load_file('f:\bbb.jpg'));
insert into Screenshots(bug_id,screenshot_image) values(2,load_file('f:\ccc.jpg'));

delete from Screenshots where bug_id=1 and image_id=3;

/*将数据库中存储的Blob图片，导出到文件系统*/
select screenshot_image into dumpfile 'F:\aaa111.jpg'
from Screenshots
where bug_id=1 and image_id=2;
/*问题：生成了 文件名 是aaa111的文件，但是打开只显示没有预览1B???????????原图片249kb*/

select screenshot_image into dumpfile 'F:\\aaa222.jpg'
from Screenshots
where bug_id=1 and image_id=2;

/*索引：在SQL标准中没有很多的说明，不同数据库实现有较大的自由度和区别*/

/*合理地使用索引!!!!
错误的观点：
1. 不使用索引或索引不足
2. 使用太多的索引
3. 执行一些让索引无能为力的查询

1. 要根据具体情况，分析需要建立哪些索引，在维护索引的开销 和 索引带来的加速之间进行比较
2. 通过mentor方法： measure(慢查询)，解释（explain),挑选，性能测试，优化，重建（定期维护：analyze table or optimize table）

注意：
1. insert update delete都要维护索引
2. 索引可以快速找到要delete或update的记录
3. 主键会自动建立索引，没有必要手工加入
4. 对于过长的varchar不建议建立索引，不太可能进行全匹配查找
5. 可以根据实际需求建立组合索引*/
drop table if exists Bugs1;
create table Bugs1(
bug_id serial primary key,
date_reported date not null,
summary varchar(80) not null,
status varchar(10) not null,
hours numeric(9,2),
index(bug_id,date_reported,status)
);

/*考虑：bugs与多个标签的关系（多个标签不互斥）*/
/*一个 bugs对应的最多标签数确定时，可以采用建立多个tage列,但在查询、添加和删除tag都很方便*/
/*一个 bugs对应的最多标签数确定时，强烈建议使用 从属表，仅使用一列存储【多值属性】，多个值存储在多行，而不是多列！
从表中定义外键和主记录关联*/

drop table if exists Tags;
create table Tags(
bug_id integer not null,
tag varchar(20),
primary key(bug_id,tag),
foreign key(bug_id) references Bugs(bug_id)
);

insert into Tags(bug_id,tag) values('1','crash'),('2','performance'),('2','printing'),('2','crash'),(3,'printing');
select * from Tags where bug_id=2;

/*查询和 某标签 相关的所有bug*/
select * from Bugs join Tags using(bug_id) where tag='performance';

/*查询和 某2个标签 相关的所有bug记录*/
select * from Bugs 
join Tags as t1 using (bug_id)
join Tags as t2 using (bug_id)
where t1.tag='printing' and t2.tag='performance';
/*结果：
+--------+----------+-------------+
| bug_id | tag      | tag         |
+--------+----------+-------------+
|      2 | printing | performance |
+--------+----------+-------------+
*/


/*使用从属表 可以更方便地 添加和移除 bugs和tag标签之间的关系*/
insert into Tags(bug_id,tag) values (3,'save');
delete from Tags where bug_id=2 and tag='crash';

/**/

/**/