笺注:这是在 Navicat连接Oracle11gR2 的基础上进行的。
新建表STU_01:
自定义表名:
保存成功后的效果:
- Oracle的字段类型:
- NUMBER(p,s) 数值型 p指的是有效位数(最大为38) s指的是小数位数(会自动进行四舍五入) 整数位数为p-s ,正负都可以的; number(p)=number(p,0),声明的是一个整数,小数部分会被自动去除;
- CHAR 字符型 可以存储2000个任意字符,一般用于存储固定长度的字符串;每个汉字是按照3个字符来存放的,每个字母则是一个字符;
- VARCHAR2 字符型 可以存储4000个任意字符,一般用于存储可变长度的字符串;每个汉字是按照3个字符来存放的,每个字母则是一个字符;
- CLOB 字符型 可以存储4000个任意字符,不用指定大小;
- DATE 日期时间型 格式为 yyyy-mm-dd hh:mm:ss
复制代码
新建查询:
往表STU_01一次性插入多条记录:
INSERT into STU_01 (ID,NAME,AGE,GRADE,ADDRESS,MATH,CHINESE,SHIJIAN) values (1,'小明',15,'三年二班','广东省,广州市',60.5,70.5,to_timestamp('2020-10-5 19:3:15','yyyy-mm-ddhh24:mi:ss'));
INSERT into STU_01 (ID,NAME,AGE,GRADE,ADDRESS,MATH,CHINESE,SHIJIAN) values (2,'李大杰',16,'三年二班','广东省,佛山市',66,78,to_timestamp('2020-10-06 10:23:15','yyyy-mm-ddhh24:mi:ss'));
INSERT into STU_01 (ID,NAME,AGE,GRADE,ADDRESS,MATH,CHINESE,SHIJIAN) values (3,'Zhuohua',18,'三年十二班','四川省,成都市',100,99,to_timestamp('2020-8-06 8:11:26','yyyy-mm-ddhh24:mi:ss'));
输出表STU_01中的所有记录:
SELECT * FROM STU_01;
只输出表STU_01中的前面两条记录:
SELECT * FROM (SELECT * FROM STU_01 order by ID ) WHERE ROWNUM <= 2;
只输出表STU_01中的前面三十条记录:
SELECT * FROM (SELECT * FROM STU_01 order by ID ) WHERE ROWNUM <= 30;
查询中可以只显示某几个字段:
SELECT NAME,GRADE,CHINESE,SHIJIAN FROM STU_01;
查询结果中更改字段的标题:(标题实际上没有更改)
SELECT NAME as 姓名,GRADE as 班级 FROM STU_01;
查询结果中更改字段的内容:(数据实际上没有更改)
SELECT NAME,GRADE,'语文成绩优秀' AS CHINESE FROM STU_01 WHERE CHINESE >= 78;
查询结果中更改字段的标题、内容:
SELECT NAME as 姓名,GRADE as 班级,'语文成绩优秀' AS CHINESE FROM STU_01 WHERE CHINESE >= 78;
注释:在这种情况下,不可以再更改字段CHINESE的标题了。
排序:
SELECT NAME,GRADE,CHINESE,SHIJIAN FROM STU_01 order by CHINESE;
注释: order by 排序,默认是升序,从小到大。
SELECT NAME,GRADE,CHINESE,SHIJIAN FROM STU_01 order by CHINESE desc;
注释: order by 排序; desc 降序,从大到小。
按日期进行排序:(字段类型DATE也是可以排序的)
SELECT NAME,GRADE,CHINESE,SHIJIAN FROM STU_01 order by to_char(SHIJIAN,'yyyy-mm-dd');
SELECT NAME,GRADE,CHINESE,SHIJIAN FROM STU_01 order by to_char(SHIJIAN,'yyyy-mm-dd') desc;
SELECT NAME,GRADE,CHINESE,to_char(SHIJIAN,'yyyy-mm-dd') as 日期部分 FROM STU_01 order by to_char(SHIJIAN,'yyyy-mm-dd') desc;
SELECT NAME,GRADE,SHIJIAN,to_char(SHIJIAN,'hh24') as 时,to_char(SHIJIAN,'mi') as 分, to_char(SHIJIAN,'ss') as 秒 FROM STU_01;
SELECT NAME,GRADE,SHIJIAN,to_char(SHIJIAN,'hh24') as 时,to_char(SHIJIAN,'mi') as 分, to_char(SHIJIAN,'ss') as 秒 FROM STU_01 order by ID DESC;
SELECT NAME,GRADE,SHIJIAN,to_char(SHIJIAN,'hh24') as 时,to_char(SHIJIAN,'mi') as 分, to_char(SHIJIAN,'ss') as 秒 FROM STU_01 order by to_char(SHIJIAN,'hh24') DESC;
按日期范围进行查询:
SELECT NAME,GRADE,CHINESE,SHIJIAN FROM STU_01 where to_char(SHIJIAN,'yyyy-mm-dd') > '2020-10-05';
SELECT NAME,GRADE,CHINESE,SHIJIAN FROM STU_01 where to_char(SHIJIAN,'yyyy-mm-dd') >= '2020-10-05';
SELECT NAME,GRADE,CHINESE,SHIJIAN FROM STU_01 where to_char(SHIJIAN,'yyyy-mm-dd') between '2020-10-05' and '2020-10-06';
等于的写法:
SELECT NAME,AGE,ADDRESS,SHIJIAN FROM STU_01 where NAME = '小明';
注释:字符型 要使用英文单引号。
查询时,关键字区分英文字母大小写:
SELECT NAME,AGE,ADDRESS,SHIJIAN FROM STU_01 where NAME = 'zhuohua';
SELECT NAME,AGE,ADDRESS,SHIJIAN FROM STU_01 where NAME = 'Zhuohua';
SELECT NAME,AGE,ADDRESS,SHIJIAN FROM STU_01 where to_char(SHIJIAN,'yyyy-mm-dd') = '2020-10-05';
注释:日期时间型 要使用英文单引号。
SELECT NAME,AGE,ADDRESS,SHIJIAN FROM STU_01 where NAME = 'Zhuohua' or AGE = 15;
注释:
or 或者
数值型不要使用英文单引号。
SELECT NAME,AGE,ADDRESS,SHIJIAN FROM STU_01 where AGE in (15,18);
注释:in 多个等于
SELECT NAME,AGE,ADDRESS,GRADE FROM STU_01 where GRADE = '三年二班' and AGE in (15,18);
注释:and 而且
不等于的写法:
SELECT NAME,ADDRESS,GRADE FROM STU_01 where GRADE != '三年二班';
SELECT NAME,ADDRESS,GRADE FROM STU_01 where GRADE <> '三年二班';
SELECT NAME,AGE,ADDRESS,SHIJIAN FROM STU_01 where AGE not in (15,18);
注释:not in 多个不等于
数值大小比较的写法:(大于)
SELECT NAME,GRADE,CHINESE FROM STU_01 where CHINESE > 70.5;
数值大小比较的写法:(大于或等于)
SELECT NAME,GRADE,CHINESE FROM STU_01 where CHINESE >= 70.5;
数值大小比较的写法:(小于)
SELECT NAME,GRADE,CHINESE FROM STU_01 where CHINESE < 78;
数值大小比较的写法:(小于或等于)
SELECT NAME,GRADE FROM STU_01 where CHINESE <= 78;
数值大小比较的写法:(在某个范围之间)
SELECT NAME,GRADE,CHINESE FROM STU_01 where CHINESE between 70 and 78;
LIKE语句:
SELECT NAME,GRADE,CHINESE FROM STU_01 where NAME like '李大_';
注释:一个下划线代表一个字符。
SELECT NAME,GRADE,CHINESE FROM STU_01 where NAME LIKE '李__';
注释:两个下划线代表两个字符,以此类推。
SELECT NAME,GRADE,CHINESE FROM STU_01 where NAME LIKE 'Zhuoh__';
SELECT NAME,GRADE,CHINESE FROM STU_01 where NAME like '李%';
注释: % 代表任意多个字符。
SELECT NAME,GRADE,CHINESE FROM STU_01 where NAME like 'Zh%';
查询中使用加法:(数据实际上没有更改)
SELECT NAME,CHINESE,MATH,CHINESE+MATH AS 总成绩 FROM STU_01;
SELECT NAME,CHINESE,MATH,CHINESE+MATH AS 总成绩 FROM STU_01 order by CHINESE+MATH desc;
查询中使用减法:(数据实际上没有更改)
SELECT NAME,CHINESE,MATH,CHINESE-MATH AS 新值 FROM STU_01;
查询中使用乘法:(数据实际上没有更改)
SELECT NAME,CHINESE,MATH,CHINESE * MATH AS 新值 FROM STU_01;
查询中使用除法:(数据实际上没有更改)
SELECT NAME,CHINESE,MATH,CHINESE / MATH AS 新值 FROM STU_01;
查询中使用加法与乘法:(数据实际上没有更改)
SELECT NAME,CHINESE,MATH,CHINESE + MATH * 2 AS 新值 FROM STU_01;
查询中使用加法与乘法:(加上小括号)
SELECT NAME,CHINESE,MATH,(CHINESE + MATH) * 2 AS 新值 FROM STU_01;
可以在小括号里再使用小括号:
SELECT NAME,CHINESE,MATH,((CHINESE + MATH) * 2) + 1 AS 新值 FROM STU_01;
聚合函数有5个,分别是COUNT()、SUM()、AVG()、MAX()、MIN()
求数量:
SELECT COUNT(*) AS 记录数量 FROM STU_01;
求和:
SELECT SUM(MATH) AS 数学总成绩 FROM STU_01;
求平均值:
SELECT AVG(MATH) AS 数学平均成绩 FROM STU_01;
求最大值:
SELECT MAX(MATH) AS 数学最高成绩 FROM STU_01;
求最小值:
SELECT MIN(MATH) AS 数学最低成绩 FROM STU_01;
按字段GRADE进行分组后,再求字段MATH的最大值:
SELECT GRADE,MAX(MATH) AS 各班级的数学最高成绩 FROM STU_01 GROUP BY GRADE;
按字段GRADE进行分组后,再求字段MATH的最大值,最后以字段MATH的最大值进行排序:
SELECT GRADE,MAX(MATH) AS 各班级的数学最高成绩 FROM STU_01 GROUP BY GRADE order by MAX(MATH) desc;
分组再加条件:( group by + having )
SELECT GRADE as 班级,MAX(MATH) AS 各班级的数学最高成绩 FROM STU_01 GROUP BY GRADE having MAX(MATH) >= 60;
分组再加条件:( group by + having )
SELECT GRADE as 班级,MAX(MATH) AS 各班级的数学最高成绩 FROM STU_01 GROUP BY GRADE having MAX(MATH) >= 90;
分组再加条件:( group by + having )
SELECT GRADE as 班级,MAX(MATH) AS 数学最高成绩 FROM STU_01 GROUP BY GRADE having GRADE = '三年二班';
更改记录:(假如匹配条件,会一次性更改多条记录)
UPDATE STU_01 SET GRADE = '三年十一班' WHERE Name = 'Zhuohua';
SELECT NAME,GRADE FROM STU_01;
更改记录:(可以一次性更改同一条记录中的多个字段的内容)
UPDATE STU_01 SET AGE =17,SHIJIAN = to_timestamp('2021-10-05 19:13:15','yyyy-mm-ddhh24:mi:ss') WHERE Name = '小明';
SELECT NAME,AGE,SHIJIAN FROM STU_01;
给字段的值加大:(数据实际上更改了)
UPDATE STU_01 SET MATH = MATH + 20 WHERE Name = '小明';
给字段的值减小:(数据实际上更改了)
UPDATE STU_01 SET MATH = MATH - 20.5 WHERE Name = '小明';
SELECT NAME,MATH FROM STU_01;
可以使用乘法:(数据实际上更改了)
UPDATE STU_01 SET MATH = MATH * 2 WHERE NAME = '小明';
SELECT NAME,MATH FROM STU_01;
也可以使用除法:(数据实际上更改了)
UPDATE STU_01 SET MATH = MATH / 2 WHERE NAME = '小明';
SELECT NAME,MATH FROM STU_01;
删除记录:(假如匹配条件,会一次性删除多条记录)
DELETE from STU_01 WHERE ADDRESS LIKE '广东省%';
SELECT * FROM STU_01;
删除记录:(假如匹配条件,会一次性删除多条记录)
DELETE from STU_01 WHERE MATH like '10%';
SELECT * FROM STU_01;
相关文章:
MySQL的SQL语句
Python3脚本管理Oracle11gR2 |