数据库知识（自整理）

一、数据库的DDL、DML、DCL的区别与理解

1.DML (Data Manipulation Language)数据操作语言

主要有insert、delete、update、select，主要是用来对数据库里的数据进行增删改查等操作。

2.DDL (Data Definition Language)数据定义语言

主要有create、alter、drop、rename、truncate等，主要是用来定义和改变表（table）的结构，列的数据类型等。

3.DCL (Data Control Language)数据控制语言

主要有grant,deny,revoke等，主要是用来设置或更改数据库用户或角色权限。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

4.其他语言：TCL (Transaction Control Language)(事务控制语言)

COMMIT - 保存已完成的工作，
SAVEPOINT - 在事务中设置保存点，可以回滚到此处，
ROLLBACK - 回滚，
SET TRANSACTION - 改变事务选项

二、数据操作：

1、常用的的sql语句

• 选择

select * from table1 where --范围  

• 插入

insert into table1(field1,field2) values(value1,value2)  

• 删除

delete from table1 where --范围  

• 更新

update table1 set field1=value1 where --范围  

• 查找

select * from table1 where field1 like '%value1%' ---like的语法很精妙，查资料!  

• 排序

select * from table1 order by field1,field2 --[asc升序/desc降序]  

• 总数：

select count as totalcount from table1  

• 求和：

select sum(field1) as sumvalue from table1  

• 平均：

select avg(field1) as avgvalue from table1  

• 最大：

select max(field1) as maxvalue from table1  

• 最小：

select min(field1) as minvalue from table1  

2、sql语句修改某个字段的部分内容（replace）

• 修改mysq数据库中的某一字段值

update 表名 set 字段 = replace(字段, ‘要修改的内容’ , ‘修改后的内容’);

update table1 set field1=replace(field1, 'm' , 'n')

• 修改sqlserver数据库中某一字段的值，可以使用replace()函数进行修改

update 表名 set s_id=replace(s_id,‘123’,‘321’) where s_id like ‘123%’;
replace()函数中，第一个参数表示的是需要进行操作的字段或值，第二个参数表示的需要被替换的字符串，第三个参数表示进行替换的字符串。

update table1 set field1=replace(field1, 'm' , 'n') where field1 like '123%'

3、取前10条记录

1、access：

select top (10) * from table1 where 1=1 

2、db2：

select column from table where 1=1 fetch first 10 rows only 

3、mysql：

select * from table1 where 1=1 limit 10 

4、sql server:

--读取前10条：
select top (10) * from table1 where 1=1 
--读取后10条：
select top (10) * from table1 order by id desc

5、oracle：

select * from table1 where rownum<=10 

三、数据库操作：

1、创建数据库

CREATE DATABASE database-name   

2、删除数据库

drop database dbname  

3、对数据库进行操作：

分离数据库： sp_detach_db;
附加数据库：sp_attach_db 后接表明，附加需要完整的路径名
如何修改数据库的名称:

sp_renamedb 'old_name', 'new_name'  

四、表操作：

1、创建新表

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)  

根据已有的表创建新表：

--A
create table tab_new like tab_old (使用旧表创建新表)  
--B
create table tab_new as select col1,col2… from tab_old definition only  

2、删除新表

drop table tabname   

3、增加一个列

Alter table tabname add column col type  
--注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加	varchar类型的长度。  

4、删除某一列：

删除数据库某一列的值分几种情况：
1）不删除字段，只更新其中的内容
update 表 set 字段=null

update table1 set field1=null

2）删除字段
alter table 表 drop column 字段

alter table table1 drop column field1

5、SQL语句增加列、修改列、删除列

1、增加列：

alter table tableName add columnName varchar(30)  

2、 修改列类型：

alter table tableName alter column columnName varchar(4000)  

3、修改列的名称：
EXEC sp_rename ‘tableName.column1’ , ‘column2’ (把表名为tableName的column1列名修改为column2)
4、删除列：
alter table tableName drop column columnName
5、添加主键： Alter table tabname add primary key(col)
删除主键： Alter table tabname drop primary key(col)

6、SQL修改列名，增加列，删除列语句的写法

1.修改数据表名
ALTER TABLE[表名.]OLD_TABLE_NAME RENAME TO NEW_TABLE_NAME;
2.修改列名
ALTER TABLE [表名.]TABLE_NAME RENAME COLUMN OLD_COLUMN_NAME TO NEW_COLUMN_NAME;
3.修改列的数据类型
ALTER TABLE [表名.]TABLE_NAME MODIFY COLUMN_NAME NEW_DATATYPE;
4.插入列
ALTER TABLE [表名.]TABLE_NAME ADD COLUMN_NAME DATATYPE;
5.删除列
ALTER TABLE [表名.]TABLE_NAME DROP COLUMN COLUMN_NAME;

五、索引

1、创建索引：create [unique] index idxname on tabname(col….)
2、ALTER TABLE eva_tr_device ADD INDEX (feeder_id)
删除索引：drop index idxname
注：索引是不可更改的，想更改必须删除重新建。

六、视图

创建视图：

create view viewname as select statement   

删除视图：

drop view viewname  

七、事务

八、sql优化

创建索引
创建视图