MySQL数据库学习笔记——基础篇

一.概述

1.数据库相关概述

1）关系型数据库

概念：建立在关系模型上，由多张相互连接的二维表组成的数据库。

特点：

1.使用表存储数据，格式统一，便于维护

2.使用SQL语句操作，标准统一，使用方便

我正在学习的MySql就是较主流的关系型数据库管理系统，其他比较主流的还有Oracle。

2）非关系型数据库

如果不用表结构存储数据的数据库就是非关系型数据库。

2.启动与停止MySQL服务的方法

方法1 可以在计算机管理-服务-手动开启或关闭MySQL服务。如下图，我已开启开机自启动

方法2 也可以在cmd命令提示符里手动开启或关闭MySQL服务                                                         

启动服务：net start mysql80

停止服务：net stop mysql80

3.MySQL客户端连接的方法

方法1 MySQL提供的客户端命令行工具

然后输入密码（若闪退是因为没有启动MySQL服务）

方法2 系统自带的cmd命令提示符（需提前配置环境变量）

点击命令提示符或使用win+r快捷键直接打开

输入mysql -u root -p(不用加分号），然后输入密码

二.SQL

1.SQL的通用语法和分类

1）SQL通用语法

SQL语句可以单行或多行书写，以分号结尾

SQL语句可以使用空格/缩进来增强语句的可读性

MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

注释：单行注释：--注释内容 或 #注释内容（MySQL特有）

           多行注释：/* 注释内容 */

2）SQL分类

三.DDL

Data Definition Language数据定义语言，用来定义数据库对象（数据库，表，字段）

1.数据库操作

1）查询

1.查询所有数据库

SHOW DATABASES;

2.查询当前数据库

SELECT DATABASE();

2）创建

方括号内为 可选内容，可以写也可以不写。

CREATE DATABASE [ IF NOT EXISTS ] 数据库名称 [ DEFAULT CHARSET 字符集 ] [ COLLATE 排序规则 ];

3）删除

DROP DATABASE [ IF EXISTS ] 数据库名称;

4）使用

USE 数据库名称;

2.表操作1

1）查询

1.查询当前数据库中的所有表

SHOW TABLES;

2.查询表结构

DESC 表名;

3.查询指定表的建表语句     (比DESC显示的表结构信息更加全面)

SHOW CREATE TABLE 表名;

2）创建

CREATE TABLE 表名(

        字段1  字段1类型 [ COMMENT  字段1注释 ],

        字段2  字段2类型 [ COMMENT  字段2注释 ],

        字段3  字段3类型 [ COMMENT  字段3注释 ],

        ...... 

        字段n 字段n类型 [ COMMENT  字段n注释 ]      (注意，最后这一行没有逗号)

)[COMMENT  表注释];

3.数据类型

MySQL中的数据类型主要分为3类：数值类型，字符串类型，日期时间类型

1）数值类型

2）字符串类型

char(3)，varchar(3)分别代表可以存储两个字符的定长字符串和变长字符串

3）日期时间类型

4.表操作2

1）修改

1.往表中添加字段

ALTER TABLE 表名 ADD 字段名 类型(长度) [ COMMENT 注释 ]  [ 约束 ];

例：向emp表中增加一个字段“昵称”为nickname，类型为varchar(20)

2.修改某一字段的类型

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度); 

3.修改某一字段的字段名及其字段类型

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [ COMMENT 注释 ] [ 约束 ];

例：将emp表中的nickname字段修改为username，类型为varchar(30)

4.删除字段

ALTER TABLE 表名 DROP 字段名;

例：将emp表中的字段username删除

5.修改表名

ALTER TABLE 表名 RENAME TO 新表名;

例：将emp表的表名修改为employee

2）删除

1.删除表

DROP TABLE [ IF EXISTS ] 表名;

2.删除指定表，并重新创建该表  （即删除了表中的数据，只留下了表结构）

TRUNCATE TABLE 表名;

四.DML

Data Manipulation Language数据操作语言，用来对数据库中表的数据记录进行增删改操作

1.添加数据（insert into）

1.给指定字段添加数据

INSERT INTO 表名 (字段名1,字段名2,....) VALUES (值1,值2,...);

2.给全部字段添加数据

INSERT INTO 表名 VALUES (值1,值2,...);

3.批量添加数据

INSERT INTO 表名 (字段名1,字段名2,....) VALUES (值1,值2,...), (值1,值2,...), (值1,值2,...);

INSERT INTO 表名 VALUES (值1,值2,...), (值1,值2,...), (值1,值2,...);

注：        插入数据时，指定的字段顺序需要与值的顺序是一一对应的

                   字符串和日期类型数据应该包含在引号内

                   插入的数据大小，应该在字段的规定范围之内

