2021 April 06 Java后端开发

JavaEE开发-第二节 MySQL

MySQL简介

  1. 数据库分为：关系型数据库（Relational Database）、非关系型数据库（NoSQL Database)
    1. 关系型数据库： MySQL、ORACLE、SYBASE、IBMDB2、PostgreSQL、SQLServer、SQLite
    2. 非关系型数据库：redis、mongoDB
  2. MySQL是一款开源的关系型数据库，有免费版（社区版）、商业版，2008年被Sun公司收购，Sun公司在2009年被Oracle公司收购
  3. 下载
    1. 首先要下载安装MySQL服务器软件：MySQL Community Server(社区版)
      1. 常用版本：5.7、8.0，当前使用的是5.7.29版本
    2. 下载地址
      1. windows：https://downloads.mysql.com/archives/installer/
      2. Mac：https://downloads.mysql.com/archives/community/
    3. 官方文档
      1. 英文文档
      2. 中文文档
  4. 安装MySQL
    1. 查看git代码库（学习时自己维护的）Doc文件夹下pdf文档
    2. 安装时会默认一个用户名root
    3. 注意： mac系统下也需要设置兼容中文（百度搜索如何兼容中文）
  5. MySQL Server原理
    1. MySQL Server是一个服务器软件，跟Tomcat一样，安装在服务器机器上
    2. 默认占用端口为3306，监听外部的请求
    3. 里面可以存放很多数据库，通常是一个项目一个数据库
  6. MySQL的使用步骤
    1. 登录、连接MySQL服务器（管理员账户名是root)
      1. 方式1命令：mysql -uroot -p密码
      2. 方式2命令：mysql -uroot -p
    2. 退出（mac系统）
      1. 使用quit命令
    3. 使用SQL语句对数据库进行CRUD
      1. Create:创建，增
      2. Read:读取，查
      3. Update:更新，改
      4. Delete: 删除，删
    4. 在window中，可以通过以下命令查看占用3306端口号的进程ID
      1. netstat -aon|findstr 3306
    5. 数据存储，使用步骤
      1. 创建一个数据库
      2. 在数据中添加表

      3. mysql默认情况下创建的有默认数据库

         information_schema
 mysql
 performance_schema
 sys
  7. 数据库的内部存储细节
    1. 一个数据库(Database)中可以存放多张表（Table ）
    2. 每个表(Table)中包含一些列（Column,也叫做字段）
    3. 每个表(Table)中存放的数据,一般称为记录（record）
  8. MySQL Server、数据库、表，之间的关系
    1. MySQL Server就是一个mysql服务器应用，也叫一个实例
    2. 一个实例下可以创建n个数据库，提供给n个项目，通常每个项目对应一个数据库
    3. 每个数据库下可以建n张表，每张表存储数据。

GUI工具

  1. 在实际开发过程中，经常会使用图形化界面工具来管理数据库。常用的有Navicat Premium、SQLyog（均是付费软件）
  2. Navicat Premium
    1. 支持MySQL、MariaDB、MongoDB、SQLServer、Oracle、PostgreSQL、SQLite
    2. 使用：
      1. 点击左上角Connection->选择数据库类型
      2. 设置地址、端口号、MySQL Server密码，随便起一个连接名称，点击连接即可
      3. 查看当前MySQL Server实例中的所有数据库，可以编辑
      4. 点击 新建查询（New Query），选择实例（MySQL Server）、数据库名，可以在里面输入SQL语句进行CURD操作，点击运行
  3. SQLyog：支持MySQL

