逝者如斯夫，不捨晝夜 所有的SQL都經過測試，可粘貼，可複製，有問題請各位大神指出...... 後續，會有視圖和索引，以及存儲過程的文章，客官們不要著急，耐心等待......... ...
逝者如斯夫，不捨晝夜
所有的SQL都經過測試，可粘貼，可複製，有問題請各位大神指出......
1 2 --約束 3 4 與表一起使用 5 6 約束不合理的數據，使其不能進入表中? 7 8 insert into stu values ('13','李小龍','一班','該學生成天練武，不愛學習','不知道念什麼中學'); 9 10 約束的類型 11 12 約束的種類（重要）? 13 1、主鍵約束? 14 表中能夠體現一行記錄的唯一標識，這樣的列就是主鍵? 15 特性：? 16 一張表中只能有一個主鍵約束? 17 主鍵約束可能作用在不止一個列上（聯合主鍵） 18 19 主鍵約束列中的值不能重覆? 20 主鍵約束列中的值不允許出現nul 21 22 如何創建主鍵? 23 關鍵字?primary?key? 24 創建表的時候直接創建主鍵? 25 直接在列上指定主鍵 26 27 28 create table haha( 29 hahaid number primary key, 30 hahaname varchar2(16), 31 hahadate date 32 ); 33 34 select * from haha ; 35 --–註意：不能指定聯合主鍵 36 建表語句後在指定主鍵 37 38 drop table haha; 39 create table haha( 40 hahaid number, 41 hahaname varchar2(16), 42 hahadate date, 43 constraint pk_haha_id primary key(hahaid) 44 ); 45 --做聯合主鍵 46 47 create table haha( 48 hahaid number, 49 hahaname varchar2(16), 50 hahadate date, 51 constraint pk_haha_id primary key(hahaid,hahaname) 52 ); 53 54 創建表之後追加主鍵 55 56 主鍵創建的規範：? 57 一般來說，做為主鍵的列，應該沒有任何的實際意義。? 58 一個列做為主鍵後，不應該有任何其他的意義。 59 60 61 62 63 外鍵約束? 64 關鍵字：foreign?key? 65 一個表的某一個列關聯另一個表的主鍵或者唯一鍵，? 66 這個列就是一個外鍵?emp中deptno? 67 創建一個外鍵 68 69 create table haha( 70 hid number, 71 hname varchar2(32), 72 deptno number, 73 constraint fk_haha_deptno foreign key(deptno) 74 references dept(deptno) 75 ); 76 --追加主鍵 77 78 create table haha( 79 hid number, 80 hname varchar2(32), 81 deptno number 82 ); 83 alter table haha 84 add constraint fk_haha_deptno foreign key(deptno) 85 references dept(deptno); 86 87 --3 唯一約束 88 表中的某一個類，其錄入的值不能互相重覆 89 90 --unique 91 alter table haha 92 add constraint un_haha_hname unique(hname); 93 94 95 主鍵約束和唯一約束的區別（重要）? 96 1）一張表中只能有一個主鍵，可以有多個唯一約束 97 98 99 create table xixi( 100 xxid number, 101 xname1 varchar2(16), 102 xname2 varchar2(16), 103 xname3 varchar2(16), 104 constraint pk_xx_id primary key(xxid), 105 constraint un_xx_x1 unique(xname1), 106 constraint un_xx_x2 unique(xname2) 107 ); 108 109 2)主鍵的值必須是非空，唯一約束可以為空且可以不止一個為空? 110 3）主鍵創建的索引是聚集索引，? 111 唯一約束創建索引是非聚集索引（可以不提） 112 113 --4、檢查性約束? 114 –關鍵字：check 115 116 alter table xixi 117 add(salary number); 118 alter table xixi 119 add constraint ck_xixi_sal check(salary > 0 120 and salary < 2000); 121 create table xixi( 122 xxid number, 123 xname1 varchar2(16), 124 xname2 varchar2(16), 125 xname3 varchar2(16), 126 salary number, 127 constraint pk_xx_id primary key(xxid), 128 constraint un_xx_x1 unique(xname1), 129 constraint un_xx_x2 unique(xname2), 130 constraint ck_xixi_sal check(salary > 0 131 and salary < 2000) 132 ); 133 134 –5、非空約束? 135 –只能在列上定義，不能利用constraint關鍵字聲明? 136 –not?null 137 138 create table xixi( 139 xxid number, 140 xname1 varchar2(16), 141 xname2 varchar2(16) not null, 142 xname3 varchar2(16) not null, 143 salary number, 144 constraint pk_xx_id primary key(xxid), 145 constraint un_xx_x1 unique(xname1), 146 constraint un_xx_x2 unique(xname2), 147 constraint ck_xixi_sal check(salary > 0 148 and salary < 2000) 149 ); 150 151 152 一個列上可以作用多個約束 153 啟用和禁用約束? 154 預設的情況是約束啟用狀態? 155 禁用約束 156 157 alter table xixi disable constraint ck_xixi_sal; 158 159 alter table xixi enable constraint ck_xixi_sal; 160 161 –啟用約束時，表中存在違反約束條件的數據時，約束無法啟用 162 163 164 --rownum 165 偽劣 166 167 select rownum , e.* from emp e ; 168 169 170 查詢工資前三的人 171 172 select rownum, e.* from emp e where rownum <= 3 order by sal desc; 173 174 錯誤? 175 查詢排在第3到第5的人?根據月薪 176 1）rownum表示的是排序之前的序號? 177 2）rownum在where中可以進行篩選，但是只能用<?<=?，? 178 其他的得不到結果 179 排前三 180 181 select rownum,t1.* from (select * from emp order by sal desc)t1 where rownum <=3; 182 183 184 --–查詢排在第3到第5的人?根據月薪 185 186 187 select rownum,t1.* from (select * from emp order by sal desc)t1 where rownum <=5 minus select rownum,t1.*from 188 (select * from emp order by sal desc)t1 where rownum <=2; 189 190 --分頁顯示 191 select t2.* from (select rownum as haha, t1.* 192 from (select * from emp order by sal desc) t1--排序 193 ) t2--將rownum變成一個實際的列 194 where haha between 3 and 5; 195 196 --rownum是oracle下的偽列?MySQL：limit ，SQL server top 197 198 199 200 201 子查詢： 202 203 什麼是子查詢： 204 205 單行單列子查詢 206 在一個查詢中嵌套的查詢被稱為子查詢 207 208 -- 問題一： 209 210 查詢和scott同一個部門的其他員工 211 212 select *from emp where deptno = (select deptno from emp where ename = 'SCOTT') and ename <> 'SCOTT'; 213 214 單行多列子查詢（瞭解） 215 查詢與scott同部門，同經理的其他員工 216 217 select * from emp e1 where (e1.deptno, e1.mgr) = (select e2.deptno, e2.mgr from emp e2 where e2.ename = 'SCOTT') 218 and e1.ename <> 'SCOTT'; 219 220 221 多行子查詢（多行單列子查詢） 222 多行多列基本看不到 223 當子查詢結果返回的不止一條記錄，外部查詢不能使用=，而是 224 使用in進行條件關聯 225 練習：查詢scott或ADAMS同一個部門的員工信息 226 註意：在多行子查詢下 = 效率高還是in效率 227 當確定子查詢結果只有一條時，外部使用=，效率高 228 當無法確定子查詢結果的條目數，外部推薦使用in， 229 為了保證正確 230 犧牲了效率保證了正確率 231 --查詢與scott獎金數不同的人 232 233 select * from emp where comm not in (select comm from emp where ename = 'SCOTT' or ename = 'ALLEN'); 234 235 236 重點 237 not in 238 如果not in中的值出現null，則不會查詢出任何結果 239 確保子查詢結果中一定沒有null 240 241 242 select * from emp 243 where comm not in (select nvl(comm, 0) from emp 244 where ename = 'SCOTT' 245 or ename = 'ALLEN'); 246 247 248 –子查詢可以使用再哪些語句中（嵌套子查詢） 249 1、where（重要） 250 查詢比scott月薪高的人 251 252 select * from emp 253 where sal > (select sal from emp where ename = 'SCOTT'); 254 255 --查詢與scott不在同一個部門的人 256 257 select * from emp 258 where deptno <> (select deptno from emp where ename = 'SCOTT'); 259 260 from 重要 261 262 select * from emp e,(select deptno,dname from dept) d 263 where e.deptno = d.deptno; 264 265 266 多表連接 267 268 select * from emp, dept; 269 270 笛卡爾積 271 兩個集合“相乘”（結合），等於兩個集合中的每個元素互相組成 272 一個新的元素。 273 274 --1等值連接 275 276 select * from emp, dept 277 where emp.deptno = dept.deptno; 278 279 280 --2、不等值連接 281 不使用等號進行表的連接 <= >= 也是不等值連接 282 查詢員工的姓名，月薪和工資級別 283 姓名和月薪（EMP） 工資級別（salgrade） 284 285 select e.ename, e.sal, s.grade 286 from emp e, salgrade s 287 where e.sal >= s.losal and e.sal <= s.hisal; 288 289 --3、外連接 290 查詢員工姓名和其所在部門的名稱，包括沒部門的員工 291 292 select * from emp e, dept d 293 where e.deptno = d.deptno or e.deptno is null; 294 295 296 此方法不行，因為所有的null都出來 297 外連接（左外連接/右外連接） 298 簡稱左連接 右連接 299 查詢員工姓名和其所在部門的名稱，包括沒部門的員工 300 左連接 oracle 下的連接 301 302 303 select * from emp e, dept d 304 where e.deptno = d.deptno(+); 305 306 307 右連接 308 查詢沒有員工的部門 309 310 select * from emp e, dept d 311 where e.deptno(+)= d.deptno 312 313 314 多表外連接 315 練習：查詢員工的姓名(empolyees)，部門名稱(departments)， 316 工作城市(locations)，沒部門的員工也要顯示出來 317 (+)在多表連接中的傳遞 318 319 select * from employees e,departments d, locations l 320 where e.department_id = d.department_id(+) 321 and d.location_id = l.location_id(+); 322 323 324 --4、自連接(難點) 325 通常用於模擬層次關係的數據（行政體系，組織結構） 326 查詢員工姓名e1.ename和其負責人的名字e2.ename 327 328 329 select e1.ename as 員工姓名 , e2.ename as 負責人姓名 330 from emp e1, emp e2 331 where e1.mgr = e2.empno ; 332 333 334 --SQL99標準 （重點） 335 join…on(…) 336 表A join 表B on(連接條件) where 篩選條件 337 338 339 等值連接 340 查詢員工姓名和其部門的名稱 341 342 select e.ename, d.dname 343 from emp e join dept d on(e.deptno = d.deptno); 344 345 --查詢月薪高於1000的員工姓名和其部門的名稱 346 347 select e.ename, d.dname 348 from emp e join dept d on(e.deptno = d.deptno) 349 where e.sal > 1000; 350 351 352 不等值連接 353 查詢員工的姓名，月薪和工資級別 354 姓名和月薪（EMP） 工資級別（salgrade） 355 356 357 select e.ename, e.sal, s.grade 358 from emp e join salgrade s 359 on(e.sal between s.losal and s.hisal); 360 361 362 自連接 363 查詢員工姓名和其經理的姓名 364 365 select e1.ename, e2.ename 366 from emp e1 join emp e2 on(e1.mgr = e2.empno ); 367 368 369 370 外連接 371 左連接(常用) 372 hr環境下，查詢員工的姓名，部門名稱，包含沒有部門的員工 373 374 select e.first_name, d.department_name 375 from employees e left join departments d 376 on(e.department_id = d.department_id); 377 378 右連接（意義不大，少用） 379 380 select e.first_name, d.department_name 381 from employees e right join departments d 382 on(e.department_id = d.department_id); 383 384 全連接 385 386 select * 387 from employees e, departments d 388 where e.department_id(+) = d.department_id(+); 389 390 --錯誤的語句，oracle做不了全連接 391 SQL99標準寫法 392 393 select * 394 from employees e full join departments d 395 on(e.department_id = d.department_id); 396 397 398 399 --集合 400 401 union與union all關鍵字 402 union 去掉重覆的記錄 403 union 預設按照第一列進行排序 404 405 select ename, sal from emp where sal > 1000 406 union select ename, sal from emp where sal < 1500; 407 408 union all 409 不排序任何列，包含重覆的記錄 410 411 select ename, sal from emp where sal > 1000 412 union all select ename, sal from emp where sal < 1500; 413 414 交集 intersect 取交集 415 416 select ename, sal from emp where sal > 1000 intersect select ename, sal from emp where sal < 1500; 417 418 相減 419 420 select ename, sal from emp where sal > 1000 421 minus select ename, sal from emp where sal > 1500; 422 423 424 交並集的操作重要條件：（重要） 425 組成集合的多個SQL語句，其查詢結果必須類型一致， 426 而且應該意義相同，這些SQL語句應該有相同的結構 427 類型是一致，但是沒有意義 428 429 430 select ename, sal from emp where deptno = 10 431 union select ename, comm from emp 432 where deptno = 10; 433 434 類型不一致的，語法錯誤 435 436 select ename, sal from emp where deptno = 10 437 union select ename, hiredate from emp where deptno = 10; 438 439 --不相同的結構，語法錯誤 440 select ename, sal 441 from emp where deptno = 10 442 union select ename, sal, comm from emp where deptno = 10;
後續，會有視圖和索引，以及存儲過程的文章，客官們不要著急，耐心等待.........
