数据库命令语句总结

1 真实企业开发中，不用图形用户界面，太占用资源，一般都是通过代码（SQL）来操作数据库

SQL：结构化操作数据库的语言

 

一、 使用SQL语句来创建数据库

在硬盘上先创建一个文件夹library，用来保存创建数据库

创建的格式：

create database 数据库名字 

on primary(

  name = 主要文件的逻辑名,

  filename = "保存数据库文件夹的路径\文件全名",

  size = 初始化数据库（核心文件）大小,

  maxsize =指定最大的范围/unlimited,

  filegrowth = 文件增长的方式

   （1 M  / 2  %）

)log on(

name = 日志文件的逻辑名,

flename ="保存数据库文件夹的路径\日志文件全名",

  size = 初始化日志文件的大小,

  maxsize = 指定最大的范文/unlimited,

  filegrowth = 文件增长的方式

  （1 M / 2 %）

)

 

二、 常用数据类型

int ：整数

char和varchar区别：

char：定长字符串，char(10)，'liu'真实存储

会占用10个字符的空间，比较浪费空间，但是

运行速度较快，一般在字段值较为固定（比如电话号码）和主键，最好用char

varchar:可变长字符串，varchar(10)，'liu'真实存储会占用3个字符的空间，节约空间

 

四、 建表语句（必须记住）

use 数据库名称（在那个数据库里创建表格）

create table 表名(

 列名1 数据类型 primary key,把该设置主键

 列名2 数据类型 ,

 列名3 数据类型 ,

 ... 

)

 

五、 插入语句（往表中添加新的数据） 

1）insert into 表名

   values(列值1,列值2,列值3...)

 a)往表中添加数据，所有的列都必须要赋值

 b)列值的顺序、类型、个数都必须要跟表结构相匹配

 c)如果该插入语句，有任何一个位置出错，则整条数据都无法插入

 d) into 可以省略，建议大家最好都别省略

 

2) 只插入部分列的列值

格式：

insert into 表名（列名1，列名2...）

values（列值1，列值2...）

a) 表名后，添加要插入列的名字，values后面根据列名对应列值，而且列名跟列值的顺序必须完全一致

b) into 依然可以省略

 

六、修改操作：针对表中的数据，对某些列值进行修改

1)格式：

  update 表名 set 列名1 = 列值1，列名2 = 列值2...

  where 条件

2）where条件:对哪些数据进行修改，如果不加where条件，则整张表中所有的数据都会被修改

 

七、 删除操作：根据条件删除表中的数据

1）格式：delete from 表名 where 条件

2）如果where条件没有的，会删除表所有的数据

 

八、简单的查询操作

1）查询表中所有的数据

select * from 表名

 

2）查询表中某些列的值

select 列名1，列名2... from 表名

 

3）别名:给查询出来列名起一个另外的名字

a)select 列名1 as 别名1,列名2 as 别名2... from 表名

 

b）使用空格，给列起别名

select 列名1 别名,列名2 别名2 from 表名

 

c）使用“=”起别名

select 别名1 = 列名1，别名2 = 列名2

from 表名

 

九、 带条件的查询

select */列名 from 表名 where 条件

1）关系运算符：=，>,>=,<,<=,!=(<>)

2）逻辑运算符：and（逻辑与） ，or（逻辑或），not（逻辑非）

4) in:用来指代多个值

where 列名 in(列值1，列值2，列值3...)

 

5) is null :null，当前这条数据中，该列没有值，就null来表示

is null：当前该列没有值

is not null

 

用not来逻辑非type is null

 

6) 模糊查询：like 

  %：代表0个或多个字符

  _:：代表任意一个字符

 

注意：where author like '%明'：从结果上来看是正确的，但如果在企业开发中，表中数据特别多的，有的作者名字叫'李明'，这样就有问题了，作者的名字必须是3个字符

 

十、 约束：指定表中的数据，必须要符合某些要求，而这些要求就是约束

1、主键约束：主键约束就是用来定义表中的主键，主键用来标识表中的数据（主键是表中数据唯一的标识），而且非空，并且唯一（不重复）

 

 a）可以再表中指定主键约束的名字

格式：

constraint 约束名 primary key(列名)

 

 b）指定为主键的列，必须用not null(非空)来进行修饰

 

 c）一张表中永远只能由一个主键，该主键可以由一个列或者多个列组合

  多个列组合，定义为主键，联合主键

 

d）在表创建以后，再来指定主键（不推荐）

 

可以向任意的表中，追加各种约束

alter table 表名 

add constraint 约束名字 约束类型 列

 

