天天看點
傳回

Msql入門實戰之下1.限制表的建立和插入2.存儲過程：這裡隻進行簡單介紹3.其他介紹：

前面一章主要講解了mysql的select的用法，将select的大部分用法進行分别講解。本章主要講解Msql限制表的建立，以及存儲過程的實作,附帶其他介紹。暫時就算入門了，Mysql索引之後另開章節；

講解不到位的地方歡迎大家指正:聯系方式rlovep.com

全部代碼下載下傳（csdn）：連結

Github連結：連結https://github.com/wpeace1212/Mysql

寫文章不易，歡迎大家采我的文章，以及給出有用的評論，當然大家也可以關注一下我的github；多謝；

1.限制表的建立和插入

      資料類型告訴我們需要存儲什麼樣子的資料，而限制告訴我們這些資料具體需要滿足的規則。如：age int, 我們可以存儲負整數，price float，我們可以存儲負小數，但是負數是不滿足實際規則的，是以我們需要限制。

常見限制：

  1. 非空限制：not null
  2. 唯一限制：unique
  3. 主鍵限制：primary key
  4. 外鍵限制：foreign key

1.1非空限制：

1.定義：

      在建立表的字段時，在後面帶上not null就可，正如字面意思，代表當插入資料時該字段必須有值：

2.示範建立：

#建立一個名為goods的表，有兩個字段商品名和商品價格：

