T-sql 语法总结

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

一、创建数据库(create database)

-------------------------------------------------------------------------------------------
语法: 

use master --打开master数据库，一般的创建语句都在master中执行.
go

if exists (select * from sysdatabases where name='数据库名')
  drop database 数据库名  --检查有没有这个数据库，如果有就删除它。
go

create database 数据库名
on
(
name=主数据文件名,
filename='主数据文件物理路径名',
size=默认数据库大小,
maxsize=最大容量,
filegrowth=增长量
)
log on
(
name=日志文件名,
filename='日志文件物理路径名',
size=默认数据库大小,
maxsize=最大容量,
filegrowth=增长量
)
-------------------------------------------------------------------------------------------------

数据库有三个主要名称:1.数据库名 它是显示在服务器(如: 企业管理器 中的名称)
       2.主数据文件名 它是数据文件的名称！
       3.主数据文件物理路径名 它是由硬盘上的路径(d:/studb)和物理文件名(students.mdf)组成.如果是
         d:/文件夹 的话文件夹必须存在,如果不存在,手动建立一个,名字一定要打对!!!

name 、 filename 、 size 、 filegrowth 不可以省略，其他的可以省略作为默认选项， log on()可以省略，但是一般都
建议写上  

size 默认数据库大小,默认单位是mb,可以省略mb.不写的话默认是1mb.

maxsize 最大容量是限制数据库容量的属性.不写的话默认是无限制.

filegrowth 是数据库的增长量,它有两种方式增长: 一种是 1mb 以每次增长1mb的方式增长(1可以换任意数字),一种是 20% 以
每次增长现有数据库大小增长20%(20可以任意换数字).

建立数据库一定是在master数据库中运行的.一般打开查询分析器默认的就是master数据库.

SQL语言不区分大小写!!!!

------------------------------------------------------------------------------------------------------
我们下面来建立一个数据库:

数据库名称:   students
主数据文件名: students_data       ←------------  养成好习惯,数据文件加_data,日志文件加_log
物理路径名称: d:/studb/students.mdf          ←------------  一定要是.mdf的文件,代表主数据文件.
默认大小:     5mb
最大容量:     20mb
增长方式:     20%

日志文件名称: students_log
物理路径名称: d:/studb/students.ldf      ←------------  一定要是.ldf的文件,代表日志文件.
默认大小:     1mb
最大容量:     5mb
增长方式:     1mb

use master --打开master数据库，一般的创建语句都在master中执行.
go

if exists (select * from sysdatabases where name='students')
  drop database students
go

create database students
on
(
name=students_data,
filename='d:/studb/students.mdf',
size=5mb,
maxsize=20mb,
filegrowth=20%
)
log on
(
name=students_log,
filename='d:/studb/students.ldf',
size=1,
maxsize=5,
filegrowth=1
)

我们先在d盘下建立一个文件夹，名称为 studb

然后选中这段代码,运行它!....

如果建立不成功的话,1.检查d盘下的文件夹是否有建立好,2.检查是不是在master数据库下创建的.

数据库建立好了.

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

二、创建表(create table)

下面我们打开我们建立的数据库,来创建表结构.

打开数据库语法:

use 数据库名(服务器内的名称)

use students

 

我们可以选中这段代码,运行它!...

------------------------------------------------------------------------

创建表stuinfo

语法:

create table 表名称
(
列名1 数据类型1 约束1,
列名2 数据类型2 约束2,
列名3 数据类型3 约束3
)

列名不要重复

每一个列都要用 , 分开,只有最后一列不是必要的

 

约束   语法

非空约束:   not null    
标示列,自动增长约束:  identity(标识种子,增长量)
默认:   default 默认值
主键约束:  primary key
外键约束:  foreign key （外键表引用的id） references 主表名(主表主键)
alter table smallclass
add constraint  FK_bigid foreign key (bigid) references bigclass(id)
检查约束:  check(条件)

非空约束:  该数据中此列不允许为空

标示列,自动增长约束:  该数据中此列从标识种子的数值开始,以增长量为单位自动增加 如:identity(2,2)    就是从2开始,每次增加2,那么第2个数据此列就为4了,要注意的有两点:一，如果数    据插入失败的话,那么标识列仍然会增加,只是没有数据不会显示出来,如:插人第一    条成功则标识列为2,插入第二条失败,重新插入第二条或者直接插入第三条的时候标    识列就为6了.二，必须是数值类型列才允许添加标识列约束．如: int.

默认:   默认是定义了一般人都会填写的数据,使用户能更快速的填写信息.

