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操作更为省事，但造成了数据的冗余量过大。各有利弊。
多对多模式下的表的设计就这样了，其它的一对一，多对一，一对多情况下的就更好理解了。
