Mysql基础

S妖O风F

已于 2024-04-02 08:45:05 修改

阅读量41

点赞数

文章标签： mysql 数据库 oracle

于 2022-05-16 20:34:07 首次发布

本文链接：https://blog.csdn.net/sooooof/article/details/124807405

版权

1.概念与安装

2.库表操作

3.基本增删改查

4.别名运算符和排序

5.数据库三范式

6.表约束

7.数据类型

8.视图与触发器

9.存储过程

10.数据处理函数

11.索引

12.连接查询

13.子查询与联合查询

14.报表函数

15.分组查询

16.索引最左匹配原则

17.Oracle 基本操作

1.概念与安装

我们每天都在访问各种网站、APP，如微信、QQ、抖音、今日头条、腾讯新闻等，这些东西上面都存在大量的信息，这些信息都需要有地方存储，一般都是存储在数据库中。

所以如果我们需要开发一个网站、app，数据库我们必须掌握的技术。常用的数据库有 mysql、 oracle、sqlserver、db2 等。上面介绍的几个数据库，oracle性能排名第一，服务也是相当到位的，但是收费也是非常高的，金融公司对数据库稳定性要求比较高，一般会选择 oracle。

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

SQL（Structured Query Language 结构化查询语言）是一种定义、操作、管理关系数据库的句法。大多数关系型数据库都支持。结构化查询语言的工业标准由 ANSI (美国国家标准学会，ISO 的成员之一)维护。世界著名数据库厂商如 Oracle、MySQL 都遵循 SQL 规范，只是在各家设计数据库时为了性能更好，相同功能的 SQL 有一些小小的区别。

Mysql 数据库

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

Mysql 是开源的，所以你不需要支付额外的费用。
Mysql 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL 使用标准的 SQL 数据语言形式。
Mysql 可以允许于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
Mysql 对 PHP 有很好的支持，PHP 是目前最流行的 Web 开发语言。
MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为 8TB。
Mysql 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 Mysql 系统。

搜索 phpStudy V8.1 ，下载大约 78m 左右：官网下载地址

phpStudy 可以随时升级和更换 mysql 版本，安装其他常用插件。而且 phpStudy 对于数据库的移植，备份，启动，操作等等都特别方便。

安装完 phpStudy 后会在 D 盘解压一个文件夹 phpstudy_pro，将来如果有需要可以直接考走

MySQL 的默认账户与密码分别是 root root

启动数据库后，在左侧导航栏数据库中点击操作可以修改数据库密码。

phpStudy 默认自带一个好用的 MySQL 管理工具 HeidiSQL，不想安装 SQLyog 或 Navicat 数据库管理工具的可以使用它。

点击管理，输入基本信息即可连接数据库。

当然也可以使用 SQLyog 管理数据库：下载地址

打开 SQLyog，新建一个连接，填入相关信息，即可在 JspStudy 的 MySQL 启动的情况下连上数据库

还可以使用 idea 连接 MySQL

2.库表操作

数据库作为数据表存储的基本单位，即一个数据库存储一张和多张表，同一个数据库中数据表可以关联查询。一般的，在实际开发中每个项目都会使用独立的一个数据库，一个项目也不建议连接多个数据库。

CREATE DATABASE mydb

当再次执行时会报错，数据库 mydb 已经存在（exists）

错误代码： 1007
Can't create database 'mydb'; database exists

使用命令查看所有的数据库

SHOW DATABASES

删除数据库

DROP DATABASE mydb;

使用数据库，方便在数据库中创建表。

USE mydb;

数据库中每列数据的标识叫字段，表操作一般就是针对字段的操作。创建表时一般会有一个或多个字段，它们之间用逗号隔开。数据库中最常用的两种字段类型为 int 和 char 分别用于存储整数和字符串类型的数据，char 类型后需要指定存储的字符个数。

CREATE TABLE mytb (
    id INT,
    name CHAR(5)
);

创建完表后可以使用 desc 表名查看表结构。

mysql> desc mytb;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(5) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

字段的增删改操作，下面 SQL 演示如何操作字段

ALTER TABLE mytb  DROP id;  删除字段
ALTER TABLE mytb  ADD ids INT;  增加字段

