1. 首页
  2. 编程与技术

SQL Server,Oracle,DB2,MySql数据库常用SQL语句总结

发布于 2014-10-31 08:24:40 | 1234 次阅读 | 评论: 0 | 来源: 网友投递

这里有新鲜出炉的Mysql教程，程序狗速度看过来！

Mysql关系型数据库管理系统

MySQL是一个开放源码的小型关联式数据库管理系统，开发者为瑞典MySQL AB公司。MySQL被广泛地应用在Internet上的中小型网站中。由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，许多中小型网站为了降低网站总体拥有成本而选择了MySQL作为网站数据库。

本文为大家整理了SQL Server,Oracle,DB2,MySql数据库常用的SQL语句大全，sql语法一般是通用的，只有个别语法不能使用在其他数据库中，大家参考时注意下。


数据库表级操作

创建数据库

create table person
(
    FName varchar(20),
    FAge int,
    FRemark varchar(20),
    primary key(FName)
)

删除数据库,表

drop database dbname

drop table tablename

清空数据表

truncate tablename


基本sql语句

--查询
select * from person where FAge<18;
--删除
delete from person where FRemark='名誉总裁';
--插入
insert into person(FAge,FRemark)values(19,'新员工')
--更新
update person set FRemark='总经理'where FRemark='名誉总裁' or position='副经理';


一次插入数据

