[大数据]mysql学习

MySQL

##MySQL目标

一、为什么要学习数据库
二、数据库的相关概念      
	DBMS、DB、SQL
三、数据库存储数据的特点
四、初始MySQL
	MySQL产品的介绍        
	MySQL产品的安装            ★        
	MySQL服务的启动和停止       ★
	MySQL服务的登录和退出       ★      
	MySQL的常见命令和语法规范      
五、DQL语言的学习   ★              
	基础查询        ★             
	条件查询  	   ★			
	排序查询  	   ★				
	常见函数        ★               
	分组函数        ★              
	分组查询	   ★			
	连接查询	   ★			
	子查询         √                  
	分页查询       ★              
	union联合查询  √			
	
六、DML语言的学习   ★             
	插入语句						
	修改语句						
	删除语句						
七、DDL语言的学习  
	库和表的管理	    √				
	常见数据类型介绍  √          
	常见约束  	     √			
八、TCL语言的学习
	事务和事务处理                 
九、视图的讲解        √
十、变量                      
十一、存储过程和函数   
十二、流程控制结构       

##数据库的好处

1.持久化数据到本地
2.可以实现结构化查询，方便管理

##数据库相关概念*

1、DB：数据库，保存一组有组织的数据的容器
2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
3、SQL:结构化查询语言，用于和DBMS通信的语言

##数据库存储数据的特点

1、将数据放到表中，表再放到库中
2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5、表中的数据是按行存储的，每一行类似于java中的“对象”。

##MySQL产品的介绍和安装

MySQL背景

前身属于瑞典的一家公司，MyAQL AB
08年被sun公司收购
09年sun被oracle收购

MySQL的优点

1、开源、免费、成本低
2、性能高、移植性好
3、体积小，便于安装

MySQL安装

c/s架构软件，一般安装服务器
分为企业版和<u>社区版</u>
常用5.5,<u>5.6</u>,5.7,8.0

###MySQL服务的启动和停止

方式一：计算机——右击管理——服务
方式二：通过管理员身份运行cmd
net start 服务名（启动服务）
net stop 服务名（停止服务）

###MySQL服务的登录和退出

方式一：通过mysql自带的客户端
只限于root用户
方式二：通过windows自带的客户端
登录：
mysql 【-h主机名 -P端口号 】-u用户名 -p密码
退出：
exit或ctrl+C

?
?
?

###MySQL的常见命令

1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(

	列名 列类型,
	列名 列类型，
	。。。
);
6.查看表结构
desc 表名;

7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql --V

###MySQL的语法规范

1.不区分大小写,但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进 或换行
4.注释
	单行注释：#注释文字
	单行注释：-- 注释文字
	多行注释：/* 注释文字  */

?
?

###SQL的语言分类

DQL（Data Query Language）：数据查询语言
	select 
DML(Data Manipulate Language):数据操作语言
	insert 、update、delete
DDL（Data Define Languge）：数据定义语言
	create、drop、alter
TCL（Transaction Control Language）：事务控制语言
	commit、rollback

###SQL的常见命令

show databases； 查看所有的数据库
use 库名； 打开指定 的库
show tables ; 显示库中的所有表
show tables from 库名;显示指定库中的所有表
create table 表名(
	字段名 字段类型,	
	字段名 字段类型
); 创建表

desc 表名; 查看指定表的结构
select * from 表名;显示表中的所有数据

DQL语言

• （Database Query Language）数据库表查询操作语言

基础查询

• 语法：

  select 
    查询列表1,
    查询列表2
    ··· 
  from
    表名 ;
  

• 特点：

  • 查询列表可以是：表中的字段、常量值、表达式、函数、 *(代表表中的所有字段)
  • 字符型和日期型的常量值必须用单引号引起来，数值型不需要
  • 查询的结果是一个虚拟的表格

条件查询

• 语法：

  select 
    查询的字段 
  from
    表名 
  where 筛选条件 ;
  

• 分类：

  • 简单条件运算符： > 、< 、= 、>=、<=、<> (不等于) 、<=> (安全等于)

  • 逻辑运算符：and、or、not

  • 模糊查询：like、between and（闭区间）、in、is null 、is not null (由于=、<>不能判断null值)

    • like一般搭配通配符使用，可以判断字符型或数值型

    //like
    like '_$_%' escape '$';
    //between and
    between 100 and 200;
    //in
    in(`A`,`B`);
    

• 特点：where一定放在from的后面（挨着）

• where不支持别名

排序查询

