sql:查询语句大全

一、mysql

查看数据库：SHOW DATABASES;
创建数据库：CREATE DATABASE db_name;
使用数据库：USE db_name;
删除数据库：DROP DATABASE db_name;
创建表：
CREATE TABLE table_name(
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,　--id值，无符号、非空、递增——唯一性，可做主键。
name VARCHAR(60) NOT NULL,
score TINYINT UNSIGNED NOT NULL DEFAULT 0,--设置默认列值
PRIMARY KEY(id)
)ENGINE=InnoDB　　--设置表的存储引擎，一般常用InnoDB和MyISAM；InnoDB可靠，支持事务；MyISAM高效不支持全文检索

复制表：CREATE TABLE tb_name2 SELECT * FROM tb_name
创建临时表：CREATE TEMPORARY TABLE tb_name
查看表的结构：DESCRIBE tb_name
删除表：DROP TABLE IF EXISTS tb_name;
表重命名：ALTER TABLE name_old RENAME name_new;
更改表结构：ALTER TABLE tb_name ADD[CHANGE,RENAME,DROP]
实例
ALTER TABLE tb_name ADD COLUMN address varchar(80) NOT NULL
ALTER TABLE tb_name DROP address
ALTER TABLE tb_name CHANGE score score SMALLINT(4) NOT NULL;

插入数据：
INSERT INTO tb_name(id,name,score)
VALUES(NULL,'张三',140),(NULL,'张四',178),(NULL,'张五',134);
更新数据：UPDATE tb_name SET score=189 WHERE id=2;
删除数据：DELETE FROM tb_name WHERE id=3;

分组查询：HAVING 语句、group by 、order by：
SELECT * FROM tb_name GROUP BY score HAVING count(*)>2 order by id

Mysql支持REGEXP的正则表达式：
SELECT * FROM tb_name WHERE name REGEXP '^[A-D]' //找出以A-D 为开头的name
字符串连接：SELECT CONCAT(name,'=>',score) FROM tb_name
数学函数：AVG、SUM、MAX、MIN、COUNT；
文本处理函数：TRIM、LOCATE、UPPER、LOWER、SUBSTRING
日期：DATE()、CURTIME()、DAY()、YEAR()、NOW().....
select
current_timestamp --当前时间'YYYY-MM-DD HH:MM:SS'格式
,now()
,SUBDATE(now(),interval 60 second) --前一分钟
,ADDDATE(now(),interval 1 minute) --后一分钟
,ADDDATE(now(),interval 1 hour) --后一小时
,ADDDATE(now(),interval 1 day) --后一天
,UNIX_TIMESTAMP(now()) --时间戳
,DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%S')
,date_add('2018-02-17',interval -day('2018-02-17')+1 day) --当月第一天

视图：CREATE VIEW name AS SELECT * FROM tb_name WHERE ~~ ORDER BY ~~;
存储过程：
CREATE PROCEDURE pro(
IN num INT,OUT total INT)
BEGIN
SELECT SUM(score) INTO total FROM tb_name WHERE id=num;
END;
----BEGIN...END...开始和结束，这里的存储过程两个变量，一个是IN一个是OUT，这里的OUT也是需要写上的，不写会出错

调用存储过程：create procedure 存储过程名(参数)
CALL pro(13,@total)

SELECT @total　　　　--这里就可以看到结果了；

rownum实现排序显示行号
SELECT @rownum:=@rownum+1 AS rownum, Orderstate.*
FROM (SELECT @rownum:=0) r, Orderstate ;

二、mssql

大部分同mysql
查看数据库列表：
select * from sysobjects where type='u' order by name
查询上月开始结束等日期：
--当月开始结束时间
select dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120))
select dateadd(s,-1,dateadd(month,1,dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120))))

--上个月开始结束时间
select dateadd(m,-1,dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120)))
select dateadd(s,-1,dateadd(m,-0,dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120))))
--dateadd(m,-1,dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120))) and dateadd(s,-1,dateadd(d,-datepart(d,getdate())+1,convert(varchar(10),getdate(),120)))

--上周开始结束时间
select dateadd(d,(datepart(week,getdate())-1-1)7,DATEADD(wk,DATEDIFF(wk,0,DATEADD(yy, DATEDIFF(yy,0,getdate()),0)),0))
select dateadd(s,-1,dateadd(d,(datepart(week,getdate())-1-1)7,DATEADD(wk,DATEDIFF(wk,0,DATEADD(yy, DATEDIFF(yy,0,getdate()),0)),7)))

--查询上个月日期列表
select
序号=row_Number() over(order by number)
,日期=convert(varchar(10),dateadd(dd,number,convert(varchar(8),dateadd(m,-1,getdate()),120)+'01'),120)
from master..spt_values
where type='P'
and dateadd(dd,number,convert(varchar(8),dateadd(m,-1,getdate()),120)+'01')<=dateadd(s,-1,convert(varchar(8),getdate(),120)+'01')

跨服务器、数据链接：
--开启
--允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
--开启xp_cmdshell服务
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

--关闭
--允许配置高级选项
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
--禁用xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 0
GO
RECONFIGURE
GO

--在使用openrowset/opendatasource前首先要启用Ad Hoc Distributed Queries服务
--启用Ad Hoc Distributed Queries
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

--关闭Ad Hoc Distributed Queries
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

