目录
- 一、mysql概述
- 1.mysql简述
- 2.mysql的启动和关闭
- 3.mysql使用
- 4.mysql的安装
- 二、mysql语言的组成
- 1.DCL(数据控制语言,操作人的)
- 2.DDL(数据定义语言,操作库的)
- 库表管理
- 约束语言
- 3.DML(数据操作语言,操作表的)
- 增删改
- 4.DQL(数据查询语言,操作数据的)
- 执行顺序
- 基础查询
- 条件查询
- 排序查询
- 函数
- 分组查询
- 连接查询
- 子查询
- 分页查询
- 5.TCL(事务处理语言,操作事务的)
- 1.事务
- 2.并发事务
- 三、mysql优化
- 1.索引
- 2.视图
- 3.备份
- 四、mysql设计范式
一、mysql概述
1.mysql简述
mysql是常用的关系型数据库,是一种数据库管理系统(DBMS),当然还有noSQL类型的非关系型数据库,具体区别自行百度。在同类型的数据库当中mysql是开源且免费的,所以用途较广。
那我们如何理解数据库呢?
其实就是存放数据的仓库,涉及到了对用户的操作(DCL)库的操作(DDL)库中数据的操作(DML)数据查询和使用(DQL)特殊数据的处理(TPL)这一完整的逻辑,和相应的增删改查(CRUD)操作。
mysql是基于客户机-服务器的数据库,可以理解为,需要使用数据的一方软件称为客户机,就像软件的使用者,使用mysql语句对数据进行操作的一方被称为服务器,类似开发者。因此,安装了mysql的计算机被称为数据库服务器。数据库是一个系统软件,它提供的是一种操作数据的服务,有DOS命令行操作方式和图形化软件(navicat、SQLyog)操作两种方式,使用起来很方便。
2.mysql的启动和关闭
上面提到了mysql提供的是一种服务,因此下载完成后是什么都看不见的,只有开启之后才能进行操作。
- 手动开启
1、打开cmd输入services.msc打开服务管理器,找到MySQL - 2、此时会发现启动类型处是自动类型,表示打开电脑的时候就会自动开启mysql服务,我们当然是不需要的,右击–>点击属性–>设置为手动,我们也可以看到,右击后显示启动和关闭这就是第一种开启方式。
- 程序员开启
1、打开cmd右击点击以管理员身份打开,输入net start mysql开启,输入net stop mysql关闭。
3.mysql使用
- DOS命令行
1、一定要在mysql打开的状态下使用,密码就是安装完mysql之后设置的密码,之后就可以快乐的敲代码了。
2、输入exit退出 - navicat
1、将连接名和密码填写完成就好(mysql一定要是开着的)
- SQLyog
1、mysql连接状态下进入就好
4.mysql的安装
二、mysql语言的组成
1.DCL(数据控制语言,操作人的)
管理用户:
1.添加
create user ‘用户名’@‘主机名’ identitied by ‘密码’;
2.删除
drop user ‘用户名’;
3.查询
切换到 mysql数据库
use mysql
查询user表
select * from user
4.修改密码
update user set password =password(‘新密码’) where user=“用户名”;
set password for ‘用户名’@‘主机名’=password(‘新密码’);
2.DDL(数据定义语言,操作库的)
库表管理
- 操作数据库(CRUD)
1、创建create:
Create database + 库名;
Create database if not exits + 库名;
Create database + 库名 character set gbk;//设置编码规则
Create database if not exits + 库名 character set gbk;
- 查询resrieve:
Show databases;
Show create database + 库名;--显示数据库详细内容
- 修改update:
Alter database + 库名 character set utf8;//修改格式
- 删除delete:
Drop darabase + 库名
- 使用数据库:
Select database()// 查询当前数据库
Use 库名
- 操作表
1、创建create:
语法:create table 表名(列名 数据类型1,列名 数据类型2);
数据类型:
Int、double、date、timestamp、varchar
复制操作:create table 表2 like 表1
- 查询resrieve:
Show tables
Desc 表名//表的结构
- 修改update:
1.添加一列
Alter table 表名 add column 列名 类型 【first|after 字段名】;
2.删除列
Alter table 表名 drop column 列名;
3.修改表的字符集
Show create table 表名
Alter table 表名 character set utf8;
4.修改表名
Alter table 表名 rename 【to】 新表名;
5.修改列名称:
Alter table 表名 change column 旧列名 新列名 类型;
6.修改列类型:
Alter table 表名 modify column 列名 改的数据类型
7.修改列的约束:
Alter table 表名 modify column 列名 新约束
- 删除delete:
drop table if exits book;
show tables;
- 复制表
1.复制表的结构
create table 表2 like 表2
2.复制表中的数据
create table 表2 select * from 表1;//全部数据
create table 表1 select id from 表2 where 条件;//部分数据
约束语言
- 常见约束
1.not null;
非空,该字段不能为空值。
2.unique
该字段的值不能重复。
3.primary key
主键,该字段不可重复且非空==unique+not null
4.foreign key
外键,1、用于限制两个表的关系,从表的字段值引用了主表的某些字段值。
2、外键的列和主表的列引用类型要求一致,意义相同,名称无要求。
3、主表中被引用的得是一个主键(要不然为什么叫外键)。
4、插入数据先插主表,删除数据先删从表。
5.unique和primary的辨析:
区别:主键不能为空,但unique可以。
相同:都具有唯一性,支持组合键,但是不支持。
创建约束:
create table 表名(
列名 列类型 not null,#非空
列名 列类型 primary key,#主键
列名 列类型 unique,#唯一
【constraint 约束名】foreign key(从表名) references 主表(被引用列)
)
删除约束:
1、非空
删除非空
alter table 表名 modify column 列名 列类型 ;
2、主键
删除主键
alter table 表名 drop primary key;
3、唯一
删除唯一
alter table 表名 drop index 索引名;
4、外键
删除外键
alter table 表名 drop foreign key 外键名;
修改约束:
1、非空
添加非空
alter table 表名 modify column 字段名 字段类型 not null;
2、主键
添加主键
alter table 表名 add【 constraint 约束名】 primary key(列名);
3、唯一
添加唯一
alter table 表名 add【 constraint 约束名】 unique(列名);
4、外键
添加外键
alter table 表名 add【 constraint 约束名】 foreign key(列名) references 主表(被引用列);
- 自增长列
解释:
1.自增长列不用手动插入值,自动提供序列,默认从1开始。
2.一个表至多有一个自增序列。
3.自增长列只能是数值型。
4.自增长列必须为一个主键。
会出现的问题:
如果删除某些数据之后,序列值仍然下延不会自动补齐,会出现1、3、8、9的序列情况。
创建自增:
create table 表名(列名 列类型 约束语句 auto_increment);
删除自增:
alter table 表名 modify column 列名 列类型 约束语句 auto_increment;
修改自增:
alter table 表名 modify column 列名 列类型 约束语句
3.DML(数据操作语言,操作表的)
增删改
1.添加数据
方法一:Insert into 表名(列名1,列名2)values(值1,值2)
1、列名和值一一对应
2、表名后不列表示对所有赋值
3、values中除了数字,其他需要用引号
4、支持子查询
方法二:Insert into 表名 set 列名=值,列名=值
2.删除数据
Delete from 表名 [where 条件]
如果不加条件,会删除所有数据,可以回滚(rollback)
Truncate table 表名
截断表,只留着表头,不能回滚,删除大量数据的时候效率很高。
3.修改数据
修改单表:
Update 表名 set 列名1=值1,列名2=列2 [where 条件] 【limit 开始条目 条目数】
注意上面写的逗号不能省略,limit 开始条目 条目数 表示选择多少条数据进行修改
修改多表:
update 表1 别名
left|right|inner join 表2 别名
on 连接条件
set 列名=值,列名=值
【where 筛选条件】;
提前介绍一下表连接:
left,right表示左侧写的表,右侧写的表是主表。
inner完全可以省略,on后面表示两个表连接的条件。
4.DQL(数据查询语言,操作数据的)
执行顺序
语句执行顺序
select distinct 查询列表 ⑦distinct用来去重
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选 ④
group by 分组列表 ⑤ 分组一定要写在where后面
having 筛选 ⑥ 筛选写在分组后面
order by排序列表 ⑧
limit 起始条目索引,条目数; ⑨
基础查询
注意:查询到的,看到的是虚拟的,原本数据库可能不长这样。
1.查询列
select 列名 from 表名;
2、查询所有
select * from 表名;
3.查询常量
select 常量值;
常量值如果是字符型或者日期型得用双引号,数字不用。
4.查询函数
select 函数名(实参名);
5.查询表达式
select 表达式;
6.起别名
select 列名 as 别名 from 表名;
select 列名 别名 from 表名;
7.去重
select distinct 列名 from 表名;
8.计算
select 数值+数值;
直接运算
select 字符+数值;
先试图将字符转换成数值,如果转换成功,则继续运算;否则转换成0,再做运算
select null+值;
结果都为null
9.拼接函数
select concat("字符1","字符2");
10.isfull函数
select ifnull(列名,指定的值) from 表名;
如果是null返回指定值,否则返回原值。
11.isnull函数
select isnull(列名) from 表名;
如果为null返回1,否则返回0
条件查询
- 条件运算
select 列名 from 表名 where 筛选条件
> < 大于 小于
>= <= 大于等于 小于等于
<> != 不等于
between and 在什么之间
in 是否在其中
- 逻辑运算
&& and
|| or
! not
- 模糊查询
一、使用like操作符和通配符
1.百分号(%):表示多个字符
select 列名 from 表名 where 待查询的列名 like '%jol%';
表示匹配到字符串中含有jol的字符串。
2.下划线(_):表示匹配一个字符
select 列名 from 表名 where 待查询的列名 like '_jol';
表示只能匹配到包含4个字符的字符串。
二、使用正则表达式
1.基本字符匹配
select 列名 from 表名 where 待查询的列名 regexp '1000';
表示检索出包含1000的所有行
select 列名 from 表名 where 待查询的列名 regexp '.000';
.可以表示任意字符
2.进行or匹配
select 列名 from 表名 where 待查询的列名 regexp '1000|2000';
匹配含有1000或者2000的行
3.匹配几个字符之一
select 列名 from 表名 where 待查询的列名 regexp '[123]000';
匹配含有1000、2000或者3000的行,但是可能造成错误查找,
因为mysql会认为是1|2|3000,因此可以写他的完全形式[1|2|3]000。
4.匹配范围
select 列名 from 表名 where 待查询的列名 regexp '[1-9]000';
匹配1000-9000
5.匹配特殊字符
select 列名 from 表名 where 待查询的列名 regexp '\\.';
匹配带.字符的字符串。
\\f 换页 \\n 换行 \\r 回车 \\t 制表 \\v 纵向制表
6.匹配字符类
见下图
7.匹配多个实例
select 列名 from 表名 where 待查询的列名 regexp '[[:digit:]]{4}';
表示匹配匹配任意数字连续4个
* 0或多个
+ 1或多个
? 0或1个
{n} 指定n个
{n,} 不少于n个
{n,m} n-m个
8.定位符
select 列名 from 表名 where 待查询的列名 regexp '^[0-9\\.]';
表示匹配以数字和.开头的字符串。
^ 开始
$ 结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
匹配字符类表
like和regexp的区别 : regexp=like+通配符
排序查询
select 查询列表 from 表where 筛选条件order by 排序列表 【asc|desc】
asc表示升序,一般是默认的可以不写,desc降序.
函数
一.字符函数
1.concat连接函数
select concat(last_name,'_',first_name) 姓名 from 表名;
2.substr截取函数
select substr("123456789",2,5);
输出2,3,4,5.默认索引为1.
3.upper,lower变大写,变小写
select upper("tom");
4.replace替换函数
SELECT REPLACE('一个董憨憨','一个','一只');
参数1:原字符串
参数2:要被替换的字符串
参数3:替换字符串
5.length获取字节长
select length("123456")
6.trim去除前后空格
select trim(' 一个董憨憨 ')
select trim('aa' from "aaaaaaaa一个董憨憨aaaaaaaa");
7.lpad左填充rpda右填充
select lpad("一个董憨憨",4,'a');
8.instr返回第一次出现的索引数
select instr("aaaa一个董憨憨aaaa","一个");
二.数学函数
1.ceil向上取整 floor向下取整
select ceil(1.09);
select floor(1.99);
2.round四舍五入
select round(1,458,2);--保留两位
3.truncate截断
select truncate(1.458,2);
4.mod取整
select mod(10,3);
select 10%3;
5.rand函数,0-1之间
三.日期函数
1.返回当前日期+时间
select now();
2.返回年
select year(now());
select year('2021-4-12');
select year(日期类型的列名) from 表名;
3.返回月
select month(now());
select MONTHNAME(now());
4.返回日
select day(now());
5.data_format将日期转换成字符
select date_format(now(),'%y年%m月%d日');
6.curdate返回当前日期
select curdate();
7.str_to_date将字符转换成日期
select str_to_date('2021-4-12','%y-%c-%d');
8.curtime返回当前时间
select curtime();
9.datediff返回两个日期相差天数
select datediff('2021-4-12','2021-4-13');
四.流程控制函数
1.select if(条件表达式,表达式1,表达式2);
如果表达式成立返回表达式1,否则返回表达式2.
2.case类型
select salary 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;
分组函数
select SUM(列名) from 表名;
select AVG(列名) from 表名;
select MIN(列名) from 表名;
select MAX(列名) from 表名;
select COUNT(列名) from 表名;
1.语法
select max(字段) from 表名;
2.支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型
3.以上分组函数都忽略null
4.都可以搭配distinct使用,实现去重的统计
select sum(distinct 字段) from 表;
5.count函数
count(字段):统计该字段非空值的个数
count(*):统计结果集的行数
count(1):统计结果集的行数
分组查询
select 分组函数,分组后的字段
from 表
【where 筛选条件(分组前筛选)】
group by 分组的字段
【having 分组后的筛选】
【order by 排序列表】
注意:在where后面不能加上分组函数
例如:select avg(scores) from student where avg(scores)>80;
是错误的
连接查询
1、内连接:等值连接、非等值连接、
2、自连接
3、外连接:左外连接、右外连接、全外连接
4、交叉连接
内连接与外连接的区别:内连接连接的两个表都要满足条件,外连接还要包括不满足条件的部分
1.笛卡尔积(自然连接):就是每一个元组都匹配.
select 列1,列2 from 表1,表2;
2.内连接
select 查询列表
from 表1 别名
【inner】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
3.外连接
select 查询列表
from 表1 别名
left|right|full【outer】 join 表2 别名 on 连接条件
where 筛选条件
group by 分组列表
having 分组后的筛选
order by 排序列表
limit 子句;
4.交叉连接(类似笛卡尔积)
select 查询列表
from 表1 别名
cross join 表2 别名;
子查询
子查询:嵌套在其他语句内部的select语句称为子查询或内查询, 外面的语句可以是insert、update、delete、select等,
一般select作为外面语句较多, 外面如果为select语句,则此语句称为外查询或主查询select 后面:仅仅支持标量子查询
from后面:支持表子查询
where和having后面:标量子查询、列子查询、行子查询
exists后面:标量子查询、列子查询、行子查询、表子查询标量子查询(单行子查询):结果集为一行一列
列子查询(多行子查询):结果集为多行一列
行子查询:结果集为多行多列
表子查询:结果集为多行多列
不清楚可以移步:各个查询的解析
分页查询
当与java语句进行交互的时候,我们可能会要求每次只显示一部分内容,就像网页一样,一页只显示一部分这时候就可以用到limit语句了。
limit 开始页数的索引 需要页数
select 查询列表
from 表
limit (page-1)*size,size;
5.TCL(事务处理语言,操作事务的)
1.事务
什么是事务?
比如我们去银行取钱给另一个人的银行账户汇钱,那么取钱的操作和汇钱的操作必须同时成功或者同时失败,要不然就会出乱子。事务的特点:(acid)
原子性:一个事务是不可再分割的整体,要么都执行要么都不执行。
一致性:一个事务可以使数据从一个一致状态切换到另外一个一致的状态。
隔离性:一个事务不受其他事务的干扰,多个事务互相隔离的。
持久性:一个事务一旦提交了,则永久的持久化到本地。
1.开启事务 set autocommit=0; start transaction;#可以省略
2.编写一组逻辑sql语句 注意:sql语句支持的是insert、update、delete
3.结束事务 提交:commit; 回滚:rollback;
4.回滚(rollback)
设置回滚点:savepoint 回滚点名;
回滚到指定的地方:rollback to 回滚点名;
解释:在事务中某一项sql语句不想执行了,就可以选择回滚,就是后退的意思。
2.并发事务
想一想,如果两个人同时访问同一个数据库中的某个数据,其中一个人A修改它,另一个人B会看到什么呢?这就是并发事务的问题。mysql用隔离等级来划分另一个人B看到的程度。
隔离等级:
第一级别:读未提交(read uncommitted),B能读到A还未提交的数据,
安全等级最低,相当于没有分隔。会造成脏读现象。(没有)
第二级别:读已提交(read committed),B能读到A已经提交的数据,但是会造成
B用户想要读取A没修改之前的数据就没办法实现,称为不可重复读现象。(玻璃)
第三级别:可重复读(repeatable read),B能重复读了,但是此时数据库内的
数据已经被修改了,B看到的是幻象,已经不是真实的数据了。(墙)
第四级别:串行化(serializable),A访问和修改的时候,不允许B再访问了,解
决了一切问题。(城墙)
设置隔离等级:
在默认状态下,事务是被自动提交的,就是执行一句提交一句sql
命令。因此需要手动设置等级。
set global transaction isolation level read uncommitted;
设置第一等级。
1.事务使用步骤:
set autocommit=0;
start transaction;
update 表名 set 列名=值 where 限制条件;
commit;
2.savepoint的使用:
set autocommit=0;
start transaction;
update 表名 set 列名=值 where 限制条件;
savepoint 保存点名;
update 表名 set 列名=值 where 限制条件;
rollback to 保存点名;
commit;
三、mysql优化
1.索引
索引可以大大降低查找的时间,注意,主键和unique约束字段自动添加索引,当使用含有索引的字段名进行查找的时候,可以优化。
1.查看扫描状况:可以看到扫描了多少条数据。
explain select * from 表名;
2.创建索引:
create index 索引名称 on 表名(字段名);
3.删除索引
drop index 索引名称 on 表名;
2.视图
当部分数据不想让别人看到的时候就可以使用视图,就是对同一个数据库不同的看待方式,名称、数据量等可能会有变化,如果对视图进行修改的话,对数据库本身也会修改,提供了很好的接口。
1.创建视图:
create view 视图名 as 查询语句;
2.修改视图:
create or replace view 视图名 as 查询语句;
alter view 视图名 as 查询语句;
3.删除视图:
drop view 视图1,视图2;
4.查看视图:
desc 视图名;
show create view 视图名;
5.插入数据:
insert into 视图名 values();
6.修改数据:
update 视图名 set 字段名=值 where 限制条件;
7.删除数据:
delete from 视图名 where 限制条件;
8.查看视图:
select * from 视图名;
注意:视图一般用于查询的,而不是更新的,所以具备以下特点的视图都不允许更新.
1.包含分组函数、group by、distinct、having、union、
2.join
3.常量视图
4.where后的子查询用到了from中的表
5.用到了不可更新的视图
3.备份
1.导出数据(备份数据):
mysqldump 库名 > 路径名 -uroot -p 密码;
2.导入数据
create database 库名;
use 新创建的库;
source 备份的路径名;
四、mysql设计范式
mysql设计范式:设计表的依据,使得表中不会产生数据的冗余。
1.第一范式:任何一张表都有主键,并且每个字段不可再分。2.第二范式:非主键字段完全依赖主键,不能产生部份依赖
口诀:多对多,三张表,关系表两个外键(如下图)
3.第三范式:非主键字段直接依赖主键,不能产生传递依赖
口诀:一对多,两张表,多的表加外键。