黑马程序员-------SQL语言基础

最新推荐文章于 2022-06-15 16:17:49 发布

原创最新推荐文章于 2022-06-15 16:17:49 发布 · 896 阅读

2 ·

CC 4.0 BY-SA版权

本文深入探讨SQL语言的基础知识，包括SQL语句的执行、数据库的创建与删除、表的操作、数据的增删改查，以及SQL常用函数与联合查询的应用。详细介绍了SQL在数据库管理中的核心作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

---------------------- <a href=" http://edu.youkuaiyun.com "target="blank">ASP.Net+Android+IOS开发</a>、

<a href="http://edu.youkuaiyun.com"target="blank">.Net培训</a>、期待与您交流！ ----------------------

一、SQL语句

（1）SQL语言简介

SQL全称是Structured Query Language（结构化查询语言）是关系型数据库管理系统的标准语言。同样的SQL语句在数据库中都可以运行。是与DBMS交谈的专用语句。

T-SQL：是对标准SQL语言的扩充

在SQL语言中，字符串用单引号，大小写不敏感

SQL语言分为数据定义语言、数据操作语言和数据库控制语言。

数据定义语言（DDL）主要是定义数据库的（例如新建数据库，新建表、修改表等）

数据操作语言（DML）主要是对数据库中的数据进行操作（例如增删改查表中的数据）

数据库控制语言（DCL）主要是控制数据库权限（例如GRANT授权、REVOKE取消权限等）

（2）执行SQL语句的注释

SQL语句的注释格式：--要注释的内容

例如

--go

--use master

（3）使用SQL语句要新建查询

（4）数据库的操作（DDL语言）

1）新建数据库例子：

create database zch1 --创建一个名为zch1的数据库

On --主数据文件信息

(

name='zch1.mdf', --主数据文件的逻辑名

filename='D:\C#练习\zch1.mdf', --文件保存的全路径名

size=3MB, --定义初始文件大小

filegrowth=2MB, --文件的自动增长大小

maxsize=10MB --文件最大值

)

log on --日志文件信息

(

name='zch1.ldf', --日志逻辑文件名

filename='D:\C#练习\zch1_log.ldf',--日志文件全路径名

size=1MB, --日志文件初始大小

filegrowth=1MB, --日志文件自动增长

maxsize=5MB --日志文件最大值

)

Create database D_name[数据库名]

这时数据库默认创建的方式。会与master数据库保存在相同的文件夹下。

还可以自己定义一些数据库的属性

2）删除数据库：

Drop Database 数据库名：

drop database zch2 --删除了数据库zch2

（5）表的操作

1）创建表

建表的格式：

Use 数据库名

Create Table 表名

（

字段名1 数据类型1 约束1，

字段名 2 数据类型 2 约束2，

...........................................

）

新建表示例：

use zch1 --使用的数据库，表示在该数据库下创建表

Go --往下执行

create table T_person --创建表 T_person是表名

(

ID int not null identity(0,2) primary key, --定义ID字段，并设定自动增长（从0开始每次增2），并设定主键，主键不能为空。

name nvarchar(3) null , --name字段，可为空，为3个字符

sex char(2) null check (sex='男' or sex='女'), --sex字段，可为空，并且设定了约束，其值只要不为空，就只能是男或者女

age int not null default 10 --age字段为int,不可以为空，如果为空就自动为默认值10

)

2）删除表

use zch2 ---要删除的表所在的数据库

drop table T_person ---要删除的表

3）修改表

修改表结构：

use zch1 ---使用zch1数据库

alter table T_person ---修个zch1中的T_person表

alter column name char(6) not null ---修改name字段的信息

给已有的表增加字段：

use zch1 ---使用zch1数据库

alter table T_person ---修改T_person表

add address char(6) not null default '北京' ---添加字段address，默认值为‘北京’（注：后添加的字段如果不设置默认值，就得为空，不然，已有的数据不能保证数据完整性）

删除表中的字段：

use zch1 ---使用zch1数据库

alter table T_person ---修改T_person表

drop column hobby ---删除字段hobby(注：要删除的字段一般没有什么数据的完整性约束)

（6）数据的增删改查（数据操作语言DML）

1）插入数据

数据插入格式：Insert into 表名（每个字段名） values （对应字段的名）；如果主键设置了标识列，就不用添加主键字段的值。

Insert into 表名 values （值）

例如：

