db3數(shù)據(jù)庫(kù)數(shù)據(jù)表創(chuàng)建管理

1、復(fù)習(xí)MySql的安裝實(shí)驗(yàn)注意問(wèn)題數(shù)據(jù)庫(kù)設(shè)計(jì)步驟三范式 創(chuàng)建數(shù)據(jù)庫(kù)和數(shù)據(jù)表SQL語(yǔ)句的書寫規(guī)范在數(shù)據(jù)庫(kù)系統(tǒng)中，SQL語(yǔ)句不區(qū)分大小寫(建議用大寫) 。但字符串常量區(qū)分大小寫。SQL語(yǔ)句可單行或多行書寫，以“;”結(jié)尾。關(guān)鍵詞不能跨多行或簡(jiǎn)寫。用空格和縮進(jìn)來(lái)提高語(yǔ)句的可讀性。子句通常位于獨(dú)立行，便于編輯，提高可讀性。SELECT * FROM tb_table注釋：SQL標(biāo)準(zhǔn)：/*/。多行注釋 “-” 單行注釋MySql注釋：“#”創(chuàng)建數(shù)據(jù)庫(kù)創(chuàng)建數(shù)據(jù)庫(kù)建立數(shù)據(jù)庫(kù)操作：建立數(shù)據(jù)庫(kù)操作：語(yǔ)法：create database 數(shù)據(jù)庫(kù)名數(shù)據(jù)庫(kù)名敘述：創(chuàng)建一個(gè)具有指定名稱的數(shù)據(jù)庫(kù)。如果要?jiǎng)?chuàng)建的數(shù)據(jù)庫(kù)已經(jīng)存在，

2、或者沒(méi)有創(chuàng)建它的適當(dāng)權(quán)限，則此語(yǔ)句失敗。例：建立一個(gè)student庫(kù)。 mysql create database student;查看數(shù)據(jù)庫(kù)命令：show databases;列出當(dāng)前數(shù)據(jù)庫(kù)服務(wù)器實(shí)例下所有的數(shù)據(jù)庫(kù)切換數(shù)據(jù)庫(kù)： use 數(shù)據(jù)庫(kù)的名字，例：use students;SHOW DATABASES語(yǔ)法：SHOW DATABASESLIKE wild；如果使用LIKE wild不符，wild字符串可以是一個(gè)使用SQL的”%”和”_”通配符的字符串。功能：列出在MySql服務(wù)器主機(jī)上的數(shù)據(jù)庫(kù)。MySql自帶數(shù)據(jù)庫(kù)：Information_schema:主要存儲(chǔ)了系統(tǒng)中的一些數(shù)據(jù)庫(kù)對(duì)象信

3、息：如用戶表信息、列信息、權(quán)限信息、字符集信息、分區(qū)信息等。(數(shù)據(jù)字典表)Mysql:存儲(chǔ)了系統(tǒng)的用戶權(quán)限信息及幫助信息。Test:系統(tǒng)自動(dòng)創(chuàng)建的測(cè)試數(shù)據(jù)庫(kù)，任何用戶都可以使用。使用USE選用數(shù)據(jù)庫(kù)語(yǔ)法：USE 數(shù)據(jù)庫(kù)名;功能：把指定數(shù)據(jù)庫(kù)作為默認(rèn)(當(dāng)前)數(shù)據(jù)庫(kù)使用，用于后續(xù)語(yǔ)句。修改數(shù)據(jù)庫(kù)查看數(shù)據(jù)庫(kù)信息: status;命令語(yǔ)句：alter database 數(shù)據(jù)庫(kù)名字 character set gb2312 修改字符集Default collate gb2312_Chinese_ci; 修改排序規(guī)則 數(shù)據(jù)庫(kù)對(duì)象數(shù)據(jù)庫(kù)對(duì)象的命名規(guī)則必須以字母開頭可包括數(shù)字和三個(gè)特殊字符（# _ $）不要使

4、用MySQL的保留字同一Schema下的對(duì)象不能同名建立表操作建立表操作：語(yǔ)法：create table 表名（表名（列名列名1 列類型列類型 ,列名列名2 列類型列類型 , . . );敘 述：在當(dāng)前數(shù)據(jù)庫(kù)下新創(chuàng)建一個(gè)數(shù)據(jù)表。列類型：表示該列的數(shù)據(jù)類型。例：建立一個(gè)表建立一個(gè)表school,其由兩列組成，第一列屬性為非空，并做為主鍵其由兩列組成，第一列屬性為非空，并做為主鍵,并自增并自增create table school( school_id int(10) not null auto_increment primary key, school_name varchar(20) )；創(chuàng)建

5、表常見完整性約束：常見完整性約束：PRIMARY KEY 主碼約束(主鍵)UNIQUE唯一性約束NOT NULL非空值約束AUTO_INCREMENT 用于整數(shù)列默認(rèn)自增1UNSIGNED 無(wú)符號(hào)整數(shù)DEFAULT default_value默認(rèn)值約束DEFAULT cur_timestamp 創(chuàng)建新記錄時(shí)默認(rèn)保存當(dāng)前時(shí)間（僅適用timestamp數(shù)據(jù)列）ON UPDATE cur_timestamp 修改記錄時(shí)默認(rèn)保存當(dāng)前時(shí)間（僅適用timestamp數(shù)據(jù)列）CHARACTER SET name 指定字符集（僅適用字符串）數(shù)據(jù)表類型在創(chuàng)建一個(gè)新的MySQL數(shù)據(jù)表時(shí)，可以為它設(shè)置一個(gè)類型，其中

6、最重要的3種類型是MyISAM：成熟、穩(wěn)定和易于管理InnoDB：加入事務(wù)、數(shù)據(jù)行級(jí)鎖定機(jī)制、外鍵約束條件、崩潰恢復(fù)等新功能HEAP：只存在于內(nèi)存中，可做臨時(shí)表create table tmp()ENGINE=MyISAM列類型MySql支持多種列類型：數(shù)值類型日期/時(shí)間類型字符串(字符)類型數(shù)值數(shù)據(jù)類型-整數(shù)數(shù)值數(shù)據(jù)類型-浮點(diǎn)數(shù)日期/時(shí)間類型字符串類型思考q 電話號(hào)碼一般使用什么數(shù)據(jù)類型存儲(chǔ)?q 性別一般使用什么數(shù)據(jù)類型存儲(chǔ)?q 年齡信息一般使用什么數(shù)據(jù)類型存儲(chǔ)?q 照片信息一般使用什么數(shù)據(jù)類型存儲(chǔ)?q 薪水一般使用什么數(shù)據(jù)類型存儲(chǔ)?關(guān)于識(shí)別符建表示例建表示例SHOW語(yǔ)法：SHOW TABL

7、ESFROM 數(shù)據(jù)庫(kù)名LIKE wild;功能：顯示當(dāng)前數(shù)據(jù)庫(kù)中已有的數(shù)據(jù)表的信息顯示表結(jié)構(gòu)操作顯示表結(jié)構(gòu)操作語(yǔ)法：describe 表名；表名； desc 表名；表名；敘述：用于顯示表的創(chuàng)建結(jié)構(gòu)。刪除數(shù)據(jù)庫(kù)或表操作刪除數(shù)據(jù)庫(kù)或表操作刪除表操作語(yǔ)法：drop table if exists tab_name ,tab_name.敘述：從數(shù)據(jù)庫(kù)中刪除給定的表。如果給出if exists 子句，則刪除不存在的表不會(huì)出錯(cuò)。刪除數(shù)據(jù)庫(kù)操作語(yǔ)法：drop database if exists db_name敘述：刪除給定的數(shù)據(jù)庫(kù)。在刪除一個(gè)數(shù)據(jù)庫(kù)后，它就永遠(yuǎn)沒(méi)有了，因此要特別小心。如果給出if exis

8、ts 子句，則刪除不存在的數(shù)據(jù)庫(kù)不會(huì)出錯(cuò)。刪除命令要慎用用ALTER TABLE語(yǔ)句修改表的結(jié)構(gòu)修改列類型ALTER TABLE 表名 MODIFY 列名 列類型;增加列ALTER TABLE 表名 ADD 列名 列類型;刪除列ALTER TABLE 表名 DROP 列名;列改名ALTER TABLE 表名 CHANGE 舊列名 新列名 列類型；更改表名ALTER TABLE 表名 RENAME 新表名；RENAME TABLE 表名 TO 新表名；在表中增加字段使用ADD 子句增加字段，新的字段只能被加到整個(gè)表的最后ALTER TABLE employees ADD (gender CHAR

9、(1);修改字段可修改列的數(shù)據(jù)類型,大小和默認(rèn)值A(chǔ)LTER TABLE employeesMODIFY(age number(1);不是任何情況都可以修改的，當(dāng)字段只包含空值時(shí)，類型、大小都可以修改，否則修改可能不能成功修改默認(rèn)值，只會(huì)對(duì)以后的插入有作用，對(duì)以前已經(jīng)存在的數(shù)據(jù)不會(huì)有影響刪除字段可以從表中刪除列：ALTER TABLE employeesDROP COLUMN sex;從每行中刪除掉字段占據(jù)的長(zhǎng)度和數(shù)據(jù),釋放在數(shù)據(jù)塊中占用的空間.刪除大表中的字段將需要比較長(zhǎng)的時(shí)間，因?yàn)檫€需要回收空間刪除表表中所有數(shù)據(jù)將被刪除沒(méi)有完成的事務(wù)被提交所有相關(guān)的索引被刪除不能回滾表結(jié)構(gòu)被刪除，表對(duì)象不再

10、存在DROP TABLE table_name;更改表名rename table . to .支持一次重命名多個(gè)表。RENAME TABLE old_table1 TO tmp_table, new_table TO old_table,tmp_table TO new_table; 完成表名交換。MYSQL數(shù)據(jù)庫(kù)中的表用戶表:被用戶創(chuàng)建和維護(hù)的一些表包括了用戶自己的信息數(shù)據(jù)字典表(視圖):被數(shù)據(jù)庫(kù)系統(tǒng)創(chuàng)建和維護(hù)的一些表包括了數(shù)據(jù)庫(kù)的信息MYSQL數(shù)據(jù)庫(kù)字典由information_schema數(shù)據(jù)庫(kù)負(fù)責(zé)維護(hù)tables存放數(shù)據(jù)庫(kù)里所有的數(shù)據(jù)表、以及每個(gè)表所在數(shù)據(jù)庫(kù)。schema存放數(shù)據(jù)庫(kù)里所

11、有的數(shù)據(jù)庫(kù)信息views存放數(shù)據(jù)庫(kù)里所有的視圖信息。columns存放數(shù)據(jù)庫(kù)里所有的列信息。triggers存放數(shù)據(jù)庫(kù)里所有的觸發(fā)器。routines存放數(shù)據(jù)庫(kù)里所有存儲(chǔ)過(guò)程和函數(shù)。key_column_usage存放數(shù)據(jù)庫(kù)所有的主外鍵table_constraints存放數(shù)據(jù)庫(kù)全部約束。statistics存放了數(shù)據(jù)表的索引。什么是約束約束是在表上強(qiáng)制執(zhí)行的數(shù)據(jù)校驗(yàn)規(guī)則.約束主要用于保證數(shù)據(jù)庫(kù)的完整性。當(dāng)表中數(shù)據(jù)有相互依賴性時(shí),可以保護(hù)相關(guān)的數(shù)據(jù)不被刪除.大部分?jǐn)?shù)據(jù)庫(kù)支持下面五類完整性約束:NOT NULL非空UNIQUE Key唯一鍵PRIMARY KEY主鍵FOREIGN KEY外鍵CH

12、ECK檢查約束概述約束作為數(shù)據(jù)庫(kù)對(duì)象，存放在系統(tǒng)表中，也有自己的名字創(chuàng)建約束的時(shí)機(jī)在建表的同時(shí)創(chuàng)建建表后創(chuàng)建（修改表）可定義列級(jí)或表級(jí)約束有單列約束和多列約束定義約束的語(yǔ)法非空約束(NOT NULL)列級(jí)約束，只能使用列級(jí)約束語(yǔ)法定義。確保字段值不允許為空只能在字段級(jí)定義NULL值所有數(shù)據(jù)類型的值都可以是NULL?？兆址坏扔贜ULL。0也不等于NULL。非空約束(NOT NULL)CREATE TABLE tb_student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(18) NOT NULL)唯一約束唯一性約束條件確保所在的字段或者字段

13、組合不出現(xiàn)重復(fù)值唯一性約束條件的字段允許出現(xiàn)多個(gè)NULL同一張表內(nèi)可建多個(gè)唯一約束唯一約束可由多列組合而成建唯一約束時(shí)MySQL會(huì)為之建立對(duì)應(yīng)的索引。如果不給唯一約束起名，該唯一約束默認(rèn)與列名相同。唯一約束CREATE TABLE tb_student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(18) UNIQUE NOT NULL)主鍵約束主鍵從功能上看相當(dāng)于非空且唯一一個(gè)表中只允許一個(gè)主鍵主鍵是表中唯一確定一行數(shù)據(jù)的字段主鍵字段可以是單字段或者是多字段的組合當(dāng)建立主鍵約束時(shí)，MySQL為主鍵創(chuàng)建對(duì)應(yīng)的索引主鍵約束名總為PRIMARY。外鍵

14、約束外鍵是構(gòu)建于一個(gè)表的兩個(gè)字段或者兩個(gè)表的兩個(gè)字段之間的關(guān)系外鍵確保了相關(guān)的兩個(gè)字段的兩個(gè)關(guān)系：子(從)表外鍵列的值必須在主表參照列值的范圍內(nèi)，或者為空（也可以加非空約束，強(qiáng)制不允許為空）。當(dāng)主表的記錄被子表參照時(shí)，主表記錄不允許被刪除。外鍵參照的只能是主表主鍵或者唯一鍵，保證子表記錄可以準(zhǔn)確定位到被參照的記錄。格式FOREIGN KEY (外鍵列名)REFERENCES 主表(參照列)主鍵與外鍵數(shù)據(jù)表之間的關(guān)聯(lián)/引用關(guān)系是依靠具體的主鍵（primary key）和外鍵（foreign key）建立起來(lái)的。主鍵：幫助MySQL以最快的速度把一條特點(diǎn)的數(shù)據(jù)記錄的位置確定下來(lái)。主鍵必須是唯一的主

15、鍵應(yīng)該是緊湊的，因此整數(shù)類型比較適合外鍵：引用另外一個(gè)數(shù)據(jù)表的某條記錄。外鍵列類型盡可能與主鍵列類型保持一致外鍵列應(yīng)該加上NOT NULL主鍵create table student(sid int not null auto_increment,name varchar(20) not null,primary key(sid);外鍵（自動(dòng)檢查外鍵是否匹配，僅適用自動(dòng)檢查外鍵是否匹配，僅適用InnoDB）create table score(cid int not null auto_increment primary key,score int,sid int,foreign key(si

16、d) references student(sid);主表和從表1、當(dāng)主表中沒(méi)有對(duì)應(yīng)的記錄時(shí)，不能將記錄添加到子表成績(jī)表中不能出現(xiàn)在學(xué)員信息表中不存在的學(xué)號(hào)；2、不能更改主表中的值而導(dǎo)致子表中的記錄孤立把學(xué)員信息表中的學(xué)號(hào)改變了，學(xué)員成績(jī)表中的學(xué)號(hào)也應(yīng)當(dāng)隨之改變；3、子表存在與主表對(duì)應(yīng)的記錄，不能從主表中刪除該行不能把有成績(jī)的學(xué)員刪除了4、刪除主表前，先刪子表先刪學(xué)員成績(jī)表、后刪除學(xué)員信息表選擇主鍵的原則q最少性q盡量選擇單個(gè)鍵作為主鍵q穩(wěn)定性q盡量選擇數(shù)值更新少的列作為主鍵主鍵約束CREATE TABLE tb_student(id INT PRIMARY KEY AUTO_INCREMEN

17、T,NAME VARCHAR(18)外鍵約束CREATE TABLE tb_dept(dept_id INT PRIMARY KEY,NAME VARCHAR(18),description VARCHAR(255);CREATE TABLE tb_employee(employee_id INT PRIMARY KEY,NAME VARCHAR(18),gender VARCHAR(10),dept_id INT REFERENCES tb_dept(dept_id),address VARCHAR(255);外鍵約束CREATE TABLE tb_dept(dept_id INT PRIM

18、ARY KEY,NAME VARCHAR(18),description VARCHAR(255);CREATE TABLE tb_employee(employee_id INT PRIMARY KEY,NAME VARCHAR(18),gender VARCHAR(10),dept_id INT,address VARCHAR(255),CONSTRAINT FOREIGN KEY tb_employee_fk (dept_id) REFERENCES tb_dept(dept_id);Check 約束既可作為列級(jí)約束，也可作為表級(jí)約束定義在字段上的每一記錄都要滿足的條件在check中定義

19、檢查的條件表達(dá)式，數(shù)據(jù)需要符合設(shè)置的條件條件表達(dá)式不允許使用參照其他記錄的值CREATE TABLE tb_student(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(18),age INT CHECK(age 18 AND age 60)建立表之間的關(guān)系示例列級(jí)約束與表級(jí)約束列級(jí)約束直接跟在列后定義，不再需要指定列名，與列定義之間用空格分開表級(jí)約束通常放在所有的列定義之后定義，要顯式指定對(duì)哪些列建立列級(jí)約束。與列定義之間采用英語(yǔ)逗號(hào),隔開。如果是對(duì)多列建聯(lián)合約束，只能使用表級(jí)約束語(yǔ)法。增加約束可增加或刪除約束，但不能直接修改可使約束啟用和禁用非空約束必須使用MODIFY子句增加只要是可以使用列級(jí)約束語(yǔ)法來(lái)定義的約束，都可以通過(guò)modify來(lái)增加該約束。ALTER TABLE tableADD CONSTRAINT constraint type (column);增加約束加FOREIGN KEY 約束到EMP表ALTER TABLE tb_employeeADD CONSTRAINT tb_employee_