mySQL教程 第1章 資料庫設計

標籤：

E-R設計

很多同學在學SQL語句時，覺得非常困難，那是因為你在學一個你根本不瞭解的資料庫，資料庫中的表不是你設計的，表與表之間的關係你不明白。因此在學SQL語句之前，先介紹一下資料庫設計。

下面舉例說明資料庫設計：

學校需要開發一個系統記錄有學生、課程和成績資訊。資料庫如何設計？

這裡面涉及到兩個實體，學生表、課程，這些表為實體表。

這些表之間有什麼關係呢？、學生考試出成績，成績記錄在成績表。

一個學生可以參加多門課程，關係是1對多。

資料庫設計執行個體

設計資料庫和表

安裝mySQL

運行apt-get update命令以確保您的軟體包列表是最新的。

[email protected]:~# apt-get update

安裝mySQL服務

[email protected]:~#apt-get install mysql-server

設定mySQL允許遠端連線

需要更改設定檔和建立遠端連線的mySQL使用者。

1. 更改設定檔允許遠端連線mySQL

預設mySQL只允許本地串連，需要更改設定檔。

[email protected]:~# vi /etc/mysql/my.cnf

注釋掉紅色部分。

重啟mySQL服務

[email protected]:~# /etc/init.d/mysql restart

關閉防火牆或開啟3306連接埠

[email protected]:~# ufw disable

2. 建立遠端管理員

mySQL的登入賬戶包括使用者名稱和電腦名稱組成，比如[email protected]和[email protected]是兩個mySQL使用者，有不同的許可權和密碼。

登入mySQL

mysql> use mysql; 切換資料庫

查看user表

Select host,user,password from user;

建立新mySQL管理員root在192.168.80.%網段串連mySQL，

以下命令建立並授權‘root‘@‘192.168.80.%‘使用者，all privileges代表所有許可權，*.*代表所有資料庫所有表，identified by 代表該使用者的密碼，with grant option代表該使用者還可以授權其他使用者。

mysql> grant all privileges on *.* to ‘root‘@‘192.168.80.%‘ identified by ‘a1!‘ with grant option;

運行以下命令重新整理許可權

mysql> flush privileges;

運行以下命令查看，建立的使用者

mysql> select host,user from user;

使用管理工具串連mySQL

以下操作串連將會使用以上建立的[email protected]%使用者串連mySQL。並建立資料庫

在物理機上安裝MyManagerLiteSetup.exe，語言選擇“English”。

準備mySQL學習環境

建立以後學習過程中用到的表。

將以下代碼複製到SQL Manager工具中，選中代碼，按 或F9運行，完成

1. 建立學生表

