--创建数据库 CREATE DATABASE 数据库名;CREATEDATABASE RUNOOB;--删除数据库 drop database <数据库名>dropdatabase RUNOOB;--返回指定的第几条 row_number()over(order by id)select*from(select id,row_number()over(orderby id) num from b_teacher )where num=2;--返回前多少条数据select*from b_teacher where ROWNUM<=2;--模糊查询select*from b_teacher where name like'%王%';--通配符 % 代表的一个或者是多个 _代表是一个字符 [charlist]字符列中的任何单一字符 [^charlist]不在字符列的任何一个字符select*from b_teacher where name like'王_';select*from b_teacher wherenot regexp_like(name,'[王张]');--不包含select*from b_teacher where regexp_like(name,'[王张]');--包含--IN 包含select*from b_teacher where name in('陈军杰','韩爱鸽');select*from b_teacher where name in(select name from bt_teacher where name like'%王%');--BETWEEN 之间的数值select id,LEAVE_DAY from b_leave where LEAVE_DAY between1and10;--AS 别名select id , name as tname,CONCAT(kind,',',SEX)astextfrom b_teacher;select kind||(nvl2(kind,',',''))||sex astextfrom b_teacher;--组合别名kind和sex设置为同一个,这个时候对于字段的组合可以起到作用--行的处理select*from S_USER where name='王传平';select*from b_leave where name='测试销假附件和请假天数回显'
sql的常用功能LEFT JOINselectfrom on join where group by having select distinct union order by LEFT JOIN从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。