MySQL------数据表的创建和简单、条件，模糊查询

基本概念：

数据库：

英文单词DataBase，简称DB,按照一定格式存储数据的一些文件的组合，顾名思义:存储数据的仓库，实际上就是一堆文件，这些文件中存储了具有特定格式的数据。

数据库管理系统：

DataBaseManagement,简称DBMS

数据库管理系统是专门用来管理数据库中的数据，数据库管理系统可以对数据库当中的数据进行增删改查。

常见的数据库管理系统：

MySQL,Oracle,MS SqlServer,DB2,sybase等…

SQL:结构化查询语言

首先，我们需要去学习SQL语句，再通过编写SQL语句，由DBMS负责执行SQL语句，最终来完成数据库中数据的增删改查操作。

SQL是一套标准的语言，我们主要学习的就是SQL语句，而SQL不仅可以在mysql中使用，同时也可以在Oracle中进行使用，还可以在DB2中进行使用。

三者之间的关系：

数据库操作系统----执行------>结构化查询语句—对数据进行操作

安装数据库管理系统MySQL：

大家可以参考如下链接文章的安装教程，非常详细。

http://t.csdn.cn/UBd09

查看MySQL在计算机的位置：

右击计算机，找到电脑：

如下所示：

点击服务和应用程序：

点击服务：

找到Mysql服务：

右击该服务，点击属性：

MySQL的服务默认是“启动”的状态，只有启动了MySQL才能用，默认情况下是“自动”启动，自动启动表示下一次重启操作系统的时候，自动启动该服务。

我们也可以改变服务的默认配置：服务商点击右键，属性，然后可以选择启动方式。

右击服务：

登录MySQL：

Win+R,在命令行输入：