2)唯一性约束（unique）：唯一，不重复

a)唯一性约束用于指定一个或多个列的组合的值是不重复的，可以防止在插入数据的时候，出现重复值。

b)使用唯一约束修饰的列，可以出现null，但是只能出现一次

 

c) 主键约束和唯一约束的区别：

 主键（约束）在表中只能出现一次（主键只有一个）

 唯一约束可以出现多次（多个唯一约束）

 主键不能为null

 唯一约束可以为null

 主键是用来标识数据

 唯一强调的是该列的值，必须是独一无二

 

d）可以表的外界来指定唯一约束

alter table 表名

add constraint 约束名字 约束 (列)

 

e) 删除唯一约束

alter table 表名 

drop constraint 约束名

 

f) 指定多个列的组合用一个唯一约束

 

3) 非空约束：插入数据时候，被非空约束修饰的列必须要有值not null

 

name这个列，用唯一和非空同时进行约束

name这个列必须要有值，而且该列的值必须要唯一

 

4）默认值约束

默认值约束是指给表插入新的数据时候，某些列，如果没有插入数值，可以再设计表，直接加入该列默认值，这样话，该列如果没有插入新的值，则使用默认值

反之，如果插入新的值，则不再使用默认值

 

2、外键约束

外键就是用来连接两张表之间的关联关系的列。

外键约束用来定义外键

第3范式：用来关联多张表，可以其中一张表设置一个列，该列的取值是另外一张表中主键的值，用这个列来维护两张表的关联关系，这个列就是外键

 

首先往父表中插入数据，然后再往子表插入数据

 

3、 检查约束：对输入的列中的值进行检查，符合要求的，才可以插入；

  检查约束一般是设置在单个列上，用来限制并检查用户的输入

 

十一、 利用select语句复制表

1）把整张表中所有的数据全部复制到新表中

select * into 新表名 from 源表名

 

2）跟条件，把源表中部分数据赋值到新表中

select * into 新表名 from 源表名

where 条件

 

3) 指定列来创建新表（并复制对应的数据）

select 列名1，列名2，... into 新表名

from 源表名 

 

4) 只复制表的结构，不复制表中的数据

select */列名 into 新表名 from  源表名

where 条件（false）

 

十二、 限定返回数据的条数

1）限制返回的行数

