Mysql高级学习笔记

最新推荐文章于 2024-09-26 10:53:32 发布
最新推荐文章于 2024-09-26 10:53:32 发布
阅读量215 收藏
点赞数
分类专栏： Java学习笔记 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_57041698/article/details/118475382
版权

1 数据完整性

1.1 数据库的完整性

用来保证存放到数据库中的数据是有效的,即数据的有效性和准确性

确保数据的完整性 = 在创建表时给表中添加约束

完整性的分类：

  • 实体完整性(行完整性):
  • 域完整性(列完整性):
  • 引用完整性(关联表完整性):
约束类型命令
主键约束primary key
唯一约束unique [key]
非空约束not null
默认约束default
自动增长auto_increment
外键约束foreign key

这些约束在创建表时设置，多个约束条件之间使用空格间隔。

create table student(
    studentno int primary key auto_increment,
    loginPwd varchar(20) not null default '123456',
    studentname varchar(50) not null,
    sex char(2) not null,
    gradeid int not null,
    phone varchar(255) not null,
    address varchar(255) default '学生宿舍',
    borndate datetime,
    email varchar(50)
);

1.2 实体完整性

实体：即表中的一行(一条记录)代表一个实体（entity）

实体完整性的作用：标识每一行数据不重复。

约束类型：

  • 主键约束（primary key）
  • 唯一约束(unique)
  • 自动增长列(auto_increment)

1.2.1 主键约束(primary key)

每个表中要有一个主键，数据唯一，且不能为null。

三种添加主键的方式：

1.创建表同时创建主键

CREATE TABLE student( 
    id int primary key, 
    name varchar(50) 
);

2.创建联合主键

CREATE TABLE student( 
    id int, 
    name varchar(50), 
    primary key(id) 
);
CREATE TABLE student( 
    classid int, 
    stuid int, 
    name varchar(50), 
    primary key(classid,stuid) 
);

3.创建表后修改

CREATE TABLE student( 
    id int, 
    name varchar(50) 
);
ALTER TABLE student ADD PRIMARY KEY (id);

1.2.2 唯一约束(unique)

数据不能重复，与主键约束区别在于可以为空一次

CREATE TABLE student( 
    Id int primary key, 
    Name varchar(50) unique 
);

1.2.3 自动增长列(auto_increment)

sqlserver数据库 (identity-标识列)

oracle数据库(sequence-序列)

给主键添加自动增长的数值，列只能是整数类型

CREATE TABLE student( 
    Id int primary key auto_increment, 
    Name varchar(50) 
);
INSERT INTO student(name) values(‘tom’);

1.3 域完整性

域完整性的作用：域代表当前单元格，限制此单元格的数据正确，不对照此列的其它单元格比较

域完整性约束：

  • 数据类型 非空约束（not null）
  • 默认值约束(default)
  • check约束（mysql不支持）, check(sex=‘男’ or sex=‘女’)

1.3.1 数据类型

