第四章 数据基本查询

数据表的查询原理：

                从源表中根据需要查询的列和条件逐行对数据进行判断并将满足条件的行重新放入一张新表中，这张新表就是我们看到的查询结果表格。

                查询的结果称为结果表，查询的结果集是数据库中的虚拟表，将数据文件的内容以表格的方式进行呈现的结果。

1、查询语法

    select    *    from    表名

*    是SQL中的通配符，用来匹配表中的所有数据列。

select * from users

   select    列名，列名    from    表名    where    条件

--查询users所有的人的姓名
select userName from users
--查询user表中所有人的姓名和他的邮箱
select userName,email from users
--查询没有填写邮箱的用户姓名，年龄和生日
select userName,age,birthday from users where email is null

2、指定列名查询

       select    列    as    显示列名    from    表名

     或者select    列    显示列名    from    表名

第二种相对于第一种少个as，但第一种比较直观，所以尽量使用前者。

--查询员工的姓名和对应的薪水
select first_name||last_name as empName,salary from emp
select first_name||last_name empName,salary from emp

3、为表起别名

           select    *    from    表名 新表名

注意：这里没有as，不同于为列起别名

--为表起别名
select * from users u

4、常量列的查询

源表中没有的列作为常量进行查询， 如下num列下 都是10

--常量列的查询
select first_name||last_name as empName,salary,sysdate as oprate_time,10 as num from emp

5、排序查询

           select    列名    from    表名    where    条件    order    by   列  

升序排列  asc （默认值）

降序排列  desc

--查询员工的姓名和薪资，并按照由低到高的方式进行呈现
--asc表示升序，默认排序就是升序的方式，desc表示降序
select first_name||last_name as empName,salary from emp order by salary asc
--由高到底显示工资
select rownum,first_name,salary from emp order by salary desc

       1）order by 通常对列进行排序，同时也可以对表达式的结果进行排序呢

select * from emp order by salary+500

       2）也可以对数字、日期、字母进行排序

select * from emp order by first_name

       3）可以对多个列进行排序，

多个列的排列原则，优先排序第一个列，当第一个列的值存在重复的情况，才会对第二个列进行排序，并且每个排序列需要单独设置排序策略

--要求入职时间升序排列，薪资降序排列
select * from emp order by hire_date asc,salary desc

6、rowid 和 rownum

rowid

select rowid from emp

结果如下：

 

rownum

    rownum在结果集生成后由Oracle自动分配，且必须从1开始，所有基于rownum的查询也必须从1开始。

--每页5条数据，查询第一页的数据
select first_name||last_name,salary from emp where rownum between 1 and 5

---

Oracle 函数

    函数是PL/SQL对SQL的补充

     Oracle函数只在Oracle中使用

日期转换格式

    YYYY                    完整年份

    MM                        月份

    DD                        天数

    HH24                    24制小时

    MI                          分钟

    SS                        秒

    YEAR                    年份拼写

    RR                        数值年份的最后两位

    MON                    月份字母的缩写

    DAY                      星期

数值转换格式

     9                任意数值

     0                以0显示

     L                本地系统货币符号

     .                  小数点

     ,                 千位符

     $                以$显示

0 和 9 的区别：0对不足位数的数字进行补位，9不会补位。

例子：

--将数值按照指定的格式转换
select to_char('1234.4567','99999.99') from dual
--格式化字符0将进行对不足位数的数字进行补位
select to_char('1234.4567','000000.000') from dual
select to_char(123456.789,'$9,999,999.99') from dual
--查询所有员工的姓名和薪资，薪资使用财务方式来呈现
select first_name||last_name as empName,to_char(salary,'$99,999,999.99') as salary from emp

常用的Oracle函数

        1、字符函数

        2、日期函数

        3、数值函数

        4、转换函数

7、转换函数

        1）to_char    用于将字符格式化，或者日期转换成字符串

                获取日期中指定单位

--获取日期中的指定单位
select to_char(sysdate,'mm') from dual

--查询部门编号为80-100的员工的姓名，入职的年份，以及薪水
select first_name||last_name as empName,to_char(hire_date,'yyyy') as hire_year,salary from emp where department_id between 80 and 100
--将这个月入职的员工薪资全部提高100块
update emp set salary=salary+100 where to_char(sysdate,'mm')=to_char(hire_date,'mm')

        2）to_date    将字符串转换成date类型

select to_date('20160710','yyyymmdd') from dual

        3）to_number   将字符转换成number类型

select to_number('2016') from dual

select * from emp order by to_number(to_char(hire_date,'yyyy')) 

8、字符串函数

        1）upper    转换成大写

--将字母转换成大写
select upper('niit') from dual

        2）lower    转换成小写

--将字母转换成小写
select lower('NIIT') from dual

--查询名字中首字母是t（不区分大小）的用户名字和入职时间
select first_name||last_name as empName,hire_date from emp where upper(first_name) like 'T%'
select first_name||last_name as empName,hire_date from emp where lower(first_name) like 't%'

        3）initcap    首字母大写

--首字母大写
select initcap(userName) from users

        4）concat    字符串连接

--拼接字符串
select concat(first_name,last_name) from emp
select first_name||last_name from emp

        5）length    获取字符数

