当前位置: 澳门新濠3559 > 数据库 > 正文

希望可以让您对MySQL表结果集有更深的了解,在关

时间:2019-10-06 19:46来源:数据库
做了一个如下的小厕所,如果我需要得到返回是 d,f 那我需要用那组语句呢? 数据分组 – max,min,avg,sum,count Eg: 数据分组查询,熟练应用max,min,avg,sum,count 1. (min max) SQL select min(sal),max(sal

做了一个如下的小厕所,如果我需要得到返回是 d,f 那我需要用那组语句呢?

数据分组 – max,min,avg,sum,count

Eg: 数据分组查询,熟练应用max,min,avg,sum,count

1.

(min

max)

SQL> select min(sal),max(sal) fromemp;

  MIN(SAL)  MAX(SAL)

      800      5000      查询出最大工资和最少工资

2.

(min

max)

SQL>  select ename,sal fromemp where sal=(select max(sal) from emp);

ENAME           SAL

KING        5000.00        查询出员工中工资最高的人(子查询)

3.

(avg

sum)

SQL> select avg(sal),sum(sal) fromemp;

  AVG(SAL)  SUM(SAL)

2073.21428     29025      查询员工的平均工资和总工资

4.

(count)

SQL> select count(ename) from emp;

COUNT(ENAME)

         14               查询员工总人数

5.

(avg

子查询)

SQL> select * from emp where sal>(selectavg(sal) from emp);

EMPNO ENAME     JOB        MGR HIREDATE         SAL     COMM DEPTNO

7566 JONES     MANAGER   7839 1981/4/2     2975.00              20

 7698 BLAKE     MANAGER   7839 1981/5/1     2850.00              30                        查询出工资高于平均工资的人的信息

MySQL表结果集相信大家都比较了解,下面就为您介绍多个MySQL表结果集组合的几种方法,希望可以让您对MySQL表结果集有更深的了解。

1 数据库和SQL

A:

Groupby 和having子句

1.      Groupby 用于对查询的结果分组统计

2.      Having 子句用于限制分组的显示结果

Eg:groupby和having语句的熟练应用。注意group by后可以跟多个条件

1.(

Group

by)

SQL> selectDEPTNO,avg(sal),sum(sal) from emp group by DEPTNO order byDEPTNO;

DEPTNO  AVG(SAL)  SUM(SAL)

10     2916.66666  8750

20      2175     10875  查询每个部门的平均工资和最高工资

2.

(group

   by)

SQL> selectDEPTNO,avg(sal),job from emp group by DEPTNO,job order by DEPTNO;

DEPTNO  AVG(SAL) JOB

10      1300 CLERK

10      2450 MANAGER

10      5000 PRESIDENT查询每个部门的每种岗位的平均工资和最低工资

3.

(group

By,  having)

SQL> selectdeptno,avg(sal) from emp group by deptno having avg(sal)<(select avg(sal) fromemp);

DEPTNO  AVG(SAL)

30 1566.66666   查询出部门平均工资低于平均工资的部门及其平均工资

3.      分组函数只能出现在选择列表、having、order by子语句中

4.      如果在select语句中同时包含group by、having和order by,那么他们的顺序是group by→having→order by

5.      在选择列中如果有列、表达式和分组函数,那么至少有一个要出现在group by字句中,否则则会出错。 ---(oracle11g测试分组条件可以不出现在查询字段中)

select * from table2
union all
select * from table2

1.1 数据库

  DB, DBMS

  DBMS的种类: 层次性数据库, 关系型数据库, 非关系型数据库

  RDBMS, 关系数据库管理系统

;WITH CA AS(
SELECT *
    FROM (VALUES('a'),('b'),('c'),('d'))a (A))
,CB AS (
SELECT *
    FROM (VALUES('a'),('b'),('c'),('f'))a (A)
)
SELECT * FROM CA
EXCEPT SELECT * FROM CB
UNION 
SELECT * FROM CB
EXCEPT SELECT * FROM CA

 Oracle的多表查询

select * from table2 union select * from table2

1.2 数据库的结构

  RDBMS常见的系统结构是 客户端服务器结构

  表: 在关系数据库中管理数据的二维表

  关系数据库必须以航为单位进行数据的读写

B:

---基于2个或两个以上的表的查询

Eg:表的联力查询

1.

(a1.no=

a2.no)

显示雇员名,雇员工资和所在部门的名称

SQL> select ename,sal,DNAME from emp,dept whereemp.DEPTNO = dept.DEPTNO;

ENAME           SAL DNAME

CLARK       2450.00 ACCOUNTING

SCOTT       3000.00 RESEARCH

WARD        1250.00 SALES

2.

(and

)

显示部门号为10的部门名和员工和工资

SQL> select ename,sal,DNAME from emp,dept whereemp.DEPTNO = dept.DEPTNO and emp.deptno=10;

ENAME           SAL DNAME

CLARK       2450.00 ACCOUNTING

3.

(between

and)

显示雇员的性命,工资及其工资的级别 – (添加表salgrade)

SQL> select ename,sal,grade from emp,salgradewhere emp.sal between salgrade.losal andsalgrade.hisal;

ENAME           SAL     GRADE

SMITH        800.00         1

MILLER      1300.00         2

ALLEN       1600.00         3

JONES       2975.00         4

KING        5000.00         5

注:where内容可where (emp.sal>salgrade.losal andemp.sal

4.

(order

by)

显示雇员名,工资以及部门名,并按部门排序

SQL> select ename,sal,dname from emp,dept whereemp.deptno=dept.deptno order by dept.dname;

ENAME           SAL DNAME

MILLER       1300.00ACCOUNTING

KING        5000.00 ACCOUNTING

FORD        3000.00 RESEARCH

在MS-SQL如果将两个或更多查询的结果组合为单个结果集,该结果集包含联合查询中的所有查询的全部行,常用的方法如下:

1.3 SQL

  标准SQL和特定的SQL

  SQL语句的分类

    1) DDL 数据定义语言: create, drop, alter

    2) DML 数据操作语言(90%的使用率): select, instert, update, delete

    3) DCL 数据控制语言: commit, rollback, grant, revoke

  SQL的基本原则

    1) 以分号结尾

    2) 不区分大小写, 一般关键字大写, 表首字母大写, 列小写

    3) 常数的书写方式是固定的, 字符串日期常数用单引号, 数字直接写就行

    4) 空格要是半角的

    5) 注释有两种写法 单行用-- 多行用/**/

