mysql基础

mysql基础

一、数据库基础

1. 什么是数据库：存储数据的仓库。
数据：
数据(data)是事实或观察的结果，是对客观事物的逻辑归纳，是用于表示客观事物的未经加工的原始素材。
数据可以是连续的值，比如声音、图像，称为模拟数据。也可以是离散的，如符号、文字，称为数字数据。
在计算机系统中，数据以二进制信息单元0,1的形式表示。
2.数据库的作用：方便数据的存储和管理。
3.数据库的使用场景：有数据产生的地方就有数据库的使用场景。
4.数据库的发展史：实体的空间 随着计算机的发展（文件系统的发展）。
5.数据库管理系统： mysql sql server oracle
关系型数据 Mysql c语言 sql server
非关系型数据 redis
如何制定关系：mysql 创建二维表的形式
通过指定关系（创建二维表） 把数据放在对应表中；通过常识 + 范式 —> 创建二维表
例如：学生学号 姓名 性别 年龄
6.数据库系统：数据库 + 数据库管理系统 = 数据库系统

二、mysql数据库操作

1. 数据库基础操作

1.1 命令 登录 mysql: mysql -u root -p
1.2 退出 : exit
1.3 创建数据库：create database 数据库名 （命名规则：school school_db 不能重名）
1.4 查看已经创建好的数据库：show databases;
1.5 删除数据库：drop database 数据库名;
1.6 使用当前数据库：use 数据库名;

2.数据表基础操作

（1）表
表，也称为关系，是一个二维的数据结构.
它由表名、构成表的各个列及若干行数据组成。每个表有一个唯一的表名，表中每一行数据描述一条具体的记录值，如一个学生的基本信息。

（2）关系
一个关系逻辑上对应一张二维表，可以为每个关系取一个名称进行标识。
关系可以有三种类型，即基本关系、查询表和视图表。
基本关系通常又称为基本表或基表，是实际存在的表，它是实际存储数据的逻辑表示；
查询表是查询结果对应的表；
视图表是由基本表或其他视图表导出的表，是虚表。

（3）列
表中的列，也称作字段或属性。表中每一列有一个名称，称为列名、字段名或属性名。每一列表示实体的一个属性，具有相同的数据类型。

（4）属性
表中的一列即为一个属性，给每一个属性起一个名称极为属性名。表中属性的个数称为关系的元或度。

（5）值域
列的值称为属性值，属性值的取值范围称为值域。

（6）行
表中的行，也称作元组或记录。表中的数据是按行存储的。表中的一行数据即为一个元组或一条记录，其每行由若干字段值组成，每个字段值描述该对象的一个属性或特征。

（7）分量
元组中的一个属性值，称为分量。

2.1 创建二维表

create table 表名 (
属性名1 数据类型 完整性约束(可以没有的),
属性名2 数据类型 完整性约束,
属性名3 数据类型 完整性约束,
…
属性名N 数据类型 完整性约束
);

2.1.1 约束：

主键：通过主键推出其他的所有属性的值。如果不满足上述条件说明主键设置的有问题,一个表只能有一个主键.
stu_id(主键) name sex age 不能重复 不能为空
联合主键: 多字段主键 (stu_id class_id)主键 score

2.1.2 数据类型的选择：尽量小但要合适
2.1.3 字符串类型：尽量小但要合适
int：最多向里面存储int个字符
varchar(int ‘10’):
1 ~ int(10)
（1）存储数据的时候根据存储的数据计算所占的字符大小
（2）然后再去开辟空间
（3）数据放进去
char() :创建表的时候空间就已经开辟好了.存储数据时 直接将数据存储进去。
name: 可长可短 varchar(20)
sex : 长度固定 char(2)
text: 文本文件 很少用的 mysql空间太珍贵了 一般只在mysql中存储 文本的地址
enum(‘m’,‘w’) 单选
set(‘java’,‘cpp’,‘linux’); 多选
float/DOUBLE/dec (M,D) M:数据的长度 D 小数点后保留几位
二进制类型：
不会把数字转成二进制存储
使用场景：存放文件 非文本文件 .mp3 .mp4 .avi .jpg
一般只在mysql中存储 文本的地址

2.2 插入语句