create table if not exists goods(



 goods_name varchar() not null  comment '商品名',#設定不能為空

 goods_price float not null comment '商品價格'

）;

3.示範操作：

##插入：

insert into goods values('餅幹1','2.3');

insert into goods(goods_price) values('23.0')##不能執行

1.2唯一限制：

1.定義：

      在建立表的字段時，在後面帶上unique就可，正如字面意思，代表當插入資料時該字段不能有重複值

2.示範建立：

#建立一個名為goods的表，有兩個字段商品名和商品價格：

drop table goods;

create table goods(

    goods_id int not null unique comment '商品編号',#設定為非空，唯一



 goods_name varchar() not null  comment '商品名',#設定不能為空

 goods_price float not null comment '商品價格'

）;

3.示範操作：

##插入：

insert into goods values(,'餅幹1','2.3');

insert into goods values(,'餅幹2',);##報錯    Error Code: 1062. Duplicate entry '1' for key 'goods_id'    0.00022 sec

insert into goods values(,'餅幹2',);

1.3主鍵限制：

1.定義：

      在建立表的字段時，在後面帶上primary key就可；主鍵限制：不允許為空，不允許重複，主鍵一般自增長一起使用：auto_increment

2.示範建立：

#建立一個名為goods的表，有兩個字段商品名和商品價格：

drop table goods;

create table if not exists goods(#判斷表是否存在進行建立 

       goods_id int not null auto_increment primary key comment '商品編号',#設定為主鍵，自動增長

    goods_name varchar() not null  comment '商品名',#設定不能為空

    goods_price float not null comment '商品價格'

);

3.示範操作：

##插入：

insert into goods values(,'餅幹1','2.3');#标号001，沒有用，生成的标号為1；标号按1增長

insert into goods values(null,'餅幹2','2.3');#可以直接這樣插入

insert into goods values('餅幹3','2.3');#這樣報錯，提示值不比對

insert into goods(goods_name,goods_price) values('餅幹3','2.3');##這樣可以正常插入

##删除主鍵：

alter table tablename drop primary key ;

1.3外鍵限制：

1.定義：

      在建立表的時候，在後面帶上 foreign key(本表字段) references goods其他表(其他表字段)就可；外建限制是比較有用的，在建立多對一，多對多，一對一，繼承等關系的資料庫時都要用到。

作用：使一個表參考另一個表的字段，使兩表建立關系。比如：商品表中有id号，在商品訂單表中将商品id作為外建，這樣就可以通過查找訂單表中的商品id，進而通過連接配接查詢查詢到商品的詳細資訊；

2.示範建立：

#MySQL建立關聯表可以了解為是兩個表之間有個外鍵關系，但這兩個表必須滿足三個條件

#1.兩個表必須是InnoDB資料引擎

#2.使用在外鍵關系的域必須為索引型(Index)

#3.使用在外鍵關系的域兩者資料類型需要相似

#建立關聯表 

#建立商品表:

create table if not exists goods(#判斷表是否存在進行建立 

    goods_id int not null auto_increment primary key comment '商品編号',#設定為主鍵，自動增長

    goods_name varchar() not null  comment '商品名',#設定不能為空

    gooods_price float not null comment '商品價格',

    goods_maker varchar() default null comment '生産商',#morning為空

    index(goods_id)#建立外鍵關系的域必須為索引類型

)engine=innodb character set utf8 collate utf8_general_ci auto_increment=;#設定引擎以及字元集

#建立訂單表

create table if not exists detil(

    customer_id int not null primary key comment '客戶id',#設定為主鍵 

    goods int not null comment '商品id',

    count int not null comment '數量',

    index(goods),#經測試此去的索引可以省去。

    foreign key(goods) references goods(goods_id) on delete cascade on update cascade#建立外鍵，使用goods(goods_id)作為外鍵。

    #on delete(update) cascade 意思為當goods表有相關記錄删除(修改)時，detil想要的記錄也會被删去（修改）。

)engine=innodb character set utf8 collate utf8_general_ci;#設定引擎以及字元集

3.示範操作：

#1.插入示範：

#向goods表添加資料 

insert into goods values(null,'餅幹1','2.3','三無産品');

insert into goods values(null,'餅幹2','2.3','三無産品');

insert into goods values(null,'餅幹3','2.3','三無産品');

insert into goods values(null,'餅幹4','2.3','三無産品');

insert into goods values(null,'餅幹5','2.3','三無産品');

insert into goods values(null,'餅幹6','2.3','三無産品');

insert into goods values(null,'餅幹7','2.3','三無産品');

insert into goods values(null,'餅幹8','2.3','三無産品');

#向detil表添加資料

#必須在goods中有goods_id的編号，能正确插入detil；

insert into detil values(,,);

insert into detil values(,,);

insert into detil values(,,);

insert into detil values(,,);

insert into detil values(,,);

insert into detil values(,,);

insert into detil values(,,);

insert into detil values(,,);

#insert into detil values(,,);#30外鍵不存在會報錯。

#2.删除示範：

##删除goods中的一行，detil對應的外鍵等于2的列也會被删去。

delete from goods where goods_id=;

#3.查詢示範：從訂單表中查詢對應的商品；

select * from goods where goods_id=(select goods from detil where customer_id=)

#删除外鍵限制：

alter table tableName drop foreign key 外鍵名;

4.一個例子：

-- 解決資料備援高的問題：給備援的字段放到一張獨立表中

-- 獨立設計一張部門表

CREATE TABLE dept(

    id INT PRIMARY KEY,

    deptName VARCHAR()

);

-- 添加員工表

CREATE TABLE employee(

    id INT PRIMARY KEY,

    empName VARCHAR(),

    deptId INT,-- 把部門名稱改為部門ID

    -- 聲明一個外鍵限制

    CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) 

    ON UPDATE CASCADE ON DELETE CASCADE  -- ON CASCADE UPDATE ：級聯修改

    --           外鍵名稱                  外鍵               參考表(參考字段)

);

INSERT INTO dept(id,deptName) VALUES(,'軟體開發部');

INSERT INTO dept(id,deptName) VALUES(,'應用維護部');

INSERT INTO dept(id,deptName) VALUES(,'秘書部');

INSERT INTO employee VALUES(,'張三',);

INSERT INTO employee VALUES(,'李四',);

INSERT INTO employee VALUES(,'王五',);

INSERT INTO employee VALUES(,'陳六',);

-- 1）當有了外鍵限制，添加資料的順序： 先添加主表，再添加副表資料

-- 2）當有了外鍵限制，修改資料的順序： 先修改副表，再修改主表資料

-- 3）當有了外鍵限制，删除資料的順序： 先删除副表，再删除主表資料

-- ON CASCADE UPDATE ：級聯修改

UPDATE dept SET id= WHERE id=;

2.存儲過程：這裡隻進行簡單介紹

      存儲過程是一種存儲在書庫庫中的程式(就像正規語言裡的子程式一樣),準确的來說,MySQL 支援的“ routines (例程)”有兩種:一是我們說的存儲過程, 二是在其他 SQL 語句中可以傳回值的函數(使用起來和 Mysql 預裝載的函數一樣,如 pi() )。

2.1存儲過程入門：

  1. 出現的緣由：
    1. 存儲過程是可複用的元件
    2. 存儲過程将被儲存
    3. 存儲過程可以 移植
    4. 存儲過程會使系統運作更快
  2. 文法： 
DELIMITER $      /*定義分隔符*/


CREATE PROCEDURE procedure1/* name 存儲過程名 */

(IN parameter1 INTEGER)   /* parameters 參數 */

BEGIN      /* start of block 語句塊頭 */

DECLARE variable1 CHAR();     /* variables 變量聲明 */

IF parameter1 = 17 THEN    /* start of IF IF 條件開始 */

        SET variable1 = 'birds';    /* assignment 指派 */

ELSE

      SET variable1 = 'beasts';    /* assignment 指派 */

END IF;                    /* end of IF IF 結束 */

     INSERT INTO table1 VALUES (variable1);        /* statement SQL 語句 */

END   $ /* end of block 語句塊結束 */
  1. 簡單執行個體： 
#1.存儲過程（方法建立）-- 

-- 建立存儲過程--

DELIMITER $ #-- 聲明結束符為$ 

create procedure pro_test()

begin

    SELECT * FROM employee;

    INSERT INTO employee(id,deptId) VALUES(,);
END $

````

存儲過程的操作：

````

-- 執行存儲過程-- 

CALL  pro_test();

drop procedure pro_test;#删除存儲過程





<div class="se-preview-section-delimiter"></div>

2.2建立帶輸入參數的函數（存儲過程） ：

前面我們建立的函數，參數清單是空的。Mysql資料庫的存儲過程是可以帶參數的，包括輸入輸出參數，輸入參數的聲明方式：

CREATE PROCEDURE p1

([IN] name data-type) ...





<div class="se-preview-section-delimiter"></div>

1.簡單示範建立：

-- 建立帶輸入參數的函數 

delimiter $

create procedure pro_findById(in eid int) -- in:輸入參數 類型為int

begin 

    select * from employee where id=eid;

end $





<div class="se-preview-section-delimiter"></div>

2.調用：

-- 調用帶輸入參數的方法-- 

call pro_findById();





<div class="se-preview-section-delimiter"></div>

2.3建立帶輸出參數的函數（存儲過程） ：

同樣可以代輸出參數，相當于建立了變量，使得在函數結束後，可以獲得變量的值；輸出參數的聲明方式：

CREATE PROCEDURE p2

([out] name data-type) ...





<div class="se-preview-section-delimiter"></div>

1.簡單示範建立：

-- 建立帶有輸出參數的函數

delimiter $

create procedure pro_testout(out str varchar(), out sid int)-- 定義兩個輸出參數，str存儲員工名字，id存儲最小的員工編号

begin 

    select min(id) into sid from employee;-- 使用into，給輸出變量指派

    select empName from employee where id= into str;

end $





<div class="se-preview-section-delimiter"></div>

2.帶輸出參數的函數調用：

-- 帶輸出參數的函數調用

-- 變量用@name表示

call pro_testout(@str,@sid);

select @str,@sid;#使用輸出變量





<div class="se-preview-section-delimiter"></div>

2.4變量介紹：

  1. 全局變量（内置變量）：

    mysql資料庫内置的變量 （所有連接配接都起作用）

    1. 檢視所有全局變量： show variables

如： character_set_client: mysql伺服器的接收資料的編碼

character_set_results：mysql伺服器輸出資料的編碼

  1. 檢視某個全局變量：

    select @@變量名

    select @@character_set_client;輸出：utf8

  2. 修改全局變量：

    set 變量名=新值

    1. 會話變量： 輸出參數屬于會話變量
    隻存在于目前用戶端與資料庫伺服器端的一次連接配接當中。如果連接配接斷開，那麼會話變量全部丢失！

  3. 定義會話變量:

    set @變量=值

  4. 檢視會話變量：

    select @變量

    1. 局部變量：

在存儲過程中使用的變量就叫局部變量。隻要存儲過程執行完畢，局部變量就丢失！！

  1. 定義局部變量：

    DECLARE 變量名 INT DEFAULT 1;#預設值為1

    DECLARE i INT DEFAULT 1;

  2. 檢視局部變量：

    select 變量

    select i;

2.5帶有輸入輸出參數的存儲過程：

參數既是輸入參數，也是輸出參數，輸出參數的聲明方式：

CREATE PROCEDURE p2

([INTOUT] name data-type) ...





<div class="se-preview-section-delimiter"></div>

1.簡單示範建立：

DELIMITER $

CREATE PROCEDURE pro_testInOut(INOUT n INT)  -- INOUT： 輸入輸出參數

BEGIN

   -- 檢視變量

   SELECT n;

   SET n =;-- 修改變量 

END $





<div class="se-preview-section-delimiter"></div>

2.簡單調用：

-- 調用

SET @n=;

CALL pro_testInOut(@n);

SELECT @n;#顯示修改後的值





<div class="se-preview-section-delimiter"></div>

2.6,帶條件循環的存儲過程：

Mysql的存儲過程中可以使用if，while，case等語句：

  1. 帶if的存儲過程： 
-- 帶有條件判斷的存儲過程

-- 需求：輸入一個整數，如果，則傳回“星期一”,如果，傳回“星期二”,如果，傳回“星期三”。其他數字，傳回“錯誤輸入”;

DELIMITER $

CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR())