SQL语句

  1. SQL是Structured Query Language的简称，译为“结构化查询语言“，用于操作关系型数据库
  2. SQL语句主要可以分为4大类
    1. DDL(Data Definition Language)
      1. 数据定义语言，创建(CREATE) 、修改(ALTER)、删除(DROP) 数据库\表，操作的对象是：数据库\表
    2. DML(Data Manipulation Language)
      1. 数据操纵语言，增加 (INSERT)、删除(DELETE) 、修改 (UPDATE) 记录，操作的对象是记录，即表中的每一行数据
    3. DQL (Data Query Language)
      1. 数据查询语言，查询记录 (SELECT)，操作的对象是记录
    4. DCL (Data CControl Language)
      1. 数据控制语言，控制访问权限(GRANT、REVOKE)
  3. 语句特点
    1. 每一条语句是分号（;）结束,语句输入完要加个分号，然后点击回车
    2. 不区分大小写，建议：关键字使用大写，其他使用小写，单词之间用下划线连接，比如my_firstname

    3. 单行注释

       -- 注释内容 (--后要预留至少一个空格)
 #注释内容

    4. 多行注释

       /*注释内容*/
    5. 参考资料官方文档 第13章 SQL语句语法

DDL语句

数据库操作

  1. 创建

     CREATE DATABASE 数据库名 # 创建数据库(使用默认的字符编码)
 CREATE DATABASE 数据库名 CHARACTER SET 字符编码 # 创建数据库(使用指定的字符编码)
 CREATE DATABASE IF NOT EXISTS 数据库名 # 如果这个数据库不存在，才创建它
 CREATE DATABASE IF NOT EXISTS 数据库名 CHARACTER SET 字符编码

  2. 查询

     SHOW DATABASES # 查询所有的数据库
 SHOW CREATE DATABASE 数据库名 # 查询某个数据库的创建语句
 USE 数据库名 # 使用数据库,用于各个数据库之间的切换
 SELECT DATABASE() # 查询正在使用的数据库

  3. 修改

     ALTER DATABASE 数据库名 CHARACTER SET 字符编码 #修改数据库的字符编码

  4. 删除

     DROP DATABASE 数据库名
 DROP DATABASE IF EXISTS数据库名 # 如果这个数据库存在，才删除它

表操作

  1. 创建（基本语法）

     CREATE TABLE 表名(
     列名1 数据类型1，
     列名2 数据类型2，
     列名3 数据类型3，
     ...
     列名n 数据类型n
 )

  2. 查询

     SHOW TABLES # 查询当前数据库的所有表
 DESC 表名 # 查看表结构

  3. 删除

     DROP TABLE 表名
 DROP TABLE IF EXISTS 表名 # 如果这个表存在，才删除它

  4. 修改

     ALTER TABLE 表名 RENAME TO 新表名 # 修改表名
 ALTER TABLE 表名 CHARACTER SET 字符编码 # 修改表格的字符编码
 ALTER TABLE 表名 ADD 列名 数据类型 # 增加新的-列
 ALTER TABLE 表名 MODIFY 列名 新数据类型 #修改 某一列的数据类型
 ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型 # 修改某一列的列名、数据类型
 ALTER TABLE 表名 DROP 列名 # 删除某一列

常用数据类型

数字类型

  1. 整型

     类型                      字节  有符号取值范围             无符号取值范围
 TINYINT\BOOL\BOOLEAN      1     -128~127                0~255
 SMALLINT                  2     -32768~32767            0~65535
 MEDIUMINT                 3     -8388608~8388607        0 ~ 16777215
 INT\INTEGER               4     -2147483648~2147483647  0~4294967295
 BIGINT                    8     -9223372036854775808~   0~18446744073709551615
                                 9223372036854775807
    1. 上述数类型的使用格式为：TYPE[(M)] [UNSIGNED] [ZEROFILL]
      1. UNSIGNED：无符号
      2. ZEROFILL：等价于UNSIGNED ZEROFILL，表示在显示数值时，若数字不足M位， 就在前面用0填充
      3. 中括号代表可有可无，M代表数字

  2. 浮点类型

     类型                          字节              描述
 FLOAT                          4            存储的小数是近似值
 DOUBLE\DOUBLE PRECISION        8            存储的小数是近似值
 DECIMAL\DEC\NUMERIC\FIXED     看情况         存储的小数可以更加精确

  3. 参考资料

    1. 【官方文档】11.1.1数字类型概述
    2. 【官方文档】11.8数据类型存储要求