（1）属性不可缺省方式：
test_user(id int ,name varchar(20),age int)
insert into(into可以不写) 表名 values(属性值1,属性值2,属性值3,属性值4…);
例：insert test_user values (1,‘zhangSan’,15);
insert test_user values (2,‘liSi’);错
（2）属性可缺省方式：
insert into(into可以不写) 表名 (属性1,属性2,属性3,属性4…)values(属性值1,属性值2,属性值3,属性值4…);
例：insert test_user(id,name) values (2,‘liSi’);
（3）字符串类型 日期时间 加 ‘’ ‘Bob’ '1990’
例：

create table test_user(id int primary key auto_increment,name varchar(10) not null default 'zhansan');
      enum('m','w')  
      set('java','cpp','') ;

 create table stu_test(
 stu_id int primary key,
 bir datetime
 );

 insert into stu_test values(1,'1990-01-01');
 insert into stu_test (stu_id) values(2);

2.3 删除语句

(1)delete from 表名 where 筛选条件;
(2)delete from 表名; 清空了整张表

2.4 修改语句

update 表名 set 属性1 = 新值1,属性2 = 新值2 … where 筛选条件;

2.5 基本查询

(1)查看表中的所有元组对应的所有字段：select * from 表名;
(2)查看表中的查看表中的所有元组对应的指定字段： select 属性名1,属性名2, … from 表名;
(3)查询指定的元组：
根据题目中的要求或者项目开发的需求写合适筛选条件
select * from 表名 where 筛选条件;
select 属性名1,属性名2, … from 表名 where 筛选条件;
(4)如果所要查询的数据分布在多张表中这时我就要使用联合查询或者子查询。
联合查询(连接查询)：把多张表联合（连接）在一起查询出我们想要的数据。
如何联合（连接）：这多张表中必须有意义相同的字段。
意义相同的字段 ： EX
(5)内连接查询：能够把多挣表中意义相同字段相等时的那部分数据查出来。

语法：
 (1)select 表1.属性名,.... 表3.* from 表1,表2,表3.......... 
    where 表1.EX = 表2.EX and 表2.EX  = 表3.EX  and 表3.EX =表4.EX ......;
    select a.属性名,.... c.* from 表1 a,表2 b,表3 c.......... 
    where a.EX = b.EX and b.EX  = c.EX  and c.EX =d.EX ......;
 (2) (inner) join 括号表示可以省略
    select 表1.属性名,.... 表3.*  from 表1 (inner)join 表2 on 表1.EX = 表2.EX  (inner)join 表 表2.EX  = 表3.EX .......;	
    select 表1.属性名,.... 表3.*  from 表1 a (inner) join 表2 b on a.EX = b.EX  (inner) join 表3  c on  b.EX  = c.EX .......;

(6)外连接查询：
（1）左连接查询

select 表1.属性名,.... 表3.*  from 表1 left join 表2 on 表1.EX = 表2.EX  left join 表3 on  
             表2.EX  = 表3.EX .......;	
             select 表1.属性名,.... 表3.*  from 表1 a leftjoin 表2 b on a.EX = b.EX  left  join 表3  c on  
             b.EX  = c.EX .......;

（2）右连接查询

select 表1.属性名,.... 表3.*  from 表1 right join 表2 on 表1.EX = 表2.EX  right join 表3 on  
             表2.EX  = 表3.EX .......;	
             select 表1.属性名,.... 表3.*  from 表1 a right 表2 b on a.EX = b.EX  right  join 表3  c on  
             b.EX  = c.EX .......;

几种连接合在一起使用

 select 表1.属性名,.... 表3.*  from 表1 right join 表2 on 表1.EX = 表2.EX  left  join 表3 on  
      表2.EX  = 表3.EX (inner)join 表4 on 表3.EX  = 表4.EX .......;	
      select 表1.属性名,.... 表3.*  from 表1 a right 表2 b on a.EX = b.EX  left  join 表3  c on  
	  b.EX  = c.EX join 表4 d on c.EX = d.EX .......;

2.6 子查询

子查询：是将一个查询语句嵌套在另外一个查询语句当中。内层查询的结果可以为外层查询提供查询条件
子查询的语法：select 属性列表 from 表1 where 连接方式 (select 属性列表 from 表2 where 刷选条件);
where之后+ 筛选条件/查询条件
内层查询
外层查询
目的：某一张表中的一部分数据，作为另一个表中的查询条件。
（1）内层这张表中所有的数据先找出来。11 2 33
（2）连接方式：in 、not in、exists not exists、 比较运算符、 比较运算符和any/all的结合

