MySQL之----在java編程加強知識點

標籤：varchar   sel   沒有   order   三種方式   引用   完整性   serial   lca   

在資料中，建表處理是非經常見且非常有用的方法。

表和表之間的關係有 1:1

?1:N

? ? ? ?N:N

三種方式。

1對1的方式

<span style="font-size:18px;">示範範例：一夫一妻思考：在一個表中儲存著全部人的資訊。有男有女。要求查詢出全部的夫妻。即一男一女。注意以下：王五是光棍不應該能查詢出來。</span>

<span style="font-size:18px;">CREATE TABLE person(? ?id INT,? ?NAME VARCHAR(10),? ?sex CHAR(1),? ?wife INT,? ?husband INT);INSERT INTO person VALUES(1,‘小花‘,‘0‘,0,3);INSERT INTO person VALUES(2,‘玉芬‘,‘0‘,0,4);INSERT INTO person VALUES(3,‘張三‘,‘1‘,1,0);INSERT INTO person VALUES(4,‘李四‘,‘1‘,2,0);INSERT INTO person VALUES(5,‘王五‘,‘1‘,0,0);//一對一關聯性的操作：查出每對夫妻的姓名CREATE VIEW w AS SELECT * FROM person WHERE sex=‘0‘;CREATE VIEW m AS SELECT * FROM person WHERE sex=‘1‘;//不利用表與表之間的關係SELECT w.NAME AS 妻子, m.NAME AS 丈夫 FROM w,m WHERE w.husband=m.id AND m.wife=w.id;//如今更先進的方式：利用表間的關係SELECT w.NAME AS 妻子, m.NAME AS 丈夫 FROM w INNER JOIN m ON w.husband=m.id AND m.wife=w.id;</span>

1對多的方式

1對多，多對1。一個人能夠擁有多輛汽車，要求查詢出某人所擁有的全部汽車。依據範式的要求，應該設計兩張表。分別表示人的資訊和汽車的資訊。<strong></strong>

//步驟1：畫E-R圖//步驟2:分別建實體表，並給多方的表加入外鍵約束CREATE TABLE person2(? ?id VARCHAR(32) PRIMARY KEY,? ?pname VARCHAR(30),? ?sex CHAR(1));CREATE TABLE car(? ?id VARCHAR(32) PRIMARY KEY,? ?cname VARCHAR(30),? ?price NUMERIC(10,2),? ?pid VARCHAR(32),? ?CONSTRAINT car_fk FOREIGN KEY(pid) REFERENCES person2(id));DROP TABLE car;//步驟3:為兩個表加入測試資料//實體表1INSERT INTO person2(id,pname,sex) VALUES(‘P001‘,‘Jack‘,‘1‘);INSERT INTO person2(id,pname,sex) VALUES(‘P002‘,‘Tom‘,‘1‘);INSERT INTO person2(id,pname,sex) VALUES(‘P003‘,‘Rose‘,‘0‘);INSERT INTO person2(id,pname,sex) VALUES(‘P004‘,‘Mary‘,‘0‘);INSERT INTO person2(id,pname,sex) VALUES(‘P005‘,‘Mike‘,‘1‘);SELECT * FROM person2;////實體表2INSERT INTO car(id,cname,price,pid) VALUES(‘C001‘,‘BMW‘,123.5,‘P001‘);INSERT INTO car(id,cname,price,pid) VALUES(‘C002‘,‘Benz‘,123.5,‘P001‘);INSERT INTO car(id,cname,price,pid) VALUES(‘C003‘,‘BMW‘,223.5,‘P001‘);INSERT INTO car(id,cname,price,pid) VALUES(‘C011‘,‘BMW‘,83.5,‘P003‘);INSERT INTO car(id,cname,price,pid) VALUES(‘C012‘,‘Benz‘,100,‘P003‘);INSERT INTO car(id,cname,price,pid) VALUES(‘C013‘,‘Audi‘,223.5,‘P003‘);INSERT INTO car(id,cname,price,pid) VALUES(‘C021‘,‘BMW‘,88.5,‘P004‘);INSERT INTO car(id,cname,price,pid) VALUES(‘C022‘,‘QQ‘,10,‘P004‘);INSERT INTO car(id,cname,price,pid) VALUES(‘C023‘,‘Audi‘,73,‘P005‘);INSERT INTO car(id,cname,price) VALUES(‘C033‘,‘Audi‘,1000);

/查詢：哪些人有什麼樣的車 (用"表名.列名"的形式訪問列，假設列名不反覆，能夠省略表名)//利用一方的主鍵和“多方”的外鍵進行關聯SELECT person2.pname,car.cname FROM person2,car WHERE person2.id=car.pid;//查詢Jack有什麼車SELECT person2.pname,car.cname FROM person2,car WHERE person2.id=car.pid AND person2.pname=‘Jack‘ ;//查詢哪些人有兩輛以上的車SELECT person2.pname,COUNT(pname) AS 車數量 FROM person2,car WHERE person2.id=car.pid GROUP BY pname HAVING COUNT(pname)>=2 ORDER BY 車數量;SELECT * FROM person2 WHERE id IN ( SELECT pid FROM car GROUP BY pid HAVING COUNT(pid)>=2 );//查詢哪些人沒有車SELECT * FROM person2 WHERE id NOT IN( SELECT pid FROM car );

多對多

多對多：一個人能夠擁有多種角色。如某人，即是父親、又是兒子、又是丈夫。而同一時候這三個角色又能夠給其它全部的人。
要求查詢出即是父親、又是兒子、又是丈夫的全部人。
查詢出全部人擁有的全部角色。<strong></strong>

還有學生選課系統都是多對多的情況 

詳細的實現方法在最後的比筆記中貼出來

