當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

mysql数据库应用_MySQL数据库应用从入门到精通学习笔记

發布時間：2023/12/3 数据库 26 豆豆

生活随笔收集整理的這篇文章主要介紹了 mysql数据库应用_MySQL数据库应用从入门到精通学习笔记小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

以下內容是學習《MySQL數據庫應用從入門到精通》過程中總結的一些內容提要，供以后自己復現使用。

一：數據庫

查看所有數據庫:SHOW DATABASES

創建數據庫:CREATE DATABSE database_name

切換數據庫:USE database_name

刪除數據庫:DROP DATABASE database_name

二：存儲引擎

查看MYSQL支持的引擎:SHOW ENGINES \G (\G 使顯示格式更好看)

查看所支持的存儲引擎: SHOW VARIABLES LIKE 'have%';

查看默認的存儲引擎：SHOW VARIABLES LIKE 'storage_engine%';

經試驗，自己mysql上運行應該是: SHOW VARIABLES LIKE 'default_storage_engine%'

修改默認存儲引擎:方法1：向導模式修改方法2：修改配置文件my.ini中default-storage-engine

三：數據類型

數據類型字節

TINYINT 1

SMALLINT 2

MEDIUMINT 3

INT 和 INTEGER 4

BIGINT 8

FLOAT 4

DOUBLE 8

DECI(M,D) M+2

DECIMAL(M,D) M+2

存儲小數用float，需要精確到小數點后10位，用double；需要更精確的，用decimal

BIT(M) 1~8

DATE 4

DATETIME 8

TIMESTAMP 4

TIME 3

YEAR 1

年月日用DATE;年月日時分秒用DATETIME;經常插入或更新日期為當前系統時間，用TIMESTAMP;時分秒用TIME;年份用YEAR。

存儲的日期需要讓不同地區矠用戶使用，則可以使用TIMESTAMP，因為只有該類型日期能夠與實際時區相對應。

eg:

create table t_data_test(

f_date DATE,

f_datetime DATETIME,

f_timestamp TIMESTAMP,

f_time TIME,

f_year YEAR);

SELECT CURDATE(),NOW(),NOW(),TIME(NOW()),YEAR(NOW());

結果:

CURDATE()NOW()NOW()TIME(NOW())YEAR(NOW())

12/1/201712/1/2017 20:16:5612/1/2017 20:16:5620:16:562017

INSERT INTO t_data_test VALUES(CURDATE(),NOW(), NOW(), time(NOW()), YEAR(NOW()));

SELECT * FROM t_data_test;

f_datef_datetimef_timestamp f_timef_year

12/1/201712/1/2017 20:13:37 12/1/2017 20:13:3720:13:37 2017

CHAR(M) M

VARCHAR(M) M

TINYTEXT 0~255

TEXT0~65535

MEDIUMTEXT0~167772150

LONGTEXT0~4294967295

存文本文件

BINARY(M)M

VARBINARY(M)M

存二進制數據(如圖片、音樂、視屏文件)

TINYBLOB 0~255

BLOB0~2^16

MEDIUMBLOB0~2^24

LONGBLOB0~2^32

存大量二進制數據(如電影等視屏文件)

四：表

創建表：

CREATE TABLE table_name(

屬性名數據類型,

屬性名數據類型

)

查看表:

DESCRIBE table_name;

查看表詳細定義(創建語句):

SHOW CREATE TABLE table_name (\G);

刪除表:

DROP TABLE table_name;

修改表：

改表名: ALTER TABLE old_table_name RENAME [TO] new_table_name;

在表的最后一個位置增加字段: ALTER TABLE table_name ADD 屬性名數據類型;

在表的最后一個位置增加字段: ALTER TABLE table_name ADD 屬性名數據類型 FIRST;

在表的指定字段之后增加字段: ALTER TABLE table_name ADD 屬性名數據類型 AFTER 屬性名;

刪除字段：ALTER TABLE table_name DROP 屬性名;

修改字段：ALTER TABLE table_name MODIFY 屬性名數據類型;

修改字段名字：ALTER TABLE table_name CHANGE 舊屬性名新屬性名數據類型;

同時修改字段名字和屬性：ALTER TABLE table_name CHANGE 舊屬性名新屬性名新數據類型;

修改字段的順序：ALTER TABLE table_name MODIFY 屬性名1 數據類型 FIRST

或: ALTER TABLE table_name MODIFY 屬性名1 數據類型 AFTER 屬性名2

五：表約束

NOT NULL

DEFAULT

UNIQUE KEY(UK)

PRIMARY KEY(PK)

FOREIGH KEY(FK)

AUTO_INCREMENT

eg:

