数据库MySQL入门知识点（纯理论自留）

本文链接：https://blog.csdn.net/KUSPCE/article/details/132312495

🪷创建数据库：

create database  数据库名

🪷使用数据库：

use 数据库名

🪷创建数据库表

create table 表名(
    id int,
    name varchar(32),
    gender varchar(2)
);

补：数据类型

数值类型：

bit（M）范围1-64，默认值1，M指定位数

tinyint[UNSIGNED] 带符号的范围-128～127 无符号范围0～255 默认有符号

bool 0，1表示真假

smallint[unsigned] 带符号是-2^15次方～2^15-1，无符号是2^16-1

int[unsigned] 带符号是-2^31次方～2^31-1，无符号是2^32-1

bigint[unsigned] 带符号是-2^63次方～2^63-1，无符号是2^64-1

float[(M,D)][unsigned] M指定显示长度，d指定小数位数，占用4字节

double[(M,D)][unsigned] 表示比float精度更大的小数，占用8字节

decimal(M,D)[unsigned] 定点数M指定长度，D表示小数点的位数

文本，二进制类型：

char（size）固定长度字符串，最大255

varchar（size）可变长度字符串，最大长度65535

blob 二进制数据

text 大文本，不支持全文索引，不支持默认值

时间日期：

date/datetime/timestamp 日期类型（yyy-mm-dd）（yyy-mm-dd hh：mm：ss）时间戳

string类型：

enum 字符串对象

set 字符串对象

🪷表中插入数据：

insert into student (id,name,gender) values (1,'张三','男');
insert into student (id,name,gender) values (2,'王二','男');

🪷查询表中数据：

select * from 表名

存储引擎：数据库管理系统如何存储数据，如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。

查看存储引擎：

show engines

🪷库的操作：

🌼创建数据库：创建一个使用utf字符集，并带校对规则的db3数据库

create database db3 charset=utf8 collate utf8_general_ci
\\没有指定会默认以上这个样式，utf8_general_ci（不区分大小写）utf8_bin（区分大小写）

🌼查看系统默认字符集以及校验规则：

show variables like 'character_set_database';
show variables like 'collation_database';

🌼查看数据库支持的字符集和字符集校验规则：

show charset;
show collation;

🌼查看数据库：

show databases;

🌼显示创建语句：

show create database 数据库名;

补：查询的数据库创建语句说明

关键词尽量使用大写，但不是必须的;

数据库名字的反引号‘’，是为了防止使用的数据库刚好是关键词

/*!40100 default ......*/不是注释

🌼修改数据库：

对数据库的修改主要指是修改数据库的字符集，校验规则：

//将数据库字符集改为gbk
alter database 数据库名 charset=gbk;

🌼删除数据库：

删除后数据库内部看不到对应的数据库

对应的数据库文件夹被删除，级联删除，里面的数据表全部被删除

不要随便删除数据库

OROP DATABASE 数据库名

🌼备份和恢复--放到最后：

//备份  
mysqldump -P3306 -u root -p 密码 -B 数据库名> 数据库备份存储的文件路径
//备份把整个创建数据库，建表，导入数据的语句都装载这个文件中。
//备份多个
mysqldump -u root -p 数据库名 表1 表2 。。。 >D:mytest.sql
mysqldump -u root -p -B 数据库名1 数据库名2 。。。>数据库存放路径
//如果备份数据库没有带上-B参数，在恢复数据库时，需要先创建空数据库，然后使用数据库，再使用source还原
//还原
source D:/mysql-5.7.22/数据库备份存储的文件名称（mytest.sql）

🌼查看连接情况：

show processlist

补：可以告诉我们当前有哪些用户连接到我们的mysql，如果查出某个用户不是正常登陆的，可能数据库被人入侵了。

🪷表的操作：

🌼创建表：

create table 表名(
  列名 列类型
)character set 字符集 collate 校验规则 engine 存储引擎

//例子
create table users(
  id int,
  name varchar(20) comment '用户名',
  password char (20) comment '密码是32位的md5值',
  birthday date comment '生日'
)character set utf8 engine MyISAM;
//不同的存储引擎，创建表的文件不一样。
//users表存储引擎是MyISAM，在数据目中有三个不同的文件，分别是：
//users.frm：表结构
//users.MYD：表数据
//users.MYI：表索引

🌼查看表结构：

desc 表名;

🌼修改表：

