一千行MySQL学习笔记.docx

一千行MySQL学习笔记.docx

《一千行MySQL学习笔记.docx》由会员分享，可在线阅读，更多相关《一千行MySQL学习笔记.docx（42页珍藏版）》请在冰豆网上搜索。

一千行MySQL学习笔记

/*启动MySQL*/

netstartmysql

/*连接与断开服务器*/

mysql-h地址-P端口-u用户名-p密码

/*跳过权限验证登录MySQL*/

mysqld--skip-grant-tables

--修改root密码

密码加密函数password（）

updatemysql、usersetpassword=password（'root'）;

SHOWPROCESSLIST--显示哪些线程正在运行

SHOWVARIABLES--

/*数据库操作*/------------------

--查瞧当前数据库

selectdatabase（）;

--显示当前时间、用户名、数据库版本

selectnow（）,user（）,version（）;

--创建库

createdatabase[ifnotexists]数据库名数据库选项

数据库选项：

CHARACTERSETcharset_name

COLLATEcollation_name

--查瞧已有库

showdatabases[like'pattern']

--查瞧当前库信息

showcreatedatabase数据库名

--修改库得选项信息

alterdatabase库名选项信息

--删除库

dropdatabase[ifexists]数据库名

同时删除该数据库相关得目录及其目录内容

/*表得操作*/------------------

--创建表

create[temporary]table[ifnotexists][库名、]表名（表得结构定义）[表选项]飲匮賒鏃賺黾讀。

每个字段必须有数据类型

最后一个字段后不能有逗号

temporary临时表，会话结束时表自动消失

对于字段得定义：

字段名数据类型[NOTNULL|NULL][DEFAULTdefault_value][AUTO_INCREMENT][UNIQUE[KEY]|[PRIMARY]KEY][COMMENT'string']驥頒苏呐現贸迩。

--表选项

--字符集

CHARSET=charset_name

如果表没有设定，则使用数据库字符集

--存储引擎

ENGINE=engine_name

表在管理数据时采用得不同得数据结构，结构不同会导致处理方式、提供得特性操作等不同

常见得引擎：

InnoDBMyISAMMemory/HeapBDBMergeExampleCSVMaxDBArchive羨筝辐匀棗窪淒。

不同得引擎在保存表得结构与数据时采用不同得方式

MyISAM表文件含义：

、frm表定义，、MYD表数据，、MYI表索引

InnoDB表文件含义：

、frm表定义，表空间数据与日志文件

SHOWENGINES--显示存储引擎得状态信息

SHOWENGINE引擎名{LOGS|STATUS}--显示存储引擎得日志或状态信息

--数据文件目录

DATADIRECTORY='目录'

--索引文件目录

INDEXDIRECTORY='目录'

--表注释

COMMENT='string'

--分区选项

PARTITIONBY、、、（详细见手册）

--查瞧所有表

SHOWTABLES[LIKE'pattern']

SHOWTABLESFROM表名

--查瞧表机构

SHOWCREATETABLE表名（信息更详细）

DESC表名/DESCRIBE表名/EXPLAIN表名/SHOWCOLUMNSFROM表名[LIKE'PATTERN']軸蝕賓赋轍践癉。

SHOWTABLESTATUS[FROMdb_name][LIKE'pattern']

--修改表

--修改表本身得选项

ALTERTABLE表名表得选项

EG:

ALTERTABLE表名ENGINE=MYISAM;

--对表进行重命名

RENAMETABLE原表名TO新表名

RENAMETABLE原表名TO库名、表名（可将表移动到另一个数据库）

--RENAME可以交换两个表名

--修改表得字段机构

ALTERTABLE表名操作名

--操作名

ADD[COLUMN]字段名--增加字段

AFTER字段名--表示增加在该字段名后面

FIRST--表示增加在第一个

ADDPRIMARYKEY（字段名）--创建主键

ADDUNIQUE[索引名]（字段名）--创建唯一索引

ADDINDEX[索引名]（字段名）--创建普通索引

ADD

DROP[COLUMN]字段名--删除字段

MODIFY[COLUMN]字段名字段属性--支持对字段属性进行修改，不能修改字段名（所有原有属性也需写上）阗废挚没薌摻餾。

CHANGE[COLUMN]原字段名新字段名字段属性--支持对字段名修改

DROPPRIMARYKEY--删除主键（删除主键前需删除其AUTO_INCREMENT属性）绑缯鑌臏苎鵂燾。

DROPINDEX索引名--删除索引

DROPFOREIGNKEY外键--删除外键

