JavaWeb学习笔记（数据库、SQL语句、数据查询语法、完整性约束、编码、备份和恢复数据、多表查询）

JavaWeb学习笔记

数据库

数据库概念

什么是数据库
数据库就是用来存储和管理数据的仓库！
数据库存储数据的优先：

• 可存储大量数据；
• 方便检索；
• 保持数据的一致性、完整性；
• 安全，可共享；
• 通过组合分析，可产生新数据。

数据库的发展历程

• 没有数据库，使用磁盘文件存储数据；
• 层次结构模型数据库；
• 网状结构模型数据库；
• 关系结构模型数据库：使用二维表格来存储数据（MySQL就是关系型数据库！）；
• 关系-对象模型数据库；

常见数据库

• Oracle：甲骨文；
• DB2：IBM；
• SQL Server：微软；
• Sybase：赛尔斯；
• MySQL：甲骨文；

理解数据库

• RDBMS（关系数据库管理系统） = 管理员（manager）+仓库（database）
• database = N个table
• table：
  〉 表结构：定义表的列名和列类型！
  〉 表记录：一行一行的记录！

我们现在所说的数据库泛指“关系型数据库管理系统（RDBMS - Relational database management system）”，即“数据库服务器”。

应用程序与数据库
应用程序使用数据库完成对数据的存储！

基本命令

启动和关闭mysql服务器

• 启动：net start mysql；
• 关闭：net stop mysql；

在启动mysql服务后，打开windows任务管理器，会有一个名为mysqld.exe的进程运行，所以mysqld.exe才是MySQL服务器程序。

客户端登录退出mysql

在启动MySQL服务器后，我们需要使用管理员用户登录MySQL服务器，然后来对服务器进行操作。登录MySQL需要使用MySQL的客户端程序：mysql.exe

• 登录：mysql -u root -p 123 -h localhost；

  • -u：后面的root是用户名，这里使用的是超级管理员root；
  • -p：后面的123是密码，这是在安装MySQL时就已经指定的密码；
  • -h：后面给出的localhost是服务器主机名，它是可以省略的，例如：mysql -u root -p 123；

• 退出：quit或exit；

在登录成功后，打开windows任务管理器，会有一个名为mysql.exe的进程运行，所以mysql.exe是客户端程序。

SQL语句

SQL概述

什么是SQL
SQL（Structured Query Language）是“结构化查询语言”，它是对关系型数据库的操作语言。它可以应用到所有关系型数据库中，例如：MySQL、Oracle、SQL Server等。SQ标准（ANSI/ISO）有：

• SQL-92：1992年发布的SQL语言标准；
• SQL:1999：1999年发布的SQL语言标签；
• SQL:2003：2003年发布的SQL语言标签；

这些标准就与JDK的版本一样，在新的版本中总要有一些语法的变化。不同时期的数据库对不同标准做了实现。
虽然SQL可以用在所有关系型数据库中，但很多数据库还都有标准之后的一些语法，我们可以称之为“方言”。例如MySQL中的LIMIT语句就是MySQL独有的方言，其它数据库都不支持！当然，Oracle或SQL Server都有自己的方言。

语法要求

• SQL语句可以单行或多行书写，以分号结尾；
• 可以用空格和缩进来来增强语句的可读性；
• 关键字不区别大小写，建议使用大写；

分类

• DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；
• DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；
• DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；
• DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

DDL

基本操作

• 查看所有数据库名称：SHOW DATABASES；
• 切换数据库：USE mydb1，切换到mydb1数据库；

操作数据库

• 创建数据库：CREATE DATABASE [IF NOT EXISTS] mydb1；
  创建数据库，例如：CREATE DATABASE mydb1，创建一个名为mydb1的数据库。如果这个数据已经存在，那么会报错。例如CREATE DATABASE IF NOT EXISTS mydb1，在名为mydb1的数据库不存在时创建该库，这样可以避免报错。

• 删除数据库：DROP DATABASE [IF EXISTS] mydb1；
  删除数据库，例如：DROP DATABASE mydb1，删除名为mydb1的数据库。如果这个数据库不存在，那么会报错。DROP DATABASE IF EXISTS mydb1，就算mydb1不存在，也不会的报错。

