数据库原理与技术

SQL语句分类
DQL（数据查询语言）: 查询语句，凡是select语句都是DQL。
DML（数据操作语言）：insert delete update，对表当中的数据进行增删改。
DDL（数据定义语言）：create drop alter，对表结构的增删改。
TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Transaction)
DCL（数据控制语言）: grant授权、revoke撤销权限等。

数据后缀名.mdf
日志数据名.ldf

创建属于我们自己的数据库

create database mydb;

创建属于我们自己的数据库
alter database bjpowernode;
modify name
删除数据库：drop database bjpowernode;

分离和删除的区别：分离后可附加回来
分离：从数据库系统把数据库移出去，但是注意不会删除数据文件
删除：会把数据文件一起删掉
附加：把数据文件添加到数据库系统，添加的是数据文件.mdf

数据类型：
数值类型:int,float (double,tinyint,smallint,bigint)
日期类型:date,time,year,datetime,timestamp(时间戳的格式)
字符类型:char,varchar,(tinyblob,tinytext)

库：增删改查
增：

create database mydb
on
(
name=mydb,
filename='E:\data\mydb.mdf',
size=3,
maxsize=20,
filegrowth=2
)
log on
(
name=mydb_log,
filename='E:\data\mydb_log.ldf',
size=1,
maxsize=20,
filegrowth=10%
)

修改数据库
alter database mydb
modify file
(
name=mydb,
size=10,
filegrowth=2%
)

删除数据库：会把数据文件一起删掉

drop database mydb;

分离：从数据库系统把数据库移出去，但是注意不会删除数据文件
附加：把数据文件添加到数据库系统，添加的是数据文件.mdf
数据类型：
1.数值类型
int float
2.日期类型
date
time
datetime
year
timestamp 时间戳的格式
3.字符类型
char varchar

建表
use db_school01;
create table tb_student
(
studentNo char(10),
studentName varchar(20),
sex char(20),
birthday date,
nativePlace varchar(20),
nation varchar(20),
classNo char(6)
)

查询数据语句
select * from tb_student;

表：增删改查
修改表的名称，基本语法格式
exec sp_rename ‘old_name’,‘new_name’;

use db_school01;
create table tb_student
{
studentNo char(10),
studentName varchar(20),
sex char(20),
birthday date,
nativePlace varchar(20),
nation varchar(20),
classNo char(6)
}

exec sp_rename ‘tb_student’,‘student’; //修改表名

alter table student add phone char(11); //增加表的字段

alter table student add address varchar(50); //增加表的字段

later table student drop column phone; //删除表的字段

alter table student alter column address char(100); //修改表的数据类型

exec sp_rename ‘student.[address]’,‘addr’,‘column’; //修改表的列名

drop table student; //删除表

修改:
（1）修改表名：
exec sp_rename ‘old_name’,‘new_name’;
（2）增加表的字段
alter table 表名 add 列名 数据类型;
（3）删除表的字段
//column 列（不能缺少，否则会报错）
alter table 表名 drop 字段名;
later table student drop column phone;
（4）修改表的数据类型
alter table 表名 alter column 列名 数据类型;
alter table student alter column address char(100);
（5）修改表的列名
exec sp_rename ‘表名.[旧列名]’,‘新列名’,‘column’; /修改字段名,可以没有column/
exec sp_rename ‘student.[address]’,‘addr’,‘column’;

查：在界面刷新一下即可

删除表：
drop table 表名;
drop table student;

数据的操作：
增删查改
增：insert
删：delete
查：select
改：update

新增数据：向表中插入数据

表新增单条数据
insert into 表名 (字段名1，字段名2…字段名n) values ()

表新增多条数据
insert into teacher (name,teacherNo,age) //指定字段名向表插入数据
values(‘zhangsan’,20201013,30),(‘lisi’,20201014,50); //多条数据方法

use db_school01;
create table reacher
(
teacherNo int,
name varchar(30),
sex char(2),
age int
)

select * from teacher; //查询数据语句（*表示查询所有数据）

insert into teacher values (20201010,‘liu’,‘女’,20); //表插入数据，不指定字段名

insert into teacher (teacherNo,name,sex,age) //指定字段名向表插入数据
values(20201011,‘zhang’,‘男’,20); //指定字段名

insert into teacher (teacherNo,name,age) //指定字段名向表插入数据
values(20201012,‘wang’,30); //指定部分字段名，注意数据顺序要和字段顺序一致

insert into teacher (name,teacherNo,age) //指定字段名向表插入数据
values(‘zhangsan’,20201013,30),(‘lisi’,20201014,50); //新增多条数据

