oracle+bpm10.3,一、初识数据库 - osc_91760i21的个人空间 - OSCHINA - 中文开源技术交流社区...

阳阙

于 2021-04-14 06:19:09 发布

阅读量170

点赞数

文章标签： oracle+bpm10.3

1 --创建数据库

2 create databasetest;3

4 --查看创建好的数据库

5 show create databasetest;6

7 --查看所有数据库列表

8 show databases;9

10 --使用数据库

11 usetest;12

13 --删除数据库

14 drop databasetest;15

16 --创建员工信息表

17 create tableemp(18 depid char(3),19 depname varchar(20),20 peoplecount int

21 );22

23 --查看表是否创建成功

24 show tables;25

26 --删除数据表

27 drop tableemp;28

29 --创建带约束条件的emp表

30 create tableemp(31 depid char(3) primary key,32 depname varchar(20) not null default '-',33 peoplecount int unique

34 );35

36 descemp;37

38 --创建含各种约束条件的数据表

39 CREATE TABLE example(id INT PRIMARY KEY AUTO_INCREMENT, --创建整数型自增主键

40 lname VARCHAR(4) NOT NULL, --创建非空字符串字段

41 math INT DEFAULT 0, --创建默认值为0的整数型字段

42 minmax FLOAT UNIQUE --创建唯一约束小数型字段

43 );44 descfruits;45 --创建fruits数据表

46 create tablefruits(47 f_id char(10) not null,48 s_id int not null,49 f_name varchar(255) not null,50 f_price decimal(8,2) not null,51 primary key(f_id)52 );53

54 --插入数据

55 insert intofruits(f_id,s_id,f_name,f_price)56 values('a1',101,'apple',5.2),57 ('b1',101,'blackberry',10.2),58 ('bs1',102,'orange',11.2),59 ('bs2',105,'melon',8.2),60 ('t1',102,'banana',10.3),61 ('t2',102,'grape',5.3),62 ('o2',103,'coconut',9.2),63 ('c0',101,'cherry',3.2),64 ('a2',103,'apricot',25.2),65 ('l2',104,'lemon',6.4),66 ('b2',104,'berry',7.6),67 ('m1',106,'mango',15.6),68 ('m2',105,'xbabay',2.6),69 ('t4',107,'xbababa',3.6),70 ('b5',107,'xxxx',3.6);71

72 select * fromfruits;73

74 --创建大气质量表

75 create tableMonthly_Indicator(76 city_name varchar(20) not null,77 month_key date not null,78 aqi int(4) not null default 0,79 aqi_range varchar(20) not null default '-',80 air_quality varchar(20) not null default '-',81 pm25 float(6,2) not null default 0,82 pm10 float(6,2) not null default 0,83 so2 float(6,2) not null default 0,84 co float(6,2) not null default 0,85 no2 float(6,2) not null default 0,86 o3 float(6,2) not null default 0,87 ranking int(4) not null default 0,88 primary key(city_name,month_key)89 );90 descmonthly_indicator;91 --为Monthly_Indicator表导入外部txt文件

92 load data local infile 'D:/data/all.txt'

93 into tableMonthly_Indicator94 fields terminated by '\t'

95 ignore 1lines;96

97 --检查倒入内容Monthly_Indicator

98 Select * fromMonthly_Indicator;99

100 --检查导入数据总行数Monthly_Indicator

101 Select count(*) fromMonthly_Indicator;102

103 --检查表结构

104 DescMonthly_Indicator;105

106 --更改表名

107 alter tableemp rename empdep;108

109 --更改字段数据类型

110 alter table empdep modify depname varchar(30);111

112 --查看表结构

113 descempdep;114

115 --更改字段名称

116 alter table empdep change depname dep varchar(30);117

118 --更改字段名称及字段数据类型

119 alter table empdep change dep depname varchar(20);120

121 --为表添加新字段

122 alter table empdep add maname varchar(10) not null;123

124 --将字段顺序改为第一位

125 alter table empdep modify maname varchar(10) not nullfirst;126

127 --将字段顺序改为另一个字段之后

128 alter table empdep modify maname varchar(10) after depid;129

130 --删除字段

131 alter table empdep dropmaname;132

133 --删除主键

134 alter table empdep drop primary key;135

136 --增加主键

137 alter table empdep add primary key(city_name,month_key);138

139 --查看表结构

