SQL知识整理
一:Select语句:
select 字段名 from 表名 where 条件 order by 排序
see:select distinct 从多个相同字段中抓唯一值
see:查找ld_det_andy表中有数据但ld_det_temp表中没数据的数据
select * from ld_det_andy a where
(not exists (select * from ld_det_temp where ld_loc = a.ld_loc and ld_part = a.ld_part))
see:选出累计访问量最大的10个ip地址,并按访问量降序排列。
select top 10 ip, countip from
(select ip,count(*) as countip from records group by ip) a
order by countip desc
see:表student(id,name,score)根据分数列(score)每10分为一段,查询每段分数的人数
select ScoreRank,count(*) from
(select ScoreRank = case
when score >= 0 and score < 10 then '0-9'
when score >= 10 and score < 20 then '10-19'
when score >= 20 and score < 30 then '20-29'
when score >= 30 and score < 40 then '30-39'
when score >= 40 and score < 50 then '40-49'
when score >= 50 and score < 60 then '50-59'
when score >= 60 and score < 70 then '60-69'
when score >= 70 and score < 80 then '70-79'
when score >= 80 and score < 90 then '80-89'
&nb
相关文档:
SQL位运算
select 2|8 --10
select 2|8|1 --11
select 10&8 --8,包含,10=8+2
select 10&2 --2,包含,10=2+8
select 10&4 --0,不包含
select 19&16 --16,包含,19=16+2+1
s ......
MySQL:
SELECT column from table
ORDER BY RAND()
LIMIT 1
PostgreSQL:
SELECT column from table
ORDER BY RANDOM()
LIMIT 1
Microsoft SQL Server:
SELECT TOP 1 column from table
ORDER BY NEWID()
IBM DB2
SELECT column, RAND() as IDX
from table
ORDER BY IDX FETCH FIRST 1 ROWS ONLY
Thanks Ti ......
#Region " 命名空间 "
Imports System.Data
Imports System.Data.SqlClient
#End Region
Public Class DBCommon
Implements IDisposable
#Region " 成员变量 "
Private conn As SqlConnection
Private cmd As SqlCommand
Private trans As SqlTransaction
#End Region
#Region " 构造函数 "
......
SQL宝典
SQL必知必会第三版
SQL入门经典第四版
Sams Teach Yourself SQL in 10 Minutes Third Edition
SQL The Complete Reference
......
--显示版本号,当前日期
SELECT VERSION(),CURRENT_DATE(),NOW();
--免费的计算器
SELECT (20+5)*4 AS RESULT,SIN(PI()/3);
--创建数据库
CREATE DATABASE databasename;
--删除数据库
DROP DATABASE databasename;
--显示当前存在的数据库
SHOW DATABASES;
--选择数据库
USE ......