ALTER TABLE mytb  ADD id INT FIRST;  添加一个字段在最前面
ALTER TABLE mytb  ADD sex INT AFTER name; 增加一个字段在 name 后

ALTER TABLE mytb MODIFY name CHAR(10);修改 name 字段的长度为 10
ALTER TABLE mytb MODIFY ids BIGINT NOT NULL DEFAULT 100;修改 ids 字段的属性为 long 不为空默认值为 100

修改表名为 mytable

ALTER TABLE mytb RENAME TO mytable;

刪除表 mytable

DROP TABLE mytable

注意：

删除表有 drop [drɒp]，truncate [trʌŋˈkeɪt]，delete [dɪˈliːt] 三种操作，他们的区别如下：

drop：删除数据和表结构，释放空间。

drop table user;

truncate：保留表结构，删除数据，释放空间。

truncate table user;

delete：保留表结构，删除数据，不释放空间。

delete from user;

三者的执行速度，一般来说：drop > truncate > delete

释放空间可以体现在：

1.通过 delete 删除的行数据是不释放空间的，如果表 id 是递增式的话，那么表数据的 id 就可能不是连续的；而通过 truncate 删除数据是释放空间的，如果表 id 是递增式的话，新增数据的 id 又是从头开始，而不是在已删数据的最大 id 值上递增。

2.使用 delete 删除数据时，mysql 并没有把数据文件删除，而是将数据文件的标识位删除，没有整理文件，因此不会彻底释放空间。被删除的数据将会被保存在一个链接清单中，当有新数据写入的时候，mysql 会利用这些已删除的空间再写入。即，删除操作会带来一些数据碎片，正是这些碎片在占用硬盘空间。

3.基本增删改查

数据库的每条数据通常称为一条记录，记录的常用操作通常就是增删改查，往数据表中插入记录使用如下语法：

INSERT INTO table [(字段名 [, 字段名...])]
VALUES		(value [, value...]);

注意：

插入的数据应与字段的数据类型相同。
数据的大小应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。
在 values 中列出的数据位置必须与被加入的列的排列位置相对应。字符和日期型数据应包含在单引号中。
插入空值，不指定或 insert into table value(null) 即可插入空值。

示例：

INSERT INTO `emp` (`name`,`sal`) VALUES ('张三',10000.5);

修改记录

修改数据表中记录使用如下语法：

UPDATE table_name SET 字段名1=expr1 [, 字段名2=expr2 ...] [WHERE where_definition]

注意：

UPDATE 语法可以用新值更新原有表行中的各列。
SET 子句指示要修改哪些列和要给予哪些值。
WHERE 子句指定应更新哪些行。如没有 WHERE 子句，则更新所有的行。

示例：

UPDATE emp SET `name` ='李四'
UPDATE emp SET `name`='王二' WHERE `id` = 1;
UPDATE emp SET `name`='王二',`sal`=16.5 WHERE `id` = 1;
UPDATE emp SET `name`='王二',`sal`=16.5 WHERE `id` IN(1,3,5);

注意：如果在数据修改时，该表的全部字段相同，修改命令发送时会导致多条数据被修改。同时 SQLYog 会弹窗。所以为了避免这种问题数据库各大厂商提出数据库三大范式作为约束。

删除记录

删除数据表的数据使用如下语法：

delete from table_name [WHERE where_definition]

注意：

如果不使用 where 子句，将删除表中所有数据。
使用 delete 语句仅删除记录，不删除表本身。
如要删除表，使用 drop table 语句。

示例：

delete from `emp` where id = 1;

简单查询

查询数据表数据一般使用如下语法：

SELECT [DISTINCT] *|{字段名1, 字段名2, 字段名3..} FROM table;

参数：

Select 指定查询哪些列的数据。
* 号代表查询所有列，在实际开发中很少用 *，它会增加查询的负担。
From 指定查询哪张表。
DISTINCT [dɪˈstɪŋkt] 可选，指显示结果时，是否剔除重复数据

示例：

SELECT * FROM `emp`;
SELECT `name`,`sal` FROM `emp`;
SELECT DISTINCT `name`,`sal` FROM `emp`;

4.别名运算符和排序

在查询过程中，使用原字段的名称有时候不太方便，MySQL 提供别名处理这样的情况。

