MySQL学习记录

不变量

已于 2022-10-20 16:36:54 修改

阅读量283

点赞数

分类专栏： MySQL 文章标签： mysql 数据库学习

于 2022-10-20 16:35:50 首次发布

本文链接：https://blog.csdn.net/qq_60916002/article/details/127430099

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

MySQL

//以下笔记来自笔者记录的老杜MySQL网课
选择使用某个数据库:

mysql>use test;
Database changed

创建数据库:

mysql>create database bjpowernode

数据库中最基础的单元是表(table)，任何一张表都有行和列:

行(row)：被称为数据/记录

列(column)：被称为字段，每一个字段都有：字段名，数据类型，约束等属性。

查看表中的数据：select * from 表名;
查看当前有哪些数据库：show databases;
不看表中的数据，只看表的结构：desc 表名;
查看数据库版本号：mysql>select version();
查看当前使用的是哪个数据库：mysql>select database();

MySQL是不见分号不执行，CTRL+C或者/c用来终止一条命令的输入

sql语句不区分大小写，以;结尾

SQL语言分类

DQL：数据查询语言(凡是带有select关键字都是查询语句)

DML：数据操作语言(凡是对表中数据进行增删改的都是DML) 主要操作表中的数据

insert 增
delete 删
update 改

DDL：数据定义语言(凡是带有create，drop，alter的都是DDL) 其主要操作的是表的结构，而不是表中的数据

create 新建，等同于增
drop 删除
alter 修改

TCL：事务控制语言

事务提交：commit
事务回滚：rollback

DCL：数据控制语言

授权：grant
撤销权限：revoke

DQL

简单查询

查询一个字段：select 字段名 from 表名;
查询两个或多个字段：select 字段名1,字段名2,字段名3… from 表名; 若要查询所有字段：select * from 表名;
给查询序列起别名：as关键词，不会改变数据库本身。

条件查询

select 字段 from 表名 where 条件;
//eg. select ename from emp where sal = 3000;

MySQL中的不等于可以用<> or != 来表示，等于直接用=就行。

between—and—，使用时需要注意条件是左小右大，且包括两端的值(都是闭区间)
is null / is not null 查询哪些为空哪些不为空
and 并且 and优先级比or高
or 或者
in / not in 包含与不包含，in不是一个区间，其后跟的是一个具体的值。
not 取非
like 模糊查询，支持%或下划线匹配

**%匹配任意个字符 **

select ename from emp where ename like '%O%';//找出名字里面有O的
select ename from emp where ename like '%T';//找出名字里面以T结尾的
select ename from emp where ename like 'K%';//找出名字以K开头的
select ename from emp where ename like '__R%';//找出第三个字母是R的

若碰见关键字，用转移字符’ \ ’

下划线 一个下划线只匹配一个字符

按序列查询：

select ename,sal from emp order by sal asc;//升序排列，降序用desc

数据处理函数：分为单行处理函数和多行处理函数。

单行处理函数

lower/upper 转换小写/大写 select lower(ename) from emp;
substr 取子串，下标从1开始。 select ename from emp where(ename,1,1)=‘A’;
concat函数，做字符串拼接
trim 去空格 round(123.456 , 2)保留两位小数
round 四舍五入
rand 生成随机数
ifnull 可以将null转化为一个具体值，是空处理函数，专门处理空的；在所有数据库中，只要有null参与的数学运算，结果都是null；用法：ifnull(数据，被当作哪个值)

case…when…then…when…then…else…end.

select ename,job,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;

多行处理函数

count 计数
sum 求和
avg 平均值
max/min 最大值/最小值

分组函数在使用的时候必须先分组才能用，如果对数据没有分组，整张表默认为一组。
分组函数自动忽略null，不需要对null做预处理。
count(*) 和 count()的区别：
1. count(具体字段)：统计该字段下所有不为null的元素的元素总数
2. count(*)：统计表当中的总行数(只要有一行数据就count++)
分组函数不能直接使用在where语句中。

分组查询

select() from() group by()
select job,sum(sal) from emp group by job;

关键字执行顺序：

from
where
group by
having
select
order by
limit

select ename,sal from emp where sal>min(sal);

这段语句不能执行的原因是，where执行的时候还没有分组，所以后面不能出现分组函数。

select ename,job,sum(sal) from emp group by job;
上述语句中ename是不能写的，在一条select语句中如果有 group by语句的话，select后面只能跟：参加分组的字段及分组函数。