INSERT INTO T_ Person(FName,FAge,FRemark) VALUES('Jim',20,'USA');
INSERT INTO T_ Person(FName,FAge,FRemark) VALUES('Lili',22,'China');
INSERT INTO T_ Person(FName,FAge,FRemark) VALUES('XiaoWang',17,' China’);

insert 语句中的顺序可以是任意的,语句中的列的顺序不会影响插入的结果,但是values后面的字段值要和前面的字段值对应.可以忽略某些字段的插入

insert into T_person(FAge,FName)values(22,'LXF')

不指定插入的列

insert into T_person values('lurenl',23,'China')

如果不指定要插入的列,values后面的字段要按照数据库定义的字段顺序插入
非空约束对数据插入的影响.

如果对一个字段添加了非空约束,是不能向字段中插入null值的,比如T_Debt表的FAmount字段有非空约束.

insert into t_debt(FAmount,Fperson)values(null,'jim')

会报错:不能将值NULL插入列’FAmount'，表’dbo.T Debt';列不允许有空值。INSERT失败

主键对数据插入的影响

主键是在同一张表中必须是唯一的，如果在进行数据插入的时候指定的主键与表中己有
的数据重复的话则会导致违反主键约束的异常。

insert into t_debt(FNumber,FAmount,FPerson)values('1',300,'Jim')

会报错:不能在对象dbo.T Debt中插入重复键
数据的删除

删除person表里面的所有数据

delete from person;

delete删除的是表中的数据.drop删除数据的同时连表的结构都删除了

drop table person

delete执行完成之后还能查询,只是表是空的数据,drop执行完成之后再select,就会提示"数据表person不存在"

给列起别名


别名的定义规则(列名 AS 别名)

select FName as name from person

也可以省略as

select FName name from person


按照条件过滤
SELECT FName FROM T_ Employee WHERE FSalary<5000

聚合(最)

MAX查询年龄大于25岁的员工的最高工资

select Max(FSalary) from person where FAge>25


也可以为聚合函数的结构取别名

SELECT  MAX(FSa工ary) as MAX-SALARY FROM T_ Emp工oyee
WHERE FAge>25


AVG求平均值

SELECT AVG(FAge) FROM  T_ Emp工oyee WHERE FSalary>3800


SUM求总数

SELECT SUM(FSa工ary) FROM  T_ Emp工oyee


MIN最小值与MAX一起使用

SELECT MIN(FSalary)，MAX(FSalary) FROM  T_ Emp工oyee


Count计算数据总条数

SELECT COUNT(*)，COUNT(FNumber)FROM  T_ Employee

 

排序Order By

order by位于select 语句的末端(asc代表升序,可以省略:desc是降序,不能省略)

SELECT * FROM  T_ Employee ORDER BY FAge ASC

按照年龄从大到小排序，如果年龄相同则按照之类的排序功能

SELECT * FROM  T_ Employee ORDER BY FAge DESC,FSalary DESC


ORDER BY子句完全可以与WHERE子句一起使用(ORDER BY子句要放到WHERE子句之后，不能颠倒它们的顺序)

SELECT  * FROM FAge>23 BY FAge T_ Emp工oyee WHERE ORDER by FAge DESC,FSalary DESC

 

通配符过滤

like,_
_(单个通配符)

SELECT * FROM T_ Employee WHERE FName LIKE '_erry'

以任意字符开头，剩余部分为“erry"
要检索长度为4、第3个字符为“n ",其他字符为任意字符

SELECT * FROM T_ Employee WHERE FName LIKE，'__n_'

%(多字通配符)

SELECT * FROM T_ Employee WHERE FName LIKE’T%’

以“T”开头，长度任意。
姓名中包含字母"n”的员工信息

SELECT * FROM T_ Employee WHERE FName LIKE’%n%’

单字符匹配和多字符匹配还可以一起使用。

SELECT * FROM T_ Employee WHERE FName LIKE’%n_'

上面表示:最后一个字符为任意字符、倒数第_个字符为“n " ,长度任意的字符串。
集合匹配[](表示匹配集合中的任意一个)(只有MSSQLServer支持)

SELECT * FROM T_Employee WHERE FName LIKE '[SJ]%'

上面代表:以“S”或者“J“开头长度，长度任意
否定符"^"(用来对集合取反)(只有MSSQLServer支持)

SELECT * FROM T_Employee WHERE FName LIKE '[^SJ]%'

上面表示:不以“S”或者“J“开头,长度任意。
is null和is not null(空值检测)

SELECT * FROM T_Employee WHERE FNAME IS NOT NULL AND FSalary <5000

上面表示:查询所有姓名已知且工资小于5000的员工信息
"!","<>","not"(反义运算符)

SELECT * FROM T_Employee WHERE FAge<>22 AND FSALARY !=2000

上面表示:所有年龄不等于22岁并且工资不等于2000元”.(!=和<>)

SELECT * FROM T_Employee WHERE NOT(FAge=22) AND NOT(FSALARY<2000)

上面表示:检索所有年龄不等于22岁并且工资不小于2000元(not)
where...or...和in(),(多值运算)

SELECT FAge,FNumber,FName FROM T_Employee WHERE FAge=21 OR FAge=22 OR FAge=25;
SELECT FAge,FNumber,FName FROM T_Employee WHERE FAge IN (21,22,25)

上面两句结果完全一样,都是要把21,22,25的所有信息查询出来
范围值查询(between...and...)

SELECT * FROM T_Employee WHERE FAGE>=23 AND FAGE <=27;
SELECT * FROM T_Employee WHERE FAGE BETWEEN 23 AND 27

上面结果完全一样,检索所有年龄介于23岁到27岁之间的员工信息


group by(分组)

SELECT FAge FROM T_Employee WHERE FSubCompany = 'Beijing' GROUP BY FAge

GROUP BY子句必须放到SELECT语句的之后，如果SELECT语句有WHERE子句，则GROUP BY子句必须放到WHERE语句的之后。

虽然GROUP BY子句常常和聚合函数一起使用，不过GROUP BY子句并不是不能离开聚合函
数而单独使用的,GROUP BY子句中可以指定多个列，只需要将多个列的列名用逗号隔开即可。

数据分组与聚合函数

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge

上面表示:查看每个年龄段的员工的人数
count(*)是会对每个分组统计总数
having(对部分数组尽心筛选)

--这是错误的(语法错误)
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge WHERE COUNT(*)>1

上面报错是因为:聚合函数不能在where语句中使用,必须要用having字句来代替.比如下面

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*)>1

HAVING语句中也可以像WHERE语句一样使用复杂的过滤条件:检索人数为1个或者3个的年龄段

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*) =1 OR COUNT(*) =3

也可以使用IN操作符来实现上面的功能

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING COUNT(*) IN (1,3)


where 和having(使用where的时候group by要位于where之后,使用having的时候group by要位于having之前)

--这句是错的
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee HAVING COUNT(*) IN (1,3) GROUP BY FAge

需要特别注意，在HAVING语句中不能包含未分组的列名,例如

--这句是错的
SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee GROUP BY FAge HAVING FName IS NOT NULL

