品读《数据库系统概念》

本人浅读《数据库系统概念》前11章，品读经典，大有裨益，全篇结合了《mysql必知必会》一书，将所有sql语句改为mysql语法。由于本书的12-16章太过晦涩，参考了中国人民大学的数据库系统概论课程，根据该课程整理笔记，与君分享。本人才疏学浅，且经验不足，许多知识仅仅停留在理论阶段，欢迎指正和补充，不胜感激。

引言

设计数据库系统的目的是为了管理大量信息，对数据的管理既涉及信息存储结构的定义，又涉及信息操作机制的提供。此外数据库系统必须提供所存储信息的安全性保证，即使在系统崩溃或有人企图越权访问时也应保障信息的安全性。如果数据将被多用户共享，那么系统必须设法避免可能产生的异常结果。

数据视图

数据库系统是一些相互关联的数据以及一组使得用户可以访问和修改这些数据的程序的集合。数据库系统的一个主要目的是给用户提供数据的抽象视图，也就是说，系统隐藏关于数据存储和维护的某些细节。

数据抽象

• 物理层。物理层详细描述复杂的底层数据结构
• 逻辑层。描述数据库中存储什么数据以及这些数据间存在什么关系。
• 视图层。只描述数据库的某个部分，用户不关心所有信息，只需要访问数据库的一部分，视图层的定义是为了使用户与系统的交互更简单。

在物理层，数据库系统为数据库程序设计人员屏蔽了最底层的存储细节。

在逻辑层，程序设计人员正是在这个抽象层次上使用某种程序设计语言进行工作，数据库管理员也常常在这个层次上工作。

在视图层，视图层定义了数据库的多个视图，数据库用户看到的是这些视图。

实例和模式（instance & schema）

数据库实例：特定时刻存储在数据库中的信息的集合

数据库模式：数据库的总体设计。数据库系统可以分为不同的模式。物理模式在物理层描述数据库的设计，逻辑模式在逻辑层描述数据库的设计，数据库在视图层也可以有几种模式，有时称子模式，它描述了数据库的不同视图。

数据模型（data model）

数据库结构的基础是数据模型。数据库模型是一个描述数据、数据联系、数据语义以及一致性约束的概念工具的集合。数据模型提供了一种描述物理层、逻辑层以及视图层数据库设计的方式。

• 关系模型（relational model）。关系模型用表的集合来表示数据和数据间的联系。每个表有多个列，每一列有唯一的列名。每个表包含某种特定类型的记录，每个记录类型定义了固定数目的字段。表的列对应于记录类型的属性。当今大量的数据库系统都是基于关系模型。
• 实体联系模型（E-R）。这种模型现在在构建关系模型之前使用，来更好的构建关系模型。
• 基于对象的数据模型（object-based data model）。可以看做是E-R模型增加了封装、方法和对象标识等概念后的扩展。
• 半结构化数据模型。允许先沟通能够类型的数据项含有不同属性集的数据定义，一般用于配置文件。可扩展标记语言XML广泛的用来表示半结构化数据模型。

数据库语言

SQL、中级SQL、高级SQL三章介绍SQL语言。本文将语言内容都替换成了mysql语法！

数据库系统提供数据定义语言来定义数据库模式，以及数据操纵语言来表达数据库的查询和更新。这两种语言并不是分离的，它们构成了单一数据库语言的不同部分。

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

数据库操纵语言使得用户可以访问或操纵那些按照某种适当的数据模型组织起来的数据。

• 对存储在数据库中想信息进行检索
• 向数据库中插入信息
• 删除信息
• 修改信息

数据定义语言

数据库模式是通过一系列定义来说明的，这些定义由数据定义语言（Data-Definition Language，DDL）来表达。

存储在数据库中的数据必须满足某些完整性约束。DDL语言提供了指定这种约束的工具。

• 域约束。每个属性都必须对应一个所有可能的取值构成的域（整数、字符、日期等）。声明一种属性属于属于某种具体的域就相当于约束它可以取的值。
• 参照完整性。一个关系中给定属性集上的取值也在另一关系某属性集的取值中出现（表现为外键）。
• 断言。数据库需要时刻满足的某一条件。域约束和参照完整性是断言的特殊形式。（CREATE ASSERTION语句）
• 授权（authorization）对于不同的用户在数据库中的不同数据值上允许不同的访问类型。最常见的是读权限、插入全险、更新权限、删除权限。可以赋予用户所有权限或者没有或者部分权限。

数据库设计

数据库设计和R-R模型章讨论设计过程和ER模型内容

设计过程

数据库设计的主要内容是数据库模式的设计。

1.初始阶段是全面刻画预期的数据库用户的数据需求，此阶段成果是制定出用户需求的规格文档

2.概念设计阶段。选择数据模型，将需求转换成数据库的概念模式。用ER模型表示概念设计。概念设计定义了数据库中表示的实体，实体的属性，实体之间的联系，以及实体上的约束。

