Mysql第一周总结

数据库相关概念

1. 数据库:

英文单词pataBase，简称DB。按照一定格式存储数据的一些文件的组合.顾名思义:存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。

2. 数据库管理系统：

DataBaseManagement,简称DBMS 。

数据库管理系统是专门用来管理数据库中数据的，数据库管理系统可以对数据库当中的薮据进行增删改查。

常见的教据库管理系统:MysQLoracle、MS sqlserver、DB2、sybase等....

3. SQL:结构化查询语言：

程序员需要学习SQr语句，程序员通过编写SQL语句，然后DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。

SQL是一套标准，程序员主要学习的就是SQL语句，这个SQL在mysql中可以使用，同时在oracle中也可以使用,在DB2中也可以使用。

三者之间的关系？

DBMS-―执行-->SQL—-操作-->DB

SQL相关语法

1. SQL语句可以单行或多行书写，以分号结尾。

2. SQL语句可以使用空格/缩进来增强语句的可读性。

3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写

4. 注释：

• 单行注释：--注释内容或#注释内容（MySQL）特有

• 多行注释：/*注释内容*/

SQL语句的分类

DQL：

数据查询语言（凡是带有select关键字的都是查询语句）

select...

DML：

数据操作语言（凡是对表当中的数据进行增删改的都是DML）

insert delete update

insert 增

delete 删

update 改

这个主要是操作表中的数据data。

DDL：

数据定义语言

凡是带有create、drop、alter的都是DDL。

DDL主要操作的是表的结构。不是表中的数据。

create：新建，等同于增

drop：删除

alter：修改

这个增删改和DML不同，这个主要是对表结构进行操作。

TCL：

是事务控制语言

包括：

事务提交：commit;

事务回滚：rollback;

DCL：

是数据控制语言。

例如：授权grant、撤销权限revoke....

DDL-数据库操作

查询

查询所有数据库：show databases;

查询当前数据库：select database();

创建

create database [if not exists] 数据库名 [default charset字符集] [collate 排序规];

删除

drop database [if exists] 数据库名；

使用

use 数据库名;

DDL-表操作

创建

craete table 表明(

字段1 类型 [comment 注释],

字段2 类型 [comment 注释],

......

字段n 类型 [comment 注释]

) [comment 表注释];

查询

查询当前数据库所有表：show tables;

查询表结构：desc 表名；

查询指定表的建表语句：show create table 表名;

修改

添加字段：alter table 表名 add 字段名 类型 [comment 注释];

修改数据类型：alter table 表名 modify 字段名 新数据类型;

修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型 [comment 注释];

删除字段：alter table 表名 drop 字段名;

修改表名：alter table 表名 rename to 新表名;

删除

删除表：drop table [if exists] 表名;

删除表中的数据：truncate table 表名;

mysql中的常见数据类型

varchar(最长255)

可变长度的字符串

比较智能，节省空间。

会根据实际的数据长度动态分配空间。

优点：节省空间

缺点：需要动态分配空间，速度慢。

char(最长255)

定长字符串

不管实际的数据长度是多少。

分配固定长度的空间去存储数据。

使用不恰当的时候，可能会导致空间的浪费。

优点：不需要动态分配空间，速度快。

缺点：使用不当可能会导致空间的浪费。

varchar和char我们应该怎么选择？

性别字段你选什么？因为性别是固定长度的字符串，所以选择char。

姓名字段你选什么？每一个人的名字长度不同，所以选择varchar。

int(最长11)

数字中的整数型。等同于java的int。

bigint

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

float

单精度浮点型数据

double

双精度浮点型数据

date

短日期类型

datetime

长日期类型

clob

字符大对象

最多可以存储4G的字符串。

比如：存储一篇文章，存储一个说明。

超过255个字符的都要采用CLOB字符大对象来存储。

Character Large OBject:CLOB

blob

二进制大对象

Binary Large OBject

