MySQL數(shù)據(jù)庫和表的創(chuàng)建與管理

1、.,1,數(shù)據(jù)庫和表的創(chuàng)建與管理,.,2,項目知識要點與目標,.,3,任務一 創(chuàng)建與管理數(shù)據(jù)庫,數(shù)據(jù)的物理模型：即指數(shù)據(jù)的存儲結構，如對數(shù)據(jù)庫物理文件、索引文件的組織方式、文件的存取路徑，內存的管理，等。物理模型不僅與數(shù)據(jù)庫管理系統(tǒng)有關，還和操作系統(tǒng)甚至硬件有關，物理模型對用戶是不可見的。 按關系模型組織的數(shù)據(jù)表達方式簡潔、直觀，插入、刪除、修改操作方便，而按層次、網(wǎng)狀模型組織的數(shù)據(jù)表達方式復雜，插入、刪除、修改操作復雜。因此，關系模型得到了廣泛應用，MySQL是一個典型的支持關系數(shù)據(jù)模型的數(shù)據(jù)庫管理系統(tǒng)。,.,4,MySQL數(shù)據(jù)庫模型,在數(shù)據(jù)庫服務器中可以存儲多個數(shù)據(jù)庫文件，所以建立數(shù)據(jù)庫時要

2、設定數(shù)據(jù)庫的文件名，每個數(shù)據(jù)庫有惟一的數(shù)據(jù)庫文件名作為與其它數(shù)據(jù)庫區(qū)別的標識。 數(shù)據(jù)庫文件： 數(shù)據(jù)庫是由相關數(shù)據(jù)表組成，一個數(shù)據(jù)庫包括多個數(shù)據(jù)表。數(shù)據(jù)庫文件用于記錄數(shù)據(jù)庫中數(shù)據(jù)表構成的信息。 數(shù)據(jù)庫只能由得到授權的用戶訪問，這樣保證了數(shù)據(jù)庫的安全。,.,5,數(shù)據(jù)庫和表,數(shù)據(jù)庫可以看成是一個存儲數(shù)據(jù)對象的容器，這些數(shù)據(jù)對象包括表、視圖、觸發(fā)器、存儲過程等，其中，表是最基本的數(shù)據(jù)對象，用以存放數(shù)據(jù)庫的數(shù)據(jù)的。,必須首先創(chuàng)建數(shù)據(jù)庫，然后才能創(chuàng)建數(shù)據(jù)庫的數(shù)據(jù)對象。 MySQL可以采用兩種方式 創(chuàng)建、操作數(shù)據(jù)庫和數(shù)據(jù)對象 （1）命令行方式 （2）圖形界面工具方式,數(shù)據(jù)表,視圖,存儲 過程,DELIMIT