• 语法：

  select 
    查询的字段 
  from
    表 
  where 筛选条件 
  order by 排序列表 asc | desc,
    排序列表 asc | desc ······;
  

• 理解：

  • ASC：升序 （如果不写，默认是升序）
  • DESC：降序

特点：order by支持别名

分组查询

• 语法：

  select
    查询的字段，分组函数
  from
    表名
  [where 筛选条件]
  group by 分组的字段(表达式或函数)
  [order by 排序的字段或表达式]
  

• 分组后筛选语法：

  select
    查询的字段，分组函数
  from
    表名
  group by 分组的字段(表达式或函数)
  having 分组后的筛选条件;
  

• 特点：

  • 可以按单个字段分组。也可以按多个字段分组，字段之间用逗号隔开
  • 和分组函数一同查询的字段最好是分组后的字段
  • group by、having支持别名

多表连接查询

• 查询的字段来自于多个表时用到

• 分类：

  • sql92标准：在MySQL中仅支持内连接
  • sql99标准：在MySQL中支持内连接 + 外连接（左外连接、右外连接）+ 交叉连接

• 特点：

  • 表支持别名（一般为表起别名，提高阅读性和性能）
  • 如果为表起了别名，就不能使用原来的表名去限定
  • n个表连接，至少需要n-1个连接条件
  • 多个表不分主次，没有顺序要求

• 语法( sql99 )：

  select 查询的字段
  from 表1 别名
  连接类型 join 表2 别名
  on 连接条件 
  [where 筛选条件]
  [group by 分组的字段]
  [having 分组后的筛选条件]
  [order by 排序的字段或表达式]
  

• 分类：

  • 内连接☆：inner(默认值，可省略)
    • 等值连接
    • 非等值连接
    • 自连接
  • 外连接：
    • 左外☆：left outer （outer可省略）
    • 右外☆：right outer
    • 全外：full outer
  • 交叉连接：cross

• 理解：

  • 等值连接：等值连接的结果 = 多个表的交集
  • 左、右外连接：查询结果为主表中的所有记录。如果从表中有和它匹配的，则显示匹配的值，否则显示null
  • 全外连接 = 内连接的结果+表1中有但表2没有的+表2中有但表1没有的
  • 交叉连接 = 笛卡尔乘积的结果

子查询

• 含义：

  • 一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
  • 在外面的查询语句，称为主查询或外查询

• 分类：

  • 标量子查询（结果集只有一行一列）[或称单行子查询]
  • 列子查询（结果集只有一列多行）[或称多行子查询]
  • 行子查询（结果集有一行多列）
  • 表子查询（结果集一般为多行多列）

• 特点：

  • 子查询都放在小括号内
  • 子查询可以放在from后面、select后面、where后面、having后面，exists后面····· 但一般放在条件的右侧
  • 子查询优先于主查询执行，主查询使用了子查询的执行结果
  • 单行子查询，一般搭配单行操作符使用：> < = <> >= <=
  • 多行子查询，一般搭配多行操作符使用：any、all、some、in、not in

• 非法使用子查询的情况：
  • 子查询的结果类型于所需不匹配
  • 子查询的结果为空

分页查询

• 语法：

  select 字段|表达式
  from 表名
  [where 条件]
  [group by 分组字段]
  [having 条件]
  [order by 排序的字段]
  limit 起始的条目索引 , 条目数;
  

• 特点：

  • 起始条目索引从0开始(可省略，默认为0)

  • limit子句放在查询语句的最后

  • 公式(page = 要显示的页数，sizePerPage = 每页显示条目数)：

    limit (page - 1) * sizePerPage,sizePerPage
    

联合查询

• 将多条查询语句的结果合并成一个结果

• 语法：

  select 查询列表 from 表名 where 条件
  union [all]
  select 查询列表 from 表名 where 条件
  union [all]
  ···
  

• 特点：

  • union去重，union all不去重
  • 多条查询语句的查询的列的类型几乎相同
  • 多条查询语句的查询的列数必须是一致的

杂知识点及常见函数

单行函数

• 日期格式符：

分组函数

• 功能：做统计使用，又称统计函数、聚合函数、组函数

• 特点：
  • 以上五个分组函数都忽略null值，除了count(*)
  • sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型
  • 都可以搭配distinct使用，用于统计去重后的结果
  • count的参数可以支持：字段、*、常量值（一般放1）

起别名(as)

• 格式：

  select 查询列表 as 别名;
  

• 特定：

  • as 可省略
  • 别名若需要引号，建议使用双引号 " "

去重(distinct)

• 格式：

  select distinct 查询列表;
  