;WITH CA AS(
SELECT *
    FROM (VALUES('a'),('b'),('c'),('d'))a (A))
,CB AS (
SELECT *
    FROM (VALUES('a'),('b'),('c'),('f'))a (A)
)
SELECT * FROM CA
EXCEPT SELECT * FROM CB
UNION 
(SELECT * FROM CB
EXCEPT SELECT * FROM CA)

自连接查询

  • ### 自连接是指在同一张表的连接查询

Eg:显示员工的上级领导的姓名--- 同表联立当成2个表来考虑就可以了

SQL> selecta1.empno,a1.ename,a1.mgr,a2.empno,a2.ename from emp a1,emp a2 wherea1.mgr=a2.empno;

EMPNO ENAME       MGR EMPNO ENAME

7902 FORD       7566  7566JONES

7788 SCOTT      7566  7566JONES

  • ### 子查询

---- 子查询是指嵌入在其他sql语句中的select语句

一.   使用union 或union All语句
1.   union 与 union all语句的区别
Ø UNION 组合多个表或结果集)并将其作为单个结果集返回;
Ø UNION ALL 在结果中包含所有的行,包括重复行。
Ø 也就是说,使用UNION组合两个表时,将重复的记录删除;而使用UNION ALL组合两多个表时,不考虑结果集中是否存在重复记录。
2.   使用 UNION 运算符时请注意:
Ø 所有查询中的列数和列的顺序必须相同。
 在使用 UNION 运算符组合的语句中,所有显示列表的数目必须相同列表内容可以包括:列名、算术表达式、聚合函数等);
Ø 数据类型必须兼容。
 在使用 UNION 组合的结果集中的相应列必须具有相同数据类型,或者两种数据类型之间存在隐性数据转换,或提供了类型转换。例如,在datetime 数据类型的列和 binary 数据类型的列之间不可能存在 UNION 运算符,除非提供了显式转换,而在 money 数据类型的列和 int 数据类型的列之间可以存在 UNION 运算符,因为它们可以进行隐性转换。
