【MySQL基础】带你速通MySQL

前言

第一篇博客,希望能坚持写下去,用来复习所学过的知识以及分享给大家.

此博客用到的表

一.基础概念

1.数据库的基础概念

2.登录mysql

3.SQL语句分类

4.进入数据库

1.查看所有数据库

2.创建数据库(DDL语句)

3.删除数据库(DDL语句)

4.使用数据库

5.查看此数据库中的所有表

6.查看表中所有内容(DQL语句)

7.查看表结构

8.查看当前使用的是哪个数据库

5.快捷键

\c 结束这个语句重新输入

\q 退出mysql

system cls; 清屏

二.DQL查询语句

不会改变表结构以及表数据

格式:select 查询字段 from 查询表名;

select * 为查询全部字段

1.where条件查询

格式:select 查询字段 from 查询表名 where 字段条件;

1.1简单条件查询

1.2between and

等价于>= and <=

1.3is null

当字段数据为NULL时,不能用=查询,只能用is null或者is not null查询,因为NULL是空,表示什么都没有,没有数值

1.4and or in

and表示并且,左右都满足查询,不同字段查询

---

or

相同字段查询,表和,满足左和右的都查出来

不同字段查询,表或者,只要满足其中一个就查询

and和or同时出现,and优先级高,要让or先执行要加()

---

in

和or作用相同,用在相同字段查询

注意:
in后面不是一个区间,而是一个具体的值,括号里可以有很多的值
查询范围的话用between and

1.5like模糊查询

%匹配任意多个字符
_匹配任意一个字符

当需要找到精确位置的时候用_,注意个数

拓展:
模糊查询避免%出现在开头,会造成索引失效

2.order by 排序

格式:select 查询字段 from 查询表名 order by 排序字段;

升序:order by 字段名 asc; (默认升序,可以不写asc)

降序:order by 字段名 desc;

多个字段排序

先按照sal升序排列,如果有相等的sal,再根据ename的升序去排(首字母)
有先后顺序

3.其他关键字

3.1distinct去重

用在select 查询字段前,去除重复出现的字段数据

一般和count()函数连用,用来统计数量,保证数据不重复

3.2union拼接

就是把两个select查询结果进行拼接

union拼接注意:
1.两个select后面的字段数要一致
2.字段的数据类型建议一致,因为Oracle语法不可以,但mysql可以

4.查询函数

4.1单行处理函数

一个输入对应一个输出,每个数据都会输出一个不同的值

null只要参与了运算,最终结果一定是null.所以需要用到ifnull函数
ifnull(字段,修改值)

4.2多行处理函数

也被称为分组函数

多个输入对应一个输出

分组函数不能直接使用在where后面

分组函数必须先分组,后执行,如果不分组,整张表为一组

一般和分组查询连用

4.3和时间相关的函数

1.知道出生日期，如何求出其年龄？

timestampdiff(year, 时间类型字段, curdate());

2.获取当前时间

now();

5.group by分组查询

查询每个工作岗位的平均薪资

查询每个工作岗位的最高薪资

查询每个工作岗位(分组字段)的工资和(分组函数)...........

在一条select语句当中,如果有group by语句的话,select后面只能跟:参加分组的字段,以及分组函数

可以进行联合分组

联合:把两个字段看成一个字段

---

having(类似于where条件查询)

找出每个部门最高薪资,要求显示最高薪资大于3000的?

1.先分组,后用having对分组后数据过滤,必须和group by联合使用

2.先用where过滤后再group by 分组查询(效率高于having)

注意where不能用分组函数

6.连接查询

两个有关系的表进行连接查询

格式:

select 查询字段

from 表1 join 表2

on 表1和表2的连接条件

6.1起别名

1.在表连接中,给两张表起别名

from 表名1 别名1 join 表名2 别名2

2.在select中起别名,一般用在分组函数后

select 分组函数 as 别名

6.2等值连接

on条件相等,一般为外键

6.3非等值连接

6.4自连接

