嘘~ 正在从服务器偷取页面 . . .

MySQL 学习


0. 写在前面

​ 前四节课分别由我们Java组的组长、后端可靠大哥哥和协草带我们初识Java SE。这次由我带大家入门MySQL的基础,这节课我们主要讲一些MySQL的基本语法和CRUD(Creating、Retrieving、Updating、Deleting)。之后一些具体的东西还希望大家课后自己复习和掌握,具体的学习网站和推荐书籍会放在课件的最后。

1. 引入

​ 说到数据库,其实就可以说到前后端的本质问题。后端的本质其实就是对数据库进行“CRUD”,其他的东西都是让我们更好的“CRUD”。前端的本质也就是把后端传递的数据库信息显示出来,把数据传给后端。

前后端交互图

1.1 什么是数据库

​ 数据库(DB DataBase),顾名思义就是存储数据和管理数据的东西。在数据库之前,其实有很多东西都是作为存储数据的媒介,比如Excel,txt,io流,甚至是存储在变量中。数据库是数据仓库,它是一个软件,安装在操作系统之上(Windows、Linux、MacOs)。可以存储大量的数据,500万以下都没有什么问题,超过500万可能需要做一些索引优化。

1.2 为什么使用数据库

  • 数据库可以结构化存储大量的数据信息,方便用户进行有效的检索和访问;
    数据库可以对数据进行分类保存,并且能够提供快速的查询。例如,我们平时使用百度搜索内容时,百度也是基于数据库和数据分类技术来达到快速搜索的目的。

  • 数据库可以有效地保持数据信息的一致性、完整性、降低数据冗余;

  • 数据库可以满足应用的共享和安全方面的要求,把数据放在数据库中在很多情况下也是出于安全的考虑;
    例如,如果把所有员工信息和工资数据都放在磁盘文件上,则工资的保密性就无从谈起。如果把员工信息和工资数据放在数据库中,就可以只允许查询和修改员工信息,而工资信息只允许指定人(如财务人员)查看,从而保证数据的安全性。

  • 数据库技术能够方便智能化地分析,产生新的有用信息。

1.3 DBMS分类

​ DBMS即数据库管理系统(Database Management System),是操作和管理数据库的软件,用于建立、使用和维护数据库。

​ 我们最为熟知的MySQL就是典型的关系型数据库管理系统。

1.3.1 关系型数据库管理系统:(SQL)

​ 关系型数据库是指采用了关系模型来组织数据的数据库。简单来说,关系模式就是一个二维表格模型,可以类比Excel。

​ 主要代表:MS SQL Server、Oracle、MySQL、PostgreSQL 等等。

​ 优点:容易理解、使用方便、易于维护(方便我们进行查重等操作) 。

1.3.2 非关系型数据库管理系统:(NoSQL)

No SQL Not Only SQL

​ NoSQL提出了另一种理念,以键值来存储,且结构不稳定,每一个元组都可以有不一样的字段,这种就不会局限于固定的结构,可以减少一些时间和空间的开销。可以类比Java集合中的Map。

​ 主要代表:MongoDB(大数据处理),Redis(面向高性能并发读写)等等。

1.4 为什么使用MySQL

  • 上手简单、容易安装;
  • MySQL 开放源码,一般可以免费使用。很多企业一般都会重写 MySQL中的插件,从而使其效率大大提高;
  • 性能高,面对500万以下的数据量绰绰有余;
  • 安全性高,可依赖。

2. 前置知识

2.1 连接使用

2.1.1 简单控制台命令

​ 上课之前已经让你们安装了MysQL和Navicat,Navicat其实是一个可视化管理数据库的软件,这样你就不需要使用丑陋的Windows Terminal和MySQL打交道了,但是这里我们还是简单提几句终端的指令。

  • 打开window终端。先输入mysql --version确认自己安装正确。
  • 之后我们使用mysql -u root -p准备连接(-u代表username,-p表示password),之后输入安装时设定的root密码便可以成功进入mysql的操作页面。