//以例子形式呈现
//在users表添加二条记录
insert into users values (1,'a','b','1982-01-04'),(2,'b','c','1984-01-04');
//在users表添加一个字段，用于保存图片路径
alter table users add assets varchar(100) comment '图片路径' after birthday;
//修改name，将其长度改为60
alter table users modify name varchar(60);
//删除password列
alter table users drop password;
//修改表名为employee
alter table users rename to employee;//这里to可以不加
//将name列修改为xingming
alter table employee change name xingming varchar(60);//新字段需要完整定义
//删除表
drop table 表名

🪷数据类型：

//举几个例子
//可以通过unsigned来说明某个字段是无符号的
create table tt2(num tinyint unsigned);
//bit字段在显示时，是按照ASCLL码对应的值显示
//只存放0或1，可以定义bit（1)
//float(4,2)表示的范围是-99.99~99.99      m表示总位数
//decimal的精度比float更准确，float表示的精度大约是7位，decimal最大位数是30
//如果数据确定长度都一样，就使用定长（char），比如：身份证，手机，md5
//如果数据长度有变化，就使用变长（varchar），比如：名字，地址
//定长的磁盘比较浪费，但是效率高。变长的磁盘空间比较节约，但是效率低
//timestamp时间戳，从1970年开始的yyy-mm-dd hh：ii：ss 和datetime完全一样，不需要手动insert
//              值，时间戳自动补上当前时间，更新数据，时间戳会更新成当前时间
create table votes(
  hobby set('登山','游泳','篮球','武术')，
  gender enum('男','女')
);
insert into votes values('雷锋','登山','武术','男');
insert into votes values('Juse','登山','武术','2');
//集合查询使用find_in_set
select * from votes where find_in_set('登山'，hobby);

🪷表的约束：

🌺空属性：

两个值：null和not null

数据库默认字段基本都是字段为空，但是实际开发时，尽可能保证字段不为空，因为数据为空没办法参与计算

create table myclass(
  class_name varchar(20) not null,
  class_name varchar(10) not null
);

🌺默认值：

关键词：default

某一种数据会经常性的出现某个具体的值，可以在一开始就指定好，在需要真实数据的时候，用户可以选择性的使用默认值

create table tt10(
  name varchar(20) not null,
  age tinyint unsigned default 0,
  sex char(2) default '男'
);

🌺列属性：

关键词：comment

没有实际含义，专门用来描述字段

create table tt12(
  name varchar(20) not null comment '姓名',
  age tinyint unsigned default 0 comment '年龄'，
  sex char(2) default '男' comment '性别'
);
//注意  not null 和 default一般不需要同时出现，因为default本身有默认值，不会为空
//通过desc查看不到注释，只能通过show create table tt12查看建表信息

🌺zerofill：

alter table tt3 change a a int(5) unsigned zerofill;//这里注意回顾一下alter关键词
//这里没加zerofill之前a可能为1，加了之后int（5）这里的5开始起作用了，a会显示00001，这就是
//zerofill属性的作用，如果宽度小于设定的宽度（这里设置的是5），自动填充0，这只是最后显示的结果，在
//MySQL中实际存储的还是1。

🌺主键：

关键词：primary key

用来约束字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键，但是可以多个列类型都是主键，主键所在的列通常是整数类型

create table tt13(
  id int unsigned primary key comment '学号不能为空',
  name varchar(20) not null
);

desc查表时key中pri表示该字段是主键

当表创建好以后但是没有主键的时候，可以再次追加主键

alter table 表名 add primary key(字段列表);
//删除主键
alter table 表名 drop primary key;

复合主键：

create table tt14(
  id int unsigned,
  course char(10) comment '课程代码',
  score tinyint unsigned default 60 comment '成绩',
  primary key(id,course)//id和course为复合主键
);

🌺自增长：

关键词：auto_increment

当对应的字段，不给值，会自动被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同的值，通常和主键搭配使用，作为逻辑主键

自增长的特点：

任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）

自增长字段必须是整数

一张表最多只能有一个自增长

create table tt21(
  id int unsigned primary key auto_increment,
  name varchar(10) not null default ''
);

🌺唯一键：

关键词：unique

唯一键允许为空，而且可以多个为空，空字段不做唯一性比较。（这里建议把主键设置成为和当前业务无关的字段，当业务调整的时候，可以尽量不会对主键做过大的调整）