• 修改数据库编码：ALTER DATABASE mydb1 CHARACTER SET utf8
  修改数据库mydb1的编码为utf8。注意，在MySQL中所有的UTF-8编码都不能使用中间的“-”，即UTF-8要书写为UTF8。

数据类型

MySQL与Java一样，也有数据类型。MySQL中数据类型主要应用在列上。

常用类型：

• int：整型
• double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
• decimal：泛型型，在表单钱方面使用该类型，因为不会出现精度缺失问题；
• char：固定长度字符串类型；
• varchar：可变长度字符串类型；
• text：字符串类型；
• blob：字节类型；
• date：日期类型，格式为：yyyy-MM-dd；
• time：时间类型，格式为：hh:mm:ss
• timestamp：时间戳类型；

操作表

• 创建表：
  CREATE TABLE 表名(
  列名 列类型,
  列名 列类型,
  …
  );

例如：

CREATE TABLE stu(
	sid	    CHAR(6),
	sname	VARCHAR(20),
	age		INT,
	gender	VARCHAR(10) 
);

CREATE TABLE emp(
	eid		CHAR(6),
	ename	VARCHAR(50),
	age		INT,
	gender	VARCHAR(6),
	birthday	DATE,
	hiredate	DATE,
	salary	DECIMAL(7,2),
	resume	VARCHAR(1000)
);

• 查看当前数据库中所有表名称：SHOW TABLES；
• 查看指定表的创建语句：SHOW CREATE TABLE emp，查看emp表的创建语句；
• 查看表结构：DESC emp，查看emp表结构；
• 删除表：DROP TABLE emp，删除emp表；
• 修改表：

1. 修改之添加列：给stu表添加classname列：

   ALTER TABLE stu ADD (classname varchar(100));
   

2. 修改之修改列类型：修改stu表的gender列类型为CHAR(2)：

   ALTER TABLE stu MODIFY gender CHAR(2);
   

3. 修改之修改列名：修改stu表的gender列名为sex：

   ALTER TABLE stu change gender sex CHAR(2);
   

4. 修改之删除列：删除stu表的classname列：

   ALTER TABLE stu DROP classname;
   

5. 修改之修改表名称：修改stu表名称为student：

   ALTER TABLE stu RENAME TO student;
   

DML

插入数据

语法1：
INSERT INTO 表名(列名1,列名2, …) VALUES(值1, 值2)

INSERT INTO stu(sid, sname,age,gender) VALUES('s_1001', 'zhangSan', 23, 'male');
INSERT INTO stu(sid, sname) VALUES('s_1001', 'zhangSan');

语法2：
INSERT INTO 表名 VALUES(值1,值2,…)
因为没有指定要插入的列，表示按创建表时列的顺序插入所有列的值：

INSERT INTO stu VALUES('s_1002', 'liSi', 32, 'female');

注意：所有字符串数据必须使用单引用！

修改数据

语法：
UPDATE 表名 SET 列名1=值1, … 列名n=值n [WHERE 条件]

UPDATE stu SET sname='zhangSanSan', age='32', gender='female' WHERE sid='s_1001';
UPDATE stu SET sname='liSi', age='20' WHERE age>50 AND gender='male';
UPDATE stu SET sname='wangWu', age=’30’ WHERE age>60 OR gender='female';
UPDATE stu SET age='18' WHERE name IN ('zhangSan', 'liSi', 'wangWu');
UPDATE stu SET gender='female' WHERE gender IS NULL 
UPDATE stu SET age=age+1 WHERE sname='zhaoLiu';

注意：对于值为NULL的列需要用’is’判断，如果为’WHERE xx=null’那么返回的值一定是false！

删除数据

语法1：
DELETE FROM 表名 [WHERE 条件]

DELETE FROM stu WHERE sid='s_1001'003B
DELETE FROM stu WHERE sname='chenQi' OR age > 30;
DELETE FROM stu;

语法2：
TRUNCATE TABLE 表名

