MySQL汇总

 

 

连接数据库操作

/*连接mysql*/
mysql -h 地址 -P 端口 -u 用户名 -p 密码
例如: mysql -h 127.0.0.1 -P 3306 -u root -p ****

/*退出mysql*/
exit;

 

数据库操作

#数据库操作
/*关键字:create 创建数据库(增)*/
create database 数据库名 [数据库选项];
例如: create database test default charset utf8 collate utf8_bin;
/*数据库选项:字符集和校对规则*/
字符集:一般默认utf8;
校对规则常见: ⑴ci结尾的:不分区大小写 ⑵cs结尾的:区分大小写 ⑶bin结尾的：二进制编码进行比较

/*关键字:show 查看当前有哪些数据库(查)*/
show databases;

/*查看数据库的创建语句*/
show create database 数据库名;

/*关键字:alter 修改数据库的选项信息(改)*/
alter database 数据库名 [新的数据库选项];
例如: alter database test default charset gbk;

/*关键字:drop  删除数据库(删)*/
drop database 数据库名;

/*关键字:use 进入指定的数据库*/
use 数据库名;

 

表的操作

#表的操作
/*关键字:create 创建数据表(增)*/
create table 表名(
字段1  字段1类型 [字段选项],
字段2  字段2类型 [字段选项],
字段n  字段n类型 [字段选项]
)表选项信息;

例如: create table test(
  id int(10) unsigned not null auto_increment comment 'id',
  content varchar(100) not null default '' comment '内容',
  time int(10) not null default 0 comment '时间',
  primary key (id)
)engine=InnoDB default charset=utf8 comment='测试表';

语法解析(下文MySQL列属性单独解析):
如果不想字段为NULL可以设置字段的属性为NOT NUL,在操作数据库时如果输入该字段的数据为NULL,就会报错.
AUTO_INCREMENT定义列为自增的属性,一般用于主键,数值会自动加1.
PRIMARY KEY关键字用于定义列为主键.可以使用多列来定义主键,列间以逗号分隔.
ENGINE 设置存储引擎,CHARSET 设置编码, comment 备注信息.

/*关键字:show 查询当前数据库下有哪些数据表(查)*/
show tables;

/*关键字:like 模糊查询*/
通配符：_可以代表任意的单个字符，%可以代表任意的字符
show tables like '模糊查询表名%';

/*查看表的创建语句*/
show create table 表名;

/*查看表的结构*/
desc 表名;

/*关键字:drop  删除数据表(删)*/
drop table [if exists] 表名
例如: drop table if exists test;

/*关键字:alter 修改表名(改)*/
alter table 旧表名 rename to 新表名;

/*修改列定义*/
/*关键字:add 增加一列*/
alter table 表名 add 新列名 字段类型 [字段选项];
例如: alter table test add name char(10) not null default '' comment '名字';

/*关键字:drop 删除一列*/
alter table 表名 drop 字段名;
例如: alter table test drop content;

/*关键字:modify 修改字段类型*/
alter table 表名 modify 字段名 新的字段类型 [新的字段选项];
例如: alter table test modify name varchar(100) not null default 'admin' comment '修改后名字';

/*关键字:first 修改字段排序,把某个字段放在最前面*/
alter table 表名 modify 字段名 字段类型 [字段选项] first;
例如: alter table test modify name varchar(100) not null default 'admin' comment '最前面' first;

/*关键字:after 修改字段排序,字段名1放在字段名2的后面*/
alter table 表名 modify 字段名1 字段类型 [字段选项] after 字段名2;
例如: alter table test modify name varchar(100) not null default 'admin' comment 'time字段后面' after time;

/*关键字:change 重命名字段*/
alter table 表名 change 原字段名 新字段名 新的字段类型 [新的字段选项];
例如: alter table test change name username varchar(50) not null default '' comment '用户名字';

/*修改表选项*/ 
alter table 表名 表选项信息;
例如: alter table test engine Myisam default charset gbk; --修改存储引擎和修改表的字符集

 

数据操作

#数据操作
/*关键字:insert 插入数据(增)*/
insert into 表名(字段列表) values(值列表);
例如: create table user(
  id int(10) unsigned not null auto_increment comment 'id',
  name char(10) not null default '' comment '名字',
  age int(3) not null default 0 comment '年龄',
  primary key (id)
)engine=InnoDB default charset=utf8 comment='用户表';
--插入数据
insert into user(id,name,age) values(1,'admin_a',50);
insert into user(name) values('admin_b');

/*关键字:select 查询数据(查)*/
select *[字段列表] from 表名[查询条件];
例如: select * from user;--查全部字段用*代替

