快速入门SQL语句与函数使用

--通过查询创建一个新表
create table demo27 as select * from title;
--复制表结构
--drop table demo27
create table demo27 as select * 
from title
where 1=2;
--还有一种是通过select into的形式，复制表的数据，
--放在plsql中进行说明
select * from demo27

select * from teacher t  where t.t_entertime>
to_date('2006-01-01','yyyy-mm-dd')

--dual是一个虚表，这个表本身没有任何意义，
--就是为了保证函数查询语法一致
select sysdate from dual;
select * from dual;

--uuid
select sys_guid() from dual;

create table demotemp(
       did varchar2(36) primary key,
       age number(2,0),--number有两个参数，第一个是位数，第二个是小数位，没有小数位的写零
       enterday date--时间戳，时间
);

insert into demotemp values (sys_guid(),10,sysdate)
commit;--增删改需要进行数据的提交

--聚合函数
--1.count 求总行数
select count(s_id) from student--这样写执行效率高
--2.sum 求和
select sum(s.s_chinese) from student s;
--3.avg 求平均
select avg(s.s_chinese) from student s;
--4.min 求最小
select min(s.s_chinese) from student s;
--5.max 求最大
select max(s.s_chinese) from student s;
--每个聚合函数都会返回一个一行一列的值
select count(*) from student;
select * from student;
--分组 查询 group by
--1.在使用group by时，不能用* (星号)
--2.在使用group by时，能显示的列，是被分组的列
--3.使用group by时，多用聚合函数搭配
--select * from student s group by s.s_classname;
--select s_classname from student s group by s.s_classname;
select s_classname,avg(s.s_chinese)  
from student s  where s.s_gender='男'
group by s.s_classname
having count(*) >10
order by avg(s.s_chinese) desc;
--order by默认是升序排序就是从小到大，asc
--desc是降序排序，从大到小
--having是对分组后的数据进一步筛选
--执行顺序
/*
        1.from 表
        2.where 进行过滤
        3.group by对过滤的数据进行分组或合并同类项
        4.having 对已经分组的数据进行第二次筛选
        5.order by排序默认是asc升序，desc是降序排序
        

*/
--伪劣和分页
--mysql中通过limit
--oracle中有两种方法来做
--1.rownum
--2.row_number() over()

--1.rownum
select * from student where rownum=1;
--获取前10行的数据
select * from student where 
rownum >10
--rownum的起始值是1
select * from 
(select rownum rn,s.* from student s)
where rn >=11 and rn <=20

--row_number()
select * from
(select 
row_number() over (order by s_id desc) as rn,
s.*
from student s) a
where a.rn between 21 and 50
--在student表中，
--查找每个班级中政治面目是共青团员的学生
--根据班级分组
--至少有25人的班级升序排序


select s.s_classname,count(*) from student s
where s.s_political='共青团员'
group by s.s_classname
having count(*) >28
order by 2  desc
--这里的1表示查询结果中的第一列

create table usermail(
       userid int primary key,
       email varchar2(200) not null
);
insert into usermail values (1,'ll@a.com');
insert into usermail values (2,'lll@a.com');
insert into usermail values (3,'bill@a.com');
insert into usermail values (4,'lll@a.com');
insert into usermail values (5,'bill@a.com');
delete from usermail where userid not in
(select min(userid) from usermail
group by email)

select * from usermail

--内置函数
--字符函数
--1.sys_guid  uuid和guid是一样的
--在oracle中sys_guid产生的值是32位的，
--在mysql和Java中是36位的
select sys_guid() from dual;

--2.initcap  单词首字母大写
select initcap('hello') from dual;

--3.upper 将小写的字母变成大写
select upper('hello') from dual;

--4.lower 将大写的字母变成小写
select lower('HELLO') from dual;
--开发中，为了避免出现大小写不一致的问题，统一将字符变成大写或小写

--5.ltrim 取消左边的空格
select ltrim(' abc def ') from dual;
--5.1 删除左边的特定字符
select ltrim('abcdef','abc') from dual;


--6.rtrim 删除右边的空格
select rtrim(' abc def ') from dual;
--6.1rtrim 删除右边的特定字符
select rtrim('abcdef','def') from dual;

--7.删除字符串两边的空格
select ltrim(rtrim(' abc def ')) from dual;
--7.1 trim
select trim(' abc def ') from dual;
--7.2 删除指定的字符
select trim('x' from 'axxxxsadfxxxxsadfxx3245xxx') from dual;

--8.translate 方法中有三个参数，第一个参数是原始字符，第二个参数是需要被替换的字符，
--第三个参数是替换的内容
select translate('jackja','j','bg') from dual;