一张表看成两张表

查询员工的上级领导,要求显示员工名和对应的领导名字

缺点,king是老板所以没有领导,所以不会显示,只有13条记录,需要外连接

6.5外连接

内连接是平等
外连接有主次关系

右外连接right join
以右边的表为主表查询,将右边的表中的数据全部查询出来,捎带关联查询左边的表

左外连接left join 同理

可以用左外连接解决了king没有领导的问题,会显示14条记录,king的领导为NULL

6.6多表连接

一个from 多个join,on永远是第一个表和连接的表的条件

7.子查询

select语句中嵌套select语句

子查询可以出现在哪里呢?

1.where中子查询

因为where后不能有分组函数

2.from中子查询

第一步:找出每个岗位的平均薪资(按照job分组求avg)

第二步:把以上的查询结果当作一张表t,与salgrade表进行表连接

注意:
1.select要直接查询t表的全部结构*
2.要给t表的avg(sal)起别名,因为要在on后只能用别名,用avg(sal)会被判断为函数报错

8.limit截取

截取数据,分页查询

limit 起始下标,截取长度;
起始下标从零开始,省略就是从第一个开始截取

9.select语句顺序总结

三.DDL建表语句

1.数据类型

字符串类型

char
定长,长度指定为n,不够用空格补齐,效率高,但使用不当会浪费空间
varchar
变长,长度最多为n,会自动根据输入长度改变,效率偏低,但节省空间

---

数值类型

---

日期类型

date 年月日
time 时分秒
year 只有年
datetime 日期+时间

2.创建表结构

create table 表名称(
字段1 类型 ,
字段2 类型 ,
字段3 类型
) ;
注意:创建表的时候,char和varchar要给定括号里的值

删除表
drop table 表名;
drop table if exists 表名;(语句健壮,不会报错)

3.修改表结构

添加字段
alter table 表名 add 字段名 字段类型 ;

修改字段
1.修改数据类型
alter table 表名 modify 字段名 新字段类型;
2.修改字段名+类型
alter table 表名 change 老字段名 新字段名 新字段类型;

删除字段
alter table 表名 drop 字段名;

修改表名
alter table 老表名 rename to 新表名;

四.DML对表中数据的增删改

1.insert into 增

给指定字段添加数据
insert into 表名 (字段名1,字段名2,...) values(数据1,数据2,....);

省略字段名会按顺序添加数据,没给值默认为NULL

插入多条记录
insert into 表名 (字段名1,字段名2,...) values(数据1,数据2,....),(数据1,数据2,...);

2.delete from 删

delete from 表名 where 条件;

没有where条件会把整个表删除

当没有where条件时删除整张表和drop table 的区别

delete from table
实际删除了数据,但内存还在,不会被释放
效率低,但支持回滚

truncate table(属于DDL建表语句)
不能加where条件,删除所有表中数据,但保留表结构
效率高,但不支持回滚

drop table(DDL)
删除整个表,表结构也没有

3.update set 改

update 表名 set 字段名1=修改值,字段名2 = 修改值 where 条件;

没有where条件会把当前字段所有数据都修改了

五.约束

1.非空约束

not null

被not null约束的字段不能为空

只有列级约束,就是只能一个一个约束,不能联合约束

2.唯一性约束

unique

被唯一性约束的字段不能重复,但可以有多个NULL

唯一性约束可以联合约束**(表级约束)**

注意,联合唯一和各自唯一不同

3.主键约束

primary key

既唯一unique又非空not null的字段就会自动变成主键字段(只有mysql这样)

理解为主键就是一行数据的身份证

可以复合主键但实际开发不建议,会产生**部分依赖,**只建议单一主键

一张表主键约束只能有一个

主键的维护:自增

auto_increment

自增属性一般用在主键字段上,不建议用在其他字段

设置自增后insert into 就不需要给id默认值

给表结构添加默认值

default

create table 表名称(
字段1 类型 ,
gender char(1) default '男' ,
字段3 类型
) ;

