MySql 数据库的基础操作、存储原理、事务

一、数据库分类：

1.关系型数据库

也叫SQL，一部分在内存，一部分硬盘

1.SQL Server ：微软出的，（对C#、.net 、cpp支持性强 用 IIS网络）常用在.asp、asxp、游戏方面；
2.oracle ：银行、政府机关、大型厂商使用，收费
3.Access：几乎淘汰
4.MySQL：刚开始全开源，属于oracle 公司的，有问题收服务费

2.非关系型数据库

也叫NoSQL，NOT only SQL ,不仅仅是数据库，缓存体系数据库，吞吐量大，完全在内存里，速度快

二、mysql命令

1.操作数据库

1.创建数据库：create database db_test;
2.展示所有数据库：show databases;
3.选择使用数据库：use db_test;
4.删除数据库：drop database db_test;

2.操作表结构

create table tb_student(id int primary key auto_increment,stuname varchar(256) not null,gender varchar(10) default ‘男’,age int);

drop table tb_students

desc tb_student;

加字段：alter table tb_student add(age int);

删字段：alter table tb_student drop age;

修改字段属性：alter table tb_student modify gender varchar(30) default ‘男’;

修改字段属性alter table tb_student change gender gender varchar(30) default ‘男’;

3.操作表内容

增：
insert into tb_student(stunum,stuname,gender,age) value(0,‘徐露’,‘男’,21),(1,‘王伟’,‘男’,25);

删：
delete from tb_student;

delete from tb_student where stuname=‘徐露’;

delete from tb_student where stuname='徐露’and gender=‘男’;

delete from tb_student where stuname=‘王伟’ or 1=1; 直接全删了

** 改：**
update tb_student set age =22; --------全改成22

update tb_student set age =30 where stunum =2;

update tb_student set age =40,gender =‘女’ where stunum =0;

查：

简单查询
select * from tb_student;

select stuname,age from tb_student;

select stuname,age from tb_student where age<25;

where------判断

空 age is null;

gender not in(‘男’);

gender in(‘男’);

age between 20 and 24;---------包含20和24；

select stuname as ‘姓名’,age as ‘年龄’ from tb_student where age<25;

select stuname ‘姓名’,age ‘年龄’ from tb_student where age<25; —as可以省略

4.数据库设计三大范式：

1．第一范式(确保每列保持原子性)

2．第二范式(确保表中的每列都和主键相关)

3．第三范式(确保每列都和主键列直接相关,而不是间接相关)

5.多表联查：

select s.stuname,c.kcname,r.score from tb_student s ,tb_course c ,r_student_score r where s.id=r.sid and c.id =r.cid and s.stuname=‘张三’;

select s.stuname,c.kcname,r.score from tb_student s join r_student_score r on s.id=r.sid join tb_course c on c.id =r.cid where s.stuname=‘张三’;

6.排序

按age 升序：

select name,age from tb_students order by age asc;

select name,age from tb_students order by age;（默认是升序）

按age 降序：

select name,age from tb_students order by age desc;

7.排序加排名：

按顺序 相同不会跳，row_number()

select * from(select *,(sal+comm), row_number() over(order by (sal+comm) desc)as num from tb_emp)as a where a.num =1;

相同数据会出现相同排名，会跳 rank()

select * from(select *,(sal+comm), rank() over(order by (sal+comm) desc)as num from tb_emp)as a where a.num =1;

相同数据会出现相同排名，不会跳 dense_rank()

select * from(select *,(sal+comm), dense_rank() over(order by (sal+comm) desc)as num from tb_emp)as a where a.num =1 ;

将搜出来的数据分成（n）n个段 ntile(3)

select * from(select *,(sal+comm), ntile(2) over(order by (sal+comm) desc)as num from tb_emp)as a;

8.索引

优缺点

大大提高了查询速度

降低更新表的速度，如对表进行INSERT、UPDATE和DELETE，建立索引会占用磁盘空间的索引文件

索引的分类

1.主键索引：必须是不重复唯一的，且不能为NULL。

2.唯一索引：必须是不重复唯一的，能为NULL（多个NULL不算重复）。-------UNI

