Java-数据库编程技术（MySQL）

初识MySQL

数据库简介

什么是数据库

数据库（Database，DB）就是存放数据的仓库，为了实现一定目的，按照某种规则组织起来的数据的集合。当然数据有多种形式，如文字、数码、符号、图形、声音等。从广义的角度上定义，计算机中任何可以保存数据的文件或者系统我们都可以称之为数据库，如一个Word文件等。

常见数据库

1. Oracle
2. SQL Server
3. MySQL

我们详细了解一下MySQL相对于其他数据库的优势：

• 运行速度快：MySQL体积小，命令执行的速度快
• 使用成本低：MySQL是开源的，且提供免费版本，对大多数用户来说大大降低了使用成本
• 容易使用：与其他大型数据库的设置和管理相比，其复杂程度较低，易于使用
• 可移植性强：MySQL能够运行于多种系统平台上，如Windows、Linux、UNIX等
• 使用更多用户：MySQL支持最常用的数据管理功能，适用于中小企业甚至大型网站应用

实体和数据库表

上图为数据库中存储的数据表格

每一行（Row）实际上对应一个实体，在数据库中通常叫做一条"记录"

每一列，如"id"，“name"等，在数据库中通常称之为"字段”

上图中将相同类型的记录组织在一起的数据结构称为数据库"表"（Table），表是实体的集合

主键和外键

主键

主键：一列的值用来唯一标识表中的每一行，用于强制表的实体完整性

注意：
一个表只能有一个主键，并且主键列不允许出现空值（NULL），尽管有的表中允许没有主键，但是通常情况下建议为表设置主键。

复合主键：如果两列或多列组合起来唯一的标识表中的每一行，则该主键也叫做复合主键。

选择主键的原则：

1. 最少性
2. 稳定性

外键

外键：如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

命令行连接MySQL

命令行方式连接数据库

语法：

#host--->服务器主机地址
#username--->数据库用户名
#password--->数据库密码

mysql -h host -u username -p password

注意：
如果是在本机操作，可省略-h参数
-p后面可以不写密码，按Enter后输入密码。如果写密码，-p和密码间没有空格。

例如
或者

登录之后，可以用以下指令查看当前的MySQL版本信息以及用户名：

SELECT VERSION(),USER();

例如：

SQL简介

什么是SQL

SQL全称是：结构化查询语言（Structured Query Language），是关系数据库的标准语言

SQL的组成

1. DML（Data Manipulation Language，数据操作语言，也称为数据操纵语言）：用来插入、修改和删除数据库中的数据，如INSERT UPDATE DELETE等
2. DDL（Data Definition Language，数据定义语言）：用来建立数据库、数据库对象和定义其列，大部分是以CREATE开头的命令，如CREATE TABLE``CREATE VIEW以及DROP TABLE等
3. DQL（Data Query Language，数据库查询语言）：用来对数据库中的数据进行查询，如SELECT等
4. DCL（Data Control Language，数据控制语言）：用来控制数据库组件的存取许可、存取权限等，如GRANT和REVOKE等

SQL中的运算符

1. 算术运算符：+ - * / %这五个与Java是同样的意思和用法
2. 赋值运算符：=同样与Java意思用法相同
3. 比较运算符：> < >= <= !=(不等于，非SQL-92标准)与Java用法相同，除此之外=表示等于，<>表示不等于
4. 逻辑运算符：AND OR NOT：分别相当于Java的&& || ！

例如：

NOT(性别 = '男') AND (年龄>=18 AND 年龄<=45)

使用DDL语句操作数据库

创建数据库

语法：

CREATE DATABASE 数据库名;

例如：
创建dog数据库的语句如下

CREATE DATABASE dog;

结果如下：

注意：
在MySQL中，以英文半角分号;作为一条命令的结束符，且在Windows系统下，默认不区分大小写。

查看数据库列表

语法：

SHOW DATABASES;

运行结果如下：

选择数据库

语法：

USER 数据库名;

例如：

USE tangccoep;
USE test;

运行结果如下：

删除数据库

语法：

DROP DATABASE 数据库名;

例如：

DROP DATABASE dog;

运行结果如下：

使用DDL语句操作数据表

数据类型

数值类型

数值数据类型：存储数值，每种类型具有不同的存储范围，支持取值范围越大，所需存储空间越多。所有数值类型（除BIT和Boolean外）都可以有符号或无符号，有符号数据列可存储正或负的数值，默认情况为有符号。具体类型描述如下表：

数据类型	字节	说明
TINYINT[(M)]	1字节	有符号值：-27~27；无符号值：0~2^8-1
SMALLINT[(M)]	2字节	有符号值：-32768 到32767（- 2^15 到2^15-1；无符号值：0到65535（0 到2^16 -1）
MEDIUMINT[(M)]	3字节	有符号值：-8388608 到8388607（- 2^23 到2^23 -1）；无符号值：0到16777215（0 到2^24 -1）
INT[(M)]	4字节	有符号值：-2147683648 到2147683647（- 2^31 到2^31 -1）无符号值：0到4294967295（0 到2^32 -1）
FLOAT[(M,D)]	4字节	有符号值：-3.402823466E+38-1.175494351E-38；无符号值：1.175494351E-383.402823466E+38
DOUBLE[(M,D)]	8字节	有符号值：-1.7976931348623157E+308 ~ -2.2250738585072014E-308；无符号值：2.2250738585072014E-308 ~ 1.7976931348623157E+308
DECIMAL[(M[,D])]	M+2字节	M：最大精度位数即总位数，取值范围1 ~ 65，默认值为10；D：小数位精度位数，D的取值范围是0 ~ 30；该类型可能的取值范围与DOUBLE相同，但有效取值范围由M、D决定，例如类型为DECIMAL(5,2)的字段取值范围是-999.99 ~ 999.99

