Fork me on GitHub

mysql基础语法

历史

开源免费的数据库，小型的数据库，已经被 Oracle 收购了。MySQL6.x 版本也开始收费。后来 Sun 公司收购了 MySQL，而 Sun 公司又被 Oracle 收购。

数据库管理系统

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。

数据库管理系统、数据库和表的关系

数据库管理程序(DBMS)可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体 User 的数据。

数据库管理系统、数据库和表的关系如图所示：
image

数据库三大范式

  • 第一范式
    每一列不可再拆分，称为原子性。
  • 第二范式
    在第一范式的基础上所有列完全依赖于主键列。

当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。

  • 第三范式
    在满足第二范式的基础上，任何非主列不得传递依赖于主键，即非主键列只依赖于主键，不依赖于其他非主键。

SQL的概念

Structured Query Language 结构化查询语言。

注释

注释的语法 说明
--空格 单行注释
/**/ 多行注释

DDL(Data Definition Language)(数据库定义语言)

操作数据库

  • 创建
    • 创建数据库
      CREATE DATABASE 数据库名;
    • 判断数据库是否已经存在，不存在则创建数据库
      CREATE DATABASE IF NOT EXISTS 数据库名;
    • 创建数据库并指定字符集
      CREATE DATABASE 数据库名 CHARACTER SET 字符集;
  • 查询
    • 查看所有的数据库
      show databases;
    • 查看某个数据库的定义信息
      show create database db3;
  • 修改
    • 修改数据库默认的字符集
      ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
  • 删除
    • 删除数据库
      DROP DATABASE 数据库名;
    • 判断数据库存在，存在再删除
      drop database if exists 数据库名称;
  • 使用数据库
    • 查询当前正在使用的数据库名称
      select database();
    • 使用数据库
      use 数据库名称;

操作表

  • 创建

    • 创建表
      CREATE TABLE 表名 (
      字段名 1 字段类型 1,
      字段名 2 字段类型 2
      );

    • 数据类型分类

分类 类型名称 类型说明 JAVA中对应类型
整数 tinyInt 微整型：很小的整数(占 8 位二进制) 如果 tinyInt1isBit 配置设置为 true(默认为 true)，是java.lang.Boolean，存储空间为 1；否则是为 java.lang.Integer
整数 smallint 小整型：小的整数(占 16 位二进制) java.lang.Integer(不管是否无符)
整数 mediumint 中整型：中等长度的整数(占 24 位二进制) java.lang.Integer
整数 int(integer) 整型：整数类型(占 32 位二进制)
int(integer) java.lang.Integer；无符的话是 java.lang.Long
小数 float 单精度浮点数，占 4 个字节 java.lang.Float
小数 double 双精度浮点数，占 8 个字节 java.lang.Double
小数 decimal(p,d) 用于保留准确精确度的列 java.math.BigDecimal
日期 time 表示时间类型 java.sql.Time
日期 date 表示日期类型 java.sql.Date
日期 datetime 同时可以表示日期和时间类型 java.sql.Timestamp
字符串 char(m) 固定长度的字符串，无论使用几个字符都占满全部，M 为 0~255 之间的整数 java.lang.String(除非该列字符集设置为 BINARY，那样返回 byte[])
字符串 varchar(m) 可变长度的字符串，使用几个字符就占用几个，M 为 0~65535 之间的整数 java.lang.String(除非该列字符集设置为 BINARY，那样返回 byte[])
大文本 text 允许长度 0~65535 字节 java.lang.String
大文本 mediumtext 允许长度 0~167772150 字节 java.lang.String
大文本 longtext 允许长度 0~4294967295 字节 java.lang.String
  • 复制表：
    create table 表名 like 被复制的表名;
  • 查询：
    • 查询某个数据库中所有的表名称
      show tables;
    • 查询表结构
      desc 表名;
  • 修改：
    • 修改表名
      alter table 表名 rename to 新的表名;
    • 修改表的字符集
      alter table 表名 character set 字符集名称;
    • 添加一列
      alter table 表名 add 列名 数据类型;
    • 修改列名称 类型
      alter table 表名 change 列名 新列别 新数据类型;
      alter table 表名 modify 列名 新数据类型;
    • 删除列
      alter table 表名 drop 列名;
  • 删除:
    • drop table 表名;
    • drop table if exists 表名 ;

DML(Data Manipulation Language)(数据操纵语言)

  • 添加数据
    insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);

除了数字类型，其他类型需要使用引号(单双都可以)引起来

  • 删除数据
    delete from 表名 [where 条件]
  • 修改数据
    update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];

DQL(Data Query Language)(数据查询语言)

  • 基础查询顺序
    select
    字段列表
    from
    表名列表
    where
    条件列表
    group by
    分组字段
    having
    分组之后的条件
    order by
    排序
    limit
    分页限定

  • 查询函数

    • 基础运算符

    < 、> 、<= 、>= 、= 、<>、 BETWEEN...AND、 IN、 LIKE、 IS NULL、 and 或 &&、or 或 ||、not 或 !

    • 复杂运算符
    符号 含义
    distinct 去掉重复数据
    concat('a', 'bbb') 字符串连接,和null进行连接，会导致连接后的数据成为null
    ifnull(id,'bb') 如果为空,则为bb
    max(列名) 求这一列的最大值
    min(列名) 求这一列的最小值
    avg(列名) 求这一列的平均值
    count(列名) 统计这一列有多少条记录
    sum(列名) 对这一列求总和

