一、Mysql
1、数据库介绍
数据库概念
数据库分类
关系型数据库
-
介绍：
-
采用关系模型来组织数据的存储，以行和列的形式存储数据并记录数据与数据之间的关系，一一将数据存储在表格中。可以通过建立表格与表格之间的关联来维护数据与数据之间的关系
-
- 产品：
- MySQL(最常用 免费)
- MariaDB
- Percona Server
- PostgreSQL
- Oracle （收费 常用）
- SQL Server
- Access
- Sybase
- 达梦数据库
- MySQL(最常用 免费)
非关系型数据库
- 介绍：
- 采用键值对的模型来存储数据，只完成数据的记录，不会存储数据与数据之间的关系。
- 在非关系型数据库中基于其特定的存储结构来解决一些大数据应用的难题
- NoSQL(Not only SQL)数据库来指代非关系型数据库
- 产品：
- 面向检索的列式存储 Column-Oriented
- HaBase (Hadoop子系统）
- BigTable （Google）
- 面向高并发的缓存存储Key-Value
- Reids
- MemcacheDB
- 面向海量数据访问的文档存储 Document-Oriented
- MongoDB
- CouchDB
- 面向检索的列式存储 Column-Oriented
数据库术语
- 数据库（database）：存储的数据的集合，提供数据存储的服务
- 数据（data）：实际上指的是描述事物的符号记录
- 数据库管理系统（database management system 简称DBMS）：是位于用户和操作系统之间的一层数据管理软件
- 数据库管理员(database anministrator 简称DBA)：负责数据库创建、使用和维护的专门人员
- 数据库系统（database system 简称DBS）：数据库系统管理员、 数据库管理系统、数据库组成的整个单元
2、下载安装MySQL
官网地址：Mysql官网
企业常用5.6或者5.7或者8.0.x
- 8.x新特性
- 性能：比5.7速度快2倍
- 支持NoSQL（非关系型）存储，5.7开始提供对NoSQL的支持，8.0.x做了更进一步的改进
- 窗口函数：一种新的查询方式
- 索引：隐藏索引、降序索引
- 可用性、可靠型
（1）下载
下载地址：官网下载
镜像下载地址：镜像下载
（2）安装
按步骤安装
默认选择了Developer Default 模式安装，我们选择自定义custom，接着下一步
如果想更改MySQL的安装位置，可以在选择具体安装内容后，选择以下选项更改安装位置
这里点OK会弹出提示，直接选择OK即可。再往下选择Next时也会弹出提示，选YES即可
这里是确认安装地址，直接Next,也会弹出提示，选YES即可
显示即将安装的内容，点击 Execute ，等待运行完成，然后连续点击下一步
端口号默认为3306，如图为3306端口被占用，可进行修改，然后下一步
继续下一步
这一步设置管理员账户密码，如需新增其他用户，可以点击新增用户，然后下一步
之后的操作就是一直下一步，不需要其他修改
（3）校验
1.win + R 输入 services.msc 在服务中可以看到mysql的进程
2.win + R 输入cmd 在命令行中输入 mysql -u root -p ,回车，当提示输入密码则表示安装成功，如果提示不存在mysql命令，可查看系统环境变量，是否配置，若未配置，添加配置即可
（4）启动与暂停
mysql默认开机自启，可以通过命令行进行操作
启动：net start mysql服务名称（安装时的命名）
停止：net stop mysql服务名称（安装时的命名）
重启：systemctl restart mysql服务名称（安装时的命名）
（5）客户端连接
1.Mysql提供的客户端命令行工具 《MySQL 5.7 Command Line Client》
2.系统自带命令行工具
-
命令：mysql -h 127.0.0.1 -u root -p 缩写 mysql -u root -p
-
配置环境变量
（6）mysql图形化界面
sqlyog
navicat
dataGrip
（7) 卸载
- 以管理员权限打开命令窗口
- net stop mysql服务名称（安装时的命名）停止服务
- 控制面板卸载程序
- 删除目录
- 自己的安装目录进行删除
- 若未更改，默认安装目录为 c:\Program Files (x86)\MySQL
- 数据文件默认目录：c:\ProgramData \MySQL
- 删除注册表
- 打开注册表： win+r 输入 regedit 回车
- 删除 HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\service\MySQL(新版默认自动删除)
- 删除搜索mysql的相关项
二、操作mysql
（1）原理
（2）SQL语法
-
以分号结尾
-
使用空格/缩进增强可读性
-
sql语句不区分大小写，关键字建议用大写
-
注释
-
--注释内容 或者 #注释内容（单行注释）
-
/*内容*/ (多行注释)
-
（3）SQL分类
1.数据定义语言 DDL
DDL ：用来定义数据库对象（数据库，表，字段）
数据库操作
## 查询
## 显示所有数据库：
SHOW DATABASES
## 显示指定数据库创建的SQL指令
SHOW create database 数据库名
## 查询当前数据库：
SELECT DATABASE()
## 创建 IF NOT EXISTS 不存在创建
CREATE DATABASE IF NOT EXISTS 数据库名 DEFAULT CHARSET 字符集 collate 排序；
CREATE DATABASE 数据库名 ；
--修改
alter database 表名 character set utf8; --把字符集修改为utf8
## 删除 IF EXISTS 存在删除
DROP DATABASE [IF EXISTS] 数据库名
## 使用
USE 数据库名表操作
--查询当前数据库所有表
SHOW TABLES
--查询表结构
DESC 表名
--查询指定表的建表语句
SHOW CREATE TABLE 表名
--创建表
CREATE TABLE 表名(
字段1 字段1类型 COMMENT '字段注释',
字段2 字段2类型 COMMENT '字段注释',
...，
字段n 字段n类型 COMMENT '字段注释',
) COMMENT '表注释'
--修改表名
ALTER TABLE 表名 RENAME TO 新表名
--删除表
DROP TABLE IF EXISTS 表名
--删除指定表并重新创建该表
TRUNCATE TABLE 表名--表结构修改
--添加字段
ALTER TABLE 表名 ADD 字段名 类型 COMMENT 注释
--修改数据类型
ALTER TABLE 表名 MODIFY 字段名 新数据类型
--修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 COMMENT 注释
--删除字段
ALTER TABLE 表名 DROP 字段名
2. 数据操作语言 DML
DML ：用来对数据库表中的数据进行增删改
#增加 INSERT
#指定字段添加数据
INSERT INTO 表名 (字段1，字段2，...) VALUES (值1，值2...);
#给全部字段添加数据
INSERT INTO 表名 VALUES (值1，值2...);
#批量添加数据
INSERT INTO 表名 (字段1，字段2，...) VALUES (值1，值2...),(值1，值2...),(值1，值2...);
INSERT INTO 表名 VALUES (值1，值2...),(值1，值2...),(值1，值2...);
#修改 UPDATE
UPDATA 表名 SET 字段1=值1，字段2=值2，... WHERE 条件
#删除 DELETE
DELETE FROM 表名 WHERE 条件3.数据查询语言 DQL
DQL：用来查询数据库中表的记录
# 编写顺序
SELECT 字段列表 FROM 表名 WHERE 条件 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页查询
#执行顺序
FROM 表名 -> WHERE 条件 -> GROUP BY 分组字段列表 HAVING 分组后条件列表 -> SELECT 字段列表 -> ORDER BY 排序字段列表 -> LIMIT 分页查询#基本查询
SELECT 字段1，字段2... FROM 表名；
SELECT * FROM 表名
#设置别名
SELECT 字段1 AS 别名1,字段2 AS 别名2... FROM 表名；
#去除重复
SELECT DISTINCT 字段列表 FROM 表名条件查询
| 比较运算符 | 功能 | 逻辑运算符 | 功能 |
|---|---|---|---|
| > | 大于 | AND / && | 并且 |
| >= | 大于等于 | OR / || | 或者 |
| < | 小于 | NOT / ! | 非，不是 |
| <= | 小于等于 | ||
| = | 等于 | ||
| <> / != | 不等于 | ||
| BETWEEN...AND... | 在某个范围内，含最大最小值 | ||
| IN(...) | 在in之后的列表中的值，多选一 | ||
| LIKE 占位符 | 模糊匹配(_匹配单个字符，%匹配任意个字符) | ||
| IS NULL | 是NULL |
SELECT 字段列表 FROM 表名 WHERE 条件列表聚合函数
| 函数 | 功能 |
|---|---|
| count | 统计总数 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
SELECT 聚合函数(字段列表) FROM 表名
#所有NULL不参与聚合函数计算分组查询
SELECT 字段列表 FROM 表名 WHERE 条件 GROUP BY 分组字段名 HAVING 分组后过滤条件
注意
执行顺序：WHERE > 聚合函数 > HAVING分组后，查询字段一般为聚合函数和分组字段，查询其他字段无任何意义
排序查询
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式，字段2 排序方式，...
# 排序方式 ASC:升序 DESC:降序 默认升序分页查询
SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数4.数据控制语言 DCL
DCL ：用来创建数据库用户、控制数据库的访问权限
#查询用户
USE mysql
SELECT * FROM user
#创建用户 %代表任意主机
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'
#修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码'
#删除用户
DROP USER '用户名'@'主机名'权限控制
| 权限 | 说明 |
|---|---|
| ALL,ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库、表、视图 |
| CREATE | 创建数据库、表 |
#查询权限
SHOW GRANTS FOR '用户名'@'主机名'
#授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名'
#撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名'(4)数据库表字段类型(常用）
1.数值类型
| 类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
| SMALLINT | 2 bytes | (-32768,32768) | (0,65535) | 大整数值 |
| MEDIUMINT | 3 bytes | (-8388608,8388607) | (0,167772151) | 大整数值 |
| INT/INTEGER | 4 bytes | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
| BIGINT | 8 bytes | (-2^63,2^63 -1) | (0,2^64 ) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402823466 E+38,3.402823466351 E+38) | 0和(1.175494351 E-38, 3.402823466 E+38) | 单精度浮点数值 |
| DOUBLE | 8 bytes | (-1.7976931348623157 E+308, 1.7976931348623157 E+308) | 0和(2.225073858072014 E-308, 1.7976931348623157 E+308 ) | 双精度浮点数值 |
| DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值 |
2.字符串类型
| 类型 | 大小 | 描述 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOS | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-255 535 bytes | 长文本数据 |
| MEDIUMBLOS | 0-16 777 215 bytes | 二进制形式的中等长度数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOS | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
3.日期时间类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01 ~ 999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | -838:59:59 ~ 838:59:59 | HH:MM:SS | 时间值/持续时间 |
| YEAR | 1 | 1901 ~ 2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00 ~ 2036-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值、时间戳 |
三、函数(常用）
(1)字符串函数
| 函数 | 功能 |
|---|---|
| concat(s1,s2...) | 字符串拼接 |
| lower(str) | 将字符串全转为小写 |
| upper(str) | 全转为大写 |
| lpad(str,n,pad) | 左填充，用pad对str的左边进行填充，达到n个字符长度 |
| rpad(str,n,pad) | 右填充，用pad对str的右边进行填充，达到n个字符长度 |
| trim(str) | 去掉字符串头尾的空格 |
| substring(str,start,len) | 返回字符串str从start位置开始的len个长度字符串 |
| REPLACE(字段名, 现在的, 替换成) | 把表中对应字段的字符串进行替换 |
(2)数值函数
| 函数 | 功能 |
|---|---|
| CEIL(X) | 向上取整 |
| FLOOR(X) | 向下取整 |
| MOD(x,y) | 返回x/y的摸(取余) |
| RAND() | 返回()~1以内的随机数 |
| ROUND(x,y) | 求参数x的四舍五入的值，保留y位小数 |
(3)日期函数
| 函数 | 功能 |
|---|---|
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date,INTERVAL expr type) | 返回一个日期/时间值加上一个时间隔expr后的时间值 |
| DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
SELECT DATE_ADD(now(),INTERVAL 70 YEAR)
SELECT DATEDIFF('2023-01-01','2022-12-01') (4)流程函数
| 函数 | 功能 |
|---|---|
| IF(value,t,f) | 如果value为true,则返回t,否则返回f |
| IFNULL(value1,value2) | 如果value不为空，返回value1，否则返回value2 |
| CASE WHEN val1 THEN res1 ... ELSE default END | 如果val为true，返回res1,...否则返回default默认值 |
| CASE expr WHEN val1 THEN res ... ELSE default END | 如果expr的值等于val1，返回res1,...否则返回default默认值 |
四、约束(常用）
作用于表中字段上的规则，用于限制存储在表中的数据
保证数据库中数据的正确性、有效性、完整性
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制字段的数据不能为null | NOT NULL |
| 唯一约束 | 帮正该字段的数据唯一，不重复 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRIMARY KEY |
| 默认约束 | 保存数据时，如未指定该字段的值，即采用默认约束 | DEFAULT |
| 检查约束（8.0.16） | 保证字段值满足一定条件 | CHECK |
| 外键约束 | 让两张表的数据建立连接，保证数据的一致性和完整性 | FOREIGN KEY |
create table users (
id int primary key auto_increment comment 'ID', --唯一标识，主键，自动递增
name varchar(10) not null unique comment '姓名', --不为null,唯一
age int check(age > 0 && age <= 120) comment '年龄', --大于0，小于等于120
status char(1) default '1' comment '状态', --默认为1
gender char(1) comment '性别'
) comment '用户表'
#添加外键 add constraint
alter table 主表名 add constraint 外键名称 foreign key (主表外键字段名) references 副表(副表列名)
alter table role add constraint fk_role_user_id foreign key (user_id) references user(id)
#删除外键
alter table 表名 drop foreign key 外键名；
alter table role drop foreign key fk_role_user_id外键约束
| 行为 | 说明 |
|---|---|
| NO ACTION | 当父表进行删除更新时，检查如果该记录有外键，则不允许删除更新 |
| RESTRICT | 当父表进行删除更新时，检查如果该记录有外键，则不允许删除更新 |
| CASCADE | 当父表进行删除更新时，检查如果该记录有外键，则同时删除更新子表中的外键记录 |
| SET NULL | 当父表进行删除时，检查如果该记录有外键，则设置子表中的外键为null(要求外键允许取null) |
| SET DEFAULT | 当父表有变更时，检查如果该记录有外键，子表中的外键设置为默认值 |
五、多表查询(常用）
一对多、多对多、一对一
连接查询
内连接
#隐式内连接
SELECT 字段列表 FROM 表1,表2 WHERE 条件...
SELECT e.name,d,name FROM emp e,dept d WHERE e.dept_id = d.id;
#显示内连接
SELECT 字段列表 FROM 表1 INNER JOIN 表2 ON 连接条件
SELECT e.name,d,name FROM emp e inner join dept d on e.dept_id = d.id;
SELECT e.name,d,name FROM emp e join dept d on e.dept_id = d.id;外连接
#左外连接
#相当于查询表1(左表)所有数据 包含表1和表2交际部分的数据
SELECT 字段列表 FROM 表1 LEFT OUTER JOIN 表2 ON 连接条件
select e.*, d.name from emp e left outer join dept d on e.dept_id = d.id
select e.*, d.name from emp e left join dept d on e.dept_id = d.id
#右外连接
#相当于查询表2(右表)所有数据 包含表1和表2交际部分的数据
SELECT 字段列表 FROM 表1 RIGHT OUTER JOIN 表2 ON 连接条件
select e.*, d.* from emp e right outer join dept d on e.dept_id = d.id
select e.*, d.* from emp e right join dept d on e.dept_id = d.id自连接
SELECT 字段列表 FROM 表1 别名1 JOIN 表1 别名2 ON 连接条件
#自连接查询，可以是内连接，也可以是外连接
#查询员工及其领导姓名
select e.name,d.name from emp e ,emp d where e.managerid = d.id;
select e.*, d.name from emp e left join emp d on e.managerid = d.id联合查询
把多次查询结果合并起来，形成一个新的查询结果集
使用联合查询多张表列数必须保持一致，字段类型也需要保持一致
union all 会把全部数据直接合并，union 会对合并结果进行去重
SELECT 字段列表 FROM 表1 WHERE 条件...
UNION ALL
SELECT 字段列表 FROM 表2 WHERE 条件...子查询
select * from 表1 where column1=(select column1 from 表2)
#子查询外部的语句可以是 INSERT/UPDATE/DELECT/SELECT 任意一个根据子查询结果分为
-
标量子查询（子查询结果为一个单值）
-
列子查询（子查询结果为一列)
-
行子查询（子查询结果为一行）
-
表子查询（子查询结果为多行多列）
根据子查询位置分为
-
WHERE之后
-
SELECT之后
-
FROM之后
标量子查询
常用操作符：=、 <>、 >、 >=、 < 、<=
#查询销售部门所有员工
select * from emp where dept_id=(select id from dept where name = '销售部');列子查询
常用操作符：IN、NOT IN、ANY、SOME、ALL
| 操作符 | 描述 |
| IN | 在指定集合范围多选一 |
| NOT IN | 不在指定集合范围之内 |
| ANY | 子查询返回列表中，有任意一个满足即可 |
| SOME | 与ANY等同， |
| ALL | 子查询返回列表的所有值必须满足 |
#查询销售部，市场部所有员工
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
#查询比财务部所有人工资都高的员工
select * from emp where salary > all (select salary from emp where dept_id=(select id from edpt where name = '财务部') );
#查询比研发部任意一人工资都高的员工
select * from emp where salary > any (select salary from emp where dept_id=(select id from edpt where name = '研发部') );
行子查询
常用操作符：IN、NOT IN、= 、<>
#查询与张三工资及领导相同的员工
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = '张三')
表子查询
常用操作符：IN
#查询与张三,李四 工资及职位相同的员工
select * from emp where (job,salary) in (select job,salary from emp where name = '张三' or name='李四')
#查询2022-01-01之后入职员工及部门的信息
select e.*,d.* from (select * from emp where entrydate > '2022-01-01') e left join dept d on e.dept_id = d.id;
六、事务
一组操作的集合，是一个不可分割的工作单位，事务会把所有操作作为一个整体一起向系统提交或者撤销操作请求，即这些操作要么同时成功，要么同时失败
事务操作
方法一 关闭事务的自动提交
-- 查看/设置事务提交方式
SELECT @@AUTOCOMMIT;
SET @@AUTOCOMMIT = 0;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;方法二 指令操作
-- 开启事务
START TRANSACTION;
START BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
事务的特性
- 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败
- 一致性：事务完成时，必须使数据都保持一致
- 隔离性：数据库系统提交的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
- 持久性：事务一但提交或者回滚，他对数据库中数据的改变就是永久的
问题
并发事务问题
| 问题 | 描述 |
| 脏读 | 一个事务读到另一个事务还没有提交的数据 |
| 不可重复读 | 一个事务先后读取同一条记录，但两次数据不同，称为不可重复读 |
| 幻读 | 一个事务按照条件查询数据时，并没有对应的数据行，但在插入数据时，又发现这行数据已经存在，好像出现幻影 |
解决问题
事务的隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|---|
| Read uncommitted | 读未提交 | √ | √ | √ |
| Read committed | 读已提交 | × | √ | √ |
| Repeatable Read(默认） | 可重复读 | × | × | √ |
| Serializable | 串行化，序列化 | × | × | × |
-- 查看事务的隔离级别
-- MySQL在5.7.20版本之前是使用tx_isolation
SELECT transaction_isolation;
-- 设置事务的隔离级别
set session transaction isolation level 隔离级别
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