having是对分完组之后的数据进一步过滤，不能单独使用，不能代替where，且必须和group by联合使用

select deptno,max(sal) from emp deptno having max(sal) > 3000;

distinct关键字：去重

select count(distinct ename) from emp;
select distinct ename,job from emp;

连接查询

表连接的方式：

内连接：
- 等值连接
- 非等值连接
- 自连接(将自身看成两张表，自己连接自己)
外连接
- 左外连接(左连接)
- 右外连接(右连接)
全连接(用的很少)

当两张表进行无条件的连接时，会出现笛卡尔积现象。若要避免这种情况，应该设置限制条件:

select ename,dname from emp,dept where emp.deptno = dept.deptno;
或者
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;//emp e是使emp的别名为e，中间其实省略了as。这种写法是sql92版的写法。
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;//这是sql99的写法,inner是内连接，可以省略，但是带着inner代码可读性更好。

匹配次数并未减少，只是将符合where条件的行筛选出来显示。通过笛卡尔积现象知，表达连接次数越多，效率就越低。

外连接(右外连接):
right代表将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询坐标的表，连接中产生了主次关系，但是内连接不会产生主次关系。

select e.ename,d.dname from emp e right join dept d on e.deptno = d.deptno;

由上定义可知，外连接的查询结果条数 >= 内连接的查询结果条数一定成立。

连接多张表：

select...from a join b on ... join c on ... join d on ...

子查询

select语句中嵌套select语句，被嵌套的select语句成为子查询。

where子句中的子查询：

select ename,sal from emp where sal > (select min(Sal) from emp);

from子句中的子查询：可以将子查询的查询结果当作一张表，这张表是临时的。

select t.*,s.grade from (select job avg(sal) as avgsal from emp group by job) t join salgrade s on t.avgsal between s.losal and s.hisal;

select子句后出现的子查询：

select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

union合并查询结果集

select * from emp where job in ('MANAGER','SALESMAN');
-----------------------------------------------------
select * from emp where job = 'MANAGER'
union
select * from emp where job = 'SALESMAN';

union效率比起直接连接效率更高，相当于是简化了笛卡尔积，将乘法变成除法。union在进行结果集合并的时候，要求两个结果集列数相同。若有数据类型不一样，在Oracle中会报错，但是MySQL可以使用，因为MySQL语法相对宽松。

limit关键字

limit是用来将查询结果集的一部分取出来，通常使用在分页查询中。
limit如果后面只跟一个数字n，就是取前n条数据的意思。若有两个参数a,b，就是从下标a开始取b条数据：

limit(n);
limit(n,m);

select ename,sal from emp order by sal desc limit 5;

在MySQL中，limit在order by之后执行。

DDL

建表

create table tableName(
 columnName dataType(length),
 ………………..
 columnName dataType(length)
);

常用数据类型

类型	描述
Char(长度)	定长字符串，存储空间大小固定，适合作为主键或外键。(速度快)
Varchar(长度)	变长字符串，存储空间等于实际数据空间(速度慢)
double(有效数字位数，小数位，双精度)	数值型
Float(有效数字位数，小数位，单精度)	数值型
Int(长度)	整型
bigint(长度)	长整型
Date	日期型
BLOB(插入时需要使用IO流)	Binary Large OBject（二进制大对象）存储图片，声音，视频等。
CLOB	Character Large OBject（字符大对象) 存储超过255个字符以上的串。

e.g. 建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识

create table t_student(
	student_id int(10),
    student_name varchar(20),
    sex char(2) default 'm',//默认值
    birthday date,
    email varchar(30),
    class_id int(3)
)
create table emp2 as select * from emp;//相当于是表的复制。

删表

truncate table dept_bak;
drop table s_user;

truncate语句删除数据原理：删除效率比较高，表被一次截断，物理删除，但是表的结构还在

缺点：不支持回滚
优点：快速

drop table是直接把表删除，啥也不留下来。

DML

插入(insert)

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 
'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10);//可以不按顺利。
insert into t_user(id,name,birth,breate_time) values (1,'zs','1980-10-11',now()),(2,'hr','1999-10-10',now());//一次性插入多条记录。

格式化数据

format(数字,‘格式’)

select ename,format(sal,'$999,999')as sal from emp;

关于时间操作

str_to_date：将字符串varchar类型转换成date类型。
用法：str_to_date(‘字符串日期’,‘日期格式’)

