2022年小布老师SQL学习笔记.docx

精选学习资料 - - - - - - - - - Liao SQL基础介绍 Oracle 11g SQL Fundamentals Training Introduction 1、primary key 主键 表中的唯独的标示 非空 2、foreign key 外键 在本表中引用的另外一张表的主键 3、ER 实体关系模型4、SQL 的分类4.1、Data manipulation languageDML 数据操作语言Select 查询数据 Insert 插入数据 Update 更新数据 Delete 删除数据 Merge 合并数据 4.2、Data definition languageDDL 数据定义语言 Create 创建表 Alter 修改表 Drop 删除表 Rename 重命名表 Truncate 截取 Comment 4.3、Data control languageDCL 数据掌握语言 Grant Revoke 取消 4.4、Transaction control事物掌握 Commit 提交 Rollback 回滚 Savepoint SQL> show user -查看当前用户 USER is "SYS" desc dba_users -查看全部用户 SQL> set lines 200 SQL> select username,account_status from dba_users; USERNAME ACCOUNT_STATUS - - 名师归纳总结 PERFSTAT OPEN 第 1 页,共 36 页HR OPEN OE OPEN SH OPEN - - - - - - -精选学习资料 - - - - - - - - - OPS$ORACLE USER01 OPEN OPEN DIP EXPIRED & LOCKED IX EXPIRED & LOCKED MDDATA EXPIRED & LOCKED PM EXPIRED & LOCKED BI EXPIRED & LOCKED SQL> select table_name from user_tables; -查看存在的表-字符串和日期型是左对齐,数字型是右对齐,表头是大写；SQL> select * from departments; DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID MANAGER_ID - - - - 10 Administration 200 1700 20 Marketing 201 1800 30 Purchasing 114 1700 01-Restricting Data Using the SQL SELECT Statement 1、算数表达式+ - * / 可以用于除 FROM 子句的其他子句SQL> select last_name,salary, salary + 300 from employees; LAST_NAME SALARY SALARY+300 - - - Walsh 45331.68 45631.68 Feeney 45190.56 45490.56 107 rows selected. -算数表达式中有 NULL值结果为 NULL -和字符串做运算时,结果为字符串；2、别名用双引号括起来 不能用于 WHERE 子句 select last_nameas "Name",commission_pct as "Comm" from employees; Name - - Ozer Bloom Fox Comm .25 .2 .2 Order by 中可以使用别名和指定序号名师归纳总结 - - - - - - -第 2 页,共 36 页精选学习资料 - - - - - - - - - 3、链接 | 将两个字段连起来显示-两个字段中插入空格 select last_name | ' | job_id as "Employees" from employees; Employees - OConnell SH_CLERK Grant SH_CLERK Whalen AD_ASST select last_name | ' is a ' |job_id as "Employees" from employees; Employees - OConnell is a SH_CLERK Grant is a SH_CLERK; Whalen is a AD_ASST q'可以输出“ ”单引号 试验证明应当是输出一段自定义字符 4、distinct 查不同的值（范畴是整个 SELECT 后的全部字段）select distinctdepartment_id from employees; 02-Restricting and Sorting Data 补充 where 子句：1）子句内的需要比较的字符需要用单引号引起来 大小写 的；2）子句假如比较的是日期字符（默认的格式是 3）运算符（比如 where xxx='123' ）,而且字 符是区分 '日-月-年'）,需要留意日期格式；名师归纳总结 - - - - - - -第 3 页,共 36 页精选学习资料 - - - - - - - - - 例子：Between and: select emp_name,sal where sal between 2500 adn 3000; Select emp_name,sal where emp_name where emp_name between 'tony' adn 'suen' 字符也可以比较,A-Z,这样比较Like 通配符 % 代表 0 或者多个字符 _ 代表 1 个字符 Select emp_name from emp where empname like ' 张 %' 4）IS NULL / IS NOT NULL 字段是否为空 WHERE 子句里判定5）规律运算符AND OR NOT 1、Escape 字符转义SQL> select last_name,job_id from employees 2 where job_id like '%SA_%' escape '' 是'|' 或者其他的escape 来定义转义字符,也可以-escape ''是将斜线设置为转义字符,把下划线转义成了一个一般的字符名师归纳总结 - - - - - - -第 4 页,共 36 页精选学习资料 - - - - - - - - - LAST_NAME - - JOB_ID Russell Partners Errazuriz SA_MAN SA_MAN SA_MAN 2、SQL 执行优先级名师归纳总结 - - - - - - -第 5 页,共 36 页精选学习资料 - - - - - - - - - 补充：排序 ORDER BY 默认是升序, DESC是降序；ORDER BY子句里可以使用别名, WHERE ,group by , having子句不能使用Order by N 后面可以自己加数字,代表 SELECT第 N 个字段,但不值得举荐；Order by 后面可以跟多个字段, 表示先依据第一个字段先排序,然后再第一个字段中相同的值中,其次例再排序；假如有 NULL值,升序是放到最终,降序是放到开头；但也可以自己转变：表的别名：另外：1、 form 子句的别名和国际标准不一样：不能加 AS,其他子句可以；2、 表的别名设置后,其他地方使用表就只能用别名；列的别名就不用这样Where 子句不能包含多组函数,而且3、替换变量&和&& 简洁的字符串的替换； 几乎可以适用全部子句是客户端软件的功能SQL> select last_name,salary from employees 号2 where employee_id=&employee_num; 如是字符串和日期肯定要加单引Enter value for employee_num: 123 old 2: where employee_id=&employee_num new 2: where employee_id=123 LAST_NAME SALARY - - 名师归纳总结 Vollman 50161.44 第 6 页,共 36 页双&&替换变量后, 同一个变量, 只用输入一次；- - - - - - -精选学习资料 - - - - - - - - - 4、Define 定义变量（适合用在批处理里面）SQL> define employee=200 SQL> select employee_id,last_name from employees 2 where employee_id=&employee; old 2: where employee_id=&employee new 2: where employee_id=200 EMPLOYEE_ID LAST_NAME - - 200 Whalen Set verify off 关闭提示 Set verify on 打开提示03-Using Single-Row Functions to Customize Output 单行函数1 、函数名师归纳总结 - - - - - - -第 7 页,共 36 页精选学习资料 - - - - - - - - - 2、单行函数 ：字符串函数,数值函数,日期函数,转换函数 conversion ,通用函数 general 2.1、字符串函数Lower 字符变大写 Upper 字符变小写 Initcap 首字大写2.2、字符串操作函数SQL> select concat'12345','67890' from dual;-将两个字符串连接起来CONCAT'12 - 1234567890 from dual; -截取 1 到 5 喂SQL> select substr'123456789',1,5SUBST - 12345 SQL> select substr'123456789',5 from dual;-截取 5 位以后的SUBST - 56789 SQL> select length '1234567890' from dual; -运算字符串的长度 LENGTH'1234567890' - 10 SQL> select instr'1234567890',0 from dual; -查看 0 所在的位置 INSTR'1234567890',0 - 名师归纳总结 - - - - - - -第 8 页,共 36 页精选学习资料 - - - - - - - - - 10 SQL> select lpad'12345',10,'*' from dual; -在左侧添加指定的字符 LPAD'1234 - *12345 SQL> select rpad'12345',10,'*' from dual; -在右侧添加指定的字符 RPAD'1234 - 12345* SQL> select replace'1234567890','1','0' from dual; -替换字符 REPLACE'1 - 0234567890 SQL> select trim'1' from '1234567890' from dual; -去除字符 TRIM'1'F - 234567890 2.3、数值函数2.3.1 、Round 数值 ,保留小数点的位数 0 为小数点位正值向右移,负值向左移,进行四舍五入 ；名师归纳总结 - - - - - - -第 9 页,共 36 页精选学习资料 - - - - - - - - - 2.3.2 、trunc 数值 ,保留小数点的位数 0 为小数点位 直接去掉后面的值正值向右移, 负值向左移,2.3.3 、Mod 数值,除数 取余数 mod1600,500 1600/500 余 100 常常用于判定数值的奇偶 mod数值, 2 2.4、日期函数 date Sysdate显示系统时间 SQL> select sysdate from dual; SYSDATE - 2022-02-22:13:50:30 Last_day 日期 显示当月的最终一天 SQL> Select last_day '20220221' from dual; LAST_DAY'20220221' - 2022-02-29:00:00:00 Add_months日期 ,增加的月数 SQL> Select add_months '20220222','1' from dual; ADD_MONTHS'2022022 - 2022-03-22:00:00:00 名师归纳总结 - - - - - - -第 10 页,共 36 页精选学习资料 - - - - - - - - - 04-Using Conversion Functions and conditional Expressions 转换函数1、To_char 日期转字符SQL> select employee_id, to_charhire_date,'YYYY-MM-DD' hired 2 from employees 3 where last_name='Higgins' EMPLOYEE_ID HIRED - - 205 1994-06-07 2、加 fm 去掉前导字符SQL> select employee_id,to_charhire_date,' fmYYYY-MM-DD' hired 2 from employees 3 where last_name='Higgins' EMPLOYEE_ID HIRED - - 205 1994-6-7 显示时间SQL> select employee_id,to_charhire_date,'YYYY-MM-DD HH24:MI:SS' hired 2 from employees 3 where last_name='Higgins' EMPLOYEE_ID HIRED - - 205 1994-06-07 00:00:00 3、To_char 数值转字符SQL> select to_charsalary,'$99,999.00' SALARY 2 from employees 名师归纳总结 - - - - - - -第 11 页,共 36 页精选学习资料 - - - - - - - - - 3 where last_name='Ernst' SALARY - $49,530.72 4、To_number 字符转数值SQL> select to_number'-$12,345.67','$99,999.99' from dual; TO_NUMBER'-$12,345.67','$99,999.99' - -12345.67 5、To_date 字符转日期SQL> select to_date'July 4, TO_DATE'JULY4,2007 - 2007-07-04:00:00:00 6、加 fx 为精准配备SQL> select to_date'July 4, 2007','Month DD, YYYY' FROM dual; 2007','fxMonth DD, YYYY' FROM dual; select to_date'July 4, 2007','fxMonth DD, YYYY' FROM dual ERROR at line 1: ORA-01858: a non-numeric character was found where a numeric was expected 7、显示系统当前时间 sysdate SQL> select to_charsysdate,'YYYY-MM-DD HH24:MI:SS' sysdata from dual; SYSDATA - 2022-02-20 15:22:54 名师归纳总结 - - - - - - -第 12 页,共 36 页精选学习资料 - - - - - - - - - 2.、General 通用函数2.1、NVL 判定函数运算一年的工资并且加上分红NVLcommission_pct,0 NVL对括号中第一个参数进行判定,如第一个参数为非 第一个参数为 NULL就显示为 0 2.2、NVL2 判定函数 2 NULL就显示其本身,如名师归纳总结 - - - - - - -第 13 页,共 36 页精选学习资料 - - - - - - - - - NVLcommission_pct,SAL+COMM, SAL NVL对括号中第一个参数进行判定, 如第一个参数为非如第一个参数为 NULL就显示第三个参数；2.3、NULLIF NULL就显示其次个参数,NULLIF将括号中的两个参数进行比较,如不等显示第一个参数,如相等显示为 NULL 名师归纳总结 - - - - - - -第 14 页,共 36 页精选学习资料 - - - - - - - - - 2.4、COALESCE 将括号中的参数,自左向右进行逐个判定,如第一个参数为非NULL,就显示其本身,判定终止,如第一个参数为NULL,就对下一个参数进行判定,以此类推,如前面的参数都为 NULL,就显示 NULL；2.5、CASE 依据部门的不同来运算工资2.6、DECODE 是 oracle 特有的函数,等同于CASE,但写法简洁；名师归纳总结 - - - - - - -第 15 页,共 36 页精选学习资料 - - - - - - - - - 05-Reporting Aggregated Data Using the Group Functions 组函数Where 子句不能用列别名,在1、Having 过滤 group by 的结果SELECT column,group_function FROM table WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BY column ; 字句的执行次序06-Displaying Data from Mutiple Tables 从多表查询数据1、JOIN 的语法-国际标准 SELECT table1.column, table2.column FROM table1 NATURAL JOIN table2 | JOIN table2 USING volumn_name | JOIN table2 ON table1.column_name = table2.column_name | LEFT|RIGHT|FULL OUTER JOIN table2 ON table1.column_name = table2.column_name | CROSS JOIN table2; -ORACLE标准 SELECT table1.column, table2.column FROM table1,table2 WHERE table1.column1 = table2.column2; 名师归纳总结 - - - - - - -第 16 页,共 36 页精选学习资料 - - - - - - - - - 2、别名SQL> Select employee_id,last_name,e.department_id,department_name 2 From employees e,departments d 3 Where e.department_id = d.department_id; MPLOYEE_ID LAST_NAME DEPARTMENT_ID DEPARTMENT_NAME - - - - 108 Greenberg 111 Sciarra 112 Urman 113 Popp 109 Faviet 206 Gietz 205 Higgins 100 Finance 100 Finance 100 Finance 100 Finance 100 Finance 110 Accounting 110 Accounting 106 rows selected. 3、NATURAL JOIN 自然连接 和 using 不能共存 系统自动将不同表中的相同的列进行自然连接-国际标准 SQL> Select department_id,department_name,location_id,city 2 From departments 3 Natural join locations; DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID CITY - - - - 270 Payroll 1700 Seattle 20 Marketing 1800 Toronto 40 Human Resources 2400 London 80 Sales 2500 Oxford 70 Public Relations 2700 Munich 27 rows selected. -oracle标准SQL> Select d.department_id,d.department_name,d.location_id,l.city 2 From departments d,locations l 3 Where d.location_id=l.location_id; DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID CITY - - - - 名师归纳总结 270 Payroll 1700 Seattle 第 17 页,共 36 页20 Marketing 1800 Toronto 40 Human Resources 2400 London 80 Sales 2500 Oxford 70 Public Relations 2700 Munich - - - - - - -精选学习资料 - - - - - - - - - 27 rows selected. 4、USING 指定不同表中的某一列进行连接 相同）-国际标准（只要求名字相同, 不要求数据类型SQL> Select employee_id,last_name,location_id,department_id 2 From employees join departments 3 Using department_id; （括号留意要加）EMPLOYEE_ID LAST_NAME LOCATION_ID DEPARTMENT_ID - - - - 108 Greenberg 1700 100 111 Sciarra 1700 100 112 Urman 1700 100 113 Popp 1700 100 109 Faviet 1700 100 206 Gietz 1700 110 205 Higgins 1700 110 106 rows selected. 国际标准补充：假如 using 语句中的列, where 也使用了这列,那么-oracle标准where 子句中不要加前缀；SQL> Select d.location_id, d.city,l.department_id 2 From locations d,departments l 3 Where d.location_id = l.location_id; LOCATION_ID CITY - - - DEPARTMENT_ID 名师归纳总结 1700 Seattle 270 第 18 页,共 36 页1800 Toronto 20 2400 London 40 2500 Oxford 80 2700 Munich 70 - - - - - - -精选学习资料 - - - - - - - - - 27 rows selected. 5、ON 连接两个表的列名可以不同,但内容要相同SQL> Select e.employee_id,e.last_name,e.department_id,d.location_id 2 From employees e join departments d 3 One.department_id=d.department_id; EMPLOYEE_ID LAST_NAME DEPARTMENT_ID LOCATION_ID - - - - 100 1700 108 Greenberg 111 Sciarra 100 1700 112 Urman 100 1700 113 Popp 100 1700 109 Faviet 100 1700 206 Gietz 110 1700 205 Higgins 110 1700 106 rows selected. 5.1、多表进行连接 SQL> select employee_id,city,department_name 2 from employees e 3 join departments d 4 on d.department_id=e.department_id 5 join locations l 6 on d.location_id=l.location_id; EMPLOYEE_ID CITY DEPARTMENT_NAME - - - 191 South San Francisco 192 South San Francisco 193 South San Francisco 194 South San Francisco 195 South San Francisco 196 South San Francisco 197 South San Francisco Shipping Shipping Shipping Shipping Shipping Shipping Shipping 106 rows selected. SQL> run 1 select employee_id,city,department_name 2 from employees e,departments d,locations l 3 where e.department_id=d.department_id4* and d.location_id=l.location_id EMPLOYEE_ID CITY DEPARTMENT_NAME - - - 名师归纳总结 191 South San Francisco Shipping 第 19 页,共 36 页192 South San Francisco Shipping - - - - - - -精选学习资料 - - - - - - - - - 193 South San Francisco 194 South San Francisco Shipping Shipping 195 South San Francisco 196 South San Francisco 197 South San Francisco Shipping Shipping Shipping 106 rows selected. 6、Self-Joins 用于同一张表的 JION SQL> select a.last_name|' works for '|b.last_name 2 from employees a,employees b3 where a.manager_id=b.employee_id; A.LAST_NAME|'WORKSFOR'|B.LAST_NAME - Ozer works for Cambrault Johnson works for Zlotkey Grant works for Zlotkey Livingston works for Zlotkey Taylor works for Zlotkey Hutton works for Zlotkey Abel works for Zlotk