数据库DDL与DML

一、数据定义语言DDL (负责创建，修改，删除表，索引和视图等对象)

1. 创建表

create table 表名(

列名1 数据类型1  not null,  // 列名不能为空

列名2 数据类型2,

.....

constraint PK_列名 primary key (列名1,列名2), //主键

)

2. 修改表名

alter table 旧表名 rename to 新表名;

3. 删除表

drop table 表名;

4. 向表中添加外键约束

alter table 子表 add constraint FK_列名 foreign key (外键列名) refrences 主表(主键);

5. 删除约束

alter table 表名 drop constraint 约束名;

6. 增加列

alter table 表名 add 列名 数据类型;

7. 修改列类型

alter table 表名 modify 列名 新数据类型;

8. 修改列名

alter table 表名 rename column 旧列名 to 新列名;

9. 删除列

alter table 表名drop column 列名;

10. 创建索引

create index idx_列名 on 表名(列名);

11. 删除索引

drop index 索引名;

12. 清除表中的数据

truncate table 表名;

二、数据操纵语言DML (负责数据库中数据的插入，查询，删除等操作)

1. 数据插入

insert into 表1 (target.field1,target.field2,...) select (source.field1,source.field2,...) from 表2;

2. 数据查询

(1) 使用别名(可加或不加as关键字)

select p.name [as]“姓名”,p.age [as]“年龄” from people p;

(2) where 查询条件(使用>,>=,<,<=,=,!=)

select ename,salary from emp where salary > 2000;

(3) 使用LINKE条件( 模糊查询 )

l %:表示0到多个字符

l _:标识单个字符

例：查询职员姓名中第二个字符是‘A’的员工信息

select ename,job from emp where ename like ‘_A%’;

(4) 使用AND 或OR 关键字(当返回的结果满足多个条件或多个条件之一时)

select ename,salary,job from emp where salary >1000 and job=’clerk’;

(5) 使用IN /NOT IN (用比较操作符IN(list)来取出符合列表范围中的数据)

select ename,job from emp where deptno in (10,20,30);

(6) BETWEEN...ADN...(用来查询符合某个值域范围条件的数据)

select ename salary from emp where salary between 1500 and 2000;

(7) 使用distinct过滤重复

select distinct deptno,job from emp;

注：deptno和job联合起来不重复

(8) 排序order by （默认升序asc、降序desc,必须出现在select中的最后一个字句）

select ename,salary from emp order by ename;

注：多个列排序order by ename desc,salary asc;

(9) 聚合函数

l MAX和MIN( 用于取得列的最大值或最小值，忽略null)

select ename,MAX(salary) from emp;

l AVG和SUM(只能操作数字类型，忽略null)

select AVG(salary) avg_sal, SUM(salary) sum_sal from emp;

l COUNT(用于计算表中的记录条数，忽略null)

select COUNT(job) total_job from emp;

l 聚合函数忽略NULL值，特殊情况会强制加入空值

oracle中NVL(exp1,exp2)不为null时，是exp1,为null是exp2

mysql中IFNULL(exp1,exp2),同上

select AVG(NVL(salary,0)) avg_sal from emp;

(10) 分组group by

例：得到每个部门的平均工资把,整个数据表按部门划分成一个个小组，每个小组中包含一行或多行数据，在每个小组中再使用分组函数进行计算，每组返回一个结果

select deptno,AVG(salary) from emp group by deptno;

(11) having字句(对分组后的结果进一步限制，必须跟在group by 后面，不能单独存在)

例：查询每个部门的最高薪水，只有最高薪水大于4000的记录才被输出显示

select deptno “部门编号”,MAX(salary) “最高薪资” from emp group by deptno having MAX(salary) > 4000;

(12) 查询语句的执行顺序

select * from 表名 where 条件语句 group by 分组条件 having 过滤条件 order by 列名 desc(降序) / asc(升序);

(13)  嵌套SQL查询

 

3. 数据修改

update 表名 set 列名1=?,列名2=? where 条件语句;

4. 数据删除

delete from 表名 where 条件

注：truncate与delete区别

(1) truncate是DDL语句;delete是DML语句,需要使用commit 提交

(2) truncate删除比较快，delete比较慢，因为要记录日志

5.  注意

DML语句对表都加上了行级锁，确认完成后加commit生效，撤销使用rollback