MySQL

-- 创建数据库
create database test;
-- 删除数据库
drop database test;

-- 创建表格
create table if not exists test.t_test(
id int auto_increment not null primary key,
name varchar(20) not null,
age int,
borthday date,
profile text
)engine=InnoDB default charset=utf8;

-- 删除表格
drop table test.t_test;

-- 向表格中插入数据
insert into test.t_test
(name, age, borthday, profile)
values
('sss', 12, '2008-04-20', 'I am a boy');

insert into test.t_test
(name, age, borthday, profile)
values
('ddd', 32, '1988-12-01', 'I am a teacher');

insert into test.t_test
(name, age, borthday, profile)
values
('syn', 26, '1994-09-03', 'I am a driver');

-- 查询语句
select name from test.t_test where age>20 limit 2 offset 1;

-- 更改数据
update test.t_test set name='xxxxx' where id=3;
select * from test.t_test;

-- 删除数据
delete from test.t_test where name='syc' ;

-- Like的使用
select * from test.t_test where name like 's%';
select * from test.t_test where borthday like '20%';

-- union的使用  用于将多个select语句的输出联合成一个集合
select * from test.t_test where name='ddd' 
union distinct 
select * from test.t_test where name='sss';

-- 用于按指定的列进行排序  asc ： 升序  desc  ：降序
select * from test.t_test order by age desc;

-- group by
select name, avg(age) as avg_age from test.t_test group by name;
select name, avg(age) as avg_age from test.t_test group by name with rollup;
select coalesce(name, 'average'), avg(age) as avg_age from test.t_test group by name with rollup;


-- mysql中的连接使用
-- 重新建一张表
drop table test.t_count;
create table test.t_count (
id int auto_increment primary key,
name varchar(20) not null,
gender varchar(8)
)engine=InnoDB, charset=utf8;

insert into test.t_count(
name, gender
)
values ('sss','男'), ('syn', '女');
select * from test.t_count;
select gender, count(gender) from test.t_count group by gender;

 -- 连接 join，  left join, right join
select a.name, a.age, b.gender, a.profile from test.t_test a inner join test.t_count b on a.name=b.name;
select a.name, a.age, b.gender, a.profile from test.t_test a, test.t_count b where a.name=b.name;


-- NULL 值  对空值的判断为 is null 或 is not null
-- 在表t_test中插入含有空值的行
insert into test.t_test
(name, age)
values('ttt', null);
select * from test.t_test;

select * from test.t_test where age is null;


-- 事务 （只有InnoDB存储引擎才会支持事务）
/*BEGIN 或 START TRANSACTION 显式地开启一个事务；

COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；

ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；

RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；

ROLLBACK TO identifier 把事务回滚到标记点；

SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。*/

begin;
set transaction isolation level  read committed;
insert into test.t_test
(name, age)
values
('qqq', 90);
commit;
select * from test.t_test;


-- Alter 修改表名或修改数据表字段
show columns from test.t_count;
-- 修改数据类型
alter table test.t_count modify gender varchar(10);
alter table test.t_count change gender gender varchar(20);
 -- 修改列名
 alter table test.t_count change gender sex varchar(10);
 -- 修改表名
 alter table test.t_count rename test.t_sex;
 show columns from test.t_sex;
 -- 增加列
 alter table test.t_sex add profile varchar(100);
 -- 删除列
 alter table test.t_sex drop profile;
 -- 添加索引
 alter table test.t_sex add index name(name);

MySQL 索引

聚簇索引和非聚簇索引
聚簇索引是将数据和索引存放在一块，找到索引就找到了数据
非聚簇索引是将数据存储与索引分开，索引的叶子节点指向索引对应的行

---

InnoDB 辅助索引 + 聚簇索引 减少IO操作， 访问数据需要二次查询
InnoDB 使用where id = 19 进行查询的时候，InnoDB直接再聚簇索引的B+树上进行查找，找到之后返回行数据，如果使用where name = ’syc’ 进行查找时，首先在辅助索引上找到其所对应的主键 id，然后通过二次查询在聚簇索引上进行查找所对应的行。
一个表只能对应一个聚簇索引，如果表中没有显示定义主键，InnoDB会隐式定义一个主键。

---

MyISAM 使用非聚簇索引，叶子节点为表数据的地址

MySQL事务隔离机制

	脏读：是指一个事务在读取数据的时候读到了另外一个事务未提交的数据，违背了事务的隔离性。
	不可重复读：一个事务需要多次读取数据，在两次读取数据期间另外一个数据对数据进行更改导致两次读取的数据不一致，违背了事务的一致性。
	幻读：一个事务在读取数据时，由于另外一个数据对插入或删除数据导致两次读取到的数据的数目不一致。
	MySQL事务隔离级别：
		1. 读未提交 ： 会出现脏读
		2. 读已提交 ：会出现不可重复读
		3. 可重复读 ： 默认事务隔离级别
		4. 串行化 ： 可以避免幻读
	实现原理 ： MVCC（多版本并发控制）
	MySQL事务隔离机制实现 MVCC：每一行记录多个版本，
	MVCC（多版本并发控制）是通过在每行记录后面保存两个隐藏版本的列实现的，这两个列，一个保存行的创建时间，一个						保存行的删除时间，在读取事务时，系统会给当前读事务一个版本号，事务会读取版本号小于等于当前版本号的数据。
	MySQL如何通过MVCC实现读已提交？
		总是读取最新的版本
	MySQL如何通过MVCC实现可重复读？
		总是读取事务之前的版本