create table student(
  id char(10) unique comment '学号，不能重复，但是可以为空',
  name varchar(10)
);

🌺外键：

关键词：foreign key (字段名) references 主表（列）

外键用于定义主表和从表之间的关系，外键约束主要定义在从表上，主表则必须有主键约束或unique约束。当定义外键后，要求外键列数据必须在主表的主键列存在或者为null

//先创建主键表
create table myclass(
  id int primary key,
  name varchar(30) not null comment '班级名'
);
//再创建从表
create table stu(
  id int primary key,
  name varchar(30) not null comment '学生名',
  class_id int,
  foreign key (class_id) reference myclass(id)
);

🪷表的增删查改：

🌺多行插入：

关键词：insert into

insert into students(id,sn,name) values(102,20001,'曹梦的'),(103,20002,'孙重重');

🌺插入否则更新：

关键词：on duplicate key update

当主键和唯一键对应的值已经存在而导致插入失败

insert into students (id,sn,name) values (100,100001,'唐大师') on duplicate key update sn=100001,name='唐大师';
//0 row affected 表中有冲突数据，但是冲突数据的值和update的值相等
//1 row affected 表中没有冲突数据，数据被插入
//2 row affected 表中有冲突数据，并且数据已经被更新
//on duplicate key当发生重复key的时候

🌺替换：

关键词：replace

主键或者唯一键没有冲突，则直接插入

主键或者唯一键如果冲突，则删除后再插入

replace into students (sn,name) values (20001,'曹');

🌺查询表：

🌺select：通常情况下不建议使用*进行全列查询

指定列查询：指定列的顺序不需要按定义表的顺序来

select id,name,english from 表名

为查询结果指定别名：

select id,name,chinese+math+english 总分 from 表名

结果去重：

关键词：distinct

select distinct math from 表名

🌺where条件：

这里只罗列几个不一样的

<==>：等于，NULL安全

！=,<>：不等于

between a0 and a1：范围匹配左闭右闭

in（option,....）：如果是option中的任意一个，返回对

like：模糊匹配。%表示任意多个（包括0）任意字符，_表示任意一个字符

逻辑运算符：

and or not

//以例子来展示
//英语不及格的同学及英语成绩<60
select name,english from 表名 where english<60
//语文成绩在[80，90]分的同学及语文成绩
select name,chinese from 表名 where chinese >=80 and chinese<=90
//between   and
select name,chinese from 表名 where chinese between 80 and 90
//数学成绩是58或者59或者98或者99的同学及数学成绩
select name,math from 表名 where math in(58,59,98,99)
//姓孙的同学及孙某同学
select name from 表名 where name like '孙%';
//匹配严格的一个任意字符
select name from 表名 where name like '孙_';
//语文成绩好于英语成绩的同学
select name,chinese,english from 表名 where chinese>english
//语文成绩>80并且不性孙
select name,chinese from 表名 where chinese>80 and name not like '孙%';
//孙某，否则要求总成绩>200并且语文成绩小于数学成绩并且英语成绩大于80   ---这里where中不能用别名
select name,chinese,math,english,chinese+math+english 总分 from 表名 where name like '孙_' or(chinese+math+english>200 and chinese<math and english>80);

🌺结果排序：

关键词：order by

asc为升序（默认），desc为降序

//同学及数学成绩，按数学成绩升序显示
select name,math from 表名 order by math;
//同学及qq号，按qq号排序显示
//NULL视为比任何值都小
select name,qq from students order by qq;
select name,qq from students order by qq desc;
//查询同学个门成绩，依次按数学降序，英语升序，语文升序的方式显示、
select name,math,english,chinese from 表名 order by math desc,english,chinese;
//查询同学及总分，由高到低
select name,chinese+english+math from 表名 order by chinese+english+math desc;
//order by 子句中可以使用列别名
select name,chinese+english+math 总分 from 表名 order by 总分 desc;
//查询姓孙的同学或者姓曹的同学的数学成绩，结果按数学成绩由高到底显示
select name,math from 表名 where name like '孙%' or name like '曹%' order by math desc;

🌺筛选分页结果：

关键词：limit

