mySQL教程 第1章 数据库设计
E-R設(shè)計
很多同學(xué)在學(xué)SQL語句時，覺得非常困難，那是因為你在學(xué)一個你根本不了解的數(shù)據(jù)庫，數(shù)據(jù)庫中的表不是你設(shè)計的，表與表之間的關(guān)系你不明白。因此在學(xué)SQL語句之前，先介紹一下數(shù)據(jù)庫設(shè)計。
下面舉例說明數(shù)據(jù)庫設(shè)計：
學(xué)校需要開發(fā)一個系統(tǒng)記錄有學(xué)生、課程和成績信息。數(shù)據(jù)庫如何設(shè)計？
這里面涉及到兩個實體，學(xué)生表、課程，這些表為實體表。
這些表之間有什么關(guān)系呢？、學(xué)生考試出成績，成績記錄在成績表。
一個學(xué)生可以參加多門課程，關(guān)系是1對多。
數(shù)據(jù)庫設(shè)計實例
設(shè)計數(shù)據(jù)庫和表
安裝mySQL
運行apt-get update命令以確保您的軟件包列表是最新的。
root@ubuntuServer:~# apt-get update
安裝mySQL服務(wù)
root@ubuntuServer:~#apt-get install mysql-server
設(shè)置mySQL允許遠程連接
需要更改配置文件和創(chuàng)建遠程連接的mySQL用戶。
1. 更改配置文件允許遠程連接mySQL
默認mySQL只允許本地連接，需要更改配置文件。
root@ubuntuServer:~# vi /etc/mysql/my.cnf
注釋掉紅色部分。
重啟mySQL服務(wù)
root@ubuntuServer:~# /etc/init.d/mysql restart
關(guān)閉防火墻或打開3306端口
root@ubuntuServer:~# ufw disable
2. 創(chuàng)建遠程管理員
mySQL的登錄賬戶包括用戶名和計算機名組成，比如root@127.0.0.1和root@localhost是兩個mySQL用戶，有不同的權(quán)限和密碼。
登錄mySQL
mysql> use mysql; 切換數(shù)據(jù)庫
查看user表
Select host,user,password from user;
創(chuàng)建新mySQL管理員root在192.168.80.%網(wǎng)段連接mySQL，
以下命令創(chuàng)建并授權(quán)'root'@'192.168.80.%'用戶，all privileges代表所有權(quán)限，*.*代表所有數(shù)據(jù)庫所有表，identified by 代表該用戶的密碼，with grant option代表該用戶還可以授權(quán)其他用戶。
mysql> grant all privileges on *.* to 'root'@'192.168.80.%' identified by 'a1!' with grant option;
運行以下命令刷新權(quán)限
mysql> flush privileges;
運行以下命令查看，創(chuàng)建的用戶
mysql> select host,user from user;
使用管理工具連接mySQL
以下操作連接將會使用以上創(chuàng)建的root@192.168.80.%用戶連接mySQL。并創(chuàng)建數(shù)據(jù)庫
在物理機上安裝MyManagerLiteSetup.exe，語言選擇“English”。
準備mySQL學(xué)習(xí)環(huán)境
創(chuàng)建以后學(xué)習(xí)過程中用到的表。
將以下代碼復(fù)制到SQL Manager工具中，選中代碼，按 或F9運行，完成
1. 創(chuàng)建學(xué)生表
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. 創(chuàng)建課程表
create table TSubject
(
subJectID nvarchar(10),
subJectName nvarchar(30),
BookName nvarchar(30),
Publisher nvarchar(20)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
3. 創(chuàng)建分數(shù)表
create table TScore
(
StudentID nvarchar(15),
subJectID nvarchar(10),
mark decimal
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
4. 插入課程信息
insert into TSubject values ('0001','計算機網(wǎng)絡(luò)','奠基計算機網(wǎng)絡(luò)','清華出版社');
insert into TSubject values ('0002','數(shù)據(jù)結(jié)構(gòu)','大話數(shù)據(jù)結(jié)構(gòu)','人郵出版社');
insert into TSubject values ('0003','JAVA開發(fā)','JAVA企業(yè)級開發(fā)','人郵出版社')
5. 創(chuàng)建函數(shù) 該函數(shù)能夠產(chǎn)生學(xué)生姓名
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='偉剛勇春菊毅俊峰強軍平保東文輝力明永健世廣志瑗琰韻融園藝詠卿聰瀾純毓悅昭冰爽琬茗羽希寧欣飄育瀅馥新利筠柔竹靄凝曉歡霄楓蕓菲寒伊亞宜可姬舒義興良海山仁波寧貴福生龍元全國勝學(xué)祥亮政謙亨奇固之嵐苑富順信子杰濤昌成康星光天達安巖中茂進林有堅和彪博誠先敬震振壯會思群豪清飛彬娜靜淑惠珠翠雅芝妍茜秋珊莎錦黛青倩婷姣婉嫻瑾穎露瑤怡嬋雁蓓紈儀荷丹蓉眉君琴蕊薇菁夢素偉剛勇毅俊峰強軍平保東文輝力明永健世廣志義興良海山仁波寧貴福生龍元全國勝學(xué)祥才發(fā)武新利清飛彬富順信子杰濤昌成康星光天達安巖中茂進林有堅和彪博誠先敬震振壯會思群豪心邦承樂紹功松善厚慶磊民友裕河哲江超浩亮政謙亨奇固之輪翰朗伯宏言若鳴朋斌梁棟維啟克倫翔旭鵬澤晨辰士以建家致樹炎德行時泰盛雄琛鈞冠策騰楠榕風(fēng)航弘';
SET FN='偉剛勇毅俊云蓮真環(huán)雪榮愛妹霞香月鶯媛艷瑞凡佳嘉瓊勤珍貞莉桂娣葉璧才發(fā)武麗琳輪翰朗伯宏言若鳴朋斌梁棟維啟克倫翔旭鵬澤晨辰士以建家致樹炎德河哲江超浩璐婭琦晶裕華慧巧美婕馨影荔枝思心邦承樂紹功松善厚慶磊民友玉萍紅娥玲芬芳燕彩蘭鳳潔梅秀娟英行時泰盛雄琛鈞冠策騰楠榕風(fēng)航弘峰強軍平保東文輝力明永健世廣志義興良海山仁波寧貴福生龍元全國勝學(xué)祥才發(fā)武新利清飛彬富順信子杰濤昌成康星光天達安巖中茂進林有堅和彪博誠先敬震振壯會思群豪心邦承樂紹功松善厚慶磊民友裕河哲江超浩亮政謙亨奇固之輪翰朗伯宏言若鳴朋斌梁棟維啟克倫翔旭鵬澤晨辰士以建家致樹炎德行時泰盛雄琛鈞冠策騰楠榕風(fēng)航弘';
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. 創(chuàng)建添加學(xué)生的存儲過程
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 '網(wǎng)絡(luò)與網(wǎng)站開發(fā)' when 2 then 'JAVA' ELSE 'NET' END,
NOW());
set i=i+1;
end while;
select * from TStudent;
end
7. 創(chuàng)建漢字轉(zhuǎn)拼音的函數(shù)，用來產(chǎn)生用戶用戶的郵箱
創(chuàng)建漢字轉(zhuǎn)拼音的函數(shù)使用的表
CREATE TABLE `pinyin` (
`letter` char(1) NOT NULL,
`chinese` char(1) NOT NULL,
PRIMARY KEY (`letter`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
插入數(shù)據(jù)
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','咗');
創(chuàng)建漢字轉(zhuǎn)拼音的函數(shù)
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. 創(chuàng)建插入學(xué)生成績的存儲過程 學(xué)生成績在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個學(xué)生
call addStudent(1000)
select * from TStudent
10. 插入成績
call fillSore()
11. 查看三張表連接的結(jié)果
select a.*,b.*,c.* from TStudent a join TScore b on a.studentid=b.studentid join TSubject c on b.subjectid=c.subjectid
設(shè)置mySQL字符集
支持中文的字符集是utf8，該設(shè)置可以更改mySQL配置文件進行全局設(shè)置，也可以針對數(shù)據(jù)庫設(shè)置，也可以針對表設(shè)置，也可以針對列設(shè)置。字符集更改后新插入的數(shù)據(jù)生效，對以前不生效。
練習(xí)：更改MySQL的默認字符集
直接修改 my.cnf，增加一行內(nèi)容，然后重啟 MySQL，使之全局生效，影響新建的所有數(shù)據(jù)庫。
default-character-set = utf8
重啟mySQL服務(wù)
root@ubuntuServer:~# /etc/init.d/mysql restart
查看全局默認字符集
查看支持的所有字符集
mysql> show character set;
練習(xí)：為一個數(shù)據(jù)庫指定字符集
指定數(shù)據(jù)庫字符集后，該數(shù)據(jù)庫下創(chuàng)建的表默認都采用該庫指定的字符集。
1、創(chuàng)建數(shù)據(jù)庫時，指定其字符集
CREATE DATABASE db DEFAULT CHARSET UTF8;
2、或者采用 ALTER 語法來轉(zhuǎn)換字段的字符集
ALTER DATABASE db DEFAULT CHARSET UTF8;
使用圖形管理界面也可以為數(shù)據(jù)庫指定字符集
練習(xí)：為 一個數(shù)據(jù)表指定字符集
指定數(shù)據(jù)表字符集后，該數(shù)據(jù)表里的所有字符型字段默認都使用該表指定的字符集。
1、創(chuàng)建數(shù)據(jù)表時，指定其字符集
CREATE TABLE tbl
(
......
) ENGINE = MyISAM DEFAULT CHARSET UTF8;
2、或者采用 ALTER 語法來轉(zhuǎn)換字段的字符集
ALTER TABLE tbl CONVERT TO CHARACTER SET utf8;
3、采用其他客戶端工具來設(shè)定，例如 Navicat/MySQL Front/PhpMyAdmin
練習(xí)：為一個字段指定字符集
盡管數(shù)據(jù)表創(chuàng)建時已經(jīng)指定默認字符集了，但是該表里面的字段還是可以指定自己的字符集的。
1、創(chuàng)建數(shù)據(jù)表時，指定其字符集
CREATE TABLE tbl
(
......
name CHAR(20) CHARACTER SET UTF8,
......
) ENGINE = MyISAM DEFAULT CHARSET UTF8;
2、或者采用 ALTER 語法來轉(zhuǎn)換字段的字符集
ALTER TABLE tbl CHANGE name name CHAR(20) CHARACTER SET utf8;
3、采用其他客戶端工具來設(shè)定，例如 Navicat/MySQL Front/PhpMyAdmin
練習(xí)：設(shè)定客戶端字符集
為客戶端連接指定字符集，最好服務(wù)器的字符集一致。
設(shè)置putty字符集
查看姓名是亂碼
點擊“應(yīng)用”。
MySQL存儲引擎
MySQL存儲引擎概述
插件式存儲引擎是MySQL數(shù)據(jù)庫最重要的特性之一，用戶可以根據(jù)應(yīng)用的需要選擇如何存儲和索引數(shù)據(jù)庫，是否使用事物等。mySQL默認支持多種存儲引擎，以適應(yīng)不同領(lǐng)域的數(shù)據(jù)庫應(yīng)用需要。用戶可以通過選擇使用不同的存儲引擎提高應(yīng)用的效率，提供靈活的存儲，用戶設(shè)置可以按照自己的需要定制和使用自己的存儲引擎，以實現(xiàn)最大程度的可定制性。
MySQL常用的存儲引擎為MyISAM、InnoDB、MEMORY、MERGE，其中InnoDB提供事務(wù)安全表，其他存儲引擎都是非事務(wù)安全表。
MyISAM是MySQL的默認存儲引擎。MyISAM不支持事務(wù)、也不支持外鍵，但其訪問速度快，對事務(wù)完整性沒有要求。
InnoDB存儲引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。但是比起MyISAM存儲引擎，InnoDB寫的處理效率差一些并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引。MySQL支持外鍵存儲引擎只有InnoDB，在創(chuàng)建外鍵的時候，要求附表必須有對應(yīng)的索引，子表在創(chuàng)建外鍵的時候也會自動創(chuàng)建對應(yīng)的索引。
存儲引擎各自的一些特點
上面提到的四種存儲引擎都有各自適用的環(huán)境，這取決于它們獨有的一些特征。主要體現(xiàn)在性能、事務(wù)、并發(fā)控制、參照完整性、緩存、 故障恢復(fù)，備份及回存等幾個方面
目前比較普及的存儲引擎是MyISAM和InnoDB.而MyISAM又是絕大部分Web應(yīng)用的首選。MyISAM與InnoDB的主要的不同點在于性能和事務(wù)控制上。
MyISAM是早期ISAM(Indexed Sequential Access Method,我現(xiàn)在用的MySQL5.0已經(jīng)不支持ISAM了）的擴展實現(xiàn)，ISAM被設(shè)計為適合處理讀頻率遠大于寫頻率這樣一種情況，因此ISAM以及后來的MyISAM都沒有考慮對事物的支持，不需要事務(wù)記錄，ISAM的查詢效率相當(dāng)可觀，而且內(nèi)存占用很少。MyISAM在繼承了這類優(yōu)點的同時，與時俱進的提供了大量實用的新特性和相關(guān)工具。例如考慮到并發(fā)控制，提供了表級鎖。而且由于MyISAM是每張表使用各自獨立的存儲文件（MYD數(shù)據(jù)文件和MYI索引文件），使得備份及恢復(fù)十分方便（拷貝覆蓋即可），而且還支持在線恢復(fù)。
所以如果你的應(yīng)用是不需要事務(wù)，不支持外鍵。處理的只是基本的CRUD（增刪改查）操作，那么MyISAM是不二選擇。
1. 設(shè)置mysql的默認存儲引擎
編輯my.cnf配置文件，在服務(wù)器端配置信息[mysqld]下面添加：
default-storage-engine = MyISAM
即可設(shè)置mysql數(shù)據(jù)庫的默認引擎為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;
練習(xí)：更改默認存儲引擎和表的存儲引擎
1. 更改默認存儲引擎
查看默認存儲引擎
更改默認存儲引擎
編輯配置文件
在[mysql]下添加
default-storage-engine = InnoDB
再次查看默認引擎，發(fā)現(xiàn)已經(jīng)被更改
重啟mySQL服務(wù)
2. 更表的存儲引擎
雙擊某個表，可以更改表的存儲引擎
使用命令更改
mysql> use schoolDB;
mysql> alter table TStudent engine=InnoDB;
使用管理工具更改
InnoDB存擎的特點
1. 自動增長列
自動增長列可以送給插入，但是插入的如果是空或者為0，則實際插入的值是自動增長后的值。
創(chuàng)建一個表，指定自動增長列，存儲引擎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，在創(chuàng)建外鍵的時候，要求父表必須有對應(yīng)的索引，子表在創(chuàng)建外鍵的時候也會自動創(chuàng)建索引
在刪除更新父表時，對子表進行相應(yīng)的操作，包括restrict、cascade、set null和no action
使用InnoDB存儲引擎數(shù)據(jù)按主鍵順序存放
如果不創(chuàng)建索引，數(shù)據(jù)以插入順序存放
create table au1
(
studentid int,
name varchar(10),
primary key(studentid)
)
engine=innodb
insert au1 values (1,'韓立剛'),(3,'韓立輝'),(2,'張京'),(8,'楊帥'),(6,'楊柳青')
select * from au1
創(chuàng)建數(shù)據(jù)庫和表
練習(xí)：創(chuàng)建數(shù)據(jù)庫
也可使用命令創(chuàng)建數(shù)據(jù)庫
輸入以下命令 create database SchoolDB; 選中執(zhí)行
練習(xí)：創(chuàng)建表
選中剛才創(chuàng)建的數(shù)據(jù)庫 執(zhí)行創(chuàng)建表的語句
創(chuàng)建學(xué)生表
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 )
創(chuàng)建課程表
create table TSubject
(
subJectID nvarchar(10),
subJectName nvarchar(30),
BookName nvarchar(30),
Publisher nvarchar(20)
)
創(chuàng)建分數(shù)表
create table TScore
(
StudentID nvarchar(15),
subJectID nvarchar(10),
mark decimal
)
練習(xí)：使用圖形界面產(chǎn)生表的SQL語句
使用圖形界面可以產(chǎn)生創(chuàng)建、刪除、更改對象的SQL語句。
使用mySQL幫助
查看mySQL幫助
如果你不知道幫助能夠提供什么?輸入
? contents
可以查看可用的幫助
輸入? Data definition
查看創(chuàng)建表的語法
快速查閱幫助
在實際應(yīng)用當(dāng)中，如果需要快速查看某項語法時，可以使用關(guān)鍵字進行快速查詢。比如想知道show命令都能看到些什么東西，可以使用如下命令。
查看數(shù)據(jù)庫
查看創(chuàng)建數(shù)據(jù)庫的命令
查看創(chuàng)建表的命令
?
轉(zhuǎn)載于:https://www.cnblogs.com/kscnchina/archive/2013/03/01/2938772.html
超強干貨來襲 云風(fēng)專訪：近40年碼齡，通宵達旦的技術(shù)人生總結(jié)
以上是生活随笔為你收集整理的mySQL教程 第1章 数据库设计的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 2.25~当svn服务器ip地址变了怎么
- 下一篇： cello 有关trigger