修改数据
update 表名 set 班级号=’’ where 学生名=’’;
update tb_student set classNo=‘s2171’ where studentName=‘张三’; //修改部分记录数据
update tb_student set classNo=‘s2171’ //修改全部数据
同时修改多个字段的数据
update tb_student set birthday=‘1999-09-09’,nativePlace=‘江西南昌’,nation=‘汉’ where studentName=‘张三’;

删除数据：delete
delete from tb_student where studentName=‘张三’;
delete from tb_student;

drop和delete区别：
drop:删表的数据和结构
delete：只删数据
约束
主键（primary key）约束，外键(foreign key)约束，唯一性(unique)约束，默认(default)约束，非空(not null)约束，检查(check)约束
数据完整性约束包括：定义实体完整性（主键约束，候选键约束）
定义参照完整性（外键约束）
用户定义完整性（非空约束，默认约束，自增等）

主键约束：
	1.一个表中只能有一个主键
	2.唯一性
	3.主键的值不能为空
	4.主键，复合主键
	5.列级，表级（注：创建方式）

候选键(unique)
1.一个表可以有多个候选键
2.唯一性
3.候选键的值可以为空
4.候选键，复合候选键
5.列级，表级
/列级的方式创建主键/
use tb_school01
create table tb_student
(
studentNo char(10) primary key,
studentName varchar(20),
sex char(2),
birthday date,
nativePlace varchar(20),
nation varchar(20),
classNo char(10)
)
/表级方式创建主键/
create table tb_student
(
studentNo char(10),
studentName varchar(20),
sex char(2),
birthday date,
nativePlace varchar(20),
nation varchar(20),
classNo char(10),
primary key(studentNo)
)

/复合主键方式创建主键/
create table tb_student
(
studentNo char(10),
studentName varchar(20),
sex char(2),
birthday date,
nativePlace varchar(20),
nation varchar(20),
classNo char(10),
primary key(studentNo,studentName)
)
/给主键命名/
语法规则：constraint 约束名字 primary key(列名1,…,列名n)

create table tb_student
(
studentNo char(10),
studentName varchar(20),
sex char(2),
birthday date,
nativePlace varchar(20),
nation varchar(20),
classNo char(10),
constraint PK_tb_student_sno_sname primary key(studentNo,studentName)
)

/修改非空/（注意修改权限）
alter table tb_student alter column studentNo char (10) not null； //遇到权限的问题：表-右键-属性-权限，添加更改的权限

/在已经存在的表上，创建主键/

语法规则：alter table 表名 add constraint 约束名字 primary key (列名1,…,列名n)
alter table tb_student alter column studentNo char (10) not null；
alter table tb_student add constraint PK_tb_student_sno primary key
create table tb_student
(
studentNo char(10),
studentName varchar(20),
sex char(2),
birthday date,
nativePlace varchar(20),
nation varchar(20),
classNo char(10),
)

/删除主键/
alter table 表名 drop constraint 主键名;
alter table tb_student drop constraint PK_tb_student_sno;

候选键
1.一个表可以有多个候选键
2.唯一性
3.候选键的值可以为空
4.候选键，复合候选键
5.列级，表级

/列级的方式创建候选键/
create database db_school01
use db_school01
create table tb_class
(
classNo char(10) unique,
className varchar(20),
grade char(10),
classNum int
)
drop table tb_class //删除表

/表级方式创建主键，候选键命名/
create table tb_class
(
classNo char(10),
className varchar(20),
grade char(10),
classNum int,
constraint UQ_tb_class_classNo unique(classNo)
)

/复合候选键方式创建主键/
create table tb_class
(
classNo char(10),
className varchar(20),
grade char(10),
classNum int,
constraint UQ_tb_class_classNo_className unique(classNo,className)
)

/在已经存在的表上，添加复合候选键/
create table tb_class
(
classNo char(10),
className varchar(20),
grade char(10),
classNum int,
)
alter table tb_class add constraint UQ_tb_class_classNo_className unique(classNo,className)；

/删除候选键/
alter table tb_class drop constraint UQ_tb_class_classNo_className;

/非空约束:not null/
增删改查
新增一个非空约束：
（1）直接在表增加非空约束
create table book
(
id int,
bname varchar(50),
booknum int not null
)
（2）修改表，给已经存在的字段添加非空约束
create table book
(
id int,
bname varchar(50),
booknum int
)
alter table book alter column booknum int not null
（3）表已经存在，新增一列，定义非空属性
alter table book add bookPrice float not null;

/删除非空属性/
alter table book alter column booknum int null