//起始下标为0
//从0开始，筛选n条结果
select .... from 表名 where ... order by .... limit n;
//从s开始，筛选n条结果
select .... from 表名 where ... order by .... limit s,n;
//从s开始，筛选n条结果，比第二种用法更明确，建议使用
select .... from 表名 where ... order by .... limit n offset s;
//对未知表进行查询时，最好加上一条limit 1，避免因为表中数据过大，查询全表数据导致数据库卡死，按id
//进行分页，每页3条记录，分别显示1、2、3页
select id,name,math,english,chinese from 表名 order by id limit 3 offset 0;
select id,name,math,english,chinese from 表名 order by id limit 3 offset 3;
select id,name,math,english,chinese from 表名 order by id limit 3 offset 6;

🌺更新：

关键词：update...set

//将孙悟空的数学成绩变更为80
upate 表名 set math=80 where name='孙悟空';
//将曹孟德的数学成绩变更为60，语文成绩变更为70
update 表名 set math=60,chinese=70 where name='曹孟德';
//将总成绩倒数前三的3位同学的数学成绩加上30分
//数据更新不支持math+=30
update 表名 set math=math+30 order by chinese+math+english limit 3;
//将所有同学的语文成绩更新为原来的2倍
update 表名 set chinese=chinese*2

🌺删除：

关键词：delete from 表名

//删除孙悟空的考试成绩
delete from 表名 where name='孙悟空';
//删除整张表数据
delete from 表名;
//这里删除，再自增id在原值上增长,比如原本有3个值，删完了，再插入新值，那个auto_increment会从4开始

🌺截断表：

关键词:truncate

补:这个操作只能对整表操作，不能像delete一样针对部分数据操作

会重置auto_increment的值

truncate 表名;
//这里截断整表数据，注意影响行数是0，所以实际上没有对数据真正操作

🌺插入查询结果：

//案例：删除表中的重复记录，重复的数据只能有一份
//创建原数据表
create table old_table(
  id int,name varchar(20)
);
//插入测试数据
insert into old_table values (100,'aaa'),(100,'aaa'),(200,'bbb');
//创建一张空表，和old结构一样
create table new_table(
  id int,name varchar(20)
);
//将old_table的去重数据插入到new_table中
insert into new_table select distinct * from old_table;
//通过重命名表，实现原子的去重操作
rename table old_table to false_table,new_table to old_table;

🌺聚合函数：

count([DISTINCT] expr) 返回查询到的数据的数量

sum([DISTINCT] expr) 返回查询到的数据的总和，不是数字没有意义

avg([DISTINCT] expr) 返回平均数

max([DISTINCT] expr)

min([DISTINCT] expr)

//统计班级共有多少同学 不受NULL影响
select count(1) from student; 
select count(*) from student; 
//统计班级收集的qq号有多少
select count(qq) from student;
//统计本次考试的数学成绩分数个数
select count(math) from 表名
//统计去重成绩数量
select count(distinct math) from 表名;
//其他比较简单，不做例子

🌺group by：

在select中使用group by子句可以对指定列进行分组查询

补：在sql语句中，使用as可以对字段、表等取别名

//EMP员工表
//DEPT部门表
//SALGRADE工资等级表
//如何显示每个部门的平均工资和最高工资  这里group by对员工列进行按照部门分组查询
select deptno,avg(sal),max(sal) from EMP group by deptno;
//显示每个部门的每种岗位的平均工资和最低工资
select avg(sal),min(sal),job,deptno from EMP group by deptno,job;
//显示平均工资低于2000的部门和它的平均工资
//统计各个部门的平均工资
select avg(sal) from EMP group by deptno
//having 和group by配合使用，对group by结果进行过滤
select avg(sal) as myavg from EMP group by deptno having myavg<2000;
//having经常和group by搭配使用，作用是对分组进行筛选，作用有些像where

补：关键字的执行先后顺序 from(+表名)>on(连接)>join(交集和联合这种功能)>where(+筛选条件)>group by(+指定列分组筛选)>with(别名Mysql不能用)>having(和分组筛选一起用，像where)>select(查找)>distinct(去重)>order by(+排序依据字段。默认阿asc，desc自己设置)>limit(+从s开始筛选n条记录offset)

🪷函数：

🌷日期函数：

current_date() 当前日期

current_time() 当前时间

current_timestamp() 当前时间戳

date（datetime）返回datetime参数的日期部分

date_add(date,interval d_value_type) 在date中添加日期或者时间 interval后的数值单位可以是：year minute second day

date_sub(date,interval d_value_type) 在date中减去日期或者时间