--9.replace 字符串替换函数
select replace('jackma','j','bl') from dual;

--10.instr 查找第二个参数在第一个参数中首次出现的位置,没有找到返回零，字符串的索引是从1开始的
select instr('helloworld','k') from dual;

--11.substr 从第一个开始截图两个长度，字符串索引默认从1开始
select substr('abcdefg',1,2) from dual;

--12.concat 连接两个字符串或者可以通过双竖线操作
select concat('hello','world') from dual;

--13.chr 将ascii�码转成字符
select chr(65) from dual;

--14.ascii 将字符变成ascii码
select ascii('A') from dual;

--15 lpad 第一个参数是字符串，第二参数是总长，如果第一个参数的位数不足第二个参数，
--则用空格进行填充，如果超过了第二参数的长度，仅显示第二个参数这么长
select lpad('abc12345667788',7,0) from dual;

--16.rpad 同上将左换成👉
select rpad('abc',7) from dual;

--17.length 使用length来计算字符串中的字符个数，不是长度！！！！！！！
select length('abc') from dual;
select length('你好') from dual;
--18.lengthb 使用lengthb来计算字符串中的字符长度，不是个数！！！！
--有部分的oracle计算结果是一个汉字的长度是3不是2,是版本不同，结果都对！
select lengthb('abc') from dual;
select lengthb('你好') from dual;

--数学类型函数
--1.绝对值
select abs(-1) from dual

--2.返回大于当前数值的最小值
select ceil(44.5) from dual;

--3.返回小于当前数值的最大值
select floor(44.5) from dual;

--4.power(x,y) x的y次方
select power(4,2) from dual;

--5.mod 求余数
select mod(9,3) from dual;

--6.round 四舍五入，第二个参数是控制小数点位的
select round(100.256,2) from dual;

--7.截取第二个参数指定的位数，不进位的
select trunc(100.256,2) from dual;

--8.sqrt开方
select sqrt(9) from dual;

--9.sign 获取数字n的的符号，大于0返回1，小于0返回-1，等于0返回0
select sign(100),sign(-100),sign(0) from dual;

--10.to_char
select to_char(78) from dual;
select to_char(sysdate,'yyyy-mm-dd HH:Mi:ss') from dual;
--to_char
--大写的L表示当前系统的货币符号
--9表示任意一个数字
--.表示小数点
--,表示千分位分隔符
select to_char(1178,'L9,999.99') from dual;
--11.to_number
select to_number('78') from dual;

--日期类型
--1.sysdate 获取当前的系统日期加时间指的是数据库安装的那台计算机的时间
select sysdate from dual;

--2.获取当前系统时间 获取的是当前的回话系统时间 有时候current_date 比sysdate 快1秒钟
select current_date from dual;

--3.extract截取特定的时间部分
select extract(year  from date'2019-02-27') from dual;
select extract(month  from date'2019-02-27') from dual;
select extract(day  from date'2019-02-27') from dual;

--4.next_day 指定时间的下一个星期几
--星期几可以写中文星期一。。。星期日，或者写1~7的数字，其中星期日的对应数字是1，2代表星期一
select next_day(sysdate,1) from dual;
select next_day(sysdate,'星期日') from dual;

--5.last_day(x) 返回的是x月的最后一天 ,加一表示下个月的第一天
select last_day(sysdate) from dual;

select last_day(sysdate)+1 from dual;

--6.add_months(x,y) 在x月基础上增加y的月，如果y是负数表示向前移动数据
select add_months(sysdate,3) from dual;

--7.months_between(x,y),比较x和y之间差了几个月如果x>y的返回正数，小于返回负数
select months_between(sysdate,sysdate) from dual;
select trunc(months_between(sysdate,to_date('2018-02-24','yyyy-mm-dd'))) from dual;

--8.trunc 计算两个日期之间的天数
select '距离放假还有' || abs(trunc(sysdate-to_date('2019-06-30','yyyy-mm-dd'))) || '天' 倒计时 from dual;

--9.trunc
select trunc(sysdate,'mm') from dual;
select trunc(sysdate,'yyyy') from dual;

--其他类型
--1.nvl(x,y) 类似于if判断,如果x是null,就返回y
select nvl(s.s_duty,'没有职位') from student s;

--2.nvl2(x,y,z) 类似于if else结构 如果x不是null，返回y,反则返回z
select nvl2(s.s_duty,s.s_duty,'没有职位') from student s;


--3.nullif(x,y) 比较两个参数xy，如果x和y相同返回null,不同返回x
select nullif('abc','abc') from dual;

--4.decode 如果第一个参数和第二个参数相同返回第三个参数，不同返回第四个参数
select decode(s.s_gender,'男','猛汉','软妹') from student s;