/默认值约束:default/
（1）创建表时，给字段增加默认值约束
create table student02
(
studentNo char(10),
studentName varchar(30),
nation varchar(20) default ‘汉’,
classNo char(6)
)
（2）表已经存在，新增一列，同时定义默认值
alter table student02 add sex char(2) default ‘男’
（3）删除表中某个字段默认值
alter table student02 drop constraint DF__student02__sex__07020F21
（4）修改默认值
alter table student02 drop constraint DF__student02__natio__060DEAE8; //先删默认值
alter table student02 add constraint DF_student02_nation default ‘汉族’ for nation; //再修改默认值
自增约束（auto_increment）
在SQL Server 中，可通过关键字identity为列设置自增属性，只有整形列才能设置此属性，每个列表只能设置一个identity属性
1.会在int型设置
2.在主键/候选键的列上设置自增属性
3.identity

增删改查
（1）创建表时，给字段增加自增约束
create table 表名（字段 数据类型 primary key|unique auto_increment）
例:create table book2（id int primary key indentity(1,1) not null
//新增一个自增属性，修改表，为表中新增字段并同时增加自增属性
create table book
(
id int primary key identity(1,1) not null,
bname varchar(50),
bprice float
)

（2）参照完整性（外键）
create table tb_class
(
classNo char(6) primary key, //主键
className varchar(20),
grade int,
classNum int
)

create table tb_student01
(
studentNo char(10),
studentName varchar(20),
sex char(2),
classNo char(6), //外键
constraint FK_tb_class_classNo foreign key(classNo) references tb_class(classNo)
)

（3）参照表已经存在，增加外键
create table tb_class
(
classNo char(6) primary key, //主键
className varchar(20),
grade int,
classNum int
)

create table tb_student01
(
studentNo char(10),
studentName varchar(20),
sex char(2),
classNo char(6), //外键
)
alter table tb_student01 add constraint FK_tb_student01_classNo foreign key(classNo) references tb_class(classNo);
索引
索引主要分为：普通索引（没有限制），唯一索引（唯一但可以为空），主键索引（唯一且不能为空）。一般建在经常需要查询的地方
/增删查改/
1.普通索引：
/新增普通索引/
create table 表名(
属性名 数据类型,
属性名 数据类型,
…
属性名 数据类型,
index | key [索引名] (字段名[asc | desc]) // asc:升序，desc:降序
);
create table t_dept
(
deptno int,
dname varchar(30),
loc varchar(50),
)
create index index_deptno on t_dept(deptno asc); //asc:升序，desc:降序

/查看索引/
exec sp_helpindex t_dept;

/删除索引/
drop index t_dept.index_deptno; //index_deptno:索引名

/修改索引名称/
exec sp_rename ‘t_dept.index_deptno’,‘index_t_dept_deptno’;
2.唯一索引
/表t_dept的deptno字段上创建唯一聚集索引/
create unique clustered index uniqueclus on t_dept(deptno asc) with(sort_in_tempdb=off);
//sort_in_tempdb=off：指定排序结果是否放在tempdb中

/删除索引/
drop index t_dept.uniqueclus;

/创建多列索引/
create index index_t_dept_dname_loc on t_dept(dname asc,loc desc);
drop index t_dept.index_t_dept_dname_loc; /删除索引/
创建主键索引
主键索引不需要额外创建，创建主键时会自动创建主键索引
create table t_dept
(
deptno int primary key,
dname varchar(20),
loc varchar(50)
)
//不可以用drop index删除主键索引，若要删除需要把主键直接删除，这样会把主键索引一起删除
alter table t_dept drop constraint PK__t_dept__BE2C337D00551192 //删除主键
查询 //重要！
单表数据查询：
1.查询所有数据
select 字段名1,字段名2,…from 表名
或select * from 表名; //*表示所有
create table tb_employee
(
id int,
name varchar(30),
job varchar(10),
salary float, //salary:薪资
award float,
age int
)
select * from tb_employee; //查询表的所有数据
insert into tb_employee values(123,’张三’,’店员’,800.00,null,29),
(123,’李四’,’店员’,1600.00,300.00,30),(125,’王五’,’店长’,3200.00,800.00,32),
(126,’王五七’,’经理’,5000.00,1000.00,35);
select * from tb_employee;
select id,name,job,salary,award,age from tb_employee;

2.查询指定字段
select name,salary from tb_employee;

3.去除重复数据
select distinct job from tb_employee;

4.四则运算查询数据
select 字段名数值 from 表名;
select name,salary12 as ‘年薪’ from tb_employee; //as ‘字段名’ 表示取别名
select name,(salary+award)*12 as ‘年薪’ from tb_employee;
//出现问题：若某个字段数据为空则最终的那个数据为空

5设置显示格式数据查询：
年薪：张三这个员工的年薪是:9600
select concat (name,’这个员工的年薪是:’,salary*12) as ‘年薪’ from tb_employee;
//SQL 2008不支持concat这个内置函数名称

条件数据记录查询
1.带关系运算符和逻辑运算符的条件数据查询
/单条件数据查询/
select 字段名 from 表名 where 字段名1=’数据’;
select name,job,salary from tb_employee where job=’店员’;
/多条件查询/
select name,job,salary from tb_employee where job=’店员’ and salary<1000;
//如果条件之间是且的关系用and,或的关系用or,