1 数值类型
类型大小范围（有符号）范围（无符号 ）用途
tinyint1 字节(-128，127)(0，255)小整数值
smallint2 字节(-32 768，32 767)(0，65 535)大整数值
mediumint3 字节(-8 388 608，8 388 607)(0，16 777 215)大整数值
INT4 字节(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值
bigint8 字节(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)(0，18 446 744073 709 551 615)极大整数值
float4 字节(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度浮点数值
double8 字节(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4E-308，1.797 693 134 862 315 7 E+308)双精度浮点数值
2 日期类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性。

类型大小(字节)范围格式用途
DATE31000-01-01/9999-12-31YYYY-MM-DD日期值
TIME3‘-838:59:59’/‘838:59:59’HH:MM:SS时间值或持续时间
YEAR11901/2155YYYY年份值
DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
TIMESTAMP41970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值，时间戳 ,当更新数据的时候自动添加更新时间
3 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

类型大小用途
CHAR0-255字节定长字符串
VARCHAR0-65535 字节变长字符串
TINYBLOB0-255字节不超过 255 个字符的二进制字符串
TINYTEXT0-255字节短文本字符串
BLOB0-65 535字节二进制形式的长文本数据
MEDIUMBLOB0-16 777 215字节二进制形式的中等长度文本数据
MEDIUMTEXT0-16 777 215字节中等长度文本数据
LONGBLOB0-4 294 967 295字节二进制形式的极大文本数据
LONGTEXT0-4 294 967 295字节极大文本数据

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

定义一个char[10]和varchar[10],如果存进去的dao是‘abcd’,那么char所占的长度依然为10，除了字符‘abcd’外，后面跟六个空格，而varchar就立马把长度变为4了，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar是不需要的。

char的存取速度还是要比varchar要快得多，因为其长度固定，方便程序的存储与查找；但是char也为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可谓是以空间换取时间效率，而varchar是以空间效率为首位的。

char的存储方式是，对英文字符（ASCII）占用1个字节，对一个汉字占用两个字节；而varchar的存储方式是，对每个英文字符占用2个字节，汉字也占用2个字节，两者的存储数据都非unicode的字符数据。

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值。

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的
最大长度和存储需求。

1.3.2 非空约束

not null

CREATE TABLE student( 
    Id int primary key, 
    Name varchar(50) not null, 
    Sex varchar(10) 
); 
INSERT INTO student values(1,’tom’,null);

1.3.3 默认值约束

default

CREATE TABLE student1( 
    Id int primary key, 
    Name varchar(50) not null, 
    Sex varchar(10) default '男' 
);
insert into student1 values(1,'tom','女');
insert into student1 values(2,'jerry',default);

1.4 引用完整性（参照完整性）

外键列的数据类型一定要与主键的类型一致，外键约束：foreign key

CREATE TABLE student(
    id int primary key, 
    name varchar(50) not null, 
    sex varchar(10) default '男' 
);

//alter table 被修改表名 add constraint 自定义外键名称 foreign key(外键列名) references 主键表名(主键列名)
create table score(
    id int primary key,
    score int,
    sid int ,
    constraint fk_score_sid foreign key(sid) references student(id) 
);

第二种添加外键的方式：

ALTER TABLE score1 
ADD CONSTRAINT fk_stu_score FOREIGN KEY(sid) REFERENCES student(id);

2 多表查询

多个表之间是有关系的，这种关系受外键列来维护。

2.1 多表的关系

2.1.1 一对多/多对一关系

如： 客户和订单，分类和商品，部门和员工

  • 一对多建表原则：在多的一方创建一个字段，字段作为外键指向一的一方的主键。

2.1.2 多对多关系

学生和课程

  • 多对多关系建表原则：需要创建第三张表,中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

2.1.3 一对一关系

在实际的开发中应用不多.因为一对一可以创建成一张表.

  • 两种建表原则：
    • 唯一外键对应：假设一对一是一个一对多的关系，在多的一方创建一个外键指向一的一方的主键，将外键设置为unique.
    • 主键对应：让一对一的双方的主键进行建立关系.

2.2 多表查询

多表查询有如下几种：

  1. 合并结果集:UNION，UNION ALL

  2. 连接查询

    1. 内连接 [INNER] JOIN ON

    2. 外连接 OUTER JOIN ON

      • 左外连接 LEFT [OUTER] JOIN
      • 右外连接 RIGHT [OUTER] JOIN
      • 全外连接（MySQL不支持）FULL JOIN

    3. 自然连接 NATURAL JOIN

  3. 子查询

2.2.1 合并结果集

作用：合并结果集就是把两个select语句的查询结果合并到一起

合并结果集有两种方式：

  1. UNION：去除重复记录，例如：SELECT* FROM t1 UNION SELECT * FROM t2；
  2. UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。

注意：被合并的两个结果：列数、列类型必须相同。

2.2.2 连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2，select * from t1,t2;。

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

那么多表查询产生这样的结果并不是我们想要的，那么怎么去除重复的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过关联关系去除笛卡尔积。

使用主外键关系做为条件来去除无用信息

SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

SELECT emp.ename,emp.sal,emp.comm,dept.dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;
1 内连接
select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where.....

select 列名
from 表1,表2
where 表1.列名=表2.列名 and ...(其他条件)

注:

  1. 表1和表2的顺序可以互换
  2. 找两张表的等值关系时,找表示相同含义的列作为等值关系。
  3. 点操作符表示“的”,格式:表名.列名
  4. 可以使用as,给表名起别名,注意定义别名之后,统一使用别名
//查询学生表中的学生姓名和分数表中的分数
select name,score 
from student as s 
inner join scores as c
on s.studentid=c.stuid

//等价于：
select name,score
from student as s,scores as c
where s.studentid=c.stuid

三表联查：

//语法：
select 列名 from 表1
inner join 表2 on 表1.列名=表2.列名
inner join 表3 on 表1/表2.列名=表3.列名 
where...
//等价于:
select 列名 from 表1,表2,表3
where 表1.列名=表2.列名 and 表1/表2.列名=表3.列名

SQL标准的内连接：

SELECT * FROM emp e
INNER JOIN dept d 
ON e.deptno=d.deptno;
2 外连接

包括左外连接和右外连接，外连接的特点：查询出的结果存在不满足条件的可能。

左外联
  • 左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
    1. 主表数据全部显示，次表数据匹配显示，能匹配到的显示数据，匹配不成功的显示null
    2. 主表和次表不能随意调换位置

使用场景:一般会作为子查询的语句使用

显示还没有员工的部门名称?

select depname,name from
(select e.*,d.depname from department d left join employee e
on e.depid=d.depid
) aa where aa.name is null;

左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。左表为主

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

显示的表:emp (主表) 在左，dept (次表) 在右，以emp为主，查询dept，dept显示结果可能有null。

右外联
  • 右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名

右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。右表为主

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

显示的表:emp (次表) 在左，dept (主表) 在右，以dept为主，查询emp，emp显示结果可能有null。

连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不
是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两
张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。

3 自然连接

自然连接（NATURAL INNER JOIN）：自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列。

select * from emp e natural join dept d;

2.2.3 子查询

一个select语句中包含另一个完整的select语句。

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

子查询出现的位置：

a. where后，作为条为被查询的一条件的一部分；

b. from后，作表；

SELECT * FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename='SCOTT');

当子查询出现在where后作为条件时，还可以使用如下关键字：

a. any

b. all

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

子查询结果集的形式：
a. 单行单列（用于条件）
b. 单行多列（用于条件）
c. 多行单列（用于条件）
d. 多行多列（用于表）

3 多行操作

3.1 多行新增

insert into 表名(列名) values (列值),(列值),(列值)；

3.2 多表更新

update 表1,表2 set 列名1=列值1,列名2=列值2... where 表1.列名=表2.列名 and 其他限定条件;

update 表1
inner join 表2 on 表1.列名=表2.列名
set 列名=列值
where 限定条件;;

示例：
update employee e,salary s
set title='助工',salary=1000
where e.empid=s.empid and name='李四';

3.3 多表删除

语法：

delete 被删除数据的表 from 删除操作中使用的表 where 限定条件

注:多张表之间使用逗号间隔

示例：

//删除人事部的信息
delete d,e,s from department d,employee e,salary s
where d.depid=e.depid and s.empid=e.empid and depname='人事部'

3.4 日期运算函数

now() 获得当前系统时间

year(日期值) 获得日期值中的年份

date_add(日期,interval 计算值 计算的字段);

注：计算值大于0表示往后推日期，小于0表示往前推日期

示例：

date_add(now(),interval -40 year);//40年前的日期

4 数据库优化

  1. 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
  2. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null

最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

  1. 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。
  2. 应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10
union all
select id from t where Name = 'admin'
  1. in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

很多时候用 exists 代替 in 是一个好的选择

MySQL | 利用 LEFT JOIN 实现增量数据的 INSERT INTO 插入
weixin_42659958的博客
05-17 7980
问题：在实际的系统开发和后期的数据维护工作中，经常会遇到这样的需求—将一个表的增量数据插入到该数据表中。 这个需求场景可以这么理解： 第一种、A系统和B系统共用某一基础数据，但A系统是在内网部署，为了严格的安全考虑，只允许B系统定期去A系统所在内网，现场将该基础数据导出为excel表（或者导出为insert into语句），再通过光盘拷贝。B系统就需要将excel表数据导入到自己系统的数据库。 第...
MySQL高级学习笔记
新新
12-05 7868
目录 1、MySQL数据库逻辑架构。 （1）网络连接层。 （2）服务层（MySQL Server）。 1）连接池（Connection Pool）。 2）系统管理和控制工具（Management Services & Utilities）。 3）SQL接口（SQL Interface）。 4）解析器（Parser）。 5）查询优化器（Optimizer）。 6）缓存和缓冲池（Cache&Buffer）。 （3）存储引擎层（Pluggable Storag...
SQL进阶
weixin_43960044的博客
07-13 828
SELECT TOP, LIMIT, ROWNUM 子句（取前 n 条记录） SQL Server / MS Access语法： SELECT TOP number|percent column_name(s) FROM table_name; MySQL语法： SELECT column_name(s) FROM table_name LIMIT number; Oracle语法： SELECT column_name(s) FROM table_name WHERE ROWNUM
SQL之LEFT JOIN,EIGHT JOIN,INSERT JOIN的区别
qq_41575913的博客
09-23 1304
SQL之LEFT JOIN,EIGHT JOIN,INSERT JOIN的区别 left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录 right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录 inner join(等值连接) 只返回两个表中联结字段相等的行 举例如下： 表A记录如下： aID　　　　　aNum 1　　　　　a20050111 2...
【实战篇】到底可不可以使用join？
最新发布
shenyongsi的博客
09-26 1092
今天这篇文章，我们先来说说 join 语句到底是怎么执行的，然后再来回答这两个问题。为了便于量化分析，我还是创建两个表 t1 和 t2 来和说明。可以看到，这两个表都有一个主键索引 id 和一个索引 a，字段 b 上无索引。存储过程 idata() 往表 t2 里插入了 1000 行数据，在表 t1 里插入的是 100 行数据。
MySQL中的Join用法
Auto
01-12 747
文章目录SQL中的Join用法#1 环境#2 创建表及插入数据#3 开始#3.1 inner join#3.2 left join#3.3 right join#3.4 full join SQL中的Join用法 #1 环境 MySQL5.7 Mac #2 创建表及插入数据 新建两个表tableA及tableB mysql> create table tableA ( id int(10)...
Mysql数据库的基本操作
123456
04-06 385
1,最基本语法 select [要查询的数据] from [在哪个表中查询] where [查询的条件(条件是判断真假的条件)] 2,group by 条件语法 select [] from [] group by [给那列进行分组] having [分组之后的多虑条件(条件是判断真假的条件)] 3,函数语法 select [经过函数计算查询的列] from [] 4,链接表的语法(连接之后...
MySQL高级学习笔记（三）：Mysql逻辑架构介绍、mysql存储引擎详解
01-21
Mysql逻辑架构介绍总体概览 和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储...
数据库 MySQL 学习笔记高级篇.md
09-01
数据库 MySQL 学习笔记高级篇.md
尚硅谷MySQL高级_笔记
10-05
尚硅谷MySQL高级的视频学习笔记，由本人整理
MySQL内连接INNER JOIN
2401_83963227的博客
04-07 322
MySQL内连接INNER JOIN
mysql/mariadb知识点总结 之 insert、delele、update AND select 语句总结
qq_48456652的博客
12-07 1288
insert常用语句 1、mysql中insert语句的语法一 insert into tablename(字段1名称,字段2名称,…) values(字段1值,字段2值,…) 示例如下： 向表tb1中插入一条数据，插入数据对应的字段为name和age，name的值为tom，age为33。 insert into tbl (name,age) values ('tom',33); 向表tb1中插入多条数据，具体含义同上，只不过是插入多条语句。 insert into tbl (na...
数据库系列之MySQL中Join语句优化问题
牧羊人的方向
10-16 3109
最近使用MySQL 8.0.25版本时候遇到一个SQL问题，两张表做等值Join操作执行很慢，当对Join连接字段添加索引优化后，执行效率反而变得更差，其中的原因值得分析。因此本文介绍下MySQL中常见的Join算法，并对比使用不同Join算法时候的性能情况。
多表联合查询（join、union等）
热门推荐
学无止境
04-11 6万+
<br /><br /> <br />内部连接 inner join 两表都满足的组合<br />full outer--全连.两表相同的组合在一起，A表有，B表没有的数据（显示为null）,同样B表有<br />A表没有的显示为(null)<br />A表 left join B表--左连,以A表为基础，A表的全部数据，B表有的组合。没有的为null<br />A表 right join B表--右连,以B表为基础，B表的全部数据，A表的有的组合。没有的为null<br /> <br />查询分析器中执行
MySql SQL
韩搏专栏
09-15 1348
db_name：数据库名字 tb_name：表名字 1、create database db_name; 创建数据库 2、show databases; 查看数据库 3、show create database db_name; 查看数据创建信息 4、drop database db_name; 删除数据库 ...
经典笛卡尔积SQL
ziyunlong1984的专栏
08-06 2301
经典笛卡尔积SQL： 下面的SQL会造成笛卡尔积： insert into tydic.temp_0731 select a.user_id,a.province_code,b.attr_code from tydic.pline_user_tmp a,tydic.pline_attr_tmp b where a.user_id = b.user_id and (a.service_id
python中几个易混淆函数区别，append，join，insert
每天早起吃早餐
10-19 1718
append是list（列表）的方法，函数参数是可以是任意一个元素，作用就是把元素追加到后边。例如a=[1,2,3]则a.append(4)以后a就是[1,2,3,4] join是string（字符串）的方法，函数参数是一个由字符串组成的列表比如str1=['a','b','c']，作用是用字符串把这个字符串列表里的字符串连接起来，比如：a='-'则a.join(str1)='a-b-c' i
尚硅谷MySQL高级学习笔记：存储引擎、SQL优化与索引详解
本资源是一份尚硅谷MySQL高级视频学习笔记，涵盖了多个关键知识点，旨在帮助用户深入理解并掌握MySQL高级操作。以下是对部分章节的详细解读： 1. Linux下MySQL编码设置：当在Linux环境下修改MySQL的字符集编码时，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值