这篇博客详细介绍了SQL数据库的操作，包括创建数据库的默认和指定参数方式，删除数据库的逻辑，以及建表和各种约束的设定。内容涵盖主键、非空、标识列、检查、默认值和外键约束。此外，还讨论了SQL查询的基本过程、WHERE和HAVING子句的使用，以及表连接和子查询的概念。文章最后提到了存储过程、事务和触发器的应用。
java企业级软件
前端(网页) 后台(java) 数据库
建库 建表 建约束
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
#数据库如果存在，就先将其删除
建表
建约束
主键约束:唯一的标识这条记录,主键值要求唯一非空 ,primary key
非空约束:字段不能为null值，not null
标识列约束: identity(种子,增量) 默认 identity(1,1)
设置字段的值自增
检查约束:规定字段取值的范围 check(表达式)
默认值约束: 设置字段的默认值 default:默认值
外键约束:字段的值引用另外一张表的主键或唯一键的值 references
唯一约束:字段的值必须唯一, unique
增删改
查
查询的基本过程
select * from emp where age = 22
where 子句的内容
where 关系表达式 > >= < <= != <> =
and or not
between and
过滤null值只能用 is null和is not null
不能用 = !=
in(在某个取值范围) not in(不在某个取值范围)
where子句过滤的是记录，实体信息
group by 和 having
having 过滤的是组信息
group by 之后的字段称为组标识
在一个查询中如果出现了group by 子句,那么在select子句中只能出现组标识
如果要出现其他字段，这个字段必须加聚合函数
having之后也只能出现组标识和聚合函数
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 (排序)
表连接与子查询
表连接
为什么要使用表连接？
员工姓名 部门名称
交叉连接 --无条件连接，获得笛卡儿积
内连接-- 将符合连接条件的两条记录连接起来
最后的结果集只会显示匹配的数据
外连接（左右）
查询部匹配的记录
查询所有没有安排部门的员工
查询所有没有员工的部门
子查询(where,select,虚拟表)
查询薪资最高的员工
员工姓名 薪资 平均薪资
员工姓名 薪资 部门平均薪资
exists 与 not exists
where exists(子查询)
子查询查询有记录 ，返回true
子查询没有记录,返回false
where not exists(子查询)
子查询查询有记录 ，返回false
子查询没有记录,返回true
t-sql
transact-sql
标准sql + 变量 + if + 循环 + 游标
存储过程
数据库对象(表,索引,视图,函数...)
类似于函数
create procedure 过程名
参数
as
声明变量
begin
sql,t-sql代码
end
简化
create proc 过程名
参数
as
变量声明
sql,t-sql
go
参数有两种: 输入参数，输出参数
事务
原子性
一组 insert delete update操作要么同时成功，要么同时失败
张三: 1000 1000
李四: 1000 1500
触发器***
创建数据库使用默认参数
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
create database db01
建表
部门表
删除表
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)
)
select * from dept
select * from emp
在表建好后添加约束
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)
添加
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
修改
select * from emp
update emp set sal=20000,age=21
where ename='李四'
查询
查询年龄20~21
select * from emp where age>=20 and age<=21
select * from emp where age between 下限 and 上限
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
查询每个部门的最高薪资，最低薪资,平均年龄
select deptno,max(sal) 最高薪资,min(sal) 最低薪资,
avg(age) 平均年龄
from emp
group by deptno -- 组标识，按照什么分组
聚合函数
max:最大值
min:最小值
avg:平均值
sum:组求和
count:组计数
查询最高薪资超过15000的部门
select deptno,max(sal) maxSAl
from emp
group by deptno
having max(sal) > 15000
select *
from emp e cross join dept d
内连接
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,'后勤部','长沙')
select * from dept
查询所有没有安排部门的员工
select e.ename 员工姓名,d.dname 部门名称
from dept d right join emp e
on e.deptno = d.deptno
where d.dname is null
查询所有没有员工的部门
select d.dname,e.ename
from emp e right join dept d
on d.deptno = e.deptno
where e.ename is null
select * from emp
select * from dept
主查询/子查询
查询薪资最高的员工
select * from emp --主查询
where sal =(
select max(sal) from emp --子查询
)
员工姓名 薪资 平均薪资
select ename,sal,(select round(avg(sal),1) from emp) 平均薪资
from emp
员工姓名 薪资 部门平均薪资
select ename,sal,(
select round(avg(sal),1) from emp
where deptno =e.deptno
) 部门平均薪资
from emp e
子查询做为虚拟表
select tb.ename,tb.dname
from (select e.empno,e.ename,e.sal,d.dname
from emp e inner join dept d
on e.deptno = d.deptno) tb
with tb(empno,ename,sal,dname)
as(
select e.empno,e.ename,e.sal,d.dname
from emp e inner join dept d
on e.deptno = d.deptno
)
select ename,dname from tb
select * from emp
哪些部门拥有年龄在25岁以上的员工
select distinct d.deptno,d.dname
from dept d inner join emp e
on d.deptno = e.deptno
where age>=25
select *
from dept d
where exists(
select 1 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
)
-- @---
declare @a int = 1
print @a
declare @b varchar(100)
set @b = '你好'
print @b
declare @age int = 19
if @age>=18
begin
print '成年'
end
else
begin
print '未成年'
end
declare @score int = 67
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
print '总和:'+convert(varchar,@sum)
创建一个存储过程打印Hello World
alter procedure proc_print_hello
as
begin
print '你好，世界'
end
proc_print_hello
删除存储过程
drop procedure 过程名
输入参数
alter proc proc_msg
@msg varchar(300)='you can you up', --设置默认值
@name varchar(100)='eric'
as
print @name+'说:'+@msg
go
proc_msg '你好啊','tom'
输出参数
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 output
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
银行存取款问题
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)
select * from account
--李四转入500
begin transaction
update account set balance += 500
where id = 2
commit
--张三转出500
begin transaction
update account set balance -= 500
where id = 1
rollback transaction
select * from account
@@error 是一个全局变量,它会记录最近一条sql语句
执行后错误编号,如果执行成功编号就是0,如果失败
是一个大于0的数
select xxx from emp
print @@error
--------------------------------------
加事务后的处理
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
select * from account
扫一扫
1377
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
