mysql导入导出 触发器 存储过程 事件 视图

mysql导入导出数据
关键字: db mysql
1、导出整个数据库
mysqldump -u 用户名 -p 数据库名 > 导出的文件名
mysqldump -u root -p testDb > db.sql


2.导出数据库的一个表或一些表
mysqldump -u 用户名 -p 数据库名 表名> 导出的文件名
mysqldump -u root -p testdb testTable>db.sql


3.导出一个数据库结构
mysqldump -u root -p -d testDb>f:\dbddl.sql

-d 只导出结构，无数据

4.导出一个数据库数据
mysqldump -u root -p -t testDb>f:\dbData.sql
-t 只导出数据，无结构



5.导入数据库
常用source 命令
进入mysql数据库控制台，
如mysql -u root -p

mysql>use 数据库

然后使用source命令，后面参数为脚本文件（如这里用到的db.sql）
mysql>source f:\db.sql


6.导入大数据量 用mysql比较好
命令:
mysql -u root -p password --default-character-set=utf8 数据库名 < d:/文件名db.sql
mysql -u root -p password -h 10.1.8.36 --default-character-set=utf8 数据库名 < d:/文件名db.sql


7。创建触发器
DELIMITER //
CREATE TRIGGER `insertAccount` AFTER INSERT ON `user` FOR EACH ROW BEGIN

INSERT INTO oss_account (login_name) values (new.username);

END;
//

8.事件
事件查看和启动sql
SET GLOBAL event_scheduler = 1;
SELECT @@event_scheduler;
如下实例：
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

CREATE EVENT `customer_event`
ON SCHEDULE EVERY 1 DAY
ON COMPLETION PRESERVE
ENABLE
DO
INSERT INTO rpt_oss_customer_year_quarter_month_top(years,quarters,months,days,order_id,customer,goods_price) SELECT YEAR(from_unixtime(o.dateline/1000)) years,QUARTER(from_unixtime(o.dateline/1000)) quarters,MONTH(from_unixtime(o.dateline/1000)) months,DAY(from_unixtime(o.dateline/1000)) days,o.order_id order_id,o.from_user customer,o.goods_price goods_price FROM oss_orders o WHERE DAY(from_unixtime(o.dateline/1000))=DAY(now())


9.视图
drop view if exists view_credits;
create view view_credits as
(select `a`.`order_id` AS `id`,`a`.`buyer` AS `login_name`,`a`.`seller` AS `credit_name`,`a`.`buyer_credit` AS `credit`,`a`.`buyer_evaluate` AS `evaluate`,`a`.`buyer_evaltime` AS `evaltime`,`a`.`buyer_explanation` AS `explanation`,`a`.`buyer_exptime` AS `exptime` from `cetvoss`.`oss_imall_credit` `a` where (`a`.`buyer_exptime` is not null)) union all (select `b`.`order_id` AS `id`,`b`.`seller` AS `login_name`,`b`.`buyer` AS `credit_name`,`b`.`seller_credit` AS `credit`,`b`.`seller_evaluate` AS `evaluate`,`b`.`seller_evaltime` AS `evaltime`,`b`.`seller_explanation` AS `explanation`,`b`.`seller_exptime` AS `exptime` from `cetvoss`.`oss_imall_credit` `b` where (`b`.`seller_evaltime` is not null));


10.存储过程
CREATE PROCEDURE `proc_oss_customer_year_quarter_month_top`()
BEGIN
DECLARE tableNew int;

SELECT IF ((SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_NAME`='rpt_oss_customer_year_quarter_month_top') IS NOT NULL,1,0) INTO tableNew;
#if语句
IF tableNew=1 THEN

INSERT INTO rpt_oss_customer_year_quarter_month_top(years,quarters,months,days,order_id,customer,goods_price) SELECT YEAR(from_unixtime(o.dateline/1000)) years,QUARTER(from_unixtime(o.dateline/1000)) quarters,MONTH(from_unixtime(o.dateline/1000)) months,DAY(from_unixtime(o.dateline/1000)) days,o.order_id order_id,o.from_user customer,o.goods_price goods_price FROM oss_orders o;

ELSEIF tableNew=0 THEN

CREATE TABLE rpt_oss_customer_year_quarter_month_top(SELECT YEAR(from_unixtime(o.dateline/1000)) years,QUARTER(from_unixtime(o.dateline/1000)) quarters,MONTH(from_unixtime(o.dateline/1000)) months,DAY(from_unixtime(o.dateline/1000)) days,o.order_id order_id,o.from_user customer,o.goods_price goods_price FROM oss_orders o);

END IF;

END;
11。触发器
CREATE TRIGGER `insertAccount` AFTER INSERT ON `user`
FOR EACH ROW BEGIN
INSERT INTO oss_account (login_name) values (new.username);
END;