mysql数据库的笔记

增删改查置顶：

插入数据：

基本语法 ：

insert into [表名](字段名1，字段名2……) values(记录1),(记录2)；

insert into [表名] values(记录1),(记录2)；

eg:

 INSERT INTO student(id,name,grade)

 　　   VALUES(1,'zhangshan',98);

 更新数据：

基本语法 ：

update 表名 set 字段 = 值【where条件】;

高级语法：

update 表名 set 字段 = 值【where条件】[limit更新数量(限制更新数量)];

eg:

 UPDATE student 

 　　　　SET name=‘caocao’,grade=50

 　　　　WHERE id=1;

  删除数据：

delete from 表名 [where条件][limit数量]；

如果表中存在主键自增长，那么删除之后，自增长不会还原。

清空表（重置自增长）：truncate 表名。

eg:

 DELETE  FROM student 

 　　　WHERE id=7;

 查询数据：

select[select选项] 字段列表[字段别名]/*from 数据源 [where条件子句] [group by 子句][having 子句][order by 子句][limit 子句];

select选项：对查出来的结果的处理方式

ALL:默认的，保留所有的结果

Distinct：去重，查出来的结果，将重复给去除（所有字段都相同）

 eg:

SELECT id,name,grade ,gender

　　    FROM student2;

------------------------------------------------------------------

忘了就来看看

----------------------------------------------------------------------

基础操作部分：

mysql -hlocalhost -uroot -p
数据库操作：
show databases;
create database [表名];
show create database [表名];
alter database [表名] [字符集charset/效验集collate] [目的集];
drop database [表名];
show databases like ''；%_
crate database 20155101012fjx charset utf8 collate utf8_general_ci;

 

表操作：
create table [if no exists] [表名](

字段名 数据类型,
字段名 数据类型 ---最后一行不用逗号
)[表选项charset/……]
show tables ;
show tables like '';
show create table [表名] ;\g\G
desc/describe/show columns from [表名];
rename table [老表名] to [新表名];
alter table [表名] [表选项] (=) 值;
alter table [表名] add[column] 字段名 数据类型 [列属性][位置(first)];
alter table [表名] moditfy 字段名 数据类型 [属性][位置];
alter table [表名] change [旧字段] [新字段] 数据类型 [属性][位置]；
alter table [表名] drop [字段名];
drop table 表名1,表名2,表名3,表名……,

数据操作：
insert into [表名](字段名1，字段名2……) values(记录1),(记录2)；
insert into [表名] values(记录1),(记录2)；
select * from [表名] [while条件]；
select 字段1，字段2，字段3，字段4…… from [表名] where 条件;
update [表名] set 字段 = 值 [where条件];
delete from 表名 [where条件];
show character set;
show variables like 'character_set%'; --查看服务器默认的对外处理的字符集；
set character_set_client = gbk; --修改服务器认为的客户端数据的字符集为gbk;
set names 字符集; --快捷方式 set names gbk;===>character_client,character_connection,character_result;
show collation;
order by 字段名 [asc|desc] ;--asc降序，默认升序

--------------------------------------------------------------------------------------------------------------

数据类型区：
列类型：
1. 数值型：
整型数据：
Tinyint:1字节
Smallint:2
Mediumint:3
Int:4
Bigint:8

无符号：XXX unsigned
显示宽度：告诉用户可以显示的形式，用户可以控制显示宽度，但这种控制不会改变数据本身的大小。用途：通过zerofill(零填充)
alter table 表名 add 字段名 数据类型（宽度值） unsigned; alter table my_int int_6 tinyint(1) unsigned;
alter table my_int int_6 tinyint(2) zerofill; --不够两位用0填充，0填充导致数值自动变为无符号
小数型：
浮点型：小数点浮动，精度有限，而且丢失精度
定点型：小数点固定，精度固定，不会丢失精度

2. 浮点型：
Float:单精度，4字节，精度范围7位左右
double:双精度，8字节，15位左右
创建浮点数表：浮点的使用方式：直接float表示没有小数部分；float(M,D)：M表示总长度，代表小数部分长度，整数部分长度为M-D；
create table my_float(
f1 float,
f2 float(10,2),--10位在精度范围外
f3 float(6,2)--6位在精度范围内
)charset utf8;
超出数值的小数部分系统自动四舍五入，如果因为小数部分进位导致整数部分超出范围，则系统允许成立
定点型：
绝对的保证小树部分不会被四舍五入（不会丢失精度），小数部分有可能（理论上小数部分也不会丢失精度）decimal 变长，大致每9个数字用4个字节存储。整数和小数分开计算 M最大是65，D最大是30,默认是10，2（65个9）最大数据65个9
定点数的整数部分一定不能超出长度（仅为不可以）,小数部分任意超出（系统四舍五入）
show warnings;--查看警告

确定的精确数据用定点；数据过大，不精确用浮点

3. 时间日期类型：
Datetime:时间日期，格式YYYY-mm--dd HH:ii:ss，表示范围从1000到9999年，有0值：0000-00-00 00：00：00
Date:日期，datatime中的date部分
Time：时间（段），指定的某个区间之间的时间，
Timestamp：时间戳，并不是时间戳，只是重1970年开始的YYYY-mm-dd HH:ii:ss 格式与datetime完全一致
Year：年份，两种格式，year(2)和year(4)表示从1901年到2156

4. 字符串类型：
定长字符串：
char,varchar,text,blob,enum和set
Char(4):3*4=12字节
变长字符串：
Varchar：L表示字符长度，理论长度65536个字符，但是会多1到2个字节来确定存储的实际长度
Varchar（10）：utf8有10*3+1=31个字符多于255的情况下+2

实际上超过255个字符既不用定长也不用变长，用text文本来存
如何选择定长或者变长字符串？
定常的磁盘空间比较浪费，但是效率高；如果数据基本上长度都一样，用定长。
变长节省空间，效率低。数据不能确定长度。

5. 文本字符串：
如果数据量非常大，通常说超过255个字符就会使用文本字符串：text和blob。
Text:存储文字（二进制数据实际上都是存储路径）
blob:存储二进制数据（通常不用）。
枚举字符串：
枚举：事先将所有可能的记过设计好，实际上存储的数据必须是规定哈好的数据中的一个。
使用方式：
定义：enum（可能出现的元素列表）；如enum('男'，'女'，'保密')；
插入数据时，可以插入元素名，也可以插入对应的数字。
枚举，实际上存储的是枚举列表对应元素的数字号，创建枚举时，形成一个数字 与列表中元素对应的关系表，显示的是数字对应的元素，存储的是数字。
在mysql中，系统是自动转换数据格式的，而且基本与php一样（尤其是字符串转数字）
集合字符串：
集合存储的是数值，而不是字符串（集合是多选）
集合定义：Set(元素列表)
插入数据时，可以插入数值，也可以插入元素名
查看集合数据：（集合中每一个元素都是对应一个二进制位，0，表示不选中，1表示选中。最后将二进制序列反过来得到对应的数值即表示选中的集合中的元素）集合众元素的顺序没有关系，最终系统都会去匹配顺序。
set节省了空间和规范数据，但是增加了PHP的维护成本。
select 变量名+0，变量名 from 表名；

Mysql 记录长度
Mysql中规定：任何一条记录最长不能超过65535个字节。
varchar 的实际存储长度：
utf8下的varchar的实际顶配：21844个字符。
Gbk下的varchar的实际顶配：32766字符。
mysql记录中：如果有任何一个字段允许喂空，那么系统就会自动从整个记录中保留一个字节来存储NULL(想释放NUll所占用的字节：必须保持正所有的字段不允许为空not null)
mysql中text文本字符串，不占用记录长度，额外存储，但是text文本字符串也是属于记录的一部分：一定需要占据记录中的部分长度：10个字节。
空属性：
NULL,NOT NUll
实际开发中尽可能保证所有数据不应该为空：空数据没有意义；空数据无法参与运算

Comment(描述):
create table my_teachaer(
name varchar(20) not null comment'姓名'
)charset utf-8;
通过创建查看语句来查看描述（show create table 表名）；

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
主键：
一张表只能有一个字段可以使用对应的键，用来唯一的约束该字段里面的数据，不能重复，这种成为主键。
增加主键：
1.字段之后加primary key （主键本身不允许为空）（只能使用一个字段为主键）
2.创建表的时候，在所有字段之后，使用primary key(主键字段列表)（多个主键，复合主键）
3.创建表之后追加主键，可以直接追加，或者修改字段属性。
alter table 表名 add primary key(字段列表)前提：表中字段对应的数据本身是独立的（不重复）。
主键约束：
主键对应的数据不允许重复：一旦重复，数据操作失败。
删除主键：
alter table 表名 drop primary key;
逻辑主键；大多数时候使用逻辑主键
自增长： 通常与主键搭配
特点： 1.任何一个字段要做自增长前提本身必须是一个索引（key有值）
2.自增长字段必须是数字（整型）
3。一个表最多有一个自增长
自增长的字段如果输入了值，那么自增长失效，但是下一次还是能够正确的自增长（最大值+1）
可以通过查看创建语句查看下一次自增长的数值
自增长修改只能改为比已有数值大的值，不能小，变小的修改不生效alter table 表名 auto_increment = 值。
所有系统的变现（如字符集，校对集）都是由系统内部的变量进行控制的，查看自增长对应的变量：show variables like 'auto_increment'
set auto_increment = X;修改自增长的增长值
删除自增长：alter 表名 modify 字段 类型（有主键的时候不加主键）
唯一键：避免表中除了主键之外的字段出现数据重复：解决表中多个字段需要唯一性约束的问题。
唯一键默认的允许自动为空，而且可以多个为空（空字段不参与唯一性比较）

增加唯一键：
1.创建表的时候，字段之后跟unique/unique key
2.在所有的字段之后增加unique key(字段列表);复合唯一键
没有主键的表，创建不为空的唯一键时，唯一键显示为主键。
3。创建表之后追加唯一键。alter table 表名 add unique key(字段名)
唯一键与主的区别是：唯一键允许为空，而且多个为空。如果唯一键不允许为空，那么唯一键与主键的作用一致。
唯一键可以有多个，可以不删除。
删除唯一键：alter table 表名 drop index 索引名字；--唯一键默认使用字段名作为索引名字。

索引：
几乎所有的索引都是建立在字段之上。
索引：系统根据某种算法，将已有的数据（未来可能新增的数据），单独建立一个文件；文件能实现快速的匹配数据，并且能够快速的找到对应表的记录。
索引的意义：
1.提升查询数据的效率
2.约束数据的有效性（唯一性等）
增加索引的前提条件：索引本身会产生索引文件（可能比数据文件还大），非常小号磁盘空间。
如果某个字段作为查询的条件经常是好用，那么可以使用索引。
mysql体重多种索引：
1.主键索引：primary key
2.唯一索引：unique key
3.全文索引：fulltext index
4.普通索引：index
全文索引：按照文章内部的关键字进行索引，最大的问题是：如何确定关键字
英文很容易：英文单词与单词之间有空格
中文很难：没空格，中文单词的随意组合(分词：sphinx)

关系：
所有的关系都是表与表之间的关系。
一对一：一张表的一条记录只能与另一张表的一条记录对应；
一对多：一张表中有一条记录可以对应另外一张表中的多条记录；但是返回过，另一张表的一条记录只能对应第一张表的一条记录
多对多：增加一张表保存多对多表之间的关系；

范式：
1NF:不可拆分。
2NF:部分依赖。
3NF:传递依赖。逻辑主键
逆规范化：为了效率，放弃范式规范，使数据冗余

高级数据操作：
新增数据：
主键冲突（主键对应的值已经存在，插入失败）：
insert into 表名[(字段列表：包含主键)] values(值列表) on duplicate key update 字段 = 新值;
例如：Insert into my_class values('phadf') on duplicate key update room = 'phpdfasdfwe';
replace into 表名 values('值');
总体来说：插入失败时，先删除在插入的意思。
蠕虫复制：从已有的数据中获取数据，然后将数据有进行新增操作，数据城北的增加。
间接等于表创建的高级操作，从已有的表创建新表（复制表结构）:
create table 表名 like 数据库名，表明；
蠕虫复制：先查出数据，然后将查出的数据新增一遍。
insert into 表名（字段列表） select 字段列表 /* from 数据表名；（从别的表和自己表都可以复制）

更新数据：
基本语法 ：
update 表名 set 字段 = 值【where条件】;
高级语法：
update 表名 set 字段 = 值【where条件】[limit更新数量(限制更新数量)];

删除数据：
delete from 表名 [where条件][limit数量]；
如果表中存在主键自增长，那么删除之后，自增长不会还原。
清空表（重置自增长）：truncate 表名。
查询数据：
select[select选项] 字段列表[字段别名]/*from 数据源 [where条件子句] [group by 子句][having 子句][order by 子句][limit 子句];
select选项：对查出来的结果的处理方式
ALL:默认的，保留所有的结果
Distinct：去重，查出来的结果，将重复给去除（所有字段都相同）

字段别名：
字段别名：当数据进行查询出来的时候，有时候名字并一定就满足需求（多表查询的时候，会有同名字段）需要对字段名进行重命名：别名
语法：
字段名[as]别名;
数据源：
数据的来源：
select * from 表明1，表明2，……（从一张表中取出一条记录，去另外一张表中匹配所有记录，而且全部保留）形成笛卡儿积
子查询：
select /* from （select /* from 表名）as 表名；
where 子句：
where 子句：用来判断数据，筛选数据。返回结果0或者1.
判断条件：
比较运算符：>,<,<=,>=,!=,=,<>(不等于)，=，like,between,and,in/not in,
逻辑运算符：&&(and),||(or),!(not);
集合in（元素1，元素2，元素3……）；
where原理：where是唯一一个直接从磁盘获取数据的时候就开始判断的条件；从磁盘去除一条记录，开始进行where判断；判断的结果如果成立就保存到内存中；如果失败直接放弃；
between本身是闭区间，between左边的值必须小于或者等于右边的值
where 1满足所有条件，等于不加

Group by子句：分组的意思根据某个字段进行分组（相同的放一组，不同的分到不同的组）
基本语法：
group by 字段名[asc|desc]；--对分组后的结果合并之后排序
分组是为了统计
统计函数：
count():统计每组由多少个记录，参数有两种类型，*代表统计记录，字段名代表统计对应的字段（NULL不统计）
Max():统计最大值
Min():统计最小值
Avg():统计平均值
Sum(字段名):求和
多字段分组：先根据一个字段进行分组，然后对分组后的记过再次按照其他字段进行分组 group_concat(字段)：连接字段：
select c_id,sex,count(*),group_concat(name) from my_student group by c_id,sex;
回溯统计：with rollup;最后加一个统计记录总数等于总结之后向上级汇报
第二次回溯要看第一次分组的组数，组数多少，回溯就是这个数+1。
having 子句：
having 与where一样，进行条件判断
where针对磁盘数据进行判断，进入内存之后，会进行分组操作，分组结果不能用where操作。 having能做where不能做的。
分组统计的结果或者说统计函数都只有having能够使用。
having能够使用字段别名，where不能
order by子句：
order by:排序，根据某个字段进行升序或降序排序，以来校对集。
order by字段名[asc|desc];--asc升序（默认的）
select *from my_student order by c_id,sex desc;c_id升序排序，sex降序排序

Limit 子句：限制数量
方式1.限制长度（数量）：limit 数据量
2.限制起始位置，限制数量：limit 起始位置，长度;select *from my_student limit 0,2;
方式2.实现数据的分页，加速服务器响应效率，
limit offset,length;length :每页显示的数据量：基本不变；offset =（页码-1）*每页显示量

连接查询分类：
sql中奖链接查询分成四类：内连接，外连接，自然连接，交叉连接；
连接查询：join,使用方式 左表 join 右表
交叉连接：从一张表中循环去除每一条记录，每条记录都去另外一张表中进行匹配：匹配一定保留（没有条件匹配），二链接本身字段就会增加（保留），最终形成的记过叫做：笛卡儿积；
左表cross join 右表======》from 左表， 右表；（没有实际作用）
select *from 左表cross join 右表；--左表cross join 右表是数据源；
内连接：[inner] join，从左表中取出每一条记录，去由表中所有的记录进行匹配：匹配必须是某个条件在左表和右表中相同最终才会保留结果，否则不保留
左表[inner]join 右表 on 左表=右表.字段；on 表示连接条件：条件字段就是代表相同的业务含义（如，my_student.c_id和my_class.id）
例如：
select s.*,c.name as c_name,c.room from--字段别名
my_student as s inner join my_class as c--表别名
on s.c_id = c.id;（可以用where代替on 但是没有on效率高where 先去匹配然后判断之后与所有的结果匹配一边，on是只匹配第一个，没有就不匹配……不太懂）
内链接可以没有链接条件：没有On之后的内容，这个时候系统会保留所有的结果（形成笛卡儿积）；

外连接：outer join，以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接；不管能不能匹配上条件，最终都会保留：能匹配，正确保留，不能匹配，其他表的字段都置空为NULL.
外连接：分两种：是以某张表为主：有主表
left join：左外连接，以左表为主表
right join：右外连接（右连接），以右表为主表
基本语法：左表 left/right join 右表 On 左表.字段=右表.字段；
左连接和右连接可以互相转换；

自然连接：natural join，就是自动匹配连接条件：系统一字段名字作为匹配模式（同名字段就作为条件，多个同名字段都作为条件）。分为自然内连接和自然外连接。
自然内链接：左表natural join右表；自动使用同名字段作为链接条件：连接之后会合并同名字段
自然外链接：左边 natural left/right join右表；
内连接和连接都可以模拟自然链接：使用同名字段，合并字段。
左表 left /right/inner join 右表 using（字段名）；---使用同名字段作为链接条件，并且自动合并条件

 

PHP操作Mysql:php的角色是mySql的一个客户端
1.连接认证：
数据库链接资源 Mysql_connect( 服务器地址包含端口，用户名，密码);
在mysql_connect函数的第四个参数控制：true可以产生多个连接资源
2.php发送sql指令（等待执行结果）
3.Mysql 服务端接收指令，返回结果
4.php接受结果
Minxed Mysql_query(sql指令)
boolean结果：指令返回没有返回值，布尔结果只能代表Sql语句没有语法错误，False代表有语法错误， 主要用于增删改指令
Resource结果：结果集资源，Sql指令有结果返回（show,select），结果为true，主要用于查询语句
var_dump(变量名)；---查询变量类型；
5.php没有办法直接使用结果集：需要解析结果集。mysql扩展提过了一系列函数：
mysql_fetch(任何操作都是指针操作：操作完就会指针下移):
mysql_fetch_array:默认获取混合数组，有一组关联，有一组索引。通过第二个参数限制实现只获取关联数组或者索引数组。mysql_both是默认的，mysql_assoc是关联数组，mysql_num是索引数组。
var_dump(mysql_fetch_array(变量名))；
关联数组获取：var_dump(mysql_fetch_array(变量名，mysql_assoc));
索引数组获取：var_dump(mysql_fetch_array(变量名，mysql_num));
直接获取关联数组：var_dump(mysql_fetch_assoc(变量名));
直接获取索引数组：var_dump(mysql_fetch_row(变量名));

如果指针移动到最后，那么需要重置指针实现其他操作；MYsql_data_seek(变量名，x);重置到x处
7.循环遍历所有内容：
$database = array();
while($row = mysql_fetch_assoc($res)){//取出数组或者false
//关联数组
$databases[] = $row;//$databases是一个二维数组；
}
var_dump($databases);
8.释放资源
var_dump(mysql_close($link));
写操作：exit；终止脚本；
连接认证：不一定成功，需要对结果进行判断：可以直接使用三目运算符来进行处理，但是无法获取错误信息，如果要获取错误信息要使用mysql_errno()获取错误编号，mysql_error（）获取错误信息；
新增操作：通常会获取当前新增记录的自增长id：mysql_insert_id(),直接获取上次新增操作的自增长id,如果没有自增长id获取0；

删改操作：通常都不能直接通过mysql_query的结果true来进行判断：true不代表执行成功，之只代表sql语句没有语法错误，通过受影响的行数来判断是否成功：mysql_affected_rows()获取上次操作的受影响的行数（新增也有）；