数据库脚本的导入导出语句
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");