MySQL提供了5种整型： tinyint、smallint、mediumint、int和bigint(字节数1、2、3、4、8)，这些类型在可表示的取值范围上是不同的。整数列可定义为unsigned从而禁用负值；这使列的取值范围为0以上。

MySQL 提供三种浮点类型： float、double和decimal。与整型不同，浮点类型不能是unsigned的，其取值范围也与整型不同，这种不同不仅在于这些类型有最大值，而且还有最小非零值。最小值提供了相应类型精度的一种度量，这对于记录科学数据来说是非常重要的（当然，也有负的最大和最小值）。

在选择了某种数值类型时，应该考虑所要表示的值的范围，只需选择能覆盖要取值的范围的最小类型即可。选择较大类型会对空间造成浪费，使表不必要地增大，处理起来没有选择较小类型那样有效。对于整型值，如果数据取值范围较小，如人员年龄或兄弟姐妹数，则tinyint最合适。mediumint能够表示数百万的值并且可用于更多类型的值，但存储代价较大。bigint在全部整型中取值范围最大，而且需要的存储空间是表示范围次大的整型int类型的两倍，因此只在确实需要时才用。对于浮点值，double占用float的两倍空间。除非特别需要高精度或范围极大的值，一般应使用只用一半存储代价的float型来表示数据。

在定义整型列时，可以指定可选的显示尺寸m。如果这样，m应该是一个1 到255的整数。它表示用来显示列中值的字符数。例如，mediumint(4)指定了一个具有4个字符显示宽度的mediumint列。如果定义了一个没有明确宽度的整数列，将会自动分配给它一个缺省的宽度。缺省值为每种类型的"最长"值的长度。如果某个特定值的可打印表示需要不止m个字符，则显示完全的值；不会将值截断以适合m个字符。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。

对每种浮点类型，可指定一个最大的显示尺寸m 和小数位数d。m 的值应该取1 到255。d的值可为0 到3 0，但是不应大于m – 2（如果熟悉odbc 术语，就会知道m 和d 对应于odbc 概念的"精度"和"小数点位数"）。m和d对float和double 都是可选的，但对于decimal是必须的。在选项m 和d时，如果省略了它们，则使用缺省值。

串类型（包括字符串）

串数据类型：最常用的数据类型，有两种基本的串类型：分别为定长串和不定长串。定长串结束长度固定的字符，其长度是创建表是指定的，不允许多于指定的字符数据，它们分配的存储空间与指定的一样多，CHAR属于定长串类型。变长串存储长度可变的文本，有些变长数据类型具有最大的定长，而有些则是完全变长的，不管哪种只有指定的数据得到保存（不会添加额外的空格保存），TEXT属于变长串类型。变长数据类型灵活，定长数据类型高效，MySQL处理定长数据类型比变长列快很多，MySQL不允许对变长列（或一个列的可变部分）进行索引，这会极大影响性能。具体类型描述如下表：

数据类型	字节	说明
CHAR[(M)]	M字节	固定长字符串；M为0 ~ 225的整数；它的长度必须在创建时指定，否则MySQL假定为CHAR（1）
VARCHAR[(M)]	可变长度	可变长度；M为0 ~ 65535的整数
TINYTEXT	0 ~ 255	微型字符串
MEDIUMTEXT	0 ~ 16383	中型文本串
TEXT	0 ~ 65535	文本串
LONGTEXT	最大长度4GB	纯文本串，一般不会到4GB
ENUM	-	接受最多64K个串组成的预定义集合的某个串
SET	-	接受最多64K个串组成的预定义集合的零个或多个串

注意：
不管任何形式的串数据类型，串值都必须在引号内（通常单引号更好）；如果数值是计算中使用的数值，则应存储在数值数据类型列中，如果作为字符串使用（如电话号码、邮政编码）则应保存在串数据类型列中。

日期和时间数据类型

MySQl中有多种表示日期和时间的数据类型。其中YEAR表示年份，DATE表示日期，TIME表示时间，DATETIME和TIMESTAMP表示日期和实践。具体如下表：

数据类型	存储字节数	格式	取值范围
DATE	4	YYYY-MM-DD	1000-01-01 ~ 9999-12-31
TIME	3	YY-MM-DD hh:mm:ss:	-838:59:59 ~ 838:59:59
DATETIME	8	hh:mm:ss:	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMP	4	YYYYMMDDHHMMSS	19700101080001 ~ 20380119111407
YEAR	1	YYYY格式的年份	1901 ~ 2155

当插入值超出有效取值范围时，系统会报错，并将零值插入到数据库中。

MySQL是以 YYYY-MM-DD 格式来显示DATE类型的值，插入数据时，数据可以保持这种格式。另外，MySQL还支持一些不严格的语法格式，分隔符"-"可以用"@"、"."等符号来替代。 在插入数据时，也可以使用"YY-MM-DD"格式，YY转化成对应的年份的规则与YEAR类型类似。如果我们想插入当前系统的时间，则可以插入CURRENT_DATE或者NOW()。

