Java之数据库相关内容
——MySQ数据库与JDBC编程
一.JDBC:Java database connectivity 即Java数据库连接,是一种可以执行SQL语句的Java 即。即可以通过这个链接对外连接数据库。
这体现了Java的跨平台性。
JDBC可以完成下列操作:
1, 建立与数据库的连接
2, 执行SQL语句
3, 获得SQL语句的执行结果
JDBC驱动有四种形式,主推一种形式:
纯Java的,直接与数据库实例交互,这种驱动是智能的,它知道数据库使用的底层协议。
二.MySQL数据库基本命令与关系数据库
数据库的全称是:database management system简称DBMS
DBMS是所有数据的知识库,它负责储存,安全,一致,并发,恢复和访问等操作。
DBMS有一个数据字典(有时也被称之为系统表),它拥有储存每个事物的相关信息。
根据时间顺序出现了四种数据库:
网状数据库,层次数据库,关系数据库,面向对象数据库。
上述四种数据库中,关系数据库理论最成熟,应用最广泛。
对于关系数据库而言最基本的储存单元就是数据表,因此可以简单地把数据库想象成表的集合,当然这只是简单的认为。
数据表是数据储存的逻辑单元,可以将表理解为由行和列组成的表格,其中每一条称之为一条记录,每一列也被称之为一个字段。为数据库建表时通常要指出该表包含多少列,
每行数据类型。无需指定需要多少行,因为行是动态改变的,每行用于保存一条用户数据。
除此之外还应该为每个数据表指定一个特殊列,该特殊列可以唯一的表示此行记录,这叫做主键列。
MySQL的一个实例Server instance可任意同时包含多个数据库,mySQL通过下列命令来查看当前实例包含多少个数据库。
show database;
用户想要创建一个新的数据库:
create database [IF NOTEXISTS] 数据库名;
删除数据库:
Drop database 数据库名;
建立数据库后若想要操作数据库(进行增删改查等工作):
Use database;
进入数据库后若想知道该数据库包含多少个表:
Show tables;
查看表结构(看有多少列,每行的数据类型等信息):
Desc 表名;
如果一次性将多条数据库命令导入到mySQL数据库命令行窗口里,这种方式被称之为导入SQL脚本。
MySql的两种储存机制:
MyISAM:MySQL早期的储存机制。
Innodb:innoDB提供事物的安全的储存机制。通过建立行级锁,保证事务完整性。这是默认的储存方式,可以通过skip-innodb命令进行调整。
若要在建表的时候默认任何一种储存机制,可以在建表语法后加入下面任何一句:
ENGING=MyISAM强制使用MyISAM储存方式
ENGING=InnoDB强制使用InnoDB储存方式
SQL语句介绍:
(结构化查询语言)
Sql 语言是数据库检索关键数据的标准语言。
使用SQL语言实现的功能主要有:
1, 在数据库中检索信息。
2, 对数据库的信息进行更新
3, 改变数据结构
4, 更改系统安全设置
5, 增加用户对数据库或表的许可权限。
上述五个功能前三个主要有程序员来完成,但后两个主要有dba来完成。
根据语言来分类:
主要有以下几种:
1, 查询语句:select关键字,查询语句在所有数据库语言中是最重要的。
2, DML语句(数据操作语言):insert(插入),delete(删除),update(更新)。
3, DDL语言(数据定义语言):create,alert,drop,truncate(清空数据表)。
4, DCL语言(数据控制语言):grant,revoke。
5, 事物控制语句:commit,rollback,savepoint。
特别的SQL语句关键字不区分大小写。
其中dcl语句用于授权用户权限,收回权限,通常不需要程序员来完成。
SQL命令中可以使用标识符,可以用于定义表名,列名,也可以用于定义变量。
规则:
1,标识符通常需要以字母开头。
2,标识符包括字母,数字,#,_,$。
3,不能使用数据库语言的关键字保留字。建议使用多个关键字中间以_连接。
4,同一个模式下的对象不应该重名。
DDL语句与数据库中的数据类型:
常见数据库对象:
表table:数据库的逻辑单元,以行和列存在,列就是字段,行就是记录。
数据字典(系统表):存放系统关键信息的表。程序员不应更改,只能查询。
约束constraint:执行数据校验的规则。
视图view:一个或多个表中数据的显示方法,不用于储存数据。
索引index:用于提高数据的查询性能,相当于目录。
函数function:用于完成一次特定的计算,具有一个返回值。
存储过程procedure:用于完成一次完整的业务处理,没有返回值,但可以通过传出参数将多个值传给调用环境。
触发器trigger:相当于一个数据监听器。
这些数据库对象都可以通过DDL语言进行相对应的create alert drop truncate
建表语法:
Create table [模式名]表名
(
#多个列的定义
列名 数据类型,
……
)
每个列名的之间以“,”隔开
若要使用列默认值,使用关键字-default
MySQL中的列数据类型:
Tinyint/samllint/mediumint/int(integer)/bigint1字节2字节3字节4字节8字节整数。又可以分为有无符号。
Float/double
Decimal(dec)精确小数,相对于上述两个精度的小数不会产生小数丢位问题。
Date日期类型,不能保存时间,当把Java.date对象储存进来时,会发生时间部分丢失。
Time事件类型,不能保存日期,类似于上面。
Datetime日期,时间类型
timestamp时间戳类型
year年类型只保存年
char定义长字符串类型
varchar可变长度字符串类型
binary定义二进制字符串类型,以二进制保存字符串类型。
Varbinary可变二进制字符串类型。
Tinyblob/blob/midiumblob/longblob1字节2字节3字节4字节的二进制大对象,可用于储存图片,音乐等二进制文件分别可储存:255b,64k,16m,4g
Tinytext/text/midiumtext/longtext1字节2字节3字节4字节文本对象,可用于储存超长长度的字符串储存空间类似于上面的blob。
Enum(‘value1’,‘value2’…)枚举,只能储存括号中的类型之一。
Set(‘value1’,‘value2’…)集合,可以是括号中的几个。
这样建表的结果仅仅是一个空表,但如果使用子查询建表语句可以在创建表格的同时添加数据。语法:
Create table 表名1 [字段列表]<可以省略>
As
Select * from 表名2;
这时必须要满足的:
1, 表2必须已经存在;
2, 表1和表2的字段列表类型和大小必须相同,或者表1没有字段列表(此时)。
修改表结构数据方法(追加alert):
Alert table 表名
Add
(与建表括号相同);
此时的括号中的列表定义都将追加到原有列定义后面。
提示:如果仅仅是追加新的一列,可以省略括号,仅在add后面追加一个列的定义即可。
注意SQL中的字符串使用单引号引入的,而非双引号。
修改失败:新增的列,或者已有列的修改与已有的数据约束相冲突时修改失败,这是很常见的一种情况。
更改语法:
Alert 表名
Modify列名 数据类型;
可见modify语句不支持一次性更改多个列的定义。
如果数据表里已有数据记录则更改数据类型很容易失败,但是如果要更改默认值,则之前已存在的数据是不会受到影响的,从更改后的数据储存开始生效。
删除:
Alert table 表名
Drop 列名;
这个语法总可以成功。回释放相对应的储存空间。
重命名数据表:
Alert table 表名
Rename to 新表名;
重命名列名:
Alert 表名
Change 列名 新列名 数据类型;
删除表:
Drop table表名;
’truncate表:
Truncate被称为截断,他的作用是:删除表中的全部数据但是保留表的数据结构。
Truncate相对于delete而言要快得多。
语法:
Truncate 表名;
数据库约束:
上面仅仅是一些对于列的约束,这里还可以对表进行相对应的一些列约束,通过约束可以。保证数据表里数据的完整性。
大部分数据库支持的5种完整性约束:
NOT NULL:不能为空,某列不能为空。
UNIQUE:唯一约束,指定某列或者某几个列的组合不能重复。
PRIMARY KEY:主键指定该列的值可以唯一的标识该条记录。
FORIGN KEY:外键,指定该行记录从属于主表中的某一条记录。
CHECK:检查,指定该行必须满足的某一个布尔表达式。
注意:MySQL不支持最后一个check约束
约束也是一种数据,是储存在数据库中的。
分为以下几类:
单列约束:每个约束之约束一列。
多列约束:相对应。
为数据表指定约束:
建表的同时为表指定约束。
建表后创建,以修改表,的形式来修改表。
大部分约束都可以使用列级约束语法,或者表级约束语法。
注意:MySQL使用information_schema数据库中的TABLE_CONSTRAINTS 来保存这个数据库中的所有约束信息。
5中约束的建立和删除:
1, 非空
SQL中的null
1, 不区分大小写,包括Boolean,float,int都可以是null
2, 与Java相类似,null不是空字符串,也不是0
非空约束的指定只要在每一列定义之后加上一个not null即可。
如:hehe_id int not null,
同时可以使用alert modify加入not null约束。
2, UNIQUE约束
, 唯一约束指定不可以出现重复值,但可以出现多个null,因为在数据库中null值是不等于null的。
列级语法指定unique与上面的非空约束相类似。
值的注意的是,唯一约束是有名字的,若不指定约束名则默认为表的名字。
但是要指定约束名就只能使用表级语法指定约束。
表级约束语法:
Constraint 约束名(可以没有) 约束定义;<也可以没有constraint ,但要改名则必有constraint>
例:
Create table text
(
Test _id int not null,
Test_name varchar(255),
Unique (test_name ),
Constraint bb unique(test_id)
)
当然unique后面括号跟随的内容可以是多个表名以逗号隔开。
也可以在alert table 表名
Add unique();
或者alert table 表名
Modify 列名 数据类型unique;
对于大部分数据库来说,使用drop constraint 约束名;删除约束
但MySQL执行drop index 约束名;删除约束。
3, Primary key 约束
主键约束在某种程度上相当于上述两个约束的集合。
每个表中最多允许有一个主键,但这个主键可以有多个列组成。(即只能指定一次primary key)
同样这个时候也是可以使用列级语法,和表级语法。
同上只有表级约束可以指定约束名,但mySQL中默认主键约束名:PRIMAY
语法与唯一约束几乎相同(unique 换为 primary key即可)。
只有删除时:
Alert table 表名
Drop primary key;
即可。
很多数据库对主键列都支持一种自增长的特性——主键列为整型时,可以指定自增长。
指定自增长功能通常用于设置逻辑主键,改键的值没有任何逻辑意义,仅用于标识(即类似与人的id)。
使用关键字:auto_increment,设置某一列的自增长。
在创建列的时候使用:列名 int auto_increment primary key,
4,Foreign key约束
外键约束,主要用于两个字段之间的关系(一个表的两个字段之间,或者两个表的两个字段之间关系)
外键确保了两个字段之间的主从关系,当主表的记录被删除时,从表记录全部被删除,或者从表的记录全部删除后才可以删除主表记录。
从表参照的只能是主表唯一键列,或者直接是主键列。
同一个表可以同时有多个外键。
MySQL在外键约束建立时会设置索引。
外键约束通常建立在事物之间一对多或一对一关系。
其中增加外键列的表叫做从表,从表外键列设置为唯一约束就可以体现一对一的关系。
对于多对多的关系可以增加一个关联表,来设置之间的关联关系。
列级语法:
references关键字,指定该列参照那个主表。
列名1 数据类型 references表名(列名2)
然后列2就是列1的外键了,但mysql此时不会生效该外键的设置,这种语法只是为了使sql语句兼容。
若想生效,则应使用表级语法:
Foreign key (本表列名<即外键列>) references 表名 (列名)
使用表级语法同时也可以重命名外键约束 在之前加上
constraint 约束名
就可以了。
如果不指定约束名则mysql会自动设置为
表名_ibfk_n (其中 n=1,2,3……)
其中本表列名和外表列名可以是多个列名,中间以“ ,”
隔开。
同样删除约束也是十分简单:
Alert table 表名
Drop foreign key 约束名
增加只需将表级语法前加上:
Alert table 表名
Add
即可。
设置主表记录删除时从表记录也会删除:
在表级语法后面加入:
On delete cascade #也可以是 on delete set null
5,check约束
虽然mySQL不支持check约束,但也可以写在建表语法里,尽管这种语法不会有任何的作用。
表级语法:
Check(逻辑表达式<如:salary[这是一个列名]>0>)
索引
索引是存放在schema中的一个数据库对象,虽然索引总是从属于数据表,但索引时一个对象。
索引的作用是:减少io,加速搜索。
MySQL在schema的 STATISTICS表来储存索引,可以查询该表来查询信息。
创建:
自动创建:在某一列定义主键约束,或者外键约束时会自动的添加索引。
手动:通过create inde…语句来创建索引。
删除:
自动:数据表被删除时,其上的索引自动被删除。
手动:drop index…语句来删除。
就像每一本书都有一个目录一样,每一个表格总有一个索引。
一个表中可以有多个索引列用来加速查询速度。
手动创建语法:
Create index indexname
On tablename (列1,列2);
删除语法:
Drop index indexname on tablename
有些数据库不需要指定表名,因为每个索引都是独立不重名的。但mySQL只要求每个表中的索引不能重名,所以也需要指定表名。
索引的弊:
当数据表中的信息被删除或者修改时,索引也需要相当的维护。
储存索引也要一定的空间。
视图
视图是一个表或者多个表中数据的逻辑显示,与表相似。
视图的优势:
可以限制对数据的访问。
使一个复杂的查询变得简单。
提供了数据的独立性。
提供了数据库的不同显示。
在创建视图的同时还要创建一个用于查询该视图的查询语句
语法:
Create or replace view view_name
As
Subquery
创建和修改视图所使用的语法都是上述语法。
上述语法中subquery是一条自定义的语法,可以变得很复杂用途是查询该视图。
通常不推荐修改是图中的数据,所以mySQL提供了一种限定不可修改其中数据的子语句。
在最后一行加入:
With check option;
这样这个视图就只能用作查询不能用于删除了。
删除:
Drop view view_name
DML(数据操作语言)语句
Dml的作用对象主要是表中的数据,而非表格本身。
作用:
插入(insert into)
修改(update)
删除(delete from)
1, insert to
用于向表格中插入数据:
语法:
Insert into table_name [(列……)]
Values(value[……])
如果省略列的话,则默认将所有列都插入值(默认值,或者自增长值,或者指定的值)
并且如果不确定某一列的值可以使用null,自增长的列如果指定为null的话,仍然会自增长插入值。
Values(值),中的值可以根据列的顺序依次给值,这样就不用再前面一一写出每个列的名称。
当主值列表中有数据的时候,外键列表中的外键列才能有值否则只能为null,并且外键列的值只能是在主键列中的一个,不能填写主值列以外的值。
可以使用袋带子查询的插入语句这样可以一次性的插入多个列。
语法:
Inset into table_name1 (colum_name1 )
Select colum_name2 from table_name2
这种从一个列的所有值复制到另外一个列中的方法,要求两个列的数据类型,匹配,数据列数相同即可。
当然也可以在values之后列出多个(值)以逗号隔开,这样可以简化多个数据的插入。
2,Update语句
修改表中的记录。
通过where子句可以限定修改的位置。
语法:
Update table_name
[WHERE condition<布尔表达式,总为真> ]
通过where语句可以限定只修改特定的值。
2, delete from 语句
删除。
语法:
Delete from table_name
[where condition ]
如果省略where则删除整个表格的全部数据。
值得注意:如果从表的记录没有被删除,则不可以删除主表数据,但如果设置了on delete set null约束,则可以删除主标记录,此时从表外键直接设置为null。
单表查询
Select关键字用于查询数据。
Select可以查询单表也可以查询多表。
单表查询语法:
Select colum1,colum2…
From 数据源(table_name)
[where coditon]
如果想查询所有列则可以使用*代表所有列。
Select限定查询的列,where限定查询的行。
在使用select语句时还可以使用算数表达式(-,+,*,/)
规则:
对数值型的列,变量,常量可以使用算术运算符。
对日期型的列,变量,常量只能使用加减运算符。
运算符也可以在两个列之间。
数据列可以当做一个变量(如:列名+n代表该列从第n+1开始的数据)
MySQl没有字符串连接符(+),但可以使用concat表示连接
例:
Concat(column_name,‘s’)。
即这里表示的是column_name列的字符串和‘s’字符连接的结果。
注意:如果在表达式中使用null值,则表达式的返回值会是null。
例:concat(column_name,null)
则结果为null。
如果想查询的列使用别的名字,可以在select column_name [as 或者空格] 新的名字。
这样查询结果列标题头就是新的名字了,但是数据库中的内容本什么有改变。
注意:如果强调大小写敏感,或者需要使用敏感符号(如空格等),则可以在别名外使用双引号。
如果同时使用多个列的select并且使其重命名,则每个列的后面可以紧跟其别名。列和列之间以逗号隔开。
当然也可以为表格起别名,如:from table table_name 别名
Select和where后面都可以不出先列,这样的情况会比对所有表中的数据。
当然还有一种相对应的
Select 表达式 from dual;
语法,这种语法中的dual只是一种占位符,本身没有什么效果。
若果想要去除多个列表一起查询时重复的行(注意是两行记录而不是单纯的两个值)
使用distinct语法。
Select distinct column_name
From table table_name;
注意where后的比较运算符也可以用于字符串,日期之间的比较。
注意SQL语句中赋值符号是:= 而比较是否相等是=,并且不相等是<>
并且sql中还有一些非常特殊的比较运算符:
语法:A between B and C (B<=A<=C)
A in (B1,B2, B3…)(A在后面的集合中)
Like (字符串的匹配,like后面紧跟的表达式可以是通配符,主要用在模糊查询)
Is null (指定的值是null)
通配符:SQL中的通配符有(下划线,代表某一个字符,注意不要在多个下划线中加空格,另外百分号可以代表多个字符,当然如果要在字符中使用这两个字符只需要使用转义字符\即可,但是标准SQL语句中并没有提供这样的语法,不能使用反斜杠代表转义字符,而是使用了escepe关键字,语法:
Where column_name like ‘/_%’escape’/’
表示查询以_开头的所有该列的数据。)
且注意:字符串勿忘使用单引号引起。
同时SQL中也可以使用或且非连接多个表达式代表对多个表达式求真值。
其中使用 and or not关键字。
执行后的数据显示以默认插入序列显示,但是如果对数据的的显示有要求可以使用Order by语句。
语法:
Order by column_name1 [desc], column_2…
进行时默认以升序排列,若要使用降序排列,则在后面使用关键字desc。
(与其相对应的是asc关键字)
如果需要按多列排序,则每列的desc与asc必须单独设置,且放在第一个位置的是首要排列,只有第一列存在多组数据的时候第二列的数据才会有作用。
数据函数库
SQL语句中函数的位置多样,比较常见的是出现在select语句和where语句中(如前面讲的concat)
这里的函数分为多行函数和单行函数。
单行函数对每个行的数据单独计算,每一行都将有一个返回值多行行数对多个行同时计算,最后返回一个值。
SQL中的函数可以直接使用,无需引进类什么的。
执行函数的语法:
Function_name (arg1…)
多行函数主要用于分组,求和一些统计功能。
MySQL中的函数具有特性:
单行函数的参数可以是变量,常量,数据列。单行函数可以接受多个值,但最后只返回一个值。
单行函数会对每行单独起作用,每行都会返回一个值。
使用单行函数可以改变数据类型,单行函数可以嵌套使用。
MySQL中的单行函数主要进行数据转换,其他的还有:
位函数,流程控制函数,加密解密函数,信息函数。
函数简介:
Char_length(column_name)
计算字符长度
Sin(integer)
计算sin值
Date_add(‘year-month-day’,interval integer 单位<year month day>)
其中interval是关键字。
Adddate(‘year-month-date’,integer)
这种与上述用法相对应。
Curdate()
计算当前时间,注意没有单位如:select curdate();
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2017-03-08 |
|
相对应的还有curtime()
显示当前时间
Md5(‘string’)
这是一种md5加密
处理null的函数:
Ifnull(expr1,expr2)
若e1为null则返回e2否则返回e1
Nullif(e1,e2)
如果e1=e2则返回null,否则返回e1
If(e1,e2,e3)
如果e1为true且不为0,且不为null则返回e2,否则返回e3.
Isnull(e1)
判断是否为null,为null则返回true,否则返回false
流程控制函数case:
Case value
When compare_value1 then result 1
When compare_value2 then result 2
……
Else result
End
类似于switch case语句。
不同的是compare_value
可以换成一个表达式,如果表达式成立则会执行。
而且此时case后面没有value
注意不能在Java中使用数据库函数,将导致函数耦合。
分组和组函数
也就是多行函数。
常用组函数:
Avg([distinct/all]e)计算e的平均值,e可以是变量,常量或者数据列,其数据类型必须是数值,可以在变量,列前,使用distinct或者all关键字,若使用distinct则表示不计算重复的值,使用all表示需要计算重复的值,可以不使用。
Count({*/[distinct/all]e})计算e的总条数,其中e可以是变量常量或者数据列,其中数据类型任意,*表示该表中的任意条数,distinct表示不计重复值。
Max(e)计算最大值,其中e可以是变量,常量,数据列,类型可以是任意。
Min(e)与上相对应。
Sum([diatinct/all]e)计算多行e的总和,数据类型必须是数值型。
使用案例:
Select count(*)
From table_name;
计算table_name中的记录条数
注意:count方法不会计算null,如果要计算null可以使用ifnull()函数返回一个别的值。
Distinct关键字不能和*一起使用。
在默认情况下会自动分成一组,为了显示的进行分组,可以使用group by关键字,在group by后面加入一列或者多列的列名,当这些列的组合值完全相同的时候就会被分成一组。
注意:如果使用了group by 将导致多个相同的值仅有一个输出。在select函数后面的子列要求要么在组函数中出现要么在group by后面出现。
如果需要对分组进行过滤应使用having子句,having后面也是一个表达式,与where相似,但也有不同之处。
1, 不能在where子句中过滤组,只能在where子句中过滤行。
2, 不能在where子句中使用组函数,只能在having子句中使用。
例:
Select *
From table_name
Group by column_name
Having e;
其中e是该组数据列满足的条件。
多表查询
多表查询相对于单表查询,是一种多个数据来源于不同表。与之前仅仅是来自于不同的数据列完全不同。
多表连接查询有两种规范:
SQL92规范和SQL99规范。
SQL92规范:
这种规范语法比较简洁,将多个数据表的表名放在from之后,多个表之间以逗号隔开。连接条件放置于where之后。与查询条件之间使用and逻辑运算符连接。如果条件要求两列的值相等则成为等值连接,否则称为非等值连接,其中没有任何连接符称为广义笛卡尔。
语法:
Select column_name ,…
From table_name, …
[where condition ]
多表查询相对于单表查询,存在可能出现重名列的问题,所以在相应的列名前增加表名前缀或者表别名前缀。
如:
Select s.*, column_name
From table_name s,table_name t
Where s.column_name=t.column_name
显示时会显示相应的select后面的列。
这是一种嵌套循环(有些像排序算法),不断判断的过程,其中如果不使用where就是广义笛卡尔积。会显示n×m条记录。
非等值连接就是使用不等号连接两个列之间。
左外链接与右外链接(mySQL不支持这种连接)
符号:+或*
位置在左则称为左外链接,位置在右则称右外链接。(位置在where column_name(*left)=column_name(*right))
这种连接就是在表中增加一个万能行,不管连接条件是否满足,都可以将另外一个表中的所有数据都选出来。
还有一种自连接,当不同的记录之间存在主外键关系可以使用自连接查询。
SQL99规则
这与SQL92规则很相似但是不同的是,SQL99的可读性更强。连接不在放在where之后。并且from之后不在有多个表名而是只有一个表名。
交叉连接:
效果就是92规则中的笛卡尔积,交叉连接无需任何连接条件。
使用关键字cross
语法:
Select column…
From table_name table_othername
Cross joint othertable_nameothertable_othername;
自然连接:
自然连接看似无连接条件,实际上有,自然连接根据连个表格的重名列进行分类。
如果没有重名列效果与交叉连接相同。
Select column…
From table_name table_othername
Natural joint othertable_nameothertable_othername;
Using子句连接:
可以指定一列或多列显示的两个表中的同名列进行连接。
可以指定那些同名列是连接条,可以不是所有。
语法:
Select column…
From table_name table_othername
Joint othertable_name othertable_othername
Using(column_name)
如果两个表中无重名列则出现错误。
On子句连接:
最常用的连接方式,用于指定连接条件。可以是等值条件,也可以非等值条件。若有N个表格之间分组,则需要N-1个 on…joint对
语法:
Select column…
From table_name table_othername
Joint othertable_name othertable_othername
On condition;
左,右,全外连接:
就是在on 子句连接的基础上加上left,right, full关键字(在joint前面)。
但与92规则不相同的是:
这里的left会将左边不满足表达式全部列出,right会将右边不满足表达式的全部列出,full会将两个表中不满足表达式的都列出。
子查询
子查询是一种在查询语句中嵌套另一个的查询,子查询可以支持多层嵌套。
子查询可以出现在两个位置。
1, 出现在from之后当成数据表,这种用法也被称为内视图。
2, 出现在where条件之后作为过滤条件。
注意:子查询需要使用括号括起。把子查询看做数据表时可以为该子查询起别名。且若作为前缀限定数据列,必须给该子查询起别名。把子查询当成过滤条件时应放在比较运算符的右方,把子查询当成过滤条件时,单行子查询使用单行运算符,多行子查询使用多行运算符。
使用案例:
1, select *
from (select * from table_name) t
where condition
这种用法将子查询看做数据表使用。
2, 当返回单值,或者单列,单行值时子查询可以看成一个量,用于比较
当子查询返回多个值的时候,需要使用 in any all等关键字。
In:可单独使用,表示某个值在后面的查询结果中。
如:where x in
(select column_name
From table_name
);
Any, all 与>,<,>=,<=,<>,=连用
Any表示任何一个值,all表示这其中的全部值。
其中=any与上述in的用法相同。
例:select column_name
From table_name
Where x =
Any(select column_name
From table_name );
<any 只要小于最大值,>any只要大于最小值——这是一种存在性的关系
All与之相反,是一种全称性,任意性的关系。
还可以使用多个列的子查询,此时where后面的匹配量应该使用括号括起来。
例:
Select column_name
From table_name
Where (x,y) > any
(select column_name ,column_name
From table_name );
集合运算
select运算的结果是一个集合,相对的集合可以进行交并补的运算。
两个集合需满足一定条件:
1, 两个集合包含的数据列数一定相等
2, 数据类型一一对应。
1 union运算(并集)
语法:select statement union select statement;
2 minus运算(差集)
语法: select statement minus select statement;
注意MySQL不支持这种语法
可以通过where … not in …完成
3 intersect运算(交集运算)
语法:select statement intersect select statement;
同样mySQL不支持这样的语句。
可以通过join…on(condition)完成