MySQL基础知识（适合有一点点MySQL基础的小伙伴当作 ...

远海星光

听课时的笔记，有写错的地方欢迎指正，由于主要是自用，所以记录方式从简，如果有时间更新详细版。
课程链接：MySQL入门基础,mysql基础视频+数据库实战,老杜带你学_哔哩哔哩_bilibili
1.基础知识

关系型数据库：有表和表之间的关系
非关系型数据库：不用SQL 有自己的查询语言
数据库：DataBase 简称DB
数据库管理系统：DataBaseManagement 简称DBMS
结构化查询语言：SQL
启动/关闭服务：net start/stop 服务名称;

使用MySQL mysql -uroot -p密码

                mysql -uroot -p
                Enter password: ***

退出mysql ：exit
查看mysql中有哪些数据库： show databases;
选择使用某个数据库：use test;
创建数据库：create database bjpowernode;
查看某个数据库下有哪些表：show tables;
描述表：desc 表名；
查看mysql数据库的版本号：select version();
查看当前使用的是哪个数据库：select database();
终止一条命令的输入：\c
合并查询结果集：union 效率高避免产生笛卡尔积 合并时两个结果集列数相同
select ename,job from emp where job = 'MANAGER' union select ename,job from emp where job = 'SALESMAN';
数据库中最基本的单元是表 table；表的行(row)被称为数据/记录；表的列(column)被称为字段

SQL语句分类

DQL (data query language) 数据查询语言 select
DML (data manipulation language) 数据操作语言 对表中数据操作       增insert  删delete  改update
DDL (data defination language) 数据定义语言 对表的结构操作
          增create  删drop  改alter
TCL (transaction control language) 事务控制语言
          事务提交commit  事务回滚rollback
DCL (data control language) 数据控制语言
          授权grant  撤销授权revoke

导入提前准备好的数据：source 路径 (不要有中文)

2.DQL

select  

查询语句只会查询，不会对原表进行修改
起别名：字段 (as) 别名；字段1 别名，字段2 别名；(别名有空格用单引号括起来，mysql中双引号也行但是不标准)
字段可以使用数学表达式：select ename,sal*12 from emp;
单行处理函数 ：一个输入一个输出
  字段；*；字段1，字段2；
  小写lower；大写upper；
  substr(被截取字符串，起始下标，截取长度)；
  字符串拼接concat；取长度length；去空格trim；
  将字符串转换成日期str_to_date
  格式化日期date_format
  设置千分位format
  case..when..then..when..then..else..end
  select
  ename, job, sal as oldsal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
  from
  emp;
case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal
  四舍五入round：select round(1236.567, 0)//保留整数位
  生成随机数rand()：select round(rand()*100,0)//生成100以内随机数
  将null转化为具体值ifnull(字段，具体值)//在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL
select ename, (sal + ifnull(comm, 0)) * 12 as yearsal
多行处理函数 ：多个输入一个输出
  计数count；求和sum；平均值avg；最大值max；最小值min；
  字段去除重复记录distinct：/select ename,distinct job  /  select distinct
job,deptno 联合去重
子查询 ：对于select后面的子查询来说，这个子查询只能一次返回1条结果，多于1条，就报错了

from

子查询：from后面的子查询，可以将子查询的查询结果当做一张临时表

(inner内连接)； right/left (outer外连接 表有主次) Join right代表join右边是主表，left代表join左边是主表，主表的数据全部显示

  外连接的查询结果条数一定是 >= 内连接的查询结果条数
select a.ename as '员工名', b.ename as '领导名' from emp a left join emp b on a.mgr = b.empno;
  Using （列名）合并条件在两个表列名相同时可用
  Natural join 自然链接 就是让MySQL自动检索同名列作为合并条件。最好别用乱七八糟
  Cross join 交叉链接 显式语法

on

  连接的条件

where      

子查询   
  等于=；不等于<>/!=；
  小于<；小于等于<=；大于>；大于等于>=
  between 小 and 大/>= and <=
  is null；is not null；in( , )；not in( , )
  and；or；in( , )   (and优先级高于or  in等于or)
  like （%任意多个，_任意一个，\转义） ename like '_A%';
  regexp 正则表达式