主键约束:  一个表中只允许有一个主键.定义了主键的列,它的值是唯一的,比如定义学号列,那    么相同学号的数据就无法插入这个表中了,主键可以用多列组合起来建立,多列的可    以称之为组合键.表中可以没有主键,但是一般都建议设置一个标识列为主键,如果有    需要别的列做主键的,可以不用标识列.

外键约束:  通过强制引用来把两个表数据联系起来,外键存在于"子表"中,它引用了作为"主表"    的主键,一定要是主键!插入"子表"的数据,外键列中的数据,在"主表"中一定要有.一    定要对应起来,否则插入不进去.而"主表"可以在满足约束和类型符合的情况下随意    插入.想插入一个在"主表"中不存在的数据时,可以先在"主表"插入,然后再插"子表"

检查约束:  里面的条件的概念就和if是一样的是当满足条件的才可以插入.注意创建表的时候检   查约束只能是定义自己的列名不允许引用其他列的列名比如:结束时间列ftime大于   开始时间列btime，fime>btime这个约束不可以在创建表的时候建立，只能在创建表    之后再添加这个约束!!!!!

我的列名的命名规则就是全部使用英文含义定义列名 如:储存姓名的列就定义为 name .

数据类型中如果是varchar char ntext text的全部都要设定长度 如: varchar(30) char(5) 别的类型全部不
需要加长度(起码暂时用到的不需要加,以后遇到古怪的类型再说!).

有主键或者外键约束的列,可以省略非空约束,因为默认就是非空了

----------------------------------------------------------------------------------------------

下面我们先创建一个名为 stuinfo 的学生信息表

create table stuinfo   --创建学生信息stuinfo表
(     --创建列开始
sid int identity(1,1) primary key, --sid 学生编号 类型int 自动增长 主键
sname char(8) not null,   --sname 学生姓名 类型char(8) 非空
saddress varchar(30),   --saddress 学生地址 类型varchar(30)
sclass int,    --sclass 学生班级 类型int
semail char(15) check(semail like '%@%'), --semail 学生邮箱
ssex bit default 1   --ssex 学生性别 类型bit bit只有两个值:0和1,这里用1代表'男'
)

好,我们选中代码创建一个stuinfo表

OK!表创建好了!

alter table BBSTopic    -- 增加一个check约束，创建表的时候不允许引用别的列名 如TLastClickT>TTopic
add constraint CK_TLastClickT check(TLastClickT>TTime)

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

三、数据库基本操作(增[insert] 删[delete] 改[update] 查[select])

------------------------------------------------------------------------------------------------

增[insert]

 

在表中插入数据

数据都是储存在数据库的表结构中的.下面我们在刚才建立的stuinfo表中添加几个数据,添加数据的方式一般有种.

1.插入一行数据

语法:

insert into 表名(列名1,列名2,列名3,列名4)
values(数据1,数据2,数据3,数据4)

into 语句可以省略但是不建议省略!

注意,标识列自动添加,不能自己插入数据,所以不要写到列名中去!

数据添加的时候,除了数值类型,全部要加单引号!!!如: '2007-11-04','张三',1,50,'北京'

小数数据精度要和列所定义的类型匹配!

如果插入的是表中的全部列时,可以省略不写列名如:

insert into 表名
values(数据1,数据2,数据3,数据4,数据5)

下面我们来添加三行数据

insert into stuinfo(sname,saddress,sclass,semail,ssex)
values('张三','北京丰台',1,'[e-mail]',1)

insert into stuinfo(sname,saddress,sclass,semail,ssex)
values('王五','北京海淀',2,'[e-mail]',default)

insert into stuinfo(sname,saddress,sclass,semail,ssex)
values('李丽','北京朝阳',1,'[e-mail]',0)

添加成功!

也可以只添加有非空约束列的数据,没有非空约束的列,可以选择性添加,系统会自动添加null值!非空约束列必须添加数据!如:

insert stuinfo(sname,sclass)
values('孙武',2)

添加成功!

------------------------------------------------------------------------------------------------------

2.把一个表数据复制到新表中

语法:
select 目标列1,目标列2,目标列3  复制所有列用*就可以了
into 新目标表
from 原有表

如果我们想创建一张新表来只储存stuinfo的sid,sname,semail三列,那么我们可以执行下面的语句:

select sid,sname,semail
into stubif
from stuinfo

在数据库中创建了一张新表储存了sid,sname,semail三列的信息.

我们想在新表中变换列名储存,好使得数据更容易被查询!

