MySQL筆記（第一天）

數(shù)據(jù)庫是用來存放數(shù)據(jù)的倉庫

關(guān)系模型：記錄和記錄之間通過屬性之間的關(guān)系來進(jìn)行連接琅摩，保證數(shù)據(jù)獨(dú)立性，并形成數(shù)據(jù)集之間的關(guān)系因宇。

關(guān)系模型的特點(diǎn)：
每個(gè)表都是獨(dú)立的刹淌；通過關(guān)系字段將兩個(gè)表連接起來；關(guān)系：兩個(gè)表的公共字段芹橡。關(guān)系型數(shù)據(jù)庫中多個(gè)表聯(lián)合查詢效率低下毒坛。

為了解決關(guān)系型數(shù)據(jù)庫多表查詢效率的問題，項(xiàng)目中使用了NoSQL（非關(guān)系型數(shù)據(jù)庫林说，Redis煎殷、mongodb等等），在數(shù)據(jù)庫中按照鍵值對來存儲腿箩，它是關(guān)系型數(shù)據(jù)庫的補(bǔ)充豪直。

非關(guān)系型數(shù)據(jù)庫主要就是為了查詢用的。存儲還是要用關(guān)系型數(shù)據(jù)庫珠移。也就是一個(gè)項(xiàng)目中要用到兩個(gè)數(shù)據(jù)庫弓乙。

數(shù)據(jù)庫中存放的是表，表中存放的是數(shù)據(jù)钧惧。

Structured Query Language（結(jié)構(gòu)化查詢語言）暇韧，是用來操作關(guān)系型數(shù)據(jù)庫的一門語言。這是一個(gè)關(guān)系型數(shù)據(jù)庫的通用操作語言浓瞪，也稱為標(biāo)準(zhǔn)SQL懈玻，也叫SQL-92。

常用的關(guān)系型數(shù)據(jù)庫
關(guān)系型數(shù)據(jù)庫 開發(fā)公司 使用語言
access 微軟 SQL 辦公室使用的一種小型數(shù)據(jù)庫
SQL Server 微軟 T-SQL
Oracle 甲骨文 PL/SQL
MySQL 被甲骨文收購 MySQL

思考：已知標(biāo)準(zhǔn)SQL可以在所有的關(guān)系型數(shù)據(jù)庫上運(yùn)行乾颁，在Oracle上編寫的PL/SQL能否在MySQL上運(yùn)行涂乌？
答：不一定艺栈，只能運(yùn)行標(biāo)準(zhǔn)SQL。擴(kuò)展的東西不能運(yùn)行湾盒。

安裝PHPStudy湿右，數(shù)據(jù)庫就自動安裝好了，就可以使用了罚勾。

第一步：連接服務(wù)器诅需。

數(shù)據(jù)庫是CS模式的軟件，所以要連接數(shù)據(jù)庫必須要有客戶端軟件荧库。

MySQL數(shù)據(jù)庫默認(rèn)端口號是3306。localhost=127.0.0.1 本地

第一類：通過windows界面連接服務(wù)器

Navicat_for_MySQl MySQL-Front

第二類：通過web窗體連接服務(wù)器

phpMyAdmin(只要有瀏覽器就可以訪問數(shù)據(jù)庫)

第三類：通過命令行連接服務(wù)器

host -h 主機(jī)
port -P 端口號
user -u 用戶名
password -p 密碼

連接數(shù)據(jù)庫

mysql -h127.0.0.1(/localhost) -P3306 -uroot -proot (明文)

如果連接本地?cái)?shù)據(jù)庫赵刑，-h可以省略分衫，如果服務(wù)器端口號是3306，-P也可以省略般此。

mysql -uroot -proot (明文)

密文:

mysql -uroot -p => Enter => 輸入密碼即為密文

退出：exit/quit/\q

數(shù)據(jù)庫蚪战、表相關(guān)

數(shù)據(jù)庫：數(shù)據(jù)庫中存放的是表，一個(gè)數(shù)據(jù)庫中可以存放多個(gè)表
表：表示用來存放數(shù)據(jù)的
關(guān)系：兩個(gè)表的公共字段
行：也稱記錄铐懊，也稱實(shí)體
列：也稱字段邀桑，也稱屬性

就表結(jié)構(gòu)而言，表分為行和列科乎；就表數(shù)據(jù)而言壁畸，表分為記錄和字段；就面向?qū)ο蠖悦┟粋€(gè)記錄就是一個(gè)實(shí)體捏萍，一個(gè)字段就是一個(gè)屬性。

