Mysql篇————基本知识

数据库基本概念

一，SQL,DB,DBM分别是什么
DB:DataBase(数据库)
DBMS：（数据库管理系统），常见的有MySql,Oracle,sqlSever…
SQL：结构化查询语言，是一门标准通用的语言，标准sql通用所有的数据库产品

​ DBMS——（执行）—— SQl ——（操作）——DB
二，对表的理解：
​ ①表：table，是数据库的基本组成单元，所有的数据库都是以表格的形式组织，目的是可读性增强。
​ ②表包括，行和列
​ 行：被称为数据（data）/记录 （row）
​ 列：被称为字段 （column）
三，SQL语句分类
​ DQL;查询语句，凡是select语句都是
​ DML:操作语句，对表格中的数据进行增（insert）删（delete）改（update）
​ DDL:定义语句，对表格结构进行增（create）删（drop）改（alter）
​ TCL:事物控制语句，提交事务（commit），回滚事务(rollback)
​ DCL:数据控制语句，（grant）授权，（revoke）撤销权限
四，在Mysql小黑窗中常用的DOM命令
​ ①登录Mysql： Mysql -u root -p ****
​ ②查看有哪些数据库：show databases；不属于sql语句，属于Mysql语句
​ ③创建数据库：create database 数据库名称；
​ ④使用数据库：use 数据库名称；
​ ⑤产看数据库有哪些表格：show tables；
​ ⑥查看表格内容：describe(desc) 表名；
​ ⑦查看表中数据：select * from 表名；
​ ⑧结束一条语句：\c
​ ⑨退出：exit

sql查询语句

单表查询

select 1+1，2*3 from dual
#dual：伪表

一，简单的查询语句
select 字段名1q，字段名2，… from 表名；
①一条sql语句以“;”结尾
②sql语句不区分大小写
③查询结果重命名 用as，也可以省略

select 字段（as） 别名 from 表名

二，条件查询
①语法格式：select 字段名1，字段名2，… from 表名 where 条件；
去除重复-----distinct

select distinct 字段名 from 表名;

②查询条件：

查询条件	谓词
比较	=,>, <,>=,<=,!=,<>(不等),!>,!<,NOT+运算符
确定范围	between…and,not between…and
确定集合	in,not in
字符匹配	like
空值	is null , is not null
多重条件	and,or,not

注意：

1. and与or连用时，and优先级大于or
2. in(’’,’’)不是集合，后面每一个都是值
3. like 模糊查询，两个特殊符号 %，_ 。 %:代表多个字符，_：代表一个字符
4. NULL参与运算结果都为空，可以使用**IFNULL（a，0）**函数来进行运算

三，排序数据
order by 语句，asc表示升序（默认），的desc表示降序

例如：select 字段1，字段2 from 表名 order by 字段1 desc

四，分组函数（聚集函数）

函数	作用
count	计数
sum	求和
avg	平均值
max	最大值
min	最小值

注意：
Ⅰ，所有的分钟函数都是对某一组数据进行计算
Ⅱ，只要null参与运算结果都为空，需要用ifnull（）函数处理
Ⅲ，分组函数不可以直接出现在where语句中
Ⅳ，count（*），与count（某个字段）的区别，第一个时统计记录条数，第二个是统计字段不为空的条数
Ⅴ，group by 与having
group by：按某个字段或者某些字段进行分类
having：对分组之后的数据再次过滤
Ⅵ，一条语句有group by时，select后面只可以跟分组函数，或者用于分组的字段

五，limit（分页查询）

使用limit字句
limit [offset,],n
offset:代表从第几行开始索引，默认0；
n：表示取几行

//获取前三行
select 字段1，字段2 from 表名 order by 字段1 desc limit 0,3

五，总结一个完整的selectDQL语句
select —from ----where — group by — having — order by - limit

5 - 1 - 2 - 3 - 4 - 6 - 7

连接查询（多表查询）

一，连接查询分类
内连接：等值连接，非等值连接，自连接
外连接：左外连接，右外连接
全连接：
内连接：合并具有同一.列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行
外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另 一个表匹配的行之外,
还查询到了左表或右表中不匹配的行。

二，笛卡尔积现象

三，内连接之等值连接
①语法格式：
select … from …
join …on …

select 表1 别名.字段 表2 别名.字段 from 表1 表1别名 join 表2 表2别名 on 条件

建议：从sql优化的角度，建议多表查询时，每个字段前都指明其所在的表
有n个表连接查询，至少有n-1个连接条件

四，内连接之非等值连接
语法格式：
 select … from …
 join…on 范围
五，外连接:
 左外连接 left join
 右外连接 right join

 外连接与内连接的区别：
  	外连接有主副表之分，主要查寻主表，当副表中的数据没有时 自动补null，而内连接没有。
 	左连接左表为主表，右连接右边为主表

子查询（嵌套查询）

概念：子查询是将一个select语句嵌套在另一个select语句的where子句中，包含只查询的select语句成为父查询（外部查询）。

子查询也可以嵌套在insert，update和delete语句中

​ （1）.使用圆括号将子查询的select包起来

​ （2）.当只查询的返回值是单个值时，子查询可以应用到任何表达式中

分类：
角度一：比较子查询，IN子查询，批量子查询
角度二：单行子查询，多行子查询。
角度三：不相关子查询，相关子查询。