Ø 用 UNION 运算符组合的各语句中对应的结果集列出现的顺序必须相同,因为 UNION 运算符是按照各个查询给定的顺序逐个比较各列。
3.   与其它 Transact-SQL语句一起使用 UNION 语句时,应注意:
Ø 如果在有UNION的语句中使用INTO语句,则只能在最终表或结果集)中使用,如果要在单个表或结果集)中使用INTO语句,MS-SQL将会提示错误;
错误语句:Select AID,AName,ADemo Into From ATable Union All Select BID,BName,BDemo Into From Btable
Ø 只有在 UNION 语句的结尾,才允许使用 ORDER BY 和 COMPUTE 子句以定义最终结果的顺序或计算汇总值。不能在组建 UNION 语句的单独查询中使用这些子句。
错误语句:
Select AID,AName,ADemo From ATable order by AID
Union All
Select BID,BName,BDemo From BTable Order By BID  
可以这样写:
Select * From
(Select AID,AName,ADemo From ATable Union All Select BID,BName,BDemo From BTable) a
Order By a.AID
也可以这样写:
Select AID,AName,ADemo From ATable
Union All
Select BID,BName,BDemo From BTable
  Order By AID
   此处之所以按AID来排序,是因为MS-SQL中将最后的结果表中的第一列命名为AID。某些DBMS的系统并不对使用不同列名的查询的表列命名,这样就不能使用Order By 列名的方式来排序,不过可以引用结果表中列的顺序来排序,例如:Order By 1,则相当于Order By AID
Ø GROUP BY 和 HAVING 子句只能在单独的查询中使用;它们不能用来影响最终的结果集。
Ø UNION 运算符可用于 INSERT 语句中。
 FOR BROWSE 子句不能在包含 UNION 运算符的语句中使用。FOR BROWSE使用及说明可以参考SELECT语句的说明)
   注意:某些DBMS系统在由UNION组合查询的Select子句中不允许使用函数和表达式。

1.4 表的创建

  数据库的创建

create database 数据库名称;

  表的创建

create table 表名(
    列名 数据类型 该列的约束
    ...
    表的约束        
);

  命名规则

    名字可以是: 半角英文字母, 数字, 下划线, 

    必须是以英文开头

    名称不能重复

  数据类型

    integer, char(定长的字符串), varchar(不定长的字符串), date

  主键约束

    primary key

 C:

单行子查询

---- 只返回单行数据的子查询

Eg:查询与SMITH同一部门的所有员工

SQL> select ename,DEPTNO from emp whereDEPTNO=(select DEPTNO from emp whereename='SMITH');

ENAME     DEPTNO

SMITH         20

SCOTT         20

二.使用except 或 INTERSECT 运算符
与UNION相似,except 或 INTERSECT 运算符也可以将多个表或结果集)组合生成一个新表或结果集),只是结果不同而已。
1.   Except运算符
和 UNION 指令类似, Except也是对两个 SQL 语句所产生的结果做处理的。不同的地方是Except在每一个表或结果集)与第二个表或结果集)比较前,已从每一个表的结果集中清除了所有重复的信息。也就是从Except运算所得到的结果集绝不会包含重复的记录行)。
例如:我们要在Table_A(学生信息表)与Table_B(学生必修课完成信息表)中找出未完成的学生信息
   Select * From Table_A
   Except
   Select * From Table_B
注意:许多DBMS系统不支持EXCEPT运算符,则此时不得不使用Left Outer Join运算符来实现。而在Oracle中的Minus与EXCEPT相似。
2.  INTERSECT运算符
和 UNION 指令类似, INTERSECT 也是对两个 SQL 语句所产生的结果做处理的。不同的地方是,UNION 是联集,而 INTERSECT 是交集。也就是说,INTERSECT生成的结果集是多个表或结果集所共有的记录行)。
   Select AID,AName,ADemo From ATable
   INTERSECT
Select BID,BName,BDemo From Btable

1.5 表的删除和更新

  删除表

drop table 表名;

  添加行

alter table 表名 add column 列的定义;

  插入数据

insert into 表 values (值, 值...);
;WITH CA AS(
SELECT *
    FROM (VALUES('a'),('b'),('c'),('d'))a (A))
,CB AS (
SELECT *
    FROM (VALUES('a'),('b'),('c'),('f'))a (A)
)
(SELECT * FROM CA
EXCEPT SELECT * FROM CB)
UNION 
(SELECT * FROM CB
EXCEPT SELECT * FROM CA)

多行子查询

---- 返回多行数据的子查询

