MySQL学习笔记_mysql不允许外部连接-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_58212551/article/details/128569440

初学者学习MySQL数据库的笔记。

1、数据库分类：

关系型数据库：MySql、 Oracle、 DB2等；

数据以表的形式进行数据的存储，外键进行表间关系建立。

非关系型数据库：Redis等；

数据以对象的形式存储在数据库。

2、数据库及表的操作，DDL(Data Definition Language，数据定义语言)

MySQL默认不区分大小写

2.1数据库操作：

CREATE DATABASE [IF NOT EXISTS] 数据库名; -- 创建数据库

DROP DATABASE [IF EXIST] 数据库名; -- 删除数据库

USE 数据库名; -- 使用数据库

SHOW DATABASES; -- 查看数据库

2.2 数据类型：

1.数值类型

        tinyint（1字节）、smallint（2字节）、mediumint（3字节）、int（4字节）、bigint（8字节）、float（4字节）、double（8字节）、decimal。

2.字符串类型

char：字符串固定大小即定义多大字符串就占用多大空间，最大255个字符（字符集对char没有影响）；

        varchar：可变字符串，即以实际字符串大小存储，最大65535个字节，字符集对VARCHAR有影响, eg: UTF8字符集每个字符3字节，所以VARCHAR最多21845个字符；

        tinytext：微型文本、text ：文本串。

3.时间类型

date: YYYY-MM-DD

time: HH: mm: ss

datetime: YYYY-MM-DD HH: mm: ss

timestamp: 从1970.1.1到现在的毫秒数

year: 年份

4. null

2.3 创建表操作：

