Java數據庫基礎 · 漸入佳境的Java學習之路

## Java數據庫基礎--以SQL?Server為例 ### sql?server數據庫基本概念使用文件保存數據存在幾個缺點： 1、文件的安全性問題； 2、文件不利于查詢和對數據的管理； 3、文件不利于存放海量數據 4、文件在程序中控制不方便。 ? ### 數據庫的定義(1) 嚴格地說，數據庫是“按照數據結構來組織、存儲和管理數據的倉庫”。在經濟管理的日常工作中，常常需要把某些相關的數據放進這樣的“倉庫”，并根據管理的需要進行相應的處理。例如，企業或事業單位的人事部門常常要把單位職工的基本情況(職工號、姓名、年齡、性別、籍貫、工資、簡歷等)存放在表中，這張表就可以看成一個數據庫。有了這個“數據倉庫”我們就可以根據需要隨時查詢某職工的基本情況，也可以查詢工資在某個范圍內的職工人數等等。這些工作如果都能在計算機上自動進行，那我們的人事管理就可以達到極高的水平。此外，在財務管理、倉庫管理、生產管理中也需要建立眾多的這種“數據庫”，使其可以利用計算機實現財務、倉庫、生產的自動化管理。 ? ### 數據庫的定義(2) J.Martin給數據庫下了一個比較完整的定義：數據庫是存儲在一起的相關數據的集合，這些數據是結構化的，無有害的或不必要的冗余，并為多種應用服務；數據的存儲獨立于使用它的程序；對數據庫插入新數據，修改和檢索原有的數據均能按一種公用的和可控制的方式進行。當某個系統中存在結構上完全分開的若干個數據庫時，則該系統包含一個“數據庫集合”。 ? ### 數據庫的基本結構數據庫的基本結構分三個層次，反映了觀察數據庫的三種不同角度。 1、物理數據層 ????它是數據庫的最內層，是物理存貯設備上實際存儲的數據的集合。這些數據的原始數據，是用戶加工的對象，由內部模式描述的指令操作處理的位串、字符和字組成。 2、概念數據層 ????它是數據庫的中間一層，是數據庫的整體邏輯表示。指出了每個數據的邏輯定義及數據間的邏輯聯系，是存貯記錄的集合。它所涉及的是數據庫所有對象的邏輯關系，而不是它們的物理情況，是數據庫管理員概念下的數據庫。 3、邏輯數據層 ????它是用戶所看到和使用的數據庫，表示了一個或一些特定用戶使用的數據集合，即邏輯記錄的集合。數據庫不同層次之間的聯系是通過映射進行轉換的。 ? ### 數據庫的基本特點 1、實現數據共享 ????數據共享包含所有用戶可同時存取數據庫中的數據，也包括用戶可以用各種方式通過接口使用數據庫，并提供數據共享。 2、減少數據的冗余度 ????同文件系統比，數據庫實現了數據共享，從而避免了用戶各自建立應用文件。減少了大量重復數據，減少了數據冗余，維護了數據的一致性 3、數據實現集中控制 ????文件管理方式中，數據處于一種分散的狀態，不同的用戶或同一用戶在不同處理中其文件之間毫無關系。利用數據庫可對數據進行集中控制和管理，并通過數據模型表示各種數據的組織以及數據間的聯系。 4、數據一致性和可維護性，以確保數據的安全性和可靠性。 5、故障恢復 ? ### 目前主流數據庫微軟： Sql?Server和Access 瑞典MySQL： AB公司MySql IBM公司： DB2和Informix 美國Sybase公司： Sybase 美國Oracle公司： Oracle ? 數據庫選擇： 1、成本；2、功能；3、并發性要求；4、安全性； ? ? ### sql?server介紹(1) SQL是英文Structured?Query?Language的縮寫，意思為結構化查詢語言。SQL語言的主要功能就是同各種數據庫建立聯系，進行溝通。按照ANSI(美國國家標準協會)的規定，SQL被作為關系型數據庫管理系統的標準語言。SQL語句可以用來執行各種各樣的操作，例如更新數據庫中的數據，從數據庫中提取數據等。目前，絕大多數流行的關系型數據庫管理系統，如Oracle、Sybase、Microsoft?SQL?Server、Access等都采用了SQL語言標準。 ? ### sql?server介紹(2) SQL?Server是一個關系數據庫管理系統。它最初是由Microsoft?Sybase和Ashton-Tate三家公司共同開發的，于1988年推出了第一個OS/2版本。在Windows?NT推出后，Microsoft與Sybase在SQL?Server的開發上就分道揚鑣了，Microsoft將SQL?Server移植到Windows?NT系統上，專注于開發推廣SQL?Server的Windows?NT版本。Sybase則較專注于SQL?Server在UNIX操作系統上的應用。 ? ? 企業管理器的使用(PS:比較簡單，不再贅述) ? ### 查詢分析器的使用(1) ????企業管理器給用戶提供了一個很方便的圖形界面管理工具，用起來感覺直觀方便，可是它也有缺點，當一個表的記錄非常大的時候，對表的各種操作，都顯得不方便了，比如： 1、要求從1000行記錄中查詢是否存在名為“孫小明”的人 2、要求把1000行記錄中工資低于100的人，工資增加10% 3、要求把年齡大于30的人從數據表中刪除這時，會發現使用企業管理器是很不方便的所以微軟為我們提供另外一個操作數據庫的工具，查詢分析器。查詢分析器可以非常方便的完成上述任務。 ? ### 查詢分析器的使用(2)--sql語句 SQL全稱是“結構化查詢語言(Structured?Query?Language)”。 SQL(Structured?Query?Language)是一種數據庫查詢和程序設計語言，用于存取數據以及查詢、更新和管理關系數據庫系統。結構化查詢語言(Structured?Query?Language)最早是IBM的圣約瑟研究實驗室為其關系數據庫管理系統SYSTEM?R開發的一種查詢語言，它的前身是SQUARE語言。SQL語言結構簡潔，功能強大，簡單易學，所以自從IBM公司1981年推出以來，SQL語言得到了廣泛的應用。如今無論是像Oracle、Sybase、Informix、SQL?Server這些大型的數據庫管理系統，還是像Visual?Foxpro、PowerBuilder這些PC上常用的數據庫開發系統，都支持SQL語言作為查詢語言。 ? ? ### 查詢分析器的使用(3)--sql語句 SQL語言包含4個部分：數據定義語言(DDL)，例如：CREATE、DROP、ALTER等語句。數據操作語言(DML)，例如：INSERT、UPDATE、DELETE語句。數據查詢語言(DQL)，例如：SELECT語句。數據控制語言(DCL)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等語句。 ? ### 查詢分析器的使用(4)--實例 ? ~~~ --創建數據庫 create database LiangshanHero; --使用數據庫 use LiangShanHero; --創建表 create table hero ( heroId int, --排名 heroName varchar(50), --名字 heroNickName varchar(50), --外號 sex char(2) --性別 ); alter table hero add sal int; --添加薪水屬性 --使用SQL語句來添加數據 insert into hero values(1, '宋江', '及時雨', '男'); insert into hero values(2, '盧俊義', '玉麒麟', '男'); insert into hero values(3, '吳用', '智多星', '男'); insert into hero values(4, '公孫勝', '入云龍', '男'); --使用SQL語句來修改數據 update hero set sal = 20000 where heroid = 1; update hero set sal = 15000 where heroid = 2; update hero set sal = 10000 where heroid = 3; update hero set sal = 13000 where heroid = 4; --使用SQL語句來查詢數據 select * from hero; --查詢工資低于的人 select * from hero where sal < 15000; --把工資低于的人，工資提高% update hero set sal = sal * 1.1 where sal < 15000; --刪除性別為女的人 delete from hero where sex = '女'; ~~~ ### 表的管理--表名和列的命名規則 1、必需以字母,_開頭，例如：ta,_ta 2、長度不能超過128個字符 3、不要使用sql?server的保留字 4、只能使用如下字符A-Z，a-z，0-9，$，#，_等 ? ### 表的管理--支持的數據類型 ### 字符型(非unicode編碼) char?定長?最大8000字符(非unicode編碼) char(10)'Switch'前5個字符放'Switch'，后添5個空格補全 varchar?變長?最大8000字符(非unicode編碼) varchar(10)'Switch'sql?server分配5個字符，這樣可以節省空間 ntext可變長度Unicode數據的最大長度為2的30次方-1(1,073,741,823)個字符 text可變長度非Unicode數據的最大長度為2的31次方-1(2,147,483,647)個字符區別： 1、text是字節格式存儲英文的，也可以存中文但有時候會顯示成亂碼 2、ntext是多字節格式存儲unicode的，也就是存儲各種文字用的。在什么時候使用char型而不使用varchar,在知道字段定長固定時就使用char ? ### 字符型(unicode編碼) nchar?定長?最大4000字符(unicode編碼) nchar(20)'Switch'前10個字符放'Switch'，后添10個空格補全 nvarchar?變長?最大4000字符(unicode編碼) nvarchar(20)'Switch'sql?server分配10個字符，這樣可以節省空間特別說明： 1、一般帶有漢字的字段用nvarchar，全英文或符號的用varchar，因為nvarchar為unicode字符集，該類型的字段無論是單個字母還是單個漢字都占兩個字節，而varchar，字母占一個字節，漢字占兩個，nvarchar處理漢字或其它unicode字符集的速度要比varchar字段快。 2、如果有一些特殊字符在nvarchar中沒有的，如日文的某些字符，那當然只能選合適的代碼頁用varchar了，而且這些特有語言特有的字符轉換到nvarchar會消失 ? ### 數字型 1、bit?范圍?0到1 2、int?范圍?負的2的31次方到正的?2的31次方-1 3、bigint?范圍?負的2的63次方到正的?2的63次方-1 4、float?存放小數，不推薦使用 5、numeric?小數強烈建議?如果要去存放小數最好使用numeric ? ### 日期類型 datetime(表示日期) timestamp(時間戳) 一般情況下?用datetime表示日期 --sql?server為我們提供一個專門的時間函數getdate() ~~~ create table ta--建表 (bir datetime)--字段 insert into ta values(getdate())--添加時間 ~~~ ### 圖片 image保存圖片，但是用的很少，一般用路徑保存圖片，在軟件公司往往使用圖片服務器和圖床技術 ? ### 視頻 binary字段可以存放，但是往往將視頻文件保存在文件服務器上，sql?server中只保留文件路徑，這樣存取效率高。 ? ? ### 表的管理--添加數據插入部分字段(語法) insert?into?表名?(字段名1,字段名2,...)?values?(對應字段數據1,對應字段數據2,...) ? ### 表的管理--修改數據修改一個字段(語法) update?表名?set?字段名='新值'?where?字段='值' 例： ? ~~~ update student set sex='女' where xh='A001' update clerk set age=38 where cleName='賈政' ~~~ 修改多個字段(語法) update?表名?set?字段名1='新值',字段名2='新值'...?where?字段='值' 例： ? ~~~ update student set sex='男',birthday='1980-04-01' where xh='A001' update clerk set cleName='薛蟠',age=40 where cleId=2 ~~~ 修改含有null值的數據(語法) update?表名?set?字段名='新值'?where?字段?is?null 例： ? ~~~ update clerk set cleName='薛寶釵' where age is null ~~~ ### 表的管理--刪除數據刪除全部數據 delete?from?表名 ? 刪除指定數據 delete?from?表名?where?字段名='值' ? 刪除多個指定數據 delete?from?表名?where?字段名1='值'?and?字段名2='值'?and或or?... ? ### 表的管理--修改表添加一個字段 ? ~~~ ALTER TABLE distributors ADD COLUMN address varchar(30); ~~~ 修改字段的類型/或是名字(不能有數據) ~~~ ALTER TABLE distributors ALTER COLUMN address TYPE varchar(80), ALTER COLUMN name TYPE varchar(100); ALTER TABLE distributors RENAME COLUMN address TO city; ~~~ 刪除一個字段 ? ~~~ ALTER TABLE distributors DROP COLUMN address RESTRICT; ~~~ 修改表的名字 ~~~ ALTER TABLE distributors RENAME TO suppliers; ~~~ 刪除表 drop?table?表名; ### 主鍵和外鍵主鍵，一張表中只能有一個主鍵 1、主鍵(primary?key)，不能夠重復出現； 2、主鍵必需給值，換言之主鍵不能為null 3、主鍵可以修改，但不能修改為同名的，同時要明確字段不有重復，否則報錯。 ? 外鍵 1、外鍵只能指向主鍵 2、外鍵和主鍵的數據類型要一致 ? ### 員工管理系統 PS:之后很多例子都是基于下面所建數據庫和表的 ~~~ ---創建dept表 create table dept ( deptno int primary key, --部門編號 dname nvarchar(30), --部門名稱 loc nvarchar(30) --所在地 ); --創建emp表 create table emp ( empno int primary key, --員工編號 ename nvarchar(30), --員工姓名 job nvarchar(30), --員工職位 mgr int, --上級編號 hiredate datetime, --入職日期 sal numeric(8, 2), --薪水 comm numeric(8, 2), --獎金 deptno int foreign key references dept(deptno) --deptno是外鍵 ); --外鍵 --①外鍵引用的鍵必須是主鍵 --②外鍵的數據類型必須和引用的主鍵數據類型一致 --向dept表中添加數據 insert into dept values (10,'accounting','new york'); insert into dept values (20,'research','dallas'); insert into dept values (30,'sales','chicago'); insert into dept values (40,'operations','boston'); --向emp表中添加數據 insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7369,'smith','clerk',7902,'1980-12-17',800.00,20); insert into emp values(7499,'allen','salesman',7698,'1981-2-20',1600.00,300.00,30); insert into emp values(7521,'ward','salesman',7698,'1981-2-22',1250.00,500.00,30); insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7566,'jones','manager',7839,'1981-4-2',2975.00,20); insert into emp values(7654,'martin','salesman',7698,'1981-9-28',1250.00,1400.00,30); insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7698,'blake','manager',7839,'1981-5-1',2850.00,30); insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7782,'clark','manager',7839,'1981-6-9',2450.00,10); insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7788,'scott','analyst',7566,'1987-4-19',3000.00,20); insert into emp (empno,ename,job,hiredate,sal,deptno) values (7839,'king','president','1981-11-17',5000.00,10); insert into emp values (7844,'turner','salesman',7698,'1981-9-8',1500.00,0.00,30); insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7876,'adams','clerk',7788,'1987-5-23',1100.00,20); insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7900,'james','clerk',7698,'1981-12-3',950.00,30); insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7902,'ford','analyst',7566,'1981-12-3',3000.00,20); insert into emp (empno,ename,job,mgr,hiredate,sal,deptno) values (7934,'miller','clerk',7782,'1982-1-23',1300.00,10); ~~~ ### 表的簡單查詢 ~~~ --表的簡單查詢 --查詢部門信息 select * from dept; --查詢員工信息 select * from emp; --查詢SMITH的薪水，工作，所在部門 select sal, job, deptno from emp where ename = 'SMITH'; --查詢員工表中有多少個部門 --distinct會消除全部屬性一樣的行,只保留一行 select distinct deptno from emp; select distinct deptno, ename from emp; --顯示每個員工的年工資 --as后面是別名，可以不加as，別名也可以不加引號 --四則運算，空值問題,用isnull()解決 select ename, (sal * 12) + isnull(comm * 12, 0) as '年工資' from emp; --顯示工資高于3000的員工 select * from emp where sal > 3000; --查找.1.1以后入職的員工 select * from emp where hiredate > '1982-1-1'; --顯示工資在到的員工 select * from emp where sal > 2000 and sal < 2500; --between A and B表示A >= X <= B select * from emp where sal between 2001 and 2499; --顯示首字符為S的員工的姓名和工資 select ename, sal from emp where ename like 'S%'; --顯示第三個字符是O的員工姓名和工資 select ename, sal from emp where ename like '__O%'; --顯示empno為123,345,800的員工 select * from emp where empno = 123 or empno = 345 or empno = 800; select * from emp where empno in(123, 345, 800); --查詢沒有上級的員工 select * from emp where mgr is null; --查詢工資高于或者是崗位為MANAGER的員工，并且姓名首字母為J select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%'; --按照工資從低到高顯示員工 --order by ASC 默認升序 --order by DESC 降序 select * from emp order by sal ASC; --按照入職先后順序顯示員工 select * from emp order by hiredate ASC; --按照名字升序排列 select * from emp order by ename ASC; --按照部門升序，員工的工資降序顯示 --order by 可以根據不同的字段排序，order by a, b; select * from emp order by deptno ASC, sal DESC; --算出每個人的年薪，并按照升序顯示 select ename, sal * 13 + isnull(comm * 13, 0) as '年薪' from emp order by '年薪' ASC; ~~~ ### 表的復雜查詢數據分組?-max(最大),min(最小),avg(平均),sum(和),count(統計) group?by和having子句 group?by用于對查詢的結果分組統計 having子句用于限制分組顯示結果 ~~~ --表的復雜查詢 --顯示所有員工中工資最高的員工 select ename, sal from emp where sal = ( select max(sal) as '最高薪水' from emp); --顯示所有員工中工資最低的員工 select ename, sal from emp where sal = ( select min(sal) as '最低薪水' from emp); --顯示所有員工的平均工資和總工資 select avg(sal) as '平均工資', sum(sal) as '總工資' from emp; --把高于平均工資的員工的名字和工資顯示 select ename, sal from emp where sal > (select avg(sal) from emp); --顯示員工名并顯示平均工資 select ename, sal,(select avg(sal) from emp) as '平均工資' from emp where sal > (select avg(sal) from emp ); --計算一共有多少員工 select count(*) as '員工總數' from emp; --顯示每個部門的平均工資和最高工資 select deptno,avg(sal) as '平均工資', max(sal) as '最高工資' from emp group by deptno; --顯示每個部門的每個崗位的平均工資和最低工資 select deptno, job, avg(sal) as '平均工資', min(sal) as '最低工資' from emp group by deptno, job order by deptno ASC, '平均工資' DESC; --顯示平均工資低于2000的部門號和平均工資 --group by ... having ... having是對分組后的數據進行篩選 select deptno, avg(sal) as '平均工資' from emp group by deptno having avg(sal) < 2000; ~~~ 對數據分組的總結 1、分組函數只能出現在選擇列表，having、order?by子句中 2、如果在select語句中同時包含有group?by，having，ovrder?by那么他們的順序是group?by，having，order?by 3、在選擇列中如果有列、表達式、和分組函數，那么這些列和表達式必需有一個出現在group?by子句中，否則就會出錯 4、如果想在分組情況下，選擇列中顯示某列，那么這行必須要在group?by子句中，否則會出錯如：select?deptno,avg(sal),max(sal)?from?emp?group?by?deptno?having?avg(sal)<2000 這里deptno就一定要出現在group?by中 ### 表的復雜查詢--多表查詢 ????多表查詢是指基于兩個或兩個以上的表或是視圖的查詢，在實際應用中，查詢單個表可能不能滿足需求，(如顯示sales部門位置和其員工的姓名)，這種情況下需要使用到(dept表和emp表) ~~~ --多表查詢 --如果多張表都有相同名字的字段，則需要帶表名(別名) --顯示sales部門位置和其員工的姓名 select loc, ename from dept, emp where dname = 'sales' and dept.deptno = emp.deptno; --顯示員工名，部門名和部門編號 --連接的表中有相同的字段，需要帶表名(別名) select dept.deptno, dname, ename from dept, emp where dept.deptno = emp.deptno; --顯示部門號為10的部門名，員工名和工資 select d.deptno, e.ename, e.sal from emp e, dept d where e.deptno = d.deptno and d.deptno = 10; --顯示員工名，員工工資及所在部門的名稱，并按部門排序 select e.ename, e.sal, d.dname from emp e, dept d where e.deptno = d.deptno order by d.deptno; ~~~ ### 表的復雜查詢--自連接查詢自連接是指在同一張表的連接查詢 ~~~ --自關聯查詢，內連接 --顯示'FORD'的上級領導的姓名 select e2.ename from emp e1, emp e2 where e1.mgr = e2.empno and e1.ename = 'FORD'; --顯示每個員工的名字和他的上級姓名 select e1.ename,e2.ename from emp e1 emp e2 where e1.mgr = e2.empno; ~~~ ### 表的復雜查詢--子查詢子查詢是批嵌入在其它sql語句中的select語句，也叫嵌套查詢 ? #### 單行子查詢單行子查詢是指子查詢只返回一行數據的子查詢語句 ~~~ --顯示與SMITH同一部門的所有員工 select * from emp where deptno = (select deptno from emp where ename = 'SMITH'); ~~~ #### 多行子查詢多行子查詢指子查詢返回多行數據的子查詢 ~~~ --查詢和部門10的工作相同的員工的名字 select ename from emp where job = any(select distinct job from emp where deptno = 10); select ename from emp where job in(select distinct job from emp where deptno = 10); --查詢和部門的工作相同的員工的名字并且不包括號部門的員工 select ename from emp where job in(select distinct job from emp where deptno = 10) and deptno != 10; ~~~ #### 在from子句中使用子查詢當在from子句中使用子查詢時，該子查詢會被作為一個臨時表來對待，當在from子句中使用子查詢時，必需給子查詢指定別名 ~~~ --顯示高于部門平均工資的員工的信息 select emp.ename, emp.deptno, emp.sal from emp, (select deptno, avg(sal) as '部門平均工資' from emp group by deptno) e2 where emp.deptno = e2.deptno and emp.sal > e2.部門平均工資; ~~~ ### 分頁查詢 ~~~ --顯示第5個到第10個入職的員工(按照時間的先后順序) --top后面的數表示要取出幾條記錄 select top 6 * from emp where empno not in (select top 4 empno from emp order by hiredate) order by hiredate; --顯示第11個到第13個入職的員工(按照時間的先后順序) select top 3 * from emp where empno not in (select top 10 empno from emp order by hiredate) order by hiredate; --顯示第5個到第9個員工(按照薪水的降序) select top 5 * from emp where empno not in (select top 4 empno from emp order by sal DESC) order by sal DESC; --演示分頁性能，PS:可以試試看自己電腦性能怎樣 --第一步：建一個表 --第二步：利用一條語句成指數增長插入數據 --第三步：測試查詢全部的時間 --第四步：測試分頁查詢的時間 --第一步：建表 create table test ( tId int primary key identity(1, 1), --自增主鍵 tName varchar(30), --賬號 tPass varchar(30) --密碼 ); --第二步：插入數據 insert into test values('test','test'); --指數增長復制插入,一共插入w+記錄 insert into test(tName, tPass) select tName,tPass from test; --第三步：查詢全部的時間 --查詢全部信息用時:45s select * from test; --查詢tId 用時:39s select tId from test; --第四步：查詢分頁的時間 --查詢第100-105的數據按照id排序用時:0.1s select top 6 * from test where tId not in (select top 99 tId from test); --查詢第500000-550000的數據按照id排序用時:6s select top 50001 * from test where tId not in (select top 499999 tId from test); --刪除一張表的重復記錄 --把test的記錄distincth后的結果，放入#temp select distinct tName, tPass into #temp from test --清除test中數據表 delete from test --把#temp表的數據(沒有重復記錄),插入到test表中 insert into test(tName, tPass) select tName, tPass from #temp --刪除#temp drop table #temp; ~~~ ### 表的復雜查詢--外連接查詢 ~~~ --顯示每個員工和他上級的名字，沒有上級的人，也要顯示出來 --左外連接:指左邊的表的記錄全部顯示，如果沒有匹配的記錄就用NULL填寫 select e1.ename as '下級', e2.ename as '上級' from emp e1 left join emp e2 on e1.mgr = e2.empno; --右外連接:指右邊的表的記錄全部顯示，如果沒有匹配的記錄就用NULL填寫 select e1.ename as '下級', e2.ename as '上級' from emp e1 right join emp e2 on e1.mgr = e2.empno; ~~~ ### 維護數據的完整性--約束約束用于確保數據庫數據滿足特定的規則。在sql?server和oracle中，約束包括：not?null、unique，primary?key，foreign?key和check五種 ? ### 維護數據的完整性--使用 ### not?null(非空) 如果在列上定義了not?null，那么當插入數據時，必需為列提供數據。 ~~~ --約束機制--not null(非空) --創建一張表 create table test1 ( test1Id int primary key identity(1,1), testname varchar(30) not null,--not null不能為空 testpass varchar(30) not null, testage int --不寫代表可以為空 ) create table test1 ( test1Id int primary key identity(1,1),--identity(1,1)自增長1條記錄 testname varchar(30), testpass varchar(30), testage int --不寫代表可以為空 ) --刪除表 drop table test1 --向表插入數據 insert into test1 (testage) values (3) insert into test1 (testname,testpass,testage) values ('','',5)--''空與null空是不一樣的 --查詢表內容 select * from test1 ~~~ ### unique(唯一)(一張表中可以有多個) 當定義了唯一約束后，該列值是不能重復的，但是可以為null,并只能有一個空值 ~~~ --約束機制--unique(唯一) --建表 create table test2 ( test2Id int primary key identity(1,1),--identity(1,1)自增長1條記錄 testname varchar(30) unique, --unique唯一的，數據不允許重復,但可以為空 testpass varchar(30), testage int --不寫代表可以為空 ) insert into test2 (testname,testpass,testage) values ('aa','123',45) insert into test2 (testpass,testage) values ('123',45) select * from test2 ~~~ ### primary?key(主鍵)(一張表中只可以有一個主鍵) 用于唯一的標示表行的數據，當定義主鍵約束后，該列不但不能重復而且不能為null 需要說明的是：一張表最多只能有一個主鍵，但是可以有多個unqiue約束。表可以有復合主鍵，有多個列構成一個主鍵 ~~~ --復合主鍵 create table test3 (testId int, testname varchar(30), testpass varchar(30), testage int, primary key (testId,testname)--復合主鍵，需單獨聲明 ) --行級定義和表級定義 create table test4 (testId int,--在字段中定義主鍵為行級定義.例：testId int primary key testname varchar(30), testpass varchar(30), testage int, primary key (testId,testname)--復合主鍵，需單獨聲明為表級定義 ) ~~~ ### foreign?key(外鍵)(外鍵在從表上，要配合主表，但主表要有主鍵或unique約束) 用于定義主表和從表之間的關系。外鍵約束要定義在從表上，主表則必需具有主鍵約束或是unique約束，當定義外鍵約束后，要求外鍵列數據必需在主表的主鍵列存在或是null ? ### check(強制條件) 用于強制行數據必需滿足的條件，假定在sal列上定義了check約束，并要求sal列值在1000-2000之間如果不再1000-2000之間就會提示出錯。 ~~~ --check(強制條件) create table test5 (testId int, testname varchar(30), testpass varchar(30), sal int check(sal>=1000 and sal<=2000),--規定sal的值在1000-2000之間 ) insert into test5 values (4,'aa','aa',1200)--可以加入sal值在范圍之內 insert into test5 values (5,'bb','bb',2200)--不可加入sal值在范圍之外 select * from test5 ~~~ ### default使用(默認值) ~~~ --default使用 create table mes (mesId int primary key identity(1,1), mesccn varchar(2000) not null, mesDate datetime default getdate()--在不指定值是default可以直接取默認值，也可以由用戶指定值 ) insert into mes(mesccn) values('abc') insert into mes(mesccn,mesDate) values('cba','1976-1-6') select * from mes ~~~ ### 商店售貨系統表設計案例 ????現有一個商店的數據庫，記錄客戶及其購物情況，由下面三個表組成：商品goods(商品號goodsId，商品名goodsName，單價unitprice，商品類別category，供應商provider)；客戶customer(客戶號customerId，姓名name，住址address，電郵email，性別sex，身份證cardId)；購買purchase(客戶號customerId，商品號goodsId，購買數量nums)；請使用sql語言完成下列功能： 1、建表，在定義中要求聲明： 1、每個表的主外鍵； 2、客戶的姓名不能為空值； 3、單價必需大于0，購買數量大于0； 4、電郵不能夠重復； 5、客戶的性別必需是男或者女，默認為男 6、商品類別是'食物'，'日用品' ~~~ --商店收貨系統表設計 --goods表 create table goods ( goodsId nvarchar(50) primary key, --商品號 goodsName nvarchar(100) not null, --商品名 unitprice numeric(8, 2) check(unitprice > 0), --單價 category nvarchar(3) check(category in('食物', '日用品')), --商品類別,只能在食物或者日用品中 provider nvarchar(50) --供應商 ); --customer表 create table customer ( customId nvarchar(50) primary key, --客戶號 cusname nvarchar(50) not null, --客戶名 address nvarchar(100), --地址 email nvarchar(100) unique, --電子郵件 sex nchar(1) check(sex in('男','女')) default '男', --性別 cardId nvarchar(18) --身份證號 ); --purchase表 create table purchase ( customId nvarchar(50) foreign key references customer(customId), --客戶號 goodsId nvarchar(50) foreign key references goods(goodsId), --商品號 nums int check(nums >0) --數量 ); ~~~ ### 數據庫的備份和恢復 ### 使用企業管理器完成備份和恢復使用企業管理器有兩種方式完成備份和恢復 #### 1、分離/附加 ????分離完后，請到sql?server安裝的目錄下去找兩個文件數據庫名.mdf和數據庫名.ldf，這兩個文件就是分離后的文件，數據庫分離后，該數據庫就不能再使用了。 ????附加是指，當用戶需要重新使用某個分離的數據庫時進行的操作，就是讓sql?server數據庫重新關聯該數據庫。 ? #### 2、備份/恢復 ????備份數據庫是指，把某個數據庫文件從sql?server中備份出來，這樣用戶可以根據需要再使用(用于恢復、復用..)，備份數據庫不會影響到源數據庫的使用 ????恢復數據庫是指，當源數據庫因為某種原因(比如源數據庫毀壞、數據丟失、系統崩潰)需要恢復時進行的操作 ? ### 使用查詢分析器完成備份和恢復用企業管理器完成對數據庫的備份和恢復簡單直觀，同樣也可以在查詢分析器中完成類似的任務。而且這樣的好處是在主語言比如JAVA，C++連接好數據庫之后，可以實現對數據庫的定時備份、恢復等。 ~~~ --備份數據庫 --語法：backup database 數據庫名 to disk='存儲路徑' backup database LiangshanHero to disk='C:/LiangshanHero.bak'; --恢復數據庫 --語法：restore database 數據庫名 from disk='讀取路徑' restore database LiangshanHero from disk='C:/LiangshanHero.bak'; --新建數據庫 --語法：create datebase 數據庫名 create database LiangshanHero; --刪除數據庫 --語法：drop database 數據庫名 drop database LiangshanHero; ~~~ ----------參考《韓順平.循序漸進學.java.從入門到精通》 ----------參考《JDK_API_1_6_zh_CN》 Java學習筆記--導航[http://blog.csdn.net/q547550831/article/details/49819641](http://blog.csdn.net/q547550831/article/details/49819641)