数据库入门学习 | 个人学习笔记 | MySQL

常用命令

SQL语句不见分号;不执行❗️
SQL语句不区分大小写~

• exit 退出数据库mysql
• show database;展示当前所有数据库
• use databaseName;使用名为databaseName的数据库
• create databaseName;创建一个名为databaseName的数据库
• select version();查看当前数据库版本号

基础单元

数据库最基本的单元是表table

姓名	性别	年龄
张三	男	20
李四	女	21
王五	男	22

• 行（row）：数据/记录
• 列（col）：字段。例如上表的姓名字段、性别字段、年龄字段。
• ❗️Attention。
  每个字段都有：字段名（性别）、数据类型（男/女）、约束等属性
  数据类型：字符串、数字、日期等。
  ⭕️唯一性约束，添加约束后，该字段内的数据不允许重复。（类似集合里的唯一性）

SQL语句结构

• 语句分类
  • 数据查询语言DQL（Data Query Language）
    查
  • 数据操作语言DML（Data Manipulation Language）
    操作对象：表的数据
    增删改
    
  • 数据定义语言DDL
    操作对象：表table
    • CREATE新建一个表
    • DROP删除一个表
    • ALTER修改一个表
  • 事务处理语言TPL
    确保被DML语句影响的表及时更新
    • BEGIN TRANSACTION
    • COMMIT事务提交
    • ROLLBACK事务回滚
  • 数据控制语言DCL
    获取许可，确定单个用户与用户组对数据库对象的访问
    • GRANT授权
    • REVOKE撤销权限
  • 指针控制语言CCL
    对一个or多个独行操作
    • DEVLARE CURSOR声明光标
    • FETCH INTO
    • UPDATE WHERE CURRENT

查询语言DQL

简单查询

select后面可以跟【字段名】$\to$ 变量名。
也可以跟【字面量/字面值】$\to$ 数据。
字面量要用单引号''括起来。

• 多字段查询需要逗号,连接

select 字段名1/字面量1,字段2/字面量2 from 表名;

• *代表选择所有字段【可读性差】
  选中student表中的所有数据

  select * from student;
  

• 去重查询
  distinct关键字，去掉所选字段的重复数据【数据唯一】

  select distinct 字段1 from 表名;
  

条件查询

• 语法格式

  select 字段1,字段2... from 表名 where 条件;
  

• 运算符

  运算符	说明
  =	等于
  <>或者!=	不等于
  <	小于
  <=	小于等于
  >	大于
  >=	大于等于
  between...and...	两个值之间【左下右大】【闭区间】，等同于>= and <=
  is null	为空null
  is not null	不为空
  and	并且【优先级 > or】
  or	或者
  in	包含，相当于多个or 【非区间，是集合】
  not in	不在范围内
  not	取非
  like	模糊查询。支撑%或者下划线_匹配 。

  • like模糊查询

    • %匹配任意字符；
    • _一个下划线只匹配一个字符

    🌰举栗
    1️⃣ 请查询student表中，名字name中包含字母o的学生

    select name from student where name like '%o%';
    

    2️⃣ 请查询student表中，名字第二个字母是r的学生

    select name from student where name like '_r%';
    

    3️⃣ 请查询student表中，名字包含下划线_的学生

    select name from student where name like '%\_%';
    

    ⭕️ 由于_是模糊查询的关键字，我们需要在前面加上\转义字符将其转义成正常字符下划线_。

排序

排序一般都放在最后执行
order by【默认升序】

select 字段1 from 表名 order by salary desc;
//降序descend；升序ascend

• 多字段排序。
  👊优先级：字段1>字段2
字段1起主导作用，只有字段1相等时，才使用字段2进行排序❗️

  select 字段1 from 表名 order by 字段1 asc, 字段2 asc;
  

• 可以根据字段位置进行排序【不健壮】
  按照第$2$列进行排序

  select 字段1 from 表名 order by 2;
  

数据处理函数

单行处理函数：一个input对应一个output
多行处理函数：多个input 对应一个output