CREATE TABLE `TStudent` (

`StudentID` varchar(15) NOT NULL,

`Sname` varchar(10) DEFAULT NULL,

`sex` char(1) DEFAULT NULL,

`cardID` varchar(20) DEFAULT NULL,

`Birthday` datetime DEFAULT NULL,

`Email` varchar(40) DEFAULT NULL,

`Class` varchar(20) DEFAULT NULL,

`enterTime` datetime DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 建立課程表

create table TSubject

(

subJectID nvarchar(10),

subJectName nvarchar(30),

BookName nvarchar(30),

Publisher nvarchar(20)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. 建立分數表

create table TScore

(

StudentID nvarchar(15),

subJectID nvarchar(10),

mark decimal

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. 插入課程資訊

insert into TSubject values (‘0001‘,‘電腦網路‘,‘奠基電腦網路‘,‘清華出版社‘);

insert into TSubject values (‘0002‘,‘資料結構‘,‘大話資料結構‘,‘人郵出版社‘);

insert into TSubject values (‘0003‘,‘JAVA開發‘,‘JAVA企業級開發‘,‘人郵出版社‘)

5. 建立函數 該函數能夠產生學生姓名

create function create_name()

RETURNS varchar(3)

begin

DECLARE LN VARCHAR(300);

DECLARE MN VARCHAR(200);

DECLARE FN VARCHAR(200);

DECLARE LN_N INT;

DECLARE MN_N INT;

DECLARE FN_N INT;

SET LN=‘李王張劉陳楊黃趙周吳徐孫朱馬胡郭林何高梁鄭羅宋謝唐韓曹許鄧蕭馮曾程蔡彭潘袁於董餘蘇葉呂魏蔣田杜丁沈薑範江傅鐘盧汪戴崔任陸廖姚方金邱夏譚韋賈鄒石熊孟秦閻薛侯雷白龍段郝孔邵史毛常萬顧賴武康賀嚴尹錢施牛洪龔‘;

SET MN=‘偉剛勇春菊毅俊峰強軍平保東文輝力明永健世廣志瑗琰韻融園藝詠卿聰瀾純毓悅昭冰爽琬茗羽希寧欣飄育瀅馥新利筠柔竹靄凝曉歡霄楓芸菲寒伊亞宜可姬舒義興良海山仁波寧貴福生龍元全國勝學祥亮政謙亨奇固之嵐苑富順信子傑濤昌成康星光天達安岩中茂進林有堅和彪博誠先敬震振壯會思群豪清飛彬娜靜淑惠珠翠雅芝妍茜秋珊莎錦黛青倩婷姣婉嫻瑾穎露瑤怡嬋雁蓓紈儀荷丹蓉眉君琴蕊薇菁夢素偉剛勇毅俊峰強軍平保東文輝力明永健世廣志義興良海山仁波寧貴福生龍元全國勝學祥才發武新利清飛彬富順信子傑濤昌成康星光天達安岩中茂進林有堅和彪博誠先敬震振壯會思群豪心邦承樂紹功松善厚慶磊民友裕河哲江超浩亮政謙亨奇固之輪翰朗伯宏言若鳴朋斌梁棟維啟克倫翔旭鵬澤晨辰士以建家致樹炎德行時泰盛雄琛鈞冠策騰楠榕風航弘‘;

SET FN=‘偉剛勇毅俊雲蓮真環雪榮愛妹霞香月鶯媛豔瑞凡佳嘉瓊勤珍貞莉桂娣葉璧才發武麗琳輪翰朗伯宏言若鳴朋斌梁棟維啟克倫翔旭鵬澤晨辰士以建家致樹炎德河哲江超浩璐婭琦晶裕華慧巧美婕馨影荔枝思心邦承樂紹功松善厚慶磊民友玉萍紅娥玲芬芳燕彩蘭鳳潔梅秀娟英行時泰盛雄琛鈞冠策騰楠榕風航弘峰強軍平保東文輝力明永健世廣志義興良海山仁波寧貴福生龍元全國勝學祥才發武新利清飛彬富順信子傑濤昌成康星光天達安岩中茂進林有堅和彪博誠先敬震振壯會思群豪心邦承樂紹功松善厚慶磊民友裕河哲江超浩亮政謙亨奇固之輪翰朗伯宏言若鳴朋斌梁棟維啟克倫翔旭鵬澤晨辰士以建家致樹炎德行時泰盛雄琛鈞冠策騰楠榕風航弘‘;

SET LN_N=CHAR_LENGTH(LN);

SET MN_N=CHAR_LENGTH(MN);

SET FN_N=CHAR_LENGTH(FN);

return Concat(substring(LN,ceil(rand()*LN_N),1),substring(MN,ceil(rand()*MN_N),1),substring(FN,ceil(rand()*FN_N),1));

end

6. 建立新增學生的預存程序

create procedure addStudent(in num int)

begin

declare i int;

set i=1;

delete from TStudent;

while num>=i do

insert TStudent values (

LPAD(convert(i,char(5)),5,‘0‘),

create_name(),

if(ceil(rand()*10)%2=0,‘男‘,‘女‘),

RPAD(convert(ceil(rand()*1000000000000000000),char(18)),18,‘0‘),

Concat(‘198‘,convert(ceil(rand()*10),char(1)),‘-‘,LPAD(convert(ceil(rand()*12),char(2)),2,‘0‘),‘-‘,LPAD(convert(ceil(rand()*30),char(2)),2,‘0‘)),

Concat(PINYIN(sname),‘@hotmail.com‘),

case ceil(rand()*3) when 1 then ‘網路與網站開發‘ when 2 then ‘JAVA‘ ELSE ‘NET‘ END,

NOW());

set i=i+1;

end while;

select * from TStudent;

end

7. 建立漢字轉拼音的函數，用來產生使用者使用者的郵箱

建立漢字轉拼音的函數使用的表

CREATE TABLE `pinyin` (

`letter` char(1) NOT NULL,

`chinese` char(1) NOT NULL,

PRIMARY KEY (`letter`)

) ENGINE=MyISAM DEFAULT CHARSET=gbk;

插入資料

INSERT INTO `pinyin` VALUES (‘A‘,‘驁‘),(‘B‘,‘簿‘),(‘C‘,‘錯‘),(‘D‘,‘鵽‘),(‘E‘,‘樲‘),(‘F‘,‘鰒‘),(‘G‘,‘腂‘),

(‘H‘,‘夻‘),(‘J‘,‘攈‘),(‘K‘,‘穒‘),(‘L‘,‘鱳‘),(‘M‘,‘旀‘),(‘N‘,‘桛‘),(‘O‘,‘漚‘),(‘P‘,‘曝‘),(‘Q‘,‘囕‘),(‘R‘,‘鶸‘),

(‘S‘,‘蜶‘),(‘T‘,‘籜‘),(‘W‘,‘鶩‘),(‘X‘,‘鑂‘),(‘Y‘,‘韻‘),(‘Z‘,‘咗‘);

建立漢字轉拼音的函數

CREATE FUNCTION PINYIN(str CHAR(255))

RETURNS char(255)

BEGIN

DECLARE hexCode char(4);

DECLARE pinyin varchar(255);

DECLARE firstChar char(1);

DECLARE aChar char(1);

DECLARE pos int;

DECLARE strLength int;

SET pinyin = ‘‘;

SET strLength = CHAR_LENGTH(LTRIM(RTRIM(str)));

SET pos = 1;

SET @str = (CONVERT(str USING gbk));

WHILE pos <= strLength DO

SET @aChar = SUBSTRING(@str,pos,1);

SET hexCode = HEX(@aChar);

IF hexCode >= "8140" AND hexCode <= "FEA0" THEN

SELECT letter into firstChar

FROM pinyin

WHERE chinese >= @aChar

LIMIT 1;

ELSE

SET firstChar = @aChar;

END IF;

SET pinyin = CONCAT(pinyin,firstChar);

SET pos = pos + 1;

END WHILE;

RETURN UPPER(pinyin);

END

8. 建立插入學產生績的預存程序 學產生績在50-100分之間

create procedure fillSore()

begin

DECLARE St_Num INT;

DECLARE Sb_Num INT;

DECLARE i1 INT;

DECLARE i2 INT;

set i1=1;

set i2=1;

delete from TScore;

select count(*) into St_Num from TStudent;

select count(*) into Sb_Num from TSubject;

while St_Num>=i1 do

set i2=1;

while Sb_Num>=i2 do

insert TScore values

(LPAD(convert(i1,char(5)),5,‘0‘),LPAD(convert(i2,char(4)),4,‘0‘),ceil(50+rand()*50));

set i2=i2+1;

END WHILE;

set i1=i1+1;

END WHILE;

end

9. 插入1000個學生

call addStudent(1000)

select * from TStudent

10. 插入成績

call fillSore()

11. 查看三張表串連的結果

select a.*,b.*,c.* from TStudent a join TScore b on a.studentid=b.studentid join TSubject c on b.subjectid=c.subjectid

設定mySQL字元集

支援中文的字元集是utf8，該設定可以更改mySQL設定檔進行全域設定，也可以針對資料庫設定，也可以針對錶設定，也可以針對列設定。字元集更改後新插入的資料生效，對以前不生效。

練習：更改MySQL的預設字元集

直接修改 my.cnf，增加一行內容，然後重啟 MySQL，使之全域生效，影響建立的所有資料庫。

default-character-set = utf8

重啟mySQL服務

[email protected]:~# /etc/init.d/mysql restart

查看全域預設字元集

查看支援的所有字元集

mysql> show character set;

練習：為一個資料庫指定字元集

指定資料庫字元集後，該資料庫下建立的表預設都採用該庫指定的字元集。

1、建立資料庫時，指定其字元集

CREATE DATABASE db DEFAULT CHARSET UTF8;

2、或者採用 ALTER 文法來轉換欄位的字元集

ALTER DATABASE db DEFAULT CHARSET UTF8;

使用圖形管理介面也可以為資料庫指定字元集

練習：為 一個資料表指定字元集

指定資料表字元集後，該資料表裡的所有字元型欄位預設都使用該表指定的字元集。

1、建立資料表時，指定其字元集

CREATE TABLE tbl

(

......

) ENGINE = MyISAM DEFAULT CHARSET UTF8;

2、或者採用 ALTER 文法來轉換欄位的字元集

ALTER TABLE tbl CONVERT TO CHARACTER SET utf8;

3、採用其他用戶端工具來設定，例如 Navicat/MySQL Front/PhpMyAdmin

練習：為一個欄位指定字元集

儘管資料表建立時已經指定預設字元集了，但是該表裡面的欄位還是可以指定自己的字元集的。

1、建立資料表時，指定其字元集

CREATE TABLE tbl

(

......

name CHAR(20) CHARACTER SET UTF8,

......

) ENGINE = MyISAM DEFAULT CHARSET UTF8;

2、或者採用 ALTER 文法來轉換欄位的字元集

ALTER TABLE tbl CHANGE name name CHAR(20) CHARACTER SET utf8;

3、採用其他用戶端工具來設定，例如 Navicat/MySQL Front/PhpMyAdmin

練習：設定用戶端字元集

為用戶端串連指定字元集，最好伺服器的字元集一致。

設定putty字元集

查看姓名是亂碼

點擊“應用”。

MySQL儲存引擎

MySQL儲存引擎概述

外掛程式式儲存引擎是MySQL資料庫最重要的特性之一，使用者可以根據應用的需要選擇如何儲存和索引資料庫，是否使用事物等。mySQL預設支援多種儲存引擎，以適應不同領域的資料庫應用需要。使用者可以通過選擇使用不同的儲存引擎提高應用的效率，提供靈活的儲存，使用者佈建可以按照自己的需要定製和使用自己的儲存引擎，以實現最大程度的可定製性。

MySQL常用的儲存引擎為MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事務安全表，其他儲存引擎都是非事務安全表。

MyISAM是MySQL的預設儲存引擎。MyISAM不支援事務、也不支援外鍵，但其訪問速度快，對事務完整性沒有要求。

InnoDB儲存引擎提供了具有提交、復原和崩潰恢複能力的事務安全。但是比起MyISAM儲存引擎，InnoDB寫的處理效率差一些並且會佔用更多的磁碟空間以保留資料和索引。MySQL支援外鍵儲存引擎只有InnoDB，在建立外鍵的時候，要求附表必須有對應的索引，子表在建立外鍵的時候也會自動建立對應的索引。

儲存引擎各自的一些特點

上面提到的四種儲存引擎都有各自適用的環境，這取決於它們專屬的一些特徵。主要體現在效能、事務、並發控制、參照完整性、緩衝、 故障恢複，備份及回存等幾個方面

目前比較普及的儲存引擎是MyISAM和InnoDB.而MyISAM又是絕大部分Web應用的首選。MyISAM與InnoDB的主要的不同點在於效能和事務控制上。

MyISAM是早期ISAM(Indexed Sequential Access Method,我現在用的MySQL5.0已經不支援ISAM了）的擴充實現，ISAM被設計為適合處理讀頻率遠大於寫頻率這樣一種情況，因此ISAM以及後來的MyISAM都沒有考慮對事物的支援，不需要事務記錄，ISAM的查詢效率相當可觀，而且記憶體佔用很少。MyISAM在繼承了這類優點的同時，與時俱進的提供了大量實用的新特性和相關工具。例如考慮到並發控制，提供了表級鎖。而且由於MyISAM是每張表使用各自獨立的隱藏檔（MYD資料檔案和MYI索引檔案），使得備份及恢複十分方便（拷貝覆蓋即可），而且還支援線上恢複。

所以如果你的應用是不需要事務，不支援外鍵。處理的只是基本的CRUD（增刪改查）操作，那麼MyISAM是不二選擇。

1. 設定mysql的預設儲存引擎

編輯my.cnf設定檔，在伺服器端配置資訊[mysqld]下面添加：

default-storage-engine = MyISAM

即可設定mysql資料庫的預設引擎為MyISAM

2. 指定表的儲存引擎

如：

create table t_innodb( id int(3))

engine = innodb;

然後使用

mysql> show table status like ‘t_innodb‘;

查看錶的詳細資料。

3. 修改表的儲存引擎

ALTER TABLE t_name ENGINE = innodb;

練習：更改預設儲存引擎和表的儲存引擎

1. 更改預設儲存引擎

查看預設儲存引擎

更改預設儲存引擎

編輯設定檔

在[mysql]下添加

default-storage-engine = InnoDB

再次查看預設引擎，發現已經被更改

重啟mySQL服務

2. 更表的儲存引擎

雙擊某個表，可以更改表的儲存引擎

使用命令更改

mysql> use schoolDB;

mysql> alter table TStudent engine=InnoDB;

使用管理工具更改

InnoDB存擎的特點

1. 自動成長列

自動成長列可以送給插入，但是插入的如果是空或者為0，則實際插入的值是自動成長後的值。

建立一個表，指定自動成長列，儲存引擎innoDB。

create table au

(

studentid int not null auto_increment,

name varchar(10),

primary key(studentid)

)

engine=innodb CHARACTER SET UTF8,

插入記錄，有空值且自增列也沒按順序

insert au values (1,‘韓立剛‘),(3,‘韓立輝‘),(2,‘張京‘),(null,‘楊帥‘)

可以看到自增列的值

select * from au

外鍵約束

MySQL支援外鍵儲存引擎只有InnoDB，在建立外鍵的時候，要求父表必須有對應的索引，子表在建立外鍵的時候也會自動建立索引

在刪除更新父表時，對子表進行相應的操作，包括restrict、cascade、set null和no action

使用InnoDB儲存引擎資料按主鍵順序存放

如果不建立索引，資料以插入順序存放

create table au1

(

studentid int,

name varchar(10),

primary key(studentid)

)

engine=innodb

insert au1 values (1,‘韓立剛‘),(3,‘韓立輝‘),(2,‘張京‘),(8,‘楊帥‘),(6,‘楊柳青‘)

select * from au1

建立資料庫和表

練習：建立資料庫

也可使用命令建立資料庫

輸入以下命令 create database SchoolDB; 選中執行

練習：建立表

選中剛才建立的資料庫 執行建立表的語句

建立學生表

create table TStudent

(StudentID nvarchar(15),

Sname nvarchar(10),

sex nchar(1),

cardID nvarchar(20),

Birthday datetime,

Email nvarchar(40),

Class nvarchar(20),

enterTime datetime )

建立課程表

create table TSubject

(

subJectID nvarchar(10),

subJectName nvarchar(30),

BookName nvarchar(30),

Publisher nvarchar(20)

)

建立分數表

create table TScore

(

StudentID nvarchar(15),

subJectID nvarchar(10),

mark decimal

)

練習：使用圖形介面產生表的SQL語句

使用圖形介面可以產生建立、刪除、更改對象的SQL語句。

使用mySQL協助

查看mySQL協助

如果你不知道協助能夠提供什麼?輸入

? contents

可以查看可用的協助

輸入? Data definition

查看建立表的文法

快速查閱協助

在實際應用當中，如果需要快速查看某項文法時，可以使用關鍵字進行快速查詢。比如想知道show命令都能看到些什麼東西，可以使用如下命令。

查看資料庫

查看建立資料庫的命令

查看建立表的命令

 

mySQL教程 第1章 資料庫設計