TRUNCATE TABLE stu;

  虽然TRUNCATE和DELETE都可以删除表的所有记录，但有原理不同。DELETE的效率没有TRUNCATE高！
　　TRUNCATE其实属性DDL语句，因为它是先DROP TABLE，再CREATE TABLE。而且TRUNCATE删除的记录是无法回滚的，但DELETE删除的记录是可以回滚的（回滚是事务的知识！）。

DCL

创建用户

语法：
CREATE USER ‘用户名’[@‘登录主机名’] [ IDENTIFIED BY ] [ ‘密码’ ]

CREATE USER user1@localhost IDENTIFIED BY '123'; //用户只能在本机IP上登录
CREATE USER user2@'%' IDENTIFIED BY '123'; //用户可以在任意IP地址上登录

给用户授权

语法：
GRANT 权限1, … , 权限n ON 数据库.* TO 用户名

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON mydb1.* TO user1@localhost; //给user1用户分派在mydb1数据库上的create、alter、drop、insert、update、delete、select权限
GRANT ALL ON mydb1.* TO user2@localhost; //给用户分派指定数据库上的所有权限

撤销授权

语法：
REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名

REVOKE CREATE,ALTER,DROP ON mydb1.* FROM user1@localhost;

查看用户权限

语法：
SHOW GRANTS FOR 用户名

SHOW GRANTS FOR user1@localhost;

删除用户

语法：
DROP USER 用户名

DROP USER user1@localhost;

修改用户密码

语法：
USE mysql;
UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=‘用户名’ and Host=‘IP’;
FLUSH PRIVILEGES;

UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user2' and Host=‘localhost’;
FLUSH PRIVILEGES;

mysql 新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表，否则会出现拒绝访问，还有一种方法，就是重新启动mysql服务器，使新设置生效。­

DQL（数据查询语法）

基础查询

查询所有列
语法：
SELECT * FROM 表名;

SELECT empno, ename, sal, comm FROM 表名;

查询指定列
语法：
SELECT 列1 [, 列2, … 列N] FROM 表名;

SELECT empno, ename, sal, comm FROM 表名;

条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

• =、!=、<>、<、<=、>、>=；
• BETWEEN…AND；
• IN(set)；
• IS NULL；
• AND；
• OR；
• NOT；

示例
查询性别为女，并且年龄50的记录：

SELECT * FROM stu WHERE gender='female' AND ge<50;

查询学号为S_1001，或者姓名为liSi的记录：

SELECT * FROM stu WHERE sid ='S_1001' OR sname='liSi';

查询学号为S_1001，S_1002，S_1003的记录：

SELECT * FROM stu WHERE sid IN ('S_1001','S_1002','S_1003');

查询学号不是S_1001，S_1002，S_1003的记录：

SELECT * FROM tab_student WHERE s_number NOT IN ('S_1001','S_1002','S_1003');

查询年龄为null的记录：

SELECT * FROM stu WHERE age IS NULL;

查询年龄在20到40之间的学生记录：

SELECT * FROM stu WHERE age>=20 AND age<=40;
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;

查询性别非男的学生记录：

SELECT * FROM stu WHERE gender!='male';
SELECT * FROM stu WHERE gender<>'male';
SELECT * FROM stu WHERE NOT gender='male';

查询姓名不为null的学生记录：

SELECT * FROM stu WHERE NOT sname IS NULL;
SELECT * FROM stu WHERE sname IS NOT NULL;

模糊查询

当想查询姓名中包含a字母的学生时就需要使用模糊查询了。模糊查询需要使用关键字LIKE和特殊字符。

_：匹配一个任意字符，注意，只匹配一个字符而不是多个。
%：匹配0~N个任意字符

示例
查询姓名由5个字母构成的学生记录：

SELECT * FROM stu WHERE sname LIKE '_____';

查询姓名由5个字母构成，并且第5个字母为“i”的学生记录：

SELECT * FROM stu WHERE sname LIKE '____i';

查询姓名以“z”开头的学生记录

SELECT * FROM stu WHERE sname LIKE 'z%';

查询姓名中第2个字母为“i”的学生记录：