TIME类型表示为"时：分：秒"，尽管小时范围一般是0~23，但是为了表示某些特殊时间间隔，MySQL将TIME的小时范围扩发了，而且支持负值。对TIME类型赋值，标准格式是'HH：MM：SS'，但不一定非要是这种格式。 如果插入的是'D HH：MM：SS'格式，则类似插入了'（D*24+HH）：MM：SS'。比如插入'2 23:50:50'，相当于插入了'71:50:50'。如果插入的是'HH：MM'或'SS'格式，则效果是其他未被表示位的值赋为零值。比如插入'30'，相当于插入了'00:00:30'；如果插入'11:25'，相当于插入了'11:25:00'。在MySQl中，对于'HHMMSS'格式，系统能够自动转化为标准格式。如果我们想插入当前系统的时间，则可以插入CURRENT_TIME或者NOW()。

DATETIME类型准格式为"YYYY-MM-DD HH：MM：SS"，具体赋值方法与上面的方法相似。

TIMESTAMP的取值范围比较小，没有DATETIME的取值范围大，因此输入值时一定要保证在TIMESTAMP的范围之内。它的插入也与插入其他日期和时间数据类型类似。那么TIMESTAMP类型如何插入当前时间？第一，可以使用CURRENT_TIMESTAMP；第二，输入NULL，系统自动输入当前的TIMESTAMP；第三，无任何输入，系统自动输入当前的TIMESTAMP。 另外有很特殊的一点：TIMESTAMP的数值是与时区相关。

给YEAR类型赋值可以有三种方法： 第一种是直接插入4位字符串或者4位数字； 第二种是插入2位字符串，这种情况下如果插入'00'~'69'，则相当于插入2000~2069；如果插入'70'~'99'，则相当于插入1970~1999。第二种情况下插入的如果是'0'，则与插入'00'效果相同，都是表示2000年； 第三种是插入2位数字，它与第二种（插入两位字符串）不同之处仅在于：如果插入的是一位数字0，则表示的是0000，而不是2000年。所以在给YEAR类型赋值时，一定要分清0和 '0'，虽然两者相差个引号，但实际效果确实相差了2000年。

二进制数据类型

二进制类型可存储任何数据，如文字、图像、多媒体等。具体类型描述如下：

数据类型	说明
TITYBLOB	最大长度为255字节
BLOB	最大长度为64KB
MEDIUMBLOB	最大长度为16MB
LONGBLOB	最大长度为4GB

创建表

语法：

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1 数据类型 [字段属性][约束][索引][注释],
	字段2 数据类型 [字段属性][约束][索引][注释],
	......
	字段n 数据类型 [字段属性][约束][索引][注释],
)[表类型][表字符集][注释];

字段约束与属性

常用的属性约束如下表：

字段属性、约束名	关键字	说明
非空约束	NOT NULL	如该字段不允许为空，需要设置NOT NULL约束，如学生姓名字段不允许为空
默认约束	DEFAULT	赋予某字段默认值，如果该字段没有插入数据，则其值为默认值，如学生表中男生居多，可设置性别列默认值为"男"
唯一约束	UNIQUE KEY(UK)	设置字段的只是唯一的。允许为空，但只能有一个空值
主键约束	PRIMARY KEY(PK)	设置该字段为表的主键，可以作为该表记录的唯一标识，如学生号能唯一确定一名学生，可设置为主键
外键约束	FOREIGN KEY(FK)	用于在两表之间建立关系，需要指定引用主表的哪一字段。在发生插入或更新表中数据时，数据库将自动检查更新的字段值是否符合约束的限制。如果不符合约束要求，则更新操作失败。使用时注意：（1）InnoDB支持外键，MyISAM不支持，外键关联的表要求都是InnoDB类型的表；（2）作为外键的字段要求在主表中是主键（单字段主键）
自动增长	AUTO_INCREMENT	（1）设置该列为自增字段，默认每条自增1； （2）通常用于设置主键，且为整数类型； （3）可设置初始值和步长

主键可以是单字段的，也可以是多字段的

1.单字段主键

在定义字段的同时定义主键，语法如下：

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1 数据类型 PRIMARY KEY;
	......
);

在定义完所有字段之后定义主键，语法如下：

CREATE TABLE [IF NOT EXISTS] 表名(
	字段1 数据类型,
	......
	[CONSTRAINT<约束名>] PRIMARY KEY[列名]
);

例如：

CREATE TABLE student(
	`studentNo` INT(4) PRIMARY KEY;
	......
);

或者：

CREATE TABLE student(
	`studentNo` INT(4),
	......
	PRIMARY KEY(`id`)
);

2.多字段联合主键

主键由多字段组成，语法如下：

CREATE TABLE [IF NOT EXISTS] 表名(
	......
	PRIMARY KEY [字段1,字段2......]
);

例如：

CREATE TABLE tb_temp(
	`id` INT(4),
	`name` VARCHAR(11),
	......
	PRIMARY KEY(`id`,`name`)
);

注释

在创建表的同时可以为表或字段添加说明性的文字，即注释。
注释是使用COMMENT关键字来添加的。

例如：

CREATE TABLE test(
	`id` INT(11) UNSIGNED COMMENT `编号`
)COMMENT='测试表';

编码格式设置

可以在创建表时指定字符集，语法如下：

CREAT TABLE [IF NOT EXISTS] 表名(
	#省略代码
)CHARSET = 字符集名;

下面利用CREATE TABLE语句在数据库myschool中创建学生表student，具体设计如下表所示：

