数据库

一、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;