BEGIN

    IF num= THEN

        SET str='星期一';

    ELSEIF num= THEN

        SET str='星期二';

    ELSEIF num= THEN

        SET str='星期三';

    ELSE

        SET str='輸入錯誤';

    END IF;

END $

CALL pro_testIf(,@str);

SELECT @str;

drop procedure pro_testIf;





<div class="se-preview-section-delimiter"></div>

2.帶有while的存儲過程

--  帶有循環功能的存儲過程

-- 需求： 輸入一個整數，求和。例如，輸入，統計-的和

DELIMITER $

CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)

BEGIN

    -- 定義局部變量

    DECLARE i INT DEFAULT ;

    DECLARE vsum INT DEFAULT ;

    WHILE i<=num DO

          SET vsum = vsum+i;

          SET i=i+;

    END WHILE;

    SET result=vsum;

END $





<div class="se-preview-section-delimiter"></div>

#調用

CALL pro_testWhile(,@result);

drop procedure pro_testWhile;

SELECT @result;





<div class="se-preview-section-delimiter"></div>

3.帶有case的存儲過程：

CREATE PROCEDURE p13 (IN parameter1 INT)

BEGIN

   DECLARE variable1 INT;

   SET variable1 = parameter1 + ;

 CASE variable1

    WHEN  THEN INSERT INTO t VALUES ();

     WHEN  THEN INSERT INTO t VALUES ();

    ELSE INSERT INTO t VALUES ();

  END CASE;