2.带between and 关键字范围查询 // between边界包含等号
select name,job,salary from tb_employee where salary>=1000 and salary<=4000;
select 字段名 from 表名 where 字段名 between 数据值 and 数据值
select name,job,salary from tb_employee where salary between 1000 and 4000;
/不在数据范围内/
select 字段名 from 表名 where 字段名 not between 数据值 and 数据值
select name,job,salary from tb_employee where salary not between 1000 and 4000;
select name,job,salary from tb_employee where salary<1000 or salary>4000;
3.带is null关键字的空值查询
(1)空值数据查询记录
select name from tb_employee where award is null;
(2)非空数据查询记录
select name from tb_employee where award is not null;

4.带in关键字的集合查询 ：
（1）在集合中数据记录查询:in
select name,salary from tb_employee where id in(123,124); //in适用于枚举时
select name,salary from tb_employee where id=123 or id=124;
（2）不在集合中数据记录查询:not in
select name,salary from tb_employee where id not in(123,124);
5.带like关键字的模糊查询 //关键字是like
（1）带%通配符的查询
select * from tb_employee;
select * from tb_employee where name like ‘张%’; //查询姓张的人
select * from tb_employee where name like ‘王%’; //查询姓王的人
（2）带有_通配符的查询
select * from tb_employee where name like ‘王_%’; //查询姓王某的人
select * from tb_employee where name like ‘%五%’; //查询名字带五的人
//注意_表示匹配一个字符，%表示匹配任意多个字符

6.按照单字段排序 // 排序关键字是: order by
（1）降序排序
slelect name,salary from tb_employee order by salary desc; //desc表示降序，asc表示升序
（2）升序排序
slelect * from tb_employee order by id asc; //按id降序

7.分组：group by
select courseNo,COUNT(studentNo) from tb_score group by courseNo; // count表示人数
select courseNo,COUNT(studentNo) from tb_score group by courseNo order by COUNT(studentNo) //先分组再按选课人数升序
8.对分组后的数据过滤： //关键字:having
select courseNo,COUNT(studentNo) from tb_score group by courseNo having count(studentNo)>=5 order by COUNT(studentNo) desc;
//先课程号分组统计人数，对分组后数据进行过滤，最后排序

聚合函数
（1）count()函数:统计表的记录个数
select COUNT() from tb_score; //统计这张表的所有记录数有多少个
select COUNT(studentNo) from tb_score; //使用关键字统计，不会统计到null的字段
//注意：count()会把null的数据也统计进去
（2）sum()函数 //count()和sum()区别：count求记录个数，sum求总和
select sum(score) from tb_score; //求和类型需为整形或浮点型
（3）avg()函数
select sum(salary) from tb_employee; //求和类型需为整形或浮点型
（4）max()函数
select max(salary) from tb_employee;
（5）min()函数
select min(salary) from tb_employee;
（6）getdate函数：获取当前日期 year(),month(),day()
select getdate()；
select year(getdate()); //获取当前日期的年份
select day(getdate()); //获取当前日期
数学函数：ABS(),RAND(),FLOOR(),Sqrt():根号
（1）ABS(x)函数:绝对值
select ABS(5),ABS(-5); //得到结果都为5
（2）Rand()函数：获取0-1之间的随机数 //数学函数重点了解随机函数即可
select rand(),rand();

字符串函数
upper():小写转换成大写
select upper(‘hello’),lower(‘HELLO’)
lower():大写转换成小写

left(s,n)函数：用于返回字符串S的前n个字符

substring(s,n,len)函数：用于从字符串s的第n个位置开始获取长度为len
多表查询：
交叉查询与内链接查询及自然查询
查询某个学生的学号，姓名，课程，成绩
学号，姓名：tb_student
课程： tb_score tb_course
成绩： tb_score

查询某个学生的学号，姓名，班级，学院
学号，姓名 tb_student
班级，学院 tb_class
交叉查询：笛卡尔积 //较少用
//一个表的每一行会与另一张表的每一行连接起来，结果是每张表的记录数的乘积。交叉查询会有很多冗余的数据,故较少用
交叉连接（查询）：cross join
语法格式：select * from 表1 cross join 表2
select * from tb_student cross join tb_class ; //查询学生表和班级表
交叉查询：
语法格式：select * from 表1，表2
select * from tb_student,tb_class;
内连接：inner join
语法格式1：
select 目标列表达式1，目标列表达式1，…，目标列表达式n
from 表1 inner join 表2 on 连接条件
查询学生的学号，姓名，班级号，班级名称，所在学院；
select studentNo,studentName,tb_student.classNo,className,department from tb_student inner join tb_class on tb_student.classNo=tb_class.ClassNo; //当两张表都有classNo时要明确classNo所在的表

