SQL数据库基础（七）
这些都是存到内存中的数据,都是临时存储！！！
软件重开或者电脑重启数据就会丢失！！
问题：如果要永久保存数据，怎么办？
解决办法：用数据库保存！！
2、数据库：database,简称DB。用于存储数据一个仓库。
有哪些常见数据库软件：
SQL Server(微软)、MySQL、Oracel等。
数据库系统：简称DBS。指一个能支持数据库的完整的计算机系统，包含了硬件、操作系统、其它系统软件、数据库管理系统、数据库管理员等。
数据库管理系统：简称DBMS。指的是数据库软件。
比如SQL Server(微软)、MySQL、Oracel等。
数据库管理员：简称DBA。
3、登录SQL Server数据库：
1)登录前，要先开启数据库引擎服务：
a、第一种：
SQL Server配置管理器中找到服务--
SQL Server(MSSQLSERVER),右键启动
b、第二种：
win+r打开运行栏输入control，打开控制面板。
找到管理工具-->服务：
从服务列表中找到引擎服务，右键开启。（直接运行栏输入services.msc快速打开服务）
c、第三种：用DOS命令
运行栏输入cmd，打开dos界面：
net start mssqlserver(开启服务：mssqlserver是服务名)
net stop mssqlserver(停止服务)
2)登录：
a、服务器名称：
本地服务器：本机，服务器名称一般写：
计算机名、(local)、localhost、.
如果命名实例，还要在后面加上实例名:
eg： ./xiaoming (xiaoming是命名实例)
远程服务器：一般写对方计算机的IP地址
b、身份验证：windows身份验证和SQL Server身份验证。
windows身份验证：使用操作系统管理账号进行登录，不需要输入密码。
SQL Server身份验证：软件本身的验证方式。
登录名是sa (系统管理员)
要输入密码：安装时设置的密码！
如果忘记密码，怎么修改？
先用windows身份验证登录，
安全性-->登录名-->双击sa进去修改密码！
4、数据库类型有两类：
系统数据库：4个
master: 核心数据库，存储很多关于服务器配置、账号等数据。
model:模板数据库，提供定义数据库的一种模板。
msdb: 提供关于错误提示等信息。
tempdb:临时数据库，用于保存操作过程中一些临时数据。
用户自定义数据库：
数据库基本操作：创建数据库、重命名、删除、分离、附加
5、数据库的组成：
1）数据库本质是由文件组成；
2）常见的数据库文件有3种类型：
a、主数据文件: 文件扩展名是.mdf，最大的作用就是用于存储数据。每个数据库有且只有一个 主数据文件。
b、次数据文件：文件扩展名是.ndf，也是用于存储数据的。每个数据库可以0个以上的次数据文件。
c、日志文件：文件扩展名是.ldf，用于存储日志信息。每个数据库有1个以上的日志文件。
3）文件组（了解）：相当于文件夹 (分类管理文件)用于分类管理数据库的2种数据文件（主要和次要）。
文件组分成两类：
a、第一种：叫Primary，这是主文件组（系统提供）
主数据文件只能放主文件组。
b、第二种：用户自定义文件组。
次数据文件可以放主文件组或自定义文件组中。
注意：日志文件不适用文件组！
6、数据库代码操作：
1、
--SQL脚本语言:不区分大小写
/*
数据库管理的
代码操作！
*/
--1）、创建数据库：所有设置默认
create database StudentDB
go
--2）、切换数据库：在使用某个数据库前，要切换到它
use StudentDB
go
--3）、重命名数据库（了解）
alter database StudentDB
modify name=NFStudentDB --新名称
go
--4）、删除数据库：没有在使用时才能删除
drop database NFStudentDB
go
--5）、分离数据库：移动数据库文件之前一定要分离
exec sp_detach_db StudentDB
go
--6）、附加数据库：把数据库文件重新加入服务器中
exec sp_attach_db StudentDB,'D:\StudentDB.mdf'
go
2、
--创建数据库 create database PersonDB go --分离数据库 exec sp_detach_db PersonDB go --附加数据库 exec sp_attach_db PersonDB,'d:\mydb\PersonDB.mdf','d:\mydb\PersonDB_log.ldf' go --重命名数据库 alter database PersonDB modify name=MyPersonDB go --切换数据库 use MyPersonDB go --创建员工表 create table EmpInfo ( Eid int, --工号 Ename varchar(50), --姓名 Sex char(2), --性别 JoinTime datetime, --入职日期 WorkYears int, --工作年限 Postion varchar(50), --职位 BaseMoney money, --基本工资 Remark text --简介 ) go create table WorkInfo ( Wid int, --考勤编号 Eid int, --工号 WorkTime datetime,--上班打卡时间 OutTime datetime --下班打卡时间 ) go
7、类型
1、整型：int、bigint、smallint、tinyint（0-255）、bit(0和1) 2、浮点型：float/real、numeric(18,2)/decimal(18,2) 3、字符型：char(50)、varchar(50)、nchar(50)、nvarchar(50) 4、文本型：text、ntext 5、日期时间：datetime、date(只有日期)、smalldatetime 6、货币：money、smallmoney 7、图像：image(二进制) 8、二进制:binary(100)、varbinary(100)
1）创建了数据库后，就可以考虑往里面存储数据了。
2）数据库中把数据保存在数据表中，保存前，要创建数据表。
3）建表前，要考虑数据的类型。常见的数据类型有：
a、整型：
int、bigint、smallint、tinyint(0~255)、 bit(位，表示0或1，相当于false或true)
b、浮点型:
float、real、decimal(m,n)或numeric(m,n):一样的含义
eg:
decimal(10,2):10是总位数，2是小数位数
c、字符型:
a、char(n): n表示最多能存储多少个字符,n范围1~8000.表示定长的字符数据。适用于长度固定的字符数据。
eg:
char(10):最多存10个字符的数据。
存了'hello'，5个字符，占了5个位置。
剩余的位置用空格补齐。事实上，真正存的是'hello '。
b、varchar(n)：表示变长的字符数据。
eg:
varchar(10):最多存10个字符的数据。
存了'hello'，5个字符，占了5个位置。
剩余的位置不会用空格补齐。真正存的是'hello'。
c、nchar(n)、nvarchar(n):表示国际化，前面的n表示使用Unicode编码。
d、日期时间类型：
datetime:范围更大 smalldatetime:1900~2079范围小
e、货币类型：
存货币值，比如：￥100,$100 money:范围更大 smallmoney:范围小
e、文本类型:
存大量的文本数据。 text：文本 ntext:国际化
f、图像类型:
存储图片数据。(了解) image
g、二进制类型:
存二进制数据 binary(n):固定长度，n表示字节数 varbinary(n):可变长度 nbinary(n),nvarbinary(n):国际化
8、创建数据表：一种事物的数据用一张表存。
比如：学生、老师、班级数据用3张表。 表名: dbo.StudentInfo dbo:database owner 数据库所有者
9、约束
1）约束的概念
1、约束：对数据的限制条件，比如性别只能是男或女。
可以更好保证数据的可靠性和准确性。
2）约束大概有7类：
a、主键约束：(primary key)
目的要保证表中的每一条数据都是唯一的。
不会出现两条一模一样的数据。
(数据冗余：出现重复的数据！如果设置了主键，减少数据冗余。)
一般选择编号这样的列作为主键列（加主键约束）。
主键列的值不能出现重复值和空值（null）。
b、自增约束:(identity(初值,每次增加多少))
自动增长（标识列），由系统给出值，用户不能修改。
标识种子：初值
标识增量：每次增加多少
注意：只有整型的列才能设置自增。
一张表只能有1个自增列。
c、非空约束：(not null)
不允许有null值。
d、默认值约束:(default 值)
当某列有默认值，输入时没有填写数据，会自动取值为默认值。 datetime default getdate()--默认当前时间
e、唯一约束: (unique)
不允许出现重复值，允许出现一个null值。 比如身份证号是唯一的。
f、检查约束:(check(条件))
check(len(Code)=18)--长度必须18位
限制数据的取值范围。
比如：性别只能是男或女
check(列名='男' or 列名='女')
check(列名 in('男','女'))
成绩在0到100之间
check(列名>=0 and 列名<=100)
写条件表达式需要用到运算符：
1、关系运算符：
>、>=、<、<=、=（等于）、!=(不等于)、<>(不等于)
2、逻辑运算符：
and：与，多条件同时成立
or：或，多条件只要满足一个
not:非
g、外键约束：(foreign key references 表名(列名))
外键：指的是某个列在一张表中是主键，在另一张表不是主键列，那叫外键。
外键：体现表与表之间的关系。
外键的数据只能来源于主键表出现过的数据；
外键允许null值。
10、创建 数据库 数据表
1、
--创建数据库SchoolDB create database SchoolDB go --切换到SchoolDB use SchoolDB go --创建学生信息表StudentInfo create table StudentInfo ( --列名 数据类型 [约束] StuId int, --学号:整型 StuName varchar(50), -- 姓名:字符型，变长 Gender char(2), --性别:字符型，定长 Birthday datetime, --生日 Phone char(11), --手机号 [Address] varchar(100) --家庭住址 ) go
2、
--创建数据库并切换 create database BookDB go use BookDB go --建表：图书类型表 create table TypeInfo ( TypeId int primary key, --主键:primary key,值不能重复、不允许空值 TypeName varchar(50) unique not null --唯一：unique,值不能重复，允许出现一个空值 ) go --图书信息表 create table BookInfo ( BookID int primary key identity(1000,1), --identity(1000,1)：从1000开始自增，每次增加1 BookName varchar(50) not null, --非空：not null Author varchar(50) null, --允许空：可以不写，或者写null Price money check(Price>=0), --检查约束：check(条件) Pub varchar(50) default '南方IT', --默认值:default 值 Remark varchar(max) null, TypeId int foreign key references TypeInfo(TypeId) --类型编号：外键(值允许重复，允许空， 如果要写值，那么就要填对应主键表中出 现过的值) ) go
11、数据的功能
1）添加
[ɪnˈsɜːt ]
1、用insert 语句来实现添加
基本语法：insert [into] 表名[(列名1,列名2,....)]
values(值1,值2,....)
go
2、增加数据：
1)insert into ...values语句
into 可以省略，默认给所有列添加数据
自增列不能给数据
default表示使用默认值
getdate()默认当前时间
null表示空值
一次性添加多条数据
如果要指定列：自增列不能写，非空列一定要写！！
用insert 语句来实现添加
基本语法：insert [into] 表名[(列名1,列名2,....)]
values(值1,值2,....)
go
--默认给所有列添加数据
insert into StudentInfo
values(1,'tom',18),
(2,'jack',10)
go
--一次性添加多条数据
insert into TypeInfo
values(2,'科幻'),
(3,'玄幻'),
(4,'言情')
go
--如果要指定列：自增列不能写，非空列一定要写！！
insert into BookInfo(BookName)
values('小林传奇')
go
insert into BookInfo(BookName,Author,Price)
values('小莫总裁','小秦',100)
go
3、insert into ...select语句实现添加
union连接下一条数据
default关键字不能使用
insert into TypeInfo
select 5,'历史' union --union连接下一条数据
select 6,'悬疑' union
select 7,'计算机'
go
insert into StudentInfo
select 1,'tom',18 union
select 2,'jack',10
go
[sɪˈlekt]
在insert...select中，default关键字不能使用
insert into BookInfo
select '计算机基础','张三',9.9,'清华大学',2008,null,1
go
2）备份
可以用insert..select语句实现数据备份: --从一张表查询数据出来放入另一张表备份起来 --先创建备份表: create table TypeInfo_bak --图书类型的备份表 ( TypeId int primary key, TypeName varchar(50) unique not null ) go --把图书类型表TypeInfo数据备份到TypeInfo_bak中 insert into TypeInfo_bak select * from TypeInfo go select....into语句：实现备份 --把所有图书查询出来，备份到新表BookInfo_bak中 select * into BookInfo_bak --备份表（自动创建出来） from BookInfo
3）修改
1、修改数据
基本语法：update 表名
set 列名1=值1,列名2=值2,....
[where 条件]
go
加where语句，定位到具体的行
不加where，整列值都改成一样的了
同时修改多列:自增列不允许修改
修改后的数据也要满足约束
--加where语句，定位到具体的行
--把类型编号为1的类型名称改成编程
update TypeInfo
set TypeName='编程'
where TypeId=1
go
update studentInfo
set name='小明',age=30
where sid=1
go
--不加where，整列值都改成一样的了
update BookInfo
set Author='李四',Price=10
go
--同时修改多列:自增列不允许修改
--修改后的数据也要满足约束
update TypeInfo
set TypeID=10,TypeName='COMPUTER'
where TypeID=7
go
update BookInfo
set BookName='SQL SERVER',Author='王 五',Price*=1.2,Pub='NF'
where BookID=1005
go
--修改主键表主键列的值：
--如果外键有引用的时候，不能直接修改
--怎么办？先把外键引用的地方修改成null（去除引用）
--再去修改主键表主键列的值
--第一步:先把外键引用设置成null
update BookInfo
set TypeId=null --外键设置成null
where TypeId=1
go
--第二步：再把主键表主键进行修改
update TypeInfo
set TypeId=8
where TypeId=1
go
4）删除
[dɪˈliːt]
1、删除数据：delete [from] 表名
[where 条件]
go
不加条件，删除所有数据（from 可以省略）
删除某些数据
delete from studentInfo
where sid=1
go
--不加条件，删除所有数据（from 可以省略）
delete from BookInfo
go
--删除某些数据
delete from BookInfo
where BookId=1008
go
--删除所有数据（清空表）
[trʌŋˈkeɪt][ˈteɪbl]
truncate table 表名
go
truncate table TypeInfo_bak
go
--删除表：先删外键表，再删主键表
drop table BookInfo
go
drop table TypeInfo
go
--要删除主键被引用的数据？
--先把外键修改成NULL
update BookInfo
set TypeId=null
where TypeID=10
go
delete from TypeInfo
where typeId=10
go
5）查询
一、SQL：结构化查询语言，分成4类
1）数据操纵语言：DML
包含insert、update、delete、select
2）数据定义语言：DDL
包含create、alter、drop、truncate
create database
alter database xxx...
drop database ...
create table
alter table
drop table ...
truncate table xxx 清空表
3) 数据控制语言：DCL
包含grant、revoke（权限的赋予和移除）
4）事务控制语言：TCL
包含commit、rollback
二、SQL的应用方式：
交互式SQL：现在在服务器用SQL完成各种操作
嵌入式SQL：嵌入计算机编程语言中使用
1、单表查询数据：
查询语句的语法：
select 列名 from 表名
[where 条件表达式] --数据筛选
[ɡruːp]
[group by 分组依据] --分组查询
[having 条件表达式] --分组后的筛选
[order by 排序依据] --排序（放最后）
在where子句中，写条件常用到的运算符：
1）关系运算符：
>、>=、<、<=、=（等于）、!=或<>（不等于）
2）逻辑运算符：
and:与
or：或
not：非，常用的有：
not null(非空)
not between ... and...: 不在两者之间
not in(...):不匹配里面出现的值
not like:不像...一样
3）特殊的匹配运算符：
between...and...:两者之间
in(值1,值2,....):匹配()中任意一个值即可
比如:检查约束
check(sex in ('男','女'))
like:像...一样,用于模糊查询。
比如：要找出姓李的所有同学
包含一个“小”字的同学
要配合通配符使用:
1)%:匹配任意个字符：
eg：
%a%:可以匹配a，xay,ab,abcd,xxxxayyyy
2)_：匹配一个字符
eg:
a_:可以匹配：ab、ac、a1
不可以匹配：abc,a1234234
3)[]: 匹配[]出现的任意一个字符
eg：
a[xyz]可以匹配：ax,ay,az
其它的都不能匹配 aa
a[A-Za-z]:第二个位置可以出现大小写字母
a[^xyz]:除了xyz以外都匹配
2、查询语句
--1）查询全部列的数据，*代表所有列
select * from ClassInfo
--2）查询指定列的数据，写出列名
select Cid,Grade from ClassInfo
--3) 给列指定别名
select Cid as 班级编号,Grade as 年级 -- as取别名,as可以省略
from ClassInfo
select 班级编号=Cid,年级=Grade -- = 取别名，别名在前
from ClassInfo
--4）查询前n条数据:top n/top n percent
select top 2 * from ClassInfo --前2条数据
[pəˈsɛnt]
select top 50 percent * from ClassInfo --前50%的数 据
[dɪˈstɪŋkt]
--5）去掉重复项：distinct
select distinct Gender from StudentInfo
--where子句：在查询中，用于数据的筛选
select * from StudentInfo
--where Sid=1 --等于
--where Sid!=1 --不等于
--where Sid<>1 --不等于
--where Sid>=2 and Sid<=5 --and：并且
--where Sid between 2 and 5 --学号在2到5之间，跟Sid>=2 and Sid<=5 一样
--where Sid=1 or Sid=3 --or：或者
--where Sid in(1,3) --学号匹配1或3都可以，跟Sid=1 or Sid=3
--where Phone is null --空与非空不能用=，要用is
--where Phone is not null
go
3、 --模糊查询
select * from StudentInfo
--where Sname like '张%' --找出所有姓张的，以张开头
--where Sname like '%三%' --包含三的学生
--where Sname not like '%三%' --不包含三的
--where Address like '%斗门' --以斗门结尾
--where Sname like '张_' --下划线匹配单个字符
go
6）排序
--排序
select * from StudentInfo
--order by Sname --默认升序
--order by Sname asc --asc表示升序
--order by Sname desc --desc表示降序
order by Gender desc,Sid asc --按多列排序，先按性别，再按学号
go
7）聚合函数
1、--聚合函数：
[sʌm]
select sum(Age) 年龄和 from StudentInfo
select avg(Age) 年龄平均 from StudentInfo
[mæks]
select max(Age) 最大年龄 from StudentInfo
select min(Age) 最小年龄 from StudentInfo
[kaʊnt]
select count(*) 学生人数 from StudentInfo
2、 SQL中常见的聚合函数：用于统计的函数
sum():求和
avg():平均值
max():最大值
min():最小值
count():计数
8）统计
--分组查询：group by子句，分组经常要配合聚合函数一起使用
--按照性别分组，统计男生、女生的人数
select Gender 性别,COUNT(*) 人数
from StudentInfo
group by Gender --按照性别分组
go
--分组时，select后只能跟两种列：
--1）group by后出现的列（用于分组的列）
--2）聚合函数列 （用于统计的列）
--按照班级分组，统计每个班有多少人
select Cid 班级编号,COUNT(*) 人数
from StudentInfo
group by Cid
go
--分组之后的筛选：having
select Cid 班级编号,COUNT(*) 人数
from StudentInfo
group by Cid
--having Cid is not null --一定要用在group by后
having COUNT(*)>1
go
--全部子句用上的案例：
select Address 地区,COUNT(*) 人数
from StudentInfo
where Sid>1
group by Address
having COUNT(*)>=1
order by 人数 desc --排序可以使用别名
go
9）基本连接查询
--基本连接查询:返回两张表匹配的数据
select Sid,Sname,B.Cid,Grade,Term --所需要的列
from ClassInfo A,StudentInfo B --给表取别名
where A.Cid=B.Cid --连接条件：主外键的值相等
go
10）内连接
--内连接：返回两张表匹配的数据
--关键字：inner join ... on
select Sid,Sname,B.Cid,Grade,Term
from StudentInfo A inner join ClassInfo B --给表取别名
on A.Cid=B.Cid --连接条件：主外键的值相等
go
11）外连接
--外连接：
--左外连接：返回两张表匹配的数据，
--还可以返回左表有，右表没有的数据（不匹配的数据， 会在右表的列用NULL替代）
-- 关键字：left [outer] join ...on：outer可以省略
select A.*,B.* from StudentInfo A --学生表是左表
left join ClassInfo B --班级表是右表
on A.Cid=B.Cid
--where A.Cid is null --可以查询哪些学生没有被分配班级
go
select * from ClassInfo A --班级表是左表
left join StudentInfo B --学生表是右表
on A.Cid=B.Cid
--where Sid is null --找出哪些班级没有学生
go
--右外连接：返回两张表匹配的数据，
--还可以返回右表有，左表没有的数据（不匹配的数据， 会在左表的列用NULL替代）
select * from StudentInfo A --学生表是左表
right join ClassInfo B --班级表是右表
on A.Cid=B.Cid
go
--左外和右外下面写法是等价的
--select * from A left join B on ....
--select * from B right join A on ....
--全外连接：完整外连接
--全外=左外+右外
select * from StudentInfo A
left join ClassInfo B
on A.Cid=B.Cid
go
select * from StudentInfo A
[dʒɔɪn]
right join ClassInfo B
on A.Cid=B.Cid
go
select * from StudentInfo A
[fʊl][dʒɔɪn]
full join ClassInfo B
on A.Cid=B.Cid
go
12、数据综合
1）增加、修改、删除、查询、单表查询、聚合函数
1、增加数据：
1)insert into ...values语句
insert into StudentInfo
values(1,'tom',18),
(2,'jack',10)
go
2)insert into ...select语句
insert into StudentInfo
select 1,'tom',18 union
select 2,'jack',10
go
3)select into 语句：生成一张表实现备份
select *
into student_BAK
from studentInfo
2、修改数据：
update 表名
set 列1=值1,列2=值2,....
where 条件
go
update studentInfo
set name='小明',age=30
where sid=1
go
3、删除数据：
delete from 表名
where 条件
go
delete from studentInfo
where sid=1
go
第四章 查询
1、SQL：结构化查询语言，分成4类
1）数据操纵语言：DML
包含insert、update、delete、select
2）数据定义语言：DDL
包含create、alter、drop、truncate
create database
alter database xxx...
drop database ...
create table
alter table
drop table ...
truncate table xxx 清空表
3) 数据控制语言：DCL
包含grant、revoke（权限的赋予和移除）
4）事务控制语言：TCL
包含commit、rollback
2、SQL的应用方式：
交互式SQL：现在在服务器用SQL完成各种操作
嵌入式SQL：嵌入计算机编程语言中使用
3、单表查询数据：
查询语句的语法：
select 列名 from 表名
[where 条件表达式] --数据筛选
[group by 分组依据] --分组查询
[having 条件表达式] --分组后的筛选
[order by 排序依据] --排序（放最后）
在where子句中，写条件常用到的运算符：
1）关系运算符：
>、>=、<、<=、=（等于）、!=或<>（不等于）
2）逻辑运算符：
and:与
or：或
not：非，常用的有：
not null(非空)
not between ... and...: 不在两者之间
not in(...):不匹配里面出现的值
not like:不像...一样
3）特殊的匹配运算符：
between...and...:两者之间
in(值1,值2,....):匹配()中任意一个值即可
比如:检查约束
check(sex in ('男','女'))
like:像...一样,用于模糊查询。
比如：要找出姓李的所有同学
包含一个“小”字的同学
要配合通配符使用:
1)%:匹配任意个字符：
eg：
%a%:可以匹配a，xay,ab,abcd,xxxxayyyy
2)_：匹配一个字符
eg:
a_:可以匹配：ab、ac、a1
不可以匹配：abc,a1234234
3)[]: 匹配[]出现的任意一个字符
eg：
a[xyz]可以匹配：ax,ay,az
其它的都不能匹配 aa
a[A-Za-z]:第二个位置可以出现大小写字母
a[^xyz]:除了xyz以外都匹配
4、SQL中常见的聚合函数：用于统计的函数
sum():求和
avg():平均值
max():最大值
min():最小值
count():计数
2）删除、添加、备份、查询、修改、删除、查询
--创建数据库并切换
create database BookDB
go
use BookDB
go
--建表：图书类型表
create table TypeInfo
(
TypeId int primary key, --主键:primary key,值不能重复、不允许空值
TypeName varchar(50) unique not null --唯一： unique,值不能重复，允许出现一个空值
)
go
--图书信息表
create table BookInfo
(
BookID int primary key identity(1000,1), --identity(1000,1)：从1000开始自增，每次增加1
BookName varchar(50) not null, --非空：not null
Author varchar(50) null, --允许空：可以不写，或者写null
Price money check(Price>=0), --检查约束：check(条件)
Pub varchar(50) default '南方IT', --默认值:default 值
PubYear int,
Remark varchar(max) null,
TypeId int foreign key references TypeInfo(TypeId) --类型编号：外键(值允许重复，允许空， 如果要写值，那么就要填对应主键表中出 现过的值)
)
go
--删除表：先删外键表，再删主键表
drop table BookInfo
go
drop table TypeInfo
go
---数据操作：添加、修改和删除
--添加数据：
--1）用insert 语句来实现添加
-- 基本语法：insert [into] 表名[(列名1,列名2,....)]
-- values(值1,值2,....)
go
--into 可以省略，默认给所有列添加数据
insert into TypeInfo
values(1,'软件开发')
go
--一次性添加多条数据
insert into TypeInfo
values(2,'科幻'),
(3,'玄幻'),
(4,'言情')
go
--自增列不能给数据
--default表示使用默认值
--null表示空值
insert into BookInfo
values('计算机基础','张三',9.9,default,2008,null,1)
go
insert into BookInfo
values('SQL','张三',19.9,'清华大学出版社',2021,'这是一本好书',1),
('CS架构','张三',39.9,'清华大学出版社',2020,'张三威武',2)
go
--如果要指定列：自增列不能写，非空列一定要写！！
insert into BookInfo(BookName)
values('小林传奇')
go
insert into BookInfo(BookName,Author,Price)
values('小莫总裁','小秦',100)
go
--2、insert...select 语句实现添加
insert into TypeInfo
select 5,'历史' union --union连接下一条数据
select 6,'悬疑' union
select 7,'计算机'
go
--在insert...select中，default关键字不能使用
insert into BookInfo
select '计算机基础','张三',9.9,'清华大学',2008,null,1
go
--可以用insert..select语句实现数据备份:
--从一张表查询数据出来放入另一张表备份起来
--先创建备份表:
create table TypeInfo_bak --图书类型的备份表
(
TypeId int primary key,
TypeName varchar(50) unique not null
)
go
--把图书类型表TypeInfo数据备份到TypeInfo_bak中
insert into TypeInfo_bak
select * from TypeInfo
go
--3)select....into语句：实现备份
--把所有图书查询出来，备份到新表BookInfo_bak中
select *
into BookInfo_bak --备份表（自动创建出来）
from BookInfo
--查询所有数据
select * from TypeInfo
select * from BookInfo
select * from TypeInfo_bak
--2、修改数据
--基本语法：update 表名
-- set 列名1=值1,列名2=值2,....
-- [where 条件]
go
--不加where，整列值都改成一样的了
update BookInfo
set Author='李四',Price=10
go
--加where语句，定位到具体的行
--把类型编号为1的类型名称改成编程
update TypeInfo
set TypeName='编程'
where TypeId=1
go
--同时修改多列:自增列不允许修改
--修改后的数据也要满足约束
update TypeInfo
set TypeID=10,TypeName='COMPUTER'
where TypeID=7
go
update BookInfo
set BookName='SQL SERVER',Author='王五',Price*=1.2,Pub='NF'
where BookID=1005
go
--编号1000或1003的图书涨价20%
update BookInfo
set Price*=1.2
where BookID=1000 or BookID=1003
go
--写条件时，常以主键列作为条件，但也可以用其它列
update BookInfo
set Price*=1.2
where BookName='SQL' --书名作为条件
go
--修改主键表主键列的值：
--如果外键有引用的时候，不能直接修改
--怎么办？先把外键引用的地方修改成null（去除引用）
--再去修改主键表主键列的值
--第一步:先把外键引用设置成null
update BookInfo
set TypeId=null --外键设置成null
where TypeId=1
go
--第二步：再把主键表主键进行修改
update TypeInfo
set TypeId=8
where TypeId=1
go
--3、删除数据：delete [from] 表名
[where 条件]
go
--不加条件，删除所有数据（from 可以省略）
delete from BookInfo
go
--删除某些数据
delete from BookInfo
where BookId=1008
go
--要删除主键被引用的数据？
--先把外键修改成NULL
update BookInfo
set TypeId=null
where TypeID=10
go
delete from TypeInfo
where typeId=10
go
--清空表：truncate table 表名
go
--删除所有数据
truncate table TypeInfo_bak
go
--查询
select * from TypeInfo
select * from BookInfo
3）修改、删除、查询、模糊查询、排序、聚合函数、分组查询
/*
小学生的成绩管理数据库:
班级表：班级编号PK、所在年级、学期
学生表：学号PK、姓名、性别、联系电话、住址、班级编号FK
课程表：课程编号PK、课程名称、备注
成绩表：成绩编号PK、学号FK、课程编号FK、考试成绩、考试时间
*/
create database ScoreDB --创建数据库
go
use ScoreDB --切换数据库
go
--班级表
create table ClassInfo
(
Cid int primary key, --班级编号:主键
Grade varchar(50) not null, --年级
Term varchar(50) not null --学期
)
go
insert into ClassInfo
values(2101,'一年级','上'),
(2102,'一年级','上'),
(2103,'一年级','上')
go
--学生表
create table StudentInfo
(
[Sid] int identity(1,1) primary key, --学号：主键、自增
Sname varchar(50) not null, --姓名:非空
Gender char(2) default '男' check(Gender='男' or Gender='女'), --性别:默认、检查
Age int, --年龄
--SchoolTime datetime default getdate(), --默认当前时间
Phone varchar(50), --联系电话
[Address] varchar(500), --地址
Cid int foreign key references ClassInfo(Cid) --班级编号：外键
)
go
insert into StudentInfo
values('张三疯','男',10,null,'珠海斗门',null),
('李四','男',20,'1568798961','珠海斗门',2101),
('王五','女',30,'1000','广州',2101),
('赵六','男',15,null,'深圳',2102),
('小黄','女',18,null,'珠海香洲',2102)
go
--修改数据
update ClassInfo
set Grade='二年级',Term='下'
where Cid=2102
go
--如果修改主键，且有被外键引用
--那么先把外键改成null，再修改主键
update StudentInfo
set Cid=null
where Cid=2101
go
update ClassInfo
set Cid=2201
where Cid=2101
go
--删除数据
delete from StudentInfo
where Sid=5
go
--查询
select * from ClassInfo
select * from StudentInfo
--查询语句
--1）查询全部列的数据，*代表所有列
select * from ClassInfo
--2）查询指定列的数据，写出列名
select Cid,Grade from ClassInfo
--3) 给列指定别名
select Cid as 班级编号,Grade as 年级 -- as取别名,as可以省略
from ClassInfo
select 班级编号=Cid,年级=Grade -- =取别名，别名在前
from ClassInfo
--4）查询前n条数据:top n/top n percent
select top 2 * from ClassInfo --前2条数据
select top 50 percent * from ClassInfo --前50%的数据
--5）去掉重复项：distinct
select distinct Gender from StudentInfo
--where子句：在查询中，用于数据的筛选
select * from StudentInfo
--where Sid=1 --等于
--where Sid!=1 --不等于
--where Sid<>1 --不等于
--where Sid>=2 and Sid<=5 --and：并且
--where Sid between 2 and 5 --学号在2到5之间，跟Sid>=2 and Sid<=5 一样
--where Sid=1 or Sid=3 --or：或者
--where Sid in(1,3) --学号匹配1或3都可以，跟Sid=1 or Sid=3
--where Phone is null --空与非空不能用=，要用is
--where Phone is not null
go
--模糊查询
select * from StudentInfo
--where Sname like '张%' --找出所有姓张的，以张开头
--where Sname like '%三%' --包含三的学生
--where Sname not like '%三%' --不包含三的
--where Address like '%斗门' --以斗门结尾
--where Sname like '张_' --下划线匹配单个字符
go
--排序
select * from StudentInfo
--order by Sname --默认升序
--order by Sname asc --asc表示升序
--order by Sname desc --desc表示降序
order by Gender desc,Sid asc --按多列排序，先按性别，再按学号
go
---聚合函数：
select sum(Age) 年龄和 from StudentInfo
select avg(Age) 年龄平均 from StudentInfo
select max(Age) 最大年龄 from StudentInfo
select min(Age) 最小年龄 from StudentInfo
select count(*) 学生人数 from StudentInfo
--分组查询：group by子句，分组经常要配合聚合函数一起使用
--按照性别分组，统计男生、女生的人数
select Gender 性别,COUNT(*) 人数
from StudentInfo
group by Gender --按照性别分组
go
--分组时，select后只能跟两种列：
--1）group by后出现的列（用于分组的列）
--2）聚合函数列 （用于统计的列）
--按照班级分组，统计每个班有多少人
select Cid 班级编号,COUNT(*) 人数
from StudentInfo
group by Cid
go
--分组之后的筛选：having
select Cid 班级编号,COUNT(*) 人数
from StudentInfo
group by Cid
--having Cid is not null --一定要用在group by后
having COUNT(*)>1
go
--全部子句用上的案例：
select Address 地区,COUNT(*) 人数
from StudentInfo
where Sid>1
group by Address
having COUNT(*)>=1
order by 人数 desc --排序可以使用别名
go
4）添加、查询
--创建数据库NewsDB
create database NewsDB
go
--切换
use NewsDB
go
--创建管理新闻类型表NewsInof
create table NewsType
(
TypeId int primary key identity(1,1),--类型编号:主键、自增
TypeName varchar(50)unique not null --类型名称:唯一、非空
)
go
--创建新闻信息表News
create table News
(
Nid int primary key, --新闻编号:主键
Title varchar(50) not null, --新闻标题:非空
Author varchar(50), --作者
PubTime datetime default getdate(), --发布时间:默认当前时间
Content text, --新闻内容：文本型
Ncount int check(Ncount>0), --新闻字数: 整型，字数必须大于0
TypeId int foreign key references NewsType(TypeId)--所属新闻类型：外键
)
go
--给每张表添加5条以上测试数据
--1）添加NewsType表
insert into NewsType
select '电竞'union
select '中国是'union
select '电影'union
select '股票'union
select '国务'
go
select*from NewsType
--添加News表
insert into News
values(1001,'王者荣耀','张三',default,null,1520,1),
(1002,'《长津湖》','吴京',2020,'方法广东夫妇',4653,3),
(1003,'XXX绯闻','李四',2021,'高富帅',5456,2),
(1004,'发货','张三',2021,'新时代',34423,5),
(1005,'中国','王五',2019,null,1999,3)
go
select*from News
--1)查询所有新闻类型，分别用as和=给列取中文别名；
select TypeId 类型编号,TypeName 类型名称
from NewsType
go
select 类型编号=TypeId ,类型名称=TypeName
from NewsType
go
-- 2)查询前2条新闻类型的数据,查询前20%新闻类型的数据
select top 2 * from NewsType
go
select top 20 percent * from NewsType
go
--3)查询作者"张三"发布的所有新闻
select * from News
where Author='张三'
go
--4)查询新闻字数在5000到10000字之间的新闻信息：分别用and或between...and实现
select * from News
where Ncount>=5000 and Ncount<=10000
go
select * from News
where Ncount between 5000 and 10000
go
--5)查询新闻类型分别是1,3,5的新闻信息：分别用or或in()实现
select * from News
where typeId=1 or typeId=3 or typeId=5
go
select * from News
where typeId in(1,3,5)
go
-- 6)查询新闻标题包含"中国"的新闻信息
select * from News
where Title like '%中国%'
go
--7)查询新闻内容包含"新时代"的新闻信息
select * from News
where Content like '%新时代%'
go
--8)查询所有新闻信息，按照新闻字数降序排序
select * from News
order by Ncount desc --asc升序，desc降序
go
-- 9)查询所有新闻中，最多字数、最少字数、平均字数、字数之和分别是多少。
select SUM(Ncount) 字数和,AVG(Ncount) 平均字数,MAX(Ncount) 最多字数,MIN(Ncount) 最少字数
from News
go
--10)查询总共有多少条新闻信息
select COUNT(*) 新闻数量 from News
go
--11)查询作者"张三"发布了多少条新闻信息
select COUNT(*) 新闻数量 from News
where Author='张三'
go
--12)按照作者分组，统计每个作者发布的新闻数量
select Author 作者,COUNT(*) 数量
from News
group by Author
--having COUNT(*)>1 --分组后的筛选
go
--13)按照新闻类型分组，统计每种类型新闻的平均字数
select TypeId 类型,AVG(Ncount) 平均字数
from News
group by TypeID
go
5）基本连接查询、内连接、外连接（左外连接、右外连接、全外连接）
--切换 use ScoreDB go select * from ClassInfo select * from StudentInfo --基本连接查询:返回两张表匹配的数据 select Sid,Sname,B.Cid,Grade,Term --所需要的列 from ClassInfo A,StudentInfo B --给表取别名 where A.Cid=B.Cid --连接条件：主外键的值相等 go --内连接：返回两张表匹配的数据 --关键字：inner join ... on select Sid,Sname,B.Cid,Grade,Term from StudentInfo A inner join ClassInfo B --给表取别名 on A.Cid=B.Cid --连接条件：主外键的值相等 go --外连接： --左外连接：返回两张表匹配的数据， --还可以返回左表有，右表没有的数据（不匹配的数据， 会在右表的列用NULL替代） -- 关键字：left [outer] join ...on：outer可以省略 select A.*,B.* from StudentInfo A --学生表是左表 left join ClassInfo B --班级表是右表 on A.Cid=B.Cid --where A.Cid is null --可以查询哪些学生没有被分配班级 go select * from ClassInfo A --班级表是左表 left join StudentInfo B --学生表是右表 on A.Cid=B.Cid --where Sid is null --找出哪些班级没有学生 go --右外连接：返回两张表匹配的数据， --还可以返回右表有，左表没有的数据（不匹配的数据， 会在左表的列用NULL替代） select * from StudentInfo A --学生表是左表 right join ClassInfo B --班级表是右表 on A.Cid=B.Cid go --左外和右外下面写法是等价的 --select * from A left join B on .... --select * from B right join A on .... --全外连接：完整外连接 --全外=左外+右外 select * from StudentInfo A left join ClassInfo B on A.Cid=B.Cid go select * from StudentInfo A right join ClassInfo B on A.Cid=B.Cid go select * from StudentInfo A full join ClassInfo B on A.Cid=B.Cid go
===================================================================
6、数据库代码操作：
--SQL脚本语言:不区分大小写
/*
数据库管理的
代码操作！
*/
--1）、创建数据库：所有设置默认
[kriˈeɪt]
create database StudentDB
go
--2）、切换数据库：在使用某个数据库前，要切换到它
use StudentDB
go
--3）、重命名数据库（了解）
[ˈɔːltə(r)]
alter database StudentDB
[ˈmɒdɪfaɪ]
modify name=NFStudentDB --新名称
go
--4）、删除数据库：没有在使用时才能删除
[drɒp]
drop database NFStudentDB
go
--5）、分离数据库：移动数据库文件之前一定要分离
[ɪɡˈzek][dɪˈtætʃ]
exec sp_detach_db StudentDB
go
--6）、附加数据库：把数据库文件重新加入服务器中
[ɪɡˈzek][əˈtætʃ]
exec sp_attach_db StudentDB,'D:\StudentDB.mdf'
go
类型
1、整型：int、bigint、smallint、tinyint（0-255）、bit(0和1)
2、浮点型：float/real、numeric(18,2)/decimal(18,2)
3、字符型：char(50)、varchar(50)、nchar(50)、nvarchar(50)
4、文本型：text、ntext
5、日期时间：datetime、date(只有日期)、smalldatetime
6、货币：money、smallmoney
7、图像：image(二进制)
8、二进制:binary(100)、varbinary(100)
1、创建了数据库后，就可以考虑往里面存储数据了。
2、数据库中把数据保存在数据表中，保存前，要创建数据表。
3、建表前，要考虑数据的类型。常见的数据类型有：
[bɪɡ] [smɔːl] [ˈtaɪni]
1)整型：int、bigint、smallint、tinyint(0~255)、
[bɪt] [fɔːls]
bit(位，表示0或1，相当于false或true)
[fləʊt] [ˈriːəl]
2)浮点型: float、real
[ˈdesɪml] [nju(ː)ˈmɛrɪk]
decimal(m,n)或numeric(m,n):一样的含义
eg:
decimal(10,2):10是总位数，2是小数位数
3)字符型:
[tʃɑː(r)]
a、char(n): n表示最多能存储多少个字符,
n范围1~8000.表示定长的字符数据。适用于长度固定的字 符数据。
eg:
char(10):最多存10个字符的数据。
存了'hello'，5个字符，占了5个位置。
剩余的位置用空格补齐。事实上，真正存的是'hello '。
[var]
b、varchar(n)：表示变长的字符数据。
eg:
varchar(10):最多存10个字符的数据。
存了'hello'，5个字符，占了5个位置。
剩余的位置不会用空格补齐。真正存的是'hello'。
c、nchar(n)、nvarchar(n):表示国际化，前面的n表示使
[kəʊd]
用 Unicode编码。
4)日期时间类型：
[deɪt]
datetime:范围更大
smalldatetime:1900~2079范围小
5)货币类型：存货币值，比如：￥100,$100
money:范围更大
smallmoney:范围小
6)文本类型: 存大量的文本数据。
text：文本
ntext:国际化
7)图像类型:存储图片数据。(了解)
image
8)二进制类型:存二进制数据
[ˈbaɪnəri]
binary(n):固定长度，n表示字节数
[var]
varbinary(n):可变长度
nbinary(n),nvarbinary(n):国际化
4、创建数据表：一种事物的数据用一张表存。
比如：学生、老师、班级数据用3张表。
表名: dbo.StudentInfo
[ˈəʊnə(r)]
dbo:database owner 数据库所有者
约束
[ˈpraɪməri][kiː]
1)主键约束(primary key)：目的要保证表中的每一条数据都是唯一的。
不会出现两条一模一样的数据。
(数据冗余：出现重复的数据！如果设置了主键，减少数据冗余。)
一般选择编号这样的列作为主键列（加主键约束）。
主键列的值不能出现重复值和空值（null）。
[aɪˈdentəti]
2)自增(identity(初值,每次增加多少))：自动增长（标识列），由系统给出值，用户不能修改。
标识种子：初值
标识增量：每次增加多少
注意：只有整型的列才能设置自增。
一张表只能有1个自增列。
[nʌl]
3)非空约束(not null)：不允许有null值。
[dɪˈfɔːlt]
4)默认值约束(default 值): 当某列有默认值，输入 时没有填写数据，会自动取值为默认值。
datetime default getdate()--默认当前时间
[juˈniːk]
5)唯一约束(unique): 不允许出现重复值，允许出现一个null值。
比如身份证号是唯一的。
[tʃek]
6)检查约束(check(条件)): 限制数据的取值范围。
[tʃek][lɛn]
check(len(Code)=18)--长度必须18位
比如：性别只能是男或女
check(列名='男' or 列名='女')
成绩在0到100之间
check(列名>=0 and 列名<=100)
写条件表达式需要用到运算符：
a、关系运算符：
>、>=、<、<=、=（等于）、!=(不等于)、<>(不等于)
b、逻辑运算符：
and：与，多条件同时成立
or：或，多条件只要满足一个
not:非
[ˈfɒrən] [ˈrefrənsɪz]
7)外键约束(foreign key references 表名(列名))：
外键：指的是某个列在一张表中是主键，在另一张表不是主键列，那叫外键。
外键：体现表与表之间的关系。
外键的数据只能来源于主键表出现过的数据；
外键允许null值。
1、创建数据库
--1）、创建数据库：所有设置默认
create database StudentDB
go
2、切换数据库
--2）、切换数据库：在使用某个数据库前，要切换到它
use StudentDB
go
3、创建数据表
--创建员工表
create table EmpInfo
(
Eid int, --工号
Ename varchar(50), --姓名
Sex char(2), --性别
JoinTime datetime, --入职日期
WorkYears int, --工作年限
Postion varchar(50), --职位
BaseMoney money, --基本工资
Remark text --简介
)
go
4、添加
[ɪnˈsɜːt ]
1）用insert 语句来实现添加
基本语法：insert [into] 表名[(列名1,列名2,....)]
values(值1,值2,....)
go
1、增加数据：
1)insert into ...values语句
into 可以省略，默认给所有列添加数据
自增列不能给数据
default表示使用默认值
getdate()默认当前时间
null表示空值
一次性添加多条数据
如果要指定列：自增列不能写，非空列一定要写！！
用insert 语句来实现添加
基本语法：insert [into] 表名[(列名1,列名2,....)]
values(值1,值2,....)
go
--默认给所有列添加数据
insert into StudentInfo
values(1,'tom',18),
(2,'jack',10)
go
--一次性添加多条数据
insert into TypeInfo
values(2,'科幻'),
(3,'玄幻'),
(4,'言情')
go
--如果要指定列：自增列不能写，非空列一定要写！！
insert into BookInfo(BookName)
values('小林传奇')
go
insert into BookInfo(BookName,Author,Price)
values('小莫总裁','小秦',100)
go
2)insert into ...select语句实现添加
union连接下一条数据
default关键字不能使用
insert into TypeInfo
select 5,'历史' union --union连接下一条数据
select 6,'悬疑' union
select 7,'计算机'
go
insert into StudentInfo
select 1,'tom',18 union
select 2,'jack',10
go
[sɪˈlekt]
在insert...select中，default关键字不能使用
insert into BookInfo
select '计算机基础','张三',9.9,'清华大学',2008,null,1
go
5、备份
可以用insert..select语句实现数据备份:
--从一张表查询数据出来放入另一张表备份起来
--先创建备份表:
create table TypeInfo_bak --图书类型的备份表
(
TypeId int primary key,
TypeName varchar(50) unique not null
)
go
--把图书类型表TypeInfo数据备份到TypeInfo_bak中
insert into TypeInfo_bak
select * from TypeInfo
go
select....into语句：实现备份
--把所有图书查询出来，备份到新表BookInfo_bak中
select *
into BookInfo_bak --备份表（自动创建出来）
from BookInfo
6、修改
2、修改数据
基本语法：update 表名
set 列名1=值1,列名2=值2,....
[where 条件]
go
加where语句，定位到具体的行
不加where，整列值都改成一样的了
同时修改多列:自增列不允许修改
修改后的数据也要满足约束
--加where语句，定位到具体的行
--把类型编号为1的类型名称改成编程
update TypeInfo
set TypeName='编程'
where TypeId=1
go
update studentInfo
set name='小明',age=30
where sid=1
go
--不加where，整列值都改成一样的了
update BookInfo
set Author='李四',Price=10
go
--同时修改多列:自增列不允许修改
--修改后的数据也要满足约束
update TypeInfo
set TypeID=10,TypeName='COMPUTER'
where TypeID=7
go
update BookInfo
set BookName='SQL SERVER',Author='王 五',Price*=1.2,Pub='NF'
where BookID=1005
go
--修改主键表主键列的值：
--如果外键有引用的时候，不能直接修改
--怎么办？先把外键引用的地方修改成null（去除引用）
--再去修改主键表主键列的值
--第一步:先把外键引用设置成null
update BookInfo
set TypeId=null --外键设置成null
where TypeId=1
go
--第二步：再把主键表主键进行修改
update TypeInfo
set TypeId=8
where TypeId=1
go
7、删除
[dɪˈliːt]
3、删除数据：delete [from] 表名
[where 条件]
go
不加条件，删除所有数据（from 可以省略）
删除某些数据
delete from studentInfo
where sid=1
go
--不加条件，删除所有数据（from 可以省略）
delete from BookInfo
go
--删除某些数据
delete from BookInfo
where BookId=1008
go
--删除所有数据（清空表）
[trʌŋˈkeɪt][ˈteɪbl]
truncate table 表名
go
truncate table TypeInfo_bak
go
--删除表：先删外键表，再删主键表
drop table BookInfo
go
drop table TypeInfo
go
--要删除主键被引用的数据？
--先把外键修改成NULL
update BookInfo
set TypeId=null
where TypeID=10
go
delete from TypeInfo
where typeId=10
go
8、查询
3、单表查询数据：
查询语句的语法：
select 列名 from 表名
[where 条件表达式] --数据筛选
[ɡruːp]
[group by 分组依据] --分组查询
[having 条件表达式] --分组后的筛选
[order by 排序依据] --排序（放最后）
查询语句
--1）查询全部列的数据，*代表所有列
select * from ClassInfo
--2）查询指定列的数据，写出列名
select Cid,Grade from ClassInfo
--3) 给列指定别名
select Cid as 班级编号,Grade as 年级 -- as取别名,as 可以省略
from ClassInfo
select 班级编号=Cid,年级=Grade -- = 取别名，别名在前
from ClassInfo
--4）查询前n条数据:top n/top n percent
select top 2 * from ClassInfo --前2条数据
[pəˈsɛnt]
select top 50 percent * from ClassInfo --前50%的数 据
[dɪˈstɪŋkt]
--5）去掉重复项：distinct
select distinct Gender from StudentInfo
--where子句：在查询中，用于数据的筛选
select * from StudentInfo
--where Sid=1 --等于
--where Sid!=1 --不等于
--where Sid<>1 --不等于
--where Sid>=2 and Sid<=5 --and：并且
--where Sid between 2 and 5 --学号在2到5之间，跟 Sid>=2 and Sid<=5 一样
--where Sid=1 or Sid=3 --or：或者
--where Sid in(1,3) --学号匹配1或3都可以，跟 Sid=1 or Sid=3
--where Phone is null --空与非空不能用=，要用is
--where Phone is not null
go
--模糊查询
select * from StudentInfo
--where Sname like '张%' --找出所有姓张的，以张开头
--where Sname like '%三%' --包含三的学生
--where Sname not like '%三%' --不包含三的
--where Address like '%斗门' --以斗门结尾
--where Sname like '张_' --下划线匹配单个字符
go
9、排序
--排序
select * from StudentInfo
--order by Sname --默认升序
--order by Sname asc --asc表示升序
--order by Sname desc --desc表示降序
order by Gender desc,Sid asc --按多列排序，先按性别， 再按学号
go
10、聚合函数
--聚合函数：
[sʌm]
select sum(Age) 年龄和 from StudentInfo
select avg(Age) 年龄平均 from StudentInfo
[mæks]
select max(Age) 最大年龄 from StudentInfo
select min(Age) 最小年龄 from StudentInfo
[kaʊnt]
select count(*) 学生人数 from StudentInfo
11、统计
--分组查询：group by子句，分组经常要配合聚合函数一起使用
--按照性别分组，统计男生、女生的人数
select Gender 性别,COUNT(*) 人数
from StudentInfo
group by Gender --按照性别分组
go
--分组时，select后只能跟两种列：
--1）group by后出现的列（用于分组的列）
--2）聚合函数列 （用于统计的列）
--按照班级分组，统计每个班有多少人
select Cid 班级编号,COUNT(*) 人数
from StudentInfo
group by Cid
go
--分组之后的筛选：having
select Cid 班级编号,COUNT(*) 人数
from StudentInfo
group by Cid
--having Cid is not null --一定要用在group by后
having COUNT(*)>1
go
--全部子句用上的案例：
select Address 地区,COUNT(*) 人数
from StudentInfo
where Sid>1
group by Address
having COUNT(*)>=1
order by 人数 desc --排序可以使用别名
go
12、基本连接查询
--基本连接查询:返回两张表匹配的数据
select Sid,Sname,B.Cid,Grade,Term --所需要的列
from ClassInfo A,StudentInfo B --给表取别名
where A.Cid=B.Cid --连接条件：主外键 的值相等
go
13、内连接
--内连接：返回两张表匹配的数据
--关键字：inner join ... on
select Sid,Sname,B.Cid,Grade,Term
from StudentInfo A inner join ClassInfo B --给表取 别名
on A.Cid=B.Cid --连接条件：主外键的值相等
go
14、外连接
--外连接：
--左外连接：返回两张表匹配的数据，
--还可以返回左表有，右表没有的数据（不匹配的数据， 会在右表的列用NULL替代）
-- 关键字：left [outer] join ...on：outer可以省略
select A.*,B.* from StudentInfo A --学生表是左表
left join ClassInfo B --班级表是右表
on A.Cid=B.Cid
--where A.Cid is null --可以查询哪些学生没有 被分配班级
go
select * from ClassInfo A --班级表是左表
left join StudentInfo B --学生表是右表
on A.Cid=B.Cid
--where Sid is null --找出哪些班级没有学生
go
--右外连接：返回两张表匹配的数据，
--还可以返回右表有，左表没有的数据（不匹配的数据， 会在左表的列用NULL替代）
select * from StudentInfo A --学生表是左表
right join ClassInfo B --班级表是右表
on A.Cid=B.Cid
go
--左外和右外下面写法是等价的
--select * from A left join B on ....
--select * from B right join A on ....
--全外连接：完整外连接
--全外=左外+右外
select * from StudentInfo A
left join ClassInfo B
on A.Cid=B.Cid
go
select * from StudentInfo A
[dʒɔɪn]
right join ClassInfo B
on A.Cid=B.Cid
go
select * from StudentInfo A
[fʊl][dʒɔɪn]
full join ClassInfo B
on A.Cid=B.Cid
go
浙公网安备 33010602011771号