小饶学编程之JAVA SE第二部分——01MySQL基础

Love The Way You Lie 2022-12-21 06:08 198阅读 0赞

### MySQL基础 ###

*  一、基本概念
 *   *  1.1 数据库
     *  1.2 数据库的特点
     *  1.3 数据库管理系统
     *  1.4 数据库分类
     *  1.5 常见数据库
     *  1.6 MySQL安装与卸载
     *  1.7 MySQL服务的启动
     *  1.8 MySQL的登录跟退出
 *  二.、SQL
 *   *  2.1 什么是SQL
     *  2.2 SQL分类
     *  2.3 数据库操作
     *   *  2.3.1 创建数据库
         *  2.3.2 查询数据库
         *  2.3.3 修改数据库
         *  2.3.4 使用数据库
     *  2.4 表结构操作
     *   *  2.4.1 创建表
         *  2.4.2 查询表
         *  2.4.3 修改表
         *  2.4.4 删除表
     *  2.5 表数据操作（重点）
     *   *  2.5.1 新增数据
         *  2.5.2 修改数据
         *  2.5.3 删除数据
     *  2.6 查询数据（重点）
     *   *  2.6.1 基础查询
         *  2.6.2 条件查询
         *  2.6.3 排序查询
         *  2.6.4 聚合函数
         *  2.6.5 分组查询
         *  3.2.6 分页查询
 *  三、数据类型
 *   *  3.1 整型
     *  3.2 浮点
     *  3.3 定点数
     *  3.4 日期和时间类型
     *  3.5 字符
     *  3.6 枚举
 *  四、约束
 *   *  4.1 主键约束（重点）
     *  4.2 非空约束
     *  4.3 唯一约束
     *  4.4 默认约束
     *  4.5 自动增长
     *  4.6 外键约束
 *  五、多表查询
 *   *  5.1 内连接
     *  5.2 左（外）连接
     *  5.3 右（外）连接
     *  5.4 子查询
 *  六、事务
 *   *  6.1 事务的概念
     *  6.2 事务的特性
     *  6.3 事务的并发问题
     *  6.4 事务的隔离级别
 *  七、其它
 *   *  7.1 添加用户
     *  7.2 删除用户
     *  7.3 修改密码
     *  7.4 修改root密码
     *  7.5 授予权限
     *  7.6 查看权限
     *  7.6 撤销权限
     *  7.7 数据备份
     *  7.8 数据还原
 *  八、案例
 *  九、总结

# 一、基本概念 #

## 1.1 数据库 ##

数据库（database）：是按照数据结构来组织、存储和管理数据的仓库。

## 1.2 数据库的特点 ##

*  数据结构化
    
    据库系统实现了整体数据的结构化，这是数据库的最主要的特征之一。
 *  实现数据共享
    
    因为数据是面向整体的，所以数据可以被多个用户、多个应用程序共享使用。
 *  数据独立性高
    
    数据的独立性包含逻辑独立性和物理独立性，其中，逻辑独立性是指数据库中数据的逻辑结构和应用程序相互独立，物理独立性是指数据物理结构的变化不影响数据的逻辑结构。
 *  数据统一管理与控制
    
    数据的统一控制包含安全控制、完整控制和并发控制。简单来说就是防止数据丢失、确保数据的正确有效，并且在同一时间内，允许用户对数据进行多路存取，防止用户之间的异常交互。

## 1.3 数据库管理系统 ##

数据库管理系统(Database Management System)是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称DBMS。

## 1.4 数据库分类 ##

*  **关系型数据库**
    
    这种类型的数据库是最古老的数据库类型，关系型数据库模型是把复杂的数据结构归结为简单的二元关系（即二维表格形式）。
 *  **非关系型数据库**
    
    关系型数据库以外的统称为非关系型数据库，简称NoSQL（not only SQL）。从存储结构可以划分键值存储数据库（Redis）、列存储数据库（HBase）、面向文档数据库（MongoDB）、图形数据库（Neo4J）、搜索引擎存储（Elasticsearch）。

## 1.5 常见数据库 ##

*  MYSQL：开源免费的数据库，小型的数据库.已经被Oracle收购了。
 *  Oracle：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL。
 *  DB2：IBM公司的数据库产品，收费的。常应用在银行系统中。
 *  SQLServer: MicroSoft公司收费的中型的数据库。C\#、.net等语言常使用。
 *  SQLite：嵌入式的小型数据库，默认内置在android系统中。
 *  Redis： 是完全开源免费的，是一个高性能的key-value数据库。

## 1.6 MySQL安装与卸载 ##

*  安装
    
     *  一般安装到C盘，傻瓜式安装。
 *  卸载
    
     *  先卸载MySQL软件，然后进入C盘，打开显示隐藏目录，进入programData目录，删除mysql目录。