语法格式2：（非常常见）
select 目标列表达式1，目标列表达式1，…，目标列表达式n from 表1,表2
where 连接条件[and 过滤条件]
select studentNo,studentName,tb_student.classNo,className,department from tb_student, tb_class where tb_student.classNo=tb_class.ClassNo; //重点掌握

查询女生的学号，姓名，班级号，班级名称，所在学院；
select studentNo,sex,studentName,tb_student.classNo,className,department from tb_student, tb_class where tb_student.classNo=tb_class.ClassNo and sex=’女’; //语法格式1
select studentNo,sex,studentName,tb_student.classNo,className,department from tb_student inner join tb_class on tb_student.classNo=tb_class.ClassNo where sex=’女’; //语法格式2

给表取别名：
select studentNo,sex,studentName,a.classNo,className,department from tb_student a, tb_class b where a.classNo=b.ClassNo and sex=’女’; //a和b为取别名

自连接：
查询和“数据库”这门课程学分相同的课程信息
select b. * from tb_course a,tb_course b where a.credit=b.credit and a.courseName=’数据库’;
select b. * from tb_course a,tb_course b where a.credit=b.credit and a.courseName=’数据库’ and b.courseName!=’数据库’; //去掉数据库本身

外连接：
insert into tb_class values(‘Y1901’,’软件工程雨田班’,’人工智能学院’,2019,40);
select * from tb_class; //先增加信息

select 目标列表达式1，目标列表达式1，…，目标列表达式n
from 表1 left outer join 表2 //左外连接
select * from tb_class a left join tb_student b on a.ClassNo=b.classNo;//若无信息则置为空null
右外连接
select * from tb_student a right join tb_class b on a.ClassNo=b.classNo;

查询张晓勇选修的课程的名称和成绩
张晓勇 tb_student
课程的名称 tb_course
成绩 tb_score
确定要查询的字段:studentname,coursename,score
确定过滤条件
select studentname,coursename,score from tb_student a,tb_course b,tb_score c where a.studentno=c.studentno and c.courseno=b.courseno and studentname=’张晓勇’;
确定三张表两两连接的连接条件

多表查询之子查询
子查询:只能涉及一张表，多张表查询需要用连接查询
子查询也称嵌套查询，是将一个查询语句嵌套在另一个查询语句的where子句、having子句或from子句以及select子句中，前者被称为内层查询或子查询，后者被称为外层查询或父查询。在整个select语句中，先计算子查询，然后将子查询的结果作为父查询的过滤条件。
1、带in关键字的子查询：
带in关键字的子查询是最常用的一类子查询，用于判断一个给定值是否存在于子查询的结果集中。
例子：查询选修了课程的学生姓名。
select studentName from tb_student where tb_student.studentNo in
(select distinct tb_score.studentNo from tb_score); //tb_student和tb_score可不要
//in可改成关系运算，in比较适合一个集合，单值比较用运算符=
内带比较 运算符的子查询是指父查询与子查询之间用比较运算符进行连接。当用户 能确切知道内层返回的是单值时，可以用<,<=,>,>=,=,<>,!=等比较运算符构造子查询。
查询班级‘计算机14-1班’所有学生的学号、姓名
select studentNo,studentName from tb_student where classNo =
(select classNo from tb_class where className=’计算机14-1班’)；
查询选修了数据库的学生的学号和姓名 //先确定表，再确定连接条件
select studntNo,studentName from tb_student where studentNo in
(select studentNo from tb_score where courseNo=(select courseNo from tb_course where courseName=‘数据库’));
查询计算机14-1班的同学选修的课程名称和学分
select courseName,credit from tb_course where courseNo in(select courseNo from tb_score where studentNo in(select studentNo from tb_student where classNo =(select classNo from tb_class where ClassName=‘计算机14-1班’)));
查询分数在90分以上的学生的姓名和所在班级号
select studentNo,classNo from tb_student where studentNo=(select studentNo from tb_score where score>90);
查询分数在90分以上的课程名称
select ClassName from tb_class where classNo in(select classNo from tb_student where studentNo in(select studentNo from tb_score where score>=90));
查询选修了第三学期(tb_course.term)开课的课程的学生姓名和班级号
select studentName,classNo from tb_student where studentNo in(select studentNo from tb_score where courseNo in(select courseNo from tb_course where term=3));