3.逻辑设计阶段。将高层的概念模式映射到要使用的数据库系统的实现数据模型上。E-R模型映射到表上。涉及关系数据理论和规范化。可以使用3NF设计方法

4.物理设计阶段。指定数据库的物理特性，这些特性包括文件组织的形式以及内部的存储结构的选择。

E-R模型

E-R数据模型描述实体对象，以及这些对象间的联系。

实体通过属性集合来表述。

联系是几个实体之间的关联。

实体集是同一类型所有实体的集合。

同一类型所有联系的集合称作联系集。

数据库逻辑模式可以用E-R图进行图形化表示（下图示例，第一张图为国内课本定义的ER图，用navicat工具构建的时候如第二张图片，会标明主键，表示外键关系）。最常用的方法之一是采用统一建模语言（UML）。

规范化

概念设计ER模型构建完毕后，需要将其进行规范化，即ER模型到关系表的转换，目标是生成一个关系模式集合，使我们存储信息时没有不必要的冗余，同时又能很轻易的检索数据。这个过程需要用到后面的规范化理论。

数据存储和查询

后面有几章会单独讨论存储和文件结构、索引和散列、查询处理、查询优化。

存储管理器

存储管理器将各种应用程序提交的DML语句翻译为底层文件系统命令，因此，存储管理器负责数据库中的数据的存储、检索和更新。存储管理器负责与文件管理器进行交互，原始数据通过操作系统提供的文件系统存储在磁盘上。

存储管理部件包括：

• 权限及完整性管理器，它检测是否满足完整性约束，并检查视图访问数据的用户的权限。
• 事务管理器，它保证即使发生故障，数据库也保持在一致的状态，并保证并发事务的执行不发生冲突。
• 文件管理器，它管理磁盘存储空间的分配，管理用于标识磁盘上所存储信息的数据结构。
• 缓冲区管理器，它负责将数据从磁盘上存到内存中来，并决定哪些数据应被缓存在内存中。

存储管理器实现了几种数据结构，作为数据库系统物理实现的一部分：

• 数据文件，存储数据库自身
• 数据字典，存储关于数据库结构的元数据，尤其是数据库模式。
• 索引，提供对数据项的快速访问。提供了指向包含特定值的数据的指针。散列是另外一种索引方式，在某些情况下速度更快。

查询处理器

• DDL解释器，解释DDL语句并将这些定义记录在数据字典中。
• DML编译器，将查询语言中的DML语句翻译为一个执行方案，包括一系列查询执行引擎能理解的低级指令。一个查询被翻译为多种执行方案，进行查找优化，即找出代价最小的。
• 查询执行引擎，执行低级指令。

事务管理

关于事务管理更详细内容单独有事务、并发、恢复 三章内容。

• 原子性：要么做完，要么就不做。

• 一致性：如果从 A 账户转账到 B 账户，不可能因为 A 账户扣了钱，而 B 账户没有加钱吧。

• 隔离性：类似数据库进程间的互斥，比如对同一条数据同时进行删除和修改。事务隔离级别（Transaction Isolation Level），从上往下，级别越来越高，安全性越来越高。隔离性是保证一致性的重要手段。

1. READ_UNCOMMITTED
2. READ_COMMITTED（oracle默认隔离级别）
3. REPEATABLE_READ（mysql默认隔离级别）
4. SERIALIZABLE

• 持久性：执行insert，数据库保证有一条数据永久存放在磁盘中。

事务管理器包括并发控制管理器和恢复管理器。原子性和持久性的保证是恢复管理器的职责。发生故障，数据库必须被恢复到开始执行以前的状态。当多个事务同时更新数据库时，并发控制管理器控制并发事务之间的相互影响，保证数据库一致性和隔离性。

关系模型

术语

在关系模型的术语中，关系用来指代表，而元组用来指代行。属性指代的是表中的列。关系实例表示一个关系某时刻的元组集合。对于关系的每个属性，都存在一个允许取值的集合，称为该属性的域。

当我们谈论数据库时，我们必须区分数据库模式（数据库逻辑设计）和数据库实例（数据库快照）。类似关系和关系实例的区别。

码

• 超码：是一个或多个属性的集合，这些属性的组合可以使我们在一个关系中唯一标识一个元组。超码中可能包含无关紧要的属性，即去掉这些属性也能唯一标识。
• 候选码：去掉所有无关紧要属性后的属性，又称为最小超码。
• 主码：代表被数据库设计者选中的，主要用来在一个关系中区分不同元组的候选码。习惯上把一个关系模式的主码属性列在其他属性前面。主码属性划上下划线。
• 外码：一个关系模式r1可能在它的属性中包括另一个关系模式r2的主码，这个属性在r1上称作参照r2的外码。

SQL

SQL、中级SQL、高级SQL三章内容由于联系紧密，并于一章中。所有的语法都是MYSQL