date_format：将date类型转换成具有一定格式的varchar字符串类型

select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;
date_format(日期数据类型,'日期格式');

date和datetime的区别：

date是短日期，只包括年月日信息，格式为 ‘%Y-%m-%d’。
datetime是擦汗那个日期：包括年月日时分秒信息，格式为**‘%Y-%m-%d %h:%i:%s’**。

获取当前时间：**now()**函数，并且获取的时间带有时分秒的。

修改(update)

语法格式：

update 表名 set 字段名称 1=需要修改的值 1, 字段名称 2=需要修改的值 2 where …….
//没有条件会导致所有数据全部更新。
udpate t_user set name = 'jack',birth = '2000-10-11',create_time = now() where id = 'z';

删除数据(delete)

语法格式：

delete from 表名 where 条件;
delete from t_student where id = 2;
delete from t_user;//删除所有

delete语句删除原理：表中数据被删除，但是这个数据在硬盘上的空间不会被释放。(同DDL中的truncate比较)

优点：支持回滚rollback，可以恢复已删除的数据。
缺点：删除效率比较低。

约束

常见的约束

非空约束 not null
唯一性约束 unique
主键约束 primary key(简称PK)
外键约束 foreign key(简称FK)
检查约束 check(MySQL不支持，Oracle支持)

非空约束 not null

非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空

drop table if exists t_student; 
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date, 
email varchar(30),
classes_id int(3)
)

insert into t_student(student_id, birthday, email, classes_id) 
values
(1002, '1988-01-01', 'qqq@163.com', 10)//此时这个会报错，因为student_name设定了not null

唯一约束

唯一性约束，它可以使某个字段的值不能重复，但是可以为NULL，如：email 不能重复：

drop table if exists t_student; 
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date, 
email varchar(30) unique,
classes_id int(3)
)

若要多个字段联合起来具有唯一性：

drop table if exists t_student; 
create table t_student(
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date, 
email varchar(30)，
classes_id int(3),
unique(name,email)
)//此时除非name和email看作整体时重复，系统不会报错。
insert into t_student(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_student(id,name,email) values(1,'zhangsan','zhangsan@qq.com');
以上两句同时执行，系统不会报错。

直接加在列的后面的称为列级约束，放在最后的称为表级约束。

在MySQL中，如果一个字段同时被not null和unique约束的话，该字段自动变成主键字段。(Oracle不一样)

主键约束

每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的。
主键的特征：not null + unique(主键值不能是null也不能重复)。

drop table if exists t_student; 
create table t_student()
student_id int(10) primary key,/*列级约束*/
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date, 
email varchar(30) ,
classes_id int(3)
)
insert into t_student(student_id, student_name , sex, birthday, email, classes_id) 
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)
上述是单一主键

重复执行两次这个insert，会出现错误，因为加入了主键错误。

drop table if exists t_student; 
create table t_student()
student_id int(10),
student_name varchar(20) not null,
sex char(2) default 'm',
birthday date, 
email varchar(30) ,
classes_id int(3),
primary key(id,name)
)
此时为复合主键，但这种在开发中不太常用。

另一种分类：

自然主键：主键值是一个自然数，和业务没关系，这种用的较多。
业务主键：主键值和业务紧密关联。

id int primary key auto_increment//表示自增，比如从1开始增到n

外键约束

外键主要是维护表之间的关系的，主要是为了保证参照完整性。
如果表中的某个字段为外键字段，那么该字段的值必 69 / 103 须来源于参照的表的主键，如：emp 中的 deptno 值必须来源于 dept 表中的 deptno 字段值。
被引用的是父表，引用的是子表。删除时是先删子表再删父表。

子表中外键引用的父表中的某个字段，被引用的这个字段不一定是主键，但至少具有unique约束。
外键值可以为null

CREATE TABLE t_student(
	no int primary key auto_increment,
	name VARCHAR(255),
	cno int,
	FOREIGN KEY(cno) REFERENCES t_class(classno));

存储引擎

存储引擎是MySQL中特有的概念，实质上是一个表存储/组织数据的方式。
不同的存储引擎，表存储数据的方式不同。

查询当前服务器有哪些引擎可用，可使用SHOW ENGINES语句：

mysql>SHOW ENGINES\G

MySQL默认的存储引擎是InnoDB，字符集编码是utf8

