一 : 主鍵
primary key,主要的鍵. 一張表只能有一個(gè)字段可以使用對應(yīng)的鍵, 用來唯一的約束該字段里面的數(shù)據(jù), 不能重復(fù): 這種稱之為主鍵.一張表只能有最多一個(gè)主鍵
操作
-
增加主鍵
(1) : 在創(chuàng)建表的時(shí)候,直接在字段之后,跟primary key 關(guān)鍵字(主鍵本身不允許為空)
優(yōu)點(diǎn) : 非常的直接
缺點(diǎn) : 只能使用一個(gè)字段作作為主鍵
create table my_pri1(
name varchar(20) not null comment '姓名',
number char(10) primary key comment '學(xué)號: stu + 0000, 不能重復(fù)'
)charset utf8;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| number | char(10) | NO | PRI | NULL | |
+--------+-------------+------+-----+---------+-------+
(2) : 在創(chuàng)建表的時(shí)候,在所有字段之后,使用primary key(主鍵字段列表)來創(chuàng)建主鍵(如果有多個(gè)字段為主鍵,可以是復(fù)合主鍵)
-- 復(fù)合主鍵
create table my_pri2(
number char(10) comment '學(xué)號: stu + 0000',
course char(10) comment '課程代碼: 3901 + 0000',
score tinyint unsigned default 60 comment '成績',
-- 增加主鍵限制: 學(xué)號和課程號應(yīng)該是個(gè)對應(yīng)的,具有唯一性
primary key(number,course)
)charset utf8;
+--------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------------------+------+-----+---------+-------+
| number | char(10) | NO | PRI | NULL | |
| course | char(10) | NO | PRI | NULL | |
| score | tinyint(3) unsigned | YES | | 60 | |
+--------+---------------------+------+-----+---------+-------+
(3) : 當(dāng)表已經(jīng)創(chuàng)建好之后,額外追加主鍵,可以通過修改表字段屬性,也可以直接追加,Alter table 表名add primary key(字段列表);
注意 : 表中字段對應(yīng)的數(shù)據(jù)本身是獨(dú)立的(不重復(fù))
create table my_pri3(
course char(10) not null comment '課程編號: 3901 + 0000',
name varchar(10) not null comment '課程名字'
);
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| course | char(10) | NO | | NULL | |
| name | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
alter table my_pri3 modify course char(10) primary key comment '課程編號: 3901 + 0000';
或者
alter table my_pri3 add primary key(course);(追加主鍵主要用這個(gè))
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| course | char(10) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
-
主鍵約束
主鍵對應(yīng)的字段中的數(shù)據(jù)不允許重復(fù),一旦重復(fù),數(shù)據(jù)操作失敗(增/改)
mysql> select * from my_pri1;
+--------+-----------+
| name | number |
+--------+-----------+
| 雪芙 | class0001 |
| 思思 | class0002 |
+--------+-----------+
-- 主鍵沖突(重復(fù))
insert into my_pri1 values('美美','class0002'); -- 不可以: 主鍵沖突
ERROR 1062 (23000): Duplicate entry 'class0002' for key 'PRIMARY'
-
更新與刪除主鍵
沒有辦法更新主鍵,主鍵必須先刪除再增加.
mysql> desc my_pri1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| number | char(10) | NO | PRI | NULL | |
+--------+-------------+------+-----+---------+-------+
刪除主鍵
Alter table 表名 drop primary key
alter table my_pri3 drop primary key;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| number | char(10) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
二 : 主鍵分類
在實(shí)際創(chuàng)建表的過程中, 很少使用真實(shí)業(yè)務(wù)數(shù)據(jù)作為主鍵字段(業(yè)務(wù)主鍵,如學(xué)號,課程號); 大部分的時(shí)候是使用邏輯性的字段(字段沒有業(yè)務(wù)含義,值是什么都沒有關(guān)系), 將這種字段主鍵稱之為邏輯主鍵
Create table my_student(
Id int primary key auto_increment comment ‘邏輯主鍵: 自增長’,?-- 邏輯主鍵
Number char(10) not null comment ‘學(xué)號’,
Name varchar(10) not null
)
三 : 自動(dòng)增長
自增長: 當(dāng)對應(yīng)的字段,不給值,或者說給默認(rèn)值,或者給NULL的時(shí)候, 會自動(dòng)的被系統(tǒng)觸發(fā), 系統(tǒng)會從當(dāng)前字段中已有的最大值再進(jìn)行+1操作,得到一個(gè)新的在不同的字段.
自增長通常是跟主鍵搭配
( 1 )新增自增長
auto_increment
① : 任何一個(gè)字段要做自增長必須前提是本身是一個(gè)索引(key一欄有值)
mysql> create table my_auto(
-> id int auto_increment comment '自動(dòng)增長',
-> name varchar(10) not null
-> )charset utf8;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
② : 自動(dòng)增長字段必須是數(shù)字(整形)
mysql> create table my_auto(
-> id varchar(1) primary key auto_increment comment '自動(dòng)增長',
-> name varchar(10) not null
-> )charset utf8;
ERROR 1063 (42000): Incorrect column specifier for column 'id'
③ : 一張表最多只能有一個(gè)自增長
-- 自增長
mysql> create table my_auto(
-> id int primary key auto_increment comment '自動(dòng)增長',
-> name varchar(10) not null
-> )charset utf8;
Query OK, 0 rows affected (0.03 sec)
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
( 2 )自增長使用
當(dāng)自增長被給定的值為NULL或者默認(rèn)值的時(shí)候會觸發(fā)自動(dòng)增長.
insert into my_auto(name) values('雪芙');
insert into my_auto values(null,'思思');
insert into my_auto values(default,'彩彩');
+----+--------+
| id | name |
+----+--------+
| 1 | 雪芙 |
| 2 | 思思 |
| 3 | 彩彩 |
+----+--------+
自增長如果對應(yīng)的字段輸入了值,那么自增長失效,但是下一次還是能夠正確的自增長(從最大值+1)
insert into my_auto(name) values('雪芙');
insert into my_auto values(null,'思思');
insert into my_auto values(default,'彩彩');
+----+--------+
| id | name |
+----+--------+
| 1 | 雪芙 |
| 2 | 思思 |
| 3 | 彩彩 |
| 6 | 美美 |
| 7 | 想想 |
+----+--------+
如何確定下一次是什么自增長,可以通過查看表創(chuàng)建語句看到
mysql> show create table my_auto;
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| my_auto | CREATE TABLE `my_auto` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自動(dòng)增長',
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
( 3 )修改自增長
自增長如果是涉及到字段改變,必須先刪除自增長,后增加(一張表只能有一個(gè)自增長)
修改當(dāng)前自增長已經(jīng)存在的值 : 修改只能比當(dāng)前已有的自增長的最大值大,不能小(小了不生效)
Alter table 表名 auto_increment = 值;
向下修改(小),但是不生效
alter table my_auto auto_increment = 4; -- 向下修改(小)
向上修改(大),生效
alter table my_auto auto_increment = 10; -- 向上修改
---------------------------------------------------------+
| my_auto | CREATE TABLE `my_auto` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自動(dòng)增長',
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
( 4 )修改自增長步長
為什么自增長是從1開始,為什么每次都是自增1呢.
所有系統(tǒng)的變現(xiàn)(如字符集,校對集)都是由系統(tǒng)內(nèi)部的變量進(jìn)行控制的.
查看自增長對應(yīng)的變量 : show variables like 'auto_increment%';
-- 查看自增長變量
show variables like 'auto_increment%';
auto_increment_incremen : 步長
auto_increment_offset : 起始值
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 1 |
| auto_increment_offset | 1 |
+--------------------------+-------+
可以修改變量實(shí)現(xiàn)不同的效果,修改是對整個(gè)數(shù)據(jù)修改,而不是單張表;
set auto_increment_increment = 5; 一次自增5
set auto_increment_increment = 5;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 5 |
| auto_increment_offset | 1 |
+--------------------------+-------+
+----+--------+
| id | name |
+----+--------+
| 1 | 雪芙 |
| 2 | 思思 |
| 3 | 彩彩 |
| 6 | 美美 |
| 7 | 想想 |
| 11 | 勁勁 |
| 16 | 辰辰 |
+----+--------+
( 5 )刪除自增長
自增長是字段的一個(gè)屬性,可以通過modify來進(jìn)行修改(保證字段沒有auto_increment即可)
Alter table 表名 modify 字段類型;
錯(cuò)誤 : 主鍵理論是單獨(dú)存在,有主鍵的時(shí)候,千萬不要再加主鍵
alter table my_auto modify id int primary key;
ERROR 1068 (42000): Multiple primary key defined
正確 :
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
alter table my_auto modify id int;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
四 : 唯一鍵
一張表往往有很多字段需要具有唯一性,數(shù)據(jù)不能重復(fù): 但是一張表中只能有一個(gè)主鍵: 唯一鍵(unique key)就可以解決表中有多個(gè)字段需要唯一性約束的問題.
唯一鍵的本質(zhì)與主鍵差不多: 唯一鍵默認(rèn)的允許自動(dòng)為空,而且可以多個(gè)為空(空字段不參與唯一性比較)
( 1 )增加唯一鍵
-
在創(chuàng)建表的時(shí)候,字段之后直接跟unique/unique key
create table my_unique1(
number char(10) unique comment '學(xué)號: 唯一,允許為空',
name varchar(20) not null
)charset utf8;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | char(10) | YES | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
-
在所有的字段之后增加unique key (字段列表); 復(fù)合唯一鍵
create table my_unique2(
number char(10) not null comment '學(xué)號',
name varchar(20) not null,
-- 增加唯一鍵
unique key(number)
)charset utf8;
之所以會顯示PRI : 剛好是一個(gè)不為空的唯一鍵(主鍵性質(zhì)一樣) : 原因是因?yàn)樵摫頉]有主鍵
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| number | char(10) | NO | PRI | NULL | |
| name | varchar(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+-------+
-
在創(chuàng)建表之后增加為一鍵
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| number | char(10) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
-- 追加唯一鍵
alter table my_unique3 add unique key(number);
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| number | char(10) | NO | UNI | NULL | |
| name | varchar(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
( 2 )唯一鍵約束
唯一鍵與主鍵本質(zhì)相同 : 唯一的區(qū)別就是唯一鍵默認(rèn)允許為空,而且多個(gè)為空.
insert into my_unique1 values(null,'美美'),('class0001','彩彩'),(null,'娜娜');
+-----------+--------+
| number | name |
+-----------+--------+
| NULL | 美美 |
| class0001 | 彩彩 |
| NULL | 娜娜 |
+-----------+--------+
添加重復(fù)元素報(bào)錯(cuò)
insert into my_unique1 values('class0001','思思');
ERROR 1062 (23000): Duplicate entry 'class0001' for key 'number'
注 : 如果唯一鍵也不允許為空那么唯一鍵與主鍵的約束作用是一致的.
( 3 )更新/刪除唯一鍵
更新唯一鍵 : 先刪除后增(唯一鍵可以有多個(gè) : 可以不刪除)
刪除唯一鍵
Alter table 表名 drop unique key; -- 錯(cuò)誤: 唯一鍵有多個(gè)
Alter table 表名 drop index 索引名字; -- 唯一鍵默認(rèn)的使用字段名作為索引名字
alter table my_unique3 drop index number;
+--------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| number | char(10) | NO | | NULL | |
| name | varchar(20) | NO | | NULL | |
+--------+-------------+------+-----+---------+----------------+
五 : 索引
幾乎所有的索引都是建立在字段之上.
索引 : 系統(tǒng)根據(jù)某種算法,將已有的數(shù)據(jù)(未來可能新增的數(shù)據(jù))單獨(dú)建立一個(gè)文件,文件能夠?qū)崿F(xiàn)快速匹配數(shù)據(jù),并且能夠快速的找到對應(yīng)表中的記錄.
索引的意義
1.提升查詢數(shù)據(jù)的效率
2.約束數(shù)據(jù)的有效性( 唯一性等)
增加索引的前提條件: 索引本身會產(chǎn)生索引文件(有時(shí)候有可能比數(shù)據(jù)文件還大) ,會非常耗費(fèi)磁盤空間.
如果某個(gè)字段需要作為查詢的條件經(jīng)常使用, 那么可以使用索引(一定會想辦法增加);
如果某個(gè)字段需要進(jìn)行數(shù)據(jù)的有效性約束, 也可能使用索引(主鍵,唯一鍵)
Mysql中提供了多種索引
- 主鍵索引: primary key
- 唯一索引: unique key
- 全文索引: fulltext index
- 普通索引: index
全文索引: 針對文章內(nèi)部的關(guān)鍵字進(jìn)行索引
全文索引最大的問題: 在于如何確定關(guān)鍵字
英文很容易: 英文單詞與單詞之間有空格
中文很難: 沒有空格, 而且中文可以各種隨意組合(分詞: sphinx)
六 : 范式
Normal Format : 是一種離散數(shù)學(xué)中的知識, 是為了解決一種數(shù)據(jù)的存儲與優(yōu)化的問題: 保存數(shù)據(jù)的存儲之后, 凡是能夠通過關(guān)系尋找出來的數(shù)據(jù),堅(jiān)決不再重復(fù)存儲: 終極目標(biāo)是為了減少數(shù)據(jù)的冗余.
范式: 是一種分層結(jié)構(gòu)的規(guī)范, 分為六層: 每一次層都比上一層更加嚴(yán)格: 若要滿足下一層范式,前提是滿足上一層范式.
六層范式 : 1NF,2NF,3NF...6NF, 1NF是最底層,要求最低;6NF最高層,最嚴(yán)格.
Mysql屬于關(guān)系型數(shù)據(jù)庫: 有空間浪費(fèi): 也是致力于節(jié)省存儲空間: 與范式所有解決的問題不謀而合: 在設(shè)計(jì)數(shù)據(jù)庫的時(shí)候, 會利用到范式來指導(dǎo)設(shè)計(jì).
但是數(shù)據(jù)庫不單是要解決空間問題,要保證效率問題: 范式只為解決空間問題, 所以數(shù)據(jù)庫的設(shè)計(jì)又不可能完全按照范式的要求實(shí)現(xiàn): 一般情況下,只有前三種范式需要滿足.
范式在數(shù)據(jù)庫的設(shè)計(jì)當(dāng)中是有指導(dǎo)意義: 但是不是強(qiáng)制規(guī)范.
( 1 ) 1NF
第一范式 : 在設(shè)計(jì)表存儲數(shù)據(jù)的時(shí)候, 如果表中設(shè)計(jì)的字段存儲的數(shù)據(jù),在取出來使用之前還需要額外的處理(拆分),那么說表的設(shè)計(jì)不滿足第一范式: 第一范式要求字段的數(shù)據(jù)具有原子性: 不可再分.
講師代課表
上表設(shè)計(jì)不存在問題,但是如果需求是將數(shù)據(jù)查出來之后,要求顯示一個(gè)老師從什么時(shí)候開始上課,到什么時(shí)候節(jié)課: 需要將代課時(shí)間進(jìn)行拆分: 不符合1NF, 數(shù)據(jù)不具有原子性, 可以再拆分.
解決方案 : 將代課時(shí)間拆分成兩個(gè)字段就解決問題
( 2 )2NF
第二范式 : 在數(shù)據(jù)表設(shè)計(jì)的過程中,如果有復(fù)合主鍵(多字段主鍵), 且表中有字段并不是由整個(gè)主鍵來確定, 而是依賴主鍵中的某個(gè)字段(主鍵的部分): 存在字段依賴主鍵的部分的問題, 稱之為部分依賴: 第二范式就是要解決表設(shè)計(jì)不允許出現(xiàn)部分依賴.
講師帶課表
以上表中: 因?yàn)橹v師沒有辦法作為獨(dú)立主鍵, 需要結(jié)合班級才能作為主鍵(復(fù)合主鍵: 一個(gè)老師在一個(gè)班永遠(yuǎn)只帶一個(gè)階段的課): 代課時(shí)間,開始和結(jié)束字段都與當(dāng)前的代課主鍵(講師和班級): 但是性別并不依賴班級, 教室不依賴講師: 性別只依賴講師, 教室只依賴班級: 出現(xiàn)了性別和教室依賴主鍵中的一部分: 部分依賴.不符合第二范式.
解決 :
解決方案1: 可以將性別與講師單獨(dú)成表, 班級與教室也單獨(dú)成表.
解決方案2: 取消復(fù)合主鍵, 使用邏輯主鍵
( 3 )3NF
要滿足第三范式,必須滿足第二范式.
第三范式: 理論上講,應(yīng)該一張表中的所有字段都應(yīng)該直接依賴主鍵(邏輯主鍵: 代表的是業(yè)務(wù)主鍵), 如果表設(shè)計(jì)中存在一個(gè)字段, 并不直接依賴主鍵,而是通過某個(gè)非主鍵字段依賴,最終實(shí)現(xiàn)依賴主鍵: 把這種不是直接依賴主鍵,而是依賴非主鍵字段的依賴關(guān)系稱之為傳遞依賴. 第三范式就是要解決傳遞依賴的問題.
講師帶課表
以上設(shè)計(jì)方案中: 性別依賴講師存在, 講師依賴主鍵; 教室依賴班級,班級依賴主鍵: 性別和教室都存在傳遞依賴.
解決方案 :
將存在傳遞依賴的字段,以及依賴的字段本身單獨(dú)取出,形成一個(gè)單獨(dú)的表, 然后在需要對應(yīng)的信息的時(shí)候, 使用對應(yīng)的實(shí)體表的主鍵加進(jìn)來.
講師代課表
講師表
班級表
從表面來看講師表與班級表也會有傳遞依賴,但是邏輯主鍵只是業(yè)務(wù)主鍵的一個(gè)替代,永遠(yuǎn)不要看邏輯主鍵,要看真正所代表的主鍵
所以 講師表: ID = 講師?, 班級表中: ID = 班級
( 4 )逆規(guī)范化
有時(shí)候, 在設(shè)計(jì)表的時(shí)候,如果一張表中有幾個(gè)字段是需要從另外的表中去獲取信息. 理論上講, 的確可以獲取到想要的數(shù)據(jù), 但是就是效率低一點(diǎn). 會刻意的在某些表中,不去保存另外表的主鍵(邏輯主鍵), 而是直接保存想要的數(shù)據(jù)信息: 這樣一來,在查詢數(shù)據(jù)的時(shí)候, 一張表可以直接提供數(shù)據(jù), 而不需要多表查詢(效率低), 但是會導(dǎo)致數(shù)據(jù)冗余增加.
講師代課信息表
逆規(guī)規(guī)范化 : 磁盤利用率與效率的對抗
七 : 數(shù)據(jù)高級操作
( 1 )主鍵沖突
在數(shù)據(jù)插入的時(shí)候, 假設(shè)主鍵對應(yīng)的值已經(jīng)存在: 插入一定會失敗!
當(dāng)主鍵存在沖突的時(shí)候(Duplicate key),可以選擇性的進(jìn)行處理: 更新和替換
+---------+------+
| name | room |
+---------+------+
| PHP0810 | B203 |
| PHP0710 | B203 |
+---------+------+
插入重復(fù)數(shù)據(jù)
insert into my_class values('PHP0810','B205');
ERROR 1062 (23000): Duplicate entry 'PHP0810' for key 'PRIMARY'
解決主鍵沖突 :
-- 主鍵沖突: 更新
insert into my_class values('PHP0810','B205')
-- 沖突處理
on duplicate key update
-- 更新教室
room = 'B205';
+---------+------+
| name | room |
+---------+------+
| PHP0710 | B203 |
| PHP0810 | B205 |
( 2 )主鍵沖突 : 替換
Replace into 表名 [(字段列表:包含主鍵)] values(值列表);
replace into my_class values('PHP0710','A203');
+---------+------+
| name | room |
+---------+------+
| PHP0710 | A203 |
| PHP0810 | B205 |
+---------+------+
( 3 )蠕蟲復(fù)制
從已有的數(shù)據(jù)中去獲取數(shù)據(jù),然后將數(shù)據(jù)又進(jìn)行新增操作: 數(shù)據(jù)成倍的增加.
表創(chuàng)建高級操作: 從已有表創(chuàng)建新表(復(fù)制表結(jié)構(gòu))
Create table 表名 like 數(shù)據(jù)庫.表名;
+--------+--------+
| name | money |
+--------+--------+
| 思思 | 123.23 |
| 美美 | 234.55 |
mysql> create table my_copy like my_teacher;
Query OK, 0 rows affected (0.04 sec)
+-------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+-------+
| name | varchar(20) | NO | | NULL | |
| money | decimal(10,2) | NO | | NULL | |
+-------+---------------+------+-----+---------+-------+
蠕蟲復(fù)制: 先查出數(shù)據(jù), 然后將查出的數(shù)據(jù)新增一遍
Insert into 表名[(字段列表)] select 字段列表/* from 數(shù)據(jù)表名;
mysql> insert into my_copy select * from my_copy;
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into my_copy select * from my_copy;
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> insert into my_copy select * from my_copy;
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
+--------+--------+
| name | money |
+--------+--------+
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
+--------+--------+
蠕蟲復(fù)制的意義
- 從已有表拷貝數(shù)據(jù)到新表中
- 可以迅速的讓表中的數(shù)據(jù)膨脹到一定的數(shù)量級: 測試表的壓力以及效率
( 4 )更新數(shù)據(jù)
Update 表名 set 字段 = 值 [where條件];
高級新增語法
Update 表名 set 字段 = 值 [where條件] [limit 更新數(shù)量];
mysql> update my_copy set name = '可可' where name = '美美' limit 3;
Query OK, 3 rows affected (0.00 sec)
+--------+--------+
| name | money |
+--------+--------+
| 思思 | 123.23 |
| 可可 | 234.55 |
| 思思 | 123.23 |
| 可可 | 234.55 |
| 思思 | 123.23 |
| 可可 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
+--------+--------+
( 5 )刪除數(shù)據(jù)
與更新類似: 可以通過limit來限制數(shù)量
Delete from 表名 [where條件] [limit 數(shù)量];
mysql> delete from my_copy where name = '思思' limit 5;
Query OK, 5 rows affected (0.00 sec)
+--------+--------+
| name | money |
+--------+--------+
| 可可 | 234.55 |
| 可可 | 234.55 |
| 可可 | 234.55 |
| 美美 | 234.55 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
| 思思 | 123.23 |
| 美美 | 234.55 |
+--------+--------+
刪除: 如果表中存在主鍵自增長,那么當(dāng)刪除之后, 自增長不會還原
數(shù)據(jù)的刪除是不會改變表結(jié)構(gòu), 只能刪除表后重建表
Truncate 表名;?-- 先刪除改變,后新增改變
+----+--------+
| id | name |
+----+--------+
| 1 | 雪芙 |
| 2 | 思思 |
| 3 | 彩彩 |
| 6 | 美美 |
| 7 | 想想 |
| 11 | 勁勁 |
| 16 | 辰辰 |
+----+--------+
-- 清空表: 重置自增長
truncate my_auto;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
| my_auto | CREATE TABLE `my_auto` (
`id` int(11) NOT NULL,
`name` varchar(10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------+
( 6 )查詢數(shù)據(jù)
基本語法
Select 字段列表/* from 表名 [where條件];
完整語法
Select [select選項(xiàng)] 字段列表[字段別名]/* from 數(shù)據(jù)源 [where條件子句] [group by子句] [having子句] [order by子句] [limit 子句];
Select選項(xiàng): select對查出來的結(jié)果的處理方式
All: 默認(rèn)的,保留所有的結(jié)果
Distinct: 去重, 查出來的結(jié)果,將重復(fù)給去除(所有字段都相同)
select distinct * from my_auto;
字段別名
字段別名: 當(dāng)數(shù)據(jù)進(jìn)行查詢出來的時(shí)候, 有時(shí)候名字并一定就滿足需求(多表查詢的時(shí)候, 會有同名字段). 需要對字段名進(jìn)行重命名: 別名
語法
字段名 [as] 別名;
數(shù)據(jù)源
數(shù)據(jù)源: 數(shù)據(jù)的來源, 關(guān)系型數(shù)據(jù)庫的來源都是數(shù)據(jù)表: 本質(zhì)上只要保證數(shù)據(jù)類似二維表,最終都可以作為數(shù)據(jù)源.
數(shù)據(jù)源分為多種: 單表數(shù)據(jù)源, 多表數(shù)據(jù)源, 查詢語句
單表數(shù)據(jù)源: select * from 表名;
多表數(shù)據(jù)源: select* from 表名1,表名2...;
從一張表中取出一條記錄,去另外一張表中匹配所有記錄,而且全部保留:(記錄數(shù)和字段數(shù)),將這種結(jié)果成為: 笛卡爾積(交叉連接): 笛卡爾積沒什么卵用, 所以應(yīng)該盡量避免.子查詢: 數(shù)據(jù)的來源是一條查詢語句(查詢語句的結(jié)果是二維表)
Select * from (select 語句) as 表名;
( 7 )Where子句
Where子句: 用來判斷數(shù)據(jù),篩選數(shù)據(jù).
Where子句返回結(jié)果: 0或者1, 0代表false,1代表true.
判斷條件:
比較運(yùn)算符:>,<, >=,<= ,!= ,<>,=, like, between and,in/not in
邏輯運(yùn)算符: &&(and), ||(or), !(not)
Where原理:
where是唯一一個(gè)直接從磁盤獲取數(shù)據(jù)的時(shí)候就開始判斷的條件: 從磁盤取出一條記錄, 開始進(jìn)行where判斷: 判斷的結(jié)果如果成立保存到內(nèi)存;如果失敗直接放棄.
( 8 )Group by子句
Group by:分組的意思, 根據(jù)某個(gè)字段進(jìn)行分組(相同的放一組,不同的分到不同的組)
+----+-----------+--------+------+--------+
| id | number | name | sex | height |
+----+-----------+--------+------+--------+
| 1 | class0001 | 思思 | 男 | 184 |
| 2 | class0002 | 可可 | 男 | 182 |
| 3 | class0003 | 娜娜 | 女 | 156 |
| 4 | class0004 | 美美 | 男 | 172 |
| 5 | class0005 | 彩彩 | 男 | 166 |
+----+-----------+--------+------+--------+
基本語法 : gruop by 字段名;
select * from my_student group by sex;
分組的意思: 是為了統(tǒng)計(jì)數(shù)據(jù)(按組統(tǒng)計(jì): 按分組字段進(jìn)行數(shù)據(jù)統(tǒng)計(jì))
SQL提供了一系列統(tǒng)計(jì)函數(shù)
- Count(): 統(tǒng)計(jì)分組后的記錄數(shù): 每一組有多少記錄
- Max():?統(tǒng)計(jì)每組中最大的值
- Min(): 統(tǒng)計(jì)最小值
- Avg(): 統(tǒng)計(jì)平均值
- Sum(): 統(tǒng)計(jì)和
mysql> select sex,count(*),max(height),min(height) from my_student group by sex;
+------+----------+-------------+-------------+
| sex | count(*) | max(height) | min(height) |
+------+----------+-------------+-------------+
| 女 | 1 | 156 | 156 |
| 男 | 4 | 184 | 166 |
+------+----------+-------------+-------------+
Count函數(shù): 里面可以使用兩種參數(shù): *代表統(tǒng)計(jì)記錄,字段名代表統(tǒng)計(jì)對應(yīng)的字段(NULL不統(tǒng)計(jì))
Group by 字段 [asc|desc];?-- 對分組的結(jié)果然后合并之后的整個(gè)結(jié)果進(jìn)行排序
按照男,女 校對集順序
多字段分組 : 先根據(jù)一個(gè)字段進(jìn)行分組,然后對分組后的結(jié)果再次按照其他字段進(jìn)行分組
alter table my_student add c_id int;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> update my_student set c_id = ceil(random()*3);
+----+-----------+--------+------+--------+------+
| id | number | name | sex | height | c_id |
+----+-----------+--------+------+--------+------+
| 1 | class0001 | 思思 | 男 | 184 | 3 |
| 2 | class0002 | 可可 | 男 | 182 | 1 |
| 3 | class0003 | 娜娜 | 女 | 156 | 1 |
| 4 | class0004 | 美美 | 男 | 172 | 3 |
| 5 | class0005 | 彩彩 | 男 | 166 | 3 |
+----+-----------+--------+------+--------+------+
多字段分組,先按班級份,再按性別分
mysql> select c_id,sex,count(*),group_concat(name) from my_student group by c_id,sex; -- 多字段排序
group_concat: 可以對分組的結(jié)果中的某個(gè)字段進(jìn)行字符串連接(保留該組所有的某個(gè)字段):
+------+------+----------+----------------------+
| c_id | sex | count(*) | group_concat(name) |
+------+------+----------+----------------------+
| 1 | 女 | 1 | 娜娜 |
| 1 | 男 | 1 | 可可 |
| 3 | 男 | 3 | 思思,美美,彩彩 |
+------+------+----------+----------------------+
回溯統(tǒng)計(jì) : with rollup: 任何一個(gè)分組后都會有一個(gè)小組, 最后都需要向上級分組進(jìn)行匯報(bào)統(tǒng)計(jì): 根據(jù)當(dāng)前分組的字段. 這就是回溯統(tǒng)計(jì): 回溯統(tǒng)計(jì)的時(shí)候會將分組字段置空.
select c_id,count(*) from my_student group by c_id with rollup;
+------+----------+
| c_id | count(*) |
+------+----------+
| 1 | 2 |
| 3 | 3 |
| NULL | 5 |
+------+----------+
多字段回溯 : 考慮第一層分組會有此回溯: 第二次分組要看第一次分組的組數(shù), 組數(shù)是多少,回溯就是多少,然后加上第一層回溯即可.
+------+------+----------+------------------------------------+
| c_id | sex | count(*) | group_concat(name) |
+------+------+----------+------------------------------------+
| 1 | 女 | 1 | 娜娜 |
| 1 | 男 | 1 | 可可 |
| 1 | NULL | 2 | 娜娜,可可 |
| 3 | 男 | 3 | 思思,美美,彩彩 |
| 3 | NULL | 3 | 思思,美美,彩彩 |
| NULL | NULL | 5 | 娜娜,可可,思思,美美,彩彩 |
+------+------+----------+------------------------------------+
( 9 )Having子句
Having子句: 與where子句一樣: 進(jìn)行條件判斷的.
Where是針對磁盤數(shù)據(jù)進(jìn)行判斷: 進(jìn)入到內(nèi)存之后,會進(jìn)行分組操作: 分組結(jié)果就需要having來處理.
Having能做where能做的幾乎所有事情, 但是where卻不能做having能做的很多事情.
① : 分組統(tǒng)計(jì)的結(jié)果或者說統(tǒng)計(jì)函數(shù)都只有having能夠使用.
select c_id,count(*) from my_student group by c_id having count(*) >= 2;
+------+----------+
| c_id | count(*) |
+------+----------+
| 1 | 2 |
| 3 | 3 |
+------+----------+
② Having能夠使用字段別名: where不能: where是從磁盤取數(shù)據(jù),而名字只可能是字段名: 別名是在字段進(jìn)入到內(nèi)存后才會產(chǎn)生.
select c_id,count(*) as total from my_student group by c_id having total >= 2;
+------+-------+
| c_id | total |
+------+-------+
| 1 | 2 |
| 3 | 3 |
+------+-------+
報(bào)錯(cuò)
select c_id,count(*) as total from my_student where total >= 2 group by c_id ;
ERROR 1054 (42S22): Unknown column 'total' in 'where clause'
( 10 )Order by 子句
Order by: 排序, 根據(jù)某個(gè)字段進(jìn)行升序或者降序排序, 依賴校對集.
Order by 字段名 [asc|desc]; -- asc是升序(默認(rèn)的),desc是降序
select * from my_student order by c_id;
+----+-----------+--------+------+--------+------+
| id | number | name | sex | height | c_id |
+----+-----------+--------+------+--------+------+
| 2 | class0002 | 可可 | 男 | 182 | 1 |
| 3 | class0003 | 娜娜 | 女 | 156 | 1 |
| 1 | class0001 | 思思 | 男 | 184 | 3 |
| 4 | class0004 | 美美 | 男 | 172 | 3 |
| 5 | class0005 | 彩彩 | 男 | 166 | 3 |
+----+-----------+--------+------+--------+------+
排序可以進(jìn)行多字段排序: 先根據(jù)某個(gè)字段進(jìn)行排序, 然后排序好的內(nèi)部,再按照某個(gè)數(shù)據(jù)進(jìn)行再次排序:
-- 多字段排序: 先班級排序,后性別排序
select * from my_student order by c_id, sex desc;
+----+-----------+--------+------+--------+------+
| id | number | name | sex | height | c_id |
+----+-----------+--------+------+--------+------+
| 2 | class0002 | 可可 | 男 | 182 | 1 |
| 3 | class0003 | 娜娜 | 女 | 156 | 1 |
| 1 | class0001 | 思思 | 男 | 184 | 3 |
| 4 | class0004 | 美美 | 男 | 172 | 3 |
| 5 | class0005 | 彩彩 | 男 | 166 | 3 |
+----+-----------+--------+------+--------+------+
( 11 )Limit子句
Limit子句是一種限制結(jié)果的語句: 限制數(shù)量.
Limit有兩種使用方式
- 只用來限制長度(數(shù)據(jù)量): limit 數(shù)據(jù)量
select * from my_student limit 2;
+----+-----------+--------+------+--------+------+
| id | number | name | sex | height | c_id |
+----+-----------+--------+------+--------+------+
| 1 | class0001 | 思思 | 男 | 184 | 3 |
| 2 | class0002 | 可可 | 男 | 182 | 1 |
+----+-----------+--------+------+--------+------+
- 限制起始位置,限制數(shù)量: limit 起始位置,長度
select * from my_student limit 0,2; -- 記錄數(shù)是從0開始編號
+----+-----------+--------+------+--------+------+
| id | number | name | sex | height | c_id |
+----+-----------+--------+------+--------+------+
| 1 | class0001 | 思思 | 男 | 184 | 3 |
| 2 | class0002 | 可可 | 男 | 182 | 1 |
+----+-----------+--------+------+--------+------+
Limit主要用來實(shí)現(xiàn)數(shù)據(jù)的分頁: 為用戶節(jié)省時(shí)間,提交服務(wù)器的響應(yīng)效率, 減少資源的浪費(fèi).
對于用戶來講: 可以點(diǎn)擊的分頁按鈕: 1,2,3,4
對于服務(wù)器來講: 根據(jù)用戶選擇的頁碼來獲取不同的數(shù)據(jù): limit offset,length;
Length: 每頁顯示的數(shù)據(jù)量: 基本不變
Offset: offset = (頁碼 - 1) * 每頁顯示量
