mysql学习

MySQL

##MySQL目标

一、为什么要学习数据库
二、数据库的相关概念      
	DBMS、DB、SQL
三、数据库存储数据的特点
四、初始MySQL
	MySQL产品的介绍        
	MySQL产品的安装            ★        
	MySQL服务的启动和停止       ★
	MySQL服务的登录和退出       ★      
	MySQL的常见命令和语法规范      
五、DQL语言的学习   ★              
	基础查询        ★             
	条件查询  	   ★			
	排序查询  	   ★				
	常见函数        ★               
	分组函数        ★              
	分组查询	   ★			
	连接查询	   ★			
	子查询         √                  
	分页查询       ★              
	union联合查询  √			
	
六、DML语言的学习   ★             
	插入语句						
	修改语句						
	删除语句						
七、DDL语言的学习  
	库和表的管理	    √				
	常见数据类型介绍  √          
	常见约束  	     √			
八、TCL语言的学习
	事务和事务处理                 
九、视图的讲解        √
十、变量                      
十一、存储过程和函数   
十二、流程控制结构

##数据库的好处

1.持久化数据到本地
2.可以实现结构化查询，方便管理

##数据库相关概念*

1、DB：数据库，保存一组有组织的数据的容器
2、DBMS：数据库管理系统，又称为数据库软件（产品），用于管理DB中的数据
3、SQL:结构化查询语言，用于和DBMS通信的语言

##数据库存储数据的特点

1、将数据放到表中，表再放到库中
2、一个数据库中可以有多个表，每个表都有一个的名字，用来标识自己。表名具有唯一性。
3、表具有一些特性，这些特性定义了数据在表中如何存储，类似java中 “类”的设计。
4、表由列组成，我们也称为字段。所有表都是由一个或多个列组成的，每一列类似java 中的”属性”
5、表中的数据是按行存储的，每一行类似于java中的“对象”。

##MySQL产品的介绍和安装

MySQL背景

前身属于瑞典的一家公司，MyAQL AB
08年被sun公司收购
09年sun被oracle收购

MySQL的优点

1、开源、免费、成本低
2、性能高、移植性好
3、体积小，便于安装

MySQL安装

c/s架构软件，一般安装服务器
分为企业版和<u>社区版</u>
常用5.5,<u>5.6</u>,5.7,8.0

###MySQL服务的启动和停止

方式一：计算机——右击管理——服务
方式二：通过管理员身份运行cmd
net start 服务名（启动服务）
net stop 服务名（停止服务）

###MySQL服务的登录和退出

方式一：通过mysql自带的客户端
只限于root用户
方式二：通过windows自带的客户端
登录：
mysql 【-h主机名 -P端口号 】-u用户名 -p密码
退出：
exit或ctrl+C

###MySQL的常见命令

1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(

	列名 列类型,
	列名 列类型，
	。。。
);
6.查看表结构
desc 表名;

7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql --V

###MySQL的语法规范

1.不区分大小写,但建议关键字大写，表名、列名小写
2.每条命令最好用分号结尾
3.每条命令根据需要，可以进行缩进 或换行
4.注释
	单行注释：#注释文字
	单行注释：-- 注释文字
	多行注释：/* 注释文字  */

###SQL的语言分类

DQL（Data Query Language）：数据查询语言
	select 
DML(Data Manipulate Language):数据操作语言
	insert 、update、delete
DDL（Data Define Languge）：数据定义语言
	create、drop、alter
TCL（Transaction Control Language）：事务控制语言
	commit、rollback

###SQL的常见命令

show databases； 查看所有的数据库
use 库名； 打开指定 的库
show tables ; 显示库中的所有表
show tables from 库名;显示指定库中的所有表
create table 表名(
	字段名 字段类型,	
	字段名 字段类型
); 创建表

desc 表名; 查看指定表的结构
select * from 表名;显示表中的所有数据

DQL语言

（Database Query Language）数据库表查询操作语言

基础查询

语法：

select 
  查询列表1,
  查询列表2
  ··· 