执行的时候数据库系统会提示类似如下的错误信息：

HAVING 子句中的列 'T_Employee.FName' 无效，因为该列没有包含在聚合函数或 GROUP BY 子句中。

需要用WHERE语句来代替HAVING，修改后的SQL语句如下：

SELECT FAge,COUNT(*) AS CountOfThisAge FROM T_Employee WHERE FName IS NOT NULL GROUP BY FAge


限制结果集行数:在进行数据检索的时候有时候需要只检索结果集中的部分行

MySql(MYSQL中提供了LIMIT关键字用来限制返回的结果集，LIMIT放在SELECT语句的最后位置)(语法:“LIMIT 首行行号，要返回的结果集的最大数目”。)

select * from person order by age desc limit 2,5;

上面表示:返回按照年龄降序的从第二行开始(行号从0开始)的最多5条记录;

SELECT * FROM T_Employee ORDER BY FSalary DESC LIMIT 0,5

上面表示:按照工资降序排列的前五条记录
MSSQLServer 2000(提 供 了 TOP 关 键 字 用来 返回 结 果 集 中的 前 N条 记录)

select top 5 * from T_Employee order by FSalary Desc

上面表示:检索工资水平排在前五位（按照工资从高到低）的员工信息.

SELECT top 3 * FROM T_Employee WHERE FNumber NOT IN(SELECT TOP 5 FNumber FROM T_Employee ORDER BY FSalary DESC)ORDER BY FSalary DESC

上面表示:

检索按照工资从高到低排序检索从第六名开始一共三个人的信息;

MSSQLServer2005(容几乎所有的MSSQLServer2000的语法,另外提供ROW_NUMBER()帮助更好的限制结果集行数的功能)(ROW_NUMBER()函数可以计算每一行数据在结果集中的行号（从1开始计数）)<语法:

ROW_NUMBER OVER(排序规则)

select ROW_NUMBER() OVER(order by FSalary),FNumber,FName,FSalary,FAge from T_Employee

会输出如下图

如果想得到第3行到第5行的数据:下面的是错的

--代码是错的
SELECT ROW_NUMBER() OVER(ORDER BY FSalaryDESC),FNumber,FName,FSalary,FAge FROM T_Employee WHERE (ROW_NUMBER() OVER(ORDER BY FSalary DESC))>=3 AND (ROW_NUMBER() OVER(ORDER BY FSalary DESC))<=5

运行时会报错:开窗函数只能出现在 SELECT 或 ORDER BY 子句中::也就是说ROW_NUMBER()不能用在WHERE语句中.

select * from (select row_number()over(order by fsalary desc)as rownum,fname,fname,fsalary,fage from t_employee) as a where a.rownum>=3 and a.rownum<=5

上面的结果就是想要的结果
Oracle(Oracle中支持窗口函数ROW_NUMBER()，其用法和MSSQLServer2005中相同)(orace中定义别名的时候不能用as)

select * from(select row_number() over(order by fsalary desc) row_num,fnumber,fname,fsalary,fage from t_employee) a where a.row_num>=3 and a.row_num<=5

不过oracle提供了更方便的特性,用来计算行号,oracle为每个结果集都默认增加了一个默认的表示行号的列,这个列的名称为rownum（从1开始计数）

SELECT * FROM T_Employee WHERE rownum<=6 ORDER BY FSalary Desc

上面表示:按工资从高到底排序的前6名员工的信息;

当进行检索的时候，对于第一条数据，其rownum为1，因为符合“WHERE rownum<=6”
所以被放到了检索结果中；当检索到第二条数据的时候，其rownum为2，因为符合“WHERE
rownum<=6”所以被放到了检索结果中……依次类推，直到第七行。

---这是错误的代码
SELECT rownum,FNumber,FName,FSalary,FAge FROM T_Employee WHERE rownum BETWEEN 3 AND 5 ORDER BY FSalary DESC

当进行检索的时候，对于第一条数据，其rownum为1，因为不符合“WHERE rownum
BETWEEN 3 AND 5”，所以没有被放到了检索结果中；当检索到第二条数据的时候，因为第
一条数据没有放到结果集中，所以第二条数据的rownum仍然为1，而不是我们想像的2，所以因
为不符合“WHERE rownum<=6”，没有被放到了检索结果中；当检索到第三条数据的时候，
因为第一、二条数据没有放到结果集中，所以第三条数据的rownum仍然为1，而不是我们想像的3，所以因为不符合“WHERE rownum<=6”，没有被放到了检索结果中……
DB2也支持row_number(),语法跟mssql和oracle一样,还提供了fetch关键字用来提取前N行..语法为:fetch first 条数 rows only