--获取字符串的长度
select length('helloniit') from dual
--查询users表中姓名长度超过4位的用户信息
select * from users where length(userName)>4

        6）lpad        左填充

        7）rpad        右填充

--左右填充数据
--第一个参数表示源字符串，第二个参数表示填充的长度，第三个参数表示填充的字符
select lpad('niit',6,'*') from dual
select rpad('niit',10,'#') from dual

        8）ltrim        去除左空格

--去除左空格
select length(ltrim('   niit')) from dual

        9）rtrim        去除右空格

--去除右空格
select length(rtrim('niit    ')) from dual

--去除左右空格
select ltrim(rtrim('   niit   ')) from dual

        10）instr        获取查询字符串的索引

                第一个参数表示源字符串，第二个参数表示要搜索的字符，第三个参数可以不写，默认从1开始，表示起始的检索位置

                索引从1开始,如果检索不到数据则返回0

select instr('hello niit','l',5) from dual

--查询使用niit邮箱的用户
select * from users where instr(email,'@niit.com') != 0

        11）substr    截取字符串

                如果只传一个索引 表示从该索引截取到最后

                如果传2个数值参数，第二个数值参数表示截取的长度

--截取字符串
select substr('hello niit123',2,5) from dual;

--查询编号为1020用户的邮箱用户名和邮箱名
select substr(email,1,instr(email,'@')-1),substr(email,instr(email,'@')+1) from users where userId=1020
--要求查询用户的姓名和电话号码，要求电话号码进行加密呈现(只显示电话号码的前三位和后三位，其余部分用*屏蔽)
select substr(tel,1,3)||lpad(substr(tel,length(tel)-2),length(tel)-3,'*') from users

        12）replace    替换字符串

                       第一个参数表示源字符串，第二个参数表示被替换的字符，第三个参数表示替换的新字符

--替换字符串内容
select replace('niit','i','a') from dual

9、日期函数

            1）sysdate                        获取系统时间

 

            2）extract                          获取时间单位

  

--获取年月日单位
select extract(year from sysdate) from dual;
select extract(month from sysdate) from dual;
select extract(day from sysdate) from dual;
select userName,extract(year from birthday) from users

            3）months_between         计算月差

--获取月份的差值,返回浮点数
select months_between(sysdate,to_date('20150519','yyyymmdd')) from dual
--date可以直接使用算数运算符计算，获得天数的差
select sysdate-to_date('20160818','yyyymmdd') from dual

            4）add_months                 添加月份

--添加月份 返回增加月份后的date类型
select add_months(sysdate,5) from dual
--sysdate+1是添加天数
select sysdate+1 from dual

例子：

--查询15年前入职的员工姓名和入职的时间
select first_name||last_name as empName,hire_date from emp where months_between(sysdate,hire_date)/12>=15
--查询15年前入职的员工姓名和入职的时间
select first_name||last_name as empName,hire_date from emp where add_months(hire_date,15*12)<=sysdate

            5）next_day                       下一个星期数

--查询最近的指定星期的日期
select next_day(sysdate,'星期一') from dual

额外例子：

--查询今天生日人的姓名和出生年月
select userName,birthday from users where to_char(sysdate,'mm')=to_char(birthday,'mm') and to_char(sysdate,'dd')=to_char(birthday,'dd')
select userName,birthday from users where to_char(sysdate,'mmdd')=to_char(birthday,'mmdd')
select to_char(sysdate,'mmdd') from dual
--查询最近的周一生日的人的姓名和出生年月
select userName,birthday from users where to_char(next_day(sysdate,'星期一'),'mmdd')=to_char(birthday,'mmdd')
--查询7天内生日的人
select userName,birthday from users where to_date(to_char(birthday,'mmdd'),'mmdd') between to_date(to_char(sysdate,'mmdd'),'mmdd') and to_date(to_char(sysdate+7,'mmdd'),'mmdd')
select to_char(to_date(to_char(sysdate,'mmdd'),'mmdd'),'yyyy-mm-dd hh24:mi:ss') from dual

            6）last_day                        当月的最后一天

--获取这个月的最后一天
select last_day(sysdate) from dual

            7）round                            对日期四舍五入

                    如果参数是year则用来判断是否是上下半年，如果是上半年则返回当前年份的1月1日，如果是下半年则返回下一年的1月1日

                   如果参数是month则用来判断是否是上下半月，如果是上个月则返回当前年月的第一天

                   如果参数是day则用来判断是否是上下礼拜，如果是前半星期，则返回当前星期的第一天（日）

select round(sysdate,'day') from dual

            8）trunc                             获取特定时间的第一天

                       year获取这一年的第一天

                       month获取这个月的第一天

                       day获取这个星期的第一天     

select trunc(sysdate,'year') from dual

例：

--查询下个月第一天生日的人
select userName,birthday from users where to_char(add_months(trunc(sysdate,'month'),1),'mmdd')=to_char(birthday,'mmdd')

10、数值函数

            1）abs                               绝对值

            2）ceil                               向上取整

            3）floor                              向下取整

            4）power                           幂次方

                    power(2,3) ------->8

            5）round                            四舍五入

                   有两个参数，第二个参数表示保留的小数位数

            6）sqrt                               平方根

            7）trunc                             整值截断

                    截断函数，根据指定的小数位直接截取数字

            8）mod                               整数取余