## 1.7 MySQL服务的启动 ##

*  手动从系统的服务打开，可以设置为自动开启
 *  命令行打开跟关闭服务
    
     *  net start mysql：启动mysql的服务
     *  net stop mysql：关闭mysql服务

## 1.8 MySQL的登录跟退出 ##

*  命令行
    
     *  明文登录：mysql -uroot \-proot,其中红色字体的root/root分别表示用户名跟密码。
     *  密文登录：mysql -uroot \-p 回车后输入密码即可。
     *  远程登录：mysql -h\[远程电脑ip\] -u用户名 -p密码。
     *  退出：`exit` 或者 `quit`
 *  第3方工具（Navicat ）

# 二.、SQL #

## 2.1 什么是SQL ##

*  结构化查询语言（Structured Query Language）简称SQL，SQL语句就是对数据库进行操作的一种语言。
 *  SQL语句可以分单行或者多行书写，以分号结尾。
 *  可使用空格跟缩进增强语句的可读性。
 *  MySQL不区分大小写。
 *  注释有3种：
    
     *  – 单行注释，–后面紧跟的空格不能省略
     *  \# 单行注释
     *  /\* 多行注释 \*/

## 2.2 SQL分类 ##

*  DDL（Data Definition Language）数据定义语言
    
     *  用来定义数据库对象：数据库，表，列等。关键字：create，drop，alter 等
 *  DML（Data Manipulation Language）数据操作语言
    
     *  用来对数据库中表的**数据**进行增删改。关键字：insert，delete，update 等
 *  DQL（Data Query Language）数据查询语言
    
     *  用来查询数据库中表的记录（数据）。关键字：select等
 *  DCL（Data Control Language）数据控制语言（了解）
    
     *  用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT，REVOKE等

## 2.3 数据库操作 ##

### 2.3.1 创建数据库 ###

*  直接创建数据库
    
        create database blb_db ;
 *  如果数据库不存在则创建，存在则不创建。
    
        create database if not exists blb_db;
 *  创建数据库，不存在则创建，并指定制定字符集
    
        create database if not exists blb_db character set utf8;

### 2.3.2 查询数据库 ###

*  显示所有数据库
    
        show databases;
 *  查看某个数据库的创建以及字符集
    
        show create database blb_db;

### 2.3.3 修改数据库 ###

*  修改数据库字符集
    
        alter database blb_db character set utf8 ;

### 2.3.4 使用数据库 ###

*  查询当前正在使用的数据库
    
        select database();
 *  使用数据库
    
        use blb_db;

## 2.4 表结构操作 ##

### 2.4.1 创建表 ###

*  语法
    
        create table 表名 (
        	列名  类型  [约束]  [COMMENT '备注1'] ,
        	列名  类型  [约束]  [COMMENT '备注2'] ,
        	列名  类型  [约束]  [COMMENT '备注3'] ,
        	......
        	列名  类型  [约束]  [COMMENT '备注N'] 
        )
 *  一个栗子
    
        create table student(
        	stu_id  varchar(10)  ,
        	stu_name varchar(10) ,
        	stu_sex varchar(2),
        	stu_address varchar(100),
        	stu_age int ,
        	stu_date datetime 
        );

### 2.4.2 查询表 ###

*  查询表的数据
    
        SELECT * from student ;
 *  询某个数据中所有的表
 *      show tables;
 *  查询表结构
    
        desc student;
 *  查询表的创建SQL
    
        show create table student ;

### 2.4.3 修改表 ###

*  修改表名
    
     *  `alter table 表名 rename to 新的表名;`
        
            alter table student rename to s1;
 *  修改表的字符集
    
     *  `alter table 表名 character set 字符集的名称;`
        
            alter table student character set utf8
 *  添加一列
    
     *  `alter table 表名 add column 列名 列类型 [约束];`
     *  `alter table 表名 add (列名 列类型 [约束] , 列名 列类型 [约束] , ...... );`
        
            alter table student add  column stu_height int  not null 
            alter table student add (stu_weight int);
     *  `alter table 表名 add column 列名 列类型 [约束] [FIRST]/[AFTER 列名];`
     *      alter table s1 add  column stu_height int  not null  FIRST 
            alter table s1 add  column stu_height int  not null  AFTER stu_age
 *  修改列名称跟类型
    
     *  `alter table 表名 change 原列名 新列名 新数据类型;`
        
            alter table student change  stu_weight  stu_weight1  double
 *  删除列
    
     *  `alter table 表名 drop 列名;`
        
            alter table student drop column stu_weight

### 2.4.4 删除表 ###

*  `drop table 表名;`
 *  `drop table if exists 表名;`
    
        drop table student;
        drop table if exists student;

## 2.5 表数据操作（重点） ##

### 2.5.1 新增数据 ###