use local_datebase
go
select * from openrowset ---------数据库中无来源扩充行业表
('microsoft.Ace.oledb.12.0','excel 12.0;hdr=yes;database=D:\backup\desk\航空展导入来源.xls',Sheet2) a ---------注意工作表名接符号
---------注:HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES.
---------excel 2003：'microsoft.jet.oledb.4.0','excel 5.0
---------excel 2007：xlsx的2007版以上的为microsoft.Ace.oledb.12.0','excel 12.0
---------工作表以数字开头'select * from [3et$]'
go

链接服务器：使用OLEDB驱动程序创建SQL Server链接服务器
IF EXISTS(SELECT * FROM master..sysservers WHERE srvname='192.168.1.12')
EXEC sp_dropserver '192.168.1.12','droplogins'
EXEC sp_addlinkedserver '192.168.1.12','','SQLOLEDB','192.168.1.12'
EXEC sp_addlinkedsrvlogin '192.168.1.12','false',null,'sa','sa'
GO

row_number()实现排序显示行号:row_number() over(partition by column order by column)
不重复统计：来源不重复统计
select
来源类型=soName
,soCode
,成交量=(select count(distinct saclId) from v_SalesAuditList
where sassId=3047 and sasdId not in(1044,1047,1049,1054,1055,1058,1060,1062,1068,1070,1072,1076,1077,1079)
and saBocDate between '2014-01-01' and '2018-01-31'
--and saclId not in(select saclId from V_SalesAuditList where sasdId not in(1044,1047,1049,1054,1055,1058,1060,1062,1068,1070,1072,1076,1077,1079) and sassId=3047 and saBocDate<'2018-01-01')
and (select min(soId) from tb_Source where len(soCode)=8 and soCode in (select left((select soCode from tb_source where soId=snsoId),8) from tb_SourceNexus where sncoId=saclId))=soId
)
from tb_Source a
where len(soCode)=8
order by soCode

多行转一行：
select
产品=isnull((select STUFF((SELECT ','+waName from tb_WaySort
where exists(select 1 from tb_ProductNexus where pncoId=clId and pnwaId=waId) FOR XML PATH('')),1, 1, '') AS A
),'')
from tb_ClientMain
where clId=1000001

一行转多行：

查询方法如下：

select

a.id,b.txt

from

(select id,txt=convert(xml,'<root><v>'+replace(txt,';','</v><v>')+'</v></root>') from PkTable)a

outer apply

(select txt=C.v.value('.','nvarchar(100)') from a.txt.nodes('/root/v')C(v))b

我解释下这个sql中用到的一些语法：

（1）replace函数，是将';'替换为了'</v><v>'，又经'+'连接，经convert类型转化，表a的数据如下：


(2)在b表中，a.txt指的是a表的txt列，nodes函数的作用是根据参数中的字符串（标签）将xml转化为关系数据集，即转化为行 ，C(v)是别名，C为表明，v为列名。

通过以下语句进行查询C表的数据：
select C.v.query('.') txt
from PkTable
outer apply txt.nodes('/root/v')as C(v)

查询结果如下：

(3)C.v.value('.','nvarchar(100)')，C是表，v是列，value函数是读取标签之间的值，对于这个列子，读取的为<v>和</v>中间的值；

value的第一个参数是一个字符串文字，从 XML 实例内部检索数据。 XQuery 必须最多返回一个值。 否则，将返回错误；

value的第二个参数是指将查询结果转化为何种类型的数据。

此处，'.'表示当前目录，即<v>目录，另外'..'表示上级目录，'/'表示根目录，这个跟Linux是一样的。
通过以下语句查询b表：
select b.txt
from
(select id,txt=convert(xml,'<root><v>'+replace(txt,';','</v><v>')+'</v></root>') from PkTable)a
outer apply
(select txt=C.v.value('.','nvarchar(100)') from a.txt.nodes('/root/v')C(v))b
b表的数据如下：



（4）最后就是outer apply了，他是先定好a表的列，然后取b表的列插入。对于这个函数，最后的查询结果为：


三、python连接数据库

import pymysql
--连接mysql，获取连接的对象
dbmysql = pymysql.connect(host='localhost', port=3306, user='root', passwd='root123', db='school', charset='utf8')

with dbmysql:
--获取连接的cursor对象，用于执行查询
cur = dbmysql.cursor()
--类似于其他语言的query函数，execute是python中的执行查询函数
cur.execute("select * from tb_Student")
--使用fetchall函数，将结果集（多维元组）存入datas里面
datas = cur.fetchall()
--依次遍历结果集，发现每个元素，就是表中的一条记录，用一个元组来显示
for data in datas:
print(data)
cur.close()
dbmysql.close()

import pymssql
--连接mysql，获取连接的对象
dbmssql = pymssql.connect(host='localhost',user='sa',password='sa',database='db_tables',charset='utf8')

with dbmssql:
--获取连接的cursor对象，用于执行查询
cur = dbmssql.cursor()
--类似于其他语言的query函数，execute是python中的执行查询函数,varchar乱码转为ncarchar正常
cur.execute("select * from tb_Student")
--使用fetchall函数，将结果集（多维元组）存入datas里面
datas = cur.fetchall()
--依次遍历结果集，发现每个元素，就是表中的一条记录，用一个元组来显示
for data in datas:
print(data)
cur.close()
dbmssql.close()

import sqlite3
conn = sqlite3.connect('sqlite.db')
cur = conn.cursor()
cur.execute("select * from tb_Student")
datas = cur.fetchall()
con.close()

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 203,324评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,303评论 2赞 381
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 150,192评论 0赞 337
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,555评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,569评论 5赞 365
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,566评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,927评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,583评论 0赞 257
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,827评论 1赞 297
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,590评论 2赞 320
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,669评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,365评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,941评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,928评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,159评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,880评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,399评论 2赞 342