T-SQL 学习随笔

SQL 的数据定义:

操作对象           操作方式

      创建           删除         修改  

模式  CREATE SCHEMA    DROP SCHEMA

表   CREATE TABLE      DROP TABLE      ALTER TABLE

视图  CREATE VIEW      DROP VIEW    

索引  CREATE INDEX     DROP INDEX      ALTER INDEX

A1.表的创建

CREATE 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;/*集合的排序要放最后*/

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。