序号	字段名称	字段说明	数据类型	长度	属性	备注
1	studentNo	学号	INT	4	非空，主键
2	loginPwd	密码	VARCHAR	20	非空
3	studentName	姓名	VARCHAR	50	非空
4	sex	性别	CHAR	2	非空，默认值"男"
5	gradeId	年级编号	INT	4	无符号
6	phone	联系电话	VARCHAR	50
7	address	地址	VARCHAR	255	默认值"地址不详"
8	bornDate	出生日期	DATETIME
9	email	邮件账号	VARCHAR	50
10	identityCard	身份证号	VARCHAR	18	唯一	身份证号全国唯一

详细代码如下：

CREATE TABLE `student`(
	`studentNo` INT(4) NOT NULL COMMENT '学号' PRIMARY KEY, #非空主键
	`loginPwd` VARCHAR(20) NOT NULL COMMENT '密码',
	`studentName` VARCHAR(50) NOT NULL COMMENT '学生姓名',
	`sex` CHAR(2) DEFAULT '男' NOT NULL COMMENT '性别', #非空，默认值男
	`gradeId` INT(4) UNSIGNED COMMENT '年级编号', #无符号数
	`phone` VARCHAR(50) COMMENT '联系电话',
	`address` VARCHAR(255) DEFAULT '地址不详' COMMENT '地址',  #默认值"地址不详"
	`bornDate` DATETIME COMMENT '出生日期',
	`email` VARCHAR(50) COMMENT '邮件账号',
	`identityCard` VARCHAR(18) UNIQUE KEY COMMENT '身份证号'  #唯一
)COMMENT='';  #表注释"学生表"

查看表

显示数据库中的所有表名称，语法如下：

SHOW TABLES;

查看表的定义，语法如下：

DESCRIBE 表名;
或
DESC 表名;

例如，查看myschool数据库中的student表，SQL语句如下：

USER myschool;
DESCRIBE `student`;

删除表

语法：

DROP TABLE [IF EXISTS] 表名;

例如：

DROP TABLE `student`;

创建student表的语句改写成完整的创建表的语句，如下所示：

USE myschool;
DROP TABLE IF EXISTS `student`;
CREATE TABLE 'student'(
	......
);

修改表

修改表名

语法如下：

ALTER TABLE<旧表名> RENAME [TO] <新表名>;
#TO 为可选参数，使用与否不影响结果。仅修改表名，表结构不变

例如：
实现在test数据库中创建表demo01，将其改名为demo02，SQL语句如下：

DROP TABLE IF EXISTS `demo01`;
CREATE TABLE IF NOT EXISTS `demo01`(
	`id` INT(10) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(8) NOT NULL,
	PRIMARY KEY(`id`)
);
#修改表名
ALTER TABLE `demo01` RENAME `demo02`;

添加字段

添加字段的语法如下：

ALTER TABLE 表名 ADD 字段名 数据类型 [属性];

例如，向demo02表中添加密码字段，SQL语句如下：

#添加字段
ALTER TABLE demo02 ADD `password` VARCHAR(20) NOT NULL;

修改字段

修改字段，可以包括修改字段名和修改数据存储的类型。SQL语句如下：

ALTER TABLE 表名 CHANGE 原字段名 新字段名 数据类型 [属性];

其中，"原字段名"指修改前的字段名，"新字段名"指修改后的字段名，"数据类型"指修改后的数据类型，如果不需要修改数据类型，则和原数据类型保持一致，但"数据类型"不能为空

例如：
将demo02表中name字段名改为username，数据类型改为CHAR(10)，SQL语句如下：

#修改字段名
ALTER TABLE demo02 CHANGE `name` `username` CHAR(10) NOT NULL;

删除字段

将数据表中的某个字段从表中删除，语法如下：

ALTER TABLE 表名 DROP 字段名;

例如： 删除demo02表中password字段，SQL语句如下：

#删除字段
ALTER TABLE demo02 DROP `password`;

MySQL系统帮助

暂无

DML和DQL

MySQL的存储引擎

暂无

使用DML插入数据

INSERT插入数据

1.插入单行数据

语法：

INSERT INTO 表名 [(字段名列表)] VALUES (值列表);

其中：

• 表的字段名是可选的，如果省略，则依次插入所有字段
• 多个列表和多个值之间使用逗号分隔
• 值列表必须和字段名列表数量相同，且数据类型相符
• 如果插入的是表中部分数据，字段名列表必须填写
• 如果插入数据的值列表与表的字段名列表一一对应且个数相同，则字段名可以省略

例如，向student表中插入一条记录：

INSERT INTO `student` 
(`loginPwd`,`studentName`,`gradeId`
,`phone`,`bornDate`) 
VALUES('123','黄小平',1,'13988888888','1996-5-8');
#或者 插入数据的值列表与表的字段名列表一一对应且个数相同
#，则字段名可以省略
INSERT INTO `student`
VALUES('123','黄小平',1,'13988888888','1996-5-8');

2.插入多行数据

语法：

INSERT INTO 表名(字段名列表) VALUES(值列表1),(值列表2),...,(值列表n);

例如，一次向subject表中插入三条数据，SQL语句如下：

INSERT INTO `subject`(`subjectName`,`classHour`,`gradeID`) 
VALUES('Logic Java',220,1),('HTML',160,1),('Java OOP',230,2);

3.将查询结果插入到新表

语法：

CREATE TABLE 新表(SELECT 字段1,字段2,...... FROM 原表);

例如，将student表中的studentName，phone字段数据保存到新表phoneList中，SQL语句如下：

CREATE TABLE `phoneList` (SELECT `studentName`,`phone` FROM `student`);

使用DML更新数据

语法：

