sql语句学习总结

/*=================表操作===================*/

--创建用户设置密码

create user tianhp identified by 237219;

--授权

grant connect,resource to tianhp;

--创建表

create table t_student

(

studentName varchar2(100),

studentAge number(2),

birthday date

);

--查看表结构,在command window中输入 desc 表名

--查看表数据

select * from t_student; 

--查看指定列的数据

select studentage,studentname from t_student;

--更改表的名字

--rename 表名 to 新表名

rename t_stu to t_student;

--删除表中的列

--alter table 表名 drop column 列名;

alter table t_student drop column birthday;

--增加表中的列

--alter table 表名 add 列名 列的数据类型(类型的长度)

alter table t_student add birthday date;

alter table t_student add address varchar2(100);

alter table t_student add yuwen number(3);

alter table t_student add shuxue number(3);

alter table t_student add yingyu number(3);

--修改表中列的名字

--alter table 表名 rename column 列名 to 新列名

alter table t_student rename column address to addr;

--修改表中类的类型

--alter table 表名 modify 列名 新类型

alter table t_student modify addr varchar2(500);

--删除表

--drop table 表名;

drop table t_student;

/*======================表操作结束==============================*/

/*======================select的各种操作==========================*/

--查看所有表的名称

select table_name from user_tables;

--查询所有字段可以使用*

select * from t_student;

--列是可以参与运算(+,-,*,/) 

--多个列之间用逗号分隔

--计算每名同学的平均成绩

select studentname,(yuwen+shuxue+yingyu)/3 from t_student;

--给列(字段)起别名

--1.通过空格起别名

select studentname,(yuwen+shuxue+yingyu)/3 avgscore from t_student;

--2.通过as关键字起别名 

select studentname,(yuwen+shuxue+yingyu)/3 as avgscore from t_student;

--3.如果别名中含有特殊字符 如空格，则需要将别名用双引号引起来

select studentname,(yuwen+shuxue+yingyu)/3 as "avg score" from t_student;

--oracle中通过||将字段(变量)和字符串进行拼接

--oracle中的字符串用单引号括起来,别名用双引号括起来

--姓名:张三;平均成绩:69

select '姓名:'||studentname||';'||'平均成绩:'||(yuwen+shuxue+yingyu)/3 as 学生的平均成绩 from t_student;

--通过distinct关键字进行去重操作 

--注意:只有当distinct关键字后跟的所有的字段值都一致的情况下才算做是重复数据。

select distinct studentname from t_student;

select distinct studentname,studentage from t_student;

--通过where关键字限制返回符合条件的记录,即where后跟上限制条件

--查询学生名为小李的学生的信息

select * from t_student where studentname = '小李';

--查询性别为男的所有学生的信息 

select * from t_student where sex=1;

--查询语文成绩不及格的学生的信息

--数值类型的列进行比较(>,<,=,>=,<=,<>,!=)

select * from t_student where yuwen < 60;

--年龄不等于20岁的学生的信息

select * from t_student where studentage != 20;

select * from t_student where studentage <> 20;

--字符串类型的列进行比较(=)

--查询姓名为小李的的语文，数学成绩，以及各科成绩的平均值

select yuwen,shuxue,(yuwen+shuxue+yingyu)/3 as pingjunchengji from t_student where studentname='小李';

--查询语文成绩及格的所有学生的姓名以及年龄

select studentname,studentage from t_student where yuwen >=60;

--查询平均成绩大于80分的所有学生的姓名以及年龄

select studentname,studentage,(yuwen+shuxue+yingyu)/3 as pingjunchengji from t_student where (yuwen+shuxue+yingyu)/3 > 80;

--注意:oracle中的逻辑运算符 并且:and  或者:or  非:not

--and求的交集，and越多，限制条件就越多，求出的结果就越少

--or求的并集，or越多，限制条件越宽松，求出的结果越多

--查询班级中语文成绩不及格的男生的信息

select * from t_student where yuwen < 60 and sex=1;

--查询班级中年龄大于28岁或者性别为男的学生的信息

select * from t_student where studentage > 28 or sex = 1;

--查询班级中语文成绩不及格的所有未成年的女生

select * from t_student where yuwen < 60 and studentage < 18 and sex=0;

--在oracle中通过sysdate关键字可以得到系统的当前时间

--如果想看到只有一个字段的结果时，可以借助dual这张表

select sysdate from dual;

--查询语文成绩在60-80之间的所有学生的信息 

select * from t_student where yuwen>=60 and yuwen<=90;

--通过between...and...来求两个数值之间的结果,包含这两个数值

select * from t_student where yuwen between 50 and 90;

--求语文成绩不在50到90之间的所有的学生信息,使用 not between...and...

select * from t_student where yuwen not between 50 and 90;

--查询小张或者小李的信息

select * from t_student where studentname='小张' or studentname='小李';

--通过in(...)来查询信息

select * from t_student where studentname in ('小张', '小李');

--通过not in(...)来查询信息

--查询除了小张和小李以外所有其他同学的信息

select * from t_student where studentname not in ('小张','小李');

--通过like进行模糊匹配 %:代表任意多个字符(0个或多个)，_:代表任意一个字符(必须有1个)

--查询所有姓张的学生的信息

select * from t_student where studentname like '张%';

select * from t_student where studentname like '张__';

--查询所有包含张的学生的信息

select * from t_student where studentname like '%张%';

--not like ...

select * from t_student where studentname not like '%张%';

--通过is null查找为空的信息

--查找语文成绩为空的学生的信息

select * from t_student where yuwen is null;

--查找语文成绩不为空的学生的信息

select * from t_student where yuwen is not null;

--按照年龄从低到高对学生进行排序；