select name from user where age>0;--查name字段,age大于0

/*关键字:delete 删除数据(删)*/
delete from 表名[删除条件];
例如: delete from user where age<1;--删除age小于1数据

/*关键字:update 修改数据(改)*/
update 表名 set 字段1=新值1,字段n=新值n [修改条件];
例如: update user set age=100 where name='admin_a';

 

MySQL数据类型

#MySQL数据类型
/*MySQL三大数据类型:数值型、字符串型和日期时间型*/

/*数值型*/

/*字符串型*/

/*日期时间型*/

 

MySQL列属性

#MySQL列属性
/*null、not null、default、primary key、auto_increment、comment*/
MySQL真正约束字段的是数据类型,但是数据类型的约束太单一,需要有一些额外的约束,来更加保证数据的合法性.
MySQL常用列属性有：null、not null、default、primary key、auto_increment、comment.

/*空属性: null和not null*/
空属性: null(空,默认) 和 not null(不为空). mysql数据库默认字段都是为null的,实际开发过程中尽可能保证所有的数据都不应该为null,空数据没有意义.
例如: create table test(
    a int not null,
    b int
);
insert into test (a,b) values(10,null);
insert into test (a,b) values(null,10);--报错

/*默认值: default*/
default: 自定义默认值属性,通常配合not null一起使用.
例如: create table test1(
    a int not null default 200,
    b int
);
insert into test1(b) values(20);--或 insert into test1(a,b) values(default,20);

/*主键|唯一索引*/
Mysql中提供了多种索引? (下文索引更多解析)
1.主键索引:primary key
2.唯一索引:unique key
3.全文索引:fulltext index
4.普通索引:key 或 index

主键:primary key  一张表中只能有一个字段可以使用对应的主键,用来唯一的约束该字段里面的数据,不能重复和不能为null.
设置主键有两种方式：
(1)在定义一个字段的时候直接在后面进行设置primary key
例如: create table test2(
  id int(10) unsigned not null primary key,
  name char(20) not null default ''
);

(2)定义完字段后再定义主键
例如: create table test3(
  id int(10) unsigned not null,
  name char(20) not null default '',
  primary key (id)
);

唯一键:unique key 解决表中多个字段需要唯一性约束的问题.
例如:create table test4(
  id int(10) unsigned not null,
  name char(20) not null default '',
  goods varchar(100) not null default '',
  primary key (id),
  unique key (name,goods)
);

/*自动增长: auto_increment*/
自增长属性:每次插入记录的时候,自动的为某个字段的值加1(基于上一个记录). 通常跟主键搭配.
自增长规则：(1)任何一个字段要做自增长前提必须是一个索引  (2)自增长字段必须是整型数字
例如: create table test5(
  id int(10) unsigned not null auto_increment,
  name char(20) not null default '',
  primary key (id)
);

/*列描述 comment*/
列描述(注释):comment 与其他的注释符不同之处在于,这里的注释内容属于列定义的一部分.
例如:create table user(
  id int(10) unsigned not null auto_increment comment 'id',
  name char(20) not null default '' comment '名字',
  primary key (id)
)engine=InnoDB default charset=utf8 comment='用户表';

 

索引

#索引
/*索引的概述和优缺点和种类*/
什么是索引？
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分),它们包含着对数据表里所有记录的引用指针.
类比理解:数据库中的索引相当于书籍目录一样,能加快数据库的查询速度.
没有索引的情况,数据库会遍历全部数据后选择符合条件的选项.
创建相应的索引,数据库会直接在索引中查找符合条件的选项.

索引的性质分类：
索引分为聚集索引和非聚集索引两种,聚集索引是索引中键值的逻辑顺序决定了表中相应行的物理顺序,而非聚集索引是不一样;
聚集索引能提高多行检索的速度,而非聚集索引对于单行的检索很快.

索引的优点：
(1)加快数据检索速度 (创建索引主要原因)
(2)创建唯一性索引,保证数据库表中每一行数据的唯一性
(3)加速表和表之间的连接
(4)使用分组和排序子句对数据检索时,减少检索时间
(5)使用索引在查询的过程中,使用优化隐藏器,提高系统的性能

索引的缺点：
(1)创建索引和维护索引要耗费时间,时间随着数据量的增加而增加
(2)索引需要占用物理空间和数据空间
(3)表中的数据操作插入、删除、修改, 维护数据速度下降

索引种类
(1)普通索引: 仅加速查询
(2)唯一索引: 加速查询 + 列值唯一（可以有null）
(3)主键索引: 加速查询 + 列值唯一（不可以有null）+ 表中只有一个
(4)组合索引: 多列值组成一个索引,专门用于组合搜索,其效率大于索引合并
(5)全文索引: 对文本的内容进行分词,进行搜索 (注意:目前仅有MyISAM引擎支持)