（1）insert into [dbo].[T_person] (name, sex, age, address) values ('me','女',25,'北京');

不是所有的字段都要赋值，除了非空约束。

（2）insert into T_test (id,name,sex,age) values

(15,'zch','女',25) ,

(16,'guo','男', 25),

(13,'zch','女',25) ,

(14,'guo','男', 25)

--还可以批量添加数据

2）删除数据

1、Delete from 表名

示例： delete from T_person --删除表内所有数据：

2、Delete from 表名 where (条件)

3、Truncate 删除表数据，不能配合where，一旦使用truncate将删除所有数据。

truncate table T_test

4、Trunate与Delete的区别：

当用delete执行删除时，如果该字段的主键为自动增长，则再次输入数据是，还是接着上次数据来增加，并且可以搭配where来删除某一条记录。

而Truncate则删除所有数据，也不能搭配where，并且使用它的操作不计入日志，要慎用。

如果删除了自动增长列字段，等再次添加数据时，从头开始。

示例：delete from T_person where ID=4; --删除表内某一条数据（ID=4的数据被删除了）

3）更新数据

1、update 表名 set 列名=value ;

update T_person set name='me'; --修改所有name字段

2、Update 表名 set 列名=value where (条件)

update T_person set name='we' where id=6;--修改id=6 的name字段的值

3、字段可以累加

方法：字段=字段+值

update T_person set age=age+1

3）查询数据

（1）基本查询语句：

查询表内所有数据语法：Select * from 表名

示例：use D_test --选择要查询的表所在的数据库

select * from T_students --选择T_students表内的所有字段

有选择的查询：Select * from 表名 Where 查询条件

示例：

--选择年龄是17、18或者19岁的 id、name、age、score、gender的信息

以下几种的查询方式的结果是一样的：

1）select id, name, age, score, gender from [dbo].[T_students] where age=17 or age=18 or age=19

2）select id, name, age, score, gender from [dbo].[T_students] where age>=17 and age<=19

3）select id, name, age, score, gender from [dbo].[T_students] where age between 17 and 19

--between可以取到一定范围中间的数据

4）select id, name, age, score, gender from [dbo].[T_students] where age in (17,18,19)

（2）给指定列定义别名

语法：

1）方法一：select 字段名 as 自定义名称

示例：

select id as 编号, name as 姓名, age as 年龄, score as 成绩, gender as 性别 from [dbo].[T_students] where age in (17,18,19) --使用as关键字定义别名

2）方法二：省略as select 字段名自定义名称

示例：select id 编号, name 姓名, age 年龄, score 成绩, gender 性别 from [dbo].[T_students] where age in (17,18,19) --省略as定义别名

3）方法三：别名=字段名

示例：select 编号=id , 姓名=name , 年龄=age , 成绩=score , 性别=gender from [dbo].[T_students]

4）表的备份（只备份表结构，不会备份约束）

Select * into 新建表名 from 源表名

示例：select * into T_temp from T_person --把T_person表内的数据复制了一份到T_temp表里，但是没有主键和任何的约束信息，并且可以使用where条件，有选择性的备份数据。

--age自动加1

（3）print与select

Print 查询的内容放在“消息”中；没有查询语法。

Select 查询内容放在表中。

（4）使用top查询（一般与order by 搭配使用）

示例：select * from Score order by english desc --按照english字段降序排列

select top 10 * from Score order by english desc--按照english字段降序排列后取前十行数据

select top 10 percent * from Score order by english desc ,chinese desc,math desc

--按照排列顺序去前百分比来排列如果有多有要排序的字段，就从左到右依次排列。

（5）在检索中出去重复数据

用法：select distinct * from 表名

示例：select distinct * from score --去掉完全相同的结果

（6）聚合函数

Count()：计算个数

Max ()计算最大值

Min ()计算最小值

Sum ()求总数

Avg ()求平均数

select min(age) from T_students --求最大年龄

select max(english) from score --求english列的最大值

select count(gender) from T_students where gender='男' --计算符合gender=‘男’的个数

