数据库基础知识

主键：唯一标示一行记录，使用单一的列来标示，或者组合多个字段形成“复合主键”。

索引：无索引的表就是一个无序的行集。必须查看表中的每一行数据，看它是否与所需的值匹配（这是一个全表扫描）。

可以把索引想象成汉语字典的按笔画查找的目录，在查找目录时就可以非常快速地查找到所需要的数据。

虽然索引可以提高数据查询的速度，但是任何事物都是双刃剑，它也有些缺点：

（1）      索引占据一定磁盘空间

（2）      索引减慢了数据插入和删除的速度。因为每次插入和删除时都需要更新索引，一个表拥有的索引越多，写操作时的平均性能下降就越大。

外键：两张表通过字段关联起来称为“表关联”。某张表的一个字段，作为另一张表的主键，

该字段称为“外键”。

创建表：

create table students (s_name varchar(10)not null, s_age int not null, s_sex varchar(4) not null, s_ismonitor varchar(5)default 'NO', s_classnum int, primary key(s_name), foreign key (s_classnum)references class(c_num));

插入数据：

最简单：

insert into class(c_num,c_count)values('1','30');

insert into class(c_count,c_num)values('15','2');

insert语句可以不指定要插入的列表，这种情况下将按照定义表中字段的顺序来进入插入。

insert into class values('3','23');

外键是指向另一个表中已有数据的约束，因此外键值必须是在目标表中存在的。如果插入的数据在目标表中不存在就会导致违反外键约束的异常。

更新数据：

update class set c_count = '50';会修改所有的值

update class set c_count = '25' where c_num= '2';带有where子句

外键是指向另一个表中已有数据的约束，因此外键值必须是在目标表中存在的。如果更新后的数据在目标表中存在则会导致违反外键约束异常。

删除数据：

delete from class where c_count>30;带where子句

查询语句：

select c_num as NUM from class wherec_count<30 and c_count>20;

select c_num as NUM from class wherec_count>30 or c_count<20;

标准函数：

MAX	计算字段最大值
MIN	计算字段最小值
AVG	计算字段平均值
SUM	计算字段合计值
COUNT	统计数据条数

select max(c_count) as max from class;

select avg(c_count) from class;

select sum(c_count) from class;

select max(c_count),min(c_count) fromclass;

select count(*) from class;

排序：

不指定排序方式，默认是asc

select * from class order by c_count desc;

select * from class order by c_count asc;

select * from class order by c_count asc,c_numdesc;

在同人数的时候，按照班级的降序

 

通配符：

SQL中的通配符过滤使用LIKE关键字。

单字符匹配：_

多字符匹配：%

select * from students where s_name like'%n_';

 

范围值：

IN();Between

或者用and语句加<,>等

select * from class where c_num in(2,3,4);

select * from class where c_num between 3and 7;

获取部分结果：

Limit

select * from class limit 5,10;获取第6个数据开始的连续10个数据

select * from class limit 5,-1;获取第6个数据开始的后面所有数据

select * from class limit 5;获取前5个数据

分组：

数据分组用来将数据分为多个逻辑组，从而可以对每个组进行聚合运算。SQL语句中使用GROUP BY子句进行分组，使用方式为“GROUP BY”分组字段。分组语句必须和聚合函数一起使用，GROUP BY子句负责将数据分成逻辑组，而聚合函数则对每一个分组进行统计计算。

GROUP BY子句将检索结果划分为多个组，每个组是所有记录的一个子集。

GROUP BY子句必须放到Select语句之后，如果Select子句中有where子句，则GROUPBY子句必须放到WHERE语句之后。

需要分组的所有列都必须位于GROUPBY子句的列名列表中，也就是没有出现在GROUP BY子句中的列（聚合函数除外）是不放在到Select语句后的列名列表中的。？？？？？

数据分组与聚合函数：

select c_count, count(*) as class fromclass group by c_count;得到每个分组的个数

Having语句：

有的时候需要对部分分组进行过滤，而聚合函数不能在WHERE语句中使用，必须使用Having子句来代替。

select c_num,count(*) from class group byc_count having count(*)>1;

限制结果集行数:

select * from class order by c_num desclimit 2,5;

