黑马SSM练习中 Oracle SQL代码

  1 -- 10张表 product member orders traveller order_traveller users role permission  users_role role_permission
  2 --请先创建用户，并给用户添加权限，然后把sql 直接复制到plsql中执行即可；
  3 
  4 -- 产品表
  5 CREATE TABLE product(
  6 id varchar2(32) default SYS_GUID() PRIMARY KEY,
  7 productNum VARCHAR2(50) NOT NULL,
  8 productName VARCHAR2(50),
  9 cityName VARCHAR2(50),
 10 DepartureTime timestamp,
 11 productPrice Number,
 12 productDesc VARCHAR2(500),
 13 productStatus INT,
 14 CONSTRAINT product UNIQUE (id, productNum)
 15 );
 16 
 17 insert into PRODUCT (id, productnum, productname, cityname, departuretime, productprice, productdesc, productstatus) values ('676C5BD1D35E429A8C2E114939C5685A', 'itcast-002', '北京三日游', '北京', to_timestamp('10-10-2018 10:10:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 1200, '不错的旅行', 1);
 18 insert into PRODUCT (id, productnum, productname, cityname, departuretime, productprice,productdesc, productstatus) values ('12B7ABF2A4C544568B0A7C69F36BF8B7', 'itcast-003', '上海五日游', '上海', to_timestamp('25-04-2018 14:30:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 1800, '魔都我来了', 0);
 19 insert into PRODUCT (id, productnum, productname, cityname, departuretime, productprice,productdesc, productstatus) values ('9F71F01CB448476DAFB309AA6DF9497F', 'itcast-001', '北京三日游', '北京', to_timestamp('10-10-2018 10:10:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 1200, '不错的旅行', 1);
 20 commit;
 21 
 22 
 23 -- 会员表
 24 CREATE TABLE member(
 25        id varchar2(32) default SYS_GUID() PRIMARY KEY,
 26        NAME VARCHAR2(20),
 27        nickname VARCHAR2(20),
 28        phoneNum VARCHAR2(20),
 29        email VARCHAR2(20) 
 30 );
 31 insert into MEMBER (id, name, nickname, phonenum, email)
 32 values ('E61D65F673D54F68B0861025C69773DB', '张三', '小三', '18888888888', 'zs@163.com');
 33 commit;
 34 
 35 
 36 
 37 
 38 -- 订单表
 39 
 40 CREATE TABLE orders(
 41   id varchar2(32) default SYS_GUID() PRIMARY KEY,
 42   orderNum VARCHAR2(20) NOT NULL UNIQUE,
 43   orderTime timestamp,
 44   peopleCount INT,
 45   orderDesc VARCHAR2(500),
 46   payType INT,
 47   orderStatus INT,
 48   productId varchar2(32),
 49   memberId varchar2(32),
 50   FOREIGN KEY (productId) REFERENCES product(id),
 51   FOREIGN KEY (memberId) REFERENCES member(id)
 52 );
 53 insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
 54 values ('0E7231DC797C486290E8713CA3C6ECCC', '12345', to_timestamp('02-03-2018 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '676C5BD1D35E429A8C2E114939C5685A', 'E61D65F673D54F68B0861025C69773DB');
 55 insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
 56 values ('5DC6A48DD4E94592AE904930EA866AFA', '54321', to_timestamp('02-03-2018 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '676C5BD1D35E429A8C2E114939C5685A', 'E61D65F673D54F68B0861025C69773DB');
 57 insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
 58 values ('2FF351C4AC744E2092DCF08CFD314420', '67890', to_timestamp('02-03-2018 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB');
 59 insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
 60 values ('A0657832D93E4B10AE88A2D4B70B1A28', '98765', to_timestamp('02-03-2018 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB');
 61 insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
 62 values ('E4DD4C45EED84870ABA83574A801083E', '11111', to_timestamp('02-03-2018 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB');
 63 insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
 64 values ('96CC8BD43C734CC2ACBFF09501B4DD5D', '22222', to_timestamp('02-03-2018 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '12B7ABF2A4C544568B0A7C69F36BF8B7', 'E61D65F673D54F68B0861025C69773DB');
 65 insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
 66 values ('55F9AF582D5A4DB28FB4EC3199385762', '33333', to_timestamp('02-03-2018 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F', 'E61D65F673D54F68B0861025C69773DB');
 67 insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
 68 values ('CA005CF1BE3C4EF68F88ABC7DF30E976', '44444', to_timestamp('02-03-2018 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F', 'E61D65F673D54F68B0861025C69773DB');
 69 insert into ORDERS (id, ordernum, ordertime, peoplecount, orderdesc, paytype, orderstatus, productid, memberid)
 70 values ('3081770BC3984EF092D9E99760FDABDE', '55555', to_timestamp('02-03-2018 12:00:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 2, '没什么', 0, 1, '9F71F01CB448476DAFB309AA6DF9497F', 'E61D65F673D54F68B0861025C69773DB');
 71 commit;
 72 
 73 -- 旅客表
 74 CREATE TABLE traveller(
 75   id varchar2(32) default SYS_GUID() PRIMARY KEY,
 76   NAME VARCHAR2(20),
 77   sex VARCHAR2(20),
 78   phoneNum VARCHAR2(20),
 79   credentialsType INT,
 80   credentialsNum VARCHAR2(50),
 81   travellerType INT
 82 );
 83 insert into TRAVELLER (id, name, sex, phonenum, credentialstype, credentialsnum, travellertype)
 84 values ('3FE27DF2A4E44A6DBC5D0FE4651D3D3E', '张龙', '男', '13333333333', 0, '123456789009876543', 0);
 85 insert into TRAVELLER (id, name, sex, phonenum, credentialstype, credentialsnum, travellertype)
 86 values ('EE7A71FB6945483FBF91543DBE851960', '张小龙', '男', '15555555555', 0, '987654321123456789', 1);
 87 commit;
 88 
 89 
 90 ---- 订单与旅客中间表
 91 CREATE TABLE order_traveller(
 92   orderId varchar2(32),
 93   travellerId varchar2(32),
 94   PRIMARY KEY (orderId,travellerId),
 95   FOREIGN KEY (orderId) REFERENCES orders(id),
 96   FOREIGN KEY (travellerId) REFERENCES traveller(id)
 97 );
 98 
 99 insert into ORDER_TRAVELLER (orderid, travellerid)
100 values ('0E7231DC797C486290E8713CA3C6ECCC', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E');
101 insert into ORDER_TRAVELLER (orderid, travellerid)
102 values ('2FF351C4AC744E2092DCF08CFD314420', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E');
103 insert into ORDER_TRAVELLER (orderid, travellerid)
104 values ('3081770BC3984EF092D9E99760FDABDE', 'EE7A71FB6945483FBF91543DBE851960');
105 insert into ORDER_TRAVELLER (orderid, travellerid)
106 values ('55F9AF582D5A4DB28FB4EC3199385762', 'EE7A71FB6945483FBF91543DBE851960');
107 insert into ORDER_TRAVELLER (orderid, travellerid)
108 values ('5DC6A48DD4E94592AE904930EA866AFA', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E');
109 insert into ORDER_TRAVELLER (orderid, travellerid)
110 values ('96CC8BD43C734CC2ACBFF09501B4DD5D', 'EE7A71FB6945483FBF91543DBE851960');
111 insert into ORDER_TRAVELLER (orderid, travellerid)
112 values ('A0657832D93E4B10AE88A2D4B70B1A28', '3FE27DF2A4E44A6DBC5D0FE4651D3D3E');
113 insert into ORDER_TRAVELLER (orderid, travellerid)
114 values ('CA005CF1BE3C4EF68F88ABC7DF30E976', 'EE7A71FB6945483FBF91543DBE851960');
115 insert into ORDER_TRAVELLER (orderid, travellerid)
116 values ('E4DD4C45EED84870ABA83574A801083E', 'EE7A71FB6945483FBF91543DBE851960');
117 commit;
118 
119 
120 -- 用户表
121 CREATE TABLE users(
122 id varchar2(32) default SYS_GUID() PRIMARY KEY,
123 email VARCHAR2(50) UNIQUE NOT NULL,
124 username VARCHAR2(50),
125 PASSWORD VARCHAR2(50),
126 phoneNum VARCHAR2(20),
127 STATUS INT
128 );
129 
130 -- 角色表
131 CREATE TABLE role(
132 id varchar2(32) default SYS_GUID() PRIMARY KEY,
133 roleName VARCHAR2(50) ,
134 roleDesc VARCHAR2(50)
135 );
136 
137 
138 -- 用户角色关联表
139 CREATE TABLE users_role(
140 userId varchar2(32),
141 roleId varchar2(32),
142 PRIMARY KEY(userId,roleId),
143 FOREIGN KEY (userId) REFERENCES users(id),
144 FOREIGN KEY (roleId) REFERENCES role(id)
145 );
146 
147 -- 资源权限表
148 CREATE TABLE permission(
149 id varchar2(32) default SYS_GUID() PRIMARY KEY,
150 permissionName VARCHAR2(50) ,
151 url VARCHAR2(50)
152 );
153 
154 -- 角色权限关联表
155 CREATE TABLE role_permission(
156 permissionId varchar2(32),
157 roleId varchar2(32),
158 PRIMARY KEY(permissionId,roleId),
159 FOREIGN KEY (permissionId) REFERENCES permission(id),
160 FOREIGN KEY (roleId) REFERENCES role(id)
161 );

product member orders traveller order_traveller users role permission users_role role_permission

 1 -- 创建表空间
 2 create tablespace itheima_ssm2
 3 datafile 'c:oracledataitheima_ssm2.dbf'
 4 size 50m
 5 autoextend on
 6 next 20m 
 7 maxsize 100m;
 8 
 9 -- 删除表空间同时删除数据文件
10 drop tablespace itheima_ssm2 including contents and datafiles;
11 
12 
13 -- 查询所有表空间
14 select * from dba_tablespaces;
15 
16 -- 查询所有表
17 select * from dba_tables;
18 
19 -- 查询表空间下的表，表空间必须全大写
20 select table_name from dba_tables where tablespace_name='ITHEIMA_SSM2';
21 select table_name from dba_tables where tablespace_name=upper('itheima_ssm2');
22 
23 
24 -- 创建用户
25 create user ssm2
26 identified by ssm2
27 default tablespace itheima_ssm2;
28 
29 -- 删除用户
30 drop user ssm2;
31 
32 -- 用户授权
33 grant create session to ssm2;
34 grant connect,resource to ssm2;
35 
36 -- 撤销权限
37 revoke resource from ssm;
38 
39 -- 权限CONNECT RESOURCE DBA角色的权限
40 select * from dba_sys_privs where grantee='RESOURCE';
41 select * from dba_sys_privs where grantee='CONNECT';
42 select * from dba_sys_privs where grantee='DBA';
43 -- 当用户被授予RESOURCE或DBA角色时，自动获得UNLIMITED TABLESPACE权限。
44 
45 -- 查看用户权限
46 select * from dba_sys_privs where grantee='SSM2';
47 
48 -- 查看用户角色
49 select * from dba_role_privs where grantee='SSM';
50 
51 -- 查看数据库里所有用户，必须是dba
52 select * from dba_users;
53 
54 -- 查看数据库里所有用户，部分字段
55 select * from all_users;
56 
57 -- 查看当前用户
58 select * from user_users;
59 
60 
61 -- 编码问题，2者的值保持一致，不一致时设置NLS_LANG环境变量值为服务器变量的值。
62 -- 查看服务器环境变量
63 select * from V$NLS_PARAMETERS;
64 
65 -- 查看客户端环境变量
66 select userenv('language') from dual;

建表空间建用户操作

相关阅读:
手写token解析器、语法解析器、LLVM IR生成器（GO语言）
Windows下切分文件（GnuWin32）
转载：教你分分钟搞定Docker私有仓库Registry
marathon传参一
 DC/OS安装
 自己写编程语言-m语言
 揽货最短路径解决方案算法
 揽货最短路径解决方案算法
 用keras作CNN卷积网络书本分类（书本、非书本）
用keras做SQL注入攻击的判断
原文地址：https://www.cnblogs.com/mozq/p/11058043.html