数据库操作-阿里云开发者社区

      use mytest
     
      go
     
      /* SQL有两种命令：
     
      DDL（结构定义  create   alter   drop)
     
      DML（数据操作  insert   delete  update   select)
     
      */
     
      --nchar类型与char类型完全相同，除了nchar是以Unicode格式而非ANSI格式来存储字符。
     
      --Unicode格式比ANSI格式有更大的字符集范围。ANSI字符集仅有256个字符，Unicode字符集有65536个不同的字符。
     
      --在SQL server中，Unicode数据类型要占用更多的存储空间，SQL server将为Unicode数据类型分配双倍的内部
     
      --存储空间，因此，除非数据库中的确需要以这种格式保存字符，否则应使用ANSI。
     
      --新建数据库
     
      --create database mytest
     
      --删除数据库
     
      --drop database mytest
     
      --新建表
     
      --create table SaleManager(
     
      --number varchar(10) not null,
     
      --name vachar(20) not null,
     
      --sex char(4) not null)
     
      --删除表
     
      --drop table SaleManager
     
      --修改表名
     
      --EXEC sp_rename 'SaleManager','Student'
     
      --delete from tablename where name=value
     
      --插入数据
     
      --insert into SaleManager values(1501,N'jia',N'男'),(1502,N'ze',N'男')
     
      --更改列名      注意: 更改对象名的任一部分都可能会破坏脚本和存储过程。
     
      --exec sp_rename 'SaleManager.[birthday]','Birthday ','column '
     
      --更改列类型
     
      --alter table SaleManeger alter column Birthday date
     
      --alter table PY_BNK_ID alter column WORK_STATUS CHAR(1) NULL;
     
      --增加列
     
      --alter table SaleManager add Birthday date
     
      --更新数据
     
      --update SaleManager set Birthday = '07/15/2015' where number = 1501
     
      --删除列
     
      --alter table SaleManager drop column Birthday
     
      --变量的使用
     
      --declare @number int
     
      --set @number = 2015
     
      --print @number
     
      --赋值列所有数据
     
      --update SaleManager set [Birthday ] = '02/12/2014'
     
      --使用聚合函数
     
      --select N'人员数量' = COUNT(*),
     
      --     N'平均年龄' = AVG(Age),
     
      --     N'最小年龄' = MIN(Age),
     
      --     N'最大年龄' = MAX(Age),
     
      --     N'年龄方差' = STDEV(Age)
     
      --from SaleManager
     
      --使用数学函数
     
      --select N'自然对数' = LOG(10),
     
      --     N'指数' = EXP(12),
     
      --     N'符号' = SIGN(2),
     
      --     N'圆周率' = PI(),
     
      --     N'正弦' = SIN(PI()/2.0),
     
      --     N'余弦' = COS(PI()/2.0)
     
      --使用字符串函数
     
      --select name,
     
      --     N'名字长度' = LEN(name)
     
      --from SaleManager
     
      --选取id为1和2的数据行，in可译为'有，为'
     
      --select * from Score
     
      --where id in (1,2)
     
      --通配符%替代一个或多个字符，_仅替代一个字符，[charlist]字符列中的任何一个首字符，[!charlist]
     
      --select * from Score
     
      --where name like 'j%'
     
      --where name like 'z_'
     
      --where name like '[j]%'
     
      --操作符 BETWEEN ... AND 会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
     
      --select * from Score
     
      --where name between 'jia' and 'ze'
     
      --通过使用 SQL,可以为列名称和表名称指定别名(Alias),as可省略
     
      --指定表别名,多表查询
     
      --select sc.name,sa.number
     
      --from Score as sc,SaleManager as sa
     
      --where sc.name = 'jia' and sa.number = 1501
     
      --指定列别名
     
      --select name as n,sex as s
     
      --from Score
     
      --UNION 操作符用于合并两个或多个 SELECT 语句的结果集.
     
      --默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL
     
      --select name from SaleManager
     
      --union     --(union all)
     
      --select name from Score
     
      --SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中
     
      --SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档
     
      --select * into Score1   --(in 'backup.mdb')
     
      --from Score
     
      --(where id = 01)
     
      --select子句技术:
     
      --(1)使用 DISTINCT 消除重复值;
     
      --(2)在聚合函数中使用 DISTINCT ;(select AVG(DISTINCT score) from...)
     
      --(3)使用列别名;
     
      --(4)使用 select 创建脚本;(select first_name + 'IS NULL AND' from ... where ...)
     
      --(5)字符串拼接;(select 'The ' + name + ' is  ' from ...)
     
      --(6)使用 select 创建逗号分隔的列表;
     
      --  DECLARE @Shifts varchar(20) = ''
     
      --  select @Shifts = @Shifts + name + ','
     
      --  from ...
     
      --  select @Shifts
     
      --(7)使用 INTO 子句;
     
      /*UNIQUE 约束唯一标识数据库表中的每条记录。
     
      UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
     
      PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
     
      请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
     
      例:Id_P int NOT NULL UNIQUE,
     
      */
     
      /*PRIMARY KEY 约束唯一标识数据库表中的每条记录。
     
      主键必须包含唯一的值。
     
      主键列不能包含 NULL 值。
     
      每个表应该都一个主键，并且每个表只能有一个主键。
     
      例:Id_P int NOT NULL PRIMARY KEY,
     
      */
     
      /*一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
     
      FOREIGN KEY 约束用于预防破坏表之间连接的动作。
     
      FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
     
      例:FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
     
      */
     
      /*CHECK 约束用于限制列中的值的范围。
     
      如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
     
      如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
     
      例:Id_P int NOT NULL CHECK (Id_P>0),
     
      */
     
      /*DEFAULT 约束用于向列中插入默认值。
     
      如果没有规定其他的值，那么会将默认值添加到所有的新纪录。
     
      例:City varchar(255) DEFAULT 'Sandnes'
     
      */
     
      /*Auto-increment 会在新纪录插入表中时生成一个唯一的数字。
     
      我们通常希望在每次插入新纪录时，自动地创建主键字段的值。
     
      我们可以在表中创建一个 auto-increment 字段。
     
      MS SQL 使用 IDENTITY 关键字来执行 auto-increment 任务
     
      默认地，IDENTITY 的开始值是 1，每条新纪录递增 1。
     
      要规定 "P_Id" 列以 20 起始且递增 10，请把 identity 改为 IDENTITY(20,10)
     
      下列 SQL 语句把 "Persons" 表中的 "P_Id" 列定义为 auto-increment 主键：
     
      P_Id int PRIMARY KEY IDENTITY
     
      */
     
      /*SQL Server 使用下列数据类型在数据库中存储日期或日期/时间值：
     
      DATE            - 格式: YYYY-MM-DD 
     
      DATETIME        - 格式: YYYY-MM-DD HH:MM:SS 
     
      SMALLDATETIME   - 格式: YYYY-MM-DD HH:MM:SS 
     
      TIMESTAMP       - 格式: 唯一的数字 
     
      */
     
      /*NULL 值的处理方式与其他值不同。
     
      NULL 用作未知的或不适用的值的占位符。
     
      注释：无法比较 NULL 和 0；它们是不等价的。
     
      */
     
      /*SQL索引有两种，聚集索引和非聚集索引，聚集索引存储记录是物理上连续存在，
     
      而非聚集索引是逻辑上的连续，物理存储并不连续。
     
      聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。
     
      */
     
      --创建视图
     
      --create view [FirstView] as
     
      --select name, score
     
      --from Score
     
      --(where score>80)
     
      --go
     
      --查询视图
     
      --select * from [FirstView]
     
      /*--GROUP BY 语句用于结合合计函数，根据一个或多个列对结果集进行分组。
     
      --group by子句指明了按照哪几个字段来分组，而将记录分组后，用having子句过滤这些记录；
     
      --分组统计必须有“统计函数”来配合才能使用，使用时至少需要一个分组标志字段
     
      --group by不能对别名进行分组排序
     
      --select name, AVG(score) as AveScore from Score
     
      --group by name
     
      --HAVING 子句,在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
     
      --select name, AVG(score) as AveScore from Score
     
      --where sex = '女'
     
      --group by name
     
      --having AVG(score)>80
     
      */
     
      /******************************************/
     
      --CREATE TABLE emp_pay
     
      --(
     
      -- employeeID int NOT NULL,
     
      -- base_pay money NOT NULL,
     
      -- commission decimal(2, 2) NOT NULL
     
      --)
     
      --INSERT emp_pay
     
      --   VALUES (1, 500, .10)
     
      --INSERT emp_pay 
     
      --   VALUES (2, 1000, .05)
     
      --INSERT emp_pay 
     
      --   VALUES (6, 800, .07)
     
      --INSERT emp_pay
     
      --   VALUES (5, 1500, .03)
     
      --INSERT emp_pay
     
      --   VALUES (9, 750, .06)
     
      --go
     
      --TOP关键字用来查询出开始的n行
     
      --select TOP 10 score where ... order by ...
     
      --创建唯一聚集索引
     
      --create unique clustered index employeeID_index        
     
      --on emp_pay (employeeID)
     
      --go
     
      /*
     
      索引对于select count(*)类型的查询，可以提升查询效率。
     
      索引对于select *类型的查询，没有提高查询效率。
     
      */
     
      --增加索引
     
      --alter table emp_pay add index employeeID_index (employeeID)
     
      --删除索引
     
      --DROP INDEX employeeID_index ON emp_pay
     
      --SET NOCOUNT off
     
      --使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。
     
      --当 SET NOCOUNT 为 ON 时，不返回计数（表示受 Transact-SQL 语句影响的行数）。
     
      --当 SET NOCOUNT 为 OFF 时，返回计数。
     
      --SELECT INTO 与 INSERT INTO
     
      --SELECT vale1, value2 into Table2 from Table1
     
      --Insert into Table2(field1,field2,...) select value1,value2,... from Table1
     
      --查询相同项 INTERSECT
     
      --SELECT DD_ID FROM TABLE1 INTERSECT SELECT DD_ID FROM TABLE2;
     
      --查询不同项 EXCEPT
     
      --SELECT DD_ID FROM TABLE1 EXCEPT SELECT DD_ID FROM TABLE2;
     
      /*在 SQL Server 中的合计函数:
     
      数值函数                    描述 
     
      ABS(x)                  绝对值
     
      FLOOR(x)                传回小于或等于给定数值表达式的最大整数
     
      MOD(x,y)                返回x除以y的余数
     
      PI()                    求π值
     
      POWER(x,y)              返回x的y次方的数值
     
      RAND(x)                 用人选的x做种子值得出0~1之间的随机浮点数，该函数oracle不支持
     
      字符串函数
     
      ASCII(char)             把字符串转换成ASCII
     
      CHAR(n)                 把n的ASCII转换成字符
     
      CHARINDEX(str1,str2)    返回字符串str1在str2中的起始位置
     
      CONCAT(str,str2)        字符串串联
     
      LEN(str1)               字符串长度
     
      LOWER(str1)             将字符串转换成小写
     
      UPPER(str1)             将字符串转换成大写
     
      REVERSE(str1)           字符串反转
     
      日期时间函数
     
      DATEADD(date, number)              日期加（返回指定日期date加上指定的额外日期间隔number产生的新日期）
     
      DATEDIFF(date1, date2)             返回两个指定日期的差值
     
      DATENAME()                          以字符串的形式返回日期的指定部分
     
      --PRINT DATENAME(YEAR, GETDATE()) => 2015
     
      --PRINT DATENAME(DAY, GETDATE()) => 19
     
      DAY()                               日期：天的整数
     
      --PRINT DAY(GETDATE()) => 19
     
      类型转换函数
     
      ISNULL()                如果为NULL，则转换字符串
     
      --PRINT ISNULL('A','B')    => A
     
      --PRINT ISNULL(NULL,'C') => C
     
      STR()                   将数字数据转换为字符串数据
     
      --PRINT STR(3.141592, 7, 2) => 3.14
     
      CAST()                  在连接不同的数据类型时进行数据类型转换
     
      --SELECT CAST(C# AS VARCHAR(10)) + CAST(score AS VARCHAR(5)) FROM SC
     
      CONVERT()               在sql server下实现各种时间类型的转换
     
      --PRINT CONVERT(VARCHAR(30), GETDATE(), 111) => 2015/11/19  --参数：类型，数据，格式模型
     
      统计函数
     
      AVG(column)             返回某列的行数   
     
      COUNT(column)           返回某列的行数（不包括NULL值） 
     
      COUNT(*)                返回被选行数 
     
      COUNT(DISTINCT column)  返回相异结果的数目 
     
      FIRST(column)           返回在指定的域中第一个记录的值（SQLServer2000 不支持） 
     
      LAST(column)            返回在指定的域中最后一个记录的值（SQLServer2000 不支持） 
     
      MAX(column)             返回某列的最高值 
     
      --SELECT NAME, SAL FROM TEACHER WHERE SAL = (SELECT MAX(SAL) FROM TEACHER)
     
      MIN(column)             返回某列的最低值  
     
      SUM(column)             返回某列的总和 
     
      */
     
      --选择表中前20%的行
     
      SELECT 
      TOP 
      20 PERCENT * 
      FROM 
      kbssfms..CUACCT; 
     
      /*--JOIN
     
      left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
     
      换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
     
      B表记录不足的地方均为NULL.
     
      right join和left join的结果刚好相反,是以右表(B)为基础的,A表不足的地方用NULL填充.
     
      inner join(join)只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
     
      */
     
      /*--IN / NOT IN
     
      SELECT NAME ,SAL FROM Teacher WHERE NAME IN ('张三', '李四', '王五');
     
      */
     
      /*--LIKE 通配符
     
      ? 或 _（下划线）        任何一个单一的子符
     
      * 或 %                 任意长度的字符（如果是中文，请使用两个百分号即%%）
     
      #                       0~9之间的单一数字
     
      [字符列表]              在字符列表里的任一值
     
      [!字符列表]             不在字符列表里的任一值
     
      -                       指定字符范围，两边的值分别为其上下限
     
      [^]转义符              使用关键字ESCAPE指定转义字符
     
      --限制以“黄”之外的字符开头
     
      --SELECT * FROM TEACHER WHERE NAME LIKE '[^黄]%'
     
      */
     
      /*--EXISTS 
     
      --在T1表中查找与T2表中ID字段数据相同的记录内容
     
      SELECT * FROM T1 WHERE EXISTS(SELECT * FROM T2 WHERE T1.ID = T2.ID)
     
      */
     
      /*求平均工资大于1500的教研室的平均工资
     
      SELECT RNO, AVG(SAL) FROM TEACHER GROUP BY RNO HAVING AVG(SAL) > 1500;
     
      011     1555
     
      013     1600
     
      015     1570
     
      */
     
      /*HAVING 与 WHERE 子句的区别
     
      1）与WHERE 子句不同，HAVING子句与组有关，而不是与单个的行有关
     
      2）如果指定了group by子句，那么having子句定义的搜索条件将作用于这个group by子句创建的那些组
     
      3）如果指定where子句，而没有指定group by子句，那么having子句定义的搜索条件将作用于where子句的输出，并把这个输出看做是一个组
     
      4）如果既没有指定group by子句也没有指定where子句，那么having子句定义的搜索条件将作用于from子句的输出，并把这个输出看做是一个组
     
      having子句可以脱离group by单独使用，但必须要有统计函数
     
      */
     
      /*--事务、锁
     
      事务是构成单一逻辑工作但与的操作集合，他是一个sql语句序列；
     
      通常，每个INSERT/UPDATE/DELETE命令被作为一个事务处理；
     
      按对资源的占用方式锁分两种：一种是读操作要求的共享锁，另一种是写操作要求的排他锁；
     
      按锁定的对象分为：表锁、行锁、数据库锁
     
      死锁是系统中两个或多个进程无限期地等待永远不会发生的条件,系统处于停滞状态;
     
      产生死锁的原因主要有:系统资源不足/进程裕兴推进的顺序不合适/资源分配不当等;
     
      产生死锁的四个必要条件:
     
      1)互斥条件:一个资源每次每次只能被一个进程使用
     
      2)请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放
     
      3)不可剥夺条件:进程已获得的资源,在未使用完之前,不能强行剥夺
     
      4)循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系
     
      事务提交:
     
      1)显示提交:用COMMIT语句直接完成的提交
     
      2)隐式提交:用sql语句间接完成的提交,ALTER/CONNECT/CREATE/DICONNECT/DROP/EXIT/GRANT/QUIT/REVOKE
     
      3)自动提交:若把AUTOCOMMIT设置为on,则在插入、修改、删除语句执行后，系统将自动提交
     
      --查看数据库锁定信息：
     
      1）直接按Ctrl+2
     
      2）EXEC sp_lock
     
      --将锁超时期限设置为2000毫秒
     
      SET LOCK_TIMEOUT 2000
     
      --锁定数据库的一个表
     
      SELECT * FROM CUACCT WITH (HOLDLOCK)
     
      --解锁
     
      SELECT * FROM CUACCT WITH (NOLOCK)
     
      --开始事务
     
      BEGIN TRAN [事务名]
     
      --终止事务
     
      COMMIT TRANSATION [事务名]
     
      --事务回滚
     
      ROLLBACK TRANSATION
     
      */
     
      /*--存储过程
     
      存储过程是保存起来的可以接受和返回用户提供的参数的sql语句的集合，它将常用的或很复杂的工作预先用sql语句写好，
     
      并用一个指定的名称存储起来以调用execute。
     
      ---------查询教师的工资是否低于1200元，以便决定是否加薪
     
      IF EXISTS (SELECT * FROM TEACHER WHERE SAL < 1200
     
      BEGIN 
     
      SELECT TNO,TNAME,RNO,SAL FROM TEACHER WHERE SAL < 1200] 
     
      SELECT ' 
     
      END 
     
      ---------
     
      ---------WAITFOR语句的用使用,块语句BEGIN...END
     
      BEGIN
     
      WAITFOR TIME '17:30' 
     
      SELECT GETDATE() 
     
      PRINT '该下班了' 
     
      END
     
      ---------
     
      ---------变量
     
      DECLARE @NAME VARCHAR(20), @AGE INT
     
      SET @NAME = 'JACK'
     
      SET @AGE = 18
     
      PRINT @NAME
     
      PRINT @AGE
     
      ----------
     
      /*--------全局变量
     
      @@IDENTITY  : 返回最后插入行的标识列的列值。
     
      @@ERROR  : 返回最后执行的Transact-SQL语句的错误代码。没有错误则为零。
     
      @@ROWCOUNT  : 返回受上一语句影响的行数，任何不返回行的语句将这一变量设置为0。
     
      @@DBTS  : 返回当前数据库的时间戳值必须保证数据库中时间戳的值是惟一的。
     
      */
     
      */

数据库操作

热门文章

最新文章

相关电子书