数据库学习手册

*sql 语言是不区分大小写 *标识符：表名：多个单词用下划线分割 字段名：多个单词用下划线分割 1、以 dba 连接到 oracle 服务端( 需要指明 as sysdba ；末尾没有分号 ) sqlplus sys/kjlink0:1521/orcl as sysdba 当连接到数据库后，需要切换其他用户，使用 conn 命令 conn kjlink/kjlink0:1521/orcl; 2、创建用户（末尾有分号) create user kjlink identified by kjlink; 刚创建的用户不具备任何权限，此时要分配两个基本的角色 GRANT CONNECT TO TEST;-分配连接到数据库的角色 GRANT RESOURCE TO TEST;-分配访问资源的角色 解锁已有用户：alter user scott account unlock; 3、更改用户密码 alter user kjlink identified by oracle; 4、删除用户 drop user kjlink; drop user kjilnk cascade;-删除用户的级联数据（如果用户下已经存在表，或者视图等其他 对象时，需要用此命令） 5、基本操作 显示所有表 select * from tab; 查看系统时间 select sysdate from dual; 可以查看当前用户 select user from dual; 查看当前用户还可以使用 show user 来显示 查看表结构 desc 表名； 连接用户 conn system/admin; 建立连接 disc 断开连接 退出 exit/quit 6、练习： 1） 、以 sys 登录，每个学生创建一个以自己姓名拼音第一个字母组成的用户名（密码相同） ； 2） 、分配基本权限 3） 、练习以上 sql 基本语法 select * from all_users;-查看所有用户 7、数据定义语言（DDL） 1） 、创建表 语法：Create table 表名 ( 列名 1 类型 1, 列名 2 类型 2, 完整性约束) 示例 create table t_tab(id number(10),name varchar2(8); 2） 、删除表 DROP TABLE ; 示例 drop table t_tab； 3） 、向表中添加新字段 ALTER TABLE ADD (字段 1 类型 NOT NULL, 字段 2 类型 NOT NULL ); 示例 alter table t_tab add(create_date date); 4） 、修改表中字段（对类型修改） ALTER TABLE modify(字段 1 类型,字段 2 类型 ); alter table t_tab modify(create_date timestamp); 5） 、删除表中字段 ALTER TABLE drop(字段 1,字段 2 ); alter table t_tab drop(create_date); 6） 、修改表中字段 Alter table 表名 rename column PSWD to PSWD1; alter table t_tab rename column id to user_id; 7） 、修改表的名称 RENAME to ; rename t_tab to m_tab; 8、数据操纵语言（DML） 1） 、插入操作 语法： insert into tablename (column1, column2, ) values (column1_value, column2_value, ) insert into m_tab(user_id,name) values(123,张三); -插入日期时，需要使用 to_date()方法，把一个符合日期格式的字符串转化为 date 类型。 如果小时是 24 小时制，需要使用 hh24 insert into m_tab(user_id,name,create_date) values(234,李四,to_date(2014-10-12 11:09:10,yyyy-mm-dd hh:mi:ss); insert into m_tab(user_id,name,create_date) values(111,李四,to_date(2014-10-12 14:09:10,yyyy-mm-dd hh24:mi:ss); -如果是需要插入微秒，需要使用 ff 来格式化，并且使用 to_timestamp(); insert into m_tab(user_id,name,create_date,create_timestamp) values(222,李四,to_date(2014-10- 12 14:09:10,yyyy-mm-dd hh24:mi:ss),to_timestamp(2014-10-12 14:09:10:123,yyyy-mm-dd hh24:mi:ss:ff); 2） 、查询操作 SELECT columnname/* FROM table -如果查询的字段长度超过了 dos 窗口默认的宽度，需要用 set linesize 500;增加显示的宽度 select * from m_tab; -查询日期时，可按照自定义格式输出日期（使用 to_char()）; select user_id,name,to_char(create_date,yyyy-mm-dd hh:mi:ss) from m_tab; -查询 timestamp 时要注意使用 ff select user_id,name,to_char(create_date,yyyy-mm-dd hh:mi:ss),to_char(create_timestamp,yyyy- mm-dd hh:mi:ss:ff) from m_tab; 3） 、删除操作 语法： delete from tablename where condition-如果不加 where 指明条件，会删除表中所有数据 delete from m_tab where user_id = 123;-删除 user_id 为 123 的行记录 4） 、更新操作 语法： update tablename set column1=value1, column2=value2, where condition-如果不加 where 条件，会把所有记录的列改为新值 update m_tab set name=王五 where user_id=234; update m_tab set user_id=890,name=赵六 where user_id=234; 9、oracle 函数：相见 ORACLE APIoralce_funs.docnvl decode 函数 10、DML 语句详解 select deptno as dn from dept where deptno 20; select 语句执行顺序 from dept1 where deptno202 select deptno as dn3; 11、BETWEEN .AND.在两个值之间 (含), select * from dept where deptno between 10 and 30; = select * from dept where deptno =10 and deptno null; 15、 使用 ORDER BY 子句对行结果排序，多列排序使用,分割 ASC: 升序 , 默认的 DESC: 降序 ORDER BY c 子句必须是 SELECT 最后的位置. select deptno as dn from dept where deptno 20 order by deptno asc; from dept1 where deptno202 select deptno as dn3 order by deptno asc4; select deptno as dn from dept where deptno 20 order by dn asc; 16、group by 的执行顺序 select min(empno),deptno,job from emp group by deptno,job order by deptno; from emp1 group by deptno,job2 select min(empno)3 order by deptno4; 17、where 对每一条记录进行筛选 select min(empno),deptno,job from emp where deptno102 group by deptno,job3 select min(empno)4 order by deptno5; 18、having by 是对分组后的数据进行筛选 -按照部门和工种分组后，最小的员工工号不得小于 7369 select min(empno),deptno,job from emp where deptno7369 order by deptno; -按照部门和工种分组后，员工的工种不得是 CLERK select min(empno),deptno,job from emp where deptnoCLERK order by deptno; select min(empno),deptno,job from emp where deptnoCLERKorder by deptno; from emp1 where deptnoCLERK4 select min(empno),deptno,job 5 order by deptno6 19、连接查询的执行顺序 select * from employee e full outer join department d on e.dept_no = d.dept_no; from employee e1 full outer join department d2 on e.dept_no = d.dept_no3 select * 4 20、sql 执行顺序 select count(*), e.EMP_LNAME7 from employee e1 full outer join department d2 on e.dept_no = d.dept_no3 where e.emp_no 2581 and e.emp_no Jones6 order by e.EMP_LNAME asc;8 21、嵌套查询 -查询参加过 Gemini 项目的员工编号 select wo.emp_no from works_on wo where ject_no = (select project_no from project p where ject_name=Gemini);-p2:Gemini -查询 research 部门中的所有人的名字 1、查询什么？ select e.EMP_LNAME from employee e; 2、查询的条件 where e.dept_no=research 部门的编号 编号从哪来？ select d.dept_no from department d where d.dept_name=research 3、代入 select e.EMP_LNAME from employee e where e.dept_no=(select d.dept_no from department d where d.dept_name=research); -查 lname 为 Smith 的人参加的所有项目 1、查询什么？ select * from project;-都有项目信息 select * from works_on wo; 2、查询的条件 where wo.emp_no=lname 为 Smith 的人的编号 编号？ select e.emp_no from employee e where e.l_name=Smith; 3、代入 select * from project p where ject_no in(select ject_no from works_on wo where wo.emp_no=(select e.emp_no from employee e where e.EMP_LNAME=Smith); 22、exists 查询 -查看参加过项目的员工信息 select * from employee e where exists(select 1 from works_on w where w.emp_no = e.emp_no); -查看没有参加过项目的员工信息 select e.* from employee e where not exists ( select w.* from works_on w where e.emp_no = w.emp_no ); 23、事务的提交和回滚 oracle 的事务机制默认是不提交事务的，当前对数据表的操作在 sqlplus 窗口关闭的时候， 操作会撤销，必须显示的 commit; 如果需要回滚事务，使用 rollback; 设置 oracle 数据库的事务提交机制： set autocommit on/off; 24、数据库的安全性(DBA 常用操作) 了解数据库文件的存放位置： select file_name from dba_data_file; 25、系统安全性 表空间（tablespace）：用来放置用户表及数据的 -创建表空间 create tablespace my_tablespace datafile d:/oraclespace/my_tablespace.dbf-指明表空间对应的物理硬盘的文件位置 size 10m-初始化的文件大小 autoextend on next 5m-超过初始化大小后，自动增长的幅度 maxsize 20m;-文件上限大小： unlimited 文件大小不受限 -创建临时表空间：缓存大数据 create TEMPORARY tablespace my_temp_tablespace tempfile d:/oraclespace/my_temp_tablespace.dbf size 10m autoextend on next 5m maxsize 20m; 创建好表空间后，验证：select tablespace_name from dba_data_files; 指定用户表空间： create user my_user identified by kjlink default tablespace my_tablespace TEMPORARY tablespace my_temp_tablespace; 创建角色： create role my_role; 权限分类： create table drop table insert delete update select create session grant select on jy.employee to my_role;-把对应权限赋给相应的角色 grant delete on cjh.employee to my_role; grant create session to my_role; grant my_role to my_user;-把角色赋给用户 revoke delete on cjh.employee from my_role;-撤销权限 grant update on pjc.employee to my_user;-把单个权限赋给某个用户 alter user my_user quota unlimited on MY_TABLESPACE01;-给用户增加表空间 create table my_table(name varchar(20) tablespace MY_TABLESPACE01;-创建表的时候指明表空间，不使用默认的 grant create table to m