UPDATE 表名 SET 列名1=更新值1,列名2=更新值2 [WHERE 更新条件];

其中：
（1）SET后面可以紧随多个"列名=更新值"，修改多个数据列的值，不限一个，使用逗号分隔。
（2）WHERE子句是可选的，用来限制更新数据的条件，若不限制，则整个表的所有数据行都会被更新。

例如：

#student表中，要把所有学生性别更新为女性
UPDATE `student` SET sex='女';

对于地址为'清华大学'的学生，若这个班级改为北京大学，则需要按照条件更新

UPDATE student
SET address='北京大学'
WHERE address='清华大学';

将所有分数低于或等于95分的成绩都在原来的基础上加5分，代码如下：

UPDATE result
SET studentResult = studentResult + 5
WHERE studentResult<=95;

注意： 更新数据时，一般都有条件限制，别忘了写WHERE条件语句，否则将更新表中的所有行的数据，这可能导致有效数据的丢失。

使用DML删除数据

DELETE删除数据

语法如下：

DELETE [FROM] 表名 [WHERE<删除条件>];

例如：

#删除学生表中姓名为'小明'的数据的SQL语句如下：
DELETE FROM `student` WHERE studentName = '小明';

注意：

#如果要删除的行的主键值被其他表引用，
#例如分数表中的studentID引用了学生表中的studentNo列，
#也就是约束冲突！那么删除操作会报错，比如：
DELETE FROM student WHERE studentNo = 22;

TRUNCATE TABLE删除数据

TRUNCATE TABLE用来删除表中的所有行，功能上类似于没有WHERE子句的DELETE语句。

例如，要删除学生表中的所有记录行，代码如下：

TRUNCATE TABLE student;

与DELETE相比较：
优点：TRUNCATE TABLE比DELETE执行速度快，使用的系统资源和事务日志资源更少，并且删除数据后表的标识列会重新开始编号。

缺点：TRUNCATE TABLE删除表中的所有行，但是表的结构、列、约束、索引等不会被改动；TRUNCATE TABLE不能用于有外键约束引用的表，这种情况下，需要用DELETE语句；

实际工作中，不建议使用TRUNCATE TABLE语句，因为使用它删除的数据不能恢复还原。

DQL语句

SELECT语句进行查询

最简单的SELECT查询语句可以表示成如下语句：

SELECT <列名|表达式|函数|常量>
FROM <表名>
[WHERE <查询条件表达式>]
[ORDER BY <排序的列名> [ASC或DESC]];
#ASC升序 DESC降序 不写的话默认ASC

1.查询所有的数据行和列

例子：查学生表所有数据

SELECT * FROM `student`

2.查询部分行或列

查询部分列需要列举不同的列名
查询部分行需要使用WHERE子句进行条件限制
例子：

#查地址为北京的学生的编号和姓名
SELECT `studentNo`,`studentName` FROM `student` 
WHERE address = '北京';

#查地址不在北京的学生编号和姓名
SELECT `studentNo`,`studentName` FROM `student` 
WHERE address <> '北京';

3.在查询中使用列的别名

1.AS子句可以用来改变结果集中列的名称，也可以为组合或者计算出的列指定名称，还有一种情况是让标题列的信息更易懂，例如，把studentNo列名查询后显示为"学生编号"。例如：

SELECT `studentNo` AS 学生编号,`studentName` AS 学生姓名
,`address` AS 学生地址
FROM `student`
WHERE address <> '北京';

2.使用计算、合并得到新列的命名。例如，假设在某数据库的顾源表employee中存在firstName列和lastName列，现在需要将这两列合并成一个叫做"姓名"的列，SQL语句如下：

#SELECT `firstName` + '.' + `lastName` AS 姓名 FROM `employee`;
#上面这句话是错的，不知道为什么，字符串拼接失败，输出0
#应该使用CONCAT(str1,str2...,strn) 方法
SELECT CONCAT(`firstName`, `lastName`) AS 姓名 FROM `employee`;

4.查询空值

在SQL语句中采用"IS NULL"或者"IS NOT NULL"来判断是否为空，因此，如果要查询学生表中没有填写email信息的学生，SQL语句如下：

SELECT `studentName` FROM `student` WHERE `email` IS NULL;

5.在查询中使用常量列

将一些常量的默认信息添加到查询输出中，方便计算或统计。

例如，查询学生信息的时候，学校名称统一都是"北大"，SQL语句如下：

SELECT `studentName` AS 姓名,`address` AS 地址,'北大' AS 学校名称
FROM `student`;

查询输出多了一列"学校名称"，该列的所有数据都是"北大"。

常用函数

1.聚合函数

函数名	作用
AVG()	返回某字段的平均值
COUNT()	返回某字段的行数
MAX()	返回某字段的最大值
MIN()	返回某字段的最小值
SUM()	返回某字段的和

例如：

#返回`student`表`score`列的总和
SELECT SUM(score) FROM student;

#返回`student`表`score`列的平均值
SELECT AVG(score) FROM student;

2.字符串函数

函数名	作用	举例
CONCAT(str1，str2，...，strn)	连接字符串str1、str2、...、strn为一个完整字符串	SELECT CONCAT('My','S','QL'); 返回：MySQL
INSERT(str，pos，len，newstr)	将字符串str从pos位置开始，len个字符长的子串替换为字符串newstr	SELECT INSERT('这是Oracle数据库',3,6,'MySQL'); 返回：这是MySQL数据库
LOWER(str)	将字符串str中所有字符变为小写	SELECT LOWER('MySQL'); 返回：mysql
UPPER(str)	将字符串str中所有字符变为大写	SELECT UPPER('MySQL'); 返回：MYSQL
SUBSTRING(str，num，len)	返回字符串str的第num个位置开始长度为len的子字符串	SELECT SUBSTRING('JavaMySQLOracle',5,5); 返回：MySQL