多表查询

  • 笛卡尔积现象
    image
  • 内连接
    用左边表的记录去匹配右边表的记录，如果符合条件的则显示。如：从表.外键=主表.主键。
    • 隐式内连接
      SELECT 字段名 FROM 左表, 右表 WHERE 条件

    看不到 JOIN 关键字，条件使用 WHERE 指定

    • 显式内连接
      SELECT 字段名 FROM 左表 [INNER] JOIN 右表 ON 条件
      使用 INNER JOIN ... ON 语句, 可以省略 INNER
  • 左外连接
    SELECT 字段名 FROM 左表 LEFT [OUTER] JOIN 右表 ON 条件
    使用 LEFT OUTER JOIN ... ON，OUTER 可以省略
    用左边表的记录去匹配右边表的记录，如果符合条件的则显示；否则，显示 NULL

可以理解为：在内连接的基础上保证左表的数据全部显示

  • 右外连接
    SELECT 字段名 FROM 左表 RIGHT [OUTER ]JOIN 右表 ON 条件
    使用 RIGHT OUTER JOIN ... ON，OUTER 可以省略
    用右边表的记录去匹配左边表的记录，如果符合条件的则显示；否则，显示 NULL

在内连接的基础上保证右表的数据全部显示

  • 子查询
    • 一个查询的结果做为另一个查询的条件
    • 有查询的嵌套，内部的查询称为子查询
    • 子查询要使用括号

MySql数据库约束

作用

对表中的数据进行限制，保证数据的正确性、有效性和完整性。一个表如果添加了约束，不正确的数据将无法插入到表中。约束在创建表的时候添加比较合适。

约束种类

约束名 约束关键字
主键 primary key
唯一 unique
非空 not null
外键 foreign key
默认值约束 Default
自增约束 AUTO_INCREMENT
  • 哪个字段应该作为表的主键
    通常不用业务字段作为主键，单独给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序使用的，不是给最终的客户使用的。所以主键有没有含义没有关系，只要不重复，非空就行。

主键约束相当于 唯一约束 + 非空约束 的组合，主键约束列不允许重复，也不允许出现空值。

  • 唯一约束
    什么是唯一约束： 表中某一列不能出现重复的值
  • 非空约束
    什么是非空约束：某一列不能为 null。
  • 外键约束
    外键约束是用来加强两个表（主表和从表）的一列或多列数据之间的连接的，可以保证一个或两个表之间的参照完整性，外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。

创建外键约束的顺序是先定义主表的主键，然后定义从表的外键。也就是说只有主表的主键才能被从表用来作为外键使用，被约束的从表中的列可以不是主键，主表限制了从表更新和插入的操作。现已不用

  • 默认值约束
    若在表中定义了默认值约束，用户在插入新的数据行时，如果该行没有指定数据，那么系统将默认值赋给该列，如果我们不设置默认值，系统默认为NULL。
  • 自增约束(AUTO_INCREMENT)
    自增约束(AUTO_INCREMENT)可以约束任何一个字段，该字段不一定是PRIMARY KEY字段，也就是说自增的字段并不等于主键字段。
    但是PRIMARY_KEY约束的主键字段，一定是自增字段，即PRIMARY_KEY 要与AUTO_INCREMENT一起作用于同一个字段。

事物

定义

事务执行是一个整体，所有的 SQL 语句都必须执行成功。如果其中有 1 条 SQL 语句出现异常，则所有的SQL 语句都要回滚，整个业务执行失败。

手动提交事物的SQL语句

功能 SQL语句
开启事物 start transaction
提交事务 commit
回滚事务 rollback

手动提交事物使用的过程

1）执行成功的情况： 开启事务-》执行多条 SQL 语句-》成功提交事务；
2）执行失败的情况： 开启事务-》执行多条 SQL 语句-》事务的回滚

事物的步骤
  1. 客户端连接数据库服务器，创建连接时创建此用户临时日志文件
  2. 开启事务以后，所有的操作都会先写入到临时日志文件中
  3. 所有的查询操作从表中查询，但会经过日志文件加工后才返回
  4. 如果事务提交则将日志文件中的数据写到表中，否则清空日志文件。

自动提交事务

MySQL 默认每一条 DML(增删改)语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务，MySQL 默认开始自动提交事务

  • 原理图
    image

事物的隔离级别

  • 事物的四大特性ACID
    image
  • 并发引起的问题
    事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题：
    image
  • MySQL数据库有四种隔离级别
    上面的级别最低，下面的级别最高。“是”表示会出现这种问题，“否”表示不会出现这种问题。
    image

隔离级别越高，性能越差，安全性越高。

posted @ 2020-04-02 17:31  晨度  阅读(160)  评论(0)  编辑  收藏  举报