基礎 · 數據庫

[toc] ## 安裝 > [官方下載地址](http://www.jb51.net/article/96898.htm) > > [安裝注意事項](http://www.jb51.net/article/96898.htm) ## 數據庫的基本概念 ### 實體只要是在客觀世界存在的、可以被描述出來的都是實體 ### 數據庫數據庫就是數據的倉庫，可以存放結構化的數據 ### 數據庫管理系統(DBMS) 是一種系統軟件，提供操作數據庫的環境，可以通過數據庫管理系統對數據進行插入、修改、刪除和查詢等操作。 ### SQL 結構化查詢語言專門用來和數據庫進行交流的語言,幾乎所有的DBMS都支持SQ ![](http://img.zhufengpeixun.cn/dbmspng.png) #### SQL規范 - SQL語句不區分大小寫，建議SQL關鍵字大寫，表名和列表小寫 - 命令用分號結尾 - 命令可以縮進和換行，一種類型的關鍵字放在一行 - 可以寫單行和多行注釋 , #和--是單行注釋，/*/多行注釋 #### SQL組成 ##### DDL(data definition language) 是數據定義語言。主要的命令有`CREATE`、`ALTER`、`DROP`等，**DDL**主要是用在定義或改變（TABLE）的結構，數據類型，表之間的鏈接和約束等初始化工作上，他們大多在建立表時使用。 ##### DML(data manipulation language) manipulation:.操縱；操作；處理；篡改是數據庫操縱語言它們是`SELECT`、`UPDATE`、`INSERT`、`DELETE`，就像它的名字一樣，這4條命令是用來對數據庫里的數據進行操作的語言。 ##### DCL(DataControlLanguage) 是數據庫控制語言是用來設置或更改數據庫用戶或角色權限的語句，包括(`grant`、`revoke`等)語句 #### SQL運算符是一種符號，它是用來進行列間或則變量之間的比較和數學運算的。 ##### 算數運算符 `+,-,*,/,%` ![](https://box.kancloud.cn/cc799286b016247bb3a9215ad2e8fc31_347x286.png) **不能**直接將姓和名相加 ![](https://box.kancloud.cn/1e843425962fb2edf889a09bd184ac96_522x247.png) 需要使用內置函數 ![](https://box.kancloud.cn/e6fa109f052f2b8f0b5bbb28ad1419c7_659x161.png) ##### 邏輯運算符與比較運算符 `AND`，`OR`，`NOT` and ``` SELECT * FROM users WHERE age > 10 AND age < 80; ``` or ``` SELECT * FROM users WHERE age < 10 OR age > 80; ``` not ``` SELECT * FROM users WHERE NOT(age < 10 OR age > 80); ``` between 閉區間，包括30和50 ``` SELECT * FROM users WHERE age BETWEEN 30 AND 50 ``` 枚舉 30或則80歲 ``` SELECT * FROM users WHERE age in (30,80); ``` ## 數據表 - 表是數據庫中包含所有數據的數據庫對象，也是其它對象的基礎。 - 表定義是一個列的集合，數據在表中是按行和列的格式組織的，用來存放數據 - 行也稱為記錄用來存放一個個實體，列稱為字段用來描述實體的某一個屬性 ![](http://img.zhufengpeixun.cn/table.png) ## MYSQL配置 C:\Program Files\MySQL\MySQL Server 5.5\my.ini - port 端口號 - basedir 安裝目錄 - datadir 數據存放訪目錄(不是數據庫系統的的存放目錄) - charcter-set-server 字符集 - default-storage-engine 存儲引擎 - sql-mode 語法模式 - max-connections 最大連接數 ## MySQL啟動和停止 ``` net start MySQL net stop MySQL ``` 以配置文件啟動 ``` C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.5\my.ini" ``` ## 連接服務器 ``` mysql -h 127.0.0.1 -P 3306 -uroot -p123456 exit ``` ## 數據庫操作 ### 切換數據庫 ``` use test; ``` ### 顯示有哪些表 ``` show tables; show tables from mysql; ``` ### 顯示當前數據庫 ``` select database(); ``` ## 表操作 ### 查詢表結構 ``` desc user; ``` ### 創建表 ![](https://box.kancloud.cn/fc1d789d381e91feb31140e1ef0abb6c_340x299.png) ``` //1 3 代表初始值為1 步長為3 CREATE TABLE student( id init(11) NOT NULL PRIMARY KEY AUTO_INCREMENT 1 3, name varchar(50) NOT NULL, age init(11), city varchar(50) DEFAULT '北京' ) ``` #### 設置主鍵 ![](https://box.kancloud.cn/704bf0ba33cd9beff55ef9b5aa709a0d_402x316.png) #### 設置聯合主鍵 ### 查詢 ``` SELECT * FROM student; SELECT name,age FROM student; ``` ``` SELECT <列名> FROM <表名> [WHERE <查詢條件表達式>] [ORDER BY <排序的列名>[ASC或DESC]] ``` 雖然有些參數是可選的但順序是固定的，order不能在where前面可以給列名自定義名字 ![](https://box.kancloud.cn/c06431d7da5d39feefc55ff142723a3b_474x450.png) 增加一列固定值 ![](https://box.kancloud.cn/9f92cc90c9d4a6f29c8914c7a2584965_721x262.png) 分頁 `LIMIT skip_num,page_size` ![](https://box.kancloud.cn/3e5a4e37f9da8c1628f284182db6baf5_440x267.png) `DISTINCT` 取樣 ``` SELECT DISTINCT home FROM users ``` `ORDER BY`可以指定多個條件，先按第一個條件排，在第一個的基礎上再按第二個條件排 ![](https://box.kancloud.cn/66b7717fdff4de89520326995c43c1f3_452x332.png) ### 修改表 #### 增加一列 ``` ALTER TABLE student ADD COLUMN idcard varchar(15) NOT NULL; ``` #### 修改字段 ``` ALTER TABLE student MODIFY COLUMN idcard varchar(18) NOT NULL; ``` #### 刪除列 ``` ALTER TABLE student DROP COLUMN idcard varchar(18) NOT NULL; ``` #### 添加主鍵索引 ``` ALTER TABLE student ADD PRIMARY KEY(id); ``` #### 添加唯一索引 ``` ALTER TABLE student ADD UNIQUE INDEX unique_idcard(idcard) ``` 給`idcard`字段添加了一個名為`unique_idcard`的唯一索引。 #### 添加外鍵索引 constraint: 約束；局促，態度不自然；強制 ``` ALTER TABLE score ADD CONSTRAINT fk_student_id FOREIGN KEY(student_id) REFERENCE student(id) ``` 其中`student_id`為`score`表中的鍵，它是個子鍵，被主表(student)的`id`主鍵所約束。這意味著student中的id有什么，score表中的student_id才允許有什么，比如說student有id:1、2、3，那么score表中的student_id也頂多只能有1、2、3 另外要刪除表時，必須先刪除子表才能刪除主表。 ### 插入 ``` INSERT INTO users(firstname,lastname,email,age,birthday) VALUES('張','三','123@qq.com',100,'1984-12-30'); ``` - 插入的字段要與你`INTO xx()`選擇的字段一一對應 - 不指定字段時，`INTO xx()`相當于全字段 ![](https://box.kancloud.cn/b35cf83ca4151b715524638e60da689b_661x235.png) - 必須要有，即使用null占位，如果是null且對應id會自動生成 #### 其它注意事項 - 每次插入一行數據，不能只插入一部分數據，插入的數據是否有效將按照整行的完整性要求來檢驗 - 每個數據值的數據類型、精度、位數必須與要應的列名精確匹配 - 不能為標識符指定值 - 如果某字段設置為不能為空，則必須插入數據 - 插入數據時還要符合檢查性約束的要求 - 有缺省值的列，可以使用DEFAULT關鍵字來代替插入實際的值 ### 更新默認會全部更新？ So，需要搭配`where` ``` UPDATE users SET home = '上海',birthday='xxx' WHERE id = 6; ``` #### 注意事項 - 多列時用逗號隔開，一定要加更新條件以免錯誤更新 - 多個聯合條件使用 AND `id=7 and idcard='1231'` - 判斷某字段是否為空 `email is null or email =` ![](https://box.kancloud.cn/540b68f7e9a922b8ac69cc1fd0c27b6f_269x65.png) ![](https://box.kancloud.cn/35de033035a95ff3cbcb9d3b63296f70_301x65.png) ### 刪除 ``` DELETE [FROM] 表名 [WHERE <刪除條件>] ``` ``` DELETE FROM student WHERE id=7; ``` #### 注意事項 - Delete語句是對整行進行操作，因此不需要提供列名 - 如果要刪除主表數據，則要先刪除子表記錄 ### TRUNCATE 截斷表截斷整個表中的數據 ``` TRUNCATE TABLE 表名 ``` 和`DELETE`還有一點重要的區別在于會讓`id`的計數器重新開始計數。（無法再通過MySQL恢復，delete會有日志，盡量在生產環境中不要使用這個命令） ### 刪除表 ``` DROP TABLE student ``` ## 數據完整性 - 為了實現數據完整性，需要檢驗數據庫表中的每行和每列數據是否符合要求 - 在創建表的時候，應該保證以后的數據輸入是正確的，錯誤的數據不允許輸入 ### 域完整性不同的字段需要設置為各種合適的類型，比如年齡就是整數類型 ![](http://img.zhufengpeixun.cn/datatype.jpg) (mysql支持的所有數據類型) varchar(n)，長度不能大于n，大于n會報錯，小于n放幾個字符就是幾個字符 char(n)則是即使你只有一個字符，也會占用50個字符的空間 ### 默認值默認值是指如果用戶沒有指定值的情況下會記錄的此字段指定一個提供一個預先設定的值 ![](https://box.kancloud.cn/c63a1775f969a7e4718482d9b8de4d7c_548x377.png) ### 非空約束我們可以指定某個字段不能不輸入，必須提供一個非空的值 ![](https://box.kancloud.cn/242953f8fd520b28d399efa4c6015b81_507x112.png) ## 實體完整性 ### 主鍵約束 #### 主鍵表中一列或者幾列組合的值能用來唯一標識表中的每一行，這樣的列或者列組合稱為表的主鍵，主鍵表的數據不能重復。 >主鍵的選擇標準 - 最少性盡量選擇單個鍵作為主鍵 - 穩定性，由于主鍵是用來在兩個表間建立聯接的，所以不能經常更新，最好就不更新 ### 外鍵成績表中的學生ID應該在學生表中是存在的我們應該讓成績表中的ID只能引用學生表中的ID，它們的值應該是一一對應的，也就是說成績表中的ID是成績表中的外鍵，對應學生表的主鍵，這樣就可以保證數據的引用完整性 ### 唯一索引唯一約束是指某個字段值是唯一的，在所有的記錄中不能有重復的值. ![](https://box.kancloud.cn/01a068bf355b769d992ce692cd512b88_457x135.png) 索引名字隨便取 ### 標識列標識列，即id,用來標識每一條數據當表中沒有合適的列作為主鍵時可以考慮增加標識列，標識列是一個無實際業務含義的列，僅僅用來區分每條記錄。標識列的值是自動生成的，不能在該列上輸入數據 >思考: 如果標識列id的初始值為1，增長量為3，則輸入3行數據以后，再刪除1行，下次再輸入數據行的時候，標識值自動插入的值是多少? //->4 不會回收利用 ### 外鍵約束一個表的外鍵必須引用另一個表的主鍵,比如成績表中的學生ID會引用學生表的主鍵，課程ID會引用成績表的主鍵 - 主表沒有記錄，子表中不能添加相應的記錄 - 修改和刪除主表記錄不能讓子表記錄孤立，必須相應修改和刪除 - 數據操作 8.1 創建學生表 ![](https://box.kancloud.cn/3739470aa82d5b66bdb592ff055c0741_612x199.png) (fk,foreign key的意思，不是固定的隨便取) 另外要刪除表時，必須先刪除子表才能刪除主表。 ## 函數函數不僅在`select`能用，在哪都可以用 ### length() ![](https://box.kancloud.cn/3a4d5902820d7e87c9392e5fc3fa5244_411x226.png) ### upper/lower() ### substr() 第一種和第二種是等價的，第是哪個參數為截取的長度 ![](https://box.kancloud.cn/4b2e385202842b4ebcff1e20fcc6a7bb_464x106.png) ### INSTR() 相當于indexOf ``` SELECT INSTR(email,'@') FROM student; ``` ### LPAD/RPAD 等同于padsStartsWith ``` SELECT LPAD('ahh',10,'0') ``` ### replace ``` SELECT REPPLACE('ahhh','a','A'); //->Ahhh ``` ### round ### ceil ### floor ### truncate 不管怎樣，一律舍去 ``` SELECT TRUNCATE(2.668,2); //->2.66 ``` ### mod 取余 ``` SELECT MOD(-10,3); //->-1 ``` ### now 返回當前事件，年月日時分秒 ![](https://box.kancloud.cn/69fa1e580c61640466033dd093173049_250x171.png) ### curdate 年月日 ### curtime 時分秒 ### year(now()) 年 ### month(now()) ### day(now()) ### hour(now()) ### minute(now()) ### second(now()) ### str_to_date() ``` str_to_date('2018-09-03 10-20-16','%m-%d-%Y $s-$i-$H') ``` | 序號 |格式符 |功能 | | --- | --- | --- | |1 | %Y |4位的年份 | | 2 | %y |2位的年份 | | 3|%m |月份(01,02) | |4 |%c |月份(1,2) | | 5 | %d |日(01,02) | | 6 |%H |小時(24小時制) | | 7 |%h |小時(12小時制) | | 8| %i |分鐘(00,01) | | 9 | %s |秒(00,01) | ![](https://box.kancloud.cn/2c285b74536e85045f00c6cdbc4ca620_405x69.png) ### version() 數據庫版本 ->5.6.0推薦 ### database() 返回當前數據庫 ### FORMAT(1000.111,n) 保留n位小數 ![](https://box.kancloud.cn/50a407a7d62f286a73ded3fa3e949910_558x307.png) ### LEFT/RIGHT ![](https://box.kancloud.cn/c6206c01dc63c957a53421d4bd1cc998_507x234.png) ### 數學函數 ### 日期函數 ![](https://box.kancloud.cn/4b206e6b5543fc317669c853e82a58ee_659x133.png) ### connection_id(); ### user ![](https://box.kancloud.cn/4c6399a376d5fd87f8aafb906f7b3c16_371x123.png) ### 其它函數 ``` SELECT CONNECTION_ID(); SELECT DATABASE(); SELECT VERSION(); select LAST_INSERT_ID(); SELECT USER(); SELECT MD5('123456');//摘要算法 SELECT PASSWORD('123456');//修改當前用戶的密碼 SELECT User,Password from mysql.user; ``` ### 流程控制函數 mysql中相等是一個等號 if ``` //類似于三元 SELECT IF(gender=1,'男','女') FROM student; ``` case ``` CASE WHEN 常量1 then 要顯示的值或語句1 WHEN 常量2 then 要顯示的值或語句2 ELSE 要顯示的值 END SELECT CASE WHEN grade<60 then '不及格' WHEN grade>=60 then '及格' ELSE '未知' END FROM score; ``` 當`case`后面根了個變量時，when后只能放常量，但如果`case`后沒有東西，那么when后就可以帶上變量 ![](https://box.kancloud.cn/eb7d0fe28adcfb99fb6dafc7138b77f1_483x428.png) ### 自定義函數只有返回值的話可以不用大括號直接放函數體 case1 ``` CREATE FUNCTION znow() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(),'%Y年%m月%d日 %H點:%i分:s秒'); SELECT znow1(); ``` case2 ``` CREATE FUNCTION zadd(num1 INT,num2 INT) RETURNS INT RETURN num1+num2; SELECT zadd1(1,2); ``` case3 有兩個語句必須要包一下，用的是`BEGIN`和`END` ``` CREATE TABLE stu(id int PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50)); CREATE FUNCTION addUser(name VARCHAR(50)) RETURNS INT BEGIN INSERT INTO stu(name) VALUES(name); RETURN LAST_INSERT_ID(); END SELECT addUser('zfpx'); DROP FUNCTION addUser ``` ## 模糊查詢就是查詢的條件是模糊的，不是特別明確的 ### 通配符 ![](http://img.zhufengpeixun.cn/matchchar.png) ### BETWEEN AND ``` select * from score where grade between 80 and 100 ``` ### IN ``` select * from student where city in ('北京','上海','廣東') ``` ### IS NUL - 查詢沒有郵箱的 IS NUL - 查詢有郵箱的 IS NOT NULL