select top n 列名/* from 表名 where 条件

 

2）按百分比来返回对应数据

select top n percent 列名/* from 表名

where 条件

 

十三、 子查询：查询语句中又包含了查询语句

 

十四、 去掉重复数据（列）

select * from book

insert into book

values('00006','史记','机械出版社','司马迁','历史类',5000,2000)

insert into book

values('00007','大唐双龙传','清华大学出版社','黄易','武侠类',8000,1200)

 

查询所有图书的出版社名字

select pub from book

 

列之前加入关键distinct

select distinct pub from book

 

查询所有图书的类型（去掉所有重复类型名）

select distinct type from book

 

十五、排序：把数据查询出来以后，根据其中某一个列的值，来进行升序，或降序的排列

1）格式：

select 列名/* from 表名

where 条件

order by 列名 asc / desc

 

2）asc：默认值，表示按照该列的值进行

升序排列（由小到大）

   desc：表示按照该列的值进行降序排列

 

降序（由大到小）

案例：按照进货量把所有的图书信息进行升序排列

select * from  book

order by numinput

 

案例：查询所有清华大学出版社出版的图书信息，按照库存量，进行降序排列

select * from book 

where pub='清华大学出版社'

order by numstore desc

 

3) order by 列名1 asc/desc , 

   列名2 asc/desc

   首先根据列名1进行升序或者降序排列，如果列名1中的列值，要是一直，再根据列名2中的列值，进行升序或者降序排列

 

案例：查询所有图书的信息，然后按照进货量升序进行排序，如果进货量一致，则按照库存量进行降序排列

select * from book 

order by numinput asc, numstore desc

 

案例：查询所有的图书信息，然后按照库存量降序排列，如果库存量一致，则按照进货量进行升序排列

select * from book

order by numstore desc, numinput asc

 

十六、 聚合函数（分组函数）

1）count(列名/*):获得该列或整张表的数据的个数

select COUNT(*) from book

 

count(*):表中所有数据的个数

select COUNT(bid) from book

select * from book

select COUNT(type) from book

 

count(列名):该列中列值的个数

 

2）sum(列名)：获得该列所有的列值之和

案例：求出所有进货量之和

select SUM(numinput) from book

 

案例：所有库存量之和

select SUM(numstore) from book

 

3）avg(列名)：获得该列平均值

案例：每种图书的库存量的平均值

select AVG(numstore) from book

 

案例：每种图书的进货量的平均值

select AVG(numinput) as 进货量的平均值

from book

 

4）max(列名)：获得该列最大值

案例：每种图书的进货量的平均值以及库存量的最大值

select AVG(numinput) 进货量平均值,

MAX(numstore) 库存量最大值 

from book

 

5）min(列名)：获得该列的最小值

案例：求出每种图书的库存量的最小值

select MIN(numstore) from book

 

案例：求出每种图书的进货量的最大值，平均值，最小值，总和

select MAX(numinput) 进货量最大值,

AVG(numinput) 进货量平均值,

MIN(numinput) 进货量最小值,

SUM(numinput) 进货量总和

from book

 

案例：求出清华大学出版社所有图书的库存量最大值，平均值

select MAX(numstore) 库存量最大值,

AVG(numstore) 库存量平均值

from book

where pub = '清华大学出版社'

 

案例：每个出版社的进货量最大值

清华：10000   机械：20000   海南：30000

 

十七、 如何使用聚合函数进行查询语句的分组

1）格式

select 列名 / 聚合函数 

from 表名

group by 列名

 

2）首先根据group by 列名把表中所有的数据进行分组，分组以后，会在每一组的数据中使用聚合函数

案例：每个出版社图书的进货量的最大值

首先根据出版社，把所有的图书进行分组，然后每组图书使用MAX，求出每组中图书进货量的最大值

select MAX(numinput) 进货量最大值,

pub 出版社的名称

from book

group by pub

 

案例：求出每种类型的图书的进货量最大值以及平均值，最小值

select MAX(numinput) 进货量最大值,

AVG(numinput) 进货量平均值,

MIN(numinput) 进货量最小值,

type 图书的类型

from book

group by type

 

案例：求出每个出版社的图书的库存量的最大值及进货量的平均值

select MAX(numstore) 库存量最大值,

AVG(numinput) 进货量平均值,

pub 出版社名称

from book

group by pub

 

十八、分组并排序

1）是把所有的数据分组以后，每一组数据中使用聚合函数，最后查询的结果，进行排序（排序永远在最后）

2）格式：

select 列名 / 聚合函数

from 表名

where 条件

group by 列名

order by 列名/别名/聚合函数 asc/desc

案例：查询作者不是鸟山明的图书根据出版社进行分组，显示出来每个出版社，图书的进货量最大值，进货量的平均值

select MAX(numinput) 进货量最大值,

AVG(numinput) 进货量平均值,

pub 出版社名字

from book

where author !='鸟山明'

group by pub

 

先执行where语句，再执行分组，然后再使用聚合函数

select * from book

 

案例：根据类型对图书进行分组，求出每组图书的进货量最大值，根据进货量最大值进行倒序排列

select MAX(numinput) 进货量最大值,

type 类型的名称

from book

group by type

order by MAX(numinput) desc

 

使用别名替代order by 聚合函数

select MAX(numinput) 进货量最大值,

type 类型的名称

from book

group by type

order by 进货量最大值 desc

 

案例：根据出版社对图书进行分组，求出每个出版社图书的进货量之和，进货量的平均值，然后根据进货量的平均值，升序显示每个出版社名称以及图书的进货量之和，平均值

select pub 出版社名称,

SUM(numinput) 进货量之和,

AVG(numinput) 进货量平均值

from book

group by pub 

order by 进货量平均值 asc

 

案例：查询类型不为null的图书，根据type进行分组，显示每种类型图书的库存量的最大值，进货量的平均值及图书的type，按照库存量最大值进行升序排列，如果库存量最大值一致，按照进货量的平均值进行降序排列

select MAX(numstore) 库存量最大值,

AVG(numinput) 进货量平均值,

type 图书的类型

from book

where type is not null

group by type

order by 库存量最大值 asc,进货量平均值 desc

 

十九、 having子句：对分组以后的结果进行过滤

select 列名/聚合函数

from 表名

where 条件         -  第1次过滤

group by 列名      -  分组

having 子句        -  第2次过滤（分组以后）

order by 别名/列名/聚合函数  - 排序

案例：查询每个出版社，图书进货量最大值，并且显示进货量最大值超过10000

use library

select pub 出版社名字,

MAX(numinput) 进货量最大值

from book

group by pub

having MAX(numinput)>10000

 

案例：图书的类型不能为null，求出每种类型的图书库存量的平均值，进货量的总和，并且显示进货量总和超过10000的类型，及库存量的平均值进货量的总和

select AVG(numstore) 库存量平均值,

SUM(numinput) 进货量总和,

type 图书的类型

from book 

where type is not null

group by type

having SUM(numinput) > 10000

 

案例：查询库存量超过100的图书，每个出版社图书库存量总和，进货量总和，显示进货量总和小于20000的图书，把结果按照库存量总和进行降序排列

select SUM(numinput) 进货量总和,

SUM(numstore) 库存量总和,

pub  出版社

from book

where numstore > 100

group by pub

having SUM(numinput) < 20000

order by SUM(numstore) desc

 

案例：查询库存量超过所有图书平均库存量的图书信息

分析：首先查询图书平均库存量，然后只要超过平均库存量就是我们需要数据

select AVG(numstore) from book

1671

select * from book where numstore>1671

select * from book

where numstore > (

select AVG(numstore) from book

)

 

查询进货量超过清华大学出版社进货量的平均值的图书信息

select * from book

where numinput > (

select AVG(numinput) from book

where pub = '清华大学出版社'

)

 

二十、删除表和数据库

1）删除表：drop table 表名

案例：删除book1表

drop table book1

drop table book2

 

2）删除数据库：

 drop database 数据库名

drop database worker

 

二十一、 identity函数：创建主键的值

1）identity(初始值，增量)

2）利用identity来自动给主键，创建主键的值，第1次调用identity，返回是当前初始值，从第2次开始每次调用identity

在原来值得基础上每次加上增量的值

use library

create table worker1(

id int identity(1,1) primary key,

name varchar(50)

)

insert into worker1(name) values('张辽')

insert into worker1(name) values('乐进')

insert into worker1(name) values('徐晃')

insert into worker1(name) values('关羽')

insert into worker1(name) values('陆逊')

select * from worker1

 

 

二十二、 SQLSERVER中常用函数

1）字符函数

len(str):统计str字符的个数

select LEN('liuyingqian')

 

lower(str):大写字母改为小写字母

select LOWER('HELLO')

 

upper(str):小写字母改为大写字母

select UPPER('World')

 

去掉空格

LTRIM(str):去掉左边的空格

select LTRIM('       java')

函数之间可以进行嵌套

select LEN(LTRIM('       java'))

RTRIM(str):去掉右边的空格

select RTRIM('java          ')

如何去掉左右两端的空格？

select LTRIM(RTRIM('      java    '))

left(str,数字)：从左边开始，截取字符，指定数字个数字符

select LEFT('abc123',3)

right(str,数字)：从右边开始截取

select RIGHT('abc123',3)

 

2） 日期函数：

日期包含：年月日时分秒（毫秒）

getDate():获取当前日期

select GETDATE()

 

DateAdd(位置，数字，str)：在日期指定的位置添加数字

select DATEADD(YEAR,5,GETDATE())

select DATEADD(MM,20,'2012-12-11')

select DATEADD(DD,15,GETDATE())

 

DateDiff(位置，日期1，日期2):两个日期对应位置之间的间隔

select DATEDIFF(YEAR,'2012-1-1','2014-1-1')

select DATEDIFF(MM,'2012-1-1',GETDATE())

select DATEDIFF(DD,'1999-1-1',GETDATE())

 

DateName(位置，日期)：取得对应位置的日期的数字

select DATENAME(YEAR,GETDATE())

select DATENAME(MONTH,GETDATE())

select DATENAME(DD,GETDATE())

 

3) 数字函数

abs(数字)：求出数字的绝对值

select ABS(100)

select ABS(-100)

 

CEILING(小数)：向上取整

select CEILING(12.1)13

 

floor(小数)：向下取整

select FLOOR(12.9)12

 

round(小数，数字)：四舍五入

如果数字为负数，往小数点左边四舍五入

如果数字为正数，往小数点右边四舍五入

如果数字为0，整数

select ROUND(10.9,0)11

select ROUND(10.3,0)10

select ROUND(10.87,1)10.9

select ROUND(17,-1)20

 

POWER(底数，指数):

select POWER(10,3)

 

sign(数字)

如果数字为正数，返回值就是1

如果数字为负数，返回值就是-1

如果数字为0，返回值就是0

select SIGN(100)1

select SIGN(-999999)-1

select SIGN(0) 0

 

sqrt(数字)：求出平方根。2的平方是4,2就是4的平方根

select SQRT(99)

 

二十三、 视图：视图不是一个真实表，是一个虚拟的表，可以通过视图查询一个或多个表的信息

1）视图中不能用来保存任何数据，只是能用来查看表的数据。对视图任何的操作，都不能修改表中的数据

 

2）视图用处：可以简化查看表中的数据，尤其是多张表之间的数据

 

3）创建视图格式（视图很像是一条SQL查询语句的结果）

create view 视图名

as

查询语句

select * from book

 

案例：创建一个视图，查询清华大学出版社和历史类所有图书的名字，出版社，作者，类型

 

create view book_view1

as

select bname,pub,author,type

from book

where pub='清华大学出版社' or

type = '历史类'

 

查询清华大学出版社和历史类所有图书的名字，出版社，作者，类型

第1种方式，我们通过SQL直接查询book表

select bname,pub,author,type from book

where pub='清华大学出版社' or type='历史类'

第2种方式，我们通过book_view1视图去查询

select * from book_view1

 

案例：创建视图book_view2

查询书名，作者，进货量，以及库存量

并且进货量要大于10000，小于25000

create view book_view2

as

select bname,author,numinput,numstore

from book

where numinput between 10000 and 25000

 

4) 修饰视图

alter view 视图名

as

查询语句

案例：修改book_view2视图，查询书名，作者，

进货量，库存量，图书类型所有的图书信息

alter view book_view2

as

select bname,author,numinput,numstore,

type

from book

select * from book_view2

 

5) 删除视图：drop view 视图名

drop view book_view2

 

二十四、 索引：是建立列上的一种数据库对象，索引中记录当前表中的数据在物理存储的位置，通过索引可以提高查询（检索）的速度

1）索引分类：索引的（物理）顺序是否与表中数据的物理顺序一致

a）聚集索引：索引的顺序与表中的数据的物理存储顺序完全一致（默认情况下，表中的数据的位置，跟主键有关），只要表中定义了主键，默认情况主键上就是聚集索引，比如：书的目录

b）非聚集索引：索引的顺序跟表中的数据的物理存储顺序不一致

c）表中主键，默认就是聚集索引，而且只有一个，查询操作根据主键，查询速度最快

  非聚集索引：可以我们来自己指定非聚集索引，

比如那些列经常作为索引的条件，就可以在这些列上，添加非聚集索引

eg：按照价格查询，按照作者查询...

 

2) 创建非聚集索引格式：

 create index 索引名 on 表名（列名）

在book表中，bname这个列，添加非聚集索引

create index book_name_index1 

on book(bname)

在bname列上添加索引以后，根据bname进行查询操作，速度会提高很多

 

3) 删除非聚集索引

drop index 表.索引名

drop index book.book_name_index1

 

4) 一个表最多可以创建250个非聚集索引（理论上，实际不可能，表中一定会有主键，一定会有一个聚集索引），所以一个表中最多创建249个非聚集索引和1个聚集索引

 

5) 索引的缺点：

a）创建索引会占用磁盘物理空间

b）建立索引虽然可以提高查询的速度，但是减慢了数据修改的速度

 

6）如果该表中的某些列，使用唯一（unique）性约束，该列上默认创建一个唯一索引

 

7）复合索引：可以用一个索引指定多个列

create index 索引名 

on 表名（列名1，列名2...）

案例：在book表上，bname，author，type指定在一个复合索引中

create index book_bat_index 

on book(bname,author,type)

 

 

 

 

 

=====================总结==================

1、 练习：创建一张表：worker

 列：id（主键），name（非空，唯一），address（非空）

插入2条数据

 

2、修改操作

 1)update 表名 set列名1 = 新的列值1，列名2 = 新的列值2….

   Where 条件

 2)多个列之间必须用“，”分隔，而且如果没有where条件，则整张表所有的数据都要进行修改

案例：修改id是1 的数据的名字，改为 祖大寿，地址 ：北京

update worker set name ='祖大寿',address = '北京' where id =1;

select name,address from worker where id = 1;

 

3、删除操作

 1)delete from 表名 where 条件

 2) where条件指定要删除的数据，如果没有where条件，删除表中所有的数据

案例：删除id为1 的数据

delete from worker where id =1 ;

select name from worker where id = 1;

 

4、查看表的结构：desc 表名

 

5、简单的查询语句

 1)select * from 表名

  eg：select * from dept

 2) select 列名1，列名2 …from 表名

  eg：select empno,ename,mgr,sal,comm from emp

      select ename,hiredate from emp;

 

3)别名

   a)select 列名1 as 别名1，列名2 as 别名2…..from 表名

eg：select empno as 编号,ename as 姓名 from emp;

   b)select 列名1 别名1，列名2 别名2…..from 表名

eg：select empno 编号,sal 工资,comm 奖金 from emp;

   c)表也可以起别名，一般给表起别名的时候，基本都是多表的连接查询（多张表连接在一起以后，会出现很多表中列的名字，重名现象）

         select 别名.列名1，别名.列名2….

from  表名 别名

eg：select e.empno,e.ename,e.mgr,e.deptno 

from  emp e;

 

4)nvl函数的使用

  案例：求出所有员工的姓名及年薪（不带奖金）

select ename,sal*12 from emp;

求出所有员工的姓名及年薪（带奖金）

select ename,sal*12+comm from emp;

 规律：任何值跟null，进行运算，返回的结果都为null

 

nvl函数：

格式：nvl（列名，数值）如果列上有数值，nvl返回的结果就是当前的列值。如果该列上列值为null，nvl返回的结果就是数值

案例: 求出所有员工的姓名及年薪（带奖金）

Eg: select ename,sal*12+nvl(comm,0) from emp;

 

5)distinct：去掉重复的列

 select distinct deptno from emp;

 

6)带where条件的查询

Commit：保存

 1)关系运算符：

案例：求出工资超过1000的员工的姓名、工资，职位

Select ename,sal,job from emp where sal>1000;

 

案例：查询职位是SALESMAN的员工的姓名，职位，上级领导编号

Select ename,job,mgr from emp where job = 'SALESMAN';?????

 

案例：查询奖金是300的员工的姓名、、编号、奖金、工资

Select ename,empno,comm,sal from emp where comm = 300;

 

2）null：为空

Is null：不为空

Is not null：

案例：查询所有奖金不为null的员工信息

select * from emp where comm is not null;

 

3)between 参数1 and 参数2：求出参数1-参数2之间

案例：查询工资在1500-3000之间所有员工的信息()

Select *from emp where sal between 1500 and 3000;

 

4)in ：

 查询编号是7566.7654.7698.7782.7839这5个员工的姓名。职位，工资

Select ename,job,sal from emp where empno in(7566,7654,7698,7782,7839);

 

5)模糊查询：（like）

 %：指代0或多个字符

_:指代任意一个字符

案例：查询名字中第2个字母是M的员工名字

select ename from emp where ename like '_M%';

 

案例：查询名字中包含LL的员的名字

select ename from emp where ename like '%LL%';

 

6)and，or，not

案例：员工工资超过3000，并且奖金为null的员工信息的名字、工资、奖金

select ename,sal,comm from emp where sal>3000 and comm is null;

 

案例：部门编号是10，或职位SALESMAN的员工的名字。职位、部门编号

select ename,job,deptno from emp where deptno = 10 or job = 'SALESMAN';

 

案例：奖金不为null员工的名字、奖金

select ename,comm from emp where comm is not null;

 

7)排序：order by asc（升序）/desc（降序）

案例：查询按照员工的编号降序排列，显示员工的姓名、编号、职位

Select ename,empno ,job from emp order by empno desc;

 

案例：查询按照员工的工资升序排列，显示员工的姓名、工资

Select ename,sal from emp order by sal asc;

 

8)聚合函数：Count，sum，max，min，avg

案例：求出所有员工的平均工资

Select avg(sal) from emp;

 

案例：求出所有员工的个数以及所有员工总的工资

Select count(*) , sum(sal)from emp;

案例：求出部门编号是10的部门最高的、最低的工资

Select max(sal),min(sal) from emp where deptno = 10;

 

9)分组：group by 列名

根据某一个列把表中所有的数据分成几组

案例：求出各个部门的平均工资，最高工资以及员工的人数，部门编号

Select deptno avg(sal),max(sal),sum(empno) 

from emp group by deptno;

 

10)having：对分组以后的结果，在进行过滤

案例：各个部门的平均工资，最高工资、员工的人数、部门编号，并平均工资必须超过2000

Select deptno, avg(sal),max(sal),count(*)

From  emp

group by deptno

Having avg(sal)>2000;

 

11)连接查询（一）：

 1、连接查询：我们所需要查询的数据来源于多个表中，需要连接多个表来进行连接查询

 2、多表连接查询的格式：

    select 别名1.列名 或 别名1.*，别名2.列名 或 别名2.*

    from 表名1 别名1，表名2 别名2

    where 关联条件

 案例：查询员工的名字及员工所在的部门的名字(不加关联条件  where  )

  select e.ename,d.dname

  from emp e, dept d

 结果是48条数据（emp 12，dept 4）

 

3) 笛卡尔积：当我们进行连接查询的时候，如果没有添加关联条件，返回结果是每张表的数据条数相乘，就是笛卡尔积

 

4) 加入关联条件，就是为了去除笛卡尔积

分析：emp表，dept表之间，关联条件（外键），emp表中的deptno外键的值，跟dept中主键的值一致，

关联条件：emp.deptno = dept.deptno

案例：查询员工的名字及员工所在的部门的名字（加关联条件  where  )

Select e.ename , d.dname

From emp e,dept d

Where e.deptno = d.deptno

 

案例：查询emp表中姓名、编号、工资，及与之对应的dept表中部门编号及部门名称

Select e.ename,e.empno,e.sal ,d.deptno,d.dname

From  emp e,dept d

Where e.deptno = d.deptno;

 

案例：查询emp表中员工姓名，职位，及与之对应的dept表中部门名称及地址

Select e.ename,e.job,d.dname,d.loc

From emp e,dept d

Where e.deptno = d.deptno;

 

12)子查询：

 Emp表和dept表

案例：查询员工编号为7369的员工所在的部门的名称

Select dname from dept where deptno =(

Select deptno from emp where empno = 7369);

 

案例：部门编号是10的所有员工的姓名、职位、工资

 

Select ename ,job,sal from emp where deptno = 10;

案例：员工的工资超过平均工资的员工的姓名、职位、工资

 

 

案例：员工的工资超过30号部门最高工资的员工的姓名及工资

Select ename ,sal from emp where sal>(

Select max(sal) from emp where deptno =30);

 

 

13)连接查询（二）

企业开发中，一般针对多表的连接查询，分成3种情况，内连接，左外连接，右外连接，

1、内连接：查询出来2张表中有关联关系的数据

 格式：select 别名1.*/列名 , 别名2.*/列名

       From  表名1 别名1 inner join 表名2 别名2

       On 关联条件