字符串类型

类型          描述
CHAR        长度可以指定为0~255,查询数据时，会省略后面的空白字符
VARCHAR     长度可以指定为0~65535(较常使用),查询数据时，不会省略后面的空白字符
BLOB        用于存储二进制数据(照片、文件、大文本等)
TEXT        用于存储大文本
  1. 参考资料:【官方文档】11.4.1CHAR和VARCHAR类型

日期和时间类型

类型          字节(MySQL 5.6.4之前) 字节(MySQL 5.6.4开始)   显示格式
YEAR            1                   1                   YYYY
DATE            3                   3                   YYYY-MM-DD
TIME            3                   3+小数秒存储         HH：MM：SS[.fraction]
DATETIME        8                   5+小数秒存储         YYYY-MM-DDHH：MM：SS[.fraction]
TIMESTAMP       4                   4+小数秒存储         YYYY-MM-DDHH：MM：SS[.fraction]
  1. 从MySQL 5.6.4开始， 允许TIME\DATETIME\TIMESTAMP有小数部分，需要0~3字节的存储
  2. DATETIME支持的范围：1000-01-01 00：00：00.000000到9999-12-31 23：59：59.999999
  3. TIMESTAMP支持的范围：1970-01-01 00：00：01.000000到2038-01-19 03：14：07.999999

DATETIME\TIMESTAMP的自动设置（重要！！！）

  1. DEFAULT CURRENT_TIMESTAMP
    1. 默认当前时间戳：当插入记录时，如果没有指定时间值（原来表中有时间值字段），就设置时间为当前的系统时间
  2. ON UPDATE CURRENT_TIMESTAMP
    1. 当修改记录时，如果没有指定时间值（原来表中有时间值字段），就设置时间为当前的系统时间
  3. 注意：上面1，2就是为了修饰DATETIME\TIMESTAMP这2个类型
    1. 比如新建一个表有：姓名、年龄、生日、添加记录时间、修改记录时间等字段，添加记录时间字段类型是DATETIME用DEFAULT CURRENT_TIMESTAMP修饰，修改记录时间段类型是DATETIME用ON UPDATE CURRENT_TIMESTAMP修饰
    2. 添加一条数据没有设置添加时间
    3. 修改一条数据没有设置修改时间

DML语句

  1. 增加

     INSERT INTO 表名(列名1，列名2，··· 列名n) VALUES (值1，值2，值n)
 //非数字类型的值，一般需要用引号括住(单引号或双引号，建议使用单引号)
 INSERT INTO 表名 VALUES (值1，值2，....,值n) # 从左至右按顺序给所有列添加值

  2. 修改

     UPDATE 表名 SET 列名1 = 值1，列名2 = 值2，...，列名n = 值n [WHERE条件]
    1. 如果没有添加条件，将会修改表中的所有记录

  3. 删除

     DELETE FROM 表名 [WHERE 条件]
    1. 如果没有添加条件，将会删除表中的所有记录

TRUNCATE

  1. 如果要删除表中的所有数据（保留表结构），有2种常见做法

     DELETE FROM 表名 #逐行删除每一条记录
 TRUNCATE [TABLE] 表名 #先删除后重新创建表（效率高）
    1. TRUNCATE归类为DDL语句，而不是DML语句
    2. 为了实现高性能，它绕过了删除数据的DML方法。因此，它不能被回滚，不会导致ON DELETE触发器触发，并且不能对InnoDB具有父子外键关系的表执行

  2. 参考资料:【官方文档】13.1.34 TRUNCATE TABLE语法

DQL语句