Eg:查询和部门10的工作相同的雇员的姓名、工作和部门号

SQL> select ename,job,deptno from empwhere job in (select distinct job from empwhere deptno=10);

ENAME     JOB      DEPTNO

CLARK     MANAGER      10

BLAKE     MANAGER      30

JONES     MANAGER      20

---- 使用all操作符

Eg:查询出工资比部门20的所有员工的工资高的员工

SQL> select ename,sal,deptno from emp wheresal> all (select sal from empwhere deptno=30);

ENAME           SAL DEPTNO

JONES       2975.00    20

SCOTT       3000.00    20

--- 使用any操作符

Eg:显示工资比20部门任意一个员工工资的员工

SQL> select ename,sal,deptno from emp wheresal> any (select sal from empwhere deptno=30);

ENAME           SAL DEPTNO

KING        5000.00    10

FORD        3000.00    20

三.使用UNION Corresponding组合来自两个或多个与UNION不兼容的表中的行
例如:有两个表
   Create Table Table_A(ID int,office int,address varchar(20),department char(5),pay money)
   Create Table Table_B(office int,ID int,address varchar(20),department char(5))
   可以使用UNION Corresponding来获取两个表中有共同列名的列中数据:
   Select * From table_A UNION Corresponding Select * From Table_b
   注意:并不是所有的DBMS都支持UNION Corresponding运算,包括MS-SQL Server

2 基础查询

 

多列子查询

--- where后的条件是一一对应的,实际是两个条件的and连接

Eg:查询出与'SMITH'部门和岗位一样的员工信息

SQL> selectename,job,deptno from emp where (deptno,job)=(select deptno,job from emp whereename='SMITH');

ENAME     JOB      DEPTNO

ADAMS     CLERK        20

--- 在from子句中使用子查询  ----  重要内容

 From中的子查询被认定为一个内嵌试图,并且应该取别名

Eg:显示高于自己部门平均工资的员工信息

SQL> selectename,sal,mysal,a1.deptno from emp a1,(select deptno,avg(sal) mysal from emp group bydeptno) a2 wherea1.deptno=a2.deptno anda1.sal>a2.mysal;

ENAME           SAL     MYSAL DEPTNO

ALLEN       1600.00 1566.66666    30

JONES       2975.00      2175    20

MySQL分表处理的实现方法

2.1 select语句基础

  1) 基本格式

select 列名, 列名...
from 表名,..  ;

  2) 其中在列名去中输入* 表示显示所有列, 而且是按照表定义的列的顺序进行显示

  3) 为列设置别名

select 列名 as 别名, 
    列名 as 别名, 
    ...
from 表名,..  ;    

    设置别名之后, 查询得到的结果的行首就显示的是别名

    如果设置成中文的话需要使用双引号来定义

  4) 设置常数列

select 列名 as 别名, 
    列名 as 别名, 
    常数 as 别名
    ...
from 表名,..  ; 

    这个常数可以是数字也可是字符串

    这么设置之后这个列的值就是这个常数值, 且表头显示的是别名, 如果不设置别名, 那也显示成该常数

  5) 去除重复行

    在select后面加上关键字distinct

    注意distinct的位置, 只能在第一个列名之前

    去除是根据后面选取行的组合来判断的重复

  6) where限定行

    在from之后加上where语句限定查询出来的结果

 实际情况执行一下就知道,其实BC 的语句都可以执行。而A的执行情况就是只返回了 d。这个其实是跟select 的执行顺序有关的。

分页查询          -----         非常重要

  • Rownum分页

a.      (select * from emp) 建立内嵌试图

b.      显示rownum。(这里的rownum是oracle自带分配的)

Eg

SQL> select ename,job,sal,rownum rn from(select * from emp) a1;

ENAME     JOB            SAL        RN

SMITH     CLERK       800.00         1

ALLEN     SALESMAN   1600.00         2

WARD      SALESMAN   1250.00         3

c.      筛选出rn为6-10的数据 --(非常重要)

d.      Oracle采用二分法筛选,所以需要先筛选出1-10.,在筛选出6-10

e.      如需改变查询列时,只需改变最里层的select的就可以了

f.       如需排序,也只需修改最里层的select视图就可以了

Eg: 在第一次的筛选中要用rownum,第二次要用别名rn

SQL> select * from (select a1.*,rownum rn from (selectename,job,sal from emp) a1 where rownum<=10) wherern>=6;