CREATE TABLE IF NOT EXISTS `xs`(
	`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8


-- 格式 create table if not exists `表名`(

--    `字段名` 列类型 [属性] [索引] [注释]  
--    []里的为可选项
-- )

-- 除最后一个语句，所有语句都要加逗号
-- AUTO_INCREMENT 表示自增
-- COMMENT 其后为注释
-- DEFAULT 设置默认值
-- PRIMARY KEY (`字段名`) 设置表的主键，主键不为空
-- ENGINE=INNODB 存储引擎为INNODB ,默认使用
-- CHARSET=utf8 设置字符集
-- int(4) 代表int型数据宽度为4，比如0001，1234

格式 create table if not exists `表名`(

`字段名` 列类型 [属性] [索引] [注释]
[]里的为可选项
)

对于上面格式里的索引：

        索引是一种数据库结构，可以加快数据查询速度。MySQL通常有两种查询方式，顺序查询和索引查询。顺序查询很容易理解，按表的顺序查询，索引查询则是先按索引列查找，在根据查找到的索引指针找到对应数据，两者相比，在存在大量数据时，索引查询效率更高。

        索引查询方式实现的前提是对表建立一个索引，在列上建立索引后，查询数据可直接根据该索引找到对应记录位置，再根据指针找到数据，也即索引存储了列数据值的指针。

        举例：在学生表table_xs中，对学生id建立索引，系统就会建立一张索引列到实际记录的映射表。当用户查询id时，则先在索引列中找到该记录，在通过映射表找到数据行并返回数据。

更多总结见6、索引。

        index和key都有索引的意思，但是自我简单粗暴的理解为key带其他词如（primary key）为同时具有constraint和index的意思，单独key和index差不多吧。。。

2.4 修改表操作（对表字段等属性操作，非数据）：

--student 表名， xs 表名， age 字段名

--修改表名
ALTER TABLE xs RENAME AS student;

-- 增加表的字段
ALTER TABLE student ADD age INT(3);

-- 修改表的字段(字段重命名，修改约束)
ALTER TABLE student MODIFY age VARCHAR(5); -- 修改约束 
ALTER TABLE student CHANGE age age1 INT(2); -- 字段重命名 age -> age1

-- 删除表的字段
ALTER TABLE student DROP age;  

--删除表
DROP TABLE IF EXISTS student;

3、数据管理部分：

3.1 外键（foreign key）

表间公共关键字在一个表中是主键，则这个公共数据管理关键字就是另一个关系的外键。外键作用是保持数据的一致性和完整性，控制存储在外键表中的数据约束。但很多时候都不建议用，因为会使很多操作复杂化，例如DELETE 或者UPDATE都必须考虑外键约束，很不方便。

-- 实现学生表和成绩表的外键关联

CREATE TABLE IF NOT EXISTS `xs`(
	`id` INT(4)	NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
	`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `cj`(
    `cjid` INT(4) PRIMARY KEY,
    `id` INT(4)	NOT NULL COMMENT '学号',
    `grade` INT(3) NOT NULL COMMENT '学生成绩',
    CONSTRAINT `FK_xs` FOREIGN KEY (`id`) REFERENCES `xs`(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 直接跟在列后面定义的约束为列级约束 eg: id int(4) primary key
-- 单独一行定义的为表级约束，
-- eg: constraint 约束名 foreign key (字段名（其他表的主键）) references 表名（主键名）

-- 如果创建表的时候没有外键关系，可如下定义外键
-- alter table `cj` add constraint `FK_xs` foreign key(`id`) references `xs`(`id`);

3.2 DML语言（Data Manipulation Language，数据操纵语言）

数据管理，数据存储用的语言, 增、删、改。

-- 插入 insert
-- INSERT INTO `student`([字段1,字段2..]) VALUES('值1','值2'..),[('值1','值2'..)..];
INSERT INTO `student`(`name`) VALUES ('张三');  
-- 插入单个字段值
INSERT INTO `student`(`name`, `sex`) VALUES ('张三', '男'), ('李四', '女');
-- 插入多个数据
INSERT INTO `student` VALUES('4','王五','男','2001-02-14','12345@qq.com');
-- 插入一条完整数据，省略字段，值完整且一一对应

-- 修改 update
-- UPDATE 表名 SET 字段1=值1, [字段2=值2...] WHERE 条件;
UPDATE `student` SET `name`='张六' WHERE id=1;                    -- where后为指定数据对象
UPDATE `student` SET `name`='张六';                               -- 无条件时修改表的所有数据
UPDATE `student` SET `name`='张一', `sex`='女' WHERE id = 1;      -- 同时修改多个属性值
UPDATE `student` SET `name`='张五' WHERE `name`='张一' AND `id`='1';  -- 多条件定位数据并修改
-- where的判断符号：=、<>或!= （不等于）、>、<、<=、>=、BETWEEN .. AND ..（区间）、AND、OR
 
-- 删除 delete
-- DELETE FROM `表名` [WHERE 条件];       
DELETE FROM `student`;                 -- 删除整张表的数据，尽量小心使用
DELETE FROM `student` WHERE id = 1;    -- 删除指定数据

-- 删除 truncate
TRUNCATE TABLE `student`;              -- 删除整张表

两种删除方式有不小区别：delete可以条件删除，truncate只能删除整个表； delete支持事务回滚，也就是删除后回滚可复原数据，但truncate不支持；delete删除后自增数据从表中出现过的最大的数据开始，重启数据库后有两种情况：INNODB引擎从1开始自增，MYISAM 引擎继续从上一个子增量开始，而truncate 重新设置自增列，计数器会归零

4、DQL(Data Query Language, 数据查询语言)

数据查询，查。

4.1 基础查询

-- SELECT 查询列表 FROM 表名

SELECT * FROM student;
-- 查询全部学生

SELECT `name`,`sex` FROM student;
-- 查询指定字段

SELECT `name` AS 姓名, `sex` AS 性别 FROM student AS 学生表;
-- AS 后为别名，字段、表均可

SELECT CONCAT('姓名', `name`) AS 姓名 FROM student;
-- CONCAT函数起连接作用，只要其中一个是NULL,那么将返回NULL

SELECT VERSION();
-- 查询系统版本

SELECT 3*4-5 AS 计算结果;
-- 计算表达式

SELECT @@auto_increment_increment;
-- 查询自增变量

SELECT DISTINCT `姓名` FROM student;
-- distinct 重复数据去重

4.2 条件查询

-- SELECT 查询列表 FROM 表名 WHERE 筛选条件;
-- where的判断符号：=、<>或!= （不等于）、>、<、<=、>=、BETWEEN ..AND ..（区间）、AND、OR、IN

SELECT `name`, `sex` FROM student WHERE `id` >= 1 AND `id` <= 4; -- 查询id在[1,4]之间的数据

SELECT `name`, `sex` FROM student WHERE `id` BETWEEN 1 AND 4;    -- 查询id在[1,4]之间的数据

SELECT `name`, `sex` FROM student WHERE `id` >= 1 && `id` <= 4;  -- 查询id在[1,4]之间的数据

SELECT `name`, `sex` FROM student WHERE `id` IN (2,3);           -- 查询id为2，3的学生

SELECT `name`, `sex` FROM student WHERE `id` != 3;               -- 查询除id为3的剩余学生

SELECT `name`, `sex` FROM student WHERE NOT `id` = 3;            -- 查询除id为3的剩余学生

SELECT `name`, `sex` FROM student WHERE `id` <> 3;               -- 查询除id为3的剩余学生

SELECT `name`, `sex` FROM student WHERE `name` LIKE '%张%';      -- 查询姓名有张的学生

SELECT `name`, `sex` FROM student WHERE `name` LIKE '%张_'; -- 查询姓名倒数第二个字为张的学生

4.3 分组查询

-- SELECT 分组函数，分组后的字段
-- FROM 表
-- [WHERE 筛选条件]
-- GROUP BY 分组的字段
-- [HAVING 分组后的筛选]
-- [ORDER BY 排序列表]

SELECT SubjectName AS 课程名, AVG(`StudentGrade`) AS 均分, MAX(`StudentGrade`) AS 最高分, MIN(`StudentGrade`) AS 最低分
FROM `grade` g 
INNER JOIN `subject` s
ON g.SubjectNo = s.SubjectNo
GROUP BY g.SubjectNo
HAVING AVG(StudentGrade) >80;

-- 根据课程进行分组，查询平均分大于80的科目的平均分、最高分和最低分
-- INNER JOIN..ON..为内连接，在4.4连接查询总结

对查询结果进行分组的目的是细化组函数的作用对象。如果未对查询结果分组，组函数将作用于整个查询结果，即整个查询结果只有一个函数值。否则组函数将作用于每个组，每组都有一个相应的函数值。

        这有什么问题呢？看这样一个例子，

SELECT SubjectName , AVG(`StudentGrade`) , FROM `grade`；

如果不使用上述代码段里的方式，就会报错，原因就在于AVG()结果只有一个，但课程名有多个，不能做到一一对应。

        现使用分组查询方式后，首先会根据分组字段分组，如下图，组函数将作用于每个组，这样即可进行一对一的结果输出。

        where和having的区别：where是分组前进行筛选，也即对于未分组的表进行筛选，where位置在group by 的前面。而having是分组后进行筛选，即对分组的结果进行再筛选，位置在group by的后面。

常见分组函数（聚合函数或组函数）：

        sum()求和、avg()求均值、min()求最小值、max()求最大值、count()计算非空数据个数。

4.4 连接查询

数据库中用join操作的被称之为连接，可以将多个表的数据通过连接条件连接起来，合并在一起作为结果集返回给客户端。

        现有两个表：

        成绩表：



        课程表：



         通过如下方式：


-- 内连接
select * 
from `subject` inner join `grade` 
on subject.SubjectNo = grade.SubjectNo;

-- 自然连接
select * 
from `subject` natural join `grade`;

        两种连接方式得到的结果如下：

        结果一为内连接：

        结果二为自然连接：

4.4.1连接关键字

   一般用到两个关键字：on, using。

        on子句的语法格式为：

        table1.column_name = table2.column_name。
        当模式设计对连接表的列采用了相同的命名样式时，就可以使用 using语法来简化 on 语法，格式为：using(column_name)。

select * 
from `subject` inner join `grade` 
on subject.SubjectNo = grade.SubjectNo;

select * from `subject` inner join `grade` using(SubjectNo);

select *
from `subject` natural join `grade`;

查了很多资料，说上面这两种方式结果一致，但我实验得到结果反而是第二种和第三种方式结果一致。。。

4.4.2 连接类型

内连接：

1. 内连接和交叉连接

语法格式：A join | inner jin | cross join B

A和B表满足连接条件记录的交集，若没有连接条件则连接结果是A和B的笛卡尔积

笛卡尔积：设A的度为m，B的度为n，两者的笛卡尔积就是m+n列的集合，若A有r个元组，B有s个元组，则笛卡尔积有r×s个元组

如下图：

MySql中，cross join、inner join、join所实现的功能是一样的，属于等价关系；

2.隐式连接

语法格式：from A, B, C

相当于无法使用on和using的join；逗号是隐式连接运算符；

外连接：

1.左外连接

语法格式：A left join B

左表数据全部保留，右表满足连接条件的记录显示，不满足条件的记录则为null;

2.右外连接

语法格式：A right join B

右表数据全部保留，左表满足条件的记录显示，不满足条件的记录全为null；

3.全外连接

MySQL不支持全外连接，但可通过左右外连接合并得到。

eg：

select * from A left join B on A.name = B.name

union                          -- 会自动去重

select * from A left join B on A.name = B.name

自然连接：

语法格式：A natural join B | A natural left join B | A natural right join B

相当于不能指定连接条件的连接，Mysql会使用左右表内相同的名字和类型的字段作为连接条件；自然连接也分自然内连接，左外连接，右外连接，只是连接条件由MySQL自行判定。

连接中的on和where的区别：

先执行on再执行where；on是建立关联关系，先根据on里的条件建立关联关系，没有where时输出on关联的结果，有where时再用where里的条件对关联关系进行筛选，将筛选结果输出。

4.5 排序和分页

排序：

-- 语法格式
-- select 查询列表
-- from 表
-- where 筛选条件
-- order by 排序列表 asc/desc
-- asc：升序，若不写默认升序  desc：降序

select *
from subject
inner join grade 
on subject.SubjectNo = grade.SubjectNo
order by `StudentGrade` asc;

分页：

-- select 查询列表
-- from 表
-- limit offset, pagesize;

-- offset代表的是起始的条目索引，默认从0开始
-- size代表的是显示的条目数
-- offset=(n-1)*pagesize

-- 第n页  limit (n-1)*pagesize, pagesize
-- n为当前页
-- pagesize当前页大小
-- (n-1)*pagesize 页面起始值
-- limit n 表示从0到n的页面

4.6 子查询

where里再插入个子查询语句

-- 查询‘高数’所有考试结果并按成绩降序排列

-- 法一
select `StudentNo`, grade.`SubjectNo`, `StudentGrade` 
from grade
inner join `subject`
on grade.SubjectNo=subject.SubjectNo
where SubjectName='高数'
order by StudentGrade desc;
-- 通过on条件建立grade和subject连接后，用where条件查询高数信息；
-- 查询字段里的grade是因为内连接结果得出的表格有两个SubjectNo，不指定会报错


-- 法二
select StudentNo, SubjectNo, StudentGrade
from grade
where SubjectNo=(
    select SubjectNo from `subject`
    where SubjectName='高数'
)
-- 先执行（）里的子查询，得到subject表高数元组的SubjectNo再与grade表的SubjectNo比较得出结果

4.7 MySQL常用函数

-- 数学运算函数
select abs(-1);          -- 取绝对值
select ceil(1.3);        -- 上取整
select ceiling(1.3);     -- 上取整
select rand();           -- 返回0~1之间的一个随机数
select sign(-10);        -- 正数返回1；负数返回-1；0返回0


-- 字符串函数
select char_length('心有猛虎细嗅蔷薇');           -- 字符串长度
select concat('心有猛虎', '，', '细嗅蔷薇');      -- 字符串拼接
select insert('心有爱',3,1, '猛虎');        
-- insert(str, pos, len, newstr)，str的第pos个字符开始替换len个字符，换成newstr
-- 上述的第3个字符，长度为1，则只有一个‘爱’，将‘爱’换成‘猛虎’，输出心有猛虎
select upper('miss');                            -- 转大写
select lower('MISS');                            -- 转小写
select instr('miss', 's');                       -- 返回第一次出现字符串索引的位置
select replace('miss you', 'miss', 'like');      -- 替换miss为like
select reverse('olleh');                         -- 字符串反转


-- 时间日期函数
select current_date();         -- 获取当前日期
select curdate();              -- 获取当前日期
select now();                  -- 获取当前时间
select localtime();            -- 本地时间
select sysdate();              -- 系统时间
select year(now());            -- 当前年份,下同
select month(now());
select day(now());           
select hour(now()); 
select minute(now());
select second(now());   
               

-- 系统信息
select system_user();
select user();
select version();

5、事务

详细讲解：https://blog.youkuaiyun.com/wang_luwei/article/details/119619105

        事务处理可以用来维护数据库的完整性，保证一批SQL语句要么全部执行，要么全部不执行，上面提到过MySQL中只有使用了INNODB数据库引擎的数据库或表才支持事务，可以用来管理insert、update、delete语句。

事务满足的4个条件（ACID）：

原子性（Atomicity，不可分割性 )：一个事务中的所有操作，要么全部完成，要么全部不完成。如果事务在处理过程中发生出错，会被回滚（Rollback）到事务开始前的状态，就像该事务没有执行过一样。

一致性（Consistency）：在事务开始前和结束后，数据库完整性未被破坏。写入的数据必须完全符合所有预设规则，这包括资料的精确度、串联性和后续数据库可以自发性地完成预定的工作。

隔离性（Isolation，独立性）：数据库允许多个并发事务同时进行，独立性可以防止多个事务并发执行时由于交叉执行而导致数据不一致。

持久性（Durability）：事务处理结束后，对数据的修改是永久的，即便系统故障也不回丢失。

MySQL默认事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。故要显式开启一个事务须使用BEGIN或START TRANSACTION，或者执行命令set autocommit=0，用来禁止使用当前会话的自动提交。

事务控制语句：

  begin或start transaction显式地开启一个事务；

commit与commit work等价。commit会提交事务，并使已对数据库进行的所有修改成为永久性的；

rollback。事务回滚，撤销所有未提交的修改；

savepoint identifier，创建一个保存点，一个事务中可以有多个；

release savepoint identifier 删除一个事务的保存点；

rollback to identifier 把事务回滚到标记点；

set transaction用来设置事务的隔离级别。

两种主要的事务处理方式：

法一：

        begin 开始一个事务

rollback 事务回滚

commit 事务确认

法二：

set autocommit=0 禁止自动提交

        set autocommit=1 开启自动提交

begin;
insert into student(name) vlaues ('张三');
insert into student(name) vlaues ('李四');
-- rollback;    若回滚，上面插入的等于没插入
commit;     -- 提交后，插入数据永久插入

6.索引

6.1索引分类

主键索引（primary key）：

唯一标识，主键不可重复不为空，确保数据记录的唯一性，一般表都要有个主键。

普通索引（key/index）：

纯粹的索引，默认的可快速定位数据。不宜添加过多索引，影响数据插入删除和修改操作。

create index indexname on tablename (columename);  -- 直接创建索引

-- 建表时指定索引格式
create table tablename(
    ....
    index [indexname] (columename)
)

-- 修改表结构添加索引
alter table tablename add index indexname(columename);

唯一索引（unique key）

普通索引基础上加上值必须唯一但允许有空值的约束，可以有多个。

create unique index indexname on tablename(columename);

create table tablename(
    ......
    unique index [indexname] (columename)
);

alter table tablename add unique index [indexname] (columename);

全文索引（fulltext）

快速定位特定数据（eg:百度搜索），在特定数据库引擎才有：MyISAM，只能用于char,varchar,text数据类型，适合大型数据集。在检索长文本的时候，效果最好，短文本建议使用Index。

空间索引（spatial）

6.2 索引使用

创建索引上述已说明。

索引删除：

drop index 索引名 on 表名； -- 删除索引

alter table 表名 drop primary key; -- 删除主键索引

显示索引信息：

show index from 表名;

explain分析sql执行情况

explain select * from student; -- 非全文索引

explain select * from student where match(StudentName) against('d'); -- 全文索引

6.3 索引原则