# 认识数据库

  • 数据库 (DataBase), 即数据存储的仓库,一个数据库由多个表空间组成
  • 用户可在数据库中对库中的表进行增删改查的操作

# 数据库管理系统(DBMS)

  • 具有管理数据库的一种软件系统,具有存储、截取、安全保障、数据备份等功能

# 数据库分类

# 什么是关系模式

  • 关系的描述称为关系模式(Relation Schema)它可以形式化地表示为:
    • R(U,D,dom,F)
  • 其中 R 为关系名,U 为组成该关系的属性名集合,D 为属性组 U 中属性所来自的域,dom 为属性向域的映象集合,F 为属性间数据的依赖关系集合。
  • 通常简记为:
    • R (U) 或 R (A1,A2,…,An)
    • 其中 R 为关系名,U 为属性名集合,A1,A2,…,An 为各属性名。

# 什么是关系模型

  • 关系实际上就是关系模式在某一时刻的状态或内容。也就是说,关系模式是型,关系是它的值。关系模式是静态的、稳定的,而关系是动态的、随时间不断变化的,因为关系操作在不断地更新着数据库中的数据。但在实际当中,常常把关系模式和关系统称为关系,读者可以从上下文中加以区别。

# 关系型数据库

# 定义

  • 采用了关系模型来组织数据的数据库,其以行和列的形式存储数据,以便于用户理解,关系型数据库这一系列的行和列被称为表,一组表组成了数据库。

  • MySQL
  • Oracle
  • SQL Server
  • SQLite
  • DB2

# 非关系型数据库

# 定义

  • NoSQL,泛指非关系型的数据库。随着互联网 web2.0 网站的兴起,传统的关系数据库在处理 web2.0 网站,特别是超大规模和高并发的 SNS 类型的 web2.0 纯动态网站已经显得力不从心,出现了很多难以克服的问题,而非关系型的数据库则由于其本身的特点得到了非常迅速的发展。NoSQL 数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战,特别是大数据应用难题。

  • Redis
  • MongoDB

# 关于 MySQL

  • 原开发者为瑞典的 MySQL AB 公司,该公司于 2008 年被昇阳微系统(Sun Microsystems)收购。2009 年,甲骨文公司(Oracle)收购昇阳微系统公司,MySQL 成为 Oracle 旗下产品。
  • 开源的数据库系统软件
  • 最好的 RDBMS (Relational DataBase Manager System) 关系型数据库管理系统
  • 体积小,速度快,开发成本低等优点,多用于中小型网站的开发,大型网站用到集群

# MySQL 安装

  • 官网下载压缩包

https://www.mysql.com/cn/downloads/

  • 解压
  • 把解压包放在电脑环境目录下
  • 配置环境变量
  • 新建 MySQL 配置文件 ini
    [mysqld]
    # 目录按个人的来
    basedir=E:\software\mysql-5.02.11\
    datadir=E:\software\mysql-5.02.11\data\
    port=3306
    skip-grant-tables
  • 启动管理员运行,运行 CMD 命令
    • 先把命令路径切换至 mysql 下的 bin 命令然后输入 mysqld -install
    • 再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件
    • 然后再次启动 mysql,用命令 mysql -u root -p 进入 mysql 管理界面
    • 进入界面更改 root 密码
    • update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
    • 然后输入 flush privileges 刷新权限
  • 修改 my.ini 文件删除最后一句 skip-grant-tables
  • 重启 mysql 即可使用
    • net stop mysql
    • net start mysql

# 数据库使用

# 使用命令行连接数据库

mysql -uroot -p123456 -- 连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -- 修改用户密码
flush privileges; -- 刷新权限
show databases; -- 查看所有数据库
mysql> use college -- 切换数据库 + use 关键字 + 数据库名
Database changed
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有表信息
create database stuRepo; -- 创建一个数据库
exit; -- 退出连接