因为我们已经创建了表stubif 所以我们必须先删除它才能再次建立新stubif表:

删除表

drop table stubif

系统中不存在stubif表了!

下面复制数据到新表并插入新标识列:

语法:

identity(数据类型,标识种子,增长量) as 列名

select sname,semail,identity(int,1,1) as tid
into stubif
from stuinfo

标识列只能有一列

--------------------------------------------------------------------------------------------------

3.通过 union 关键字合并数据进行插入多行数据

语法:

insert 表名(列名1,列名2,列名3,列名4)
select 数据1,数据2,数据3,数据4 union
select 数据1,数据2,数据3,数据4 union
select 数据1,数据2,数据3,数据4 union
select 数据1,数据2,数据3,数据4 union
select 数据1,数据2,数据3,数据4

最后一个select 语句绝对不可以有 union 关键字,union 后面一定要有插入的数据!

数据中不可以出现 default 代替默认选项

下面我们来运行下面这段多行插入代码

insert stuinfo(sname,saddress,semail,sclass,ssex)
select '测试1','地址1','[e-mail]',3,1 union
select '测试2','地址2','[e-mail]',3,1 union
select '测试3','地址3','[e-mail]',3,1 union
select '测试4','地址4','[e-mail]',3,1 union
select '测试5','地址5','[e-mail]',3,0 union
select '测试6','地址6','[e-mail]',3,0 union
select '测试7','地址7','[e-mail]',3,0 union
select '测试8','地址8','[e-mail]',3,0

在插入有外键关系的"子表"中插入数据的话,那么主键列的数据必须在"主表"中存在才可以插入进去!

插入就先说到这里

----------------------------------------------------------------------------------------------

删[delete]

删除有3种方式:

1. drop

drop table 表名

删除这个表!表中的约束和数据全部被删除!不过如果有外键关系的话那么删除会失败!

drop table stubif

drop database 数据库名

自己实验去！！我懒的再重新建立数据库了！

删除这个数据库!数据库整个被删除!表约束一起被删除!所有都被删除!

注意!删除表时候!表不能被打开！删除数据库的时候，数据库不能被打开！

2. truncate

truncate table 表名

truncate 相当于没有条件的 delete 语句 ，但是他比 delete 执行速度快！使用的系统资源和事务日志资源更少.

表的结构、列、约束、索引不会改动，而且不能用于有外键约束引用的表，这时候要用 delete 语句

重新创建一个表

select sid,sname,semail
into stubif
from stuinfo

truncate table stubif

删除外键约束
ALTER TABLE TABLE_NAME DROP CONSTRAINT CONSTRAINT_NAME

3. delete

delete from 表名

delete from where 条件

我们先还原原来的表.

drop table stubif

select sid,sname,semail
into stubif
from stuinfo

----------------------------
下面我们删除sid列是偶数的数据！

delete from stubif where sid%2=0

我们查询一下结果：

select * from stubif

我们运行下面的代码：

delete from stubif

查看一下：

select * from stubif

删除了所有的数据，对吗？表还存在么？

数据全部被删除了，而表还是存在的！

分别运行它们，想想有 where 和没有的区别是什么！

一般 delete 语句都要有条件的，要不一个表中的数据会被都删除掉了！想哭都来不及！

------------------------------------------------------------------------------------------

改[update]

语法：

update 表名 set 列名=新数据
update 表名 set 列名=新数据 where 条件
update 表名 set 列名1=新数据1,列名2=新数据2 where 条件

下面我们来更新数据！

我们先看看现在的表中有什么数据

select * from stuinfo

更新单个的！

update stuinfo set semail='[e-mail]' where sname='沈杨'

select * from stuinfo

更新多行的！

update stuinfo set saddress='美国',semail='[e-mail]' where sname='沈杨'

select * from stuinfo

更新这列的全部数据！不写 where 条件就可以了

update stuinfo set semail='[e-mail]'

select * from stuinfo

---------------------------------------------------------------------

查[select]

select 列名1(，列名2，列名3，如果是全部的话，可以写*)
from 表

select 列名1(，列名2，列名3，如果是全部的话，可以写*)
from 表
where 条件

select 列名1(，列名2，列名3，如果是全部的话，可以写*)
from 表
where 条件
order by 列1 (排列方式，列2 排列方式)

--------------------------------------------------------------------------

select sid,sname,saddress,sclass,semail,ssex
from stuinfo   --查询所有学生信息的所有列

select * from stuinfo  --查询所有学生信息的所有列

select sid,sname
from stuinfo   --查询stuinfo表的sid,和sname列