140 descempdep;141

142 --SQL语句查询

143 --查询大气质量表中的全部内容

144 select * frommonthly_indicator;145

146 --查询北京的大气质量数据

147 select * frommonthly_indicator148 where city_name = '北京';149

150 --查询不同月份PM2.5的最大值

151 select month_key, max(pm25) frommonthly_indicator152 group bymonth_key;153

154 --降序查询不同城市PM10的平均值

155 select city_name, avg(pm10) frommonthly_indicator156 group bycity_name157 order by avg(pm10) ;158

159 --对大气质量表进行有选择的查询

160 select city_name, avg(pm25), avg(pm10) fromMonthly_Indicator161 where pm25 > 50

162 group by city_name, month_key having city_name <> '北京'

163 order by avg(pm25) desc;164 /*

165 165 以上程序是这样读的，从数据表Monthly_Indicator中，先找出pm25>50的所有记录，166 166 然后再对这些记录按照【city_name, month_key having city_name <> '北京'】167 167 不同的city_name，不同的month_key进行分组，同时取city_name 不是 '北京'的，168 168 也就是删除city_name是北京的记录。在得到以上结果后，【elect city_name, avg(pm25), avg(pm10)】选出不同的city_name下，169 pm25的平均值，pm10的平均值，最后【order by avg(pm25) desc】，以降序的顺序排列。170 */

171

172

173 select city_name, pm25, pm10 fromMonthly_Indicator174 where pm25 > 50 and city_name <> '北京'

175 order by pm25 desc;176

177 --链接练习

178 create tablet1(179 key1 varchar(20),180 v1 int(4)181 );182

183 load data local infile 'D:/data/t1.csv'

184 into tablet1185 fields terminated by ','

186 ignore 1lines;187

188 create tablet2(189 key2 varchar(20),190 v2 int(4)191 );192

193 load data local infile 'D:/data/t2.csv'

194 into tablet2195 fields terminated by ','

196 ignore 1lines;197

198 select * fromt1;199 select * fromt2;200

201 select * from t1 left join t2 on t1.key1 = t2.key2; --左链接

202 /*

203 以上程序是这个意思，【t1 left join t2 on t1.key1 = t2.key2】意思是以表t1作为主表，用左连接的方式连接表 t2，204 on 后面跟关键字段，分别是表t1中的key1 表t2中的key2, [select *]表示选取全部结果205 其实其功能就相当于power query当中的表的合并查询，方式就是左反。下同206 */

207

208 select t1.*, t2.* from t1 right join t2 on t1.key1 = t2.key2; --右链接

209 select t1.*, t2.* from t1 inner join t2 on t1.key1 = t2.key2; --内链接

210

211

212 /*

213 以上的方式统称横向合并，注意当中字段的对应关系214 多对多(不符合业务逻辑)215 多对一(最常用，就是选取其中一个表的主键(非空且唯一)作为关键字段，另一个表选取非主键作为关键字段)216 一对一(不符合数据库设计者的初衷，如果两个关键字段都是主键，就没有必要这样做了)217 */

218

219

220 --以下是纵向合并，类似于power query中的追加查询

221

222 --用union合并t1与t2表

223 --union: 用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行

224 select t1.* fromt1225 union

226 select t2.* fromt2;227

228

229 --用union all合并t1与t2表

230 --用于合并两个或多个 SELECT 语句的结果集，保留重复行

231 select t1.* fromt1232 union all

233 select t2.* fromt2;234

235 -------------------------------------------------------------------------------------------------------------------------

236 --查询操作符

237 --用and操作符查询s_id为101并且f_id为a1的水果记录

238 select * fromfruits239 where s_id = 101 and f_id = 'a1';240

241

242 --用or操作符查询苹果或者橙子的相关记录

243 select * fromfruits244 where f_name = 'apple' or f_name = 'orange';245

246

247 --用in操作符查询苹果和橙子的相关记录

248 select * fromfruits249 where f_name in('apple','orange');250

251

252 --用not in操作符查询苹果和橙子之外的水果的相关记录

253 select * fromfruits254 where f_name not in('apple','orange');255

256

257 --用between...and操作符查询f_price在10元到20元之间的水果记录

258 select * fromfruits259 where f_price between 10 and 20;260

261

262 --用like操作符查询所有f_name由a开始的水果记录，%表示不限字符数，是通配符