如果是count（*）则计算全部，如果是count(字段名）则不计入null

select avg(english) from score --计算english列的平均数（不计入null）

select sum(english) from score --english列的总数

（7）模糊查询（like or not like）

Select * from 表名 where 条件 like/not like 条件

示例：select * from T_students where name like '张%' --所有姓张的同学

通配符：% 任意匹配零或多个

_任意匹配一个字符

[]任意匹配[]中定义的一个字符

[^]不匹配[]中定义的任何字符

select * from T_students where name like '_志强' --查询所有名字叫志强的人

select * from T_students where name like '[张王]%' --查询所有姓张或姓王的人

--下面是查询既不姓张也不姓王的人，两种查询结果是一样的

select * from T_students where name not like '[张王]%' -- not like

select * from T_students where name like '[^张王]%' --[^]不匹配

（8）空值处理（Isnull函数）

如果是空值，则不用“=”来匹配（空值与空字符串是不一样的）

用is 或者 not is

示例：

select count(*) from T_test where name = null --错误的空值匹配

select count(*) from T_test where name is null --正确的空值匹配

空值的替换：

用法：select isnull(字段名，如果为空要替换的值） from 表名

select id, isnull(name,'me')姓名, sex, age from T_test --如果name字段为NULL，则用‘me’代替NULL来显示，但是要注意替换的数据类型要与该字段的数据类型一致。

（9）分组函数

Group by ：select 后面出现的值一定要是经过group by分组的值或者聚合函数

示例：select count(*) 人数, english, sum(english) 英语总分 from score group by english

分组之后可以空having进行筛选

select count(*) 人数, english, sum(english) 英语总分

from score group by english having english>90

Where 筛选与having 筛选的区别：

先执行where 条件在执行select所以是先筛选（select之前）where后不能用聚合函数，因为聚合函数是汇总之后的结果。

Having 是在经过执行分组操作之后才筛选（select之后）

（10）联合结果集（union）

如果有两个select查询语句，则执行结果有两张表，如果使用union就把两个表的结果集统计到一张表上。

语法：select 字段名 from 表一

Union

Select 字段名from 表二

注：1、但是要注意连接的列的数据类型要一致，如果不一致，需要进行类型转换，通过函数cast(字段名 as 要转成的数据类型)进行数据类型转换。

2、在使用union时自动合并重复的数据，如果要保留，则使用union all 。

3、合并的列数要一致。

示例：select max(chinese) from score

union

select min(chinese) from score

--这两句的语句的执行结果是一列两行的数据，但是如果要再加入一列，给每一行添加说明信息，要用下面的方式：

select '语文最高成绩', max(chinese) from score

union

select '语文最低成绩' , min(chinese) from score

--以上的方式，是给每行添加了说明，说明了每个数据的意义。

使用union可以实现添加多条数据

方法：insert into 表名（字段名）

Select 值(多个值中间用“，”分开) union

示例：

insert into T_test (id ,name,sex,age)

select 100,'zch','男',25 union

select 101,'guo','女',25 union

select 102,'me','女',25

（11）常用函数

（1）Datalength () ：计算字节数

select * , datalength(name) from T_students where id=5; --name的值为‘喻千凡’，结果为6，是计算字节数，因为每个汉字占两个字节

（2）Len():计算字符数

select * , len(name) from T_students where id=5; --同样的值，结果为3，是字符数

（3）lower()，upper()字符串转换大小写

select upper('abc') --执行结果为ABC

（4）ltrim（）去掉左侧空格 rtrim（）去掉右边空格 rtrim(ltrim(‘ aa ’))嵌套使用来去掉左右空格。

（5）LEFT（）从左边开始截取字符串 RIGHT（）从右边开始截取字符串

select left('abc',2) --从左侧开始截字符串，节两个，结果为‘ab’

（6）SUBSTRING()

select substring('abcdefg',1,3) --索引从1开始结果是abc

（12）Getdate()获取系统当前时间

（13）获取年份year() 获取月份month() 获取日期day()

示例：

select year( getdate() ) --获取年份

select year('2012-5-18')

（14）DATEADD（）日期增加的计算

用法：DATEADD（在那个部分增加，增加多少，日期）

示例：以这个时间为例：2014-01-12 10:04:24.813

select dateadd(year,1,‘2014-01-10 10:04:24.813’)

--在年份上加一年结果是：2015-01-12 10:04:24.813

select dateadd(month,-2,‘2014-01-10 10:04:24.813’)

--在月份上加-2，也就是倒退两个月结果是：2013-11-12 10:04:24.813

select dateadd(day,-2,‘2014-01-10 10:04:24.813’)

--在日期上加-2，也就是倒退两天，结果是：2014-01-10 10:04:24.813

（15）DATEDIFF()计算日期间隔

用法：DATEDIFF(日期单位，开始时间，结束时间)

示例：以这个时间为例：2014-01-12 10:04:24.813

select datediff(month,'1990-3-10','2014-01-12 10:04:24.813')

--计算从时间1990-3-10到2014-01-12 10:04:24.813之间的月份差

--运行结果是286

select datediff(year,'1990-3-10','2014-01-12 10:04:24.813')

--计算从时间1990-3-10到2014-01-12 10:04:24.813之间的年份差

--运行结果是24

还可以把year改成day计算一下相差的天数。

（16）DATEPART（）

用法：DATEPART（要得到的时间部分，时间）

示例：select datepart(minute,'2014-01-12 10:04:24.813')

--运行结果是4，因为，minute是分钟，还可以换成year,month,day,hour...等等

（17）CAST（）与CONVERT（）数据转换函数

用法：CAST（表达式 as 要转成的类型） CONVERT（要转成的类型，表达式）

示例：

select cast('45' as int)+convert(int,'55') --字符串转换为数字的结果：100

select '45'+'55' --字符串运算的结果：4555

Convert 的其他用法，设置日期格式：

用法：Convert（Nvarchar类型，日期类型，日期格式选择类型）

示例：

select convert(nvarchar(20) ,cast('2012/7/8' as datetime),3) --运行结果是：08/07/12

（18）ABS（）求绝对值

示例：select abs(-1)

--结果是1

（19）ROW_NUMBER（）查询介于两个结果之间的数据

示例：

select row_number() over (order by datediff(second,starttime,endtime) desc) as rownumber,datediff(second,starttime,endtime),* from calling

--row_number函数在所查询到的表中新增了一列是排序列，内容是根据over（）中的内容排序的

比较下面的两个语句用了row_number（）的函数语句:

这个是错误的：这是因为直接用row_number()语句定义的列名不可以直接为where语句使用

select row_number() over (order by datediff(second,starttime,endtime) desc) as rownumber,datediff(second,starttime,endtime),*

from calling

where rownumber>=3 and rownumber<=5

=======================================================================================

这个是正确的：因为在使用row_number()函数后，作为了一个结果集，从而这个结果集可以供另一个select语句查询，因此可以引用该结果集的列名。（应用了子查询）

select * from

(

select row_number() over(order by datediff(second,starttime,endtime) desc) as rownumber,*,datediff(second,starttime,endtime) 时长

from calling

) as table1

where rownumber>=3 and rownumber<=5

二、用T_SQL添加约束（2中方式：1、建表是添加约束2、修改表结构添加约束）

1、建表时添加约束

主键表：

create table T_add --被引用的表（主键表）

(

id int primary key identity(1,1), --主键表的主键（主键约束）并定义标识

[address] nvarchar(10)

)

外键表：

create table T_info --引用外表的表（外键表）

(

id int identity(1,1) primary key, --主键约束并定义标识

age int not null, --非空约束

sex nvarchar(1) check(sex='男' or sex='女'), --检查约束

hobby nvarchar(3) default '计算机', --默认值约束

idcard int unique, --唯一约束

[address] int foreign key references T_add(id) --外键约束

)

2、修改表结构来添加约束

（1）为表添加约束：

语法：ADD Constraint 约束名约束类型建立约束的字段名

示例： alter table T_test --修改要添加约束的表

add constraint P_K primary key(id) --把该表的id字段设置为主键

add constraint U_K unique(name) --把该表的name字段设置为unique

add constraint D_K check (age>0 and age<100) --年龄只能在0到100之间的数

add constraint DF default ('计算机') for hobby --为hobby字段添加默认值约束

（2）为表删除约束：

语法：DROP Constraint 约束名

alter table T_test --修改T_test表

drop constraint P_K --删除约束 P_K

（3）定义外键：

语法：ADD Constraint 外键名 foreign key(设置外键的字段）references 引用的表名（引用表的字段名）

示例：

alter table T_test2 --为T_test2表的字段设置外键

add constraint F_K foreign key(P_id) references T_test(id)--定义T_test1表的P_id字段是T_test表的主键。

3、查看已有的约束

select * from sys . objects
   ---------------------- <a href=" http://edu.youkuaiyun.com "target="blank">ASP.Net+Android+IOS开发</a>、
<a href=" http://edu.youkuaiyun.com "target="blank">.Net培训</a>、期待与您交流！ ----------------------