mysql 优化设计库_数据库的优化设计

你的名字 2022-10-27 02:37 195阅读 0赞

一，数据库设计

设计数据库的步骤：

1.需求分析:分析客户的业务和数据库处理需求。

2.概要设计：绘制数据库的E-R图，设计人员和客户之间进行沟通，确认需求的正确性和完整性。

3.详细设计阶段：将E-R图转换为多张表进行设计。

映射基数

一对一   1：1

一对多   1：N

多对一    N:1

多对多    M:N

实体关系

矩形表示实体集

椭圆表示属性

菱形表示联系集

第一范式：

第一范式的目标是确保每列的原子性，每列都不可再分的最小数据单元。

第二范式：

第二范式在第一范式的基础上更进一层，其目标是确保表中的每列都和主键相关。

第三范式：

第三范式在第二范式的基础上更进一层，第三范式的目标是确保每列都和主键列直接相关联，而不是间接相关。

二,数据库的实现

创建数据库：

use master

if exists(select  \* from sysdatabases where name='Myschool')---首先检查是否有同样的数据库

drop database Myschool

create database myschool

(

name=''--文件逻辑名

filename=''--文件物理名

size=5mb---文件的初始大小

maxsize=100mb---文件增长的最大值

filegrowth=15%----文件的增长率

)

建立数据库表

use myschool

if exists(select \* from sysobjects where name='student')-----首先检查是否有同样的表

drop table student

create table student

(

......

)

添加约束

alter table student  ---添加主键约束

add constraint pk\_studentNo primary key(student)

alter table student  ----添加唯一约束

add constraint UQ\_identityCard  UNIOE(identityCard)

alter table student -----添加默认约束

add constraint DF\_address  default('地址不详')

alter table student -----添加检查约束

add costraint CK\_BornDate  Check(borndate>=1980-01-01)

alter table Result ---外键约束

add constraint FK\_studentNo

foreign key(studentno)References student(studentNo)

SQL编程

局部变量

声明变量

declare @name varchar(20)

declare @age int

set @name='张三'---给变量赋值

set @age=18

查看于张三相邻的学号

declare  @studentno int

declare @name nvarchar(20)

select @studentno=studentno from student

where studentname=@name

select \* from student

where(studenno=@studentno+1)or(studentno=@studentno-1)

全局变量

@@error --最后一个T-sql错误号

其他也有写

数据库类型转换

cast() 和 convare()函数

'姓名:'+@name+',性别:'+@sex+',年龄:'

\+cast(@age as varchar)

\--+convert(varchar,@age)

\--两个转型函数:

1.convert -- 语法:  convert(类型,数据)

2.cast    -- 语法:  case(数据as 类型)

区别

convert(narchar(20),'2016-6-9',120)--日期

case

when  条件1 then 结果1

when 条件2  then 结果2

end

insert into shuying(Datet,sy)values

('2016-1-2','赢'),

('2016-1-3','赢'),

('2016-1-2','输'),

('2016-1-3','输')

select \* from shuying

select Datet as 日期,

sum(case when sy='赢'then 1 else 0 end)as 胜,

sum(case when sy='输' then 1 else 0 end)as 负

from shuying

group by Datet

![4ba7e5a5f5d8e3cd5637f52113a40820.png][]

高级查询

简答子查询

查询张三年龄大的人

select studentno,studentName,sex,bordate,address from student

where Borndate>

(

select Borndate from studet where studentname='张三'

)

用表连接来用子查询

select studentname from student where studentno=

(

select studentno from result

inner join subject on result.subjectno=subject.subjectno

where studentResult=60 and subjectname='java logic'

)

in 和not in

in子查询使用in关键字可以使父查询匹配子查询返回的多个单列值。

ont in

和子查询相反的结果，返回列值。

exists和not exists

if exists 如果子查询的结果非空，则返回exists(子查询)将返回true否则返回false

not exists和in一样可以在前加not表示不存在。

子查询注意事项：

子查询语句可以嵌套在SQL语句中任何表达式出现的位置

在select语句中，子查询可以被嵌套在select语句的列，表和查询条件中，即select子句，from子句，where子句，group by子句，having子句。

在子查询的select子句中不能出现TEXT,NTEXT或IMAGE数据类型的列。

事物：

事物是一种机制，一个操作序列，它包含了一组数据库指令。

事物有四个属性，原子性，一致性，隔离性，持久性。

