MySQL数据库（上）-CSDN博客

本文链接：https://blog.csdn.net/2501_91884770/article/details/148345734

数据库、数据库管理系统、SQL

数据库（DataBase（DB））：存储数据的仓库，数据是有组织的进行存储

数据库管理系统（DataBase Management System (DBMS)）：操纵和管理数据库的大型软件

SQL（Structured Query Language (SQL)）：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准

在终端中开启数据库和停止数据库


net start mysql80        --开启数据库


 net stop mysql80        --停止数据库

注意：在终端中开启数据库和停止数据库时一定要以管理员的身份运行

关系型数据库（RDBMS）

建立在关系模型基础上，由多张相互连接的二维表组成的数据库，我们在此讨论的数据库都是关系型数据库

不是基于二维表存储数据的数据库，就是非关系型数据库

SQL通用语法

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

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

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

4. 注释：单行注释：-- 注释内容或 # 注释内容多行注释：/* 注释内容 */

SQL分类

根据其功能，主要分为四类：DDL、DML、DQL、DCL。

DDL

操作数据库

--查询所有数据库
show databases;
 
--查询当前数据库
select database();

--创建数据库
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序
规则 ] ;

--删除数据库
drop database [ if exists ] 数据库名;

--切换数据库
use 数据库名;

表操作

--查询当前数据库所有表
show tables;

--查看指定表结构
desc 表名;

--查询指定表的建表语句
show create table 表名;

--创建表结构
CREATE TABLE  表名(
字段1  字段1类型 [ COMMENT  字段1注释 ],
字段2  字段2类型 [COMMENT  字段2注释 ],
字段3  字段3类型 [COMMENT  字段3注释 ],
 ......
字段n  字段n类型 [COMMENT  字段n注释 ] 
) [ COMMENT  表注释 ] ;
--注意: [...] 内为可选参数，最后一个字段后面没有逗号

表操作-数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型

数值类型

注意

1. 年龄字段 -- 不会出现负数, 而且人的年龄不会太大
age tinyint unsigned
2. 分数 -- 总分100分, 最多出现一位小数
score double(4,1) 

字符串类型

注意：char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关。而varchar是变长字符串，指定的长度为最大占用长度。相对来说，char的性能会更高些

日期时间类型

表操作-修改

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

--修改数据类型
ALTER TABLE 表名 MODIFY  字段名  新数据类型 (长度);
 
--修改字段名和字段类型
ALTER TABLE 表名 CHANGE  旧字段名  新字段名  类型 (长度)  [ COMMENT 注释 ]  [ 约束 ];

--删除字段
ALTER TABLE 表名 DROP  字段名;

--修改表名
ALTER TABLE 表名 RENAME TO  新表名;


--删除表
DROP  TABLE [ IF  EXISTS ]  表名;

--删除指定表, 并重新创建表
TRUNCATE  TABLE 表名;
--注意: 在删除表的时候，表中的全部数据也都会被删除

图形化工具

我们可以直接使用idea作为我们的图形化工具，直接在idea中配置即可，无需无需下载别的额外的图形化工具

DML

作用

添加数据（INSERT）

修改数据（UPDATE）

删除数据（DELETE）

添加数据（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）

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

注意

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据

删除数据

 DELETE  FROM  表名  [ WHERE  条件 ];

注意

1.DELETE语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据

2.DELETE语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)

3.当进行删除全部数据操作时，datagrip会提示我们，询问是否确认删除，我们直接点击 Execute即可

DQL

1.查询关键字: SELECT

2.我们使用查的频率远高于使用增删改的频率

3.在查的过程中，涉及到条件、排序、分页等操作

基本语法

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

基础查询

--查询指定字段的数据
SELECT   字段1, 字段2, 字段3 ...  FROM   表名; 

--查询所有数据
SELECT  *  FROM   表名;

--字段设置别名
SELECT   字段1  [ AS  别名1 ] , 字段2  [ AS  别名2 ]   ...  FROM   表名; 
--还可以
SELECT   字段1  [ 别名1 ] , 字段2  [ 别名2 ]   ...  FROM   表名;

--去除重复记录
SELECT  DISTINCT  字段列表  FROM   表名;

注意 : * 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）

条件查询

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

常用的比较运算符

常用的逻辑运算符

-- 查询姓名为两个字的员工信息 _ %
select * from emp where name like '__';

--查询身份证号最后一位是X的员工信息
select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';

聚合函数

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

注意

NULL值是不参与所有聚合函数运算

分组查询

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

where与having区别

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

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

注意

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

2.执行顺序: where > 聚合函数 > having

3.支持多字段分组, 具体语法为 : group by columnA,columnB

排序查询

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

排序方式

ASC : 升序(默认值)

DESC: 降序

分页查询

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

注意

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

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

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

--查询第1页员工数据, 每页展示10条记录
 select * from emp limit 0,10;
 select * from emp limit 10;

--查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数
select * from emp limit 10,10;

执行顺序

DCL

--查询用户
select * from mysql.user;

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

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