from
  表名 ;

特点：
- 查询列表可以是：表中的字段、常量值、表达式、函数、 *(代表表中的所有字段)
- 字符型和日期型的常量值必须用单引号引起来，数值型不需要
- 查询的结果是一个虚拟的表格

条件查询

语法：

select 
  查询的字段 
from
  表名 
where 筛选条件 ;

分类：
- 简单条件运算符： > 、< 、= 、>=、<=、<> (不等于) 、<=> (安全等于)
- 逻辑运算符：and、or、not
- 模糊查询：like、between and（闭区间）、in、is null 、is not null (由于=、<>不能判断null值)
  - like一般搭配通配符使用，可以判断字符型或数值型
```
//like
like '_$_%' escape '$';
//between and
between 100 and 200;
//in
in(`A`,`B`);
```
特点：where一定放在from的后面（挨着）
where不支持别名

排序查询

语法：

select 
  查询的字段 
from
  表 
where 筛选条件 
order by 排序列表 asc | desc,
  排序列表 asc | desc ······;

理解：
- ASC：升序（如果不写，默认是升序）
- DESC：降序

特点：order by支持别名

分组查询

语法：

select
  查询的字段，分组函数
from
  表名
[where 筛选条件]
group by 分组的字段(表达式或函数)
[order by 排序的字段或表达式]

分组后筛选语法：

select
  查询的字段，分组函数
from
  表名
group by 分组的字段(表达式或函数)
having 分组后的筛选条件;

特点：
- 可以按单个字段分组。也可以按多个字段分组，字段之间用逗号隔开
- 和分组函数一同查询的字段最好是分组后的字段
- group by、having支持别名

多表连接查询

查询的字段来自于多个表时用到
分类：
- sql92标准：在MySQL中仅支持内连接
- sql99标准：在MySQL中支持内连接 + 外连接（左外连接、右外连接）+ 交叉连接
特点：
- 表支持别名（一般为表起别名，提高阅读性和性能）
- 如果为表起了别名，就不能使用原来的表名去限定
- n个表连接，至少需要n-1个连接条件
- 多个表不分主次，没有顺序要求

语法( sql99 )：

select 查询的字段
from 表1 别名
连接类型 join 表2 别名
on 连接条件 
[where 筛选条件]
[group by 分组的字段]
[having 分组后的筛选条件]
[order by 排序的字段或表达式]

分类：
- 内连接☆：inner(默认值，可省略)
  - 等值连接
  - 非等值连接
  - 自连接
- 外连接：
  - 左外☆：left outer （outer可省略）
  - 右外☆：right outer
  - 全外：full outer
- 交叉连接：cross
理解：
- 等值连接：等值连接的结果 = 多个表的交集
- 左、右外连接：查询结果为主表中的所有记录。如果从表中有和它匹配的，则显示匹配的值，否则显示null
- 全外连接 = 内连接的结果+表1中有但表2没有的+表2中有但表1没有的
- 交叉连接 = 笛卡尔乘积的结果

子查询

含义：
- 一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询
- 在外面的查询语句，称为主查询或外查询
分类：
- 标量子查询（结果集只有一行一列）[或称单行子查询]
- 列子查询（结果集只有一列多行）[或称多行子查询]
- 行子查询（结果集有一行多列）
- 表子查询（结果集一般为多行多列）
特点：
- 子查询都放在小括号内
- 子查询可以放在from后面、select后面、where后面、having后面，exists后面····· 但一般放在条件的右侧
- 子查询优先于主查询执行，主查询使用了子查询的执行结果
- 单行子查询，一般搭配单行操作符使用：> < = <> >= <=
- 多行子查询，一般搭配多行操作符使用：any、all、some、in、not in

操作符	含义
in \| not in	等于列表中的任意一个
any \| some	和子查询返回的某一个值比较
all	和子查询返回的所有值比较

非法使用子查询的情况：
- 子查询的结果类型于所需不匹配
- 子查询的结果为空

分页查询