數(shù)據(jù)相關(guān)

數(shù)據(jù)冗余：相同的數(shù)據(jù)存儲在不同的地方（冗余只能減少空闲，不能杜絕）
數(shù)據(jù)完整性：正確性+準(zhǔn)確性=數(shù)據(jù)完整性
正確性：數(shù)據(jù)類型正確
準(zhǔn)確性：數(shù)據(jù)范圍要準(zhǔn)確

解決數(shù)據(jù)冗余問題：分表（用關(guān)系轉(zhuǎn)化成多個(gè)表） => 冗余越少令杈，表越多，查詢速度越慢

數(shù)據(jù)庫的操作

連接數(shù)據(jù)庫：mysql -uroot -proot

顯示所有數(shù)據(jù)庫：show databases;

創(chuàng)建數(shù)據(jù)庫：create database [if not exists] 數(shù)據(jù)庫名 [選項(xiàng)];

如果是特殊字符或關(guān)鍵字做數(shù)據(jù)庫名怎么辦碴倾？使用反引號將數(shù)據(jù)庫名引起來逗噩。
由此引申出：不管你起的數(shù)據(jù)庫名是不是關(guān)鍵字或特殊字符，統(tǒng)統(tǒng)加上反引號跌榔，絕對出不了錯异雁。

創(chuàng)建數(shù)據(jù)庫時(shí)指定存儲的字符編碼：create database student charset=gbk;
如果不指定，安裝數(shù)據(jù)庫的時(shí)候使用的是什么編碼僧须，就是什么編碼片迅。
查看安裝數(shù)據(jù)庫時(shí)使用的是什么編碼的指令：show variables like 'character_set_%';

數(shù)據(jù)庫保存的路徑在安裝MySQL的時(shí)候就配置好。
也可以在my.ini配置文件中更改數(shù)據(jù)庫的保存地址皆辽。(datadir="")
一個(gè)數(shù)據(jù)庫就對應(yīng)一個(gè)文件夾柑蛇，在文件夾中有一個(gè)db.opt文件芥挣，在此文件中設(shè)置數(shù)據(jù)庫的字符集和校對集。

如果要創(chuàng)建的數(shù)據(jù)庫已存在耻台，就會報(bào)錯空免。
解決方法：創(chuàng)建數(shù)據(jù)庫的時(shí)候判斷一下數(shù)據(jù)庫是否存在，如果不存在再創(chuàng)建盆耽。
如果數(shù)據(jù)庫名是關(guān)鍵字和特殊字符要報(bào)錯蹋砚。
解決方法：在特殊字符、關(guān)鍵字上加上反引號摄杂。
創(chuàng)建數(shù)據(jù)庫的時(shí)候可以指定字符編碼坝咐。
創(chuàng)建數(shù)據(jù)庫如果不指定字符編碼，默認(rèn)和MySQL服務(wù)器的字符編碼是一致的析恢。

數(shù)據(jù)庫創(chuàng)建后墨坚，只默認(rèn)帶有information_schema、mysql映挂、performance_schema泽篮、test/sys四個(gè)數(shù)據(jù)庫。

刪除數(shù)據(jù)庫：drop databaase [if exists] 數(shù)據(jù)庫名;

顯示創(chuàng)建數(shù)據(jù)庫的語句：show create database 數(shù)據(jù)庫名;

修改數(shù)據(jù)庫：alter database 數(shù)據(jù)庫名 charset=字符編碼;
只能修改數(shù)據(jù)庫的字符編碼柑船。（注意：數(shù)據(jù)庫里的utf字符編碼沒有“-”）

選擇數(shù)據(jù)庫：use 數(shù)據(jù)庫名;

表的操作

顯示所有表：show tables;

創(chuàng)建表：create table [if not exists] 表名(
字段名 數(shù)據(jù)類型 [null|not null] [default] [auto_increment] [primary key] [comment],
字段名 數(shù)據(jù)類型 ...
)[engine=存儲引擎] [charset=字符集];

null|not null 是否為空
default 默認(rèn)值(此處如果要輸入中文字符帽撑，要在創(chuàng)建表之前先執(zhí)行mysql>set names gbk;)
auto_increment 自動增長，默認(rèn)從1開始鞍时，每次遞增1
primary key 主鍵亏拉，主鍵的值不能重復(fù)，不能為空逆巍，每個(gè)表必須只能有一個(gè)主鍵
comment 備注
engine 引擎 myisam专筷，innodb(引擎決定了數(shù)據(jù)的存儲方式和查詢方式)