ENAME     JOB            SAL        RN

BLAKE     MANAGER    2850.00         6

CLARK     MANAGER    2450.00         7

SCOTT     ANALYST    3000.00         8

  • 根据rowid分页
  • 按分析函数分页

MySQL授权表使用示例

2.2 算数运算符和比较运算符

  算数运算符:

    • * /

    关于NULL的运算结果都是NULL

  比较运算符: = <> >= <= > <

    数字的比较是按照值比较的

    字符串的比较是按照字典顺序进行比较的

    NULL不会和别的结果进行比较

    判断是NULL用IS NULL

    判断不是NULL用 IS NOT NULL

通常我们看select 的执行顺序的时候,可能会忽略这2个不常用的连接谓词导致误判。这里做一个实验就可以发现,其实 Except(Intersect 同理) 的查询优先级是和 union 或者union all 是同级的。

利用查询结果来创建新表

Eg:创建一个部门平均工资表从emp表中生成

SQL> create table avgsal (deptno,avgsal) as select deptno,avg(sal) fromemp group by deptno;

Table created

SQL> select * from avgsal;

DEPTNO    AVGSAL

30      1566.66666

20      2175

10      2916.66666

MySQL内存表的弊端

2.3 逻辑运算符

  NOT 表示否定

  AND OR 与或者非

    AND的优先级是高于OR的

    要想按照指定的顺序执行需要使用括号

  逻辑运算中NULL需要单独讨论

    对NULL使用比较运算符得到的结果是unkonwn(不确定)

    除了true, false还有unknown

    在SQL中的逻辑运算有三值逻辑

    判断值的原则是 真>unknown>假, AND取等级低的, OR取等级高的

    因此在AND中, 真 AND unknown = unknown, unknown AND false = false

    在OR中, 真 OR unknown = 真, unknown OR false = unknown

所以遵循从左到右的顺序,如果不用括号改变执行顺序,从上面的例子,将会返回  CA Except CB -> Union CB -> Except CA 的执行结果。也就是一个容易忽略的位置。

合并查询   ----    比较麻烦,但效率较高

Ag:在实际应用中,为了合并多个select语句的结果,可以使用集合操作符号:union all,union,intersect,minus

教您彻底删除MySQL注册表信息

3 聚合和排序

因为比较少用,所以我也躺枪了。在此分享一波,希望大家不要踩坑。

a.      Union

Union用于取得两个结果集的合集

Eg:查询sal大于2500并且部门为20的员工,实际效果等同于and

SQL> select ename,deptno,sal from emp wheresal>2500

  2  union select ename,deptno,sal from emp where deptno=20;

ENAME     DEPTNO      SAL

ADAMS         20  1100.00

BLAKE         30  2850.00

FORD          20  3000.00

JONES         20  2975.00

KING          10  5000.00

Mysql User表的权限字段说明介绍

3.1 对表进行聚合查询

  常用的聚合函数

    count: 计算个数(行数), 可以指定参数, 当指定列值为NULL时, 不做统计

    sum: 求和

    avg: 求平均值

    max: 求最大值

    min: 求最小值

  关于NULL

    指定列时, 如果值为NULL, 会忽略

    对于AVG, 如果有NULL值, 同样也是忽略, 也就是分子没有那个值, 分母也不算拿一行

  在聚合函数中, 可以传入参数, 可以传入distinct来删除重复的值

PS

b.      Unionall

注: union all与union用法相同,但他的效果是缺两个集合的全集(不会合并相同的)

selec...

3.2 对表进行分组

  使用group by来分组

  group by后面指定的列为聚合键或者分组列

  分组NULL同样会单独形成一个分组

  有where会先执行where, 得到筛选的结果之后再进行分组

  注意事项:

    1) select 不能选取除group by选定的列的其他列

    原因是: 既然已经分组了, 那么一个分组对应的记录就有可能是多条, 在当前分组下, group by后面的列的值是一致的并没有什么关系, 但是如果选取别的列, 那么就有可能是不同的值, 那么此时就不能够确定显示哪一个值了, 这样就造成了歧义, 在mysql中会排序然后得到第一个值

    2) group by后面不要写别名

    原因是: select语句是在group by之后执行的, 因此使用了别名那个时候是找不到的

    3) 不要在where中使用聚集函数

    实际上能够使用聚集函数的就有: select子句, having, order by, group by

