数据库——连接查询

1.定义：同时涉及多个表的查询。
2.连接条件常用格式：
[<表名1>]<列名><比较运算符>[<表名2>]<列名2>
连接字段类型必须是可比的。（字符串比字符串，数值比数值）
引用表1和表2中同名属性时，必须加表名前缀；引用唯一属性时可加可不加。
3.连接字段

• 连接谓词中的列名称为连接字段
• 连接条件中的各连接字段类型必须是可比的，但不必是相同的。

广义笛卡尔积（不带任何连接条件,很少使用）：

自然等值连接：

表别名（from语句中 原名as别名）

distinct避免重复

select语句中加入distinct可以解决这个问题

自身连接（取别名以示区别且必须使用别名前缀）：

内连接

where语句中指定的内连接称为旧式内连接
transact-SQL查询中内连接用：
表1 Inner Join 表2 on 连接条件

外连接

• 普通连接操作只输出满足连接条件的元组
• 外连接指定表作为主体表，主体表中不满足连接条件的元组也输出，另一表对应的位置给予空值。
  左外连接:主体表Left Outer Join非主体表
  
  
  右外连接:非主体表Right Outer Join主体表
  
  全外连接:主体表Full Outer Join主体表
  

多表连接

复合条件连接

mysql基本语句

1.进入数据库
mysql -uroot -p
2.退出数据库
exit;
3.查看有哪些数据库
show databases;
4.创建新的数据库
create database 英文名字;
5.使用数据库
use 名字
6.查看表
show tables;
7.查看数据库版本
select version();
注意：mysql没有分号；不执行，终止指令/c

表的定义

什么是表table?为什么用表来存储数据呢?