若不指定引擎，默認(rèn)是innodb蒸苇。
若不指定字符集磷蛹，就默認(rèn)跟所在數(shù)據(jù)庫保持一致。

表名和字段名如果用了關(guān)鍵字溪烤，要用反引號引起來味咳。

數(shù)據(jù)表的文件

一個(gè)數(shù)據(jù)庫對應(yīng)一個(gè)文件夾
一個(gè)表對應(yīng)一個(gè)或多個(gè)文件
引擎是myisam，一個(gè)表對應(yīng)三個(gè)文件
.frm 存儲的是表結(jié)構(gòu)
.myd 存儲的是表數(shù)據(jù)
.myi 存儲的是表索引
引擎是innodb檬嘀，一個(gè)表對應(yīng)一個(gè)表結(jié)構(gòu)文件(.frm->framework架構(gòu):存儲的是表結(jié)構(gòu))
innodb的所有表的數(shù)據(jù)都保存在ibdata1文件中槽驶，如果數(shù)據(jù)量很大，會自動的創(chuàng)建ibdata2鸳兽，ibdata3...

推薦使用innodb掂铐。

myisam:查詢速度快，容易產(chǎn)生碎片，不能約束數(shù)據(jù)全陨。
innodb:以前沒有myisam查詢速度快爆班，現(xiàn)在已經(jīng)提速了(雖然還是沒有myisam快，但已經(jīng)很可以了)辱姨，不產(chǎn)生碎片柿菩，可以約束數(shù)據(jù)。

顯示創(chuàng)建表的語句：show create table 表名;
show create table 表名 \G;（豎著排）

查看表結(jié)構(gòu)：describe 表名;/desc 表名;

刪除表：drop table [if exists] 表1雨涛，表2枢舶，...;(注意：沒有drop *;的寫法)

復(fù)制表：語法一：create table 新表名 select 字段,字段,字段,... from 舊表名;
create table 新表名 select * from 舊表名;(復(fù)制所有的數(shù)據(jù))
特點(diǎn)：不能復(fù)制父表的鍵，能夠復(fù)制父表的數(shù)據(jù)
語法二：create table 新表名 like 舊表名;
特點(diǎn)：只能復(fù)制表結(jié)構(gòu)替久，不能復(fù)制表數(shù)據(jù)

所有字段可以用*表示凉泄。

修改表：

語法：alter table 表名;

添加字段：alter table 表名 add [column] 字段名 數(shù)據(jù)類型 [位置]
alter table stu add add varchar(20); 默認(rèn)放到最后的位置
alter table stu add gender char(1) after name; 放到name字段的后面(注意，沒有before)
alter table stu add score int first; (也沒有l(wèi)ast,second之類的)蚯根，只有兩個(gè)關(guān)鍵字after和first后众，足夠了。

刪除字段：alter table 表名 drop [column] 字段名
alter table stu drop gender;

修改字段(改名)：alter table 表名 change [column] 原字段名 新字段名 數(shù)據(jù)類型...
alter table stu change name stu_name varchar(20);

修改字段(不改名)：alter table 表名 modify 字段名 字段屬性...
alter table stu modify sex char(1);(主鍵被視為約束稼锅，不能用modify來修改)
alter table stu modify add varchar(20) default '地址不詳';

修改引擎：alter table 表名 engine=引擎名
alter table stu engine=innodb;

修改表名：alter table 表名 rename to 新表名
alter table student rename to stu;

將表移動到其他數(shù)據(jù)庫(這種情況很少實(shí)用)
alter table student rename to 另一個(gè)存在的數(shù)據(jù)庫名.新表名(表名可相同也可不同)

數(shù)據(jù)的操作(增刪改查)

插入數(shù)據(jù)
語法：insert into 表名 (字段名,字段名,...) values (值1,值2,...);
insert into student values (值1,值2,...);(插入所有字段，前面的字段名可以省略僚纷。若是省略前面的字段名矩距，則必須插入所有字段并且要跟表的字段順序保持一致)
insert into student (id,name) values (1,'tom');
(插入的字段和表的字段可以順序不一致，但是插入字段名和插入的值一定要一一對應(yīng)怖竭。)
insert into student values (1,'tom',12,'男');

注意：如果主鍵位數(shù)據(jù)已存在锥债，則不允許重復(fù)插入。比如id為主鍵痊臭，id=1的位置上已經(jīng)有了數(shù)據(jù)哮肚，則不可再往id=1的位置插入數(shù)據(jù)。