*  语法
    
    `insert into 表名 [(字段名1,字段名2......)] values (字段值1,字段值2,......);`
    
        insert into student values ('101' ,'zhangsan','男','asdfa',18, str_to_date('08.09.2008 08:09:33','%d.%m.%Y %h:%i:%s')  );
        -- 插入多个值的话可以一次性插入
        insert into student values
        ('101' ,'zhangsan','男','asdfa',18, '2020-07-15 12:11:12' ),
        ('102' ,'lisi','女','123',18, '2020-06-15 11:11:12' ),
        ('103' ,'wangwu','男','456',18, '2020-04-15 10:11:12' );
    
     *  列名跟值需要一一对应。
     *  如果表名后没有跟指定字段则必须将全部所有字段值给出。

### 2.5.2 修改数据 ###

*  语法
    
    `update 表名 set 字段名1=新字段值1, 字段名2=新字段值2 ... [ where 条件]`
    
        update  student  set  stu_age = 21,stu_sex='女'  where stu_name = 'zhangsan';
    
     *  如果不加任何条件，则会将表中所有记录全部修改。

### 2.5.3 删除数据 ###

*  语法
    
    `delete from 表名 [where 条件];`
    
        delete from student  where stu_name = 'zhangsan';
    
     *  如果不加条件，则删除表中所有记录。
     *  当需要删除所有数据（表结构保留）的时候也可以使用`TRUNCATE TABLE 表名`。
     *  `delete from`属于DML，所以操作可以回滚。而`TRUNCATE TABLE 表名`属于DDL，所以操作不能回滚。
     *  从效率上讲`TRUNCATE TABLE 表名`高于`delete from`

## 2.6 查询数据（重点） ##

*  语法
    
    `select` 字段列表 `from` 表名列表
    
    \[`where` 条件列表\]
    
    \[`group by` 分组字段列表\]
    
    \[`having` 分组后的条件列表\]
    
    \[`order by` 排序字段\]
    
    \[`limit` 分页限定\]

### 2.6.1 基础查询 ###

1.  多个字段的查询
    
     *  `select 字段名1,字段名2... from 表名;`
     *  如果查询表中的所有字段可以使用\*号`select * from 表名`
2.  去重
    
     *  `select distinct 字段列表 from 表名`
3.  列计算
    
     *  `SELECT stu_age+1 from student ;`
     *  如果有null参与的运算，计算结果都会为null，可以使用IFNULL函数解决
        
            -- IFNULL：如果第一个参数为null的时候按照第2个参数值代替处理。
            SELECT IFNULL(stu_age,0)+1  from student  ;
4.  取别名
    
     *  `select 字段名1 as 别名1 ,字段名2 as 别名2 ... from 表名;`
        
            select stu_id as Stuid,stu_name  as "Stu Name" from student2 ;
     *  如果别名字段有空格需要用引号
     *  `as`可以省略

### 2.6.2 条件查询 ###

*  `> < >= <= <> !=`
    
        select * from student where stu_age > 18 ;
 *  `BETWEEN AND`
    
        -- 18跟28都包括在内
        select * from student where stu_age between 18 and 28 ;
 *  `is null`、`is not null`
    
     *  对表中的字段进行空或者非空判断
        
            select * from student where stu_sex is null ;
            select * from student where stu_sex is not null ;
 *  `in`、`not in`
    
        select * from student where stu_age in (18,28,38);
        select * from student where stu_age not in (18,28,38);
        -- 使用not in的时候如果表中有null的值的话，则查询无结果，需要先把null值过滤掉，这是MySQL的bug
        select * from student where stu_age is not null and  stu_age not in (18,28,38);
    
     *  `in 、not in`后面的集合可以是具体值也可以是个子查询。
     *  `not in`在mysql中是有bug的，需要把null值给排除掉。
 *  `and` 、`or`、`not`
    
        select * from student where stu_name is not null and stu_age > 18 ;
        select * from student where stu_age > 18 or stu_sex = '男';
        select * from student where not stu_age>18 ;
    
     *  `and`效果同`&&`、`or`效果同`||`、`not`效果同`!`
 *  `like`
    
     *  用来进行模糊查询
     *  占位符`_`表示任意一个字符
     *  占位符`%`表示任意N个字符
        
            -- 查询姓名第2个字符是h的学生信息
            select * from student where stu_name like '_h%'

**所有null值不会参与运算，如果需要参与可以使用IFNULL函数。**

-- 查询所有年龄小于18岁的学生信息，如果年龄字段为null按照0处理。
     SELECT * from student where IFNULL(stu_age,0) < 18 ;

### 2.6.3 排序查询 ###