SELECT * FROM stu WHERE sname LIKE '_i%';

查询姓名中包含“a”字母的学生记录：

SELECT * FROM stu WHERE sname LIKE '%a%';

字段控制查询

去除重复记录

当查询结果中的多行记录一模一样时，只显示一行。

语法：
SELECT DISTINCT * | 列1 [, 列2, … 列N] FROM 表名;

SELECT DISTINCT sal FROM emp;

列运算

1.数量类型的列可以做加、减、乘、除运算（不影响数据库中的内容）

SELECT sal*1.5 FROM emp;
SELECT sal+comm FROM emp;

2.连接字符串（利用CONCAT函数）
CONCAT(str1,str2,…)：返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。

SELECT CONCAT('$', sal) FROM emp;

注意：
如果所有参数均为非二进制字符串，则结果为非二进制字符串。
如果自变量中含有任一二进制字符串，则结果为一个二进制字符串。

3.转换NULL值

有时需要把NULL转换成其它值，例如com+1000时，如果com列存在NULL值，那么NULL+1000还是NULL，而我们这时希望把NULL当前0来运算。

SELECT IFNULL(comm, 0)+1000 FROM emp; //如果comm中存在NULL值，那么当成0来运算。

4.给列起别名

你也许已经注意到了，当使用列运算后，查询出的结果集中的列名称很不好看，这时我们需要给列名起个别名，这样在结果集中列名就显示别名了。

SELECT IFNULL(comm, 0)+1000 AS 奖金 FROM emp;

注意：
AS可以省略，即：SELECT IFNULL(comm, 0)+1000 奖金 FROM emp;

排序

查询所有学生记录，按年龄升序排序：

SELECT * FROM stu ORDER BY sage ASC;
SELECT * FROM stu ORDER BY sage; //默认为ASC升序

查询所有学生记录，按年龄降序排序：

SELECT * FROM stu ORDER BY age DESC;

查询所有雇员，按月薪降序排序，如果月薪相同时，按编号升序排序

SELECT * FROM emp ORDER BY sal DESC,empno ASC; //可以一直写下去，但最多用三个就足够

聚合函数

聚合函数是用来做纵向运算的函数：

• COUNT()：统计指定列不为NULL的记录行数；
• MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
• MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
• SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
• AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

COUNT
当需要纵向统计时可以使用COUNT()。

• 查询emp表中记录数：
  SELECT COUNT(*) AS cnt FROM emp;
  注意：COUNT(1)（可以是任何数字）和COUNT(*)的返回结果是一样的。

• 查询emp表中有佣金的人数：
  SELECT COUNT(comm) cnt FROM emp;
  注意，因为count()函数中给出的是comm列，那么只统计comm列非NULL的行数。

• 查询emp表中月薪大于2500的人数：
  SELECT COUNT(*) FROM emp WHERE sal > 2500;

• 统计月薪与佣金之和大于2500元的人数：
  SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) > 2500;

• 查询有佣金的人数，以及有领导的人数：
  SELECT COUNT(comm), COUNT(mgr) FROM emp;

SUM和AVG
当需要纵向求和时使用sum()函数。

• 查询所有雇员月薪和：
  SELECT SUM(sal) FROM emp;

• 查询所有雇员月薪和，以及所有雇员佣金和：
  SELECT SUM(sal), SUM(comm) FROM emp;

• 查询所有雇员月薪+佣金和：
  SELECT SUM(sal+IFNULL(comm,0)) FROM emp;

• 统计所有员工平均工资：
  SELECT SUM(sal), COUNT(sal) FROM emp;
  或者
  SELECT AVG(sal) FROM emp;

MAX和MIN

• 查询最高工资和最低工资：
  SELECT MAX(sal), MIN(sal) FROM emp;

分组查询

当需要分组查询时需要使用GROUP BY子句，例如查询每个部门的工资和，这说明要使用部分来分组。

• 查询每个部门的部门编号和每个部门的工资和：
  SELECT deptno, SUM(sal) FROM emp GROUP BY deptno;

• 查询每个部门的部门编号以及每个部门的人数：
  SELECT deptno,COUNT(*) FROM emp GROUP BY deptno;

