数据库知识-建表，约束，查询

建表

1. 创建一张表

create table test--表名
(
 --字段名 数据类型
 stname varchar2(10) not null ,
 stsex char(3),
 stdate date 
 stgrade varchar(5) default '一年级' 
);

2.复制表结构以及数据（不会复制约束）

create table test as select * from emp;

3.复制数据

insert into test select * from emp;

insert into test(empno,ename)(select empno,ename from emp);

4.修改表名

alter table test rename to test01;

5.给表添加备注

comment on table test is'测试表'

6.给表的字段添加备注

comment on column test.ename is '员工姓名'

7.改变表结构（添加列）

alter table test add(addr varchar2(100);

8.修改列名

alter table test rename column ename to name;

9.修改表结构 (字段)

alter table student modify (stsex char(4))

10.删除表结构（字段）

alter table abc drop (b);
alter table abc drop column a;

11.删除表数据内容，能回滚

delete from 表名  where 条件

12.,删除表数据内容，不能回滚

truncate table emp_form

13.删除表(慎用)

drop table emp_form

约束

约束类型
主键约束 :Primary Key Constraint 注：要求主键列数据唯一，并且不允许为空，pk_
唯一约束 :Unique Constraint 注：要求该列唯一，允许为空,uq_ 或uk_
检查约束 :Check Constraint 注：某列取值范围限制、格式限制等，如有关年龄的约束,ck_
外键约束 :Foreign Key Constraint 注：用于两表间建立关系，需要指定引用主表的那列,fk_

添加约束
通过字典表查看约束 表：user_constraints

select * from user_constraints where table_name ='STUDENT';

主键约束（主键列值必须唯一，不能为空）

alter table 表名 add constraint 约束名 primary key (需约束字段名)；

alter table grade add constraint pk_a primary key (stno);

select * from user_constraints;

唯一约束：唯一键列值必须唯一，允许为空

alter table 表名 add constraint 约束名 unique (需约束字段名);

alter table test1 add constraint   UK_job  unique (job);

检查约束：

alter table 表名 add constraint 约束名 check (约束字段名+约束条件)
alter table student add constraint ck_stsex check (stsex='男' or stsex ='女');
alter table student add constraint ck_stage check (stage between 10 and 20);

外键约束 ：建立表跟表之间的关系，需要明确指定引用主表那一列：(被引用的列必须为主键，或者唯一键)

alter table 表名 add constraint 约束名 foreign key (需约束字段名) references 引入外表(外表约束)
alter table student add constraint fk_stno foreign key (stno) references grade(stno) ;

删除约束

alter table 表名 drop constraint 约束名
alter table student drop constraint ck_stage;

拓展：
序列：生成唯一的值，自动增长
创建默认的序列
create sequence xulie;

查看当前的序列
select xulie.currval from dual;

查询下一个序列(每执行一次都会更新一次序列)
select xulie.nextval from dual;

创建指定参数的序列
create sequence xu_l
start with 10 --开始值
maxvalue 10000 --最大值
increment by 5 --增量值
cache 50;–缓存值(预先存五十个值)

删除序列
drop sequence xu_l;

插入语法
insert into 表名 values (列值1,列值2…); (tno number default 1) 默认值

修改语法
nupdate 表名 set 列名=新的值 where 条件

查询

1.基础查询

select * from 表 where  条件  order by 排序；--desc 降序，asc升序，默认升序

注：不等于号，<>,!= 都可以

2.连接

• 内连接(inner)
  语法1（等值连接）：显示多表均满足关联条件的数据

     select * from 表1 inner join 表2  on 表1.字段 = 表2.字段; 
          --语法2（等值连接）：
     select * from 表1,表2 where  表1.字段 = 表2.字段;

• 自然连接（natural)
  找到共同的列名，如果未找到就会出现笛卡尔积

 select * from 表1 natural join 表2;

• 左连接(left)
  查询出左边表所有的数据，如果右边表没有相关联的数据，就会空着

select * from dept left join emp on dept.deptno = emp.deptno;
select * from dept,emp where dept.deptno = emp.deptno(+);

• 右连接(right)
  查询出右边表所有的数据，如果左边表没有相关联的数据，就会空着

select * from dept right join emp on dept.deptno = emp.deptno;
select * from dept,emp where dept.deptno(+) = emp.deptno;

• 全连接(full) ：
  查询出左右表所有的数据

select * from dept full join emp on dept.deptno = emp.deptno;

• 自连接：把本身看成多张表，自己跟自己进行连接

  查询员工编号，姓名，上级的编号，上级的姓名

select t1.empno,t1.ename,t1.mgr,t2.ename from emp t1,emp t2 where t1.mgr = t2.empno

• 交叉连接(别名：笛卡尔积)

select * from emp cross join dept;

3.子查询：在查询里面嵌套的查询

• 不相关联子查询

 select * from emp where deptno =(select deptno from dept where dname = 'SALES');
 select * from emp where deptno = (select deptno from emp where ename = 'SCOTT') and ename <> 'SCOTT';
 select * from emp where sal>(select avg(sal) from emp t  where t.job=emp.job group by job); ```

 -    相关联子查询
  

```sql
select empno,ename,deptno,(select dname from dept where deptno = t1.deptno) from emp t1;

拓展：
exists:判断子查询是否返回结果，有：TRUE 没有：FALSE
查询出不存在员工的部门信息
select * from dept where exists (select 1 随意写 from emp where deptno = dept.deptno);

4.分页查询
伪列 (每个表都有，但不能修改，只能用于查询)
rowid：每一行数据的地址，自动生成，且不会重复
rownum：单表查询结果，数据行的序号，自动生成，且不会重复

**注意：**rownum从1开始，没有1就没有二，随着没有后面的页码。每张表都会存在一个rownum
例如：
1）、查询出第一个的员工信息

select t1.*,rownum from emp t1 where rownum =1;

2）、查询第三个，第四个员工信息

select t2.* from(
select t1.*,rownum r from emp t1 ) t2
where r =3 or r =4;