mysql学习

最新推荐文章于 2024-08-23 09:39:07 发布

rz-kapok

最新推荐文章于 2024-08-23 09:39:07 发布

阅读量559

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/qq_46622887/article/details/124397275

版权

学习自（B站）：
老杜带你学_mysql入门基础（mysql基础视频+数据库实战)

文章目录

一.数据库了解
二.安装MySQL
- zip的
- msi的
三.卸载mysql
四.命令学习（select）
- 表的理解
- SQL语句分类
- MySQL常用命令
五、简单查询
六、条件查询
七、排序
八、数据处理函数
九、分组函数（多行处理函数）
十、分组查询（非常重要）
- 去除重复记录
十一、连接查询
- 内连接之等值连接
- 非等值连接
- 自连接
- 外连接
- 三张表，四张表连接
十二、子查询
- where子查询
- from子查询
- select 后面出现的子查询（了解）
- union合并查询结果集
- limit
- 分页
十三、表
- 表的创建
- mysql数据类型
- 删除表
- 插入数据insert
- date和datetime
- 修改update
- 删除数据
- insert 插入多条记录
- 快速创建表
- 删除表中的数据
- 约束
- 主键值建议类型
- 自然主键和业务主键
- 外键约束（foreign key）
十四、存储引擎
- 给表添加、指定存储引擎
- 查看mysql支持的存储引擎
- 常见存储引擎
十五、事务
- 怎么提交事务，回滚事务
- 事物的特性
- 事务的隔离性
十六、索引（目录）
十七、视图
- 增删改查，原表变化
十八、DBA命令
十九、数据库设计的三范式
- 第一范式
- 第二范式
- 第三范式
- 总结表的设计
- 注意

一.数据库了解

数据库：DataBase（DB）
数据库管理系统： DataBaseManagement（DBMS）
常见数据库管理系统：MySQL，Oracle，MS sqlServer，DB2 等

DBMS–执行–>SQL语句–操作–>DB

二.安装MySQL

下载网址
https://dev.mysql.com/downloads/

选择 MySQL Community Server
在这里插入图片描述

zip的

下载第一个即可
在这里插入图片描述

详细参考：
2021MySql-8.0.26安装详细教程（保姆级）

注意：my.ini配置文件里的mysql安装目录和mysql数据库的数据存放目录是自己的，要注意更改。

msi的

在这里插入图片描述

详细参考：
MySQL 8.0.27.1版本安装(mysql-installer-community-8.0.27.1.msi)

三.卸载mysql

1.双击安装包进行卸载删除
2.删除目录
C：\programData下面的MySQL目录删除
C：\program Files（x86）下面的MySQl目录删除

四.命令学习（select）

表的理解

数据库中最基本的单元是表：table
数据库当中是以表格的形式表示数据的,比较直观
任何一张表都有行和列

行（row）	被称为数据、记录
列（column）	被称为字段

SQL语句分类

DQL	数据库查询语言（凡是带select关键字的都是查询语句）select…
DML	数据库操作语言（凡是对表当中的数据进行增删改的都是）增insert；删delete；改update
DDL	数据定义语言，主要操作表的结构create 新建；drop删除；alter修改；
TCL	事务控制语言 commit 事务提交；rollback事务回滚
DCL	数据控制语言 grant授权；revoke撤销权限

MySQL常用命令

不见分号不执行

退出mysql

exit

查看有哪些数据库

mysql> show databases;

使用数据库

use test;
表示正在使用一个名字叫做test的数据库

创建数据库

create database bjpowernode;

查看数据库下有哪些表

show tables;

导入

source 路径信息

导入数据(注意：路径中不要有中文)

source D:\course\mysql\document\bjpowernode.sql

查看表中的数据

select * from 表名;

不看表数据，只看表结构

desc 表名;

在这里插入图片描述
查看mysql数据库的版本号

select version();

查看当前使用哪个数据库

select database();

