02-再探MySQL数据库
生活随笔
收集整理的這篇文章主要介紹了
02-再探MySQL数据库
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
一、數據類型
1、數值類型
a、整數類型
整數類型：TINYINT SMALLINT MEDIUMINT INT BIGINT
作用：存儲年齡，等級，id，各種號碼等。
========================================tinyint[(m)] [unsigned] [zerofill]小整數，數據類型用于保存一些范圍的整數數值范圍：有符號：-128 ～ 127無符號：0 ～ 255PS： MySQL中無布爾值，使用tinyint(1)構造。========================================int[(m)][unsigned][zerofill]整數，數據類型用于保存一些范圍的整數數值范圍：有符號：-2147483648 ～ 2147483647無符號：0 ～ 4294967295========================================bigint[(m)][unsigned][zerofill]大整數，數據類型用于保存一些范圍的整數數值范圍：有符號：-9223372036854775808 ～ 9223372036854775807無符號：0 ～ 18446744073709551615驗證一下：
=========有符號和無符號tinyint========== # tinyint默認為有符號 mysql> create table t1(num tinyint); # 創建t1表，默認為有符號，即數字前有正負號 mysql> desc t1; # 查看表信息 +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | num | tinyint(4) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ mysql> insert into t1 values(-128),(127); # 注意：當插入的值超過這個范圍，會報1264錯誤。 mysql> select * from t1; +------+ | num | +------+ | -128 | | 127 | +------+ # 設置無符號tinyint mysql> create table t2(num tinyint unsigned); # 創建t2，指定沒有符號。 mysql> desc t2; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | num | tinyint(3) unsigned | YES | | NULL | | +-------+---------------------+------+-----+---------+-------+ mysql> insert into t2 values(0),(255); # 注意：當插入的值超過這個范圍，會報1264錯誤。 mysql> select * from t2; +------+ | num | +------+ | 0 | | 255 | +------+============有符號和無符號int============= # int默認為有符號 mysql> create table t3(num int); mysql> desc t3; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | num | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ mysql> insert into t3 values(-2147483648),(2147483647); mysql> select * from t3; +-------------+ | num | +-------------+ | -2147483648 | | 2147483647 | +-------------+ # 設置無符號int mysql> create table t4(num int unsigned); # 指定沒有符號。 mysql> desc t4; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | num | int(10) unsigned | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ mysql> insert into t4 values(0),(4294967295); mysql> select * from t4; +------------+ | num | +------------+ | 0 | | 4294967295 | +------------+==============有符號和無符號bigint============= # bigint默認為有符號 mysql> create table t5(num bigint); mysql> desc t5; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | num | bigint(20) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ mysql> insert into t5 values(-9223372036854775808),(9223372036854775807); mysql> select * from t5; +----------------------+ | num | +----------------------+ | -9223372036854775808 | | 9223372036854775807 | +----------------------+ # 設置無符號bigint mysql> create table t6(num bigint unsigned); mysql> desc t6; +-------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+-------+ | num | bigint(20) unsigned | YES | | NULL | | +-------+---------------------+------+-----+---------+-------+ mysql> insert into t6 values(0),(18446744073709551615); mysql> select * from t6; +----------------------+ | num | +----------------------+ | 0 | | 18446744073709551615 | +----------------------+==========用zerofill測試整數類型的顯示寬度============= mysql> create table t7(num int(3) zerofill); mysql> desc t7; +-------+--------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------------------+------+-----+---------+-------+ | num | int(3) unsigned zerofill | YES | | NULL | | +-------+--------------------------+------+-----+---------+-------+ mysql> insert into t7 values(1),(11),(111),(1111); mysql> select * from t7; +------+ | num | +------+ | 001 | | 011 | | 111 | | 1111 | # 超過寬度限制仍然可以存 +------+ 注意：為該類型指定寬度時，僅僅只是指定查詢結果的顯示寬度，與存儲范圍無關，存儲范圍如下圖。int的存儲寬度是4個Bytes，即32個bit，即2**32
無符號最大值為：4294967296-1
有符號最大值：2147483648-1
有符號和無符號的最大數字需要的顯示寬度均為10，而針對有符號的最小值則需要11位才能顯示完全，所以int類型默認的顯示寬度為11是非常合理的。
最后：整形類型，其實沒有必要指定顯示寬度，使用默認的就ok。
b、浮點型
定點數類型 DEC等同于DECIMAL
浮點類型：FLOAT DOUBLE
作用：存儲薪資、身高、體重、體質參數等。
====================================== #FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]定義：單精度浮點數（非準確小數值），m是數字總個數，d是小數點后個數。m最大值為255，d最大值為30有符號：-3.402823466E+38 to -1.175494351E-38,1.175494351E-38 to 3.402823466E+38 無符號：1.175494351E-38 to 3.402823466E+38精確度： **** 隨著小數的增多，精度變得不準確 ****====================================== #DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]定義：雙精度浮點數（非準確小數值），m是數字總個數，d是小數點后個數。m最大值為255，d最大值為30有符號：-1.7976931348623157E+308 to -2.2250738585072014E-3082.2250738585072014E-308 to 1.7976931348623157E+308無符號：2.2250738585072014E-308 to 1.7976931348623157E+308精確度：****隨著小數的增多，精度比float要高，但也會變得不準確 ****====================================== decimal[(m[,d])] [unsigned] [zerofill]定義：準確的小數值，m是數字總個數（負號不算），d是小數點后個數。 m最大值為65，d最大值為30。精確度：**** 隨著小數的增多，精度始終準確 ****對于精確數值計算時需要用此類型decaimal能夠存儲精確值的原因2、日期類型
DATE TIME DATETIME TIMESTAMP YEAR
作用：存儲用戶注冊時間，文章發布時間，員工入職時間，出生時間，過期時間等。
YEARYYYY（1901/2155）DATEYYYY-MM-DD（1000-01-01/9999-12-31）TIMEHH:MM:SS（'-838:59:59'/'838:59:59'）DATETIMEYYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）TIMESTAMPYYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某時）驗證一下：
============year=========== mysql> create table t8(born_year year); # 無論year指定何種寬度，最后都默認是year(4) mysql> desc t8; +-----------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+-------+ | born_year | year(4) | YES | | NULL | | +-----------+---------+------+-----+---------+-------+ mysql> insert into t8 values(1901),(2155); # 超出這個范圍會報1264錯誤。 mysql> select * from t8; +-----------+ | born_year | +-----------+ | 1901 | | 2155 | +-----------+============date,time,datetime=========== mysql> create table t9(d date,t time,dt datetime); mysql> desc t9; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ mysql> insert into t9 values(now(),now(),now()); mysql> select * from t9; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2019-04-10 | 12:26:12 | 2019-04-10 12:26:12 | +------------+----------+---------------------+============timestamp=========== mysql> create table t10(time timestamp); mysql> desc t10； mysql> insert into t10 values(); mysql> insert into t10 values(null); mysql> select * from t10; +---------------------+ | time | +---------------------+ | 2018-01-10 11:50:52 | | 2018-01-10 11:51:07 | +---------------------+============注意啦，注意啦，注意啦=========== 1. 單獨插入時間時，需要以字符串的形式，按照對應的格式插入 2. 插入年份時，盡量使用4位值 3. 插入兩位年份時，<=69，以20開頭，比如50, 結果2050 >=70，以19開頭，比如71，結果1971mysql> create table t11(y year); mysql> desc t11; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | y | year(4) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ mysql> insert into t11 values(50),(69),(70),(99); mysql> select * from t11; +------+ | y | +------+ | 2050 | | 2069 | | 1970 | | 1999 | +------+datetime與timestamp的區別：
在實際應用的很多場景中，MySQL的這兩種日期類型都能夠滿足我們的需要，存儲精度都為秒，但在某些情況下，會展現出他們各自的優劣。
下面就來總結一下兩種日期類型的區別：
如果不做特殊處理，并且update語句中沒有指定該列的更新值，則默認更新為當前時間。
3、字符串類型
字符串類型主要為char與varchar類型，字符串類型在生產場景中，主要存儲像姓名、郵箱、地址等一些描述信息。
#注意：char和varchar括號內的參數指的都是字符的長度#char類型：定長，簡單粗暴，浪費空間，存取速度快字符長度范圍：0-255（一個中文是一個字符，是utf8編碼的3個字節）存儲：存儲char類型的值時，會往右填充空格來滿足長度例如：指定長度為10，存>10個字符則報錯，存<10個字符則用空格填充直到湊夠10個字符存儲檢索：在檢索或者說查詢時，查出的結果會自動刪除尾部的空格，除非我們打開pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）#varchar類型：變長，精準，節省空間，存取速度慢字符長度范圍：0-65535（如果大于21845會提示用其他類型 。mysql行最大限制為65535字節，字符編碼為utf-8：https://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html）存儲：varchar類型存儲數據的真實內容，不會用空格填充，如果'ab ',尾部的空格也會被存起來強調：varchar類型會在真實數據前加1-2Bytes的前綴，該前綴用來表示真實數據的bytes字節數（1-2Bytes最大表示65535個數字，正好符合mysql對row的最大字節限制，即已經足夠使用）如果真實的數據<255bytes則需要1Bytes的前綴（1Bytes=8bit 2**8最大表示的數字為255）如果真實的數據>255bytes則需要2Bytes的前綴（2Bytes=16bit 2**16最大表示的數字為65535）檢索：尾部有空格會保存下來，在檢索或者說查詢時，也會正常顯示包含空格在內的內容官網解釋如下：
測試前了解兩個函數
length：查看字節數 char_length:查看字符數我們實踐來了解一下：
mysql> create table t14(c char(5),v varchar(5)); mysql> desc t14; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c | char(5) | YES | | NULL | | | v | varchar(5) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ mysql> insert into t14 values("蒼老師 ","蒼老師 "); mysql> select c,char_length(c),v,char_length(v) from t14; +--------+----------------+---------+----------------+ | c | char_length(c) | v | char_length(v) | +--------+----------------+---------+----------------+ | 蒼老師 | 3 | 蒼老師 | 4 | +--------+----------------+---------+----------------+ mysql> select c,length(c),v,length(v) from t14; +--------+-----------+---------+-----------+ | c | length(c) | v | length(v) | +--------+-----------+---------+-----------+ | 蒼老師 | 9 | 蒼老師 | 10 | +--------+-----------+---------+-----------+ # char填充空格來滿足固定長度，但是在查詢時卻會很不要臉地刪除尾部的空格（裝作自己好像沒有浪費過空間一樣），然后修改sql_mode讓其現出原形。 # 略施小計，讓char現出原形 mysql> SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH'; mysql> select c,char_length(c),v,char_length(v) from t14; +----------+----------------+---------+----------------+ | c | char_length(c) | v | char_length(v) | +----------+----------------+---------+----------------+ | 蒼老師 | 5 | 蒼老師 | 4 | +----------+----------------+---------+----------------+ mysql> select c,length(c),v,length(v) from t14; +----------+-----------+---------+-----------+ | c | length(c) | v | length(v) | +----------+-----------+---------+-----------+ | 蒼老師 | 11 | 蒼老師 | 10 | +----------+-----------+---------+-----------+ # 跟上面相比char_length(c)和length(c)的值就原形畢露了。# 雖然 CHAR 和 VARCHAR 的存儲方式不太相同,但是對于兩個字符串的比較,都只比 較其值,忽略 CHAR 值存在的右填充,即使將 SQL_MODE 設置為 PAD_CHAR_TO_FULL_LENGTH 也一樣,,但這不適用于like。總結：
#常用字符串系列：char與varchar 注：雖然varchar使用起來較為靈活，但是從整個系統的性能角度來說，char數據類型的處理速度更快，有時甚至可以超出varchar處理速度的50%。因此，用戶在設計數據庫時應當綜合考慮各方面的因素，以求達到最佳的平衡#其他字符串系列（效率：char>varchar>text） TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT BLOB 系列 TINYBLOB BLOB MEDIUMBLOB LONGBLOB BINARY系列 BINARY VARBINARYtext：text數據類型用于保存變長的大字符串，可以組多到65535 (2**16 ? 1)個字符。 mediumtext：A TEXT column with a maximum length of 16,777,215 (2**24 ? 1) characters. longtext：A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 ? 1) characters.4、枚舉類型與集合類型
字段的值只能在給定范圍中選擇，如：單選框，多選框。
enum 單選 只能在給定的范圍內選一個值，如性別 sex 男male/女female
set 多選 在給定的范圍內可以選擇一個或一個以上的值（愛好1,愛好2,愛好3...）
mysql> create table t15(id int,name varchar(32),sex enum('male','female'),level enum('vip1','vip2','vip3','vip4'), hobby set('music','read','run','movie'));mysql> insert into t15 values-> (1,'alex','female','vip3','music,movie'),-> (2,'egon','male','vip1','read,run,music'); mysql> select * from t15; +------+------+--------+-------+----------------+ | id | name | sex | level | hobby | +------+------+--------+-------+----------------+ | 1 | alex | female | vip3 | music,movie | | 2 | egon | male | vip1 | music,read,run | +------+------+--------+-------+----------------+ # 插入值不在枚舉與集合類型時，值為空。 mysql> insert into t15 values-> (3,'yuan','female1','vip31','music1,movie1'); mysql> select * from t15; +------+------+--------+-------+----------------+ | id | name | sex | level | hobby | +------+------+--------+-------+----------------+ | 1 | alex | female | vip3 | music,movie | | 2 | egon | male | vip1 | music,read,run | | 3 | yuan | | | | +------+------+--------+-------+----------------+二、完整性約束
1、介紹
約束條件與數據類型的寬度一樣，都是可選參數
作用：用于保證數據的完整性和一致性
主要分為：
PRIMARY KEY (PK) 標識該字段為該表的主鍵，可以唯一的標識記錄 FOREIGN KEY (FK) 標識該字段為該表的外鍵 NOT NULL 標識該字段不能為空 UNIQUE KEY (UK) 標識該字段的值是唯一的 AUTO_INCREMENT 標識該字段的值自動增長（整數類型，而且為主鍵） DEFAULT 為該字段設置默認值UNSIGNED 無符號 ZEROFILL 使用0填充說明：
1. 是否允許為空，默認NULL，可設置NOT NULL，字段不允許為空，必須賦值 2. 字段是否有默認值，缺省的默認值是NULL，如果插入記錄時不給字段賦值，此字段使用默認值 sex enum('male','female') not null default 'male' age int unsigned NOT NULL default 20 必須為正值（無符號） 不允許為空 默認是20 3. 是否是key 主鍵 primary key 外鍵 foreign key 索引 (index,unique...)2、not null 與 default
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
默認值，創建列時可以指定默認值，當插入數據時如果未主動設置，則自動添加默認值。
mysql> create table t16(id int,-> name varchar(32),-> sex enum('male','female') not null default 'male'); mysql> desc t16; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | sex | enum('male','female') | NO | | male | | +-------+-----------------------+------+-----+---------+-------+ mysql> insert into t16(id,name) values(1,'egon'); mysql> select * from t16; +------+------+------+ | id | name | sex | +------+------+------+ | 1 | egon | male | +------+------+------+3、unique
對字段值進行唯一性約束。可以對單列設置，也可以為多列設置聯合唯一。
# 單列唯一 # 方式一： mysql> create table t17(id int unique,name varchar(32) unique); mysql> desc t17; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | varchar(32) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> insert into t17 values(1, 'IT'),(2,'IT'); # 插入相同的值會報錯。 ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'# 方式二： mysql> create table t18(id int,-> name varchar(32),-> unique(id),-> unique(name)); mysql> desc t18; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | varchar(32) | YES | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> insert into t18 values(1, 'IT'),(2,'IT'); ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'# 聯合唯一 mysql> create table t19(-> id int,-> ip char(15),-> port int,-> unique(id),-> unique(ip,port)-> ); mysql> desc t19; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | ip | char(15) | YES | MUL | NULL | | | port | int(11) | YES | | NULL | | +-------+----------+------+-----+---------+-------+ mysql> insert into t19 values-> (1,'192.168.11.11',80),-> (2,'192.168.11.11',88),-> (3,'192.168.11.12',80); mysql> select * from t19; +------+---------------+------+ | id | ip | port | +------+---------------+------+ | 1 | 192.168.11.11 | 80 | | 2 | 192.168.11.11 | 88 | | 3 | 192.168.11.12 | 80 | +------+---------------+------+ # 咱們接下來重新插入第一條IP和端口 mysql> insert into t19 values(4,'192.168.11.11',80); ERROR 1062 (23000): Duplicate entry '192.168.11.11-80' for key 'ip'4、primary key
primary key字段的值不為空且唯一（非空且唯一 ，not null unique）
一個表中可以：
單列做主鍵
多列做主鍵（復合主鍵）
但一個表內只能有一個主鍵primary key
# 默認的存儲引擎為InnoDB：一張表內必須要有一個主鍵。 # 單列主鍵 mysql> create table t20(-> id int primary key,-> name varchar(32)-> ); mysql> desc t20; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> insert into t20 values-> (1,'alex'),-> (2,'egon'); mysql> select * from t20; +----+------+ | id | name | +----+------+ | 1 | alex | | 2 | egon | +----+------+ mysql> insert into t20 values(2,'yuan'); ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' mysql> insert into t20(name) values('yuan'); ERROR 1364 (HY000): Field 'id' doesn't have a default value# 復合主鍵 mysql> create table t22(-> ip char(15),-> port int,-> primary key(ip,port)-> ); mysql> desc t22; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | ip | char(15) | NO | PRI | NULL | | | port | int(11) | NO | PRI | NULL | | +-------+----------+------+-----+---------+-------+ mysql> insert into t22 values-> ('192.168.11.11',80),-> ('192.168.11.11',81),-> ('192.168.11.12',80); mysql> select * from t22; +---------------+------+ | ip | port | +---------------+------+ | 192.168.11.11 | 80 | | 192.168.11.11 | 81 | | 192.168.11.12 | 80 | +---------------+------+ # 插入重復的值 mysql> insert into t22 values-> ('192.168.11.11',80); ERROR 1062 (23000): Duplicate entry '192.168.11.11-80' for key 'PRIMARY'5、auto_increment
約束字段為自動增長，被約束的字段必須同時被key約束。
mysql> create table t23(-> id int primary key auto_increment,-> name varchar(32)-> ); mysql> desc t23; +-------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | YES | | NULL | | +-------+-------------+------+-----+---------+----------------+ mysql> insert into t23(name) values-> ('alex'),-> ('egon'); mysql> select * from t23; +----+------+ | id | name | +----+------+ | 1 | alex | | 2 | egon | +----+------+ mysql> insert into t23 values(5,'yuan'); # 自己指定id只要不違反約束條件即可。# 關于起始偏移量和步長（了解） # 1. 查看默認值 mysql> show variables like 'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 1 | # 默認步長為1 | auto_increment_offset | 1 | # 默認起始偏移量為1 +--------------------------+-------+# 2. 設置步長mysql> set session auto_increment_increment=5; # 當前會話級別mysql> set global auto_increment_increment=5; # 設置全局，需要退出重新加載才能生效。# 3.設置起始偏移量mysql> set session auto_increment_offset=3;mysql> set global auto_increment_offset=3;# 注意：起始偏移量要<=步長，否則設置無效。 # 對于自增的字段，在用delete刪除后，再插入值，該字段仍按照刪除前的位置繼續增長，一般情況下，delete后面會接where條件，對指定的內容進行刪除。 mysql> delete from t23; # 清空表 mysql> insert into t23(name) values('alex'); mysql> select * from t23; +----+------+ | id | name | +----+------+ | 16 | alex | +----+------+ # truncate是直接清空表，在刪除大表時用它。它會將起始偏移量重置至初始狀態。 mysql> truncate table t23;6、foreign key
foreign key，建立表之間的關系。
下面我們通過一個例子來說明，比如我們現在有一張員工表：
| employee | ||||
| id | name | age | department | comment |
| 1 | 任盈盈 | 18 | 公關部 | 公關能力有限部門 |
| 2 | 張無忌 | 23 | 銷售部 | 銷售能力有限部門 |
| 3 | 令狐沖 | 25 | 銷售部 | 銷售能力有限部門 |
| 4 | 小龍女 | 24 | 公關部 | 公關能力有限部門 |
| 5 | 滅絕師太 | 56 | 公關部 | 公關能力有限部門 |
| 6 | 歐陽鋒 | 53 | 技術部 | 靠技術吃飯部門 |
大家通過觀察這張表，能不能找出兩個問題：
1、新增員工時，部門和部門描述重復，浪費空間；
2、后期部門信息發生變更，不便于維護。
為了解決上面的問題，就引出了foreign key 的知識點了。先通過一張圖，我們來了解一下。
大家看一下這樣是不是就完美的解決了上面的問題了。有思路了，我們來動手實現一下。
# 1、建立表關系# a、先建被關聯的表，且保證被關聯的字段唯一mysql> create table department(-> id int primary key,-> name varchar(32),-> comment varchar(128)-> );# b、再建關聯的表mysql> create table employee(-> id int primary key,-> name varchar(32),-> age tinyint unsigned,-> dep_id int,-> foreign key(dep_id) references department(id) # 指定外建字段，跟哪張表哪個字段建立關系。-> ); # 2、插入數據a、先往被關聯表插入記錄mysql> insert into department values-> (1,'公關部','公關能力有限部門'),-> (2,'銷售部','銷售能力有限部門'),-> (3,'技術部','靠技術吃飯部門');mysql> select * from department; +----+--------+------------------+ | id | name | comment | +----+--------+------------------+ | 1 | 公關部 | 公關能力有限部門 | | 2 | 銷售部 | 銷售能力有限部門 | | 3 | 技術部 | 靠技術吃飯部門 | +----+--------+------------------+ b、再往關聯表插入記錄mysql> insert into employee values-> (1,'任盈盈',18,1),-> (2,'張無忌',23,2),-> (3,'令狐沖',25,2),-> (4,'小龍女',24,1),-> (5,'滅絕師太',56,1),-> (6,'歐陽鋒',53,3);mysql> select * from employee; +----+----------+------+--------+ | id | name | age | dep_id | +----+----------+------+--------+ | 1 | 任盈盈 | 18 | 1 | | 2 | 張無忌 | 23 | 2 | | 3 | 令狐沖 | 25 | 2 | | 4 | 小龍女 | 24 | 1 | | 5 | 滅絕師太 | 56 | 1 | | 6 | 歐陽鋒 | 53 | 3 | +----+----------+------+--------+ # 3、刪除數據和更新數據# a、先刪除關聯的數據mysql> delete from employee where dep_id=2;# b、再刪除被關聯的數據mysql> delete from department where id=2;# c、因為有外建約束，無法進行更新操作。 # 4、解決上面的問題，只需在建關聯表的時候，指定一下同步即可。# a、建立關聯表mysql> create table employee(-> id int primary key,-> name varchar(32),-> age tinyint unsigned,-> dep_id int,-> foreign key(dep_id) references department(id) on delete cascade # 刪除時同步on update cascade # 更新時同步-> );# b、插入記錄mysql> insert into employee values-> (1,'任盈盈',18,1),-> (2,'張無忌',23,2),-> (3,'令狐沖',25,2),-> (4,'小龍女',24,1),-> (5,'滅絕師太',56,1),-> (6,'歐陽鋒',53,3);# c、刪除部門mysql> delete from department where id=1;mysql> select * from department; +----+--------+------------------+ | id | name | comment | +----+--------+------------------+ | 2 | 銷售部 | 銷售能力有限部門 | | 3 | 技術部 | 靠技術吃飯部門 | +----+--------+------------------+mysql> select * from employee; # 與之關聯的員工一并刪除了。 +----+--------+------+--------+ | id | name | age | dep_id | +----+--------+------+--------+ | 2 | 張無忌 | 23 | 2 | | 3 | 令狐沖 | 25 | 2 | | 6 | 歐陽鋒 | 53 | 3 | +----+--------+------+--------+ # d、更新部門mysql> update department set id=222 where id=2;mysql> select * from department; +-----+--------+------------------+ | id | name | comment | +-----+--------+------------------+ | 3 | 技術部 | 靠技術吃飯部門 | | 222 | 銷售部 | 銷售能力有限部門 | +-----+--------+------------------+mysql> select * from employee; +----+--------+------+--------+ | id | name | age | dep_id | +----+--------+------+--------+ | 2 | 張無忌 | 23 | 222 | | 3 | 令狐沖 | 25 | 222 | | 6 | 歐陽鋒 | 53 | 3 | +----+--------+------+--------+三、單表查詢
1、單表查詢語法
SELECT 字段1,字段2... FROM 表名WHERE 條件GROUP BY fieldHAVING 篩選ORDER BY fieldLIMIT 限制條數2、關鍵字的執行優先級（重點）
重點中的重點：關鍵字的執行優先級 from where group by having select distinct order by limit注解： 1.找到表:from2.拿著where指定的約束條件，去文件/表中取出一條條記錄3.將取出的一條條記錄進行分組group by，如果沒有group by，則整體作為一組4.將分組的結果進行having過濾5.執行select6.去重7.將結果按條件排序：order by8.限制結果的顯示條數看一下在SQL語句中如何體現。
(7) SELECT (8) DISTINCT <select_list> (1) FROM <left_table> (3) <join_type> JOIN <right_table> (2) ON <join_condition> (4) WHERE <where_condition> (5) GROUP BY <group_by_list> (6) HAVING <having_condition> (9) ORDER BY <order_by_condition> (10) LIMIT <limit_number>3、簡單查詢
a、建立表
mysql> create table employee(-> id int primary key auto_increment,-> name varchar(32) not null,-> sex enum('male','female') not null default 'male',-> age tinyint unsigned not null default 28,-> hire_date date not null,-> post varchar(32),-> post_comment varchar(128),-> salary decimal(9,2),-> office int, # 一個部門一個屋子-> dep_id int-> ); mysql> desc employee; +--------------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------------+-----------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(32) | NO | | NULL | || sex | enum('male','female') | NO | | male | || age | tinyint(3) unsigned | NO | | 28 | || hire_date | date | NO | | NULL | || post | varchar(32) | YES | | NULL | || post_comment | varchar(128) | YES | | NULL | || salary | decimal(9,2) | YES | | NULL | || office | int(11) | YES | | NULL | || dep_id | int(11) | YES | | NULL | |+--------------+-----------------------+------+-----+---------+----------------+b、插入數據
insert into employee(name,sex,age,hire_date,post,post_comment,salary,office,dep_id) values ('任盈盈','female',18,'2018-11-14','sale',null,8000,401,1), ('小龍女','female',23,'2017-11-24','sale',null,9000,401,1), ('王語嫣','female',22,'2016-1-14','sale',null,7000,401,1), ('黃蓉','female',21,'2018-12-14','sale',null,8000,401,1), ('秋香','female',19,'2019-1-11','sale',null,8000,401,1), ('周芷若','female',20,'2018-11-11','sale',null,7000,401,1), ('趙敏','female',21,'2017-2-18','sale',null,8000,401,1),('令狐沖','male',24,'2017-1-24','operation',null,7000,402,2), ('張無忌','male',23,'2016-5-21','operation',null,7500,402,2), ('郭靖','male',25,'2016-7-28','operation',null,8000,402,2), ('楊康','male',24,'2016-8-28','operation',null,7000,402,2), ('楊鐵心','male',54,'2015-4-18','operation',null,9000,402,2), ('楊過','male',27,'2017-5-28','operation',null,7000,402,2),('張三豐','male',68,'2014-8-8','teacher','主授太極拳',11000,403,3), ('任我行','male',54,'2015-2-4','teacher','主授吸星大法',10000,403,3), ('岳不群','male',50,'2016-12-24','teacher','主授華山劍法',9000,403,3);c、簡單查詢
# 1、簡單查詢 select id,name,sex,age,hire_date,post,post_comment,salary,office,dep_id from employee;select * from employee;select id,name,salart from employee;# 避免重復distinct select distinct post from employee;# 通過四則運算查詢 select name,salary * 12 from employee;select name,salary * 12 as annual_salary from employee; # 取別名方式一 select name,salary * 12 annual_salary from employee; # 取別名方式二d、小練習
# 1、查出所有員工的name和薪資； # 2、查出所有的崗位（去重）； # 3、查出所有員工的name和年薪（指定別名為annual_salary）；? 4、where約束
where字句中可以使用：
小練習：
1. 查看崗位是teacher的員工姓名、年齡 2. 查看崗位是teacher且年齡大于30歲的員工姓名、年齡 3. 查看崗位是teacher且薪資在9000-10000范圍內的員工姓名、年齡、薪資 4. 查看崗位描述不為NULL的員工信息 5. 查看崗位是teacher且薪資是10000或9000或30000的員工姓名、年齡、薪資 6. 查看崗位是teacher且薪資不是10000或9000或30000的員工姓名、年齡、薪資 7. 查看崗位是teacher且名字是“楊”開頭的員工姓名、年薪5、分組查詢 group by
# 什么是分組，為什么要分組？ #1、首先明確一點：分組發生在where之后，即分組是基于where之后得到的記錄而進行的；#2、分組指的是：將所有記錄按照某個相同字段進行歸類，比如針對員工信息表的職位分組，或者按照性別進行分組等；#3、為何要分組呢？取每個部門的最高工資取每個部門的員工數取男人數和女人數小竅門：‘每’這個字后面的字段，就是我們分組的依據#4、大前提：可以按照任意字段分組，但是分組完畢后，比如group by post，只能查看post字段，如果想查看組內信息，需要借助于聚合函數。# 小試牛刀 select * from employee group by post; # 注意：會報1055錯誤，下面會詳細說明# 單獨使用GROUP BY關鍵字分組SELECT post FROM employee GROUP BY post;注意：我們按照post字段分組，那么select查詢的字段只能是post，想要獲取組內的其他相關信息，需要借助函數。# GROUP BY關鍵字和GROUP_CONCAT()函數一起使用# 按照崗位分組，并查看組內成員名SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;# GROUP BY與聚合函數一起使用# 按照崗位分組，并查看每個組有多少人select post,count(id) as count from employee group by post;# 強調：# 如果我們用unique的字段作為分組的依據，則每一條記錄自成一組，這種分組沒有意義。# 多條記錄之間的某個字段值相同，該字段通常用來作為分組的依據。# 聚合函數 # MAX：最大值 # MIN：最小值 # AVG：平均值 # SUM：求和 # COUNT：計數 # 查詢每個職位最高的薪資 select post,max(salary) from employee group by post; # 查詢每個職位共有多少員工 select post,count(id) from employee group by post;小練習：
1. 查詢崗位名以及崗位包含的所有員工名字 2. 查詢崗位名以及各崗位內包含的員工個數 3. 查詢公司內男員工和女員工的個數 4. 查詢崗位名以及各崗位的平均薪資 5. 查詢崗位名以及各崗位的最高薪資 6. 查詢崗位名以及各崗位的最低薪資 7. 查詢男員工與男員工的平均薪資，女員工與女員工的平均薪資6、having過濾
# HAVING與WHERE不一樣的地方在于: #！！！執行優先級從高到低：where > group by > having #1. Where 發生在分組group by之前，因而Where中可以有任意字段，但是絕對不能使用聚合函數。#2. Having發生在分組group by之后，因而Having中可以使用分組的字段，無法直接取到其他字段,可以使用聚合函數# 查詢各崗位內包含的員工個數小于4的崗位名、崗位內包含員工名字、個數 select post,group_concat(name),count(id) from employee group by post having count(id) < 4;小練習：
# 1、查詢各崗位平均薪資大于7800的崗位名、平均工資 # 2、查詢各崗位平均薪資大于8000且小于10000的崗位名、平均工資7、查詢排序 order by
按單列排序SELECT * FROM employee ORDER BY salary; # 默認為升序SELECT * FROM employee ORDER BY salary ASC; # 升序SELECT * FROM employee ORDER BY salary DESC; # 降序按多列排序:先按照age排序，如果年紀相同，則按照薪資排序SELECT * from employeeORDER BY age,salary DESC;小練習：
1. 查詢所有員工信息，先按照age升序排序，如果age相同則按照hire_date降序排序 2. 查詢各崗位平均薪資大于7000的崗位名、平均工資,結果按平均薪資升序排列 3. 查詢各崗位平均薪資大于7000的崗位名、平均工資,結果按平均薪資降序排列8、限制查詢的記錄數 limit
示例：SELECT * FROM employee ORDER BY salary DESC LIMIT 3; #默認初始位置為0 SELECT * FROM employee ORDER BY salary DESCLIMIT 0,5; #從第0開始，即先查詢出第一條，然后包含這一條在內往后查5條SELECT * FROM employee ORDER BY salary DESCLIMIT 5,5; #從第5開始，即先查詢出第6條，然后包含這一條在內往后查5條小練習：
# 分頁顯示，每頁5條 select * from employee limit 0,5; # 1---5條記錄 select * from employee limit 5,5; # 6---10條記錄 select * from employee limit 10,5; # 11---15條記錄9、使用正則表達式查詢
SELECT * FROM employee WHERE name REGEXP '^任';SELECT * FROM employee WHERE name REGEXP '女$';小結：對字符串匹配的方式 WHERE name = '任盈盈'; WHERE name LIKE '楊%'; WHERE name REGEXP '女$';小練習：
查看所有員工中名字是“楊”開頭，“過”或者“康”結果的員工信息?
轉載于:https://www.cnblogs.com/Michael--chen/p/10758284.html
總結
以上是生活随笔為你收集整理的02-再探MySQL数据库的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 上传图片或文件 方法一
- 下一篇： 第九章：内存管理