--删除用户
DROP USER '用户名'@'主机名' ;

注意

1.在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户

2.主机名可以使用 % 通配

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

权限控制

--查询权限
1SHOW GRANTS FOR '用户名'@'主机名';

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

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

注意

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

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

字符串函数

具体演示

--concat : 字符串拼接 
select concat('Hello' , ' MySQL'); 

--lower : 全部转小写
 select lower('Hello');
 
--upper : 全部转大写
 select upper('Hello'); 

--lpad : 左填充
 select lpad('01', 5, '-');
 
--rpad : 右填充
 select rpad('01', 5, '-'); 

--trim : 去除空格
 select trim(' Hello  MySQL ');
 
--substring : 截取子字符串
select substring('Hello MySQL',1,5);

数值函数

日期函数

流程函数

--if
select if(false, 'Ok', 'Error');

--ifnull
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');

--case when then else end
 select
  name,
  ( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else 
'二线城市' end ) as '工作地址'
 from emp;

--例子
 create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
 ) comment '学员成绩表';
 insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95 
), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

select
 id,
 name,
 (case when math >= 85 then '优秀' when math >=60 then '及格' else '不及格' end ) 
'数学',
 end ) '英语',
 end ) '语文'
 (case when english >= 85 then '优秀' when english >=60 then '及格' else '不及格' 
(case when chinese >= 85 then '优秀' when chinese >=60 then '及格' else '不及格' 
from score;

约束

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

目的：保证数据库中数据的正确、有效性和完整性。

注意

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

我们可以利用idea的图形化界面来设置约束

CREATE TABLE tb_user(
    id int AUTO_INCREMENT PRIMARY KEY  COMMENT  'ID唯一标识',
    name varchar(10) NOT NULL UNIQUE  COMMENT  '姓名' ,
    age int check (age > 0 && age <= 120)  COMMENT  '年龄' ,
    status char(1) default  '1'  COMMENT  '状态',
    gender char(1)  COMMENT  '性别'
 );

外键约束

CREATE TABLE 表名(
字段名    数据类型,
 ...
 [CONSTRAINT]    [外键名称]  FOREIGN  KEY (外键字段名)   REFERENCES   主表 (主表列名)
);
 

 ALTER   TABLE  表名   ADD  CONSTRAINT   外键名称   FOREIGN   KEY (外键字段名)  
REFERENCES  主表 (主表列名) ;

删除外键

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

删除/更新行为

 ALTER TABLE  表名  ADD CONSTRAINT  外键名称  FOREIGN KEY  (外键字段)   REFERENCES   
主表名 (主表字段名)   ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

一对多(多对一)：部门与员工的关系

多对多：学生与课程的关系

一对一：用户与用户详情的关系

我们要执行多表查询，就只需要使用逗号分隔多张表即可

select * from emp , dept;

分类

连接查询

内连接：相当于查询A、B交集部分数据

外连接：左外连接和右外连接

左外连接：查询左表所有数据，以及两张表交集部分数据

右外连接：查询右表所有数据，以及两张表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

内连接的语法分为

隐式内连接

显式内连接

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

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

外连接

左外连接

右外连接

--左外连接
1SELECT  字段列表   FROM   表1  LEFT  [ OUTER ]  JOIN 表2  ON  条件 ... ;


--右外连接
SELECT  字段列表   FROM   表1  RIGHT  [ OUTER ]  JOIN 表2  ON  条件 ... ;

注意

左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。

而我们在日常开发使用时，更偏向于左外连接。

自连接

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

联合查询

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

SELECT  字段列表   FROM   表A  ...  
UNION [ ALL ]
SELECT  字段列表  FROM   表B  ....;

注意

1.对于联合查询的多张表的列数

必须保持一致，字段类型也需要保持一致

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

子查询

 SELECT  *  FROM   t1   WHERE  column1 =  ( SELECT  column1  FROM  t2 );

标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询

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

列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询

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

行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询

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

表子查询

子查询返回的结果是多行多列，这种子查询称为表子查询。

常用的操作符：IN

事务

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

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

控制事务1

--查看/设置事务提交方式
SELECT  @@autocommit;
SET   @@autocommit = 0;

--提交事务
1COMMIT;

--回滚事务
1ROLLBACK;

注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。

控制事务2

--开启事务
START  TRANSACTION   或  BEGIN ;

--提交事务
COMMIT;

--回滚事务
ROLLBACK;

转账案例

-- 开启事务
start transaction 
-- 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 = '李四';
-- 如果正常执行完毕, 则提交事务
commit;
-- 如果执行过程中报错, 则回滚事务-- rollback;

事务四大特性

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

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

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

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

上述就是事务的四大特性，简称ACID

并发事务问题

1. 赃读：一个事务读到另外一个事务还没有提交的数据。

2. 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。

3. 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 "幻影"。

事务隔离级别

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

SET  [ SESSION | GLOBAL ]  TRANSACTION  ISOLATION  LEVEL  { READ UNCOMMITTED | 
READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

注意

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