语言简介

MYSQL、ORACLE以及SQL SERVER是基于客户机-服务器的数据库。

SQL语言有一下几个部分（前两部分是本章讨论内容，我们实践为主，不将其特意分开）：

• 数据定义语言（Data-Definition Language，DDL）：提供定义关系模式、删除关系以及修改关系模式的命令。
• 数据操纵语言（Data-Manipulation Language，DML）：提供从数据库中查询信息，以及在数据库中插入元组删除元组、修改元组的能力。
• 完整性（integrity）：SQL DDL包括定义完整性约束的命令，保存在数据库中的数据必须猫族所定义的完整性约束。破坏完整性约束的更新是不允许的。
• 视图定义（view definition）：SQL DDL包括定义视图的命令
• 事务控制（transaction control）：SQL包括定义事务的开始和结束的命令
• 嵌入式SQL和动态SQL：定义SQL语句如何嵌入到通用编程语言中
• 授权（authorization）：SQL DDL包括定义对关系和视图的访问权限的命令

mysql安装与卸载

mysql5.7下载安装包https://pan.baidu.com/s/1VKyhkilLoqmELCBm6jp62w 提取码：qqhn

mysql安装下载环境配置https://blog.youkuaiyun.com/WA_MC/article/details/106657652

navicatFormysql下载压缩包https://pan.baidu.com/s/1PX-YbGDIAZ560hspLcdgfw 提取码：12j5

mysql卸载

1.控制面板卸载（或geek）
2.c盘programFiles、programFiles 86文件夹内删除 mySQL文件夹
3.c盘隐藏文件夹programData 也有mySQL文件夹（数据库数据），删除

mysql引擎

为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。建表的时候可以指定扫描引擎（表后使用ENGINE=，如果不使用，默认引擎为MyISAM）

• InnoDB是一个可靠的事务处理引擎，mysql5.6开始支持全文本搜索
• MEMORY在功能等同MyISAM，但由于数据存储在内存中，速度很快
• MySIAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理

外键不能跨引擎。

连接、建库、等命令

控制台连接本机mySQL

mysql -u root -p

输入密码

控制台连接远程mySQL

mysql -h主机地址 -u用户名 -p

eg:mysql -h192.168.1.103 -uroot -p

输入用户密码

退出mysql

quit

建库命令

create database wollo default character set utf8;

删库

drop database wollo;

显示表信息

show tables [from database];

显示创建表语句

show create table shiro.student;

显示库

show databases;

显示创建库信息

show create database shiro;

打开库(相当于进入此库中，进行操作不再需要是哪个库的表)

use shiro

查看表结构

describe student

服务器状态

show status

显示授权用户的安全权限

show grants

数据类型

数值类型（常用）：
int（20）
float（8，1）
double（20，1）

