Mysql 的基本使用

爱被打了一巴掌 2022-04-23 22:46 130阅读 0赞

## 数据库的基本操作 ##

### 什么是数据库 ###

> *数据库*就是储存数据的地方。在电脑中,在内存中,在硬盘中的东西都是存储在*数据库*中的数据。而这些数据所待的地方就叫做*数据库*

### 常用的数据库 ###

1.  SQL SERVLER
2.  Oracle
3.  MySQL
4.  Postgre SQL

### SQL语言分类 ###

1.  DDL 数据库定义语言
2.  DCL数据库控制语言
3.  DML 数据库操作语言
4.  DQL 数据库查询语言

### 常用数据库基本操作 ###

1.  登入： `mysql -uroot -p`
2.  获取数据库版本号： `mysql -V`or `mysql -version`
3.  登录并打开指定数据库：`mysql - uroot-p - D db_name`
4.  退出： `exit`or `quit`

### sql语法规范 ###

1.  常用Mysql 的关键字大写，库名， 和表名，字段名小写
2.  SQL 支持折行操作，拆分时不要拆分一个完整的单词
3.  数据库名和表名，字段名不要使用MYSQL的保留字，必须要使用，需要反引号起来`'字段'`

### 常用SQL语句 ###

`SELECT USER()`得到登入的用户

`SELECT VERSION()` 获取SQL信息

`SELECT NOW()`得到当前时间

`SELECT DATABASE()`获取当前打开的数据库

## SQl语句的相关操作 ##

--------------------

### 数据库相关操作 ###

1.  创建数据库:`CREATE{DATABASE or SCHEMA}db_name`
2.  创建数据库时判断名称是否存在，不存在则创建：`CREATE {DATABASE or SCHEMA}[IF NOT EXISTS]db_name`
3.  查看当前服务器下所有数据`SHOW DATABASES;`
4.  查看指定的数据库 `SHOW DATABASE db_name`
5.  修改指定的数据库的编码方式 `ALTER DATABASER db_name CHARSET='UTF8'`
6.  打开指定的数据库 `USE db_name`
7.  删除指定的数据库 `DROP DATABASE db_name`
8.  删除指定的数据库如果存在则删除 `DROP DATABASE IF NOT EXISTS db_name`

## MySQL 的数据结构 ##

### 数值型 ###

#### 整数型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>值范围</th> 
   <th>字节大小</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>TINYINT</code></td> 
   <td>有符号值-128—127 无符号 0到255</td> 
   <td>1 字节</td> 
  </tr> 
  <tr> 
   <td><code>SMALLUT</code></td> 
   <td>有符号值 -32768—32767无符号 0到62235</td> 
   <td>2字节</td> 
  </tr> 
  <tr> 
   <td><code>INT</code></td> 
   <td>有符号值 -32768—32767无符号 0到62235</td> 
   <td>4字节</td> 
  </tr> 
  <tr> 
   <td><code>BIGINT</code></td> 
   <td>有符号 -2<sup>63—2</sup>63-1 无符号 0— 2^64 -1</td> 
   <td>8字节</td> 
  </tr> 
 </tbody> 
</table>

#### 浮点型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>值范围</th> 
   <th>字节大小</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>FLOAT(M,T)</code></td> 
   <td>负数取值范围 -3.40E+38 到-1.74E-38 0和1.175E-38到 3.40E+38 M是数字总数，T 是小数点中的后面位数</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td><code>DOUBLE</code></td> 
   <td>-1.79E+308 到- 2.2E-308</td> 
   <td>8</td> 
  </tr> 
  <tr> 
   <td><code>DECIMAL(M,D)</code></td> 
   <td>和Double一样内部义字符串形式存储数值</td> 
   <td>M+2</td> 
  </tr> 
 </tbody> 
</table>

#### 字符型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>存储需求</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>CHAR(M)</code></td> 
   <td>M个字节 0&lt;M&lt;255</td> 
  </tr> 
  <tr> 
   <td><code>VARCHAR(M)</code></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td><code>TINYEXT</code></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td><code>TEXT</code></td> 
   <td></td> 
  </tr> 
  <tr> 
   <td><code>('VALUER1','VALUER2',…)</code></td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