函数名	说明	使用方法
lower	转换小写	lower(字段)
upper	转换大写	upper(字段)
concat	字符串拼接	concat(字符串1, 字符串2)
substr	子串	substr(被截取的字符串, 起始pos, 截取长度len)
length	长度	length(字段)
trim	去掉空格	trim(字符串)
str_to_date	字符串转日期。将字符串varchar类型$\to$date类型	str_to_date('字符串日期','日期格式')
date_format	格式化日期。将date类型$\to$具有格式的varchar类型	date_format(日期, '格式')
format	设置千分位	format(数字, '格式') 🌰加入千分位format(salary, '$999,999')
round	四舍五入	round(数值, 保留小数个数)
rand()	生成0~1 随机数	rand()*100 100以内的随机数
ifnull	将null 转换成一个具体数值	ifnull(数据, 替换值)

数据库中，只要有NULL参与的数学运算，最终结果皆为NULL

• mysql的日期格式

  代号	说明
  %Y	年
  %m	月
  %d	日
  %h	时
  %i	分
  %s	秒

  ❓将字符串varchar类型的日期改成date类型?
  答：str_to_date('01-10-2002','%d-%m-%Y')。当日期字符串格式为%Y-%m-%d时，mysql会自动转换格式，此时就不需要使用str_to_date函数。
  查找数据时，对日期进行格式化输出$\Rightarrow$🌰date_format(birth, '%Y/%m/%d')

• 分类讨论

  MANAGER工资上调10%，SALESMAN工资上调50%，其他正常

  case 字段名 when 条件1 then 操作1 when 条件2 then 操作2 else 操作3 end;
  case job when 'MANAGER' then salary*1.1 when 'SALESMAN' then salary*1.5 else salary end;
  

分组函数/聚合函数/多行处理函数

函数名	说明
count	取得记录的个数
sum	求和
avg	取平均
max	取最大值
min	取最小值

👻分组函数一定要【分组】哦！

select 函数名(字段名) from 表名;
select count(salary) from employer;
//count()里面也可以是一个表达式
select count(gender='male') from user_profile;

1. 自动忽略空值NULL，无需手动加where条件排除空值NULL。
2. 分组函数中count(*)与count(某字段)区别
   • count(*)：统计表中总行数【即表内有多少条记录】
   • count(某字段)：该字段下，不是空值NULL的数据个数
3. 分组函数不能直接使用在where子句
4. 所有分组函数可以组合使用

   select sum(salary),max(salary),count(salary) from employer;
   

🔥分组查询

select ... 
from ...
group by ... 
having ... 
order by ...
limit ...;

👊执行顺序:
1️⃣ from $\to$ 在数据库中打开这个表
2️⃣ where $\to$ 在整张表里进行检索
3️⃣ group by $\to$ 开始分组
4️⃣ having $\to$ 再次过滤【已分组】的数据
5️⃣ select $\to$ 提取数据
6️⃣ order by $\to$ 排序
📎 为什么where子句不能用分组函数？
答：由于where执行时，整张表还是一个未分组的状态，此时执行【分组函数】是会❌报错的。

• having对分完组后的数据进一步过滤
  ⭕️having必须和group by 联合使用
  ✅优先选择使用where进行过滤【效率更高】
  BUT❗️ 当要求过滤（使用运算符）的数据是平均数值【即需要先进行分组的数据】，则只能使用having了、

🚩连接查询

从一张表中单独查询，称为单表查询。
联合两个以上的表进行查询数据，称为连接查询。
🌰举个栗子，运营想要从employer表中拿到员工名字，再去department表中拿到部门详情。

• 笛卡尔积现象
  蓝色组 要和 黄色组 拼接，蓝色组每一个成员都要和黄色组的每一个成员有🔗连接。形成的连接对有(A,1)、(A,2)、(A,3)、(B,1)、(B,2)、(B,3)、(C,1)、(C,2)、(C,3)。
  总共3*3=9个连接对。
  为了避免笛卡尔积现象，一定是要带着条件去连接两张表！
  👊 where子句中，要表示某个表的某个字段要用点.连接。类似于C++中取某个类的函数~