--删除表

DROPTABLE[IFEXISTS]表名、、、

--清空表数据

TRUNCATE[TABLE]表名

--复制表结构

CREATETABLE表名LIKE要复制得表名

--复制表结构与数据

CREATETABLE表名[AS]SELECT*FROM要复制得表名

--检查表就是否有错误

CHECKTABLEtbl_name[,tbl_name]、、、[option]、、、

--优化表

OPTIMIZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name]、、、闔錆宝潆鰱殡縱。

--修复表

REPAIR[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name]、、、[QUICK][EXTENDED][USE_FRM]堊绶镂薌誠舊掸。

--分析表

ANALYZE[LOCAL|NO_WRITE_TO_BINLOG]TABLEtbl_name[,tbl_name]、、、鲭損鲕鐔仓觎侠。

/*数据操作*/------------------

--增

INSERT[INTO]表名[（字段列表）]VALUES（值列表）[,（值列表）,、、、]樅矿罰驢揽绁間。

--如果要插入得值列表包含所有字段并且顺序一致，则可以省略字段列表。

--可同时插入多条数据记录！

REPLACE与INSERT完全一样，可互换。

INSERT[INTO]表名SET字段名=值[,字段名=值,、、、]

--查

SELECT字段列表FROM表名[其她子句]

--可来自多个表得多个字段

--其她子句可以不使用

--字段列表可以用*代替，表示所有字段

--删

DELETEFROM表名[删除条件子句]

没有条件子句，则会删除全部

--改

UPDATE表名SET字段名=新值[,字段名=新值][更新条件]

/*字符集编码*/------------------

--MySQL、数据库、表、字段均可设置编码

--数据编码与客户端编码不需一致

SHOWVARIABLESLIKE'character_set_%'--查瞧所有字符集编码项脅俩啬滢悅亩鹑。

character_set_client客户端向服务器发送数据时使用得编码

character_set_results服务器端将结果返回给客户端所使用得编码

character_set_connection连接层编码

SET变量名=变量值

setcharacter_set_client=gbk;

setcharacter_set_results=gbk;

setcharacter_set_connection=gbk;

SETNAMESGBK;--相当于完成以上三个设置

--校对集

校对集用以排序

SHOWCHARACTERSET[LIKE'pattern']/SHOWCHARSET[LIKE'pattern']查瞧所有字符集顽铰癞巹锱瓔軍。

SHOWCOLLATION[LIKE'pattern']查瞧所有校对集

charset字符集编码设置字符集编码

collate校对集编码设置校对集编码

/*数据类型（列类型）*/------------------

1、数值类型

--a、整型----------

类型字节范围（有符号位）

tinyint1字节-128~127无符号位：

0~255稈赊謗吓轟镄蹣。

smallint2字节-32768~32767

mediumint3字节-~

int4字节

bigint8字节

int（M）M表示总位数

-默认存在符号位，unsigned属性修改

-显示宽度，如果某个数不够定义字段时设置得位数，则前面以0补填，zerofill属性修改

例：

int（5）插入一个数'123'，补填后为'00123'

-在满足要求得情况下，越小越好。

-1表示bool值真，0表示bool值假。

MySQL没有布尔类型，通过整型0与1表示。

常用tinyint

（1）表示布尔型。

锓劉荦囀韉紓胜。

--b、浮点型----------

类型字节范围

float（单精度）4字节

double（双精度）8字节

浮点型既支持符号位unsigned属性，也支持显示宽度zerofill属性。

不同于整型，前后均会补填0、

定义浮点型时，需指定总位数与小数位数。

float（M,D）double（M,D）

M表示总位数，D表示小数位数。

M与D得大小会决定浮点数得范围。

不同于整型得固定范围。

M既表示总位数（不包括小数点与正负号），也表示显示宽度（所有显示符号均包括）。

支持科学计数法表示。

浮点数表示近似值。

--c、定点数----------

decimal--可变长度

decimal（M,D）M也表示总位数，D表示小数位数。

保存一个精确得数值，不会发生数据得改变，不同于浮点数得四舍五入。

将浮点数转换为字符串来保存，每9位数字保存为4个字节。

2、字符串类型

--a、char,varchar----------

char定长字符串，速度快，但浪费空间

varchar变长字符串，速度慢，但节省空间

M表示能存储得最大长度，此长度就是字符数，非字节数。

不同得编码，所占用得空间不同。

char,最多255个字符，与编码无关。

varchar,最多65535字符，与编码有关。

一条有效记录最大不能超过65535个字节。

