40 - 数据库基础

目录

1 数据库概述

        数据库指的是按照数据结构来组织、存储、管理数据的仓库，常用的数据库种类为关系型数据库和非关系型数据库。关系型数据库模型是把复杂的数据归结为简单的二元关系(即二维表格形式——)。
        在关系数据库中，对数据的操作几乎全部建立在一个或多个关系表格上，通过对这些关联的表格分类、合并、连接或选取等运算实现数据的管理。
常见的数据库有MySQL。

1.1 关系数据库之ACID理论

        ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。一个支持事务（Transaction）的数据库，必须要具有这四种特性，否则在事务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达不到交易方的要求。MySQL的Innode支持事务，所以它遵守ACID原则。

特性 描述
原子性(atomicity) 一个事务是一个不可分割的工作单位，事务中包括的所有操作要么全部做完，要么什么都不做
一致性(consistency) 事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的
隔离性(isolation) 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
持久性(durability) 持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响

1.2 关系数据库之概念

在关系数据库中，关系就是二维表，由行和列组成。

  • 行row，也称为记录Record、元组。
  • 列Column，也称为字段Filed、属性。
  • 域Domain，叫做字段的取值范围 。例如gender字段的取值就是M或者F两个值。

其他概念性的东西：

  • 维数：关系的维数指关系中属性的个数
  • 基数：元组的个数

注意在关系中，属性的顺序并不重要。理论上，元组顺序也不重要，但是由于元组顺序与存储相关，会影响查询效率。

1.2.1 候选键

关系中，能唯一标识一条元组(记录)的属性或属性集合，成为候选键可以是多个字段的组合。
houxuan

1.2.2 主键

表示一列或者多列组成唯一的key，也就是通过这一个或者多个列能唯一的标识一条记录。即被选择的候选键。

  • 主键的列不能包含空值null。主键往往设置为整型、长整型，且自增AUTO_INCREMENT。
  • 表中可以没有主键，但是一般表设计中，往往都会有主键，以免记录重复
  • 功能主要在于查数据、定位数据

zhujian

1.2.3 外键

严格来说，当一个关系中的某个属性或属性的集合与另一个关系（也可以是自身）的候选键匹配时，就称作这个属性或属性集合的外键。

  • 简单来说，就是A表的某个字段数据来源于B表的主键(候选键)。
  • 外键约束，和外键不约束。 约束时，字段数据必须来源于关联表的主键(候选键)

虽然可以关联自己，但是并不常用

waijian

员工表的FK_depet_no字段就是员工表的外键。

1.2.4 约束Constraint

为了保证数据的完整正确，数据模型还必须有完整性约束。必须有值约束，某些列的值必须有值，不许为空null。

  • 域约束Domain Constraint：限定了表中字段的取值范围 -- (一般在代码中判断)
  • 实体完整性Entity Integrity：PRIMARY KEY约束定义了主键，就定义了主键约束。主键不重复且唯一，不能为空。
  • 引用完整性Referential Integrity：外键定义中，可以不是引用另一张表的主键(可以是任意字段)，但往往实际只会关注引用主键。
    • 插入规则：如果在表B插入一条记录，B的外键列的数据，必须是表A中存在的主键值。（自动生效，不需要指定）
    • 更新规则：定义外键约束时指定该规则。
    • 删除规则：定义外键约束时指定该规则。

更新规则和删除规则，可选项如下：

  • CASCADE:级联删除，删除被关联数据时，从表关联的数据全部删除。
  • SET NULL:从父表删除或更新行，会设置子表中的外键列为NULL，但必须保证子表没有指定 NOT NULL，也就是说子表的字段可以为NULL才行。
  • RESTRICT:如果从父表删除主键，如果子表引用了，则拒绝对父表的删除或更新操作。(保护数据)
  • NO ACTION:表中SQL的关键字，在MySQL中与RESTRICT相同。拒绝对父表的删除或更新操作。

外键约束，是为了保证数据完整性、一致性、杜绝数据冗余、数据错误。

建议先使用，等到连接的非常深入时，再使用代码逻辑来解决外键约束关系。

1.2.5 索引

可以看作是一本字典的目录，为了快速检索用的。空间换时间，显著提高查询效率。可以对一列或者多列字段设定索引。(B+树)

  • 主键索引(PRIMARY)：主键会自动建立主键索引，主键本身就是为了快速定位唯一记录。
  • 唯一索引(UNIQUE)：表中的索引列组成的索引必须唯一，但可以为空，非控制必须唯一。
  • 普通索引(KEY)：没有唯一性的要求，就是建了一个字典的目录而已。