语法：

select 字段|表达式
from 表名
[where 条件]
[group by 分组字段]
[having 条件]
[order by 排序的字段]
limit 起始的条目索引 , 条目数;

特点：
- 起始条目索引从0开始(可省略，默认为0)
- limit子句放在查询语句的最后
- 公式(page = 要显示的页数，sizePerPage = 每页显示条目数)：
```
limit (page - 1) * sizePerPage,sizePerPage
```

联合查询

将多条查询语句的结果合并成一个结果

语法：

select 查询列表 from 表名 where 条件
union [all]
select 查询列表 from 表名 where 条件
union [all]
···

特点：
- union去重，union all不去重
- 多条查询语句的查询的列的类型几乎相同
- 多条查询语句的查询的列数必须是一致的

杂知识点及常见函数

单行函数

	功能	说明
字符函数
length	获取字节个数	utf8中一个汉字是3个字节
concat	拼接字符	若用引号，建议用单引号
upper \| lower	大小写转换
substr	截取子串	SQL中索引从1开始
instr	返回子串第一次出现的索引	若找不到，则返回0
trim	去前后指定的空格和字符	trim(字符 from 字符)
lpad \| rpad	左 \| 右填充
replace	替换
数学函数
round	四舍五入
ceil \| floor	向上 \| 下取整
mod	取模	结果符号和被除数正负同
truncate	截断
日期函数
now	当前系统日期+时间
curdate	当前系统日期
curtime	当前系统时间
year\|month ··	获取日期指定部分
str_to_date	将字符转换成日期	如(‘1998-3-2’,‘%Y-%c-%d’)
date_format	将日期转换成字符
其他函数
version	版本号
database	当前数据库
user	当前连接用户
流程控制函数
if	处理双分支	类似于Java中三元运算符
case	处理多分支	when条件then语句 …end;

日期格式符：

格式符	功能
%Y	四位的年份
%y	两位的年份
%m	月份（01，02）
%c	月份（1，2）
%d	日（01，02）
%H	小时（24小时制）
%h	小时（12小时制）
%i	分钟（00，01）
%s	秒（00，01）

分组函数

功能：做统计使用，又称统计函数、聚合函数、组函数

	功能	说明
sum	求和
max	最大值
min	最小值
avg	平均值
count	计数

特点：
- 以上五个分组函数都忽略null值，除了count(*)
- sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型
- 都可以搭配distinct使用，用于统计去重后的结果
- count的参数可以支持：字段、*、常量值（一般放1）

起别名(as)

格式：
```
select 查询列表 as 别名;
```
特定：
- as 可省略
- 别名若需要引号，建议使用双引号 " "

去重(distinct)

格式：
```
select distinct 查询列表;
```

+号

使用情况：
- 两个操作数都为数值型，做加法运算
- 若存在字符型，试图将字符型数值转换成数值型。若转换失败，则将字符型数值转换成0
- 只要有以个操作数为null，则结果肯定为null

=号

不能判断null值
<=> 安全等于号既可以判断普通的数组，也可以判断null值

通配符

分类：
- % (任意多个字符，包含0个字符)
- _ (任意单个字符)
想让通配符作为字符出现：
- 方式一（推荐使用）：
```
like '_$_%' escape '$'; // $ 可替换为任意字符
```
- 方式二：
```
like '_\_%';
```

DML语言

（Database Manipulation Language）数据库表数据操作语言

插入

语法：

#方式1
insert into 表名(列名 ，···)
values(值1 ，,...)，(值2 ，,...);
#方式2
insert into 表名
set 列名1=值，列名2=值···

特点
- 可以为空的字段，可以不用插入值，或用null填充
- 字段可以省略，但默认所有字段，并且顺序和表中的存储顺序一致
- 方式1可以插入多行，支持子查询

修改

语法：

#单表
update 表名              ①
set 字段=新值,字段=新值    ③
[where 条件]             ②
#多表
update 表1 别名
连接类型 join 表2 别名
on 连接条件
set 字段=新值,字段=新值    
[where 条件]