# 操作数据库

  • 创库
    CREATE DATABASE [IF NOT EXIST] stuRepo;
  • 删库
    DROP DATABASE [IF EXIST] stuRepo;
  • 使用数据库
    use 'stuRepo';
  • 查看数据库
    show Databases;

# 数据库的数据类型

# 数值型

类型 适用范围 大小
tinyint 极小的整数 1 字节
smallint 较小的整数 2 字节
mediumint 中等的整数 3 字节
int 标准的整数 4 字节
bigint 较大的整数 8 字节
float 浮点数 4 字节
double 双精度浮点数 8 字节
decimal 字符串形式的浮点数

# 字符串

类型 解释 数值范围 备注
char 字符串固定大小的 0~255
varchar 可变字符串 0~65535 最常用的变量如 String
tinytext 微型文本 2^8-1
text 文本串 2^16-1 存储大文本

# 时间日期型

  • 出自 java.util.Date
类型 格式 解释
date YYYY-MM-DD 日期格式
time HH:mm:ss 时间格式
datatime YYYY-MM-DD HH:mm:ss 最常见的时间格式
timestamp 即时间戳 1970.1.1 至今的毫秒数
year 年份表示

# NULL

  • 无数值
  • 不可使用 NULL 进行运算,结果为 NULL

# 数据库字段属性

  • Unsigned
    • 无符号的整数
    • 声明了该列不可为 负数
  • zerofill
    • 0 填充的
    • 不足的位数,使用 0 来填充
  • 自增 (AUTO_INCREMENT)
    • 通常理解为自增,自动在上一条记录的属性上加 1(默认)
    • 通常用来设计唯一的主键 (如 id、index 等),必须为整数类型
    • 可以自定义设计主键自增的起始值和步长
  • 非空 (NOT_NULL)
    • 若字段设置为非空,则不给该字段赋值,系统报错
    • NULL,不填写默认为空
  • 默认 (DEFAULT)

# 表的操作

# 创表

-- 业务:创建一个 school 数据库
-- 然后创建学生表,使用 SQL 语句创建
-- 关键点:
-- 使用英文的 (), 表名和字段名尽量使用 `` 括起来
--AUTO_INCREMENT 自增
--PRIMARY KEY 主键,一个表只有一个主键
CREATE DATABASE IF NOT EXISTS `school`;
use `school`;
CREATE TABLE IF NOT EXISTS `student` {
  `id` int(6) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(22) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(30) NOT NULL DEFAULT '123' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY(`id`)
}ENGINE=INNOOB DEFAULT CHARSET=UTF8

# 标准格式

CREATE TABLE [IF NOT EXISTS] `表名`{
  `字段名1` 列类型 [属性] [索引] [注释],
  `字段名2` 列类型 [属性] [索引] [注释],
  ...
}[表类型] [字符集设置] [注释]

# 查看库与表的语句

SHOW CREATE DATABASE school -- 查看创建数据库的语句
SHOW CREATE TABLE student -- 查看 student 数据库中表的定义语句
DESC student -- 显示表的结构

# 数据表类型

# 数据库引擎

  • 定义:
    • 数据库引擎是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务,从而满足企业内大多数需要处理大量数据的应用程序的要求。 使用数据库引擎创建用于联机事务处理或联机分析处理数据的关系数据库。
  • 目前常用的(必记的)
    • INNODB 默认使用
    • MYISAM 早期使用的
  • 两者区别
MYISAM INNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大,约为 MYISAM 的两倍
  • 使用特点
    • 对于 MYISAM 而言,可节约空间,高速运行
    • 对于 INNODB 而言,安全性高,可实现多表多用户操作

在物理环境中存在的位置
索引的数据库文件都存储在 data 文件目录下,一个文件夹对应一个数据库
本质上是文件的存储

  • 两个数据库引擎在物理上的区别
    • INNODB 在数据库表中只有一个 *.frm 文件,以及上级目录下的 ibdata1 文件
    • MYISAM 对应文件
      文件后缀 解释
      *.frm 表结构定义文件
      *.MYD 数据文件 data
      *.MYI 索引文件 index