263 select * fromfruits264 where f_name like 'a%';265

266 --用like操作符查询所有f_name中含有a的，不限定a的位置

267 select * fromfruits268 where f_name like '%a%';269

270 --用like操作符查询所有f_id由b开始且字符长度为两位的水果记录

271 select * fromfruits272 where f_id like 'b_';273

274 --用is null操作符查询所有f_name为空的水果记录

275 select * fromfruits276 where f_name is null;277

278 --查询fruits表中所有不重复的s_id

279 select distinct s_id fromfruits;280

281 -------------------------------------------------------------------------------------------------------------------------

282 --子查询：写在()中，把内层查询结果当做外层查询参照的数据表来用

283

284

285 --用in操作符与子查询语句来查询所有f_id对应的f_price在10元到20元之间的水果记录

286 select * from fruits where f_id in

287 (select f_id from fruits where f_price between 10 and 20);288

289 --用all操作符与子查询语句来查询所有f_price大于20元的水果记录

290 select * from fruits where f_price > all

291 (select f_price from fruits where f_price <=20);292 /*

293 以上程序的解读：【(select f_price from fruits where f_price < 20);】先找出所有价格小于20的f_price，再找出所有价格294 都比之前范围大的f_price所对应的记录295 */

296

297 --用exists操作符与子查询语句来查询是否存在f_price大于30元的水果记录

298 select * from fruits where exists

299 (select * from fruits where f_price > 30);300

301 --用as将fruits表名重命名为f后使用

302 select f.* from fruits asf;303

304 --显示f_price金额最大的前三名水果记录

305 select * fromfruits306 order by f_price desc

307 limit 3;308

309 --使用abs函数求所有水果平均值与最大值差值的绝对值

310 select abs(avg(f_price)-max(f_price)) fromfruits;311

312 --使用length函数求每个f_name的名字与他们的字符长度

313 select f_name, length(f_name) from fruits group byf_name;314

315 --使用now函数求当前的日期和时间

316 selectnow();317

318 --使用group_concat函数查询不同s_id下对应的所有f_name信息

319 SELECT s_id, GROUP_CONCAT(f_name) FROMfruits320 GROUP BYs_id;321 /*

322 这段话的是这样读的，找出所有的s_id的记录，然后把后面的f_name以逗号的形式连接起来323 */

324 -----------------------------------------------------------------------------------------

325

326

327 --concat()的用法就是用来连接字符串

328 --语法：concat(str1, str2,...)

329 --返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。

330 --使用concat函数在f_name字段值前添加'fruit_'信息

331 update fruits set f_name = concat('fruit_',f_name);332 select * fromfruits;333

334 --删除f_id为'b5'的数据记录

335 delete from fruits where f_id = 'b5';336 select * fromfruits;337

338 --单表查询练习，彩票数据规则

339 --奖票分析 --------------------------------------------------------------

340 --导入测试用完整数据

341 create tableFinal(342 FNo varchar(10) not null,343 TNo varchar(10) not null,344 Mark varchar(20) not null,345 reward varchar(20) not null,346 bingovalue int not null

347 );348

349 load data local infile 'E:/LiWork/CDA/data/final.csv'

350 into tableFinal351 fields terminated by '\,';352

353 alter table Final add RowNumber int primary key auto_increment; --自增字段，用来记录彩票张数

354

355 select * fromFinal;356 select count(*) fromFinal;357

358

359 #1求总中奖张数及金额360 select count(bingovalue) as 中奖总张数, sum(bingovalue) as中奖总金额361 fromFinal362 where bingovalue <> 0;363

364 #2求各不同奖幅的张数及金额365 select bingovalue as 奖幅, count(bingovalue) as 张数, sum(bingovalue) as金额366 fromFinal367 group bybingovalue368 having bingovalue <> 0;369

370 #3求中奖张数与总张数占比，中奖金额与总金额的占比371 set @allcount = (select count(bingovalue) fromFinal);372 set @allsum = (select count(bingovalue) * 5 fromFinal);373 select count(bingovalue)/@allcount as中奖张数占比,374 sum(bingovalue)/@allsum as 中奖金额占比 fromFinal375 where bingovalue <> 0;376

377 #4检查每个本号下有100张彩票378 select FNo, count(FNo) fromFinal379 group byFNo380 having count(FNo) <> 100;381