SELECT 字段名 AS 别名 FROM 表名;

AS 可以省略

示例：

SELECT `name` 姓名,`sal` 薪水 FROM `emp`;

注意：别名处可以不使用单引号，但是为了不和数据库的一些关键字冲突建议所有表名、字段名都加上数据库专用单引号（一般位于 tab 键上方）。

运算符

在 WHERE 子句中经常使用相关的运算符，常见运算符有比较运算符、逻辑运算符等。

比较运算符

>   <   <=   >=   =    <>/!=	大于、小于、大于(小于)等于、不等于
BETWEEN  ...AND...	        显示在某一区间的值(含头含尾)
IN(set)	                        显示在 in 列表中的值，例：in(100,200)
LIKE ‘%张_’	                模糊查询
IS NULL	                        判断是否为空（is not null）

LIKE 语句中，% 代表零个或多个任意字符，_ 代表一个字符。

示例：

name LIKE ‘_a%'    表示第二个字符为 a 的人
name like '张%'     姓张的所有人
name like '_伟'     所有姓名为两个字且第二个字为 “伟”
name like '%商%'    姓名中包含 “商”字
name like '%友'     姓名以“友”结尾

逻辑运算符

and	                        多个条件同时成立
or	                        多个条件任一成立
not	                        不成立，例：where not(salary>100);

示例：

SELECT `id`, `name`,`sal` FROM `emp` WHERE id > 1;
SELECT `id`, `name`,`sal` FROM `emp` WHERE id BETWEEN 1 AND 3;
SELECT `id`, `name`,`sal` FROM `emp` WHERE `name` IS NULL;
SELECT `id`, `name`,`sal` FROM `emp` WHERE `name` LIKE '%王%' AND id = 1;

排序

排序的列即可是表中的列名，也可以是 SELECT 语句后指定的列名。

ASC 升序、DESC 降序

ORDER BY 子句应位于 SELECT 语句的结尾。

案例：

SELECT `id`, `name`,`sal` FROM `emp` WHERE `id` > 0 ORDER BY `name` ASC ;
SELECT * FROM `mytabe` ORDER BY `sex` DESC,`id` DESC

5.数据库三范式 (自学)

第一范式（1NF）：字段具有原子性，不可再分。所有关系型数据库系统都满足第一范式

数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。

第二范式（2NF）：第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键。实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

第三范式（3NF）：满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。所以第三范式具有如下特征： 1，每一列只有一个值 2，每一行都能区分。 3，每一个表都不包含其他表已经包含的非主关键字信息。例如，帖子表中只能出现发帖人的 id，而不能出现发帖人的 id，还同时出现发帖人姓名，否则，只要出现同一发帖人 id 的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。

6.表约束

在数据库中为了保证数据库插入时按照指定规则插入，引入表约束，常见表约束有

1.主键约束 primary key [ˈpraɪməri]（不允许为空，不允许重复）如果是整数类型一般定义主键自动增长 auto_increment [ˈɔːtəʊ] [ˈɪŋkrəmənt]，一般通过可视化连接工具添加。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10),
  `password` char(10),
  PRIMARY KEY (`id`)
)

2.唯一约束，也叫唯一索引，用于设置该字段不可重复。

ALTER TABLE `user`.`user` ADD UNIQUE INDEX index_name (`name`);

3.非空约束 not null 用于指定该字段不能为空一般在创表时指定或可视化连接工具添加。也可以和 DEFAULT 配合使用，表示不输入时该字段的默认值。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  `password` char(10) NOT NULL DEFAULT 123456,
  PRIMARY KEY (`id`)
)

4.外键约束，在创建外键约束时一定要保证两个表的数据库引擎相同且不能为 MYISAM，一般通过可视化连接工具添加。

ALTER TABLE `client` ADD CONSTRAINT `emp_id_fk` FOREIGN KEY (`emp_id`) REFERENCES `emp`(`id`);

一般创表命令如下