select * from t_employee order by fsalary desc fetch first 6 rows only
--必须注意:fetch字句要放在order by语句后面

上面表示:

按工资从高到底排序的前6名员工的信息

select * from t_employee where fnumber not in(select fnumber from t_employee order by fsalary desc fetch first 5 rows only) order by fsalary desc fetch first 3 rows only

上面表示:检索按照工资从高到低排序检索从第六名开始一共三个人的信息

数据库分页

假设页面上有首页,尾页,上一页,下一页.每页显示条数为PageSize,当前页数(从0开始)为CurrentIndex,那么只要查询从第PageSize*CurrentIndex开始的PageSize条数据,得到的就过就是当前页的数据;点击[上一页],将currentindex设置为currentindex-1;点击[下一页],将currentindex设置为currentindex+1;点击[首页],将currentindex设置为0;点击[尾页],将currentindex设置为 "总条数/PageSize"(如果有余,再加1).

抑制数据重复


当我们做数据检索的时候,如果一列的数据有重复的,这不是我们想要的,比如,我们检索公司人元列表的时候,很多部门名称是相同的,这不是我们想要的,我门想要的就是把重复的部门只留下一个,distinct关键字是用来进行重复数据抑制的最简单的功能,而且所有的数据库都支持distinct.distinct语法只需要在select之后增加distinct即可

select fdepartment from t_employee

效果是这样的

select distinct fdepartment from t_employees

加了distinct之后的效果


DISTINCT是对整个结果集进行数据重复抑制的，而不是针对每一个列

select distinct fdepartment,fsubcompany from t_employees

检索结果中不存在fdepartment和fsubcompany列都重复的数据行,但却存在fdepartment列重复的数据行,这就说明DISTINCT是对整个结果集进行数据重复抑制的

字符串的拼接

MySql中可以使用"+"来拼接两个字符串.

select '12'+'33',FAge+'1' from t_employee

执行完毕会输出

下面是解释:在 Mysql中当用+连接两个字段的时候,Mysql会尝试将字段值转换为数字类型(如果转换失败则默认字段值为0),然后进行字段的加法运算,因此,当计 算'12'+'33'的时候,mysql会将"12"和"33"这两个字符串尝试转换为数字类型的12和33,然后计算12+33的值,所以第一列是 45,同样,在计算fage+'1'的时候,由于fage本来就是数字类型,不需要转换,会尝试转换'1',然后计算fage+1作为计算列的值.

select 'abc'+'123',fage+'a' from t_employee

执行完毕不能准换成功输出

在MYSQL中进行字符串的拼接要使用CONCAT函数(CONCAT函数支持一个或者多个参数,参数类型可以为字符串类型也可以是非字符串类型,对于非字符串类型的参数mysql将尝试将其转换为字符串类型,CONCAT函数会将所有参数的顺序拼接成一个字符串做为返回值)

select concat('工号为:',fnumber,'的员工的幸福指数:',fsalary/(fage-21)) from t_employee

执行完毕会输出

concat支持只有一个参数的用法,这时的concat可以看作是一个将这个参数值尝试转换为字符串类型值的函数

MYSQL中还提供了另外一个进行字符串拼接的函数CONCAT_WS(CONCAT_WS可以在待拼接的字符串之间加入指定的分隔符，它的第一个参数值为采用的分隔符,而剩下的参数则为待拼接的字符串值)

select concat_ws(',',fnumber,fage,fdepartment,fsalary)from t_employee

执行完毕结果输出

MSSQLServer(MSSQLServer中可以直接使用加号“+”来拼接字符串)

select '工号为'+fnumber+'的员工姓名为'+fname from t_employee where fname is not null

执行完毕结果输出

Oracle(Oracle中使用“||”进行字符串拼接,方式与MSSQLServer中的"+"一样)

SELECT '工号为'||FNumber||'的员工姓名为'||FName FROM T_Employee WHERE FName IS NOT NULL