utf8最大为21844个字符，gbk最大为32766个字符，latin1最大为65532个字符鳅雙誚镭閑骤敗。

varchar就是变长得，需要利用存储空间保存varchar得长度，如果数据小于255个字节，则采用一个字节来保存长度，反之需要两个字节来保存。

歷鴆摶绶鈔叽壯。

varchar得最大有效长度由最大行大小与使用得字符集确定。

最大有效长度就是65532字节，因为在varchar存字符串时，第一个字节就是空得，不存在任何数据，然后还需两个字节来存放字符串得长度，所以有效长度就是64432-1-2=65532字节。

詰杩艦賣谜統专。

例：

若一个表定义为CREATETABLEtb（c1int,c2char（30）,c3varchar（N））charset=utf8;问N得最大值就是多少？

答：

（65535-1-2-4-30*3）/3郸欏傥掄縲輯蛻。

--b、blob,text----------

blob二进制字符串（字节字符串）

tinyblob,blob,mediumblob,longblob

text非二进制字符串（字符字符串）

tinytext,text,mediumtext,longtext

text在定义时，不需要定义长度，也不会计算总长度。

text类型在定义时，不可给default值

--c、binary,varbinary----------

类似于char与varchar，用于保存二进制字符串，也就就是保存字节字符串而非字符字符串。

char,varchar,text对应binary,varbinary,blob、

3、日期时间类型

一般用整型保存时间戳，因为PHP可以很方便得将时间戳进行格式化。

datetime8字节日期及时间1000-01-0100:

00:

00到9999-12-3123:

59:

59镏籜殚沪撓议问。

date3字节日期1000-01-01到9999-12-31哗盗碜组嚦鏷釤。

timestamp4字节时间戳197到2038-01-1903:

14:

07陕鋪蒉擬吳鲨馒。

time3字节时间-838:

59:

59到838:

59:

59銼窥轾栈琺瑣滤。

year1字节年份1901-2155

datetime“YYYY-MM-DDhh:

mm:

ss”

timestamp“YY-MM-DDhh:

mm:

ss”

“YYYYMMDDhhmmss”

“YYMMDDhhmmss”

YYYYMMDDhhmmss

YYMMDDhhmmss

date“YYYY-MM-DD”

“YY-MM-DD”

“YYYYMMDD”

“YYMMDD”

YYYYMMDD

YYMMDD

time“hh:

mm:

ss”

“hhmmss”

hhmmss

year“YYYY”

“YY”

YYYY

YY

4、枚举与集合

--枚举（enum）----------

enum（val1,val2,val3、、、）

在已知得值中进行单选。

最大数量为65535、

枚举值在保存时，以2个字节得整型（smallint）保存。

每个枚举值，按保存得位置顺序，从1开始逐一递增。

鯔痒順绍茕傳骁。

表现为字符串类型，存储却就是整型。

NULL值得索引就是NULL。

空字符串错误值得索引值就是0。

--集合（set）----------

set（val1,val2,val3、、、）

createtabletab（genderset（'男','女','无'））;

insertintotabvalues（'男,女'）;

最多可以有64个不同得成员。

以bigint存储，共8个字节。

采取位运算得形式。

当创建表时，SET成员值得尾部空格将自动被删除。

/*选择类型*/

--PHP角度

1、功能满足

2、存储空间尽量小，处理效率更高

3、考虑兼容问题

--IP存储----------

1、只需存储，可用字符串

2、如果需计算，查找等，可存储为4个字节得无符号int，即unsigned

1）PHP函数转换

ip2long可转换为整型，但会出现携带符号问题。

需格式化为无符号得整型。

利用sprintf函数格式化字符串

sprintf（"%u",ip2long（'192、168、3、134'））;

然后用long2ip将整型转回IP字符串

2）MySQL函数转换（无符号整型，UNSIGNED）

INET_ATON（'127、0、0、1'）将IP转为整型

INET_NTOA（2130706433）将整型转为IP

/*列属性（列约束）*/------------------

1、主键

-能唯一标识记录得字段，可以作为主键。

-一个表只能有一个主键。

-主键具有唯一性。

-声明字段时，用primarykey标识。

也可以在字段列表之后声明

例：

createtabletab（idint,stuvarchar（10）,primarykey（id））;鶩剮踬問憮哟誘。

-主键字段得值不能为null。

-主键可以由多个字段共同组成。

此时需要在字段列表后声明得方法。

例：

createtabletab（idint,stuvarchar（10）,ageint,primarykey（stu,age））;埘邐鶼犢瑶调撫。