删除

语法：
- 方式一（delete语句）：
```
#单表
delete
from 表名 
[where 筛选条件]
#多表
delete 别名1，别名2
from 表1 别名1
连接类型 join 表2 别名2
on 连接条件
where 筛选条件;
```
- 方式二（truncate语句）：
```
truncate table 表名
```
- 两种方式对比：
  - truncate不能加where条件，而delete可以加where条件
  - truncate的效率高一点
  - truncate 删除带自增长的列的表后，如果再插入数据，数据从1开始
  - delete 删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
  - truncate删除不能回滚，delete删除可以回滚

DDL语言

（Database Definition Language）数据库表结构操作语言

库和表的管理

库的管理：

#创建库
create database (if not exists) 库名
#删除库
drop database (if exists) 库名

表的管理：

创建表：

create table (if not exists) 表名(
    列名1 列的类型1 [(长度) 约束],
    列名2 列的类型2 [(长度) 约束],
    ·······
);

修改表：
```
alter table 表名 操作名 column 列名 [列类型 约束]
```
- 操作名：add | modify | drop | change | rename [to]
  - add (添加字段)
  - modify (修改字段类型和列级约束)
  - drop (删除字段)
  - change (修改字段名) alter table 表名 change column 旧列名新列名新类型 (类型不能省)
  - rename [to] (修改表名)
删除表：
```
drop table (if exists) 表名;
```

复制表：

#仅复制结构
create table 表名1 like 表名2;
#复制表的结构+数据
create table 表名1
select * from 表名2
[where 条件]

补充操作：

创建mysql数据库用户

create user tom identified by '新密码';

授予权限

#授予通过网络方式登录的tom用户，对所有库所有表的全部权限，密码设为abc123.
grant all privileges on *.* to tom@'%'  identified by 'abc123'; 

 #给tom用户使用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
grant select,insert,delete,update on atguigudb.* to tom@localhost identified by 'abc123';

常见类型

数值型

整形

分类：tinyint(1)、smallint(2)、mediumint(3)、int / integer(4)、bigint(8) (括号内为所占字节数)
特点：
- 如果不特殊设置，则默认是有符号（+ -）
- 在关键字后添加unsigned设置为无符号
- 如果插入的数值超出了整形的范围，则会报out of range异常，并且插入临界值
- 如果不设置长度，则会有默认值。
- 长度代表了显示的最大宽度，如果不够，搭配zerofill使用会用0在左边填充（使用了zerofill，则默认为无符号）

小数

分类：
- 浮点型：
  - float(M , D)
  - double(M , D)
- 定点型：
  - dec(M , D)
  - decimal(M , D)
特点：
- M：整数位数+小数位数
- D：小数位数
- 如果超过范围，则同样插入临界值
- M和D都可以省略。如果是decimal，则M默认为10，D默认为0。如果是float和double，则会根据插入的数值的精度来决定精度
- 定点型的精确度较高，若对精确度有高要求可选用

字符型

分类：
- 较短的文本(M代表最大的字符数)：
  - char(M)：[M可以省略，默认为1。固定长度的字符。比较耗费空间。效率高]
  - varchar(M)：[M不可以省略。可变长度的字符。比较节省空间。效率低]
  - binary和varbinary（用于保存二进制）
  - enum (用于保存枚举) 、set(用于保存集合)
- 较长的文本：text、blob(较大的二进制)

日期型

分类：
- data(日期)、time(时间)、year(年)
- datetime(日期+时间) [8个字节。范围1000-9999。不受时区等影响]
- timestamp(日期+时间) [4个字节。范围1970-2038。受时区等影响]

常见约束