执行完毕输出结果

;
并且.除了“||”，Oracle还支持使用CONCAT()函数进行字符串拼接

SELECT CONCAT('工号:',FNumber) FROM T_Employee

执行完毕输出结果

;
如果CONCAT中连接的值不是字符串，Oracle会尝试将其转换为字符串

SELECT CONCAT('年龄:',FAge) FROM T_Employee

执行完毕输出结果


与MYSQL的CONCAT()函数不同，Oracle的CONCAT()函数只支持两个参数,否则会报参数无效,如果要进行多个字符串的拼接的话,可以使用多个concat()函数嵌套使用

SELECT CONCAT(CONCAT(CONCAT('工号为',FNumber),'的员工姓 为'),FName) FROM T_Employee WHERE FName IS NOT NULL

执行完毕输出结果

DB2(DB2中使用“||”进行字符串拼接，其使用方式和MSSQLServer中的加号“+”一样)

SELECT '工号为'||FNumber||'的员工姓名为'||FName FROM T_Employee WHERE FName IS NOT NULL

执行完毕输出结果


除了“||”，DB2还支持使用CONCAT()函数进行字符串拼接

SELECT CONCAT('工号:',FNumber) FROM T_Employee

执行完毕输出结果

与Oracle不同，如果CONCAT中连接的值不是字符串，则DB2不会尝试进行类型转换而报错;比如

--这句是错误的
SELECT CONCAT('年龄:',FAge) FROM T_Employee

运行以后DB2会报出:未找到类型为 "FUNCTION" 命名为 "CONCAT" 且具有兼容自变量的已授权例!!!!!!与MYSQL的CONCAT()函数不同，DB2的CONCAT()函数只支持两个参数.如果要进行多个字符串的 拼接的话，可以使用多个CONCAT()函数嵌套使用

SELECT CONCAT(CONCAT(CONCAT('工号为',FNumber),'的员工姓 为'),FName) FROM T_Employee WHERE FName IS NOT NULL

执行完毕输出结果



最新网友评论  共有(0)条评论 发布评论 返回顶部
推荐阅读
  • 终极炫富——2016 年 iOS 开发者收入调查报告
  • 年度大片：Stack Overflow 2017 开发者调查报告发布！
  • 开发者需要大学学位吗？
  • 程序员多大年纪算高龄，届时该何去何从？
  • 2017 年高薪编程语言排行榜，你的语言上榜没？
  • 各编程语言开发者最常使用的按键，中了没?
  • 伤不起的三十岁，干不动的程序员要何去何从？
  • 2016年中国加班最狠IT公司排行
  • 一句话"面试"漫画：黑了苹果、谷歌、三星
  • 拿代码量算 KPI ……跟程序员们来这套？
  • 程序员遇到 Bug 时的 30 个反应，你是哪一种？
  • 2016年收入最高的5个编程语言 Java和C没在前五?
  • 为什么很少见人傻钱多的程序员炫富？
  • 阿里月薪两万是什么体验？
  • 代码缩进时 程序员更喜欢空格键还是Tab键？
    • 最新资讯
  • Python获取系统所有进程PID及进程名称的方法示例
  • Python简单获取网卡名称及其IP地址的方法【基于psutil模块】
  • Python实现的根据IP地址计算子网掩码位数功能示例
  • 好的Python培训机构应该具备哪些条件
  • 详解Pytorch 使用Pytorch拟合多项式(多项式回归)
  • Python加载带有注释的Json文件实例
  • 对python的文件内注释 help注释方法
  • Python实现判断一行代码是否为注释的方法
  • Oracle数据库表中字段顺序的修改方法
  • ASP.NET MVC 4使用PagedList.Mvc分页的实现代码
  • Oracle实例启动时报错：ORA-32004的解决方法
  • 解读ASP.NET 5 & MVC6系列教程（7）：依赖注入
  • 详解IDEA用maven创建springMVC项目和配置
  • ASP.NET MVC5网站开发之展示层架构（五）
  • SpringBoot创建maven多模块项目实战代码
    • 后端技术
    前端技术
    数据库
    热门框架
    常用IDE
    其他
    Copyright © 2007-2017 PHPERZ.COM All Rights Reserved   冀ICP备14009818号  版权声明  广告服务