0.前言

快到课程设计了,自己的数据库课程几乎都在摸鱼,今天花一点点时间重新整理整理,并二次学习数据库,必拿下。学习资料就是菜鸟教程 +学习通。(练习使用的数据库的创建代码在文末,使用环境为SQL Server2012)

1.SQL为何

结构化查询语言(Structured Query Language)简称SQL,是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统

结构化查询语言是高级的非过程化编程语言,允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放方式,所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。结构化查询语言语句可以嵌套,这使它具有极大的灵活性和强大的功能。[复制于百度百科]

2.常用SQL语句

2.1.一些最重要的SQL命令

  • select 从数据库中查询数据
  • update 更新数据库中的数据
  • delete 删除数据库中的数据
  • insert into 向数据库中插入新的数据
  • create datebase 创建新数据库
  • alter datebase 修改数据库
  • create table 创建新表
  • alter table 修改表
  • drop table 删除表
  • create index 创建索引
  • drop index 删除索引

[备注] 在SQL中是不区分大小写的,所以SELECTselect 没区别。

2.2.select 与 select distinct

1
2
select * from student /*会将所有的键值对*/
select name frome student /*仅仅查询name这一个建的所有值*/

如上例子,是我们最简单的select语句,但是如果一个表的某个key对应非常多重复的value,比如一个学生选了很多课程,那么我们在选课表里就会发现对应学号这个key可能出现很多重复的value,如下我们就有非常多的J0401。

db1

1
select * from SC

结果如下:

db2

select distinct可以将重复值去除,比如我想知道SC表中,都有哪些学生,我就可以这么写。

1
select distinct studno from SC

结果如下:

db3

2.3.where条件语句

1
2
3
4
5
6
7
8
9
select name,studno,sex,age from student where name = '张三' 
select name,studno,sex,age from student where age = 18
/*
对于sql中的字符串我们用单引号将其环绕,
当然许多的数据库系统也支持我们使用双引号

对于数值我们直接使用数值即可,如第二行的
age = 18
*/

上面第一条sql语句的意思就是在表student中找寻名字为张三的姓名,学号,性别的信息。

当我们使用了多个条件判断语句,我们可以使用andor,举例如下:

1
2
select name,studno,sex,age from student 
where sex = '男' and (age >= 18 or age <= 20)

上述语句我们可以查出年龄在18到20岁间的男同学的信息。

运算符 含义
= 等于
<> 不等于,在SQL的某些版本中我们也可以写成!=
> 大于
< 小于
>= 大于等于
<= 小于等于
between 在…..之间 用法:between A and B
like 搜索的某种模式
in 指定针对某个列的多个可能值
and 表示条件语句之间做与操作(A*1 = A , A * A=A , A * 0 = 0 )
or 表示条件语句之间做或操作(A +1 = 1 , A + A=A , A + 0 = A )

2.4.order by排序关键字

order by关键字用于对结果集按照一个列或者多个列进行排序

1
2
3
4
5
6
7
8
9
select studno,cno
from SC
order by studno desc,cno asc;
/*
我们可以使用desc将排序顺序设置为降序,即从大到小排序;
我们也可以使用asc将排序顺序设置为升序,即从小到大排序;
值得一提的是,当我们使用升序的时候,我们可以将此参数缺省
缺省默认为升序排序。
*/

测试结果如下:

db4

2.5.insert into 语句

1
2
3
insert into C values ('C04','计算机应用基础','2','李学成')
insert into C(课程号,课程名,学分,教师) values ('C04','计算机应用基础','2','李学成')
insert into C(课程号,课程名,预选课程号) values ('C05','网络技术','C04')

如上面代码都是正确代码,我们发现插入成绩可以有两种方式,一种是不用指定插入数据的列名的,另一种形式是需要指定插入列名的。通过对比三行,我们不难发现,如果要使用不指明数据列名的方式时候,我们需要将所有的数据列都要赋值,否则在sql server中将报错,报错信息为:列名或所提供值的数目与表定义不匹配

2.6.update更新关键字

1
2
3
update S /*update后接表名*/
set age = 18 /*重新设置的值*/
where name = '王永明'

上述语句就是将王永明同学的年龄设置为了18岁。

问题:如果我们不加where条件语句会怎么样?
回答:会将所有的人的年龄全部设置为18岁。!!!所以当我们要更新某一个人的信息的时候一定要加上where语句,否则将会将所有的人的信息一同修改。

2.7.delete删除关键字

1
2
delete from S
where sex = '男' and age = 20

上述语句可以将S表中,20岁的男生删除。如果没有where语句,将会把整个表的数据删除,表结构、属性、索引将保持不变

3.SQL进阶

3.1.select top | select limit | select rownum

select top 子句用于规定要返回的记录的个数,对于拥有数千记录的大表而言,此语句非常有用。但是不是所有的dbs都支持select top语句,MySQL中我们可以使用limit语句来选取指定条数的数据,Oracle中我们可以用rownum来选取

1
2
3
4
5
6
7
8
9
10
11
/* SQL Server*/
select top 3 * from S
select top 3 name from S

/* MySQL*/
select * from S limit 3
select name from S limit 3

/* Oracle*/
select * from S where rownum <= 3
select name from S where rownum <= 3