1. 内连接inner join
   连接的表之间无主次关系

   • 等值连接
     sql92的语法结构不清晰，连接条件没有与后续筛选条件分割开来。

     select e.name, d.name 
     from employer e, department d 
     where e.deptno = d.deptno;
     

     sql99表连接的条件是独立的。进一步筛选可以在后面使用where语句进行过滤。

     select e.name, d.name 
     from employer e
     join department d 
     on e.deptno = d.deptno;
     

   • 不等值连接

     select e.name, e.sal, s.grade
     from employer e
     join salgrade s
     on e.sal between s.losal and s.hisal;//条件关系非等量关系 -->  非等值连接
     

   • 自连接
     一张表看成是两张表
     🌰举个栗子
     我现在有一张表employ

     empNo	ename	mgr
     1001	Sam	1002
     1002	William	1003
     1003	Caroline	1005

     select a.ename as '下级' , b.ename as '上级'
     from employ a
     join employ b
     on a.mgr = b.empNo;
     

2. 外连接outer join
   连接的表之间有主次关系

   • 右外连接right join

     select 表1.字段1, 表2.字段2 
     from 表1 right join 表2
     on 表1.字段3 = 表2.字段3
     

     right代表，join关键字右边的表2作为主表，主要为了将主表数据全部查询到，捎带查询关联的表1
   • 左外连接left join

     //outer可选，加入的话可读性更强
     select 表1.字段1, 表2.字段2 
     from 表1 left outer join 表2
     on 表1.字段3 = 表2.字段3
     

     与右外连接同理。

   ⭕️利用using关键字简化
   连接查询时如果是同名字段作为连接条件，using可以代替on出现（比on更好）
   using是针对同名字段进行自动合并
   🌰（using(id)===on A.id=B.id）

   select cust_name,order_num 
   from Customers a
   left join Orders using(cust_id)
   order by cust_name;
   

   ❓ 外连接的查询结果一定大于等于内连接的查询结果？
   答：✅正确。

3. 全连接
   连接的表都是主表~（两张表都会被展示）

在一条SQL中，内连接与外连接是可以混合出现的~

子查询

select语句中嵌套select语句，被嵌套的select语句成为子查询。
嵌套可以出现在各个位置

select ...(select)
from ...(select)
where ...(select)

front后面的子查询的结果可以当作一张临时表。
select后面的子查询只能一次返回1条结果，多余1条就报错。

union合并

union可以减少匹配次数，同时完成两个结果集的拼接。
BUT❗️join表连接的匹配次数翻倍。

⭕️union结果集合并的时候，要求两格结果集列数col相同并且数据类型也要相同。
【按行拼接】连接表，对行操作。

• union–将两个表做行拼接，同时自动删除重复的行。
• union all—将两个表做行拼接，保留重复的行

limit限制

将查询结果的一部分【子集】取出来，适用于分页查询。
7️⃣ limit在order by之后执行❗️

1. 选择前n行
   1️⃣limit语句只有1个参数时，代表返回的最大记录(行row)数目
   2️⃣有2个参数时

   limit para1, para2
   

   para1第一个参数代表第一个返回记录行的偏移量(offset)，para2第二个参数代表返回记录行的最大数量。
   ✌️当para2为-1时，代表某一偏移量开始到当前表的末尾。

   • 🌰选择前5行

   //从开头最多返回5个
   select 字段 from 表名 limit 5;
   //偏移位置=0【从第一行数据开始】
   //5代表往下数5个数据输出
   select 字段 from 表名 limit 0,5;
   

2. 选择中间任意行

   //查询第n~m行
   select 字段 from 表名 limit m-n offset n;
   select 字段 from 表名 offset n rows fetch next m-n rows only;
   

3. 分页

   limit (pageNo - 1)*pageSize, pageSize
   

定义语言DDL

建表