group by

  在一条select语句当中，如果有group by语句的话, select后面只能跟：参加分组的字段，以及分组函数。其它的一律不能跟。
  两个字段联合分组group by deptno, job;

having

  having不能单独使用，having不能代替where，having必须和group by联合使用。

order by     

  默认升序；降序desc；升序asc；sal asc, ename asc;

Limit

  起始下标(默认从0开始)，长度

执行顺序1.from2.where3.group by4.having 5.select 6.order by 7.limit..

3.DDL  表的 增create  删drop  改alter

创建表 create

create table 表名(
字段名1 数据类型 表级约束，
字段名2 数据类型 表级约束，
字段名3 数据类型 表级约束，
列级约束
)
表名：建议以t_ 或者 tbl_开始，可读性强。见名知意。表名字段名都属于标识符
快速创建表 create table emp2 as select * from emp；

数据类型

varchar 可变长度字符串，最长225，根据实际字符长度动态分配空间。优点：节约空间，缺点：速度慢
char 定长字符串，最长225，不管字符实际长度分配固定长度空间。优点：速度快，缺点：使用不当导致空间浪费。
int 整数型，最长11
bigint 长整型
date 短日期类型 %Y-%m-%d
datetime 长日期类型 %Y-%m-%d %h:%i:%s
clob 字符大对象，char acter large最多可以 可以存储4G字符串，储存文章、说明等
blob 二进制大对象，binary large object，储存图片、声音、视频等需要使用IO流

约束 constraint

非空：not null
唯一：unique (可以为null；可以表级约束，联合起来唯一)
主键：primary key （pk）= not null+unique
(可以表级约束，叫复合主键，不建议用)
主键值是每一行记录的唯一标识；任何一张表都应该有主键，没有主键，表无效；一个表只能有一个主键。
id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增
建议用 int bright char
外键：foreign key (fk)
父表 子表




检查约束：cheak(mysql不支持，Oracle支持)

删除表 drop

drop table 表名；
drop table if exists 表名；
truncate table 表名；删除数据表还在，不支持回滚

修改表 alter

alter 很少用
什么是对表结构的修改？ 添加一个字段，删除一个字段，修改一个字段！！！

4.DML   数据的 增insert  删delet  改update

增 insert

Insert into 表名(字段1，字段2，字段3…) values(值1，值2，值3…)；
一一对应！没有给定值默认为null
字段名省略的话，等于都要写上，所有值都要填上。
只要执行成功，表中就会多一条记录。
可以一次性插入多条语句。
将查询结果插入表中：insert into dept_bak select * from dept; //很少用！
数字格式化：format(数字，'格式')
select ename,format(sal, '$999,999') as sal from emp;
将字符串varchar类型转换成date类型：str_to_date  
str_to_date('01-10-1990','%d-%m-%Y')
如果提供的日期字符串%Y-%m-%d，str_to_date函数就不需要了
将date类型转换成具有一定格式的varchar字符串类型：date_format
select id,name,date_format(birth,'%Y/%m/%d') as birth from t_user;
在mysql当中获取系统当前时间：now()





删 delet

delete from 表名 where 条件;
delete from t_user; // 删除所有！表中数据被删除了但是数据在硬盘上的真实储存空间不会被释放。缺点：效率低；优点：支持回滚
truncate(DDL) table 表名；删除数据表还在，不支持回滚

改 update

update 表名 set 字段1=值1，字段2=值2，字段3=值3… where 条件；
update t_user set name = 'jack', birth = '2000-10-11' where id = 2;

5.储存引擎

查看储存引擎：show create table t_student;
CREATE TABLE `t_student` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `cno` int(11) DEFAULT NULL,
  PRIMARY KEY (`no`),
  KEY `cno` (`cno`),
  CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
)
ENGINE=InnoDB (默认)AUTO_INCREMENT=11 DEFAULT CHARSET=utf8(默认)
建表时指定存储引擎，以及字符编码方式。
create table t_product(
  id int primary key,
  name varchar(255)
)engine=InnoDB default charset=gbk;
查看mysql存储引擎：show engines\G
常用储存引擎
MyISAM：

• 使用三个文件表示每个表：格式文件(储存表结构定义 mytable.frm)；数据文件(储存表行的内容 mytable.MYD)；索引文件(储存表上索引mytable.MYI)
  
• 可被转换为压缩、只读来节省空间。优势！！
  