4.外键约束

两张表有关系的情况下,关系对应的字段需要外键约束,
这个约束字段只能是另一张表的范围

子表:学生表(含外键的表)
父表:班级表

创建外键sql语句
先创父再创子

foreign key(外键约束关系字段) references 父表(约束参照字段)

六.存储引擎

存储引擎就是一张表存储数据的方式

查看一张表的建表语句(存储引擎和字符编码等)
show create table 表名;

1.MyISAM

MyISAM优点是可以压缩,只读节省空间

2.InnoDB

mysql默认存储引擎,也是一个重量级存储引擎

存储方式
1.表头表结构存储在.frm格式文件
2**.数据和索引存储在表空间中
3.记录事务活动的日志文件**

优点:
支持事务(安全),支持数据崩溃自动恢复

缺点:
效率慢,不能被压缩只读,占用空间大

3.MEMORY

内存存储引擎,关机数据消失

存储格式
1.表头表结构存储在.frm格式文件
2.数据和索引储存在内存中

优点:
查询效率最快,因为数据和索引都是在内存中

缺点:
不安全,关机数据消失

七.事务

一个事务就是一个完整的业务逻辑
一个事务就是需要完成一件事

最小的工作单元,不可再分,必须同时成功或者同时失败
只有DML语句和事务相关,因为DML语句涉及数据的增删改,需要考虑安全问题

存在事务机制的原因是一条DML语句执行不完,如果一条DML语句就能执行完就不需要事务
事务的本质就是多条DML语句同时成功或者同时失败

开启事务:
start transaction;

commit(提交) /rollback(回滚到上次提交)

每一句DML语句都会储存在日志文件中
提交事务commit或者回滚事务rollback,都会清空日志文件
commit提交到表中(硬盘中),rollback回滚到上次提交,都是结束事务的标志

1.事务的特性

2.事务的隔离级别

2.1读未提交

read uncommitted(最低的隔离级别)
事务A可以读取(select)到事务B未提交的数据
存在"脏读"现象

2.2读已提交

read committed(oracle默认)
每次读到的数据都是绝对真实
事务A可以读取(select)到事务B已提交的数据
不可重复读取数据,就是事务A一旦开启每次读取(select)都会读取到事务B提交的数据

2.3可重复读

repeatable read(mysql默认)
事务A只要开启后,读取内容不会被事务B的提交内容所改变(计算银行总账时候存款取款)
存在"幻影读",永远读的都是幻象,不够真实

理解:
银行总账,需要执行一条select语句,读取早上时的数据
需要从早上执行到晚上
在这期间会有人存款或者取款(DML语句)
如果是读已提交会把每次存款或者取款的数据都读取到,数据就不是早上的数据了
所以要可重复读,就是从早上开启事务后,期间的存款和取款不会影响到读取早上的数据
事务结束后读取到的还是早上的数据

2.4序列化/串行化

serializable(最高级别的隔离级别)
效率最低,表示事务A开启,事务B不能动,表示事务排队,不能并发
每次读取到的都是最真实的数据

八.索引

索引就是一本书的目录
索引是在表的字段上添加的,目的是为了缩小扫描范围存在的一种机制
一个字段可以添加一个索引,也可以多个字段联合添加索引

mysql查询方面主要就是两种方式
1.全表扫描
2.根据索引检索
如果一个字段上没有添加索引,mysql就会进行全表扫描,效率较低

1.索引的排序原理

只要是主键或者加有unique约束的字段会自动添加索引
索引是一个单独的对象
每一行数据记录都会在硬盘上有一个编号

在mysql中是一个自平衡二叉树(B-Tree)
表中字段id不会动,索引对象idIndex会通过二叉树排序

排序排的是索引对象idindex,不是字段id

查找方式:
当执行select * from t_user wher id = 101;时,
mysql发现id字段上有索引对象,所以通过索引对象idIndex进行查找
从根节点开始左小右大快速定位到101,通过101得出物理编号0x6666,此时马上sql语句转换
select * from t_user where 物理编号 = 0x6666;