从结果集的第3行（第一行是0行，也就是0,1,2（对应这个2））开始，显示5行。

抑制数据重复：DISTINCT关键字

select distinct c_count from class;

索引：

索引用来提高数据的检索速度，而约束用来保证数据的完整性。

索引是建立在数据表上的。

索引是针对字段的，因此创建索引的时候需要指定要在哪个字段创建索引。还可以为多个字段创建一个索引，这样可以指定与索引相关的字段列表。

Create Index 索引名 On 表名(字段1，字段2，字段3…)

其中“索引名”为被创建的索引的名称，这个名称必须是唯一的；“表名”为要创建索引的表；“字段1…”为组成这个索引的字段列表，允许一到多个。

create index id_class on class(c_num);

索引创建后是可以被删除的，删除索引使用的语句

Drop Index 索引名 On 表名

数据库约束：

非空约束、唯一约束、CHECK约束、主键约束、外键约束。

NOTNULL、UNIQUE、

CHECK约束会检查输入到记录中的值是否满足条件，如果不满足这个条件则对数据库做的修改不会成功。

用法：create table teacher (t_name varchar(10) not null,t_age intnot null check(t_age>18));

Primary Key、ForeignKey References

 

表连接：

从多个表中进行检索才能得到想要的数据。表连接使用JOIN关键字将一个或者多个表按照彼此间的关系连接为了一个结果集。

使用SQL中的表连接则可以简化开发，并且由于数据库系统会对表连接进行查询优化，所以使用表连接进行数据的检索会非常快速。

表连接就像连接两张数据表的连线，线的两端分别在两张表的特定字段上。在使用表连接的时候必须显示地指定这个关联关系。

内连接：

内连接组合两张表，并且基于两张表中的关联关系来连接他们。使用内连接需要指定表中哪些字段组成关联关系，并且需要指定基于什么条件进行连接。

Inner Join table_name On Condition

其中table_name为被关联的表名，condition则为进行连接时的条件。

select s_name,s_age from students inner join class on s_classnum = class.c_num where class.c_count=3;

不等值连接：

select s_name,s_age from students innerjoin class on s_classnum = class.c_num where class.c_count<16;

交叉连接：

交叉连接不存在ON子句。交叉连接会将涉及到的所有记录都包含在结果集中。可以采用两种方法来定义交叉连接，分别是隐式连接和显示的连接。

隐式：select *from students,class;

显示：select *from students cross join class;

自连接：

表与其自身连接称为自连接。自连接并不是独立于交叉连接、内连接、外连接等连接方式，而只是这些连接方式的一种特例，也就是说交叉连接、内连接、外连接等连接方法中只要参与连接的表是同一张。

虽然大多都是在连接不同的表，但是有时候表也需要与自身连接，其主要用途就是检索一张表内部的匹配情况。

Selecto1.FNumber,o1.FPrice,o1.FtypeId,o2.FNumber,o2.FPrice,o3.FTypeId from T_Order o1inner join T_Order o2 On o1.FTypeId = o2.FTypeId ando1.Fid<o2.Fid;
外连接：

有些情况下，我们要求无法匹配的NULL值也要显示到结果集中。

 

外部连接的语法与内部连接几乎一样，主要区别就是对于空值的处理。外部连接不需要两个表具有匹配记录，这样可以指定某个表中的记录总是放到结果集中。

根据哪个表中的记录总是放到结果集中，外部连接分为3中类型：右外部连接，左外部连接和全外部连接。

三者的共同点是都返回符合连接条件的数据，这一点和内部连接是一样的，不同点在于它们对不符合连接条件的数据的处理。三者的不同点说明如下：

左外部连接还返回左表中不符合连接条件的数据。

右外部连接还返回右表中不符合连接条件的数据。

全外部链接还返回左表中不符合连接条件的数据及右表中不符合连接条件的数据，它其实是左外部连接和右外部连接的集合。

这里的左表和右表是相对于JOIN关键字来说的，位于JOIN关键字左侧的表即被称为左表，而位于JOIN关键字右侧的表即被称为右表。

Select o.fnumber,o.fprice,o.fcustomerid,c.fname,c.fage from t_order o left outer join t_customer c ono.fcustomerid = c.fid;