alter table tb_user add unique hhh(username(10));

10代表每列的前10位作为索引，可加可不加

alter table tb_user add unique hhh(username，pwd);

是唯一索引，两个字段都相同才无法插入数据

alter table tb_user drop unique index hhh；

3.普通索引：可重复，可为NULL；-------------------MUL

创建：

alter table tb_user add index jjj(username);

删除：

alter table tb_user drop index hhh；

4.全文索引

查看索引：

desc select * from tb_user where id=2 \G;

注意：尽量不用 or

9.模糊查找：

select * from tb_students where age like '3%‘and name like’成%’;

10.去重：

select distinct deptno from emp

11.函数：

1.select count(id) from tb_student;--------------不记录null，记录重复

2.select sum(age) from tb_student;--------------不记录null，按0算

3.select avg(age) from tb_student;--------------除的是 age的个数，如果有的人 age 是null，则不计

4.select min(age) from tb_student;

5.select max(age) from tb_student;

6.select replace(gender,‘男’,‘male’) from tb_student; -把 gender 字段的所有内容’男’替换成’male’；

7.select concat(‘aaa’,‘bbb’) ; ------------拼接字符串

8.select uuid(); 大型公司用-----
insert into tb_student(id,stuname) value(replace(uuid(),’-’,’’),‘王伟’);

12.group by

谨记：group 用在where后面

select avg(score) from r_student_score group by sid; 先分组在操作

13.limit

limit 0,1;-----------------分页 从第一行取一行

limit 1,1;-----------------分页 从第二行取一行.

14.union

联合查询 只会用在统计 两张表 字段数相同（重复会覆盖）

on -------where------group by -----having -----order by------limit

三、数据库实现原理

数据库常用引擎

InnoDB引擎（默认）

数据库以 B+树（多路查找树）的形式保存数据， 只有树的最底层叶子节点（最后一排的每个节点连在一起，链表）保存数据 ，不创建主键，也会有一个隐藏主键，主键是聚簇索引，唯一索引叫 非聚簇索引，也叫二级索引，也是以B+树形式保存，最底叶子节点 保存的是在主表里的主键id（数字保存）；当用两个字段做索引，B+树连着B+树，下面的B+数叶子节点存储主键id，所以多个字段做索引时，查找按字段顺序写，可以少字段，但不能乱序，否则不命中索引（mysql 优化器）；

MyISAM引擎

以B树存储，B树索引。（对比B+，节点不重复 把数据存在自己节点）

B+树的效率 高于B树，主键效率较低，但其他索引 效率远高于B树；

InnoDB是聚集索引，支持事务，支持行级锁；MyISAM是非聚集索引，不支持事务，只支持表级锁。

四、事务

事务的特性：

1.原子性：代表这件事是一个整体，绑在一起的，都失败或都成功，不可分割；原子性：代表这件事是一个整体，绑在一起的，都失败或都成功，不可分割；

2.一致性：表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作

3.隔离性：事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）默认的和序列化（Serializable）。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）默认的和序列化（Serializable）。

1.读未提交（Read uncommitted）
出现脏读、 不可重复读、幻读

脏读：最低级一边删，插入，不提交事务，对面也能读到（两边同步的）
读到对面没提交数据

不可重复读：（一般出现在列）刚开始读到数据，另一边修改提交，再读一次发现（两次读到的数据不一样）

幻读：（一般出现在行）A边修改（改、删）数据，B边添加数据，A在B用户修改完突然发现有一条数据没修改


2.读已提交（read committed）
oracle数据库用
仅拒绝脏读

3.可重复读（repeatable read）
mysql用
拒绝不可重复读和脏读

启动两边都是 副本   CAS原理差不多，一边删一行数据，另一边改这一行，出问题

4.序列化（Serializable）

4.持久性：事务处理结束后，对数据的修改就是永久的，除非另一个事务改变这个状态。事务处理结束后，对数据的修改就是永久的，除非另一个事务改变这个状态。

事务操作：

事务开始：start transaction;

事务提交：commit；------------提交修改

事务回滚：rollback；-----------不保存修
改，恢复到事务开始前的数据；

修改隔离级别：set transaction isolation level ***

附件：