[SQL入门级] 第一天 SQL初步

SQL语句分为以下三种类型：

• DML：Data Manipulation Language 数据操纵语言
  1. INSERT
  2. UPDATE
  3. DELETE
  4. SELECT
• DDL：Data Definition Language 数据定义语言
  1. CREATE TABLE
  2. ALTER TABLE
  3. DROP TABLE
  4. CREATE INDEX
  5. DROP INDEX
• DCL：Data Control Language 数据控制语言
  1. GRANT 授予访问权限
  2. REMOVE 撤销访问权限
  3. COMMIT 提交事务处理
  4. ROLLBACK 事务处理回退
  5. SAVEPOINT 设置保存点
  6. LOCK 对数据库的特定部分进行锁定

一、基本的SQL-SELECT语句

别名 

①直接在字段名后面写  select last_name name,employee_id id from empolyees;

②在字段名后加as  select last_name as name,employee_id as id from empolyees;

③加双引号（当别名由多个单词构成且中间以空格隔开时必用）  select last_name "my name",employee_id as id from empolyees;      

 

连接符

①把列与列，列与字符连接在一起

②用 || 表示

③可以用来'合成'列

select last_name||'`s job_id is '||job_id as details from employees

 

重复行（去重）distinct

select distinct department_id from employees;

注意：null值也单独为一行

注意：字符和日期必须用单引号括起来（SQL中只有在上面提到的别名时会用到双引号，其它情况都用单引号），SQL语言大小写不敏感，但''括起来的字符串严格区分大小写

 

二、过滤和排序

过滤 where

-- AND OR NOT 逻辑且/逻辑或/逻辑否

-- [NOT] BETWEEN AND 表示区间内的值，包含边界 等价于 >= and <=

-- [NOT] IN 表示离散的值，where department_id in(70,80,90) 等价于 where department_id=70 or department_id=80 or department_id=90

-- [NOT] LIKE 模糊查询 

   百分号%表示0…n个字符；

   下划线_表示单个字符；

   转义字符 ESCAPE，where last_name like  '%\_%' escape '\' (转义字符可以是任何字符 如 \ # $ 等)

-- IS [NOT] NULL 空值

-- 运算优先级

    算术运算符>连接符>比较符>IS NULL,LIKE,IN>BETWEEN>NOT>AND>OR

    括号可以改变优先级

 

排序

ORDER BY … DESC（逆序）/ASC（顺序，默认）

select last_name,department_id,salary*12 annual_sal
from employees

--order by 可以不放在where子句

--where department_id > 80

--多级排序

--依字段别名排序
order by annul_sal,last_name desc

 

三、单行函数

字符

① 大小写控制函数：因为被单引号括出来的内容是严格区分大小写，而有时候查询不在乎大小写，这时候此类函数派上用场

   LOWER('SQL Course') → sql course    全部小写

   UPPER('SQL Course') → SQL COURSE 全部大写

   INITCAP('SQL Course') → Sql Course  单词首字母大写

② 字符控制函数：

   CONCAT('Hello','World') → HelloWorld 连接字符串

   SUBSTR('HelloWorld',1,5) → Hello       从第1个下角标位置开始截取长度为5的子字符串--Java中下标从0开始，-1代表无效数据；SQL中下标从1开始，0代表无效数据

   LENGTH('HelloWorld') → 10                字符串长度

   INSTER('HelloWorld','W') → 6             字符在字符串中的位置

   LPAD(salary,10,'*') → *****24000     字符串左对齐，用10个位去存，不足在左端用*补齐

   RPAD(salary,10,'*') → 24000*****     字符串右对齐，用10个位去存，不足在右端用*补齐

   TRIM('H' FROM 'HelloHWorldH') → elloHWorld 去除原字符串中首尾与指定字符相同的字符

   REPLACE('abcdb','b','m') → amcdm      将字符串中所有与指定字符相同的字符替换为另一个字符

 

数值

   ROUND() 四舍五入

      --ROUND(435.45,1) ROUND(435.45) ROUND(435.45,-1)
                         435.5                  435                       440

   TRUNC() 截断

      --ROUND(435.45,1) ROUND(435.45) ROUND(435.45,-1)
                         435.4                  435                       430

   MOD() 求余

      --MOD(1600,15)

                         10

 