+号

• 使用情况：
  • 两个操作数都为数值型，做加法运算
  • 若存在字符型，试图将字符型数值转换成数值型 。若转换失败，则将字符型数值转换成0
  • 只要有以个操作数为null，则结果肯定为null

=号

• 不能判断null值
• <=> 安全等于号 既可以判断普通的数组，也可以判断null值

通配符

• 分类：

  • % (任意多个字符，包含0个字符)
  • _ (任意单个字符)

• 想让通配符作为字符出现：

  • 方式一（推荐使用）：

    like '_$_%' escape '$'; // $ 可替换为任意字符
    

  • 方式二：

    like '_\_%';
    

DML语言

• （Database Manipulation Language）数据库表数据操作语言

插入

• 语法：

  #方式1
  insert into 表名(列名 ，···)
  values(值1 ，,...)，(值2 ，,...);
  #方式2
  insert into 表名
  set 列名1=值，列名2=值···
  

• 特点

  • 可以为空的字段，可以不用插入值，或用null填充
  • 字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致
  • 方式1可以插入多行，支持子查询

修改

• 语法：

  #单表
  update 表名              ①
  set 字段=新值,字段=新值    ③
  [where 条件]             ②
  #多表
  update 表1 别名
  连接类型 join 表2 别名
  on 连接条件
  set 字段=新值,字段=新值    
  [where 条件]             
  

删除

• 语法：

  • 方式一（delete语句）：

    #单表
    delete
    from 表名 
    [where 筛选条件]
    #多表
    delete 别名1，别名2
    from 表1 别名1
    连接类型 join 表2 别名2
    on 连接条件
    where 筛选条件;
    

  • 方式二（truncate语句）：

    truncate table 表名
    

  • 两种方式对比：

    • truncate不能加where条件，而delete可以加where条件
    • truncate的效率高一点
    • truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
    • delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
    • truncate删除不能回滚，delete删除可以回滚

DDL语言

• （Database Definition Language）数据库表结构操作语言

库和表的管理

• 库的管理：

  #创建库
  create database (if not exists) 库名
  #删除库
  drop database (if exists) 库名
  

• 表的管理：

  • 创建表：

    create table (if not exists) 表名(
        列名1 列的类型1 [(长度) 约束],
        列名2 列的类型2 [(长度) 约束],
        ·······
    );
    

  • 修改表：

    alter table 表名 操作名 column 列名 [列类型 约束]
    

    • 操作名：add | modify | drop | change | rename [to]
      • add (添加字段)
      • modify (修改字段类型和列级约束)
      • drop (删除字段)
      • change (修改字段名) alter table 表名 change column 旧列名 新列名 新类型 (类型不能省)
      • rename [to] (修改表名)

  • 删除表：

    drop table (if exists) 表名;
    

  • 复制表：

    #仅复制结构
    create table 表名1 like 表名2;
    #复制表的结构+数据
    create table 表名1
    select * from 表名2
    [where 条件]
    

• 补充操作：

  • 创建mysql数据库用户

    create user tom identified by '新密码';
    

  • 授予权限

    #授予通过网络方式登录的tom用户，对所有库所有表的全部权限，密码设为abc123.
    grant all privileges on *.* to tom@'%'  identified by 'abc123'; 
    
     #给tom用户使用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
    grant select,insert,delete,update on atguigudb.* to tom@localhost identified by 'abc123'; 
    

常见类型

数值型

整形

• 分类：tinyint(1)、smallint(2)、mediumint(3)、int / integer(4)、bigint(8) (括号内为所占字节数)
• 特点：
  • 如果不特殊设置，则默认是有符号（+ -）
  • 在关键字后添加unsigned设置为无符号
  • 如果插入的数值超出了整形的范围，则会报out of range异常，并且插入临界值
  • 如果不设置长度，则会有默认值。
  • 长度代表了显示的最大宽度，如果不够，搭配zerofill使用会用0在左边填充（使用了zerofill，则默认为无符号）

小数

• 分类：

  • 浮点型：
    • float(M , D)
    • double(M , D)
  • 定点型：
    • dec(M , D)
    • decimal(M , D)

• 特点：

  • M：整数位数+小数位数
  • D：小数位数
  • 如果超过范围，则同样插入临界值
  • M和D都可以省略。如果是decimal，则M默认为10，D默认为0。如果是float和double，则会根据插入的数值的精度来决定精度
  • 定点型的精确度较高，若对精确度有高要求 可选用

字符型

