[数据库] 数据库基础语法

1.create table

整个create table和select from一样，是一条完整的语句。语法为：

create table name(属性 type);

因为是一条语句，所以结尾需要有分号，括号里的属性和约束相当于参数，多个属性之间用逗号分割。例：

create table 班级表(
    班级名     	varchar(10),
    所属专业    	varchar(8),
    班主任代号 	varchar(10),
 	学生数 		number(3),
    primary key (班级名) --定义主键
    foreign key(班主任代号) REFERENCES 班主任表(代号) --定义外键
);

--删表
drop table class;

值得注意的是建表时可以定义表的primary key(主键)和foreign key(外键)：主键是能确定一条记录的唯一标识。外键用于表示与另一张表的关联。可以理解为相关联表的主键，使用主键和外键可以保持数据的一致性。

2.alter table add/motify/drop/rename

alter table 用于对表属性的更改，例如对上面class表进行操作：

--添加班主任这个属性
alter table 班级表 add(班主任 varchar(10));
--对已有属性的更改
alter table 班级表 motify(班主任 varchar(12));
--将班主任属性改名为teacher
alter table 班级表 rename 班主任 to teacher;
--删除班主任这个属性
alter table 班级表 drop column teacher;

为表补充定义主码、外码【级联删除、级联修改】。

 --为dept表添加主键
ALTER TABLE dept ADD PRIMARY KEY(deptno);
--为dept添加名为fk_dept的外键约束,constrant:约束
ALTER TABLE dept ADD CONSTRAINT fk_dept FOREIGN KEY(cid) REFERENCES countries [on delete cascade];

3.insert into values()

为表插入数据，注意数值与属性一一对应：

Insert into dept(deptno,dname,CID,LOC) values ('60','PRODUCTION','CN','xiangtan');

4.update set

更新数据，多个属性的更新用逗号分开：

--将emp中工作为null的人的工作设为clerk，入职日期设为2020-05-10
UPDATE emp SET job = 'clerk',hiredate = to_date('2020-05-10','YYYY-MM-DD') where job is null;
--所有职工的工资增加5000
Update emp set sal=NVL(sal,0)+5000;

5.case when then

case分情况：

update dept set emps =
case 
when deptno=10 then 
(select count(*) from dept left outer join emp on dept.deptno = emp.deptno where emp.deptno=10)
when deptno=20 then 
(select count(*) from dept left outer join emp on dept.deptno = emp.deptno where emp.deptno=20)
else 
(select count(*) from dept left outer join emp on dept.deptno = emp.deptno where emp.deptno=30)
end;
--更新dept表，按照不同的deptno算出部门的人数

6.delete from

删除某个属性：

--删除20号部门
Delete from dept where deptno=20;
--删除工资低于15000的职工
Delete from emp where sal<15000;

7.查询语句

select子句用于列出查询结果中需要的属性，它可以：

• 添加distinct合并相同的查找项；

• 使用where限制查询条件，where子句中可以使用and，or和not；

• 通过group by在查询时按属性分组查询；

• 用order by (ASC/DESC)（升序/倒序）排序。
  from子句是查询中需要访问的关系列表；
  where子句是一个作用在from子句中关系属性上的谓词。

--列出工资(SAL)在15000到25000之间的所有员工的ENAM和SAL，并按ENAME排序。
select ename,sal from emp where sal between 15000 and 25000 order by ename;
--显示所有不同的工作类型，并降序排序。
Select distinct job from emp order by job DESC;
-- 列出在每个部门工作的职工数量。
Select deptno,count(*) from emp group by deptno;

8.模糊查询(like)

用 % 代表一段字符，用 _ 代表单个字符：

• '%1%'表示包含1的字符串

• '_1%'表示第二位为1的字符串

--显示姓名中包含AR和IN的职工姓名，并按姓名排序。
Select * from emp where ename like '%AR%' and ename like '%IN%'  order by ename;

查询的字符串里面包含%和_时，需要用到转义字符，like比较运算中可以用escape来定义转义字符。

 --以%a开头的字符串
like '\%a%' escape '\'

9.一些常用的函数

数学函数：（用n代表属性）

• NVL(n,0)：属性为空时，将属性按0处理（注意null和0的区别）；

• count(n)：属性的数量；

• round(n,0)：将属性取整；

• avg(n)：取属性的平均值；

• max(n),min(n)：取属性的最大值和最小值

• abs(n)：取绝对值

字符串函数：

• upper(s)：将字符串s转换为大写
• lower(s)：将字符串s转换为小写
• trim(s)：去掉s后面的空格

10.savepoint保存点

在执行操作后设置保存点以方便回退，在提交或保存后保存点将失效：

--创建保存点
savepoint sp1;
--回退至保存点
rollback to sp1;

11.view视图

视图是基于 SQL 语句的结果集的可视化的表。它包含行和列，就像一个真实的table。视图中的字段来自一个或多个数据库中的真实的表中的字段。我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句。

语法：CREATE VIEW view_name AS …

在视图中使用表达式或函数时，必须在CREATE VIEW语句中显式指定视图的所有列名。

--创建视图
--1.创建一个视图v1，包含emp表中的“名字”和“工资与提成之和”，并按姓名排序
create view v1("SAL","COMM") as Select ename,sal+NVl(COMM,0) from emp order by ename;
--2.创建一个视图v2，包含emp表中部门号为20的职工的“职工号”和“姓名”
Create view v2 as select empno,ename FROM emp WHERE deptno = 20;

--查询视图
select * from v1;

--删除视图
drop view v1;