Select o.fnumber,o.fprice,o.fcustomerid,c.fname,c.fage from t_order o right outer join t_customer c ono.fcustomerid = c.fid;

Select o.fnumber,o.fprice,o.fcustomerid,c.fname,c.fage from t_order o full outer join t_customer c ono.fcustomerid = c.fid;

子查询：（单值子查询和列子查询（又称表子查询，可以返回多行多列））

SQL允许将一个查询语句作为一个结果集供其他SQL语句使用，就像使用普通的表一样，被当做结果集的查询语句称为子查询。

可以将子查询看成一张临时的数据表，这张表在查询开始时被创造，在查询结束时被删除。

Select * From (Select * from T2 whereFAge<20);

 

SELECT语句可以嵌套在其他语句中，比如SELECT、INSERT、UPDATE及DELETE等，这些被嵌套的SELECT语句就称为子查询。可以这么说，当一个查询依赖于另外一个查询时就可以使用子查询。子查询有两种类型，一种是只返回一个单值的子查询，这时它可以用在一个单值可以使用的地方；另一种是返回一列值的子查询，这时子查询可以看做是一个在内存中临时存在的数据表。

单值子查询：

单值子查询的语法和普通的Select语句没有什么不同，唯一的限制就是子查询的返回值必须只有一行记录，而且只能有一个列。

select 1 as f1, 2, (select min(c_num) fromclass) as f3;

select 1 as f1, 2, (select min(c_num) fromclass) as f3, (select max(c_num) from class);

如果一个子查询返回的值不止一行记录或者多个列的话，都不能当做标量子查询。

列子查询：

列子查询可以返回一个多行多列的结果集。又称为表子查询，表子查询可以看做一个临时的表，表子查询可以用于Select、Insert等多个场合。

Select * From (Select * from T2 whereFAge<20);

Select列表中的标量子查询

Select fid,fname,(

       Selectmax(fyearpublished) from t_book where t_book.fcategoryid=t_gategory.fid;

)

From t_gategory;

通过where这个子查询被连接到外部的select查询语句中。返回最新出版年份。

Where子句中的标量子查询：

Select freaderid from t_readerfavoritewhere fcategoryid = (select fid from t_category where fname=’story’)这个不能用连接实现，连接必须有相同字段，这里是fcategoryid相同，但是我们并不知道fname=’story’的id，所以必须先子查询。

 

集合运算符和子查询：

SQL提供了对这样的集合进行操作的运算符，包括IN、ANY、ALL及EXISTS等。

IN运算符可以用来匹配一个固顶集合的某一项。

ANY和SOME是同义词，用法和功能和ANY相同。ANY必须和其他的比较运算符共同使用，而且必须将比较运算符放在ANY关键字之前，ANY的意思就是任意的，英文的意思。

Select * from t_reader where fyearofjoin =ANY (select fyearpublished from t_book);

所有出版年入会的读者

Select * from t_reader where fyearofjoin IN(select fyearpublished from t_book);

作用一样=ANY和IN是一样的

除了等于“=”还有其他符号< > <=…

Select * from t_reader where fyearofjoin <ANY (select fyearpublished from t_book);

ANY不能用于固定值，这点和IN不同不能用于ANY(1,2,3,4);

ALL关键字就是全部的意思，也必须和比较运算符共同使用

Select * from t_reader where fyearofjoin< ALL(select fyearpublished from t_book);

小于所有的，所有会员入会之前的书。同样ALL不能用于固定值。

EXISTS运算符是单目运算符，它不与列匹配，因此它也不要求待匹配的集合是单列的。EXISTS运算符用来检查每一行是否匹配子查询，可以认为EXISTS就是用来测试子查询的结果是否为空的，如果结果集为空匹配结果为False，负责匹配结果为True。

Select * from t_book where exists(select *from t_reader where fprovince = ‘ShanDong’);

如果EXISTS的括号右边是null的，则返回false。

 

SQL调优

索引是数据库调优的最根本的优化方法，很多优化方法都是围绕索引展开的。

根据索引的顺序和数据表的物理顺序是否相同，可以把索引分成两种类型：聚簇索引（顺序相同），非聚簇索引（反之）。汉语字典中拼音目录就是聚簇索引（和数据的顺序一致），偏旁目录就是非聚簇索引。