select * from t_students order by studentsage;

--按照年龄从高到低对学生进行排序；desc

select * from t_students order by studentsage desc;

--order by 后可以跟多个字段进行排序，如：order by，studentage，shuxue；

--先按照年龄进行升序排序，如果年龄相同，再按照数学成绩。

select * from t_students order by studentsage,shuxue;

————————————————————————————————————————————————————————————————————————————————————————————————————————————————————--

--order聚合函数只会返回一个值；

--max(),语文最高分；

select max(yuwen) from t_students;

--min ()年龄最小；

select min(yuwen) from t_students;

--count()学生的总人数；

select count(*) from t_students;

select count(1) from t_students;

select count(studentsname) from t_students;

--count求总个数不会求计算该列中为空的那条记录；

--sum()求语文成绩的总分数；

select sum(yuwen)from t_students;

--avg(),求数学成绩的平均分。

select avg(shuxue)from t_students;

--语文分数成绩最高的那个人信息（嵌套子查询，会先执行子查询）；

select * from t_students where yuwen=(select max(yuwen)from t_students);

--年龄最小的同学的姓名及其平均年龄；

select studentsname,(yuwen+shuxue+yingyu)/3 as avgscore from t_students where studentsage = (select min(studentsage) from t_students);

--分组函数 group by(在oracle中进行group by 分组时，应将先查询字段所有非聚合函数都写在group by 的后面)；

--求男女生的各自人数

select studentssex,count(*) from t_students group by studentssex;

--每个年龄段各自的人数；

select studentsage,count(*) from t_students group by studentsage;

--求男女生各自语文成绩的最大值；

select studentssex,max(yuwen) from t_students group by studentssex;

--通过在group by后 加having 关键字，来对分组后的结果进行过滤；

--求每个年龄段里面人数大于一个年龄信息；

select studentsage,count(*) from t_students group by studentsage having count(*)>1;

--求英语成绩小于90的学生中，男女的比例

select studentssex,(count(*)/(select count(*)from t_students where yingyu < 90)) * 100||'%'as 百分比 from t_students where yingyu <90 group by studentssex;

/*=================数字函数===================*/

--数字函数；

--abs（）求绝对值；

select abs(89.8) from dual; 

--ceil向上取整；

select ceil(78.21) from dual;

select ceil(78.61) from dual;

select ceil(78.01) from dual;

--floor（）向下取整；

select floor(78.81) from dual;

--round()四舍五入；

--可以有第二个参数，参数也可以为负，表示小数点左边制定位数后面的部分均以0计，进行四舍五入；

select round(78.56)from dual;

select round(78.16)from dual;

select round(78.56,2)from dual;

select round(78.56,1)from dual;

select round(78.56,-1)from dual;

select round(178.56,-2)from dual;

--trunc（）截取；第二个参数也可以是负数，对小数点进行截取；

--截取只只求整数，加参数另算；

select trunc(45.69)from dual;

select trunc(45.69,1)from dual;

select trunc(245.69,-2)from dual;

select trunc(2245.69,-3)from dual;

select trunc(29245.69,-4)from dual;

/*=================字符串函数===================*/

--concat()连接两个字符串，（1,2）等同于//的效果；

select concat('姓名','张三')from dual;

select concat(concat('姓名',studentsname),'张三'),concat(concat('年龄',studentsage),99) from dual;

create table t_students_bak

as

select *from t_students;

select *from t_students_bak;

create table t_students_bak_1

as

select *from t_students where studentssex=1; 

select *from t_students_bak_1;

--设置一个主键；

create table t_teachers

(

id number(6)primary key,

teachersname varchar2(100),

teachersage number(2)

)

--设置一个序列；

create sequence seq_teachers_id

minvalue 1

maxvalue 99999999999

start with 1

increment by 1

cache 100

order;

--班级表

create table t_classes

(

id number(6) primary key,

classesName varchar2(100)

)

--学生表

create table t_student

(

id number(6) primary key,

studentName varchar2(100),

classesId number(6)

)

--连接表

select s.id,s.studentname,c.classesname from t_student s inner join t_classes c on s.classesid =c.id;

--多表联查

create table t_teachers

(

id number(6) primary key,

teachersName varchar2(100)

);

create table t_course

(

id number(6) primary key,

courseName varchar2(100),

teachersId number(6)

);

insert into t_teachers values (1, '李天');

insert into t_teachers values (2, '张小鱼');

insert into t_teachers values (3, '李浩');

insert into t_teachers values (4, '李强');

commit;

insert into t_course values (1, '语文', 1);

insert into t_course values (2, '数学', 1);

insert into t_course values (3, '英语', 2);

insert into t_course values (4, 'java', 3);

insert into t_course values (5, 'html', 4);

commit;

select * from t_teachers;

select * from t_course;

--查询没门代课老师

select t.teachersname,c.coursename from t_teachers t inner join t_course c on c.teachersid =t.id;

--查询李老师代课的所有信息

select t.teachersname,c.coursename from t_teachers t inner join t_course c on c.teachersid =t.id where t.teachersname='李天';

--查询姓李的老师代课的信息；

select * from t_teachers t inner join t_course c on c.teachersid=t.id where t.teachersname like '李%';

--查询每个老师代课的数量；

select teachersname,count(*) from t_teachers t inner join t_course c on c.teachersid=t.id group by teachersname;

--查询下姓李老师带的课程量；

select teachersname,count(*) from t_teachers t inner join t_course c on c.teachersid=t.id where t.teachersname like '李%' group by teachersname;

--查询姓李的老师的数量大于1的；

select teachersname,count(*) from t_teachers t inner join t_course c on c.teachersid=t.id where t.teachersname like '李%' group by teachersname

having count(*)>1;