END; $





<div class="se-preview-section-delimiter"></div>

3.其他介紹：

3.1觸發器 

-- 當進行 update,insert,delete的前後觸發一個事件；

-- 建立日志表

create table test_log(

      id int primary key auto_increment,

      content varchar()

);

-- 需求： 當向員工表插入一條記錄時，希望mysql自動同時往日志表插入資料

-- 1.建立觸發器(添加)After

create trigger tri_empAdd After insert on employee for each row

     insert into test_log(content) values('員工插入了一條記錄');

-- 插入資料；

insert into employee values(,'peace3',,);





<div class="se-preview-section-delimiter"></div>

##顯示觸發的資料：

select * from test_log;

-- 2.建立觸發器(修改) before

CREATE TRIGGER tri_empUpd before UPDATE ON employee FOR EACH ROW    -- 當往員工表修改一條記錄時

     INSERT INTO test_log(content) VALUES('員工表修改了一條記錄');

 -- 修改

 UPDATE employee SET empName='eric' WHERE id=;





<div class="se-preview-section-delimiter"></div>

##顯示觸發的資料：

select * from test_log;

-- 3.建立觸發器(删除)before

CREATE TRIGGER tri_empDel before DELETE ON employee FOR EACH ROW    -- 當往員工表删除一條記錄時

     INSERT INTO test_log(content) VALUES('員工表删除了一條記錄');

 -- 删除

 DELETE FROM employee WHERE id=;





<div class="se-preview-section-delimiter"></div>

##顯示觸發的資料：

 SELECT * FROM employee;

 SELECT * FROM test_log;





<div class="se-preview-section-delimiter"></div>

3.2mysql權限問題 

-- ***********mysql權限問題****************

 -- mysql資料庫權限問題：root ：擁有所有權限（可以幹任何事情）

 -- 權限賬戶，隻擁有部分權限（peace）例如，隻能操作某個資料庫的某張表

 -- 如何修改mysql的使用者密碼？

 -- password: md5加密函數(單向加密)

 SELECT PASSWORD('root'); -- *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B

 --  mysql資料庫，使用者配置 : user表

USE mysql;

show tables;

-- 檢視所有使用者

select user from user;

-- 建立使用者賬号

create user peace identified by '1234';

-- 授權 select,insert,其他一樣--

grant select,insert on day01.student to peace;

-- 授權所有：grant all on *.* to peace;

-- 設定與更改使用者名--

set password for peace = password('123456');

-- 撤銷使用者權限--

-- 指令：

revoke insert on day01.student from peace;

-- 展示權限--

show grants for peace;

-- 删除使用者--

drop user peace;

本章介紹到這裡：

來自伊豚wpeace（rlovep.com）

sql javaweb JavaWeb我畫你閱 mysql 限制表 存儲過程 觸發 權限
上一篇: springMvc的Hello world（xml配置）
下一篇: Mysql入門實戰上

繼續閱讀