Mysql的一些问题解决

foreign key:
alter table teacher add constraint FK_teacher_REFERENCE_academy foreign key (aca_id) references academy (aca_id);

--解决mysql网络连接的问题
--在mysql中输入：
grant all on 数据库名.表名 to 用户名@IP地址 identified by 密码 with grant option;
eg:
grant all on test.* to 'wu'@'%' identified by '111111' with grant option;

--数据库备份
mysqldump -uroot -proot -x database_name>../backup/database_name.sql

--导入备份
mysql -uroot -proot database_name < ../backup/database_name.sql


--transaction
CREATE TABLE test (
  id INT,
  data VARCHAR(100)
) ENGINE=InnoDB;

Mysql中需要在创建表的时候加入该表所匹配的引擎库，否则很多的操作不能实现

set autocommit=0;    --关闭自动事务

start transaction;    --or begin;

rollback;    --or commit;


--load data from txt

CREATE TABLE pet
(
    name VARCHAR(20),
    owner VARCHAR(20),
    species VARCHAR(20),
    sex CHAR(1),
    birth DATE,
    death DATE
);

LOAD DATA LOCAL INFILE "D://Program Files//mysql//pet.txt" INTO TABLE pet;


--create view


--create index



--create trigger
create trigger <触发器名称>
{ before | after}
{insert | update | delete}
on <表名>
for each row
<触发器SQL语句>

CREATE TRIGGER testref BEFORE INSERT ON test1
  FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1; 
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
  END;
|

------------------------------//trigger example:
CREATE TABLE test1(a1 INT);

CREATE TABLE test2(a2 INT);

CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);

CREATE TABLE test4(

  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

  b4 INT DEFAULT 0

);

 

DELIMITER |

 

CREATE TRIGGER testref BEFORE INSERT ON test1

  FOR EACH ROW BEGIN

    INSERT INTO test2 SET a2 = NEW.a1;

    DELETE FROM test3 WHERE a3 = NEW.a1; 

    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;

  END

|

 

DELIMITER ;
INSERT INTO test3 (a3) VALUES

  (NULL), (NULL), (NULL), (NULL), (NULL),

  (NULL), (NULL), (NULL), (NULL), (NULL);

 

INSERT INTO test4 (a4) VALUES

  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);



--create function
create table users
(
    username varchar(20),
    password varchar(20)
);

delimiter /
create procedure pro_reg(uname varchar(20),psw varchar(20))
begin
    insert into users values(uname,psw);
end;
/
delimiter ;


call pro_reg('aa','1111');

show create procedure database_name.pro_reg /g;


--------------------------------------
--if...else...end if

CREATE PROCEDURE p12 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
IF variable1 = 0 THEN
INSERT INTO t VALUES (17);
END IF;
IF parameter1 = 0 THEN
UPDATE t SET s1 = s1 + 1;
ELSE
UPDATE t SET s1 = s1 + 2;
END IF;
END; //

------------------------------
--case...when...end case;
CREATE PROCEDURE p13 (IN parameter1 INT)
BEGIN
DECLARE variable1 INT;
SET variable1 = parameter1 + 1;
CASE variable1
WHEN 0 THEN INSERT INTO t VALUES (17);
WHEN 1 THEN INSERT INTO t VALUES (18);
ELSE INSERT INTO t VALUES (19);
END CASE;
END; //

-----------------------------------
--while...end while
CREATE PROCEDURE p14 ()
BEGIN
DECLARE v INT;
SET v = 0;
WHILE v < 5 DO
INSERT INTO t VALUES (v);
SET v = v + 1;
END WHILE;
END; //

--------------------------------------