数据库笔记
关系型数据库:
范式:
什么是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 (注意:两个表中的要复制的列数据类型和长度最好要一致,要注意长度大小问题)