CREATE TABLE mytb(
    id INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    pwd VARCHAR(40) NOT NULL,
    birth DATE,
    PRIMARY KEY ( id )
 )ENGINE=INNODB DEFAULT CHARSET=utf8;

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为 NULL ，就会报错。
AUTO_INCREMENT 定义列为自增的属性，一般用于主键，每次插入数据后数值会自动加 1。
PRIMARY KEY 关键字用于定义列为主键。可以使用多列来定义主键称为复合主键，列间以逗号分隔。
ENGINE 设置存储引擎，使用 show engines 命令可以查看 MySQL 支持的数据库引擎，CHARSET 设置编码。

注意：utf8 与 utf8mb4 的区别在于 utf8mb4 兼容部分图标字体如：☺◐❀♬▯ 。如果引擎和字符编码在安装时设置好的则在创表时可以不指定。

7.数据类型

MySQL 中为了满足各种业务需要，提供很多数据类型，常用类型如下：

INT 整数 4 字节
BIGINT 长整数 8 字节
FLOAT 单精度小数单精度浮点数值
DOUBLE 双精度小数双精度浮点数值
DECIMAL 小数值对DECIMAL(P,D)
DATE 日期值 YYYY-MM-DD
TIME 时间值 HH:MM:SS
DATETIME 混合日期和时间值 YYYY-MM-DD HH:MM:SS
CHAR 定长字符串 0-255字节
VARCHAR 变长字符串 0-65535 字节
TEXT 长文本数据 0-65535 字节
BLOB 二进制形式的数据 0-65535 字节

注意：

1.在 DECIMAL(P,D) 的语法中： P 是表示有效数字数的精度。 P 范围为 1〜65。 D 是表示小数点后的位数。 D 的范围是 0~30。MySQL 要求 D 小于或等于(<=)P。如：DECIMAL(6,2)中最多可以存储 6 位数字，小数位数为 2 位；因此，列的范围是从-9999.99 到 9999.99。

2.char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。char 是一种定长字符串。它一旦声明后无论存储的值是否达到该长度，都占有相应的字节长度。varchar 是一种变长字符串，它的长度由存储的内容决定，为存满的时以实际存储内容为主。

3.BLOB 是一个二进制大对象，可以容纳可变数量的二进制数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

4.TEXT 同样也有 4 种类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。它们的区别也是可存储的最大长度不同。具体使用哪种类型可根据实际情况选择。

8.视图与触发器

视图其实就是一张虚拟表，一张从别的表中导出的一张不完整或经过运算的虚拟表。

视图有什么用，当我们经常查询一个表中的某几个字段的时候，我们可以把这几个字段单独导出一个视图，以后查询的时候直接可以 select *，在数据库中只存放了视图的定义，没有存放视图的数据，视图是依赖原来表中的数据的，原表中的数据一旦发生变化，视图查询出的结果也会跟着变化。下面进行创建一个视图。

案例：给语文老师创建一个视图

CREATE VIEW vi_chinese_user 
AS SELECT id,`name`,`chinese` FROM `student`;

视图在查询时和普通表查询语句完全相同。删除视图使用命令如下：

DROP VIEW `vi_chinese_user`;

触发器是由事件来触发某个操作，这些事件包括 insert、delete、update 语句。当数据库执行这些事件时，就会激活触发器执行相应的操作。

我们需要一个表，这里用 user 表，用来对该表进行增删改操作，另外需要一个 user_log 表，当对 user 表操作的时候，会激活我们的触发器，对 user_log 表做出相应的操作。

CREATE TRIGGER auto_save_time BEFORE INSERT 
ON `user` FOR EACH ROW 
INSERT INTO user_log(log_time) VALUES(NOW());

这条语句的意思是，创建一个触发器名字是 auto_save_time，在 user 表的任意行执行 insert 语句之前会激活触发器，并执行 insert into user_log(log_time) values(now()); 语句。下面我们对 user 表进行一个 insert 操作，可以看出，激活了触发器，并执行了那条语句。now() 表示当前时间括号前不能有空格。

查看触发器使用命令 show triggers；删除触发器使用命令 drop trigger 触发器名；触发器有 6 个分别是插入前后，删除前后，修改前后六个，与前面案例中的 before 对应的还有 after。

9.存储过程

存储过程 (Stored Procedure) 是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集 , 存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它，存储过程是数据库中的一个重要对象；存储过程中可以包含逻辑控制语句和数据操纵语句 , 它可以接受参数，输出参数，返回单个或多个结果集以及返回值；