select saddress
from stuinfo   --查询学生信息表的地址列

select sid,sname,saddress,sclass,semail,ssex
from stuinfo
where sid%2=0   --查询所有学号列是偶数的学生信息

select sid,sname,saddress,sclass,semail,ssex
from stuinfo
order by sid asc  --查询所有学生信息,按照sid列升序排列,不写也可以默认为升序

select sid,sname,saddress,sclass,semail,ssex
from stuinfo
order by sid desc  --查询所有学生信息,按照sid列降序排列

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

四、T-SQL中的内部函数

字符串函数:

---------------------------------------------------------------------

1.用来寻找一个字符串在另外一个字符串中的起始位置

charindex('想查询的字符串','目标字符串',从哪个位置查起)

只要能查询的字符串在目标字符串的起始位置后面,那么无论起始位置是多少,返回的数值是一样的,都是从目标字符串的第一个
字符算起.如果在查询的字符串在目标字符串起始位置的前面或者根本就不存在这个查询的字符串,则返回0.

举例:                                             

select charindex('AbC','we are learn abc now!',10)

返回值: 14

select charindex('123','wang luo 123 chuan mei!',11)

返回值: 0

----------------------------------------------------------------------

2.返回字符串长度

len('字符串')

所有字符都算在内,包括空格!

举例:                      

select len('SQL Server课程')

返回值: 12

----------------------------------------------------------------------

3.转换字符串为小写

lower('字符串')

所有的字母都小写输出

举例:                                        

select lower('ABcdE2')

返回值: abcde2

----------------------------------------------------------------------

4.转换字符串为大写

upper('字符串')

所有的字母都大写输出

举例:                                               

select upper('ABcdE2')

返回值: ABCDE2

----------------------------------------------------------------------

5.清除空格

ltrim('字符串')  rtrim('字符串')

ltrim去掉左边的空格,右面的保留,rtrim去掉右边的空格,左面的保留!

举例:                                                

select ltrim('  王 五   ')

返回值: 王 五   (后面三个空格)

select rtrim('  王 五   ')

返回值:   王 五(前面两个空格)

可以一起用:

select ltrim(rtrim('  王 五   '))  或者  select rtrim(ltrim('  王 五   '))

返回值: 王 五(前后没空格,注意中间的空格不会去掉)

-----------------------------------------------------------------------------------------

6.从字符右(左)边返回指定数目的字符

right('字符串',数量)  left('字符串',数量)

举例:

select right('王5撒旦自行车飞.张3',2)

返回值: 张3

select left('王5撒旦自行车飞.张3',4)

返回值: 王5撒旦

------------------------------------------------------------------------------------------

7.替换一个字符串中的字符

replace('字符串','原字符','新字符')

只要是原字符,都换成新字符(一般题出在密码中的 l和1 [爱欧和一] O和0 [哦和零]的替换)

举例:

select replace('acabcbe12','b','c')

返回值: acaccce12

-------------------------------------------------------------------------------
8.在一个字符串中,删除指定长度的字符,并在该位置插入一个新的字符串

stuff('字符串',起始位置,删除个数,'插入的字符串')

举例:

select stuff('ABCDEFG',2,3,'我的音乐我的世界')

返回值: A我的音乐我的世界EFG

------------------------------------------------------------------------------
9.返回整数表达式

char(0~127之间的整数(对应ASCLL码))

举例:

select char(65)

返回值: A

--------------------------------------------------------------------------------

10.返回输入表达式的第一个字符的整数值

unicode('字符串')

举例:

select unicode('bcdef')

返回值: 98

------------------------------------------------------------------------------
select GETUTCDATE()
11.

日期函数:

日期的各部分表示

year  yy yyyy 年
quarter  qq q 季度
month  mm m 月
dayofyear dy y 当天是一年中的第几天
day  dd d 天
week  wk ww 当周是一年中的第几周
weekday  dw  当天是星期几
hour  hh  小时
minute  mi n 分
second  ss s 秒
millisecond ms  毫秒

 

-----------------------------------------------------------------------------------------------------

1.取得当前时间

getdate()

获得系统时间 格式: 年-月-日 时:分:秒.毫秒

举例:

select getdate()

返回值: 2007-11-28 22:52:38.573

select getdate()

返回值: 2007-11-28 22:53:27.721

----------------------------------------------------------------------------------------------------

2.将指定的数值添加到指定的日期部分后的日期

dateadd(部分名称,指定数值,'日期')