2.修改数据（update）

UPDATE 表名 SET 字段名1=值1,字段名2=值2,.... [ WHERE 条件 ];

注：修改语句的条件可有可无，若没有条件，则会修改整张表的所有数据。 

例1：修改id为1的数据，将name修改为abc

例2：修改id为2的数据，将name修改为kim，gender修改为女

例3：将所有员工的入职时间修改为 2010-1-1

3.删除数据（delete from）

DELETE FROM 表名 [ WHERE 条件 ];

注：DELETE语句的条件可有可无，若没有条件，则会删除整张表的所有数据

       DELETE语句不能删除某一个字段的值（应使用update）

例1：删除所有gender为女的员工

例2：删除所有员工

五.DQL

Data Query Language数据查询语言，用来查询数据库表中的记录

查询关键字：SELECT

下图为DQL的语句编写顺序

1.基本查询

1.查询多个字段

SELECT 字段1,字段2,字段3... FROM 表名;

例1：查询指定字段name，workno，age

2.查询所有字段

SELECT * FROM 表名;

例2：查询所有字段

推荐使用第一种将所有字段列出的方式

3.设置别名（可增强字段的可读性）

SELECT 字段1 [ AS 别名1 ], [ AS 别名2 ] ... FROM 表名;    （AS也可省去）

例3：查询所有员工的工作地址，并起别名

4.去除重复记录

SELECT DISTINCT 字段列表 FROM 表名;

此时重复的记录就不会显示出来

2.条件查询

1.语法

SELECT 字段列表 FROM 表名 WHERE 条件列表;

2.条件

例：

1.查询年龄等于88的员工信息

2.查询年龄小于等于30岁的员工信息

3.查询没有身份证号的员工信息

4.查询有身份证号的员工信息

5.查询年龄不等于88的员工信息

6.查询年龄在20岁到30岁之间的员工信息（包含20和30）

7.查询年龄等于18或20或40的员工信息

、

8.查询姓名为两个字的员工信息

9.查询身份证号最后一位是X的员工信息

3.聚合函数

1.介绍

将一列数据作为一个整体，进行纵向计算

2.常见的聚合函数

3.语法

SELECT 聚合函数 (字段列表) FROM 表名;

注意：null值不参与所有聚合函数运算

例：

1.统计该企业员工数量

2.统计该企业员工的平均年龄

3.统计该企业员工的最大年龄

4.统计西安地区员工的年龄之和

4.分组查询

1.语法 

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];

2.WHERE 与 HAVING 的区别

执行时机不同：WHERE是分组之前进行过滤，不满足WHERE条件，不参与分组；而HAVING是分组之后对分组后的结果进行过滤

判断条件不同：WHERE不能对聚合函数进行判断，而HAVING可以 

注意：

执行顺序：WHERE>聚合函数>HAVING

分组之后，查询的字段一般为聚合函数和分组字段，查询其他的字段无任何意义

例：

1.根据性别分组，统计男员工和女员工的数量

2.根据性别分组，统计男员工和女员工的平均年龄

如上图，还可对显示的字段起别名，显示的时候更具可读性

3.查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

5.排序查询

1.语法

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;

2.排序方式

ASC：升序（默认值）

DESC：降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

例：

1.根据年龄对公司的员工进行升序排序

2.根据入职时间，对员工进行降序排序

3.根据年龄对公司的员工进行升序排序，年龄相同，再按照入职时间进行降序排序

6.分页查询

1.语法

SELECT 字段列表 FROM 表名 LIMIT 起始索引，查询记录数;

注意：

-起始索引从0开始，起始索引=（查询页码-1）*每页显示的记录数

-分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

-如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 查询记录数

例：

1.查询第1页员工数据，每页展示10条记录

2.查询第二页员工数据，每页展示10条记录

7.DQL复习

1.查询年龄为20，21，22，23岁的女性员工

2.查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工

3.统计员工表中， 年龄小于60岁的，男性员工和女性员工的人数

4.查询所有年龄小于等于35岁的员工的姓名和年龄，并对查询结果按年龄升序排列，如果年龄相同按入职时间降序排列

5.查询性别为男，且年龄在20-40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同的按入职时间升序排列

注意5. limit要写在最后，不能先limit再order by

7.DQL执行顺序

DQL的编写顺序：

DQL的执行顺序：

六.DCL

Data Control Language（数据控制语言），用来管理数据库用户，控制数据库的访问权限

1.管理用户

1.查询用户

USE mysql;   

SELECT * FROM user;

2.创建用户