专门用来存储图片、声音、视频等流媒体数据。

往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，

你需要使用IO流才行。

常见约束

含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性

添加约束的时机：创建表时、修改表时

添加分类：

1 列级约束

直接在字段名和类型后面追加约束类型，只支持默认、非空、主键、唯一，不可起名

2 表级约束

在各个字段最下面 [constraint 约束名] 约束类型（字段名），支持主键、唯一、外键，可起名

分类：

• NOT NULL 非空约束 保证该字段的值不为空

• DEFAULT 默认约束 用于保证该字段有默认值

• PRIMARY KEY 主键约束 用于保证该字段值具有唯一性，并且非空

• UNIQUE 唯一约束 保证该字段值唯一，但可为空

• CHECK 检查约束 MySQL8.0.16后支持

• FOREIGN KEY 外键约束 限制两个表的关系，保证该字段必须来自于主表关联列的值，在从表添加外键约束，用于引用主表中某列的值

添加外键：

alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名);

删除外键：

alter table 表名 drop foreign key 外键名称;

主键和唯一键的区别：

①都可以保证唯一性

②主键不允许为空，唯一允许为空（只能插入一个null）

③一个表中至多一个主键，唯一键可以有多个

④都可以组合使用（不推荐）

外键的特点：

1.要求在从表设置外键关系

2.从表的外键列类型和主表的关联列类型要求一致或兼容，名称无要求

3.主表的关联列必须是一个key（一般是主键）

4.要求插入数据时，先插入主表，再插入从表，删除数据时，先删除从表，再删除主表。

DML-数据操作语言

插入：insert into 表名（字段...） values （值...）

• 要求插入值得类型必须与列得类型一致或兼容

• 不为null的列必须插入值，可为null的列可以插入null或同时省略字段和值

• 省略插入列名默认所有列，而且列的顺序和表的顺序一致

• 字符串和日期型数据应该包含在单引号中

修改：

修改单表的记录：update 表名 set 列 = 新值，... where 筛选条件

删除：

方式一

单表的删除：delete from 表名 where 筛选条件

方式二：

语法：truncate table 表名，整个表全部删除

区别：

①delete 可以加where条件

②truncate效率较高

③如果要删除的表中有自增长列，如果用delete删除后再插入数据，值从断点开始，而truncate删除后再插入数据，值从1开始。

④truncate删除没有返回值，delete有。

⑤truncate不能回滚，delete可以。

DQL-数据查询语言

基础查询

语法：select 查询列表 from 表名；

查询列表可以是：表中的字段、常量、表达式、函数

查询的结果是一个虚拟的表格

1. 查询表中的单个字段

例：select last_name from employees；

2. 查询表中的多个字段

例：select last_name，salary from employees；

3. 查询表中的所有字段

例： select * from 表名；

4. 查询常量值

例：select 100；/ select ‘john’;

5. 查询表达式

例：select 100*98;

6. 查询函数

例：select version();

去重：在查询列表前加distinct

条件查询

语法： select 查询列表 from 表名 where 筛选条件

分类：

1.按条件表达式筛选

条件运算符：大于> 小于< 等于= 不等于<> !=大于等于>= 小于等于<=

2.按逻辑运算符筛选

逻辑运算符：

与：&& and 如果两个条件都为true则为true，否则为false

或：|| or 如果有一个条件为true则为true，否则为false

非：！not 取反

3.模糊查询

like

一般和通配符搭配使用 %：0或多个字符 _任意单个字符

可判断字符型或数值型

查名字是两个字的：select * from 表名 where name like '_ _';

4.英语语法

between...and...

in( ... , ... , ... , ...)

多个条件连接：and or

排序查询

语法：select 字段列表 from 表名 order by 字段1 [asc/desc],字段2 [asc/desc];

默认为asc升序。desc 降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

一般放在查询语句的最后面，limit语句除外。

分页查询