• 不支持事务机制，安全性低
  

InnoDB：

• mysql默认存储引擎
  
• 支持事务，支持数据库崩溃后自动恢复机制，安全
  
• 不能节约存储空间
  

MEMORY

6.TCL  事务提交commit  事务回滚rollback

只有DML有事务这一说
就是批量的DML语句同时成功，或者同时失败
提交事务 commit 清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。 提交事务标志着，事务的结束。并且是一种全部成功的结束。 回滚事务 rollback 将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件 回滚事务标志着，事务的结束。并且是一种全部失败的结束。
mysql关闭自动提交：start transaction
事务包括4个特性 A：原子性：说明事务是最小的工作单元。不可再分。 C：一致性：所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败，以保证数据的一致性。 I：隔离性： A事务和B事务之间具有一定的隔离。 D：持久性： 事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！
隔离
读未提交：read uncommitted 脏读
读已提交：read committed
可重复读：repeatable read
序列化/串行化：serializable 最高隔离级别
设置隔离级别：set global transaction isolation level read uncommitted;
查看隔离级别：mysql8.0:select @@transaction_isolation;
                        SELECT @@tx_isolation

7.索引 index

索引相当于一本书的目录。索引是在数据库表的字段上添加的，是为了提高查询效率存在的一种机制。一张表的一个字段可以添加一个索引，当然，多个字段联合起来也可以添加索引。在mysql当中索引是一个B-Tree数据结构。遵循左小又大原则存放。采用中序遍历方式遍历取数据。

• 提醒1：在任何数据库当中主键上都会自动添加索引对象，id字段上自动有索引，因为id是PK。另外在mysql当中，一个字段上如果有unique约束的话，也会自动创建索引对象。
  
• 提醒2：在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
  
• 提醒3：在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式存在。（自平衡二叉树：B-Tree）
  

什么条件下，考虑字段添加索引

• 条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
  
• 条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
  
• 条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）
  
• 建议不要随意添加索引，因为索引也是需要维护的，太多的话反而会降低系统的性能。 建议通过主键查询，建议通过unique约束的字段进行查询，效率是比较高的。
  

创建索引： create index emp_ename_index on emp(ename); 给emp表的ename字段添加索引，起名：emp_ename_index 删除索引： drop index emp_ename_index on emp; 将emp表上的emp_ename_index索引对象删除。
怎么查看一个SQL语句是否使用了索引进行检索？ explain select * from emp where ename = 'KING';
看扫描记录条数 type
索引失效

• 模糊匹配
  
• or中有字段没有索引
  
• 使用复合索引的时候，没有使用左侧的列查找，索引失效
  

复合索引：两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引。 create index emp_job_sal_index on emp(job,sal);

• 在where当中索引列参加了运算，索引失效。
  
• 在where当中索引列使用了函数
  

8.视图 DQL

像是创建一个表，但是对视图增删改会导致原表被操作！
创建：create view 图名 as select；
删除：drop view 图名；
查图：select * from 图名；
图的插入：insert into 图名（字段1，字段2…）values （值1，值2…）
图的删除：delete from dept2_view;
表的更新：update emp_dept_view set sal = 1 where dname = 'A';
增删改查，又叫做：CRUD。
C:Create（增） R:Retrive（查：检索） U:Update（改）D:Delete（删）

9.DBA

数据导出：
在windows的dos命令窗口中： mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456 导出指定的表 mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456
数据导入
需要先登录到mysql数据库服务器上。 然后创建数据库：create database bjpowernode; 使用数据库：use bjpowernode 然后初始化数据库：source D:\bjpowernode.sql

• 数据库设计三范式
  

数据库表的设计依据

• 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
  
• 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
  
• 第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
  

• 一对多：一对多，两张表，多的表加外键！！！！！！！！！！！！
  
• 多对多：多对多，三张表，关系表两个外键！！！！！！！！！！！
  
• 一对一：在实际的开发中，可能存在一张表字段太多，太庞大。这个时候要拆分表。多个表外键唯一。
  

数据库设计三范式是理论上的。 实践和理论有的时候有偏差。 最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。 因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积） 有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的， 并且对于开发人员来说，sql语句的编写难度也会降低。 面试的时候把这句话说上：他就不会认为你是初级程序员了！