CREATE USER  '用户名' @ '主机名'  IDENTIFIED BY  '密码' ;

例：

创建一个用户 itcast，只能在当前主机localhost访问，密码 112233

 创建一个用户 itcast2，可以在任意主机访问该数据库，密码 112233

当前创建的两个用户虽然可以连接登录MySQL数据库，当还没有权限访问数据

3.修改用户密码

ALTER USER  '用户名' @ '主机名'  IDENTIFIED WITH  mysql_native_password BY '新密码';

例：修改用户 itcast2 的访问密码为 1234

4.删除用户

DROP USER  '用户名' @ '主机名';

例：删除 itcast2@% 用户

注意：

-主机名可以使用%通配

-这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用

2.权限控制

MySQL中定义了很多种权限，但是常用的就以下几种：

1.查询权限

SHOW GRANTS FOR  '用户名' @ '主机名';

2.授予权限

GRANT 权限列表 ON 数据库名.表名 TO  '用户名' @ '主机名';

例：将数据库dyw_database中所有表的所有权限 授予用户 itcast

3.撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM  '用户名' @ '主机名';

例：撤销用户 itcast 在数据库dyw_database中所有表的所有权限

注意：

-多个权限之间，使用逗号分隔

-授权时，数据库名和表名可以使用*进行通配，代表所有

七.函数

函数是指一段可以直接被另一段程序调用的程序或代码

1.字符串函数

MySQL中内置了很多字符串函数，常用的有以下几种：

例：

1.截取字符串 'Hello mysql' 中的 'Hello'

注：MySQL中字符串索引从1开始

2.现要将企业员工的工号统一为5位数，目前不足5位数的全部在前面补0。如：1号员工的工号应该为00001。

2.数值函数

常见的数值函数如下：

例：通过数据库的函数，生成一个六位数的随机验证码

3.日期函数

常见的日期函数如下：

例：

1.计算 '2022-6-8' 1000天后的日期

2.查询所有员工的入职天数，并根据入职天数倒序排列

4.流程函数

流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率

例：

1.查询emp表员工的姓名和工作地址（北京/上海 ---> 一线城市，其他--->二线城市）

2.统计班级各个同学的成绩，展示的规则如下：

>=85，展示优秀

>=60，展示及格

否则，展示不及格

八.约束

1.概述

1.概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据

2.目的：保证数据库中数据的正确性，有效性和完整性

3.分类：

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

2.约束演示

根据需求，完成表中结构的创建

AUTO_INCREMENT 为自动增长项：

1. 自增长字段的值从1开始, 每次递增1。
2. 自增长字段数据不可以重复, 合适生成唯一的id。
3. 自增长字段可以使用null或者default来设置值。
4. 自增长字段必须是主键 (primary key)，每张表只能有一个

3.外键约束

1.概念

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性

具有外键的表叫子表，外键所关联的表叫父表

2.外键约束

删除/更新行为

前两条是默认值

2.语法：

1.添加外键

CREATE TABLE 表名(

        字段名  数据类型

        .....

[CONSTRAINT]  [外键名称]  FOREIGN KEY(外键字段名)  REFERENCES  主表 (主表列名) ON UPDATE  行为 ON DELETE 行为；

);

或建表后：

ALTER TABLE 表名 ADD CONSTRAINT  外键名称 FOREIGN KEY(外键字段名)  REFERENCES  主表 (主表列名) ON UPDATE  行为 ON DELETE 行为；

2.删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

例：

九.多表查询

1.多表关系

概述：项目开发中，在进行数据库表结构的设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种关系，基本上分为3种：

一对多（多对一），多对多，  一对一

一对多（多对一）:

案例：部门与员工的关系

关系：一个部门对应多个员工，一个员工对应一个部门

实现：在多的一方建立外键，指向一的一方的主键

多对多：

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

一对一：

案例：用户与用户详情的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情放在另一张表中，以提升操作效率

实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

例：

此为原表：

将表拆分为两张表：

2.多表查询概述

概述：指从多张表中查询数据

笛卡尔积：笛卡尔乘积是指在数学中，两个集合A和B的所有组合情况（在多表查询时，需要消除无效的笛卡尔积）

如：直接这样查询

会显示出两张表的所有组合情况

多表查询分类：

3.内连接

 内连接查询的是两张表交集的部分（下图绿色部分）

语法：

隐式内连接：SELECT 字段列表 FROM  表1,表2  WHERE 条件...;

显式内连接：SELECT 字段列表 FROM  表1 [ INNER ] JOIN 表2 ON 连接条件...;

例：

查询每一个员工的姓名以及关联的部门的名称

隐式：

显式：

此时是查询不到部门id为null的员工信息的