datediff（date1，date2）两个日期的差，单位是天

now（）当前日期时间

//请查询在2分钟内发布的帖子
select * from 表名 where date_add(sendtime,interval 2 minute)>now();

🌷字符串函数：

charset(str) 返回字符串字符集

concat(string2 [....]) 连接字符串

instr(string,substring) 返回substring在string中出现的位置，没有返回0

ucase(string2) 转换成大写

lcase(string2) 转换成小写

left(string2,length) 从string2中的左边起取length个字符

length(string) string的长度

replace(str,search_str,replace_str) 在str中用replace_str替换search_str

strcmp(string1,string2) 逐字符比较两字符串大小

substring(str,position [,length]) 从str的postion开始，取length个字符

ltrim(string) rtrim(string) trim(string) 去除前空格或后空格

🌷数学函数：

abs(number) 绝对值函数

bin(decimal_number) 十进制转换二进制

hex(decimalNumber) 转换成16进制

conv(numbei,from_base,to_base) 进制转换

ceiling(number) 向上取整

floor(number) 向下取整

format(number,decimal_places) 格式化，保留小数位数//format(5.222,2)，不要给它重命名，这是最后一步

rand() 返回随即浮点数，范围[1.0,1.0)

mod(number,denominator) 取模，求余

//最小基数为2，最大基数为36.如果要转换的基数为负数，则该数字被视为带符号数。否则，它被视为未签名。
//num可以是int型也可以是string类型，from_base表示原来的进制，to_base表示要转成的进制，返回的结
//果是string类型。如果num有NULL，那么conv的结果也是NULL。
select conv('100',2,10)
>4
select conv(-10,16,-10)
>-16
select conv（19,10，-16）
>13

🌷其他函数：

user（）查询当前用户

md5（str）对一个字符串进行md5摘要，摘要后得到一个32位字符串

database（）显示当前正在使用的数据库

password（） MySQL数据库使用该函数对用户加密

ifnull（val1，val2）如果val1为null，返回val2，否则返回val1的值

补：md5（）函数为字符串算出一个md5 128比特校验和，该值以32位十六进制数字的二进制字符串的形式返回，若参数为NULL则会返回NULL，一种数据加密手法

🪷复合查询（重点）：

//基本查询回顾
//查询工资高于500或岗位为MANAGER的雇员，同时还满足他们的姓名首字母为大写的J
select * from EMP where (sal>500 or job='MANAGER')and ename like 'J%';
//按照部门号升序而员工工资降序排序
select * from EMP order by deptno,sal desc;
//使用年薪进行降序排序
select ename,sal*12+ifnull(comm,0) as '年薪' from EMP order by 年薪 desc;
//显示工资最高的员工的名字和工作岗位
select ename,job from EMP where sal =(select max(sal) from EMP);
//显示工资高于平均工资的员工信息
select ename,sal from EMP where sal>(select avg(sal) from EMP);
//显示每个部门的平均工资和最高工资
select deptno,format(avg(sal),2),max(sal) from EMP group by deptno;
//显示平均工资低于2000的部门号和它的平均工资
select deptno,avg(sal) as avg_sal from EMP group by deptno having avg_sal>2000;
//显示每种岗位的雇员总数，平均工资
select job,count(*),format(avg(sal),2) from EMP group by job;

🌷多表查询：

解决多表查询：

1.先读题，确定都和那些表有关

2.'“无脑”组合形成一张表，能加where的加---多表转成一张表

3.将多表查询，看做成为一张表的查询

select * from EMP,DEPT;
//1.从第一张表中选出第一条记录，和第二个表的所有记录进行组合
//2.然后从第一张表中取第二条记录，和第二张表中的所有记录组合
//3.不加过滤条件，得到的结果称为笛卡尔积

//显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP、DEPT表，因此要联合查询
select EMP.ename,EMP.sal,DEPT.dname from EMP,DEPT where EMP.deptno=DEPT.deptno;
//显示部门号为10的部门名，员工名和工资
select ename,sal,dname from EMP,DEPT where EMP.deptno=DEPT.deptno and DEPT.deptno=10;
//显示各个员工的姓名，工资，及工资级别
select ename,sal,grade from EMP,SALGRADE where EMP.sal between losal and hisal;

🌷自连接：

自连接是指在同一张表连接查询

select * from dept,dept;//这个操作不行
select * from dept as dt1,dept as dt2;//这个操作行

