创建临时表，对表进行操作：
删除表中重复的author，并保留id最大的一行
delete from liuyan where author in (select * from (select author from liuyan group by author having count(1)>1) a) and id not in (select * from (select max(id) from liuyan group by author having count(1)) b )
mysql 约束：
介绍：约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
作用分为：
PRIMARY KEY (PK) -- 标识该字段为该表的主键，可以唯一的标识记录 FOREIGN KEY (FK) -- 标识该字段为该表的外键 NOT NULL -- 标识该字段不能为空 UNIQUE KEY (UK) -- 标识该字段的值是唯一的 AUTO_INCREMENT -- 标识该字段的值自动增长(整数类型，而且为主键) DEFAULT -- 为该字段设置默认值 UNSIGNED -- 无符号 ZEROFILL -- 使用0填充
说明：
1、是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值
2、字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值
sex enum ( 'male' , 'female' ) not null default 'male'
age int unsigned NOT NULL default 20 -- 必须为正直 (无符号 ) 不允许为空，默认是20
3、是否是key
主键 primary key
外键 foreign key
索引 (index,unique.....)
1、非空约束 not null
是否可空，null表示空，非字符串
not null -- 不可空
null -- 可空
==================not null==================== mysql> create table t1(id int); #id字段默认可以插入空 mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ mysql> insert into t1 values(); #可以插入空 mysql> create table t2(id int not null); #设置字段id不为空 mysql> desc t2; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ mysql> insert into t2 values(); #不能插入空 ERROR 1364 (HY000): Field 'id' doesn't have a default value
2、默认约束
默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table tb1(
n_id int not null default 2,
num int not null
)
==================default==================== #设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值 mysql> create table t3(id int default 1); mysql> alter table t3 modify id int not null default 1;
1、2的综合练习
==================综合练习====================
mysql> create table student(
-> name varchar(20) not null,
-> age int(3) unsigned not null default 18,
-> sex enum('male','female') default 'male',
-> hobby set('play','study','read','music') default 'play,music'
-> );
mysql> desc student;
+-------+------------------------------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------------+------+-----+------------+-------+
| name | varchar(20) | NO | | NULL | |
| age | int(3) unsigned | NO | | 18 | |
| sex | enum('male','female') | YES | | male | |
| hobby | set('play','study','read','music') | YES | | play,music | |
+-------+------------------------------------+------+-----+------------+-------+
mysql> insert into student(name) values('egon');
mysql> select * from student;
+------+-----+------+------------+
| name | age | sex | hobby |
+------+-----+------+------------+
| egon | 18 | male | play,music |
+------+-----+------+------------+
3、唯一约束
方法一：
create table department1(
id int,
name varchar(20) unique,
comment varchar(100)
);
方法二：
creat table department2(
id int,
name varchar(10),
comment varchar(100),
constraint uk_name unique(name)
)
insert into department1 values(1,'IT','技术');
结果：Query OK, 1 row affected (0.00 sec)
insert into department1 values(1,'IT','技术');
结果：ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'
create table t1(id int not null unique); 结果：Query OK, 0 rows affected (0.02 sec) mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec)
create table service(
id int primary key auto_increment,
name varchar(20),
host varchar(15) not null,
port int not null,
unique(host,port) #联合唯一
);
insert into service values
-> (1,'nginx','192.168.0.10',80),
-> (2,'haproxy','192.168.0.20',80),
-> (3,'mysql','192.168.0.30',3306)
-> ;
结果：Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);
结果：ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'
4、主键(PRIMARY KEY)约束是用于约束表中的一行，作为这一行的标识，在一张表中通过主键就能准确定位到一行，因此主键十分重要。
注意：主键这一行的数据不能重复且不能为空
create table t2(
id int(10) not null primary key
);
还有一种特殊的主键---复合主键。主键不仅可以是表中的一列，也可以由表中的两列或多列来共同标识。
create table t3(
id int(10) not null,
name varchar(225),
primary key(id,name)
);
//alter 删除主键约束
alter table t3 drop primary key;
//alter 添加主键约束
alter table t3 add primary key(name,pwd);
//alter 修改列为主键
alter table t3 modify id int primary key;
5、外键(FOREIGN KEY)约束既能确保数据完整性，也能表现表之间的关系。
注意：一个表可以有多个外键，被外键约束的列，取值必须在它参考的列中有对应值。
------部门表
create table dept(
dept_id int(30) not null auto_increment primary key,
dept_name varchar(255) not null
);
------员工表
create table emp(
id int(10) not null auto_increment primary key,
name varchar(50) not null,
dept_id int(10) not null,
constraint f_key foreign key (dept_id) references dept(dept_id) on delete cascade
);
注意：添加外键约束后，部门表中的数据如果被员工表中的数据使用了，则不可以删除
注意：员工表中的外键字段只能添加部门表中存在的数据
级联删除：删除主表的数据时，关联的从表数据也删除，则需要中建立外键约束的后面增加on delete cascade 或on delete set null,前者是级联删除，后者是将从表的关联列的值设置为null
警告：进公司工作时，要问清楚，表的主外键设计是否需要添加约束。
数据库设计：
1、概念：
简单来说，数据库设计就是对需求进行分析、逻辑设计、物理设计以及维护和优化的过程。可以看到，数据库设计不仅仅体现在软件开发过程中，还体现中软件后期的维护上。(时间周期)
这里的软件需求分析与软件开发过程中的需求分析不太一样，数据库设计中的需求分析更侧重数据源(什么数据)、数据的属性以及数据和属性的特点。
总结：1、有效存储数据
2、满足用户的多种需求
2、规则：
数据库设计三范式：
1、第一范式：确保每列保持原子性
2、第二范式：确保表中的每列都和主键相关
3、第三范式：确保表中每一列都和主键列直接相关，而不是间接相关
3、数据库表关系
表与表之间一般存在三种关系，即一对一，一对多，多对多关系。
1、一对一关系
CREATE TABLE IF NOT EXISTS person(
id INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10),
sex CHAR(1),
husband INT,
wife INT
);
INSERT INTO person VALUES(1,'小花','0',3,0);
INSERT INTO person VALUES(2,'小明','1',0,4);
INSERT INTO person VALUES(3,'张三','1',0,1);
INSERT INTO person VALUES(4,'小丽','0',2,0);
INSERT INTO person VALUES(5,'王五','1',0,0);
sql代码对应的person表：
可以很容易看出，小花和张三是夫妻，小明和小丽是夫妻
通常为了查询方便，需要两个表，但在实际情况中为了省空间，通常只建一个表。
2、一对多关系
例如：一个人可以拥有多辆汽车，要求查询某个人拥有的车辆。
分析：这种情况下可以采用一张表，但是因为一个人可以拥有多辆汽车，如果采用一张表，会造成冗余信息过多。此时应该选择人和车辆分别单独建表，在车辆的表中加个外键字段（人的编号）即可。
*(思路总结：建两个表，‘一’方不动，‘多’方添加一个外键字段)*
//建立人员表
CREATE TABLE people(
id VARCHAR(12) PRIMARY KEY,
sname VARCHAR(12),
age INT,
sex CHAR(1)
);
INSERT INTO people VALUES('H001','小王',27,'1');
INSERT INTO people VALUES('H002','小明',24,'1');
INSERT INTO people VALUES('H003','张慧',28,'0');
INSERT INTO people VALUES('H004','李小燕',35,'0');
INSERT INTO people VALUES('H005','王大拿',29,'1');
INSERT INTO people VALUES('H006','周强',36,'1');
//建立车辆信息表
CREATE TABLE car(
id VARCHAR(12) PRIMARY KEY,
mark VARCHAR(24),
price NUMERIC(6,2),
hid VARCHAR(12),
CONSTRAINT fk_people FOREIGN KEY(hid) REFERENCES human(id)
);
INSERT INTO car VALUES('C001','BMW',65.99,'H001');
INSERT INTO car VALUES('C002','BenZ',75.99,'H002');
INSERT INTO car VALUES('C003','Skoda',23.99,'H001');
INSERT INTO car VALUES('C004','Peugeot',20.99,'H003');
INSERT INTO car VALUES('C005','Porsche',295.99,'H004');
INSERT INTO car VALUES('C006','Honda',24.99,'H005');
INSERT INTO car VALUES('C007','Toyota',27.99,'H006');
INSERT INTO car VALUES('C008','Kia',18.99,'H002');
INSERT INTO car VALUES('C009','Bentley',309.99,'H005');
sql代码对应的人员表：
sql代码对应的车辆信息表：
3.多对多关系
例如：学生选课，一个学生可以选修多门课程，每门课程可供多个学生选择。
分析：这种方式可以按照类似一对多方式建表，但冗余信息太多，好的方式是实体和关系分离并单独建表，实体表为学生表和课程表，关系表为选修表，其中关系表采用联合主键的方式(由学生表主键和课程表主键组成)建表。
//建立学生表
CREATE TABLE student(
id VARCHAR(10) PRIMARY KEY,
sname VARCHAR(12),
age INT,
sex CHAR(1),
class VARCHAR(6)
);
INSERT INTO student VALUES('p0001','王军',20,1,'c101');
INSERT INTO student VALUES('p0002','张宇',21,1,'c101');
INSERT INTO student VALUES('p0003','刘飞',22,1,'c102');
INSERT INTO student VALUES('p0004','赵燕',18,0,'c103');
INSERT INTO student VALUES('p0005','曾婷',19,0,'c103');
INSERT INTO student VALUES('p0006','周慧',21,0,'c104');
INSERT INTO student VALUES('p0007','小红',23,0,'c104');
INSERT INTO student VALUES('p0008','杨晓',18,0,'c104');
INSERT INTO student VALUES('p0009','李杰',20,1,'c105');
INSERT INTO student VALUES('p0010','张良',22,1,'c105');
//建立课程表
CREATE TABLE course(
id VARCHAR(10) PRIMARY KEY,
sname VARCHAR(12),
credit NUMERIC(2,1),
teacher VARCHAR(12)
);
INSERT INTO course VALUES('C001','Java',3.5,'李老师');
INSERT INTO course VALUES('C002','高等数学',5.0,'赵老师');
INSERT INTO course VALUES('C003','JavaScript',3.5,'王老师');
INSERT INTO course VALUES('C004','离散数学',3.5,'卜老师');
INSERT INTO course VALUES('C005','数据库',3.5,'廖老师');
INSERT INTO course VALUES('C006','操作系统',3.5,'张老师');
//建立选修表
CREATE TABLE sc(
sid VARCHAR(10),
cid VARCHAR(10)
);
ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY(sid,cid);
ALTER TABLE sc ADD CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(id);
ALTER TABLE sc ADD CONSTRAINT fk_course FOREIGN KEY(cid) REFERENCES course(id);
INSERT INTO sc VALUES('S0001','C001');
INSERT INTO sc VALUES('S0001','C002');
INSERT INTO sc VALUES('S0001','C003');
INSERT INTO sc VALUES('S0002','C001');
INSERT INTO sc VALUES('S0002','C004');
INSERT INTO sc VALUES('S0003','C002');
INSERT INTO sc VALUES('S0003','C005');
INSERT INTO sc VALUES('S0004','C003');
INSERT INTO sc VALUES('S0005','C001');
INSERT INTO sc VALUES('S0006','C004');
INSERT INTO sc VALUES('S0007','C002');
INSERT INTO sc VALUES('S0008','C003');
INSERT INTO sc VALUES('S0009','C001');
INSERT INTO sc VALUES('S0009','C005');
sql代码对应的学生表：
sql代码对应的课程表：
sql代码对应的选课表： 
4.分表
在数据库表使用过程中，为了减小数据库服务器的负担、缩短查询时间，常常会考虑做分表设计。
分表分两种，一种是纵向分表（将本来可以在同一个表的内容，人为划分存储在为多个不同结构的表）和横向分表（把大的表结构，横向切割为同样结构的不同表）。
其中，纵向分表常见的方式有根据活跃度分表、根据重要性分表等。其主要解决问题如下：
表与表之间资源争用问题；
锁争用机率小；
实现核心与非核心的分级存储，如UDB登陆库拆分成一级二级三级库；
解决了数据库同步压力问题。
横向分表是指根据某些特定的规则来划分大数据量表，如根据时间分表。其主要解决问题如下：
单表过大造成的性能问题；
单表过大造成的单服务器空间问题。