T-SQL 学习随笔

TABLE Student( Sno VARCHAR ( 10 ) PRIMARY KEY , Sname VARCHAR ( 10 ) NOT NULL );

 

2.向表中插入数据

 INSERT   INTO  Student(Sno,Sname)  VALUES ( ‘  1307020  ‘ , ‘  xiaohua  ‘ );

 INSERT   INTO   Student
  SELECT  ..... /*插入查询结果*/

 

3.对表进行数据更新

 UPDATE   Student
  SET  Sname  =   ‘  xiaoming  ‘ 
 WHERE  Sno  =   ‘  1307020  ‘ ;

4.删除表数据

 DELETE   FROM   Student
  WHERE  Sno  =   ‘  1307020  ‘ ;

B查询
1.最基本的查询

 SELECT   Sno 学号,Sname 姓名 /*别名*/
  FROM   Student
  WHERE  Sno  =   ‘  1307020  ‘ ;

2.查询中有重复的行可以用 DISTINCT消除，默认情况是ALL保留表中取值重复的行

 SELECT   DISTINCT   Sno,Sname
  FROM   Student
  WHERE  Sno  =   ‘  1307020  ‘ ;

3.

　　1.谓词 BETWEEN...AND... 和 NOT BETWEEN...AND...表示在范围之内

　　2.谓词 IN 可以用来查找属性属于指定集合的元祖

 /*  查找名字是xiaohua 或 xiaoming 学生的信息  */ 
 SELECT   Sno,Sname
  FROM   Student
  WHERE  Sname  IN ( ‘  xiaohua  ‘ , ‘  xiaoming  ‘ );

　　3.字符匹配 LIKE （%表示任意长度的字符串，_表示单个字符////！正则）

 /*  查找名字以xiao开头学生的信息  */ 
 SELECT   Sno,Sname
  FROM   Student
  WHERE  Sname  LIKE   ‘  xiao%  ‘ ;

　　4.ORDER BY (默认升序(ASC),降序(DESC))

 /*  查询结果按学号降序排序  */ 
 SELECT   Sno,Sname
  FROM   Student
  WHERE  Sname  LIKE   ‘  xiao%  ‘ 
 ORDER   BY  Sno  DESC ;

　　5.聚集函数

 COUNT ()  SUM ()  AVG ()  MAX ()  MIN ()

 /*  查询总人数  */ 
 SELECT   COUNT ( * );

　　6.GROUP BY 查询结果按照某一列或多列分组

 SELECT   Sno,Sname
  FROM   Student
  WHERE  Sname  LIKE   ‘  xiao%  ‘ 
 GROUP   BY  Sno,Sname;

4.连接 一个表与其他表用 ‘ = ‘ 连接
5.嵌套查询/*不想写了 = = 就是在where 或 having 后可以继续嵌套子查询 子查询中不能使用 order by */

　　1.带有 IN 谓词的子查询/**IN 是关于集合的运算，问是否在一个集合里*/

 SELECT   Sno,Sname
  FROM   Student
  WHERE  Sname  IN   (
      SELECT   Sname
      FROM   Student
      WHERE  Sname  LIKE   ‘  xiao  ‘ );

　　2.带有ANY(SOME)或ALL谓词的子查询

 SELECT   Sno,Sname
  FROM   Student
  WHERE  Sno  <   ANY   (
      SELECT   Sno
      FROM   Student
      WHERE  Sname  LIKE   ‘  xiao  ‘ );

　　3.带有EXISTS（存在）谓词的子查询

 SELECT   Sno,Sname
  FROM   Student
  WHERE   EXISTS  (
      SELECT   * 
     FROM   Student
      WHERE  Sname  LIKE   ‘  xiao  ‘ );

6.集合查询 并（UNION）、交（INTERSECT）、差（EXCEPT）

 SELECT   Sno,Sname
  FROM   Student
  WHERE  Sname  =   ‘  xiaohua  ‘ 
 UNION 
 SELECT   Sno,Sname
  FROM   Student
  WHERE  Sname  =   ‘  xiaoming  ‘ 
 ORDER   BY  Sname; /*  集合的排序要放最后  */ 

 

T-SQL 学习随笔

标签：

查看更多关于T-SQL 学习随笔的详细内容...