A : id  :33 40 54  66  67   70  78   90 
B : id  :33 40 89  78  70   90  109  108
in / not in
select * from A where id in (select id from B where id > 70);
select * from A where id in (89 78  90  109 108); 
78  90 
select * from A where id not in (select id from B where id > 70);
select * from A where id not in (89 78  90  109 108); 
33 40 54  66  67   70

exists ：当内层查询查询到结果是记为true 这是外层查询才执行，否则外层查询不执行。

if(内层查询查询到结果){  //内层查询查询到结果 为true
	外层查询开始执行}
select * from A where exists (select id from B where id > 70);
                                   true
33 40 54  66  67   70  78   90
select * from A where exists (select id from B where id > 170);
									false
空
not exists
if(!内层查询查询到结果){  //内层查询查询到结果 为true
	内层查询开始执行}	

三、运算符

比较运算符

select * from A where id = (select id from B where id > 70); 报错。
select * from A where id = (89 78 90 109 108); 报错
< > <= >= !=
所有的比较运算符 只能和一个数字进行比较,如果内层查询返回多个数据，请用in 和 not in 进行比较。

select * from A where id = (select max(id) from B);
select * from A where id = 109 ;

A : id :33 40 54 66 67 70 78 90
B : id :33 40 89 78 70 90 109 108
比较运算符和any/all的结合
select * from A where id >any (select id from B where id > 50);
select * from A where id >any (70 89 78 90 109 108);
90 78
外层查询和内层查询中任何一个数据作比较。大于任意一个 《—》大于最小
大于所有 《—》 大于最大
select * from A where id >all (select id from B where id > 50);
select * from A where id >all (70 89 78 90 109 108);

四、mysql编码问题

查询mysql数据库默认编码：show variables like ‘character_set_database’;
（1）给varchar/char 前面+n
只改变一个属性的编码形式。
create table test_char (
name nvarchar(10)
);
只改变一个属性的编码形式。
create table test_char (
name varchar(10) CHARACTER SET utf8
);
create table test_char1 (
sex enum(‘男’,‘女’) CHARACTER SET utf8
);

create table sex_tb1(
Ssex enum(‘m’,‘w’)
);
alter table sex_tb1 modify Ssex enum(‘男’,‘女’) character set utf8;

改变整张表的编码形式：
create table test_char3 (
name varchar(10)
)CHARSET=utf8;

改变整个数据库编码形式
改变mysql的配置文件 my.ini
这个配置文件是只在mysql 服务启动的时候会读取的，如果改变了mysql的配置文件一定要重启
服务之后改变好的配置才会起到作用。

改变编码形式 utf-8编码

五、mysql索引

1.什么是索引

索引用来快速地寻找那些具有特定值的记录，所有MySQL索引都以B树的形式保存。如果没有索引，执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录，直至找到符合要求的记录。表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。
一列或者多列值 排序之后的一个数据结构 （B+树）
index(a,b,c)
a —> b —> c
二叉树 查找二叉树
index（age，name）
age
log(2)N log(M)N
innodb：只在B+树的叶子节点存储数据，存储是建立索引的所有字段和主键。
Myisam：只在B+树的叶子节点存储数据存储是建立索引的所有字段和数据的地址。

2.为什么要有索引（索引的作用）

提高查询速度 跟sql语句有关

explain   select * from user where id = 1;
index(age)
user : id  age  name     
select * from user where age = 15;
 最左前缀原则
 索引的有效性

3.索引建立在哪？索引这样的结构放在哪？

建立表上
Innodb
.frm 存储表结构
.ibd 存储数据和索引

myIsam：
.myi ： 存储索引
.myd ： 存储数据
.frm ： 存储表结构

4.索引的种类，不同种类的索引如何建立？如何判断索引已经建立？

	a.建立索引的字段特点
	唯一性索引  普通索引 全文索引 空间索引  主键索引（默认就有）