1.3 实体联系

首先来看一下实体和联系：

  • 实体Entity：现实世界中具有相同属性的一组对象，可以是物理存在的事物或抽象的事物。
  • 联系Relationship：实体之间的关联集合。

总结一下：

  • 实体在数据库中就是一个一个表
  • 联系在数据库中就是记录与记录的对应关系

在关系型数据库中实体主要有以下三种联系:

类型 描述 解决方法
一对多联系
1:n		 一个员工属于一个部门，一个部门有多个员工 员工外键
部门主键
多对多联系
m:n		 一个员工属于多个部门，一个部门有多个员工 建立第三表
一对一联系
1:1		 假设有实体管理者，一个管理者管理一个部门，一个部门只有一个管理者 字段建在哪张表都行

一对一很少用。

1.4 视图

视图、也称虚表、开起来很像表。它是由查询语句生成的。可以通过视图进行CRUD操作。
作用：

  1. 简化操作，将复杂查询SQL语句定义为视图，可以简化查询。
  2. 数据安全，视图可以只显示真实表的部分列，或计算后的记过，从而隐藏真实表的数据

2 SQL

        Structured Query Language,是一种对关系数据库中的数据进行定义和操作的语言方法,是大多数关系数据库管理系统所支持的工业标准。

2.1 分类

  • 数据查询语言(DQL):data query language,也成为数据检索语句，作用是从表格中获取数据，确定数据怎么样在应用程序给出。关键字select是SQL用的最多的动词，其他DQL常用的保留字段有where、order by、group by和having
  • 数据操作语言(DML):data manipulation language,其中包括动词insert、update和delete，他们用于添加、修改、删除表中的行，也称动作查询语句。
  • 数据处理语言(TPL):它的语句能确保被DML语句影响的表的所有行及时得以更新。TPL语句包括，begin、transaction、commit和rollback
  • 数据控制语言(DCL):data control languag,它的语句通过grant或revoke 获得许可，确定 单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或revoke控制对单个列的访问。
  • 指针控制语言(CCL):它的语句，像 declare cursor、fetch into、update where current用于对一个或多个表单独执行的操作
  • 数据定义语言(DDL):data definition language,其语句包括create和drop、alter。在数据库中创建新表或删除表（create table 或者 drop table），为表加入索引等。是动作查询的一部分

我们日常开发会用到的SQL语言类型为：DML、DQL以及DDL

RDBMS的数据库、表的概念其实就相当于目录，文件，及内容

2.2 规范

编写SQL语句时，我们要遵顼如下规范：

  1. SQL语句大小写不敏感(一般建议，SQL的关键字、函数等大写)
  2. SQL语句末尾应该使用分号结束
  3. 注释
    • 多行注释 /*注释内容*/
    • 单行注释 --注释内容
    • MySQL 注释可以使用 #
  4. 使用空格或缩进来提高可读性
  5. 命名规范
    • 必须以字母开头
    • 可以使用数字、#、$和_
    • 不可使用关键字

3 MySQL

MySQL是Web世界中使用最广泛的数据库服务器，访问及管理mysql数据库的最常用标准化语言为SQL结构化查询语句。早先的MySQL使用的是MyISAM引擎，它是一种利用索引顺序存取数据的软件。从MySQL 4.0版本开始支持InnoDB引擎。

  • MyISAM，不支持事物，插入、查询速度快
  • InnoDB：支持事务，行级锁，MySQL 5.5起的默认引擎

3.1 安装MySQL

这里仅列出在Linux下的安装：

  • 通过yum进行安装
1、安装MySQL
yum install -y mysql-server (centos 7以下)
yum install -y mariadb-server (centos 7及以上)

2、启动服务
/etc/init.d/mysqld start (centos 7以下)
systemctl start maridab (centos 7及以上)

3、关闭服务
/etc/init.d/mysqld stop (centos 7以下)
systemctl stop maridab (centos 7及以上)
  • 编译安装
1.安装依赖包
yum install -y ncurses-devel libaio-devel
rpm -qa ncurses-devel libaio-devel

2.安装cmake编译工具
yum install -y cmake

3.上传源码包
rz -y mysql-5.5.49.tar.gz

