MySQL學習基礎

MySQL是被Sun公司收購了，是以也有熱咖啡圖示，不過MySQL的作者後來又做了一個MariaDB，小海豚圖示，也很好用。

MySQL學習：

《MySQL網絡資料庫設計與開發》（電子工業出版社）

資料庫的基本的概念都是一樣的，而且都是用的标準的SQL文法。

學習了SQLServer之後，看MySQL感覺很熟悉，好像在複習一樣。

概念模型：

域，domain，屬性的取值範圍。

資料模型：

層級模型，IBM開發的資料庫，IMS就是的。

網狀模型，資料庫任務組（DBTG）

關系模型，最常見的資料庫的形式，oracle，sybase，informix，MS SQL Server，visual FoxPro。

面向對象的資料模型，CAD，每個對象都要唯一辨別（object identifier，OID），最具活力。

為了完整資料庫設計任務，需要從6個方面來：

需求分析設計；

概念結構設計（畫E-R圖，最後将所有單個的E-R圖拼接，形成全局模式的E-R圖）；

邏輯結構設計；

資料庫實體結構設計；

資料庫實施；

資料庫運作和維護。

建立索引3思：

一個屬性經常在查詢中出現；

一個屬性經常作為聚集函數的參數；

一個屬性經常在連接配接操作的連接配接條件中出現，這些情況請建立索引；

dos視窗登入mysql

定位到MySQL目錄，輸入：mysql -h 127.0.0.1 -u root -p

管理系權限：

授權myuser使用者student表的select權限：

USE 學生管理

GRANT SELECT ON student

TO myuser@localhost;

收回權限：

REVOKE SELECT ON student

FROM myuser@localhost

檢視權限：

SHOW GRANTS FOR myuser@localhost

備份資料mysqldump

備份學生管理資料庫的student表和scores表到d:/data/路徑：

mysqldump -h localhost -u root -p 123456 學生管理 student scores>d:/data/twotables.sql

資料還原：

mysql -u root -p 123456 學生管理<d:/data/twotables.sql

修改mysql配置（端口，字元集）：

修改安裝目錄下的my.ini檔案的[client],[mysqld]

mysql常用的圖形管理工具：

MySQL GUI tools：MySQL官方的圖形化管理工具。

phpMyAdmin：最常用的MySQL維護工具，一個用PHP開發的基于web方式架構在網站主機上的MySQL圖形管理工具，可以使用XAMPP來內建化安裝，很友善。

Navicat：支援大部分MySQL功能，包括觸發器，存儲過程，函數，事件，視圖，管理使用者，和微軟的sqlserver很類似，有免費版本。

SQLyog：完全免費，通過網絡來維護遠端的mysql資料庫，著名的Webyog公司出品。

不過對比phpmyadmin和navicat，我覺得還是navicat的界面比較美觀，而phpmyadmin的界面有點太老了。

操作資料庫對象:

建立資料庫（并設定字元集為utf-8）：

CREATE DATABASE [IF NOT EXISTS] 學生管理;

DEFAULT CHARACTER SET UTF-8;

檢視資料庫（顯示所有的資料庫，注意是DATABASE+S）：

SHOW DATABASES;

删除資料庫：

DROP DATABASE [IF EXISTS] 學生管理;

資料庫引擎：

InnoDB存儲引擎（預設），支援事務，外鍵，自增長主鍵，并發，建立表的表結構存儲在.frm檔案，但是讀寫效率稍差，占用資料空間相對較大。可以最大存儲64TB。

MyISAM存儲引擎，不支援事務，外鍵，完整性限制，并發，但是處理速度快，占用空間小。大小受限制。

MEMORY存儲引擎，不支援事務，外鍵，所有資料放在記憶體中，處理速度非常快，但是記憶體資料斷電就沒有了（你懂得），資料處理預設使用hash索引，速度比使用B型樹索引快。大小受限制。

檢視資料庫引擎：

SHOW ENGINES;

資料表的基本操作

建立表：

USE `學生管理`;

