MySQL資料庫基本操作（三）

標籤：基礎   into   table   select   rom   保持資料   情況下   電腦   維表   

MySQL補充：

mysql是關係型資料庫，關聯式資料庫，是建立在關聯式模式基礎上的資料庫，
現實世界中的各種實體，以及實體之間的各種聯絡，均用關聯式模式（table）來表示。
#關聯式模式就是指二維表格模型,因而一個關係型資料庫就是由二維表及其之間的聯絡組成的一個資料群組織。

# 實體：就是資料對象 （可以是有型的，也可以是無型的）

E-R 圖 （實體關聯模型）

E-R圖也稱實體-聯絡圖(Entity Relationship Diagram)，提供了表示實體類型、屬性和聯絡的方法，用來描述現實世界的概念性模型。它是描述現實世界關係概念性模型的有效方法。是表示概念關聯式模式的一種方式。用“矩形框”表示實體型，矩形框內寫明實體名稱；用“橢圓圖框”表示實體的屬性，並用“實心線段”將其與相應關係的“實體型”串連起來；用”菱形框“表示實體型之間的聯絡成因，在菱形框內寫明聯絡名，並用”實心線段“分別與有關實體型串連起來，同時在”實心線段“旁標上聯絡的類型（1:1,1:n或m:n）

外鍵約束 FOREING KEY

外鍵約束FOREIGN KEY，保持資料一致性，完整性實現一對一或一對多關聯性。

（因為一個表只存一類資訊。用外鍵來做參照，保證資料的一致性，可以減少資料冗餘）

外鍵約束的要求：

資料表的儲存引擎只能為InnoDB外鍵列和參照列資料類型一致外鍵必須關聯到鍵上面去，一般情況是關聯到，另一張表的主鍵

#例：##表amysql> create table `a`(     -> a_id int primary key,    -> a_name varchar(20) not null    -> );insert into a values(1,‘a1‘),(2,‘a2‘);alter table a modify a_id int auto_increment;show create table a;alter table a auto_increment=6;   # 只能調大，不可以調小，一個表裡面只有一個auto_increment,一般和主鍵一起用。insert into a(a_name) value(‘a6‘);##表bcreate table `b`(b_id int primary key,b_name varchar(20) not null,fy_id int not null,constraint AB_id foreign key(fy_id) references `a`(a_id));#刪除外鍵alter table `b` drop foreign key AB_id;#增加外鍵mysql> alter table `b`     -> add constraint AB_id foreign key(fy_id) references `a`(a_id);#一個表只存一類資訊。

一對多關聯性

舉例，學校中一個學院可以有很多的學生，而一個學生只屬於某一個學院（通常情況下），學院與學生之間的關係就是一對多的關係，通過外部索引鍵關聯來實現這種關係。

#例：##建立學院表mysql> create table department(     -> d_id int primary key AUTO_INCREMENT,    -> d_name varchar(20) not null    -> );    ##建立學生表mysql> create table student(    -> s_id int primary key AUTO_INCREMENT,    -> s_name varchar(20) not null,    -> dept_id int not null,    # 一個不同列， 關聯到另一種表的主鍵    -> constraint SD_id foreign key(dept_id) references department(d_id)    -> );#插入資料mysql> INSERT INTO `department`(`d_name`)    -> VALUES(‘電腦學院‘),    ->       (‘外語學院‘)    -> ;Query OK, 2 rows affected (0.10 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> INSERT INTO `student`(`s_name`,`dept_id`)    -> VALUES(‘s1‘,1),    ->       (‘s2‘,2),    ->       (‘s3‘,2)    -> ;Query OK, 3 rows affected (0.08 sec)Records: 3  Duplicates: 0  Warnings: 0

一對一關聯性

舉例，學生表中有學號、姓名、學院，但學生還有些比如電話，家庭住址等比較私密的資訊，這些資訊不會放在學生表當中，會建立一個學生的詳細資料表來存放。這時的學生表和學生的詳細資料表兩者的關係就是一對一的關係，因為一個學生只有一條詳細資料。用外鍵加主鍵的方式來實現這種關係。