案例：

//显示员工FORD的上级领导的编号和姓名
//1.使用子查询-----先获得该员工领导的编号，再去找他的姓名，一起显示
select empno,ename from emp where emp.empno=(select mgr from emp where ename='FORD');
//2.使用多表查询（自查询）-----自己和自己拼一张表，重命名一下
select leader.empno,leader.ename from emp (as) leader,emp (as) worker where leader.empno=worker.mgr and worker.ename='FORD';

🌷子查询：

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询------用多个select查询

//单行子查询-----返回一行记录的子查询-----where后面只有一行满足条件（等于）
//显示SMITH同一部门的员工
select * from EMP where deptno=(select deptno from EMP where ename='smith');
//多行子查询-----返回多行记录的子查询-----where后面多行满足条件（in\all\any）
//in关键字:查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包括10自己的
select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno<>10;
//all关键字：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
select ename,sal,deptno from EMP where sal>all(select sal from EMP where deptno=30);
//any关键字：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
select ename,sal,deptno from EMP where sal>any(select sal from EMP where deptno=30)
//多列子查询------返回多个列数据-----什么叫返回子查询，是里面那个select的where有多列
//查询和SMITH的部门和岗位完全相同的所有雇员，不包含SMITH本人
select ename from EMP where (deptno,job)=(select deptno,job from EMP where ename='SMITH') and ename<>'SMITH';

//some=any
//not in  与<>any 用法相同
//in 与=any用法相同

🌷在from子句中使用子查询：

相当于把另一个表缩水一下，再和其他表拼接（穷举）

//把一个子查询当做一个临时表使用-----where中不能使用别名，MySQL中grop by可以使用别名
//显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
select ename,deptno,sal,fromat(asal,2) from EMP,(select avg(sal) asal,deptno dt from EMP grop by deptno) tmp where EMP.sal>tmp.asal and EMP.deptno=tmp.dt;
//查找每个部门工资最高的人的姓名、工资、部门、最高工资
select EMP.ename,EMP.sal,EMP.deptno,ms from EMP,(select max(sal) ms,deptno from EMP group by deptno) tmp where EMP.deptno=tmp.deptno and EMP.sal=tmp.ms;

//显示每个部门的信息（部门名、编号、地址）和人员数量
//1.使用多表
select DEPT.dname,DEPT.deptno,DEPT.loc,count(*) '部门人数' from EMP,DEPT where EMP.deptno=DEPT.deptno group by DEPT.deptno,DEPT.dname,DEPT.loc;
//这里分组要加上DEPT.dname,DEPT.loc的原因可能是select DEPT.dname,DEPT.deptno,DEPT.loc这
//里怕不显示（不一定对）
//2.使用子查询
//（1）对EMP表进行人员统计
select count(*),deptno from EMP group by deptno;
//(2)将上面的表看作临时表
select DEPT.deptno,dname,mycnt,loc from DEPT,(select count(*) mycnt,deptno from EMP group by deptno) tmp where DEPT.deptno=tmp.deptno;

🌷合并查询：

关键字：union、union all

1.union---该操作符用于取得两个结果集的并集。会自动去掉结果集合中的重复行

//将工资大于2500或职位是MANAGER的人找出来-----并集
select enamee,sal,job from EMP where sal>2500 union select ename,sal,job from EMP where job='MANAGER';---union可以去掉重复记录

2.union all

取得两个结果集的并集，不会去掉结果集合中的重复行

select ename,sal,job from EMP where sal>2500 union all select ename,sal,job from EMP where job='MANAGER';-----不会去除掉重复行

🪷表的内连和外连（重点）：

内连接：利用where子句对两种表形成的笛卡尔积进行筛选，前面学到的都是内连接

关键词：inner join...on=...where

//两种写法
select ename,dname from EMP,DEPT where EMP.deptno=DEPT.deptno and ename='SMITH';
select ename,dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and ename='SMITH';

外连接：

分为左外连接和右外连接，左侧的表完全显示就是左外连接（左侧表+和右侧表的交集），右侧表完全显示就是右外连接（右侧表+和左侧表的交集）

//案例
create table stu(id int,name varchar(30));
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam(id int,grade int);
insert into stu values(1,56),(2,76),(11,8);
//查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来
//当左边表和右边表没有匹配时，也会显示左边表的数据
select * from stu left join exam on stu.id=exam.id;
//对stu和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应
select * from stu figth join exam on stu.id=exam.id;

