5.1、常用函數
-- ==============常用函數=============== -- 數字運算 SELECT ABS(-7) -- 絕對值 SELECT CEILING(9.4) -- 向上取整 SELECT FLOOR(9.4) -- 向下取整 SELECT RAND() -- 傳回一個0~1之間的随機數 SELECT SIGN(10) -- 判斷一個數的符号 負數傳回-1，正數傳回1 -- 字元串函數 SELECT CHAR_LENGTH('我愛資料庫') -- 字元串長度 SELECT CONCAT('我','愛','你們') -- 拼接字元串 SELECT LOWER('ZhangGuorong') -- 小寫字母 SELECT UPPER('ZhangGuorong') -- 大寫字母 SELECT INSERT('我愛程式設計helloWorld',1,2,'超級熱愛') -- 查詢，從某個位置開始替換某個長度 SELECT SUBSTR('榮哥說堅持就能成功',4,6) -- 傳回指定的子字元串 SELECT REPLACE('榮哥說堅持就能成功','堅持','努力') -- 替換出現的指定字元串 SELECT REVERSE('你愛我') -- 反轉 -- 查詢 姓 周的同學 ,把周替換成張 SELECT REPLACE(studentName,'周','張') FROM student WHERE studentName LIKE '周%' -- 時間和日期函數 （記住） SELECT CURRENT_DATE() -- 擷取目前日期 SELECT LOCALTIME() -- 本地時間 SELECT SYSDATE() -- 系統時間 SELECT YEAR(NOW()) -- 顯示目前年份 SELECT DAY(NOW()) -- 天 SELECT SECOND(NOW()) -- 秒 -- 系統 SELECT VERSION() SELECT USER() SELECT SYSTEM_USER() 5.2、聚合函數(常用)
| 函數名稱 | 描述 |
| count() | 計數 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
| ... |
5.3資料庫級别的MD5加密（擴充）
什麼是MD5
主要增強算法的複雜性和不可逆性
MD5 不可逆，具體的值的 MD5 是一樣的
MD5 破解網站的原理，背後有一個字典，MD5加密後的值，加密前的值
-- 加密 UPDATE student SET pwd=MD5(pwd) WHERE id=1 UPDATE student SET pwd=MD5(pwd) -- 加密全部的密碼 -- 插入的時候加密 INSERT INTO student (`id`,`name`,`pwd`) VALUES('13','胡',MD5('123456')) -- 如何校驗：将使用者傳遞進來的密碼，進行md5加密，然後比對加密後的值 SELECT * FROM student WHERE `name` = '胡' AND pwd=MD5('123456') select總結
6、事務
6.1、什麼是事務
要麼都成功，要麼都失敗
事務原則：ACID原則 原子性，一緻性，隔離性，持久性 （髒讀，幻讀。。。） 原子性：要麼都成功，要麼都失敗
一緻性：事務前後的資料完整性要保證一緻
持久性：事務一旦送出則不可逆，被持久化到資料庫中
隔離性：事務的隔離性是多個使用者并發通路資料庫時，資料庫為每一個使用者開啟的事務，不能被其他事務的操作數資料所幹擾，多個并發事務之間要互相隔離
-- 模拟轉賬：事務 SET autocommit = 0 -- 關閉自動送出 START TRANSACTION -- 開啟一個事務 UPDATE `account` SET money = money-500 WHERE `name` = 'A' -- A減500 UPDATE `account` SET money = money+500 WHERE `name` = 'B' -- B加500 COMMIT; -- 送出事務,就被持久化了，這時候在按復原就回不去了 ROLLBACK; -- 復原 SET autocommit = 1; -- 恢複預設值 7、索引
MySQL官方對索引的定義為：索引（index）是幫助MySQL高效擷取資料的資料結構。 提取句子主幹，就可以得到索引的本質：索引是資料結構 7.1、索引的分類
在一個表中，主鍵索引隻能有一個，唯一索引可以有多個 - 主鍵索引 （PRIMARY KEY）
- 唯一的辨別，主鍵不可重複，隻能有一個列作為主鍵
- 唯一索引（UNIQUE KEY）
- 避免重複的列出現，唯一索引可以重複，多個列都可以辨別位 唯一索引
- 正常索引 (KEY/INDEX)
- 預設的， index、key關鍵字來設定
- 全文索引（FullText）
- 在特定的資料庫引擎下才有，MyISAM
- 快速定位資料
8、權限管理和備份
8.1、使用者管理
SQL yog 可視化管理 
SQL指令操作 使用者表：mysql.user
本質：讀這張表進行增删改查
-- 建立使用者 create user 使用者名 identified by '密碼' CREATE USER kuangshen IDENTIFIED BY '123456' -- 修改密碼（修改目前使用者密碼） SET PASSWORD = PASSWORD('123456') -- 修改密碼（修改指定使用者密碼） SET PASSWORD FOR kuangshen = PASSWORD('111111') -- 重命名 RENAME USER kuangshen TO zhang -- 使用者授權 all privileges 全部的權限， 庫.表 -- all privileges 除了給别人授權，其他都能幹 GRANT USER PRIVILEGES ON *.* TO kuangshen2 -- 查詢權限 SHOW GRANTS FOR kuangshen2 -- 檢視指定使用者權限 SHOW GRANTS FOR root@localhost -- ROOT使用者權限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION -- 撤銷權限 revoke 哪些權限，在哪個庫撤銷，給誰撤銷 REVOKE ALL PRIVILEGES ON *.* FROM kuangshen2 -- 删除使用者 DROP USER kuangshen 8.2、MySQL備份
為什麼要備份：
- 保證重要的資料不丢失
- 資料轉移
MySQL資料庫備份的方式
- 直接拷貝實體檔案（在date檔案裡面）
- 在Sqlyog這種可視化工具中手動導出
- 右鍵點選表，選擇備份或導出
- 使用指令行導出 mysqldump 指令行使用（cmd）
# mysqldump -h 主機 -u 使用者名 -p 密碼 資料庫 表名 >實體磁盤位置/檔案名 mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql # mysqldump -h 主機 -u 使用者名 -p 密碼 資料庫 表1 表2 表3 >實體磁盤位置/檔案名 mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql # mysqldump -h 主機 -u 使用者名 -p 密碼 資料庫>實體磁盤位置/檔案名 mysqldump -hlocalhost -uroot -p123456 school>d:/a.sql #導入（先在指令行登入資料庫，然後在use 要導入的資料庫後在執行以下操作） #登入的情況中，切換到指定的資料庫 #source 備份檔案 1.source d:/a.sql （推薦使用） 2.mysql -u使用者名 -p密碼 庫名< 備份檔案（不推薦） 假設你要備份資料庫，防止資料丢失。
9、規範資料庫設計
9.1、為什麼需要設計
當資料庫比較複雜的時候，我們就需要設計了
糟糕的資料庫設計：
- 資料備援，浪費空間
- 資料庫插入和删除都會麻煩、異常【屏蔽使用實體外鍵】
- 程式的性能差
良好的資料庫設計：
- 節省記憶體空間
- 保證資料庫的完整性
- 友善我們開發系統
軟體開發中，關于資料庫的設計
- 分析需求：分析業務和需要處理的資料庫需求
- 概要設計：設計關系圖E-R圖
9.2、三大範式
為什麼需要資料規範化？
- 資訊重複
- 更新異常
- 插入異常（無法正常顯示資訊）
- 删除異常（丢失有效的資訊）
三大範式（了解） 第一範式（1NF）
原子性：保證每一列不可再分
第二範式（2NF）
前提：滿足第一範式
每張表隻描述一件事情
第三範式（3NF）
前提：滿足第一、er範式
第三範式需要確定資料表中的每一列資料都和主鍵直接相關，而不能間接相關。
規範性 和 性能的問題
關聯查詢的表不得超過三張表
- 考慮商業化的需求和目标，（成本，使用者體驗！）資料庫的性能更加重要
- 在規範性能的問題的時候，需要适當的考慮以下規範性！
- 故意給某些增加一些備援的字段。（從多表查詢中變為單表查詢）
- 故意增加一些計算列（從大資料量降低為小資料量的查詢：索引）