MySQL簡明教程及表設計原則

MySQL的安裝與配置，不再贅述。

 

MySQL CRUD(增刪改查)

 

列出所有資料庫

show databases;                                   

建立資料庫

--以mysql預設配置建立資料庫create database mydb1;--以utf8編碼建立資料庫create database mydb2 character set utf8;--以utf8編碼和utf8校對規則建立資料庫create database mydb3 character set utf8 collate utf8_general_ci;--現實建立資料庫時的建立資訊show create database mydb2;

刪除資料庫

drop database mydb1;

 

修改資料庫

--修改資料庫，並把資料庫編碼變為gb2312alter database mydb2 character set gb2312;

 

 

建立表 

--使用mydb3資料庫use mydb3;--建立一個名為employee的表create table employee(id int,--可變字元，最大20個name varchar(20),gender char(1),birthday date,entry_date date,job varchar(40),--貨幣格式，8位元字，2位小數salary decimal(8,2),resume text--使用utf-8編碼)character set utf8;

修改表

--顯示表結構desc employee;  --顯示表的建立語句show create table employee; --表名改為user。rename table employee to user;--修改表的字元集為utf-8alter table user character set utf8;--在原有的表結構上，增加一個名為image的位元據列alter table employee add image blob;--列名name修改為usernamealter table user change column name username varchar(40); --修改job列，使其長度為60。alter table employee modify job varchar(60);--刪除sex列。alter table employee drop gender;

 

 

插入資料 

insert into employee(id,name) values(1,'aaa');

 

修改資料

--將所有員工薪水修改為5000元。update employee set salary=5000;--將姓名為’zs’的員工薪水修改為3000元。update employee set salary=3000 where name='aaa';--將姓名為’aaa’的員工薪水修改為4000元,job改為ccc。update employee set salary=4000,entry_date='1980-08-08' where name='aaa';--將wu的薪水在原有基礎上增加1000元。update employee set salary=salary+1000 where name='aaa';

 

刪除資料

--刪除表中名稱為’zs’的記錄。delete from employee where name='aaa';--刪除表中所有記錄。delete from employee;   --逐行刪除truncate table employee; --整體摧毀

 

查詢資料

--查詢表中的所有資料select * from employee;--查詢表中所有學生的資訊。select * from student;--查詢表中所有學生的姓名和對應的英語成績。select name,english from student;--以english為基準過濾表中重複資料。select distinct english from student;--在所有學生的數學分數上加10分。select name,math+10 from student;--統計每個學生的總分。select name,(chinese+english+math) from student;--使用別名表示學生分數。select name as 姓名,(chinese+english+math) as 總分 from student;select name 姓名,(chinese+english+math) 總分 from student;--查詢姓名為wu的學產生績select * from student where name='王五';--查詢英語成績大於90分的同學select * from student where english>90;--查詢總分大於200分的所有同學select * from student where (chinese+math+english)>200;--查詢英語分數在 80－90之間的同學。select * from student where english>80 and english<90;select * from student where english between 80 and 90;--查詢數學分數為89,90,91的同學。select * from student where math in(89,90,91);--查詢所有姓李的學產生績。select * from student where name like '李_';--查詢數學分>80，語文分>80的同學。select * from student where math>80 and chinese>80;--對數學成績排序後輸出。select name,math from student order by math;--對總分排序後輸出，然後再按從高到低的順序輸出select name 姓名,(chinese+math+english) 總分 from student order by 總分 desc;--對姓李的學產生績排序輸出select name,(chinese+math+english) 總分 from student where name like '李%' order by 總分 desc;--統計一個班級共有多少學生？select count(*) from student;select count(name) from student;--統計數學成績大於90的學生有多少個？select count(math) from student where math>90;--統計總分大於250的人數有多少？select count(*) from student where (chinese+math+english)>250;--統計一個班級數學總成績？select sum(math) from student;--統計一個班級語文、英語、數學各科的總成績select sum(chinese),sum(math),sum(english) from student;--統計一個班級語文、英語、數學的成績總和select sum(chinese+math+english) from student;--統計一個班級語文成績平均分select sum(chinese)/count(chinese) from student;--求一個班級語文平均分？select avg(chinese) from student;--求一個班級總分平均分select avg(chinese+math+english) from student;--求班級最高分和最低分select max(chinese+math+english),min(chinese+math+english) from student;--對訂單表中商品歸類後，顯示每一類商品的總價select product,sum(price) from orders group by product;--查詢購買了幾類商品，並且每類總價大於100的商品select product from orders group by product having sum(price)>100;

 

 

資料庫表設計的一般原則

   
     在通常的java-web設計中，為了降低耦合度，會以javabean作為資料傳遞的媒介，java-bean最終會傳遞給顯示層或資料操作層用以向使用者展現或伺服器存貯。

     在java-bean中，儲存了訊息對象的各種屬性，在資料庫中，也以相應的對象為一個單獨的表，表中的列（欄位）即為java-bean中的主要屬性。