1 select 执行顺序的地址 :

c.      Intersect

Intersect是用于取两个集合的交集

Eg: 对于分页查询同样可以采用这种方法

SQL> select * from (select ename,job,sal,rownumrn from emp) where rownum<=10

  2  intersect select * from (selectename,job,sal,rownum rn from emp) where rn>=6;

ENAME     JOB            SAL        RN

BLAKE     MANAGER    2850.00         6

CLARK     MANAGER    2450.00         7

KING      PRESIDENT  5000.00         9

SCOTT     ANALYST    3000.00         8

TURNER    SALESMAN   1500.00        10

3.3 为聚合结果指定条件

  使用having在分组的基础上进行过滤筛选

  having的组成部分

    常数

    聚合函数(聚合函数类的参数可以是非聚合键)

    聚合键

  因此在having中使用别的键是错误的

  having和where的区别

    要将having和where区分开来

    having: 指定组的条件

    where: 指定行的条件

    能使用where就使用where, 因为where的执行效率更高

2 Except 和 Intersect 都是返回交叉之后不重复的结果的,这个需要特别注意

d.      Minus

Minus是取两个集合的差集(即取A集合减去与B集合重复的部分)

union all, union,intersect,minus

Ag:A:(1,2,3)B:(3,4,5){
        1.   A minus B = (1,2)
        2.   A  union B= (1,2,3,4,5)
        3.   A  uniomall  B = (1,2,3,3,4,5)
        4.   A intersect  B = (3)澳门新濠3559 1

3.4 对查询结果进行排序

  排序使用order by

  一般地, 查询的记录的顺序是随机的(或者有的数据库会有默认的排序)

  由于进行数据的排序是在返回结果的时候执行, 因此一般地, order by语句放在查询语句的末尾

  ASC表示升序, DESC表示降序

  指定多个排序键

    可以指定多个排序键, 这样在第一个键相同的时候判断第二个键, 进一步确定排序的顺序

  NULL值会固定放在最后, 不管是升序还是降序

  查询语句的执行顺序:

    from -> where -> group by -> having -> select -> order by

  order by可以使用的列

    表中的列

    select中选取的列的序号, 从1开始

    聚合函数

 

4 数据更新

4.1 数据的插入

  insert的基本语法

insert into 表名(列1, 列2, ...) values (值1, 值2,...)... ;

  原则上, 一次insert语句只会插入一行数据

  但是有的数据库是支持多行插入的, 用括号括起来表示一行, 用逗号间隔多行的数据

  列清单可以省略, 省略之后按照顺序排序数据

  值可以是NULL

  插入默认值

    1) 通过显式的方式

    在设置了默认值的位置用default代替

    2) 通过隐式的方式

    列清单不写该列

  从其他表中赋值数据

insert into 表名(列1, 列2, ...) select... ;

4.2 数据的删除

  删除表

drop table 表名;

  全部删除数据

delete from 表名;

  删除指定记录

delete from 表名 where 条件;

  清空表, 还原表为初始状态

truncate 表名;

    不能加入where语句指定某记录

    处理速度比delete快很多

4.3 数据的更新

  update的基本语法

update 表名
    set 列名 = 表达式
   ...
    where 条件;

  表达式可以是一个固定的值, 也可以是一个运算

  NULL清空

    将值设置为NULL, 但是该列必须是允许空的

  其中列名=表达式可以有多个, 这样可以方便进行多行更新

澳门新濠3559,4.4 事务

  事务: 需要在同一个处理单元中执行的一系列更新处理的集合

  创建事务

事务开始语句;
    DML语句;
    ...
    ...
