5 T-SQL语句基础

T-SQL语句基础

一 SQL简介

    通过编写SQL语句来维护数据库便于解决重复或者批量维护数据库难题；当数据库中出现大量的数据，并且数据库表之间的数据出现引用等状况时，或者要修改、插入、删除大量的数据时，使用SSMS将非常麻烦而不方便，并且容易出错，但是如果使用SQL语言来实现这些操作，将是非常容易的事情

1 SQL（结构化查询语言）

·关系数据库的标准语言

SQL Server、 Oracle、DB2

·非过程化语言

不需要指定对数据的存放方法

·统一的语言

创建、修改以及删除数据对象（数据库、表等）

2 T-SQL

·SQL的加强版, 提供了类似于程序语言的基本功能

变量说明、流程控制、功能函数

    ·T-SQL的组成

DML：数据操纵语言

查询、插入、删除和修改数据：SELECT、INSERT、UPDATE、DELETE

DCL：数据控制语言

建立数据库、数据库对象和定义其列：CREATE、ALTER、DROP

DDL：数据定义语言

控制数据库组件的存取许可、存取权限等：GRANT、REVOKE

二使用T-SQL操作数据库表

    1 插入数据

·INSERT 语法结构

INSERT [INTO] <表名> [列名] VALUES <值列表>

·参数解释

·[INTO]是可选的

·<表名>是必需的

·<列名>是可选的

·如果省略<列名>，<值列表>与表中字段的顺序保持一致

·多个列名和多个值列表用逗号分隔

    ·注意事项

        ·每次插入一整行数据，不可能只插入半行或者几列数据。

·数据值的数目必须与列数相同，每个数据值的数据类型、精度和小数位数也必须与相应的列匹配。

·Insert语句不能为标识符列指定值，因为它的值是自动增长的

·对于字符类型的列，当插入数据的时候，用单引号将其引起来。

·如果在设计表的时候指定某列不允许为空，则该列必须插入数据，否则将报告错误信息。

·插入的数据项要求符合检查约束的要求。

·尽管可以不指定列名，但是应养成好习惯，明确指定插入的列和对应的值

    ·示例（整数类型的值可以不用单引号，其他值需要加单引号）

        右击选择需要插入数据的表选择"编辑前200行"，然后点击左上角的"新建查询"，用鼠标拖动新建查询的标题栏选择"新建水平选项卡组"，方便编辑

选择表所在的数据库，然后在查询窗口中输入插入语句，全选后点击"执行"，便可插入数据（也可以单击"√"检查语法错误）

 2 更新数据

·UPDATE语法结构

    UPDATE <表名> SET <列名 = 更新值> [WHERE <更新条件>]

·参数解释

    SET后面可以紧随多个数据列的更新值

WHERE子句是可选的，用来限制条件

如果省略WHERE字句，表中所有数据行将被更新

    ·示例

        修改"lisi"的"age"为25和"class"为2，执行语句：

update student set age='25',class='2' where name='lisi'

3 删除数据

        1）DELETE语句(可以删除指定行和整个表记录)

·DELETE语法结构

DELETE FROM <表名> [WHERE <删除条件>]

·参数解释

    Delete…From…是删除整条记录，不会只删除单个字段，所以在Delete后不能出现字段名

WHERE子句是可选的，用来限制删除条件

如果省略WHERE字句，表中所有数据行将被删除

        ·示例

Delete from student where name='lisi'    //删除lisi这一行内容

        2）Truncate Table语句（只能删除表中的所有记录）

        ·Truncate Table语法结构

            Truncate table <表名>

        ·示例

            Truncate table student            //删除student表中所有记录

3) Delete和Truncate table区别    

·Delete：使用where子句按条件删除、数据可以恢复、不会重置标示符、可以用于含有外键约束的表

·Truncate table：只能清空整个表、数据无法恢复、重置标识符列为0、不能用于含有外键约束的表、执行速度更快，用于清空大数据量表，在执行Truncate Table前要确保数据可删除

4查询数据

    ·SQL语言中最主要、最核心的部分是它的查询功能。查询语言用来对已经存在于数据库中的数据按照特定的组合、条件表达式或次序进行检索。数据库中的查询是使用Select语句来完成的。