# 编码问题

  • 设置编码 (创表的 SQL 语句中)
    CHARSET=utf8
  • 不设置 utf-8,MYSQL 默认编码为 Latin1,且不支持中文
  • 也可在 my.ini 配置文件中设置默认编码(但不推荐,因为非本机操作数据库会失效)
    character-set-server=utf8

# 增加与删除表字段

# 修改表名称

  • 举例:
    ALTER TABLE person RENAME AS man
  • 标准格式
    ALTER TABLE [表名称]  RENAME AS [新表的名称]

# 增加表字段

  • 举例:
    ALTER TABLE teacher ADD age INT(11)-- 增加 teacher 表的年龄字段
  • 标准格式:
    ALTER TABLE [表名称] ADD [字段名] [字段属性]

# 修改表字段 (修改约束与重命名)

  • 举例:
    ALTER TABLE JOB MODIFY salary int(12) -- 修改约束
  • 标准格式:
    ALTER TABLE [表名称] MODIFY [字段名] [字段属性]
  • 举例:
    ALTER TABLE JOB CHANGE salary salary1 int(12) -- 字段重命名 salary 为原字段名

# 删除表字段

  • 举例:
    ALTER TABLE JOB DROP salary1
  • 标准格式:
    ALTER TABLE [表名称] DROP [字段名]

# 删除表

  • 标准格式:
    DROP TABLE IF EXISTS JOB

# 数据管理

# 外键 (Foreign key)

-- 为学生表的 gradeid 添加年级表的 gradeid 外键
CREATE DATABASE IF NOT EXISTS `school`;
use `school`;
CREATE TABLE IF NOT EXISTS `student` {
  `id` int(6) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(22) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(30) NOT NULL DEFAULT '123' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `gradeid` int(15) NOT NULL COMMENT '年级',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY(`id`)
}ENGINE=INNOOB DEFAULT CHARSET=UTF8
-- 添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
  • 标准格式:
    ALTER TABLE 表名  ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 引用的表名(引用的表的字段);
    • 不建议使用外键约束,而是在应用层进行代码的约束
    • 外键约束对后期程序的开发有很大的影响,对 updatedelete 操作都会有更大的麻烦

# DML 数据管理语言

# 添加 insert

insert into `表名`(`字段名1`,`字段名2`,`字段名3`)values('值1','值2','值3')
  • 左边字段的个数与右边值的个数要相同,并且数据类型要一一对应

# 修改 update

update `表名` set `列名1` = `1`,`列名2` = `2` where 条件
  • 没有 where 子句后的条件,会默认修改所有列
  • value 既可以是一个值,也可以是一个变量

# 删除 delete

delete from 表名 where 条件
--where 子句可写可不写,按照实际要求决定,where 实际上相当于一个删除的约束
-- 不写 where 则会删除整个表的数据

# 清空 truncate

  • 只是清空表里的数据,表的结构和字段不会改变
-- 清空 JOB 表
truncate `JOB`

# delete 与 truncate 的区别

  • 都可删除数据,但都不影响表结构
  • 不同点:
    • truncate 重新设置自增列,计时器会归零 不会影响事务

# DQL 数据查询语言

  • 数据查询语言 DQL 基本结构是由 SELECT 子句,FROM 子句,WHERE 子句组成的查询块:

    • SELECT <字段名表>

    • FROM <表或视图名>

    • WHERE <查询条件>

# 指定查询字段

-- 查询全部学生
select * from student
-- 查询指定字段
select `id`,`name` from student
-- 别名,为某个表或字段起一个别名
select `employeeName` AS 员工姓名 from employee AS e
-- 函数 Concat(a,b)
select CONCAT('员工姓名:',employeeName) as 新列名 from employee
-- 输出的格式会为 员工姓名:张三

# 去重 distinct

  • 筛选出表中重复的数据,只显示一个
select distinct `employeeNo` from employee