事务结束语句;

    一般地, 事务开始语句分数据库, SQL server的是begin transaction, MySQL是start transaction, Oracle没有开始语句

    因此一定要注意事务的开始起点, 在标准SQL中已经规定了一种悄悄处理事务的方法

    结束语句一般是commit表示提交

    一定要记得在事务结束之后仔细确认

    结束语句还可以是rollback, 表示取消事务包含的全部更新处理

  一般的数据库产品已经不需要事务的开始语句了, 很多数据库在执行第一个SQL语句的时候就已经悄悄开始了事务

  一般有两种情况

    1) 每一条SQL语句都是一个事务(自动提交模式): SQL Server, PostgreSQL, MySQL

    2) 知道用户执行了commit或者rollback为止算作一个事务: Oracle

    因此在大多数情况下, 采用自动提交模式的数据库, 你一旦误删了某个表, 就再也找不回来了

  酸性性质

    ACID性质, 这是所有DBMS都必须遵守的规则

    A: 原子性, 事务在结束时, 其中包含的更新处理要么全部执行, 要么完全不执行, 这样就防止了中途停止的情况, 不会对业务造成不好的影响

    C: 一致性(完整性), 事务中执行的处理, 必须要满足数据库数据表设置好的约束, 如果不遵守约束, 就会报错, 然后事务回滚

    I: 隔离性, 保证事务时间互不干扰的特性, 该事务之间不会相互嵌套, 当前事务如果没有提交, 所做的修改是对别的事务是不可见的, 直到事务完成

    D: 持久性(耐久性), 事务一旦结束, 数据库会保证数据的状态得以保存, 常见的保证持久性的方法是将执行记录保存到存储介质上, 也就是一般意义的保存日志

5 复杂查询

5.1 视图

  视图基本上与表差不多, 只是说表实际存储数据, 而视图是虚表, 不会存储实际的数据

  一般的, 对表的修改更新等操作会实际的写到存储介质上进行保存, 但是对视图的处理, 不会将数据保存到任何地方, 实际上视图保存的就是select语句, 当需要从视图中读取数据的时候, 就是根据这个select语句来创建一个临时表

  视图的优点

    1) 无需保存数据, 节省存储空间

    2) 可以将常用的select语句保存成视图, 这样就不会每次重写查询语句, 且会得到数据的 最新状态

  创建视图

create view 视图名称 (视图列名, 视图列名, ...)
as
select语句 ;

    创建视图的时候, 视图名称后面的名称是可以省略的, 省略的时候默认就是查询出来的列名; 如果指定了列名, 就相当于是重新命名的列名, 但是要注意此时的列名要和查询得到的列相同数量

  视图的注意事项:

    1) 可以在视图的基础上再创建视图, 也就是形成了多重视图, 但是这样会降低SQL的性能, 一般杜绝这样的操作

    2) 一般定义视图的时候不使用order by, 因为数据行是没有顺序的, 尽管很多数据库支持这样的操作

    3) 可以对视图进行更新, 但是有条件限制, 一般视图是无法进行更新的, 因为诸如你创建的视图是基于聚合函数的, 聚合函数是针对一个分组的, 如果对视图进行添加数据了, 那么对应这个数据反映到原表上是一组数据, 数据库是没有办法处理这一组数据仅仅只有一个聚合的结果

    可以进行更新的条件

    1) select子句中没有使用distinct

    2) from后面就只有一张表

    3) 没有使用group by, 也没有使用having

  视图的使用

    一般对视图的操作就是查询操作, 可以将视图名称当成一般的表使用就好了

  视图的删除

drop view 视图名称;

    在删除的时候, 如果有其他视图是在当前视图的基础上创建的, 那么这个视图是无法被删除的, 此时还要删除需要加上级联删除

drop view 视图名称 cascade;

5.2 子查询

  子查询就是在select ..from的后面再加上select语句查询结果, 并用as关键字命名, 但不是所有的数据库都支持as, 直接写名字就行

select ..
from (select语句) as 名字
.. ;

  可以在子查询中嵌套子查询

  标量子查询: 只返回一行一列数据的子查询

    标量子查询相当于就是一个标量值, 因此可以应用到标量值应用的所有位置, selet后面, where后面, having后面等

    使用标量子查询需要注意的是, 一定要注意查询的结果是一行一列的

5.3 关联子查询

  现在有个商品表    

  澳门新濠3559 2

  现在需要根据筛选出单价大于该分类的平均值的商品

  如果仅仅使用子查询, 得到的SQL是错误的

SELECT *
FROM sp
WHERE sp_prise > (
    SELECT AVG( sp_prise) 
    FROM sp 
    GROUP BY sp_class
);

  但是这样会报错, 因为子查询的值不唯一, 那如何将当前商品和当前商品类别的平均价格对应上呢, 就是关联子查询

  关联子查询就是在子查询中, 关联表的操作, 应用关联子查询得到的SQL是这样的

SELECT *
FROM sp as s1
WHERE s1.sp_prise > (
    SELECT AVG( s2.sp_prise) 
    FROM sp as s2
    WHERE s1.sp_class = s2.sp_class
    GROUP BY s2.sp_class
);