• 查询每个部门的部门编号以及每个部门工资大于1500的人数：
  SELECT deptno,COUNT(*) FROM emp WHERE sal>1500 GROUP BY deptno;

使用GROUP BY的条件：
首先我们要明白，使用group by 关键字时，在select列表中可以指定的项目是有限制的。
此时select语句中仅允许以下几项：

1. 被分组的列（即被GROUP BY的列）；
2. 为每个分组返回一个值的表达式。

HAVING子句

• 查询工资总和大于9000的部门编号以及工资和：
  SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

注意，WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束。

LIMIT（MYSQL方言）

LIMIT用来限定查询结果的起始行，以及总行数。

查询5行记录，起始行从0开始
SELECT * FROM emp LIMIT 0, 5;
注意，起始行从0开始，即第一行开始！

查询10行记录，起始行从3开始
SELECT * FROM emp LIMIT 3, 10;

查询起始行为第5行，一共查询3行记录
SELECT * FROM emp LIMIT 4, 3;
其中4表示从第5行开始，其中3表示一共查询3行。即第5、6、7行记录。

编码

查看MYSQL编码

SHOW VARIABLES LIKE 'char%';

因为当初安装时指定了字符集为UTF8，所以所有的编码都是UTF8。

• character_set_client：你发送的数据必须与client指定的编码一致！！！服务器会使用该编码来解读客户端发送过来的数据；
• character_set_connection：通过该编码与client一致！该编码不会导致乱码！当执行的是查询语句时，客户端发送过来的数据会先转换成connection指定的编码。但只要客户端发送过来的数据与client指定的编码一致，那么转换就不会出现问题；
• character_set_database：数据库默认编码，在创建数据库时，如果没有指定编码，那么默认使用database编码；
• character_set_server：MySQL服务器默认编码；
• character_set_results：响应的编码，即查询结果返回给客户端的编码。这说明客户端必须使用result指定的编码来解码；

控制台编码
　　修改character_set_client、character_set_results、character_set_connection为GBK，就不会出现乱码了。但其实只需要修改character_set_client和character_set_results。

  控制台的编码默认是GBK，而不能轻易修改为UTF8，这就出现一个问题。客户端发送的数据是GBK，而character_set_client为UTF8，这就说明客户端数据到了服务器端后一定会出现乱码。既然不能修改控制台的编码，那么只能修改character_set_client为GBK了。
　　服务器发送给客户端的数据编码为character_set_result，它如果是UTF8，那么控制台使用GBK解码也一定会出现乱码。因为无法修改控制台编码，所以只能把character_set_result修改为GBK。

• 修改character_set_client变量：set character_set_client=gbk;
• 修改character_set_results变量：set character_set_results=gbk;

设置编码只对当前连接有效，这说明每次登录MySQL提示符后都要去修改这两个编码，但可以通过修改配置文件my.ini来处理这一问题。

设置default-character-set=gbk

使用MYSQL工具避免乱码问题
使用MySQL工具是不会出现乱码的，因为它们会每次连接时都修改character_set_client、character_set_results、character_set_connection的编码。这样对my.ini上的配置覆盖了，也就不会出现乱码了。

完整性约束

主键和主键自增长

主键
当某一列添加了主键约束后，那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。
例如学生的学号可以用来做唯一标识，而学生的姓名是不能做唯一标识的，因为学习有可能同名。

• 主键列的值不能为NULL，也不能重复！
• 指定主键约束使用PRIMARY KEY关键字
• 主键的三个特点：非空、唯一、被引用

创建主键的三种方法：

1.创建表：定义列时指定主键：

CREATE TABLE stu(
		sid	    CHAR(6) PRIMARY KEY,
		sname	VARCHAR(20),
		age		INT,
		gender	VARCHAR(10) 
);

2.创建表：定义列之后独立指定主键：

CREATE TABLE stu(
		sid	    CHAR(6),
		sname	VARCHAR(20),
		age		INT,
		gender	VARCHAR(10),
		PRIMARY KEY(sid)
);

3.修改表时指定主键：