插入字段名的順序和數(shù)據(jù)表中字段名的順序可以不一致广匙；
插入值的個(gè)數(shù)允趟、順序必須和插入字段名的個(gè)數(shù)、順序要一致鸦致；
插入值的個(gè)數(shù)潮剪、順序和表中的字段個(gè)數(shù)、順序一致分唾，這時(shí)候插入字段可以省略抗碰；
自動增長列，可以直接插入null绽乔。

插入空值和默認(rèn)值
insert into stu values (5,'jack',null,default);
default關(guān)鍵字用來插入默認(rèn)值弧蝇，null用來插入空值。

插入多條數(shù)據(jù)
insert into stu values (6,'李白','男','唐朝'),(7,'杜甫','男','宋朝'),...;

更新數(shù)據(jù)
語法：update 表名 set 字段=值 [where 條件]
update student set sex='女' where id=4;
update student set score=90 where name='李白';
update student set sex='女',age=24,score=100 where id=1;
update student set sex='女';(這樣將影響到表中所有數(shù)據(jù)，意為將表中所有人的性別都改成女)

刪除數(shù)據(jù)
語法：delete from 表名 [where 條件] [order by 排序] [limit 限制]
delete from student where id=1;
delete from student where name='berry';
delete from student;(刪除表中所有數(shù)據(jù)=truncate table student)

只能逐條刪除看疗，不能加逗號同時(shí)刪除多條沙峻。

delete from 表名和truncate table 表名的區(qū)別？
delete from 表名：遍歷表記錄鹃觉，一條一條的刪除专酗；
truncate table 表名：將原表銷毀，再創(chuàng)建一個(gè)同結(jié)構(gòu)的新表盗扇，就清空表而言祷肯，這種方法效率高。

查詢數(shù)據(jù)
語法：select 列名,列名,列名,... from 表名;(select * from 表名;)
select id from student;
select id,name from student;
select * from student;

數(shù)據(jù)傳輸時(shí)使用字符集

發(fā)現(xiàn)：在插入數(shù)據(jù)的時(shí)候疗隶，如果有中文就會報(bào)錯（或者無法插入）

show variables like 'character_set_%';

Variable_name Value
character_set_client utf8 表示用utf8編碼接收客戶端的指令
character_set_database utf8 表示數(shù)據(jù)庫用utf8編碼存儲數(shù)據(jù)
character_set_results utf8 表示用utf8編碼返回結(jié)果

更改接收客戶端指令的編碼：set character_set_client=gbk;

原因：返回編碼是utf8佑笋，客戶端是gbk。

解決：set character_set_results=gbk;更改接收返回結(jié)果的編碼斑鼻。

也可直接使用一行代碼解決：set names gbk;/set names utf8;

設(shè)置什么編碼取決于客戶端的編碼蒋纬，通過set names 編碼值;設(shè)置編碼。

環(huán)境變量配置

我的電腦->右鍵->屬性->高級->環(huán)境變量->path下加上;D:\phpstudy_pro\Extensions\MySQL8.0.12\bin 設(shè)置完成之后坚弱，先關(guān)閉cmd客戶端蜀备，再重新打開，就可以了荒叶。

校對集(實(shí)際工作中對于排序的處理一般不會用這個(gè)碾阁，會有專門的處理方法)

概念：在某種字符集下，字符之間的比較關(guān)系些楣，比如a和B的大小關(guān)系脂凶，如果區(qū)分大小寫a>B，如果不區(qū)分大小寫則a<B愁茁。比如趙錢孫李大小關(guān)系蚕钦，不同的標(biāo)準(zhǔn)關(guān)系不一樣。
校對集依賴于字符集鹅很，不同的字符集的比較規(guī)則不一樣嘶居，如果字符集更改，校對集也要重新定義促煮。
不同校對集對同一字符序列比較的結(jié)果是不一致的食听。

可以在定義字符集的同時(shí)定義校對集
語法：collate=校對集;

校對集規(guī)則：
_bin: 按二進(jìn)制編碼比較，區(qū)分大小寫
_ci: 不區(qū)分大小寫

mysql> create table stu1(
-> name char(1)
-> )charset=utf8 collate=utf8_general_ci;

mysql> create table stu2(
-> name char(1)
-> )charset=utf8 collate=utf8_bin;

mysql> insert into stu1 values ('a'),('B');

mysql> insert into stu2 values ('a'),('B');

mysql> select * from stu1 order by name; => a B

mysql> select * from stu2 order by name; => B a