数值可以为正,做加法,也可以为负,做减法.日期格式可以是 年-月-日 月/日/年   年/月/日 但是返回值是当前日期格式

年-月-日 时:分:秒.毫秒

举例:

select dateadd(mm,4,'01/01/2007')

返回值: 2007-05-01 00:00:00.000

select dateadd(mm,-4,'2007/01/01')

返回值: 2006-09-01 00:00:00.000

---------------------------------------------------------------------------------------------------

3.两个日期之间的指定日期之间的区别

datediff(指定日期部分,'日期1','日期2')

返回的是日期2中的指定部分-日期1中的指定部分 注意在它前面的部分也要算进去！这是时间的特性！如：

select datediff(mm,'01/01/2007','05/09/2006')

它就是把06-07年返回了-12再加上了（5-1）返回值为-8

举例:

select datediff(mm,'05/02/2007','09/23/2007')

返回值: 4

select datediff(mm,'01/01/2007','05/09/2006')

返回值: -8

----------------------------------------------------------------------------------

4.日期中指定日期部分的字符串形式

datename(指定日期部分,'日期')

日期格式会在月跟天的前面加0补齐2位
毫秒的最后一位上是0~1则显示0，是2~4则显示3，是5~8则显示7，是9~1则进一位显示0

举例：

select datename(dw,'11/29/2007')

返回值：星期四

select datename(ms,'12:07:38.029')

返回值: 30

select datename(mi,'23:07:38')

返回值：7

select datename(mm,'1/1/2007')

返回值：01

------------------------------------------------------------------------------------

5.日期中指定日期部分的整数形式

datepart(指定日期部分,'日期')

外国人在一个星期中是以星期日为第一天的，所以星期四会返回5。
月日前面是0的只返回数字不返回0

举例：

select datepart(dw,'11/29/2007')

返回值: 5

select datepart(dd,'01/01/2007')

返回值：1

------------------------------------------------------------------------

数学函数：

-----------------------------------------------------------------------

1.取数值表达式的绝对值

abs(数值)

举例：

select abs(-43)

返回值：43

-----------------------------------------------------------------------

2.取大于或等于指定数值、表达式的最小整数

ceiling(数值)

举例：

select ceiling(44.1)

返回值：45

select ceiling(44)

返回值：44

-------------------------------------------------------------------------

3.取小于或等于指定数值、表达试的最大整数

floor(数值)

举例：

select floor(43.9)

返回值：43

select floor(43)

返回值：43

--------------------------------------------------------------------------

4.取数值表达式的幂值

power(底数,指数)

幂全部是整数

举例：

select power(5,2.5)

返回值：55

select power(4,2.5)

返回值：32

----------------------------------------------------------------------------

5.将数值表达式四舍五入为指定精度

round(数值,保留位数)

因为是返回精度的，所以数值一般是小数。

举例：

select round(43.3545,1)

返回值: 43.4000

----------------------------------------------------------------------------

6.对于正数返回1,负数返回-1,0则返回0

sign(数值)

正小数返回1.0,负小数返回-1.0

举例:

select sign(-43.3)

返回值: -1.0

select sign(43)

返回值: 1

select sign(0)

返回值: 0

----------------------------------------------------------------

7.取符点表达式的平方根

sqrt(数值)

举例:

select sqrt(9)

返回值:3.0

 

----------------------------------------------------------------------

系统函数:

---------------------------------------------------------------------

1.用来转变数据类型:

convert(数据类型(分配大小),转换目标)

分配大小一定要大于等于转换目标的长度大小,否则不会出现正确结果.

举例:

select convert(varchar(5),12345)

返回值: 字符串12345

select convert(int(5),'12345')

返回值: ?

select convert(int(5),'abcde')

返回值: ?

-----------------------------------------------------------------------

2.返回当前用户的名字????

current_user

举例:

select current_user

返回值: 你登录的用户名

-------------------------------------------------------------------------

3.返回用于指定表达式的字节数

datalength('表达式')

举例:

select datalength('中国 A 联盟')

返回值: 5

---------------------------------------------------------------------------

4.返回当前用户所登录的计算机名字

host_name()

举例:

select host_name()

返回值: 你所登录的计算机名字

---------------------------------------------------------------------------

5.返回当前所登录的用户名称

system_user

举例:

select system_user

返回值: 你当前所登录的用户名

----------------------------------------------------------------------------

6.从给定的用户ID返回用户名

user_name

举例:

select user_name(1)

返回值: 从任意数据库中返回"dbo"

----------------------------------------------------------------------------

聚合函数:  聚合函数全部应用于数值列!别的列无法使用

