MySQL基础
1, mysql的安装与卸载
1.1mysql卸载
右键开始菜单，选择命令提示符(管理员)，打开黑框。
-
敲入
net stop mysql，回车。net stop mysql
-
再敲入
mysqld -remove mysql，回车。mysqld -remove mysql
-
最后删除MySQL目录及相关的环境变量。
至此，MySQL卸载完成！
2.1 MySQL安装
CSDN资源里面有mysql和Navicat的安装包
还有配置修改和安装步骤
2, SQL概述
2.1 SQL简介
-
英文：Structured Query Language，简称 SQL
-
结构化查询语言，一门操作关系型数据库的编程语言
-
定义操作所有关系型数据库的统一标准
-
对于同一个需求，每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”
2.2 通用语法
SQL 语句可以单行或多行书写，以分号结尾。
MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
注释
单行注释: -- 注释内容 或 #注释内容(MySQL 特有)
注意：使用-- 添加单行注释时，--后面一定要加空格，而#没有要求。
多行注释: /* 注释 */
2.3SQL分类
-
DDL(Data Definition Language) ： 数据定义语言，用来定义数据库对象：数据库，表，列等
DDL简单理解就是用来操作数据库，表等
-
DML(Data Manipulation Language) 数据操作语言，用来对数据库中表的数据进行增删改
DML简单理解就对表中数据进行增删改
DQL(Data Query Language) 数据查询语言，用来查询数据库中表的记录(数据) -
DQL简单理解就是对数据进行查询操作。
从数据库表中查询到我们想要的数据。
DCL(Data Control Language) 数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户 -
DML简单理解就是对数据库进行权限控制。
比如我让某一个数据库表只能让某一个用户进行操作等。
注意： 以后我们最常操作的是
DML和DQL，因为我们开发中最常操作的就是数据
3, DDL& DML
3.1 DDL:操作数据库
DDL来操作数据库。而操作数据库主要就是对数据库的增删查操作。
- 查询所有的数据库
SHOW DATABASES;
- 创建数据库：
CREATE DATABASE 数据库名称;
- 创建数据库(判断，如果不存在则创建)
CREATE DATABASE IF NOT EXISTS 数据库名称;
- 删除数据库
DROP DATABASE 数据库名称;
- 删除数据库(判断，如果存在则删除)
DROP DATABASE IF EXISTS 数据库名称;
- 使用数据库
USE 数据库名称;
- 查看当前使用的数据库
SELECT DATABASE();
3.2 DDL:操作表
操作表也就是对表进行增（Create）删（Retrieve）改（Update）查（Delete）。
1, 查询表
- 查询当前数据库下所有表名称
SHOW TABLES;
- 查询表结构
DESC 表名称;
2 , 创建表
- 创建表
CREATE TABLE 表名 (
字段名1 数据类型1,
字段名2 数据类型2,
…
字段名n 数据类型n
);
注意：最后一行末尾，不能加逗号
3 , 删除表
- 删除表
DROP TABLE 表名;
- 删除表时判断表是否存在
DROP TABLE IF EXISTS 表名;
4 , 修改表
- 修改表名
ALTER TABLE 表名 RENAME TO 新的表名;
-- 将表名student修改为stu
alter table student rename to stu;
- 添加一列
ALTER TABLE 表名 ADD 列名 数据类型;
-- 给stu表添加一列address，该字段类型是varchar(50)
alter table stu add address varchar(50);
- 修改数据类型
ALTER TABLE 表名 MODIFY 列名 新数据类型;
-- 将stu表中的address字段的类型改为 char(50)
alter table stu modify address char(50);
- 修改列名和数据类型
ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
-- 将stu表中的address字段名改为 addr，类型改为varchar(50)
alter table stu change address addr varchar(50);
- 删除列
ALTER TABLE 表名 DROP 列名;
-- 将stu表中的addr字段 删除
alter table stu drop addr;
3.3 DML
DML主要是对数据进行增（insert）删（delete）改（update）操作。
1, 添加数据
- 给指定列添加数据
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,…)…;
2, 修改数据
修改表数据
UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;
注意：
- 修改语句中如果不加条件，则将所有数据都修改！
- 像上面的语句中的中括号，表示在写sql语句中可以省略这部分
3, 删除数据
删除数据
DELETE FROM 表名 [WHERE 条件] ;
-- 删除张三记录
delete from stu where name = '张三';
-- 删除stu表中所有的数据
delete from stu;
4，DQL
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组后条件
ORDER BY
排序字段
LIMIT
分页限定
4.1 基础查询
- 查询多个字段
SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; -- 查询所有数据
- 去除重复记录
SELECT DISTINCT 字段列表 FROM 表名;
eg: 去除列表address的重复数据
select distinct address from stu;
- 起别名
AS: AS 也可以省略
eg: 查询姓名、数学成绩、英语成绩。并通过as给math和english起别名（as关键字可以省略）
select name,math 数学成绩,english 英文成绩 from stu;
4.2 条件查询
1, 基础查询
SELECT 字段列表 FROM 表名 WHERE 条件列表;
2, 运算符
练习
查询英语成绩为 null的学员信息
null值的比较不能使用 = 或者 != 。需要使用 is 或者 is not
select * from stu where english = null; -- 这个语句是不行的
select * from stu where english is null;
select * from stu where english is not null;
3, 模糊查询练习
模糊查询使用like关键字，可以使用通配符进行占位:
（1）_ : 代表单个任意字符
（2）% : 代表任意个数字符
练习：
查询姓'马'的学员信息
select * from stu where name like '马%';
查询第二个字是'花'的学员信息
select * from stu where name like '_花%';
查询名字中包含 '德' 的学员信息
select * from stu where name like '%德%';
排序查询
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;
-
ASC ： 升序排列 （默认值）
-
DESC ： 降序排列
注意：如果有多个排序条件，当前边的条件值一样时，才会根据第二条件进行排序
练习
查询学生信息，按照年龄升序排列
select * from stu order by age ;
查询学生信息，按照数学成绩降序排列
select * from stu order by math desc ;
查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
select * from stu order by math desc , english asc ;
4, 聚合函数
将一列数据作为一个整体，进行纵向计算
分类：
| 函数名 | 功能 |
|---|---|
| count(列名) | 统计数量（一般选用不为null的列） |
| max(列名) | 最大值 |
| min(列名) | 最小值 |
| sum(列名) | 求和 |
| avg(列名) | 平均值 |
语法：
SELECT 聚合函数名(列名) FROM 表;
注意：null 值不参与所有聚合函数运算
5, 分组查询
语法:
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
练习:
查询男同学和女同学各自的数学平均分
select sex, avg(math) from stu group by sex;
查询男同学和女同学各自的数学平均分，以及各自人数
select sex, avg(math),count(*) from stu group by sex;
查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
select sex, avg(math),count(*) from stu where math > 70 group by sex;
查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的
select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*) > 2;
where 和 having 区别：
- 执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
- 可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。
6, 分页查询
语法:
SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查询条目数;
注意： 上述语句中的起始索引是从0开始
每页显示3条数据，查询第3页数据
select * from stu limit 6 , 3;
5, 约束
5.1 概念
-
约束是作用于表中列上的规则，用于限制加入表的数据
例如：我们可以给id列加约束，让其值不能重复，不能为null值。
-
约束的存在保证了数据库中数据的正确性、有效性和完整性
添加约束可以在添加数据的时候就限制不正确的数据，年龄是3000，数学成绩是-5分这样无效的数据，继而保障数据的完整性。
5,2 分类
-
非空约束： 关键字是 NOT NULL
保证列中所有的数据不能有null值。
例如：id列在添加
马花疼这条数据时就不能添加成功。 -
唯一约束：关键字是 UNIQUE
保证列中所有数据各不相同。
例如：id列中三条数据的值都是1，这样的数据在添加时是绝对不允许的。
-
主键约束： 关键字是 PRIMARY KEY
主键是一行数据的唯一标识，要求非空且唯一。一般我们都会给没张表添加一个主键列用来唯一标识数据。
例如：上图表中id就可以作为主键，来标识每条数据。那么这样就要求数据中id的值不能重复，不能为null值。
-
检查约束： 关键字是 CHECK
保证列中的值满足某一条件。
例如：我们可以给age列添加一个范围，最低年龄可以设置为1，最大年龄就可以设置为300，这样的数据才更合理些。
注意：MySQL不支持检查约束。
这样是不是就没办法保证年龄在指定的范围内了？从数据库层面不能保证，以后可以在java代码中进行限制，一样也可以实现要求。
-
默认约束： 关键字是 DEFAULT
保存数据时，未指定值则采用默认值。
例如：我们在给english列添加该约束，指定默认值是0，这样在添加数据时没有指定具体值时就会采用默认给定的0。
-
外键约束： 关键字是 FOREIGN KEY
外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。
外键约束现在可能还不太好理解，后面我们会重点进行讲解。
5,3 非空约束
-
概念
非空约束用于保证列中所有数据不能有NULL值
-
语法
- 添加约束
-- 创建表时添加非空约束
CREATE TABLE 表名(
列名 数据类型 NOT NULL,
…
);
-- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;
- 删除约束
ALTER TABLE 表名 MODIFY 字段名 数据类型;
5,4 唯一约束
-
概念
唯一约束用于保证列中所有数据各不相同
-
语法
- 添加约束
-- 创建表时添加唯一约束
CREATE TABLE 表名(
列名 数据类型 UNIQUE [AUTO_INCREMENT],
-- AUTO_INCREMENT: 当不指定值时自动增长
…
);
CREATE TABLE 表名(
列名 数据类型,
…
[CONSTRAINT] [约束名称] UNIQUE(列名)
);
-- 建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;
- 删除约束
ALTER TABLE 表名 DROP INDEX 字段名;
5,5 主键约束
-
概念
主键是一行数据的唯一标识，要求非空且唯一
一张表只能有一个主键
-
语法
- 添加约束
-- 创建表时添加主键约束
CREATE TABLE 表名(
列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
…
);
CREATE TABLE 表名(
列名 数据类型,
[CONSTRAINT] [约束名称] PRIMARY KEY(列名)
);
-- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
- 删除约束
ALTER TABLE 表名 DROP PRIMARY KEY;
5,6 默认约束
-
概念
保存数据时，未指定值则采用默认值
-
语法
- 添加约束
-- 创建表时添加默认约束
CREATE TABLE 表名(
列名 数据类型 DEFAULT 默认值,
…
);
-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;
- 删除约束
ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;
5,7 外键约束
外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性。
语法:
- 添加外键约束
-- 创建表时添加外键约束
CREATE TABLE 表名(
列名 数据类型,
…
[CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
);
-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
- 删除外键约束
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
6，数据库设计
6,1 设计简介
-
数据库设计概念
- 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
- 建立数据库中的表结构以及表与表之间的关联关系的过程。
- 有哪些表？表里有哪些字段？表和表之间有什么关系？
-
数据库设计的步骤
- 需求分析（数据是什么? 数据具有哪些属性? 数据与属性的特点是什么）
- 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
- 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
- 维护设计（1.对新的需求进行建表；2.表优化）
-
表关系
-
一对一
- 如：用户 和 用户详情
- 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
-
一对多
-
如：部门 和 员工
-
一个部门对应多个员工，一个员工对应一个部门。
-
-
-
多对多
-
如：商品 和 订单
- 一个商品对应多个订单，一个订单包含多个商品。
-
6,2 表关系(一对多)
-
一对多
- 如：部门 和 员工
- 一个部门对应多个员工，一个员工对应一个部门。
-
实现方式
在多的一方建立外键，指向一的一方的主键
-
案例
我们还是以
员工表和部门表举例:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lXiXbYEQ-1650009736830)(F:\黑马0409\2022web资料\day02-MySQL高级\讲义\md+pdf\assets\image-20210724134145803.png)]
经过分析发现，员工表属于多的一方，而部门表属于一的一方，此时我们会在员工表中添加一列（dep_id），指向于部门表的主键（id）：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FUtWXJWO-1650009736832)(F:\黑马0409\2022web资料\day02-MySQL高级\讲义\md+pdf\assets\image-20210724134318685.png)]
建表语句如下：
-- 删除表
DROP TABLE IF EXISTS tb_emp;
DROP TABLE IF EXISTS tb_dept;
-- 部门表
CREATE TABLE tb_dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
-- 员工表
CREATE TABLE tb_emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int,
-- 添加外键 dep_id,关联 dept 表的id主键
CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)
);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BcCQOABI-1650009736834)(F:\黑马0409\2022web资料\day02-MySQL高级\讲义\md+pdf\assets\image-20210724140456921.png)]
6,3 表关系(多对多)
-
多对多
- 如：商品 和 订单
- 一个商品对应多个订单，一个订单包含多个商品
-
实现方式
建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
-
案例
我们以
订单表和商品表举例：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Jx9l2kQE-1650009736837)(F:\黑马0409\2022web资料\day02-MySQL高级\讲义\md+pdf\assets\image-20210724134735939.png)]
经过分析发现，订单表和商品表都属于多的一方，此时需要创建一个中间表，在中间表中添加订单表的外键和商品表的外键指向两张表的主键：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cEL3oJcX-1650009736839)(F:\黑马0409\2022web资料\day02-MySQL高级\讲义\md+pdf\assets\image-20210724135054834.png)]
建表语句如下：
-- 删除表
DROP TABLE IF EXISTS tb_order_goods;
DROP TABLE IF EXISTS tb_order;
DROP TABLE IF EXISTS tb_goods;
-- 订单表
CREATE TABLE tb_order(
id int primary key auto_increment,
payment double(10,2),
payment_type TINYINT,
status TINYINT
);
-- 商品表
CREATE TABLE tb_goods(
id int primary key auto_increment,
title varchar(100),
price double(10,2)
);
-- 订单商品中间表
CREATE TABLE tb_order_goods(
id int primary key auto_increment,
order_id int,
goods_id int,
count int
);
-- 建完表后，添加外键
alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);
alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);
6,4 表关系(一对一)
-
一对一
- 如：用户 和 用户详情
- 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
-
实现方式
在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)
-
案例
我们以
用户表举例：
而在真正使用过程中发现 id、photo、nickname、age、gender 字段比较常用，此时就可以将这张表查分成两张表。
建表语句如下：
create table tb_user_desc (
id int primary key auto_increment,
city varchar(20),
edu varchar(10),
income int,
status char(2),
des varchar(100)
);
create table tb_user (
id int primary key auto_increment,
photo varchar(100),
nickname varchar(50),
age int,
gender char(1),
desc_id int unique,
-- 添加外键
CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)
);
7, 多表查询
- 连接查询
-
内连接查询 ：相当于查询AB交集数据
-
外连接查询
- 左外连接查询 ：相当于查询A表所有数据和交集部门数据
- 右外连接查询 ： 相当于查询B表所有数据和交集部分数据
-
子查询
7.1 内连接查询
- 语法
-- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;
-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
内连接相当于查询 A B 交集数据
-
案例
- 隐式内连接
SELECT * FROM emp, dept WHERE emp.dep_id = dept.did;* 显式内连接select * from emp inner join dept on emp.dep_id = dept.did; -- 上面语句中的inner可以省略，可以书写为如下语句 select * from emp join dept on emp.dep_id = dept.did;
7,2 外连接查询
- 语法
-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
左外连接：相当于查询A表所有数据和交集部分数据
右外连接：相当于查询B表所有数据和交集部分数据
-
案例
- 查询emp表所有数据和对应的部门信息（左外连接）
select * from emp left join dept on emp.dep_id = dept.did;- 查询dept表所有数据和对应的员工信息（右外连接）
select * from emp right join dept on emp.dep_id = dept.did;要查询出部门表中所有的数据，也可以通过左外连接实现，只需要将两个表的位置进行互换：
7,3 子查询
- 概念
查询中嵌套查询，称嵌套查询为子查询。
什么是查询中嵌套查询呢？我们通过一个例子来看：
需求：查询工资高于猪八戒的员工信息。
来实现这个需求，我们就可以通过二步实现，
第一步：先查询出来 猪八戒的工资
select salary from emp where name = '猪八戒'
第二步：查询工资高于猪八戒的员工信息
select * from emp where salary > 3600;
第二步中的3600可以通过第一步的sql查询出来，所以将3600用第一步的sql语句进行替换
select * from emp where salary > (select salary from emp where name = '猪八戒');
这就是查询语句中嵌套查询语句。
- 子查询根据查询结果不同，作用不同
- 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断
- 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断
- 子查询语句结果是多行多列，子查询语句作为虚拟表
8, 事务
8.1 概述
数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。
事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。
事务是一个不可分割的工作逻辑单元。
这些概念不好理解，接下来举例说明，如下图有一张表
张三和李四账户中各有100块钱，现李四需要转换500块钱给张三，具体的转账操作为
- 第一步：查询李四账户余额
- 第二步：从李四账户金额 -500
- 第三步：给张三账户金额 +500
现在假设在转账过程中第二步完成后出现了异常第三步没有执行，就会造成李四账户金额少了500，而张三金额并没有多500；这样的系统是有问题的。如果解决呢？使用事务可以解决上述问题
从上图可以看到在转账前开启事务，如果出现了异常回滚事务，三步正常执行就提交事务，这样就可以完美解决问题
8,2 语法
-
开启事务
START TRANSACTION; 或者 BEGIN; -
提交事务
commit; -
回滚事务
rollback;
8,3 事务的四大特征
-
原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败
-
一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态
-
隔离性（Isolation） :多个事务之间，操作的可见性
-
持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
说明：
mysql中事务是自动提交的。
也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。
可以通过下面语句查询默认提交方式：
SELECT @@autocommit;查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式
set @@autocommit = 0;
扫一扫
772
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