create table 表名(字段1 数据类型, 字段2 数据类型, 字段3 数据类型);

⭕️表名和字段名都要用反引号 ` 括起来！！！
👍快速复制表

create table 表名2 as select * from 表名1;

数据类型

数据类型	说明
varchar	可变长度的字符串。智能节省空间，会根据实际的数据长度动态分配空间。
char	定长字符串。最长255个字符。可能造成资源浪费，速度比varchar快
int	整数型。最长11个字符。
bigint	长整型。$⟺$Java中的long
float	单精度浮点型数据
double	双精度浮点型数据
date	短日期【年月日】默认格式%Y-%m-%d
datetime	长日期 【年月日时分秒】默认格式%Y-%m-%d %h:%i:%s
clob	字符大对象。Character Large Object。最多可以存储4G的字符串🌰简介、文章。超过255个字符的使用该数据类型
blob	二进制大对象。Binary Large Object。存储图片、声音、视频等流媒体。【使用I/O流】

🕛在mysql中如何获取当前时间？
答：now()函数获取的时间是长日期类型，包含时分秒信息。

删除表

如果要删除的表不存在时会报错❗️
所以我们需要限定情况。【表名要用单引号 ` 括起来】
整个表的结构都没了❗️

drop table if exists `表名`;

操作语言DML

插入数据

1. insert插入

insert into 表名(字段1, 字段2, 字段3...) values(值1, 值2, 值3);

⭕️字段与值要一一对应
✅一次可以插入多条数据

insert into 表名(字段1, 字段2, 字段3) 
values (v1_1,v1_2,v1_3),(v2_1,v2_2,v2_3),(v3_1,v3_2,v3_3),(v4_1,v4_2,v4_3)...;
//插入了v1,v2,v3,v4四条数据

2. update修改

update 表名 set 字段1=值1,字段2=值2,字段3=值3... where 条件;

若无where条件限制，则所有数据全部更新。

3. delete删除

delete from 表名 where 条件;

没有条件的话，会将整张表的数据全部删除❌

• 删除数据操作
  • delete from table删除速度很慢！【在硬盘上的存储空间不释放】
    可以回滚，即可以ctrl+z撤销操作。
  • 💬truncate语句删除效率高，一次截断，物理删除！不支持回滚。【属于定义语言DDL】
    truncate删除表中数据，表结构还在❗️

🔒约束

创建表时，给字段加上约束以保证表中数据的完整性、有效性。
1️⃣列级约束。约束加在列(字段)后面。
🌰下列示例中，id后面的unique就是列级约束。

create table t_vip(
id int unique,
name varchar(255),
email varchar(255),
unique(name,email)
);

2️⃣ 表级约束。约束没有加在列(字段)后面，称之为 “表级约束”
🌰上列示例中的name和email就是联合约束。unique(name,email)就是表级约束。

• 非空约束not null
  字段不能为空值NULL
  只有列级约束，无表级约束。

• 唯一性约束unique
  不能重复，但可以是NULL
  ⭕️联合唯一性
  【两个字段中其中一个不重复即可】
  unique(字段1, 字段2)

• 主键约束primary key
  主键值时每一行记录的唯一标识。
  复合主键primary key(字段1, 字段2)
  ⭕️一张表中，主键约束只能添加一个❗️
  主键更像是公司里每个员工都有特定编号一样，方便记录上班打卡情况blah blah的

  • 自然主键
    自然数，与业务无关。
  • 业务主键
    主键值与业务紧密关联。eg. 银行卡号
    自动维护一个主键值使用auto_increment自动增加
    👉如果，一个字段同时被not null和unique约束了，该字段会自动变成主键字段。

• 🔥外键约束foreign key
  代表受约束的字段的每一个值都是外键值【在父表里的数据】
  ❓思考：子表中外键引用的父表某字段必须是主键吗？
  答：不一定是主键，但必须有unique约束【满足唯一性】
  外键可为NULL

• 检查约束check(mysql不支持, oracle支持)