例如：

#将student表的studentName列改为大写：
SELECT UPPER(`studentName`) FROM `student`;

3.时间日期函数

函数名	作用	举例（部分结果与当前日期有关）
CURDATE()	获取当前日期	SELECT CURDATE(); 返回：2018-04-22（当前日期）
CURTIME()	获取当前时间	SELECT CURTIME(); 返回：15：16：20（当前时间）
NOW()	获取当前日期和时间	SELECT NOW(); 返回：2018-04-22 15：16：20（当前日期 当前时间）
WEEK(date)	返回日期date为一年中的第几周	SELECT WEEK(NOW()); 返回：16（2018-04-22为2018年第16周）
YEAR(date)	返回日期date的年份	SELECT YEAR(NOW()); 返回：2018（当前日期的年份）
HOUR(time)	返回时间time的小时值	SELECT HOUR(NOW()); 返回：15
MINUTE(time)	返回时间time的分钟值	SELECT MINUTE(NOW()); 返回：16
DATEDIFF(date1，date2)	返回日期参数date1和date2之间相隔的天数	SELECT DATEDIFF('2018-04-28','2018-04-25'); 返回：3 SELECT DATEDIFF('2018-04-22','2018-04-25'); 返回：-3
ADDDATE(date,n)	计算日期参数date加上n天后的日期	SELECT ADDDATE(NOW(),5); 返回：2018-04-27 15：16：20（日期时间都显示） SELECT ADDDATE('2018-04-22',5); 返回：2018-04-27（只显示日期）

4.数学函数

函数名	作用	举例
CEIL(x)	返回大于或等于数值x的最小整数	SELECT CEIL(2.3); 返回：3
FLOOR(x)	返回小于或等于数值x的最大整数	SELECT FLOOR(2.3); 返回：2
RAND()	返回0~1之间的随机数	SELECT RAND(); 返回：0.552452347862356

LIMIT子句

语法：

SELECT <字段名列表>
FROM <表名或试图>
[WHERE <查询条件>]
[GROUP BY <分组的字段名>]
[ORDER BY <排序的列名>[ASC或DESC]]
[LIMIT [位置偏移量, ]行数];

#LIMIT介绍如下：
#1.
#位置偏移量指从结果集中第几条数据开始显示（第1条记录的位置偏移量是0，
#第2条位置偏移量是1，..，第n条位置偏移量是n-1），
#此参数可选，当省略时从第一条记录开始显示。
#2.
#行数指显示记录的条数

LIMIT子句可以实现数据的分页查询，即从一批结果数据中，规定每页显示多少条数据，可以查询中间某页记录。LIMIT子句经常与ORDER BY子句一起使用，即先对查询结果进行排序，然后根据LIMIT的参数显示其中部分数据。

例如：查询所有年级编号为1的学员信息，按学员升序显示前4条记录，SQL如下：

SELECT * FROM `student` WHERE `gradeId` = 1
ORDER BY `studentNo`
LIMIT 4;

以上例子省略位置偏移量，从第1条记录开始显示，如果每页显示4条记录，要求显示第2页全部数据，经过计算，应从第5条记录开始显示4条数据，SQL如下：

SELECT * FROM `student` WHERE `gradeId` = 1
ORDER BY `studentNo`
LIMIT 4,4;

子查询

子查询在WHERE子句中的一般用法如下：

SELECT ...... FROM 表1 WHERE 字段1 比较运算符 （子查询）;

其中，子查询语句必须放在一对圆括号内；
比较运算符包括>,=,<,>=,<=
习惯上，外面的查询称为父查询，圆括号内嵌入的查询称为子查询。执行时，先执行子查询部分，求出子查询部分的值，再执行整个父查询，返回最后的结果。
因为子查询作为WHERE条件的一部分，所以还可以和UPDATE,INSERT,DELETE一起使用，语法类似于SELECT语句。

注意：
将子查询和比较运算符联合使用，必须保证子查询返回的值不能多于一个！

例如：

#查询所有出生日期`bornDate`在小明之后的学生的信息
SELECT * FROM `student` WHERE `bornDate` > 
(SELECT `bornDate` FROM `student` WHERE `studentName` = '小明');

IN和NOT IN子查询

使用关键字IN可以使父查询匹配子查询返回的多个单字段值。

IN子查询

使用=,>等比较运算符时，要求子查询只能返回一条或空的记录。
当子查询跟随在=,!=,<,<=,>,>=之后时，不允许子查询返回多条记录。

当出现错误提示"Subquery returns more than 1 row"的意思是子查询返回值不唯一。这时只需将=改为IN即可，如下所示：

SELECT `studentName` FROM `student`
WHERE `studentNo` IN(
	SELECT `studentNo` FROM `score`
		WHERE `subjectNo` = (
			SELECT `subjectNo` FROM `subject`
			WHERE `subjectName` = 'Java'
		)AND `studentScore` = 60
);

可以看出IN后面的子查询可以返回多条记录，用于限制学号的筛选范围。

NOT IN子查询

与IN相反，这里就不做更多示范了。

高级查询

EXISTS和NOT EXISTS子查询

EXISTS子查询