/*创建表时直接指定索引*/
--创建主键索引
例如:create table student(
  id int(10) unsigned not null auto_increment comment 'id主键索引',
  name char(20) not null default '' comment '名字',
  class varchar(50) not null default '' comment '班级',
  seat_number smallint(5) not null default 0 comment '座位编号',
  primary key (id)
)engine=InnoDB default charset=utf8 comment='学生表';

/*创建索引*/
create [unique唯一索引][clustered聚集索引] index <索引名>
on <表名>(<列名称>[<排序>],<列名称>[<排序>]…);

语法解析:其中unique和clustered为可选项. 注意:基本表上最多仅仅能建立一个聚集索引.
"列名称":规定需要索引的列. "排序":可选asc(升序)和desc(降序)缺省值为asc.

--创建唯一索引(unique key 简写 unique)
例如:create unique index number on student(seat_number desc);

--创建普通索引
例如:create index name_class on student(name asc,class desc);

/*删除索引*/
drop index <索引名> on 表名;
例如:drop index number on student;

 

外键

/*外键约束: foreign key */
外键约束: foreign key 被约束的表叫做副表,外键设置在副表上面,外键引用主键字段所在的表叫做主表. (作用:约束两张表的数据)
外键定义语法: constraint 外键约束名称 foreign key(外键字段) references 主表名称(引用字段)
外键数据操作：
1.当有外键约束之后,添加数据的时候,先添加主表数据,再添加副表数据
2.当有了外键约束,修改数据的时候,先改副表的数据,在修改主表的数据
3.当有了外键约束,删除数据的时候,也是先删除副表的数据,再删除主表的数据

外键注意事项：
1.外键约束只有InnoDB存储引擎才支持.
2.实际项目中往往会用到外键的设计思想,但往往不会真正的从语法上进行外键约束. 因为外键约束的级联操作可能会带来一些现实的逻辑问题. 另外使用外键会较低mysql的效率.

 

储存引擎

/*储存引擎: engine*/
常见的引擎：Myisam InnoDB BDB Memory Archive 等

不同的引擎在保存表的结构和数据时采用不同的方式?
MyISAM表文件含义：.frm表定义，.MYD表数据，.MYI表索引
InnoDB表文件含义：.frm表定义，表数据空间文件(存放表的数据和索引)和日志文件

注意:项目中一般用InnoDB引擎.

MySQL中innodb与myisam的区别
(1):InnoDB支持事物,MyISAM不支持事物
(2):InnoDB支持行级锁,MyISAM支持表级锁
(3):InnoDB支持快照数据(MVCC),MyISAM不支持
(4):InnoDB支持外键,MyISAM不支持
(5):InnoDB不支持全文索引,MyISAM支持

innodb使用的是行级锁:只有在增删改查时匹配的条件字段带有索引时,innodb才会使用行级锁.(行级锁:要为匹配条件字段加索引)
innodb使用的是表级锁:如果增删改查时匹配的条件字段不带有索引时,innodb使用的将是表级锁.(表级锁:数据库全表查询,需要将整张表加锁,保证查询匹配的正确性)
触发读锁:就是用select命令时触发读锁.
触发写锁:就是在你使用update,delete,insert时触发写锁,并且使用rollback或commit后解除本次锁定
更好的区分:读写锁和表级锁行级锁. 将读写锁叫做权限锁(决定了加锁后用户有哪些操作权限),将表级锁行级锁叫做对象锁(决定将锁加在某一行还是整张表)

MVCC多版本并发控制,也可称之为一致性非锁定读;它通过行的多版本控制方式来读取当前执行时间数据库中的行数据. 实质上使用的是快照数据.
为什么要使用MVCC?
消除锁的开销. (如果要保证数据的一致性,最简单的方式就是对操作数据进行加锁,但是加锁不可避免的会有锁开销. 所以使用MVCC能避免进行加锁的方式并提高并发.)

 

三范式

/*数据库三范式*/
--建表规范
- 每个表保存一个实体信息
- 每个具有一个ID字段作为主键
- ID主键 + 原子表

--第一范式
字段不能再分,确保每列保持原子性.

--第二范式
满足第一范式的基础上,不能有部分依赖.
消除符合组合主键(A,B)就可以避免部分依赖. 增加单列关键字.

--第三范式
满足第二范式的基础上,不能有传递依赖.
某个字段依赖于主键,而有其他字段依赖于该字段. 这就是传递依赖.
将一个实体信息的数据放在一个表内实现