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

转载

mob604756ee63c0 2015-07-22 16:17:00

文章标签 sql 字符串数据大小写单引号 文章分类 JavaScript 前端开发

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)