• sql & tSql 操作


    sql和t-sql语句学习 本文的部分内容从网上查找得到的,并给出了原文地址。 欠你一刀
    一 常用函数(function)

    1 数据类型转换函数

    CAST ( expression AS data_type ) -- 将某种数据类型的表达式显式转换为另一种数据类型
    CONVERT (data_type[(length)], expression [, style])-- 将某种数据类型的表达式显式转换为另一种数据类型

    2 统计函数

    AVG -- 返回组中值的平均值。空值将被忽略。
    COUNT--返回组中项目的数量。
    MAX--返回表达式的最大值。
    MIN--返回表达式的最小值。
    SUM--返回表达式中所有值的和,或只返回 DISTINCT 值。SUM 只能用于数字列。空值将被忽略。

    STDEV()
      --STDEV()函数返回表达式中所有数据的标准差
      --STDEVP()
      --STDEVP()函数返回总体标准差 

      VAR()
      --VAR()函数返回表达式中所有值的统计变异数  

      VARP()
      --VARP()函数返回总体变异数 

    3 数学函数

    (1) 取近似值函数

    SQRT( float_expression )--返回给定表达式的平方根。
    CEILING( numeric_expression )--返回大于或等于所给数字表达式的最小整数。
    FLOOR( numeric_expression )--返回小于或等于所给数字表达式的最大整数。
    ROUND(numeric_expression , length)--返回数字表达式并四舍五入为指定的长度或精度。
    SIGN( numeric_expression )--返回给定表达式的正 (+1)、零 (0) 或负 (-1) 号。
    ABS ( numeric_expression )--返回给定数字表达式的绝对值。
    PI(), 返回 PI 的常量值。
    RAND(), RAND( seed )返回 0 到1 之间的随机float 值。

    (2)三角函数

    SIN(float_expression)--返回以弧度表示的角的正弦
    COS(float_expression)--返回以弧度表示的角的余弦
    TAN(float_expression)--返回以弧度表示的角的正切
    COT(float_expression)--返回以弧度表示的角的余切

    (3)反三角函数

    ASIN(float_expression)--返回正弦是FLOAT值的以弧度表示的角
    ACOS(float_expression)--返回余弦是FLOAT值的以弧度表示的角
    ATAN(float_expression)--返回正切是FLOAT值的以弧度表示的角
    ATAN2(float_expression1,float_expression2)--返回正切是float_expression1/float_expres-sion2的以弧度表示的角
    DEGREES(numeric_expression)--当给出以弧度为单位的角度时,返回相应的以度数为单位的角度。
    RADIANS(numeric_expression)------对于在数字表达式中输入的度数值返回弧度值。
    EXP(float_expression)--返回表达式的指数值
    LOG(float_expression)--返回表达式的自然对数值
    LOG10(float_expression)--返回表达式的以10为底的对数值
    SQRT(float_expression)--返回表达式的平方根

    4 字符串函数

    ASCII ( character_expression )--返回字符表达式最左端字符的 ASCII 代码值。
    CHAR ( integer_expression )--将 int ASCII 代码转换为字符的字符串函数。
    LOWER ( character_expression )--将大写字符数据转换为小写字符数据后返回字符表达式。
    UPPER ( character_expression )--返回将小写字符数据转换为大写的字符表达式。
    STR ( float_expression [ , length [ , decimal ] ] ) --由数字数据转换来的字符数据。
    LTRIM( character_expression )--删除起始空格后返回字符表达式。
    RTRIM ( character_expression )--截断所有尾随空格后返回一个字符串。
    LEFT ( character_expression , integer_expression ) --返回从字符串左边开始指定个数的字符。
    RIGHT ( character_expression , integer_expression ) --返回字符串中从右边开始指定个数的 integer_expression 字符。
    SUBSTRING ( expression , start , length )--截取字符串
    CHARINDEX ( expression1 , expression2 [ , start_location ] ) --返回字符串中指定表达式的起始位置,没有返回0
    PATINDEX ( '%pattern%' , expression ) -- 返回指定表达式中某模式第一次出现的起始位置;如果在全部有效的文本和字符数据类型中没有找到该模式,则返回零。
    REPLICATE ( character_expression , integer_expression )-- 以指定的次数重复字符表达式。
    REVERSE ( character_expression )-- 返回字符表达式的反转。
    REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )--用第三个表达式替换第一个字符串表达式中出现的所有第二个给定字符串表达式。
    STUFF ( character_expression , start , length , character_expression )--删除指定长度的字符并在指定的起始点插入另一组字符。
    SPACE ( integer_expression )-- 返回由重复的空格组成的字符串。

    5 日期函数

      DAY()--函数返回date_expression中的日期值
      MONTH()--函数返回date_expression中的月份值
      YEAR()--函数返回date_expression中的年份值
      DATEADD( datepart , number, date )--函数返回指定日期date加上指定的额外日期间隔number产生的新日期
      DATEDIFF( datepart , startdate , enddate )--函数返回两个指定日期在datepart方面的不同之处
      DATENAME(datepart , date )------函数以字符串的形式返回日期的指定部分
    DATEPART( datepart , date )--函数以整数值的形式返回日期的指定部分
    GETDATE()------函数以DATETIME的缺省格式返回系统当前的日期和时间  

    6 系统函数

    APP_NAME()------函数返回当前执行的应用程序的名称
    COALESCE()-----函数返回众多表达式中第一个非NULL表达式的值
    COL_LENGTH ( 'table' , 'column' ) ----函数返回表中指定字段的长度值
    COL_NAME ( table_id , column_id )----返回数据库列的名称,该列具有相应的表标识号和列标识号。
    DATALENGTH()-----函数返回数据表达式的数据的实际长度
    DB_ID ( [ 'database_name' ] ) ------函数返回数据库的编号
    DB_NAME(database_id)------函数返回数据库的名称
    HOST_ID()-----函数返回服务器端计算机的名称
    HOST_NAME()-----函数返回服务器端计算机的名称
    IDENTITY ( data_type [ , seed , increment ] ) AS column_name --IDENTITY()函数只在SELECTINTO语句中使用用于插入一个identitycolumn列到新表中
    ISDATE()----函数判断所给定的表达式是否为合理日期
    ISNULL ( check_expression , replacement_value ) --函数将表达式中的NULL值用指定值替换
    ISNUMERIC()----函数判断所给定的表达式是否为合理的数值
    NEWID()----函数返回一个UNIQUEIDENTIFIER类型的数值
    NULLIF ( expression , expression )--NULLIF函数在expression1与expression2相等时返回NULL值若不相等时则返回xpression1的值


    [来源: http://stevieliu.blogchina.com/stevieliu/4720568.html]
    [参考《SQL Server联机丛书》,略有修改]


    二 SQL Server中各个系统表的作用

    sysaltfiles 主数据库 保存数据库的文件
    syscharsets 主数据库 字符集与排序顺序
    sysconfigures 主数据库 配置选项
    syscurconfigs 主数据库 当前配置选项
    sysdatabases 主数据库 服务器中的数据库
    syslanguages 主数据库 语言
    syslogins 主数据库 登陆帐号信息
    sysoledbusers 主数据库 链接服务器登陆信息
    sysprocesses 主数据库 进程
    sysremotelogins主数据库 远程登录帐号
    syscolumns 每个数据库 列
    sysconstrains 每个数据库 限制
    sysfilegroups 每个数据库 文件组
    sysfiles 每个数据库 文件
    sysforeignkeys 每个数据库 外部关键字
    sysindexs 每个数据库 索引
    sysmenbers 每个数据库 角色成员
    sysobjects 每个数据库 所有数据库对象
    syspermissions 每个数据库 权限
    systypes 每个数据库 用户定义数据类型
    sysusers 每个数据库 用户

    三 Transact_SQL

    1 语法


    语 句 功 能
    (1) 数据操作
    SELECT 从数据库表中检索数据行和列
    INSERT 向数据库表添加新数据行
    DELETE 从数据库表中删除数据行
    UPDATE 更新数据库表中的数据
    (2)数据定义
    CREATE TABLE 创建一个数据库表
    DROP TABLE 从数据库中删除表
    ALTER TABLE 修改数据库表结构
    CREATE VIEW 创建一个视图
    DROP VIEW 从数据库中删除视图
    CREATE INDEX 为数据库表创建一个索引
    DROP INDEX 从数据库中删除索引
    CREATE PROCEDURE 创建一个存储过程
    DROP PROCEDURE 从数据库中删除存储过程
    CREATE TRIGGER 创建一个触发器
    DROP TRIGGER 从数据库中删除触发器
    CREATE SCHEMA 向数据库添加一个新模式
    DROP SCHEMA 从数据库中删除一个模式
    CREATE DOMAIN 创建一个数据值域
    ALTER DOMAIN 改变域定义
    DROP DOMAIN 从数据库中删除一个域
    (3)数据控制
    GRANT 授予用户访问权限
    DENY 拒绝用户访问
    REVOKE 解除用户访问权限
    (4)事务控制
    COMMIT 结束当前事务
    ROLLBACK 中止当前事务
    SET TRANSACTION 定义当前事务数据访问特征
    (5)程序化SQL
    DECLARE 为查询设定游标
    EXPLAN 为查询描述数据访问计划
    OPEN 检索查询结果打开一个游标
    FETCH 检索一行查询结果
    CLOSE 关闭游标
    PREPARE 为动态执行准备SQL 语句
    EXECUTE 动态地执行SQL 语句
    DESCRIBE 描述准备好的查询

     (6) 局部变量

    declare @id char(10)
    --set @id = '10010001'
    select @id = '10010001'

    (7)全局变量

    ---必须以@@开头

    (8) IF ELSE

    --举例:

    declare @x int @y int @z int
    select @x = 1 @y = 2 @z=3
    if @x > @y
    print 'x > y' --打印字符串'x > y'
    else if @y > @z
    print 'y > z'
    else print 'z > y'

    (9) CASE

    --举例:

    use pangu
    update employee
    set e_wage =
    case
    when job_level = ’1’ then e_wage*1.08
    when job_level = ’2’ then e_wage*1.07
    when job_level = ’3’ then e_wage*1.06
    else e_wage*1.05
    end

    (10) WHILE CONTINUE BREAK

    --举例:

    declare @x int @y int @c int
    select @x = 1 @y=1
    while @x < 3
    begin
    print @x --打印变量x 的值
    while @y < 3
    begin
    select @c = 100*@x + @y
    print @c --打印变量c 的值
    select @y = @y + 1
    end
    select @x = @x + 1
    select @y = 1
    end

    (11) WAITFOR

    --举例:

    --例 等待1 小时2 分零3 秒后才执行SELECT 语句
    waitfor delay ’01:02:03’
    select * from employee
    --例 等到晚上11 点零8 分后才执行SELECT 语句
    waitfor time ’23:08:00’
    select * from employee

    2 常见用法举例

    (1) SELECT

    select *(列名) from table_name(表名) where column_name operator value
    ex:(宿主)
    select * from stock_information where stockid = str(nid)
    stockname = 'str_name'
    stockname like '% find this %'
    stockname like '[a-zA-Z]%' --------- ([]指定值的范围)
    stockname like '[^F-M]%' --------- (^排除指定范围)
    --------- 只能在使用like关键字的where子句中使用通配符)
    or stockpath = 'stock_path'
    or stocknumber < 1000
    and stockindex = 24
    not stocksex = 'man'
    stocknumber between 20 and 100
    stocknumber in(10,20,30)
    order by stockid desc(asc) --------- 排序,desc-降序,asc-升序
    order by 1,2 --------- by列号
    stockname = (select stockname from stock_information where stockid = 4)
    --------- 子查询
    --------- 除非能确保内层select只返回一个行的值,
    --------- 否则应在外层where子句中用一个in限定符
    select distinct column_name form table_name --------- distinct指定检索独有的列值,不重复
    select stocknumber ,"stocknumber + 10" = stocknumber + 10 from table_name
    select stockname , "stocknumber" = count(*) from table_name group by stockname
    --------- group by 将表按行分组,指定列中有相同的值
    having count(*) = 2 --------- having选定指定的组

    select *
    from table1, table2
    where table1.id *= table2.id -------- 左外部连接,table1中有的而table2中没有得以null表示
    table1.id =* table2.id -------- 右外部连接

    select stockname from table1
    union [all] ----- union合并查询结果集,all-保留重复行
    select stockname from table2

    (2) insert

    insert into table_name (Stock_name,Stock_number) value ("xxx","xxxx")
    value (select Stockname , Stocknumber from Stock_table2)---value为select语句

    (3) update

    update table_name set Stockname = "xxx" [where Stockid = 3]
    Stockname = default
    Stockname = null
    Stocknumber = Stockname + 4

    (4) delete

    delete from table_name where Stockid = 3
    truncate table_name ----------- 删除表中所有行,仍保持表的完整性
    drop table table_name --------------- 完全删除表

    (5) alter table

    alter table database.owner.table_name add column_name char(2) null ..
    sp_help table_name ---- 显示表已有特征
    create table table_name (name char(20), age smallint, lname varchar(30))
    insert into table_name select ----- 实现删除列的方法(创建新表)
    alter table table_name drop constraint Stockname_default ---- 删除Stockname的default约束

    四 MS-SQL数据库开发常用汇总

    1 按姓氏笔画排序

    Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as

    2 数据库加密

    select encrypt('原始密码')
    select pwdencrypt('原始密码')
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')
    select pwdencrypt('原始密码')
    select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同

    3 取回表中字段

    declare @list varchar(1000),@sql nvarchar(1000)
    select @list=@list+','+b.name from sysobjects a,syscolumns b where a.id=b.id and a.name='表A'
    set @sql='select '+right(@list,len(@list)-1)+' from 表A'
    exec (@sql)

    4 查看硬盘分区

    EXEC master..xp_fixeddrives

    5 比较A,B表是否相等
    if (select checksum_agg(binary_checksum(*)) from A)
    =
    (select checksum_agg(binary_checksum(*)) from B)
    print '相等'
    else
    print '不相等'

    6 杀掉所有的事件探察器进程
    DECLARE hcforeach CURSOR GLOBAL FOR SELECT 'kill '+RTRIM(spid)
    FROM master.dbo.sysprocesses
    WHERE program_name IN('SQL profiler',N'SQL 事件探查器')
    EXEC sp_msforeach_worker '?'

    7 记录搜索

    (1) 开头到N条记录

    Select Top N * From 表

    (2) N到M条记录(要有主索引ID)

    Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc

    (3)N到结尾记录

    Select Top N * From 表 Order by ID Desc

    8 如何修改数据库的名称

    sp_renamedb 'old_name', 'new_name'

    9 获取当前数据库中的所有用户表

    select Name from sysobjects where xtype='u' and status>=0

    或者:

    select * from information_schema.tables

    10 获取某一个表的所有字段

    select name from syscolumns where id=object_id('表名')

    11 查看与某一个表相关的视图、存储过程、函数

    select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'

    12 查看当前数据库中所有存储过程

    select name as 存储过程名称 from sysobjects where xtype='P'

    13 查询用户创建的所有数据库

    select * from master..sysdatabases D
    where sid not in(select sid from master..syslogins where name='sa')

    或者

    select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01

    14 查询某一个表的字段和数据类型

    select column_name,data_type from information_schema.columns
    where table_name = '表名'

    15 判断一个表是否存在

    if exists(select 1 from sysobjects where name='要判断的表名' and xtype='U')
    print '在'
    else
    print '不在'

    或者

    if objectproperty(object_id('要判断的表名'),'isusertable') is null
    print '无此表'
    else
    print '有此表'

    16 在存储过程中删除表的列

    http://blog.csdn.net/scucj/archive/2006/07/14/919525.aspx

    17 创建一个表和两个字段,并指定其中一个字段为自增的关键字

    CREATE TABLE '+ @TABLENAME + ' (tableID BigInt identity(1,1) primary key,myUserID BigInt)'

  • 相关阅读:
    SQLServer 事物与索引
    SQLServer 常见SQL笔试题之语句操作题详解
    测试思想-测试设计 测试用例设计之边界值分析方法
    测试思想-测试设计 测试用例设计之等价类划分方法
    测试思想-测试设计 测试用例设计之因果图方法
    测试思想-测试设计 测试用例设计之判定表驱动分析方法
    MySql 缓存查询原理与缓存监控 和 索引监控
    测试思想-测试设计 授客细说场景测试用例设计与实践
    产品相关 细说软件产品和业务 & 业务过程(流程) & 业务逻辑
    Postman Postman接口测试工具使用简介
  • 原文地址:https://www.cnblogs.com/senion/p/2226982.html
Copyright © 2020-2023  润新知