在执行CREATE或DROP语句前，可以使用EXISTS语句判断数据库对象是否存在，返回值是TRUE或FALSE。例如，如果存在数据表temp，则先删除它，然后重新创建。

DROP TABLE IF EXISTS temp;

EXISTS也可以作为WHERE语句的子查询，基本语法如下：

SELECT ... FROM 表名 WHERE EXISTS(子查询);

EXISTS关键字后面的参数是一个任意的子查询，如果该子查询有返回行，则EXISTS子查询的结果为true，此时再执行外层查询语句。
如果子查询没有返回行，则EXISTS子查询的结果为false，此时外层语句不再执行检查。

NOT EXISTS子查询

EXISTS与IN一样，允许添加NOT关键字实现取反操作，NOT EXISTS表示不存在

注意：
EXISTS和NOT EXISTS的结果只取决于是否有返回记录，不取决于这些记录的内容，所以EXISTS和NOT EXISTS子查询后SELECT语句中的字段列表通常是无关紧要的。

子查询注意事项

注意事项

在完成较复杂的数据查询时，经常会使用到子查询。编写子查询语句时，要注意以下事项：

1.子查询语句可以嵌套在SQL语句中任何表达式出现的位置：
在SELECT语句中，子查询可以被嵌套在SELECT语句的列、表和查询条件中，即SELECT子句、FROM子句、WHERE子句、GROUP BY子句和HAVING子句。

/*嵌套在SELECT语句的SELECT子句中的子查询语句如下*/
SELECT (子查询) FROM 表名;
/*注意：子查询结果为单行单列，但不必指定列别名。*/

/*嵌套在SELECT语句的FROM子句中的子查询语句如下*/
SELECT * FROM (子查询) AS 表的别名;
/*注意：必须为表指定别名，一般返回多行多列数据记录，可以当作一张临时表。*/

2.只出现在子查询中而没有出现在父查询中的表不能包含在输出列中
多层嵌套子查询的最终数据集只包含父查询（即最外层的查询）的SELECT子句中出现的字段，而子查询的输出结果通常会作为其外层子查询数据源或用于数据判断匹配。

常见错误：

SELECT * FROM (SELECT * FROM `result`);
/*这个子查询语句产生语法错误的原因在于，
主查询语句中的 FROM 子句是一个子查询语句，因此应该为子查询结果集指定别名。
正确代码如下：*/
SELECT * FROM (SELECT * FROM `result`) AS Temp;

分组查询

使用GROUP BY进行分组查询

比如，根据课程编号分组，求每门课程的平均分：

SELECT `subjectNo`, AVG(`studentScore`) AS 课程平均成绩
FROM `score`
GROUP BY `subjectNo`;

多列分组查询

例如：统计不同年级的男，女学生人数。理论上先把每个学年分开，再针对每个学年，把男女学生人数各自统计，也就是需要按照两个列进行分组：年级，性别。

SQL语句如下：

SELECT COUNT(*) AS 人数, `grade` AS 年级, `sex` AS 性别 
FROM `student`
GROUP BY `grade`,`sex`
ORDER BY `grade`;

使用HAVING子句进行分组筛选

WHERE子句只能对没有分组统计前的数据行进行筛选。
对于分组后的条件的筛选必须使用HAVING子句。
简单的说，HAVING子句用来对分组后的数据进行筛选，将"组"看作"列"来限定条件。

例如：

#查询年级总人数超过200人的年级
SELECT COUNT(*) AS 人数, `gradeId` AS 年级 FROM `student`
GROUP BY `gradeId`
HAVING COUNT(*)>200;

注意：
HAVING和WHERE子句可以在同一个SELECT语句中一起使用，使用顺序如下：
WHERE----->GROUP BY------->HAVING

例如：

#查询有两个或两个以上员工工资不低于2000元的部门编号
SELECT 部门编号, COUNT(*) FROM 员工信息表
WHERE 工资 >= 2000
GROUP BY 部门编号
HAVING COUNT(*)>1;

多表连接查询

多表连接查询的分类

1.内连接查询
内连接查询是最典型、最常用的连接查询，它根据表中共同的列来进行匹配。特别是两个表存在外主键关系时通常会使用内连接查询。

2.外连接查询
外连接查询是至少返回一个表中的所有记录，根据匹配条件有选择性的返回另一张表的记录。外连接可以是左外连接、右外连接。

内连接查询

内连接查询通常会使用"="或"<>"等比较运算符来判断两列数据值是否相等。
求的是几个表满足条件的交集。
内连接使用INNER JOIN...ON关键字或WHERE子句来进行表之间的关联。内连接查询可以通过以下两种方式实现：

1.在WHERE子句中指定连接条件
例如，查询学生姓名和成绩的SQL如下：

SELECT student.`studentName`, result.`subjectNo`,
result.`studentResult` FROM student, result
WHERE student.`studentNo` = result.`studentNo`;
/*上面这种形式的查询，相当于FROM后面紧跟了两个表名，
在字段列表中用"表名.列名"来区分列，
再在WHERE条件子句中加以判断，要求学生编号信息相同。*/

2.在FROM子句中使用INNER JOIN...ON

上面的查询也可以用INNER JOIN...ON子句来实现，SQL如下：

SELECT S.`studentName`, R.`subjectNo`, R.`studentResult`
FROM student AS S
INNER JOIN result AS R ON (S.`studentNo` = R.`studenNo`);
/*
1）INNER JOIN用来连接两个表
2）INNER 可以省略
3）ON 用来设置条件
4）AS 指定表的别名。如果查询的列名在用到的两个表或多个表中不重复
，则对这一列的引用不必用表名来限定
*/