事物分类：

![38feeba14f78cbe6f14b959193a985db.png][]

事物的使用示例：

![1fbccf231c23a29cd1553ac7673c2d0b.png][]

视图

使用视图的注意事项：

视图是一个虚拟表，通常是作为执行查询的结果而创建的。

1.每个视图可以使用多个表

2.与查询相似，一个视图可以嵌套另一个视图，但最好不要超过三层

3.视图定义中的select语句不能包括：

>order by子句除非在select语句的选择列表中也有一个TOP子句

>into 关键字

>引用临时表或变量表

索引

索引分类有：

，唯一索引：唯一索引不允许两行具有相同的索引值

，主键索引：在数据库关系图中为表定义一个主键索引，主键索引是唯一索引的特殊类型。

，聚集索引:在一张表中聚集索引只能有一个，默认为主键。

，非聚集索引：非聚集索引建立在索引页上，查询数据时可以从索引中找到记录存放的位置。非聚集索引最多有254个

，复合索引：在创建索引时并不是对其中一列创建索引，于主键一样可以将多个列组合作为索引。

，全文索引：全文索引是一种特殊类型的基于标记的功能性索引。

create unique index index\_name--创建索引

on table\_name

with fillfactoe=30--填充因子

储存过程

1.模式化程序设计

只需要创建一次储存过程并将其存储在数据库中。

2.执行速度快效率高

3.减少网络流量

4.具有良好的安全性

用户自定义存储过程

创建不带参数的存储过程

Create proc

usp\_selectstuAs

SelectStudentName,Gender,GradeId,Phone

fromdbo.Student

创建带入参数的存储过程

Create proc

usp\_stuInfo

@gradeid int=2

Select \* from

student where gradeId=@gradeid

调用存储过程：exec

usp\_stuInfo 2

创建带出参数

createprocusp\_selectGrade@namenvarchar(10),@gradeidintoutput

Select@gradeid=gradeidfromstudentwherestudentname=@name

print@gradeid

调用存储过程

declare@idint

execusp\_selectGrade'李小龙',@idoutput

数据库分页：

假设现在有这样的一张表：

CREATE TABLE test

(

id int primary key not null identity,

names varchar(20)

)

然后向里面插入大约1000条数据，进行分页测试

假设页数是10，现在要拿出第5页的内容，查询语句如下：

\--10代表分页的大小

select top 10 \*

from test

where id not in

(

\--40是这么计算出来的：10\*(5-1)

select top 40 id from test order by id

)

order by id

原理：需要拿出数据库的第5页，就是40-50条记录。首先拿出数据库中的前40条记录的id值，然后再拿出剩余部分的前10条元素

第二种方法：

还是以上面的结果为例，采用另外的一种方法

\--数据的意思和上面提及的一样

select top 10 \*

from test

where id >

(

select is null(max(id),0)

from

(

select top 40 id from test order by id

) A

)

order by id

原理：先查询前40条记录，然后获得其最id值，如果id值为null的，那么就返回0

然后查询id值大于前40条记录的最大id值的记录。

这个查询有一个条件，就是id必须是int类型的。

第三种方法：

select top 10 \*

from

(

select row\_number() over(order by id) as rownumber,\* from test

) A

where rownumber > 40

原理：先把表中的所有数据都按照一个rowNumber进行排序，然后查询rownuber大于40的前十条记录

这种方法和oracle中的一种分页方式类似，不过只支持2005版本以上的

第四种：

存储过程查询

创建存储过程

alter procedure pageDemo

@pageSize int,

@page int

declare @temp int

set @temp=@pageSize\*(@page - 1)

begin

select top (select @pageSize) \* from test where id not in (select top (select @temp) id from test) order by id

end

执行存储过程

exec 10,5

![bf5a7fa0c70ac7300b7268068dfc332f.png][]

[4ba7e5a5f5d8e3cd5637f52113a40820.png]: /images/20221024/c7c662c3d7ec44b69372f121fceb9385.png
[38feeba14f78cbe6f14b959193a985db.png]: /images/20221024/59448f7256f54733994499dac5afb679.png
[1fbccf231c23a29cd1553ac7673c2d0b.png]: /images/20221024/f562bfc6082c4eb9a0b1707f9bb6598c.png
[bf5a7fa0c70ac7300b7268068dfc332f.png]: /images/20221024/3a7c8b113a414c21bc5e639bea24d530.png