mysql -uroot -p(密码）显示密码的登录方式

登录成功的页面如下所示：

退出MySQL：

在命令行输入：

exit

在命令行输入：

mysql -uroot -p隐藏密码的登录方式

退出MySQL：

在命令行输入：

exit

MySQL常用命令：

这些命令不区分大小写

查看MySQL中有哪些数据库：

命令：

show databases;//分号不要丢，分号是英文的
1

显示如下：

使用某个数据库：

命令：

use 数据库名;//分号不要丢，分号是英文的
1

举例：

假设现在我们使用test数据库；

创建新的数据库：

命令：

create database 新数据库名;//分号不要丢，分号是英文的
1

举例：

数据库当中最基本的单元是：表（table）

例如Excel就是常见的表，它有行有列，并且很直观的能够展示数据，任何一张表都有行和列，行（row）:被称为数据/记录。列（column）：被称为字段。

对如下所示这张表，就有姓名字段，性别字段，年龄字段

注：数据库也是有数据类型的，比如，规定性别字段是数字，那么性别字段中的数据就只能是数字，不能是字符串或者字符。

每一个字段都有：字段名，数据类型，约束等属性

前两个比较好理解，那么约束是什么呢？

不知道大家有没有留心过，在注册新账号的时候，用户名不能重复，这就是具有约束性。

查看某个数据库中包含的表：

命令：

show tables;//分号不要丢，分号是英文的
1

举例：

SQL语句的分类：

DQL[Data Query language]：数据查询语言（凡是带有select关键字的都是查询语句）

select......
1

DML[Data Manipulation]:数据操作语言（凡是对表当中的数据进行增删改的都是DML）

insert(增） delete（删） update(改）
1

操作的数据为表中的数据data。

DDL[Data Definition language]：数据定义语言（凡是带有create[新建]，drop[删除]，alter[修改]的都是DDL）

DDL主要操作的是表的结构，不是表中的数据
1

TCL[Transaction control language]：事务控制语言

包括：

事务提交：commit

事物回滚：rollback
1
2
3

DCL[Data Control language]:数据控制语言

例如;授权（grant），撤销权限（revoke）.......
1

创建学生表：

首先，我们先来查看MySQL服务器当前的所有数据库：

命令：

show databases;
1

查询结果：

删除数据库：

-- 如果sys数据库存在,则直接删除，否则编译器会进行提示
drop database if exists sys;
1
2

创建数据库并指定默认字符集：

命令：

-- 编码为utf8
create database 库名 character set utf8;
1
2

首先，我们先来创建一个学生选课系统数据库：

命令：

create database stu_course default charset utf8;
1

使用数据库：

下面我们就可以使用刚刚创建创建好的学生选课数据库了

use stu_course;
1

查看当前数据库中所有表：

show tables;
1

创建学生表：

MySQL支持所有标准的SQL数据类型，主要分3类:

    数值类型
    字符串类型
    时间日期类型
1
2
3

语法格式如下所示：

-- 表名:所要定义的基本表的名字
-- 字段名:组成该表的各个属性(列)
-- 列级完整性:涉及相应属性列的完整性约束条件
-- 表级完整性约束条件:涉及一个或多个属性列的完整性约束条件
create table 表名(字段名1 数据类型 [列级完整性约束条件],字段名2 数据类型 [表级完整性约束].....)
1
2
3
4
5

如果完整性约束条件涉及到该表的多个属性列，则必须定义在表级上，否则既可以定义在列级也可以定义在表级

举例：

 
create table TbStudent
(
stuid integer not null,
stuname varchar(20) not null,
stusex bit default 1,
stubirth datetime not null,
stutel char(11),
stuaddr varchar(255),
stuphoto longblob,		-- longblob:可以存储二进制的大的容器对象
primary key (stuid)
);
1
2
3
4
5
6
7
8
9
10
11
12

修改学生表：

修改数据表结构：

语法格式：

-- 向该表中增加新的列
alter table 表名 add 新列名 数据类型 [完整性约束]

-- 修改列名
ALTER table 表名 change 旧列名 新列名 新列名的数据类型;

-- 修改列名的类型
alter table 表名 modify column 列名 新的列的类型

--删除表中的某一列
alter table 表名 drop column 要删除的列名;

-- 添加主键约束
alter table  表名 add primary key (字段)

-- 删除主键约束
alter table 表名 drop primary key

-- 添加非空约束
alter  table 表名  modify 列名 数据类型  not null 

-- 删除非空约束
alter table 表名  modify 列名 数据类型 null

-- 添加唯一性约束
alter table 表名 add unique 约束名（字段）
-- 删除唯一性约束
alter table 表名  drop key 约束名

-- 添加自动增长
alter table 表名  modify 列名 int  auto_increment
-- 删除自动增长
alter table 表名 modify 列名 int  

-- 添加外键约束
alter table 表名 add constraint 约束名 foreign key(外键列) 
references 主键表（主键列）
-- 删除外键约束:
第一步:删除外键
alter table 表名 drop foreign key 约束名
第二步:删除索引
alter  table 表名 drop  index 索引名
-- 约束名和索引名一样

-- 添加默认值
alter table 表名 alter 列名  set default '值'
-- 删除默认值
alter table 表名  alter 列名  drop default
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48

创建课程表：

create table TbCourse
(
cosid integer not null,
cosname varchar(50) not null,
coscredit tinyint not null,//tinyint:设置无符号： 0 到 255 的整型数据；未设置无符号：-128到127
cosintro varchar(255)
);
1
2
3
4
5
6
7

给课程表指定主键：

alter table TbCourse add constraint pk_course primary key (cosid);
1

主键：

主关键字（主键，primary key）是被挑选出来，作为表的某一行的唯一标识的候选关键字

一个表只有一个主关键字，主关键字又可以称为主键，主键可以由一个字段，也可以由多个字段组成，分别成为单字段主键或多字段主键。

创建学生选课记录表：

create table TbSC
(
scid integer primary key auto_increment,-- 自动增长
sid integer not null,
cid integer,
scdate datetime not null,
score float
);
1
2
3
4
5
6
7
8

外键：

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

添加外键：

alter table TbSC add foreign key (sid) references TbStudent (stuid) on delete cascade on update cascade;
alter table TbSC add foreign key (cid) references TBCourse (cosid) on delete set null on update cascade;
1
2

注：

on delete cascade是删除级联
on update cascade是更新级联
1
2

若删除级联和更新级联同时存在，则中间不需要逗号,那么设置级联有什么作用呢？

on update cascade 和on delete cascade是数据库外键定义的可选项，用来设置当主键表中的被参考列的数据发生变化时，外键表中相应字段的变换规则

update 是主键表中被参考字段的值更新，delete是指在主键表中删除一条记录，可对应如下四个选项：

no action 表示不做任何操作
set null 表示在外键表中将相应字段设置为null
set default 表示设置为默认值
cascade 表示级联操作
如果为on update cascade，主键表中被参考字段更新，外键表中对应行相应更新
如果为on delete cascade，主键表中的记录被删除，外键表中对应行相应删除
1
2
3
4
5
6

添加学生记录：

insert into TbStudent values (1001, '张三丰', default, '1978-1-1', '成都市一环路西二段17号', null);
insert into TbStudent (stuid, stuname, stubirth) values (1002, '郭靖', '1980-2-2');
insert into TbStudent (stuid, stuname, stusex, stubirth, stuaddr) values (1003, '黄蓉', 0, '1982-3-3', '成都市二环路南四段123号');
insert into TbStudent values (1004, '张无忌', 1, '1990-4-4', null, null);
insert into TbStudent values 
(1005, '丘处机', 1, '1983-5-5', '北京市海淀区宝盛北里西区28号', null),
(1006, '王处一', 1, '1985-6-6', '深圳市宝安区宝安大道5010号', null),
(1007, '刘处玄', 1, '1987-7-7', '郑州市金水区纬五路21号', null),
(1008, '孙不二', 0, '1989-8-8', '武汉市光谷大道61号', null),
(1009, '平一指', 1, '1992-9-9', '西安市雁塔区高新六路52号', null),
(1010, '老不死', 1, '1993-10-10', '广州市天河区元岗路310号', null),
(1011, '王大锤', 0, '1994-11-11', null, null),
(1012, '隔壁老王', 1, '1995-12-12', null, null),
(1013, '郭啸天', 1, '1977-10-25', null, null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

添加课程记录：

insert into TbCourse values 
(1111, 'C语言程序设计', 3, '大神级讲师授课需要抢座'),
(2222, 'Java程序设计', 3, null),
(3333, '数据库概论', 2, null),
(4444, '操作系统原理', 4, null);
1
2
3
4
5

添加学生选课记录：

insert into TbSC values 
(default, 1001, 1111, '2016-9-1', 95),
(default, 1002, 1111, '2016-9-1', 94),
(default, 1001, 2222, now(), null),
(default, 1001, 3333, '2017-3-1', 85),
(default, 1001, 4444, now(), null),
(default, 1002, 4444, now(), null),
(default, 1003, 2222, now(), null),
(default, 1003, 3333, now(), null),
(default, 1005, 2222, now(), null),
(default, 1006, 1111, now(), null),
(default, 1006, 2222, '2017-3-1', 80),
(default, 1006, 3333, now(), null),
(default, 1006, 4444, now(), null),
(default, 1007, 1111, '2016-9-1', null),
(default, 1007, 3333, now(), null),
(default, 1007, 4444, now(), null),
(default, 1008, 2222, now(), null),
(default, 1010, 1111, now(), null);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

查看表结构：

命令：

select *from 表名;//从某个表查询该表的所有数据   *代表所有字段
1

举例：

显示当前已存在的所有表：

show tables;
1

在已存在的表中选择任意表进行查询：

select *from tbcourse;
1

输出：

如果我们想查看表的结构，使用上面这种方法显示表的全部信息好像也挺清楚，但是如果当前表存放了成百上千的数据呢？

这时如果我们还将表的全部信息显示出来就会有点麻烦，所以我们就要使用另一个命令来查询表的结构

desc 表名；
1

显示如下：

输出的两种内容，很明显的区别就是：第二种仅仅输出了关于表结构的信息，而没有显示任何的数据，第二章表指出了该表的字段，涉及的信息项等内容

MySQL----常用命令：

– 查看当前数据库的版本号

命令：

select version();
1

输出：

– 查看当前使用的数据库

命令：

select database();
1

显示如下：

不见分号不执行：

分号表示一条完整命令的结束：分号代码输入结束，开始执行命令

输出：

简单查询：

查询单个字段：

命令：

select 字段名 from 表名;
1

注：select和from都是关键字，字段名和表名都是标识符

所有的sql语句都是以";"结尾的，另外sql语句不区分大小写

举例:

select cosid from tbcourse;	//查询tbcourse表中的cosid字段
1

查询结果：

查询多个字段：

命令：

select 字段1，字段2..... from tbcourse;   -- 字段和字段之间用逗号隔开
1

举例：

select cosid,cosname from tbcourse;
1

查询结果：

查询所有字段：

方法一：将所有的字段名都写上

命令：

select 字段1，字段2.....字段n from tbcourse;
1

方法二：使用（*）—表示所有的字段

命令：

select * from tbcourse;
1

举例：

select cosid,cosname,coscredit,cosintro from tbcourse;-- 使用字段名的方法
1

查询结果：

select * from tbcourse;-- 使用*的方式
1

显示如下：

对比两种方式，在书写命令上，显然使用* 的方式大大简化了我们书写命令的量，但是在实际开发中，并不建议这样的查询方法，原因是：它的效率低，并且可读性差，因为在执行命令的过程中，计算机首先会将 *转化成实际的字段名，再执行命令

给查询的列起别名：

在这里使用as关键字起别名：

命令：

select 将要修改别名的列名 as 想要修改之后的列名 from  表名;
1

举例：

select cosid as id from  tbcourse;-- 将查询结果的列名cosid修改为id
1

查询结果：

select cosid,cosname as id from  ;-- 将后者的列名进行修改，而前者不变
1

查询结果：

注意：只是将显示的查询结果列名显示为修改后的列名，但是原表名还是叫修改之前的表名

注：select语句是永远都不会进行修改操作的（只负责查询）

as关键字也可以省略！

select 要修改的列名 修改后的列名 from 表名;
1

举例：

select cosid,cosname id from  tbcourse;
1

别名中含有空格：

举例：

select cosid as id name from  tbcourse; -- id name为修改之后的别名
1

编译并未通过：

解决办法：

将含有空格的列名用单引号或者双引号括起来

单引号是标准的，任何数据库都可以进行使用，双引号在oracle数据库中无法使用，但在MySQL中可以使用。

举例：

命令：

select scid  "sc id" from  tbsc;
1

显示如下：

列参与数学运算：

举例：

select score*100 from tbsc;		-- 将tbsc中的score扩大100倍
1

查询结果：

原数据：

我们不难发现，在参与了数学运算之后，数据的大小不仅发生了改变，而且对应的列名也发生了改变，而变化后的列名看起来有点傻，因此，我们就可以使用上面的方法对其列名进行修改。

命令：

select score*100  as '扩大100倍之后的数据' from tbsc;
1

显示如下：

需要强调的是，如果要修改的列名是中文名称，那么需要用单引号括起来。

条件查询：

将符合条件的数据显示出来，而不是显示所有的数据。

select 列名1,列名2  from tbsc where 限制条件;
1

按条件表达式筛选：

大于[>],等于[=],小于[<]：

举例：

select cid  from tbsc where score>90;  -- 查询tbsc表中成绩大于90分的id
1

查询结果：

不等于：<>或！=

select scid from tbsc where score<>95; //查询tbsc表中分数不等于95的scid
1

查询结果：

除上述所举的两种条件表达式之外，还有<=，>,>=等就不一一举例了。

按条件表达式筛选：

between…and…:

举例：

select scid from tbsc where score between 90 and 100 ;
-- 查询分数在90-100的学生的scid
1
2

查询结果：

is null为null(is not null不为空）：

select scid from tbsc where score is null; -- 注意不要将is写成=
1

查询结果：

注：在数据库当中，null不能使用等号进行衡量，需要使用 is null，因为数据库中的null代表什么也没有，它不是一个值，所以不能使用等号衡量

is not null的用法相似，这里就不过多赘述了

and：并且

举例：

select * from tbsc where cid=1111 and score>=90 ; 
-- 查询cid的值为1111，并且分数大于90分的人的信息
1
2

查询结果：

or:或者

举例：

select * from tbsc where cid=1111 or score>=90 ; 
-- 查询cid的值为1111，或者分数大于90分的人的信息
1
2

查询结果：

and和or同时出现：

举例：

假设现在我们需要查询该表中scid大于3，并且cid等于1111或者scid大于3，score大于等于90的员工

请问如下命令，能否得到我们想要的结果？

select * from tbsc where scid>3 and cid=1111 or score>=90 ; 
1

查询结果：

通过输出结果，我们发现scid小于3的也被显示出来，原因就是我们的限制条件中:包含的and和or是存在优先级顺序的，and的优先级高于or，那么编译器会根据优先级将该命令解释为找出满足scid>3 and cid=1111或者满足score>=90的相关信息

要想正确输出我们想要的信息，解决办法即为给有关or的条件带括号

修改后的命令：

select * from tbsc where scid>3 and (cid=1111 or score>=90) ; 
1

查询结果：

in：包含，相当于多个or [not in不在] 这个范围中

举例：

select * from tbsc where cid in (1111,4444) ; 
-- 查询cid等于1111或者等于4444的有关信息；
1
2

显示如下：

括号的含义：

select * from tbsc where score in (94,95) ; 
1

注意：这里的（94,95）并不是94-95，他并不代表一个范围，而是表示score等于94或等于95

查询结果：

not in和它的使用方法相同，就是含义为相反的意思，这里就不赘述了。

模糊查询：

like：

称为模糊查询，支持%或下划线匹配，%匹配任意多个字符

下划线：任意一个字符，一个下划线只匹配一个字符（%是一个特殊的符号，_也是一个特殊的符号）

包含：

举例：

select * from tbsc where sid like '%6%' ; 
-- 查询该表的sid中含有数字6的信息
1
2

查询结果：

以某个字符开始：

select * from tbsc where scid like '1%' ; 
-- 查询该表中scid以1开头的有关信息
1
2

显示如下：

以某个字符结束：

select * from tbsc where sid like '%6' ; 
-- 查询该表中sid以6结尾的有关信息
1
2

非特殊位置的某个字符的查找：

select * from tbcourse where cosname like '_语%' ; 
-- 查询该表中cosname第二个字为语的有关信息
1
2

查询结果：

如果想查找第三个，则两个下划线，以此类推。

当要查询的字段中包含下划线：

插入包含下划线的数据：

insert into tbstudent values(1000,'jason_lisa',1, '1978-01-01 00:00:00', '成都市一环路西二段17号',null);
1

查询该数据：

select *from tbstudent where stuname like '%_%';
1

查询结果：

此时输出结果将所有数据都显示出来了，并不是我们想要的名字中包含下划线的信息

解决办法即为：将下划线转义

命令：

select *from tbstudent where stuname like '%\_%';
-- \表示转义，将其下划线转化为普通的下划线
1
2

查询结果：

分组函数/聚合函数/多行处理函数：

多行处理函数的特点：输入多行，最终输出一行

分组函数在使用时候必须先进行分组，才能使用，如果你没有对数据进行分组，整张表默认为一组

举例：

select max(score) from tbsc;		-- 查询该表中的分数最大值
1

其他几个使用方法也是类似的，这里就不进行举例了

分组函数在使用的时候需要注意的问题：

1：我们不需要和之前的单行处理函数一样，当参与的数据中包含NULL，如果不进行处理，会导致结果也为NULL，分组函数自动忽略NULL，将非NULL的值进行数据运算

NULL不是一个值，因此，我们也不能理解成=NULL / !=NULL,而是应该理解成 is NULL或者 not is NULL

2:分组函数中count(*)和count(具体的字段)的区别：

count(具体的字段):表示统计该字段下所有不为NULL的元素的总数

count(*)：统计表中的所有行数（只要有一行中某一列存在数据count，则++）

那么是否存在有一行数据的所有列数都为NULL呢？

答案是不存在的，无论是在MySQL还是SQL中，都不会存在这样的列

3：分组函数不能直接使用在where字句中

举例：

查询比最低分数高的分数：

select stuname,score from tbsc,tbstudent where score>min(score);
1

报错：

无效的使用了分组查询,原因是：分组函数出现在了where字句中

4：所有的分组函数可以组合起来一起使用

举例：

//查询最高，最低，平均，总分数
select max(score),avg(score),min(score),sum(score) from tbsc;
1
2

查询结果：

分组查询：

什么是分组查询？

在实际的应用中，可能会有这样的需求，需要先进行分组，再对每一组的数据进行操作，这个时候我们需要使用分组查询，怎么进行分组查询呢？

语法格式：

select
.....
group by
.......
1
2
3
4

单个字段进行分组：

//查询每个课程对应的平均分数
select cosname,avg(score) from tbcourse,tbsc group by cosname;
1
2

查询结果：

关键字的顺序问题：

经过之前的学习，我们学习了很多的关键字，例如：select， from， where， group by等等，那么如果将他们综合使用，他们的先后顺序是否可以颠倒？

答案是不可以的，在书写的时候，必须遵循以下顺序：

select
.......
from
.......
where
.......
group by
.......
order by
......
1
2
3
4
5
6
7
8
9
10

但是他们的执行顺序可不是根据这样的先后顺序执行的

执行顺序如下：

from  where  group by  select  order by
1

执行顺序的限制，也就会导致分组函数不能直接使用在where字句中，原因是分组函数必须先分组再使用，而根据语法执行顺序，group by的执行顺序在where后面，因此分组函数不能直接使用在where后面

那么我们上述提到的实例，也没有进行分组，为什么可以使用分组函数max呢？

select max(score) from tbsc;
1

原因是select的执行顺序在group by之后，其实已经进行分组了，才输出结果

易错点：

select stuname,cosname,avg(score) from tbcourse,tbsc,tbstudent group by cosname;
1

查询结果：

根据查询结果，我们会发现，这样的查询是毫无意义的，为什么会这样说呢？原因是我们是根据课程进行分组，，但学生姓名和课程名之间完全没有关联，这样毫无意义的语法虽然在MySQL中可以正常查询，但是在Oracle中是没办法执行的，那么也就说明Oracle的语法比MySQL更为严谨一些

结论：在进行分组查询时，select后面只能跟参加分组的字段和分组函数，跟其他不相关的字段是毫无意义的，在除了MySQL以外的其他数据库中会报错的

按两个字段进行分组：

//查询不同每门课程不同cid的最高分数
select cosname,cid,max(score) from tbsc,tbcourse group by cosname,cid;
1
2

查询结果：

where和having怎么选？

举例：

要求显示最低分数小于85的分数
1

方案一：

思路:首先根据课程进行分组，找到每个课程的最小值，再对找出的最小值进行深度筛选，选出小于85的分数

先分组再筛选：

select cosname,min(score) from tbcourse,tbsc group by cosname having min(score)<85;
1

having函数可以对分完组之后的数据进一步过滤，having不能单独使用，并且having不能代替where，having必须和group by搭配使用

方案二：

思路：先查找出每个课程的最小分数且最小分数必须小于85，最后根据课程名进行分组

先筛选再分组：

select cosname,min(score) from tbcourse,tbsc where score<85 group by cosname;
1

对比两个方案，很明显第一个方案的查找效率更低一些，它涉及的不相关数据更多一些

因此，我们可以得出一个结论，where和group by，优先选择 where， where实在解决不了的，我们再选择group by

那么什么样的问题是where无法解决的呢？

答案就是：需要查询数据的限制条件中包含分组函数的

举例：

显示课程平均分数大于85的

select avg(score),cosname from tbsc,tbcourse group by cosname having  avg(score)>85;
1

有的人会产生疑惑这里真的用不了where吗？

select avg(score),cosname from tbsc,tbcourse where  score>85 group by cosname ;
1

这样不就好了吗？

你仔细查看这里虽然确实查询了每门课程的平均薪资，但是where后面的限制条件不知不觉被修改为了分数大于85，而不是平均分数。

因此该实例必须使用having

单表查询总结：

select
.....
from
.....
where
......
having
......
order by
......
1
2
3
4
5
6
7
8
9
10

以上关键字只能根据这个顺序，不能进行先后颠倒

执行顺序：

from where group by having select order by
1

综合应用：

找出每个课程的平均分数，要求显示除C语言程序设计之外平均分数大于85的，按照平均分数降序排列：

select cosname,avg(score) from tbsc,tbcourse 
where cosname!='C语言程序设计' 
group by cosname  
having avg(score)>85
order by avg(score) desc;
1
2
3
4
5

查询结果：

连接查询：

distinct关键字：

将查询结果中的重复记录进行删除，原表中的数据不会发生改变

举例：

去除单个字段中的重复记录：

select distinct(score) from tbsc;
1

查询结果：

去除多个字段中的重复记录：

distinct出现在多个字段前，表示多个字段联合起来去除重复记录

select distinct cosname,score from tbcourse,tbsc;
1

查询结果：

但是下面这种写法是错误的，因为查询的字段是两个，如果单独的将一个字段中的重复信息进行去除，很有可能导致数据无法相互对应

select score,distinct cosname from tbcourse,tbsc;
1

distinct只能出现在第一个字段名前面

distinct和分组函数结合使用：

//统计课程的数量
select count(distinct cosname) from tbcourse;
1
2

查询结果：

笛卡尔现象：

当两张表进行连接查询，没有任何条件限制的时候，最终查询结果条数是两张表条数的乘积，这种现象被称为笛卡尔现象

举例：

两张表连接，没有任何条件限制，将两张表的名字和课程进行连接

-- tbstudent中的每个名字分别和tbcourse中的每个课程进行连配对
select stuname,cosname from tbstudent,tbcourse;
1
2

查询结果如下：

总记录为：学生数*课程数

避免笛卡尔现象的方法：

连接时，添加条件，满足这个条件的记录被筛选出来

-- 课程表中的cosid和分数表中的cid相等的课程名和学分
select cosid,cid,cosname,coscredit from tbcourse,tbsc where tbcourse.cosid=tbsc.cid ;
1
2

查询结果：

为了提高查询效率，我们可以给表起别名：

-- tbcourse的别名为course tbsc的别名为score

select course.cosid,score.cid,course.cosname,course.coscredit 
from tbcourse course,tbsc score
 where course.cosid=score.cid ;
1
2
3
4
5

查询如下：

与上述未起别名的查询结果相同，但是查询效率提高了

注意：通过笛卡尔积现象得出，表的连接次数越多效率越低，尽量避免表的连接次数

内连接：

完全能够匹配上条件的数据查询出来

内连接之等值连接：

SQL.92语法：

//查询cosid和cid相等的课程名
select cosid,cid,cosname from tbsc,tbcourse where tbsc.cid=tbcourse.cosid ;
1
2

SQL99语法：

//查询cosid和cid相等的课程名
select cosid,cid,cosname from tbsc inner(可省略) join tbcourse on tbsc.cid=tbcourse.cosid ;
1
2

查询结果是相同的：

那么这两种有什么区别呢？

观察两种语法格式，我们不难看出，SQL92这种书写格式，如果后期还需要添加更多的筛选条件，那么只能使用and了，这样会导致结构不清晰

而SQL99的优点在于，表连接的条件是独立的，连接之后，如果还需要进一步的筛选，再往后面加where即可

SQL99语法格式：

select
......
from
a
join 
b
on
a和b的连接条件
where
筛选条件
1
2
3
4
5
6
7
8
9
10

内连接之非等值连接：

-- 查询每个课程分数，要求显示的分数大于等于85小于95
select s.score,c.cosname from tbsc s join tbcourse c on s.score where s.score>=85 and s.score<95;  -- 条件不是一个等量关系，称为非等值连接
1
2

查询结果：

内连接之自连接：

自连接----> 一张表看作两张表

举例：

//查询员工对应的领导编号，要求显示员工名和对应的领导名
select 
a.ename as '员工名',b.ename as '领导名'
from emp a
join emp b
on a.mgr=b.empno; -- 员工的领导编号=领导的员工编号
1
2
3
4
5
6

外连接：

外连接（右外连接）：

right的含义：表示将join关键字右边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询左边的表

举例：

-- 查询stuid和sid相等的信息，要求显示stuid,sid,stuname,score

select e.stuid,d.sid,e.stuname,d.score
from tbsc d right outer[可省略] join tbstudent e
on e.stuid=d.sid
1
2
3
4
5

查询结果如下：

由于tbstudent为主要查询的对象，因此在查询结果中，tbstudent表中涉及的stuid和stuname的字段值不会出现null的情况，而tbsc表中的sid和score字段可能会出现字段值为null的情况

结论：内连接和外连接的区别在于，在内连接中，两张表是属于平等的关系，显示出来的数据也是两张表能够匹配上的，但是外连接存在主次之分，显示出来的数据不完全是两者都能匹配上的

外连接（左外连接）：

left的含义：表示将join关键字左边的这张表看成主表，主要是为了将这张表的数据全部查询出来，捎带着关联查询右边的表

举例：

思路和上述的右连接是一样的，只不过此时的重点查询对象换成了左表

select e.stuid,d.sid,e.stuname,d.score
from tbsc d left outer[可省略] join tbstudent e
on e.stuid=d.sid
1
2
3

查询结果如下：

带有right的是右外连接，又叫做右连接，带有left的是左外连接，又叫做左连接，任何一个右连接都有左连接的写法，任何一个左连接都有右连接的写法

外连接的查询结果条数一定大于等于内连接

三张表或者四张表进行连接：

语法：

select 
.....
from
a
join -- 内连接
b
on
a和b的连接条件
join -- 内连接
c
on
a和c的连接条件
right join -- 外连接
d
on
a和d的连接条件

-- 一条SQL中内连接和外连接可以混合，都可以出现
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

需求分析：

找出每个员工的部门名称以及工资等级，要求显示员工名，部门名，薪资，薪资等级

我们先来分析这个问题：

首先我们要查找的内容是员工名，部门名，薪资，薪资等级，以下三张表都有涉及到，其次员工的部门名称需要从部门信息表中获取，那么就需要将员工信息表和部门信息表进行连接，每个员工工资等级需要从工资等级表进行获取，那么就需要将员工信息表和工资等级表进行连接

要想实现表的连接，那么就需要找出他们属性之间的关系，如下表所示：

员工信息表和部门信息表之间的共有属性是部门号码，可直接通过等值连接进行
员工信息表和薪资等级表之间的有关属性是工资，员工工资属于工资等级表的[最低工资，最高工资
1
2

命令：

select e.name,e.sal,d.dname,s.grade
from 
emp e
join 
dept d
on
e.deptno=d.deptno -- 部门编号相等
join
salgrade s
on
e.sal between s.losal and s.hisal; -- 薪资处于这个范围
1
2
3
4
5
6
7
8
9
10
11

子查询：

select 语句中嵌套select语句，被嵌套的select语句称为子查询

select
....(select)
from
....(select)
where
....(select)
1
2
3
4
5
6

where中的子查询：

举例：

查询分数高于最低分数的学生姓名和分数
1

拿到这个问题，我们首先会想到要想得到高于最低分数的学生姓名和分数，那么就必须先知道最低分数

第一步查询最低分数：

select min(score) from tbsc;
1

第二步将第一步查询到的结果作为限制条件查询学生姓名和分数

select stuname,score from tbstudent,tbsc where score>80;
1

这样虽然能够正确查询，但是编写两条命令固然很麻烦，因此：

我们需要使用子查询将其合并为一条命令

-- 将上述方法中的第一条语句的查询结果作为第二条查询语句的限制条件
select stuname,score from tbstudent,tbsc where score>(select min(score) from tbsc)
1
2

(select min(score) from tbsc)作为子查询，先进行查询，将返回结果反馈给父查询

from子句中的子查询：

from后面的子查询，可以将子查询的查询结果当做一张临时表

举例：

找出每个岗位的平均工资的薪资等级
1

第一步找到每个岗位的平均工资：

select job,avg(sal) from emp group by job; -- t表
1

查询结果如下：

第二步：把以上的查询结果就当做一张真实存在的表t.

select * from salargrade;-- s表
1

查询结果如下：

第三步：将t表和s表进行连接，连接条件即为t表的平均工资处在s表的最低工资到最高工资范围内（avg(sal）between s.losal and s.hisal;

select 
t.*,s.grade
from
(select job,avg(sal) from emp group by job) t
join 
salgrade s
on
t.avg(sal) between s.losal and s.hisal; -- 连接条件
1
2
3
4
5
6
7
8

注意：

1：这里不能直接写成t,因为t表是我们假象出来的，他在数据库中并不存在，select job,avg(sal) from emp group by job是我们查询到的结果，为了方便，我们起别名为t

(select job,avg(sal) from emp group by job) t
1

2:这里需要给avg(sal)起别名，原因是后续代码中t.avg(sal)，系统会自动认为它是个函数

(select job,avg(sal) as avgsal from emp group by job) t
1

那么后续代码中的t.avg(sal)都要修改为t.avgsal

t.avg(sal) between s.losal and s.hisal;
1

正确命令如下：

select 
t.*,s.grade
from
(select job,avg(sal)  as avgsal from emp group by job) t
join 
salgrade s
on
t.avgsal between s.losal and s.hisal;
1
2
3
4
5
6
7
8

select后面出现的子查询：

举例：

找出每个员工的部门编号，要求显示员工名，部门名
1

 -- e.enmae为员工名 d.dname为部门名
 
select e.ename,
(select d.dname from dept d where e.deptno=d.deptno)-- 这条语句是用来查询部门名
-- 注：这里的部门名是有限制条件的，他必须是每个员工对应的部门
as dname from emp e;
1
2
3
4
5
6

这种子查询只能一次返回一条结果，如果多余一条就会报错

依旧是上述例：

若使用下述这种方法就是错误的，因为一次返回的是多条记录

select e.ename,
(select d.dname from dept )//一次返回多条记录
as dname from emp e;
1
2
3

union的用法：

基本用法：

举例：

查询选修Java程序设计和C语言程序设计的学生姓名
1

根据我们之前学过的内容，常规查询如下：

select stuname,cosname from tbstudent,tbcourse where cosname='C语言程序设计'or cosname='Java程序设计';
1

除此之外，我们还可以使用in:

select stuname,cosname from tbstudent,tbcourse where cosname in('C语言程序设计', 'Java程序设计');
1

现在我们就要介绍第三种查询方法了，使用union：

select stuname,cosname from tbstudent,tbcourse where cosname='C语言程序设计'
union
select stuname,cosname from tbstudent,tbcourse where cosname='Java程序设计';
1
2
3

无论上述三种的任意一种查询方法，均可以正确查询，但是union相比于前面两种存在很大的优势，它的查询效率要高一些，对于表连接来说，每连接一次新表，则匹配的次数满足笛卡尔积，成倍的在增长，但是union可以减少匹配的次数，在减少匹配次数的情况下，还可以完成两个结果集的拼接

下面我们举一个例子来体会一下union的效率之高：

a 连接b 连接c
a:10条记录
b:10条记录
c:10条记录
1
2
3
4

那么普通查询所匹配的次数是：10* 10* 10=1000次

而使用union查询：

a连接b一个结果：10* 10-->100次
a连接c一个结果：10* 10-->100次
使用union所匹配的次数就是：100次+100次=200
1
2
3

union使用注意事项：

1：union在进行结果集合并的时候，要求两个结果集的列数相同

2：结果集合合并时列和列的数据类型也必须一致，当数据类型不相同时，虽然在MySQL中不会报错，是由于它的语法要求不是很严格，但是在Oracle中会报错

limit：

limit是将查询结果集的一部分取出来，通常使用在分页查询中

分页的作用是为了提高用户的体验，因为一次全部被查出来，用户体验感会很差

在数据库中的使用：

完整用法：limit(startIndex,length)；
缺省用法：limit 数字；-- 取出前几项数据，注意这里不能使用length-startIndex，因为前后记录都被包含

举例：

-- 查询年龄最大的5个人的信息
select *from tbstudent
order by stubirth
limit 5;
1
2
3
4

查询结果：

注意：

在MySQL中limit是在order by之后才执行

举例：

-- 取出从第二名到第六名的学生姓名和学号，按照学号降序排列
select stuname,stuid from tbstudent
order by 
stuid desc
limit 2,6;
1
2
3
4
5

查询结果如下：

分页：

每页显示三条记录：

第一页：limit 	0,3	[0,1,2]
第二页：limit	3,3	[3,4,5]
第三页: limit	6,3 [6,7,8]
1
2
3

每页显示pagesize条记录：

第pageNo页:

limit （pageNo-1）*pagesize , pagesize
1

public static void main(String[] args){
//用户提交过来一个页码，以及每页显示的记录条数
int pageNo=5;//第五页
int pagesize=10;//每页显示10条
int startInex=(pagsize-1)*pagesize;
String sql="select....limit"+startIndex+","+pagesize;
} 
1
2
3
4
5
6
7