*  语法`order by 排序字段1 排序方式1，排序字段2 排序方式2...`
 *  `ASC`升序（默认），`DESC`降序
 *  多字段排序时，只有前面字段值相同时才把相同字段值的数据按照后面的排序字段跟方式排序。
    
        -- 先按照年龄进行升序排序，年龄相同的再按照id降序排序
        select * from student order by stu_age asc ,stu_id desc ;

### 2.6.4 聚合函数 ###

将一列数据作为一个整体，进行纵向的计算。

1.  `count`
    
    计算个数，一般选择非空的列，比如主键。计算表数据总条数一般使用`count(*)`
    
        -- 计算学生表中数据总条数
        select count(*) as total_num from student ;
2.  `max`、`min`、`avg`、`sum`
    
    分别表示计算列的最大值、最小值、平均值、总和。
    
        select max(stu_age) as max_age from student ;
        select min(stu_age) as min_age from student ;
        select avg(stu_age) as avg_age from student ;
        select sum(stu_age) as sum_age from student ;
        -- 查询student表中最大年龄、最小年龄、平均年龄、总年龄
        select  max(stu_age) max_age ,min(stu_age),avg(stu_age),sum(stu_age)  from student ;
    
     *  使用了聚合函数后不能查询一般字段，如果使用了分组可以使用分组字段。
     *  `where`后面不能跟聚合函数

### 2.6.5 分组查询 ###

*  语法：`group by 分组字段 [having 条件]`
    
        -- 按照性别进行分组
        select  stu_sex from  student2 group by stu_sex    ;
        -- 按照性别分组，分组后查询平均年龄>18的分组信息
        select  avg(stu_age), stu_sex from student2 group by stu_sex having avg(stu_age) > 18
    
     *  分组后查询字段只能是分组字段跟聚合函数，非分组共有字段则无意义。
     *  `where`跟`having`的区别？
        
         *  `where`跟`having`都表示条件过滤，`where`是先过滤在分组，`having`是先分组在过滤。
         *  `where`后面不能跟聚合函数，`having`后可以跟聚合函数。

### 3.2.6 分页查询 ###

*  `limit X , Y` ;
    
    X为起始位置的索引，Y为取多少条数据。
    
        -- 从索引为0的位置开始，取5条数据
        select * from student limit 0 , 5 ;
 *  假设每页数据为`pageSize`，当前页数为`pageNum`，则取出当页数据的SQL公式为
    
    `select * from student limit (pageNum-1)*pageSize , pageSize`
 *  只有MySQL可以通过`limit`分页，其它数据库不能使用这种方式。

# 三、数据类型 #

MySQL中定义数据字段的类型对你数据库的优化是非常重要的。MySQL支持多种类型，大致可以分为三类：数值（整型/小数）、日期/时间和字符串(字符)类型。

## 3.1 整型 ##

<table> 
 <thead> 
  <tr> 
   <th align="center"><strong>类 型</strong></th> 
   <th align="center"><strong>大 小</strong></th> 
   <th align="center"><strong>范围（有符号）</strong></th> 
   <th align="center"><strong>范围（无符号）</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">TINYINT(m)</td> 
   <td align="center">1byte</td> 
   <td align="center">(-128，127)</td> 
   <td align="center">(0，255)</td> 
  </tr> 
  <tr> 
   <td align="center">SMALLINT(m)</td> 
   <td align="center">2byte</td> 
   <td align="center">(-32 768，32 767)</td> 
   <td align="center">(0，65 535)</td> 
  </tr> 
  <tr> 
   <td align="center">MEDIUMINT(m)</td> 
   <td align="center">3byte</td> 
   <td align="center">(-8 388 608，8 388 607)</td> 
   <td align="center">(0，16 777 215)</td> 
  </tr> 
  <tr> 
   <td align="center">INT(m)或INTEGER(m)</td> 
   <td align="center">4byte</td> 
   <td align="center">(-2 147 483 648，2 147 483 647)</td> 
   <td align="center">(0，4 294 967 295)</td> 
  </tr> 
  <tr> 
   <td align="center">BIGINT(m)</td> 
   <td align="center">8byte</td> 
   <td align="center">(-9223372036854775808，9223372036854 775 807)</td> 
   <td align="center">(0，18446744073709551 615)</td> 
  </tr> 
 </tbody> 
</table>

*  参数m表示的显示宽度
    
        -- 这里参数5指的是显示的宽度而已，当数字超过则不受此限制
        create table t (col int(5) ZEROFILL);
        insert into t values(1),(12),(123),(1234),(12345),(123456) ;
    
     *  参数m只是当位数小于m时候显示的宽度，对数字大小没有限制，意义不大。
     *  `ZEROFILL`表示显示长度不够的用0占位。
     *  需要在**命令行**中输出。
     *  如果实际插入的值超过了类型的范围限制则报错，无法插入。
     *  如果参数m不给出会有默认值，每种类型的默认值不同。
     *  默认是有符号的，如果要定义无符号的需要加上关键字`UNSIGNED`
     *  **定义了`ZEROFILL`则直接是有无符号的，`ZEROFILL`跟有符号冲突，只能选择1个**。
        
            create table t (col int  );
            create table t (col integer(4) UNSIGNED );
            create table t (col tinyint UNSIGNED ZEROFILL  );