> 1.  CHAR效率高于VARCHAR,CHAR相当于拿空间换时间，VARCHAR拿时间换空间
> 2.  TEXT列不能有默认值,检索的时候不存在大小写转换
> 3.  CHAR默认存储数据的时候，后面会用空格填充到指定长度；而在检索的时候会去掉后面空格；VARCHAR在保存的时候不进行填充，尾部的空格会留下

#### 时间类型 ####

<table> 
 <thead> 
  <tr> 
   <th>类型</th> 
   <th>储存需求</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>TIME</code></td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td><code>DATE</code></td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td><code>DATETIME</code></td> 
   <td>8</td> 
  </tr> 
  <tr> 
   <td><code>YERA</code></td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

### 数据表的操作 ###

> 数据表是数据库最重要的一部分，数据是保存咋数据表中的，它是由行和列组成，每个数据表至少有一列行可以零行和一行 表名必须唯一不能为数据的的关键字最好听命知意

1.  创建表

CREATE TABALE IF NOT EXISTS tbl_name(
    字段名称 字段类型 [完整约束条件],
    字段名称 字段类型 [完整约束条件]
    )ENGIN=存储引擎 CHARSET=编码方式

完整约束条件：
    1.UNSIGNED :无符号，没有负数，从零开始
    2.ZEROFILL : 零填充 当显示长度不够设置的长度时用0填充
    3. DEFAULT : 默认值如果插入的时候没有填写值，则使用默认值
    4. NOT NULL ：非空约束，表示这个插入这个元素的时候必须给值，值不为空
    5. PRIMARY KEY:主键标识记录的唯一性,一张表只能有一个主键，自动禁止为空
    6. AUTO_INCREMENT:  自动增长,只能用于数值列，配合索引使用从1开始每次增长1
    7. UNIQUE KEY: 唯一性 一个表可以多个唯一索引约束，表示值只能不能重复
    8. FOREIGN KEY: 外键约束

1.  查看当前数据库下的表 :`SHOW TABALES`
2.  查看表的详细信息: `SHOW CREATE TABLE tbl_name`
3.  查看表结构：`DESC tal_name`
4.  删除指定表：`DROP TABLE tbl_name`

#### 表结构的操作 ####

1.  添加字段:`ALTEA TABLE tbl_name ADD 字段名 字段属性 字段完整约束[FRIST]or[AFTER] 字段名称(添加某字段前和后)`
2.  删除字段：`ALTER TABLE tbl_name DROP 字段名称`
3.  添加默认值： `ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT 默认值`
4.  删除默认值： `ALTER TABLE tbl_name ALTER 字段名 DROP DEFAULT`
5.  修改字段属性 `ALTER TABLE tbl_name MODIFY 字段名 字段属性 字段约束`
6.  修改字段名称和字段属性 `ALTER TABLE tbl_name CHANGE 字段原名称 字段新名称 字段属性 字段约束`
7.  添加约束：`ALTER TABLE tbl_name ADD PRIMARY KEY(字段名称)`
8.  删除唯一：`ALTER TABLE tbl_name DROP index_name`
9.  修改表名称 `ALTER TABLE tbl_name RENAME 原表名称 现表名称`
10. 修改自增长AUTO\_INCREMENT的值:`ALTER TABLE tbl_name AUTO_INCREMENT=值`

--------------------

### MySQL 数据操作 ###

*  添加记录
    
    1.  INSERT IN TO tb\_name (属性1， 属性2，…)VALUSE (属性值 ，属性值)
    2.  不指定字段 :`INSER INTO tb_name()VALULE(values ....)`
    3.  插入指定字段 ：`INSERT INTO db_name(属性1， 属性2，...)VALUES (相对应值1 ，值2 ...)`
    4.  一次插入多次记录：`INSERT INTO tb_name(属性1， 属性2，...)VALUSE (属性值 ，属性值),(...),(...)`
 *  修改记录
    
    1.  `UPDATE INTO db_name SET 字段名='值'，字段名='值'，... WHERE [约束条件]`
    
    > 如果不添加条件 该表的该字段的所有值都会改变
 *  删除记录