create table p_user(
	id int primary key auto_increment,
	name varchar(10),
	sex char(2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into p_user(name,sex) values('A',"男");
insert into p_user(name,sex) values('B',"女");
insert into p_user(name,sex) values('C',"男");

创建存储过程(查询得到男性或女性的数量，如果传入的是 0 就获取女性的数量，否则获取男性的数量)

CREATE PROCEDURE ges_user_count(IN sex_id INT, OUT user_count INT)
BEGIN
    IF sex_id = 0 THEN
        SELECT COUNT(*) FROM p_user WHERE p_user.sex = '女' INTO user_count;
    ELSE
        SELECT COUNT(*) FROM p_user WHERE p_user.sex = '男' INTO user_count;
    END IF;
END

调用存储过程

SET @user_count = 0;
CALL ges_user_count(1, @user_count);
SELECT @user_count;

查询得到男性或女性的数量, 如果传入的是 0 就女性否则是男性。

10.数据处理函数

在查询数据时有事需要对查出的字段进行处理，如小写转大写，切割等。这时就需要使用数据库的字符串处理函数，如下是 mysql 常用字符串处理函数。

CHARSET(字段)	                            返回字段的字符集
CONCAT(string2  [,... ])	            连接字串
INSTR(string ,substring )	            返回substring在string中出现的位置,没有返回0
UCASE(string2 )	                            转换成大写
LCASE(string2 )	                            转换成小写
LEFT(string2 ,length )	                    从string2中的左边起取length个字符
LENGTH(string )	                            返回string字节数量
REPLACE(str ,search_str ,replace_str )	    在str中用replace_str替换search_str
STRCMP(string1 ,string2 )	            逐字符比较两字串大小,返回1、0和-1
SUBSTRING(str , position  [,length ])	    从str的position开始,取length个字符
LTRIM(string2 ) RTRIM (string2 )            trim 去除前端空格或后端空格

案例：

SELECT CHARSET(`name`) FROM `emp`;
SELECT CONCAT(`name`,`pwd`) FROM `emp`;
SELECT INSTR(`pwd`,'2') FROM `emp`;
SELECT UCASE(`name`) FROM `emp`;
SELECT LCASE(`name`) FROM `emp`;
SELECT LEFT(`pwd`,2) FROM `emp`;
SELECT LENGTH(`pwd`) FROM `emp`;
SELECT STRCMP('b' ,'b' ) FROM `emp`;
SELECT SUBSTRING(`pwd`,2,2) FROM `emp`;
SELECT LTRIM(`name`) FROM `emp`;
SELECT RTRIM(LTRIM(`name`)) FROM `emp`;

数字处理函数

以下是 mysql 常用的数字处理函数

ABS(number2 )	                            绝对值
CEILING (number2 )	                    向上取整
FLOOR (number2 )	                    向下取整
FORMAT (number,小数位数)	            保留小数位数
HEX (DecimalNumber )	                    转十六进制
LEAST (number , number2  [,..])	            求最小值
MOD (numerator ,denominator )	            求余
RAND([seed])	                            随机数

案例：

SELECT `name`,ABS(`english`) FROM `student`;
SELECT `name`,CEILING(100.00000001) FROM `student`;
SELECT `name`,FLOOR (100.00000001) FROM `student`;
SELECT `name`,`english`,FORMAT (`english`,2) FROM `student`;
SELECT `name`,`english`,HEX  (`english`) FROM `student`;
SELECT `name`,RAND() FROM `student`;
SELECT `name`,CEILING(RAND()*10) FROM `student`;

日期时间处理函数

获取当前的日期（包含具体的时分秒）

SELECT NOW(),LOCALTIME(),SYSDATE()

获取当前的日期（不包含具体的时分秒）

SELECT CURRENT_DATE(),CURDATE()

日期与时间戳之间的转化

SELECT UNIX_TIMESTAMP(NOW()),FROM_UNIXTIME(1629882598)

获取当前的时间（时分秒）

SELECT CURRENT_TIME(),CURTIME()

获取月份

MONTH(date)返回月份的数值； MONTHNAME(date)返回月份的英文名称

SELECT MONTH(NOW()),MONTHNAME(NOW())

获取天的函数

DAYOFMONTH(date)返回日期属于当前月的第几天； DAYOFYEAR(date)返回日期属于当前年的第几天

SELECT DAYOFMONTH(NOW()),DAYOFYEAR(NOW())

获取星期的函数

DAYNAME(date)返回星期几的英文名字；DAYOFWEEK(date)返回星期几的数值，返回值的范围是1-7，1表示星期日，以此类推； WEEKDAY(date)返回星期几的数值，返回值的范围是0-6，0表示星期一，以此类推

SELECT DAYNAME(NOW()),DAYOFWEEK(NOW()),WEEKDAY(NOW())

两个日期间的间隔天数

TO_DAYS(date)日期date与默认日期（000年1月1日）之间间隔的天数；FROM_DAYS(number)默认日期经过 number 天后的日期；DATEDIFF(date1，date2)获取指定两个日期间的相隔天数，date1-date2

SELECT TO_DAYS(NOW()),FROM_DAYS(738166),DATEDIFF(NOW(),'2020-01-01')

日期的加减法

DATE_ADD(date,INTERVAL expr type)，在 date 的基础上添加上指定的时间间隔后的日期。

DATE_SUB(date,INTERVAL expr type)在 date 的基础上减去上指定的时间间隔后的日期。

type 值常用的有：year、month、day、hour、minute、second、microsecond（毫秒）、week、quarter

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY),DATE_SUB(NOW(),INTERVAL 1 DAY)

11.索引

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索引包含多个列。创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

注意：如果过多的使用索引将会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。

索引在 mysql 中分为普通索引，唯一索引和全文索引

1.普通索引

CREATE INDEX indexName ON `users` (username(length))

如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型，必须指定 length。

2.唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

CREATE UNIQUE INDEX indexName ON mytable(username(length))

3.全文索引：针对大文本 TEXT 的内容查找

CREATE FULLTEXT INDEX indexName ON `users` (username)

由于全文索引需要对该字段进行分词，对中文的支持不是很好，所以全文索引在实际开发中不建议使用。在一些大文本的内容管理系统中一般使用 elasticsearch [ɪˈlæstɪk] [sɜːtʃ] 完成全文检索。

使用全文索引的格式：  MATCH (columnName) AGAINST ('string')
eg:
SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

删除索引：

ALTER TABLE `users` DROP INDEX `indexName`

12.连接查询

数据准备：下载

CREATE TABLE `client` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `tel` varchar(20) NOT NULL,
  `emp_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_id_fk` (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (2,'王二','123',2);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (4,'麻子','234',2);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (5,'小明','345',1);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (6,'小红','456',1);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (7,'不知道找谁','789',NULL);

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `pwd` varchar(50) NOT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (1,'张三','123456','2020-06-04');
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (2,'李四','13456','2020-06-04');
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (3,'晓峰','123','2020-06-05');

两表连接分为内连接和外连接：

内连接（inner join [ˈɪnə(r)] [dʒɔɪn]）：返回连接表中符合连接条件及查询条件的数据行。

外连接 outer [ˈaʊtə(r)]：分为

左外连接（left outer join）可以简写 left join
右外连接（right outer join）可以简写为 right join

与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。

from 表1 连接类型 表2 [on (连接条件)] [where (查询条件)]

内连接查询（重点）

SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e ,`client` c WHERE e.`id`=c.`emp_id`;
SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e INNER JOIN `client` c ON e.`id`=c.`emp_id`;
SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e LEFT OUTER JOIN `client` c ON e.`id`=c.`emp_id`;

左外连接查询

使用 left outer join 关键字，在 on 子句中设定连接条件不仅包含符合连接条件的数据行，还包含左表全部数据，右表没连上的不显示。左连接时左表优先全部显示。

13.子查询与联合查询

子查询也叫嵌套查询，是指在 select 子句或者 where 子句中又嵌入 select 查询语句。

SELECT * FROM `emp` WHERE id IN(SELECT emp_id FROM `client` WHERE id IN(2,4,5))

联合查询

合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。联合查询使用 union 关键字

SELECT * FROM `client` WHERE emp_id = 2 UNION SELECT * FROM `client` WHERE emp_id = 1;
SELECT `id`,`name` FROM `client` WHERE emp_id = 2 UNION SELECT `id`,`name` FROM `emp`;

注意：两条查询语句返回的结构和字段的数量必须相同，否则无法合并。

14.报表函数

报表函数也叫聚合函数，一般用于统计汇总数据库表信息。通常和分组函数配合使用，如果不使用分组函数则认为该表的全部数据为一个组。常用报表函数如下：

1.计数函数－COUNT，如果参数是字段名，则统计该字段不为空的记录数。

Select count(*)|count(列名) from tablename [WHERE where_definition]

案例：

SELECT COUNT(*) FROM `client`

2.求和函数－SUM

Select sum(列名){,sum(列名)…} from tablename [WHERE where_definition]

案例：

SELECT SUM(`chinese`),SUM(`english`),SUM(`math`) FROM `student` WHERE `name` LIKE '%李%'

3.平均函数－AVG

Select avg(列名){,avg(列名)…} from tablename [WHERE where_definition]

案例：

SELECT AVG(`chinese`),AVG(`english`),AVG(`math`) FROM `student` WHERE `name` LIKE '%李%'

4.边角函数－MAX/MIN

Select max(列名)　from tablename [WHERE where_definition]

案例：

SELECT MAX(`english`) FROM `client`;
SELECT MIN(`english`) FROM `client`;

15.分组查询

分组查询是按一个或多个字段进行分组，字段值相同的为一组进行分组统计，其语法格式为：

[select …] from … [where…] [ group by … [having… ]] [ order by … ][limit n]

其中 group by 子句指定按照哪些字段分组。having 子句设定分组查询条件。

数据准备：

CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `dept` varchar(10) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `edlevel` int(11) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (1,'张三','开发部',2000,3,'2020-02-10 15:19:59');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (2,'李四','开发部',2500,3,'2019-07-16 15:20:01');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (3,'王五','设计部',2600,5,'2019-09-25 15:20:03');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (4,'王六','设计部',2300,4,'2020-06-05 15:20:05');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (5,'马七','设计部',2100,4,'2019-11-11 15:20:07');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (6,'赵八','销售部',3000,5,'2020-06-05 15:20:19');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (7,'钱九','销售部',3000,7,'2020-06-05 15:20:21');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (8,'孙十','销售部',3000,7,'2020-06-05 15:20:26');

案例：

1.列出每个部门最高薪水的结果
SELECT `dept`, MAX(`salary`) AS '最高薪资' FROM `staff` GROUP BY `dept`;

2.查询每个部门的总的薪水数
SELECT `dept`, SUM( `salary` ) AS '总薪水' FROM `staff` GROUP BY `dept`;

3.查询公司 2020 年入职的各个部门每个级别里的最高薪水
SELECT `dept`, `edlevel`, MAX( `salary` ) AS maximum FROM staff WHERE `hiredate` > '2020-01-01' GROUP BY `dept`, `edlevel`;

4.查询雇员数超过2个的部门的最高和最低薪水并按照部门升序查询
SELECT `dept`, MAX( `salary` ) AS maximum, MIN( `salary` ) AS minimum FROM `staff` GROUP BY `dept` HAVING COUNT(*) > 2 ORDER BY DEPT

5.查询雇员平均工资大于或等于 2300 的部门的最高和最低薪水并按照最高薪水排序
SELECT `dept`, MAX( `salary` ) AS maximum, MIN( `salary` ) AS minimum FROM `staff` GROUP BY `dept` HAVING AVG( `salary` ) >= 2300 ORDER BY MAX(`salary`)

分组连接函数：在 MySQL 中将分组相同的行数据合并起来使用 group_concat 函数，该函数语法如下：

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

案例：以 id 分组，把 name 字段的值打印在一行，逗号分隔(默认)

select id,group_concat(name) from aa group by id;

案例：以id分组，把name字段的值打印在一行，分号分隔

select id,group_concat(name separator ';') from aa group by id;

案例：以id分组，把去冗余的name字段的值打印在一行，并用逗号分隔

select id,group_concat(distinct name) from aa group by id;

案例：以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序

select id,group_concat(name order by name desc) from aa group by id;

16.索引最左匹配原则

创建测试表并建立了复合（联合）索引（a，b，c）

create table test(
    a int ,
    b int,
    c int,
    d int,
    key index_abc(a,b,c)
)engine=InnoDB default charset=utf8;

插入 10000 条数据

DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=10000 DO
    INSERT INTO test(a,b,c,d) VALUES(i,i,i,i);
    SET i = i+1;
END WHILE;
END $
CALL proc_initData();

explain 指令查看下面SQL

explain select * from test where a<10 ;
explain select * from test where a<10 and b <10;
explain select * from test where a<10 and b <10 and c<10;

将 a，b出现顺序换一下，a，b，c 出现顺序换一下

explain select * from test where b<10 and a <10;
explain select * from test where b<10 and a <10 and c<10;

查了下资料发现：mysql 查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以 mysql 查询优化器会最终以这种顺序进行查询执行。

mysql> explain select * from test where b<10 and a <10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 5       | NULL |    9 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

为什么 b<10 and c <10，没有用到索引？而 a<10 and c <10用到了？

mysql> explain select * from test where b<10 and c <10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10005 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

当 b+ 树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+ 数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但当(20,F)这样的没有 name 的数据来的时候，b+ 树就不知道下一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了，这个是非常重要的性质，即索引的最左匹配特性。

17.Oracle 基本操作

创库：在 oracle 中创建数据库通常叫创建表空间，并需要创建该表空间用户，并赋予该用户权限

--创建表空间

create tablespace jwxt datafile 'jwxt.ora' size 500m 
autoextend on --自动扩展 
 next 50m maxsize 20480m --每次自动扩展50M，最大可到20480M 
extent management local;

--创建表空间默认用户

create user jwxt identified by 888 default tablespace jwxt quota 500m on jwxt;

--赋予其所有权限

grant all privileges to jwxt;

--拥有connect权限的用户只可以登录Oracle;拥有resource权限的用户只可以创建实体，不可以创建数据库结构;拥有全部特权，是--系统最高权限，只有DBA才可以创建数据库结构。

创表：oracle 数据库使用序列 sequence 完成数据库自增操作，通常每张表都会创建一个序列

create sequence USER_SEQ
    maxvalue 10000000

创表

create table "user"
(
    id int
        constraint USER_PK
        primary key,
    name varchar2(255)
)

在 oracle 中表名和字段名不能使用 TAB 上面的 ~（飘）而是使用双引号，值必须使用单引号。同时使用序列（sequence）的下一个值方法获取下一个值。每次使用该方法后该序列的下一个值会自动按照步长增加。

insert into "user"(ID,NAME) values (USER_SEQ.nextval,'张三')

Oracle 分页查询必须使用行号 rownum，每次查询的行号都在变化，所以一般使用子查询完成，SQL 如下

select * from ( select rownum r_, row_.* from ( select * from student order by id ) row_ where rownum <=Y ) where r_>=X ;

X：起始索引位置。

Y：结束索引位置。

jdbc 操作时 url 前的驱动方式有两种，下面进行说明。最后的 sid 称为实例名，一般每个数据库服务都会有一个实例名。每个用户下有自己所属的数据库。查询也是针对该数据库。

同时需要导入相应的依赖：

<dependency>
    <groupId>com.oracle.database.jdbc</groupId>
    <artifactId>ojdbc8</artifactId>
    <version>12.2.0.1</version>
</dependency>

案例：

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.75.128:1521:xe", "jwxt", "888");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from \"user\"");
while (resultSet.next()) {
    System.out.println(resultSet.getString(2));
}
resultSet.close();
statement.close();
connection.close();

thin 和 oci 的 url 写法上的区别：

1）从使用上来说，oci必须在客户机上安装oracle客户端或才能连接，而thin就不需要，因此从使用上来讲thin还是更加方便，这也是thin比较常见的原因。

2）原理上来看，thin是纯java实现tcp/ip的c/s通讯；而oci方式,客户端通过native java method调用c library访问服务端，而这个c library就是oci(oracle called interface)，因此这个oci总是需要随着oracle客户端安装（从oracle10.1.0开始，单独提供OCI Instant Client，不用再完整的安装client）

3）它们分别是不同的驱动类别，oci是二类驱动， thin是四类驱动，但它们在功能上并无差异。

4）虽然很多人说oci的速度快于thin，但找了半天没有找到相关的测试报告。