-----给scott用解锁
alter user scott account unlock;
----
/*给scott用户修改密码*/ --alter user 用户名 identified by 新密码
alter user scott identified by scott;
--创建一个用户
create user zyl --用户名
identified by zyl --密码
temporary tablespace temp --临时表空间
default tablespace users --默认的表空间
quota unlimited on users --在users的表空间上不收限制
---用户授权
--授权的关键字 grant
--权限有三个 connect 连接权限 resource 创建表或者删除表等权限 dba管理员的权限
grant connect,resource to zyl;
--给用户赋管理员权限
grant dba to 用户名
--查看用户
select * from dba_users;
---修改监听程序的文件地址 把localhost改为本机的计算机名 listener.ora文件和tnsnames.ora
---地址\product\11.2.0\dbhome_1\NETWORK\ADMIN
----创建用户
create user zyl1 --用户名
identified by zyl1 --密码
temporary tablespace temp --临时表空间 temp
default tablespace users --默认表空间 users
quota unlimited on users --在users的表空间上对用户不受限制
--一定要授权 授权关键字 grant resource , connect to 用户名
grant resource,connect to zyl1 ;
---删除一个用户 drop user 用户名 cascade
drop user zyl1 cascade;
---数据库的备份与恢复
--这个命令是在dos下执行
--备份/导出
--exp 用户名/密码@本地服务名（orcl） file=D:\test.dmp
--恢复/导入
--imp 用户名/密码@本地服务名（orcl) file=D:\test.dmp full=y
--oracle默认的端口号为1521
--数据定义语言（DDL） Data Definition Language : create (创建) alter（修改/更改） drop（删除）
--数据操纵语言（DML） Data Manipulation Language : insert(插入) select(查询) update(修改) delete(删除)
--事务控制语言（TCL） Transaction Control Language : commit(提交) rollback(回滚)
--数据控制语言（DCL） Data Control Language : grant(授权) revoke(撤销)
--oracle 的数据类型有三种
--字符 数值 日期
--字符有三种
--char 可变的字节长度 2000
--varchar2 可变的字节长度 4000
--nvarcahr2 可变的字符长度 2000
--数值
--number(n,m)
---数值类型如果只有一个参数代表参数长度的整数
--有俩个参数n和m n代表数值的总长度 m代表小数点后边长度
-- number(6,2) --长度为六位包含小数，整数为4位，小数点后边有俩位
--日期类型 date
---查询数据库的时间
--select sysdate from dual;
---数据定义语言 DDL
--创建一张表
/*
create table 表名 (
字段名 字段类型(数据类型), number(20)
字段名 字段类型(数据类型), varchar2(1000)
字段名 字段类型(数据类型), date
)
*/
create table t_student(
stuid number(10), -- number数值类型的长度为38位
stuname varchar2(25),
stuage number(3),
stubir date
)
==============================================================================暂时不用看
Mysql ----
创建表：
create table tta_student(
stuid INT(10) AUTO_INCREMENT PRIMARY KEY, -- AUTO_INCREMENT添加自增
stuname varchar(25),
stuage INT(3),
stubir date
)
--给表重命名 alter table 旧表名 rename to 新表名
alter table t_student rename to t_stu;
--在表中新增字段 alter table 表名 add 字段名 数据类型
alter table t_stu add stusex number(1); --在项目中一般用数字来代替性别 0 代表女 1 代表男
--修改字段的数据类型 alter table 表名 modify 字段名 新的数据类型
alter table t_stu modify stuname varchar2(4000);
alter table t_stu modify stusex varchar2(1000);
--删除表其中一个字段 alter table 表名 drop column 字段名
alter table t_stu drop column stusex;
--删除一张表 删除一张表
drop table t_stu ;
--查看表结构 不是在sql窗口看得 而是在命令窗口 命令窗口是在新建中打开的
desc t_student;
--给字段重命名 alter table 表名 rename column 旧字段名 to 新字段名( mysql 不支持此语句)
alter table t_student rename column stuname to stunames;
==================================================================================之上
---DML 数据操纵语言 insert增加 update 修改 select 查询 delete 删除
--查询这张表 select * from 表名
--*代表所有的字段
select * from t_student;
--查询姓名和年龄
--查询多个字段的时候select和from之间写字段名多个字段用逗号隔开
select stuage,stunames from t_student;
--查询生日，年龄，姓名
select stubir,stuage,stunames from t_student;
--查询姓名
select stunames from t_student;
--插入数据 或者增加一条数据
--第一中插入数据的方式 insert into 表名 (字段名,字段名,字段名,字段名,) values(对应字段的值，对应字段的值，对应字段的值，对应字段的值);
insert into t_student (stuid,stunames,stuage,stubir) values(1,'赵成燕',18,to_date('1996-11-11','yyyy-mm-dd'));
----TCL 事务控制语言 commit 提交 rollback 回滚
--第二种插入数据的方式
insert into t_student values(2,'王家田',18,to_date('1996-11-11','yyyy-mm-dd'));
commit;
--在插入一条数据
insert into t_student values(3,'强洪家1',19,to_date('1996-11-11','yyyy-mm-dd'));
insert into t_student values(4,'强洪家2',19,to_date('1996-11-11','yyyy-mm-dd'));
insert into t_student values(5,'强洪家3',19,to_date('1996-11-11','yyyy-mm-dd'));
insert into t_student values(6,'强洪家4',19,to_date('1996-11-11','yyyy-mm-dd'));
insert into t_student values(7,'强洪家5',19,to_date('1996-11-11','yyyy-mm-dd'));
rollback;
--查询生日和姓名
select stunames ,stubir from t_student;
--修改表中的数据
update t_student set stuage=20;
--查询修改后的数据
select * from t_student;
--删除所有的数据
delete from t_score;
-----算术操作符 + - * /
--计算出2+3*4/5的值 这样查询会出现冗余
select 2+3*4/5 from t_student;
--只出现一次 select 操作 from dual;
select 2+3*4/5 from dual;
--计算7*9
select 7*9 from dual;
--查询数据库时间
select sysdate from dual;
--给字段起别名 直接在字段后边空格加上别名
select stuid studentid,stunames studentname from t_student;
-- 给字段其别名 在字段后边加上 as 后边跟别名
select stuid as s ,stunames as t from t_student;
--给表起别名 表名后边加上对应的别名
select ts.* from t_student ts;
--查询姓名和生日
select ts.stunames,ts.stubir from t_student ts;
--拼接字符串
select '姓名:' || stunames || ': '|| '生日:'|| stubir from t_student;
select stunames || stubir || stuage from t_student;
--创建成绩表
create table t_score(
stuid number(10),
stuname varchar2(25),
math number(3),
english number(3),
chinese number(3)
)
--增加数据
insert into t_score values(1,'张三',100,60,80);
insert into t_score values(2,'李四',60,40,100);
insert into t_score values(3,'王五',80,100,20);
insert into t_score values(4,'赵六',30,20,10);
insert into t_score values(5,'宝强',70,20,50);
insert into t_score values(1,'张三',100,60,80);
insert into t_score values(6,'马化腾',100,100,100);
insert into t_score values(1,'张三',30,60,80);
commit;
--查询成绩表
select * from t_score;
--去掉完全重复的数据 distinct
select distinct * from t_score;
--去掉数学成绩中重复的数据
select distinct math from t_score;
--条件where
--查看姓名叫张三的信息 select * from 表名 where 条件
select * from t_score where stuname = '张三';
--查询id等于3的数据
select * from t_score where stuid=3;
--查询出姓名为王五的信息 字符串用单引号引起来 数字可以不用
select * from t_score where stuname='王五';
--查询数学成绩为60的学生信息
select * from t_score where math=60;
----------------比较操作符 > >= < <= != = <>
--查询出数学成绩大于60
select * from t_score where math>60;
--查询出语文成绩小于等于60的学生信息
select * from t_score where chinese <=60;
--查询出英语成绩不等于100的学生信息
select * from t_score where english!=100;
select * from t_score where english<>100;
-------------------------逻辑运算符 and or not
--查询出语文成绩大于60并且数学成绩大于60并且英语成绩大于60
select * from t_score where chinese>60 and math>60 and english>60;
--查询出语文成绩大于60并且数学成绩大于60
select * from t_score where chinese>60 and math>60;
--查询出语文成绩大于60或者数学成绩大于60
select * from t_score where chinese>60 or math>60;
--插入一条数据
insert into t_score values(10,'张氏','',60,80);
--查询出数学成绩不为空的学生成绩 not
select * from t_score ;
select * from t_score where math is not null;
--not null 可以设置字段不能为空
create table t_class(
classid number(10) not null,
classname varchar2(25)
)
insert into t_class values('','1608B');
--查询出数学成绩为空的
select * from t_score where math is null;
---between and 介于俩者之间
--查询出数学成绩在60到100之间的学生信息
select * from t_score where math between 60 and 100;
select * from t_score where math>=60 and math<=100;
--in 在特定范围内
--查询出id 等于 1,3,5,7,9的学生信息
select * from t_score where stuid in(1,3,5,7,9);
--查询出姓名为李四 王五 赵六的信息
select * from t_score where stuname in('李四','王五','赵六');
--like 像什么 模糊查询
--找到所有姓张的同学的信息
--%代表匹配多个字符
select * from t_score where stuname like '张%';
--找到第二字是三的学生信息
--_代表匹配一个字符
select * from t_score where stuname like '_三%';
--找到含有三字的学生信息
select * from t_score where stuname like '%三%'
---排序 order by
--查看学生id排序
---asc 默认 升序 从小到大
select * from t_score order by stuid;
--desc 降序 从大到小
select * from t_score order by stuid desc;
--查询数学成绩从大到小排序
select * from t_score order by math desc;
--查询数学成绩和语文从大到小排序
--如果排序中有俩个参数先排第一个 ，如果第一个参数一样了第二个参数按照顺序排列前提是第一个参数一样
select * from t_score order by chinese desc,math desc;
/*
oracle 复习
create user 用户名
identified by 密码
temporary tablespace temp
default tablespace users
quota unlimited on users
--授权
grant resource , connect to 用户名
--数据定义语言 DDL alter 修改更改 drop 删除 create
--创建一张表
create table t_student(
stuid number(10),
stuname varchar2(255),
stuage number(3),
stusex number(1), --0代表女 1代表男
stubir date
)
--查询表
select * from t_student;
--重名名表
alter table 表名 rename to 新表名
--重命名字段
alter table t_student rename column stunames to stuname;
--删除一个字段
alter table t_student drop column stubir;
--修改字段的数据类型
alter table t_student modify stuid number(20);
---查看表结构
desc 表名
---增加一个字段
alter table t_student add stubir date;
--删除一张表 drop table 表名
drop table t_student;
drop table t_score ;
--创建一张表
create table t_score(
stuid number(10),
math number(3),
chinese number(3),
english number(3)
)
--DML 数据操纵语言 insert 增加 select查询 update修改 delete删除 增删改查
--增加一个字段
alter table t_score add stuname varchar2(25);
--查询表
select * from t_score;
--新增数据
insert into t_score values(1,60,70,80,'张三');
insert into t_score values(2,30,40,70,'徐成燕');
insert into t_score values(3,10,20,30,'王家田');
insert into t_score values(4,5,7,8,'强洪家');
insert into t_score values(5,'',0,10,'于新帅');
insert into t_score values(1,60,70,80,'张三');
insert into t_score values(6,100,100,80,'张三1');
insert into t_score values(7,70,70,70,'张三2');
commit;
--TCL 事务控制语言 commit 提交 rollback回滚
--修改数据 update 表名 set
--修改语文成绩为0的改为20
--where
update t_score set chinese=20 where chinese=0;
--修改强洪家的英语成绩为80
update t_score set english=80 where stuname='强洪家';
--修改名字中含有三的语文成绩为100
update t_score set chinese=100 where stuname like '%三%'
--删除整张表
delete from 表名
delete 表名
--删除id=6的数据
delete t_score where stuid=6;
--删除id为 1,4,6,7数据
delete t_score where stuid in (1,4,6,7);
--查询
select * from t_score ;
--查询姓名和数学成绩
select stuname , math from t_score;
--拼接符 ||
select '数学成绩：' || math || '姓名' || stuname from t_score;
--查询学生的三科的平均成绩和姓名
select '平均成绩' || (math+chinese+english)/3 || '姓名'|| stuname from t_score;
--like 模糊查询
--查询出名字中含有三的
select * from t_score where stuname like '%三%';
--查询出名字中第二字是三的
select * from t_score where stuname like '_三%';
--查询出姓王的同学的信息
select * from t_score where stuname like '王%';
--between and 介于俩者之间
--查询数学成绩在80到100之间的
select * from t_score where math between 80 and 100;
--and
--查询出数学成绩大于80并且语文成绩小于80 的学生信息
select * from t_score where math >80 and chinese <80;
--or
--查询出数学成绩大于80或者语文成绩小于80 的学生信息
select * from t_score where math>80 or chinese <80;
--not
--查询出名字中不含有三的学生信息
select * from t_score where stuname not like '%三%';
*/
----------------------------------聚合函数--------------------------------------------
--聚合函数只返回一个结果
--max（） 最大值
--查询出英语成绩中考的最高分
select max(english) from t_score;
--min() 最小值
--查询出数学成绩中考的最低分
select min(math) from t_score;
--avg() 平均数
--查询出全班数学的成绩的平均数
select avg(math),avg(english),avg(chinese) from t_score;
--sum() 求和
--查询出语文成绩的总成绩
select sum(chinese),sum(math),sum(english) from t_score;
--count() 非空的总个数 具体到每一个字段
select * from t_score;
--查询出参加三科考试的总人数
select count(*) from t_score;
--查询出参加数学考试的人数
select count(math) from t_score;
--增加一条数据
insert into t_score values(null,null,null,null,null);
--gruop by 分组
--统计各个id有多少人
select stuid,count(*) from t_score group by stuid;
--统计各个学生姓名有多少人
select stuname,count(*) from t_score group by stuname;
--统计重名的人有哪些
-- having 过滤
select stuname,count(*) from t_score group by stuname having count(*)>1;
--增加一个字段
alter table t_score add stusex number(1);
--修改数据
select * from t_score for update;
--查询出男女分别的数学，英语，语文的平均分
select stusex,avg(math),avg(chinese),avg(english) from t_score group by stusex ;
--查询出数学成绩的分数的人数
select math,count(*) from t_score group by math;
/*-------------------------------------数学函数------------------------------------------------------------*/
--abs() 绝对值
select abs(-10) from t_score;
select abs(-10) from dual;
--ceil() 向上取整
select ceil(10.01) from dual;
select ceil(0.000000000000000000000000000001) from dual;
--floor() 向下取整
select floor(10.9999999999999999999999999) from dual;
--round() 四舍五入
select round(10.45) from dual;
select round(10.001) from dual;
select round(10.54,1) from dual;
select round(10.549898,3) from dual;
select round(10.549498,3) from dual;
select round(10.549898,-1) from dual;
select round(10.549898,-2) from dual;
select round(310.549898,-2) from dual;
select round(350.549898,-2) from dual;
select round(3150.549898,-3) from dual;
--trunc() 截取数字
--表示为小数点左边指定位数后面的部分截去
--把整数部分截取的用0来补齐
select trunc(10) from dual;
select trunc(10,32) from dual;
select trunc(10.45,1) from dual;
select trunc(10.45,2) from dual;
select trunc(10.56789,3) from dual;
select trunc(10.56789,-1) from dual;
select trunc(10.56789,-2) from dual;
select trunc(310.56789,-2) from dual;
select trunc(5310.56789,-3) from dual;
/*=====================================字符串函数=====================================================*/
--Concat()连接两个字符，等同于||(连接符)的效果
select '姓名'|| stuname || ',总成绩' || (math+chinese+english) from t_score;
select concat(concat('姓名',stuname),concat(',总成绩',(math+chinese+english))) from t_score;
select concat('a',stuname) from t_score;
--Initcap()将字符串的首字母大写；
select initcap('abcdefg') from dual;
--Instr（instruction）(src,search,start,number)在一个字符串中搜索指定的字符，返回发现指定的字符的位置
--注意：如果查找的指定的字符在字符串中不存在，则返回0，oracle的下标是从1开始的
--src：代表要查找的源字符串
--search：代表要查找的指定的字符串（可以使多个）
--Start：代表开始查找的位置
--number：字符串出现的次数
select instr('abcefabcef','c') from dual;--3
select instr('abcefabcef','gh') from dual;--0
select instr('abcefabcef','c',4) from dual;--8
select instr('abcefabcef','ce',1,2) from dual;--8
select instr('abcefabcef','f',1,2) from dual;--10
--Length()求长度
select length('ahdhdhd') from dual;--7
select length(stuname) from t_score;
--Lower()转化为小写
select lower('AAAABBBCC') from dual;
--Upper()转换为大写
select Upper('aaaabbbcc') FROM dual;
select * from t_score;
INSERT INTO T_SCORE VALUES(10,10,20,30,'杨yp普',0);
INSERT INTO T_SCORE VALUES(10,10,20,30,'杨YP普',0);
--查询字母含有yp的学生信息
select * from t_score where lower(stuname) like '%yp%';
select * from t_score where upper(stuname) like '%YP%';
--Rpad(右填充)|lpad(左填充)(str,length,char)，length代表整个字符串的长度
--当字符串的大小不满足指定大小时Rpad(右填充) lpad(左填充) 指定的字符串重复填充
select rpad('abc',10,'cfd') from dual;
select lpad('abc',10,'c')from dual;
select lpad('abc',10,'ght')from dual;
--Rtrim|ltrim(str,search)删除最右（左）边出现的字符串如果重复的都删除掉
select rtrim('aaaabbbcccddefgggg','g')from dual;
select rtrim('aaaabbbcccddefgggg','f')from dual;--删除不掉只删除最右边出现的
select rtrim('aaaabbbcccddefggggf','f')from dual;
select ltrim('aaaabbbcccddefggggf','a')from dual;
--Substr(str,start,count)取子字符串，从start开始，取count个
select substr('abcdefght',1,4) from dual;
select substr('abcdefght',3,20) from dual;
--Replace(string,s1,s2)替换字符串
--替换所有出现的字符
select replace('abcdeeeffgghh','e','呵呵')from dual;
--查询t_score
select * from t_score;
select replace(stuname,'三','呵呵') from t_score;
--Reverse( )反转字符串的每个字符
select reverse('abcdef') from dual;
select reverse(stuname) from t_score;
/*==================================转换函数==============================================*/
--To_char(日期，年月日)将日期类型转换为字符串
--注意：yyyy：年 mm：月 dd：天 hh24:24小时制 mi：分钟 ss：秒 day：星期几
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss day') from dual;
select to_char(sysdate,'yyyy:mm:dd hh24:mi:ss day') from dual;
select to_char(sysdate,'yyyy-mm-dd') from dual;
--To_date()将字符串格式的日期转换为日期类型
--trunc()可以截取日期和数字
--增加一个日期类型字段
alter table t_score add stubir date;
--增加一条数据
insert into t_score values(1,100,100,100,'张悦',0,to_date('1999-9-9','yyyy-mm-dd'));
--查询数据库
select * from t_score;
--修改数据
select * from t_score for update;
--查询出所用用户的生日和姓名
select stuname,to_char(stubir,'yyyy-mm-dd hh24:mi:ss day') from t_score ;
--查询生日和当前时间相差多长时间
select trunc(sysdate-stubir) from t_score;
select trunc(sysdate)-trunc(stubir) from t_score;
--To_number()转换为数值类型
--注意：在oracle中会自动将数值格式的字符串转换为数值
select '3'*210 from dual;
select '20'*'20' from dual;
select to_number('30')*200 from dual;
/*===================================日期函数===============================================*/
--Add_months 增加或减去月份
select add_months(sysdate,12) from dual;
select add_months(sysdate,1) from dual;
select add_months(sysdate,-2) from dual;
select add_months(sysdate,-12) from dual;
select add_months(stubir,2) from t_score;
--Last_day 返回日期所在月的最后一天
select last_day(sysdate) from dual;
select last_day(stubir) from t_score;
--Next_day(date,day)给出日期date和星期几之后计算下一个星期的日期
select next_day(sysdate,'星期六') from dual;
select next_day(sysdate,'星期一') from dual;
select next_day(stubir,'星期六') from t_score;
/*===================================其它函数===============================================*/
--nvl（列类型，列里面为空的指定值）将列类型中为null的值转换为指定的值
select * from t_score;
select nvl(math,100) from t_score;
select stuname,nvl(stuid,100) from t_score;
--nvl2（列类型，不为空的指定值，为空的指定值）将列类型中为null的值转换为指定的值
--第一个字段名
--第二个参数是指定非空值
--第三个参数是指定null的值
select stuname,nvl2(math,10,100) from t_score;
select stuname,nvl2(stuid,10,100) from t_score;
--decode【解码】（列类型，列类型的值，转换值，列类型的值，转换值）
select * from t_score for update;
--相当于if else if() else
select decode(stusex,0,'女',1,'男',2,'保密',3,'人妖',4,'咬人',5,'长老') from t_score;
select decode(stusex,0,'女') from t_score;
/*===================================分页===========================================*/
--给表起别名
--给字段起别名
select * from t_score;
select math h ,chinese c ,stuname s from t_score;
select math as h ,chinese as c ,stuname as s from t_score;
--给表其别名
--select 表的别名.字段名，表的别名.字段名 from 表名 表的别名
select ts.stuname,ts.math,ts.chinese,ts.english from t_score ts;
select ts.* from t_score ts;
--oracle中的伪列
--rowid,通过rowid可以快速定位一条记录，因为它里面存放是行的地址
--通过rowid可以对其数据进行解锁编辑。
select ts.rowid,ts.* from t_score ts;
--解锁修改
select * from t_score for update;
-- rownum是查询返回的结果集中行的序号，可以使用他无限制查询返回的行数
--注意：rownum只能和<或者<=连用，通过rownum起别名使其和>或者>=连用（嵌套查询）
-- 不能再rownum的前面加任何前缀
-- 如果想要使用列的别名时，则含有该别名的sql语句必须作为一个子查询（最内层值得是：对那些数据进行分页，所有的where条件，order by等都应该写到最内层）
select rownum ,ts.* from t_score ts;
select * from t_score where rownum <5;--对
select rownum,ts.* from t_score ts where rownum <=5;--对
select rownum,ts.* from t_score ts where rownum >5;--错
select rownum,ts.* from t_score ts where rownum >=5;--错
--rownum,通过rownum可以进行分页查询
--注意:rownum 只能和<或者<=号连用
select * from t_score where rownum <=10;
--通过给rownum起别名可以使其和>或者>=连用(嵌套查询)
select s.* from(select ts.* ,rownum rn from t_score ts)s where rn>5;
select s.* from(select ts.* ,rownum rn from t_score ts)s where rn>=5;
--把内层查询的结果当成临时表进行查询
select s.* from (select ts.*,rownum rn from t_score ts )s where rn>10
-- 分页
-- 最内层指的是 对哪些数据进行分页，所有的where条件，order by等都应该写在最内层。
-- 第二层 小于大的，即通过rownum<=结束范围
-- 最外层 大于小的，即通过rownum的别名rn>=开始范围
-- 不能在rownum的前面加任何前缀。
-- 如果想要使用某个列的别名，则含有该别名的sql语句必须作为子查询。
--每页5条数据查看第5页
select s.* from (select ts.* ,rownum rn from (select * from t_score)ts where rownum<=25)s where rn>=21;
--每页10条查看第二页
select s.* from (select ts.*,rownum rn from(select * from t_score ) ts where rownum<=20)s where rn>=11
--每页2条看第8页
select s.* from (select ts.* ,rownum rn from (select * from t_score) ts where rownum<=16)s where rn>=15;
--查看女生的信息分页展示每页2条查看第2页
select s.* from (select ts.* ,rownum rn from (select * from t_score where stusex=0)ts where rownum<=4)s where rn>=3;
--查看所有男生中姓张的信息分页展示每页3条查看第2页
select t.* from (select s.* ,rownum rn from (select * from t_score where stusex=1 and stuname like '张%')s where rownum <=6)t where rn>=4;
--查看数学成绩排序后的成绩每页6条查看第二页
select * from (select s.* , rownum rn from (select * from t_score order by math) s where rownum < =12)where rn>=7
--查看英语成绩不及格的学生信息分页展示每页5条查看第10页
select * from (select s.*,rownum rn from (select * from t_score where english<60)s where rownum<=50) where rn>=46
--查看语文成绩大于80分小于90分的学生信息并且是男生并且姓王的学生信息分页展示每页50条查看第20页
select * from (select s.* ,rownum rn from (select * from t_score where chinese>80 and chinese<90 and stusex=1 and stuname like '王%')s where rownum <=1000)where rn>=951;
--查看数学成绩大于平均成绩的学生信息分页展示每页10条查看20页
select tab.* from
( select ta.* rownum rn from
( select * from t_score where math>(select avg(math) from t_score))ta
where rownum<=200)tab
where rn>= 191
/*===================================备份表==================================================*/
--使用表中的数据和结构化来快速生成备份表
--create table 新表名 as select * from 旧表名
--备份数据和结构
create table t_score_bak as select * from t_score;
select * from t_score_bak;
select * from t_score;
--创建表结构
--create table 新表名 as select * from 旧表名 where 1=0
--备份表结构
create table t_score_bak2 as select * from t_score where 1=0;
select * from t_score_bak2;
--批量插入数据
--insert into 表名 select * from 有数据的表名
insert into t_score_bak2 select * from t_score_bak;
--删除所有数据
delete from t_score_bak2;
--批量插入指定字段
--insert into 表名 (字段名,字段名,字段名) select 字段名,字段名,字段名 from 有数据的表名
insert into t_score_bak2 (math,chinese,english,stuname) select math,chinese,english,stuname from t_score;
/*====================================表的主键外键============================================*/
--主键是一个不为空的唯一标示，一张表中只有一个主键，表的主键通常是ID，
--primary key主键
--在建立主键的同时会产生一个唯一索引（索引就相当于书的目录，可以提高查找的速度）
--外键
--foreign key 外键
--新建表
create table t_student(
stuid number(10) primary key ,--primary key主键
stuname varchar2(25),
stusex number(1), --unique 唯一
stubir date --foreign key 外键
)
/*========================================序列================================================*/
--序列就是为了产生唯一标识，通常情况下，一张表对应一个序列，多张表也可以同时使用一个序列
/*
create sequence seq_表名_id名
minvalue 1--最小值
maxvalue 999999999 --最大值
start with 1 --从哪开始
increment by 1 --每次递增多少
nocache --不缓存
order -- 排序
*/
create sequence seq_t_student_stuid
minvalue 1 --最小值
maxvalue 9999999999 --最大值
start with 1 --从哪开始
increment by 1 --每次递增1
nocache --不缓存
order --排序
--增加数据
insert into t_student values(seq_t_student_stuid.nextval,'王泽凯',0,to_date('1111-11-11','yyyy-mm-dd'));
insert into t_student values(seq_t_student_stuid.nextval,'王泽凯1',1,to_date('1111-11-11','yyyy-mm-dd'));
insert into t_student values(seq_t_student_stuid.nextval,'王泽凯2',0,to_date('1111-11-11','yyyy-mm-dd'));
insert into t_student values(seq_t_student_stuid.nextval,'王泽凯3',1,to_date('1111-11-11','yyyy-mm-dd'));
insert into t_student values(seq_t_student_stuid.nextval,'王泽凯4',0,to_date('1111-11-11','yyyy-mm-dd'));
commit;
--序列名称.nextva每一次递增一个
select * from t_student;
--删除数据
--当删除数据后id从序列开始的地方增加 不会重复出现已经出现的值
delete from t_student;
--想知道序列如今增加到哪了
select seq_t_student_stuid.currval from dual;
/*========================================表与表之间的关系======================================================*/
--一对一
--学生和班级 一个学生对应一个班级
--一对多
--一个班级对应多个学生
--多对多
--爱好和学生 多个爱好对应多个学生
--学生和班级表 学生表和班级表都有主键 外键加在多的那个表里 学生表的外键等于班级表的主键
drop table t_student;
--新建学生表
create table t_student(
stuid number(10) primary key,--主键
stuname varchar2(25),
stusex number(1),
stubir date,
classid number(10) --班级id 外键
)
select * from t_student;
--增加数据
insert into t_student values(1,'张悦',0,to_date('1998-09-09','yyyy-mm-dd'),1);
insert into t_student values(2,'张宇',1,to_date('1998-09-09','yyyy-mm-dd'),1);
insert into t_student values(3,'张非',0,to_date('1998-09-09','yyyy-mm-dd'),2);
insert into t_student values(4,'马俊',1,to_date('1998-09-09','yyyy-mm-dd'),2);
insert into t_student values(5,'于新帅',0,to_date('1998-09-09','yyyy-mm-dd'),3);
insert into t_student values(6,'徐成燕',1,to_date('1998-09-09','yyyy-mm-dd'),3);
insert into t_student values(7,'王跃军',0,to_date('1998-09-09','yyyy-mm-dd'),3);
insert into t_student values(8,'','','','');
commit;
--创建班级表
create table t_class(
classid number(10) primary key, --主键
classname varchar2(25)
)
insert into t_class values(1,'1608B');
insert into t_class values(2,'1607B');
insert into t_class values(3,'1606B');
insert into t_class values(4,'');
commit;
------------------------------------------------------------------------------------
/*==============================================================*/
--inner join 内连接 在连接中会展示所有表中不为空的信息
select * from t_student ts inner join t_class tc on ts.classid=tc.classid ;
--查询班级中女生的信息
select ts.* from t_student ts inner join t_class tc on ts.classid=tc.classid where ts.stusex=0;
--统计班级中女生的人数
select count(*) from t_student ts inner join t_class tc on ts.classid=tc.classid where ts.stusex=0;
--left join 左联接 在连接中把相对于左边的表的内容全部展示出来空的也展示
select * from t_student ts left join t_class tc on ts.classid=tc.classid ;
select * from t_class ts left join t_student tc on ts.classid=tc.classid ;
--right join 右连接 在连接中把相对于右边的表的内容全部展示出来空的也展示
select * from t_student ts right join t_class tc on ts.classid=tc.classid;
select * from t_class ts right join t_student tc on ts.classid=tc.classid;
--full join 全连接 在连接中把所有表的内容全部展示出来空的也展示
select * from t_student ts full join t_class tc on ts.classid=tc.classid;
--查询每个班级的人数
select count(*) from t_student ts inner join t_class tc on ts.classid=tc.classid group by tc.classname
--查询每个班级中女生的人数
select count(*)from t_student ts inner join
t_class tc on ts.classid=tc.classid where ts.stusex=0 group by tc.classname ;
--查询每个班级中生日大于1998-01-01的人数
select count(*) from t_student ts inner join t_class tc on ts.classid=tc.classid
where ts.stubir<to_date('1998-11-11','yyyy-mm-dd') group by tc.classname;
--查询每个班级中姓名含有王字的学生个数
select tc.classid,count(*) from t_student ts inner join
t_class tc on ts.classid=tc.classid where ts.stuname like '%王%' group by tc.classid;
--查询每个班级中不含有张字的学生信息
select ts.* from t_student ts left join t_class tc on ts.classid=tc.classid
where ts.stuname not like '%张%' ;
--查询出每个班级的名称和人数
select tc.classname,count(*) from t_student ts inner join t_class tc on ts.classid=tc.classid group by tc.classname;
