MySQL复习

创建数据库

create table if not exits `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 '家庭地址',
    primary key(`id`)
)engine=innodb default charset=utf8;

数据库引擎

null	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文检索	支持	不支持
表空间大小	较小	较大， 约为2倍

• 常规使用操作：
• MYISAM： 节约空间，速度快
• INNODB: 安全性高，事务处理，多表多用户操作
• Innodb 在数据库表中只有一个*.frm文件，以及上级目录的ibdata1文件
• Myisam: *.frm 表结构定义文件， *.myd 数据文件， *.myi 索引文件

外键

• 删除有外键关系的编的时候。必须要先删除引用别人的表，再删除被引用的表。
• 不建议使用物理外键，用程序实现

alter table `student`
add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);

insert

• insert into tableX(name, age) values(zcs, 12);
• insert into tableX values(...);

update

• update studentsetname='zcs' where id = 1;

delete

• delete from student where id = 1;
• truncate student : clear the table
• truncate: 重新设计自增列，计数器归零，不会影响事务

query

• Concat(a, b): 字符串拼接
• select distinct
• like: %(代表0到任意个字符) _(一个字符)

JoinOn

• inner join: 如果表中至少有一个匹配，返回结果
• left join：右表中没有匹配，也会返回左表的值
• right join：返回右表的值

-- 查询参加了考试的同学的学号、姓名、科目号、分数
select s.studentNo, studentName, subjectNo, studentResult
from student as s
inner join result as re
on s.studentNo = re.studentNo

-- Left join: 左表为student， 按照student中找，在result中存在学号相同的就返回结果
select s.studentNo, studentName, subjectNo, studentResult
from student as s
left join result as re
on s.studentNo = re.studentNo

-- Right join：按照右表：result查找，如果存在student的学号与result 中相等的就返回
select s.studentNo, studentName, subjectNo, studentResult
from student as s
right join result as re
on s.studentNo = re.studentNo

分页与排序

• 排序

order by ... asc
order by ... desc

• 分页

limit 开始位置， 显示个数;
-- 第一页 
limit 0, 5;
...
-- pageSize页面大小
limit (n - 1) * pageSize;

MySQL函数

select abs(-1);
select celling(9.4) = 10;
select floor(9.4) = 9;
select rand();
-- 判断符号
select sign() 
-- 返回字数
select char_length('');
select concat('', '');
-- 替换
select insert('', 1, 2,'');
select lower('');
select upper('');
select instr('', '');
-- 获取当前时间
select current_date();
select localtime();
...

聚合函数

count()
sum()
max()
min()
...
-- 分组
group by('');
-- 分组后的筛选条件
having ..

MD5加密：不可逆

insert into table values(.., md5());

查询总结

• select 去重 字段 from 表
• xxx join 表 on 等值判断
• where ...
• group by ...
• having 过滤
• order by .. [asc/desc]
• limit start, pageSize;

事务：要么都成功，要么都失败

事务原则： ACID 原子性、一致性 、隔离性、持久性

• 原子性： 要么都成功， 要么都失败

• 一致性： 前后数据完整性保持一致

• 持久性：事务一旦提交不可逆

• 隔离性：不会被干扰

• 脏读：读取了另外一个事务未提交的数据

• 不可重复读: 多次读取结果不同

• 幻读： 读取到别的事务插入的数据，前后不一致

• mysql 默认开启事务自动提交的

set autocommit = 0; /*关闭*/
set autocommit = 1; /*开启*/

-- 手动处理事务
set autocommit = 0;

start TRANSACTION

insert xx

insert xx

commit /*提交*/

ROLLBACK /*回滚*/

set autocommit = 1;

SAVEPOINT /*设置事务保存点*/

ROLLBACK to SAVEPOINT

索引

• 主键索引(primary key)：唯一标识，不可重复
• 唯一标识(unique key)： 避免重复的列出现，多个列都可以标识 唯一索引
• 常规索引(key/index):默认，index/key
• 全文索引(Fulltext)：快速定位数据

权限管理

• 用户表：mysql.user

create user zcs identitfied by '123';
set password = password('1234');
set password for zcs = password('111');
-- 用户授权
grant all privileges on *.* to zcs;

Mysql 备份

三大范式

• 第一范式：原子性

• 第二范式：每张表描述一件事

• 第三范式：确保数据表中每一列数据都和主键直接相关，不能间接相关

• 规范性：关联查询的表不得超过三张表