一种限制，用于限制表中的数据。为了保证表中数据的的准确和可靠
分类(六大约束)：
- not null ：非空，用于保证该字段的值不能为空
- default：默认，用于保证该字段有默认值
- primary key：主键，用于保证该字段的值具有唯一性，且非空
- unique：唯一，用于保证该字段的值具有唯一性，但可为空
- foreign key：外键，用于保证该字段的值必须来自主表关联列的值(在从表添加外键约束，用于引用主表中某列的值，且主表的关联列必须是一个key[一般是主键或唯一])
- check(MySQL不支持)：检查
添加分类：
- 列级约束：六大约束语法上都支持（可加多个，用空格隔开），但外键约束没有效果
- 表级约束：除了非空、默认，其他的都支持
标识列：
- 可以不用手动的插入值，系统提供默认的序列值，又称为自增长列 ( auto_increment )
- 特点：
  - 一个表中至多有一个标识列
  - 标识符要求是一个key(主键、唯一键)
  - 标识符的类型只能是数值型
  - 标识符可以通过set auto_increment_increment = number 设置步长。可以通过手动插入值，设置起始值
主键和唯一对比：

保证唯一性允许为空允许存在的个数允许组合

主键 √ × 至多有一个 √ (不推荐使用)

唯一 √ √ 可以存在多个 √ (不推荐使用)

	保证唯一性	允许为空	允许存在的个数	允许组合
主键	√	×	至多有一个	√ (不推荐使用)
唯一	√	√	可以存在多个	√ (不推荐使用)

创建表时添加约束，通用写法：

create table if not exists stuinfo(
	id int primary key,
    stuname varchar(20) not null,
    age int default 18,
    seat int unique,
    majorid int,
    #表级约束
    [constraing fk_stuinfo_major] foreign key(majorid) references major(id)
);

修改表时添加约束，语法：

#列级约束
alter table 表名 modify column 字段名 字段类型 新约束;
#表级约束
alter table 表名 add [constraint 约束名] 约束类型(字段名) [外键的引用];

修改表时删除约束，语法：

#列级约束
alter table 表名 modify column 字段名 字段类型 ;
#表级约束
alter table 表名 drop 约束类型(唯一键用的是index) [列名];

TCL语言

（Transaction Control language）事务控制语言

数据库事务

事务的分类：
- 隐式事务：事务没有明显的开启和结束的标记
- 显式事务：事务具有明显的开启和结束的标记（前提：必须先设置自动提交功能为禁用）
相关步骤：
1. 开启事务
2. 编写事务的一组逻辑操作单元（insert、delete、update、select）
3. 提交事务或回滚事务
```
#开启事务
set autocommit=0;
start transaction;
#编写事务的一组逻辑操作单元

·······

#结束事务(二选一)
commit;(提交事务)
rollback;(回滚事务)
```
savepoint 节点名(设置保存点,只能搭配rollabck to 使用)

commit to 节点
rollback to 节点

其他

视图

虚拟表，和普通表一样使用
视图的创建语法：
```
create view 视图名 as 查询语句;
```

视图的查看

#方式一：
desc 视图名;
#方式二：
show create view 视图名;

视图的修改：

方式一：

#若存在修改，否则创建
create or replace view 视图名 as 查询语句;

方式二：
```
alter view 视图名 as 查询语句;
```

视图数据的删除：
```
delete from 视图名;
```
视图的删除：
```
drop view 视图名1,视图名2···;
```
以下视图不能更新：
- 包含以下关键字的sql语句：分组函数、distinct、group by、join、having、union或者union all
- 常量视图
- select中包含子查询
- from一个不能更新的视图
- where子句的子查询引用了from子句中的表

变量

分类：
- 系统变量
  - 全局变量 (作用域：将为所有的全局变量赋初始值，针对所有的会话有效，但不能跨重启服务器)
  - 会话变量 (作用域：仅针对当前会话(连接) 有效)
- 自定义变量
  - 用户变量 (作用域：仅针对当前会话(连接) 有效)
  - 局部变量 (作用域：仅在定义它的begin、end中有效)

命令：

查看系统变量：

show global | [session] variables [ like 'XXX' ];

查看指定的某个系统变量的值

select @@global | [session].系统变量名

为某个系统变量赋值