3.2.like

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/*此时数据库中的S表里有一个叫做陈小红的同学*/
select * from S
where name like '陈%'

select * from S
where name like '%小%'

select * from S
where name like '%红'

select * from S
where name like '小%'

select * from S
where name like '%小'

前三个select语句都可以查询出陈小红同学的信息出来,而后两个是无法查询出结果的。like语句后是写通配模式的,为了搞懂为什么后两者不行,接下来我们来了解通配符。

通配符可以用来代替字符串中的任何其他字符符号

通配符 描述
% 可以替代0个或者多个字符
_ 可以替代一个字符
[ charlist ] 字符列表中的任何单一字符,写法如[A-Z]
[ !charlist ]
[ ^charlist ]
不在字符列表中的任何单一字符
1
2
3
4
5
6
7
/*S中有 陈小红,王小明,李小昕,张小,小美*/
select * from S
where like '_小_'
-----搜索结果为 陈小红,王小明,李小昕
select * from S
where like "%小%"
-----搜索结果为 陈小红,王小明,李小昕,张小,小美

补充:在MySQL中,我们使用REGEXP或 NOT REGEXP 运算符(或 RLIKE 和 NOT RLIKE)来操作正则表达式。

3.3.in

1
2
select * from S
where name in ('李大武','陈小红')

in操作符可以使我们在where子句中规定多个值,上述操作,我们就可以吧李大武和陈小红两人的信息给查出来。

3.4.between

1
2
3
4
5
select * from S
where age between 18 and 20

select * from S
where age >= 18 and age <= 20

两者等效

3.5.as

1
2
select 姓名 as name from S --- 列的别名语法
select 姓名 from S as student --- 表的别名语法

我们不难发现,就是在想替代的值后面加as,as后跟更改后的别名

3.6.join

对于join,我们先引用一个最经典的图

sql-join

join子句基于不同表之间的共同字段,将多个表的行连接起来显示。

1
2
3
4
select a.姓名,b.课程号,b.成绩 from S a
inner join SC b
on a.学号=b.学号
---- 查询同学们所有课程的成绩

left join:从左表( from 后的表)返回所有的行,即使右表( join 后的表)中没有匹配,无匹配的值为NULL
right join:从右表返回所有的行,即使左表中没有匹配,无匹配的值为NULL
full join: 从名字也能看出来就是 left join + right join

自然连接是关系R和S在所有公共属性(common attribute)上的等接(Equijoin)。但在得到的结果中公共属性只保留一次,其余删除。而等值连接并不去掉重复的属性列,因此自然连接是特殊的等值连接。而上述几种连接方式均为自然连接。

自然连接也被称为外连接。left join 实际为 left outer join的简写。

在自然连接中被舍弃的元组叫做悬浮元组。left join 保留了左表的悬浮元组。right join保留了右表的悬浮元组。

3.7.union

1
2
3
4
5
6
7
8
9
10
11
----1 begin----
select * from S where 姓名 = '王永明'
union
select * from S where 姓名 = '陈小红'
----1 end----

----2 begin----
select * from S
union
select * from C
----2 end----

1 会将王永明和陈小红的数据放在一个结果集中显示。
2 会报错,sql server报错信息:使用 UNION、INTERSECT 或 EXCEPT 运算符合并的所有查询必须在其目标列表中有相同数目的表达式。

union操作符用于合并两个或多个 select 语句的结果集。

注意点,union 内部的每个 select 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时,每个 select 语句中的列的顺序必须相同。

1
2
3
4
5
6
7
8
9
10
11
----1 begin----
select * from S where 姓名 = '王永明'
union all
select * from S where 姓名 = '王永明'
----1 end----

----2 begin----
select * from S where 姓名 = '王永明'
union
select * from S where 姓名 = '王永明'
----2 end----

union all可以显示重复的值,但是union不能,

3.8.select into

1
2
3
4
5
6
7
8
9
10
11
12
13
14
------SQL Server
select * into newtable from oldtable

select name into newtable from oldtable
------MySQL不支持select....into语句,但是支持insert into ... select
INSERT INTO newtable
SELECT * FROM oldtable;

INSERT INTO newtable(name)
SELECT name FROM oldtable;
------同样的我们可以使用下面的方法实现功能
create table newtable
as
select * from oldtable

1 将所有的列插入到新表之中
3 将name列插入到新表之中

总结就是,select into语句是可以从一个表复制数据,然后将数据插入到另外一个新的表中。select into还可以与join一起使用,实现更加灵活的功能。

3.9.create database

1
2
3
4
create database dbname 
-------创建一个数据库
create database myDataBase
-------创建一个我的数据库

3.10.create table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
)------创建语法

CREATE TABLE S
(
学号 Char (6) ,
姓名 Char (8) ,
性别 Char (2) ,
出生日期 Datetime,
Varchar (20),
电话 Char (8),
)------创建一个S表

3.11.constraints

