数据库笔记

关系型数据库：

范式：

什么是sql？

sql指结构化查询语言，使我们有能力访问数据库，是一种ANSI标准计算机语言

除了sql标准之外，大部分数据库都有自己的私有扩展。

数据库事务：

原子性（Atomicity）：要么都做，要么都不做

一致性（Consistency）：内部一致性，外部一致性

隔离性（Isolation）：并发事务之间不能互相干扰

持续性（Durability）：提交后数据的改变是永久性的

事务操作的三个读问题，两个写问题

脏读：读取到别人更新了但未确认提交的数据

不可重复读：两次读取数据不一样，中间被别人修改或删除

幻象读：两次读取行数不一样，中间被别人新增

第一类丢失更新：撤销时，把别人已经提交的更新数据覆盖了。

第二类丢失更新：A事务覆盖B事务已经提交的数据，造成B事务所做操作丢失。

事务隔离级别

未提交读：允许脏读

已提交读：允许其他事务修改当前事务所读取的数据记录，那个事务提交后，当前事务可以看到修改后的数据，大多数数据库的默认级别，例如：Oracle，DB2，Sql Server

可重复读：一个事务所读取的数据记录不允许被其他事务所修改，Mysql的默认隔离级别

序列化：所有的事务都是一个接一个的执行，不会发生任何事务之间的冲突问题。

几种数据库的表空间：

DB2：dms sms表空间

Oracle：数据表空间、系统表空间、回滚表空间、临时表空间

informix：rootdbs、workdbs、tmpdbs

mysql：数据和索引存储定义的表空间，共享表空间和独立表空间。

mysql的存储引擎：

         

 

SQL语法

添加表字段

alter table table1 add transactor varchar(10) not Null;

alter table   table1 add id int unsigned not Null auto_increment primary key

修改某个表的字段类型及指定为空或非空

alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];

alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

修改某个表的字段名称及指定为空或非空