🪷索引（重点）：

关键词：index

建立索引就是把底层存储结构变成B+

alter table EMP add index(empno);

磁盘：磁头、柱面（磁道）、扇区

系统读取磁盘，是以快为单位，基本单位是4KB

MySQL进行IO的基本单位是16KB（page）

（IO单位小，意味着读取同样的数据内容，需要进行多次磁盘访问，会带来效率的降低;文件系统读取基本单位--数据块，系统读取磁盘是以块为单位，基本是4KB）

对文件的操作，都不是直接在磁盘设备上做操作的，要通过指令，CUP，内存;任何磁盘数据，在进程中要进行操作，本质都必须在内存中进行，如果数据不在内存中，就需要换入、换出;mysql内部一定要有自己的内存管理。

MySQL中的数据文件，是以page为单位保存在磁盘当中的;MySQL的CURD操作，都需要通过计算，找到对应的插入位置，只要涉及到计算，就需要CPU参与，为了便于CPU参与，一定要先将数据移动到内存当中，所以在特定的时间内，数据一定是磁盘中有，内存也有，后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘，就涉及到磁盘和内存的数据交互，就是IO了，单位是page;MySQL服务器在内存运行的时候，会申请buffer pool的大内存空间，来进行各种缓存;为了提高效率，一定要尽可能的减少系统和磁盘IO的次数。

（为何要采用page的方案，而不是用多少加载多少？如果MYSQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，就需要2次IO，如果要找id=5，就需要5次IO。但是如果这五条数据都保存在了一个page中（16KB），能保存很多记录），那么第一次IO查找id=2的时候，整个page会被加载到MYSQL的buffer pool中，这里完成了一次I后面查找id=2、3、4、5就不需要IO了，而是直接在内存中进行了。

如何可以一下就找到id=5的值，不从id=1开始找------------目录

底层是B+树，哈希无法做范围查找，哈希更支持单项的

InnoDB BTREE

MyISAM BTREE

MEMORY/HEAP HASH,STREE

NDB HASH,BTREE

B树和B+树区别：

B树节点，既有数据又有page指针，而B+，只有叶子节点有数据，其他目录页，只有键值和page指针

B+叶子节点全部相连，B没有

为何选择B+：

叶子节点不存储date，一个节点可以存储更多的key（id，page_address），一个目录页一次可以管理更多的下级page，可以使树更矮，IO操作次数更少;

叶子节点相连，更便于进行范围查找

聚簇索引和非聚簇索引：

非聚簇索引：用户数据与索引数据分离的索引方案属性和内容分离

MyISAM-------主键索引

叶子节点的date域存放的是数据记录的地址

user.frm

user.MYD

user.MYI

MYISAM的辅助索引（普通索引）：和主键索引没有差别，主要是主键无法重复，非主键可以重复

聚簇索引：用户数据与索引数据在一起的索引方案属性+内容

InnoDB-------主键索引

user.frm

user.ibd

InnoDB的辅助索引（普通索引）：非主键索引中叶子节点没有数据，而只有对应记录的key值

所以通过辅助索引，找到目标记录，需要两遍索引:首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录，这种过程，就叫回表查询。

为啥InnoDB辅助索引不给叶子节点赋值？每次新建一个索引都会新增一个多阶B+树，如果赋值会造成数据冗余，浪费空间。

一张表不仅仅只有一个索引结构。

当我们建立表结构的时候，如果有主键，默认mysql会为我们构建主键索引。

主键索引：

1.具有主键的表，一个表一个B+;

2.如果表中没有主键，mysql会自动形成隐藏主键;

3.B+中所有的叶子节点、路上节点不需要全部加载到内存，按需进行load mysql page;

4.主键索引的列基本上是int。

唯一索引的特点：

1.一个表可以有多个唯一索引;

2.查询效率高;

3.如果某一列建立唯一索引，必须保证这列不能有重复数据;

4.如果一个唯一索引上指定not null，等价于主键索引。

普通索引的创建：

