学习自(B站):
老杜带你学_mysql入门基础(mysql基础视频+数据库实战)
文章目录
一.数据库了解
数据库: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)
注意
数据库设计三范式是理论上的
最终目的都是为了满足客户的需求,有的时候会拿冗余换速度
表和表连接越多,效率越低