1. 比较子查询：父查询与子查询之间用比较符连接，注，只查询的返回值只能有一个

   //查询学生表中选了名字为音乐的所有学生
   select * from student where cno = (select cno from course where cname="音乐" ) 
   

2. IN子查询： 父查询与子查询之间用IN进行连接判断某个字段是否在子查询的集合中

   //查询学生表中选了名字为音乐或数学的所有学生
   select * from student where cno in (select cno from course where cname="音乐" or cname="数学" ) 
   

3. 批量比较子查询：子查询的结果不止一个

   使用ANY：将表达式的每个字段的值与子查询返回值比较，一次结果为true，结果为true

   select * from 表名 where 字段名 = any(子查询) 
   

   使用ALL：将表达式字段的值与每个子查询返回值比较，全部为true，结果才为true

   select * from 表名 where 字段名 = all(子查询) 
   

   Exists子查询：子查询查找到满足条件的数据行，返回结果true

   select * from 表名 where 字段名  Exists(子查询) 
   

连接查询和子查询的使用情况

​ ①查询语句要输出字段来自多表，用连接查询

​ ②查询语句要输出字段来自一张表，且where子句只涉及另一张表，常用子查询

​ ③查询语句要输出字段和where子句都来自一张表，但where子句的查询条件设计应用集合函数进行数值比较，一般用子查询

Union（将查询结果集相加）

合并结果集是指对多个select语句查询的结果进行合并操作，组合成一个结果集。

使用union时，需注意以下几点：

1. 所有的select语句中的字段个数必须相等
2. 所有的select语句中对应的字段的数据类型必须相同或者兼容
3. 合并后的结果集中的字段名是第一个select语句中各字段的字段名
4. 使用Union运算符时，每个select语句都不能包含order by语句，只能在最后合并的结果集中使用，且order by中必须含有一个和第一个select语句中的字段

select sno ,sname from student
union
select tno ,tname from teacher

表的创建

创建表： create table teacher (id int,name char,sex char);
查看数据表 show create table 表名；
使用Desc查看表 Describe 表名

修改数据表名： alter table 旧 renam 新
修改字段名和数据类型： alter table 表名 change 旧字段名 新 数据类型
修改字段的数据类型： alter table 表名 modify 字段名 数据类型
添加字段： alter table 表名 add 新 数据类型【约束条件】
删除字段： alter table 表名 drop 字段
修改字段位置： alter table 表名 modify 字段名1 新数据类型 [first|after] 字段名2
first：设置为第一个 after：移动到字段二后

删除数据表： drop table teacher

约束

主键约束 primary key

#为已存在的表添加约束    
alter table 表名 modify 字段名 数据类型 约束
#删除约束                
alter table 表名 drop 约束
#复合主键                
primary key（字段1，字段2，...）
#为已存在的表添加复合主键约束
alter table 表名 add primary key（字段1，字段2，...）

外键约束 foreign key

constraint 外键名 foreign key（从表的主键字段）reference 主表名 （主表的主键字段）

#为已存在的表添加约束    
alter table 从表名 add constraint 外键名 foreign key（从表的主键字段）reference 主表名 （主表的主键字段）

非空约束 not null
唯一约束 unique
默认约束 default
字段自动增加 auto_increment

存储引擎

事务

什么是事务？

1. 一个事务就是一个完整的业务逻辑单元，不可再分
2. 和事务相关的只有DML语句（insert，update，delete）
3. 通常一个事务需要多个DML语句来完成

事务的原理

开启事务机制（开始）------>执行DML语句（执行成功后把记录记录到数据库的操作历史当中，并不保存（修改）一条数据）-----> 提交事务或回滚（结束）

事务的处理语句

启动事务：start Transaction

提交事务：commit

回滚事务：rollback

设置事务隔离级别：set 【session | grobal】 Transaction isolation level 隔离级别

事务的四大特性

A：原子性 ----------最小单元不可分

C：一致性 ----------必须多条DML语句同时成功或失败

I：隔离性 ----------事务A与B之间有隔离

D：持久性 ----------数据必须持久化到硬盘中

关于事务的隔离性：

MySql在数据库访问过程中采用的是并发访问方式。在多个线程同时开启事务访问数据库时，可能会出现脏读，不可重复读，以及幻读等情况

1）脏读

脏读就是一个事务读取了 另一个事务没有提交的数据。即第一个事务正在访问数据，并且对
数据进行了修改，当这些修改还没有提交时，第二个事务访问和使用了这些数据。如果第一个 事
各回滚，那么第二个事务访问和使用的数据就是错误的脏数据。

2）不可重复读

不可重复读是指在一个事务内， 对同一数据进行了两次相同查询，但返回结果不同，这是
由于在一个事务两次读取数据之间，有第二个事务对数据进行了修改，造成两次读取数据的结果不同。

3）幻读

指一个事务执行两次查询，两次相同的查询查到的记录确不同。造成幻读的原因在于事务处理还没有结束时，其他事务对同一数据集合增加或者删除了记录。

视图

索引

数据库的三范式

设计范式的目的：减少数据冗余

第一范式

任何一张表都应该有主键，并且每个字段原子性不可再分

第二范式：

在第一范式的基础上，所有的非主键字段完全依赖主键不能产生部分依赖

（复合主键）容易导致部分依赖

解决方式：多对多，三张表，关系表两外键

第三范式

在第二范式的基础上，所有的非主键字段依赖主键，不能产生依赖传递

解决方法：一对多，两张表，多的表加外键