382 #5检查每个本号下最多有一张中奖票金额超过50元383 select FNo, count(FNo) fromFinal384 where bingovalue > 50

385 group byFNo386 having count(FNo)>1;387

388 #6检查每本彩票中最多连续7张无奖票389 #创建bingonumber1390 create table bingonumber1 as(391 select Rownumber, bingovalue, FNo fromFinal392 where bingovalue > 0

393 order byrownumber);394

395 select * frombingonumber1;396

397 --drop table bingonumber1; -- 删除表

398

399 #删除第一条记录400 delete from bingonumber1 limit 1;401

402 alter table bingonumber1 add numberkey int primary key auto_increment; --自增

403

404 #重新排序405 alter table bingonumber1 modify numberkey intfirst;406

407 #创建bingonumber2408 create table bingonumber2 as(409 select Rownumber, bingovalue, FNo fromFinal410 where bingovalue > 0

411 order byrownumber);412

413 --drop table bingonumber2; -- 删除表

414

415 alter table bingonumber2 add numberkey int primary key auto_increment; --自增

416

417 #重新排序418 alter table bingonumber2 modify numberkey intfirst;419

420 #检查数据内容及记录行数421 select * frombingonumber1;422 select * frombingonumber2;423 select count(*) frombingonumber1;424 select count(*) frombingonumber2;425

426 #检查测试结果427 select b1.*, b2.*, (b1.rownumber - b2.rownumber) as gap from bingonumber1 as b1, bingonumber2 asb2428 where b1.numberkey =b2.numberkey429 and b1.FNo =b2.FNo430 and (b1.rownumber - b2.rownumber) > 7;431

432 --多表查询，电商数据查询练习

433 usetest;434 ------GoodsColor----

435 create tablegoodscolor(436 ColorID varchar(4) not null default '-',437 ColorNote varchar(20) not null default '-',438 ColorSort int not null default 0,439 pt varchar(9) not null default '-'

440 );441

442 #导入数据443 load data local infile 'D:/liwork/CDA/MySQL - Li Qi/data/goodscolor.csv'

444 into tablegoodscolor445 fields terminated by ','

446 ignore 1lines;447

448 ------GoodsSize----

449 create tablegoodssize(450 SizeID varchar(4) not null default '-',451 SizeNote varchar(100) not null default '-',452 SizeSort int not null default 0,453 pt varchar(9) not null default '-'

454 );455

456 #导入数据457 load data local infile 'D:/liwork/CDA/MySQL - Li Qi/data/goodssize.csv'

458 into tablegoodssize459 fields terminated by ','

460 ignore 1lines;461

462 ------OrderDetail----

463 create tableOrderDetail(464 OrderID varchar(6) not null default '-',465 GoodsID varchar(6) not null default '-',466 GoodsPrice double not null default 0,467 ColorID varchar(4) not null default '-',468 SizeID varchar(4) not null default '-',469 Amount int not null default 0

470 );471

472 #导入数据473 load data local infile 'D:/liwork/CDA/MySQL - Li Qi/data/OrderDetail.txt'

474 into tableOrderDetail475 fields terminated by '\t'

476 ignore 1lines;477

478 select * fromorderdetail;479 select * fromgoodscolor;480 select * fromgoodssize;481

482 --1.倒序查询卖的金额最多的产品

483 select GoodsID, sum(GoodsPrice*amount) fromorderdetail484 group bygoodsid485 order by sum(GoodsPrice*amount) desc;486

487 --2.查询不同尺码下的产品销售数量

488 select SizeNote, sum(amount) fromorderdetail489 left join goodssize on orderdetail.sizeid =goodssize.sizeid490 group byorderdetail.sizeid491 order by sum(amount) desc;492

493 --3. 查询不同颜色下的产品销售金额

494 select colornote as 颜色, sum(goodsprice * amount) as 销售额 from orderdetail asod495 left join goodscolor as gc on od.colorid=gc.colorid496 group byod.colorid497 order by sum(goodsprice * amount) desc;498

499 --4. 查询不同尺码下的不同颜色的产品销售金额

500 select sizenote,colornote,sum(goodsprice * amount) from orderdetail asod501 left join goodssize as gs on od.sizeid =gs.sizeid502 left join goodscolor as gc on od.colorid =gc.colorid503 group byod.sizeid, od.colorid504 order by sum(goodsprice * amount) desc;