DML语句(insert,update ,delete,select)--插入语句select * from student;--单独的插入一条语句insert into student(sno,sname,sex,address,cardid)values(2,'张三','男','长沙','12313')update student set sex='女'rollback;--插入的时候要注意的地方(1)要插入的列名的个数必须和值的个数匹配(2)当有约束的情况下面要考虑约束(check,default,foreign key,unique)(3) 数据类型(考虑完整性约束(实体完整性,域完整性,引用完整性,自定义完整性))(4)在SQL server里面,当有标识列存在的情况下,我们不能显示的给这个标识列插入值--考虑默认约束的情况下面insert into student(sno,sname,sex,cardid)values(2,'张三','男','12322')insert into studentvalues(3,'李四','女',default,'23')--一次性插入多行数据--在SQL SERVER 里面/*--自己输入的值insert into studentselect 110,'张三','男',getdate(),95031 unionselect 111,'张2','男',getdate(),95031 unionselect 112,'张3','男',getdate(),95031 unionselect 113,'张4','男',getdate(),95031--创建表的同时插入另外一张表里面的数据(表不存在的情况下)select * into newStudent from student;--同时只能执行一次select * from newStudent;truncate table newStudent;drop table newStudent;--表已经存在的情况下insert into newStudent select * from student;--只复制表结构不要复制数据select * into newStudent from student where 1=0;--我们在创建表的同时添加一个字段,在这个字段上面添加标识列select identity(int,1,1) as sno,sname,ssex into newStudent from student;select * from newStudent;*/select * from student;--在oracle里面一次性插入多行记录insert into studentselect 110,'张三','男','长沙','95031' from dual unionselect 111,'张2','男','长沙','95032' from dual unionselect 112,'张3','男','长沙','95033' from dual unionselect 113,'张4','男','长沙','95034' from dualselect * into newStudent from student; --在oracle里面错误create table newStudent(sno int primary key,sname varchar2(20))select * from newStudent;--往已经存在的表中插入另外一张表中的数据insert into newStudent select sno,sname from student;commit;select * from student;--更新语句update 表名 set 字段名=值 where 条件update student set sname = '王五' where sno =111;rollback;--删除语句delete from student where sno =111;delete cardid from student;--不能删除指定列的值--删除表中的所有记录truncate table student;--相当于delete from student;truncate table tt1;select * from student;insert into studentvalues(1,'张三','男',default,'111')create table tt(sno int primary key,sname varchar2(20))insert into tt select sno,sname from student;select * from tt;truncate table tt;--查询selectselect 12+12 from dual;--select 执行顺序我们不能随意更改selectfromwheregroup byhavingorder by
oracle 的 dml,oracle DML语句
最新推荐文章于 2024-06-21 11:28:40 发布