---------------------------------------------------------------------------

1.求和

sum(列名)

举例:

select sum(列名) from 表

2.求平均数

avg(列名)

举例:

select avg(列名) from 表

3.求最大值

max(列名)

select max(列名) from 表

4.求最小值

min(列名)

select min(列名) from 表

5.返回非空行数

count(列名)

如果 count(*)那么除非一行全是空的数据,否则都会算上这一行!

select count(列名)from 表

----------------------------------------------------------------------------------------------------

SQL的查询条件:

我们上一章介绍了SQL的创建数据库和表,基本的查找(select)、删除(delect)、更新(update)和插入(insert)
以及一些系统函数的基本应用,下面我们来进入高一层次的学习: 条件查询,更改列名查询,在查询中使用运算符、
表达式、函数,查询排序和 top 查询以及模糊查询、聚合函数的使用、分组查询、多表联接查询．

大家想一想，如果我会查询数据，是不是我也就可以更改，删除我所想操作的数据了？

答案是肯定的,把select 列名 from ... where ...转换成相应的 delete from ... where ... 或者
 update ... set ... where ... 就可以了!条件是一样的.

下面我们先来学习,如何使用条件查询!

我们先复习一下创建数据库,然后我再教大家一种创建以后修改数据库基本属性的命令

我们创建一个数据库MYDB,放在d:/MYDB 文件夹下.主数据文件名、物理路径等其他属性自己随意定义.

create database MYDB
on
(
name=mydb_data,
filename='d:/MYDB/mydb.mdf',
size=3,
filegrowth=2
)
log on
(
name=mydb_log,
filename='d:/MYDB/mydb.ldf',
size=1,
filegrowth=1
)

数据库好了,下面我们给数据库增加一个次要数据文件(ndf)

alter database MYDB
add file
(
name=mydb_data1,
filename='d:/MYDB/mydb.ndf',
size=1,
filegrowth=1
)

再下面我们来修改日志文件(ldf)

alter database MYDB
modify file
(
name=mydb_log,
maxsize=10,
filegrowth=2
)

执行它们!

好了,下面我们可以打开企业管理器看看数据库的属性变成了什么.

大家学会了么?具体别的属性的修改,大家自己琢磨,类似这样!

好了,下面我们把这个练习用的数据库删除了吧!

drop database MYDB

 

我们开始!

首先!我们先来建立一个学生数据库,然后再建立两张表,以及插入一些数据来配合我们的学习!

use master
go

if exists (select * from sysdatabases where name='s1')
  drop database s1
go

create database s1
on
(
name=s1_data,
filename='d:/s1.mdf',
size=2,
filegrowth=1
)
log on
(
name=s1_log,
filename='d:/s1.ldf',
size=1,
filegrowth=1
)
go

use s1
go

create table stu
(
id varchar(10) primary key check(id like 's1112%'),
name varchar(8) not null,
age int not null check(age between 18 and 50),
email varchar(20) check(email like '%@%'),
address varchar(50) default '地址不详',
sex varchar(2) not null default '男' check(sex='男' or sex='女')
)

create table mark
(
cid int identity(1,1) primary key,
id varchar(10) foreign key references stu(id),
course varchar(10),
mark float default 0.0 check(mark between 0 and 100)
)
go

insert into stu(id,name,age,email,address,sex)
select 's111201','连旭',21,'[e-mail]','北京顺义','男' union
select 's111206','沈杨',22,'[e-mail]','北京海淀','女' union
select 's111226','郭文姣',20,'[e-mail]','北京丰台','女' union
select 's111210','陈丽',21,'[e-mail]','北京大兴','女' union
select 's111212','任艳',21,'[e-mail]','北京朝阳','女' union
select 's111217','张志',22,'[e-mail]','北京朝阳','男' union
select 's111218','张华',20,'[e-mail]','北京海淀','男' union
select 's111222','马景文',23,'[e-mail]','北京海淀','男' union
select 's111228','赵鑫峰',22,'[e-mail]','北京海淀','男' union
select 's111215','艾艺',22,'[e-mail]','北京海淀','男'
go

insert into mark(id,course,mark)
select 's111206','C',59.5 union
select 's111206','C',89.0 union
select 's111226','C',87.0 union
select 's111210','JAVA',18.0 union
select 's111212','JAVA',67.5 union
select 's111217','C',12.5 union
select 's111217','C',70.0 union
select 's111222','JAVA',86.5 union
select 's111228','JAVA',64.5 union
select 's111215','C',99.5