constraints约束语句

  • not null 指示某列不能存储 NULL 值。
  • unique 保证某列的每行必须有唯一的值。
  • primary key 主键 not null 和 unique的结合。确保某列(或两个列多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定的记录。
  • foreign key 保证一个表中的数据匹配另一个表中的值的参照完整性。
  • check 保证列中的值符合指定的条件。
  • default 规定没有给列赋值时的默认值。

接下里我们从一段sql代码中直接学习写法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
----带约束条件创建的表
create table C
(课程号 Char (3) NOT NULL PRIMARY KEY,
课程名 Varchar (20) NOT NULL,
学分 Smallint,
预选课程号 Char(3),
教师 Char(8),
CHECK(课程号 Like '[C][0-9][0-9]')
)ON [PRIMARY]

CREATE TABLE S
(
学号 Char (6) NOT NULL primary key default('JO400'),--default约束
姓名 Char (8) NOT NULL,
性别 Char (2) NOT NULL,
出生日期 Datetime NOT NULL default ('1980-01-01'),
Varchar (20) NOT NULL,
电话 Char (8),
CHECK (学号 Like '[J-Q][0-9][0-9][0-9][0-9]'),
CHECK (性别='女' OR 性别='男'),
CHECK (电话 Like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
)ON [PRIMARY]

CREATE TABLE SC (
学号 Char(6) NOT NULL,
课程号 Char(3) NOT NULL,
成绩 Smallint,
FOREIGN KEY(课程号) REFERENCES C(课程号),--定义foreign约束
FOREIGN KEY(学号) REFERENCES S(学号),
CHECK (成绩>=(0) AND 成绩<=(100) OR 成绩 IS NULL),
PRIMARY KEY(学号,课程号)
)ON [PRIMARY]

你学会了吗?Q‘w’Q_hhhhhhhhh

值得注意的地方有,一个表中,只能有一个主键,但是unique约束个数是随意的

补充一点说明:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
--------------------------------------------------------------------
--若表被创建了,我们想添加 not null 约束怎么办?
ALTER TABLE Student
MODIFY Age int NOT NULL;
--若表被创建了,我们想删除 not null 约束怎么办?
ALTER TABLE Student
MODIFY Age int NULL;
---------------------------------------------------------------------
--当表已经被创建,我们需要在studno列中创建哪一个 unique 约束
ALTER TABLE Student
ADD UNIQUE (studno)
--如需命名 unique 约束,并定义多个列的 unique 约束
ALTER TABLE Student
ADD CONSTRAINT uc_PersonID/*此为约束名*/ UNIQUE (studno,LastName)
--撤销 unique 约束
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID--SQL Server / Oracle / MS Access

ALTER TABLE Persons
DROP INDEX uc_PersonID--MySQL
---------------------------------------------------------------------
-----其他约束的情况类比上述两种

3.12.index

索引可以让数据库应用程序更快地查找数据

1
2
3
4
5
6
create index index_name
on table_name(column_name)----创建一个简单的索引,允许使用重复的值

create unique index index_name
on table_name(column_name)----在表中创建一个唯一的索引,不允许使用重复的值。
--创建索引的语法在不同的数据库中不一样。因此,我们在使用新的数据库是记得先阅读官方文档。

3.13.drop

drop index:

1
2
3
4
drop index index_name on table_name---MS Access
drop index table_name.index_name---MS SQL Server
drop index index_name---DB2/Oracle
alter table teble_name drop index index_name---MySQL

drop table:

1
drop table table_name

drop database:

1
drop database database_name

如果只是想删除表内的元素,而不想删除表本身,我们应该如何做:

1
truncate table table_name

3.14.alter

alter table 语句可以在已有的表中进行 添加、删除或修改列操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--添加表中的列
ALTER TABLE table_name
ADD column_name datatype

--删除表中的列
ALTER TABLE table_name
DROP COLUMN column_name

--改变表中列的数据类型
ALTER TABLE table_name
ALTER COLUMN column_name datatype--SQL Server / MS Access:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype--MySQL / Oracle

3.15.views

视图是可视化的表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
--创建语法
create view view_name as
select column_name(s)
from table_name
where condition
--修改语法
alter view view_name as
select column_name(s)
from table_name
where condition
--查看语法
select * from view_name
--删除语法
drop view view_name

示例:

1
2
3
4
5
6
7
8
9
10
11
12
13
create view 及格 as
select 成绩 from SC
where 成绩>=70 --创建及格视图

select * from 及格 --再可以用此查看

alter view 及格 as
select * from SC
where 成绩>=70 --修改及格视图

select * from 及格 --再可以用此查看

drop view 及格 --再删除,一切归于平静hh

3.16.datas

我们处理日期,最难的任务就是要确保插入的日期的格式,与数据库中日期列的格式相互匹配。只要我们的数据包含的只有日期部分,运行查询的时候就不会出问题。但是,如果涉及到了时间部分,情况就比较复杂了。

数据库中已经内建好了一下日期处理的函数。我们先看看SQL Server,再看看MySQL。

SQL Server Date

参数列表皆在第二个表格开始列举

函数 语法 描述
GETDAE() GETDATE() 返回当前的日期和时间
DATEPART() DATEPART(datepart,date) 返回日期/时间的单独部分
DATEADD() DATEADD(datepart,number,date) 在日期中添加或者减去指定的时间间隔
DATEDIFF() DATEDIFF(datepart,startdate,enddate) 返回两个日期之间的时间
CONVERT() CONVERT(data_type(length),expression,style) 用不同的格式来显示时间

convert函数说明

描述
data_type(length) 规定目标数据类型(带有可选的长度)。
expression 规定需要转换的值。
style 规定日期/时间的输出格式。

datepart参数可选值

datepart 缩写
yy, yyyy
季度 qq, q
mm, m
年中的日 dy, y
dd, d
wk, ww
星期 dw, w
小时 hh
分钟 mi, n
ss, s
毫秒 ms
微妙 mcs
纳秒 ns

MySQL Date函数

参数列表皆在第二个表格开始列举

函数 语法 描述
NOW() NOW() 返回当前的时间和日期
CURDATE() CURDATE() 返回当前的日期
CURTIME() CURTIME() 返回当前的时间
DATE() DATE(date|表达式) 读取日期或日期/时间表达式的日期部分
EXTRACT() EXTRACT(unit FROM date) 返回日期/时间的单独部分
DAET_ADD() DATE_ADD(date,INTERVAL expr type) 向日期添加指定的时间间隔
DATE_SUB() DATE_SUB(date,INTERVAL expr type) 向日期减去指定的时间间隔
DATEDIFF() DATEDIFF(date1,date2) 返回日期之间的天数
DATE_FORMAT() DATE_FORMAT(date,format) 指定格式显示日期/时间

在EXTRACT()中date 参数是合法的日期表达式。unit 参数可以是下列的值:

Unit 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

DATE_ADD()和DATE_SUB()函数 date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。
type 参数可以是下列值:

Type 值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

date 参数是合法的日期。format 规定日期/时间的输出格式。
可以使用的格式有:

格式 描述
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时(00-23)
%h 小时(01-12)
%I 小时(01-12)
%i 分钟,数值(00-59)
%j 年的天(001-366)
%k 小时(0-23)
%l 小时(1-12)
%M 月名
%m 月,数值(00-12)
%p AM 或 PM
%r 时间,12-小时(hh:mm:ss AM 或 PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时(hh:mm:ss)
%U 周(00-53)星期日是一周的第一天
%u 周(00-53)星期一是一周的第一天
%V 周(01-53)星期日是一周的第一天,与 %X 使用
%v 周(01-53)星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天(0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位

日期比较问题

如果不涉及时间,我们可以轻松的比较日期

1
SELECT * FROM Orders WHERE OrderDate='2008-11-11'

但若是OrderDate带有时间如: 2008-11-11 13:23:44,那么,上述的sql语句将无法查询到此时间。因为上述日期默认为:

1
SELECT * FROM Orders WHERE OrderDate='2008-11-11 00:00:00'

如果我们想要查询简单且更加容易维护,那么就不要在日期中使用时间部分。

3.17.通用数据类型

数据类型 描述
CHARACTER(n) 字符/字符串。固定长度 n。
VARCHAR(n) 或 CHARACTER VARYING(n) 字符/字符串。可变长度。最大长度 n。
BINARY(n) 二进制串。固定长度 n。
BOOLEAN 存储 TRUE 或 FALSE 值
VARBINARY(n) 或 BINARY VARYING(n) 二进制串。可变长度。最大长度 n。
INTEGER(p) 整数值(没有小数点)。精度 p。
SMALLINT 整数值(没有小数点)。精度 5。
INTEGER 整数值(没有小数点)。精度 10。
BIGINT 整数值(没有小数点)。精度 19。
DECIMAL(p,s) 精确数值,精度 p,小数点后位数 s。例如:decimal(5,2) 是一个小数点前有 3 位数,小数点后有 2 位数的数字。
NUMERIC(p,s) 精确数值,精度 p,小数点后位数 s。(与 DECIMAL 相同)
FLOAT(p) 近似数值,尾数精度 p。一个采用以 10 为基数的指数计数法的浮点数。该类型的 size 参数由一个指定最小精度的单一数字组成。
REAL 近似数值,尾数精度 7。
FLOAT 近似数值,尾数精度 16。
DOUBLE PRECISION 近似数值,尾数精度 16。
DATE 存储年、月、日的值。
TIME 存储小时、分、秒的值。
TIMESTAMP 存储年、月、日、小时、分、秒的值。
INTERVAL 由一些整数字段组成,代表一段时间,取决于区间的类型。
ARRAY 元素的固定长度的有序集合
MULTISET 元素的可变长度的无序集合
XML 存储 XML 数据

3.18.SQL Server数据类型

String 类型:

数据类型 描述 存储
char(n) 固定长度的字符串。最多 8,000 个字符。 Defined width
varchar(n) 可变长度的字符串。最多 8,000 个字符。 2 bytes + number of chars
varchar(max) 可变长度的字符串。最多 1,073,741,824 个字符。 2 bytes + number of chars
text 可变长度的字符串。最多 2GB 文本数据。 4 bytes + number of chars
nchar 固定长度的 Unicode 字符串。最多 4,000 个字符。 Defined width x 2
nvarchar 可变长度的 Unicode 字符串。最多 4,000 个字符。
nvarchar(max) 可变长度的 Unicode 字符串。最多 536,870,912 个字符。
ntext 可变长度的 Unicode 字符串。最多 2GB 文本数据。
bit 允许 0、1 或 NULL
binary(n) 固定长度的二进制字符串。最多 8,000 字节。
varbinary 可变长度的二进制字符串。最多 8,000 字节。
varbinary(max) 可变长度的二进制字符串。最多 2GB。
image 可变长度的二进制字符串。最多 2GB。

Number 类型:

数据类型 描述 存储
tinyint 允许从 0 到 255 的所有数字。 1 字节
smallint 允许介于 -32,768 与 32,767 的所有数字。 2 字节
int 允许介于 -2,147,483,648 与 2,147,483,647 的所有数字。 4 字节
bigint 允许介于 -9,223,372,036,854,775,808 与 9,223,372,036,854,775,807 之间的所有数字。 8 字节
decimal(p,s) 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 5-17 字节
numeric(p,s) 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 5-17 字节
smallmoney 介于 -214,748.3648 与 214,748.3647 之间的货币数据。 4 字节
money 介于 -922,337,203,685,477.5808 与 922,337,203,685,477.5807 之间的货币数据。 8 字节
float(n) 从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。n 参数指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节,而 float(53) 保存 8 字节。n 的默认值是 53。 4 或 8 字节
real 从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 4 字节

Date 类型:

数据类型 描述 存储
datetime 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 3.33 毫秒。 8 字节
datetime2 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 100 纳秒。 6-8 字节
smalldatetime 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日,精度为 1 分钟。 4 字节
date 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 3 bytes
time 仅存储时间。精度为 100 纳秒。 3-5 字节
datetimeoffset 与 datetime2 相同,外加时区偏移。 8-10 字节
timestamp 存储唯一的数字,每当创建或修改某行时,该数字会更新。timestamp 值基于内部时钟,不对应真实时间。每个表只能有一个 timestamp 变量。

其他数据类型:

数据类型 描述
sql_variant 存储最多 8,000 字节不同数据类型的数据,除了 text、ntext 以及 timestamp。
uniqueidentifier 存储全局唯一标识符 (GUID)。
xml 存储 XML 格式化数据。最多 2GB。
cursor 存储对用于数据库操作的指针的引用。
table 存储结果集,供稍后处理。

4.SQL函数

4.1.聚合函数(Aggregate)

  • avg()
  • count()
  • frist()
  • last()
  • max()
  • min()
  • sum()

4.2.avg()

1
2
select avg(column_name) from table_name--函数返回数值列的平均值
select avg(成绩) from SC

4.3.count()

1
2
3
4
5
6
7
8
9
10
11
--列中里非空的记录数目
SELECT COUNT(column_name) FROM table_name;
select count(学号) from SC

--返回表中的非空记录数
SELECT COUNT(*) FROM table_name;
select count(*) from SC

--返回去重后的列表里非空记录的数目
SELECT COUNT(DISTINCT column_name) FROM table_name;
select count(distinct 学号) from SC

4.4.first()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT FIRST(column_name) FROM table_name;
--返回指定列中第一个记录的值
--但是只有MS Access支持这个函数
--虽然其他数据库没有我们可以用已有的操作实现它

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;--sql server

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;--mysql

SELECT column_name FROM table_name
ORDER BY column_name ASC
WHERE ROWNUM <=1;--Oracle

4.5.last()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT LAST(column_name) FROM table_name;
--返回指定列中最后一个值
--仍然只有MS Access支持这个函数
--虽然其他数据库没有我们可以用已有的操作实现它

SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;--sql server

SELECT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1;--mysql

SELECT column_name FROM table_name
ORDER BY column_name DESC
WHERE ROWNUM <=1;--Oracle

4.6.max()

1
2
select max(column_name) from table_name
select max(age) from S

4.7.min()

1
2
select min(column_name) from table_name
select min(age) from S

4.8.sum()

1
2
3
SELECT SUM(column_name) FROM table_name;
select sum(成绩) from SC where 学号 = 'J0402'
--查询J0402这个同学的所有成绩之和

4.9.group by

对一个或多个列进行分组,并结合聚合函数使用,显示更直观的效果

1
2
3
select 姓名,性别 from S
group by 姓名,性别
---这样就可以 以性别 分别查询所有的信息

4.10.having()

在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
--having语法
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
--来点实例
select a.姓名,b.学号 from S a,SC b
where b.学号 = a.学号
group by 学号
having avg(b.成绩) >= 70
--查询平均分大于70分的同学姓名与学号
/*结果如下
李丽 J0401
马俊萍 J0402
陈小红 Q0401
张干劲 Q0403
*/

4.11.exists运算符

exists运算符用于判断查询子句是否有记录,如果有一条或多条记录存在返回 True,否则返回 False

1
2
3
4
5
6
--语法
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
--啊这个,不想去想例子了,giao

4.12.ucase()

将目标字段值转换为大写的函数,和python3中的upper很像

1
2
3
4
---sql语法
select ucase(column_name) from table_name
---用于SQL Server的语法
select upper(column_name) from table_name

4.13.lcase()

将目标字段值转换为小写的函数,和python3中的lower很像

1
2
3
4
---sql语法
select lcase(column_name) from table_name
---用于SQL Server的语法
select lower(column_name) from table_name

4.14.mid()

用于提取文本字段中提取字符

1
SELECT MID(column_name,start[,length]) FROM table_name;
参数 描述
column_name 必需。要提取字符的字段。
start 必需。规定开始位置(起始值是 1)。
length 可选。要返回的字符数。如果省略,则 MID() 函数返回剩余文本。

4.15.len()

返回文本字段中值的长度

1
SELECT LEN(column_name) FROM table_name;

4.16.round()

round()函数用于把数值字段舍入为指定的小数位数

1
SELECT ROUND(column_name,decimals) FROM TABLE_NAME;
参数 描述
column_name 必需。要舍入的字段。
decimals 可选。规定要返回的小数位数。
1
2
3
4
5
6
7
8
9
10
11
12
13
---第二个参数缺省,默认返回整数
select round(-1.23)
---结果 -1
select round(-1.58)
---结果 -2
select round(1.58)
---结果 2

---第二个参数不缺省,该如何如何
select round(-1.23,1)
---结果 -1.2
select round(1.236,2)
---结果 -1.24

5.T-SQL

5.1.变量

变量有两种形式,分别是用户自定义的局部变量和系统提供的全局变量

用户自定义的局部变量:
局部变量必须先定义后使用,被引用的时候要在其名称前加上标志@。作用范围局限于程序内部

系统提供的全局变量:
全局变量是由SQL Server服务器定义,可以直接使用。全局变量被引用的时候要加上标志@@。在任何程序范围内均起作用

注意:局部变量和系统变量不能同名

1
2
3
4
5
6
7
8
9
10
11
12
13
---声明语法
declare @name Char(10) --定义一个类型为字符串的name变量

---赋值语法
set @name = 表达式 --一次只能对一个变量赋值
select @变量名1 = 表达式,@变量名2 = 表达式, ..... --一次可以对多个语句赋值

---输出语法
print @name
select @name

---由上可以知道select可以用作变量赋值,也可以用作索引(变量输出),两者不能同时操作
--- select @varsno = 学号,姓名,系 from S 就是非法的

5.2.注释

1
2
3
4
--- 注释 
--为单行注释
/*为多行注释*/
--一整篇文章各种注释,这个其实不用解释的

5.3.控制流语句

if-else语句

1
2
3
4
5
6
7
8
declare @num int
select @num=(select count(distinct 学号))
from SC
where 成绩>80
if(@num)<>0
select @num as '成绩>80的人数'
else
print '没有成绩在80分以上的学生'

case语句

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select 学号,级别=
case
when 成绩<60 then '不及格'
when 成绩>=90 then '优秀'
else '合格'
end
,成绩 from SC
where 成绩 is not null and 课程号='C01'

/*
结果:
学号 级别 成绩
J0401 合格 88
J0402 优秀 90
J0403 合格 76
Q0401 优秀 90
Q0403 合格 77
*/

while语句

1
2
3
4
5
6
7
8
9
while(select avg(成绩) from sc)<60  --出口1:强制让表里面的平均成绩大于等于60
begin
update SC set 成绩 = 成绩+10
if(select max(成绩) from SC)>80 --出口2:强制让表里面的最高成绩>80
break
else
continue
end
print '平均成绩大于60分或者最高成绩大于80分' --出来了表示满足了这句话

5.4.自定义函数

标量函数

1
2
3
4
5
6
7
8
9
10
11
12
---语法
create function 函数名(参数列表) return 返回值类型
---示例
create function score_sum(@s_name char(8)) returns int
begin
declare @sumscore int
select @sumscore=(select sum(成绩) from SC
where 学号=(select 学号 from s
where 姓名 = @s_name)
)
return @sumscore
end

调用方法主要有:1、在select语句中调用和使用exec调用

1
2
3
4
5
6
7
8
9
10
11
12
13
select 拥有者.函数名(形参1,形参2,形参3,...)--格式1

exec 拥有者.函数名 形参1=实参1,形参2=实参2,...--格式2,对比格式1,不用小括号
--格式1要求实参和形参的顺序一致,格式2的参数顺序可与定义是的参数顺序不一致

--应用:在创建数据表s_sum的同时,自动计算每个学生的成绩总和
create table s_sum
(
姓名 char(8)
总成绩 as dbo.score_sum(姓名)
)

drop function func_name --即可完成删除

内嵌表值函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
---举例:定义内嵌表函数coursegrade,要求能查询选修了某一门课程所有的学生成绩。
use studentcourse
go
create function coursegrade(@course varchar(20)) returns table
as
return
(
select sc.学号,sc.课程号,sc.成绩 from c
join sc on sc.课程号=c.课程号
where c.课程名 = @course
)

---调用,因为返回的是一张表,所有
select * from dbo.coursegrade('C语言')

多语句表值函数

1
2
3
4
5
6
7
8
9
10
11
12
13
---多语句表值函数
create function course_grade(@course varchar(20))
returns @score table(s_sno char(6),s_cname char(20),score smallint)
as
begin
insert @score
select 学号,课程名,成绩 from sc,c
where sc.课程号=c.课程号 and c.课程名=@course
return
end

---调用
select * from dbo.course_grade('数据结构')

5.5.游标的使用

使用游标的顺序:声明游标->打开游标->读取数据->关闭游标->删除游标

游标的作用:对于关系数据库中建立的二维表格,应用程序需要能够自由的处理表格中的一行或者一部分行数据,并能自由的定位指定的数据行。T-SQL提供了游标,它如一个指针一般,能在二维表格实行定位和逐行处理的功能。

游标包括两个部分:
(1)游标结果集:由定义该游标的select语句返回行的集合。
(2)游标位置:指向集合中某一行的指针。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
---声明游标
--格式为declare 游标名 cursor [下列属性选其一]
--dynamic(动态游标)
--static(静态游标)
--keyset(索引游标)
--fast_forward
declare sname_cursor cursor dynamic
for select * from s for update of 电话
---打开游标
open sname_cursor
/*
打开游标时,游标位置在第一行。
然后可以使用fetch提取数据
*/
---提取数据
/*
fetch [next|prior|frist|last|
absolute{n|@nvar}|relative{n|@nvar}]
from [游标名|@游标变量]
*/


--关闭游标
close sname_cursor
--删除游标
deallocate sname_cursor

--实例
declare s_cursor cursor
scroll for select 姓名,系 from s
go
open s_cursor
fetch next from s_cursor
while @@fetch_status=0
fetch next from s_cursor
close s_cursor
deallocate s_cursor
--fetch_status是一个全局变量,0代表成功,-1代表语句失败或慈航不在结果集中
-- -2代表被提取的行不存在。
--实例end

5.6.存储过程

存储过程和函数功能类似,是SQL服务器上一组预编译的T-SQL语句

1、存储过程的类型

  • 系统存储过程:存在master数据库中,名称以sp开头
  • 用户定义存储过程:
  • 临时存储过程:以’#‘或’##‘开头
  • 拓展存储过程:存在于DDL中,名称以xp_开头

2、存储过程的主要锈点

  • 模块化编程
  • 快速执行
  • 减少网络通信量
  • 提供安全机制
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
--过程的创建(创建之后存在在可编程性的系统储存过程中查看)
create procedure p_name
as
过程体

create procedure proc_student1
as
select 学号,姓名,性别,系
from s where 学号='J0402'

--过程的调用
exec proc_student1

--有参数的存储过程
create procedure proc_student2 @num char(6)
as
select 学号,姓名,性别,系
from s where 学号=@num

--有参过程的调用
exec proc_student2 @num='J0404'

--例9.4:创建过程s_info
--根据学生姓名和学号查询学生的学号,
--姓名,性别和所在系
create procedure s_info @name char(8),@studno varchar(10)
as
select 学号,姓名,性别,系 from s
where 姓名=@name and 学号=@studno

exec s_info '李丽','J0401'
--例9.5:创建过程s_like
--根据姓名 查询学生的姓名和平均成绩。
--如果执行时候没带参数,则显示姓陈的学生的平均成绩
create procedure s_like @stname varchar(8)='陈%'
as
select 姓名,平均成绩=avg(成绩) from sc
join s on s.学号=sc.学号
where 姓名 like @stname
group by 姓名

exec s_like
exec s_like '李丽'
--例9.8:创建过程s_count,根据课程名,检索某门课程的学生人数
create procedure s_count @ctname varchar(30)=NULL
as
if @ctname is NULL print '请输入课程名'
else
select 课程名,学生选修人数=count(学号) from sc,c
where c.课程号=sc.课程号 and c.课程名=@ctname
group by 课程名

exec s_count 'C语言'
--输出参数之例题9.11:建立一个过程,
--用于显示指定学号的各门课程平均成绩,并返回该生的平均成绩
create proc proc_student3 @num char(16),@savg int output
as
select @savg = avg(成绩) from sc where 学号=@num

--接下来输出
declare @n int
exec proc_student3 'J0401',@n output
--这里不写output会以为是一个输入变量
select @n as '平均成绩'

--返回值之例题9.14 创建存储过程checkstate,
--查询指定课程的最高成绩
--如果最高成绩大于90,则返回状态代码1,否则返回0
create proc checkstate @cno varchar(3)
as
if(select max(成绩) from sc where 课程号=@cno)>90
return 1
else
return 0

declare @mystate int
exec @mystate=checkstate 'C01'
select @mystate
1
2
--修改存储过程
alter procedure proc_name --其他部分和创建一模一样
1
2
--删除存储过程
drop procedure proc_name
1
2
--重命名储存过程(更改对象名可能会破坏脚本和储存过程)
sp_rename old_proc_name,new_proc_name

5.7.触发器

特殊的存储过程,我们会在其中定义一组tsql语句,用于完成某项任务。

触发器的主要作用是能强制数据完整性,保证数据一致性主要表现为:

  • 强化约束
  • 保证参照完整性
  • 级联运行
  • 跟踪变化
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
--语法
create trigger tri_name on table_name|view_name
[with encryption] --对文本进行加密
{for|after|instead of}[delete][,insert][,update] --触发条件
as
[sql语句]

--例9.26:在学生选课表sc上
--创建一个触发器trigger_studnet1
--该触发器被insert操作触发
--当用户向sc表插入一条新记录时,
--先判断该记录的学号在学生基本信息表s中是否存在,
--若存在,插入成功,不存在,插入失败
create trigger trigger_student1 on sc
after insert as
begin
if(select count(*) from inserted join s on inserted.学号=s.学号) = 0
begin
rollback tran
print '插入记录无效!'
end
end
--9.26 在学生信息表S上
--创建一个触发器trigger_stu2
--改触发器被delete操作触发。
--当在表S中删除一条记录时
--判断该生是否在选课成绩表sc中有数据,
--如果没有则允许删除,否则不行
create trigger trigger_stu2 on s
after delete as
begin
if(exists(select * from deleted join sc on deleted.学号=sc.学号))
begin
rollback tran
print '不允许删除该生信息'
end
end
--9.28 在学生信息表s上
--创建一个触发器my_edit,
--该触发器被update操作触发。
--当用户在S表修改某个学生的学号时,
--同时自动更新选课成绩表sc中该生的对应记录的学号
create trigger my_edit on S
after update as
/*在对s做完更新操作的时候,
老的学号会到deleted表中,
新的学号会到inserted表中*/
begin
update sc
set 学号=(select 学号 from inserted) where 学号 in (select 学号 from deleted)
end
--9.29不允许做任何操作
create trigger reminder on s
for insert,update,delete as
begin
rollback tran
raiserror('不能对数据做任何修改!',16,10)
end

--删除触发器
drop trigger tri_name

6.练习使用创建SQL Server2012下数据库语句如下

首先在d盘下创建一个data文件夹

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
create database studentcourse
on primary
(name='studentcourse',
filename='d:\data\studentcourse.mdf',
size=5mb,
maxsize=50mb,
filegrowth=1mb
),
filegroup group1
(name='secondsc00',
filename=N'd:\data\secondsc.ndf',
size=3mb,
maxsize=50mb,
filegrowth=1mb
)
LOG on
(name='studentcourse_log',
filename='d:\data\studentcourse_log.ldf',
size=1mb,
maxsize=20mb,
filegrowth=10%
)
go-------或者create database studentcourse go
use studentcourse
go

create table C
(课程号 Char (3) NOT NULL PRIMARY KEY,
课程名 Varchar (20) NOT NULL,
学分 Smallint,
预选课程号 Char(3),
教师 Char(8),
CHECK(课程号 Like '[C][0-9][0-9]')
)ON [PRIMARY]

CREATE TABLE S
(
学号 Char (6) NOT NULL primary key default('JO400'),
姓名 Char (8) NOT NULL,
性别 Char (2) NOT NULL,
出生日期 Datetime NOT NULL DEFAULT ('1980-01-01'),
Varchar (20) NOT NULL,
电话 Char (8),
CHECK (学号 Like '[J-Q][0-9][0-9][0-9][0-9]'),
CHECK (性别='女' OR 性别='男'),
CHECK (电话 Like '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]'),
)ON [PRIMARY]

CREATE TABLE SC (
学号 Char(6) NOT NULL,
课程号 Char(3) NOT NULL,
成绩 Smallint,
FOREIGN KEY(课程号) REFERENCES C(课程号),
FOREIGN KEY(学号) REFERENCES S(学号),
CHECK (成绩>=(0) AND 成绩<=(100) OR 成绩 IS NULL),
PRIMARY KEY(学号,课程号)
)ON [PRIMARY]
-----------以下为学生基本信息表S00----------
insert into S values ('J0401','李丽','女','1980-2-12','管理信息系','931-1234')
go
insert into S values ('J0402','马俊萍','女','1970-12-2','管理信息系','931-1288')
go
insert into S values ('J0403','王永明','男','1985-12-1','管理信息系','571-2233')
go
insert into S values ('J0404','姚江','男','1985-8-9','管理信息系','571-8848')
go
insert into S values ('Q0401','陈小红','女','1980-2-12','汽车系','571-1122')
go
insert into S values ('Q0403','张干劲','男','1978-1-5','汽车系','571-1111')
go

-----------以下为课程数据表C00----------

insert into C values ('C01','数据库','3','C04','陈弄清')
go
insert into C values ('C02','C语言','4','C04','应刻苦')
go
insert into C values ('C03','数据结构','3','C02','管功臣')
go
insert into C(课程号,课程名,学分,教师) values ('C04','计算机应用基础','2','李学成')
go
insert into C(课程号,课程名,预选课程号,教师) values ('C05','网络技术','C04','马努力')
go

-----------以下为学生选课数据表SC00----------
insert into SC values ('J0401','C01','88')
GO
insert into SC values ('J0401','C02','93')
GO
insert into SC values ('J0401','C03','99')
GO
insert into SC values ('J0401','C04','89')
GO
insert into SC values ('J0401','C05','86')
GO
insert into SC values ('J0402','C01','90')
GO
insert into SC values ('J0402','C02','85')
GO
insert into SC values ('J0402','C03','77')
GO
insert into SC values ('J0402','C05','70')
GO
insert into SC values ('J0403','C01','76')
GO
insert into SC values ('J0403','C02','67')
GO
insert into SC values ('J0403','C03','58')
GO
insert into SC values ('J0403','C04','55')
GO
insert into SC values ('J0403','C05','82')
GO
insert into SC values ('Q0401','C01','90')
GO
insert into SC values ('Q0401','C05','92')
GO
insert into SC values ('Q0403','C05','65')
GO
insert into SC values ('Q0403','C01','77')