2、unique唯一索引（唯一约束）

使得某字段得值也不能重复。

3、null约束

null不就是数据类型，就是列得一个属性。

表示当前列就是否可以为null，表示什么都没有。

null,允许为空。

默认。

notnull,不允许为空。

insertintotabvalues（null,'val'）;

--此时表示将第一个字段得值设为null,取决于该字段就是否允许为null

4、default默认值属性

当前字段得默认值。

insertintotabvalues（default,'val'）;--此时表示强制使用默认值。

騶椁綰馭啸軾龆。

createtabletab（add_timetimestampdefaultcurrent_timestamp）;潇鄺鈄懶终鳃脏。

--表示将当前时间得时间戳设为默认值。

current_date,current_time

5、auto_increment自动增长约束

自动增长必须为索引（主键或unique）

只能存在一个字段为自动增长。

默认为1开始自动增长。

可以通过表属性auto_increment=x进行设置，或altertabletblauto_increment=x;鈉轟礎頤殘锈尋。

6、comment注释

例：

createtabletab（idint）comment'注释内容';

7、foreignkey外键约束

用于限制主表与从表数据完整性。

altertablet1addconstraint`t1_t2_fk`foreignkey（t1_id）referencest2（id）;艷腎漬沤驾毙氽。

--将表t1得t1_id外键关联到表t2得id字段。

--每个外键都有一个名字，可以通过constraint指定

存在外键得表，称之为从表（子表），外键指向得表，称之为主表（父表）。

作用：

保持数据一致性，完整性，主要目得就是控制存储在外键表（从表）中得数据。

MySQL中，可以对InnoDB引擎使用外键约束：

语法：

foreignkey（外键字段）references主表名（关联字段）[主表记录删除时得动作][主表记录更新时得动作]餡赜譎怀觉机鲑。

此时需要检测一个从表得外键需要约束为主表得已存在得值。

外键在没有关联得情况下，可以设置为null、前提就是该外键列，没有notnull。

辞尋韞猫蓽词韵。

可以不指定主表记录更改或更新时得动作，那么此时主表得操作被拒绝。

如果指定了onupdate或ondelete：

在删除或更新时，有如下几个操作可以选择：

1、cascade，级联操作。

主表数据被更新（主键值更新），从表也被更新（外键值更新）。

主表记录被删除，从表相关记录也被删除。

闾综玀证孌毵聲。

2、setnull，设置为null。

主表数据被更新（主键值更新），从表得外键被设置为null。

主表记录被删除，从表相关记录外键被设置成null。

但注意，要求该外键列，没有notnull属性约束。

躦绨針蠟泺嘔轢。

3、restrict，拒绝父表删除与更新。

注意，外键只被InnoDB存储引擎所支持。

其她引擎就是不支持得。

/*建表规范*/------------------

--NormalFormat,NF

-每个表保存一个实体信息

-每个具有一个ID字段作为主键

-ID主键+原子表

--1NF,第一范式

字段不能再分，就满足第一范式。

--2NF,第二范式

满足第一范式得前提下，不能出现部分依赖。

消除符合主键就可以避免部分依赖。

增加单列关键字。

--3NF,第三范式

满足第二范式得前提下，不能出现传递依赖。

某个字段依赖于主键，而有其她字段依赖于该字段。

这就就是传递依赖。

将一个实体信息得数据放在一个表内实现。

/*select*/------------------

select[all|distinct]select_exprfrom->where->groupby[合计函数]->having->orderby->limit发烧诛鸥鳏濟肾。

a、select_expr

--可以用*表示所有字段。

select*fromtb;

--可以使用表达式（计算公式、函数调用、字段也就是个表达式）

selectstu,29+25,now（）fromtb;

--可以为每个列使用别名。

适用于简化列标识，避免多个列标识符重复。

-使用as关键字，也可省略as、

selectstu+10asadd10fromtb;

b、from子句

用于标识查询来源。

--可以为表起别名。

使用as关键字。

select*fromtb1astt,tb2asbb;

--from子句后，可以同时出现多个表。

--多个表会横向叠加到一起，而数据会形成一个笛卡尔积。

select*fromtb1,tb2;

c、where子句

--从from获得得数据源中进行筛选。

--整型1表示真，0表示假。

--表达式由运算符与运算数组成。

--运算数：

变量（字段）、值、函数返回值

--运算符：

=,<=>,<>,!

=,<=,<,>=,>,!

&&,||,

in（not）null,（not）like,（not）in,（not）betweenand,is（not）,