数据库相关概念
数据库:
英文单词pataBase,简称DB。按照一定格式存储数据的一些文件的组合.顾名思义:存储数据的仓库,实际上就是一堆文件。这些文件中存储了具有特定格式的数据。
数据库管理系统:
DataBaseManagement,简称DBMS 。
数据库管理系统是专门用来管理数据库中数据的,数据库管理系统可以对数据库当中的薮据进行增删改查。
常见的教据库管理系统:MysQLoracle、MS sqlserver、DB2、sybase等....
SQL:结构化查询语言:
程序员需要学习SQr语句,程序员通过编写SQL语句,然后DBMS负责执行SQL语句,最终来完成数据库中数据的增删改查操作。
SQL是一套标准,程序员主要学习的就是SQL语句,这个SQL在mysql中可以使用,同时在oracle中也可以使用,在DB2中也可以使用。
三者之间的关系?
DBMS-―执行-->SQL—-操作-->DB
SQL相关语法
SQL语句可以单行或多行书写,以分号结尾。
SQL语句可以使用空格/缩进来增强语句的可读性。
MySQL数据库的SQL语句不区分大小写,关键字建议使用大写
注释:
单行注释:--注释内容或#注释内容(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区别
执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
判断条件不同:where不能对聚合函数进行判断,而having可以。
注意
执行顺序:where>聚合函数>having。
from 表名—>where 条件—>group by 分组字段—>having 分组后条件—>select 字段—>order by排序字段—>limit 分页参数
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
特点:
分组前筛选 数据源为原始表 用where
分组后筛选 数据源为分组后结果集 用having
分组函数做条件肯定放在having子句中
能用分组前筛选的优先考虑分组前筛选
group by支持单个字段,多个字段(用,隔开),表达式或函数分组,也可以添加排序(放在最后)。