✉️实际工作中，xxx.sql脚本文件里有大量的sql语句，执行该脚本文件即可得到数据库数据。
✅在mysql里执行下列语句即可导入

source sql脚本文件绝对路径

存储引擎

存储引擎是MySQL中的独家术语，其他数据库没有。实际上存储引擎就是一个表存储/组织数据的方式。

//展示表的“存储引擎”
show create table 表名;

ENGINE指定存储引擎。
CHARSET=utf8指定当前表的字符编码方式。
🔍查看当前MySQL支持那些存储引擎。

show engine \G

ENGINE=InnoDB存储引擎支持事务。支持事务处理，保证数据的安全。支持外键和引用的完整性。

👻事务处理语言TPL

事务是一个完整的业务逻辑。
只有DML语句insert、delete、update才和事务有关系。
💰数据安全第一位。
一个事务$⟺$多条DML语句同时成功 or 同时失败。

事务执行过程中，每一条DML语句操作都会被记录在“事务性活动的日志文件”中。
🆗可以提交事务，也可以回滚事务。

4个特性

1️⃣A 原子性
事务即最小的工作单元，不可再分。
2️⃣C 一致性
在同一事务中，所有操作必须同时成功or同时失败。
3️⃣I 隔离性isolation
多线程并发。

• 4个隔离级别(由低到高⬆️)

  1. read uncommitted读未提交
     事务A可以读取到事务B未提交的数据。【脏读】

  2. read committed读已提交
     事务A只能读取到事务B提交后的数据。

  3. repeatable read可重复读
     事务A看到的数据永远只有第一版，即使事务B提交了也看不到修改❗️【海市蜃楼】

  4. serializable序列化
     事务排队，不能并发，效率最低。线程通读（事务同步）。

🔍查看隔离级别

SELECT @@tx_isolation;
//MySQL 8 之后
SELECT @@transaction_isolation;

4️⃣D 持久性
事务提交，就相当于没有保存到硬盘上的数据被保存到硬盘上了。

commit提交事务

清空日志文件，将数据持久化到数据库表中。【全部成功✔️】

• 自动提交机制
  每执行一条DML语句就提交一次事务。
  🖕关闭该机制

  start transaction;
  

rollbacke回滚事务

将之前所有的DML操作全部撤销，并且清空日志文件。【全部失败❌】
回滚只能回滚到上一次的提交点。

索引

索引在数据库表的字段上添加，是为了提高查询效率的机制。
索引在MySQL中以【树】的形式存在——自平衡二叉树B-Tree。
底层使用【哈希算法】
MySQL在查询方面有两种方式
1️⃣ 全表扫描
2️⃣ 根据索引扫描

创建索引

create index 索引名 on 表名(字段名);

删除索引

drop index 索引名 on 表名;

视图

视图对象存储在【硬盘】上。
当一条复杂的SQL语句在不同位置要反复使用，我们就可以启用视图对象将其重建。

• 增删改查CRUD
  C：Create（增）
  R：Retrive（查）
  U：Update（改）
  D：Delete（删）

创建视图对象

create view 视图名 as select....;

在as后面的语句必须是数据查询语句DQL。
看到as其实就应该想到别名，相当于引用，我们对视图的修改会直接影响到原表中的数据❗️

删除视图对象

drop view 视图名;

视图更新

update 视图名 set 操作 where 条件;

数据库设计三范式

避免表中数据冗余造成资源空间的浪费。
数据库三大设计范式就是设计数据库的设计依据。
1️⃣第一范式
要求任何一张表必须有主键，每一个字段原子性不可再分。
2️⃣第二范式
要求所有非主键字段完全依赖主键，不要产生部份依赖。
3️⃣第三范式
要求所有非主键字段直接依赖主键，不要产生传递依赖。

• 一对多
  两张表，多的表加上外键foreign key
• 多对多
  三张表，关系表两个外键foreign key

💥最终目的都是都是为了满足客户的需求，有的时候会拿冗余换取执行速度。
存在冗余也是为了减少表的连接次数。