# Where 条件子句

  • 作用:为了筛选出需要的数据,即添加条件
  • 通过逻辑运算符配合使用!!!
    运算符 语法 描述
    and   && and  b  a && b 逻辑与,两个都为真,结果为真
    or   || or  b  a  ||  b 逻辑或,其中一个为真,则结果为真
    not   ! not  a   !  a 逻辑非,真为假,假为真

# 模糊查询

  • 在 MySQL 中,LIKE 关键字主要用于搜索匹配字段中的指定内容。其语法格式如下:

    • [NOT] LIKE '字符串'
  • 其中:

    • NOT :可选参数,字段中的内容与指定的字符串不匹配时满足条件。
      字符串:指定用来匹配的字符串。“字符串” 可以是一个很完整的字符串,也可以包含通配符。

    • LIKE   关键字支持百分号 “%” 和下划线 “_” 通配符。

  • 通配符是一种特殊语句,主要用来模糊查询。当不知道真正字符或者懒得输入完整名称时,可以使用通配符来代替一个或多个真正的字符。

  • 模糊查询通常会使用到比较运算符

    运算符 语法 描述
    IS NULL a is null 如果操作符为 NULL , 则结果为真
    IS NOT NULL a is not null 如果操作符不为 NULL , 则结果为真
    BETWEEN a between b and c 若 a 在 b 与 c 之间,则结果为真
    LIKE a like b 如果 a 匹配 b,则结果为真
    IN a in (a1,a2,a3…) 假设 a 在 a1,或 a2… 其中的某一个值中,则结果为真
  • 模糊查询的 SQL 语句实例

    -- 查询姓刘的同学
    -- like 结婚 %(岱庙 0 到任意个字符) _(代表一个字符)
    SELECT `studentId`,`name` FROM `student`
    WHERE `name` LIKE '刘%'
    -- 查询姓刘的同学,姓后面为一个字的
    SELECT `studentId`,`name` FROM `student`
    WHERE `name` LIKE '刘_'
    -- 查询姓刘的同学,姓后面为两个字的
    SELECT `studentId`,`name` FROM `student`
    WHERE `name` LIKE '刘__'
    -- 查询名字中间为欣字的同学 % 欣 %
    SELECT `studentId`,`name` FROM `student`
    WHERE `name` LIKE '%欣%'
    -- 查询 1001、1002、1003 号学员
    SELECT `studentId`,`name` FROM `student`
    WHERE `studentId` IN (1001,1002,1003)
    -- 查询地址为空的学生 null
    SELECT `studentId`,`name` FROM `student`
    WHERE `address`='' OR `address` IS NULL
    -- 查询有出生日期的同学 not null
    SELECT `studentId`,`name` FROM `student`
    WHERE `birthday` IS NOT NULL

# 联表查询(重要!)

  • 联表查询的种类

joinlist

  • inner join 内连接 ,两个表中都匹配时返回行

  • left join 左连接 ,即使左侧表中没有匹配项,也返回左侧表中的所有行。

  • RIGHT JOIN 右连接 , 即使左侧表中没有匹配项,也返回右侧表中的所有行。

  • FULL JOIN 全连接, 它合并了左右外部联接的结果。

  • 联接的表将包含两个表中的所有记录,并为任一侧缺少的匹配项填充 NULL。

  • SELF JOIN 自连接, 用于将一个表连接到自身,就好像该表是两个表一样,在 SQL 语句中临时重命名至少一个表。

  • CARTESIAN JOIN 笛卡尔连接 从两个或多个联接表中返回记录集的笛卡尔积。

  • 联表图解

joindetails

  • 代码示例:

    -- inner join
    SELECT s.studentId ,studentName,subNo,stuRes FROM student AS s 
    INNER JOIN result AS r 
    ON s.studentId = r.studentId
    --right join
    SELECT s.studentId ,studentName,subNo,stuRes FROM student AS s 
    RIGHT JOIN result AS r 
    ON s.studentId = r.studentId  
    --left join
    SELECT s.studentId ,studentName,subNo,stuRes FROM student AS s 
    LEFT JOIN result AS r 
    ON s.studentId = r.studentId

