一千行MySQL学习笔记0629144852.docx

一千行MySQL学习笔记0629144852.docx

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

一千行MySQL学习笔记0629144852

千行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]数据库名数据库选项

创建数据库

最简单的方式

CREATEDATABASEmy_db;

或者是

CREATEDATABASEIFNOTEXISTSmy_db;

创建utf8编码的数据库

CREATEDATABASEIFNOTEXISTSmy_dbdefaultcharactersetutf8COLLATEutf8_general_ci;

那么在这个数据库下创建的所有数据表的默认字符集都会是utf8了，注意后面这句话"COLLATEutf8_general_ci",大

致意思是在排序时根据utf8变码格式来排序

查看数据库列表

showdatabases;

使用数据库

usemy_db;

查看数据库默认编码

showvariableslike'character_set_%'

|Variable_name

++--

|Value

|

+

|character_set_client

|utf8

|

|character_set_connection|utf8

|

|character_set_database|latin1

|

|character_set_filesystem|binary

|

|character_set_results

|utf8

|

|character_set_server

|latin1

|

|character_set_system

|utf8

|

|character_sets_dir

|/usr/local/mysql-

5.5.28-linux2.6-i686/share/charsets/

+

+

+

修改数据库编码

alterdatabasemy_dbcharactersetlatin1;

删除数据库

dropdatabasemy_db;

或者是

dropdatabaseIFEXISTSmy_db;

数据库选项：

CHARACTERSETcharset_name

COLLATEcollation_name

--查看已有库

showdatabases[like'pattern']

--查看当前库信息

showcreatedatabase数据库名

--修改库的选项信息

alterdatabase库名选项信息

--删除库

dropdatabase[ifexists]数据库名

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

/*表的操作*/

--创建表

create[temporary]table[ifnotexists][库名.]表名（表的结构定义）[表选项]

每个字段必须有数据类型

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

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

对于字段的定义：

字段名数据类型[NOTNULL|NULL][DEFAULTdefault_value]

[AUTO」NCREMENT][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表名操作名

--操作名

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

ADD

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

ADD

字段名（所有原有属性也需写上）

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

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

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字节-8388608~8388607

int4字节

bigint8字节

int（M）M表示总位数

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

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

例：

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

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

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

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

常用tinyint

（1）表示布尔型。

double（双精度）

8字节

--b.浮点型

浮点型既支持符号位

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可以很方便的将时间戳进行格式化。

datetime

8字节

日期及时间

1000-01-0100:

00:

00到9999-12-3123:

59:

59

date

3字节

日期

1000-01-01到9999-12-31

timestamp

4字节

时间戳

19700101000000到2038-01-1903:

14:

07

time

3字节

时间

-838:

59:

59到838:

59:

59

year

1字节

年份

1901-2155

datetime“

YYYY-MM

-DDhh:

mm:

ss”

timestamp

“YY-MM-

DDhh:

mm:

ss”

“YYYYMMDDhhmmss”

“YYMMDDhhmmss”YYYYMMDDhhmmssYYMMDDhhmmssdate“YYYY-MM-DD”

“YY-MM-DD”

“YYYYMMDD”

“YYMMDD”

YYYYMMDD

time

year

hh:

mm:

ss”

hhmmss”

hhmmss

YYYY”

YYMMDD

YY”

YYYY

YY

4.枚举和集合

--枚举（enum）

enum（val1,val2,val3...）在已知的值中进行单选。

最大数量为65535.

1开始

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

每个枚举值，按保存的位置顺序，从逐一递增。

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

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」d）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*