ALTER TABLE stu
ADD PRIMARY KEY(sid);

删除主键（只是删除主键约束，而不会删除主键列）：

ALTER TABLE stu DROP PRIMARY KEY;

---

主键自增长
MySQL提供了主键自动增长的功能！这样用户就不用再为是否有主键是否重复而烦恼了。当主键设置为自动增长后，在没有给出主键值时，主键的值会自动生成，而且是最大主键值+1，也就不会出现重复主键的可能了。

创建表时设置主键自增长（主键必须是整型才可以自增长）：

CREATE TABLE stu(
		sid INT PRIMARY KEY AUTO_INCREMENT,
		sname	VARCHAR(20),
		age		INT,
		gender	VARCHAR(10)
);

修改表时设置主键自增长：

ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;

修改表时删除主键自增长：

ALTER TABLE stu CHANGE sid sid INT;

---

非空和唯一约束

非空：指定非空约束的列不能没有值，也就是说在插入记录时，对添加了非空约束的列一定要给值；在修改记录时，不能把非空列的值设置为NULL。

指定非空约束：

CREATE TABLE stu(
		sid INT PRIMARY KEY AUTO_INCREMENT,
		sname VARCHAR(10) NOT NULL,
		age		INT,
		gender	VARCHAR(10)
);

当为sname字段指定为非空后，在向stu表中插入记录时，必须给sname字段指定值，否则会报错。

---

唯一：当为字段指定唯一约束后，那么字段的值必须是唯一的。这一点与主键相似！例如给stu表的sname字段指定唯一约束：

CREATE TABLE tab_ab(
	sid INT PRIMARY KEY AUTO_INCREMENT,
	sname VARCHAR(10) UNIQUE
);

INSERT INTO sname(sid, sname) VALUES(1001, 'zs');
INSERT INTO sname(sid, sname) VALUES(1002, 'zs');//当两次插入相同的名字时，MySQL会报错！

外键

外键 ( foreign key ) 是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键。

外键约束的特点：

• 外键必须是另一表的主键的值(外键要引用主键！)
• 外键可以重复
• 外键可以为空
• 一张表中可以有多个外键！

外键是另一张表的主键！例如员工表与部门表之间就存在关联关系，其中员工表中的部门编号字段就是外键，是相对部门表的外键。

我们再来看BBS（论坛）系统中：用户表（t_user）、分类表（t_section）、帖子表（t_topic）三者之间的关系。

例如在t_section表中sid为1的记录说明有一个分类叫java，版主是t_user表中uid为1的用户，即zs！
例如在t_topic表中tid为2的记录是名字为“Java是咖啡”的帖子，它是java版块的帖子，它的作者是ww。

外键就是用来约束这一列的值必须是另一张表的主键值！！！

创建t_user表，指定uid为主键列：

CREATE TABLE t_user(
	uid	INT PRIMARY KEY AUTO_INCREMENT,
	uname	VARCHAR(20) UNIQUE NOT NULL
);

创建t_section表，指定sid为主键列，u_id为相对t_user表的uid列的外键：

CREATE TABLE t_section(
		sid	INT PRIMARY KEY AUTO_INCREMENT,
		sname	VARCHAR(30),
		u_id	INT,
		CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid)
);

修改t_section表，指定u_id为相对t_user表的uid列的外键：

ALTER TABLE t_section ADD CONSTRAINT fk_t_user FOREIGN KEY(u_id) REFERENCES t_user(uid);

修改t_section表，删除u_id的外键约束：

ALTER TABLE t_section DROP FOREIGN KEY fk_t_user;

表与表之间的关系

一：一对一
　　例如t_person表和t_card表，即人和身份证。这种情况需要找出主从关系，即谁是主表，谁是从表。人可以没有身份证，但身份证必须要有人才行，所以人是主表，而身份证是从表。设计从表可以有两种方案：

1. 在t_card表中添加外键列（相对t_user表），并且给外键添加唯一约束；
2. 给t_card表的主键添加外键约束（相对t_user表），即t_card表的主键也是外键。（即该主键既有主键的特性又有外键的引用性）

