三大主流数据库特性对比与SQL实战指南
对于我们学习关系型数据库的首要，我们是需要学习好sql语句，因为对于任意一个关系型数据库，都统一的使用sql语句来操作数据库的。所有对于不同的数据库，我们都能很快上手，更深的理解就需要自己去进一步的学习。
| 特性 | MySQL (InnoDB 焦点) | SQL Server | Oracle Database |
|---|---|---|---|
| 核心存储架构 | 插件式引擎(InnoDB, MyISAM...) | 单一集成引擎 | 单一集成引擎 |
| 并发控制 | MVCC + 行级锁 | MVCC(可选) + 行级锁(可升级) | MVCC + 行级锁(无锁升级) |
| 内存管理 | 缓冲池 + 依赖 OS 缓存 | 缓冲池 + 精细内存管理 | SGA/PGA (极其精细复杂) |
| 进程模型 | 多线程 (单进程) | 多线程/调度器 (单进程) | 多进程(Unix/Linux) / 多线程(Windows) |
| 关键 HA/扩展 | 主从复制 / 组复制 / Galera (无共享) | AlwaysOn AG (日志) / FCI (共享存储) | RAC (共享存储多写) / Data Guard |
| 优化器 | 强大，持续改进 | 非常强大，深度集成 | 历史悠久，极其复杂成熟 |
| 日志与恢复 | Redo Log / Undo Log | Transaction Log (Redo+Undo) | Redo Log / Undo Segments |
| 独特底层优势 | 引擎灵活性，开源生态 | Windows 集成，易管理性，商业智能 | RAC 架构，极致扩展性/HA，精细控制 |
| 独特底层挑战 | 引擎差异需管理，历史包袱 | 许可成本，锁升级 | 高昂成本，管理复杂度 |
1. SQL
1.1. SQL分类
| 分类 | 全称 | 说明 |
| DDL |
Data Definition Language
|
数据定义语言，用来定义数据库对象(数据库，表， 字段)
|
| DML |
Data Manipulation Language
|
数据操作语言，用来对数据库表中的数据进行增删改
|
| DQL |
Data Query Language
|
数据查询语言，用来查询数据库中表的记录
|
|
DCL |
Data Control Language
|
数据控制语言，用来创建数据库用户、控制数据库的访问权限
|
1.1.1.DDL
1.1.1.1.数据库操作
查询所有数据库：show databases;
查询当前数据库：select database();
创建数据库：create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ];
删除数据库：drop database [ if exists ] 数据库名;
切换数据库：use 数据库名;
1.1.1.2.表操作
表操作-查询创建
查询当前数据库所有表：show tables;
查看指定表结构：desc 表名;
查询指定表的建表语句：show create table 表名;
创建表结构：
CREATE TABLE 表名(字段1 字段1类型 [ COMMENT 字段1注释 ],字段2 字段2类型 [COMMENT 字段2注释 ],字段3 字段3类型 [COMMENT 字段3注释 ],......字段n 字段n类型 [COMMENT 字段n注释 ]) [ COMMENT 表注释 ] ;
| 类型 | 大小 | 有符合范围 | 无符号范围 | 描述 |
| TINYING | 1byte | (-128,127) | (0,255) | 小整数值 |
| SMALLINT | 2bytes |
(-32768，32767)
|
(0，65535)
|
大整
数值
|
|
MEDIUMINT
|
3bytes
|
(-8388608，8388607)
|
(0，16777215)
|
大整
数值
|
|
INT/INTEGER
|
4bytes
|
(-2147483648，
2147483647)
|
(0，4294967295)
|
大整
数值
|
|
BIGINT
|
8bytes
|
(-2^63，2^63-1)
|
(0，2^64-1)
|
极大
整数
值
|
|
FLOAT
|
4bytes
|
(-3.402823466 E+38，
3.402823466351 E+38)
|
0 和 (1.175494351 E-
38，3.402823466 E+38)
|
单精
度浮
点数
值
|
|
DOUBLE
|
8bytes
|
(-1.7976931348623157
E+308，
1.7976931348623157
E+308)
|
0 和
(2.2250738585072014
E-308，
1.7976931348623157
E+308)
|
双精
度浮
点数
值
|
|
DECIMAL
|
依赖于M(精度)和D(标度)
的值
|
依赖于M(精度)和D(标度)
的值
|
小数
值(精
确定
点数)
|
字符串类型
日期时间类型
表操作-修改
添加字段：ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
修改数据类型：ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
修改字段名和字段类型：ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
删除字段：ALTER TABLE 表名 DROP 字段名;
修改表名：ALTER TABLE 表名 RENAME TO 新表名;
表操作-删除
删除表：DROP TABLE [ IF EXISTS ] 表名;
删除指定表, 并重新创建表：TRUNCATE TABLE 表名;
1.1.2.DML
- 添加数据（INSERT）
- 修改数据（UPDATE）
- 删除数据（DELETE）
添加数据
给指定字段添加数据：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
给全部字段添加数据：
INSERT INTO 表名 VALUES (值1, 值2, ...);
批量添加数据：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值 1, 值2, ...) ;
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
注意：
- 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
修改数据
UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;
注意：
- 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据
删除数据
删除数据的具体语法为：
DELETE FROM 表名 [ WHERE 条件 ] ;
删除所有员工：
delete from employee;
注意：
- DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数 据。
1.1.3.DQL
基本语法SELECT字段列表FROM表名列表WHERE条件列表GROUP BY分组字段列表HAVING分组后条件列表ORDER BY排序字段列表LIMIT分页参数
基础查询
查询多个字段：
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ;
字段设置别名：
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
去除重复记录：
SELECT DISTINCT 字段列表 FROM 表名;
条件查询
语法：
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
常见条件
常见的逻辑运算符
聚合函数
将一列数据作为一个整体，进行纵向计算 。
语法：
SELECT 聚合函数(字段列表) FROM 表名 ;
注意：
NULL值是不参与所有聚合函数运算的。
常见的聚合函数
分组查询
语法：
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [HAVING 分组后过滤条件 ];where和having区别：
- 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
- 判断条件不同：where不能对聚合函数进行判断，而having可以。
注意：
- 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
- 执行顺序: where > 聚合函数 > having 。
- 支持多字段分组, 具体语法为 : group by columnA,columnB
排序查询
语法：
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
排序方式：
ASC：升序(默认值)
DESC：降序
注意：
- 如果是升序, 可以不指定排序方式ASC ;
- 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;
分页查询
语法：
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;
注意：
- 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
- 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10
执行顺序
1.1.4.DCL
1.1.4.1.管理用户
查询用户：
select * from mysql.user;
创建用户：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';修改用户密码：ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;删除用户：DROP USER '用户名'@'主机名' ;注意：
- 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
- 主机名可以使用 % 通配。
- 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库管理员）使用。
1.1.4.2.权限控制
常用的权限
查询权限：
SHOW GRANTS FOR '用户名'@'主机名' ;
授予权限：GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';撤销权限：REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';注意事项：
- 多个权限之间，使用逗号分隔
- 授权时， 数据库名和表名可以使用 * 进行通配，代表所有。
2.函数
函数 是指一段可以直接被另一段程序调用的程序或代码。 也就意味着，这一段程序或代码在MySQL中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。 那么，函数到底在哪儿使用呢？ 我们先来看两个场景：
2.1.字符串函数
字符串常用函数
演示
A. concat : 字符串拼接
select concat('Hello' , ' MySQL');
B. lower : 全部转小写
select lower('Hello');
C. upper : 全部转大写
select upper('Hello');
D. lpad : 左填充
select lpad('01', 5, '-');
E. rpad : 右填充
select rpad('01', 5, '-');
F. trim : 去除空格
select trim(' Hello MySQL ');
G. substring : 截取子字符串
select substring('Hello MySQL',1,5);
2.2.数值函数
常用的数值函数
演示：
A. ceil：向上取整
select ceil(1.1);
B. floor：向下取整
select floor(1.9);
C. mod：取模
select mod(7,4);
D. rand：获取随机数
select rand();
E. round：四舍五入
select round(2.344,2);
2.3.日期函数
常用日期函数
演示：
A. curdate：当前日期
select curdate();
B. curtime：当前时间
select curtime();
C. now：当前日期和时间
select now();
D. YEAR , MONTH , DAY：当前年、月、日
select YEAR(now());
select MONTH(now());
select DAY(now());E. date_add：增加指定的时间间隔select date_add(now(), INTERVAL 70 YEAR );F. datediff：获取两个日期相差的天数select datediff('2021-10-01', '2021-12-01');
2.4.流程函数
常用的流程函数
演示：
A. if
select if(false, 'Ok', 'Error');
B. ifnull
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');
C. case when then else end
需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select
name,( case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end ) as '工作地址'from emp;
3.约束
3.1外键
添加外键
语法：
CREATE TABLE 表名(
字段名 数据类型,...[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名));ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名)REFERENCES 主表 (主表列名) ;
删除外键语法：ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
3.2.删除/更新行为
添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行
语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;
4.多表查询
我们之前在讲解SQL语句的时候，讲解了DQL语句，也就是数据查询语句，但是之前讲解的查询都是单表查询，而本章节我们要学习的则是多表查询操作，主要从以下几个方面进行讲解。
4.1.多表关系
- 一对多(多对一)
- 多对多
- 一对一
4.1.1.一对多
- 案例: 部门 与 员工的关系
- 关系: 一个部门对应多个员工，一个员工对应一个部门
- 实现: 在多的一方建立外键，指向一的一方的主键
4.1.2.多对多
- 案例: 学生 与 课程的关系
- 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
- 实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
4.1.3.一对一
- 案例: 用户 与 用户详情的关系
- 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
- 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
4.2.多表查询分类
- 连接查询
- 内连接：相当于查询A、B交集部分数据
- 外连接：
- 左外连接：查询左表所有数据，以及两张表交集部分数据
- 右外连接：查询右表所有数据，以及两张表交集部分数据
- 自连接：当前表与自身的连接查询，自连接必须使用表别名
- 子查询
4.2.1.内查询
内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据) 内连接的语法分为两种: 隐式内连接、显式内连接。先来学习一下具体的语法结构。
隐式内连接：
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
显示内连接：
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
4.2.2.外连接
外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：
左外连接：
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。
右外连接：SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。
4.2.3.自连接
4.2.3.1.自连接查询
语法：SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;注意事项:在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。
而对于自连接查询，可以是内连接查询，也可以是外连接查询。
4.2.3.2.联合查询
语法：SELECT 字段列表 FROM 表A ...UNION [ ALL ]SELECT 字段列表 FROM 表B ....;注意：
- 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致
4.2.4.子查询
4.2.4.1. 概念
语法：SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
- 标量子查询（子查询结果为单个值）
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置，分为：
-
WHERE之后
-
FROM之后
-
SELECT之后
4.2.4.2.标量子查询
案例：查询在 "方东白" 入职之后的员工信息①. 查询 方东白 的入职日期select entrydate from emp where name = '方东白';②. 查询指定入职日期之后入职的员工信息select * from emp where entrydate > (select entrydate from emp where name = '方东白');
4.2.4.3.列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。
常用的操作符：IN 、NOT IN 、 ANY 、SOME 、 ALL
案例：
查询 "销售部" 和 "市场部" 的所有员工信息
①. 查询 "销售部" 和 "市场部" 的部门ID
select id from dept where name = '销售部' or name = '市场部';
②. 根据部门ID, 查询员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
4.2.3.4.行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。
常用的操作符：= 、<> 、IN 、NOT IN
案例：
查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
①. 查询 "张无忌" 的薪资及直属领导
select salary, managerid from emp where name = '张无忌';
②. 查询与 "张无忌" 的薪资及直属领导相同的员工信息 ;
select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌');
4.2.3.5.表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。
常用的操作符：IN
案例：
查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
①. 查询 "鹿杖客" , "宋远桥" 的职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
②. 查询与 "鹿杖客" , "宋远桥" 的职位和薪资相同的员工信息
select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' );
5.事务
- 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立 环境下运行。
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
上述就是事务的四大特性，简称ACID
5.1.并发事务问题
1). 赃读：一个事务读到另外一个事务还没有提交的数据。
比如B读取到了A未提交的数据。
2). 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
事务A两次读取同一条记录，但是读取到的数据却是不一样的。
5.2.事务隔离级别
1). 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;
2). 设置事务隔离级别SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }注意：事务隔离级别越高，数据越安全，但是性能越低。
助力广东及东莞地区开发者，代码托管、在线学习与竞赛、技术交流与分享、资源共享、职业发展，成为松山湖开发者首选的工作与学习平台
更多推荐
13
0- 0
已为社区贡献1条内容
所有评论(0)