一、DDL语句
create table customer(
customerId char(30) primary key, -- 主键约束 primary key
name varchar2(50) not null, --非空约束
address varchar2(200),
email varchar2(50) unique, --唯一性约束
sex char(2) default '男' check(sex='男' or sex='女'), -- default 默认值 --默认值约束，check约束还可以这样写check( sex in ('男','女'))
cardId char(18) unique );
建表语句的其他写法
--建一张表跟另外一张表的所有的列或部份列一样
--注意：跟原来表不一样的地方就是约束关系全部丢失
1、--建一张表跟emp表一样(不要数据，只要where条件不成立或无数据）
create table emp_spare as select * from emp where 1=2;--注意要加as
2、 --建一张表跟emp表一样（要跟emp数据一样）
create table emp_spare1 as select * from emp ; - -相当于备份emp表
3、--查看表结构:
desc 表名 (describe 表名)
4、--修改表结构 alter
1） --添加列的语法 (alter table 表名 add 列名 类型(长度) 约束)
alter table customer add age number(2) check(age>=1 and age<=100);
2）--修改列的语法(alter table 表名 modify 列名 类型(长度))
alter table customer modify age number(3);
3）--修改列名的语法(alter table 表名 rename column 原列名 to 新列名)
alter table customer rename column age to sage;
4）--删除列名的语法(alter table 表名 drop column 列名）
alter table customer drop column sage;
5）更改表名rename rename 原表名 to 新表名;
rename emp_spare to emp_spare2;
6）truncate删除整张表的数据 ,但不删除表结构
--truncate的语法：truncate table 表名；
truncate table emp_spare1;
--drop 删除整张表（表结构和数据全被删除）
--drop的语法：：drop table 表名；
drop table emp_spare1;
drop table emp_spare2;
drop table purchase;
drop table customer;
drop table goods;
二、DML语句
DML语言的关键字 insert添加 update更新 delete删除
1、--insert语句插入数据（执行完后注意要commit)
--1）insert单条数据
--insert单条数据 (两种方式）
--a.第一种方式：
insert into 表名 (列名列表) values (值列表)
--上面插入的列名列表指的是所要插入的列的名称列表，用逗号隔开
--值列表指的是所插入的值的列表 ，用逗号隔开
--注意：列名列表跟值列表一定要顺序和值类型和个数一致 ，char或varchar2类型必须加单引号，不能不加单引号，也不能用双引号 ；注意：列名列表可以选择部份列，部份列中必须要包括非空约束列，否则会无法插入
insert into goods (goodsid, goodsname, unitprice, category, provider) values('g001', 'bike', 3000, 'white', 'UFO');
insert into goods (goodsid, goodsname, unitprice , provider) values('g002', 'Tv', null, 'sony');
--b.第二种方式：
insert into 表名 values (值列表)
--表示所有的列都需要插入 ，如果为空则输入null，如果是默认值则输入default,个数一个也不能少而且按顺序来设置
insert into customer values( 'c001', 'zhangsan', '上海市浦东新区浦东大道500号102室', 'zhangsan@qq.com', default, null);
insert into customer values('c002', 'lisi', '上海市浦东新区浦东大道1500号602室', 'lisi@qq.com', '女', '123456789011');
2）插入多条数据
语法：insert into 表名 (列名列表) select 列名列表 from 表名 [where ...]
--注意：表名后的列名列表跟select后的列名列表个数类型(兼容）一致
insert into customer (customerid, name) select empno, ename from emp;
--注意：select前面不可以加as
2、--update更新数据
--1）单列更新语法：（执行完后注意要commit)
--update 表名 set 更新的列名=更新值 【 where 条件表达式 】 where条件起到过滤作用
【create table emp_spare as select * from emp;】
update emp_spare set sal=2000 where ename='SMITH';--把姓名是SMITH员工薪水改为2000
update emp_spare set COMM=600 where deptno=20;--把部门20的员工奖金改为600
--2）多列更新语法：
--update 表名 set 更新的列名1=更新值1，更新的列名2=更新值3，。。。【 where 条件表达式 】
update emp_spare set sal=sal*2,comm=700 where deptno=20;
update emp_spare set comm=null where deptno=20;--清空某列或删除某列数据用：列名=null
3、--delete删除数据（行），不删除表结构
--语法： delete from 表名 【 where 条件表达式 】 --不加where条件表示整张表数据删除 等价于truncate
--delete from之间不能加任意东西
delete from emp_spare where deptno=20;--删除部份数据
delete from emp_spare;--整张表的删除
--注意：特别是主表与外表的删除（delete,drop,truncate)，先删外表，再删主表
--truncate 与delete区别
--1） truncate 与delete语法不同
--2） truncate是ddl语句，delete是dml语句里
--3） Truncate只能删除整张表数据，delete既可以删除整个表数据，也可以删除部份数据
--4) truncate属于隐式递交，所以不能恢复，delete属于显式递交，可以撤消和恢复的 -
-5） truncate不记录日志,delete记录日志,所以删除速度比delete快 -
- DML都是可以撤消的，DDL是隐式递交，不能撤消操作
三、DQL语句
1、--查询整张表所有列的数据 *是统配符，表示所有的列 t表示表的别名
select * from emp t; --表的别名这个语句中是可以加也可以不加
select * from emp；
2、 --查看部分列数据，列名间用逗号隔开
select ename,mgr,sal,comm from emp;
--dual==>oracle伪表 oracle写select语句必须要表名
select 1+1 from dual;--当查的内容不涉及到某个表时，那么就使用这张表作为 表名
select sysdate from dual;--查看数据库的系统时间
select user from dual;--查看当前连接的用户名
select 1+1,sysdate,user from dual;--dual获取结果只能是单行，但可以是多列
--列的别名
select ename,sal*12 as 年薪 from emp;--列的别名 as可以加也可以不加
select ename,sal*12 年薪 from emp;
select ename,sal*12 "年薪" from emp;
3、--排序 order by (select语句的最后面）
--1）升序(默认) order by 列名 【asc】
--2）降序 order by 列名 desc
--查询薪水从高到低进行排序 select * from emp order by sal desc;
--查询入职时间从先往后进行排序 select * from emp order by hiredate;--时间越早越小，越晚越大
--多列排序 ：先按第一列排序，当第一列值一样时，再按照第二列排序
--查询薪水从高到低,入职时间从先往后进行排序 select * from emp order by sal desc,hiredate;
--order by 后面数字也可以 数字表示第几列 从1开始数 select * from emp order by 6 desc;
--order by 后面也可以跟表达式 --按员工年薪从高到低进行排序
select * from emp order by sal*12 desc;
select ename,sal*12 from emp order by sal*12 desc;
4、--where条件 选择数据、过滤数据作用
--select语法关键字顺序 ：
select 列名列表 from 表名 where 条件 order by 排序列
-查询薪水超过2000的员工信息 select * from emp where sal>2000;
--查询薪水超过2000，小于5000的员工信息 select * from emp where sal>2000 and sal<5000;
--查询员工入职时间在1981年之后（包括1981年）的员工信息 select * from emp where hiredate>=to_date('1981-01-01','yyyy-mm-dd');
--查询薪水不是2000的员工信息 select * from emp where sal !=2000;
select * from emp where sal<>2000;
select * from emp where not sal=2000;
--查询没有奖金的员工信息 select * from emp where comm=null;--错误的，where条件中不可以用:列名=null
select * from emp where comm is null;--要用 is null
--查询有奖金的员工信息 select * from emp where comm is not null;
select * from emp where not comm is null;
5、--模糊查询 between and ,in ,like（关键）
1） --between and
--查询员工入职时间在1981年到1983间的员工信息 select * from emp where hiredate>=to_date('1981-01-01','yyyy-mm-dd') and hiredate<=to_date('1983-12-31','yyyy-mm-dd');
--等价于 select * from emp where hiredate between to_date('1981-01-01','yyyy-mm-dd') and to_date('1983-12-31','yyyy-mm-dd');
2）--条件为等于多个值时用in
--查询部门为10，20，30，40的员工信息 select * from emp where deptno=10 or deptno=20 or deptno=30 or deptno=40;
--等价于 select * from emp where deptno in (10,20,30,40);
3） --like关键字(%表示匹配0到多个字符 _表示匹配单个字符）
--查询员工表姓名是S开头的员工信息 select * from emp where ename like 'S%'；
--查询员工表姓名是S结尾的员工信息 select * from emp where ename like '%S';
--查询姓名是包含S的员工信息 select * from emp where ename like '%S%';
--查询姓名第二个字符是M的员工信息 select * from emp where ename like '_M%';
--查询姓名倒数第二个字符是R的员工信息 select * from emp where ename like '%R_';
--查询姓名是5个字符员工信息 select * from emp where ename like '_____';
--查询姓名是M开头或S开头的员工信息 select * from emp where ename like 'S%' or ename like 'M%';
6、--distinct 去重
select distinct job from emp; select distinct job,mgr from emp;
7、--分组查询(关键字group by )
--分组函数（聚合函数）
--1）count(*) 统计行数 select count(*) from emp;----统计员工人数
count(1) count(列名） 常用 count(*)
select count(*) from student;--统计学生人数
--2）sum(列名) 累计求和 select sum(sal) "每月所付薪金" from emp;
--3) max(列名）求最大值 select max(sal) from emp; --查询员工表的最高薪水
select max(sage) from student;--查询学生表的最大年龄
--4）min(列名）求最小值 select min(sal) from emp;--查询员工表的最低薪水
--5）avg(列名）求平均值 select avg(sal) from emp;--查询员工的平均薪水
select avg(sage) from student;--查询员工的平均年龄
--查询课程编号是SHC002的最高分 select max(grade) from score where cid='SHC002';
--查询年龄超过18岁的男生人数 select count(*) from student where sage>18 and ssex='M';
6）--group by 分组
--select语法关键字顺序 --select 列名列表 from 表名 where 条件 group by 分组列 having分组条件 order by 排序列 --
分组查询语法规则：
--select后面列除了分组函数之外，只能写group by 后面的列，其他的列是不允许写在select后面select ssex,count(*) from student group by ssex;
--查询年龄超过18岁男生女生人数 select ssex,count(*) from student where sage>18 group by ssex;
--查询各个部门的部门编号及人数 select deptno,count(*) from emp group by deptno;
--列出各种工作的最低工资 select job,min(sal) "最低工资" from emp group by job;
--列出各个部门的MANAGER 的最低薪金 select deptno,min(sal) "最低薪金" from emp where job='MANAGER' group by deptno;
--多列分组，统计各个班的男生和女生人数
select * from student1; select classno,ssex,count(*) from student1 group by classno,ssex;
--统计年龄大于15岁的各个班的男生和女生人数 select classno,ssex,count(*) from student1 where to_char(sysdate,'yyyy')-to_char(sbirthday,'yyyy')>15 group by classno,ssex;
7）--having分组函数条件，加在group by 后面
--查询部门人数至少有3个人的部门编号 select deptno from emp group by deptno having count(*)>=3;
--查询各个部门最高工资超过2500的部门编号 select deptno from emp group by deptno having max(sal)>3000;
列出最低薪金大于1500的各种工作 select job from emp group by job having min(sal)>1500;
--查询学生平均分超过80分的学生编号 select sid from score group by sid having avg(grade)>80;
--查询每门课最高分超过90分的课程编号 select cid from score group by cid having max(grade)>90;
--查询课程编号包含1的而且平均分大于80分的课程编号 select cid from score where cid like '%1%' group by cid having avg(grade)>80;
--查询各个班男生人数超过2个人的班级 select classno from student1 where ssex='男' group by classno having count(*)>2;
8）--子查询（嵌套查询）
--应用场景：1.一条查询语句的查询条件依赖另外一条查询语句的查询结果。 2.一条查询语句的查询结果是作为另外一条查询语句的查询表(查询依据)。
--子查询查询结果有两种情况：1）单行 = > < !=
2）多行 in
--查询员工表中工资最高的员工信息 select * from emp where sal=(select max(sal) from emp);
--查询分数最高学生编号 select sid from score where grade=(select max(grade) from score);
--查询比SMITH工资要多的所有员工信息 select * from emp where sal>(select sal from emp where ename='SMITH');
--列出薪金高于公司平均薪金的所有员工 select * from emp where sal>(select avg(sal) from emp);
--列出与SCOTT从事相同工作的所有员工 select * from emp where job=(select job from emp where ename='SCOTT');
--列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金 select ename,sal from emp where sal in (select sal from emp where deptno=30);
--列出薪金高于部门30的所有员工的薪金的员工的姓名和薪金 select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);
--查询至少有三名员工的所有部门名称 select dname from dept where deptno in (select deptno from emp group by deptno having count(*)>=3);
--查询每个学生的平均分高于所有学生考试平均分的学生编号 select sid from score group by sid having avg(grade)>(select avg(grade) from score);
9）--多表关联
--1)内联接
--内联接(特点：关联字段相等的数据会显示出来,关联字段为空的数据是不会显示出来)
--内联接第一种语法：select 列名 from 表1 inner join 表2 on 表1.关联字段=表2.关联字段 --（关联字段一般为主外键关联的列名）
--查询员工表的员工姓名以及所在的部门名称 select ename,dname from emp_spare t inner join dept d on t.deptno=d.deptno;
--内联接第二种语法：select 列名 from 表1,表2 where 表1.关联字段=表2.关联字段
select ename,dname from emp t , dept d where t.deptno=d.deptno;
select * from emp_spare; insert into emp_spare select 8001,'lisi',job,mgr,hiredate,sal,comm,null from emp where ename='SMITH';
--2）外联接（左外联接，右外联接，全联接）
--左外联接语法：select 列名 from 表1 left join 表2 on 表1.关联字段=表2.关联字段
--查询员工表的所有员工姓名以及所在的部门名称 select ename,dname from emp_spare t left join dept d on t.deptno=d.deptno;
--右外联接语法：select 列名 from 表1 right join 表2 on 表1.关联字段=表2.关联字段
--全联接语法：select 列名 from 表1 full join 表2 on 表1.关联字段=表2.关联字段。 既是左外联接又是右外联接，左右两表都全部显示
3）三表关联（两两关联）
--查询学生姓名、课程名称、考试分数 语法1： select sname, cname, degree from student1 s inner join score sc on s.sno=sc.sno inner join course1 c on sc.cno=c.cno;
语法2：select s.name, c.cname, sc.degree from student s, score sc, course c where s.sno=sc.sno and c.cno=sc.cno;