CREATE TABLE student3(
	id char(9) NOT NULL COMMENT '學号',
	name varchar(50) NOT NULL COMMENT '姓名',
	sex char(2) default NULL COMMENT '性别',
	PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=gbk;
      

　　

檢視表：

DESCRIBE student

或者DESC student

修改表：

重命名：

将表studen 重命名 student

ALTER TABLE studen RENAME student;

增加字段：

ALTER TABLE student ADD age int;

删除字段：

ALTER TABLE student DROP age;

更改表的存儲引擎：

ALTER TABLE student ENGINE=MyISAM;

建立索引：

ALTER TABLE student ADD UNIQUE INDEX index_no(sno);

删除索引：

DROP INDEX index_no ON student;

建立視圖：

CREATE VIEW 視圖

AS

SELECT * FROM student;

檢視視圖：

DESCRIBE 視圖;

删除視圖：

DROP VIEW 視圖;

觸發器

觸發器是有insert ,update , delete 等事件觸發的操作，滿足觸發條件，自動執行觸發器定義的語句。

建立觸發器：

CREATE TRIGGER 觸發器 BEFORE | AFTER 觸發事件

ON 表名 FOR EACH ROW 執行語句

比如

CREATE TRIGGER 觸發器3 AFTER INSERT
ON student3 FOR EACH ROW
INSERT INTO student4 VALUES('1','TRIGGER','BOY');
      

 檢視觸發器：

SHOW TRIGGERS;

删除觸發器：

DROP TRIGGER 觸發器;

 資料表操作

然後對資料表的增删改查和SQLServer差不多，因為都是SQL語句，是以幾乎都是大同小異：

限制查詢：

就是限制查詢條數的文法不同，在MySQL的限制查詢語句：

SELECT * FROM student LIMIT 2;

SELECT * FROM student LIMIT 2;
      

查詢student表的第2到第3條資料。

多表查詢：

SELECT student.id,name, scores.score
FROM student,scores 
WHERE student.id=scores.sid;
      

 合并查詢結果：

使用UNION合并查詢結果，并且去除重複的資料

使用UNION ALL合并查詢結果不去重

SELECT id,name,SEX FROM student
union all
SELECT id,score,SID FROM scores;
      

插入一條資料：

INSERT INTO student

VALUES(1，'david','boy');
      

 插入多條資料：

INSERT INTO student

VALUES

(1，'david','boy'),

(2，'MERRY','GIRL');
      

存儲過程：

所有的SQL語句，大小寫都是不敏感的

定義變量：

DECLARE my_sql VARCHAR(6);

指派：SET my_sql='hello';

建立存儲過程：

和sqlserver（使用go）不同的是，MySQL使用；分号作為結束标志，那麼如果一個存儲過程由多個sql語句組成怎麼辦？

這裡使用DELIMITER $$ 或者DELIMITER ##

這樣就是即将是使用 自定義的字元作為結束标志，要想恢複使用分号，作為結束符号，運作DELIMITER ;(分号)即可

DELIMITER ##
CREATE PROCEDURE DELETE_STU(IN BH INT)
BEGIN
	DELETE FROM student WHERE id=BH;
END##
DELIMITER;
      

存儲過程調用

使用CALL 存儲過程

CALL DELETE_STU(3)
      

流程控制語句：

IF ...THEN...ELSEIF...THEN...ELSE......END IF;

DELIMITER $$
CREATE PROCEDURE COMPAR(IN K1 INT,
	IN K2 INT,OUT K3 CHAR(6))
BEGIN
	IF K1>K2 THEN
		SET K3='大于';
	ELSEIF K1=K2 THEN
		SET K3='等于';
	ELSE
		SET K3='小于';
	END IF;
END $$
DELIMITER;
      

 CASE條件語句

一個小CASE可以充當一個IF-THEN-ELSE語句

delimIter ##
	CREATE PROCEDURE sexName(IN str VARCHAR(6),OUT sex VARCHAR(6))
BEGIN
	CASE str
	WHEN 'M' THEN SET sex='男';
	WHEN 'F' THEN SET sex='女';
	ELSE SET sex='無';
	END case;
END ##
delimiter;
      

循環語句

while

while 條件 DO
           循環體;
end while;      

delimIter ##
	CREATE PROCEDURE dowhile()
BEGIN
	declare v1 int default 5;
	while v1>0 DO
		set v1=v1-1;
	end while;
END ##
delimiter;      

 計算1到n的和

create PROCEDURE sum2(a int)
BEGIN
	DECLARE sum int DEFAULT 0;	
	DECLARE i int default 1;
while i<=a do 
	set sum=sum+i;
	set i=i+1;
end while;	
SELECT sum; 
END      

 repeat

repeat
    循環體；
until 條件 end repeat;      

create PROCEDURE sum2(a int)
BEGIN
	DECLARE sum int DEFAULT 0;	
	DECLARE i int default 1;
	REPEAT 
		set sum=sum+i;
		set i=i+1;
	until i>a end REPEAT;	
	SELECT sum; 
END      

loop

loop_name:loop
        if 條件 THEN -- 滿足條件時離開循環
                leave loop_name;  -- 和 break 差不多都是結束訓話
        end if；
end loop;      

create PROCEDURE sum2(a int)
BEGIN
	DECLARE sum int DEFAULT 0;	
	DECLARE i int default 1;
	loop_name:loop 
		if i>a THEN
			LEAVE loop_name;
		end if;
		set sum=sum+i;
		set i=i+1;
	end LOOP;	
	SELECT sum; 
END
      

執行存儲過程：

call sum2(10);
      

結果：55

删除存儲過程：

drop PROCEDURE sum2;
      

存儲函數

存儲函數也是過程對象之一，和存儲過程很像，但是存儲函數沒有輸出參數，不用call調用，必須包含return語句（傳回一行一列的資料）

與存儲過程不同，您可以在SQL語句中使用存儲的函數，也可以在表達式中使用。 這有助于提高程式代碼的可讀性和可維護性。

建立存儲函數

#定義函數
delimiter $$
create function getGameName(gameid int) returns VARCHAR(45)
DETERMINISTIC
begin
   return (select name from student where id=gameid);
end $$
delimiter;
      

執行存儲函數：

select getGameName(2);
      

删除存儲函數：

drop FUNCTION getGameName
      

定義條件和處理程式

　　定義條件和處理程式是事先定義程式執行過程中可能遇到的問題。并且可以在處理程式中定義解決這些問題的辦法。這種方式可以提前預測可能出現的問題，并提出解決辦法。這樣可以增強程式處理問題的能力，避免程式異常停止。MySQL中都是通過DECLARE關鍵字來定義條件和處理程式。本小節中将詳細講解如何定義條件和處理程式。

1．定義條件

　　MySQL中可以使用DECLARE關鍵字來定義條件。其基本文法如下：

DECLARE  condition_name  CONDITION  FOR  condition_value  
condition_value:  
      SQLSTATE [VALUE] sqlstate_value | mysql_error_code       

　　其中，condition_name參數表示條件的名稱；condition_value參數表示條件的類型；sqlstate_value參數和mysql_error_code參數都可以表示MySQL的錯誤。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。

【示例6】 下面定義"ERROR 1146 (42S02)"這個錯誤，名稱為can_not_find。可以用兩種不同的方法來定義，代碼如下：

//方法一：使用sqlstate_value  
DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  '42S02' ;  
//方法二：使用mysql_error_code  
DECLARE  can_not_find  CONDITION  FOR  1146 ;       

2．定義處理程式

MySQL中可以使用DECLARE關鍵字來定義處理程式。其基本文法如下：

DECLARE handler_type HANDLER FOR 
condition_value[,...] sp_statement  
handler_type:  
    CONTINUE | EXIT | UNDO  
condition_value:  
    SQLSTATE [VALUE] sqlstate_value |
condition_name  | SQLWARNING  
       | NOT FOUND  | SQLEXCEPTION  | mysql_error_code       

　　其中，handler_type參數指明錯誤的處理方式，該參數有3個取值。這3個取值分别是CONTINUE、EXIT和UNDO。CONTINUE表示遇到錯誤不進行處理，繼續向下執行；EXIT表示遇到錯誤後馬上退出；UNDO表示遇到錯誤後撤回之前的操作，MySQL中暫時還不支援這種處理方式。

　　注意：通常情況下，執行過程中遇到錯誤應該立刻停止執行下面的語句，并且撤回前面的操作。但是，MySQL中現在還不能支援UNDO操作。是以，遇到錯誤時最好執行EXIT操作。如果事先能夠預測錯誤類型，并且進行相應的處理，那麼可以執行CONTINUE操作。

　　condition_value參數指明錯誤類型，該參數有6個取值。sqlstate_value和mysql_error_code與條件定義中的是同一個意思。condition_name是DECLARE定義的條件名稱。SQLWARNING表示所有以01開頭的sqlstate_value值。NOT FOUND表示所有以02開頭的sqlstate_value值。SQLEXCEPTION表示所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值。sp_statement表示一些存儲過程或函數的執行語句。

【示例7】 下面是定義處理程式的幾種方式。代碼如下：

//方法一：捕獲sqlstate_value  
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='CAN NOT FIND';  
//方法二：捕獲mysql_error_code  
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';  
//方法三：先定義條件，然後調用  
DECLARE  can_not_find  CONDITION  FOR  1146 ;  
DECLARE CONTINUE HANDLER FOR can_not_find SET 
@info='CAN NOT FIND';  
//方法四：使用SQLWARNING  
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  
//方法五：使用NOT FOUND  
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';  
//方法六：使用SQLEXCEPTION  
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';       

　　上述代碼是6種定義處理程式的方法。

　　第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為42S02，執行CONTINUE操作，并且輸出"CAN NOT FIND"資訊。

　　第二種方法是捕獲mysql_error_code值。如果遇到mysql_error_code值為1146，執行CONTINUE操作，并且輸出"CAN NOT FIND"資訊。

　　第三種方法是先定義條件，然後再調用條件。這裡先定義can_not_find條件，遇到1146錯誤就執行CONTINUE操作。

　　第四種方法是使用SQLWARNING。SQLWARNING捕獲所有以01開頭的sqlstate_value值，然後執行EXIT操作，并且輸出"ERROR"資訊。

　　第五種方法是使用NOT FOUND。NOT FOUND捕獲所有以02開頭的sqlstate_value值，然後執行EXIT操作，并且輸出"CAN NOT FIND"資訊。

　　第六種方法是使用SQLEXCEPTION。SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值，然後執行EXIT操作，并且輸出"ERROR"資訊。

MySQL使用者安全性管理

安裝mysql之後會自動安裝一個mysql資料庫，裡面的user表記錄了多數的權限資訊，包括ssl加密資訊。

root密碼丢失的解決辦法：

1、停止權限判斷：

mysqld --skip -grant -tables

2、登入使用者修改密碼：

mysql -u root；

UPDATE mysql.user set Password=PASSWORD('root') where User='root' and Host='localhost';

3、加載權限清單：

flush privileges;