查询科目编号为1的分数大于60分的学生的姓名和分数。WHERE子句限定查询条件。
SQL如下：

SELECT S.`StudentName`, R.`subjectNo`, R.`studentResult`
FROM `student` AS S
INNER JOIN result AS R ON (S.`StudentNo` = R.`StudentNo`)
WHERE R.`score` > 60 AND R.`subjectNo` = 1;

内连接查询可以查询多个表。如下SQL：

SELECT S.`StudentName` AS 学生姓名
, R.`subjectNo` AS 课程名称
, R.`studentResult` AS 考试成绩
FROM `student` AS S
INNER JOIN `result` AS R ON (S.`StudentNo` = R.`StudentNo`)
INNER JOIN `SUBJECT` AS SU ON (SU.`subjectNo` = R.`subjectNo`);

外连接查询

外连接查询中参与连接的表有主从之分，以主表的每行数据匹配从表的数据列，将符合连接条件的数据直接返回到结果集中；对那些不符合连接条件的列，将被填上NULL值后再返回到结果集中。

1.左外连接查询

左外连接查询的结果集包括LEFT JOIN子句中指定的左表的所有行，而不仅仅是连接列所匹配的行。若左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列均为空值。

左外连接查询使用LEFT JOIN...ON或LEFT OUTER JOIN...ON关键字来进行表之间的关联。

例如：要统计所有学生的考试情况，要求显示所有参加考试学生的每次考试分数，没有参加考试的学生也要显示出来。这时候，以学生信息表为主表（有时也叫左表）、学生成绩表为从表的左外连接查询的SQL语句如下：

SELECT  S.`StudentName`, R.`subjectNo`, R.`studentResult`
FROM `student` AS S
LEFT OUTER JOIN `result` AS R ON S.`StudentNo` = R.`StudentNo`;
/*
其中，从学生信息表中把每条记录跟成绩表的记录进行数据匹配。
若成功，则返回到记录集。
若不成功，则返回NULL值来进行填充记录集。
如果有学生没参加考试，所以成绩表中没有相关的考试记录
，对应的科目编号和成绩以NULL填充进去。
`student`表为左表
`result`表为右表
显示左表全部信息，右表显示与左表匹配成功的信息，没成功的就填NULL
*/

2.右外连接查询

右外连接查询与左外连接查询类似，只不过要包含右表中所有匹配的行。若右表中有的项在左表中没有对应的项，则以NULL填充。

右外连接查询使用RIGHT JOIN...ON或者RIGHT OUTER JOIN...ON关键字来进行表之间的关联。例如，在某数据库中，存在书籍表Books和出版商表Publishers之间的右外连接将包括所有的出版商，在表Books中没有书名的出版商（很久没出书了）也会被列出。例如：

SELECT Books.`Book_id`, Books.`book`, Publishers.`Pub_name`
FROM `titles`
RIGHT OUTER JOIN `Publishers` 
ON Books.`Pub_id` = Publishers.`Pub_id`;

UNION和UNION ALL操作符

UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
简单来说就是将两张表在结果集中以上下的方式连接在一起，拼接成一张表（区别于内连接，左右连接，它们均为左右方向的连接）。

注意：UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT语句中的列的顺序必须相同。

UNION 语法：

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
#默认地 UNION 操作符选取不同的值 如果允许重复的值 请使用 UNION ALL

UNION ALL 语法：

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
/*UNION(UNION ALL) 结果集中的列名总是等于 
UNION(UNION ALL) 中第一个 SELECT 语句中的列名*/

例子1

求出成绩各排名前五的男生和女生信息（成绩降序排列，且先女后男）
表信息如下：

id（pk int）	sex（varchar）	score（int）
1	男	90
2	男	91
3	男	92
4	男	93
5	男	94
6	男	95
7	男	96
8	女	90
9	女	91
10	女	92
11	女	93
12	女	94
13	女	95
14	女	96

SQL语句如下：

(SELECT * FROM demo WHERE sex = '女' ORDER BY score DESC LIMIT 5)
UNION ALL 
(SELECT * FROM demo WHERE sex = '男' ORDER BY score DESC LIMIT 5)

运行结果如下：

id	sex	score
14	女	96
13	女	95
12	女	94
11	女	93
10	女	92
7	男	96
6	男	95
5	男	94
4	男	93
3	男	92

例子2

表信息：
Employees_China表：

E_ID	E_Name
01	Zhang, Hua
02	Wang, Wei
03	Carter, Thomas
04	Yang, Ming

Employees_USA表：

E_ID	E_Name
01	Adams, John
02	Bush, George
03	Carter, Thomas
04	Gates, Bill

UNION命令：列出中国美国的所有雇员名（重复项只显示一次）

(SELECT E_Name FROM Employees_China)
UNION
(SELECT E_Name FROM Employees_USA)

运行结果如下：

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill

注意：这个命令无法列出在中国和美国的所有雇员。在上面的例子中，我们有两个名字相同的雇员，他们当中只有一个人被列出来了。UNION命令只会选取不同的值。

---

UNION ALL命令：

UNION ALL 命令和 UNION 命令几乎是等效的，不过 UNION ALL 命令会列出所有的值。

实例：
列出在中国和美国的所有的雇员（重复项就重复显示就可）：

(SELECT E_Name FROM Employees_China)
UNION ALL
(SELECT E_Name FROM Employees_USA)

E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill

SQL语句的综合应用

暂无