1.  `DELECT FROM db_name [WHERE 约束条件]`

> 如果不填写约束条件该清空该表 但是设置的`AUTO_INCREMEN`不会重置需要使用`ALTER`语句清空

1.  清空数据并重置`AUTO_INCREMEN`：`TRUNCATE tbl_name`

*  查询语句

1.  查询所有语句：`SELECT * FROM tbl_name`
2.  查询指定字段语句：`SERECT '字段名' FROM tbl_name`
3.  查询指定数据库的表名中的字段：`SELECT 字段名 FROM db_name.tbl_name`
4.  查询指定字段并起别名：`SELECT '字段名'AS '别名'FROM db_name`
5.  查询指定表中的字段：`SELECT db_name.'字段名'FROM db_name`

*  WHERE 条件

1.  比较运算符 \`\`> < = >= <= !=\`\`\`
2.  检验是否为空\[非空\]`IF [NOT ]NULL`
3.  指定范围 `[NOT] BETWEEN ... AND`
4.  指定集合 `[NOT] IN(值,...)`
5.  逻辑运算符 ```````AND``````OR```````
6.  匹配字符 `% 任意长度，- 任意字符`, `LINKE(值)`指定前几条字段

*  `GROUP BY`分组 :把指定的值放在一起形成一个组中 在查询结果中只要显示一条
    
        GROUP BY 子句,分组子句
        
            GROUP BY 字段/别名 [排序方式] 分组后会进行排序。
            升序：ASC，降序：DESC  
        以下[合计函数]需配合 GROUP BY 使用： count 返回不同的非 NULL值数目
          count(*)、count (字段)
        
            sum 求和
        
            max 求最大值
        
            min 求最小值
        
            avg 求平均值
        
            group_concat 返回带有来自一个组的连接的非NULL 值的字符串结果。组内字符串连接。
 *  `HAVING`子句，条件子句
    
     *  与`WHERE`功能语句相同,执行时机不同
     *  `WHERE` 在开始时对元素进行过滤 `HAVING` 对筛选出的结果再次过滤
     *  `HAVING`是查询出来了 `WHERE` 必须表内存在的
     *  `WHERE` 不可以使用字段的别名，`HAVING`可以。因为执行WHERE代码时，可能尚未确定列值
     *  `WHERE` 不可以使用合计函数。一般需用合计函数才会用 having
     *  SQL标准要求`HAVING`必须引用`GROUP BY`子句中的列或用于合计函数中的列
 *  `ORDER BY`语句 子句，排序语句
    
     *  `ORDER BY`排序字段/别名 排序方式\[排序字段/别名 排序方式\] 升序 ASC 降序 DESC
 *  `LIMIT`子句 限制结构数量子句
    
     *  `仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合，按照记录出现的顺序，索引从0开始，limit 起始位置, 获取条数`

### 子查询 ###

*  `FROM` 型
    
    from 后要求是一个表,必须给子查询取个别名
    
     *  简化每个查询条件
     *  from 型会生成一个临时表格，可以用原表的锁定释放
     *  子查询返回一个表 表型子查询
    
        SELECT * FROM (SELECT * FROM tb WHERE id>1) AS SUBFROM WHERE id>1

*  `WHERE型号`
    
     *  子查询返回一个值，标量子查询
     *  不需要给子查询去别名
     *  `WHERE` 子查询内的表，不能直接用以更新
        
         *  列子查询 ：如果子查询的返回是一列 使用`IN`或者`NOT IN` 完成查询
         *  `EXISTS` 或者`NOT EXISTS` 作为条件
         *  `SELECT test FEOM demo WEHERE EXIST(SELECT * FROM demo2 )`
     *  行子查询
     *  查询的是一行\`\`\`
     *  `select * from t1 where (id, gender) in (select id, gender from t2);`

### 多表连接查询：将多个表的字段进行连接，可以指定连接条件 ###

*  内连接 ：默认就像内连接(inner join)
    
     *  默认就是内连接可省略inner
     *  只有数据存在才能发送连接 即连接结构不能粗线空行，也可以用on/where 表示 连接条件
     *  `select * from tb1 cross join tb2`
 *  外链接
    
     *  左外链接 ：`SElECT 字段名称 FROM tb_name1 LEFT JOIN til_name2 ON 条件`； 先显示左表记录，再去右表查询符合条件的记录，不符合的以NUll代替
     *  右外链接 SELECT 字段名称 `FROM tb_name1 RIGHT JOIN tb_name2 ON`条件
 *  外键约束：只有InnoDB存储引擎支持外键
    
     *  \[CONSTRAINT 外键名称 \]FOREIGN KEY(字段名称) REFERENCES 主表(字段名称)
        
         *  子表的外键字段和主表的主键字段类型要相似；如果是数值型要求一致，并且无符号也要一致；如果是字符型，要求类型一致，长度可以不同
         *  如果外键字段没有创建索引，MySQL会自动帮我们添加索引  
            子表的外键关联的必须是父表的主键
    
     *  外键约束的参照操作
        
         *  CASCADE  
            从父表删除或更新，子表也跟着删除或者更新，级
         *  SET NULL  
            从附表删除或者更新记录，并设置子表的外键NULL
         *  NO ACTION | RESTRICT  
            拒绝对父表做更新或者删除操作
    
    ## 事务 ##
    
    > 概述：事务是 事务指的是逻辑上的一组操作,组成这组操作的各个单元要么全都成功,要么全都失败.
    > 
    > 事务作用：保证在一个事务中多次操作要么全都成功,要么全都失败.
    
    ### 事务的操作 ###

*  `start transaction` ：开始事务
 *  `commint`: 提交事务
 *  `rollback` : 回滚
    
    ![事务操作流程][AyCfzV.png]
    
    ### 事务的三大特性 ###

1.  原子性： 事务包装的一组sql 要么都执行成功，要么都失败。操作是不可逆的
2.  一致性：数据库的状态是一致的
3.  持久性： 事务成功提交之后，对于数据库的改变是永久的 **哪怕数据库发生异常，重启之后数据亦然存在**
4.  隔离性：一个事务的成功与否对其他事务没有影响

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

> 在事务的并发情况,不考虑事务的隔离性 回发生
> 
>  *  脏读 — 最严重不可发生的:指一个事务读取了另外一个事务 未提交的数据
>  *  不可重复读 ：在一个事务内多次读取表中的数据，多次读取的结果不同
>  *  幻读 ：
>     
>     ![幻读和不可重复读][AyipcV.png]

#### 隔离级别 ####

> 数据库规定了4中隔离级别

1.  `read uncommitted`读未提交，一个事务读到另一个事务没有提交的数据。

*  存在：3个问题（脏读、不可重复读、虚读）。
 *  解决：0个问题

1.  `read committed` 读已提交，一个事务读到另一个事务已经提交的数据。

*  存在：2个问题（不可重复读、虚读）。
 *  解决：1个问题（脏读）

1.  `repeatable read`:可重复读，在一个事务中读到的数据始终保持一致，无论另一个事务是否提交。

*  存在：1个问题（虚读）。
 *  解决：2个问题（脏读、不可重复读）

1.  `serializable`串行化，同时只能执行一个事务，相当于事务中的单线程。

*  存在：0个问题。
 *  解决：3个问题（脏读、不可重复读、虚读）

#### 隔离离别的安全性和性能差距 ####

*  安全性: `serializable > repeatable read > read committed > read uncommitted`
 *  性能： `serializable < repeatable read < read committed < read uncommitted`

### 常用数据库的默认隔离级别 ###

*  Oracle: `read committed`
 *  Mysql ：`repeatable read`

[AyCfzV.png]: /images/20220222/7d4a7a9e4b85411d9f988be8e9b4080f.png
[AyipcV.png]: /images/20220222/009965df9f294c188e84e498b5f7ad69.png