6 函数, 谓词和case表达式

6.1 函数

  函数: 给定一些输入, 输出特定的输出

    输入值就是参数

    输出值就是返回值

  函数分为

    1) 算术函数: abs(绝对值), mod(求余, 传入两个参数), round(四舍五入), 

    2) 字符串函数

      拼接:

-- 一般数据库使用||拼接字符串
SELECT sp_name, sp_class, sp_name || sp_class as my_str
FROM sp;

-- SQL Server使用 拼接字符串
SELECT sp_name, sp_class, sp_name   sp_class as my_str
FROM sp;

-- MySQL使用concat()函数拼接字符串
SELECT sp_name, sp_class, concat(sp_name,sp_class) as my_str
FROM sp;

      字符串长度: length(字符串) SQL Server使用的是len()

      转成小写: lower()

      转成大写: upper()

      字符串替换: replace(字符串对象, 要替换的字符串, 替换成的字符串)

      字符串截取: substring(字符串对象 from 起点 for 截取的字符数) 例如: substring("ABCDE" from 2 for 3) 得到的字符串是"BCD"   

    3) 日期函数

      当前日期: current_date

      当前时间: current_time

      当前日期和时间点: current_timestamp

      截取日期元素: extract(日期元素 from 日期)

SELECT CURRENT_TIMESTAMP,
    EXTRACT(YEAR FROM CURRENT_TIMESTAMP),
    EXTRACT(MONTH FROM CURRENT_TIMESTAMP),
    EXTRACT(DAY FROM CURRENT_TIMESTAMP),
    EXTRACT(HOUR FROM CURRENT_TIMESTAMP),
    EXTRACT(MINUTE FROM CURRENT_TIMESTAMP),
    EXTRACT(SECOND FROM CURRENT_TIMESTAMP);

    4) 聚合函数: sum, avg, max, min, count

    5) 转换函数: 转换函数是进行类型的转换或者值的转换

      类型转换

        cast(转换前的值 as 想转换的数据类型)

      值转换, 将NULL转化为其他值

        coalesce(数据1, 数据2, ...)  

6.2 谓词

  常见的谓词就是一些比较运算符(= < >等)

  具体来说就是, 返回值是真值(true/false/unknown)

  常见的谓词有like betwween, is null, is not null, in, exists

  (1) like

    涉及到前方一致, 中间一致, 后方一致, 分别是: 字符串%, %字符串%, %字符串

    %表示任意个字符, _表示任意一个字符

  (2) between A and B 范围查找

    取值范围为 [A, B] 

  (3) is null, is not null

    判断是不是null不能用 值 = null 来判定, 这样始终返回为false

    判断是否为null

  (4) in

    in/not in 可以用or来实现, 但是使用in更加简洁

  (5) exist

    判断记录是否存在某种条件, 返回值是false或者true

6.3 case表达式

  case表达式主要完成条件分歧

  具体格式为

case when 判断表达式 then 表达式
    when 判断表达式 then 表达式
    when 判断表达式 then 表达式
    ...
    else 表达式
end

  课件不管有多么复杂的case语句, 最终的结果始终是一个简单的值

  基于这样的特点, case语句也可以放在任何单值的位置

7 集合运算

7.1 表的加减法

  表的加减法是处理增减或者减少数据行

  (1) 表的加法 union

  union是对表进行并集运算, 条件是列必须相同(一般地, 列对应是一样的union才有意义), 得到的结果会去掉重复

  注意:

    列要相同

    列的类型必须一致

    可以使用任何select大但是order by只能在最后使用且使用一次, 也就是说只能对union之后的表进行排序

    如果要包含重复的行要使用union all

  (2) 表的公共部分 intersect

  intersect取得的是表的交集

  (3) 表的减法 except

  求的在A表中不在B表中的记录

7.2 联结

  联结的作用是增加列, 是在列的方向进行一定的操作

  (1) 內联结 inner join

  格式为

select 列 from 表A
inner join 表B on 联结条件
其他操作;

  得到的结果是同时满足联结条件的记录, 不存在补记录的现象

  (2) 外联结 outer join

  与內联结相比, 外联结存在补齐的情况

  (3) 交叉联结 cross join

  得到的就是笛卡尔积 

 

编辑:数据库 本文来源:希望可以让您对MySQL表结果集有更深的了解,在关

关键词: 澳门新濠3559