#例：#學生表：mysql> DESC `student`;+---------+-------------+------+-----+---------+----------------+| Field   | Type        | Null | Key | Default | Extra          |+---------+-------------+------+-----+---------+----------------+| id      | int(11)     | NO   | PRI | NULL    | auto_increment || name    | varchar(20) | NO   |     | NULL    |                || dept_id | int(11)     | YES  | MUL | NULL    |                |+---------+-------------+------+-----+---------+----------------+3 rows in set (0.06 sec)#建立詳細學生表：mysql> CREATE TABLE `student_details`(    -> `id` INT PRIMARY KEY,      -> `sex` VARCHAR(10) not null,    -> `age` INT,      -> `address` VARCHAR(20) comment ‘家庭住址‘,      -> `parents` VARCHAR(20),      -> `home_num` VARCHAR(20),    -> FOREIGN KEY (`id`) REFERENCES `student`(`s_id`)    -> );   # 主鍵映射 主鍵Query OK, 0 rows affected (0.67 sec)insert into student_details value(2,‘男‘,18,‘湖南長沙‘,‘佳爸爸‘,‘123456789‘);insert into student_details value(4,‘男‘,20,‘湖南長沙‘,‘李爸爸‘,‘12541656469‘);insert into student_details value(5,‘男‘,20,‘湖南長沙‘,‘李爸爸‘,‘12541656469‘);

多對多關係

舉例，學生要報名選修課，一個學生可以報名多門課程，一個課程有很多的學生報名，那麼學生表和課程表兩者就形成了多對多關係。對於多對多關係，需要建立第三張關係表，關係表中通過外鍵加主鍵的形式實現這種關係。

#例：#建立課程表：mysql> CREATE TABLE `course`(    -> `id` INT PRIMARY KEY AUTO_INCREMENT,    -> `name` VARCHAR(20) NOT NULL    -> );Query OK, 0 rows affected (1.18 sec)insert into course(name)  values(‘python‘),(‘java‘),(‘英語‘),(‘日語‘);#學生與課程多對多關係表 (中間表)mysql> CREATE TABLE `select`(    -> `s_id` INT,    -> `crs_id` INT,      # 防止同一個學生，多次選同一門課程    -> PRIMARY KEY (`s_id`,`crs_id`),   #  必須學生表的學生，  才能選課    -> FOREIGN KEY (`s_id`) REFERENCES `student` (`s_id`),       # 只有課程存在，才能選    -> FOREIGN KEY (`crs_id`) REFERENCES `course` (`id`)    -> );Query OK, 0 rows affected (0.50 sec)insert into `select` values(1,1),(1,3),(4,4);insert into `select` values(2,3),(2,1),(2,4);

外鍵約束的參照操作：

mysql> SELECT * FROM `department`;+----+----------------------+------+| d_id | name                 | code |+-----+----------------------+------+|  1  | 電腦學院            |    1 ||  2  | 外國語學院            |    2 |+-----+----------------------+------+mysql> SELECT * FROM `student`;+----+--------+--------+| id | name   | dep_id |+----+--------+--------+|  1 | budong |      1 ||  2 | Tuple  |      1 ||  3 | Which  |      2 |+----+--------+--------+mysql> DELETE FROM `department` WHERE `d_id`=1;ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`student`, CONSTRAINT `stu_dep_for_key` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`))#因為外部索引鍵關聯的原因，不能刪除父表中的記錄mysql> UPDATE `student` SET `dept_id`=1 WHERE `dept_id`=2;mysql> DELETE FROM `department` WHERE `d_id`=2;#因為student表中的dep_id沒有值關聯department中的id=2的資料，所以可以刪除department表中id=2的資料

查詢中一些較為常見的函數

#求最大年齡mysql> SELECT MAX(`age`) FROM `student_details`;#求最小年齡mysql> SELECT MIN(`age`) FROM `student_details`;#求和mysql> SELECT SUM(`age`) FROM `student_details`;#求平均數mysql> SELECT AVG(`age`) FROM `student_details`;#四捨五入mysql> SELECT ROUND(AVG(`age`)) FROM `student_details`;#統計mysql> select count(s_id) from student;#查看目前時間mysql> SELECT NOW();mysql> create table tb1(    -> id INT,    -> name VARCHAR(20),    -> dates DATETIME    -> );mysql> insert into tb1 value(1,‘佳能‘,now());

作業

1.建立好 學院、學生、學生詳情、課程、選課詳情 這個5張表。 用文字簡單敘述其表關係，和 如何?其表關係。 

MySQL資料庫基本操作（三）