在创建表时，可使用 ENGINE 选项为 CREATE TABLE 语句显式指定存储引擎。 CREATE TABLE TABLENAME (NOINT) ENGINE = MyISAM;
现有表的存储引擎可使用 ALTER TABLE 语句来改变：ALTER TABLE TABLENAME ENGINE = INNODB

常用的存储引擎：

MyISAM

它管理的表具有以下特征：

使用三个文件表示每个表：
- 格式文件 — 存储表结构的定义（mytable.frm）
- 数据文件 — 存储表行的内容（mytable.MYD）
- 索引文件 — 存储表上索引（mytable.MYI）
灵活的 AUTO_INCREMENT 字段处理
可被转换为压缩、只读表来节省空间 (这是这种存储引擎的优势)

InnoDB

InnoDB 存储引擎是 MySQL 的缺省引擎。

每个 InnoDB 表在数据库目录中以.frm 格式文件表示
InnoDB 表空间 tablespace 被用于存储表的内容
提供一组用来记录事务性活动的日志文件
用 COMMIT(提交)、SAVEPOINT 及 ROLLBACK(回滚)**支持事务处理 **
提供全 ACID 兼容
**在 MySQL 服务器崩溃后提供自动恢复 **
多版本（MVCC）和行级锁定
支持外键及引用的完整性，包括级联删除和更新

MEMORY

使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，这两个特点使得 MEMORY 存储引擎非常快。

在数据库目录内，每个表均以.frm 格式的文件表示。
表数据及索引被存储在内存中。
表级锁机制。
不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为 HEAP 引擎，速度很快，但不安全，关机之后数据消失，因为数据和索引都是在内存当中。

选择存储引擎

MyISAM 表最适合于大量的数据读而少量数据更新的混合操作。MyISAM 表的另一种适用情形是使用压缩的只读表。
如果查询中包含较多的数据更新操作，应用InnoDB，其行级锁机制和多版本的支持为数据读取和更新的混合操作提供了良好的并发机制。
可使用MEMORY来存储非永久需要的数据，或者是能从基于磁盘的表重新生成的数据。

事务(TCL)

只有DML语句才会有事务这一说，且DML中只有insert,delete,update三个语句与事务有关。只要涉及到增删改，就要考虑数据安全问题。
事务具有四个特性ACID：

原子性(Atomicity)：整个事物的所有操作，必须作为一个单元全部完成(或全部取消)
一致性(Consistency)：在事务开始之前和结束之前，数据库都保持一致状态。
隔离性(Isolation)：一个事物不会影响其他事务的运行。
持久性(Durability)：在事务完成以后，该事务对数据库所做的更改将持久地保存在数据库中，不会被回滚。

一个事务其实就是多条DML语句同时成功，或者同时失败。

提交/回滚事务

提交：commit; 回滚： rollback; SET AUTOCOMMIT：禁用或启用事务的自动提交模式

MySQL是自动执行提交事务的，每执行一条DML语句，则提交一次。
若要关闭自动提交机制，在命令执行前用start transaction;

隔离级别

InnoDB 实现了四个隔离级别，用以控制事务所做的修改，并将修改通告至其它并发的事务：

读未提交（READ UMCOMMITTED）允许一个事务可以看到其他事务未提交的修改。
这种隔离可能会出现Dirty Read现象，这种隔离级别都是理论级别的，大多数数据库都是二档起步。
读已提交（READ COMMITTED）允许一个事务只能看到其他事务已提交的修改，未提交的修改不可见。
这种隔离级别不可重复读取数据，Oracle的默认隔离级别就是这种。
可重复读（REPEATABLE READ）可能会出现幻影读，读取的数据不够真实。
序列化（SERIALIZABLE）最高的隔离级别，把两个事务完全隔离开来，这种隔离级别表示事务排队，不能并发。

查看隔离级别

服务器变量tx_isolation(包括会话级和全局级两个变量)中保存着当前的会话隔离级别。

为查看隔离级别，可访问tx_isolation变量：

查看会话级的当前隔离级别：

mysql>select @@ tx_ioslation;
或者
mysql>select &&session.tx_ioslation;

查看全局级的当前隔离级别：
```
mysql>select @@global.tx_ioslation;
```

设置隔离级别

隔离级别也可以在运行的服务器中动态设置：

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

其中ioslation-level可以是上述四个隔离级别中任意一个。

索引

