7月27
Ddl语句，数据定义表 ，创建删除。SQL 结构化查询语句 DML数据操控表：对数据进行操作，如增删查改
Grid control网络控制 control 像是一个集成的管理工具。Bi做数据仓库的应用。
Entity实体集 每个实体集是表，每个关系是表
数据流程：建模----把用户脑海里的变成er模型表述----关系数据库中的表table model物理化-----在磁盘上进行配置。
- 查看hr用户名下的表，解锁hr用户：
$ sqlplus / as sysdba（直接利用操作系统身份验证）或SQL> conn / as sysdba在sql里切换身份
SQL> show user 进行身份确认
SQL> select table_name from dba_tables where owner='HR'; 查看里面有多少表
SQL> select * from hr.employees;
SQL> alter user hr account unlock identified by hr; 进行解锁并分配口令
$ sqlplus hr/hr或者SQL> conn hr/hr
SQL> show user
SQL> select * from tab;
SQL> select * from employees; 自己名下的表就不用写前缀
SQL> desc employees 查看表结构
- 使用sqlplus的全屏编辑功能：
$ echo $EDITOR
SQL> select * from hr.employees;
SQL> ed
SQL> / 执行
查询永远是行列的交叉。
Select last_name,salary 确定列
From employees 来自哪张表
Where department_id=90 确定行
Sql没有大小写之分，但一般语法关键字大写，对象的名称小写
可以对查询的结果，进行额外的运算
ANNUAL 列的别名，后加列名，可以写as 也可以不写
例：select last_name, salary*12*(1+COMMISSION_PCT) annual_salary from employees;
链接操作符|| ，为了让输出的信息可读性更高，还可拼凑select语句
Grant select on hr.employees to user01;把用户名下的表权限给user01
单引号是字符串的定界符。
Distinct去除重复值
Desc快速了解表结构
Where语句进行行和列的查询
字符串必须用单引号引起来 oracle的字符串区分大小写 日期：格式敏感
Select sysdate from dual; dual 空表，保存语法的完整性
Select last-name fyrom employees where hire_date=to_date(‘2006-05-23’, ‘yyyy-mm-dd’)
To_date 进行格式转换
- 基础select语句：
SQL> select * from employees;
SQL> desc employees
SQL> select LAST_NAME, SALARY, COMMISSION_PCT from employees;
SQL> desc departments
SQL> select department_id, department_name from departments;
SQL> select distinct DEPARTMENT_ID from employees;
SQL> select last_name, salary*12*(1+commission_pct) total_salary, department_id from employees;
SQL> select first_name||', '||last_name from employees;
SQL> select first_name||', '||last_name fullname from employees;
练习：
输出下列员工信息：
Eleni(first_name) Zlotkey(last_name) employeeid is ... at department .. total salary is …
- 使用连字符构造语句：
SQL> select table_name from user_tables;
SQL> select 'grant select on hr.'||table_name||' to scott;' from user_tables;
SQL> spool /home/oracle/grant.sql
SQL> select 'grant select on hr.'||table_name||' to scott;' from user_tables;
SQL> spool off
$ vi /home/oracle/grant.sql 去除没用的行
SQL> @/home/oracle/grant.sql
- 单引号的处理：
SQL> select 'I'm teaher' from dual;
ERROR:
ORA-01756: quoted string not properly terminated
SQL> select 'I''m teaher' from dual;
SQL> select q'{I'm teaher}' from dual; []<>()都可以
where和order by
数字条件：
SQL> select salary from employees where employee_id=100;
字符串大小写敏感：
SQL> select last_name, salary from employees where last_name='King';
SQL> select last_name, salary from employees where lower(last_name)='king';
SQL> select table_name, tablespace_name from user_tables where table_name='EMPLOYEES';
日期是格式敏感：
SQL> select sysdate from dual；
SQL> alter session set nls_date_format='RR-Mon-dd';
SQL> select sysdate from dual；
SQL> select last_name from employees where hire_date='2006-05-23';报错
SQL> select last_name from employees where hire_date='23-MAY-06';
SQL> select last_name from employees where hire_date=to_date('2006-05-23', 'yyyy-mm-dd');
区间查询：
SQL> select last_name from employees where salary>=3400 and salary<=4000;
SQL> select last_name from employees where salary between 3400 and 4000;
SQL> select last_name from employees where salary between 3000 and 5000 and department_id=50;
in：
SQL> select last_name from employees where department_id=30 or department_id=40 or department_id=50;
SQL> select last_name from employees where department_id in (30, 40, 50);
通配符：
SQL> select last_name, job_id from employees where job_id like '%\_MAN' escape '\';
null作为条件：
SQL> select last_name from employees where commission_pct is null;
SQL> select last_name from employees where commission_pct is not null;
and/or/not：
SQL> select last_name, job_id, department_id, salary from employees where job_id='SA_REP' or department_id=50 and salary>=8000;
SQL> select last_name, job_id, department_id, salary from employees where (job_id='SA_REP' or department_id=50) and salary>=8000;