当创建聚簇索引时每个表只能有一个聚簇索引。非聚簇索引可以有很多个。

由于索引需要占据一定的存储空间，而且索引也会降低数据插入、更新和删除的速度（因为需要同时更新索引），所以应该只创建必要的索引，一般在检索时用的字段则可以创建索引。

索引还会造成存储碎片的问题。当删除一条记录时将会导致对应索引中该记录的对应项为空，由于索引是采用B树结构存储的，所以对应的索引项并不会被删除。从而会造成碎片。

和磁盘产生碎片的原因差不多。这些存储碎片不仅占用了存储空间，而且降低了数据库运行的速度。如果发现索引中存在过多的存储碎片的，要进行“碎片整理”了，最方便的手段就是重建索引。

一般地，系统访问数据库中的数据，可以使用两种方法：全表扫描和索引查找。

全表扫描必然速度很慢。

如果表中有索引并且待匹配条件符合索引的要求，DBMS就不会执行全表扫描，而是直接到索引中查找，这将大大加快检索的速度。

优化手段：

1.      创建表的索引

2.      使用预编译查询

程序通常根据用户的输入来动态执行SQL语句，这时应该尽量使用参数化SQL，这样不仅可以避免SQL注入漏洞更新，最重要的是数据库会对这些参数化SQL执行预编译。这样第一次执行的时候DBMS会为了这个SQL语句进行查询优化并且执行预编译，以后在执行这个SQL的时候就直接使用预编译的结果，于是可以大大提高执行的速度。

3.      调整where子句中的连接顺序（对数据的筛选不同）

4.      Select语句中避免使用*

5.       尽量将多条SQL语句压缩到一句SQL中

每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句查询优化、发送执行结果，都很耗时。

6.       用where子句代替Having子句

要避免使用Having子句，因为Having只会检索出所有记录之后才对结果集进行过滤。如果能够通过WHERE子句限制记录的数目，那么就能减少这方面的开销。Having中的条件一般用于聚合函数（Group By）的过滤，其他的应该用where。

7.      使用表的别名

在使用SQL语句中有连接多个表时，请使用表的别名并把别名前缀置于每个列名上。这样可以减少解析的时间并减少那些由列名歧义引起的语法错误。

8.       用EXISTS代替IN

在查询中，为了满足一个条件，往往需要对另一个进行连接，在这种情况下，使用EXISTS而不是使用IN，通常将提高查询的效率，因为IN子句将执行一个子查询内部的排序和合并。

9.       用表连接代替EXISTS

10.   避免在索引列上使用计算

在Where子句中，如果索引列是计算或者函数的一部分，DBMS的优化器将不会使用索引而使用全表扫描。

11.  使用UNION ALL 替换 UNION

12.  避免隐式类型转换造成的全表扫描

13.  防止检索范围过宽

事务：

如果要执行一系列操作，而这些操作最终是以整体原子操作的形式完成的话。

银行转账案例。

从严格的定义上来讲，事务是形成一个逻辑工作单位的数据库操作的汇集；通俗地说，事务是能以整体的原子操作形式完成的一系列操作。

事务具有4个基本特性，即原子性、一致性、隔离性和持久性。

原子性：一个事务中所有的数据库操作，是一个不可分割的整体，要么全部执行，要么全部无效果。

一致性：一个事务独立执行的结果，将保持数据库的一致性，即数据不会因事务的执行而被破坏。在事务执行过中，可以违反一致性原则，并产生一个临时的不一致状态。

隔离性：在多个事务并发执行的时候，系统应该保证与这些事务先后独立执行时的结果一样，即并发执行的任务不必关心其他事务。对于每一个事务来讲，那一刻看起来好像只有它在修改数据库一样。事务系统是通过对后台数据库使用同步协议来实现隔离性的，同步协议使一个事务与另外一个事务相分离。如果事务对数据进行了锁定，可以使并发的事务无法影响该数据，知道锁定接触为止。

持久性：一个事务一旦完成全部操作以后，它对数据库的所有操作将永久地反映在数据库中。持久性保证了系统在操作的时候免遭破坏。