mysql个人_mysql个人总结

数据库脚本的导入导出语句

1导入：

source e:\test.sql

2导出：

C:\Windows\system32>mysqldump -hlocalhost -u root -p test>e:\test.sql

Enter password: ***

Ok!

在控制台(cmd)输入上面的话即可。

3存储过程：

两个成功运行的实例

DROP PROCEDURE IF EXISTS t1;

delimiter //

create procedure t1()

BEGIN

DECLARE i INT DEFAULT 0;

while i<=100 do

insert into t1 values(now(),i,0.35);

set i = i + 1;

end while;

end//

DROP PROCEDURE IF EXISTS t2;

delimiter //

create procedure t2()

BEGIN

DECLARE i INT DEFAULT 0;

while i<=4500 do

insert into t2 values(now(),0.35,0.35,0.35,0.35,0.35,0.35,0.35,0.35,0.35,0.35,

0.35,0.35,0.35,0.35,0.35,0.35,0.35,0.35,0.35,0.35,0.35,0.35,i);

set i = i + 1;

end while;

end//

运行存储过程：call t1();

注：在执行存储过程的时候，有可能报 ERROR 1370 (42000): execute command denied to user 'fondy'@'%' for routine 'mms.t2'错误，原因是没有赋给你执行存储过程的权限，然后让管理员附加权限即可，语句为：

grant execute on procedure mms.t2 to 'fly'@'%';

查看存储过程源码：show create procedure t2;

4触发器：

create table animals(

id int(4) primary key not null,

name varchar(8),

number int(4));

create table animals1(

ID   BIGINT(12)  NOT NULL  AUTO_INCREMENT primary key,

name varchar(8),

time date );

create table animals2(

id  int(4) primary key not null,

content varchar(20),

time datetime)

create trigger tri_alf

after insert on animals

for each row

begin

insert into animals1 (name,time) values ('增加数据',now());

end; $

delimiter $

create trigger tri_alf1

after insert on animals

for each row

begin

insert into animals1  values (NEW.id*NEW.id,'增加数据',now());

end; $

成功的案例：

drop database if exists jie;

create database jie;

use jie;

DROP TABLE IF EXISTS `tb_ana_run_room`;

/*!40101 SET @saved_cs_client     = @@character_set_client */;

/*!40101 SET character_set_client = utf8 */;

CREATE TABLE `tb_ana_run_room` (

`fd_id` varchar(36) NOT NULL,

`datetime` datetime DEFAULT NULL,

`chi_room_id` varchar(10) DEFAULT NULL,

`cap_demand` double DEFAULT NULL,

`cap_config` double DEFAULT NULL,

`power_consum` double DEFAULT NULL,

`cap_diff` double DEFAULT NULL,

PRIMARY KEY (`fd_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `tb_group_run_chp` (

`fd_id` varchar(36) NOT NULL,

`datetime` datetime NOT NULL,

`dev_group_id` varchar(10) NOT NULL,

`chp_flow` double NOT NULL,

`chp_sw_temp` double NOT NULL,

`chp_rw_temp` double NOT NULL,

`chp_sw_head` double NOT NULL,

`chp_rw_head` double NOT NULL,

`passby_status` double NOT NULL,

`chi_room_id` varchar(10) NOT NULL,

PRIMARY KEY (`fd_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

delimiter $

create trigger tri_alf_1

after insert on tb_group_run_chp for

each row

BEGIN

DECLARE i INT DEFAULT 0;

while i<=100 do

insert into tb_ana_run_room (fd_id,datetime,chi_room_id,cap_demand) values(concat(NEW.fd_id,i),NEW.DATETIME,NEW.chi_room_id,NEW.chp_rw_temp*NEW.chp_flow-NEW.chp_sw_temp*NEW.chp_flow);

set i = i + 1;

end while;

end $

delimiter ;

INSERT INTO `tb_group_run_chp` VALUES ('1','2013-01-18 11:30:00','A020100000',265.41,7,11.8,241.52,62.52,0,'A020000000');

5中文乱码：

向数据库插入内容的时候，不能插入中文，在插入之前，进行如下操作：

set names gbk;

然后就ok了!;

6字符串与数字之间的转换：

select id,cast(score as unsigned int) as scorefrom tt order by score desc;

如果id是字符串，那么mysql排序的时候就按照字母序号排，如果想要按数字的大小排序，则需要将字符串转换为数字，转换函数为cast。

Limit 2,5 从2取起，总数为5条。

Eg:

Select * from tb_dev_run_dcp order by cast(fd_id as unsigned int) limit 0,1;

7字符串转换成时间类型：date_format();

listAlarmAll1 = getAlarmServiceImp(request).findList("alarmGrade=1 and date_format(alarmTime,'%Y-%m-%d %h:%i:%s') between date_format('"+time1+"','%Y-%m-%d %h:%i:%s') and date_format('"+time2+"','%Y-%m-%d %h:%i:%s')", "alarmTime");