JavaWeb(20) : mysql(二)

零、mysql(一)巩固

SELECT * FROM emp WHERE deptno = 30;
SELECT ename,empno,deptno FROM emp WHERE job = '销售员';
SELECT * FROM emp WHERE comm > sal;
SELECT * FROM emp WHERE comm > sal * 0.6;
SELECT * FROM emp WHERE (deptno = 10 AND job = '经理') OR (deptno = 20 AND job = '销售员');
SELECT * FROM emp WHERE (deptno = 10 AND job = '经理') OR (deptno = 20 AND job = '销售员') OR (job NOT IN('经理','销售员') AND sal >= 20000);
SELECT * FROM emp WHERE comm IS NULL OR comm < 1000;
SELECT * FROM emp WHERE ename LIKE '___';
SELECT * FROM emp WHERE hiredate LIKE '2001-%';
SELECT * FROM emp ORDER BY empno ASC;
SELECT * FROM emp ORDER BY sal DESC ,hiredate ASC;
SELECT deptno,AVG(sal) 平均工资 FROM emp GROUP BY deptno;
SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;
SELECT job,MAX(sal),MIN(sal),COUNT(*) FROM emp GROUP BY job;

一、mysql编码问题

show variables like ‘char%’; // 查看mysql编码

二、mysql备份与恢复数据库

2.1、导出sql
mysqldump -uroot -proot mydb3>c:/a.sql

2.2、导入sql
方法1：mysql -uroot -proot mydb3<c:/a.sql
方法2：user mydb3;
source c:/a.sql;

三、主键约束（唯一标识）

• 非空
• 唯一
• 被引用（学习外键时）
• 当表的某一列被指定为主键后，该列就不能为空，不能有重复值出现。

建表时设置主键语法1：CREATE TABLE stu(
sid CHAR(6) PRIMARY KEY, // 指定sid列为主键列，即为sid列添加主键约束
sname VARCHAR(20),
age INT,
gender VARCHAR(10)
);

建表时设置主键语法2：CREATE TABLE stu(
sid CHAR(6)
sname VARCHAR(20)
age INT,
gender VARCHAR(20)
PRIMARY KEY(sid)
);

修改表时指定主键(原表中必须无主键！)：ALTER TABLE stu ADD PRIMARY KEY(sid);

删除主键：ALTER TABLE stu DROP PRIMARY KEY

四、主键自增长

因为主键列的特性是：必须唯一、不能为空，所以我们通常会指定主键类为整型，然后设置其自动增长，这样可以保证在插入数据时主键列的唯一和非空特性。

语法：创建表时指定主键自增长
CREATE TABLE stu(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
age INT,
gender VARCHAR(10)
);

注意：如果在表的主键自增到5时删除表中所有数据，那么再放入数据时自增从6开始。

五、非空约束

因为某些列不能设置为NULL值，所以可以对列添加非空约束。
建表时：sname VARCHAR(20) NOT NULL, //对sname列设置了非空约束

六、唯一约束

如果某些列不能设置重复的值，所以可以对列添加唯一约束（可以为空NULL）。
建表时：sname VARCHAR(20) NOT NULL UNIQUE,//设置sname既非空又唯一

七、外键约束

• 去除笛卡尔积的时候很有用！
• 外键必须是另一表的主键的值(外键要引用主键！)
• 外键可以重复
• 外键可以为空
• 一张表中可以有多个外键！

语法：CONSTRAINT 约束名称 FOREIGN KEY(外键列名) REFERENCES 关联表(关联表的主键)

概念模型在数据库中成为表。
数据库表中的多对一关系，只需要在多方使用一个独立的列来引用1方的主键即可。

例1：一个部门有多个员工(创建表时指定外键约束)
部门表:
CREATE TABLE dept (
deptno int(11) NOT NULL AUTO_INCREMENT,
dname varchar(50) DEFAULT NULL,
PRIMARY KEY (deptno);
)

员工表：
CREATE TABLE emp (
empno int(11) NOT NULL AUTO_INCREMENT,
ename varchar(50) DEFAULT NULL,
dno int(11) DEFAULT NULL,
PRIMARY KEY (empno),
KEY fk_emp_dept (dno),
CONSTRAINT fk_emp_dept FOREIGN KEY (dno) REFERENCES dept (deptno)

例2:(修改表时添加外键约束)
ALERT TABLE emp
ADD CONSTRAINT fk_emp_deptno FOREIGN KEY(deptno) REFERENCES dept(deptno);

例3: 修改表时删除外键约束
ALTER TABLE emp
DROP FOREIGN KEY fk_emp_deptno; // 约束名称

八、数据库一对一关系

在表中建立一对一关系比较特殊，需要让其中一张表的主键，即是主键又是外键。

例1：钟爱一生夫妻间的一对一关系
CREATE TABLE husband (
hid int(11) NOT NULL AUTO_INCREMENT,
hname varchar(50) DEFAULT NULL,
PRIMARY KEY (hid)
);

CREATE TABLE wife (
wid int(11) NOT NULL AUTO_INCREMENT,
wname varchar(50) DEFAULT NULL,
PRIMARY KEY (wid),
CONSTRAINT fk_wife_husband FOREIGN KEY (wid) REFERENCES husband (hid)
);

其中wife表的wid即是主键，又是相对husband表的外键！
husband.hid是主键，不能重复！
wife.wid是主键，不能重复，又是外键，必须来自husband.hid。
所以如果在wife表中有一条记录的wid为1，那么wife表中的其他记录的wid就不能再是1了，因为它是主键。
同时在husband.hid中必须存在1这个值，因为wid是外键。这就完成了一对一关系。
从表的主键即是外键！

九、数据库多对多关系

在表中建立多对多关系需要使用中间表，即需要三张表，在中间表中使用两个外键，分别引用其他两个表的主键。
比如学生和老师。一个老师可以教多个学生，一个学生也可以有多个老师。

例1：
CREATE TABLE student (
sid int(11) NOT NULL AUTO_INCREMENT,
sname varchar(50) DEFAULT NULL,
PRIMARY KEY (sid)
) ;

CREATE TABLE teacher (
tid int(11) NOT NULL AUTO_INCREMENT,
tname varchar(50) DEFAULT NULL,
PRIMARY KEY (tid)
) ;

CREATE TABLE stu_tea (
sid int(11) DEFAULT NULL,
tid int(11) DEFAULT NULL,
KEY fk_student (sid),
KEY fk_teacher (tid),
CONSTRAINT fk_student FOREIGN KEY (sid) REFERENCES student (sid),
CONSTRAINT fk_teacher FOREIGN KEY (tid) REFERENCES teacher (tid)
) ;

这时在stu_tea这个中间表中的每条记录都是来说明student和teacher表的关系。
例如在stu_tea表中的记录：sid为1001，tid为2001，这说明编号为1001的学生有一个编号为2001的老师。

sid    tid
101    201 /*编号为101的学生有一个编号为201的老师*/
101    202 /*编号为101的学生有一个编号为202的老师*/
101    203 /*编号为101的学生有一个编号为203的老师*/
102    201 /*编号为102的学生有一个编号为201的老师*/
102    204 /*编号为102的学生有一个编号为204的老师*/