MySQL 数据库操作

数据库简介

SQL语言一共分为4大类：数据定义语言DDL，数据操纵语言DML，数据查询语言DQL，数据控制语言DCL。

1. 数据定义语言DDL(Data Definition Language)

2. 数据操纵语言DML(Data Manipulation Language)

3. 数据查询语言DQL(Data Query Language)

4. 数据控制语言DCL(Data Control Language)

数据库操作

数据类型

数值

• tinyint 很小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小数据 3个字节
• <mark>int 标准大小数据 4个字节</mark>
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• <mark>decimal(M,D) 字符串形式的浮点数 M+2个字节</mark>

字符串

• char 固定大小的字符串 0~255
• <mark>varchar 可变字符串 0~65535</mark>
• tinytext 微型文本 2^8 - 1
• <mark>text 文本串 2^16 - 1</mark>

时间日期

• data YYYY-MM-DD 　日期格式
• time HH:mm:ss 时间格式
• <mark>datetime YYYY-MM-DD HH:mm:ss 常用时间格式</mark>
• <mark>timestamp 时间戳</mark>

字段属性

<mark>unsigned</mark>

• 该列声明不能为负

<mark>zerofill</mark>

• 不足的位数用零来填充

<mark>auto_increment</mark>

• 自动在上一条记录上加１，即使语句报错也会增加
• 可以自定义设计自增的起始值和步长

<mark>null | not null</mark>

• 设置为not null时，插入时必须要对其赋值
• 设置为null时，插入时若不赋值则默认为null

<mark>default</mark>

• default 默认值
  

<mark>comment</mark>

• comment '注释'
  

###基本操作

mysql -u 用户名　-p   -- 数据库连接

exit -- 退出数据库

数据定义语言DDL(Data Definition Language)

数据库操作

create database if not exists `数据库名` -- 创建数据库

drop database if exists `数据库名`　-- 删除数据库

use `数据库名` -- 使用数据库

show databases  -- 查看所有数据库

desc `表名`   -- 查看表结构

表和字段操作

alter table `旧表名`　rename as `新表名` -- 修改表名

alter table `表名` add `字段名` 列属性 -- 增加表字段

alter table `表名` change `字段名` 新字段名 -- 修改表的字段名

alter table `表名`　modify `字段名`　列属性 -- 修改表的字段属性

alter table `表名` drop `字段名` -- 删除表的字段

drop table if exists `表名` -- 删除表

create table if not exists `表名`(
	`字段名` [列类型] [属性] [索引]　[注释] ,
    [主键设置]
    -- 若需要可添加[外键设置]
)[表类型][字符集设置][注释]                 -- 创建表

创建表示例

create table if not exists `student`(
	`id` int(4) not null auto_increment comment '学号',
	`name` varchar(30) not null default '匿名' comment '姓名',
 	`pwd` varchar(20) not null default '123456' comment '密码',
 	`sex` varchar(2) not null default '男' comment '性别',
 	`birthday` datetime default	null comment '出生日期',
 	`address` varchar(100) default null comment '地址',
 	`email` varchar(50) default null comment '邮箱',
 	primary key(`id`)
)engine=InnoDB charset=utf8;

<mark>所有的删除和创建操作尽量加上判断，避免报错</mark>

<mark>charset不设置则为mysql默认字符集编码－latin－－不支持中文</mark>

InnoDB & MyISAM

• MyISAM对应文件

  • *.frm 表结构定义文件
  • *.MYD 数据文件(data)
  • *.MYI 索引文件(index)

• InnoDB对应文件

  • InnoDB在数据库表中只有*.frm文件，以及上级目录下ibdata1文件

mysql 数据管理

外键

创建表时设置外键约束

constraint `外键约束名` foreign key `字段名` references `主表名`  `主键字段名`

修改表时设置外键约束

alter table `表名`　add constraint `外键约束名` foreign key `字段名`　`主键字段名`

删除外键约束

alter table `表名` drop foreign key `外键约束名`

<mark>外键可关联两个没有关系的表，因此删除有外键关系表时，须先删除从表，再删除主表</mark>

<mark>不建议使用外键，当数据库过多时容易造成混乱</mark>

数据操纵语言DML(Data Manipulation Language)

####数据操作

增删改查

insert into `表名` (`字段名`) values (`字段数据`) -- 插入数据

update `表名` set (`字段名`) (`字段数据`) where [条件] -- 更改数据

delete from `表名` where [条件]  -- 删除表中数据(符合某一条件)

truncate `表名` -- 清空表(自增归零)

<mark>delete，drop，truncate 区别：</mark>

1. delete 和 truncate 仅仅删除表数据，drop 删除表数据和表结构。

2. delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚。

3. 执行速度，drop>truncate>delete。

数据查询语言DQL(Data Query Language)

select [选项] `字段名` [as 别名] from `数据源1` 
	[left|right|inner join `数据源2`] -- 联合查询
	[where 条件字句]　　　　　　　-- 指定查询条件
	[group by 字句]            -- 查询结果分组
	[having 字句]              -- 过滤分组结果
	[order by 字句]            -- 查询结果排序
	[limit 字句]               -- 限制查询结果

<mark>[选项]———all、distinct、distinctrow、top</mark>

联表查询&子查询

分页和排序

order by asc   -- 升序
order by desc  -- 降序

limit 查询起始位置下标,每页显示数量

数据控制语言DCL(Data Control Language)

create user '用户名'@'登录服务器名' [identified by '密码'] -- 创建用户

set password = password('密码')   -- 修改当前用户自己的密码
set password for '用户名'@'服务器名' = password('密码') -- 需有修改权限才能修改他人密码

grant 权限列表 on 库名.对象名 to '用户名'@'登录服务器名' [identified by '密码']  -- 授予用户的权限（增加权限）

revoke 权限列表 on 库名.对象名 from '用户名'@'登录服务器名'  -- 取消用户权限（减少权限）

事务

ACID原则

<mark>原子性（Atomicity）</mark>
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

<mark>一致性（Consistency）</mark>
事务前后数据的完整性必须保持一致。

<mark>隔离性（Isolation）</mark>
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

<mark>持久性（Durability）</mark>
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

set autocommit = 0;  -- 关闭事务自动提交

start transaction;    -- 开启事务

-- 事务操作

commit;　　-- 提交事务

rollback;   -- 回滚操作　若事务提交成功则不影响，事务提交失败则回滚

set autocommit = 1  -- 开启事务自动提交(默认)

索引

<mark>主键索引(primary key)</mark>

<mark>唯一索引(unique key)</mark>

<mark>常规索引(key/index)</mark>

<mark>全文索引(fulltext)</mark>

创建(删除)索引

create [index|unique index|fulltext] `索引名`　on　`表名`(`字段名`) ;
alter table `表名` add [index|unique key|primary key] (`字段名`);

drop index `索引名` on table_name ;
alter table table_name drop index `索引名` ;
alter table table_name drop primary key ;

备份和导入数据库

mysqldump -h `主机名`　-u `用户名` -p `密码`  `数据库名`　`表名`  > `备份位置`

source `备份文件`  -- 需要先登录数据库
mysql  -u `用户名` -p `密码`  `数据库名`  < `备份文件` 

三大范式

<mark>第一范式（1NF）：数据库表的每一列都是不可分割的原子数据项。</mark>

<mark>第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）</mark>

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

<mark>第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）</mark>

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

设计数据库时须考虑 <mark>数据的规范化(三大范式)与数据库性能</mark> 的平衡性。