mysql（Windows）命令行操作

如果是會出數據可以先輸入cmd,進入命令行后，在切換到MySQL\bin目錄下面是用下面的命令導出一個數據庫中的所有數據

mysqldump -h服務器的ip  -u用戶名  -p密碼  數據庫名稱>數據庫名稱.sql

比如：mysqldump -hptw-midts-01 -uroots -p1234 packing_netoffice>packing_netoffice.sql

登錄mysql數據庫的命令：mysql -h服務器名 -u用戶名 -p密碼

如下：mysql -hlocalhost -uroot -p1234

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.45-community-nt-log MySQL Community Edition (GPL)


Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>--使用packing_netoffice數據庫
mysql> use packing_netoffice;
Database changed

--查詢v_mid_user_all表中的數據條數
mysql> select count(1) from v_mid_user_all;
+----------+
| count(1) |
+----------+
|   512771 |
+----------+
1 row in set (0.00 sec)

mysql> --導入新的packing_netoffice數據
mysql> source C:\AppServ\MySQL\bin\packing_netoffice.sql

mysql> --刪除procedure

mysql> drop procedure SyncAccount;
Query OK, 0 rows affected (0.20 sec)

下面是命令行創建mysql的procedure代碼，DELIMITER //表示整個代碼以//表示結束。

DELIMITER //
CREATE PROCEDURE SyncAccount( IN deptStr VARCHAR(4000))
BEGIN
BEGIN
DECLARE deptTotal INT DEFAULT 0;
DECLARE splitResult VARCHAR(255) DEFAULT '';
DECLARE i INT DEFAULT 0;
DECLARE deptCount INT;
SET deptTotal = 1+(length(deptStr) - length(replace(deptStr,',','')));
DROP TABLE IF EXISTS dept_split;

CREATE TEMPORARY TABLE dept_split (dept_id VARCHAR(36) NOT NULL) DEFAULT CHARSET=utf8;
WHILE i<deptTotal DO
 SET i = i+1;
 SET splitResult = reverse(substring_index(reverse(substring_index(deptStr,',',i)),',',1));
 SELECT COUNT(1) INTO deptCount FROM dept_split WHERE dept_id=splitResult; 
 IF deptCount=0 THEN
INSERT INTO dept_split(dept_id) VALUES(splitResult);
 END IF;
END WHILE;    
END;
BEGIN
DECLARE v_deptname VARCHAR(255);
DECLARE v_deptid VARCHAR(36);
DECLARE v_isservice CHAR(1) DEFAULT '1';
DECLARE flag INT DEFAULT 0 ;
DECLARE n_count INT;
 
DECLARE v_cursor CURSOR FOR 
SELECT c_dept_name,dept_id FROM v_mid_department
WHERE dept_id 
IN(SELECT dept_id FROM dept_split);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1; 
OPEN v_cursor;
WHILE flag<>1 DO
 FETCH v_cursor INTO v_deptname,v_deptid;
 
 SELECT COUNT(1) INTO n_count FROM midorganizations WHERE deptid = CONVERT(v_deptid USING utf8) COLLATE utf8_unicode_ci;
 IF n_count=0  THEN
  INSERT INTO midorganizations(name,deptid,isservice) 
  VALUES(v_deptname,v_deptid,v_isservice);
 END IF;
END WHILE;
CLOSE v_cursor;
END;
BEGIN
DECLARE v_organization MEDIUMINT(8);
DECLARE v_workid VARCHAR(36);
DECLARE v_login VARCHAR(155);
DECLARE v_password VARCHAR(155) DEFAULT '12Bz/9hNlPLZk';
DECLARE v_name VARCHAR(155) ;
DECLARE v_deptid VARCHAR(155);
DECLARE v_email_work VARCHAR(155);
DECLARE v_phone_work VARCHAR(155);
DECLARE v_memberid MEDIUMINT(8);
DECLARE v_status VARCHAR(1);
DECLARE no_data_rows2 INT DEFAULT 0;
DECLARE n_count2 INT;
DECLARE v_insertmemberid INT;
DECLARE v_profil VARCHAR(1) ;
DECLARE v_created VARCHAR(19) DEFAULT SYSDATE();
DECLARE v_cursor2 CURSOR FOR 
SELECT work_id,login_id,native_name,dept_id,office_mail,office_phone,status
FROM v_mid_user_all
WHERE dept_id
IN(SELECT dept_id FROM dept_split);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_data_rows2 = 1;
OPEN v_cursor2;
WHILE no_data_rows2<>1 DO
 FETCH v_cursor2 INTO v_workid,v_login,v_name,v_deptid,v_email_work,v_phone_work,v_status;
 IF v_workid='LA0800534' or v_workid='LA0801488' THEN
SET v_profil='5';
 ELSEIF v_workid='LA0803279' or v_workid='S09900133' or v_workid='S09902409' or v_workid='W10703265' or v_workid='S10900145' THEN
    SET v_profil='1';
 ELSE
    SET v_profil='8';
 END IF;
 IF v_login IS NOT NULL THEN
  SET v_name = CONCAT(v_login,'(',RTRIM(v_name),')');
 END IF;
 SELECT COUNT(1) INTO n_count2 FROM midmemberid_workid WHERE workid=v_workid;
 IF n_count2=0 THEN
 SELECT id INTO v_organization FROM midorganizations WHERE deptid=convert(v_deptid USING utf8)COLLATE utf8_unicode_ci;
 INSERT INTO midmembers(organization,login,password,name,email_work,phone_work,profil,created)
 VALUES(v_organization,v_login,v_password,v_name,v_email_work,v_phone_work,v_profil,v_created);
 SELECT MAX(id) INTO v_memberid FROM midmembers;
 INSERT INTO midmemberid_workid(workid,memberid)VALUES(v_workid,v_memberid);
 END IF;
 IF v_status='2' THEN
    SELECT memberid INTO v_insertmemberid FROM midmemberid_workid WHERE workid = v_workid;
   UPDATE midmembers SET profil='9',login=NULL WHERE id=v_insertmemberid;
 END IF;
END WHILE;
CLOSE v_cursor2;
END;
END;//