Mysql基础

1.基础语法

-- 创建数据库
1.create database []
-- 查看数据库
1.show [databases]
2.show craete database []
-- 创建表
create table if not exists [](
    id int(10) not null auto_increment,
    name varchar(20) not null default 'ye',
    primary key(id)
)engine=innodb default charset=utf8
-- 查看表
show create [table] 查看表sql语句
describe [table]  查看表字段
-- 修改表
Alter table []  [要进行的操作] []
Alter table [] rename [] 修改表名
Alter table [] add class varchar(10) not null 添加字段
Alter table [] change class class1 int(10) 修改字段
Alter table [] modify class int(10) 修改字段，但是不能修改字段名
Alter table [] drop [] --删除字段
-- 删除表
Drop table if exists []
-- Insert操作
insert into [table] （name） value('') 插入特定的值
insert into [table] values('','','') 根据数据类型顺序插入。
insert into [table] values('','','')('',''),('','') 插入多条数据
-- update操
update [table] set name='' where id= #{id} 根据id来进行更新
# 补一下运行符号
!=,<,>,<=,>=,and,or,between and
-- delete操作
delete [table] where id=#{id}
delete from [table]  清空表数据，但是如果是自增的字段，则id指针不会归零
truncate [table]  清空表数据.
-- 查询操作
select * from [table] --查询全部结果
select id from [table]  --查询id
select distinct name from [table] --对查询出来的名字去重
select id as i from [table] --别名
select version()   查询版本
select 3+1       表达式运算
select @@auto_increment_increment  查询'变量'值
-- 模糊查询
#运算符
SELECT `name`,`Sex` FROM [student] WHERE NAME LIKE '张%' %(代表0或者任意一个符号)  _（代表一个符号）
SELECT `name`,`Sex` FROM [student] WHERE NOT NAME = '张三'
SELECT `name`,`Sex` FROM [student] WHERE NAME is not null;
SELECT `name`,`Sex` FROM [student] WHERE NAME is null;
SELECT `name`,`Sex` FROM [student] WHERE id between 10 and 12
SELECT `name`,`Sex` FROM [student] WHERE id in (a1,a2,a3,a4) 当id=a1,或者id=a2，或，，时则条件成立

-- 联合查询
select [ele][][][] from [table] left join [table] on   连接查询，以左表为基准
select [ele][][][] from [table] right join [table] on   连接查询，以右表为基准
select [ele][][][] from [table] inner join [table] on   连接查询，以交叉的数据为基准
select [ele][][][] from [table] left join [table] on where   on可以和where等值查询一起使用。
select [ele][][][] from [table] left join [table] on inner join [table] on   多表查询，建议从双表查询开始。

补一下自连接

SELECT c.name AS 专业,s.name AS 姓名 FROM `selfstudent` AS c,`selfstudent` AS s WHERE c.`id` =s.`pid`
-- 计算机是父id，姓名指向父id，从而有关联关系。

2.关键字

select[ALL|distinct][*|table.field1[AS ailed1]] from [table][AS t][left join|right join|inner join] [table]
on[] [where][group by][having (次要条件，第二次过滤，having可以写多个，where可以写多个)][order by][limit]

limit 0  -- 显示0条
limit 5,5  -- 从第五条开始显示五条
limit(当前下标,pagesize)
分页查询:
limit (n-1)pagesize,pagesize
总页数:total-1/pagesize
n:当前页

3.数据类型

• tinyint 最小的整型,一个字节
• smallint 2个字节
• mediumint 3个字节
• int 4个字节
• bigint 8个字节
• float 4个字节
• double 8个字节
• decimal 字符串的浮点类型，对于无限循环小数，可以避免精度的丢失
• char 固定长度，0~255位
• varchar（常用）0~65535
• tinytext 2^8-1
• text(常用) 2^16-1
• date：yyyy:mm:dd 日期格式
• time hh:mm:ss 时间格式
• datetime(常用): yyyy:mm:dd:hh:mm:ss
• timestamp:时间戳，例如1990.01到现在毫秒数

4.函数

4.1 常用函数