CREATE TABLE table_name(

屬性名數據類型 NOT NULL,

屬性名數據類型 DEFAULT 默認值,

屬性名數據類型 UNIQUE,

屬性名數據類型 PRIMARY KEY,

屬性名數據類型 AUTO_INCREMENT

)

或將約束條件寫在后面：

CREATE TABLE table_name(

屬性名數據類型 NOT NULL,

屬性名數據類型 DEFAULT 默認值,

屬性名數據類型,

CONSTRAINT UK約束名 UNIQUE(dname)，

CONSTRAINT PK約束名 PRIMARY KEY(屬性名，屬性名……),

CONSTRAINT FK約束名 FOREIGH KEY(屬性名1) REFERENCES 表名(屬性名2)

)

六：索引

表是存儲和操作數據的邏輯結構，而索引則是一種有效組合數據的方式。通過索引對象，可以快速查詢到表中矠特定記錄，是一種提高性能的常用方式。一個索引會包含表中按照一定順序排序的一列或多列字段。

索引按照存儲類型，可以分為B型樹索引(BTREE)和哈希索引(HASH). InnoDB和MyISAM存儲引擎支持BTREE類型索引，MEMORY存儲引擎支持HASH類型索引。默認為前者索引。

索引除了可以提高數據庫管理系統的查找速度，還可以保證字段的唯一性，從而實現數據庫表的完整性。創建索引可以提高查詢速度，但過多的創建索引則會占據許多磁盤空間。

以下情況適合創建索引：

1)經常被查詢的字段，即在where子句中出現的字段；

2)分組的字段，即在group by子句中出現的字段；

3)存在依賴關系的子表和父表之間的聯合查詢，即主鍵或外鍵字段。

4)設置唯一完整性約束的字段。

以下情況不適合創建索引：

1)在查詢中很少被使用的字段；

2)擁有許多重復值的字段。

索引可以分為普通索引、全文索引、單列索引、多列索引和空間索引。

創建(3種方式)：

CREATE TABLE table_name(

屬性名數據類型,

……

屬性名數據類型,

【UNIQUE|FULLTEXT】INDEX | KEY [索引名] (屬性名【(長度)】【ASC|DESC】, ...)

);

CREATE 【UNIQUE|FULLTEXT】INDEX 索引名 ON 表明 (屬性名【(長度)】【ASC|DESC】, ...)

ALTER TABLE table_name ADD 【UNIQUE|FULLTEXT】INDEX|KEY 索引名(屬性名【(長度)】【ASC|DESC】, ...)

eg: CREATE TABLE t_dept(

deptno INT,

dname VARCHAR(20),

loc VARCHAR(40),

INDEX index_deptno(deptno)

);

唯一索引要加UNIQUE修飾符，全文索引加FULLTEXT修飾符。普通索引和多列索引不需要加。多列索引后面是多個列名，中間用,分隔。

查看索引:

SHOW CREATE TABLE table_name \G;

校驗索引是否被啟用:

EXPLAIN SELECT * FROM t_dept WHERE deptno = 1\G;

刪除索引:

DROP INDEX index_name ON table_name；

七、視圖

視圖可以提高復雜SQL語句矠復用性和表操作的安全性。視圖本質上是一種虛擬表，其內容與真實的表相似，但視圖并不在數據庫中以存儲的數據值形式存在。行和列數據來自定義視圖的查詢所引用基本表，并且在具體引用視圖時動態生成。

視圖特點如下：

1)視圖的列可以來自不同的表，是表的抽象和在邏輯意義上建立的新關系；

2)視圖是由基本表(實表)產生的表(虛表)；

3)視圖的建立和刪除不影響基本表；

4)對視圖內容的更新(添加、刪除和修改)直接影響基本表；

5)當視圖來自多個基本表時，不允許添加和刪除數據。

創建視圖：

CREATE VIEW view_name AS 查詢語句;

查看視圖:

SHOW TABLES;

DESCRIBE view_name;

SHOW CREATE VIEW view_name;

select * from information_schema.views where table_name like 'v%'

查看視圖詳細信息:

SHOW TABLE STATUS 【FROM db_name】【LIKE 'pattern'】

刪除視圖:

DROP VIEW view_name【,view_name2, ...】

修改視圖:

CREATE OR REPLACE VIEW view_name AS 查詢語句

ALTER VIEW view_name as 查詢語句

或：先刪除后創建新的

利用視圖操作基本表：

和操作實表一樣，只不過將表名換成了視圖名。

八、觸發器

經常使用觸發器是因為觸發器可以加強數據庫表中數據的完整性約束和業務規則等。

觸發器使用地方：DELETE、INSERT、UPDATE。