SELECT语句

  1. SELECT语句格式

     SELECT [DISTINCT] 列名1， 列名2 ...，列名n
 FROM 表名 
 [WHERE ...]
 [GROUP BY ...]
 [HAVING ...]
 [ORDER BY   ...]
 [LIMIT  ...]
    1. 中括号表示可以省略

  2. 查询所有记录

     SELECT * FROM customer # 查询表中的所有记录
 SELECT DISTINCT * FROM customer # 查询表中的所有记录(去除了重复的记录)

  3. 聚合函数（Aggregate Functiong)

     SELECT COUNT(*) FROM customer # 查询表中的记录总数
 SELECT COUNT(phone) FROM customer # 查询表中phone的总数(不包括NULL)
 SELECT COUNT(DISTINCT phone) FROM customer # 查询表中phone的总数(不包括NULL， 去除了重复的记录)
 SELECT SUM(salary) FROM customer # 计算所有salary的总和
 SELECT MIN(age) FROM customer # 查询最小的age
 SELECT MAX(age) FROM customer # 查询最大的age
 SELECT AVG(salary) FROM customer # 计算所有salary的平均值
    1. 参考资料：【官方文档】12.19.1聚合函数描述

常见的 WHERE子句

  1. 比较运算

     WHERE age > 18 #age大于18
 WHERE age <= 30 #age小于等于30
 WHERE age = 20 #age等于20
 WHERE name= '张三'  #age等于张三
 WHERE age != 25 #age不等于25
 WHERE age <> 25 #age不等于25
 //NULL值判断(不能用=、!=、<>)
 WHERE phone IS NULL # phone的值为NULL
 WHERE phone IS NOT # phone的值不为NULL

  2. 逻辑运算

     WHERE age> 18 AND age <= 30 #age大于18并且小于等于30
 WHERE age> 18 && age <= 30  #age大于18并且小于等于30
 WHERE age BETWEEN 20 AND 30 #age大于等于20并且小于等于30
 WHERE age = 18 0R age = 20 OR age = 22 #age等于18或者等于20或者等于22
 WHERE age IN (18，20，22) #age等于18或者等于20或者等于22
 WHERE NOT(age < 18) # age大于等于18
 WHERE ! (age < 18) # age大于等于18

  3. 模糊查询(_代表单个任意字符，%代表任意个任意字符)

     WHERE name LIKE'_码_' # name是3个字符并且中间是'码'字
 WHERE name LIKE '___' #name是3个字符
 HERE name LIKE '李%' #name以'李'字开头
 WHERE name LIKE '_码%' # name的第2个字符是'码'字
 WHERE name LIKE '%码%' # name中包含'码'字

表的复制

  1. 创建一张拥有相同表结构的空表（只复制表结构，不复制记录）
    1. CREATE TABLE new_table LIKE old_table
  2. 创建一张拥有相同表结构、相同记录的表（复制表结构、复制记录）
    1. CREATE TABLE new_table AS (SELECT * FROM old_table)
    2. 可以省略AS

列的属性

列的常用属性

  1. NOT NULL: 不能设置为NULL值
  2. COMMENT: 注释
    1. 列名称 类型 ... COMMENT '注释内容'
  3. DEFAULT: 默认值(BLOB、TEXT、GEOMETRY、JSON类型不能有默认值)
  4. AUTO_INCREMENT: 自动增长
    1. 适用于INT、FLOAT、DOUBLE类型
    2. 在插入记录时，如果不指定此列的值或设置为NULL， 会在此前的基础上自动增长1
    3. 不能有默认值(不能使用DEFAULT)
    4. 在一个表格中，最多只能有一列被设置为AUTO_INCREMENT
    5. 这一列必须被索引(UNIQUE、PRIMARYKEY、FOREIGN KEY等)
      1. 就是用UNIQUE、PRIMARYKEY、FOREIGN KEY等修饰

UNIQUE索引

  1. 一旦某一列被设置了UNIQUE索引
    1. 该列的所有值必须是唯一的
    2. 允许存在多个NULL值

  2. 2种常见写法

     列名 数据类型 UNIQUE[KEY]
 UNIQUE [KEY] (列名)
    
 CREATE TABLE student(
     //方式1：
     id INT UNIQUE,
     name VARCHAR(20),
     //方式2:
     UNIQUE (name)
 );