樣本一：

java-bean

//一個學生類public Student {public int id;  //這就相當於主鍵了public String name;public String address;public String class;//描述這個學生得上多少個老師的課程public Set<Teacher> teachers;/*各種get set省略*//****/}

 

在這個bean中，所有的屬性描述了student的基本資料。現在根據web業務中的對象來設計資料中的student表，student表中的欄位應與bean中的屬性一致，因為他們代表了同一個東西。

表student

+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| id         | int(11)     | YES  |     | NULL    |       || address    | varchar(50) | YES  |     | NULL    |       || class      | varchar(20) | YES  |     | NULL    |       || teacher_id | int(11)     | YES  |     | NULL    |       |+------------+-------------+------+-----+---------+-------+

 

一個大致的樣子出來了,但它是獨立的,因為java-bean中直接包含了一個教師的列表，它指向了一個教師的集合。而這裡的欄位則只是一個值，這裡就需要使用主鍵和外鍵了。

現在假設有teacher表(java-bean不再贅述，與student的bean差不多)，結構如下：

+------------+-------------+------+-----+---------+-------+| Field      | Type        | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| id         | int(11)     | YES  |     | NULL    |       || address    | varchar(50) | YES  |     | NULL    |       || student_id | int(11)     | YES  |     | NULL    |       |+------------+-------------+------+-----+---------+-------+

 

這兩個表沒有任何關聯性。但是他們的id是唯一的，在資料庫中可以設為主鍵。

--修改student的id欄位為主鍵(primary key)alter table student modify id int primary key;--修改teacher的id欄位為主鍵(primary key)alter table teacher modify id int primary key;

 

然後再定義外鍵

--修改表student，添加一個名為teacher_id_FK約束，使teacher_id作為外鍵，並關聯teacher表的id欄位alter table student add  constraint teacher_id_FK foreign key(teacher_id) references teacher(id);

 

現在,兩給表的關係如下

   +------------+-------------+------+-----+---------+-------+   | Field      | Type        | Null | Key | Default | Extra |   +------------+-------------+------+-----+---------+-------+   | id         | int(11)     | NO   | PRI | NULL    |       |   | address    | varchar(50) | YES  |     | NULL    |       |   | class      | varchar(20) | YES  |     | NULL    |       ||--| teacher_id | int(11)     | YES  | MUL | NULL    |       ||  +------------+-------------+------+-----+---------+-------+||||  +------------+-------------+------+-----+---------+-------+|  | Field      | Type        | Null | Key | Default | Extra ||  +------------+-------------+------+-----+---------+-------+|->| id         | int(11)     | NO   | PRI | NULL    |       |   | address    | varchar(50) | YES  |     | NULL    |       |   | student_id | int(11)     | YES  |     | NULL    |       |   +------------+-------------+------+-----+---------+-------+

 

但這不是我們想要的，bean中描述students或teachers的屬性是個集合，但是這裡我們卻只能是單個！

上面的需求是多對多的關係，上面只能實現一對多的關係，對於多對多的關係，常用的有一種原則。既建立一個中間表，就像這樣：

                         student   +------------+-------------+------+-----+---------+-------+   | Field      | Type        | Null | Key | Default | Extra |   +------------+-------------+------+-----+---------+-------+*->| id         | int(11)     | NO   | PRI | NULL    |       ||  | address    | varchar(50) | YES  |     | NULL    |       ||  | class      | varchar(20) | YES  |     | NULL    |       ||  | teacher_id | int(11)     | YES  |     | NULL    |       ||  +------------+-------------+------+-----+---------+-------+||                       student_teacher|  +------------+-------------+------+-----+---------+-------+|  | Field      | Type        | Null | Key | Default | Extra ||  +------------+-------------+------+-----+---------+-------+└--| teacher    | int(11)     | NO   | MUL | NULL    |       |┌--| student    | varchar(50) | NO   | MUL | NULL    |       ||  +------------+-------------+------+-----+---------+-------+||                      teacher|  +------------+-------------+------+-----+---------+-------+|  | Field      | Type        | Null | Key | Default | Extra ||  +------------+-------------+------+-----+---------+-------+*->| id         | int(11)     | NO   | PRI | NULL    |       |   | address    | varchar(50) | YES  |     | NULL    |       |   | student_id | int(11)     | YES  |     | NULL    |       |   +------------+-------------+------+-----+---------+-------+

這樣,就解決問題了。這樣做的好處是邏輯更為清晰，缺點是CRUD操作相對比較複雜，還有一種方法，就是三個表融合為一個表，最為簡單，CRUD操作更為省事，但造成了資料的冗餘量過大。各有利弊。

 

多對多模式下的表的設計就這樣了，其它的一對一，多對一，一對多情況下的就更好理解了。