参考(https://www.mysqlzh.com/doc/117/233.html)

4.2 聚合函数

count() -- 
select count(*) from [table] 会忽略null
select count(name) from [table] 不会忽略null
select count(1) from [table]  将全部字段转换为1并计算行数，会忽略null
avg() --
min()
max()
sum()
INSERT INTO `count`(`name`,`password`) VALUE('Yyy',MD5('123456'))
MD5()  -- 加密。不可逆的  

5.事务

原子性

要么全部成功，要么全部失败。

一致性

事务提交前，和事务提交后，数据的完整性不变，数据的总量不变

持久性

事务提交后是不可逆的，且事务提交后所改变的数据会持久化到数据库中。

隔离性

当多个用户对数据库同个资源进行并发操作时，事务与事务之间是相互隔离的。

有关事务隔离性可能带来的问题

1. 脏读

   一个事务读取到了另一个事务为提交的数据。

2. 幻读

   一个事务读取到了另外一个事务插入的数据。

3. 不可重复读

   一个事务读取表的某一行时，分别读取了不同的数值。

   mysql实现事务

   # mysql中是默认开启事务的
   SET autocommit=0;
   START TRANSACTION
   UPDATE bank SET money=money+500 WHERE id = 1
   UPDATE bank SET money=money-500 WHERE id = 2
   COMMIT 
   ROLLBACK
   SET autocommit=1
   

6.Mysql数据存储的过程

Mysql数据是存储在硬盘中的，这样保证了数据的持久化。但是有一个问题，就是会减低数据库的性能。当每次需要进行查询的时候，都需要去硬盘去获取数据。所以数据库用了一个BufferPool来解决问题。
当查询到缓存池里有数据时，则直接从缓存池里获取数据，如果没有则从硬盘获取。在放入缓存池。那么这时候又出现了一个问题。当操作系统出现故障时，数据会丢失。这时候就引进了redo log（重写日志）

redo log:undo log有redo log buffer和redo log。前者存储在内存中，后者存储在硬盘中。其主要的作用是就记录成功提交事务后所修改的数据。当操作系统重启时，数据库会从redo log里获取最新的数据并写入。
undo log：用于每次对数据库的操作而产生的数据的变化都会存在undo log里

7.MVCC的机制

在innodb中得到支持
多版本控制，在表的最后隐藏了两个列，一个是的创建时间(事务id)，一个是过期时间(回滚指针)。
MVCC依赖于undo log与read view。MVCC不需要读锁就能读写并行。
可重复读的select语句用的是快照旧表，而updata用的是当前版本。

8. 索引

索引是能帮助用户快速找到数据的数据结构。

原则：

索引不是越多越好

索引应该加在常用来查询的字段上

索引不应该加在经常变动的数据上

数据量小不需要加索引

create index [index_name] on [table](table的字段)
create fulltext index	 [index_name] on [table](table的字段)

• 唯一索引

  标识一列为唯一索引，一个表可以有多个唯一索引。Union KEY

• 主键索引

  一个表只能有一个主键索引。PRIMARY KEY

• 普通索引

  默认是key(),或者index()

• 全文索引
  fulltext

  EXPLAIN  # 查看sql语句执行情况
  
  ALTER TABLE student.`course` ADD FULLTEXT INDEX cname(cname);
  
  EXPLAIN SELECT * FROM course WHERE MATCH(cname) AGAINST('信息')
  show index from [table]  # 查看表的索引信息
  

• 创建百万数据进行测试

  DELIMITER $$
  CREATE FUNCTION mock_data ()
  RETURNS INT
  BEGIN
     DECLARE num INT DEFAULT 1000000;
     DECLARE i INT DEFAULT 0;
     WHILE i<num DO
       INSERT INTO app_user(`name`,`email`,`phone`,`gender`)
       VALUES(CONCAT('用户',i),'673773825@qq.com','123456789',FLOOR(RAND()*2));
       SET i=i+1;
     END WHILE;
     RETURN i;
  END;
  
  CREATE INDEX name_id ON app_user(NAME)
  SELECT * FROM app_user WHERE NAME='用户999'
  
  

  9. Innodb和myisam的区别

1. innodb表所占的空间是myisam的两倍
2. innodb的物理存储文件的后缀是.frm和上一级目录的的ibdata，myisam则是.frm,myi和myd
3. innodb支持行锁和表锁，默认是行锁。myisam只支持表锁
4. innodb在5.6版本后才支持全文索引
5. innodb现在是默认的存储引擎，而myisam在早期是默认的存储引擎
6. innodb支持事务,myisam不支持事务
7. innodb支持外键,myisam不支持外键
8. 如果考虑的是安全性，可以优先使用innodb。在查询和插入方面，myisam速度更快
9. 获取行数时，innodb需要一行行的算。而myisam可以直接获取行数。
10. innodb是先存储在缓存池在持久化到硬盘，myisam是直接持久化到硬盘。