• 分类：
  • 较短的文本(M代表最大的字符数)：
    • char(M)：[M可以省略，默认为1。固定长度的字符。比较耗费空间。效率高]
    • varchar(M)：[M不可以省略。可变长度的字符。比较节省空间。效率低]
    • binary和varbinary（用于保存二进制）
    • enum (用于保存枚举) 、set(用于保存集合)
  • 较长的文本：text、blob(较大的二进制)

日期型

• 分类：
  • data(日期)、time(时间)、year(年)
  • datetime(日期+时间) [8个字节。范围1000-9999。不受时区等影响]
  • timestamp(日期+时间) [4个字节。范围1970-2038。受时区等影响]

常见约束

• 一种限制，用于限制表中的数据。为了保证表中数据的的准确和可靠

• 分类(六大约束)：

  • not null ：非空，用于保证该字段的值不能为空
  • default：默认，用于保证该字段有默认值
  • primary key：主键，用于保证该字段的值具有唯一性，且非空
  • unique：唯一，用于保证该字段的值具有唯一性，但可为空
  • foreign key：外键，用于保证该字段的值必须来自主表关联列的值(在从表添加外键约束，用于引用主表中某列的值，且主表的关联列必须是一个key[一般是主键 或 唯一])
  • check(MySQL不支持)：检查

• 添加分类：

  • 列级约束：六大约束语法上都支持（可加多个，用空格隔开），但外键约束没有效果
  • 表级约束：除了非空、默认，其他的都支持

• 标识列：

  • 可以不用手动的插入值，系统提供默认的序列值，又称为自增长列 ( auto_increment )
  • 特点：
    • 一个表中至多有一个标识列
    • 标识符要求是一个key(主键、唯一键)
    • 标识符的类型只能是数值型
    • 标识符可以通过set auto_increment_increment = number 设置步长。可以通过手动插入值，设置起始值

• 主键和唯一对比：

  	保证唯一性	允许为空	允许存在的个数	允许组合
  主键	√	×	至多有一个	√ (不推荐使用)
  唯一	√	√	可以存在多个	√ (不推荐使用)

• 创建表时添加约束，通用写法：

  create table if not exists stuinfo(
  	id int primary key,
      stuname varchar(20) not null,
      age int default 18,
      seat int unique,
      majorid int,
      #表级约束
      [constraing fk_stuinfo_major] foreign key(majorid) references major(id)
  );
  

• 修改表时添加约束，语法：

  #列级约束
  alter table 表名 modify column 字段名 字段类型 新约束;
  #表级约束
  alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];
  

• 修改表时删除约束，语法：

  #列级约束
  alter table 表名 modify column 字段名 字段类型 ;
  #表级约束
  alter table 表名 drop 约束类型(唯一键用的是index) [列名];
  

TCL语言

• （Transaction Control language）事务控制语言

数据库事务

• 事务的分类：

  • 隐式事务：事务没有明显的开启和结束的标记
  • 显式事务：事务具有明显的开启和结束的标记（前提：必须先设置自动提交功能为 禁用）

• 相关步骤：

  1. 开启事务
  2. 编写事务的一组逻辑操作单元（insert、delete、update、select）
  3. 提交事务或回滚事务

  #开启事务
  set autocommit=0;
  start transaction;
  #编写事务的一组逻辑操作单元
  
  ·······
  
  #结束事务(二选一)
  commit;(提交事务)
  rollback;(回滚事务)
  

  savepoint 节点名(设置保存点,只能搭配rollabck to 使用)

  commit to 节点
  rollback to 节点

其他

视图

• 虚拟表，和普通表一样使用

• 视图的创建语法：

  create view 视图名 as 查询语句;
  

• 视图的查看

  #方式一：
  desc 视图名;
  #方式二：
  show create view 视图名;
  

• 视图的修改：

  • 方式一：

    #若存在修改，否则创建
    create or replace view 视图名 as 查询语句;
    

  • 方式二：

    alter view 视图名 as 查询语句;
    

• 视图数据的删除：

  delete from 视图名;
  

• 视图的删除：

  drop view 视图名1,视图名2···;
  

• 以下视图不能更新：

  • 包含以下关键字的sql语句：分组函数、distinct、group by、join、having、union或者union all
  • 常量视图
  • select中包含子查询
  • from一个不能更新的视图
  • where子句的子查询引用了from子句中的表

变量

• 分类：

  • 系统变量
    • 全局变量 (作用域：将为所有的全局变量赋初始值，针对所有的会话有效，但不能跨重启服务器)
    • 会话变量 (作用域：仅针对当前会话(连接) 有效)
  • 自定义变量
    • 用户变量 (作用域：仅针对当前会话(连接) 有效)
    • 局部变量 (作用域：仅在定义它的begin、end中有效)

