|
2#
楼主 |
发表于 2018-4-12 16:22:15
|
只看该作者
- # ntile平均排序函数
- 将要排序的数据进行平分,然后按照等分排序。ntile中的参数代表分成多少等分。
- select s.id, s.name, cid, c.name,
- ntile(5) over(order by c.name) as ntile
- from student s, classes c where cid = c.id;
- Ø 集合运算
- 操作两组查询结果,进行交集、并集、减集运算
- 1、 union和union all进行并集运算
- --union 并集、不重复
- select id, name from student where name like 'ja%'
- union
- select id, name from student where id = 4;
- --并集、重复
- select * from student where name like 'ja%'
- union all
- select * from student;
- 2、 intersect进行交集运算
- --交集(相同部分)
- select * from student where name like 'ja%'
- intersect
- select * from student;
- 3、 except进行减集运算
- --减集(除相同部分)
- select * from student where name like 'ja%'
- except
- select * from student where name like 'jas%';
- Ø 公式表表达式
- 查询表的时候,有时候中间表需要重复使用,这些子查询被重复查询调用,不但效率低,而且可读性低,不利于理解。那么公式表表达式可以解决这个问题。
- 我们可以将公式表表达式(CET)视为临时结果集,在select、insert、update、delete或是create view语句的执行范围内进行定义。
- --表达式
- with statNum(id, num) as
- (
- select cid, count(*)
- from student
- where id > 0
- group by cid
- )
- select id, num from statNum order by id;
- with statNum(id, num) as
- (
- select cid, count(*)
- from student
- where id > 0
- group by cid
- )
- select max(id), avg(num) from statNum;
- Ø 连接查询
- 1、 简化连接查询
- --简化联接查询
- select s.id, s.name, c.id, c.name from student s, classes c where s.cid = c.id;
- 2、 left join左连接
- --左连接
- select s.id, s.name, c.id, c.name from student s left join classes c on s.cid = c.id;
- 3、 right join右连接
- --右连接
- select s.id, s.name, c.id, c.name from student s right join classes c on s.cid = c.id;
- 4、 inner join内连接
- --内连接
- select s.id, s.name, c.id, c.name from student s inner join classes c on s.cid = c.id;
- --inner可以省略
- select s.id, s.name, c.id, c.name from student s join classes c on s.cid = c.id;
- 5、 cross join交叉连接
- --交叉联接查询,结果是一个笛卡儿乘积
- select s.id, s.name, c.id, c.name from student s cross join classes c
- --where s.cid = c.id;
- 6、 自连接(同一张表进行连接查询)
- --自连接
- select distinct s.* from student s, student s1 where s.id <> s1.id and s.sex = s1.sex;
- Ø 函数
- 1、 聚合函数
- max最大值、min最小值、count统计、avg平均值、sum求和、var求方差
- select
- max(age) max_age,
- min(age) min_age,
- count(age) count_age,
- avg(age) avg_age,
- sum(age) sum_age,
- var(age) var_age
- from student;
- 2、 日期时间函数
- select dateAdd(day, 3, getDate());--加天
- select dateAdd(year, 3, getDate());--加年
- select dateAdd(hour, 3, getDate());--加小时
- --返回跨两个指定日期的日期边界数和时间边界数
- select dateDiff(day, '2011-06-20', getDate());
- --相差秒数
- select dateDiff(second, '2011-06-22 11:00:00', getDate());
- --相差小时数
- select dateDiff(hour, '2011-06-22 10:00:00', getDate());
- select dateName(month, getDate());--当前月份
- select dateName(minute, getDate());--当前分钟
- select dateName(weekday, getDate());--当前星期
- select datePart(month, getDate());--当前月份
- select datePart(weekday, getDate());--当前星期
- select datePart(second, getDate());--当前秒数
- select day(getDate());--返回当前日期天数
- select day('2011-06-30');--返回当前日期天数
- select month(getDate());--返回当前日期月份
- select month('2011-11-10');
- select year(getDate());--返回当前日期年份
- select year('2010-11-10');
- select getDate();--当前系统日期
- select getUTCDate();--utc日期
- 3、 数学函数
- select pi();--PI函数
- select rand(100), rand(50), rand(), rand();--随机数
- select round(rand(), 3), round(rand(100), 5);--精确小数位
- --精确位数,负数表示小数点前
- select round(123.456, 2), round(254.124, -2);
- select round(123.4567, 1, 2);
- 4、 元数据
- select col_name(object_id('student'), 1);--返回列名
- select col_name(object_id('student'), 2);
- --该列数据类型长度
- select col_length('student', col_name(object_id('student'), 2));
- --该列数据类型长度
- select col_length('student', col_name(object_id('student'), 1));
- --返回类型名称、类型id
- select type_name(type_id('varchar')), type_id('varchar');
- --返回列类型长度
- select columnProperty(object_id('student'), 'name', 'PRECISION');
- --返回列所在索引位置
- select columnProperty(object_id('student'), 'sex', 'ColumnId');
- 5、 字符串函数
- select ascii('a');--字符转换ascii值
- select ascii('A');
- select char(97);--ascii值转换字符
- select char(65);
- select nchar(65);
- select nchar(45231);
- select nchar(32993);--unicode转换字符
- select unicode('A'), unicode('中');--返回unicode编码值
- select soundex('hello'), soundex('world'), soundex('word');
- select patindex('%a', 'ta'), patindex('%ac%', 'jack'), patindex('dex%', 'dexjack');--匹配字符索引
- select 'a' + space(2) + 'b', 'c' + space(5) + 'd';--输出空格
- select charIndex('o', 'hello world');--查找索引
- select charIndex('o', 'hello world', 6);--查找索引
- select quoteName('abc[]def'), quoteName('123]45');
- --精确数字
- select str(123.456, 2), str(123.456, 3), str(123.456, 4);
- select str(123.456, 9, 2), str(123.456, 9, 3), str(123.456, 6, 1), str(123.456, 9, 6);
- select difference('hello', 'helloWorld');--比较字符串相同
- select difference('hello', 'world');
- select difference('hello', 'llo');
- select difference('hello', 'hel');
- select difference('hello', 'hello');
- select replace('abcedef', 'e', 'E');--替换字符串
- select stuff('hello world', 3, 4, 'ABC');--指定位置替换字符串
- select replicate('abc#', 3);--重复字符串
- select subString('abc', 1, 1), subString('abc', 1, 2), subString('hello Wrold', 7, 5);--截取字符串
- select len('abc');--返回长度
- select reverse('sqlServer');--反转字符串
- select left('leftString', 4);--取左边字符串
- select left('leftString', 7);
- select right('leftString', 6);--取右边字符串
- select right('leftString', 3);
- select lower('aBc'), lower('ABC');--小写
- select upper('aBc'), upper('abc');--大写
- --去掉左边空格
- select ltrim(' abc'), ltrim('# abc#'), ltrim(' abc');
- --去掉右边空格
- select rtrim(' abc '), rtrim('# abc# '), rtrim('abc');
- 6、 安全函数
- select current_user;
- select user;
- select user_id(), user_id('dbo'), user_id('public'), user_id('guest');
- select user_name(), user_name(1), user_name(0), user_name(2);
- select session_user;
- select suser_id('sa');
- select suser_sid(), suser_sid('sa'), suser_sid('sysadmin'), suser_sid('serveradmin');
- select is_member('dbo'), is_member('public');
- select suser_name(), suser_name(1), suser_name(2), suser_name(3);
- select suser_sname(), suser_sname(0x01), suser_sname(0x02), suser_sname(0x03);
- select is_srvRoleMember('sysadmin'), is_srvRoleMember('serveradmin');
- select permissions(object_id('student'));
- select system_user;
- select schema_id(), schema_id('dbo'), schema_id('guest');
- select schema_name(), schema_name(1), schema_name(2), schema_name(3);
- 7、 系统函数
- select app_name();--当前会话的应用程序名称
- select cast(2011 as datetime), cast('10' as money), cast('0' as varbinary);--类型转换
- select convert(datetime, '2011');--类型转换
- select coalesce(null, 'a'), coalesce('123', 'a');--返回其参数中第一个非空表达式
- select collationProperty('Traditional_Spanish_CS_AS_KS_WS', 'CodePage');
- select current_timestamp;--当前时间戳
- select current_user;
- select isDate(getDate()), isDate('abc'), isNumeric(1), isNumeric('a');
- select dataLength('abc');
- select host_id();
- select host_name();
- select db_name();
- select ident_current('student'), ident_current('classes');--返回主键id的最大值
- select ident_incr('student'), ident_incr('classes');--id的增量值
- select ident_seed('student'), ident_seed('classes');
- select @@identity;--最后一次自增的值
- select identity(int, 1, 1) as id into tab from student;--将studeng表的烈属,以/1自增形式创建一个tab
- select * from tab;
- select @@rowcount;--影响行数
- select @@cursor_rows;--返回连接上打开的游标的当前限定行的数目
- select @@error;--T-SQL的错误号
- select @@procid;
- 8、 配置函数
- set datefirst 7;--设置每周的第一天,表示周日
- select @@datefirst as '星期的第一天', datepart(dw, getDate()) AS '今天是星期';
- select @@dbts;--返回当前数据库唯一时间戳
- set language 'Italian';
- select @@langId as 'Language ID';--返回语言id
- select @@language as 'Language Name';--返回当前语言名称
- select @@lock_timeout;--返回当前会话的当前锁定超时设置(毫秒)
- select @@max_connections;--返回SQL Server 实例允许同时进行的最大用户连接数
- select @@MAX_PRECISION AS 'Max Precision';--返回decimal 和numeric 数据类型所用的精度级别
- select @@SERVERNAME;--SQL Server 的本地服务器的名称
- select @@SERVICENAME;--服务名
- select @@SPID;--当前会话进程id
- select @@textSize;
- select @@version;--当前数据库版本信息
- 9、 系统统计函数
- select @@CONNECTIONS;--连接数
- select @@PACK_RECEIVED;
- select @@CPU_BUSY;
- select @@PACK_SENT;
- select @@TIMETICKS;
- select @@IDLE;
- select @@TOTAL_ERRORS;
- select @@IO_BUSY;
- select @@TOTAL_READ;--读取磁盘次数
- select @@PACKET_ERRORS;--发生的网络数据包错误数
- select @@TOTAL_WRITE;--sqlserver执行的磁盘写入次数
- select patIndex('%soft%', 'microsoft SqlServer');
- select patIndex('soft%', 'software SqlServer');
- select patIndex('%soft', 'SqlServer microsoft');
- select patIndex('%so_gr%', 'Jsonisprogram');
- 10、 用户自定义函数
- # 查看当前数据库所有函数
- --查询所有已创建函数
- select definition,* from sys.sql_modules m join sys.objects o on m.object_id = o.object_id
- and type in('fn', 'if', 'tf');
- # 创建函数
复制代码
|
|