name 
    b.是否建立在主键上：  
	主键索引  辅助索引
    innodb：只在B+树的叶子节点存储数据，存储是建立索引的所有字段和主键。
	如果是主键索引叶子节点存储，主键和与之对应的所有数据。
	
     Myisam：只在B+树的叶子节点存储数据存储是建立索引的所有字段和数据的地址。
		select * from user where name = 'Tom'\G
		index(name,sex,age)
		100属性   90个
		mysiam：一定会走两次 ，第二次走到文件里。
	
	c.以几个字段建立索引
	多列索引（联合索引）
	单列索引
	d.底层数据结构分：
	B+树索引    哈希索引  （hashMap）
	哈希索引：无法做范围查询
	get（key） 近似于O(1)  哈希算法 哈希冲突
	log2(N)  O(N)
	select * from use where age > 50 and age < 30;
	hashMap.get(50);
	hashMap.get(30);	
	log M(N) M：表示B+树分支
	
	e.引擎分：
	Innodb：聚簇（聚集）型索引  
	myIsam：非聚簇（聚集）型索引
	
	查看索引：show create table  表名;

5.索引的优缺点

	优点：
	1.通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
	2.可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
	3.可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
	4.在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
	5.通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
	
	缺点：
	首先，索引要占用磁盘空间。通常情况下，这个问题不是很突出。但是，如果你创建每一种可能列组合的索引，索引文件体积的增长速度将远远超过数据文件。如果你有一个很大的表，索引文件的大小可能达到操作系统允许的最大文件限制。
	第二，对于需要写入数据的操作，比如DELETE、UPDATE以及INSERT操作，索引会降低它们的速度。这是因为MySQL不仅要把改动数据写入数据文件，而且它还要把这些改动写入索引文件。

6.索引设计原则

1. 选择唯一性索引
2. 为经常需要排序、分组和联合操作的字段建立索引
3. 为常作为查询条件的字段建立索引
4. 限制索引的数目
5. 尽量使用数据量少的索引
6. 数据量小的表最好不要使用索引
7. 尽量使用前缀来索引
8. 删除不再使用或者很少使用的索引		

7.索引失效的情况

1. 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
   index（name）;
   错误的例子：select * from test where name = 13333333333;
   需要对name先强转在比较
   100行数据 把100行数据的name属性分别取出来先强转在比较。
   原因：需要把所有的数据对应的name属性取出进行强转。
   需要将每一行数据都拿出来进行强换之后再比较。
   强转之后改变了原有字段的比较规则
   正确的例子：select * from test where name=‘13333333333’; 能用到索引

2. 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
   index(id)
   错误的例子：select * from test where id - 1 = 9;
   需要将每一个元组中的id值先取出，再减一然后和9再去比较
   正确的例子：select * from test where id=10;能用到索引
   select * from test where id = 9+1;

3. 使用MySQL内部函数导致索引失效.对于这样情况应当创建基于函数的索引.
   错误的例子：select * from test where 函数(id)=10; 说明，此时id的索引已经不起作用了
   max sum min …
   需要将每一个元组中的id值先取出,再通过函数计算，计算之后的值在和10比较

4. 以下使用会使索引失效，应避免使用；
   a. 使用 <> 、not in 、!=
   index(id)
   select * from id not in (12,13,15); 用不到 没有明确查询条件
   select * from id in (12,13,15); 能用到

   b. like “%_” 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
   %:任意个任意字符
   select * from name like ‘%a’; //由于字符串的最左比较法 用不到
   字符串的索引是如何建立：从左到右一个字符一个字符进行大小比较然后得出的B+树的结构。
   select * from name like ‘a%’; //由于字符串的最左比较法 能用到

5. 使用OR关键字查询语句
   查询语句的查询条件中只有OR关键字，且OR前后的两个条件中列都是索引时，查询中才会使用索引。否则，查询将不使用索引。

六、MySQL事务

多个事务同时操作时可能产生的问题：
脏读（Dirty Read）：一个事务读取了另一个事务未提交的数据。例如当事务A和事务B并发执行时，当事务A更新后，事务B查询读取到A尚未提交的数据，此时事务A回滚，则事务B读到的数据就是无效的脏数据。（事务B读取了事务A尚未提交的数据）
count : 40 B —> 60 rollback
A: select —> 60 40

只要没有commit 或者 rollback 事务都不算结束

