MySQL简明教程及表设计原则

MySQL的安装与配置，不再赘述。

 

MySQL CRUD(增删改查)

 

列出所有数据库

?

1	show databases;

创建数据库

?

1 2 3 4 5 6 7 8 9 10 11

--以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;

删除数据库

?

1	drop database mydb1;

 

修改数据库

?

1 2	--修改数据库，并把数据库编码变为gb2312 alter database mydb2 character set gb2312;

 

 

创建表 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

--使用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;

?

1

?

1

修改表

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

--显示表结构 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;

 

 

插入数据 

?

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

 

修改数据

?

1 2 3 4 5 6 7 8 9 10 11

--将所有员工薪水修改为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';

 

删除数据

?

1 2 3 4 5 6

--删除表中名称为’zs’的记录。 delete from employee where name='aaa';   --删除表中所有记录。 delete from employee;   --逐行删除 truncate table employee; --整体摧毁

 

查询数据

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90

--查询表中的所有数据 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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

//一个学生类 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

?

1 2 3 4 5 6 7 8

+------------+-------------+------+-----+---------+-------+ | 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差不多)，结构如下：

?

1 2 3 4 5 6 7

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

 

这两个表没有任何关联性。但是他们的id是唯一的，在数据库中可以设为主键。

?

1 2 3 4 5

--修改student的id字段为主键(primary key) alter table student modify id int primary key;   --修改teacher的id字段为主键(primary key) alter table teacher modify id int primary key;

 

然后再定义外键

?

1 2	--修改表student，添加一个名为teacher_id_FK约束，使teacher_id作为外键，并关联teacher表的id字段 alter table student add  constraint teacher_id_FK foreign key(teacher_id) references teacher(id);

 

现在,两给表的关系如下

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

+------------+-------------+------+-----+---------+-------+    | 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的属性是个集合，但是这里我们却只能是单个！

上面的需求是多对多的关系，上面只能实现一对多的关系，对于多对多的关系，常用的有一种原则。既建立一个中间表，就像这样：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

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操作更为省事，但造成了数据的冗余量过大。各有利弊。

 

多对多模式下的表的设计就这样了，其它的一对一，多对一，一对多情况下的就更好理解了。