## 3.2 浮点 ##

<table> 
 <thead> 
  <tr> 
   <th align="center"><strong>类 型</strong></th> 
   <th align="center"><strong>大 小</strong></th> 
   <th align="center">含 义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">float(m,d)</td> 
   <td align="center">4byte</td> 
   <td align="center">单精度浮点型，m总个数，d小数位</td> 
  </tr> 
  <tr> 
   <td align="center">double(m,d)</td> 
   <td align="center">8byte</td> 
   <td align="center">双精度浮点型，m总个数，d小数位</td> 
  </tr> 
 </tbody> 
</table>

*  m表示整个小数的总长度，d表示小数位的长度
    
        create table t (col FLOAT(6,3));
        
        insert into t values(123.45678);  -- 123.457
        insert into t values(1234.56); -- 报错，整数部分超出范围
        insert into t values(12.3456789); -- 12.346
        insert into t values(12.3); -- 12.300，后面的0需在命令行显示
    
     *  也可以不给（m,d）值，直接使用float跟double，则默认会按照实际的精度（由实际的硬件跟操作系统决定）来显示。
     *  （m,d）会采取四舍五入，因此会出现精度丢失的问题。
     *  （m,d）的用法是非标准用法，如果要用于数据库的迁移，则最好不要这么使用。

## 3.3 定点数 ##

浮点型在数据库中存放的是近似值，而定点类型在数据库中存放的是精确值。

*  `decimal(P,D)`
    
     *  P表示有效数字的精度，P的范围为\[1，65\]
     *  D是表示小数点后的位数，D的范围是\[0,30\]，MySQL要求D<=P
     *  `DECIMAL(6,2)` 表示此列最多可以存储`6`位数字，小数位数为`2`位; 因此，此列的范围是从`-9999.99`到`9999.99`
     *  `DECIMAL(P)`相当于`DECIMAL(P,0)`
     *  DECIMAL在不指定精度时，默认整数为10，小数为0

## 3.4 日期和时间类型 ##

<table> 
 <thead> 
  <tr> 
   <th align="center"><strong>类 型</strong></th> 
   <th align="center"><strong>大 小</strong></th> 
   <th align="center"><strong>含 义</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">DATE</td> 
   <td align="center">3</td> 
   <td align="center">表示日期，包含年月日</td> 
  </tr> 
  <tr> 
   <td align="center">TIME</td> 
   <td align="center">3</td> 
   <td align="center">表示时间，包含时分秒</td> 
  </tr> 
  <tr> 
   <td align="center">YEAR</td> 
   <td align="center">1</td> 
   <td align="center">表示年份</td> 
  </tr> 
  <tr> 
   <td align="center">DATETIME</td> 
   <td align="center">8</td> 
   <td align="center">表示日期，包含年月日，时分秒</td> 
  </tr> 
  <tr> 
   <td align="center">TIMESTAMP</td> 
   <td align="center">4</td> 
   <td align="center">表示日期，包含年月日，时分秒，自动更新</td> 
  </tr> 
 </tbody> 
</table>