终止一条命令的输入

\c

五、简单查询

1.查询一个字段

select 字段名 from 表名;

2.查询多个字段

select a,b,c from 表名;

3.查询所有字段

1.
select 所有字段 from 表名;
2.
select * from 表名;
第2种方法效率低，可读性差

给查询的列起别名
在这里插入图片描述

select  deptno ，dname as deptname from 表名;

在这里插入图片描述

别名如果有空格，需要加引号(单双都可以）
select deptno,dname 'dept name' from 表名;

注意：双引号在oracle数据库中不能使用

4.计算年薪

select 名字,月薪*12 as '年薪'  from 表名;

六、条件查询

语法格式

select
   ...
from
   ...表名
where      
      条件;

= 等于

select 字段 from 表名 where 字段=条件;

<> 或！= 不等于
<小于
<=小于等于
大于>
大于等于>=
between … and 两个值之间
is null 为null（is not null 不为空）
and 并且
or 或者
in 包含
not not 可以取非，主要用在is或in中

is null
is not null
in
not in

like like 称为模糊查询，支持%或下划线匹配
%匹配任意多个字符
下划线，一个下划线只匹配一个字符

名字 含0的
select name from emp 
where like '%0%';

第二个字母是ade
select name from emp
where like '_a%';

找有特殊含义的
select name from emp
where like '%\_%';
\是转义字符

七、排序

查询所有员工薪资，排序

select
    ename,sal
from
	emp
order by
	sal;	  
	默认是升序

降序

select
    ename,sal
from
	emp
order by
	sal desc;

指定升序

select
    ename,sal
from
	emp
order by
	sal asc;

多个字段
eg：先按照薪资升序，在按照名字升序排序

select 
	ename,sal
from
	emp
order by
	sal asc,ename asc;

根据位置来排
eg：按照第二列来排

select ename sal from emp order by 2;
不建议，列的顺序可能发生改变

找出工资在1250到3000之间的员工信息，并按照降序排列

select * from emp where sal between 1250 and 3000
order by sal desc;

八、数据处理函数

数据处理函数又称单行处理函数
单行处理函数：一个输入对应一个输出
多行：多个输入，一个输出

单行处理函数
lower 转换小写

select lower(ename) from emp;

upper 转换大写

select upper(ename) from emp;

substr 取子串（substr别截取的字符串，起始下标，截取的长度）

select substr(ename ,1,1) as name from emp;
起始下标要从1开始
eg：找第一个字母是a的
select  name  from emp where substr(name,1,1) =a;

concat 进行字符串的拼接

select concat(ename,sal) from emp;

length取长度

select length(ename) as namelength from emp;

trim 去空格

select * from emp where ename =trim('  king');

str_to_date将字符串转换为日期

data_formar 格式化日期
format设置千分位

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

当员工是manager时工资上调10%，当工作岗位是salesman时上调50%
注意：不修改数据库，只是将查询结果显示为工资上调
select
	ename,
	job,
	(case job when 'MENEGER' then sal*1.1 when 'SALESMEN' then sal*1.5 else sal end) as newsal
from 
	emp;

round 四舍五入

保留1位小数
select round(22356.568，1)as result  from emp;
保留两位小数
select round(22356.568，2)as result  from emp;
保留到十位
select round(22356.568，-1)as result  from emp;

rand（）生成随机数

select rand() from emp;
100以内随机数
select round(rand()*100,0) from emp;

ifnull 可以将null转换成一个具体值

ifnull是空处理函数，专门处理空的
在所有数据库中，只要又null参与的数学运算，最终结果就是null
需要避免要使用ifnull

语法：
ifnull(数据,被当做哪个值)
计算每个员工的年薪
补助为空的时候，将补助当做0
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

补充：
select 1000  as num from emp;
会出现原表几个记录，出现几个1000

九、分组函数（多行处理函数）

输入多行，最终输出一行
使用时必须先进行分组，然后才能用；
如果没有分组，整张表默认为一组

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

注意：
1.分组函数自动忽略null
2.count （）和count（具体字段）的区别
count（具体字段）：不为null的元素的个数
count （）：统计表中的总行数
3.分组函数不能够直接使用在where子句中
4.所有分组函数都可以组合起来一起用

十、分组查询（非常重要）

select...from... group by..;

执行顺序
from , where, group by, select, order by

找出每个岗位的工资和

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

注意：
在一条select语句中，如果有group by 语句的话，select后面只能跟：参加分组的字段，以及分组函数。其他的一律不能跟。

找出每个部门，不同工作岗位的最高薪资

select max(sal) from emp group by deptno,job;

使用having可以对分完组的数据进一步过滤

注意：having不能单独使用，需要和group by联合

eg：找出每个部门最高薪资，要求显示最高薪资大于3000的

1.
select deptno,max(sal) from emp group by deptno having max（sal） =3000;
2.(优选）
select deptno,max(sal) from emp where sal>3000 group by deptno;

where 没办法的
eg:显示平均薪资高于2500的

去除重复记录

去重,distinct只能出现在所有字段的最前方

select distinct job from emp;

十一、连接查询

多张表联合查询
分类
1.SQL92：1992年
2.SQL99：1999年
3.内连接
等值，非等值，自连接
4.外连接
左外连接，右外连接
5.全连接（了解）

注意：当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数，是两张表条数的成积。这种现象被称为，笛卡尔积现象

查询每个员工所在部门的名称

select ename,dname from emp,dept
where emp.deptno=dept.deptno;
//表起别名
select e.aname,d.dname
from emp e,dept d
where e.deptno=d.deptno

内连接之等值连接

查询每个员工所在部门名称，显示员工名和部门名

SQL92:
select e.ename,d.dname
from emp e,dept d
where e.deptno =d.deptno

SQL99:
select e.ename,d.dname
from emp e
join  dept d
on e.deptno =d.deptno

非等值连接

找出每个员工的薪资等级，要求显示员工名，薪资，薪资等级

select ename,sal,grade
from empt e
(inner) join salgrade s
on e.sal between s.losal and s.hisal;
inner 可以省略

自连接

将一张表看成两张表

select a.ename as '员工',b.ename as '领导'
from emp a join emp b
on a.mgr =b.empno;
员工的领导编号等于领导的员工编号

外连接

右外连接
select e.ename,d.dname
from emp e right join dept d
on e.deptno =d.deptno
right:表示将join关键字右边的表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表

三张表，四张表连接

语法：
select ...
from a join b on a和b的条件
 join c on a和b的条件
 join d on a和b的条件;

找出每个员工的部门名称以及工资等级，要求显示员工名，部门名，薪资，薪资等级

select e.ename,d.dname,e.sal,s.grade
from emp e join dept d on e.emptno=d.emptno
 join salgrade s on e.sal between s.losal and s.hisal;

十二、子查询

where子查询

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

select ...（select）
from ...（select）
where ...（select）

找出比最低工资高的员工信息

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

from子查询

from后面的子查询，可以将子查询的查询结果当做一张临时表（技巧）

找出每个岗位的平均工资的薪资等级

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

select 后面出现的子查询（了解）

找出每个员工的部门名称，要求显示员工名，部门名？

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

union合并查询结果集

注意：union在进行两个结果集合并的时候，要求两个结果集的列数相同
在Oracle中，还要求数据类型相同

查询工作岗位是manager和salesman的员工

1.select ename,job from emp where job ='manager' or job='salesman';

2.select ename,job from emp where job in('manager'  ,'salesman');

3.select ename,job from emp where job ='manager'
union
select ename,job from emp where job ='salesman';

union的效率要更高一些，对于表连接来说，每连接一次新表，匹配的次数满足笛卡尔积，成倍的翻
union把乘法变为加法

limit

将查询结果集的一部分取出来，通常使用在分页查询当中
limit在order by之后执行

limit startindex,length
startindex是起始下标，length是长度
起始从0开始

按照薪资排序，取出排名在前5名的员工

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

取出薪资排名在3-5名的员工？

select ename,sal from emp
order by sal desc limit 2,3;

分页

每页显示pagesize条记录
第pageno张：limit （pageno-1）*pagesize，pagesize

十三、表

表的创建

语法格式：
	create table 表名(
		字段名1 数据类型,
		字段名2 数据类型,
	);

mysql数据类型

varchar
可变长度的字符串
比较智能，节省空间
会根据实际的数据长度动态分配空间
优点：节省空间
缺点:需要动态分配空间，速度慢

char
定长字符串
不管实际的数据长度是多少
分配固定长度的空间去存储数据
使用不恰当的时候，可能会导致空间的浪费
优点：不需要动态分配空间，速度快
缺点：使用不当可能会导致空间的浪费

varchar和char怎么选择
性别：char
姓名：vachar

int（最长11）
数字中的整数型，等同于java的int

bigint
数字中的长整型，等同于java的long

float
单精度浮点型数据

double
双精度浮点型数据

date
短日期类型

datetime
长日期类型

clob
字符大对象
最多可以存储4G的字符串
超过255个字符都要采用clob字符大对象来存储

blob
二进制大对象
专门用来存储图片，声音，视频等流媒体数据

删除表

drop table 表名;

drop table if exist t_student;

插入数据insert

注意：insert语句但凡是执行成功了，那么必然会多一条记录。
没有指定值的字段，默认是null

insert into t_student(no,name,sex,age,email)
values(1,kapok,she,18,qq);

插入日期

str_to_date:将字符串varchar类型转换为date类型
date _format:将date类型转换为具有一定格式的varchar字符串类型
-%Y-%m-%d:这种情况可以不需要函数

insert into t_student(no,name,sex,age,email,birth)
values(1,kapok,she,18,qq,str_to_date('01-10-1990','%d-%m-%Y));
eg：1990-10-1

日期转其他格式

开始1990-10-1

select date_format(birth,'%Y/%m/%d')as birth from t_user;
 
 结束;1990/10/1

date和datetime

date：年月日
datetime：年月日时分秒

短日期：%Y-%m-%d
长日期：%Y-%m-%d %h:%i:%s
当前日期：now（）

修改update

update user set name='jack',birth='15620-8-9' where id=2

删除数据

delect  from user where name='kapok';

insert 插入多条记录

insert into t_student(no,name,sex,age,email)
values(1,kapok,she,18,qq),
insert into t_student(no,name,sex,age,email)
values(1,kapok,she,18,qq),
insert into t_student(no,name,sex,age,email)
values(1,kapok,she,18,qq);

快速创建表

create table emp2 as select *from emp;

删除表中的数据

删除dept_bak表中的数据
慢

delect from dept_bak;

truncate语句(快，效率高）
删数据，表结构还在

truncate table dept_bak;

约束

保证表中的数据有效
非空约束：not null

create table user{
id int,
name varchar(255) not null//只有列级约束，没有表级约束
};

唯一性约束：unique
可以为null

create table user{
id int,
name varchar(255)  unique
};

name 和email两个字段联合起来具有唯一性

drop table if exists vip;
create table vip{
id int,
name varchar(255),
email varchar(255),
unique(name,email)};//表级约束

unique和 not null 联合

create table vip{
id int,
name varchar(255) not null unique
};
name自动变为主键

主键约束：primary key

create table user{
id int primary key,
name varchar(255)  unique
};

id和name联合起来做主键，叫做联合主键

create table user{
id int ,
name varchar(255)  unique,
primary key(id,name)
};

外键约束：foreign key
检查约束：check

主键值建议类型

int
bigint
char
不建议使用varchar ，主键值一般为数字，是定长的

自然主键和业务主键

自然主键：主键值是一个自然数，和业务没关系
业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值，就是业务主键
自然主键用的比较多，业务主键的缺点，业务一旦发生改变，可能会影响

drop table if exists vip;
create table vip{
	id int primary key auto_increment,
	name varchar(255)
};	
//auto_increment 表示自增，以1开始，以1递增。

外键约束（foreign key）

drop table if exist t_student;
drop table if exist t_class;
create table t_class{
	classno int primary key,
	classname varchar（255）
};
create	table t_student{
	no int primary key auto_increment,
	name varchar(255),
	cno int,
	foreign key(cno) references t_class(classno)
};	
insert into t_class(classno,classname) values (100,'咸阳市乾县九十中高一三班');
insert into t_class(classno,classname) values (101,'咸阳市乾县九十中高一四班');
insert into t_student (name cno)values ('a',100);
insert into t_student (name cno)values ('b',100);
insert into t_student (name cno)values ('c',100);
insert into t_student (name cno)values ('d',100);
insert into t_student (name cno)values ('e',101);
insert into t_student (name cno)values ('f',101);
insert into t_student (name cno)values ('g',101);
insert into t_student (name cno)values ('h',101);

十四、存储引擎

mysql特有术语，其他数据库没有
实际上存储引擎是一个表存储/结构数据的方式
不同的存储引擎，表存储数据的方式不同

给表添加、指定存储引擎

在建表的时候可以在最后的小括号）的右边使用
ENGINE来指定存储引擎
CHARSET来指定这张表的字符编码方式
mysql默认
存储引擎：InnoDB
字符编码方式：utf8

create table product(
	id int primary key,
	name varchar(255)
)ENGINE=InnoDB  default charset=utf8;

查看mysql支持的存储引擎

show engines \G

常见存储引擎

MyISAM：

它管理的表具有以下特征：
使用三个文件表示每个表
格式文件-存储表结构的定义（mytable.frm）
数据文件-存储表行的内容（mytable.MYD）
索引文件-存储表上的索引（mytable.MYI)
索引相当于一本书的目录
…
对于一张表来说，只要是主键，或者加右unique约束的字段都会自动创建索引。
…
可被转换为压缩、只读表来节省空间

InnoDB

默认的存储引擎，同时也是一个重量级的存储引擎
支持事务，支持数据库崩溃后的自动恢复机制
非常安全
特点：
每个InnoDB表在数据库目录中以.frm格式文件表示
InnoDB表空间tablespace被用于存储表的内容
提供一组用来记录事务性活动的日志文件
用commit、savepoint、rollback支持事务处理
提供全acid兼容
在mysql 服务器崩溃后提供自动恢复
多版本（MVCC）和杭级锁定
支持外键及引用的完整性，包括级联删除和更新
最大的特点是支持事务
以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，不能很好的节省存储空间。

MEMORY

数据存储在内存中，且行的长度固定
使存储引擎非常快
特点：
	在数据库目录中，每个表均以.frm格式的文件表示
	表数据及索引被存储在内存中
	表级锁机制
	不能包含text或blob字段
MEMORY存储引擎以前被称为HEAP引擎

优点：查询效率最高，不需要和硬盘交互
缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

十五、事务

一个事务就是一个完整的业务逻辑

insert
delete
update

只有这三个语句和事务有关（数据库的增删改查）

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

事务怎么做到同时成功或失败的呢？

InnoDB存储引擎：提供一组用来记录事务性活动的日志文件
事务开始
- insert
- delete
- update
事务结束

提交事务：全部成功
回滚事务：全部撤销

怎么提交事务，回滚事务

提交：commit
回滚：rollback

事务：transaction
关闭 start transaction

事物的特性

原子性：说明事务是最小的工作单元，不可再分

一致性：所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败。保证数据的一致性

隔离性：A事务和B事务具有一定的隔离性，

持久性：事务最终结束的一个保障，事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上。

事务的隔离性

隔离级别有4个级别
读未提交;read uncommitted 最低

读未提交

读已提交：read committed

读已提交 ，每次不一样

可重复读：repeatable read

提交之后也读不到，都是事务开始时读到的

序列化：serializable 最高

 线程同步（事务同步）
 最真实，效率低

十六、索引（目录）

在mysql中，主键上和unique字段上都会自动添加索引，
添加索引的条件：

数据量庞大
经常出现在where的后面，以条件的形式存在
该字段很少DML操作（增，删，改，查）

索引需要维护，建议用主键和unique进行查询

创建索引

create index emp_ename_index on emp(ename);
给emp表的ename字段添加索引，起名：emp_ename_index

删除索引

drop index emp_ename_index on emp;

怎么查看是否使用索引

explain select* from emp where ename ='KING';

索引失效

select* from emp where ename like '%T';

1.ename模糊查询以%开始，索引失效
因为不知道第一个字符是啥
2.使用or时必须两端的字段都得有索引
3.使用复合索引时，没有使用左侧的列查找
4.在where当中索引列参加了运算
5.在where当中索引列使用了函数

索引是各种数据库进行优化的重要手段，优化的时候优先考虑的因素是索引
…
单一索引：一个字段上添加索引
复合索引：两个或更多的字段上添加索引
主键索引：主键上添加索引
唯一性索引：具有unique约束的字段上添加索引
…
注意：唯一性比较弱的字段上添加索引的用处不大

十七、视图

站在不同的角度看待同一份数据
.
创建视图

create table dept2 as select * fromm dept;

删除视图

drop view dept2_view;

注意：只有DQL语句才能以view的形式创建

增删改查，原表变化

面向视图查询

select * from dept3_view;

面向视图插入

insert into dept2_view (deptno,dname,loc) values(60,'sales','beijing')

查询原表

select * from dept2;

面向视图删除

delete from dept2_view;

作用：
简化开发，视图可用于引用复杂的sql语句

…
视图存储在硬盘上，不会消失

…
CRUD
C：create（增）
R：retrive（查）检索
U：update（改）
D：dalete（删）

十八、DBA命令

新建用户

CREATE USER username IDENTIFIED BY'password'

十九、数据库设计的三范式

第一范式：要求任何一张表必须要有主键，每一个字段原子性不可再分
第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖
第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖

第一范式

最核心，最重要的范式，所有表的设计都需要满足，必须有主键，并且每一个字段都是原子性的不可再分，

第二范式

建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖

在这里插入图片描述
满足第一范式，不满足第二范式
张三依赖 1001 王老师依赖001
需要这样设计

多对多，三张表，关系表两个外键

第三范式

建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖

在这里插入图片描述
班级和学生的关系，是一对多的关系
满足第一范式，有主键

满足第二范式，因为主键不是复合主键，没有产生部分依赖。主键是单一主键。

不满足第三范式，一年一班依赖01，01依赖1001，产生了传递依赖

怎么设计一对多？
在这里插入图片描述
背口诀：
一对多，两张表，多的表加外键

总结表的设计

…

一对多	一对多，两张表，多的表加外键
多对多	多对多，三张表，关系表两个外键
一对一	一对一，外键唯一

在这里插入图片描述
外键加唯一性约束 login_id(fk+unique)

注意

数据库设计三范式是理论上的
最终目的都是为了满足客户的需求，有的时候会拿冗余换速度
表和表连接越多，效率越低

rz-kapok

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql学习

文章目录一.数据库了解二.安装MySQL一.数据库了解数据库：DataBase（DB）数据库管理系统： DataBaseManagement（DBMS）常见数据库管理系统：MySQL，Oracle，MS sqlServer，DB2 等DBMS–执行–>SQL语句–操作–>DB二.安装MySQL下载网址https://dev.mysql.com/downloads/...
复制链接

扫一扫