//第一种------在表的定义最后，指定某列为索引
create table user8(
  id int primary key,
  name varchar(20),
  email varchar(30),
  index(name）
);
//第二种方法--------创建完表以后指定某列为普通索引
create table user9(
  id int primary key,
  name varchar(20),
  email varchar(30));
alter table user9 add index(name);
//第三种-------创建一个索引名为idx_name的索引
create table user10(
  id int primary key,
  name varchar(20),
  email varchar(30));
create index idx_name on user10(name);

普通索引的特点：

1.一个表中可以有多个普通索引;

2.如果某列有重复值，需要创建索引，使用普通索引

全文索引：

要求表的存储引擎必须是MyISAM，而且默认支持英文，不支持中文（可以使用spinx的中文版coreseek）。

explain select * from 表名 where body like '%database%'
//explain可以查看是否使用索引
//key为null表示没有用到索引
//使用全文索引
select * from 表名 where match(title,body) against（'database'）;
//查询索引
//1.show keys from 表名
//2.show index from 表名
//3.desc 表名（信息比较简略）
//删除索引
//删除主键索引
alter table 表名 drop primary key;
//删除其他索引
alter table 表名 drop index 索引名 
//索引名就是show keys from表名中的Key_name字段
alter table user10 drop index idx_name;
drop index 索引名 on 表名

索引创建原则：

1.比较频繁作为查询条件的字段应该创建索引

2.唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

3.更新非常频繁的字段不适合作创建索引

4.不会出现在where子句中的字段不该创建索引

🌺事务：

多条Mysql语句操作合起来，就构成了一个事务

事务四个属性：

原子性（A）：要么全部完成，要么全部不完成

一致性（C)：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。

隔离性 (I)：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致的数据不一致。事务隔离级别：读未提交、读提交、可重复读、串行化

持久性 (D)：事务处理结束后，对数据的修改就是永久的。

事务本质上是为了应用层服务

在mysql中，只有使用了Innodb数据库引擎的数据库或表才支持事务，MyISAM不支持

查看数据库引擎：

show engines;//表格显示
show engines \G//行显示
//Transactions:YES-----------支持事务
//Savepoints:YES---------支持事务保存点

事务提交方式：

1.自动提交

2.手动提交

查看事务提交方式：

show variables like 'autocommit'; 
//autocommit----ON 表示自动提交

用set来改变mysql的自动提交模式：

set autocommit=0;//禁止自动提交
set autocommit=1;//开启自动提交

以下为了演示，将mysql的默认隔离级别设置成读未提交

set global transaction isolation level READ uncommitted;

重启终端进行查看：

select @@tx_isolation;

事务回滚：

start transaction;//开始一个事务begin也可以
savepoint save1;//创建一个保存点save1
rollback to save2;//回滚到save2之前
rollback;//直接rollback回滚到最开始
//未commit，客户端崩溃，mysql会自动回滚（隔离级别设置为读未提交）

只要输入begin或者start transaction，事务便必须要通过commit提交，才会持久化，与是否设置set autocommit无关。

事务隔离级别：

为了保证事务执行过程中尽量不受干扰，就有了：隔离性

允许事务受不同程度的干扰，就有了：隔离级别

读未提交：所有事务都能看见其他事务没有提交的执行结果（相当于没有任何隔离性）

读提交：一个事务只能看到其他的已经提交的事务所做的改变。

可重复读：（是mysql默认的隔离级别）只能在这个事务退出之后才能看见别人提交后的事务，同一个事务在执行中，多次读会看见同样的数据。

串行化：在每个读的数据行上面加上共享锁。

查看隔离级别：

select @@global.tx_isolation;//查看全局隔离级别
select @@session.tx_isolation;//查看会话（当前）全局隔离级别
select @@tx_isolation;//默认同上

设置当前会话or全局隔离级别语句

set [session|global] transaction isolation level {read uncommitted|read committed | repeatable read|serializable};

读已提交和可重复读的区别：前者a要是commit之后，马上B就能读到，后者a要是commit之后，B要退出它当前事务，才能看见a的新值。

在RR（可重复读）级别，update，insert，delete之间是会有加锁现象的。

MVCC：是一种用来解决读-写冲突的无锁并发控制

为事务分配单向增长的事务ID，为每个修改保存一个版本，版本与事务ID关联，读操作只读该事务开始前的数据库的快照。

undo log是在事务还没提交之前使用的。

🌺视图：视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。视图的数据变化会影响到基表，基表的数据变化也会影响到视图。

创建视图：

create view 视图名 as select 语句
//案例
create view v_ename_dname as select ename,dname from EMP,DEPT where EMP.deptno=DEPT.deptno