Hibernate学习之HQL查询
马哥私房菜博客地址:https://github.com/mageSFC/myblog
HQL(hibernate query language)是面向对象的查询语言,他和sql查询语言有些相似。
在hibernate提供的各种检索方式中,hql是使用最广泛的一种检索方式,它具有如下功能:
1.在查询语句中设定各种查询条件
2.支持投影查询,即仅检索出对象的部分属性
3.支持分页查询
4.支持链接查询
5.支持分组查询,允许使用having和group by关键字
6.提供内置聚集函数,如sum(),min(),max()
7.支持子查询
8.支持动态绑定参数
9.能够调用用户定义的sql函数或者标准的sql函数
下面的例子我们采用了oracle数据库
oracle数据库安装在了windows上面。
马哥私房菜博客地址:https://github.com/mageSFC/myblog
马哥私房菜博客地址:https://github.com/mageSFC/myblog
oracle远程访问开启:
解决方法:
查看端口状态:CMD -> netstat -a -n
显示的结果是:1521端口对应的本地地址栏为:127.0.0.0:1521,
此时,修改Oracle安装目录下dbhome_1\NETWORK\ADMIN\listener.ora文件(或是PLSQL下的对应文件instantclient_11_2\listener.ora)的HOST值,改为0.0.0.0。
重启Oracle监听服务;
再次通过netstat查看端口信息,显示:1521端口对应的本地地址栏为:0.0.0.0:1521
这时通过127.0.0.1或loaclhost或主机名或本机ip都可telnet通过。
在Windows系统下完成Oracle安装后,在其防火墙设置中开放1521端口(Oracle默认的侦听端口)。若客户端仍然无法访问,则需要作进一步的设置,即在注册表“HKEY_LOCAL_MACHINE” - “Software” - “ORACLE” - “HOME”下添加一个注册表项“USE_SHARED_SOCKED”,并将其值设为TRUE,然后重启Oracle服务及Listener服务。
马哥私房菜博客地址:https://github.com/mageSFC/myblog
总结:
Oracle Telnet 1521失败,要检查以下几点:
1、防火墙是否开启,若开启,是否有对1521端口开启;
2、listener.ora文件的HOST值。
注:10.2以上,USE_SHARDED_SOCKET就已经是默认值为TRUE了,无需再修改。
最后通过nmap命令可以看到1521端口是open的状态表明可以了
$ nmap 10.0.63.42
Starting Nmap 7.01 ( https://nmap.org ) at 2017-12-22 16:01 CST
Nmap scan report for 10.0.63.42
Host is up (0.88s latency).
Not shown: 987 closed ports
PORT STATE SERVICE
1521/tcp open oracle
马哥私房菜博客地址:https://github.com/mageSFC/myblog
马哥私房菜博客地址:https://github.com/mageSFC/myblog
Nmap done: 1 IP address (1 host up) scanned in 5.13 seconds
添加oracle的jdbc驱动文件
把从官网下载的oracle的jdbc驱动jar包放到家目录下面执行下面命令:
$ mvn install:install-file -Dfile=ojdbc8.jar -DgroupId=com.oracle -DartifactId=ojdbc8 -Dversion=12 -Dpackaging=jar
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building Maven Stub Project (No POM) 1
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] --- maven-install-plugin:2.5.2:install-file (default-cli) @ standalone-pom ---
[INFO] pom.xml not found in ojdbc8.jar
[INFO] Installing /home/mamh/ojdbc8.jar to /home/mamh/.m2/repository/com/oracle/ojdbc8/12/ojdbc8-12.jar
[INFO] Installing /tmp/mvninstall5920371845117601504.pom to /home/mamh/.m2/repository/com/oracle/ojdbc8/12/ojdbc8-12.pom
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 0.332 s
[INFO] Finished at: 2017-12-22T15:54:02+08:00
[INFO] Final Memory: 8M/303M
[INFO] ------------------------------------------------------------------------
$ cat /home/mamh/.m2/repository/com/oracle/ojdbc8/12/ojdbc8-12.pom
<?xml version="1.0" encoding="UTF-8"?>
<project xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd" xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<modelVersion>4.0.0</modelVersion>
<groupId>com.oracle</groupId>
<artifactId>ojdbc8</artifactId>
<version>12</version>
<description>POM was created from install:install-file</description>
</project>
最后pom.xml中添加如下:
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc8</artifactId>
<version>12</version>
</dependency>
马哥私房菜博客地址:https://github.com/mageSFC/myblog
这里我们给出一个测试使用的数据库表,包含数据的
马哥私房菜博客地址:https://github.com/mageSFC/myblog
EMPLOYEE表格# | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | PHONE_NUMBER | HIRE_DATE | JOB_ID | SALARY | COMMISSION_PCT | MANAGER_ID | DEPARTMENT_ID | |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 00:00:00 | AD_PRES | 24000.00 | NULL | NULL | 90 |
2 | 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1989-09-21 00:00:00 | AD_VP | 17000.00 | NULL | 100 | 90 |
3 | 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1993-01-13 00:00:00 | AD_VP | 17000.00 | NULL | 100 | 90 |
4 | 103 | Alexander | Hunold | AHUNOLD | 590.423.4567 | 1990-01-03 00:00:00 | IT_PROG | 9000.00 | NULL | 102 | 60 |
5 | 104 | Bruce | Ernst | BERNST | 590.423.4568 | 1991-05-21 00:00:00 | IT_PROG | 6000.00 | NULL | 103 | 60 |
6 | 105 | David | Austin | DAUSTIN | 590.423.4569 | 1997-06-25 00:00:00 | IT_PROG | 4800.00 | NULL | 103 | 60 |
7 | 106 | Valli | Pataballa | VPATABAL | 590.423.4560 | 1998-02-05 00:00:00 | IT_PROG | 4800.00 | NULL | 103 | 60 |
8 | 107 | Diana | Lorentz | DLORENTZ | 590.423.5567 | 1999-02-07 00:00:00 | IT_PROG | 4200.00 | NULL | 103 | 60 |
9 | 108 | Nancy | Greenberg | NGREENBE | 515.124.4569 | 1994-08-17 00:00:00 | FI_MGR | 12000.00 | NULL | 101 | 100 |
10 | 109 | Daniel | Faviet | DFAVIET | 515.124.4169 | 1994-08-16 00:00:00 | FI_ACCOUNT | 9000.00 | NULL | 108 | 100 |
11 | 110 | John | Chen | JCHEN | 515.124.4269 | 1997-09-28 00:00:00 | FI_ACCOUNT | 8200.00 | NULL | 108 | 100 |
12 | 111 | Ismael | Sciarra | ISCIARRA | 515.124.4369 | 1997-09-30 00:00:00 | FI_ACCOUNT | 7700.00 | NULL | 108 | 100 |
13 | 112 | Jose Manuel | Urman | JMURMAN | 515.124.4469 | 1998-03-07 00:00:00 | FI_ACCOUNT | 7800.00 | NULL | 108 | 100 |
14 | 113 | Luis | Popp | LPOPP | 515.124.4567 | 1999-12-07 00:00:00 | FI_ACCOUNT | 6900.00 | NULL | 108 | 100 |
15 | 114 | Den | Raphaely | DRAPHEAL | 515.127.4561 | 1994-12-07 00:00:00 | PU_MAN | 11000.00 | NULL | 100 | 30 |
16 | 115 | Alexander | Khoo | AKHOO | 515.127.4562 | 1995-05-18 00:00:00 | PU_CLERK | 3100.00 | NULL | 114 | 30 |
17 | 116 | Shelli | Baida | SBAIDA | 515.127.4563 | 1997-12-24 00:00:00 | PU_CLERK | 2900.00 | NULL | 114 | 30 |
18 | 117 | Sigal | Tobias | STOBIAS | 515.127.4564 | 1997-07-24 00:00:00 | PU_CLERK | 2800.00 | NULL | 114 | 30 |
19 | 118 | Guy | Himuro | GHIMURO | 515.127.4565 | 1998-11-15 00:00:00 | PU_CLERK | 2600.00 | NULL | 114 | 30 |
20 | 119 | Karen | Colmenares | KCOLMENA | 515.127.4566 | 1999-08-10 00:00:00 | PU_CLERK | 2500.00 | NULL | 114 | 30 |
21 | 120 | Matthew | Weiss | MWEISS | 650.123.1234 | 1996-07-18 00:00:00 | ST_MAN | 8000.00 | NULL | 100 | 50 |
22 | 121 | Adam | Fripp | AFRIPP | 650.123.2234 | 1997-04-10 00:00:00 | ST_MAN | 8200.00 | NULL | 100 | 50 |
23 | 122 | Payam | Kaufling | PKAUFLIN | 650.123.3234 | 1995-05-01 00:00:00 | ST_MAN | 7900.00 | NULL | 100 | 50 |
24 | 123 | Shanta | Vollman | SVOLLMAN | 650.123.4234 | 1997-10-10 00:00:00 | ST_MAN | 6500.00 | NULL | 100 | 50 |
25 | 124 | Kevin | Mourgos | KMOURGOS | 650.123.5234 | 1999-11-16 00:00:00 | ST_MAN | 5800.00 | NULL | 100 | 50 |
26 | 125 | Julia | Nayer | JNAYER | 650.124.1214 | 1997-07-16 00:00:00 | ST_CLERK | 3200.00 | NULL | 120 | 50 |
27 | 126 | Irene | Mikkilineni | IMIKKILI | 650.124.1224 | 1998-09-28 00:00:00 | ST_CLERK | 2700.00 | NULL | 120 | 50 |
28 | 127 | James | Landry | JLANDRY | 650.124.1334 | 1999-01-14 00:00:00 | ST_CLERK | 2400.00 | NULL | 120 | 50 |
29 | 128 | Steven | Markle | SMARKLE | 650.124.1434 | 2000-03-08 00:00:00 | ST_CLERK | 2200.00 | NULL | 120 | 50 |
30 | 129 | Laura | Bissot | LBISSOT | 650.124.5234 | 1997-08-20 00:00:00 | ST_CLERK | 3300.00 | NULL | 121 | 50 |
31 | 130 | Mozhe | Atkinson | MATKINSO | 650.124.6234 | 1997-10-30 00:00:00 | ST_CLERK | 2800.00 | NULL | 121 | 50 |
32 | 131 | James | Marlow | JAMRLOW | 650.124.7234 | 1997-02-16 00:00:00 | ST_CLERK | 2500.00 | NULL | 121 | 50 |
33 | 132 | TJ | Olson | TJOLSON | 650.124.8234 | 1999-04-10 00:00:00 | ST_CLERK | 2100.00 | NULL | 121 | 50 |
34 | 133 | Jason | Mallin | JMALLIN | 650.127.1934 | 1996-06-14 00:00:00 | ST_CLERK | 3300.00 | NULL | 122 | 50 |
35 | 134 | Michael | Rogers | MROGERS | 650.127.1834 | 1998-08-26 00:00:00 | ST_CLERK | 2900.00 | NULL | 122 | 50 |
36 | 135 | Ki | Gee | KGEE | 650.127.1734 | 1999-12-12 00:00:00 | ST_CLERK | 2400.00 | NULL | 122 | 50 |
37 | 136 | Hazel | Philtanker | HPHILTAN | 650.127.1634 | 2000-02-06 00:00:00 | ST_CLERK | 2200.00 | NULL | 122 | 50 |
38 | 137 | Renske | Ladwig | RLADWIG | 650.121.1234 | 1995-07-14 00:00:00 | ST_CLERK | 3600.00 | NULL | 123 | 50 |
39 | 138 | Stephen | Stiles | SSTILES | 650.121.2034 | 1997-10-26 00:00:00 | ST_CLERK | 3200.00 | NULL | 123 | 50 |
40 | 139 | John | Seo | JSEO | 650.121.2019 | 1998-02-12 00:00:00 | ST_CLERK | 2700.00 | NULL | 123 | 50 |
41 | 140 | Joshua | Patel | JPATEL | 650.121.1834 | 1998-04-06 00:00:00 | ST_CLERK | 2500.00 | NULL | 123 | 50 |
42 | 141 | Trenna | Rajs | TRAJS | 650.121.8009 | 1995-10-17 00:00:00 | ST_CLERK | 3500.00 | NULL | 124 | 50 |
43 | 142 | Curtis | Davies | CDAVIES | 650.121.2994 | 1997-01-29 00:00:00 | ST_CLERK | 3100.00 | NULL | 124 | 50 |
44 | 143 | Randall | Matos | RMATOS | 650.121.2874 | 1998-03-15 00:00:00 | ST_CLERK | 2600.00 | NULL | 124 | 50 |
45 | 144 | Peter | Vargas | PVARGAS | 650.121.2004 | 1998-07-09 00:00:00 | ST_CLERK | 2500.00 | NULL | 124 | 50 |
46 | 145 | John | Russell | JRUSSEL | 011.44.1344.429268 | 1996-10-01 00:00:00 | SA_MAN | 14000.00 | 0.40 | 100 | 80 |
47 | 146 | Karen | Partners | KPARTNER | 011.44.1344.467268 | 1997-01-05 00:00:00 | SA_MAN | 13500.00 | 0.30 | 100 | 80 |
48 | 147 | Alberto | Errazuriz | AERRAZUR | 011.44.1344.429278 | 1997-03-10 00:00:00 | SA_MAN | 12000.00 | 0.30 | 100 | 80 |
49 | 148 | Gerald | Cambrault | GCAMBRAU | 011.44.1344.619268 | 1999-10-15 00:00:00 | SA_MAN | 11000.00 | 0.30 | 100 | 80 |
50 | 149 | Eleni | Zlotkey | EZLOTKEY | 011.44.1344.429018 | 2000-01-29 00:00:00 | SA_MAN | 10500.00 | 0.20 | 100 | 80 |
51 | 150 | Peter | Tucker | PTUCKER | 011.44.1344.129268 | 1997-01-30 00:00:00 | SA_REP | 10000.00 | 0.30 | 145 | 80 |
52 | 151 | David | Bernstein | DBERNSTE | 011.44.1344.345268 | 1997-03-24 00:00:00 | SA_REP | 9500.00 | 0.25 | 145 | 80 |
53 | 152 | Peter | Hall | PHALL | 011.44.1344.478968 | 1997-08-20 00:00:00 | SA_REP | 9000.00 | 0.25 | 145 | 80 |
54 | 153 | Christopher | Olsen | COLSEN | 011.44.1344.498718 | 1998-03-30 00:00:00 | SA_REP | 8000.00 | 0.20 | 145 | 80 |
55 | 154 | Nanette | Cambrault | NCAMBRAU | 011.44.1344.987668 | 1998-12-09 00:00:00 | SA_REP | 7500.00 | 0.20 | 145 | 80 |
56 | 155 | Oliver | Tuvault | OTUVAULT | 011.44.1344.486508 | 1999-11-23 00:00:00 | SA_REP | 7000.00 | 0.15 | 145 | 80 |
57 | 156 | Janette | King | JKING | 011.44.1345.429268 | 1996-01-30 00:00:00 | SA_REP | 10000.00 | 0.35 | 146 | 80 |
58 | 157 | Patrick | Sully | PSULLY | 011.44.1345.929268 | 1996-03-04 00:00:00 | SA_REP | 9500.00 | 0.35 | 146 | 80 |
59 | 158 | Allan | McEwen | AMCEWEN | 011.44.1345.829268 | 1996-08-01 00:00:00 | SA_REP | 9000.00 | 0.35 | 146 | 80 |
60 | 159 | Lindsey | Smith | LSMITH | 011.44.1345.729268 | 1997-03-10 00:00:00 | SA_REP | 8000.00 | 0.30 | 146 | 80 |
61 | 160 | Louise | Doran | LDORAN | 011.44.1345.629268 | 1997-12-15 00:00:00 | SA_REP | 7500.00 | 0.30 | 146 | 80 |
62 | 161 | Sarath | Sewall | SSEWALL | 011.44.1345.529268 | 1998-11-03 00:00:00 | SA_REP | 7000.00 | 0.25 | 146 | 80 |
63 | 162 | Clara | Vishney | CVISHNEY | 011.44.1346.129268 | 1997-11-11 00:00:00 | SA_REP | 10500.00 | 0.25 | 147 | 80 |
64 | 163 | Danielle | Greene | DGREENE | 011.44.1346.229268 | 1999-03-19 00:00:00 | SA_REP | 9500.00 | 0.15 | 147 | 80 |
65 | 164 | Mattea | Marvins | MMARVINS | 011.44.1346.329268 | 2000-01-24 00:00:00 | SA_REP | 7200.00 | 0.10 | 147 | 80 |
66 | 165 | David | Lee | DLEE | 011.44.1346.529268 | 2000-02-23 00:00:00 | SA_REP | 6800.00 | 0.10 | 147 | 80 |
67 | 166 | Sundar | Ande | SANDE | 011.44.1346.629268 | 2000-03-24 00:00:00 | SA_REP | 6400.00 | 0.10 | 147 | 80 |
68 | 167 | Amit | Banda | ABANDA | 011.44.1346.729268 | 2000-04-21 00:00:00 | SA_REP | 6200.00 | 0.10 | 147 | 80 |
69 | 168 | Lisa | Ozer | LOZER | 011.44.1343.929268 | 1997-03-11 00:00:00 | SA_REP | 11500.00 | 0.25 | 148 | 80 |
70 | 169 | Harrison | Bloom | HBLOOM | 011.44.1343.829268 | 1998-03-23 00:00:00 | SA_REP | 10000.00 | 0.20 | 148 | 80 |
71 | 170 | Tayler | Fox | TFOX | 011.44.1343.729268 | 1998-01-24 00:00:00 | SA_REP | 9600.00 | 0.20 | 148 | 80 |
72 | 171 | William | Smith | WSMITH | 011.44.1343.629268 | 1999-02-23 00:00:00 | SA_REP | 7400.00 | 0.15 | 148 | 80 |
73 | 172 | Elizabeth | Bates | EBATES | 011.44.1343.529268 | 1999-03-24 00:00:00 | SA_REP | 7300.00 | 0.15 | 148 | 80 |
74 | 173 | Sundita | Kumar | SKUMAR | 011.44.1343.329268 | 2000-04-21 00:00:00 | SA_REP | 6100.00 | 0.10 | 148 | 80 |
75 | 174 | Ellen | Abel | EABEL | 011.44.1644.429267 | 1996-05-11 00:00:00 | SA_REP | 11000.00 | 0.30 | 149 | 80 |
76 | 175 | Alyssa | Hutton | AHUTTON | 011.44.1644.429266 | 1997-03-19 00:00:00 | SA_REP | 8800.00 | 0.25 | 149 | 80 |
77 | 176 | Jonathon | Taylor | JTAYLOR | 011.44.1644.429265 | 1998-03-24 00:00:00 | SA_REP | 8600.00 | 0.20 | 149 | 80 |
78 | 177 | Jack | Livingston | JLIVINGS | 011.44.1644.429264 | 1998-04-23 00:00:00 | SA_REP | 8400.00 | 0.20 | 149 | 80 |
79 | 178 | Kimberely | Grant | KGRANT | 011.44.1644.429263 | 1999-05-24 00:00:00 | SA_REP | 7000.00 | 0.15 | 149 | NULL |
80 | 179 | Charles | Johnson | CJOHNSON | 011.44.1644.429262 | 2000-01-04 00:00:00 | SA_REP | 6200.00 | 0.10 | 149 | 80 |
81 | 180 | Winston | Taylor | WTAYLOR | 650.507.9876 | 1998-01-24 00:00:00 | SH_CLERK | 3200.00 | NULL | 120 | 50 |
82 | 181 | Jean | Fleaur | JFLEAUR | 650.507.9877 | 1998-02-23 00:00:00 | SH_CLERK | 3100.00 | NULL | 120 | 50 |
83 | 182 | Martha | Sullivan | MSULLIVA | 650.507.9878 | 1999-06-21 00:00:00 | SH_CLERK | 2500.00 | NULL | 120 | 50 |
84 | 183 | Girard | Geoni | GGEONI | 650.507.9879 | 2000-02-03 00:00:00 | SH_CLERK | 2800.00 | NULL | 120 | 50 |
85 | 184 | Nandita | Sarchand | NSARCHAN | 650.509.1876 | 1996-01-27 00:00:00 | SH_CLERK | 4200.00 | NULL | 121 | 50 |
86 | 185 | Alexis | Bull | ABULL | 650.509.2876 | 1997-02-20 00:00:00 | SH_CLERK | 4100.00 | NULL | 121 | 50 |
87 | 186 | Julia | Dellinger | JDELLING | 650.509.3876 | 1998-06-24 00:00:00 | SH_CLERK | 3400.00 | NULL | 121 | 50 |
88 | 187 | Anthony | Cabrio | ACABRIO | 650.509.4876 | 1999-02-07 00:00:00 | SH_CLERK | 3000.00 | NULL | 121 | 50 |
89 | 188 | Kelly | Chung | KCHUNG | 650.505.1876 | 1997-06-14 00:00:00 | SH_CLERK | 3800.00 | NULL | 122 | 50 |
90 | 189 | Jennifer | Dilly | JDILLY | 650.505.2876 | 1997-08-13 00:00:00 | SH_CLERK | 3600.00 | NULL | 122 | 50 |
91 | 190 | Timothy | Gates | TGATES | 650.505.3876 | 1998-07-11 00:00:00 | SH_CLERK | 2900.00 | NULL | 122 | 50 |
92 | 191 | Randall | Perkins | RPERKINS | 650.505.4876 | 1999-12-19 00:00:00 | SH_CLERK | 2500.00 | NULL | 122 | 50 |
93 | 192 | Sarah | Bell | SBELL | 650.501.1876 | 1996-02-04 00:00:00 | SH_CLERK | 4000.00 | NULL | 123 | 50 |
94 | 193 | Britney | Everett | BEVERETT | 650.501.2876 | 1997-03-03 00:00:00 | SH_CLERK | 3900.00 | NULL | 123 | 50 |
95 | 194 | Samuel | McCain | SMCCAIN | 650.501.3876 | 1998-07-01 00:00:00 | SH_CLERK | 3200.00 | NULL | 123 | 50 |
96 | 195 | Vance | Jones | VJONES | 650.501.4876 | 1999-03-17 00:00:00 | SH_CLERK | 2800.00 | NULL | 123 | 50 |
97 | 196 | Alana | Walsh | AWALSH | 650.507.9811 | 1998-04-24 00:00:00 | SH_CLERK | 3100.00 | NULL | 124 | 50 |
98 | 197 | Kevin | Feeney | KFEENEY | 650.507.9822 | 1998-05-23 00:00:00 | SH_CLERK | 3000.00 | NULL | 124 | 50 |
99 | 198 | Donald | OConnell | DOCONNEL | 650.507.9833 | 1999-06-21 00:00:00 | SH_CLERK | 2600.00 | NULL | 124 | 50 |
100 | 199 | Douglas | Grant | DGRANT | 650.507.9844 | 2000-01-13 00:00:00 | SH_CLERK | 2600.00 | NULL | 124 | 50 |
101 | 200 | Jennifer | Whalen | JWHALEN | 515.123.4444 | 1987-09-17 00:00:00 | AD_ASST | 4400.00 | NULL | 101 | 10 |
102 | 201 | Michael | Hartstein | MHARTSTE | 515.123.5555 | 1996-02-17 00:00:00 | MK_MAN | 13000.00 | NULL | 100 | 20 |
103 | 202 | Pat | Fay | PFAY | 603.123.6666 | 1997-08-17 00:00:00 | MK_REP | 6000.00 | NULL | 201 | 20 |
104 | 203 | Susan | Mavris | SMAVRIS | 515.123.7777 | 1994-06-07 00:00:00 | HR_REP | 6500.00 | NULL | 101 | 40 |
105 | 204 | Hermann | Baer | HBAER | 515.123.8888 | 1994-06-07 00:00:00 | PR_REP | 10000.00 | NULL | 101 | 70 |
106 | 205 | Shelley | Higgins | SHIGGINS | 515.123.8080 | 1994-06-07 00:00:00 | AC_MGR | 12000.00 | NULL | 101 | 110 |
107 | 206 | William | Gietz | WGIETZ | 515.123.8181 | 1994-06-07 00:00:00 | AC_ACCOUNT | 8300.00 | NULL | 205 | 110 |
马哥私房菜博客地址:https://github.com/mageSFC/myblog
DEPARTMENT表格 | DEPARTMENT_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID |
---|---|---|---|---|
1 | 10 | Administration | 200 | 1700 |
2 | 20 | Marketing | 201 | 1800 |
3 | 30 | Purchasing | 114 | 1700 |
4 | 40 | Human Resources | 203 | 2400 |
5 | 50 | Shipping | 121 | 1500 |
6 | 60 | IT | 103 | 1400 |
7 | 70 | Public Relations | 204 | 2700 |
8 | 80 | Sales | 145 | 2500 |
9 | 90 | Executive | 100 | 1700 |
10 | 100 | Finance | 108 | 1700 |
11 | 110 | Accounting | 205 | 1700 |
12 | 120 | Treasury | NULL | 1700 |
13 | 130 | Corporate Tax | NULL | 1700 |
14 | 140 | Control And Credit | NULL | 1700 |
15 | 150 | Shareholder Services | NULL | 1700 |
16 | 160 | Benefits | NULL | 1700 |
17 | 170 | Manufacturing | NULL | 1700 |
18 | 180 | Construction | NULL | 1700 |
19 | 190 | Contracting | NULL | 1700 |
20 | 200 | Operations | NULL | 1700 |
21 | 210 | IT Support | NULL | 1700 |
22 | 220 | NOC | NULL | 1700 |
23 | 230 | IT Helpdesk | NULL | 1700 |
24 | 240 | Government Sales | NULL | 1700 |
25 | 250 | Retail Sales | NULL | 1700 |
26 | 260 | Recruiting | NULL | 1700 |
27 | 270 | Payroll | NULL | 1700 |
马哥私房菜博客地址:https://github.com/mageSFC/myblog
SET VERIFY OFF
ALTER SESSION SET NLS_LANGUAGE=American;
REM ***************************insert data into the REGIONS table
Prompt ****** Populating REGIONS table ....
INSERT INTO regions VALUES
( 1
, 'Europe'
);
INSERT INTO regions VALUES
( 2
, 'Americas'
);
INSERT INTO regions VALUES
( 3
, 'Asia'
);
INSERT INTO regions VALUES
( 4
, 'Middle East and Africa'
);
REM ***************************insert data into the COUNTRIES table
Prompt ****** Populating COUNTIRES table ....
INSERT INTO countries VALUES
( 'IT'
, 'Italy'
, 1
);
INSERT INTO countries VALUES
( 'JP'
, 'Japan'
, 3
);
INSERT INTO countries VALUES
( 'US'
, 'United States of America'
, 2
);
INSERT INTO countries VALUES
( 'CA'
, 'Canada'
, 2
);
INSERT INTO countries VALUES
( 'CN'
, 'China'
, 3
);
INSERT INTO countries VALUES
( 'IN'
, 'India'
, 3
);
INSERT INTO countries VALUES
( 'AU'
, 'Australia'
, 3
);
INSERT INTO countries VALUES
( 'ZW'
, 'Zimbabwe'
, 4
);
INSERT INTO countries VALUES
( 'SG'
, 'Singapore'
, 3
);
INSERT INTO countries VALUES
( 'UK'
, 'United Kingdom'
, 1
);
INSERT INTO countries VALUES
( 'FR'
, 'France'
, 1
);
INSERT INTO countries VALUES
( 'DE'
, 'Germany'
, 1
);
INSERT INTO countries VALUES
( 'ZM'
, 'Zambia'
, 4
);
INSERT INTO countries VALUES
( 'EG'
, 'Egypt'
, 4
);
INSERT INTO countries VALUES
( 'BR'
, 'Brazil'
, 2
);
INSERT INTO countries VALUES
( 'CH'
, 'Switzerland'
, 1
);
INSERT INTO countries VALUES
( 'NL'
, 'Netherlands'
, 1
);
INSERT INTO countries VALUES
( 'MX'
, 'Mexico'
, 2
);
INSERT INTO countries VALUES
( 'KW'
, 'Kuwait'
, 4
);
INSERT INTO countries VALUES
( 'IL'
, 'Israel'
, 4
);
INSERT INTO countries VALUES
( 'DK'
, 'Denmark'
, 1
);
INSERT INTO countries VALUES
( 'HK'
, 'HongKong'
, 3
);
INSERT INTO countries VALUES
( 'NG'
, 'Nigeria'
, 4
);
INSERT INTO countries VALUES
( 'AR'
, 'Argentina'
, 2
);
INSERT INTO countries VALUES
( 'BE'
, 'Belgium'
, 1
);
REM ***************************insert data into the LOCATIONS table
Prompt ****** Populating LOCATIONS table ....
INSERT INTO locations VALUES
( 1000
, '1297 Via Cola di Rie'
, '00989'
, 'Roma'
, NULL
, 'IT'
);
INSERT INTO locations VALUES
( 1100
, '93091 Calle della Testa'
, '10934'
, 'Venice'
, NULL
, 'IT'
);
INSERT INTO locations VALUES
( 1200
, '2017 Shinjuku-ku'
, '1689'
, 'Tokyo'
, 'Tokyo Prefecture'
, 'JP'
);
INSERT INTO locations VALUES
( 1300
, '9450 Kamiya-cho'
, '6823'
, 'Hiroshima'
, NULL
, 'JP'
);
INSERT INTO locations VALUES
( 1400
, '2014 Jabberwocky Rd'
, '26192'
, 'Southlake'
, 'Texas'
, 'US'
);
INSERT INTO locations VALUES
( 1500
, '2011 Interiors Blvd'
, '99236'
, 'South San Francisco'
, 'California'
, 'US'
);
INSERT INTO locations VALUES
( 1600
, '2007 Zagora St'
, '50090'
, 'South Brunswick'
, 'New Jersey'
, 'US'
);
INSERT INTO locations VALUES
( 1700
, '2004 Charade Rd'
, '98199'
, 'Seattle'
, 'Washington'
, 'US'
);
INSERT INTO locations VALUES
( 1800
, '147 Spadina Ave'
, 'M5V 2L7'
, 'Toronto'
, 'Ontario'
, 'CA'
);
INSERT INTO locations VALUES
( 1900
, '6092 Boxwood St'
, 'YSW 9T2'
, 'Whitehorse'
, 'Yukon'
, 'CA'
);
INSERT INTO locations VALUES
( 2000
, '40-5-12 Laogianggen'
, '190518'
, 'Beijing'
, NULL
, 'CN'
);
INSERT INTO locations VALUES
( 2100
, '1298 Vileparle (E)'
, '490231'
, 'Bombay'
, 'Maharashtra'
, 'IN'
);
INSERT INTO locations VALUES
( 2200
, '12-98 Victoria Street'
, '2901'
, 'Sydney'
, 'New South Wales'
, 'AU'
);
INSERT INTO locations VALUES
( 2300
, '198 Clementi North'
, '540198'
, 'Singapore'
, NULL
, 'SG'
);
INSERT INTO locations VALUES
( 2400
, '8204 Arthur St'
, NULL
, 'London'
, NULL
, 'UK'
);
INSERT INTO locations VALUES
( 2500
, 'Magdalen Centre, The Oxford Science Park'
, 'OX9 9ZB'
, 'Oxford'
, 'Oxford'
, 'UK'
);
INSERT INTO locations VALUES
( 2600
, '9702 Chester Road'
, '09629850293'
, 'Stretford'
, 'Manchester'
, 'UK'
);
INSERT INTO locations VALUES
( 2700
, 'Schwanthalerstr. 7031'
, '80925'
, 'Munich'
, 'Bavaria'
, 'DE'
);
INSERT INTO locations VALUES
( 2800
, 'Rua Frei Caneca 1360 '
, '01307-002'
, 'Sao Paulo'
, 'Sao Paulo'
, 'BR'
);
INSERT INTO locations VALUES
( 2900
, '20 Rue des Corps-Saints'
, '1730'
, 'Geneva'
, 'Geneve'
, 'CH'
);
INSERT INTO locations VALUES
( 3000
, 'Murtenstrasse 921'
, '3095'
, 'Bern'
, 'BE'
, 'CH'
);
INSERT INTO locations VALUES
( 3100
, 'Pieter Breughelstraat 837'
, '3029SK'
, 'Utrecht'
, 'Utrecht'
, 'NL'
);
INSERT INTO locations VALUES
( 3200
, 'Mariano Escobedo 9991'
, '11932'
, 'Mexico City'
, 'Distrito Federal,'
, 'MX'
);
REM ****************************insert data into the DEPARTMENTS table
Prompt ****** Populating DEPARTMENTS table ....
REM disable integrity constraint to EMPLOYEES to load data
ALTER TABLE departments
DISABLE CONSTRAINT dept_mgr_fk;
INSERT INTO departments VALUES
( 10
, 'Administration'
, 200
, 1700
);
INSERT INTO departments VALUES
( 20
, 'Marketing'
, 201
, 1800
);
INSERT INTO departments VALUES
( 30
, 'Purchasing'
, 114
, 1700
);
INSERT INTO departments VALUES
( 40
, 'Human Resources'
, 203
, 2400
);
INSERT INTO departments VALUES
( 50
, 'Shipping'
, 121
, 1500
);
INSERT INTO departments VALUES
( 60
, 'IT'
, 103
, 1400
);
INSERT INTO departments VALUES
( 70
, 'Public Relations'
, 204
, 2700
);
INSERT INTO departments VALUES
( 80
, 'Sales'
, 145
, 2500
);
INSERT INTO departments VALUES
( 90
, 'Executive'
, 100
, 1700
);
INSERT INTO departments VALUES
( 100
, 'Finance'
, 108
, 1700
);
INSERT INTO departments VALUES
( 110
, 'Accounting'
, 205
, 1700
);
INSERT INTO departments VALUES
( 120
, 'Treasury'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 130
, 'Corporate Tax'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 140
, 'Control And Credit'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 150
, 'Shareholder Services'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 160
, 'Benefits'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 170
, 'Manufacturing'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 180
, 'Construction'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 190
, 'Contracting'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 200
, 'Operations'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 210
, 'IT Support'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 220
, 'NOC'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 230
, 'IT Helpdesk'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 240
, 'Government Sales'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 250
, 'Retail Sales'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 260
, 'Recruiting'
, NULL
, 1700
);
INSERT INTO departments VALUES
( 270
, 'Payroll'
, NULL
, 1700
);
REM ***************************insert data into the JOBS table
Prompt ****** Populating JOBS table ....
INSERT INTO jobs VALUES
( 'AD_PRES'
, 'President'
, 20000
, 40000
);
INSERT INTO jobs VALUES
( 'AD_VP'
, 'Administration Vice President'
, 15000
, 30000
);
INSERT INTO jobs VALUES
( 'AD_ASST'
, 'Administration Assistant'
, 3000
, 6000
);
INSERT INTO jobs VALUES
( 'FI_MGR'
, 'Finance Manager'
, 8200
, 16000
);
INSERT INTO jobs VALUES
( 'FI_ACCOUNT'
, 'Accountant'
, 4200
, 9000
);
INSERT INTO jobs VALUES
( 'AC_MGR'
, 'Accounting Manager'
, 8200
, 16000
);
INSERT INTO jobs VALUES
( 'AC_ACCOUNT'
, 'Public Accountant'
, 4200
, 9000
);
INSERT INTO jobs VALUES
( 'SA_MAN'
, 'Sales Manager'
, 10000
, 20000
);
INSERT INTO jobs VALUES
( 'SA_REP'
, 'Sales Representative'
, 6000
, 12000
);
INSERT INTO jobs VALUES
( 'PU_MAN'
, 'Purchasing Manager'
, 8000
, 15000
);
INSERT INTO jobs VALUES
( 'PU_CLERK'
, 'Purchasing Clerk'
, 2500
, 5500
);
INSERT INTO jobs VALUES
( 'ST_MAN'
, 'Stock Manager'
, 5500
, 8500
);
INSERT INTO jobs VALUES
( 'ST_CLERK'
, 'Stock Clerk'
, 2000
, 5000
);
INSERT INTO jobs VALUES
( 'SH_CLERK'
, 'Shipping Clerk'
, 2500
, 5500
);
INSERT INTO jobs VALUES
( 'IT_PROG'
, 'Programmer'
, 4000
, 10000
);
INSERT INTO jobs VALUES
( 'MK_MAN'
, 'Marketing Manager'
, 9000
, 15000
);
INSERT INTO jobs VALUES
( 'MK_REP'
, 'Marketing Representative'
, 4000
, 9000
);
INSERT INTO jobs VALUES
( 'HR_REP'
, 'Human Resources Representative'
, 4000
, 9000
);
INSERT INTO jobs VALUES
( 'PR_REP'
, 'Public Relations Representative'
, 4500
, 10500
);
REM ***************************insert data into the EMPLOYEES table
Prompt ****** Populating EMPLOYEES table ....
INSERT INTO employees VALUES
( 100
, 'Steven'
, 'King'
, 'SKING'
, '515.123.4567'
, TO_DATE('17-JUN-1987', 'dd-MON-yyyy')
, 'AD_PRES'
, 24000
, NULL
, NULL
, 90
);
INSERT INTO employees VALUES
( 101
, 'Neena'
, 'Kochhar'
, 'NKOCHHAR'
, '515.123.4568'
, TO_DATE('21-SEP-1989', 'dd-MON-yyyy')
, 'AD_VP'
, 17000
, NULL
, 100
, 90
);
INSERT INTO employees VALUES
( 102
, 'Lex'
, 'De Haan'
, 'LDEHAAN'
, '515.123.4569'
, TO_DATE('13-JAN-1993', 'dd-MON-yyyy')
, 'AD_VP'
, 17000
, NULL
, 100
, 90
);
INSERT INTO employees VALUES
( 103
, 'Alexander'
, 'Hunold'
, 'AHUNOLD'
, '590.423.4567'
, TO_DATE('03-JAN-1990', 'dd-MON-yyyy')
, 'IT_PROG'
, 9000
, NULL
, 102
, 60
);
INSERT INTO employees VALUES
( 104
, 'Bruce'
, 'Ernst'
, 'BERNST'
, '590.423.4568'
, TO_DATE('21-MAY-1991', 'dd-MON-yyyy')
, 'IT_PROG'
, 6000
, NULL
, 103
, 60
);
INSERT INTO employees VALUES
( 105
, 'David'
, 'Austin'
, 'DAUSTIN'
, '590.423.4569'
, TO_DATE('25-JUN-1997', 'dd-MON-yyyy')
, 'IT_PROG'
, 4800
, NULL
, 103
, 60
);
INSERT INTO employees VALUES
( 106
, 'Valli'
, 'Pataballa'
, 'VPATABAL'
, '590.423.4560'
, TO_DATE('05-FEB-1998', 'dd-MON-yyyy')
, 'IT_PROG'
, 4800
, NULL
, 103
, 60
);
INSERT INTO employees VALUES
( 107
, 'Diana'
, 'Lorentz'
, 'DLORENTZ'
, '590.423.5567'
, TO_DATE('07-FEB-1999', 'dd-MON-yyyy')
, 'IT_PROG'
, 4200
, NULL
, 103
, 60
);
INSERT INTO employees VALUES
( 108
, 'Nancy'
, 'Greenberg'
, 'NGREENBE'
, '515.124.4569'
, TO_DATE('17-AUG-1994', 'dd-MON-yyyy')
, 'FI_MGR'
, 12000
, NULL
, 101
, 100
);
INSERT INTO employees VALUES
( 109
, 'Daniel'
, 'Faviet'
, 'DFAVIET'
, '515.124.4169'
, TO_DATE('16-AUG-1994', 'dd-MON-yyyy')
, 'FI_ACCOUNT'
, 9000
, NULL
, 108
, 100
);
INSERT INTO employees VALUES
( 110
, 'John'
, 'Chen'
, 'JCHEN'
, '515.124.4269'
, TO_DATE('28-SEP-1997', 'dd-MON-yyyy')
, 'FI_ACCOUNT'
, 8200
, NULL
, 108
, 100
);
INSERT INTO employees VALUES
( 111
, 'Ismael'
, 'Sciarra'
, 'ISCIARRA'
, '515.124.4369'
, TO_DATE('30-SEP-1997', 'dd-MON-yyyy')
, 'FI_ACCOUNT'
, 7700
, NULL
, 108
, 100
);
INSERT INTO employees VALUES
( 112
, 'Jose Manuel'
, 'Urman'
, 'JMURMAN'
, '515.124.4469'
, TO_DATE('07-MAR-1998', 'dd-MON-yyyy')
, 'FI_ACCOUNT'
, 7800
, NULL
, 108
, 100
);
INSERT INTO employees VALUES
( 113
, 'Luis'
, 'Popp'
, 'LPOPP'