·T-SQL中的查询基本结构：由SELECT子句（想要查看什么）、FROM子句（从哪查看）和WHERE子句（条件）组成；

        1）Select语句

·Select语法结构：

        SELECT select_list

[INTO new_table_name]

FROM table_name

[ WHERE search_conditions ]

[GROUP BY group_by_expression] [HAVING search_conditions]

[ORDER BY order_expression [ASC|DESC] ]

·参数解释

SELECT子句：指定查询内容（列名，多个列名用逗号隔开，*号代表所有列）

INTO子句：把查询结果存放到一个新表中

FROM子句：指定查询源（表名）

WHERE子句：查询条件

GROUP BY子句：指定查询结果的分组条件

HAVING子句：指定分组搜索条件，与GROUP BY子句一起使用

ORDER BY子句：指定查询结果的排序方式

        ·表达式

         表达式是符号和运算符的一种组合，并且可以对它求值得到单个数据值。简单表达式可以是一个常数、变量、列或标量函数，可以用运算符把两个或多个简单表达式连接成一个复杂表达式

            A)条件表达式

常量：表示单个指定数据值的符号（字母、数字或符号）字母和日期数据类型的常量需要用单引号括起来，而二进制字符串和数字常量则不需要

列名：表中列的名称

一元运算符：仅有一个操作数的运算符("＋"表示正数，"－"表示负数，"~"表示补数运算符)

二元运算符：将两个操作数组合执行操作的运算符（算术运算符、赋值运算符（=）、位运算符、逻辑运算符、比较运算符、字符串串联运算符（+））

比较运算符

条件表达式举例：

    PRICE > 20，表示满足大于20的PRICE值

PRICE <> 20，表示满足不等于20的PRICE值

PRICE Between 10 And 20，表示满足大于等于10并且小于等于20的PRICE值

NAME Like '李%'，表示满足NAME姓名字段里姓李的所有姓名

通配符

通配符	解释	示例
'_'	任何单个字符	Name like '_ean'查找以ean结尾的所有名字
%	任意长度的字符	Name like '�n%'查找虽有包含ean的名字
[]	括号中所指定范围内的一个字符	Name like '[C-P]ean查找以ean结尾且开头是C与P之间任意单个字符开始的名字
[^]	不在括号中所指定范围内的任意一个字符	Name like 'car[^afg]查找以car开头且其后字母不为c、f、g的所有名字

            B)逻辑表达式

·用逻辑运算符（and、or、not）将条件连接起来，运算结果是一个逻辑值（TRUE或FALSE）

逻辑运算符

逻辑表达式举例：付款方式为信用卡，并且只能是A卡、B卡或C卡

    付款方式='信用卡'and 信用卡 in （'A卡','B卡','C卡'）

        2）查询举例

            A)查询student表中所有学生的所有信息

                Select * from student

            B)查询student表中所有学生的指定信息

                Select 姓名,班级,成绩 from student

        C)查询student表中的指定信息

            查询李四的所有信息

            Select * from student where 姓名='李四'    

            只查询student李四的成绩信息

            Select 成绩 from student where 姓名='李四'

            查询student表中成绩在90到100之间的学生所有信息

            Select * from student where 成绩 between 90 and 100

            查询student表中成绩低于90或者高于95的学生所有信息

            Select * from student where 成绩<90 or 成绩>95

            查询student表中成绩为89、90或91的学生所有信息

            Select * from student where 成绩 in (89,90,91)

            查询student表中姓刘的学生所有信息

            Select * from student where 姓名 like '刘%'

            查询student表中2班的名字叫'刘婷'的学生信息

            Select * from student where 姓名='刘婷' and 班级=2

            查询student表中备注不为空的学生所有信息

            Select * from student where 备注 is not null

        D)查询student表中前10行的信息

            Select top 10 * from student

            语法结构：SELECT TOP n select_list FROM table_name

参数解释：TOP关键字用来限制查询返回的行数，n为需要返回的行数

        E)改变查询结果的显示名称

            语法结构：SELECT column_name AS column_alias FROM table_name

