MySQL數(shù)據(jù)庫(kù)
數(shù)據(jù)庫(kù)有關(guān)系型和非關(guān)系型之分,MySQL屬于關(guān)系型數(shù)據(jù)庫(kù)备图。
關(guān)系型數(shù)據(jù)庫(kù)的優(yōu)勢(shì)：
1.復(fù)雜的查詢(xún), 可以使用SQL語(yǔ)句在一個(gè)或者多個(gè)表之間進(jìn)行復(fù)雜的查詢(xún)
2.事務(wù)的支持,提高安全性能
非關(guān)系型數(shù)據(jù)庫(kù)的優(yōu)勢(shì)：
1.性能, NoSQL是基于鍵值對(duì)的,不需要SQL層的解析,性能高垢村。
2.可拓展,數(shù)據(jù)之間沒(méi)有耦合性, 水平擴(kuò)展非常容易
一. 安裝好數(shù)據(jù)庫(kù)后進(jìn)行MySQL數(shù)據(jù)庫(kù)
# 在終端中輸入
>> mysql -h主機(jī)名 -u用戶(hù)名 -p # 回車(chē)
>> 輸入密碼
實(shí)例：
>> mysql -hlocalhost -uroot -p
>> 123456
# 本地可以不用加主機(jī)名
>> mysql -uroot -p
二.對(duì)數(shù)據(jù)庫(kù)的操作
對(duì)數(shù)據(jù)庫(kù)的操作命令: create創(chuàng)建, drop刪除,alter修改,show查看
# 1. 查看所有的數(shù)據(jù)庫(kù)
show databases;
# 2. 選擇數(shù)據(jù)庫(kù)
use 庫(kù)名;
# 3. 查看當(dāng)前所有數(shù)據(jù)庫(kù)
select database();
# 4. 創(chuàng)建數(shù)據(jù)庫(kù)
create database myTest;
# 5. 查看當(dāng)前所創(chuàng)建的數(shù)據(jù)庫(kù)
show create database 庫(kù)名; (將;換成\G豎著顯示)
# 6. 創(chuàng)建一個(gè)不存在的數(shù)據(jù)庫(kù)
create database if not exists myTest;
# 7. 刪除數(shù)據(jù)庫(kù)
drop database 庫(kù)名;
drop database if exists 庫(kù)名; # 如果不存在刪除數(shù)據(jù)庫(kù),可能會(huì)報(bào)錯(cuò)
# 8. 創(chuàng)建數(shù)據(jù)庫(kù)并設(shè)置字符集
create database 庫(kù)名 character set utf8;
# 9. 修改數(shù)據(jù)庫(kù)的字符集
alter database 庫(kù)名 character set utf8;
注意：
1> 在MySQL中要以;作為語(yǔ)句結(jié)束的標(biāo)志
2> 命令不區(qū)分大小寫(xiě)
3> 數(shù)據(jù)庫(kù)，表不能重名
4> 當(dāng)在命令中 多輸入引號(hào)以后 所有輸入的內(nèi)容都被認(rèn)為是引號(hào)內(nèi)的內(nèi)容 將引號(hào)補(bǔ)全即可
5> \c 撤銷(xiāo)當(dāng)前命令
6> 數(shù)據(jù)庫(kù)的退出\q 或 exit 或 quit
三.對(duì)數(shù)據(jù)庫(kù)中表的操作
實(shí)例：
# 1. 創(chuàng)建表
create teable 表名(
字段名1 類(lèi)型 約束條件,
字段名2 類(lèi)型 約束條件,....
);
# 2. 刪除表
drop table 表名;
# 3. 查看當(dāng)前所創(chuàng)建的表
show create table 表名; (將;換成\G豎著顯示)
# 4. 查看當(dāng)前數(shù)據(jù)庫(kù)中的所有表
show tables;
# 5. 查看創(chuàng)建的表的結(jié)構(gòu)
desc 表名;
# 6. 添加索引(不寫(xiě)索引名)
alter table 表名 add 索引類(lèi)型(索引字段);
例如： alter table A add index(username);
# 7. 添加索引(起索引名稱(chēng))
alter table 表名 add 索引類(lèi)型 索引名(索引字段)
例如： alter table A add unique uname(username); # 添加唯一索引
# 8. 查看當(dāng)前表的所有索引
show index from 表名;
# 9. 創(chuàng)建和表b一樣的表a
create table b like a;
# 10. 刪除索引
alter table 表名 drop index 索引名;
例如：alter table A drop index uname;
四.對(duì)表結(jié)構(gòu)的修改
實(shí)例：
# 1. 修改表的字符集
alter table 表名 character set 字符集;
# 2. 給表添加新的字段
alter table 表名 add 字段名 字段類(lèi)型 約束條件 [after/first];
例如： alter table myInt add age tinyint first -- 將age添加到第一位
# 3. 修改表字段類(lèi)型和約束條件
alter table 表名 modify 字段名 類(lèi)型 約束條件 [after/first];
例如：alter table 表名 modify 字段名 類(lèi)型 約束條件 character set utf8;
# 4. 修改字段名
alter table 表名 change 舊字段名 新字段名 類(lèi)型 約束條件;
# 5. 刪除字段名
alter table 表名 drop 字段名;
# 6. 修改表名
alter table 表名 rename 新表名;
例如：alter table company rename employee;
小技巧:
# 開(kāi)啟不嚴(yán)謹(jǐn)報(bào)錯(cuò)
可以修改my.ini的配置文件
我的路徑是在:C:\ProgramData\MySQL\MySQL Server 5.7
# 找到這行并修改為下面即可
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
五.MySQL的數(shù)據(jù)類(lèi)型
MySQL支持多種類(lèi)型, 大致可以分為四類(lèi)：數(shù)值型妙蔗、浮點(diǎn)型、日期/時(shí)間和字符類(lèi)型了赌。
1. 數(shù)值型
| 類(lèi)型 | 大小 | 范圍(有符號(hào)) | 范圍(無(wú)符號(hào)) | 用途 |
|---|---|---|---|---|
| tinyint | 1字節(jié) | -128 ~ 127 | 0~255 | 最小整數(shù)值(年齡,狀態(tài)) |
| smallint | 2字節(jié) | -32768 ~ 32767 | 0-65535 | 整數(shù)值 |
| int | 4字節(jié) | -2^31 ~ 2^31-1 | 0 ~ 2^32-1 | 整數(shù)值 |
| bigint | 8字節(jié) | -2^63 ~ 2^63-1 | 0 ~ 2^64-1 | 存儲(chǔ)大的整數(shù)值 |
2. 浮點(diǎn)型
| 類(lèi)型 | 大小 | 范圍(有符號(hào)) | 范圍(無(wú)符號(hào)) | 用途 |
|---|---|---|---|---|
| float | 4字節(jié) | -2^31 ~ 2^31-1 | 0 ~ 2^32-1 | 單精度浮點(diǎn)型 |
| double | 8字節(jié) | -2^63 ~ 2^63-1 | 0 ~ 2^64-1 | 雙精度浮點(diǎn)型 |
| decimal | 8字節(jié) | -2^63 ~ 2^63-1 | 0 ~ 2^64-1 | 更加精準(zhǔn)的小數(shù)類(lèi)型 |
3. 日期割按、時(shí)間型
| 類(lèi)型 | 大小 | 范圍(有符號(hào)) | 范圍(無(wú)符號(hào)) | 用途 |
|---|---|---|---|---|
| date | 3個(gè)字節(jié) | 1000-01-01~9999-12-31 | YYYY-MM-DD | 存儲(chǔ)日期值 |
| time | 3個(gè)字節(jié) | -838:59:59~838:59:59 | HH:MM:SS | 存儲(chǔ)時(shí)間值 |
| year | 1個(gè)字節(jié) | 1901~2155 | YYYY | 存儲(chǔ)年份 |
| datetime | 8個(gè)字節(jié) | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合時(shí)間 |
4. 字符類(lèi)型
| 類(lèi)型 | 大小 | 用途 |
|---|---|---|
| char | 0-255字節(jié) | 存儲(chǔ)定長(zhǎng)字符串 |
| varchar | 0-255字節(jié) | 存儲(chǔ)變長(zhǎng)字符串 |
| text | 0-65535字節(jié) | 長(zhǎng)文本數(shù)據(jù) |
| enum('w', 'm') | 最多65535字節(jié) | 枚舉：可賦予某個(gè)枚舉成員來(lái)存儲(chǔ) |
| set('w','m') | 最多64個(gè)成員 | 集合, 多個(gè)集合成員,用逗號(hào)隔開(kāi) |
注意：
char和varchar的區(qū)別？
答：char的執(zhí)行效率相比varchar較好, 但varchar比char更節(jié)省內(nèi)存空間
enum和set區(qū)別静浴？
答：enum只能選擇創(chuàng)建表時(shí)設(shè)置的值中的某一個(gè)值進(jìn)行存儲(chǔ); set可以選擇一個(gè)或多個(gè)值進(jìn)行存儲(chǔ), 如果存在重復(fù)堰氓，會(huì)去重。
六.字段約束
(1) unsigned 無(wú)符號(hào)整形
只能用于設(shè)置數(shù)值類(lèi)型,只能存儲(chǔ)無(wú)符號(hào), 存儲(chǔ)的正數(shù)存儲(chǔ)范圍會(huì)擴(kuò)大一倍(即0-xx)苹享。
(2) zerofill 零填充
只能設(shè)置數(shù)值類(lèi)型, 當(dāng)數(shù)值位數(shù)長(zhǎng)度不足設(shè)置的長(zhǎng)度,會(huì)使用0自動(dòng)填充到指定的長(zhǎng)度双絮。
(3) auto_increment 主鍵自增
用于設(shè)置字段值的自動(dòng)增長(zhǎng), 當(dāng)每增加一條數(shù)據(jù)的時(shí)候, 當(dāng)前值會(huì)自動(dòng)加1。
(4) default 默認(rèn)值
可以給某個(gè)字段設(shè)置默認(rèn)值, 當(dāng)不給當(dāng)前字段添加值的時(shí)候,該字段的值為默認(rèn)值得问。
(5) null 和 not null
默認(rèn)為null, 當(dāng)給當(dāng)前表添加數(shù)據(jù)的時(shí)候不給某個(gè)字段添加值,則當(dāng)前字段的值為null, 如果設(shè)置為not null, 那么在添加數(shù)據(jù)的時(shí)候就必須給當(dāng)前字段添加值囤攀。
(6) comment 設(shè)置當(dāng)前字段的說(shuō)明
(7) foreign key外鍵約束
使用外鍵可以保證數(shù)據(jù)的完整性。
在從表添加一列字段,作為外鍵宫纬。
添加外鍵:
alter table 從表(多的那一表) add [constraint] [外鍵名] foreign key 從表外鍵字段名 references 主表(主表的主鍵);
刪除外鍵:
# 法一：
alter table product(從表) drop foreign key 外鍵名(指定的外鍵名,如果外鍵名省略了,不能刪);
# 法二： 先刪除從表product中的數(shù)據(jù)
delete from product where pid in ('p009','p008');
# 在刪除主表(category)
delete from category where cid = 'c003';
一對(duì)多關(guān)系：
alter table product add FOREIGN key(category_id) REFERENCES category(cid);
多對(duì)多的關(guān)系：
# 三張表,一個(gè)是stu學(xué)生表, 一個(gè)是course課程表, 第三張表stu-course存儲(chǔ)兩個(gè)表的關(guān)系
alter table 從表(stu-course) add foreign key(sno) references stu(sid);
alter table 從表(stu-course) add foreign key(cno) referencescourse(cid);
注意：
- 和null進(jìn)行算數(shù)運(yùn)算結(jié)果都為null 例如：select 11 + null 結(jié)果為null
- null意味著沒(méi)有值或者是未知值
- 對(duì)于外鍵, 如果從表(多的那個(gè)表)中存在關(guān)聯(lián),主表(一的表)不能刪除
- 從表中不能添加主表不存在的記錄
七. MySQL的索引
MySQL的索引包括: (1) 主鍵索引primary key; (2) 唯一索引unique; (3) 常規(guī)索引index; (4) 全文索引fulltext
(1) 主鍵索引
主鍵索引(primary key)是關(guān)系型數(shù)據(jù)庫(kù)中最常見(jiàn)的一種索引類(lèi)型焚挠，用來(lái)記錄唯一標(biāo)識(shí)表中某一行的屬性或?qū)傩越M，一個(gè)表只能有一個(gè)主鍵哪怔，可以確保執(zhí)行數(shù)據(jù)更新、刪除的時(shí)候不會(huì)出現(xiàn)錯(cuò)誤向抢。而且主鍵除了上述作用外认境，還常常與外鍵構(gòu)成參照完整性約束，防止出現(xiàn)數(shù)據(jù)不一致挟鸠。數(shù)據(jù)庫(kù)在設(shè)計(jì)時(shí)叉信，主鍵起到了很重要的作用。
# 每次刪除所有的數(shù)據(jù)(delete而不是truncate), 下次在添加數(shù)據(jù)的還是會(huì)從上次記錄的位置開(kāi)始艘希，繼續(xù)自增硼身。
# 讓主鍵自增歸位(從新開(kāi)始自增)
alter table 表名 auto_increment = 1;
# 或者通過(guò)清空表并將自增歸位
truncate 表名;
注意：
1> 每個(gè)表最好都有一個(gè)主鍵索引, 但是不指定不會(huì)報(bào)錯(cuò)。
2> 一個(gè)表只有有一個(gè)主鍵, 主鍵的值不能為NULL覆享。
3> 主鍵可以有多個(gè)約束條件, 比如auto_increment, not null等佳遂。
(2) 唯一索引
唯一索引和主鍵索引相同的地方是都可以防止創(chuàng)建值的重復(fù)，確保數(shù)據(jù)的唯一性撒顿，但是唯一索引在一個(gè)表中可以有多個(gè)丑罪。
通過(guò)使用unique對(duì)對(duì)應(yīng)字段添加唯一索引。
(3) 常規(guī)索引
常規(guī)索引的唯一任務(wù)是加快對(duì)數(shù)據(jù)的訪(fǎng)問(wèn)速度凤壁， 但是缺點(diǎn)也是明顯的吩屹，會(huì)占用更多的磁盤(pán)空間，而且會(huì)減慢刪改的效率拧抖。
通過(guò)使用index或key對(duì)對(duì)應(yīng)字段添加常規(guī)索引煤搜。
實(shí)例：
mysql> create table user(
-> id int unsigned primary key auto_increment, # 添加了 unsigned和auto_increment 字段約束和主鍵索引
-> username varchar(50) not null, # 添加了not null的字段約束
-> userpass varchar(32) not null,
-> telnum varchar(11) not null unique, # 添加了唯一索引
-> sex enum('m','w') not null default 'm', # 添加了default字段約束
-> birthday date not null default '0000-00-00',
-> index iuser(username), # 也可以通過(guò)下面格式添加索引： 索引類(lèi)型 索引名稱(chēng)不寫(xiě)為字段名 (要添加索引的字段)
->key (userpass) , # key同上面的index, 給usepass添加常規(guī)索引，索引名為字段名, 括號(hào)必須要加唧席。
-> );
八. MySQL中MyISAM與InnoDB兩種引擎的區(qū)別
在MySQL中MyISAM和InnoDB兩種引擎的表的類(lèi)型最為重要, 關(guān)于二者的不同擦盾。
(1) MyISAM不支持事務(wù)處理, InnoDB支持事務(wù)
(2) MyISAM不支持外鍵, InnnoDB支持
(3) MyISAM的執(zhí)行效率要高于InnoDB
(4) 存儲(chǔ)結(jié)構(gòu)上：
首先都會(huì)創(chuàng)建一個(gè)名為 .frm文件的文件嘲驾；不同點(diǎn)是創(chuàng)建一個(gè)表時(shí)MyISAM會(huì)生成三個(gè)文件, .MyD是存My Data 表數(shù)據(jù)的文件, .MyI是存My Index索引的文件, .log日志文件; 而InnoDB引擎創(chuàng)建表的時(shí)候只會(huì)生成有一個(gè).ibd的文件存儲(chǔ)數(shù)據(jù)庫(kù)的表數(shù)據(jù)和索引
(5) 存儲(chǔ)空間上：
MyISAM可被壓縮，存儲(chǔ)空間較小; InnoDB需要更多的內(nèi)存和存儲(chǔ)厌衙。
九.InnoDB引擎的操作相關(guān)
1. 查看當(dāng)前表的存儲(chǔ)引擎
命令：show create table 表名;
2. 引擎的修改
命令：alter table 表名 engine = InnoDB;
3. 查看是否為自動(dòng)提交
命令：select @@autocommit
注意：autocommit是mysql中的InnoDb數(shù)據(jù)表特有的語(yǔ)句距淫。（只有在表引擎=InnoDb時(shí), autocommit才會(huì)生效）在InnoDb表中，所有的語(yǔ)句都是需要commit后婶希，才會(huì)在真實(shí)數(shù)據(jù)庫(kù)中生效, 設(shè)置為1之后會(huì)自動(dòng)commit,不用手動(dòng)在commit一次, 但是只在沒(méi)有開(kāi)始事務(wù)的時(shí)候是這樣的榕暇，如果開(kāi)啟了事務(wù), 必須要手動(dòng)commit才能插入或修改數(shù)據(jù)庫(kù)的內(nèi)容。
修改命令：set autocommit = 0喻杈；（默認(rèn)是1）
4.修改為手動(dòng)提交后
開(kāi)啟事務(wù): begin;
進(jìn)行sql操作: ~~~
提交或回滾:
commit work; # 提交
rollback work; # 回滾
十. 對(duì)數(shù)據(jù)的增刪改查
對(duì)數(shù)據(jù)常見(jiàn)的四種操作: 增(insert)刪(delete)改(update)查(select)彤枢；
1. insert 添加
(1) 指定字段添加值
語(yǔ)句: insert into 表名(字段名1, 字段名2,...) values(值1, 值2,..);
(2) 不指定字段添加值，有多少字段就要添加幾個(gè)值筒饰，要一一對(duì)應(yīng)
語(yǔ)句: insert into 表名 values(值1,值2,..);
(3) 一條語(yǔ)句添加多個(gè)值
語(yǔ)句1: insert into 表名 values(值1,值2,..),(值1,值2,..),...
語(yǔ)句2: insert into 表名(字段名1, 字段名2,..) (值1,值2,..),(值1,值2,..),...
2. select查詢(xún)
語(yǔ)句: select 字段 from 表名 [where 條件][group by having 字段][order by 字段]
(1) 不指定字段查詢(xún)
語(yǔ)句: select * from 表名;
(2) 指定字段查詢(xún)
語(yǔ)句: select 字段1,字段2,.. from 表名;
(3) 給查詢(xún)的字段起別名
語(yǔ)句1: select 字段名 別名, 字段名 別名 from 表名;
語(yǔ)句2: select 字段名 as 別名, 字段名 as 別名 from 表名;
(4) 添加where條件
# (1) 添加比較運(yùn)算符
> 大于 select * from 表名 where age > 18;
< 小于 select * from 表名 where age < 18;
>= 大于等于 select * from 表名 where age >= 18;
<= 小于等于 select * from 表名 where age <= 18;
!= 或<> 不等于 select * from 表名 where id != 1;
= 等于 select * from 表名 where id =1;
# (2) 邏輯運(yùn)算符
and 邏輯與 select * from 表名 where username ='zs' and age = 18; # 用戶(hù)名為zs且為18歲的記錄
or 邏輯或 select * fom 表名 where username = 'zs' or age = 18; # 用戶(hù)名為zs或?yàn)?8歲的記錄
between...and 在..之間 包含值本身 select * from 表名 where id between 3 and 9; 類(lèi)同與 select * from 表名 where id >=3 and id <= 9;
not between ... and 不在..之間 select * from a where id not between 3 and 9;
in 在..里 select * from 表名 where id in (1,2,10);
not in 不在..里 select * from 表名 where not in (1,2,10);
# (3) 子查詢(xún), 條件還是一條sql語(yǔ)句
select * from 表名 where id in (select id from 表名 where age = 18);
# (4) order by 排序
語(yǔ)法：order by 字段名 asc/desc(升序/降序);
select * from 表名 order by id desc; # 按照id降序
select * from 表名 where age > 18 order by age; # 查看age 大于18的并按照升序排序缴啡，默認(rèn)為升序, order by 要放在所有數(shù)據(jù)都處理完畢 再將數(shù)據(jù)排序顯示
# (5) is 或 is not
因?yàn)閚ull是一個(gè)特殊的值不能使用比較運(yùn)算符操作
select * from 表名 where username is null; # 查詢(xún)username為空的記錄
# (6) limit取值
limit x,y 從索引x的位置取出y條數(shù)據(jù)
limit y 從索引0開(kāi)始取出y條數(shù)據(jù)
select * from 表名 order by age desc limit 0,2; # 從0開(kāi)始取出兩條
select * from 表名 where age between 112 and 255 and username is not null order by id desc limit 1; # 哈哈這個(gè)語(yǔ)句長(zhǎng)不長(zhǎng)？ 取年齡在112-255之間且name不為空的數(shù)據(jù)按照id排序取第一條
# (7) MySQL聚合函數(shù)
count(字段名) # 統(tǒng)計(jì)記錄的個(gè)數(shù)
select count(id) from 表名; # 統(tǒng)計(jì)記錄條數(shù)
select count(*) from 表名; # 同上
max(字段名) # 最大值
select max(id) from 表名;
min(字段名) # 最小值
sum(字段名) # 求和
avg(字段名) # 平均值
select count(*) as con, max(age) as mage, min(age) as mmin, sum(age) as sumage,avg(age) as avgage from 表名; # 求age的最大值瓷们，最小值等并起別名
# (8) group by 分組
select classid, count(*) as con from 表名 group by classid; # 按照班級(jí)id進(jìn)行分組并統(tǒng)計(jì)沒(méi)班的人數(shù)
select sex,count(*) as con from 表名 group by sex;
select sex,count(*) as con from 表名 group by sex order by con desc; # 按照性別分組业栅，統(tǒng)計(jì)男女的人數(shù)，并按降序顯示
select classid,sex,count(*) from 表名 group by classid,sex; # 按照班級(jí)和性別排序
# having 條件
select classid,sex,count(*) as con from 表名 group by classid,sex having con > 5; # 按照班級(jí)和性別分組并顯示記錄大于5的
select classid,sex,count(*) as con from 表名 group by classid,sex having con>1 and sex='w'; # 查詢(xún) 人數(shù)>1且性別為w
select classid,sex,count(*) as con from 表名 group by classid,sex having classid in ('j140');
# (9) 模糊查詢(xún)
1. "%value%" 值包含就顯示
select * from 表名 where username like '%三%'; # 查看所有的用戶(hù)名中包含 三 的記錄
2. "value%" 以value值作為開(kāi)頭的數(shù)據(jù)
select * from 表名 where username like '三%';
3. '%value' 以value值作為結(jié)尾的數(shù)據(jù)
select * from 表名 where username like '%張' and age>72 order by age desc limit 2;
# (10) distinct 去除重復(fù)數(shù)據(jù)
select distinct age from 表名;
3.delete 刪除
語(yǔ)句： delete from 表名 [where 條件]
# 刪除所有記錄
delete from 表名谬晕；
# 帶條件的刪除
delete from 表名 where id > 5; # 刪除id大于5的記錄
4.update 修改
語(yǔ)句： update 表名 set 字段名=值[,字段名=值,...][where 條件]
# 修改所有記錄
update 表名 set age = 100碘裕；
# 帶條件的修改
update 表名 set age = 100 where id > 5; # 修改id大于5的記錄
十一. 其他的補(bǔ)充內(nèi)容
1. MySQL的密碼的修改
命令: set password for 用戶(hù)名@localhost = password('新密碼')
2. 對(duì)用戶(hù)的操作
(1) 選擇mysql數(shù)據(jù)庫(kù)
use mysql;
(2) 查看當(dāng)前數(shù)據(jù)庫(kù)中都有哪些用戶(hù)
select user from user;
(3) 創(chuàng)建用戶(hù)
create user 用戶(hù)名 indentified by '密碼';
(4) 分配權(quán)限
grant all on 庫(kù)名.表名 to 用戶(hù)名;
其中的all可以修改為(select, update, insert,delete)其中一個(gè),
表名可以修改為*代表所有表；
(5) 回收權(quán)限
revoke all on 庫(kù)名.表名 from 用戶(hù)名;
(6) 刪除用戶(hù)
drop user 用戶(hù)名;
(7) 刷新服務(wù)
flush privileges;