查看所有数据库名称:SHOW DATABASES;
切换数据库:USE mydb1;
创建数据库:CREATE DATABASE [IF NOT EXISTS] mydb1;
删除数据库:DROP DATABASE [IF EXISTS] mydb1;
修改数据库编码:ALTER DATABASE mydb1 CHARACTER SET utf8;
查看当前数据库中所有表名称:SHOW TABLES;

​ P.S. 这些指令都需要使用;进行结尾,不是回车。

2.1.2 使用navicat连接

​ 使用可爱的navicat之后,我们就可以直接使用可视化界面进行管理。

​ 连接名随意,是方便我们区分的东西。主机就相当于我们的IP,如果是连接本地的数据库就是localhost或者127.0.0.1。端口使用的是MySQL的默认端口3306,用户名和密码就是最开始安装MySQL的时候设定的值。

使用navicat连接MySQL

​ 这样,我们对MySQL所有的管理都可以直接通过非常简单的“点击”完成,本质上我们每一次的操作,都是一行SQL命令。等一下我们的授课内容全都基于navicat进行讲解。

2.2 MySQL 基本内容

  • 这个时候,可能有小朋友要问了,为什么创建一些字段的时候我们需要加上斜单引号呢?例如:
create database `teaching`;
create table `student`;

​ 其实这个并不是必要的,添加斜单引号的时候是防止我们添加的字段名、表名和字段名跟MySQL自带的方法名冲突。这里我们用之前我出的一个招新题为例。

