今天复习一下数据库的有关知识
代码全部是在Microsoft SQL Server Management Studio2017上完成的。
还是先上思维导图
1.基本操作
建库，建表，建约束
建立数据库，切换数据库，删除数据库
建库
创建数据库
最简单的方式创建数据库，参数是默认值
create database 数据库名
删除数据库
drop database 数据库名
指定参数创建数据库
create database 数据库名
on(
name='数据文件的逻辑名称',
filename='数据文件的物理路径',
size = 数据文件的初始化大小(KB,MB,GB,TB)
maxsize = 数据文件大小的上限
filegrowth = 数据文件的增长方式
),(设置第二个数据文件),(...),...
log on(
name='日志文件的逻辑名称'，
filename='日志文件的物理路径'，
size=日志文件的初始化大小
maxsize=日志文件大小的上限
filegrowth = 日志文件数据的增长方式(..MB,%)
),(设置第二个日志文件),(...)...
if(select 1 from sysdatabases where name='数据库名')
begin
drop database 数据库名
end
#数据库如果存在，就先将其删除
例：
create database db02
切换数据库
use db02
--建库的同时设置参数
create database db03
on(
name='db03_main',
filename='C:\db\db03_main.mdf',
size = 20MB,
maxsize = 1000MB,
filegrowth = 1MB
)
log on(
name='db03_log',
filename='C:\db\db03_log.ldf',
size=10MB,
maxsize=100MB,
filegrowth = 10%
)
--删除数据库
drop database db03建表：
--建表
--部门表
drop table Dept
drop table Emp
create table Dept(
deptno int primary key, --部门编号
dname varchar(200) not null,--部门名称
location varchar(400) not null -- 部门地址
)
--员工表
create table Emp(
empno int primary key identity,--员工编号
ename varchar(50) not null,--姓名
sex char(3) check(sex in('男','女')) default '男',--性别
age int check(age between 18 and 60),--年龄
sal float check(sal>=3000) default 3000,--薪资
deptno int references Dept(deptno)
)建表完成之后建立约束
主键约束:唯一的标识这条记录,主键值要求唯一非空 ,primary key
非空约束:字段不能为null值，not null
标识列约束: identity(种子,增量) 默认 identity(1,1)
设置字段的值自增
检查约束:规定字段取值的范围 check(表达式)
默认值约束: 设置字段的默认值 default:默认值
外键约束:字段的值引用另外一张表的主键或唯一键的值 references
唯一约束:字段的值必须唯一, unique
例：
--在表建好后添加约束
drop table Student
create table Student(
id int not null,
name varchar(200),
sex char(3),
age int,
score float
)
--添加主键约束
alter table Student
add constraint pk_Sutdent_id primary key(id)
alter table Student
add constraint ck_Student_sex check(sex in ('男','女'))
--给Emp表deptno添加外键约束引用Dept的deptno
alter table Emp
add constraint fk_Emp_Dept_deptno
foreign key(deptno) references Dept(deptno)2.基本的增删查改
增：
--增：
insert into dept values(10,'java开发部','北京'),(20,'市场','上海'),
(30,'企划部','长沙')
select * from dept
insert into emp values('熊大','男',25,12000,10),('熊二','男',23,11000,10),
('张三','女',21,9000,10),('李四','女',26,15000,20),('王五','男',27,16000,20),
('赵六','女',20,6000,20),('孙七','男',21,10000,30),('吴八','女',23,13000,30),
('邓九','男',20,9000,30),('常十','女',21,8000,30);
select * from emp删：
--删除表中所有的记录，如果再添加记录标识列，会重新开始
truncate table emp
--删除
-- delete from dept where deptno=10
-- 先删子表再删主表
delete from emp where deptno = 10
delete from dept where deptno=10改：
--在emp表里将age字段的24改为22
update emp set age=24 where age=22查：
查询的基本过程
select * from emp where age = 22
where 子句的内容
where 关系表达式 > >= < <= != <> =
and or not
between and
过滤null值只能用 is null和is not null
不能用 = !=
in(在某个取值范围) not in(不在某个取值范围)
where子句过滤的是记录，实体信息
--查询年龄20~21
select * from emp where age>=20 and age<=21
select * from emp where age between 20 and 21
update emp set age=null,deptno=30
where empno=11
select * from emp where age is null
--查询所有20岁和21岁的员工
select * from emp where age in(20,21)
select * from emp where age not in(20,21)
--delete from emp group by 和 having
having 过滤的是组信息
group by 之后的字段称为组标识
在一个查询中如果出现了group by 子句,那么在select子句中只能出现组标识
如果要出现其他字段，这个字段必须加聚合函数
having之后也只能出现组标识和聚合函数
--查询每个部门的最高薪资，最低薪资,平均年龄
select deptno,max(sal) 最高薪资,min(sal) 最低薪资,
avg(age) 平均年龄
from emp
group by deptno -- 组标识，按照什么分组
sql的执行顺序
select * from emp where ....
from emp ----1 （找到表）
where age>22---2 (过滤记录)
group by deptno ---3 (分组)
having xxx ----4 (过滤组)
select xxxx ----5 (选取查询信息)
order by xxx---6 (排序)
3.表连接与子查询
表连接
外连接
查询不匹配的记录
内连接
将符合连接条件的两条记录连接起来，最后的结果集只会显示匹配的数据
交叉连接
无条件连接，获得笛卡儿积
例：
--内连接
select e.ename 员工姓名,d.dname 部门名称
from emp e inner join dept d
on e.deptno = d.deptno
--内连接
select e.ename 员工姓名,d.dname 部门名称
from emp e ,dept d
where e.deptno = d.deptno
select *from emp
--没有部门的员工
insert into emp values('eric','男',26,20000,null)
--没有员工的部门
insert into dept values(40,'后勤部','长沙')子查询
where，select，虚拟表
子查询有记录，返回true
子查询没有记录，返回false
exists 与 not exists
where exists(子查询)
子查询查询有记录 ，返回true
子查询没有记录,返回false
where not exists(子查询)
子查询查询有记录 ，返回false
子查询没有记录,返回true
例：
--exists
--哪些部门拥有年龄再25岁以上的员工
--方法1
select distinct d.deptno,d.dname
from dept d inner join emp e
on d.deptno = e.deptno
where age>=25
--方法2
select*
from dept d
where exists(
select * from emp
where age>=25 and deptno = d.deptno)
--哪些部门没有薪资在15000以上的员工
select*
from dept d
where not exists
(
select 1 from emp
where sal>=15000 and deptno = d.deptno
)
and deptno in
(
--有员工的部门
select distinct deptno from emp
where deptno is not null
)4.T-sql
transact-sql
标准sql
变量
if
循环
游标
例：
--输入输出
declare @a int = 1
print @a
declare @b varchar(100)
set @b = '你好'
print @b
--if语句
declare @age int = 19
if @age>=18
begin
print '成年'
end
else
begin
print'未成年'
end
declare @score int = 95
if @score >=90
begin
print'优秀'
end
else if @score >=80
begin
print'良好'
end
else
begin
print'哈哈'
end
--循环
--1+2+3+…+100
declare @sum int = 0
declare @idx int = 1
while @idx <= 100
begin
set @sum += @idx
set @idx += 1
end
--int只能和int拼接，字符只能和字符拼接
--convert 将int转换为char
print '总和为：'+convert(varchar,@sum)
5.存储过程
数据库对象（表，索引，视图，函数）
类似于一个功能函数
标准结构
create procedure 过程名
参数
as
变量声明
begin
sql，t-sql代码
end简化结构
create proc 过程名
参数
as
变量声明
sql，t-sql
go例：
--创建存储过程
--打印hello world
drop procedure proc_print_hello
create procedure proc_print_hello
as
begin
print 'hello world'
end
proc_print_hello
--删除存储过程
--drop proc_print_hello
--修改存储过程
alter procedure proc_print_hello
as
begin
print '你好，世界'
end
--输入参数
create proc proc_msg
@msg varchar(300)='you can you up';--设置默认值
@msg varchar(100)='LeLe'; --设置默认值
as
print @name +'说：'+@msg
go
proc_msg 'you can you up','张三'
--输出参数
create proc proc_add
@a int,
@b int,
@sum int output--标识这是一个输出参数
as
set @sum = @a + @b
go
declare @sum int
exec proc_add 12,13,@sum
print @sum
--创建一个传输过程来查询某个部门的平均薪资
--输入参数 @deptno
--输出参数 @avgSal
create proc proc_avg_dept
@deptno int,
@avgSal float output
as
select @avgSal = round(avg(sal),1)
from emp where deptno = @deptno
go
declare @avgSal float
exec proc_avg_dept 10,@avgSal output
print @avgSal
6.事务
原子性
一组insert delete update 操作要么同时成功，要么同时失败
例：银行账户转账
--事务
create table account
(
id int primary key,
username varchar(100),
balance float
)
truncate table account
insert into account values(1,'张三',1000),(2,'李四',1000)
--添加约束，余额最少10元
alter table account
add constraint ck_account_balance check(balance>=10)
--开启事务
begin transaction
declare @er int = 0
--李四转入500
update account set balance +=500
where id = 2
set @er += @@error
--张三转出500
update account set balance -=500
where id = 1
set @er += @@error
if @er = 0
begin
--提交事务
commit
end
else
begin
rollback transaction
end
--@@error是一个全局变量，他会记录最近的一条sql语句执行后的错误编号，如果执行成功，编号就是0，如果失败则是一个大于0的数。
select * from account
3037
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