• 命令：

  • 查看系统变量：

    show global | [session] variables [ like 'XXX' ];
    

  • 查看指定的某个系统变量的值

    select @@global | [session].系统变量名
    

  • 为某个系统变量赋值

    #方式一
    set global | [session] 系统变量名=值;
    #方式二
    set @@global | [session].系统变量名=值;
    

  • 注意：

  • 全局级别用global。 会话级别用session。如果不写，则默认session

• 自定义变量：

  • 使用步骤：

    1. 声明并初始化（局部变量可以不初始化）
    2. 赋值
    • 用户变量(放在会话中的任何地方)

    #声明并初始化
    set @用户变量名=值
    set @用户变量名:=值
    select @用户变量名:=值
    #赋值
    select 字段 into @用户变量名 from 表名;
    #查看用户变量的值
    select @用户变量名;
    

    • 局部变量(只能在begin end中，且为第一句话)

    #声明
    declare 变量名 类型 [default 值];
    #赋值
    set 局部变量名=值
    set 局部变量名:=值
    select @局部变量名:=值
    select 字段 into 局部变量名 from 表名;
    #查看用户变量的值
    select 局部变量名;
    

存储过程

• 一组预先编译的SQL语句的集合

• 分类(in、out、inout都可以在一个存储过程中带多个)：

  • 无返回无参
  • 仅仅带in类型，无返回有参
  • 仅仅带out类型，有返回无参
  • 既带in又带out，有返回有参
  • 带inout，有返回有参

• 创建存储过程，语法：

  create procedure 存储过程名(参数列表)
  begin
  	存储过程体(如果仅有一条SQL语句，可以省略 begin end )
  end 结束标记
  

  • 参数列表包含：参数模式(in|out|inout)、参数名、参数类型
    • in：该参数只能作为输入，需要调用方传入值
    • out：该参数只能作为输出，返回值
    • inout：该参数既可以作为输入也可以作为输入，需要传递值，又可以返回值
  • 需要用 delimiter 新的结束标记 设置新的结束标记

• 调用存储过程，语法：

  call 存储过程名(实参列表) 结束标记
  

• 删除存储过程，语法：

  drop procedure 存储过程名;
  

• 查看存储过程，语法：

  show create procedure 存储过程名;
  

函数

• 特点：

  • 有且仅有一个返回

• 创建函数，语法：

  create function 函数名(参数列表) returns 返回类型
  begin
  	函数体(如果仅有一条SQL语句，可以省略 begin end )
  	return 值;
  end 结束标记
  

  • 参数列表：参数名、参数类型

• 调用函数，语法：

  select 函数名(参数列表) 结束标记
  

• 查看函数，语法：

  show create function 函数名;
  

• 删除函数，语法：

  drop function 函数名;
  

流程控制结构

分支结构

if 函数

• 语法：

  if(条件，值1，值2);
  

case结构

• 情况1 (类似于switch语句，一般用于实现等值判断)

  case 要判断的字段或表达式
  when 常量1 then 返回的值1或语句1;(若是值不用有分号，语句必须有分号)
  when 常量2 then 返回的值2或语句2;
  ···
  else 返回的值或语句; (可以无else)
  end [case] （如果是放在 begin end中需要加上case，如果放在select后面不需要）
  

• 情况2 (类似于多重IF语句，一般用于实现区间判断)

  case
  when 条件1 then 返回的值1或语句1;
  when 条件2 then 返回的值2或语句2;
  ···
  else 返回的值或语句;
  end [case] 
  

• 特点：

  • 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方
  • 可以作为独立的语句使用，只能放在begin end中

if elseif 结构

• 语法：

  if 条件1 then 语句1;
  elseif 条件2 then 语句2;
  ...
  else 语句n;
  end if;
  

• 特点：

  • 只能用在begin end中

循环结构

• 分类：while、loop、repeat

• 循环控制：

  • iterate：类似于continue 结束本次循环继续下一次
  • leave：类似于break 结束当前所在的循环

• 语法：

  #先判断后执行
  [标签:] while 循环条件
  do
  	循环体
  end while [标签];
  #先执行后判断
  [标签:]repeat
  	循环体
  until 结束循环的条件
  end repeat [标签];
  #没有条件的死循环
  [标签:] loop
  	循环体
  end loop [标签];
  

• 特点：

  • 只能用在begin end中