字符类型：
char(固定长度)
varchar(动态分配长度）
TEXT：64K字符
LONGTEXT：4GB字符
MIDIUMTEXT：16K字符
TINYTEXT：255字节字符
SET：64个串组成的预定义集合的0个或多个串
ENUM：64K个串组成的预定义集合的0个或多个串
日期类型：
date：格式为yyyy-MM-dd
datetime:yyyy-MM-dd hh:mm:ss 8字节
timestamp: 自动进行时区的转换，4字节
time:时间
year:年份
二进制类型：
BLOB：最大长度为64KB
MEDIUMBLOB：最大长度16MB
LONGBLOB：最大长度4GB
TINYBLOB：最大长度255字节

创建表

下例中有主键约束和外键约束的三种方法。unique唯一约束、default约束用法也类似。mysql没有检查约束，oracle有。主键约束=unique + not null 。IF NOT EXISTS用于检查创建时是否已经存在同名表。

create table teacher(
	id int(10) primary key auto_increment,
	name varchar(20)
);

create table student(
    id int(10) primary key auto_increment,  -- 添加主键1.
    name varchar(20) not null,
    age int(3),
    tid int(3) REFERENCES teacher(id), -- 添加外键1.
    -- 添加主键2.，可以设定报错信息  constraint pk_student_id primary key(id)
    -- 添加外键2. constraint fk_teacher FOREIGN key (tid) REFERENCES teacher(id)
    
);
-- 添加主键3. alter table student add constraint pk_student_id primary key(id)
-- 添加外键3，alter table student add constraint fk_teacher FOREIGN key(tid) REFERENCES teacher(id)
-- 删除主键约束 alter table student drop primary key
-- 删除外键约束 alter table student drop FOREIGN key fk_teacher

-- 添加自增约束 alter table student modify id int(10) auto_increment;
-- 删除表drop table student
-- 增加字段 alter table student add sphone int(11) 
-- 修改字段类型 alter table student modify sphone varchar(11)
-- 修改字段名 alter table student change sphone phone varchar(11)
-- 删除字段名 alter table student drop column phone  
-- 修改表名 rename table student  to student2

插入

insert into teacher values(default,'老师1');
insert into teacher values(default,'老师2');
insert into student values(default,'学生1',15,1);
insert into student values(default,'学生2',26,1);
insert into student values(default,'学生3',35,1);
insert into student values(default,'学生4',33,1);
insert into student values(default,'学生5',22,2);
insert into student values(default,'学生6',34,2);
insert into student values(default,'学生7',27,2);

指定字段插入

insert into student(id,name,age,tid) values(15,'学生15',28,2);

可以插入查询结果

insert into student (select ...)

多条插入（这样的插入比两个SQL语句效率高！）

insert   into student(id,name,age,tid) values
(15,'学生15',28,2),(16,'学生16',27,1);

insert操作可能很耗时，可能降低等待处理的select语句的性能，如果数据检索是最重要的，可以降低insert语句的优先级（当然这也适用于update 和 delete语句），在insert 和into之间添加LOW_PRIORITY

删除

delete from student where name='学生1'
-- 删除表所有信息
delete from student

更新

update student  set name='yanyue' where id=2

更新时一行或多行出现错误，整个update操作被取消，如果希望即使发生错误，也继续完成更新，使用UPDATE IGNORE TABLE

查询

单关系查询

select * from student

有时候我们想强行删除重复，使用distinct关键词

select  distinct tid  from student 

select 字句还可含带有+、-、*、/ 运算符的算数表达式

select distinct tid+1 from student

where字句，sql允许where字句中使用逻辑连词and or和not。

select * from student where age>30

多关系查询

分类方式1：等值连接和非等值连接

等值连接

student和teacher表进行连接，生成笛卡尔积（表1的每一行与表2的所有行配对，而不管它们逻辑上是否正确，笛卡尔积的元组个数为表1、表2元组个数的积）后用where字句进行筛选

select * from student,teacher where student.tid=teacher.id

非等值连接

select *from emp e,salgrade s where e.sal>s.losal and e.sal<=s.hisal 

分类方式2：字段名必须相同的自然连接

自然连接

自然连接（natural join，natural可省略）需要字段名相同，连接结果该字段名只有一列，去重

select *from emp natural  join dept

指明使用指定字段进行等值筛选，两表字段也必须同名

select *from natural  join dept using(deptno)

分类方式3：内连接和外连接。我们将不保留未匹配分组的连接运算称作内连接（Inner join）。

内连接

-- inner可省略
select student.id,student.name,age,tid,teacher.name tname  from student  inner join teacher 
on  student.tid=teacher.id

这里student.id是因为两表中有同名属性，需要进行区分，teacher.name tname 是表示给该字段起一个别名，查询表的时候显示的是该别名。

外连接
外连接分为左外连接，右外连接，全外连接

左外连接=左表全部记录+相关联结果

select * from student 
left outer join teacher 
on student.tid=teacher.id

右外连接=右表全部记录+相关联结果

select * from student 
right outer join teacher 
on student.tid=teacher.id

全外连接=左表全部记录+右表全部记录+相关联结果=左外连接+右外连接-相关联结果（即去重复）

mysql中没有全外连接

可以使用union实现

SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
UNION
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

分类方式4：自连接，和同一张表连接

自连接

在单条select语句中不止一次引用相同的表

select e1.*,e2.* from emp e1,emp e2 where e1.mgr=e2.empno

嵌套子查询

select * from student where tid in (select id from teacher)

some

> some表示至少比集合中某一个大，允许<=some，>=some，=some(等价in，等于集合中任意一个)，<>some（不等于集合中任意一个即可）

-- 至少比某一个大
select * from student s1 
where age > some(select s2.age from student s2 where tid=1)

>all表示比集合中所有都大，允许<=all，>=all，=all（等于集合中所有），<>all(等价not in，不等于集合中所有）

select * from student s1 
where age > all(select s2.age from student s2 where tid=2)

exists

select * from student s1 where
EXISTS(select * from teacher where s1.tid=teacher.id)

也有not exists

select * from student s1 where
NOT EXISTS(select * from teacher where s1.tid=teacher.id)

from子句中子查询

mysql中必须给个别名

select * from  (select * from student where tid=1) stu

计算字段子查询

select name,(select teacher.name from teacher where student.tid=teacher.id ) '老师名字' 
from student 
where tid IS NOT NULL

基本运算

字符串表示

• SQL使用单引号代表字符串。如果单引号是字符串中的组成部分，那就用两个单引号字符来表示，比如it’s写成it’'s。

• mysql默认大小写不敏感，即 ‘a’=‘A’ 为真，可以更改成敏感的。

字段连接符

select student.name,concat('老师名字是',teacher.name) '老师信息'   
from student  
join teacher on  student.tid=teacher.id

like通配符

在字符串上可以使用like操作符来实现模式匹配，我们使用两个特殊的字符来描述，是大小写敏感的，特殊字符即%和_用转义字符即可。‘ab%cd%’ 匹配所有以ab%cd开头的字符串。可以用not like搜寻不匹配项。

• %表示匹配任意子串
• _表示匹配任意一个字符

eg

 select * from student  where name like '学生%'

 select * from student  where name like '学__'

 -- 包含特殊字符用反斜杠 
 select * from student where name like '%\%'

limit

limit一般用于分页

select * from student limit 1,2

表示从第1个元组开始（0开始计数）查询2个元组

REGEXP

注意：mysql仅支持少部分正则！前半部分介绍标准正则，后面单独介绍mysql正则！

常用正则 https://c.runoob.com/front-end/854/

转义字符(多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但mysql要求两个反斜杠！)

标准字符（字母大写是取反）（小数点不能匹配 \n）

自定义字符（中括号中^表示取反,挨着的表示或） 正则表达式的特殊符号在中括号中失去特殊意义，除 ^（除某某之外）和-（范围）有了另外的特殊含义。

量词

？表示0到1次，+代表至少一次，*代表任意次。^表示开头。$表示结尾。小数点匹配除\n以外任意

\d{6} 六个数字
\d\d{6} 七个数字
(\d\d){6} 十二个数字

\d{3,6} 三到六个数字 贪婪模式（8个数字，6+2）
\d{3,6}? 三到六个数字 非贪婪模式（8个数字，3+3+2）

\d{3,}至少三个数字

\b用法

gaoqi\b 右侧不能是\w
\bgaoqi 左侧不能是\w
\bgaoqi\b 两侧不能有\w

^和$定位符

^i 文本开头是i
$i 文本结尾是i

|

表示或

分组

正则表达式通过使用括号将表达式分为不同的分组，识别的方法是通过从左至右搜寻左半括号，遇到第一个左半括号时，则该左半括号与对应的右半括号所包含的内容即为第一分组，以此类推 。例如，在表达式((A)(B( C)))，有四个这样的组：((A)(B( C)))、(A)、(B( C))、( C)

分组取值：当通过正则表达式匹配到字符串时，可以使用matcher.group(i)等方式取到第i个分组所匹配到的子字符串。

（）：目标字符串需要匹配exp,并将该分组匹配的子文本保存到自动命名的组里，自动以数字从1开始分组；

(?exp)：目标字符串需要匹配exp,并将该分组匹配的子文本保存到名称为name的组里，也可以写成

后面几个方式都不会视为分组

?: 匹配pattern，包含该pattern

正向预查：

（？=pattern） 匹配后面为pattern的，但匹配结果不包含该pattern

（？!pattern） 匹配后面不为pattern的

负向预查：

（？<=pattern） 匹配前面为pattern，不包含pattern的

（？<!pattern） 匹配前面不为pattern的

练习

1.座机或手机号码
(?:0\d{2,3}-\d{7,9})|(?:1[3,5,7,8,9]\d{9})

2.邮箱
[\w,]+@[a-z0-9A-Z]+(.[a-z]{2,4})

Java中正则

正则在java程序里面所有一个斜杠变两个斜杠，自己解析一个，mysql解析一个，另外java中的特殊字符也需要用反斜杠！

//fdasfsd87987fsdfadf8789，匹配这个字符串 \w+
//正则在java程序里面一个斜杠变两个斜杠
Pattern p=Pattern.compile("\\w+");
Matcher m=p.matcher("fdasfsd87987fsdfadf8789");
System.out.println(m.matches()); //尝试将整个字符序列与该模式匹配
//结果为true

Pattern p=Pattern.compile("\\w+");
Matcher m=p.matcher("fdasfsd87987&&fsdfadf8789");
//System.out.println(m.matches());//匹配不上会报错
System.out.println(m.find());//类似指针找到之后指针下移指向下一个匹配的
System.out.println(m.group());
System.out.println(m.find());
System.out.println(m.group());
//true
//fdasfsd87987
//true
//fsdfadf8789

//正则分组
Pattern p=Pattern.compile("([a-z]+)([0-9]+)");
Matcher m=p.matcher("fdasfsd87987&&fsdfa432&&df8789");
while(m.find()){
   
   
      System.out.println(m.group());
      System.out.println(m.group(1));
      System.out.println(m.group(2));
}
//        fdasfsd87987
//        fdasfsd
//        87987
//        fsdfa432
//        fsdfa
//        432

//replaceAll方法
Pattern p=Pattern.compile("[a-z]");
Matcher m=p.matcher("fdasfsd87987&&fsdfa432&&df8789");
String str=m.replaceAll("*");
System.out.println(str);
//*******87987&&*****432&&**8789

//split与正则
String str="a,b,c";
String []arrs=str.split("\\d+");
System.out.println(Arrays.toString(arrs));

爬取网页URL

	public static void main(String[] args) throws IOException {
   
   
		 List<String> list;
	     list = getMather("https://www.163.com","utf-8");
	     for (String str:list) {
   
   
	          System.out.println(str);
	     }
	}
	public static List<String> getMather(String url,String charSet) throws IOException {
   
   
	     List<String>list=new ArrayList<>();
	     String  destStr= String.valueOf(getURLContent(url,charSet));
	     Pattern p=Pattern.compile("href=\"(http.+?)\"");//.+表示任意个数的任意字符 ？表示0次或一次匹配  即非贪婪模式！
	     Matcher m=p.matcher(destStr);
	     while(m.find()){
   
   
	         list.add(m.group(1));
	     }
	     return list;
	}
	//获得URL对应网页源码内容
    public static StringBuilder getURLContent(String urlStr,String charSet)throws IOException{
   
   
        StringBuilder sb=new StringBuilder();
        URL url=new URL(urlStr);
        BufferedReader reader=new BufferedReader(
                new InputStreamReader(url.openStream(),charSet));
        String temp="";
        while((temp=reader.readLine())!=null){
   
   
            sb.append(temp);
        }
        return sb;
    }
//	    https://static.ws.126.net/163/f2e/www/index20170701/css/head~65fdc48dac14c.css
//	    https://static.ws.126.net/163/f2e/commonnav2019/css/commonnav_headcss-89aa49bc1b.css
//	     https://www.163.com/
//	     https://www.163.com/#f=topnav
//	     https://m.163.com/newsapp/#f=topnav
//	     https://open.163.com/#f=topnav
//	     https://hongcai.163.com/?from=pcsy-button
//	     https://u.163.com/aosoutbdbd8
//	     https://mail.163.com/client/dl.html?from=mail46

mysql正则

不确定mysql拥有正则中的哪些功能，无法一一列举

-- mysql匹配不区分大小写，如果想区分，REGEXP后使用binary关键字
select * from student where name REGEXP '..1'
-- 特殊字符使用双反斜杠，斜杠本身用四个反斜杠(mysql必知必会一书中是三个，我实践的是4个)
select * from student where name REGEXP '\\\\'

字符类

order by

order by字句默认使用升序（asc表示升序，默认的不用写），可以在后面加上desc表示降序

select * from student order by age

select * from student order by age desc

排序可以在多个属性上进行，先按照tid升序，再在相同的tid中按照age降序

select * from student order by tid,age desc

between

比较运算符说明一个值是小于或等于某个值，同时大于或等于另一个值。

select * from student where age between 20 and 30

还可以使用not between

select * from student where age not between 20 and 30

NOT

mysql支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS对各种条件取反有很大区别！

IN

IN操作符一般比OR操作符执行更快

select * from student where age in(26,22)

AND OR

AND优先于OR，不过实践中尽量用括号区别优先级

select * from student where age >20 and tid=1

select * from student where age >20 or tid=1

NULL

• 在通过过滤选择出过滤值可能为空的行时，在匹配过滤时或不匹配过滤时都不会返回为该值为空的行

• 另外还有一个筛选空字段的IS NULL

select * from student where tid IS NULL

• 当使用ORDER BY时，首先呈现NULL值。如果你用DESC以降序排序，NULL值最后显示。当使用GROUP BY时，所有的NULL值被认为是相等的，故只显示一行（如下测试）。

select tid from student group by tid 

集合运算

并运算
union自动去除重复，如果想保留重复，使用union all代替

(select * from student where age<20)
UNION
(select * from student where age>30)

注：mysql只实现了union，没有交运算，差运算。

聚集函数

我们经常需要汇总数据而不用把它们实际检索出来，为此MYSQL提供了专门的函数，使用这些函数，MYSQL查询可用于检索数据，以便分析和报表生成。所有聚集函数都能执行多个列上的计算。

AVG

计算列平均值（忽略值为null的行）

select AVG(age) from student 

count

计算符合条件行的数目（忽略null值）

select count(*) from student 

对特定列中有值的行进行计数

select count(tid) from student 

MAX

select MAX(age) from student 

MIN

select MIN(age) from student 

SUM

select SUM(age) from student 

以上5个聚集函数都可以使用DISTINCT和ALL分别表示去重和不去重（默认）。这些函数是高效的，比在应用程序中计算要快得多！

分组

前面学习了聚集函数，如果需要从逻辑上分组，计算每个组的聚集函数怎么办？能按照多个列进行分组group by tid，age

select tid,count(*) from student group by tid

HAVING子句过滤分组，分组后我们希望过滤掉某些不符合条件的分组，与where用法无差，只不过where过滤行，HAVING过滤分组

select tid,count(*) from student group by tid HAVING tid IS NOT NULL

select子句顺序

SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT

先通过select from找出表，where子句筛选，group分组，Having筛选分组，order by分组排序，limit定位分组

select * from student where tid IS NOT NULL
group by age Having age>15
order by age limit 1,2

函数

相对来说，函多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。

大多数SQL实现支持以下类型的函数

• 删除或填充值，转换大小写的文本函数

Left()返回串左边的字符，从1开始计数。Right()返回串右边字符

select id,LEFT(name,1) from student

Length()返回串的长度

select id,LENGTH(name) from student

Locate()找出串的一个子串，返回子串开始位置，从1开始计数

select id,LOCATE('ck',name) from student

Upper()、Lower()转换大小写

select id,UPPER(name) from student

LTrim()、RTrim()
去掉串左边空格、去掉串右边空格

SubString()

-- 从第二个位置开始截2个字符的子串
select id,SUBSTRING(name,2,2) from student

Soundex()
Soundex是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。

--比如cindy与cindi
select * from student where SOUNDEX(name)=SOUNDEX('cindi')

• 在数值数据上进行算数操作的数值函数

• 处理日期和时间
  

（此处系转载https://www.cnblogs.com/zluckiy/p/10295387.html）

1.时间转字符串
DATE_FORMAT(日期，格式字符串)

SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%s’);

2.字符串转时间
STR_TO_DATE(字符串，日志格式)

SELECT STR_TO_DATE(‘2019-01-20 16:01:45’, ‘%Y-%m-%d %H:%i:%s’);

3.时间转时间戳
select unix_timestamp(now());

4.字符串转时间戳
select unix_timestamp(‘2019-01-20’);

5.时间戳转字符串
select from_unixtime(1451997924,‘%Y-%d’);

附日期格式如下：
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一

select date_format(NOW(),'%Y-%m-%d') '当前时间';

2005年9月的所有订单（假设每个分组有自己的日期）
可以用between and来表示日期范围

select cust_id,order_num from orders 
where Data(order_date) between '2005-09-01' AND '2005-09-30'

另一种不需要知道该月有多少天的方法

select cust_id,order_num from orders 
where Year(order_date)=2005 AND  MONTH(order_date)=9

视图

在此之前，我们一直都在逻辑模型层操作，即操作的关系都是实际存储在数据库中的。让所有用户都看到整个逻辑模型是不合适的，处于安全考虑我们需要隐藏特定的数据。除了安全考虑，我们可能希望创建一个更符合特定用户直觉的个人化的关系集合。
、
不是逻辑模型的一部分，但作为虚关系对用户可见的关系称为视图view。视图在概念上包含查询结果中的元组，但不进行与计算和存储，存储的是与视图关系相关联的查询表达式，当视图关系被访问时，其中的元组是通过计算查询结果而被创建出来的。

create view student_teacher(id,name,age,tname)
AS
(select student.id,student.name,age,teacher.name from student,teacher 
where  student.tid=teacher.id)

select * from student_teacher

查看创建视图的语句

show create view student_teacher

更新视图

create or replace view

删除视图

drop view student_teacher

视图可以直接当普通关系来用即可。视图不能索引，也不能有关联的触发器或默认值。前面我们看到视图都是和select语句一起使用，因为视图主要用于数据检索，而不用于更新，且很多视图是无法更新的（比如有聚集函数的等）。

事务

sql标准规定当一条SQL语句被执行，就隐式的开启了一个事务，也可以显示声明事务开始位置。默认的MYSQL行为是自动提交所有更改，即一条sql语句一个事务。
可以通过更改默认提交行为，set autocommit=0表示不自动提交，该字段针对连接而不是整个服务器

for example1：

now autocommit=1

delete from student where name='jack'
rollback

rollback无效，因为已经自动commit了，commit之后无法回滚

for example2:

set autocommit=0
delete from student where name='jack'
rollback

rollback success，经过测试，如果不显示设置事务开始，一条语句就代表一个事务，不手动commit的话，执行下一跳sql语句会自动commit，无法回滚

for example3:

start TRANSACTION
delete from student where name='jack';
update  student set name='jimmy' where name='jim'; 
rollback

rollback success! rollback或commit之后，事务会自动结束

for example4:

如果事务中sql有错误，到错误处就停止执行了，直接执行以下语句，在第三行停并回滚前面操作，不执行commit，如果单独执行commit，依然会提交，第二行被提交，且无法rollback。这个地方我想过既然有自动回滚为什么在做JDBC的时候为什么还需要在catch中rollback，因为不手动回滚事务不会结束，这样可能会导致锁表之类的问题

start TRANSACTION;
delete  from  student where name='cindy' ;
delete from student where name'charlie';
commit;

for example5：

start TRANSACTION;
savepoint delete1;
delete  from  student where name='cindy' ;
savepoint delete2;
delete from student where name='charlie';

rollback to delete2

rollback to delete1

索引

索引分类

通俗点说，索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。

索引种类：

普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）unique
主键索引：加速查询 + 列值唯一 +　表中只有一个（不可以有null）
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索 ，下面单独讲解，即全文本搜索

索引创建

创建普通索引语法：

create index student_name on student(name)

创建unique索引
unique约束和create unique index的效果等价

create unique index student_name on student(name)

组合索引

create index student_name_id on student(name,tid)

删除索引

drop index  student_name on student

全文本搜索

MyISAM支持全文本搜索，innodb 从1.2.x 开始才增加了全文索引支持，MySQL 5.6 开始支持。

在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。
从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。经测试ngram不是默认的解析器，默认解析器如果有中文搜索不到英文，也不能搜索中文

全文本搜索开启

create  table student(
    id int(10) primary key auto_increment,
    name varchar(20) not null,
    age int(3),
    tid int(3),
	FULLTEXT(name) WITH PARSER ngram
)ENGINE=InnoDB

虽然语法可以在建表时这么写，但不要在导入数据前使用FULLTEXT，因为更新索引耗时。可以在建表用alter启用。

alter table student add FULLTEXT(name) WITH PARSER ngram

FULLTEXT索引指定的列，MySQL自动维护该索引，在增加、更新、删除行时候，索引自动更新。

使用全文搜索

select * from student where match(name) AGAINST('good')

可以看到为什么搜索good，doog却也匹配了？
这是因为ngram解析器分词
n=1: ‘g’, ‘o’, 'o, ‘d’
n=2: ‘go’, ‘oo’, ‘od’
n=3: ‘goo’, ‘ood’
n=4: ‘good’

查看分词n值，应该默认是2

show variables like '%token%';

所以doog中有oo，能够匹配，所以符合搜索条件，默认解析器是没有分词的。

查询扩展
比如查询good，首先进行一个基本的全文本搜索，找出与搜索条件匹配的所有行，其次mysql检查这些匹配行并选择所有有用的词，再使用这些有用的词进行搜索，第二次搜索的结果是排序在第一次之后的。

select * from student where match(name) AGAINST('good' WITH query expansion)

布尔文本搜索

搜索模式分为自然搜索和布尔搜索，自然搜索返回结果会自动按照相关性排序，相关性高的在前面。相关性的值是一个非负浮点数，0表示无相关性。在布尔方式中，不按相关性排序返回。

自然搜索中，将自然搜索作为字段可以得到每个分组相关性值。

select student.*,match(name) AGAINST('good') as rank from student  

比如匹配good而不匹配student（使用默认解析器示例，ngram情况拆分太过复杂）

select * from student where 
match(name) AGAINST('+good -student' IN BOOLEAN MODE)

使用说明

• 在索引全文本数据时，如果搜索三个（我mysql5.7测试的是不包括三个）以下字符的词，搜索结果为空。
• 如果返回结果大于50%，则该搜索无效，但这个规则不适用于IN BOOLEAN
• 如果表中的行数少于三行，全文搜索不返回结果
• 忽略词中的单引号，例如don’t索引为dont

授权

mysql创建一个名为root的用户账号，它对整个mysql服务器有完全的控制。可能在对非工作的数据库实验MYSQL时，用root进行登录很好，不过在日常工作中，决不能使用root，应该创建一系列的账号，有的用于管理，有的供开发人员，有的供用户。

通过命令查看用户，MYSQL用户账号和信息都存储在名为mysql的数据库中

创建一个用户

CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;

username – 用户名

host – 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如 果想让该用户可以从任意远程主机登陆,可以使用通配符%

password – 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登 陆服务器

CREATE USER 'wityy'@'localhost' IDENTIFIED BY '123456'; 
CREATE USER 'wityy'@'172.20.0.0/255.255.0.0' IDENDIFIED BY '123456'; 
CREATE USER 'wityy'@'%' IDENTIFIED BY '123456'; 
CREATE USER 'wityy'@'%' IDENTIFIED BY ''; 
CREATE USER 'wityy'@'%';

重新命名用户

 rename user ‘oldname’to 'newname';

删除用户

DROP USER ‘username’@'host’;

改密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

查看用户权限

show grants for ben

USAGE表示根本没有权限。

授权包括数据上的授权和数据库模式上的授权。

• 数据的授权包括增删改查四种权限。当用户提交查询或更新时，SQL执行先基于该用户获得过的权限检查此查询或更新是否授权过，如果没有，则拒绝执行该SQL。

• 数据库模式上的授权，用户创建、修改或删除关系。拥有某些形式的权限的用户还可以把这样的权限授予给其他用户（转授权限），或者收回此前授出的权限。

权限层次：

grant 权限 on 权限层次 to 用户

• 整个服务器，授予全局权限：on *.*
• 整个数据库，on db_name.*:拥有db_name数据库下的所有表的权限
• 特定的表，使用on db_name.table
• 特定的列 grant update(age) on shiro.student to ben;
• 特定的存储过程

关于权限转授GRANT OPTION，用户被授予了某个权限，那么默认情况下，该用户是不能把这个权限授予给其他人的。但是可以使用WITH GRANT OPTION这个子句来让该用户可以将权限再授予给其他人。

grant select on shiro.student to  'ben