[TOC] ## **數據庫設計** **E-R模型** E-R模型的基本元素是：實體、聯系和屬性 * E表示entry，實體：一個數據對象，描述具有相同特征的事物 * R表示relationship，聯系：表示一個或多個實體之間的關聯關系，關系的類型包括包括一對一、一對多、多對多 * 屬性：實體的某一特性稱為屬性 關系也是一種數據，需要通過一個字段存儲在表中 1、實體A對實體B為1對1，則在表A或表B中創建一個字段，存儲另一個表的主鍵值  2、實體A對實體B為1對多：在表B中創建一個字段，存儲表A的主鍵值  3、實體A對實體B為多對多：新建一張表C，這個表只有兩個字段，一個用于存儲A的主鍵值，一個用于存儲B的主鍵值  <br /> ### **數據庫** * 查看所有數據庫 ``` show databases; ``` * 使用數據庫 ``` use 數據庫名; ``` * 查看當前使用的數據庫 ``` select database(); ``` * 創建數據庫 ``` create database 數據庫名 charset=utf8; 例： create database ceshi charset=utf8; ``` * 刪除數據庫 ``` drop database 數據庫名; 例： drop database ceshi; ``` ### **數據表** * 查看當前數據庫中所有表 ``` show tables; ``` * 查看表結構 ``` desc 表名; ``` * 查看表的創建語句 ``` show create table 表名; 例： show create table students; ``` <br /> ## **函數** ### **內置函數** **字符串函數** * 拼接字符串concat(str1,str2...) ``` select concat(12,34,'ab'); ``` 案例:體現類似"王昭君的家鄉是北京"的功能. * 包含字符個數length(str) ``` select length('abc'); ``` 案例:查找班級里邊名字為兩個字的所有學生信息 * 截取字符串 * * left(str,len)返回字符串str的左端len個字符 * * right(str,len)返回字符串str的右端len個字符 * * substring(str,pos,len)返回字符串str的位置pos起len個字符 ``` select substring('abc123',2,3); ``` 案例:實現王昭君叫王某某,張飛叫張某某的功能. * 去除空格 * * ltrim(str)返回刪除了左空格的字符串str * * rtrim(str)返回刪除了右空格的字符串str ``` select ltrim(' bar '); ``` 案例:實現左右空格都去掉的功能 * 大小寫轉換，函數如下 * * lower(str) * * upper(str) ``` select lower('aBcD'); ``` <br /> ### **數學函數** * 求四舍五入值round(n,d)，n表示原數，d表示小數位置，默認為0 ``` select round(1.6); ``` * 求x的y次冪pow(x,y) ``` select pow(2,3); ``` * 獲取圓周率PI() ``` select PI(); ``` * 隨機數rand()，值為0-1.0的浮點數 ``` select rand(); ``` 案例1:實現0-10之間的隨機數 案例2:做出一個從學生中抽獎的功能 <br /> ### **日期時間函數** * 當前日期current_date() ``` select current_date(); ``` * 當前時間current_time() ``` select current_time(); ``` * 當前日期時間now() ``` select now(); ``` * 日期格式化date_format(date,format) * 參數format可選值如下 ``` %Y 獲取年，返回完整年份 %y 獲取年，返回簡寫年份 %m 獲取月，返回月份 %d 獲取日，返回天值 %H 獲取時，返回24進制的小時數 %h 獲取時，返回12進制的小時數 %i 獲取分，返回分鐘數 %s 獲取秒，返回秒數 ``` * 例：將使用-拼接的日期轉換為使用空格拼接 ``` select date_format('2016-12-21','%Y %m %d'); ``` <br /> ## **索引** * 思考：在圖書館中是如何找到一本書的？ * 一般的應用系統對比數據庫的讀寫比例在10:1左右，而且插入操作和更新操作很少出現性能問題，遇到最多的，也是最容易出問題的，還是一些復雜的查詢操作，所以查詢語句的優化顯然是重中之重 * 當數據庫中數據量很大時，查找數據會變得很慢 * 優化方案：索引 語法 * 查看索引 ``` show index from 表名; ``` * 創建索引 方式一：建表時創建索引 ``` create table create_index( id int primary key, name varchar(10) unique, age int, key (age) ); ``` 方式二：對于已經存在的表，添加索引 ``` 如果指定字段是字符串，需要指定長度，建議長度與定義字段時的長度一致 字段類型如果不是字符串，可以不填寫長度部分 create index 索引名稱 on 表名(字段名稱(長度)) 例： create index age_index on create_index(age); create index name_index on create_index(name(10)); ``` * 刪除索引： ``` drop index 索引名稱 on 表名; ``` **示例** **導入測試表test_index** ``` 右鍵點擊某個數據庫->運行sql文件->選擇test_index.sql->點擊開始 ``` 查詢 * 開啟運行時間監測： ``` set profiling=1; ``` * 查找第1萬條數據test10000 ``` select * from test_index where title='test10000'; ``` * 查看執行的時間： ``` show profiles; ``` * 為表title_index的title列創建索引： ``` create index title_index on test_index(title(10)); ``` * 執行查詢語句： ``` select * from test_index where title='test10000'; ``` * 再次查看執行的時間 ``` show profiles; ``` 缺點 * 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行INSERT、UPDATE和DELETE，因為更新表時，MySQL不僅要保存數據，還要保存一下索引文件 * 但是,在互聯網應用中,查詢的語句遠遠大于增刪改的語句,甚至可以占到80%~90%,所以也不要太在意,只是在大數據導入時,可以先刪除索引,再批量插入數據,最后再添加索引 <br /> ## **外鍵** **外鍵foreign key** * 如果一個實體的某個字段指向另一個實體的主鍵，就稱為外鍵。被指向的實體，稱之為主實體（主表），也叫父實體（父表）。負責指向的實體，稱之為從實體（從表），也叫子實體（子表） * 對關系字段進行約束，當為從表中的關系字段填寫值時，會到關聯的主表中查詢此值是否存在，如果存在則填寫成功，如果不存在則填寫失敗并報錯 語法 * 查看外鍵 ``` show create table 表名 ``` * 設置外鍵約束 方式一：創建數據表的時候設置外鍵約束 ``` create table class( id int unsigned primary key auto_increment, name varchar(10) ); create table stu( name varchar(10), class_id int unsigned, foreign key(class_id) references class(id) ); foreign key(自己的字段) references 主表(主表字段) ``` 方式二：對于已經存在的數據表設置外鍵約束 ``` alter table 從表名 add foreign key (從表字段) references 主表名(主表字段); alter table stu add foreign key (class_id) references class(id); ``` * 刪除外鍵 ``` -- 需要先獲取外鍵約束名稱 show create table stu; -- 獲取名稱之后就可以根據名稱來刪除外鍵約束 alter table 表名 drop foreign key 外鍵名稱; alter table stu drop foreign key stu_ibfk_1; ``` > 在實際開發中,很少會使用到外鍵約束,會極大的降低表更新的效率