alter table 表名称 change 字段原名称 字段新名称 字段类型 [是否允许非空

删除某一字段

ALTER TABLE mytable DROP 字段 名;

添加唯一键

ALTER TABLE `test2` ADD UNIQUE ( `userid`)

修改主键

ALTER TABLE `test2` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` )

增加索引

ALTER TABLE `test2` ADD INDEX ( `id` )

ALTER TABLE `category ` MODIFY COLUMN `id`  int(11) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (`id`);

 添加外键约束

alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key (从表外键字段) references 主表(主键字段);

select distanct 列名 FROM 表名； 去重

select * from dict_act where name like '_账%' or name like '_款%' or name like '_年%'; 第二个字为账、款、年的。

select * from dict_act where name regexp '[账款年].*[账款年]';  #没去重 会出现账账等情况。

用一条SQL语句查询出prd表中每个产品都大于800亿的银行名称

Select bank from prd group by bank having min(balance)>800;  //where 不能和合计函数一起使用

删除除了编号不同, 其他都相同的账户冗余信息

Delete * from 账户表 where 编号 not in (select id from (select min(编号) as id from 账户表 group by 账号，姓名，科目，科目名称，金额 ));

从 GL 表中查询出所有月份的发生额都比 2011 科目相应月份的发生额高的科目。GL表字段如下：accid:科目代码；mon：发生额月份；amt：发生额

Select accid from GL where acid not in (Select a.accid from GL a,(select mon,amt from GL where acid=’2011’group by mon ) b where a.mon=b.mon and a.amt<b.amt);

 

有两个表acct1、acct2, 都有zh(账号)和ye(余额)两个字段，如果acct2的账号在acct1中也有，就把acct2的余额修改为acct1中对应的余额

Update acct1 join acct2 on acct1.zh=acct2.zh set acct2.ye=acct1.ye;

 

select * s_year,sum(case when s.s_mon=1 then sal else null end) as “一月”，

sum(case when s.s_mon=2 then sal else null end) as “二月”，

sum(case when s.s_mon=3 then sal else null end) as “三月”，

sum(case when s.s_mon=4 then sal else null end) as “四月”，

from salarys s group by s_year;

 

在oracle中复制表结构和表数据：

1、复制表结构及数据：

create table new_table as select * from old_table

2、只复制表结构：

create table new_table as select * from old_table where 1<>1

3、复制表的指定字段：

create table new_table as select column1，column2... from old_table where 1<>1 （前提是column1...是old_table的列）

4、复制表的指定字段及数据：

create table new_table as select column1，column2... from old_table where（前提是column1...是old_table的列）

5、在已存在的表中插入数据：

A.两个表结构一样

insert into new_table select * from old_table (前提是必须要有一个new_table 表才能查数据)

B.表结构不一样：

insert into new_table （column1，column2...） select column1，column2... from old_table （注意：两个表中的要复制的列数据类型和长度最好要一致，要注意长度大小问题）

 

应用程序慢如牛，原因多多，可能是网络的原因、可能是系统架构的原因，还有可能是数据库的原因。

那么如何提高数据库SQL语句执行速度呢？有人会说性能调优是数据库管理员（DBA）的事，然而性能调优跟程序员们也有莫大的关系。

SQL调优

程序中嵌入的一行行的SQL语句，如果使用了一些优化小技巧，定能达到事半功倍的效果。

技巧1 比较运算符能用 “=” 就不用 “<>”

“=”增加了索引的使用几率。

技巧2 明知只有一条查询结果，那请使用 “LIMIT 1”

“LIMIT 1”可以避免全表扫描，找到对应结果就不会再继续扫描了。

技巧3 为列选择合适的数据类型

能用TINYINT就不用SMALLINT，能用SMALLINT就不用INT，道理你懂的，磁盘和内存消耗越小越好嘛。

1.1 在MySql中如何定义像Java中类型的Boolean类型数据？其实，mysql中 是没有直接定义成Boolean这种数据类型，它只能        定义成 tinyint(1) ；当booean 等于1 代表true,boolean 等于2的时候代表false；

1.2 Long型数据对应MySQL数据库中 bigint 数据类型；

技巧4 将大的DELETE，UPDATE  or  INSERT 查询变成多个小查询

能写一个几十行、几百行的SQL语句是不是显得逼格很高？然而，为了达到更好的性能以及更好的数据控制，你可以将他们变成多个小查询。

技巧5  使用UNION ALL 代替 UNION，如果结果集允许重复的话

因为 UNION ALL 不去重，效率高于 UNION。

技巧6 为获得相同结果集的多次执行，请保持SQL语句前后一致

这样做的目的是为了充分利用查询缓冲。

比如根据地域和产品id查询产品价格，第一次使用了：

 select price from order where id=123 and region='shanghai';

那么第二次同样的查询，请保持以上语句的一致性，比如不要将where语句里面的id和region位置调换顺序。

技巧7 尽量避免使用 “ SELECT  * ”

如果不查询表中所有的列，尽量避免使用 SELECT *，因为它会进行全表扫描，不能有效利用索引，增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销。

技巧8 WHERE 子句里面的列尽量被索引

只是“尽量”哦，并不是说所有的列。因地制宜，根据实际情况进行调整，因为有时索引太多也会降低性能。

技巧9 JOIN 子句里面的列尽量被索引

同样只是“尽量”哦，并不是说所有的列。

技巧10 ORDER BY 的列尽量被索引

ORDER BY的列如果被索引，性能也会更好。

技巧11 使用 LIMIT 实现分页逻辑

不仅提高了性能，同时减少了不必要的数据库和应用间的网络传输。

技巧12 使用 EXPLAIN 关键字去查看执行计划

EXPLAIN 可以检查索引使用情况以及扫描的行。

其他

SQL调优方法有很多种，同样的查询结果可以有很多种不同的查询方式。其实最好的方法就是在开发环境中用最贴近真实的数据集和硬件环境进行测试，然后再发布到生产环境中。