SQL Server数据库视图操作
视图(虚拟表)：视图的列可以来自于不同的表，是表的抽象和逻辑意义上建立的新关系
视图时由基本表(实表)产生的表(虚表)
视图的建立和删除不影响基本表
对视图内容的更新（添加，删除和修改）直接影响基本表
当视图来自于多个基本表时，不允许添加和删除数据
定义的视图中Select语句不能包含FROM子句中的子查询
（1）创建视图，语法格式：
Go
Create view 视图名 AS 查询语句 //go相当于分隔符，批处理命令
go
例:创建视图v_student,使其包含学号，学生姓名
go
Create view v_student as select studentNo,studentName classNo from tb_student;
go
（2）从视图里面查询数据
select * from v_student;
（3）向视图插入数据： //注：视图的数据来源于一张表时才可以增删改
insert into v_student values(112233,‘张三’,‘Y1901’);
insert into v_student(studentNo,classNo) values(112234,‘Y1902’);
（4）修改视图中的数据
update v_student set studentName=‘张三三’ where studentNo=112233;
（5）删除视图中的数据
delete from v_student where studentNo=112234;
视图：
包含学生的学号/姓名/班级/课程/成绩
创建：
go
create view v_score as
select a.studentNo,studentName,classNo,courseNo,score from tb_student a,tb_score b
where a.studentNo=b.studentNo;
go

//注:当视图来自于多个基本表时，不允许添加和删除数据

（6）删除视图：									//删除视图不影响基表
drop view v_score;
视图：包含学生的学号/姓名/课程名称/学分/成绩
go
cteate view v_score as
select a.studentNo,studentName,courseName,credit,score
from tb_student a,tb_score b,tb_course c
where a.studentNo=b.studentNo and b.courseNo=c.courseNo
go

带any关键字的子查询
关键字any用来表示查询条件为满足子查询返回查询结果中的任意一条数据
查询选修了课程的姓名
select studentName from tb_student where studentNo in(select studentNo from tb_score);
select studentName from tb_student where studentNo =any(select studentNo from tb_score);
//以上两种写法是等效的，但一般使用in关键字来查询，运算符可变为>等

查询男生中比某个女生出生早的学生姓名和出生年份
select studentName,year(birthday) from tb_student where sex=‘男’ and
year(birthday)<any(select year(birthday) from tb_student where sex=‘女’);
或者
select studentName,year(birthday) from tb_student where sex=‘男’ and
year(birthday)<(select max(year(birthday)) from tb_student where sex=‘女’);

查询男生中比所有女生出生晚的学生姓名和出生年份
select studentName,year(birthday) from tb_student where sex=‘男’ and
year(birthday)>all(select year(birthday) from tb_student where sex=‘女’);
或者
select studentName,year(birthday) from tb_student where sex=‘男’ and
year(birthday)>(select max(year(birthday)) from tb_student where sex=‘女’);

修改视图的结构
go
alter view v_score(sno,sname,classno,cno,credit,score) //变化列名
as
select a.studentNo,studentName,classNo,courseNo, credit,score
from tb_student a,tb_score b,tb_course c
where a.studentNo=b.studentNo and b.courseNo=c.courseNo;
go
T-SQL操作
变量：全局变量，局部变量
全局变量：
@@version:返回 SQL Server能查看当前安装的版本信息
select @@version
@@error:上一条sql语句出错，会有错误号，上一条语句的执行结果，为0则表示上一条语句执行成功，否则，表示上一条语句执行失败
@@Rowcount:上一条sql语句影响的行数
@@servername:返回正在运行 SQL Server本地服务器的名称

局部变量:@
声明一个局部变量:
declare @name varchar(20)
declare @age int

赋值
1.普通赋值:需要和声明变量一起执行
set @name=‘刘’;
set @age=20;
select @name,@age; //查询变量，注意：需要和前面的声明变量和赋值一起执行才能查询

select赋值
declare @name01,varchar(20);
select @name01=studentname from tb_student where studentNo=2013110101;
//查询的结果赋值为name01,查询学号为2013110101学生的姓名

查询王五学号前后的两个人的姓名:先创建表
declare @name001 char(10),@nameone char(10),@nametwo char(10),@id int;
set @name001=‘王五’;
select @id=id from student where name @name001;
select @id;
select @nameone=name from student where id=@id-1;
select @nametwo=name from student where id=@id+1;
select @name001;
select @nameone;
select @nametwo;
判断
分支结构
if-else语句
if(条件)
begin
T-SQL语句
end
else-if(条件)
begin
T-SQL语句
end
else
begin
T-SQL语句
end

比较男女平均成绩的优异
declare @boy_score int,@girl_score int;
select @boy_score=AVG(score) from tb_student a,tb_score b where a.studentNo=b.studentNo and sex=‘男’;
select @girl_score=AVG(score) from tb_student a,tb_score b where a.studentNo=b.studentNo and sex=‘女’;
if(@boy_score>@girl_score)
begin
print’男生的成绩更优秀’
end
else if(@boy_score=@girl_score)
begin
print’男生和女生的成绩一样好’
end
else
begin
print’女生的成绩更优秀’
end
case判断
case
when score>=90 then ‘A’
when score>=80 then ‘B’
else ‘E’
end