二：一对多（多对一）
最为常见的就是一对多！一对多和多对一之间只是从不同角度出发所区分的。例如班级是一端，学生是多端，结合面向对象的思想，一端是父亲，多端是儿子，所以多端具有一端的属性，也就是说多端里面应该放置1端的主键，那么学生表里面应该放置班级表里面的主键。

三：多对多
例如t_stu和t_teacher表，即一个学生可以有多个老师，而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。即再创建一张表t_stu_tea表，给出两个外键，一个相对t_stu表的外键，另一个相对t_teacher表的外键。

多表查询

有些时候数据在不同的表中，这个时候我们就需要用到mysql中的多表联查。

多表查询有如下几种：

• 合并结果集；
• 连接查询
  • 内连接
  • 外连接
    • 左外连接
    • 右外连接
    • 全外连接（MySQL不支持）
  • 自然连接
• 子查询

合并结果集

合并结果集就是把两个select语句的查询结果合并到一起！
前提是被合并的两个结果：列数、列类型必须相同。（不一定要求表结构相同，只要在查询语句中输入的列结构和列数相同就行）

合并结果集有两种方式：

1. UNION：去除重复记录，例如：SELECT * FROM t1 UNION SELECT * FROM t2；
   

2. UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
   

连接查询

连接查询就是求出多个表的乘积，例如t1连接t2，那么查询出的结果就是t1*t2。

　　上述图片我们发现连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。
　　产生笛卡尔积会导致有很多垃圾数据，我们可以通过要查询的多个表之间都存在的关联关系去除笛卡尔积。

例如：emp一共14行记录，dept表一共4行记录，那么连接后查询出的结果是56行记录。

若想在查询emp表的同时，把每个员工的所在的正确部门信息显示出来，那么就需要使用主外键来去除无用信息了。
使用主外键关系做为条件来去除无用信息：SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;

上面查询结果会把两张表的所有列都查询出来，也许我们不需要那么多列，这时就可以指定要查询的列了。

SELECT emp.ename,emp.sal,emp.comm,dept.dname 
FROM emp,dept 
WHERE emp.deptno=dept.deptno;

还可以为表指定别名，然后在引用列时使用别名即可。

SELECT e.ename,e.sal,e.comm,d.dname 
FROM emp AS e,dept AS d //AS可以不写
WHERE e.deptno=d.deptno;

以上的连接语句就是内连接，但它不是SQL标准中的查询方式，可以理解为MYSQL方言！

内连接

内连接也叫连接，是最早的一种连接。还可以被称为普通连接或者自然连接，内连接是从结果表中删除与其他被连接表中没有匹配行的所有行，所以内连接可能会丢失信息。

SQL标准的内连接为：

SELECT * 
FROM emp e 
INNER JOIN dept d 
ON e.deptno=d.deptno;

内连接的特点：查询结果必须满足条件。

例如我们向emp表中插入一条记录：

其中deptno为50，而在dept表中只有10、20、30、40部门，那么上面的查询结果中就不会出现“张三”这条记录，因为它不能满足e.deptno=d.deptno这个条件。

自然连接

连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式：

• 两张连接的表中名称和类型完成一致的列作为条件，例如上述的emp和dept表都存在deptno列，并且类型一致，所以会被自然连接找到！

SELECT * FROM emp NATURAL JOIN dept;
SELECT * FROM emp NATURAL LEFT JOIN dept;  //左连接
SELECT * FROM emp NATURAL RIGHT JOIN dept; //右连接

当然自然连接还有其他的查找条件的方式，但其他方式都可能存在问题！

外连接

外连接的特点：查询出的结果存在不满足条件的可能。

左连接：

SELECT * FROM emp e 
LEFT OUTER JOIN dept d 
ON e.deptno=d.deptno;

  左连接是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条件的显示NULL。
　　这么说你可能不太明白，我们还是用上面的例子来说明。其中emp表中“张三”这条记录中，部门编号为50，而dept表中不存在部门编号为50的记录，所以“张三”这条记录，不能满足e.deptno=d.deptno这条件。但在左连接中，因为emp表是左表，所以左表中的记录都会查询出来，即“张三”这条记录也会查出，但相应的右表部分显示NULL。
　

