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;
修改數據庫
--修改數據庫，并把數據庫編碼變為gb2312 alter 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-8 alter table user character set utf8; --在原有的表結構上，增加一個名為image的二進制數據列 alter table employee add image blob; --列名name修改為username alter 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操作更為省事，但造成了數據的冗余量過大。各有利弊。
多對多模式下的表的設計就這樣了，其它的一對一，多對一，一對多情況下的就更好理解了。
浙公網安備 33010602011771號