DML语句的使用
DML语句是数据库的操作语言,主要用来操作数据库的表,视图,一般情况下是指:select,update,insert,delete。
insert语句
------------------------------------------------- --插入值语法 insert into table_name (column1, column2, .. .) values (values1, values2, .. .) ------------------------------------------------- --无条件INSERT ALL insert all into sal_history values(EMPID,HIREDATE,SAL) into mrg_history values(EMPID,MGR,SAL) select employee_id EMPID,hire_date HIREDATE,salary SAL,manager_id MGR from employee where employee_id>200;
------------------------------------------------- --有条件INSERT ALL insert all --不论是否求值都为true when SAL>10000then into sal_history values(EMPID,HIREDATE,SAL) when MGE>200then into mrg_history values(EMPID,MGR,SAL) select employee_id EMPID,hire_date HIREDATE,salary SAL,manager_id MGR from employee where employee_id>200; ------------------------------------------------- --有条件的FIRST INSERT insert first --如果这个第一个when子句的求值为true,后面的when字句不会执行 --直到第一个满足条件为止 when SAL>2500then into special_sal values(DEPTID,SAL) when HIREDATE like ('%00%') then into hiredate_history_00 values(DEPTID,HIREDATE) when hiredate like ('%99%') then into hiredate_history_99 values(DEPTID,HIREDATE) else into hiredate_history values(DEPTID,HIREDATE) select department_id DEPTID,SUM(salary) SAL, MAX(hire_date) HIREDATE from employees GROUPBY department_id |
update语句
--更新表名 update table_name --更新的值 set column1 = values --限制条件 where state = '' |
delete语句
--选择删除数据 delete from customer --删除的限制条件 where customer_id='adc232' |
merge语句
merge语句作用: 1.在一个SQL语句中对表同时执行插入和更新操作 2.可以从一个或多个数据源中选择行来更新或插入一个或多个表
--1.省略条件update子句 merge into product p using newproducts np on(p.product_id = np.product_id) --当上面条件匹配 when matched then update set p.product_name = np.product_name, p.category = np.category;
--2.省略条件insert子句 merge into product p using newproducts np on(p.product_id = np.product_id) --当上面条件匹配 when not matched then insert values (np.product_id,np.product_name,np_category);
--3.带条件的update语句 merge into product p using newproduct np on (p.product_id = np.product_id) when matched then update set p.product_name = np.product_name where p.category = np.category;
--4.带条件的insert和update语句 merge into product p using newproduct np on (p.product_id = np.product_id) --匹配到 when matched then update set p.product_name = np.product_name, p.category = np.category where p.category = 'DVD' --匹配不到 when not matched then insert values (np.product_id,np.product_name,np.category) where np.category != 'BOOKS';
--5.无条件insert merge into product p using newproduct np on (1 = 0) when not matched then insert values(np.product_id,np.product_name,np.category) where np.category = 'BOOKS';
--6.新加的delete功能 merge into product p using newproduct np on (p.product_id = np.product_id) when matched then update set p.product_name = np.product_name,p.category = np.category delete where (p.category = 'ELECARNCS') where not matched then insert values (np.product_id , np.product_name,np.category) |
truncate语句
truncate table 语句 作用:快速,无日志记录删除表中的所有行 和delete比较优点 a.所有的事物日志空间较少 b.表中不会留下任何页 |