觸發器中可以寫一行語句，也可以寫多行語句。

每張表只能針對DELETE、INSERT、UPDATE三個事件的BEFORE和AFTER兩個時間點添加最多六個(3event*2時間點)觸發器。

創建觸發器:

CREATE TRIGGER trigger_name

BEFORE|AFTER trigger_event

ON table_name FOR EACH ROW

【BEGIN】

trigger_stmt;

【END】

其中trigger_event就是DELETE、INSERT、UPDATE；trigger_stmt就是要執行的語句,單行語句不需要加BEGIN END，多行則需要。

eg:

每次t_dept表插入數據前都在t_diary日志表中增加一條日志記錄。

CREATE TRIGGER tri_diarytime

BEFORE INSERT

ON t_dept FOR EACH ROW

INSERT INTO t_diary VALUES(NULL,'t_dept',now());

DELIMITER $$

CREATE TRIGGER tri_diarytime

BEFORE INSERT

ON t_dept FOR EACH ROW

BEGIN

INSERT INTO t_diary VALUES(NULL,'t_dept',now());

END

DELIMITER ;

寫多行語句前要通過DELIMITER更換分隔符或還原分隔符。

查看觸發器：

SHOW TRIGGERS;

SELECT * FROM information_schema.triggers where trigger_name like 'pattern';

刪除觸發器:

DROP TRIGGER trigger_name;

九、插入、更新、刪除數據

插入完整數據記錄:

INSERT INTO table_name VALUES(value1,value2...);

插入數據記錄一部分:

INSERT INTO table_name(field1,field2,...) VALUES(value1,value2,...);

插入多條數據:

INSERT INTO table_name VALUES(value11,value12...),(value21,value22...),...;

INSERT INTO table_name(field1,field2,...) VALUES(value11,value12...),(value21,value22...),...;

插入查詢結果:

INSERT INTO table_name(field1,field2,...)

SELECT (field1,field2,...)

FROM table_name2 WHERE ...

注意:自增的字段或有默認值的字段，可以采用插入數據記錄的一部分的方法插入，這種方法還不依賴表字段的順序，可擴展性好，建議使用。

更新數據:

UPDATE table_name

SET field1 = value1,

SET field2 = value2,

...

WHERE CONDITION;

刪除數據:

DELETE FROM table_name WHERE CONDITION;

刪除整張表里的數據：

DELETE FROM table_name;

TRUNCATE table_name；(速度更快，建議使用，原理：刪除整張表，然后再根據原表DLL語句創建一個一模一樣的表)

十、單筆數據記錄簡單查詢

簡單查詢使用方式包括:

1)簡單數據查詢；

2)避免重復數據查詢；

3)實現數學四則運算數據查詢； +-*/%

4)設置顯示格式數據查詢。

eg:

SELECT * FROM t_dept;

SELECT DISTINCT filed1,field2 ... fieldn FROM table_name; (多個字段組合起來唯一)

SELECT ename, sal*12 AS yearsalary FROM t_employee;

SELECT CONCAT(ename, '雇員的年薪為: '， sal*12) yearsalary FROM t_employee;

條件數據查詢:

單條件

多條件

between and

NOT

IS NULL / IS NOT NULL

IN / NOT IN (使用IN時，查詢的集合(IN后面的枚舉值)中如果存在NULL，則不會影響查詢；如果使用關鍵字NOT IN，查詢的集合中如果存在NULL，則不會查詢到任何數據)

LIKE / NOT LIKE _ %

排序:

SELECT field1,field2...fieldn

FROM table_name

WHERE CONDITION

ORDER BY fieldm1 [ASC|DESC], fieldm2 [ASC|DESC] ...

如果字段的值為空值(NULL)，則該值為最小值，因此在降序排序中將最后顯示，在升序排序中將最先顯示。

限制數據記錄查詢數量

SELECT field1,field2...fieldn

FROM table_name

WHERE CONDITION

LIMIT offset_start,row_count;

使用方式:

1)不指定初始位置，只指定行數。 LIMIT row_count;

2)指定初始位置，指定行數。 LIMIT offset_start,row_count; 不包括offset那一行

統計函數：

count() avg() sum() max() min()

count(*):對表中數據進行統計，不管表字段中包含的是NULL值還是非NULL值；

count(field):對指定字段的記錄進行統計，忽略NULL值。

其他幾個統計函數，必須指定字段，所以忽略NULL值。

如果所操作的表中沒有任何數據記錄，則count()函數返回0，其他統計函數返回NULL。

分組數據記錄查詢：

SELECT function()

FROM table_name

WHERE CONDITION

GROUP BY field1,field2...