不可重复读（NonRepeatable Read）：
一个事务的操作导致另一个事务前后两次读取到不同的数据。例如当事务A和事务B并发执行时，当事务B查询读取数据后，
事务A更新操作更改事务B查询到的数据，此时事务B再次去读该数据，发现前后两次读的数据不一样。
（事务B读取了事务A已提交的数据）

虚读（PhantomRead）/幻读：一个事务的操作导致另一个事务前后两次查询的结果数据量不同。
例如当事务A和事务B并发执行时，当事务B查询读取数据后，事务A新增或者删除了一条满足事务
B查询条件的记录，此时事务B再去查询，发现查询到前一次不存在的记录，或者前一次查询的一
些记录不见了。（事务B读取了事务A新增加的数据或者读不到事务A删除的数据

事务的隔离离性 事务隔离级别
不同的隔离级别解决不同的问题：
隔离级别

隔离级别 脏读 不可重复读 幻读
未提交读 可以 可以 可以
已提交读 不可以 可以 可以
可重复读 不可以 不可以 可以
串行化 不可以 不可以 不可以 效率最低

1. TRANSACTION_NONE。 表示不支持事务
2. TRANSACTION_READ_UNCOMMITTED。未提交读。
   说明在提交前一个事务可以看到另一个事务的变化。这样读”脏”数据，不可重复读和虚读都是被允许的。
3. TRANSACTION_READ_COMMITTED。已提交读。
   说明读取未提交的数据是不允许的。这个级别仍然允许不可重复读和虚读产生。
4. TRANSACTION_REPEATABLE_READ。可重复读。
   说明事务保证能够再次读取相同的数据而不会失败，但虚读仍然会出现。
5. TRANSACTION_SERIALIZABLE。可序列化/串行化。
   是最高的事务级别，它防止读脏数据，不可重复读和虚读。

隔离级别与不同隔离级别解决的问题；
准备工作：

create table ac_a(
id int primary key,
money int
);

insert into ac_a values(1,50);

create table ac_b(
	id int primary key,
	money int
);
insert into ac_b values(1,100);
insert into ac_b values(2,300);

(1)
设置mysql隔离级别为 未提交读：
A\B窗口
set session transaction isolation level Read uncommitted;
B窗口
begin;
update ac_b set money = 50 where id = 1;
update ac_a set money = 100 where id = 1;
A窗口
begin;
select * from ac_a;
B窗口
rollback;
A窗口
select * from ac_a;
rollback;

(2)设置隔离级别为已提交读
A\B窗口
set session transaction isolation level Read committed;
B窗口:
begin;
select * from ac_a;
A窗口
begin;
update ac_a set money = 60 where money = 50;
commit;
B窗口:
select * from ac_a;
rollback;
超市系统 ： 商品价格：
定时程序： 超时之后恢复原价 12：00

11 11 11:59:59 4个商品
1: 0.5 * 10 =
100

(3)设置隔离级别为可重复读 Repeatable read
update ac_a set money =50 where id = 1;
commit;
A\B窗口
set session transaction isolation level Repeatable read;
B窗口:
begin;
select * from ac_a;
A窗口
begin;
insert ac_a values(2,100);

commit;
B窗口：
select * from ac_a;
insert ac_a values(2,100);
rollback;

delete from ac_a where id = 2;
commit;
4.隔离级别设置为序列化 再操作上述
set session transaction isolation level Serializable;

三种引擎应该如何选择
MyISAM：默认的MySQL插件式存储引擎。如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常适合的。MyISAM是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。

需要锁 行锁 行锁 ：粒度 行锁的添加也更加消耗资源

InnoDB：用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询以外，还包括很多的更新、删除操作，那么InnoDB存储引擎应该是比较合适的选择。InnoDB存储引擎除了有效地降低由于删除和更新导致的锁定，还可以确保事务的完整提交（ Commit） 和回滚（ Rollback），对于类似计费系统或者财务系统等对数据准确性要求比较高的系统， InnoDB 都是合适的选择。采用事务日志，系统崩溃后易于数据的恢复。

MEMORY：将所有数据保存在RAM中，而且支持Hash索引，在需要快速定位记录和其他类似数据的环境下，可提供极快的访问。MEMORY 的缺陷是对表的大小有限制，太大的表无法 CACHE 在内存中 ，而且不支持BLOB,TEXT类型，而且使用的表级锁，并发性能低。现在很少使用，一般用于作为中间表保存中间数据。