#方式一
set global | [session] 系统变量名=值;
#方式二
set @@global | [session].系统变量名=值;

注意：
全局级别用global。会话级别用session。如果不写，则默认session

自定义变量：

使用步骤：

声明并初始化（局部变量可以不初始化）
赋值

用户变量(放在会话中的任何地方)

#声明并初始化
set @用户变量名=值
set @用户变量名:=值
select @用户变量名:=值
#赋值
select 字段 into @用户变量名 from 表名;
#查看用户变量的值
select @用户变量名;

局部变量(只能在begin end中，且为第一句话)

#声明
declare 变量名 类型 [default 值];
#赋值
set 局部变量名=值
set 局部变量名:=值
select @局部变量名:=值
select 字段 into 局部变量名 from 表名;
#查看用户变量的值
select 局部变量名;

存储过程

一组预先编译的SQL语句的集合
分类(in、out、inout都可以在一个存储过程中带多个)：
- 无返回无参
- 仅仅带in类型，无返回有参
- 仅仅带out类型，有返回无参
- 既带in又带out，有返回有参
- 带inout，有返回有参
创建存储过程，语法：
```
create procedure 存储过程名(参数列表)
begin
	存储过程体(如果仅有一条SQL语句，可以省略 begin end )
end 结束标记
```
- 参数列表包含：参数模式(in|out|inout)、参数名、参数类型
  - in：该参数只能作为输入，需要调用方传入值
  - out：该参数只能作为输出，返回值
  - inout：该参数既可以作为输入也可以作为输入，需要传递值，又可以返回值
- 需要用 delimiter 新的结束标记 设置新的结束标记

调用存储过程，语法：

call 存储过程名(实参列表) 结束标记

删除存储过程，语法：
```
drop procedure 存储过程名;
```
查看存储过程，语法：
```
show create procedure 存储过程名;
```

函数

特点：
- 有且仅有一个返回

创建函数，语法：

create function 函数名(参数列表) returns 返回类型
begin
	函数体(如果仅有一条SQL语句，可以省略 begin end )
	return 值;
end 结束标记

参数列表：参数名、参数类型

调用函数，语法：

select 函数名(参数列表) 结束标记

查看函数，语法：
```
show create function 函数名;
```
删除函数，语法：
```
drop function 函数名;
```

流程控制结构

分支结构

if 函数

语法：
```
if(条件，值1，值2);
```

case结构

情况1 (类似于switch语句，一般用于实现等值判断)

case 要判断的字段或表达式
when 常量1 then 返回的值1或语句1;(若是值不用有分号，语句必须有分号)
when 常量2 then 返回的值2或语句2;
···
else 返回的值或语句; (可以无else)
end [case] （如果是放在 begin end中需要加上case，如果放在select后面不需要）

情况2 (类似于多重IF语句，一般用于实现区间判断)

case
when 条件1 then 返回的值1或语句1;
when 条件2 then 返回的值2或语句2;
···
else 返回的值或语句;
end [case]

特点：
- 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方
- 可以作为独立的语句使用，只能放在begin end中

if elseif 结构

语法：

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...
else 语句n;
end if;

特点：
- 只能用在begin end中

循环结构

分类：while、loop、repeat
循环控制：
- iterate：类似于continue 结束本次循环继续下一次
- leave：类似于break 结束当前所在的循环

语法：

#先判断后执行
[标签:] while 循环条件
do
	循环体
end while [标签];
#先执行后判断
[标签:]repeat
	循环体
until 结束循环的条件
end repeat [标签];
#没有条件的死循环
[标签:] loop
	循环体
end loop [标签];

特点：
- 只能用在begin end中

posted @ 2021-11-16 18:12 真正的路飞阅读(21) 评论(0) 编辑收藏举报来源

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

相关博文：

· Linux学习

· 从0开始学习Spring框架

· mysql-基础

· MySQL

· MySQL基础

公告

昵称：真正的路飞
园龄： 2年10个月
粉丝： 5
关注： 1

+加关注

2025年3月

日

一

二

三

四

五

六