HAVING CONDITION

在具體進行分組查詢時，分組所依賴的字段上的值一定要具有重復值，否則將沒有任何意義。

首先針對field1進行分組，然后針對每組按照字段field2進行分組，以此類推。

SELECT * FROM table_name GROUP BY CONDITION; 是從分組中隨機選擇一條記錄顯示。所以group by 要和統計函數一起使用才有意義。

SELET deptno,GROUP_CONCAT(ename),COUNT(ename) number enames FROM t_employee GROUP BY deptno;

會將每組中的ename組合在一起顯示.

結果:

deptnoenamesnumber

10MILLER,KING,CLARK3

20FORD,ADAMS,SCOTT,JONES,SMITH4

30BLAKE,MARTIN2

十一、多表數據記錄查詢：

在具體應用中如果需要實現多表數據記錄查詢，一般不使用連接查詢，因為該操作效率比較低，而是使用子查詢操作。進行連接操作時，會求笛卡爾積，如果表數據記錄多，則可能會造成死機。

MYSQL在具體實現連接查詢操作時，首先將兩個或兩個以上的表按照某個條件連接起來，然后再查詢到所要求的數據記錄。

連接操作是關系數據庫操作中專門用于數據庫操作的關系運算，包括并(UNION)、笛卡爾積(CARTESIAN PRODUCT)和專門針對數據庫操作的運算-連接(JOIN)。

并(UNION)：把具有相同字段數目和字段類型的表合并到一起。

笛卡爾積(CARTESIAN PRODUCT):是沒有連接條件表關系返回的結果。

eg：表A：字段數目為m，行數為k。表B字段數目為n，行數為l。則結果表字段數目為m+n,行數為k*l.

連接(JOIN)：在表關系的笛卡爾積數據記錄中，按照相應字段值的比較條件進行選擇生成一個新的關系。

連接分為內連接(inner join) 、外連接(outer join) 和交叉連接(cross join)。

1.內連接：在表關系的笛卡爾積數據記錄中，保留表關系中所有匹配的數據記錄，舍棄不匹配的數據記錄。

按照匹配的條件可以分成自然連接、等值連接和不等連接。

自然連接：在表關系的笛卡爾積中，首先根據表關系中相同名稱的字段自動進行記錄匹配，然后去掉重復的字段。

eg：表A 有字段名a,b;表B也有字段名a,b。則自然連接時，先求A和B的笛卡爾積，然后匹配A.a=B.a and A.b = B.b的記錄，不符合該條件的全部舍棄。笛卡爾積中，A.a A.b B.a B.b都會顯示，共4個字段，但自然連接中，只顯示a,b2個字段。

特點:1)在具體執行自然連接時，會自動判斷相同名稱的字段，然后進行數據值的匹配。

2)在執行完自然連接的新關系中，雖然可以指定包含哪些字段，但是不能指定執行過程中的匹配條件，即哪些字段的值進行匹配。

3)在執行完自然連接的關系中，執行過程中所有匹配的字段名只有一個，即會去掉重復字段。

等值連接：在表關系的笛卡爾積中，選擇所匹配字段值相等的數據記錄。

特點：1)使用=指定匹配條件；

2)在新關系中，不會去掉重復字段。

不等連接：在表關系的笛卡爾積中，選擇所匹配字段值不相等的數據記錄。

特點：1)使用!=指定匹配條件；

2)在新關系中，不會去掉重復字段。

2.外連接:在表關系的笛卡爾積數據記錄中，不僅保留表關系中所有匹配的數據記錄，而且還會保留部分不匹配的數據記錄。

按照保留不匹配條件數據記錄來源可以分為左外連接(left outer join)、右外連接(right outer join)和全外連接(full outer join)。

左外連接：在表關系的笛卡爾積中，除了選擇相匹配的數據記錄，還包含關聯左邊表中不相匹配的數據記錄。

右外連接：在表關系的笛卡爾積中，除了選擇相匹配的數據記錄，還包含關聯右邊表中不相匹配的數據記錄。

全外連接：在表關系的笛卡爾積中，除了選擇相匹配的數據記錄，還包含關聯左右兩邊表中不相匹配的數據記錄。

3.交叉連接：不帶where子句的連接，返回的就是笛卡爾積數據記錄。

總結：

表A和B

UNION： A并B

inner join: A交B 根據交的條件分為：自然連接、等值連接、不等值連接。使用的是比較運算符進行匹配條件的判斷。

outer join:

左外連接：A并(A交B)

右外連接：(A交B)并B

全外連接:A并(A交B)并B

cross join: A乘B 十字交叉，笛卡爾積

語法:

內連接：