索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL 不得不首先以第一条记录开始，然后读完整个表直到它找出相关的行。表越大，花费时间越多。对于一个有序字段，可以运用二分查找（Binary Search），这就是为什么性能能得到本质上的提高。MYISAM 和 INNODB 都是用 B+Tree 作为索引结构
（主键，unique 都会默认的添加索引）

创建/删除索引

create index emp_ename_index on emp(ename);//给emp表的ename字段添加索引
drop index emp_ename index on emp;//将emp表上的emp_ename_index索引对象删除

索引失效的情况

使用模糊查询

select * from emo where ename like '%T';//此时索引失效，因此尽量避免这种情况提高效率。

使用or：用or时要求or两边的条件字段都要有索引，才会走索引，否则索引失效。可以用union来优化。
使用复合索引时，没有使用左侧的列查找，索引会失效。

在where中索引列参加了运算：

explain select * from emp where sal+1=800;

在where中索引列使用了函数

explain select * from emp where lower(ename)= 'smith';

视图

视图是一种根据查询（也就是 SELECT 表达式）定义的数据库对象，用于获取想要看到和使用的局部数据。

创建/删除视图对象

create view dept2_view as select * from dept2;
create view emp_dept_view as select e.ename,e.sal,d.dname from emp e join dept d on e.deptno = d.deptno;
----------------------------------------
drop view dept2_view;

我们可以面向视图对象进行增删改查，对视图对象的增删改查，会导致原表被操作。

DBA常用命令

创建用户

CREATE USER username IDENTIFIED BY 'password';
说明:username——你将创建的用户名, password——该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密
码登陆服务器.
例如：
create user p361 identified by '123';
--可以登录但是只可以看见一个库 information_schema

修改视图

alter view v_dept_emp as select ename,dname,sal,hiredate,e.deptno from emp e,dept d where e.deptno = 20;

授权

mysql> grant all privileges on dbname.tbname to 'username'@'login ip' identified by 'password' with grant option;
1) dbname=*表示所有数据库
2) tbname=*表示所有表
3) login ip=%表示任何 ip
4) password 为空，表示不需要密码即可登录
5) with grant option; 表示该用户还可以授权给其他用户
 细粒度授权
首先以 root 用户进入 mysql，然后键入命令：grantselect,insert,update,delete on *.* to p361 @localhost Identified by "123";
如果希望该用户能够在任何机器上登陆 mysql，则将 localhost 改为 "%"。
 粗粒度授权
我们测试用户一般使用该命令授权，
GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123";
注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:
GRANT ALL PRIVILEGES ON *.* TO 'p361'@'%' Identified by "123" WITH GRANT OPTION;
privileges 包括：
1) alter：修改数据库的表
87 / 103
2) create：创建新的数据库或表
3) delete：删除表数据
4) drop：删除数据库/表
5) index：创建/删除索引
6) insert：添加表数据
7) select：查询表数据
8) update：更新表数据
9) all：允许任何操作
10) usage：只允许登录

回收权限

revoke privileges on dbname[.tbname] from username;
revoke all privileges on *.* from p361;
use mysql
select * from user
进入 mysql 库中
修改密码;
update user set password = password('qwe') where user = 'p646';
刷新权限;
flush privileges

导入导出

导出整个数据库：

在 windows 的 dos 命令窗口中执行：mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123

导出指定库下的指定表：

在 windows 的 dos 命令窗口中执行：mysqldump bjpowernode emp> D:\ bjpowernode.sql -uroot –p123

导入：

登录 MYSQL 数据库管理系统之后执行：source D:\ bjpowernode.sql

数据库设计三范式

第一范式

每一行必须唯一，也就是每个表必须有主键，这是数据库设计的最基本要求[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UyukDzrg-1666254867962)(C:\Users\manyan\AppData\Roaming\Typora\typora-user-images\1666253277975.png)]

第二范式

第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YrPyCdBt-1666254867963)(C:\Users\manyan\AppData\Roaming\Typora\typora-user-images\1666253624695.png)] [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MdQaX28Y-1666254867963)(C:\Users\manyan\AppData\Roaming\Typora\typora-user-images\1666253649012.png)]设计为“多对多”，多对多，三张表，关系表两个外键

第三范式

建立在第二范式基础上的，非主键字段不能传递依赖于主键字段。（不要产生传递依赖） [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Pq3DvtPH-1666254867964)(C:\Users\manyan\AppData\Roaming\Typora\typora-user-images\1666254215110.png)]

这是一对多的设计。一对多，两张表，多的表加外键。

一对一设计，有两种设计方案：