3、ER $ CREATE PROCEDURE DELETE_STUDENT(IN XH CHAR(6) BEGIN,.,6,創(chuàng)建數(shù)據(jù)庫 MySQL安裝后，系統(tǒng)自動地創(chuàng)建information_scema和MySQL數(shù)據(jù)庫，MySQL把有關數(shù)據(jù)庫的信息存儲在這兩個數(shù)據(jù)庫中。如果刪除了這些數(shù)據(jù)庫，MySQL就不能正常工作。 對于用戶的數(shù)據(jù)，需要創(chuàng)建新的數(shù)據(jù)庫來存放。 語法格式： CREATE DATABASE | SCHEMA IF NOT EXISTS 數(shù)據(jù)庫名 DEFAULT CHARACTER SET 字符集名 | DEFAULT COLLATE 校對規(guī)則名 說明：語句中“ ”內為可選項。 |

4、 表示二選一。,創(chuàng)建數(shù)據(jù)庫,.,7,命令行方式創(chuàng)建數(shù)據(jù)庫,在SQL語言創(chuàng)建數(shù)據(jù)庫命令create database中，如果省略語句中“ ”中的所有可選項，其結構形式如下： mysqlcreate database name; name表示被創(chuàng)建數(shù)據(jù)庫名，數(shù)據(jù)庫名必須符合以下規(guī)則：,數(shù)據(jù)庫名必須唯一； 名稱內不能含有“/”及“.”等非法字符； 最大不能超過64字節(jié)。 【例3.1】 創(chuàng)建一個名為Bookstore的數(shù)據(jù)庫。 CREATE DATABASE Bookstore;,.,8,創(chuàng)建數(shù)據(jù)庫時使用IF NOT EXISTS,如果在服務器已有 test1數(shù)據(jù)庫的情況下，再創(chuàng)建一個名為test1的

5、數(shù)據(jù)庫。 因為MySQL不允許兩個數(shù)據(jù)庫使用相同的名字，所以會出錯。 使用IF NOT EXISTS從句可以不顯示錯誤信息，例如： create database IF NOT EXISTS test1; 為了表達問題簡單，在以后的示例中單獨描述命令而不需要界面結果時，在命令前省略“mysql”提示符。 注意：在MySQL中，每一條SQL語句都以“;”作為結束標志。,.,9,因為MySQL服務器中有多個數(shù)據(jù)庫，可以使用USE命令可指定當前數(shù)據(jù)庫。 語法格式： USE db_name; 說明：這個語句也可以用來從一個數(shù)據(jù)庫“跳轉”到另一個數(shù)據(jù)庫，在用CREATE DATABASE語句創(chuàng)建了數(shù)據(jù)庫

6、之后，該數(shù)據(jù)庫不會自動成為當前數(shù)據(jù)庫，需要用這條USE語句來指定。 【例】將數(shù)據(jù)庫test1設置為當前數(shù)據(jù)庫。 Use test1 use命令后直接跟數(shù)據(jù)庫名，然后按下回車鍵即完成設定過程 使用這個命令之后，所有SQL命令都會應用于該數(shù)據(jù)庫，這是幾個少數(shù)不需要分號結尾的命令。,打開數(shù)據(jù)庫,.,10,MySQL中的字符集和校對規(guī)則,字符集是一套符號和編碼。校對規(guī)則是在字符集內用于比較字符的一套規(guī)則。 ASCII字符集中四個字母：A、B、a、b，每個字母賦予一個數(shù)值：A=65，B= 66，a= 113，b= 114。字母A是一個符號，數(shù)字65是A的編碼，字母和它們的編碼組合在一起是一個字符集。 假

7、設我們希望比較兩個字符串的值：A和b。比較的最簡單的方法是查找編碼：A為65，b為114。因為65 小于114，我們可以說A小于b。我們做的僅僅是在我們的字符集上應用了一個校對規(guī)則。,.,11,MySQL字符集和校對規(guī)則應用,MySQL支持30多種字符集的70多種校對規(guī)則。在同一臺服務器、同一個數(shù)據(jù)庫或甚至在同一個表中使用不同字符集或校對規(guī)則來混合字符串。 兩個不同的字符集不能有相同的校對規(guī)則。 每個字符集有一個默認校對規(guī)則。 例如，latin1默認校對規(guī)則是latin1_swedish_ci，而gb2312默認校對規(guī)則是gb2312_chinese_ci。 ASCII 碼字符集：使用7 位二

8、進制數(shù)來表示所有的大寫和小寫字母，數(shù)字0 到9、標點符號， 以及在美式英語中使用的特殊控制字符 GB2312是中國國家標準的簡體中文字符集。 GB2312收錄簡化漢字及一般符號、序號、數(shù)字、拉丁字母、日文假名、希臘字母、俄文字母、漢語拼音符號、漢語注音字母，共 7445 個圖形字符。 【例3.2】 創(chuàng)建一個名為Bookstore的數(shù)據(jù)庫，采用字符集gb2312和校對規(guī)則gb2312_chinese_ci。 CREATE DATABASE Bookstore DEFAULT CHARACTER SET gb2312 COLLATE gb2312_chinese_ci;,.,12,修改數(shù)據(jù)庫,數(shù)據(jù)

9、庫創(chuàng)建后，如果需要修改數(shù)據(jù)庫的參數(shù)，可以使用ALTER DATABASE命令。 語法格式： ALTER DATABASE | SCHEMA 數(shù)據(jù)庫名 DEFAULT CHARACTER SET 字符集名 | DEFAULT COLLATE 校對規(guī)則名 【例3.3】 修改數(shù)據(jù)庫Pet的默認字符集為latin1，校對規(guī)則為latin1_swedish_ci。 ALTER DATABASE Pet DEFAULT CHARACTER SET latin1 DEFAULT COLLATE latin1_swedish_ci;,.,13,刪除和顯示數(shù)據(jù)庫,已經創(chuàng)建的數(shù)據(jù)庫需要刪除，使用DROP DATA

10、BASE命令。 語法格式： DROP DATABASE IF EXISTS 數(shù)據(jù)庫名 修改數(shù)據(jù)庫test2命令如下： DROP DATABASE test2; 顯示服務器中已建立的數(shù)據(jù)庫， 使用SHOW DATABASES命令。 語法格式： SHOW DATABASES；,.,14,任務二 數(shù)據(jù)庫表的操作,圖書目錄表的表結構分析,.,15,數(shù)據(jù)類型,（1）數(shù)值類型 MySQL支持所有標準SQL數(shù)值數(shù)據(jù)類型。 嚴格數(shù)值數(shù)據(jù)類型： INTEGER或INT，SMALLINT，DECIMAL 或DEC，NUMERIC 近似數(shù)值數(shù)據(jù)類型： FLOAT，REAL， PRECISION MySQL支持選擇在

11、該類型關鍵字后面的括號內指定整數(shù)值的顯示寬度(例如，INT(4)。 對于浮點列類型，在MySQL中單精度值使用4個字節(jié)，雙精度值使用8個字節(jié)。 MySQL允許使用FLOAT(M,D)或REAL(M,D)或DOUBLE PRECISION(M,D)格式?！?M,D)”表示該值一共顯示M位整數(shù)，其中D位位于小數(shù)點后面。例如，定義為FLOAT(7,4)的一個列可以顯示為-999.9999。MySQL保存值時進行四舍五入，因此如果在FLOAT(7,4)列內插入999.00009，近似結果是999.0001。,.,16,字符串類型-CHAR,（2）字符串類型： 字符串類型的數(shù)據(jù)主要是由字母、漢字、數(shù)字符

12、號、特殊符號構成的數(shù)據(jù)對象。按照字符個數(shù)多少的不同分為以下幾類： CHAR： 列的長度固定為創(chuàng)建表時聲明的長度。長度可以為從0到255的任何值。當保存CHAR值時，在它們的右邊填充空格以達到指定的長度。 例如,在學生情況表中，如果設定“name char(8)”，表示“name”是8個字符長度的字符串，可以做賦值引用即：name=張三，這里name的值包括8個字符，其中2個漢字（按照4個字符處理）和4個空格。name右側會有若干個空格占位。這樣浪費了磁盤的存儲空間。,.,17,字符串類型-VARCHAR,VARCHAR： 列中的值為可變長字符串。長度可以指定為0到65,535之間的值。(VAR

13、CHAR的最大有效長度由最大行大小和使用的字符集確定。整體最大長度是65,532字節(jié)）。同CHAR對比，VARCHAR值保存時只保存需要的字符數(shù)。 例如,在學生情況表中，如果設定“name varchar(8)”，可以做賦值引用即：name=張三，這里姓名的值包括5個字符，其中2個漢字（按照4個字符處理）和1個空格。name右側只有一個空格占位。,.,18,BLOB和 TEXT類型,BLOB：二進制字符串(字節(jié)字符串)。BLOB列沒有字符集，并且排序和比較基于列值字節(jié)的數(shù)值值。這種類型數(shù)據(jù)用于存儲聲音、視頻、圖像等數(shù)據(jù)。 例如，圖書數(shù)據(jù)處理中的圖書封面、會員照片可以設定成為blob類型。 TE

14、XT：非二進制字符串(字符字符串)。TEXT列有一個字符集，并且根據(jù)字符集的校對規(guī)則對值進行排序和比較。在實際應用中像個人履歷、獎懲情況、職業(yè)說明、內容簡介等設定為text的數(shù)據(jù)類型。 例如，圖書數(shù)據(jù)處理中的內容簡介可以設定為text類型。 BLOB和TEXT列不能有 默認值。 BLOB或TEXT對象的最大大小由其類型確定，但在客戶端和服務器之間實際可以傳遞的最大值由可用內存數(shù)量和通信緩存區(qū)大小確定。你可以通過更改max_allowed_packet變量的值更改消息緩存區(qū)的大小，但必須同時修改服務器和客戶端程序。,.,19,日期和時間類型,（3）日期和時間類型 date：表示日期，輸入數(shù)據(jù)的格

15、式是：yyyy-mm-dd。支持的范圍是1000-01-01到 9999-12-31。 time：表示時間，輸入數(shù)據(jù)的格式是：hh:mm:ss。TIME值的范圍可以從-838:59:59到838:59:59。小時部分會因此大的原因是TIME類型不僅可以用于表示一天的時間(必須小于24小時)，還可能為某個事件過去的時間或兩個事件之間的時間間隔(可以大于24小時，或者甚至為負)。 datetime: 表示日期時間，格式是：yyyy-mm-dd hh:mm:ss。支持的范圍為1000-01-01 00:00:00到9999-12-31 23:59:59。 例如，在圖書銷售信息管理中注冊時間、訂購時間

16、可以設定成為datetime類型。,.,20,創(chuàng)建數(shù)據(jù)庫表,為數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)表，可以使用SQL命令create table來完成。此語句的完整語法是相當復雜的，因為存在很多的可選子句，但在實際中此語句的應用相當簡單。其基本結構形式為： create table tbl_name (create_definition,.) tbl_name為要創(chuàng)建的表名，括號內為表的字段，多個字段用逗號隔開。,.,21,CREATE TABLE命令,創(chuàng)建表使用CREATE TABLE命令。 語法格式： CREATE TABLE IF NOT EXISTS 表名 （列名 數(shù)據(jù)類型 NOT NULL | NULL

17、DEFAULT 列默認值） ENGINE = 存儲引擎 CREATE TABLE命令的基本格式： CREATE TABLE tbl_name （列名1 數(shù)據(jù)類型 1 NOT NULL | NULL , 列名2 數(shù)據(jù)類型 2 NOT NULL | NULL , .）,.,22,案例分析-創(chuàng)建學生基本情況表,圖書目錄表如下 ，在數(shù)據(jù)庫Bookstore中創(chuàng)建圖書目錄表Book。,數(shù)據(jù)表結構分析：,.,23,案例分析-創(chuàng)建XS表命令,CREATE TABLE book ( 圖書編號 char(20) NOT NULL PRIMARY KEY, 圖書類別 varchar(20) NOT NULL DE

18、FAULT 計算機, 書名 varchar(40) NOT NULL , 作者 char(10) NOT NULL , 出版社 varchar(20) NOT NULL , 出版時間 date NOT NULL , 單價 float(5) NOT NULL , 數(shù)量 int(5), 折扣 float(3) , 封面圖片 blob ) ENGINE=InnoDB;,命令代碼：,.,24,課堂練習,創(chuàng)建實訓3中的YGGL數(shù)據(jù)庫，并在該數(shù)據(jù)庫中創(chuàng)建員工信息表Employees，請寫出相關命令,.,25,修改表,ALTER TABLE用于更改原有表的結構。例如，可以增加或刪減列，創(chuàng)建或取消索引，更改原

19、有列的類型，重新命名列或表，還可以更改表的評注和表的類型。 語法格式： ALTER IGNORE TABLE表名 ADD COLUMN 列名 FIRST | AFTER列名 /*添加列*/ | ALTER COLUMN 列名 SET DEFAULT 默認值| DROP DEFAULT /*修改默認值*/ | CHANGE COLUMN 舊列名 列定義 /*對列重命名*/ FIRST|AFTER列名 | MODIFY COLUMN 列定義 FIRST | AFTER 列名 /*修改列類型*/ | DROP COLUMN 列名 /*刪除列*/ | RENAME TO 新表名 /*重命名該表*/,.

20、,26,修改表舉例,【例3.5】 假設已經在數(shù)據(jù)庫Bookstore中創(chuàng)建了表book，表中存在“書名”列。在表book中增加“瀏覽次數(shù)”列并將表中的“書名”列刪除。 ALTER TABLE book ADD 瀏覽次數(shù) tinyint NULL , DROP COLUMN 書名 ; 【例3.6】 假設數(shù)據(jù)庫Bookstore中已經存在table1表，將table1表重命名為student。 ALTER TABLE table1 RENAME TO student; 修改表名除了上面的ALTER TABLE命令，還可以直接用RENAME TABLE語句來更改表的名字。 語法格式： RENAME

21、TABLE 舊表名1 TO 新表名1 , 舊表名2 TO 新表名2 . 【例3.7】 假設數(shù)據(jù)庫BookStore中已經存在table2表和table3表，將table2表重命名為orders，table3表重命名為orderlist。 RENAME TABLE table2 TO orders, Table3 TO orderlist;,.,27,復制表,語法格式： CREATE TABLE IF NOT EXISTS 新表名 LIKE 參照表名 | AS (select語句) 使用LIKE關鍵字創(chuàng)建一個與old_table_name表相同結構的新表，列名、數(shù)據(jù)類型、空指定和索引也將復制，但

22、是表的內容不會復制，因此創(chuàng)建的新表是一個空表。 使用AS關鍵字可以復制表的內容，但索引和完整性約束是不會復制的。 【例3.8】 假設數(shù)據(jù)庫BookStore中有一個表Book，創(chuàng)建Book表的一個名為book_copy1的拷貝。 CREATE TABLE book_copy1 LIKE Book; 【例 3.9】 創(chuàng)建表Book的一個名為book_copy2的拷貝，并且復制其內容。 CREATE TABLE book_copy2 AS (SELECT * FROM Book);,.,28,刪除表,需要刪除一個表時可以使用DROP TABLE語句。 語法格式： DROP TABLE IF EXI

23、STS 表名1 ,表名2 . 這個命令將表的描述、表的完整性約束、索引及和表相關的權限等都全部刪除。 【例3.10】 刪除表test。 USE XSCJ DROP TABLE IF EXISTS test ;,.,29,顯示數(shù)據(jù)表信息,（1）顯示數(shù)據(jù)表文件名 語法格式： SHOW TABLES 【例3.11】顯示Bookstore 數(shù)據(jù)庫建立的數(shù)據(jù)表文件: USE Bookstore SHOW TABLES ; （2）顯示數(shù)據(jù)表結構 語法格式： DESCRIBE | DESC 表名 列名 | 通配符 DESC是DESCRIBE的簡寫，二者用法相同。 【例3.12】 用DESCRIBE語句查看

24、Book 表的列的信息。 USE Bookstore DESCRIBE Book ; 【例3.13】 查看 Book 表圖書編號 列的信息。 USE Bookstore DESC Book 圖書編號 ;,.,30,課堂練習,1、對departments表增加一列負責人（manager) 2、對employees表增加一列照片（photo)，用以存放雇員照片，該字段可以為空。 3、對salary表增加序號列(No) ，用以對每筆工資收入支出進行編號。 4、將salary表的income列改為整數(shù)型，6位 4、復制departments表的結構到bm表 5、顯示gygl數(shù)據(jù)庫中的所有表 6、顯示departments表的結構 7、刪除bm表,.,31,31,任務三 圖形界面工具創(chuàng)建數(shù)據(jù)庫和表,Navicat for MySQL： 1.連接MySQL服務器 Navicat for MySQL是一個桌面版MySQL數(shù)據(jù)庫管理和開發(fā)工具，和微軟SQLServer的管理器很像