阿里巴巴规范,每个表都需要存在五个字段。
id 主键、version 乐观锁、is_delete 伪删除、gmt_create创建时间、gmt_update 修改时间。
数据库引擎
MyISAM | InnoDB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间大小 | 较小 | 较大,2倍 |
特点 | 节约空间,速度快 | 安全 |
语句
-- 重命名表
ALTER TABLE use1 RENAME AS use2
-- 删除表
DROP TABLE IF EXISTS use2
-- 修改表字段
-- 增加
ALTER TABLE `use2` ADD `age` INT
-- 修改
ALTER TABLE `use2` MODIFY `age` VARCHAR(11)
ALTER TABLE `use2` CHANGE `age` `age1` INT(1)
-- 删除
ALTER TABLE `use2` DROP `age1`
数据管理
外键
物理外键(不建议使用)
删除麻烦
alter table `student` add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`gradeid`);
逻辑外键
在java代码里解决。
DML语言
- insert
- update
- delete
添加
-- 插入语句,可以省略自增字段
insert into 表名(字段1,字段2) values('','');
-- values后面多个()()表示插入多个值
修改
-- update 表名 set 字段=' ' where 条件
update `student` set `name`='新',`email`='新2' where id between 2 and 5;
删除
-- delete from 表名 where 条件
-- 清空数据库表,结构不变
truncate `student`
delete和truncate:
- 相同点:都能删除数据,不回删除表结构
- 不同:truncate 重新设置自增列,计数器会归零,不会影响事务。
DQL查询数据
单表查询
-- select 字段 from 表
-- 起别名: as 新名字
select `studentNO` as 学号 from student as s
-- 函数
select concat('姓名',StudentName) as 新名字 from student
-- 去重 distinct
select distinct studentNO from result
-- 模糊查询 %代表0到n个字符 _表示一个字符 in里面用不了
select `studentname` from `studen`
where studentname like '_刘%'
多表查询
-- 并集inner join
-- 要注意声明哪个表的字段,如s.studentNo
select s.studentNo,studentName,subjectNo,studentResult
from student as s
inner join result as r
where s.studentNo = r.studentNo
-- 左连接left join
-- 右连接right join
select s.studentNo,studentName,subjectNo,studentResult
from student s
right join result r
on s.studentNo = r.studentNo
where studentResulet is null
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回行 |
left join | 返回左边所有数据长度,即使右表没有匹配值 |
right join | 返回右边所有数据长度,即使左表没有匹配值 |
自连接
select `a.cname` as 'ca', `b.cname` as 'cb'
from cble as a, cble as b
where a.cid = b.pid
分页和排序
ORDER by 字段
升序ASC ,降序 DESC
select * from student
order by id desc
limit分页:缓解数据库压力, limit 起始值 页大小
select * from student
order by id desc
limit 0,5
-- limit 5,5
-- limit 10,5
limit (x-1)n n ,x表示第几页 n表示页面大小
子查询
在where里再嵌套一个(select语句),跟连接查询功效差不多,对查询结果再次查询。由里及外。
分组并过滤:
group by 字段
having 条件
事务
要么都成功,要么都失败。
mysql默认开启事务自动提交
-- 关闭自动提交事务
set autocommit = 0
-- 开启
set autocommit = 1
-- 开启事务
start transaction
xxx
-- 提交了就不能回滚了
commit
-- 回滚
rollback
索引
索引是帮助mysql高校获取数据的数据结构。
在大量数据下,索引能极大提升查找速度。
- 主键索引
- 唯一的标识,不可以重复,只能有一个列。
- 唯一索引
- 避免重复列出现,可以重复,多个列都可以建立唯一索引。
- 常规索引
- 默认的,index,key关键字来设置。
- 全文索引
- 在特定数据库引擎下才有。
- 快速定位数据。
EXPLAIN 分析sql执行情况。
索引原则
- 索引不是越多越好。
- 小数据量的表不要加索引。
- 不要对经常变动的数据加索引,会重构索引。
- 索引一般加在常用来查询的字段上。
索引的数据结构
Hash类型的索引
Btree:InnerDB的默认索引。
数据库用户管理
MySQL备份
- 直接拷贝data文件夹。
- 可视化工具中导出。
- 命令行到处 mysqldump。
三大范式
第一范式(1NF)
原子性:保证每一列不可再分。
第二范式(2NF)
满足第一范式的前提下,每张表只表述一件事情。
第三范式(3NF)
满足第二范式的前提下,每一列数据都和主键直接相关,不能间接相关。
规范性和性能的问题
阿里规范:关联查询的表不能超过三张。
- 考虑性能更加重要。
- 故意增加一些冗余字段,使多表变为单表查询。
- 故意增加一些计算列,降低为小数据量的查询。
JDBC
Java Database Connectivity,java数据库连接。
数据库驱动
程序通过数据库驱动操作数据库。
为了统一对各种数据库的操作,提供了一个规范,在驱动和数据库间加入了JDBC。
开发人员只需要掌握JDBC即可。
SQL注入
sql拼接 where 1=1 or