CREATE TABLE `student`  (
   `id` int NOT NULL AUTO_INCREMENT,
   `name` varchar(32) NOT NULL,
   `position` varchar(32) NOT NULL,
   `like` varchar(32) NOT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE = InnoDB CHARACTER SET = utf8;

​ 这个时候,like这个字段名就和MySQL中模糊匹配的关键字like冲突了,如果不使用斜引号可能会出问题。

  • 另外还有一个问题,如果我将数据库表中的一个字段或者把命令行中的一个命令的字段改成大写,再执行之前的操作会不会有什么影响?

​ 答案是不会。在MySQL中,数据库与data目录中的目录相对应。数据库中的每个表都对应于数据库目录中的至少一个文件(可能是多个文件,具体取决于存储引擎)。因此,操作系统的大小写是否敏感决定了数据库大小写是否敏感,而Windows系统是对大小写不敏感的,Linux系统对大小写敏感默认情况下,库表名在Windows系统下是不区分大小写的,而在Linux系统下是区分大小写的。列名,索引名,存储过程、函数及事件名称在任何操作系统下都不区分大小写,列别名也不区分大小写。

​ 字段值的大小写则是由mysql的校对规则来控制。一般而言,校对规则以其相关的字符集名开始,通常包括一个语言名,并且以_ci(大小写不敏感)、_cs(大小写敏感)或者_bin(二元)结束。

​ 就比如我们最常见使用的utf8字符集:

  • utf8_bin:utf8_bin将字符串中的每一个字符用二进制数据存储,区分大小写;
  • utf8_general_ci:utf8_genera_ci不区分大小写,ci为case insensitive的缩写,即大小写不敏感;
  • utf8_general_cs:utf8_general_cs区分大小写,cs为case sensitive的缩写,即大小写敏感。

​ P.S. MySQL默认的字符集是不支持中文的,所以我们手动修改为utf8的编码格式,建议直接在创建表的时候就进行修改。

2.2.1 数据类型

(a) 数值
数据类型 字节数 备注
tinyint 1
smallint 2
mediumint 3
int 4 最常用的int类型
bigint 8
float 4
double 8 精度更大
decimal 字符串形式的浮点数,一般在金融计算中使用
(b) 字符串
数据类型 字符串长度 备注
char 固定长度大小0~255
varchar 可变字符串0~65535 可以指定长度大小
text 文本串2^16-1 保存大文本
(c) 时间日期
数据类型 时间格式
date YYYY-MM-DD,日期格式
time HH:MM:SS,时间格式
datetime YYYY-MM-DD HH:MM:SS,最常用的时间格式

2.2.2 字段属性

​ 每个字段除了类型之外,还有一些字段可能会有的各种属性。可以直接在navicat的可视化界面中直接设定这些属性值。

(a) 主键

​ 主键是一个列或者多个列的组合(主键可以有多个),其值能唯一地标识表中的每一行。也就是说,我们在设计表时,需要有一个东西能够让每一行唯一确定,所以主键是强制要被设定的。并且,设定主键之后,主键是不能为null值的。

​ 也就是说,如果我们有两个相同的主键,会报错误,这样保证数据的唯一性。一般在开发中,我们基本会将id设置为主键,之后给一个自动递增防止重复。

(b) 默认值

​ 默认值直观易懂,就是每个字段在初始化时如果没有赋值,那么就会给予一个设定的默认值。

​ P.S. 需要注意的是,navicat中的默认的默认值是null,空字符串则是EMPTY STRING

(c) 自动递增、无符号、填充零

​ 自动递增:每次增加一条==数据记录==时都会在原有的基础上+1。(默认)(通常用于设置唯一主键。当然,我们可以在设置中设定初始值的起始值和步长)

在这里设置

​ 无符号:相当于unsigned,声明这一列的数据不能是负数。

​ 填充零:字面意思,不够的位数使用0来填充,比如你使用了一个长度为3的int类型的字段。前面的空位会用0自动填充。

​ P.S. 整形的长度并不会限制存储的数字范围,**”整型”的长度实际上可以理解为”显示长度”**,你们使用填充零之后就会有更好的理解。你们可以自己实验一下,及时在“长度”设定为3的整形字段中输入11111也能够成功输入。这就是为什么你在创建int类型的字段的时候,默认不能更改表中长度。

3. CRUD

​ MySQL的DML(数据库操作语言)遵守SQL的DML规范,主要就是对数据库里的数据增删改查操作。

​ 如果你使用可爱的navicat,所有的增删改查操作都可以像Excel一样,直接使用可视化操作。这些操作的对应的SQL语句都可以直接在SQL预览中看到。当然,不能说你有可视化界面我们就可以不用去学MySQL的语句。我们这节课只是皮毛的东西,之后的包括事务、索引优化、三大范式等等,都需要你熟练掌握MySQL的基本的CRUD。不过不用太担心,你们之后的开发中会经常写到这些代码,多练就能熟练。

3.1 Select

​ data表示我们数据库中表的字段名称,我们可以写一个也可以写多个,这里就拿我们刚建好的student表举例。

select <data> from <tableName>

select `name`, `like` from `student`

#我们可以使用*代表所有的字段
select * from `student`

​ 刚刚演示的是最基本的语法,当然我们在正常使用中肯定不会使用到所有的语句。所以我们需要使用一些条件语句帮助我们过滤那些不需要的数据值。

  • 在索引的前面使用 distinct 使搜索时可以自动规避重复的值;

  • 在最后时候使用 limit 限制输出的行数(==行和数组的下标一样,从0开始==),limit 5表示不超过5。如果使用limit 5, 5则表示从下标为5的数据开始,向后查询5个数据;

  • 可以通过 order by 指定输出时数据的顺序问题。(默认的排序方式时升序,可以在最后加上desc实现降序排序)。

CREATE TABLE `data` (
  `id` int NOT NULL AUTO_INCREMENT,
  `number` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

SELECT DISTINCT `number` FROM `data`;
#并且你们如果使用distinct关键字,必须放在最前面

SELECT * FROM `data` LIMIT 3;
#表示查询前三个数据

SELECT * FROM `data` LIMIT 3 OFFSET 1;
#这就是一个简单的分页查询,offset表示第几组(下标从0开始)

SELECT * FROM `data` LIMIT 3, 5;
#表示从下标为3的数据开始(也就是第四行),往后查寻5个数据

SELECT * FROM `data` order by `number` (ASC);
#ASC表示正序排序,默认就是正序,可以不写

SELECT * FROM `data` order by `number` DESC;
#DESC表示倒序

3.1.1 条件查询

​ 通常我们一般只会检索大量数据中特定值的内容,这个时候我们使用where语句,这个语句很像我们程序中的if判断,表示对某个或者某几个字段进行判断。

  • = 等于;
  • <> /!= 这两个都是不等于;
  • between 两个值之间,是一个闭区间;
  • 使用and和or进行连接,如果用得比较多还是建议用圆括号括起来;
  • in 表示取括号内字段对应的值。
SELECT * FROM student WHERE `name` = 'cxy';
#在一般的项目开发中,一般都是前端传一个特定的值给我们,然后我们根据这个值返回数据库中的数据。所以这个语句使用非常频繁

SELECT * FROM `data` WHERE `number` BETWEEN 1 AND 100 ORDER BY `number` DESC;
#使用between and 进行一个区间内的查询,并且进行排序

SELECT * FROM student WHERE `name` = 'cxy' AND `age` = 18;
#使用and进行多个条件约束

SELECT * FROM `data` WHERE `number` IN('1', '3');
#选择表中,number值为1或者3的数据

3.1.2 模糊查询

​ 这个时候就不是使用 = 了,而是通过 like 表示 sql 语句使用通配符进行数据索引的过滤。

​ 最常见的就是 % 通配符,这种是通过特定位置出现的字符进行匹配得出最终的结果。

  • jet% 就表示以 jet 开头的字段;

  • %jet% 表示在任意地方的存在jet字段的数据;

  • e%f 表示以 e 开头并以 f 结尾的字段。

SELECT * FROM student WHERE `like` LIKE '乐%';

SELECT * FROM student WHERE `like` LIKE '%乐%';
#只要字段中有这个值,就匹配出来

​ 另一个有用的通配符是_,但是它只能匹配单个字符,而%是可以匹配多个字符的

SELECT * FROM student WHERE `like` LIKE '_ nice';
#相当于用_代替任意一个字符

SELECT * FROM student WHERE `like` LIKE '%nice';

3.1.3 分组查询

# 案例1:查询一个公司每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;
 
 
# 案例2:查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

# 在分组查询后用having关键词取代where
# 案例6:查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP By job_id
HAVING MAX(salary)>12000;
 
# 案例7:查询领导编号>102的每个领导手下的员工最低工资>5000的领导编号,以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000

3.1.4 子查询

​ 我们可以在select里嵌套一个select,这就是所谓的子查询。

​ 当然,有些子查询,我们也可以使用联表查询的形式。只不过看个人的使用。

(a) 放在where后面
SELECT * FROM student WHERE age >
(SELECT age FROM student WHERE uid = 5);
#查询age大于uid=5的age的数据值

SELECT * FROM student WHERE age IN
(SELECT DISTINCT age FROM student WHERE `name` = 'ckt');
#使用子查询的好处就是我们可以不用使用硬编码,可以使用可变的数据替换

SELECT id FROM `data` WHERE number =
(SELECT MAX(number) FROM `data`);

# 案例2:返回其它工种中比job_id为`IT_PROG`工种所有员工的工资都要低的员工的员工号、姓名
select id,name
from employees
where salary < all(  # min
    select distinct salary 
    from employees
    where job_id = 'IT_PROG'
) and job_id != 'IT_PROG';
#all表示所有的
#除了all之外,还有上面使用的in、not in、any(some)
(b) 放from后面

​ 将select返回的结果当作表(数据源)来使用,必须起别名。

# 案例1、查询每个部门的平均工资的工资等级
select ag_dep.*, grade.level
from(
   select avg(salary) ag ,department_id
   from employees
   group by department_id
) ag_dep
inner join job_grades g
on ag_dep.ag between lowest_sal and highest_sal;

3.1.5 笛卡尔乘积查询

​ 在select操作中直接使用from连结多个表,这样的弊端就是结果就会变成 x * y,实现了一个超级加倍。

SELECT e.`name`, e.location_id, e.company_id, c.`name` `company`
FROM employee e, company c
WHERE e.company_id = c.uid;
#MySQL中,我们可以直接在字段或者表名后面对其进行重新命名
#字段可以像Java对象的属性一样,通过.进行调用

​ 从这个糟糕的结果你可以看出来,如果使用这种查询方式,那么效率会非常低下。

糟糕的结果

3.1.6 连接查询

​ 连接查询是另一种类型的多表查询。连接查询对多个表进行JOIN运算,简单地说,就是先确定一个主表作为结果集,然后,把其他表的行有选择性地“连接”在主表结果集上。这种思想明显比之前那种全部拼接在一起的高明得多。

  • 内连接:inner join 需要两个表都有这个信息;
  • 外连接:right(left) join 左(右)作为主表。
SELECT e.`name`, e.location_id, e.company_id, c.`name` company_name
FROM employee e
INNER JOIN company c
ON e.company_id = c.uid;

SELECT e.`name`, e.location_id, e.company_id, c.`name` company_name
FROM employee e
RIGHT JOIN company c
ON e.company_id = c.uid;

3.2 Insert

insert into <tableName> (<content>) values (<content>)

INSERT INTO `student`(`name`, `age`, `scores`) VALUES
('ckt', 18, 34.2),
('ooo', 10, 30.4);
#执行这条指令之后,我们就可以向数据库中添加这些数据数据,我们可以添加多行数据
#因为我们的主键设置了自动递增,所以即使添加的命令中没有这个字段,数据库中会对其自动递增

3.3 update

update <tableName> set <data> = <content>

UPDATE student SET `name` = '嘉然' WHERE `name` = 'cxy';
#将表中name字段为cxy的数据替换成嘉然

3.4 delete

delete from <tableName>

DELETE FROM student WHERE `name` = 'ccc';

#还有一个命令是truncate,和delete的区别在于它可以把自增的计数器置0
#使用这个我们就可以删库跑路啦~
TRUNCATE `user`

4. 例题

​ 看着很熟悉嘛?没错,这就是我出的应应招新题中的SQL题。鉴于你们很多人都没做,这次我们就把这个题目当作课堂习题。

​ 最近天下都不太平,又是外卖被偷风波又是离奇失踪案件。可是最近,发生一件大事,平安学长的手办老婆被偷了!他悲愤欲绝,发誓一定要找出凶手,他已经通过监控将嫌疑犯锁定在科协内部。但是科协人员混杂,一般调查难以进行下去,好在有人已经将科协全部人员整合到一个MySQL数据库表中。可问题来了,这个学长对SQL操作并不是很熟练。请你运用你熟练的SQL知识,帮助这位可怜的学长,找到嫌犯并将其绳之以法。

​ 下面是需要使用的表的数据结构( SQL 源码)

CREATE TABLE `student`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `position` varchar(32) NOT NULL,
  `like` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARACTER SET = utf8;
 
INSERT INTO `student` ( `id`, `name`, `position`, `like` ) VALUES
( 1, '金老板', 'A', '想当嘉然小姐的狗' ),
( 2, '栋栋子', 'A', '夸赞学弟好强啊' ),
( 3, '夏桑', 'B', '嘉然我真的好喜欢你' ),
( 4, '提莫', 'B', '种蘑菇' ),
( 5, '陈三金', 'B', '女装引流' ),
( 6, '金坷垃', 'B', '吸收氮磷钾' ),
( 7, '祖师爷', 'C', '南邮还是宁最卷' ),
( 8, '杂鱼', 'C', '欺负cxy' );
  1. 通过几天的不懈排查,有知情人士透露,当时有人正使用平安学长的电脑刷着 B 站。当时传来一阵“嘉然,我真的好喜欢你啊。为了你,我要听《猫中毒》”“呜呜,然然,我的然然”。可以确定嫌疑人是嘉心糖,他决定趁热打铁继续调查。所以,在他忙着进行接下来的调查的时候,请你使用SQL知识帮他解决问题。
    要求:用一条SQL语句把兴趣中提到嘉然的同学都搜索出来。

  2. 但是很快疑点便浮出水面,无奈的平安学长只好去请教 F 侦探,在侦探的帮助下。很快就找到了相关的线索,但是还不能进行确认,所以侦探 F 为了比对证据,需要你查找出部分科协人员的名单,并且需要对名字进行排序。“真実はいつもひとつ”,F 丢下这句话后就走了,请你运用你熟练的 SQL 知识解决这个问题吧。
    要求:用一条SQL语句对姓名进行排序并输出倒数三个人的名字(注意,F 侦探只需要名字)。

  3. 最后真相公之于众,原来是两位金同学联合偷走了平安学长的老婆。F 侦探觉得以平安学长的脾气,可能会把那两个人给鲨了,所以想请你根据科协公约审判这两个人的罪行,确保公平公正。(科协公约已经通过下表的形式给出)请你使用 SQL 的 连接查询 知识,给这两位同学处于恰当的惩罚。除了连接查询之外,还有一种多表查询的方式为笛卡尔乘积式查询,但是 F 侦探并不建议你使用这种方式,你能说说为什么嘛。

CREATE TABLE `crime`  (
   `punish_id` int NOT NULL AUTO_INCREMENT,
   `punishment` varchar(64) NOT NULL,
   `position` varchar(32) NOT NULL,
   PRIMARY KEY (`punish_id`)
 ) ENGINE = InnoDB CHARACTER SET = utf8;
 
 INSERT INTO `crime` VALUES  
 (1, '禁止膜拜 JC', 'A'),
 (2, '不准看 AS', 'B'),
 (3, '打扫大活一个星期', 'C'),
 (4, '自裁', 'A'),
 (5, '拿来吧你', 'B'),
 (6, '宵禁', 'C'),
 (7, '异端教徒审判', 'A');

答案:

SELECT * FROM student WHERE `LIKE` LIKE '%嘉然%';

SELECT `name` FROM student ORDER BY `name` DESC LIMIT 3;

 SELECT 
     student.`name`,
     student.position,
     crime.punishment
 FROM student
 INNER JOIN crime
 ON student.position = crime.position
 WHERE (student.position = 'A' OR student.position = 'B')
 AND student.`name` LIKE '金%';

5. MySQL常用函数

5.1 字符函数

函数 描述
CONCAT(s1,s2…sn) 字符串 s1,s2 等多个字符串合并为一个字符串
INSERT(s1,x,len,s2) 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串
LOWER(s) 将字符串 s 的所有字母变成小写字母
REPLACE(s,s1,s2) 将字符串 s2 替代字符串 s 中的字符串 s1
REVERSE(s) 将字符串s的顺序反过来
TRIM(s) 去掉字符串 s 开始和结尾处的空格
SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;

SELECT INSERT("google.com", 1, 6, "runoob");  -- 输出:runoob.com P.S. 下标从1开始(insert是函数不是SQL命令)

SELECT REPLACE('abc','a','x') --xbc

SELECT REVERSE('abc') -- cba

SELECT TRIM("    RUNOOB     ") AS LeftTrimmedString;-- RUNOOB

5.2 数字函数

函数 描述
AVG(expression) 返回一个表达式的平均值,expression 是一个字段
COUNT(expression) 返回查询的记录总数,expression 参数是一个字段或者 * 号
MAX(expression) 返回字段 expression 中的最大值
RAND() 返回 0 到 1 的随机数
ROUND(x) 返回离 x 最近的整数
SQRT(x) 返回x的平方根
SUM(expression) 返回指定字段的总和
SELECT AVG(Price) AS AveragePrice FROM Products;

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;

SELECT MAX(Price) AS LargestPrice FROM Products;

SELECT RAND() --0.93099315644334

SELECT ROUND(1.23456) --1

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

5.3 日期函数

函数 描述
CURTIME() 返回当前时间
DATE() 从日期或日期时间表达式中提取日期值
LOCALTIME() 返回当前日期和时间
NOW() 返回当前日期和时间
SELECT CURTIME();
-> 19:59:02

SELECT DATE("2017-06-15");    
-> 2017-06-15

SELECT LOCALTIME()
-> 2018-09-19 20:57:43

SELECT NOW()
-> 2018-09-19 20:57:43

5.4 实践使用

现在你们就知道我们使用 group by 的目的了,就是为了对设定的列进行使用函数进行计算。

要注意的一点是,如果需要在 group by 中进行条件判断,需要使用 having。

select `job_name`, count(`name`) as `all_nums`
from `employees`
group by `job_id`
having `salary` > 12000;
#统计employees表中不同工种中薪资大于12000的员工个数

6. MySQL union

UNION 语句:用于将不同表中相同列中查询的数据展示出来;(不包括重复数据)

UNION ALL 语句:用于将不同表中相同列中查询的数据展示出来。(包括重复数据)

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;
-- 从两个表中查询不同国家的列

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

7. MySQL 事务

MySQL 的事务简要来说就是,要么都成功,要么都失败。

这就保证我们在插入一个表失败的时候,不会影响到其他表。因为MySQL有自动提交模式(即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION,或者执行命令 SET AUTOCOMMIT=0,用来禁止使用当前会话的自动提交)。

7.1 ACID

  • 原子性:一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。事务在执行过程中发生错误,会被回滚(Rollback)到事务开始前的状态,就像这个事务从来没有执行过一样;
  • 一致性:在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作;
  • 隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别,包括读未提交(Read uncommitted)、读提交(read committed)、可重复读(repeatable read)和串行化(Serializable);
  • 持久性:事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。
set autocommit = 0 -- 关闭自动提交
start transaction -- 标记一个事务的开始
insert into `student`(`name`, `age`) values('cxy', 18)
insert into `employee`(`name`, `job`, `location`) values('ckt', 20, '南京市')
commit -- 提交:持久化(成功)
rollback
set autocommit = 1 -- 开启自动提交

savepoint 保存点名 -- 设置一个事务的保存点
rollback to savepoint 保存点名
release savepoint -- 撤销保存点

7.2 可能问题

  • 脏读:一个事务在执行的过程中读取到了其他事务还没有提交的数据;
  • 读已提交:一个事务操作过程中可以读取到其他事务已经提交的数据;
  • 不可重复读:在同一事务中,多次读取同一数据返回的结果有所不同,换句话说,后续读取可以读到另一事务已提交的更新数据;
  • 可重复读:一个事务操作中对于一个读取操作不管多少次,读取到的结果都是一样的;
  • 幻读:当某个事务在读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录,当之前的事务再次读取该范围的记录时,会产生幻行。

7.3 隔离级别

隔离级别对问题的处理能力

  • READ UNCOMMITTED(未提交读):事务中的修改,即使没有提交,对其他事务也都是可见的;
  • READ COMMITTED(提交读):一个事务开始时,只能“看见”已经提交的事务所做的修改。换句话说,一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的;
  • REPEATABLE READ(可重复读):无法解决幻读的问题;
  • SERIALIZABLE(可串行化):每一行都进行加锁,对时间空间开销极大。

8. MySQL 索引

在 MySQL 中,我们会面对大量的数据。假设我们有100万条数据,那么如果我们在所有数据中查找一条数据,我们可以需要耗时一秒,这是非常缓慢的。但是如果我们创建了对应的索引,那么时间的开销可能会被压缩到0.07秒这样。

MySQL支持诸多存储引擎,而各种存储引擎对索引的支持也各不相同,因此MySQL数据库支持多种索引类型,如BTree索引,哈希索引,全文索引等等,这样就能尽量减少时间开销。

大部分数据库系统及文件系统都采用B-Tree或其变种B+Tree作为索引结构,MySQL就普遍使用B+Tree实现其索引结构。

这样索引的弊端也就很明显了。虽然索引可以大大加快查询的速度,但是会增加内存开销,并且在增删改的时候,时间开销会大大增加。索引文件本身要消耗存储空间。

InnoDB使用聚集索引,数据记录本身被存于主索引(一颗B+Tree)的叶子节点上。这就要求同一个叶子节点内(大小为一个内存页或磁盘页)的各条数据记录按主键顺序存放,因此每当有一条新的记录插入时,MySQL会根据其主键将其插入适当的节点和位置,如果页面达到装载因子(InnoDB默认为15/16),则开辟一个新的页(节点)。

如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。

例子如图

这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置。

举例

9. MySQL 三大范式

9.1 第一范式

数据表的每一列都要保持它的原子特性,也就是列不能再被分割。

9.2 第二范式

在第一范式满足的基础上,我们考虑第二范式。

属性必须完全依赖于主键,每张表只描述一件事情。

9.3 第三范式

在第一、第二范式的基础上,我们考虑第三范式。

所有的非主属性不依赖于其他的非主属性。

9.4 实际使用

三大范式给了我们一个良好的规范,这样就容易导致我们创建多个表。

所以我们在考虑三大范式的同时,需要关注我们数据库本身的效率。我们可以在效率的基础上同时考虑规范。

10. MySQL 备份

在平时使用的时候,为了防止我们服务器或者本机因为特殊原因导致数据数据丢失,我们需要不定时的对数据进行备份。

  • 在物理层面:因为 MySQL 都是存储在磁盘中的 data 中,我们可以直接把 data 中的文件进行拷贝;
  • 在命令层面:我们使用 mysqldump 对数据进行备份。
mysqldump -uroot -proot --all-databases >/tmp/all.sql
-- 例1:导出所有的数据库

mysqldump -uroot -proot --databases db1 db2 >/tmp/db1_and_db2.sql
-- 例2:导出数据库 db1,db2 中所有的数据

mysqldump -uroot -proot --databases db1 --tables a1 a2 >/tmp/db1.sql
-- 例3:导出数据库 db1 中的 a1,a2 表

当然,在 navicat 这种可视化界面中,我们导出导入仅仅只是点击的事情。

我们在发生错误的时候,也需要去查看 MySQL 的错误日志(去年就有过因为没有更新字段的类型,从而不得不去日志文件中逐个正则匹配的大冷门事件)。

11. MySQL 其他用处

11.1 用户管理

我们可以使用 MySQL 进行用户的管理。我们有着一个管理员 root,我们可以对其他用户进行管理,给他们设定特定的权限。这样就能保证在协同工作的时候,别人不会直接删库跑路(bushi)。

在 navicat 中,可视化界面使用的会非常舒服。

11.2 视图

视图的作用就是用来简化我们的 SQL 代码。我们可以将一个查询语句都设置为视图,这样我们的代码就会十分简洁。

但是要注意,既然这样做,那么我们每次调用视图就都会执行一遍查询操作,这会导致我们的查询效率大大降低。

create view product_total as
select cust_name, cust_contact, prod_id
from customers, orders, orderitems
where customers.cust_id = orders.cust_id
and orderitems.order_num = orders.order_num
-- 此时,我们就将这些东西都作为了一个视图,我们在使用的时候就只需要调用 product_total 即可

在 navicat 中,我们可以直接使用视图的界面进行操作

方便快捷


文章作者: 陈鑫扬
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 陈鑫扬 !
评论
  目录