# 分页和排序

  • 排序:
    -- 排序: 升序 ASC,降序 DESC
    -- ORDER BY 通过字段排序
    -- 查询的结构根据成绩降序
    SELECT s.`studentId`,`name`,`subName`,`subRes` FROM 
    student s
    INNER JOIN `result` r
    ON s.`studentId` = r.`studentId`
    INNER JOIN `subject` sub
    ON r.`subId` = sub.`subId`
    WHERE `subName` = `数据库`
    ORDER BY subRes ASC
  • 分页:
    -- 分页的目的:让用户观看更加舒服,也减轻数据库的压力,无需一次性输出所有数据
    -- 分页的效果:在一个网页中显示部分数据,给数据分页显示
    -- 语法:limit 起始值,页面大小
    -- 例如:limit 0,3 即从第一个数据到第四个数据
    SELECT s.`studentId`,`name`,`subName`,`subRes` FROM 
    student s
    INNER JOIN `result` r
    ON s.`studentId` = r.`studentId`
    INNER JOIN `subject` sub
    ON r.`subId` = sub.`subId`
    WHERE `subName` = `数据库`
    ORDER BY subRes ASC
    LIMIT 0,8
    -- 第一页 limit 0,5 (1-1)*5
    -- 第二页 limit 5,5 (2-1)*5
    -- 第 n 页 limit     (n-1)*pagesize,pagesize
    -- pageSize: 页面大小
    -- (n-1)*pageSize: 起始值
    -- n:当前值
    -- 数据总数 / 页面大小 = 总页数

# 分组和过滤

ORDER BY()
HAVING()

# 子查询

  • 子查询是将一个查询语句嵌套在另一个查询语句中;
  • 在特定情况下,一个查询语句的条件需要另一个查询语句来获取,内层查询(inner query)语句的查询结果,可以为外层查询(outer query)语句提供查询条件。
  • 代码示例
    SELECT DISTINCT s.`studentId`,`name`
    FROM student s
    INNER JOIN result r
    ON r.studentId = s.studentId
    WHERE `studentRes`>=80 AND `subId` =(
      SELECT subId FROM `subject`
      WHERE `subName` ='高数'
    )

# MySQL 常用函数

# 数学运算

SELECT ABS(-10) -- 绝对值
SELECT CEILING(8.4) -- 向上取整
SELECT FLOOR(8.4) -- 向下取整
SELECT RAND() -- 返回一个 0~1 内的随机数
SELECT SIGN(10) -- 判断一个数的符号  负数返回 - 1 正数返回 1

# 字符串函数

SELECT CHAR_LENGTH('adhsakdajsx') -- 求字符串长度
SELECT CONCAT('We','are','family') -- 拼接字符串 Wearefamily
SELECT INSERT('这是一个美好的故事',5,6,7,'动人的') -- 查询,从某个位置开始替换长度 这是一个动人的故事
SELECT LOWER('PETERPAN') -- 小写字母
SELECT UPPER('peterpan') -- 大写字母
SELECT INSTR('peterpan','e') -- 返回第一个出现的子串的索引
SELECT REPLACE('未上岸','未','已') -- 替换出现指定的字符串 已上岸
SELECT SUBSTR('只要努力就能成功',3,8) -- 返回指定的子字符串 努力就能成功
SELECT REVERSE('大家早上好') -- 反转 好上早家大

# 时间与日期函数

SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW()) -- 当前年份
SELECT MONTH(NOW()) -- 当前月份

# 系统

SELECT SYSTEM_USER() -- 获取系统管理者
SELECT USER()  -- 获取用户
SELECT VERSION()  -- 获取版本

# 常用的聚合函数

函数 解释
count() 计数
sum() 求和
avg() 求平均值
max() 求最大值
min() 求最小值

具体参考 MYSQL 官方文档:https://dev.mysql.com/doc/refman/8.0/en/function-resolution.html