下面我们来验证一下 check 约束:

学号不满足check约束:

insert into stu(id,name,age,email,address,sex)
values('112233','连旭',18,'[e-mail]','北京顺义','男')

插入失败!

姓名超过长度:

insert into stu(id,name,age,email,address,sex)
values('s111219','张阿三是谁',18,'[e-mail]','北京顺义','男')

年龄不符合条件:

insert into stu(id,name,age,email,address,sex)
values('s111219','张阿三',15,'[e-mail]','北京顺义','男')

邮箱不满足条件:

insert into stu(id,name,age,email,address,sex)
values('s111219','张阿三',19,'liaxu·163.com','北京顺义','男')

地址忘记填写：

insert into stu(id,name,age,email,address,sex)
values('s111219','张阿三',19,'[e-mail]','男')

性别填写错误：

insert into stu(id,name,age,email,address,sex)
values('s111219','张阿三',19,'[e-mail]','北京顺义','o')

重复插入！验证主键功能：

insert into stu(id,name,age,email,address,sex)
values('s111219','张阿三',19,'[e-mail]','北京顺义','男')

插入成功！

insert into stu(id,name,age,email,address,sex)
values('s111219','张阿三',19,'[e-mail]','北京顺义','男')

插入失败！！学号重复！

-----------------------------------------------------------------------

ok.下面我们来查询数据！

首先先查询一下所有学生的基本信息！

select * from stu

因为列名是英文的不好识别，下面我们来更改一下显示的列名，用中文显示！

select id as 学号,name as 姓名,age as 年龄,email as 邮箱,address as 地址,sex as 性别
from stu

怎么样！效果出现了！现在比较好识别数据了吧。

我们还有一种方式转换列名：

select 学号=id,姓名=name,年龄=age,邮箱=email,地址=address,性别=sex
from stu

效果是一样的！

大家要注意 '=' 和 'as' 分别对应的内容是相反的！

下面我们来合并列来查看数据！我们想要得到学号加姓名的新列！

select id+name as '    学名' from stu

下面我们想查询一下北京海淀的学生信息！那么我们加一个查询条件！

select 学号=id,姓名=name,年龄=age,邮箱=email,地址=address,性别=sex
from stu
where address='北京海淀'

select 学号=id,姓名=name,年龄=age,邮箱=email,地址=address,性别=sex
from stu
where address in ('北京海淀')

select 学号=id,姓名=name,年龄=age,邮箱=email,地址=address,性别=sex
from stu
where address like '%海淀'

三个查询的结果都是一样的，都是我们想得到的结果！对么？

下面我们来讲一讲，类似查询（ like ）、 范围查询（ between ）、和枚举查询（ in ）

模糊查询的通配符：

'_' 代表一个字符 A like 'C_'

 %  任意长度的字符串 B like 'CO%'

[a-d][0-9] 它们之间的一个字符 C like '[a-z][0-9][0-9][0-9]'

[aery] 它们之间的一个字符 D like '[aery]'

[^a-d] 不是它们之间的字符

[^aery] 不是它们之间的字符

-------------------------------------------------------------------------------

select * from stu where email like '[e-mail]'

下面我们学习两个连接符号 and or

select * from stu where email like '[e-mail]' and address='北京海淀'

select * from stu where email like '[e-mail]' or email like '[e-mail]'

观察出效果了么？这就是连接两个条件的符号！

----------------------------------------------------------------------------

ok,我们来查询一下mark表

select cid as 编号,id as 学号,course as 课程,mark as 成绩 from mark

建表的时候大家看到了 我用到了 between 0 and 100 这个可以查询介于两个已知值之间的一组未知值！也就是
大于等于0小于等于100之间的数值！不过小数一定要放在 and 的前面！

现在我们查询一下成绩在60到90之间的学生的学号！

select id as '60-90之间的学生学号' from mark where mark between 60 and 90

查询日期一般都使用 between 如:

where time between '1992-8-1' and '1993-8-1' 这条语句不可以直接执行，只是条件！

我们查询一下地址是北京顺义或者北京朝阳或者北京大兴的学生学号，姓名，性别！

select id,name,sex from stu where address in ('北京顺义','北京朝阳','北京大兴')

 

--------------------------------------------------------------------------------------

ok,我们再来查询一下mark表

select cid as 编号,id as 学号,course as 课程,mark as 成绩 from mark

我们现在学习聚合函数的使用！聚合函数只有一个值！所以查询结果如果显示2行的话，是不可以显示聚合函数的！

查询一下所有卷子的总分