语法：select 字段列表 from 表名 limit 起始索引,查询记录数;

注意：

起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。

如果查询的是第一页数据，起始索引可以省略。

常见函数

好处：提高重用性、隐藏实现细节

调用：select 函数名(实参列表) from 表名

分类：

单行函数，如concat、length、ifnull等

1.字符函数

①length(str) 获取str的字节个数 一个汉字占3字节(utf8)

②concat(str1,str2…strn) 拼接字符串

③upper(str)、lower（str） 转换大小写

④substr、substring返回子字符串，SQL中索引从1开始

substr(str,n) 截取从n开始的所有字符

substr(str,n,m) 截取从n开始的m个字符

⑤instr(str,substr) ，返回substr在str中第一次出现的索引，没有返回0。

⑥trim(str)，去掉字符串中空格。

trim(x from str),去掉str前后的x字符。

⑦lpad(str,n,c) 左填充，用指定字符c填充str左侧至n长度。

rpad(str,n,c) 右填充，用指定字符c填充str右侧至n长度。

注：n是总长

⑧replace(str,a,b) 替换

2.数学函数

①round(x,y) 四舍五入，保留y位小数

②ceil(x) 向上取整，返回大于等于参数的最小整数

③floor(x) 向下取整，返回小于等于参数的最大整数

④truncate(n,m) 截断n，保留m位

⑤mod(a,b) 取余

⑥rand() 获取0-1之间的随机数

3.日期函数

①now() 返回当前系统日期+时间

②curdate() 返回当前日期

③curtime() 返回当前时间

④year(date),month(date),day(date),hour(date),minute(date),second(date) 获取指定date的年月日时分秒

⑤str_to_date() 将日期格式的字符转换成指定格式的字符串

⑥date_format() 将日期转换成字符

⑦datediff(date1,date2)返回两日期相差的天数

⑧date_add(date,INTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值

例：date_add('2023-01-26',INTERVAL 70 DAY）返回2023-04-06

4.其他函数

①version() 版本号

②databases() 查看所有数据库

③user() 当前用户

④password(str) 返回加密形式（MySQL8已弃用）

⑤md5(str) 返回字符串的md5模式

5.流程控制函数

①if (exp1,exp2,exp3) 若exp1为true，返回exp2，否则返回exp3

②case

用法一：

case 要判断的字段或表达式

when 常量1 then 要显示的值 或 语句1；

…

when 常量n then 要显示的值 或 语句n；

else 要显示的值或语句m

end

用法二：

case

when 条件1 then 要显示的值或语句1；

…

when 条件n then 要显示的值 或 语句n；

else 要显示的值或语句m

end

用法三：

case [expr] when [val1] then [res1] ... else [default] end

如果exper的值等于val1,返回res1 ... 否则返回default默认值

分组函数，做统计使用，又称统计函数或聚合函数

①sum() 求和

②avg() 求平均值

③min() 求最小值

④max() 求最大值

⑤count() 求非空个数

count(*)/count(常量值) 统计总行数

null值不参与所有聚合函数运算

分组查询

语法：select 查询列表 from 表名 [where 筛选条件] group by 排序列表 [having 分组后过滤条件]

select 分组函数，列
from 表名 【where 筛选条件】
group by 分组列表 【order by 字句】

where与having区别

1. 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

2. 判断条件不同：where不能对聚合函数进行判断，而having可以。

注意

执行顺序：where>聚合函数>having。

from 表名—>where 条件—>group by 分组字段—>having 分组后条件—>select 字段—>order by排序字段—>limit 分页参数

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

特点：

分组前筛选 数据源为原始表 用where

分组后筛选 数据源为分组后结果集 用having

分组函数做条件肯定放在having子句中

能用分组前筛选的优先考虑分组前筛选

group by支持单个字段，多个字段(用，隔开)，表达式或函数分组，也可以添加排序（放在最后）。