【MySQL数据库基础】一篇文章带你入门_sql

🌈博客主页：强子的博客
📅 发文时间：2022.4.16
📕Java网络编程
🎈 一定存在只有你才能做成的事
🌹 博主水平有限，如有错误，欢迎指正
欢迎给位👍收藏💎评论✉

MySQL数据库基础数据库相关概念SQL● DDL-数据库 *** 作● DML● DQL● DCL 函数约束● 外键约束多表查询多表查询概述事务事务 *** 作四大特性ACID并发事务问题

数据库相关概念

名称	全称	简称
数据库	存贮数据的仓库，数据是有组织的进行存储	DataBase（DB）
数据库管理系统	*** 纵和管理数据库的大型软件	DataBase Management System（DBMS）
SQL	* 作关系型数据库的编程语言，定义了一套 * 作关系型数据库统一标准	Structured Query Language（SQL）

SQL

SQL通用语法
1.SQL语句可以单行或多行书写，以分号结尾。
2.SQL语句可以使用空格/缩进来增强语句的可读性。
3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4.注释：
单行注释：-- 注释内容或#注释内容(MySQL特有)
多行注释：/注释内容/ 同Java/*

● SQL分类
分类全称说明
DDL Data Definition Language 数据定义语言，用来定义数据库对象（数据库，表，字段）
DML Data Manipulation Language 数据 *** 作语言，用来对数据库表中的数据进行增删改
DQL Data Query Language 数据查询语言，用来查询数据库中表的记录
DCL Data Control Language 数据控制语言，用来创建数据库用户、控制数据库的访问权限

● DDL-数据库 *** 作

dos *** 作mysql -uroot -p

查询
查询所有数据库 SHOW DATABASES
查询当前数据库 SELECT DATABASE();
创建
CREATE DATABASE[IF NOT EXISTS]数据库名[DEFAULT CHARSET字符集].[COLLATE排序规则];
删除
DROP DATABASE[IF EXISTS]数据库名；
使用
USE 数据库名；

●DDL-表 *** 作-查询
查询当前数据库所有表 SHOW TABLES;
查询表结构 DESC表名；
查询指定表的建表语句 SHOW CREATE TABLE表名；

● DDL-表 *** 作-创建
CREATE TABLE 表名**(**
字段1 字段1类型[COMMENT 字段1注释]，
字段2 字段2类型[COMMENT 字段2注释]，
字段3 字段3类型[COMMENT 字段3注释]，
…
字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释]；
注意：[…]为可选参数，最后一个字段后面没有逗号

● DDL-表 *** 作-数据类型
MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-P17kQ2rT-1650116286791)(D:\C盘移动\图片\java\Mysql\字符.png)]注意：decimal中精度指有效位数，标度指小数位

字符串类型，带blob为二进制数据，text为文本数据
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l0A1VRnj-1650116286792)(C:\Users\BRILIANT\AppData\Local\Temp\1649073719735.png)]
定长指char(10)指定长度后写入无论几位，多出的空格占位，性能好
变长指写入几位就是几位性能较差

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HxRDs9Sz-1650116286793)(C:\Users\BRILIANT\AppData\Local\Temp\1649074291634.png)]

● DDL-表 *** 作-修改
添加字段 ALTER TABLE表名ADD字段名类型（长度）[COMMENT注释].[约束];
修改数据类型 ALTER TABLE表名MODIFY字段名新数据类型（长度）;
修改字段名和字段类型ALTER TABLE表名CHANGE 旧字段名新字段名类型（长度）[COMMENT注释].[约束];
删除字段 ALTER TABLE 表名 DROP 字段名；
修改表名 ALTER TABLE 表名 RENAME TO 新表名；
删除表 DROP TABLE[IF EXISTS]表名；
删除指定表，并重新创建该表TRUNCATE TABLE表名；

● DML

DML英文全称是Data Manipulation Language(数据 *** 作语言)，用来对数据库中表的数据记录进行增删改 *** 作。

添加数据(INSERT)
修改数据(UPDATE)
删除数据(DELETE)

● DML-添加数据
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，…）；
注意：
·插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
·字符串和日期型数据应该包含在引号中。
·插入的数据大小，应该在字段的规定范围内.

● DML-修改数据
UPDATE 表名 SET 字段名1=值1，字段名2=值2，[WHERE条件]；
注意：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

● DML-删除数据
DELETE FROM表名[WHERE条件]
注意：
·DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
·DELETE语句不能删除某一个字段的值（可以使用UPDATE)。

● DQL

DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。
查询关键字：SELECT

●DQL-语法编写顺序：
SELECT 字段列表
FROM 表名列表
WHERE 条件列表
GROUP BY分组字段列表
HAVING分组后条件列表
ORDER BY排序字段列表
LIMIT 分页参数

基本查询
条件查询(WHERE)
聚合函数(count、max、min、avg、sum)
分组查询(GROUP BY)
排序查询(ORDER BY)
分页查询(LIMT)

● DQL-基本查询
1.查询多个字段
SELECT字段1，字段2，字段3 FROM表名；
SELECT*FROM表名；
2.设置别名
SELECT字段1[AS别名1]，字段2[AS别名2]…FROM表名；
3.去除重复记录
SELECT DISTINCT字段列表FROM 表名；

● DQL-条件查询
1.语法
SELECT字段列表FROM表名WHERE条件列表；
比较运算符 >= 功能
<>或！= 不等于
BETWEEN…AND…在某个范围之内（含最小、最大值）
IN(.…) 在in之后的列表中的值，多选一
LIKE占位符模糊匹配(_匹配单个字符，%匹配任意个字符)如名字个数，含有就%
IS NULL 是NULL

逻辑运算符功能
AND或&& 并且（多个条件同时成立）
OR或|| 或者（多个条件任意一个成立）
NOT或！非，不是

● DQL-聚合函数
1.介绍：将一列数据作为一个整体，进行纵向计算。
2.常见聚合函数
函数功能
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
3.语法
SELECT聚合函数（字段列表）FROM表名；
注意：所有null值不参与聚合运算。

●DQL-分组查询
1.语法
SELECT字段列表FROM表名[WHERE条件]GROUP BY分组字段名[HAVING分组后过滤条件]；
2.where与having区别
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。
注意
·执行顺序：where>聚合函数>having。
·分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

● DQL-排序查询
1.语法
SELECT字段列表FROM表名ORDER BY字段1排序方式1，字段2排序方式2;
2.排序方式
ASC:升序（默认值）
DESC:降序
注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

●DQL-分页查询
1.语法
SELECT字段列表FROM表名LIMT起始索引，查询记录数;
注意
·起始索引从0开始，起始索引=（查询页码-1)*每页显示记录数。
·分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
·如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

执行顺序：
FROM表名列表
WHERE条件列表
GROUP BY分组字段列表
HAVING分组后条件列表
SELECT字段列表
ORDER BY排序字段列表
LIMIT分页参数

--          DQL语句练习
-- 1.查询年龄为20,21,22,23岁的女性员工信息。
select * from emp where gender = '女' && age in (20,21,22,23);
-- 2.查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工。
select * from emp where gender = '男' and (age between 20 and 40 )and name like '___';
-- 3.统计员工表中，年龄小于60岁的，男性员工和女性员工的人数。
select gender,count(*) from emp where age < 60 group by gender ;
-- 4,查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序。
select name,age from emp where age <= 35 order by  age,entrydate desc ;
-- 5.查询性别为男，且年龄在20-40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。
select * from emp where gender = '男' and  age between 20 and 40 order by age ,entrydate desc limit 5;

● DCL

DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

DCL-管理用户
1.查询用户
USE mysql;
SELECT FROM user;
2.创建用户
CREATE USER’用户名’@‘主机名’ IDENTIFIED BY’密码；
3.修改用户密码
ALTER USER’用户名’@‘主机名’IDENTIFIED WITH mysql_native_password BY’新密码’；
4.删除用户
DROP USER’用户名’@‘主机名’；
注意：
·主机名可以使用%通配。
·这类SQL开发人员 *** 作的比较少，主要是DBA(Database Administrator数据库管理员)使用。

● DCL-权限控制
MySQL中定义了很多种权限，但是常用的就以下几种：
权限说明
ALL,ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表
其他权限描述及含义，可以直接参考官方文档。

1.查询权限
SHOW GRANTS FOR’用户名’@‘主机名’；
2.授予权限
GRANT权限列表ON数据库名.表名TO‘用户名’@主机名’;
3.撤销权限
REVOKE权限列表ON数据库名.表名FROM’用户名’@‘主机名’;
注意：
·多个权限之间，使用逗号分隔
·授权时，数据库名和表名可以使用*进行通配，代表所有。

show grants for 'heima'@'%';
-- 授权
grant all on itcast.* to 'heima'@'%';
-- 撤销
revoke all on itcast.* from 'heima'@'%';

函数

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

● 字符串函数 select 函数（参数）；
MySQL中内置了很多字符串函数，常用的几个如下：
函数功能
CONCAT(S1,S2,…Sn) 字符串拼接，将S1,S2,.Sn拼接成一个字符串
LOWER(str) 将字符串str全部转为小写
UPPER(str) 将字符串str全部转为大写
LPAD(str,n,pad)左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(str) 去掉字符串头部和尾部的空格
SUBSTRING(str,start,len) 返回从字符串str从start位置起的len个长度的字符串

数值函数
常见的数值函数如下：
函数功能
CEIL(x) 向上取整
FLOOR(X刈向下取整
MOD(x,y) 返回x/y的模
RAND() 返回0~1内的随机数
ROUND(x,y) 求参数x的四舍五入的值，保留y位小数

日期函数
常见的日期函数如下：
函数功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date)获取指定date的年份
MONTH(date)获取指定date的月份
DAY(date) 获取指定date的日期
DATE_ADD(date,INTERVAL expr type)返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2)返回起始时间date1和结束时间date2之间的天数

流程函数
流程函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句的效率。
函数功能
IF(value , t , f) 如果value为true，则返回t，否则返回f
IFNULL(value1,value2) 如果value1不为空，返回value1，否则返回value2
CASE WHEN [ val1 ] THEN [res1] … ELSE [ default ] END 如果val1为true，返回res1，…否则返回default默认值
CASE[ expr ] WHEN [ val1 ] THEN [res1] … ELSE [ default ] END 如果expr的值等于val1，返回res1，…否则返回default默认值

约束

● 概述

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确、有效性和完整性。
分类：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4GAeTD0f-1650116286794)(D:\C盘移动\图片\java\Mysql\1649148168786.png)]
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。

根据需求完成表结构创建
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2Uq2Y0WO-1650116286794)(D:\C盘移动\图片\java\Mysql\1649148764779.png)]

● 外键约束

概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MNwO6SEm-1650116286795)(D:\C盘移动\图片\java\Mysql\1649149665593.png)]
注意：目前上述的两张表，在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。

●语法

添加外键

CREATE TABLE表名(
字段名	数据类型
[CONSTRAINT][外键名称]FOREIGN KEY(外键字段名)REFERENCES主表（主表列名）    
ALTER TABLE表名ADD CONSTRAINT外键名称FOREIGN KEY(外键字段名)REFERENCES主表（主表列名）；
-- 删除外键
ALTER TABLE表名DROP FOREIGN KEY外键名称；

● 删除/更新行为
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Wfrj36ij-1650116286795)(C:\Users\BRILIANT\AppData\Local\Temp\1649151186384.png)]
ALTER TABLE表名ADD CONSTRAINT外键名称FOREIGN KEY(外键字段)REFERENCES主表名（主表字段名）ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

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

●一对多（多对一）

案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键

●多对多

案例：学生与课程的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

●一对一

案例：用户与用户详情的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升 *** 作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

多表查询概述

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

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

●多表查询分类

连接查询
内连接：相当于查询A、B交集部分数据
外连接：
左外连接：查询左表所有数据，以及两张表交集部分数据
右外连接：查询右表所有数据，以及两张表交集部分数据
自连接：当前表与自身的连接查询，自连接必须使用表别名
子查询

●连接查询-内连接

内连接查询语法：
隐式内连接
SELECT字段列表 FROM表1 表2 WHERE条件；

显式内连接
SELECT字段列表FROM表1[INNER]JOIN 表2 ON连接条件…；
内连接查询的是两张表交集的部分

●连接查询-外连接
外连接查询语法：
左外连接
SELECT字段列表FROM表1 LEFT[OUTER]JOIN 表2 ON 条件；
相当于查询表1（左表）的所有数据包含表1和表2交集部分的数据
右外连接
SELECT字段列表FROM表1 RIGHT[OUTER]JOIN表2ON条件.；
相当于查询表2（右表）的所有数据包含表1和表2交集部分的数据

●连接查询-自连接
自连接查询语法：
SELECT字段列表FROM 表A别名A JOIN表A别名BON条件
自连接查询，可以是内连接查询，也可以是外连接查询。

-- 查询员工 以及 所述领导的名字 (内连接自连接)
SELECT e1.name, e2.name FROM emp e1 JOIN emp e2 ON e1.managerid = e2.id;
-- 查询员工 以及 所述领导的名字, 如果无，也要显示 (外连接自连接)
SELECT e1.name, e2.name FROM emp e1 LEFT OUTER JOIN emp e2 ON e1.managerid = e2.id;

●联合查询-union,union all
对于uion查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
SELECT字段列表FROM 表A…
UNION [ALL]
SELECT字段列表FROM 表B…;
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
union all会将全部的数据直接合并在一起，union会对合并之后的数据去重。

●子查询
概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
SELECT FROM t1 WHERE column1 =(SELECT column1 FROM t2);
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT的任何一个。
根据子查询结果不同，分为：

标量子查询（子查询结果为单个值）
列子查询（子查询结果为一列）
行子查询（子查询结果为一行）
表子查询（子查询结果为多行多列)
根据子查询位置，分为：WHERE之后、FROM之后、SELECT之后。

● 列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的 *** 作符：IN、NOT IN、ANY、SOME、ALL
*** 作符描述
IN 在指定的集合范围之内，多选一
NOT IN 不在指定的集合范围之内
ANY 子查询返回列表中，有任意一个满足即可
SOME 与ANY等同，使用SOME的地方都可以使用ANY
ALL 子查询返回列表的所有值都必须满足

●行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的 *** 作符：=、<>、IN、NOT IN

●表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的 *** 作符：IN

事务

事务是一组 *** 作的集合，事务会把所有 *** 作作为一个整体一起向系统提交或撤销 *** 作请求，即这些 *** 作要么同时成功，要么同时失败。

-- 转账 *** 作
-- 1. 查询张三余额
select * from account where name = '张三';
-- 2. 张三的余额减少1000
update account set money = money - 1000 where name = '张三';

程序抛出异常...
-- 3. 李四的余额增加1000
update account set money = money + 1000 where name = '李四';
-- 恢复数据
update account set money = 2000 where name = '李四' or name = '张三';

事务 *** 作

-- 查看事务提交方式
SELECT @@AUTOCOMMIT;
-- 设置事务提交方式，1为自动提交，0为手动提交，该设置只对当前会话有效
SET @@AUTOCOMMIT = 0;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;
-- 开启事务
START TRANSACTION BEGIN

四大特性ACID 原子性(Atomicity)：事务是不可分割的最小 *** 作但愿，要么全部成功，要么全部失败一致性(Consistency)：事务完成时，必须使所有数据都保持一致状态隔离性(Isolation)：数据库系统提供的隔离机制，保证事务在不受外部并发 *** 作影响的独立环境下运行持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的并发事务问题

问题	描述
脏读	一个事务读到另一个事务还没提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是再插入数据时，又发现这行数据已经存在

并发事务隔离级别：