参数解释：AS子句用来改变结果集列的名称（可以省略）

Select 姓名 as name,成绩 as chengji from student

    

        F)查询结果排序    

            语法结构：SELECT * FROM student ORDER BY 成绩 [ASC|DESC]

            参数解释：asc 升序，desc 降序

            Select * from student order by 成绩 asc    (可省略asc)默认按降序排列

        D)分组查询

            将表中的数据按照一定条件分类组合，再根据需要得到的统计信息；通过group by语句实现；还可以对分组结果进行筛选，只显示满足限定条件的组，使用having语句实现

            语法结构：

SELECT 列名或聚合函数 FROM student GROUP BY 列名 HAVING 条件

                参数解释："列名或聚合函数"中列名必须是group指定的列名；GROUP BY对查询结果进行分组，指定要分组的列名；HAVING指定分组搜索条件

聚合函数：用于对一组值执行计算，并返回单个值，如求和、平均值、最大或最小值等

    SUM: 求总和；只能用于数字类型的列

    AVG: 求平均值；只能用于数字类型的列

    MAX、MIN：求最大或最小值；用于数字型、字符型、日期时间等

    COUNT:求非空行数，用于数字和字符类型的列

示例

查询表中所有学生的总成绩

Select sum（成绩） as 总成绩 from student

查询表中所有学生的平均成绩

Select avg（成绩）平均成绩 from student

                查询表中所有学生中的最高分和最低分

                Select max（成绩） 最高分,min（成绩） 最低分 from student

                查询表中的行数

                Select count（*） 总行数 from student

WHERE子句和 HAVING子句的区别

·WHERE在分组之前过滤数据，条件中不能包含聚合函数，一般用于from之后

·HAVING在分组之后过滤数据，条件中经常包含聚合函数，一般用于group by之后

示例

查询表中每个班的总成绩

Select 班级,sum(成绩) as 总成绩 from student group by 班级

查询表中总成绩大于200的班级

Select 班级,sum（成绩） as 总成绩 from student group by 班级 having sum(成绩)>200

            G)经过筛选插入数据到另一新表

                语法结构：SELECT * INTO new_table_name FROM table_name

参数解释：INTO把一个表中的数据经过筛选另存到新表中，新表不存在

示例：

将A表数据插入到新表B（新表不能存在，会新建）

Select * into A from B

                将student表中2班的学生信息保存到表student-2中

                Select * into student-2 from student where 班级=2    （执行完命名后刷新）

    3）多表查询

    ·如果一个查询需要对多个表进行操作，就成为连接查询；通过各个表之间共同列的关联来查询数据

    ·表连接类型

         内连接：两个表中分别指定一列，进行对比，有相同的字段的这一行对其显示出来，否则不显示；类似于A∩B

            示例：查询两个表中名字相同的学生所有信息

Class2                                 Student        

select student.*,class2.* from student,class2 where student.姓名=class2.姓名

或

Select student.*,class.* from student inner join class on姓名=class2.姓名

外连接:根据连接查询的两个数据集合按照其出现位置的不同，可以将两个表分别定义为左侧表（左表）和右侧表（右表）

·左外连接：两个表中分别指定一列，进行对比，有相同字段的这一行与左表所有行对其显示，不相同的则显示为空;类似于A+A∩B

         示例：查询左表所有学生信息并查询右表中与左表名字相同的学生所有信息

select student.*,class2.* from student left join class2 on student.姓名=class2.姓名

                ·右外连接：两个表中分别指定一列，进行对比，有相同字段的这一行与右表所有行对其显示，不相同的则显示为空;类似于类似于A∩B+B

                     示例：查询右表所有学生信息并查询左表中与右表名字相同的学生所有信息

select student.*,class2.* from student right join class2 on student.姓名=class2.姓名

             ·完整外连接：两个表中分别指定一列，进行对比，有相同的字段的这一行对其显示出来，如果左表中没有右表匹配字段，则左表依然显示，右表则对应空值，反之亦然；即两表的信息都显示，相同的对其，一方没有的，另一方显示为空；类似于A∪B

                    示例：

查询两个表中相同