实际底层实现比这个二叉树复杂

2.创建和删除索引

索引不能随便添加,需要维护

什么时候添加索引:
1.数据量庞大
2.这个字段经常出现在where后面(也就是说这个字段总是被扫描)
3.这个字段很少会被DML操作(因为DML之后,索引对象需要重新排序(二叉树))

建议通过主键字段或者unique约束字段查询,效率比较高

创建索引
create index 索引名 on 表名(字段名);
可以复合索引
create index 索引名 on 表名(字段名1,字段名2);

删除索引
drop index 索引名 on 表名;

如何知道select的时候是否使用了索引查找
explain + select语句 + where条件;

type = ALL 就是索引失效
rows = 14 就是全表查询了14条记录

当使用了索引查找empno(主键字段,自动添加索引)

3.索引失效

1.select * from emp where name like‘%T’;(记住这一个就行)
模糊查询避免%出现在开头

2.使用or的情况，必须or两边都有索引才会走索引
不然会失效，建议使用union

3.联合索引用左侧没问题，用右侧字段就会失效

4.字段参加运算会失效，sal+1 = 800；

5.where后使用了函数

九.视图

1.创建删除视图

视图就是站在不同角度去看待同一份数据

创建视图
create view emp_view as select * from emp;
as后必须是DQL查询语句

引出,快速复制表
create table xxx as select * from emp;

删除视图
drop view emp_view;

2.视图的作用

可以面向视图对象进行增删改查(CRUD),对视图的操作会导致原表被操作

视图也是一张表,对视图修改会影响原表

这里引出一个问题,视图是引用了一个查询语句查询出来的表格,对视图的操作也会影响原表格,那么视图有个p用?

答案:
方便,简化开发,利于维护

其实就是引用了一条select语句,以后都不需要再select去修改数据,直接用视图名字就好

十.DBA命令

导出数据库
在cmd窗口中导出test这个数据库到e盘命名为test.sql
mysqldump test>E:\test.sql -u root -p
然后输入密码回车

导出数据库中的表
mysqldump test emp>E:\test.sql -u root -p

导入数据库
在mysql命令窗口操作或者在cmd中登录mysql操作

1.先创建数据库

2.使用数据库

3.source导入

输入完source空格直接把文件拖过去没有分号回车

十一.数据设计三范式

什么是数据设计三范式?

数据库表的设计依据
教你怎么设计一个数据库表

第一范式:
任何一张表必须有主键,每一个字段原子性不可再分

第二范式:
建立在第一范式的基础之上,要求所有的非主键字段完全依赖主键,不要产生部分依赖

第三范式:
建立在第二范式的基础之上,要求所有非主键字段直接依赖主键,不要产生传递依赖

避免数据冗余,减少空间浪费

1.第一范式

任何一张表必须有主键,每一个字段原子性不可再分

修改为

2.第二范式

建立在第一范式的基础之上,要求所有的非主键字段完全依赖主键,不要产生部分依赖

因为主键是复合主键,产生了部分依赖
部分依赖就是,张三只依赖于1001,和001没关系,(这个联合主键只依赖了其中一个)
这是典型的多对多的关系,数据重复冗余了,需要设计三张表

口诀:
多对多,三张表,关系表两外键

3.第三范式

建立在第二范式的基础之上,要求所有非主键字段直接依赖主键,不要产生传递依赖

满足第一范式和第二范式,因为主键只有一个,一个主键对应一行数据
但一年一班依赖班级编号01,而班级编号01依赖1001,存在传递依赖,数据冗余

口诀
一对多,两张表,多的表加外键

4.一对一拆分表

当一张表字段太多,太庞大,需要拆分表
怎么设计呢?

口诀
一对一,外键唯一

5.嘱咐一句话

为了满足客户需求,速度要快的情况下,哥们不需要节省空间,有的是空间,不怕数据冗余
可以写在一张表内,就不会出现笛卡尔积现象