主键(PRIMARY KEY)

  1. 主键的作用：可以保证在一张表中的每一条记录都是唯一的
    1. 如果将某一列设置为主键，那么这一列相当于加上了NOT NULL UNIQUE
    2. 建议每一张表都有主键
    3. 主键最好跟业务无关，常设置为INT AUTO_INCREMENT
  2. 2种常见写法

    1. 写法

       列名 数据类型 RIMARY KEY
 RIMARY KEY (列名)

    2. 举例

       CREATE TABLE company(
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(20) NOT NULL UNIQUE
 )
 CREATE TABLE company(
     id INT AUTO_INCREMENT,
     name VARCHAR(20) NOT NULL UNIQUE,
     PRIMARY KEY (id)
 )

外键（FOREIGN KEY)

  1. 一般用外键来引用其他表的主键

  2. 常见写法

     FOREIGN KEY (列名) REFERENCE 表名(列名)

  3. 举例

     //公司表
 CREATE TABLE company(
     id INT AUTO_INCREMENT PRIMARYKEY,
     name VARCHAR(20) NOT NULL UNIQUE
 );
 //人员表
 CREATE TABLE customer(
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(20) NOT NULL,
     company_id INT NOT NULL,
     #引用公司表中的主键
     FOREIGN KEY (company_id) REFERENCES company(id)
 );

级联（CASCADE）

  1. 定义外键时，可以设置级联
    1. ON DELETE CASCADE
      1. 当删除被引用的记录时，引用了此记录的其他所有记录都会被自动删除
    2. ON UPDATE CASCADE
      1. 当修改被引用的记录时，引用了此记录的其他所有记录都会被自动更新