資料庫的約束主鍵 primary key
在建立表時指定主鍵---在聲明主鍵時。應該同一時候使用not nullkeyword。
Create table stud(id int primary key,…..)
在建立完畢之後指定主鍵：
Alter table stud add constraint stud_pk primary key(id);
主鍵自己主動增長：
Auto_increment 適合於int類型的欄位
外鍵 foreign key－表示一種參考關聯性
與還有一個表的關聯關係－稱為外鍵。外鍵的作用時當主表中沒有記錄時。無法向子表中寫入無引用的記錄：
Alter table stud ?add constraint stud_fk foreign key(stud_clsid) references cls(id);
主外鍵關係是一種1對多的關係。假設主表中沒有的記錄，子表中將不能添加。
建立主外部索引鍵關聯，兩個表的資料類型和大小必須保持一致否則建立將不會成功。
能夠通過可視化介面操作主外關聯。 唯一 unique – 與主鍵有所差別，主鍵不能為null，而unique能夠有一列為null這是SqlServer的限制，但Mysql能夠寫入多列的null值，所以，在mysql上使用unique時一般再通過not null進行限制。

alter table cls ?add constraint cls_uk unique(name); /* 指定name不能反覆*/
預設值 default
在建立表時，能夠指定預設值如：
Create table stud(sex char(1) default ‘1’,…..);

使用關聯對資料進行查詢當一個表的資料不能滿足我們的須要時。我們就要從多個表中查詢資料。

此時必須使用關聯查詢：
inner join – 內關聯。兩方必須都要存在。?
left join －左關聯。

以左邊資料為準。
right join － 右關聯。在上面的表與表之間的關係用到的

總結：
預存程序

三種方式：

1。最簡單。最主要的方式。

“DELIMITER$$”這句是為了讓解析器把“$$”當作結束標誌(否則預設是把“;“號當作結束標記),這樣預存程序中的語句結束符";"就不會當作過程的結束標記。

DELIMITER$$CREATE PROCEDURE p1()BEGIN   SELECT * FROM stud;   INSERT INTO stud(id,sname,age,score,dept) VALUES(1014,‘劉三豐‘,33,55,‘通訊學院‘);END$$DELIMITER;   //把結束標記還原回來CALL p1();  //調用預存程序p1

2。帶參數的方式

//帶參數的預存程序DELIMITER$$CREATE PROCEDURE p2(IN id INT, IN nm VARCHAR(30) )BEGIN   INSERT INTO stud(id,sname) VALUES(id,nm);END$$DELIMITER ; DROP PROCEDURE p2;CALL p2(1015,‘吊絲‘);

3，帶傳回值的

//有傳回值的預存程序----參數與變數問題( @變數名  。一個@為使用者變數。兩個@即 @@為全域的系統變數 )DELIMITER$$CREATE PROCEDURE p3(IN id INT, IN nm VARCHAR(30), OUT num INT )BEGIN   INSERT INTO stud(id,sname) VALUES(id,nm);   SELECT COUNT(*) INTO num FROM stud;END$$DELIMITER ; CALL p3(1016,‘無名‘,@aa);SELECT @aa; //輸出變數aa的值

MySql差別大寫和小寫查詢

<span style="font-size: 18px; white-space: pre;"></span>mysql查詢預設是不區分大寫和小寫 如：<span style="white-space:pre"></span>select? * from? table_name where? a like? ‘a%‘??? <span style="white-space:pre"></span>select? * from? table_name where? a like? ‘A%‘??? <span style="white-space:pre"></span>select * from table_name where a like ‘a%‘<span style="white-space:pre"></span>select * from table_name where a like ‘A%‘<span style="white-space:pre"></span>效果是一樣的。 <span style="white-space:pre"></span>要讓mysql查詢區分大寫和小寫。能夠：<span style="white-space:pre"></span>select? * from? table_name where? binary? a like? ‘a%‘??<span style="white-space:pre"></span>select? * from? table_name where? binary? a like? ‘A%‘???<span style="white-space:pre"></span>select * from table_name where binary a like ‘a%‘<span style="white-space:pre"></span>select * from table_name where binary a like ‘A%‘<span style="white-space:pre"></span>也能夠在建表時，加以標識? <span style="white-space:pre"></span>create table table_name(????<span style="white-space:pre"></span> a varchar(20) binary<span style="white-space:pre"></span>)<span style="font-size: 18px;"></span>

事務transaction:?原子性(atomicity)：組成交易處理的語句形成了一個邏輯單元，不能僅僅運行其中的一部分。?
一致性(consistency)：在交易處理運行前後，資料庫是一致的(資料庫資料完整性約束)。?
隔離性(isolcation)：一個交易處理對還有一個交易處理的影響。

?
持久性(durability)：交易處理的效果能夠被永久儲存下來 。
一個事務僅僅會有一個結果：要麼成功、要麼失敗。

Start transaction;開始一個事務。
Commit;提交所做的改動。Rollback;復原所做的改動。假設在操作時出錯。應該從新開始一個事務。

watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQv/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/Center" />

設定事物隔離等級在mySql的命令列設定隔離等級。
在MySql的命令列設定隔離等級，僅僅對此開啟的命令列表單有效。第一個命令列表單，就是一個獨立的client。
Select @@tx_isolation; 能夠查看眼下的隔離等級。

Set transaction isolation level <層級名稱>能夠設定隔離等級。
層級名稱為：{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }?
須要注意的是，隔離等級必須要在事務其中使用，沒有事務，隔離等級也沒有意義了。

※交易處理START TRANSACTION  DELETE FROM stud WHERE id=1015;  DELETE FROM stud WHERE id=1014;  SELECT * FROM stud;ROLLBACK /  COMMIT;

MySQL之----在java編程加強知識點