日期：Oracle中的日期型数据实际含有两个值(日期和时间)

   在日期上加上或减去一个数字结果仍为日期

   两个日期相减返回日期之间相差的天数

   MONTHS_BETWEEN 两个日期相差的月数

   ADD_MONTHS         向指定日期中加上若干月数

   NEXT_DAY              指定日期的下一个星期几对应的日期

   LAST_DAY              本月的最后一天

   ROUND                   日期四舍五入

   TRUNC                    日期截断

 

数据类型转换

① 隐式 DATE ←→ VARCHAR2 ←→ NUMBER

源数据类型	目标数据类型
VARCHAR2/CHAR	NUMBER
VARCHAR2/CHAR	DATE
NUMBER	VARCHAR2
DATE	VARCHAR2

--注意区别，JAVA中+号在字符串运算中代表字符串连接，SQL中字符串连接用的是 ||,+号则表示数学加法运算

select '12' + 2 from dual; --此处'12'隐性转换为NUMBER类型的12

14

 

② 显式

TO_CHAR

   select employee_id,to_char(hire_date,'yyyy"年"mm"月"dd"日"') --此处在输出日期格式中穿插的字符要用""括起来
   from employees
   where to_char(hire_date,'yyyy/mm/dd') = '1994/06/07'  -- to_date('1994/06/07','yyyy/mm/dd')

TO_DATE

TO_NUMBER

   select to_char(1234567.89,'999,999,999.99') from dual; -- 1,234,567.89      不足不用补零

   select to_char(1234567.89,'000,000,999.99') from dual; -- 001,234,567.89   不足位数补零

   select to_char(1234567.89,'$999,999,999.99') from dual; -- $1,234,567.89   美元符号

   select to_char(1234567.89,'L999,999,999.99') from dual; -- ￥1,234,567.89  本地货币符号

   select to_number('￥1,234,567.89','L999,999,999.99') from dual; -- 1234567.89 格式要对应上

 

通用

这些函数适用于任何数据类型，同时也适用于空值

NVL(expr1,expr2) 等价于 expr1!=null?expr1:expr2

--commission_pct 奖金率字段的值有可能为null，是null则用 0 代替

select employee_id,last_name,salary*12*(1+nvl(commission_pct,0)) annual_sal,commission_pct from employees;

--由于department_id是NUMBER类型，而'没有部门'是严格的字符类型，直接调用NVL函数报“无效数字”错误，需进行处理

select last_name,nvl(to_char(department_id),'没有部门') from employees;

NVL2(expr1,expr2,expr3) 等价于 expr1!=null?expr2:expr3

select last_name,nvl2(commission_pct,commission_pct + 0.015,0.01) from employees;

NULLIF(expr1,expr2) 等价于 expr1==expr2?null:expr1

select first_name,length(first_name) "expr1",
       last_name,length(last_name) "expr2",
       nullif(length(first_name),length(last_name)) result
from employees

COALESCE(expr1,expr2,…,exprn) 如果第一个表达式为null，则返回下一个表达式，依次迭代

 

条件表达式（IF-THEN-ELSE逻辑）

① CASE表达式

CASE expr1 WHEN comparison_expr1 THEN return_expr1
  [WHEN comparison_expr2 THEN return_expr2
   WHEN comparison_exprn THEN return_exprn
   ELSE else_expr]

END

--查询部门号为10,20,30的员工信息，若部门号为10，则打印
--其工作的1.1倍，20号部门，1.2倍，30号部门，1.3倍
select employee_id,last_name,department_id,
       case department_id when 10 then salary*1.1
                                    when 20 then salary*1.2
                                    else salary*1.3
                                    end REVISED_SALARY
from employees
where department_id in(10,20,30)

 

② DECODE函数

select employee_id,last_name,department_id,
       decode(department_id,10,salary*1.1,
                                        20,salary*1.2,
                                        salary*1.3) REVISED_SALARY
from employees
where department_id in(10,20,30)