数据库当中是以表格的形式表示数据的-
因为表比较百观-
任何一张表都有行和列:
行(row） :被称为数据/记录。
列(column）:被称为字段。姓名字段、性别字段、年龄字段。
了解一下:
每一个字段都有:字段名、数据类型、约束等属性-
字段名可以理解，是一个普通的名字，见名知意就行-
数据类型:字符串,数字,日期等
约束:约束也有很多,其中一个叫做唯一性约束,
这种药束添加之后,该字段中的数据不能重复-

SQL语句分类

sQL语句有很多，最好进行分门别类，这样更容易记忆。
分为:
DQL:
数据查询语言(凡是带有select关键字的都是查询语句)
select. …
DML:
数据操作语言（凡是对表当中的数据进行增删改的都是DML)
insert delete update
insert增
delete删
update改

这个主要是操作表中的数据data.

DDL:
数据定义语言
凡是带有create、 drop- alter的都是DDL.
DDL主要操作的是表的结构。不是表中的数据。
create:新建，等同于增
drop:删除
alter:修改
这个增删改和DM不同，这个主要是对表结构进行操作。

TCL
是事务控制语言包括:
事务提交:corrnit;
事务回滚: rollback ;

DCL
是数据控制语言-
例如:授权grant、撤销权限revoke. . . .

select语句除了可以查看数据库中的表格和视图的信息外,还可以查看SQL Server的系统信息、复制、创建数据表。其查询功能强大，是SQL语言的灵魂语句，也是SQL中使用频率最高的语句。

基本select语句：
一个基本的select语句可以分解成三个部分：查找什么数据（ select ）、从哪里查找（ from ）、查找的条件是什么（ where ）。
select 语句的一般格式如下：
           select <目标列表达式列表>
           [into 新表名]
           from 表名或视图名
           [where <条件>]
           [group by <分组表达式>]
           [having <条件>]

           [order by <排序表达式>[ASC|DESC]]

二、单表查询：指仅涉及一个表的查询
(一)查询指定的列
1.查询表中所有列：在select语句指定列的位置上使用*号时，表示查询表的所有列。
格式：select * from 表名
例1.在xs表中查询全体学生的信息。
use xsbook

select * from xs

2.查询表中指定的列。查询多列时，列名之间要用逗号隔开。
格式： select 表名.列名1,列名2,...
from 表名
例2.在xsbook数据库的xs表中查询学生的姓名、专业名、借书数信息。
select 姓名,专业名,借书数

from xs

3.指定查询结果中的列标题
通过指定列标题（也叫列别名）可使输出结果更容易被人理解。指定列标题时，可在列名之后使用AS子句；也可以使用:列别名=<表达式>的形式指定列标题。
AS子句的格式为：列名或计算表达式 [AS] 列标题
其中：AS可省略。
例3：给列加显示标题示例：
select 借书证号 as cardno,姓名 as name, 借书数 as cnt
from xs
或采用列别名=<表达式>的形式指定列标题，如上例可改为：
select cardno=借书证号,name=姓名,cnt=借书数
from xs

4.查询经过计算的列（即表达式的值）：
使用select对列进行查询时，不仅可以直接以列的原始值作为结果，而且还可以将列值进行计算后所得值作为查询结果，即select子句可以查询表达式的值,表达式可由列名、常量及算术运算符组成。查询结果计算列显示“无列名”,一般要给计算列加列标题。
其中：表达式中可以使用的运算符有：加+、减-、乘*、除/、取余%
例4：查询所有书名、单价及8折之后的图书价格
select 书名,单价,八折=单价*0.8
from book

(二)、选择行：选择表中的部分行或全部行作为查询的结果：
格式: select [all|distinct] [top n[percent]]<目标列表达式列表>
from 表名
1. 消除查询结果中的重复行
对于关系数据库来说，表中的每一行都必须是不同的(即无重复行)。但当对表进行查询时若只选择其中的某些列，查询结果中就可能会出现重复行。在select语句中使用distinct关键字可以消除结果集中的重复行，其格式为：
select distinct 列名1[,列名2,...] from 表名
其中：select语句中使用distinct的含义是对结果集中的重复行只选择一个，以保证行的唯一性(注意：强调的是“行”，而不是某一列）。
例5：查询所有专业名
select distinct 专业名
from xs
注意：与使用Distinct关键字相反，当使用关键字All时，将保留结果集中的所有行(默认值为All)
例如：select all 姓名,性别

from xs

2. 限制查询结果中的返回行数
使用top选项可限制查询结果的返回行数，即返回指定个数的记录数。
其中：n是一个正整数，表示返回查询结果集的前n行；若带percent关键字，则表示返回结果集的前n%行。
例6：查询xs表的前5个记录信息
select top 5 * from xs

再如：select top 50 percent * from xs --查询前50%的学生信息

(三)查询满足条件的行: 用where子句实现条件查询:
通过where子句实现,该子句必须紧跟在From子句之后。
格式为：
select [all|distinct] [top n[percent]]<目标列表达式列表>
from 表名
where <条件>
说明：在查询条件中可使用以下运算符或表达式：P134
运算符 运算符标识
比较运算符 <=，<,=,>,>=,!=,<>,!>,!<
范围运算符 between... and,not between... and
列举运算符 in,not in
模糊匹配运算符 like,not like
空值运算符 is null,is not null

逻辑运算符 and,or,not

1.使用比较运算符：
例7：查询xsbook数据库xs表中借书数在2本以上的学生情况（含2本）。
select *
from xs

where 借书数>=2 --或为：where 借书数!<2

2.指定范围：
用于指定范围的关键字有两个：between...and和 not between...and。用于查找字段值在（或不在）指定范围的行。
格式为：表达式 [not] between <表达式1> and <表达式2>
其中：between关键字之后的是范围的下限（即低值）,and关键字之后的是范围的上限（即高值）
例8：查询xs表中1980 至1985年出生的学生情况。
select *
from xs
where 出生时间 between '1980-01-01' and '1985-12-31'
再如：查询xs 表中非1980年出生的学生信息。
select *
from xs

where 出生时间 not between '1980-01-01' and '1980-12-31'

3.使用列举:
使用in关键字可以指定一个值的集合，集合中列出所有可能的值，当表达式的值与集合中的任一元素个匹配时，即返回true，否则返回false。格式为：<表达式> [not] in(值1,值2,...,值n)
例9：查询xs表中专业名为'计算机'、'信息工程'、'英语'或'自动化'的学生的借书证号,姓名,借书数,专业名。
select 借书证号,姓名,借书数,专业名
from xs
where 专业名 in('计算机','信息工程','英语','自动化')
注意：与in相对的是 not in，用于查找表达式的值不属于指定集合的行。
例如：查询xs表中非'计算机'、'信息工程'、'英语'专业的学生情况：
select *
from xs

where 专业名 not in('计算机','信息工程','英语')

4.使用通配符进行模糊查询:
可用like 子句进行字符串的模糊匹配查询，like子句将返回逻辑值（true或False）。
like子句的格式为： <字符型字段> [not] like <匹配串>
其含义是：查找指定字段值与匹配串相匹配的记录。匹配串中通常含有通配符%和_（下划线）。
其中:
%：代表任意长度（包括0）的字符串
例：a%c:表示以a开头，以c结尾的任意长度的字符串，如：asc、abccccccccc、aabbc、ac
_：代表任意一个字符
例：a_c表示以a开头，以c结尾，长度为3的字符串，如:abc、asc
Like子句中使用通配符的查询也称模糊查询。
例10：查询xs表中姓“王”且姓名为两个字的学生情况
select *
from xs
where 姓名 like '王_'
再如：查询book表中书名含有“数据”字样的图书情况：
select *
from book
where 书名 like '%数据%'
注意：所有通配符都必须在like 子句中才有意义，否则将被当作普通字符处理；且like子句中的匹配串也可以是一个不含通配符的完整的字符串（如下例）。
例11：查询xs表中计算机专业的学生情况
select *
from xs
where 专业名 like '计算机'
查询相反的情况：则使用以下语句：
select *
from xs
where 专业名 not like '计算机' --查询xs表中非计算机专业的学生情况

即：如果like后面的匹配串中不含通配符，那么可以用“=”（等号）运算符来替代like。即上例中的where子句等价于：where 专业名='计算机'。

5.使用null的查询
当需要判定一个表达式的值是否为空值时，使用 is null关键字。格式为：<表达式> is [not] null
当不使用not时，若表达式的值为空值，则返回true，否则返回false；当使用not时，结果刚好相反。
例12：查询xs表中专业名尚不确定的学生情况。
select *
from xs
where 专业名 is null
再如:查询xs表中专业名已确定的学生的姓名,专业名。
select 姓名,专业名
from xs

where 专业名 is not null

6.多重条件查询：使用逻辑运算符
逻辑运算符and（与：两个条件都要满足）和or（或：满足其中一个条件即可）可用来联接多个查询条件。and的优先级高于or,但若使用括号可以改变优先级。
例13：查询xs表中借书数在3本以下的计算机专业学生的姓名和借书证号。
select 借书证号,姓名
from xs
where 专业名='计算机' and 借书数<3
再如：查询计算机和英语专业学生的基本情况：
select *
from xs

where 专业名='计算机' or 专业名='英语'

(四)对查询结果排序:
order by子句可用于对查询结果按照一个或多个字段的值（或表达式的值）进行升序（ASC）或降序（DESC）排列，默认为升序。
格式：order by {排序表达式[ASC|DESC]}[,...n]
其中：排序表达式既可以是单个的一个字段（如例14），也可以是由字段、函数、常量等组成的表达式（如例15），或一个正整数。
例14：查询xs表中的全体学生信息并按出生时间排序，要求晚出生的学生排在前面。
select *
from xs
order by 出生时间 desc
思考：若本例改为以下格式的语句，其功能是什么？
select top 5 *
from xs
order by 出生时间 desc
例15:请查询图书的书名、六折后的书价，并将结果按六折后的书价的降序排列。
select 书名,单价*0.6 as 书价六折
from book
order by 单价*0.6 desc
说明：本例中，order by子句也可写为: order by 2，这里2代表排序字段或表达式（即：单价*0.6）在select子句中出现的顺序号。
注意：排序表达式可有多个，例如：将xs表的学生按性别的降序排序，同性别的按出生时间的升序排序：
select *
from xs
order by 性别 desc,出生时间 asc

注意：对于null值，若为升序则排在最前面，若为降序则排在最后。

(五)使用统计函数:又称集函数，聚合函数
在对表进行检索时，经常需要对结果进行计算或统计，T-SQL提供了一些统计函数（也称集函数或聚合函数），用来增强检索功能。统计函数用于计算表中的数据，即利用这些函数对一组数据进行计算，并返回单一的值。
常用统计函数表
函数名 功能
AVG 求平均值
count 求记录个数，返回int类型整数
max 求最大值
min 求最小值

sum 求和

1. SUM和AVG
功能：求指定的数值型表达式的和或平均值。
例16：查询计算机专业学生所借图书的平均数、借书总数。
select avg(借书数) as 平均借书数,sum(借书数) as 借书总数
from xs

where 专业名='计算机'

2. Max和Min
功能：求指定表达式的最大值或最小值。
例17：在xs表中查询学生最多和最少的借书数
select max(借书数) as 最多借书数,min(借书数) as 最少借书数

from xs

3. count:
该函数有两种格式：count(*)和count([all]|[distinct] 字段名），为避免出错，查询记录个数一般使用count(*)，而查询某字段有几种取值用count(distinct 字段名）。
(1).count(*):
功能：统计记录总数。
例18：查询学生总数
select count(*) as 学生总数
from xs
再如：查询已借阅了图书的学生人数。
select count(*) as 借阅了图书的学生数
from xs
where 借书数<>0
思考：查询计算机专业的学生总数。
(2).count([all]|[distinct] 字段名）
功能：统计指定字段值不为空的记录个数，字段的数据类型可以是text、image、ntext、uniqueidentifier之外的任何类型。
例19：查询学生总数
select count(借书证号) as 总人数
from xs
说明：
A.本例中由于“借书证号”字段值所有记录都不为空，故这一统计结果即为总记录个数，即和count(*)的统计结果一样；
B.distinct表示：先消除指定字段取值重复的记录，然后再统计指定字段值不为空的记录个数；而all 则表示所有指定字段值不为空的全部记录都参加统计计数。默认为all。
例20：查询出版社数。
select count(distinct 出版社) as 出版社数
from book
注意:若select 列表中使用了统计函数,则列表中不允许再指定任何字段名，用group by子句指定的字段除外。如：以下操作将会产生错误：
select 借书证号,count(*)

from xs

(六):对查询结果分组：
group by子句用于将查询结果表按某一列或多列值进行分组，列值相等的为一组，每组统计出一个结果。该子句常与统计函数一起使用进行分组统计。格式为：
group by 分组字段[,...n][having <条件表达式>]
例21：统计男、女生各多少人。
select 性别,count(*) as 人数
from xs
group by 性别
再如：查询各专业人数及各专业的总借书数。
select 专业名,count(*) as 各专业人数,sum(借书数) as 各专业的借书总数
from xs
group by 专业名
说明：在使用group by子句时应注意以下几点：

1.在使用group by子句后，select列表中只能包含：group by子句中所指定的分组字段及统计函数。

2.having子句的用法:
having子句必须与group by 子句配合使用，用于对分组后的结果进行筛选（筛选条件中常含有统计函数）。
例22：查询学生人数在2人以上（含2人）的专业的专业名以及其学生人数
select 专业名,count(*) as 人数
from xs
group by 专业名

having count(*)>=2

3. 分组查询时：不含统计函数的条件，通常使用where子句；含有统计函数的条件,则只能用having子句。
例如：查询“体育”专业男、女生各有多少人。
select 性别,count(*) as 人数
from xs
where 专业名='体育'
group by 性别
再如：查询平均借书数在2本以上（含）的专业及其学生人数。
select 专业名,count(*) as 人数
from xs
group by 专业名

having avg(借书数)>=2

(七)compute子句:
格式：{compute 统计函数名}[,...n][by 分组表达式[,...n]
功能：compute子句将产生一个单独的汇总行，用以显示统计信息。相当于在select 语句的结果后面加了一个汇总行，这样既查询出了源表中一个一个的详细记录（即具体的字段值），又查询出了统计某些列数据所产生的统计信息（即：细节+统计）。
例23：查询计算机专业学生的借书证号,姓名,出生时间及学生人数。
select 借书证号,姓名,出生时间
from xs
where 专业名='计算机'
compute count(借书证号)
其中：汇总行的列标题：cnt表示：count函数汇总
avg表示：avg函数汇总
sum表示：sum函数汇总
再如：查询所有图书的书名，单价，出版社及均价。
select 书名,单价,出版社
from book
compute avg(单价)
说明：使用compute子句时应注意以下几点：
1.compute <统计函数名> 子句中所涉及到的字段是select 字段名列表中已列出的字段名。如：以下操作将产生错误，思考一下为什么？
select 书名,单价,出版社
from book

compute sum(库存量)

2.同时使用compute...by子句和order by子句也可实现分组统计查询，但compute...by子句必须紧跟在order by 子句之后；compute ...by子句中的分组字段必须是order by子句中的第一个排序字段。
例24：查询各专业学生的姓名，性别，专业名及各专业的学生人数。
select 姓名,性别,专业名
from xs
order by 专业名
compute count(姓名) by 专业名
再如：查询各专业学生的姓名，性别，专业名,借书数及其总借书数。
select 姓名,性别,专业名,借书数
from xs
order by 专业名
compute sum(借书数) by 专业名
总结:group by:可用于解决分组统计的问题。即只能显示分组字段值及统计函数值,且每组只有一行统计数据。
compute:可用于解决既要显示具体的记录数据又要显示统计信息的问题,并且不分组。即不需分组，既显示字段值又要显示统计函数值。

compute...by：可用于解决既要显示具体的记录数据又要分组显示统计信息的问题。即分组统计，既要显示除分组字段以外的其它字段值又要显示统计函数值。

(八)、select语句的其它功能：
1、复制或创建表（into子句）：
into子句功能：将select语句查询所得的结果保存到一个新建的表中。
注意：into 子句应放在select 字段名列表之后。into子句不能与compute子句一起使用。
例25：由xs表创建'计算机专业学生借书证'表，包括借书证号和姓名。
select 借书证号,姓名 into 计算机专业学生借书证
from xs
where 专业名='计算机'
再如：将xs表数据全部复制到表xs4中。
select * into xs4
from xs
又例: 只复制xs表的结构，不复制记录，即新表为空表。
select * into xs7
from xs

where 1=2 /*这里指定一个永远为假的条件*，以使无记录被复制/

2、合并结果集（Union子句）：可以将两个或多个select查询的结果合并成一个结果集，即合并两个或多个查询结果的记录。
格式为： select 语句1 {union [all] select 语句2}[...n]

说明：

(1).用来合并的各select 语句字段列表中的列数、各对应列的数据类型、列的顺序必须一致。

(2).all表示：合并的结果中保留两个查询结果的所有行，不去除重复行。若省略all则在合并的结果中将去除重复行。
(3).合并后结果集中的列名来自第一个seleect 语句。
例26:合并xsbook库中表xs1、xs2、xs3的全部记录（假设这三个表的结构相同）。
select *
from xs1
union
select *
from xs2
union
select *

from xs3

3、显示变量或表达式的值（无数据源查询）：
select语句还可显示常量、变量或表达式的值，也就是说用select语句可查询输出不在数据表中的数据，故又称为无数据源查询。
如： select 'we are friends'
又如：select 123.56,'中国','沈阳'
select 45*23-1

注意：这里也可以为要输出的值指定列显示标题，如： select '中国' as 国家,'沈阳' as 城市

4.用select 语句向表中插入多行记录。
格式：insert into 表名[(字段列表)]
select 语句
注意：insert into表名[(字段列表)]和select查询结果集的列数、列序和对应列的数据类型必须一致。
例27:将表xs2中全体学生的记录数据追加到表xs1中。
insert into xs1
select * from xs2
再如：将表xs3中女生的记录追加到表xs1中，要求只追加借书证号，姓名，性别，出生时间字段值。
insert into xs1(借书证号,姓名,性别,出生时间)
select 借书证号,姓名,性别,出生时间
from xs3
where 性别=0
注意：一个select 语句中各子句的顺序如下：
select---[记录范围]---字段名列表---[into]---[from]---[where]---[group by]---[having]---[order by]---[compute---[by]]