MySQL基本操作语句及JDBC操作(持续更新中)

MySQL基本操作语句及JDBC操作

本文将简单介绍通过MySQL命令行完成对数据库的基本操作，包括：

• 创建及使用一个数据库
• 创建表及插入数据
• 基本的select 语句
• 基本的join 操作
• 更新、删除操作
• 聚集操作
• Java与数据库
• SAE相关部署

---

创建及使用一个数据库

• 命令行操作中，每一条SQL语句使;来标记结尾。
• 创建一个数据库
  create database BookDB;
• 使用一个数据库
  use BookDB;
• 屏幕将会提示Database changed

---

创建表及插入数据

An entity–relationship model (ER model) describes inter-related things of interest in a specific domain of knowledge. An ER model is composed of entity types (which classify the things of interest) and specifies relationships that can exist between instances of those entity types.
—— E-R model [ Wikipedia]

在这里我们创建一个图书管理系统，使用刚才创建的BookDB数据库。

• Entity为作者，Attributes为AuthorID(PK), Name, Age, Country.
  上面提到的PK，即Primary Key，能唯一确定表中的一行。
  对于任意两个Author，他们的AuthorID不同。
  在我们对数据库进行操作的时候，根据Name或者Age都不一定能唯一确定一个人，而AuthorID可以。
  其中varchar代表长度不定的字符串，括号内为其最大长度。

create table Author(
AuthorID varchar(5) primary key not null,
Name varchar(30),
Age int,
Country varchar(15));

• Entity为一本书，它的Attributes为ISBN码，Title，Publisher, PublishDate，Price
  其中ISBN被选择设定为PK，AuthorID被设定为Foreign Key。
  所谓Foreign Key，是本表中存在的属性，是其他表中的Primary Key。
  本表中的AuthorID被设定为Author表中的AuthorID.
  对应创建一个table Book：

create table Book(
……
AuthorID varchar(5) not null,
……
PublishDate Date,
Price double
Foreign Key (AuthorID) references Author(AuthorID));

此处留意FK的格式为

Foreign key (fk_name) references another_table(pk_name)

插入数据的感觉和函数传参是一样的
insert table_name() values();
表名后什么都不写，则表示向表中所有的字段赋值。使用这种方式，不仅在VALUES中的值要和列数一致，而且顺序不能颠倒。
对于Author，则可以写
insert Author values("12345","Leafy",19,"PRC"),("11111","Wang",19,"PRC");

也可以写做
insert Author(AuthorID,Name) values ("11111","Wang");
Age和Country被缺省了，自动设为NULL。
而如果缺省了PK或其他被规定不可为NULL（not null）的值，也会有错误提示。

---

基本的select 语句

最基本的select语句的格式如下

select [DISTINCT/ALL] {*|ColumnExpression [AS newName][,……]}
from TableName[alias][,……]
[where condition]
[group by columnList][having condition][order by columnlist] 

[ ]中的内容是可以省略的

刚才的数据库建好并成功插入一系列数据之后，我们就可以尝试select语句了。
我们讲表中每一个属性理解为一列，每个个体（即插入的每一条数据）为一行。

select规定了 满足from where……条件的结果 哪些被展示出来。
可以选择全部：*
可以选择输出的每行都不相同，重复的被省略掉：DISTINCT （不写的时候默认是ALL）
也可以指定特定某列（某属性）、几列同时给它们命名。

比如当我想看到当前所有作者的全部属性
select * from Author;

想知道作者们的ID和名字
select AuthorID, Name from Author;

想在输出的表格中，把列名AuthorID显示为ID
select AuthorID as ID from Author;

想看到作者们都有哪个国家的
select DISTINCT Country from Author;

---

from是select的表（内容）。也就是要经过筛选的数据来源。
考虑join之前，我们暂定数据的来源是一个表。
如果数据的来源是Book表，只要把Author改为Book即可。

---

where有点类似于if，是条件判断。

试图寻找年龄为20岁的全部的作者的全部信息
select * from Author where Age = 20;

---

group by, having, order by先不看，我们先来尝试一条稍微复杂的语句：找到年龄为20岁的作者来自的国家都有哪些
`select DISTINCT Country from Author where Age = 20;

可是，如果我想要找到年龄为20的作家们都写了哪本书，应该怎么办呢？
年龄是作家的属性，对应着确实可以找到作家的AuthorID，使用AuthorID确实可以作为条件寻找对应的书籍。
但是年龄为20的作家倘若不止一位，就需要多次通过AuthorID在Book表里寻找书籍。
有没有什么简化的方式呢？
这个时候就要使用join。

---

基本的join 语句

两个关系的笛卡尔乘积，将第一个关系的每个元组与第二个关系的每个元组进行连接。
假设表A中有a1, a2, a3，表B中有b1, b2，你就会得到a1b1, a1b2, a2b1, a2b2, a3b1, a3b2。

可是假如A和B的关系，一个A可能对应几个B： a1和b1, a1和b2, a2和b3对应，就会有多余的行。就可以通过where语句进行筛选。
好比Author和Book
select * from Author，Book where Author.AuthorID = Book.AuthorID;
select * from Author a join Book b where a.AuthorID = b.AuthorID;
这样将两个表合并后，新表将具有两列AuthorID，一列来自Author,一列来自Book。

如果两个关系都有相同的属性，就可以使用natural join。
natural join考虑A、B二者拥有相同属性的元组的取值，相同时实现连接。
我们的Book和Author都拥有AuthorID这个属性，如果使用natural join，就可以自动将一本书通过AuthorID找到它的作者。相同的属性被合并，新表中只有一列AuthorID。
select * from Book natural join Author;

如果多个表join，避免不必要的相等属性带来的影响，还可以使用下面格式

select ……
from (table1 natural join table2) join table3 using(……);

join …… using运算要给定一个属性名列表。

其他outer join/inner join/right join太过繁琐此处跳过不说。

---

更新、删除操作

删除过程只能删除一个元组（tuple，可以理解为一行）

delete from table_a
where condition;

没有where限定，则会删除所有元组，但表还存在（为空）

如果想删除年龄在20和30之间的所有作家，则可以写

delete from Author
where Age between 20 and 30;

复杂一点，如果试图删除有由HIT press出版社出版的书的作者，就可以写

delete from Author
where AuthorID in (select DISTINCT AuthorID
                   from Book
                   where Publisher = 'HIT press');

括号内的select语句找到了Book中所有由HIT press出版的书籍的作者AuthorID，in表示Author的AuthorID出现在了select的结果中。

---

更新语句

update ……
set ……
where ……;

假设Author表中记录的美国作家的年龄都是15年前的值，现在要修正数据：

update Author
set Age = Age + 15
where Country = 'America';

---

聚集操作

聚集函数，是以一个集合为输入，返回单个值的函数。
SQL提供以下几种：
- 平均 avg
- 最小 min
- 最大 max
- 求和 sum
- 计数 count

假如我们试图统计作者们的年龄和并命名其为result：

select sum(Age) as result
from Author;

如果试图统计一名作者写的书的本数：

select count(*)
from Book
where AuthorID = …… ;

ToBeContinued