查询学生姓名，选修课程，成绩，成绩等级
成绩等级用case
select studentName,courseName,score,
case
when score>=90 then ‘A’
when score>=80 then ‘B’
when score>=70 then ‘C’
when score>=60 then ‘D’
else ‘E’
end as ‘成绩等级’ //取别名
from tb_student a,tb_course b,tb_score c
where a.studentNo=c.studentNo and b.courseNo=c.courseNo and course=‘程序设计’;

循环
while(@score1<60)
begin
set @score1=@score1+1
update student set score1=@score1 where 条件
end

把一个学生成绩依次相加修改到70
declare @score int;
select @score=score from student where name=‘老六’;
while(@score<60)
begin
set @score=@score+1
update student set score=@score where name=‘老六’;
end

存储过程
–创建测试books表
create table books (
book_id int identity(1,1) primary key, //id是自增属性
book_name varchar(20),
book_price float,
book_auth varchar(10)
);
–插入测试数据
insert into books (book_name,book_price,book_auth)
values
(‘论语’,25.6,‘孔子’),
(‘天龙八部’,25.6,‘金庸’),
(‘雪山飞狐’,32.7,‘金庸’),
(‘平凡的世界’,35.8,‘路遥’),
(‘史记’,54.8,‘司马迁’);

创建一个存储过程，功能是可以查看图书表中的所有信息
1.创建无参存储过程
if (exists (select * from sys.objects where name = ‘getAllBooks’))
drop procedure getAllBooks //先判断表是否存在，若存在则先删除，再重新创建
go
create procedure getAllBooks
as
select * from books; //查看图书表中的所有信息
–调用,执行存储过程
exec getAllBooks; //查询所有信息
删除存储过程
drop procedure getAllBooks; //procedure也可以是proc
修改存储过程，功能是可以查看图书表中的图书的书名
go //go是放在批处理命令中
alter procedure getAllBooks
as
select book_name from books;
exec procedure getAllBooks //调用存储过程
2.创建带参数的存储过程，功能是根据图书的编号来查询图书的信息 //期末重点
if(exists(select * from sys.objects where name=’getAllBooks_id’))
drop procedure getAllBooks_id
go
create procedure getAllBooks_id(@bookid int)
as
select * from boooks where book_id=@bookid;
exec getAllBooks_id 1 //调用存储过程,注意与无参的区别
创建存储过程，功能是通过图书的名字和作者查询图书的编号
if(exists(select * from sys.objects where name=’getBookID’))
drop procedure getBookID
go
create procedure getBookID(@name varchar(20),@auth varchar(20))
as
select * from books where book_name=@name and book_auth=@auth;
exec getBookID ‘论语’,’孔子’ //调用存储过程，实现查询
创建有返回值的存储过程,功能是可以根据输入的图书的编号，查询书名（输出参数）
if(exists(select * from sys.objects where name=’getBookName’))
drop procedure getBookName
go
create procedure getBookName(@id int,@b_name varchar(20) output)
as
select @b_name=book_name from books where book_id=@id;
declare @name22 varchar(20) //先声明变量
exec getBookName 1,@name22 output; //调用存储过程
select @name22; //查询@name22
创建一个存储过程，功能是根据学生的姓名查询学生的选课信息和成绩
if(exists(select * from sys.objects where name=’getScore’))
drop procedure getstudent
go
create procedure getScore(@name varchar(20))
as
select studentName,courseName,score from tb_student a,tb_score b,tb_course c where a.studentno=b.studentno and b.courseno=c.courseno and studentName=@name;
exec getScore ‘张晓勇’
创建一个存储过程，根据指定的学生的姓名修改学生的性别为指定的性别
if(exists(select * from sys.objects where name=’updateSex’))
drop procedure updateSex
go
create procedure updateSex(@name varchar(20),@ssex char(2))
as
update tb_student set sex=@ssex where studentName=@name;
exec getScore ‘张晓勇’,’女’

触发器的操作
触发器与存储过程的区别：
触发器与存储过程的区别是运行方式的不同，触发器不能执行execute语句调用，
//保证数据的完整性

DML(数据操纵语言)

触发器类型:insert触发器,delete触发器,update触发器

当插入数据时会产生两张虚拟表:deleted表和inserted表触发器执行完成后，自动删除
inserted表：临时保存了插入或更新后的记录行
deleted表：临时保存了删除或更新前的记录行