多表查询

  1. 内连接
    1. INNER JOIN、CROSS JOIN、JOIN
    2. 在MySQL中， 它们是等价的;但是在标准SQL中,它们并不是等价的
    3. 2个表满足条件的交集
     # a是customer的别名， b是company的别名，a、b这两张表进行内联，company_id、id两个字段相等的所有数据
 SELECT *FROM customer a JOIN company b ON a.company_id = b.id;
  2. 外连接
    1. 左外连接：LEFT [OUTER] JOIN
      1. A跟B表交集 + A表除交集以外的
    2. 右外连接：RIGHT [OUTER] JOIN
      1. A跟B表交集 + B表除交集以外的
       SELECT *FROM customer a LEFT JOIN company b ON a.company_id = b.id;
 SELECT *FROM customer a RIGHT JOIN company b ON a.company_id = b.id;
 SELECT *FROM customer a LEFT JOIN company b ON a.company_id = b.id WHERE b.id IS NULL;
 SELECT *FROM customer a RIGHT JOIN company b ON a.company_id = b.id WHERE a.compayn_id IS NULL;
  3. 并集
    1. UNION
    2. MySQL并不支持标准SQL中的“FULL [OUTER] JOIN”， 但可以用UNION来替代实现
  4. 参考教程： 13.2.9.2 JOIN语法
  5. ON和WHERE后面都可以跟着条件，它们的区别是
    1. ON：配合JOIN语句使用， 用以指定如何连接表的条件
    2. WHERE：限制哪些记录出现在结果集中
  6. INNER JOIN和逗号(,) 在没有连接条件的情况下，语义上是等价的
    1. 都在指定的表之间产生笛卡尔乘积 (Cartesian Product)
    2. 也就是说，第一个表中的每一行都连接到第二个表中的每一行
    3. A表10条，B表10条，查出来则是100条，其中B标有个id值是B表的主键
     SELECT * FROM customer, company 
 #这样查出来的是笛卡尔乘积，表customer的数据条数 * company的数据条数
 SELECT cr.name cr_name, cy.name cy_name FROM customer cr, company cy 
 # 从右往左看：cr cy 分别是customer、company的别名；cy_name，cy_name是cr.name、cy.name的别名
 #查出表customer的compan_id 表company的id的所有数据
 SELECT * FROM customer a, company b WHERE a.company_id = b.id
  7. 表的例子
    1. 表customer，表示客户列表有字段：name、phone、company_id
    2. 表company，表示公司列表有字段：id、name、adress
    3. 注意：其中customer的company_id值对应这company表的主键id值
  8. 图标显示

    1. 图片总结

      图1

    2. 代码举例：

       # l ∪ (l ∩ r)
 SELECT * FROM 
 	customer l
 LEFT JOIN 
 	company r
 ON
 	l.company_id = r.id;
        
        
 # l - (l ∩ r)
 SELECT * FROM 
 	customer l
 LEFT JOIN 
 	company r
 ON
 	l.company_id = r.id
 WHERE
 	r.id IS NULL;
        
        
 # r ∪ (l ∩ r)
 SELECT * FROM 
 	customer l
 RIGHT JOIN 
 	company r
 ON
 	l.company_id = r.id;
        
        
 # r - (l ∩ r)
 SELECT * FROM 
 	customer l
 RIGHT JOIN 
 	company r
 ON
 	l.company_id = r.id
 WHERE
 	l.company_id IS NULL;
        
        
 # l ∪ r
 (
 SELECT * FROM 
 	customer l
 LEFT JOIN 
 	company r
 ON
 	l.company_id = r.id
 )
 UNION
 (
 SELECT * FROM 
 	customer l
 RIGHT JOIN 
 	company r
 ON
 	l.company_id = r.id
 );
        
        
 # (l ∪ r) - (l ∩ r)
 (
 SELECT * FROM 
 	customer l
 LEFT JOIN 
 	company r
 ON
 	l.company_id = r.id
 WHERE
 	r.id IS NULL
 )
 UNION
 (
 SELECT * FROM 
 	customer l
 RIGHT JOIN 
 	company r
 ON
 	l.company_id = r.id
 WHERE
 	l.company_id IS NULL
 );
        
        
 # l ∩ r
 SELECT * FROM 
 	customer l
 INNER JOIN 
 	company r
 ON
 	l.company_id = r.id;

  9. 易混淆区分

     SELECT * FROM customer a LEFT JOIN company b ON a.company_id = b.id WHERE b.NAME LIKE '%移动%';
 # WHERE 在已经查到的数据中，再做一层过滤，ON完成之后，再通过WHERE赛选
    
 SELECT * FROM customer a LEFT JOIN company b ON a.company_id = b.id AND b.NAME LIKE '%移动%';
 # AND表示ON要满足AND连接的2个条件，等于是ON 后面多几个条件

排序、分页

  1. 排序

     ORDER BY 字段 [ASC | DESC]
    
 //ASC升序，DESC降序
 SELECT *FROM student WHERE id > 20 ORDER BY age DESC;

  2. 分页

     LIMIT {[offset,] row_count | row_count OFFSET offset}
    
 即：LIMIT {[offset,] row_count} 或者 LIMIT {row_count OFFSET offset}
 #假设每页15条，pageSize
 #查询第n页（n>1）
 #SELECT # FROM customer LIMIT (n-1) * pageSize, pageSize;
 SELECT *FROM customer LIMIT 0 , 15;
    1. offset是记录的偏移量(最小值是0)，从哪一条记录开始选择
    2. row_count是希望选择的记录数量
    3. 比如
      1. LIMIT 10, 20 或 LIMIT 20 OFFSET 10
      2. 表示从第10条记录开始，选择20条记录

子查询

  1. 当一个查询是另一个查询的条件时，称之为子查询

  2. 举例

     SELECT * FROM customer WHERE company_id = (SELECT id FROM company WHERE name = '腾讯')

All content is licensed under CC BY-NC-SA

Buit with Jekyll and 3-Jekyll theme • Hosted on Github

Table of Contents