数据库语言笔记

一.DDL(数据定义语言)

1.创建表空间

-- 基本语法
create tablespace 表空间名 datafile '磁盘目录\数据文件名.dbf' size 大小;

-- 示例：
create tablespace stx2 datafile 'D:\02_workspace\03_dbData\oracle\stx2.dbf' size 128m;

-- 创建用户
create user frank2 identified by frank2 default tablespace stx2;

-- 用户授权 授dba权限
grant dba to frank2;

2.创建表

-- 创建表
create table student( id number, gender char,
name varchar2(32), creation_time timestamp
);

-- 查看表结构desc 表 名 ; desc student;

二.DML(数据操纵语言)

1.插入数据

-- 基本语法
insert into 表名[列名1, 列名2, ... 列名n] values(值1, 值2, ... 值n);

-- 示例1
insert into student(id, gender, name, creation_time) values(1, '1', 'frank', current_timestamp);

-- 示例2
insert into student values(4, '1', 'frank4', current_timestamp);

-- 注意事项
-- 1, 如果没有指定列，那么是给所有列赋值。
-- 2, 字段和值的顺序是一一对应的
-- 3, 如果插入的值是字符类型的，要用英文单引号引起来。
-- 4, 如果插入部份字段，那么必须写明要插入的字段名称。

-- 基本查询
select * from student;

-- 设置自动提交
-- 1，PL/SQL: 工具-->首选项-->窗口类型-->SQL窗口

-- 2，命令行设置：查看 show autocommit; 设置 set autocommit on;

2.删除数据

-- 基本语法
delete from 表名 [条件];

-- 示例：
delete from student;

-- 示例2: 按条件删除
delete from student where id = 1;

-- 删除表中所有数据，第二种方式： 建议熟悉delete truncate 的区别。查看P307。
truncate table student;

--删除表格
drop table 表名;

3.更新数据

-- 基本语法
update 表名 set 列名1 = 值1, 列名2 = 值2,... 列名n = 值n [where 条件];

-- 示例：
update student set name = '肖江红' where id = 1;

三.DQL（数据查询语言）

1.基本查询

-- 基本语法
select 列名1, 列名2, ... 列名n from 表名;

-- 示例
select * from emp;

-- 查询指定列名：
select ename, sal from emp;

-- 查询工资总和 nvl(列名, 0); 
select ename, sal, comm,sal + nvl(comm, 0) from emp;

-- 起别名 可以用as 关键字，也可以省略
select ename, sal, comm,sal + nvl(comm, 0) 工资总和 from emp;

-- 查询工资在2500以上的员工
select * from emp where sal > 2500;

-- 查询工资介于2500~5000之间的员工
select * from emp where sal between 2500 and 5000;

-- 查询有奖金的员工
select * from emp where comm is not null;

-- 查询员工姓名中含有’天‘的
select * from emp where ename like '%天%';h 

-- 查询姓张的员工：
select * from emp where ename like '张%';

-- 查询员工姓名是两个字的 两个下划线
select * from emp where ename like '__';

2.排序

--基本语法 默认升序ASC 降序DESC
select * from emp order by 列名;

--示例
select * from emp order by sal;

--多列排序
select * from emp order by sal asc,hiredate desc;

--起别名

3.聚合函数

--count:可以写*代表这一行中没有null值就会统计；count不会统计空值；
select count(*) from emp;

--count 去重
select count(distinct job) from emp;

--求平均值
select avg(sal) from emp;

--求最大值
select max(sal) from emp;

--求和
select sum(sal) from emp;

4.分组查询

--基本语法，关键字：group by
select 列名，聚合函数 from 表名 group by 列名;

--示例
select job count(1) from emp group by job;

--统计每个岗位的平均工资，人数
select job avg(sal),count(1),from emp group by job;

--统计每个岗位的平均工资，人数，按工资倒序排序
select job avg(sal),count(1),from emp group by job order by avg(sal) desc;

--在上一个需求基础上排除掉老板的工资（工资少于5000）
select job avg(sal),count(1),from emp where sal<5000 group by job order by avg(sal) desc;

--统计每个岗位的平均工资，人数，按工资倒序排序，要求统计岗位人数在两人以上的。
select job avg(sal),count(1) from emp group by job having count(1)>2 order by avg(sal) desc;

--理解where和having的区别
where在汇总前进行过滤，汇总完成后使用having过滤，所以having可以对汇总结果进行过滤，而where不可以。
having也可以对group by进行过滤。

--查询各部门（DEPTNO）下，有多少员工工资(SAL)一样，多少不一样；
select deptno,sal,count(1) from emp group by deptno,sal order by deptno, count(1) desc;

--统计每个部门有多少人，并且按照人数倒序；


--统计每个部门，岗位有多少人，

5.连表查询

--SQL-99
select 列名1，列名2...列名n from 表1 inner join 表2 on 条件;

--示例
select ename ,dname from emp inner join dept on emp.deptno=dept.deptno;

--SQL-89
select 列名1，列名2...列名n from emp,dept where 条件;

--示例
select enname,dename from emp,dept where emp.deptno=dept.deptno;

--起别名
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

6.补充

--1.主键关键字
constraint 主键名称 primary key;

--示例
create table test1(
    id number constraint pk_id primary key,
    name varchar(16),
);

--2.timestamp默认值
create table test3(
    id number primary key;
    name varchar(16),
    creation_time timestamp default current_timestamp
);

--插入指定日期
insert into test3(id,name,creation_time) values(2,'frank2',to_date('2021-04-01 14:25:09','yyyy-MM-dd HH24:mi:ss'));

--3.序列的创建
create sequence seq_test1 start with 1 increment by 1;

--使用序列
insert into test1(id,name) values(seq_test1.nextval,'frank');

--查看序列当前值
select seq_test1.currval from dual;

--删除序列
drop sequence seq_test1;