-- 定义col列，datetime类型，默认值'2018-07-14 10:11:09'
    create table t(id int ,col DATETIME DEFAULT '2018-07-14 10:11:09' );
    
    -- 定义col列，timestamp类型，默认为当前系统时间
    create table t(id int ,col TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
    
    -- 定义col列，timestamp类型，当本行数据有改动则自动更新col列的值为系统最新时间，下面2个SQL等效
    create table t(id int ,col TIMESTAMP );
    create table t(id int ,col TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ); -- v5.5需要此设置
    
    -- 插入数据，时间字段为当前系统时间
    insert into t (col) values(CURRENT_TIMESTAMP);
    insert into t (col) values(CURRENT_TIMESTAMP());
    insert into t (col) values(SYSDATE());
    insert into t (col) values(NOW());
    
    -- 插入指定时间
    insert into t (col) values('2020-07-14 14:05:06');
    insert into t (col) values(STR_TO_DATE('2019-08-07 14:05:06','%Y-%m-%d %H:%i:%s') );
    
    -- 查询日期时间字段
    select DATE_FORMAT(col,'%Y-%m-%d %H:%i:%s ') from t ;

*  `%Y`表示年，`%m`表示月，`%d`表示天，`%H`表示小时，`%i`表示分，`%s`表示秒。
 *  `YEAR`、`TIME`、`DATE`用法类似

## 3.5 字符 ##

<table> 
 <thead> 
  <tr> 
   <th align="center">类 型</th> 
   <th align="center"><strong>含 义</strong></th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">char(n)</td> 
   <td align="center">固定长度，最多255个字符</td> 
  </tr> 
  <tr> 
   <td align="center">varchar(n)</td> 
   <td align="center">固定长度，最多65535个字符</td> 
  </tr> 
  <tr> 
   <td align="center">tinytext</td> 
   <td align="center">可变长度，最多255个字符</td> 
  </tr> 
  <tr> 
   <td align="center">text</td> 
   <td align="center">可变长度，最多65535个字符</td> 
  </tr> 
  <tr> 
   <td align="center">mediumtext</td> 
   <td align="center">可变长度，最多2的24次方-1个字符</td> 
  </tr> 
  <tr> 
   <td align="center">longtext</td> 
   <td align="center">可变长度，最多2的32次方-1个字符</td> 
  </tr> 
  <tr> 
   <td align="center">BLOB</td> 
   <td align="center">二进制形式的长文本数据</td> 
  </tr> 
  <tr> 
   <td align="center">MEDIUMBLOB</td> 
   <td align="center">二进制形式的中等长度文本数据</td> 
  </tr> 
  <tr> 
   <td align="center">LONGBLOB</td> 
   <td align="center">二进制形式的极大文本数据</td> 
  </tr> 
 </tbody> 
</table>

*  `char(n)`若存入字符数小于n，则以空格补于其后，查询之时再将空格去掉。所以`char`类型存储的字符串末尾不能有空格，varchar不限于此。定长，浪费空间，速度快。
 *  `varchar(n)`是存入的实际字符数+1个字节（n<=255）或2个字节(n>255)，所以`varchar(4)`存入3个字符将占用4个字节。不定长，节省空间。
 *  `text`不用指定长度n，`text`是实际字符数+2个字节。text类型不能有默认值。
 *  `BLOB`都是存储二进制数据，存储的数据只能整体读出。

## 3.6 枚举 ##

实现将所有可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个。

*  语法：`enum（可能出现的元素列表）`
    
        -- 定义sex列，类型为枚举，值只能是列表中的某一个值，可以为null
        create table t(sex enum('男','女') );
         
        -- 插入数据，值如果非null则必须为枚举列表中的一个
        insert into t(sex) values ("男");
        
        -- 插入数据，枚举值也可以根据索引来引用，从1开始。
        insert into t(sex) values (2);

# 四、约束 #

对表中的数据进行限定，保证数据的正确性、有效性和完整性。

分类：

*  主键约束：primary key
 *  非空约束 : not null
 *  唯一约束 : unique
 *  外键约束 : foreign key

## 4.1 主键约束（重点） ##

主键，又称主码，是表中一列或多列的组合。主键要求主键列的数据**唯一**，并且**不允许为空**，主键能够唯一地表识表中的一条记录。一个表只能有一个主键，但是可以是多列（复合主键）。

*  使用
    
        -- 方式1: 直接在主键字段后标识
        create table t(
        	id int primary key ,
        	name varchar(10)
        );
        
        -- 方式2： 后面专门定义主键约束，如果是复合主键（多列）只能使用这种方式
        create table t(
        	id int,
        	name varchar(10) ,
        	primary key(id)
        );
        
        -- 方式3：在已存在的表添加主键约束
        alter table t modify id int primary key;
        alter table t add constraint id_pri primary key (id)
        
        
        -- 删除主键约束
        alter table t drop primary key;

## 4.2 非空约束 ##

定义表的时候限定为非空约束后则此字段不能有null值。

*  使用
    
        -- 创建表的时候给指定列添加非空约束
        create table t(	 
        	stu_name varchar(10) not null
        );
        
        -- 在表已经存在的基础上添加非空约束
        alter table t modify stu_name varchar(10) not null;
        
        -- 删除非空约束
        alter table t modify stu_name varchar(10);

## 4.3 唯一约束 ##

限定列数据不能有重复的值，null可以重复。

*  使用`unique`
    
        -- 创建表的时候给指定列加上唯一约束
        create table t(	 
        	stu_name varchar(10) unique 
        );
        
        -- 在表存在的前提下添加唯一约束
        alter table t modify stu_name varchar(10) unique;
        alter table t add constraint name_unique unique(stu_name);
        
        
        -- 删除列上的唯一约束
        alter table t drop index stu_name
        -- 通过约束名删除列上的唯一约束
        alter table t drop index name_unique;

## 4.4 默认约束 ##

限制某列数据当没有给定值的时候使用指定的默认值，关键字是`default`。

-- 创建表的时候给指定列加上默认约束
    create table t(	 
    	stu_age int default 18  
    );
    
    -- 在表存在的前提下添加默认约束
    alter table t modify stu_age int DEFAULT 18;
    
    -- 删除默认约束
    alter table t modify stu_age int

## 4.5 自动增长 ##

当某列是数值时，我们可以给这列设置，让它自动增长。一般跟数值类型的主键一起使用。关键字`auto_increment`。

*  使用
    
        -- 创建表的时候直接定义自增长
        create table student(
        	s_id int primary key auto_increment,
        	s_name varchar(10) 
        ) auto_increment=10000 -- 设置自动增长从10000开始
        
        -- 设置自动增长从10000开始
        alter table student  auto_increment=10000;
        
        -- 给已经存在的表字段添加自增长
        alter table student s_id int auto_increment;
        
        -- 删除自增长
        alter table student modify s_id int ;
    
     *  添加自动增长以后，也同样可以对字段进行主动赋值。
     *  如果手动赋值跳跃性，则后面会直接根据最大值++。

## 4.6 外键约束 ##

让表与表产生关系，从而保证数据的正确性。

*  使用`foreign key`
    
        -- 先创建学生表，父表
        create table student(
        s_id int primary key ,
        	s_name varchar(10) 
        );
        
        -- 再创建子表
        create table score (
        	s_id int ,
        	s_name VARCHAR(10),
        	s_socre int ,
        	user_id int ,
            -- 指定字段user_id是外键，指向父表student表中的s_id字段，外键必须指向父表的主键
        	-- constraint s_foreign 这里可以加上，给这个外键取个名字  
            FOREIGN KEY(user_id) references student(s_id)
         ); 
         
        -- 创建表后再添加外键约束，score_foreign为给这个外键约束取的名字
         alter table score add constraint score_foreign foreign key (user_id) references student(s_id)  
         
         --  删除外键约束，score_foreign为外键约束取的名字，如果没有手动取名则改为使用默认生成的约束即可。
         alter table score drop foreign key score_foreign;
    
     *  创建完父表与字表的外键约束关系后，则子表的外键字段值如果不为null的话，则必须是主表中的存在值
     *  建立好外键约束关系后，如果子表外键值使用了主表的一个值，则默认情况下主表的这个值不能删除或者改成别的值。如果不要这个默认操作需要在定义外键的时候设置。
 *  级联删除、级联更新
    
     *  当删除主表的数据时，子表的行为可以设置级联删除跟级联更新，也可以设置null删除，设置null更新。
        
            create table score (
            	s_id int ,
            	s_name VARCHAR(10),
            	s_socre int ,
            	user_id int ,
                -- 设置外键时，级联删除跟级联更新
            	FOREIGN KEY(user_id) references student(s_id) on delete cascade on update cascade 
             );
             
             -- 设置外键时，级联删除跟级联更新
             alter table score add constraint score_foreign foreign key (user_id) references student(s_id) on update cascade on delete cascade 
             
             create table score (
            	s_id int ,
            	s_name VARCHAR(10),
            	s_socre int ,
            	user_id int ,
                -- 设置外键时，set null删除跟set null更新
            	FOREIGN KEY(user_id) references student(s_id) on delete set null on update set null );
             
              -- 设置外键时，set null删除跟set null更新
             alter table score add constraint score_foreign foreign key (user_id) references student(s_id) on update set null on delete set null

# 五、多表查询 #

*  如果有表A,B那么查询出来的数量等于A\*B，这个现象被称为笛卡尔积
    
        select * from student,score ;
 *  笛卡尔积：
    
     *  有两个集合A,B，取这两个集合的所有组成情况。
     *  要完成多表查询，需要消除无用的数据。

## 5.1 内连接 ##

*  隐式内连接: 通过`where`消除无用数据。
    
        select * from student,score where student.s_id = score.user_id ;
 *  显式内连接`INNER JOIN on`
    
        select * from student INNER JOIN score on student.s_id = score.user_id ;

## 5.2 左（外）连接 ##

*  显示左连接`left JOIN on`
    
        select * from student left join score on student.s_id = score.user_id ;

## 5.3 右（外）连接 ##

*  显示左连接`right JOIN on`
    
        select * from student right join score on student.s_id = score.user_id ;

## 5.4 子查询 ##

查询中嵌套查询，称嵌套查询为子查询。

# 六、事务 #

## 6.1 事务的概念 ##

如果一个包含多个SQL步骤的业务操作，把这些操作放入一个事务中，这些操作要么同时成功，要么同时失败。

*  操作
    
        -- 开启事务,这2种方式等效
        start transaction;
        begin;
        
        -- 提交事务，这2种方式等效
        commit;
        commit work;
        
        -- 回滚事务，这2种方式等效
        rollback;
        rollback work;
    
     *  MySQL中默认自动提交事务。
     *  可以通过`SELECT @@autocommit;`查看事务的默认提交方式，`0`为手动提交，`1`为自动提交。
     *  修改默认提交方式： `set @@autocommit = 1;`

## 6.2 事务的特性 ##

事务有4大特性，简称**ACID**。

*  原子性(Atomicity)：是不可分割的最小操作单位，要么同时成功，要么同时失败。
 *  一致性(Consistency)：事务操作前后，数据总量不变。
 *  隔离性(Isolation)：多个事务之间。相互独立。
 *  持久性(Durability)：当事务提交或回滚后，数据库会持久化的保存数据。

## 6.3 事务的并发问题 ##

*  **脏读**：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
 *  **不可重复读**：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
 *  **幻读**：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了修改或者删除操作并提交，导致事务A多次读取同一数据时，莫名的多出了一些之前不存在数据，或者莫名的丢了一些数据。像发生了幻觉一样。
    
     *  不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。
     *  解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

## 6.4 事务的隔离级别 ##

<table> 
 <thead> 
  <tr> 
   <th align="center">事务隔离级别</th> 
   <th align="center">脏读</th> 
   <th align="center">不可重复读</th> 
   <th align="center">幻读</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">读未提交（read-uncommitted）</td> 
   <td align="center">是</td> 
   <td align="center">是</td> 
   <td align="center">是</td> 
  </tr> 
  <tr> 
   <td align="center">不可重复读（read-committed）</td> 
   <td align="center">否</td> 
   <td align="center">是</td> 
   <td align="center">是</td> 
  </tr> 
  <tr> 
   <td align="center">可重复读（repeatable-read）</td> 
   <td align="center">否</td> 
   <td align="center">否</td> 
   <td align="center">是</td> 
  </tr> 
  <tr> 
   <td align="center">串行化（serializable）</td> 
   <td align="center">否</td> 
   <td align="center">否</td> 
   <td align="center">否</td> 
  </tr> 
 </tbody> 
</table>

*  MySQL默认隔离级别为`repeatable-read`
 *  Oracle默认隔离级别是`read-committed`
 *  `Serializable`强制的进行排序，在每个读读数据行上添加共享锁。会导致大量超时现象和锁竞争。
 *  隔离界别从小到大安全性越来越高，但是效率越来越低。
 *  查询数据库隔离级别`select @@tx_isolation;`
 *  设置数据库隔离级别`set global transaction isolation level 级别字符串;`

# 七、其它 #

## 7.1 添加用户 ##

*  `CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'`
    
        CREATE USER 'hello'@'localhost' IDENTIFIED BY '123';

## 7.2 删除用户 ##

*  `DROP USER '用户名'@'主机名'`
    
        -- 删除本机上的hello用户
        DROP USER 'hello'@'localhost'

## 7.3 修改密码 ##

*  `SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');`
    
        SET PASSWORD FOR 'hello'@'localhost' = PASSWORD('789');

## 7.4 修改root密码 ##

*  停止mysql服务:`net stop mysql`
 *  无验证方式启动mysql: `mysqld --skip-grant-tables`
 *  打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功
 *  接着运行`use mysql;`跟`update user set password = password('你的新密码') where user = 'root';`
 *  关闭2个CMD窗口
 *  打开任务管理器，手动结束mysqld.exe 的进程或者重启
 *  启动mysql服务
 *  使用新密码登录。

## 7.5 授予权限 ##

*  `grant 权限列表 on 数据库名.表名 to '用户名'@'主机名' identified by '密码' ;`
    
        -- 给hello用户所有的权限，操作blb_db数据库下的student表  
        GRANT ALL ON blb_db.student TO 'hello'@'localhost' identified by '123';
    
     *  权限列表代表14种权限，分别为：`select,insert,update,delete,create,drop,index,alter,grant`等，`ALL`代表所有的。
     *  用户地址可以是localhost，可以使ip地址、机器名字、域名。也可以用’%'表示任何地址连接。

## 7.6 查看权限 ##

*  `SHOW GRANTS FOR '用户名'@'主机名';`
    
        SHOW GRANTS FOR 'hello'@'localhost';

## 7.6 撤销权限 ##

*  `revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';`
    
        revoke all on blb_db.student from 'hello'@'localhost';

## 7.7 数据备份 ##

*  方式1：`mysqldump -u用户名 -p密码 数据库的名称 > 保存的路径`
    
     *  `mysqldump -uroot -proot blb_db>d://a.sql`
 *  方式2：图形工具

## 7.8 数据还原 ##

*  命令行方式
    
     *  登录数据库
     *  创建并使用数据库
     *  source 文件路径
 *  方式2：图形工具

# 八、案例 #

[MySQL基础语法使用案例][MySQL]

# 九、总结 #

主要学习内容是表的增删改查和表数据的增删改查，其次是数据类型和约束在建表的时候会用到。  
最重要的是表数据的查询。