右连接：
右连接就是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL。例如在dept表中的40部门并不存在员工，但在右连接中，如果dept表为右表，那么还是会查出40部门，但相应的员工信息为NULL。

SELECT * FROM emp e 
RIGHT OUTER JOIN dept d 
ON e.deptno=d.deptno;

总结：连接不限与两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除。

ON和WHERE的区别

ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。
WHERE条件：在有ON条件的SELECT语句中是过滤中间表的约束条件。在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
所以将WHERE条件移入ON后面是不恰当的。推荐的做法是：ON只进行连接操作，WHERE只过滤中间表的记录。

子查询

子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。

子查询出现的位置：
子查询可以嵌套在主查询中所有位置，包括SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY。

• where后，作为条件的一部分：
  当子查询出现在where后作为条件时，还可以使用如下关键字：
  any：和子查询的结果逐一比较，其中一条记录满足条件则表达式的值就为真
  all：和子查询的结果逐一比较，必须全部满足时表达式的值才为真

• from后，作为被查询的一条表；

• 子查询结果集的形式：

  • 单行单列（用于条件）
  • 单行多列（用于条件）
  • 多行单列（用于条件）
  • 多行多列（用于表）

练习：
1.工资高于甘宁的员工。
分析：
查询条件：工资>甘宁工资，其中甘宁工资需要一条子查询。

第一步：查询甘宁的工资

SELECT sal FROM emp WHERE ename='甘宁'

第二步：查询高于甘宁工资的员工

SELECT * FROM emp WHERE sal > (${第一步})

结果：

SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE ename='甘宁')

---

2.工资高于30部门所有人的员工信息
分析：
查询条件：工资高于30部门所有人工资，其中30部门所有人工资是子查询。高于所有需要使用all关键字。

第一步：查询30部门所有人工资

SELECT sal FROM emp WHERE deptno=30;

第二步：查询高于30部门所有人工资的员工信息

SELECT * FROM emp WHERE sal > ALL (${第一步})

结果：

SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno=30)

要点：

1. 子查询作为条件
2. 子查询形式为多行单列（当子查询结果集形式为多行单列时可以使用ALL或ANY关键字）

---

3.查询工作和工资与殷天正完全相同的员工信息
分析：
查询条件：工作和工资与殷天正完全相同，这是子查询

第一步：查询出殷天正的工作和工资

SELECT job,sal FROM emp WHERE ename='殷天正'

第二步：查询出与殷天正工作和工资相同的人

SELECT * FROM emp WHERE (job,sal) IN (${第一步})

结果：

SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='殷天正')

要点：

1. 子查询作为条件
2. 子查询形式为单行多列

---

4.查询员工编号为1006的员工名称、员工工资、部门名称、部门地址
分析：
查询列：员工名称、员工工资、部门名称、部门地址
查询表：emp和dept，分析得出，不需要外连接（外连接的特性：某一行（或某些行）记录上会出现一半有值，一半为NULL值）
查询条件：员工编号为1006

第一步：去除多表，只查一张表，这里去除部门表，只查员工表

SELECT ename, sal FROM emp e WHERE empno=1006

第二步：让第一步与dept做内连接查询，添加主外键条件去除无用笛卡尔积

SELECT e.ename, e.sal, d.dname, d.loc 
FROM emp e, dept d 
WHERE e.deptno=d.deptno AND empno=1006

第二步中的dept表表示所有行所有列的一张完整的表，这里可以把dept替换成所有行，但只有dname和loc列的表，这需要子查询。

第三步：查询dept表中dname和loc两列，因为deptno会被作为条件，用来去除无用笛卡尔积，所以需要查询它。

SELECT dname,loc,deptno FROM dept;

第四步：替换第二步中的dept

SELECT e.ename, e.sal, d.dname, d.loc 
FROM emp e, (SELECT dname,loc,deptno FROM dept) d 
WHERE e.deptno=d.deptno AND e.empno=1006

要点：

1. 子查询作为表
2. 子查询形式为多行多列