1.创建insert触发器
案例一：当向student表插入数据时，更新学生人数表student_sum（记录学生总人数）中的数据。当学生人数表student_sum不存在时，先创建student_sum表；
–创建insert触发器
if (exists (select * from sys.objects where name = ‘trig_insert’))
drop trigger trig_insert
go
create trigger trig_insert
on student
after insert
as
begin
if (not exists (select * from sys.objects where name = ‘student_sum’))–判断student_sum表是否存在
create table student_sum(stuCount int default(0));–创建存储学生人数的student_sum表
declare @stuNumber int;
select @stuNumber = count(*) from student;
if (not exists (select * from student_sum))–判断表中是否有记录
begin
insert into student_sum values(0);
end
update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中
End

select * from student_sum;
delete from student_sum;

insert into student values(‘liu’,‘女’,20);

2.创建delete触发器
用户执行delete操作，就会激活delete触发器，从而控制用户能够从数据库中删除数据记录，触发delete触发器后，用户删除的记录会被添加到deleted表中，原来表的相应记录被删除，所以在deleted表中查看删除的记录。
案例二：使用触发器查询已删除的学生的信息；
–创建delete触发器
if (exists (select * from sys.objects where name = ‘trig_delete’))
drop trigger trig_delete
go
create trigger trig_delete
on student
after delete
as
begin
select stu_id as 已删除的学生编号,stu_name,stu_gender,stu_age
from deleted
end;

delete from student where stu_id=1;

3.创建UPDATE触发器
update触发器是当用户在指定表上执行update语句时被调用被调用，这种类型的触发器用来约束用户对数据的修改。update触发器可以执行两种操作：更新前的记录存储在deleted表中，更新后的记录存储在inserted表中。
案例三：使用触发器，实现查看更新前的学生数据和更新后的学生的数据
–创建update触发器
if (exists (select * from sys.objects where name = ‘trig_update’))
drop trigger trig_update
go
create trigger trig_update
on student
after update
as
begin
declare @stuCount int;
select @stuCount=count(*) from student;
update student_sum set stuCount =@stuCount;
select stu_id as 更新前学生编号,stu_name,stu_gender,stu_age from deleted
select stu_id as 更新后学生编号,stu_name,stu_gender,stu_age from inserted
end

select * from student_sum;
select * from student;
update student set stu_name=‘zhang0113’ where stu_id=9;

4.创建替代触发器
　　与前面介绍的三种after触发器不同，SqlServer服务器在执行after触发器的sql代码后，先建立临时的inserted表和deleted表，然后执行代码中对数据库操作，最后才激活触发器中的代码。而对于替代(instead of)触发器,SqlServer服务器在执行触发instead of 触发器的代码时，先建立临时的inserted表和deleted表，然后直接触发instead of触发器，而拒绝执行用户输入的DML操作语句。
–创建instead of 触发器
if (exists (select * from sys.objects where name = ‘trig_insteadOf’))
drop trigger trig_insteadOf
go
create trigger trig_insteadOf
on student
instead of insert
as
begin
declare @stuAge int;
select @stuAge=(select stu_age from inserted)
if (@stuAge > 120)
begin
select ‘插入年龄错误’ as ‘失败原因’
end

end

select * from student;
insert into student(stu_name,stu_gender,stu_age) values(‘zz’,‘男’,122);

事务与游标
一．事务:transaction
@@error:上一条sql语句出错，会有错误号；上一条sql执行没有错，则为0；
create table money(
id int,
name char(20),
c_money float
)

insert into money values(1,’张三’,2000),(2,’李四’,4000),(3,’王五’,6000)
select * from money

begin transaction
declare @errorsum int;
set @errorsum=0;
update money set c_money=s_money-1000 where name=’张三’
set @errorsum=@errosum+@@ERROR;
update money set c_money=s_money+1000 where name=’李四’
set @errorsum=@errorsum+@@ERROR;

if(@errorsum!=0)
begin
print ‘转账失败’;
rollback transaction;
end
else
begin
print ‘转账失败’;
commit transaction;
end

二．游标:会与存储过程结合起来使用 //重要
books(id,name,price,auth)
需求：根据作者，查询书的编号，书名和价格，当书的价格大于30时，删除该书信息
if(exists(select * from sys.objects where name=’deletebook_cur’))
drop procedure deletebook_cur
go
create procedure deletebook_cur(@auth varchar(20))
as
declare cur_price cursor
for select book_id,book_name,book_price from books where book_auth=@auth
open cur_price;
delcare @bname varchar(100),@bprice float,@bid int;
fetch next from cur_price into @bid,@bname,@bprice;
while @@fetch_status=0
begin
if(@price>30)
begin
select @bid as ‘编号’,@bname as ‘书名’,@bprice as ‘价格’
delete from books where book_id=@bid;
end
fetch next from cur_price into @bid,@bname,@bprice;
end
close cur_price;
deallocate cur_price; //释放游标

exec deletebook_cur ‘金庸’;
select * from books;

使用游标，检查student表的数据，当student表中的性别的数据不是男或女的时候，修改该数据的性别为男