案例：查询emp表中员工的编号，姓名，职位及及与之对应的部门的名称

Select e.empno, e.ename,e.job,d.dname

From emp e inner join dept d

On e.deptno = d.deptno;

 

案例：查询emp表中姓名、编号、工资，及与之对应的dept表中部门编号及部门名称

Select e.ename,e.empno,e.sal,d.deptno,d.dname

From emp e inner join dept d

On e.deptno = d.deptno;

 

 

 

知识点：

1 连接查询(二)

   1）内连接：查询两张表中有关联关系的数据

       select 别名1.列名/* , 别名2.列名/* 

       from 表名1 别名1 inner join 表名2 别名2

       on 关联条件

案例：emp表的员工的姓名，职位，以及dept表中与之对应的部门的编号和地址

select e.ename,e.job,d.deptno,d.loc

from emp e inner join dept d

on e.deptno = d.deptno;

 

   2）左外连接：查询以左边的表为主（查询左边表中所有的数据，并且查询右边表中与之对应的数据）

       select  别名1.列名/* , 别名2.列名/*

       from 表名1 别名1 left join 表名2 别名2

       on 关联条件

案例：以dept表为主，查询dept表中所有的数据，及与之关联的emp表的员工的姓名，职位，工资

select d.* , e.ename,e.job,e.sal

from dept d left join emp e

on e.deptno = d.deptno;

 

案例：以dept表为主，查询dept表中部门的名称，及与之关联emp表中员工的姓名，上级领导编号，入职时间

select d.dname , e.ename,e.mgr,e.hiredate

from dept d left join emp e

on d.deptno = e.deptno;

 

  3) 右外连接：以右边的表为主（查询右边表的所有的数据），并查询左边表中与之关联的数据

select  别名1.列名/* , 别名2.列名/*

from 表名1 别名1 right join 表名2 别名2

on 关联条件

 

案例：往emp表添加2条数据

insert into emp(empno,ename) values('6666','曹操');

insert into emp(empno,ename) values('9999','徐达');

 

案例：以emp表为主，查询emp表中员工的名字，编号及与之

对应部门表的编号和地址（右外连接）

select e.ename,e.empno,d.deptno,d.loc

from dept d right join emp e

on e.deptno = d.deptno;

 

案例：以dept表为主，查询dept表中名称和地址，及与之关联的emp表中的名字，编号，职位

select d.dname , d.loc ,e.ename,e.empno,e.job

from emp e right join dept d

on d.deptno = e.deptno;

 

 4) 全连接：把两张表中所有的数据全部查询出来

select 别名1.列名/* , 别名2.列名/*

from 表名1 别名1 full join 表名2 别名2

on 关联条件

案例：查询emp表（empno,ename）和dept表(deptno,loc) 

select e.empno,e.ename,d.deptno,d.loc

from emp e full join dept d

on e.deptno = d.deptno;

 

2 事务：对表进行任何的修改操作，只有提交事务以后，表才能真正被修改，如果没有提交事务，所有操作，当前关闭窗口（dos），全部会被还原

create table person1(

  id number(4) primary key,

  name varchar2(50)

);

  1) commit:提交事务

     rollback：回滚事务（之前未提交的事务，还原回去）

  

 2) 事务边界：

     事务的结束：commit / rollback

     事务的开始：commit / rollback之后对表的修改操作，都可以看做新的事务的开始

 

  3) 案例：思考银行转账

     编号        姓名   存款

     1           刘英谦           100000000000000

     2           刘诗诗   10

     事务的开始

     编号是1的账户 - 10

     编号是2的账户 + 10

     事务的结束

     commit / rollback

 

3 序列：可以根据要求，连续产生不同的数字，经常使用序列产生的值作为表的主键的值

1）创建序列的格式：

    create sequence 序列名字

    start with 初始值

    increment by 增量

   （maxvalue 序列产生最大值 一般不指定）

 

案例：create sequence first_seq

       start with 1 

       increment by 1

 

2） nextval：创建序列以后第1次使用nextval时候，将返回

序列的初始值（start with 初始值），以后再使用nextval，将使用增量（increment by 增量）与前一个序列值相加的结果返回

     currval：返回当前序列的值（最后一次使用nextval返回的结果），使用不多

 

案例：设计一个表student

create table student(

  id  number(5) primary key,

  name varchar2(50)

);

insert into student

values(first_seq.nextval,'隆美尔');

insert into student

values(first_seq.nextval,'古德里安');

 

 

案例：设计一个序列second_seq,初始值设置1000，每次增量是2。

创建student2表，id（number(5)）,name(varchar(50)),age(number(2))

插入3条数据

create sequence second_seq

 start with 1000 

 increment by 2;

create table student2(

  id number(5) primary key,

  name varchar2(50),

  age number(2)

);

SQL> insert into student2

  2  values(second_seq.nextval,'秦琼',35);

 

已创建 1 行。

 

SQL> insert into student2

  2  values(second_seq.nextval,'长孙无忌',67);

 

已创建 1 行。

 

SQL> insert into student2

  2  values(second_seq.nextval,'李靖',56);

 

已创建 1 行。

 

4 to_date:oracle中时间的格式，不太符合我们的习惯，to_date函数专门把字符（串）转化为date类型的数据，经常应用于插入操作中（date）

1）可以直接模仿oracle时间的格式来插入date数据

SQL> insert into emp(empno,ename,hiredate)

  2  values(9111,'房玄龄','06-3月-11');

2）oracle中时间的格式：

    a）oracle中不区分大小写：mm-月份，mi-分钟

        hh-12小时制 ， hh24-24小时制

    b）年月日：yyyy-MM-dd

        年月日时（24）分秒：yyyy-MM-dd hh24:mi:ss

3）to_date('字符形式时间','时间的格式')

insert into emp(empno,ename,hiredate)

values(9222,'普京',to_date('2011-12-11 13:15:35','yyyy-MM-dd hh24:mi:ss'));

 

5 to_char:把date数据，转化为字符进行显示当前时间

1）dual：oracle中一个专门用于测试的一张表

    sysdate：当前系统的时间

    对比dual和一般表的区别

案例：select sysdate from emp;

       select sysdate from dual;

    使用dual表：简单方便

 

2）to_char(date,'时间的展现格式')

案例：select to_char(sysdate,'yyyy-MM-dd') from dual;

select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss') 

from dual;

 

6 trunc()函数（第1）

  trunc(sysdate) - 今天日期

  select trunc(sysdate) from dual;

  trunc(date,'YEAR')或trunc(date,'YYYY')

   -今年的第1天(0点)

  select trunc(sysdate,'YEAR') from dual;

  trunc(date,'MONTH')或trunc(date,'mm')

-当前月第1天(0点)

  select trunc(sysdate,'MONTH') from dual;

 

7 add _months(date,数字)：

  数字为正数：date的时间基础上，给月份加上数字

  数字为负数：date的时间基础上，给月份减去数字

  select add_months(sysdate,1) from dual;下个月

  select add_months(sysdate,-1) from dual;上个月

  函数之间也可以进行嵌套：

  下个月的第1天

  select trunc(sysdate,'MONTH') from dual;

  select trunc(add_months(sysdate,1),'MONTH') from   dual;      

  上个月的第1天

  select trunc(add_months(sysdate,-1),'MONTH') from

  dual;