select sum(mark) from mark

显示两列,总分，加平均分！

select sum(mark),avg(mark) from mark

显示成绩最高分的学生学号，课程名和分数！
聚合函数不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，
并且要对其进行聚合的列是外部引用。

所以我们先分两步去做！后面我们可以分组去查询！

select max(mark) from mark

select id,course,mark from mark where  mark=99.5

这次考试最少的成绩是多少？

select min(mark) from mark

下面我们插入两条带有空数据的学生信息！

insert into stu(id,name,age,email,address,sex)
select 's1112ss','本·拉登',40,null,null,'男' union
select 's1112aa','布莱尼',29,'[e-mail]',null,'女'

select * from stu

我们来查询一下stu表一共有多少学生！

select count(*) from stu

select count(id) from stu

select count(email) from stu

select count(address) from stu

count()函数统计的是非空行数！，如果查询的是*的话，只要它有一列不为空，那么就统计！如果查询的是单列的话，
那么有null的数据就不统计了。如果有条件的话再加上条件就行！

---------------------------------------------------------------------------------------------------

分组查询！

我们想统计每一门课程的课程平均分！那么我们就应该把课程分组，然后统计这门课程的平均分

select course,avg(mark) from mark
group by course

group by 将值一样的分成一组，注意 select 和 from 之间的列名只能是分组列 如course可以直接显示，非分组列
一定要放在聚合函数中才可以正常显示，也就是说，只能显示行数，和，最值，平均数这几个内容！ 可以给分组后的
数据排序 order by 依据列名称可以是虚拟名称，如

select course,avg(mark) as 课程平均分 from mark
group by course
order by 课程平均分

分组之后不可以跟 where 字句 ，条件用 having 关键子来表示

比如说：我想查一下，参加过补考的同学的学号！（注意分数有2个所以不能在分组中查询，只能查出学号再根据学号
查询到相应的信息）

这道题考察的是要把同一学生同一课程的数据分组起来。在mark表中，同一学生，同样课程的成绩出现过2次以上的为补考过的

所以我们以两个列为分组依据：

select id from mark
group by id,course
having count(id)>1

按照平均成绩的降序显示参加过补考的同学！

select id from mark
group by id,course
having count(mark)>1
order by avg(mark)

----------------------------------------------------------------------------------

大家知道我们把这两个表引用了外键关系，我们如果想查两个表的信息一起显示怎么办？

这是可以的，用联接就可以了。

 

1.内联接

我们要是想查找两个表中同公共的部分的全部信息！

select * from stu inner join mark on stu.id=mark.id

大家注意！如果遇见两个表的字段名（列名）完全一样的话，必须用表名.列名表示！否则计算机不知道到底是哪个表的！

 

只显示前两条：

select top 10 percent * from stu inner join mark on stu.id=mark.id

select top 11 percent * from stu inner join mark on stu.id=mark.id

想象一下 top 在 百分比输出的时候的规律！ 

输多不输少！至少都会有一句被输出！

如果我们只想有选择的去查询，那么只需要加上 where 条件就可以了！

输出考试不及格的学生信息

select * from stu inner join mark on stu.id=mark.id where mark<60

输出考试不及格的学生姓名：

select stu.name as 学生姓名 from stu inner join mark on stu.id=mark.id where mark<60

 

2.左外联接（前为主表，后为辅表）

主表中信息完全显示！辅表中的信息对应主表如果是无的话，全部用 null 补齐

select 学号=stu.id,姓名=name,年龄=age,邮箱=email,地址=address,性别=sex,学号1=mark.id,课程=course,分数=mark
from stu left outer join mark on stu.id=mark.id

left outer join 可以写成  left join

3.右外联接（后为主表，前为辅表）

select 学号=stu.id,姓名=name,年龄=age,邮箱=email,地址=address,性别=sex,学号1=mark.id,课程=course,分数=mark
from stu right outer join mark on stu.id=mark.id

right outer join 可以写成  right join

4.完全联接

显示两表全部信息，对应相互补 null

select 学号=stu.id,姓名=name,年龄=age,邮箱=email,地址=address,性别=sex,学号1=mark.id,课程=course,分数=mark
from stu full outer join mark on stu.id=mark.id

full outer join 可以写成  full join

两个表有外键关系的话，以主表为主的外联结与完全联结效果相同！

5.自身联接

select * from mark as a inner join mark as b on a.id=b.id

6.多表连接

select * from BBSTopic inner join BBSUsers on TUID=UID full join BBSReply on TID=RTID

好了基本语法就介绍到这里