4.外连接

记忆技巧：要查询哪个表的所有数据，就把哪个表放在LEFT关键字的左边或放在RIGHT关键字的右边

所以左外连接与右外连接的语句可以相互转换

右外连接通常改写成左外连接语句

例：

1.查询emp表的所有数据，和对应的部门信息

2.查询dept表的所有数据，和对应的员工信息

5.自连接

语法：

SELECT  字段列表  FROM  表A  别名A  JOIN  表A  别名B  ON  条件...;

自连接查询时，可以是内连接查询，也可以是外连接查询，根据情况而定

例：

1.查询员工 及其 所属领导的姓名

此时采用内连接实现自连接

此时managerid为null的员工信息不会显示

2.查询所有员工 及其领导的名字，如果员工没有领导，也需要查询出来

此时则采用外连接实现自连接

此时managerid为null的员工信息也会显示

6.联合查询

对于联合查询，就是将多次查询的结果合并起来，形成一个新的结果集

语法：

SELECT 字段列表 FROM 表A ...

UNION  [ ALL ]

SELECT 字段列表 FROM 表B ...;

注意：

联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重

例：查询薪资小于5000和年龄大于50的员工信息，若都符合 只显示一次

7.子查询

1.概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询

语法：SELECT * FROM 表1 WHERE column1=(SELECT column1 FROM 表2);

子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

根据子查询的结果不同，分为：

-- 标量子查询（子查询结果为单个值）

-- 列子查询（子查询结果为一列）

-- 行子查询（子查询结果为一行)

-- 表子查询（子查询结果为多行多列）

根据子查询位置，分为：WHERE之后，FROM之后，SELECT之后

2.标量子查询

子查询返回的结果是单个值（数字，字符串，日期等），最简单的形式

常用的操作符：= ，<>， <， >，<=，>=

例：

1.查询 销售部 所有的员工信息

2.查询在 “方东白” 入职后 入职的员工

3.列子查询

子查询返回的结果是一列（可以是多行）

常用的操作符：IN，NOT IN，ANY，SOME，ALL

in 等价于 =any

例：

1.查询 销售部 和 市场部 的所有员工信息

2.查询比 财务部所有人工资 都高的员工信息

3.查询比 研发部其中任意一人工资 高的员工信息

4.行子查询

子查询返回的结果是一行（可以是多列）

常用的操作符：=，<>，IN，NOT IN

例：查询与 张无忌 的薪资及直属领导相同的员工信息

5.表子查询

子查询返回的结果是多行多列

常用的操作符：IN

经常用于from之后，把表子查询返回的结果作为一张临时表再和其他表进行联查操作

例：

1.查询与 鹿杖客，宋远桥 的职位和薪资相同的员工信息

2.查询入职日期是 "2006-01-01" 之后的员工信息，及其部门信息

6.多表查询练习

1.查询员工的姓名，年龄，职位，部门信息

2.查询年龄小于30岁的员工姓名，年龄，职位，部门信息

3.查询拥有员工的部门ID，部门名称

4.查询所有年龄大于40岁的员工，及其归属的部门名称，如果员工没有分配部门，也要展示出来

5.查询所有员工的工资等级

6.查询 研发部 所有员工的的信息及工资等级

7.查询 研发部 员工的平均工资

8.查询工资比 灭绝 高的员工信息

9.查询比平均薪资高的员工信息

10.查询低于本部门平均薪资的员工信息

11.查询所有的部门信息，并统计部门的员工人数

12.查询所有学生的选课情况，展示出学生名称，学号，课程名称

十.事务

1.事务简介

概念：事务是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

一个事务的整体流程为：

----------开启事务

. . . .                    若出现异常，要进行回滚事务

----------提交事务

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务

2.事务操作

查看/设置事务提交方式（默认为自动提交）

查看：SELECT @@autocommit;

设置：SET @@autocommit = 0;   （0为手动提交，1为自动提交）

手动开始事务的两种方式：

1.

先将 @@autocommit 改为 0 ，即手动提交之后

提交事务：COMMIT;

回滚事务：ROLLBACK;

2.（该方式更好理解“事务范围”的概念）

开启事务：START TRANSACTION 或 BEGIN;

提交事务：COMMIT;

回滚事务：ROLLBACK;

3.事务四大特性（ACID）

1.原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败

2.一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态

3.隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

4.持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

4.并发事务问题

5.事务隔离级别

注：√ 代表会出现该问题，×代表不会出现该问题 

        Repeatable Read是MySQL的默认事务隔离级别

        事务隔离级别越高，数据越安全，但是性能越低

-- 查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

-- 设置事务隔离级别

SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL 隔离级别;