4.解压并安装
tar xf mysql-5.5.49.tar.gz
cd mysql-5.5.49
 
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.49 \
-DMYSQL_DATADIR=/application/mysql-5.5.49/data \
-DMYSQL_UNIX_ADDR=/application/mysql-5.5.49/tmp/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii \
-DENABLED_LOCAL_INFILE=ON \
-DWITH_INNOBASE_STORAGE_ENAINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 \
-DWITHOUT_PARTITION_STORAGE_ENGINE=1 \
-DWITH_FAST_MUTEXES=1 \
-DWITH_ZLIB=bundled \
-DENABLED_LOCAL_INFILE=1 \
-DWITH_EMBEDDED_SERVER=1 \
-DWITH_DEBUG=0
 
make && make install && cd ..
5.创建链接文件
ln -s /application/mysql-5.5.49/ /application/mysql

6.创建数据库用户及配置文件
useradd -M -s /sbin/nologin mysql

7.初始化数据库
/application/mysql/scripts/mysql_install_db --user=mysql --basedir=/application/mysql --datadir=/application/mysql/data

8.定义环境变量
cp /application/mysql/bin/* /usr/local/sbin
或者
PATH=/application/mysql/bin:$PATH

9.开启数据库
/application/mysql/bin/mysql start

10.登录数据库
mysql
如果设置了用户密码
mysql -uroot -p123456

11.关闭数据库
/application/mysql/bin/mysql stop

12.设置密码
mysqladmin password 123456 -S /data/3306/mysql.sock

3.2 数据类型

MySQL的数据类型：

类型 含义
tinyint 1字节，带符号的范围是-128到127。无符号的范围是0到255。bool或boolean，就是tinyint，0表示假，非0表示真
smallint 2字节，带符号的范围是-32768到32767。无符号的范围是0到65535
int 整型，4字节，同Integer，带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295
bigint 长整型，8字节，带符号的范围是-9223372036854775808到9223372036854775807。无符号的范围是0到18446744073709551615
float 单精度浮点数精确到大约7位小数位
double 双精度浮点数精确到大约15位小数位
DATE 日期。支持的范围为'1000-01-01'到'9999-12-31'
DATETIME 支持的范围是'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
TIMESTAMP 时间戳。范围是'1970-01-01 00:00:00'到2037年
char(M) 固定长度，右边填充空格以达到长度要求。M为长度，范围为0~255。M指的是字符个数
varchar(M) 变长字符串。M 表示最大列长度。M的范围是0到65,535。但不能突破行最大字节数65535
text 大文本。最大长度为65535(2^16-1)个字符
BLOB 大字节。最大长度为65535(2^16–1)字节的BLOB列

BLOG：字节类型，存图片，存二进制文件，能不用则不用，只会用一个字符串来存图片的位置

3.4 用户及授权操作

授权用户：grant语句，取消权限：revoke语句

grant all on *.* to 'dahl'@'10.0.0.13' identified by '123456';
revoke all on *.* from dahl;
  • all：表示所有权限
  • *.*：表示所有库的所有表
  • 'dah'@'10.0.0.13'：表示用户名，和登陆的IP地址，%为通配符，可以表示任意地址

删除用户：

drop user dahl;

3.5 库操作

数据库在文件系统上就是用目录体现的，所以对库的操作，可以理解为对目录的操作。创建数据库,会在MySQL的data目录下创建同名文件夹

3.5.1 创建数据库

语法格式：create database 数据库名称

create database db_name;
create database db_name default charset utf8;
create database if not exists db_name character set utf8;
create database if not exists db_name character set utf8mb4 collater utf8mb4_general_ci;
  • character set:指定字符集。
  • utf8mb4：是utf8的扩展，支持4字节utf8mb4，需要mysql5.5.3+
  • collate:指定字符串的校队规则，用来做字符串的比较的。例如a、A谁大

3.5.2 删除数据库

语法格式：drop database 数据库名称

drop database db_name;

删除数据库目录，注意会删除库下的所有表文件

3.5.3 其他操作

查看数据库中存在的数据库

show databases;

使用(切换)当前使用的数据库(激活show tables语句)

use db_name;

查看创建库的信息

show create database test;

3.6 表操作

表在文件系统上是用文件体现的，所以对表的操作，可以理解为对文件的操作。

创建表,会在对应的库目录下创建表空间文件

3.6.1 创建单表

create table user_info(
    id int not null auto_increment primary key,
    name char(20),
    age int,
    gender char(1),
    deparment_id int,
    constraint 约束名称 foreign key(deparment_id) references dep_info(id)
)engine = innodb default charset=utf8;
 
--> 语法格式：
--> create table 表名(
-->      列名  类型  [是否为空] [是否默认值] [自增] [主键]  ,
-->      列名2  类型
-->      .... ....
-->      [ constraint 外键名称 foreign key(本表的被约束字段) reference 目标表名(字段) ]      
--> ) engine = 存储引擎名称 default charset = utf8;

各字段含义：

  • 列名
  • 数据类型
  • 是否可以为空(null/not null)
  • 是否默认值(default value)
  • 是否自增(auto_icrement)：一个表只能存在一个自增列并且必须有索引(普通索引或主键索引),类型必须是数值型。
  • 主键(primarr key)：数据不能为空、不能重复，可以加速查找(数据库的B树结构)
  • 外键(constraint) ：对表内某字段的内容进行约束,必须是某个表的某个字段已有的值,含外键的表可以理解为1对多，注意外键关联的两个字段数据类型要一致　

3.6.2 创建多表外键关联

  • 一对多：一个表的某个字段的数据来自于另一个表已存在的数据。
  • 多对多：一个表的某几个字段的数据来自于另一个或几个表已存在的数据。

一对多：

CREATE TABLE user_info (
	id INT NOT NULL auto_increment PRIMARY KEY,
	NAME CHAR (20),
	age INT,
	gender CHAR (1),
	deparment_id INT,
	CONSTRAINT 约束名称 FOREIGN KEY (deparment_id) REFERENCES dep_info (id)
) ENGINE = INNODB DEFAULT charset = utf8;

CREATE TABLE dep_info (
	id INT NOT NULL auto_increment PRIMARY KEY,
	title CHAR (32),
) ENGINE = innode DEFAULT charset = utf8;

多对多：需要关系表来表明多对多关系

CREATE TABLE `boy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
CREATE TABLE `girl` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
CREATE TABLE `b2g` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `b_id` int(11) DEFAULT NULL,
  `g_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_b` (`b_id`),
  KEY `FK_G` (`g_id`),
  CONSTRAINT 约束名称1 FOREIGN KEY (`b_id`) REFERENCES `boy` (`id`),
  CONSTRAINT 约束名称2 FOREIGN KEY (`g_id`) REFERENCES `girl` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.6.3 删除表

drop table tb_name;

3.6.4 查看表结构

desc table_name [ col_name ]

如果添加列明，表示查看表中某一列的结构。

3.6.4 其他操作

查看创建表的语句

show create table table_name;

查看当前库的所有表

show tables;

3.7 操作表数据(DML语句)

针对表的数据进行操作，主要涉及4类：

  • 增加 insert
  • 删除 delete
  • 修改 update
  • 查找 select

插入内容就相当于在表文件中按照MySQL的格式写数据

3.7.1 insert语句

语法格式1：insert into table_name(col_name1，col_name2) values(value1,value2)
含义：

  • field和value是一一对应的。
  • 当字段名省略时,表示插入字段所有数据，values后面的值需要列出所有字段

语法格式2：insert into table_name(col_name) select field from table_name
含义：

  • 把select查到的结果,当作数据来赋值给value
  • 查询到的数据字段要和插入的字段数量一致

语法格式3：insert into table_name (col_name,...) values (value1,..) on duplicate key update col_name=value1,...;

  • 如果主键冲突、唯一键冲突，就执行update后的语句，这条语句的意思是：主键不存在则新增记录，主键存在，则更新部分字段。

语法格式4：insert ignore into table_name (col_name1,..) values (values1,..);

  • 如果主键冲突、唯一键冲突，就忽略错误，返回一个警告
  • 一般用在大批量导入测试数据的场景下
INSERT INTO `test`.`employees_copy` (
	`id`,
	`emp_no`,
	`birth_date`,
	`first_name`,
	`last_name`,
	`gender`,
	`FK_depet_no`,
	`hire_date`
)
VALUES
	(
    '10',
    '10010',
    '1990-08-02',
    'Dahl',
    'Lee',
    'F',
    'd001',
    '1990-08-02'
);

INSERT INTO boy (id) SELECT
	id
FROM
	employees;

3.7.2 update 语句

语法格式：update [ignore] table_name set col_name = value1,... [ where xxx = xxx

  • 注意更新语句一定要带where条件，除非你真的知道你要改什么东西
UPDATE student
SET NAME = 'dahl'
WHERE
	id = 2;

3.7.3 delete 语句

语法格式：delete from table_name [ where xxx = xxx ]

  • 删除一定要包含条件
DELETE
FROM
	student
WHERE
	id = 2;

生产中不会真的删除数据，一般都会添加标致位来区分是否删除。除非你真的要删除。

3.7.4 select 语句

select 语句是 SQL中最复杂的语句，因为它非常灵活。下面是select语句的完整格式

SELECT
	[ DISTINCT ] select_expr ,...
FROM
	table_references 
WHERE
	where_definition  
GROUP BY
	{ col_name | expr | position } [ ASC | DESC ],...[ WITH ROLLUP ]
HAVING
	where_definition 
ORDER BY
	{ col_name | expr | position } [ ASC | DESC ] ,...
LIMIT {[ OFFSET ,] row_count | row_count OFFSET OFFSET }
[ FOR UPDATE | LOCK IN SHARE MODE ]]

FOR UPDATE 会把行进行锁定(排他锁)

3.7.4.1 简单查询

使用select col_name,... from table_name;

select * from student;
select id,name from student;
select id as std_no,name from student;
select std.id ,std.name from student as std;
select id,CONCAT(first_name,last_name) as name  from student;
  • 当仅存在一个表时，select后面的字段可以不用跟表明。
  • as 语句表示别名，是可以省略的，但是建议不要省略别名。
  • * 表示所有字段。
  • concat：字符串处理函数，用于合并

3.7.4.2 limit子句

对结果集进行过滤，限制输出的条目数

  • limit：限制输出的条目数
  • offset：与limit连用，表示偏移量
select * from employees limit 3 -- 输出3条信息
select * from employees limit 3 offset 10 -- 漂移10条以后再输出3条
select * from employees limit 10,3 --漂移10条以后再输出3条

注意：limt还支持: 偏移量,取几条。其实就是简写而已。上面例子第二句和第三句的含义是相同的。

Limit 必须在排序之后，因为排序会改变limit显示的记录

3.7.4.3 where子句

where子句主要用于做条件判断，它支持的操作符如下：

运算符 描述
= 等于
<> 不等于(!=也可以。只不过不太符合SQL的规范)

、<、>=、<=|大于、小于、大于等于、小于等于
BETWEEN|在某个范围之内，between a and b等价于[a, b]
LIKE|字符串模式匹配，%表示任意多个字符，_表示一个字符
IN|指定针对某个列的多个可能值
AND|与
OR|或

select first_name from employees where emp_no > 10;
select * from salaries where salary > 50000;
select * from salaries where salary between 60000 and 80000;  -- 60000 < salary < 80000
select * from employees where last_name like 'P%';
SELECT emp.emp_no AS no,CONCAT(first_name,' ', last_name) AS name from employees AS emp where emp.emp_no in (10009,100010,10020); -- 类似集合类型

关于使用like进行模糊匹配：

  • 建议使用左前缀，因为效率高。('%a%',和'%a'效率很低，建议不要使用，除非真的需要)
  • 能不用like，就不用，效率很低，如果真的要搜索，那么建议使用搜索引擎(solr、lucence、elasticsearch）

3.7.4.4 order by子句

对查询结果进行排序，可以升序ASC、降序DESC。

select * from salaries order by salary limit 3
select * from salaries order by salary desc;
select * from salaries ORDER by emp_no desc,salary desc

默认为升序，当指定多个列时,优先按照第一列排序、当第一列相同时，再使用第二列排序。

3.7.4.5 distinct 去重

distinct 用于对字段进行去重操作，相同的只取1个

select distinct emp_no from salaries;
select distinct emp_no,salary from salaries;
  • 当distinct包含连个字段时，那么就是联合去重了。即以(emp_np,salay)为整体进行去重

3.7.4.6 聚合函数

常用的聚合函数如下表：

函数 描述
COUNT(expr) 返回记录中记录的数目，如果指定列，则返回非NULL值的行数
COUNT(DISTINCT expr,[expr...])
AVG([DISTINCT] expr)
MIN(expr), MAX(expr)
SUM([DISTINCT] expr)

看需求，count一般会和分组进行连用，也常用在统计条数，count(id)，在统计条数时，建议使用主键字段，可以获得更快的统计速度。

3.7.4.7 group by和having

group by主要用于分组显示，如果想要对分组后的数据进行再次过滤，可以使用having子句。

select emp_no,sum(salary) as sum_sal from salaries group by emp_no having sum_sal > 1000000;
  • group by 跟多个字段时，可以理解为 将多个字段组合成元组来分组,当元组唯一时，进行聚合统计。
  • 除分组和聚合字段以外的其他字段（非分组字段），是不可预知的。
  • 对分组之后的数据再次过滤，就需要使用Having了。可以对聚合后的结果设置别名来在having中引用。

3.7.4.8 子查询

查询语句可以嵌套，内部查询就是子查询。

  • 子查询必须在一组小括号中。
  • 子查询中不能使用Order by。
select f.salary from (select * from salaries where salaries.emp_no > 10003) as f
select first_name from employees where emp_no in (select emp_no from salaries);

3.7.4.9 inner join(内连接)

inner join，简写为join ,用于连接两个表的内容,需要注意的是，连接方式为笛卡尔乘积，全部交叉。

  • A表有20条数据，B表有20条数据的话，那么a join b 就是 400条数据。
  • 当B表存在于A表同名的数据时，会被重命名

有以下两种方式：

  • 等值连接：只选某些field相等的元组（行），使用on限定关联的结果
  • 自然连接(natural join)：特殊的等值连接，会去掉重复的列。用的很少（一般仅仅会留下两表中主键相同的记录）
select * from employees natural join salaries;
select * from employees inner join salaries on employees.emp_no = salaries.emp_no

3.7.4.10 outer join(外连接)

outer join 外连接，主要分为：

  • 左外连接(left join)：从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。
  • 右外连接(right join)：从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL

在mariadb中，outer join 必须要配合on参数使用，否则会提示语法错误

select * from	employees left join salaries on employees.emp_no = salaries.emp_no where employees.emp_no > 10010;

在使用left jion时，on和where条件的区别如下：

  1. on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。
  2. where条件是在临时表生成好后，再对临时表进行过滤的条件。

自己和自己连接

给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工

表结构：

Create table If Not Exists Employee (Id int, Name varchar(255), Salary int, ManagerId int)
Truncate table Employee
insert into Employee (Id, Name, Salary, ManagerId) values ('1', 'Joe', '70000', '3')
insert into Employee (Id, Name, Salary, ManagerId) values ('2', 'Henry', '80000', '4')
insert into Employee (Id, Name, Salary, ManagerId) values ('3', 'Sam', '60000', 'None')
insert into Employee (Id, Name, Salary, ManagerId) values ('4', 'Max', '90000', 'None')

sql语句：

select emp.Name as Employee from Employee  as emp left join Employee as man_emp on emp.ManagerID = man_emp.Id where emp.Salary > man_emp.Salary;

4 储过程、触发器

  • 存储过程（Stored Procedure）：数据库系统中，一段完成特定功能的SQL语句。编写成类似函数的方式，可以传参并调用。支持流程控制语句。
  • 触发器（Trigger）：由事件触发的特殊的存储过程，例如insert数据时触发。

这两种技术，虽然是数据库高级内容，性能不错，但基本很少用了。它们移植性差，使用时占用的服务器资源，排错、维护不方便。

最大的原因，不太建议把逻辑放在数据库中。

5 事务Transaction

InnoDB引擎，支持事务。
事务，由若干条语句组成的，指的是要做的一系列操作。
关系型数据库中支持事务，必须支持其四个属性（ACID）：

特性 描述
原子性（atomicity） 一个事务是一个不可分割的工作单位，事务中包括的所有操作要么全部做完，要么什么都不做
一致性（consistency） 事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的
隔离性（isolation） 一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰
持久性（durability） 持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响

分析：

  • 原子性:要求事务中的所有操作，不可分割，不能做了一部分操作，还剩一部分操作；
  • 一致性:多个事务并行执行的结果，应该和事务排队执行的结果一致。如果事务的并行执行和多线程读写共享资源一样不可预期，就不能保证一致性。
  • 隔离性:就是指多个事务访问共同的数据了，应该互不干扰。隔离性，指的是究竟在一个事务处理期间，其他事务能不能访问的问题
  • 持久性:比较好理解，就是事务提交后，数据不能丢失。

6 MySQL隔离级别

隔离性不好，事务的操作就会互相影响，带来不同严重程度的后果。

6.1 隔离性不好，带来的问题

  1. 更新丢失Lost Update：事务A和B，更新同一个数据，它们都读取了初始值100，A要减10，B要加100，A减去10后更新为90，B加100更新为200，A的更新丢失了，就像从来没有减过10一样。
  2. 脏读：事务A和B，事务B读取到了事务A未提交的数据（这个数据可能是一个中间值，也可能事务A后来回滚事务）。事务A是否最后提交并不关心。只要读取到了这个被修改的数据就是脏读。
  3. 不可重复读Unrepeatable read：事务A在事务执行中相同查询语句，得到了不同的结果，不能保证同一条查询语句重复读相同的结果就是不可
    以重复读。
    • 例如，事务A查询了一次后，事务B修改了数据，事务A又查询了一次，发现数据不一致了。
    • 注意，脏读讲的是可以读到相同的数据的，但是读取的是一个未提交的数据，而不是提交的最终结果。
  4. 幻读Phantom read：事务A中同一个查询要进行多次，事务B插入数据，导致A返回不同的结果集，如同幻觉，就是幻读。数据集有记录增加了，可以看做是增加了记录的不可重复读。

6.2 隔离级别

隔离级别由低到高，如下表

隔离级别 描述
READ UNCOMMITTED 读取到未提交的数据
READ COMMITTED 读已经提交的数据，ORACLE默认隔离级别
REPEATABLE READ 可以重复读，MySQL的 默认隔离级别。
SERIALIZABLE 可串行化。事务间完全隔离，事务不能并发，只能串行执行
  • 隔离级别越高，串行化越高，数据库执行效率低；隔离级别越低，并行度越高，性能越高。
  • 隔离级别越高，当前事务处理的中间结果对其它事务不可见程度越高。
-- 设置会话级或者全局隔离级别
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}
-- 查询隔离级别
SELECT @@global.tx_isolation;
SELECT @@tx_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 禁用自动提交
SET AUTOCOMMIT = 0

小结：

  • SERIALIZABLE，串行了，解决所有问题
  • REPEATABLE READ，事务A中同一条查询语句返回同样的结果，就是可以重复读数据了。例如语句为(select *from user)。解决的办法有：
    1. 对select的数据加锁，不允许其它事务删除、修改的操作
    2. 第一次select的时候，对最后一次确切提交的事务的结果做快照
    3. 解决了不可以重复读，但是有可能出现幻读。因为另一个事务可以增删数据。
  • READ COMMITTED，在事务中，每次select可以读取到别的事务刚提交成功的新的数据。因为读到的是提交后的数据，解决了脏读，但是不能解决 不可重复读 和 幻读 的问题。因为其他事务前后修改了数据或增删了数据。
  • READ UNCOMMITTED，能读取到别的事务还没有提交的数据，完全没有隔离性可言，出现了脏读，当前其他问题都可能出现。

6.3 事务语法

START TRANSACTION或BEGIN开始一个事务，START TRANSACTION是标准SQL的语法。
使用COMMIT提交事务后，变更成为永久变更。
ROLLBACK可以在提交事务之前，回滚变更，事务中的操作就如同没有发生过一样（原子性）。
SET AUTOCOMMIT语句可以禁用或启用默认的autocommit模式，用于当前连接。SET AUTOCOMMIT = 0禁用自
动提交事务。如果开启自动提交，如果有一个修改表的语句执行后，会立即把更新存储到磁盘。

6.4 数据仓库和数据库的区别

本质上来说没有区别，都是存放数据的地方。但是

  • 数据库关注数据的持久化、数据的关系，为业务系统提供支持，事务支持；
  • 数据仓库存储数据的是为了分析或者发掘而设计的表结构，可以存储海量数据。

数据库存储在线交易数据OLTP（联机事务处理OLTP，On-line Transaction Processing）；数据仓库存储历史数据用于分析OLAP（联机分析处理OLAP，On-Line Analytical Processing）。数据库支持在线业务，需要频繁增删改查；数据仓库一般囤积历史数据支持用于分析的SQL，一般不建议删改。

posted @ 2019-03-15 20:49  SpeicalLife  阅读(1608)  评论(0)  编辑  收藏  举报