2008从入门到精通,NET学习笔记08MySQL基础知识

2019-11-02 01:12 来源:未知

数据库范式理论

范式理论是为了建立冗余较小结构合理的数据库所遵循的规则。关系数据库中的关系必须满足不同的范式。目前关系数据库有六种范式:第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、BCNF、第四范式(4NF)、第五范式(5NF)

mysql数据库基础知识

一. 什么时候使用表锁

  对于INNODB表,在绝大部分情况下都应该使用行锁。在个别特殊事务中,可以考虑使用表锁(建议)。
  1. 事务需要更新大部份或全部数据,表又比较大,默认的行锁不仅使这个事务执行效率低,可能造成其他事务长时间锁等待和锁冲突,这种情况考虑使用表锁来提高事务的执行速度(具我在sql server中的经历,该大表有上100w,删除40w,表锁有时会造成长时间未执行完成. 还是使用分批来执行好)。
  2. 事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况可以考虑一次性锁定事务涉及的表,避免死锁,减少数据库因事务回滚带来的开销。
  使用表锁注意两点
    (1) lock tables虽然可以给innodb加表锁,但表锁不是由innodb存储引擎层管理,则是由上层mysql server负责。仅当autocommit=0, innodb_table_locks=1(默认设置)时,innodb层才知道mysql加的表锁,mysql server也才能感知innodb加的行锁。
    (2) 用lock tables对innodb表加锁时要注意, 要将autocommit 设置为0,否则mysql 不会给表加锁; 事务结束前,不要用unlock tables释放表锁,因为它会隐式的提交事务。 commit 或rollback 并不能释放用lock tables 加的表锁。必须用unlock tables释放表锁。

    下面在5.7版本数据库中,会话2也会阻塞,按上面说法是不会阻塞的,因为会话1没有设置SET autocommit =0(以后在论证)

-- 会话1 给city加表锁读,  不设置  SET autocommit =0
  LOCK TABLES city READ

  --  会话2 会阻塞
 UPDATE city SET CityCode='005' WHERE city_id=103  

  -- 会话1提交
 COMMIT;
 -- 会话1 释放表锁
 UNLOCK TABLES;

第一范式

对象的任意属性不能被拆分,每个属性有且仅有一个值,即没有重复的行,没有重复的列。

什么是数据库

数据库顾名思义,就是用来存储数据的工具,用一个比例形象的例子来比喻,就是Excel,一个Excel文件就可以看成是一个数据库。

二. 关于死锁

  在myisam中是使用的表锁,在获得所需的全部锁时, 要么全部满足,要么等待,因此不会出现死锁。下面在innodb中演示一个死锁例子:

会话1

会话2

SET autocommit =0

SELECT * FROM city  WHERE city_id=103 FOR UPDATE;

SET autocommit =0

SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE;

-- 因为会话2 已获得排他锁, 该语句等待

 SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE;

 

 

-- 死锁

 SELECT * FROM city  WHERE city_id=103 FOR UPDATE;

错误代码: 1213

Deadlock found when trying to get lock; try restarting transaction

  上面案例中, 两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。 发生死锁后,innodb会自动检测到,并使一个事务释放锁并回退(回滚),另一个事务得锁完成事务。

第二范式

在第一范式的基础上,要求所有非主属性都与主属性完全相关。假设属性1和属性2为主属性,属性3为非主属性,如果属性1或者属性2能唯一确定属性3,则不符合2NF,只有(属性1+属性2)能唯一确定属性3(有助于数据库基础性操作的实现)才符合2NF

关系型数据库

就是以行与列构成的二维数据表的形式,用来存储数据的,并且将多张数据表存储在一个单元中的存储形式,就称为关系型数据库。

三. 锁等待查看    

  涉及外部锁或表锁,innodb并不能完全自动检测到死锁,这需要设置锁等待超时参数innodb_lock_wait_timeout来解决(设置需慎重),这个参数并不是只用来解决死锁问题,在并发下,大量事务无法立即获得所需锁而挂起,将占用大量资源,甚至拖跨数据库 (在sql server中默认是-1 总是等待)。

--  下面是5秒  获取不到锁就超时
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

图片 1

第三范式

在第二范式的基础上,要求除主键外其他字段不相关,不存在依赖性。比如一张表中非主属性1,2,3,属性1=属性2-属性3,则该表不满足第三范式。(不要在数据库中存储可以简单计算得出的数据)

数据表

由行与列构成的二维结构的存储数据的表格。

BCNF

在第三范式基础上,要求表中所有字段(包括主键)都互不相关,不存在依赖性。即主属性不依赖于主属性。

记录

就是数据表中行,一横行数据我们就称为一条数据记录。

通常而言,每条数据记录都有一个ID号,我们可以把这个id理解成是excel中的行号,用来对每条记录进行区分与标记。

第四范式

表内不存在多对多关系。如果A和B是1:N的关系,A和C是1:N的关系,B和C互相独立,则不满足第四范式。

主键

很多时候我们将id称为主键,主键这是指这张表的排列顺序的依据。

第五范式

在第四范式的基础上,可以分解成更小的表。从最终结构重新建立原始结构。

字段

数据字段,就是数据表中的列。

每一个字段都需要指定一个名称,用来说明该列数据的作用,就叫作字段名。

Transact-SQL行构造器

例:用INSERT语句一次性插入多行数据

CREATE TABLE a(
    Column1 NVARCHAR(max),
    Column2 NVARCHAR(max)
);
Go
INSERT INTO a VALUES(‘1’,’1’),(‘2’,’2’),(‘3’,’3’);

常用的数据库的类型

Mysql 性能强悍,可以免费使用。

MSSQL server 微软公司的数据库软件,通常用于搭配微软体系的编程语言。

Access是office的办公套件之一。

Oracel 性能非强悍,也非常昂贵。

用存储过程新建登录名和用户名

创建登录名huyan1,密码111111,默认数据库test,切换到test数据库下,创建登录名huyan1在test数据库中的用户hy1

EXECUTE sp_addlogin ‘huyan1’,’111111’,’test’
USE test
EXEC sp_adduser 'huyan1','hy1'

或使用sp_grantdbaccess创建一个与登录名相同的数据库用户名。

EXEC sp_addlogin 'hy2';
GO
USE test
EXEC sp_grantdbaccess 'hy2'

注:仅创建登录名而没有创建数据库用户名,该登录名无法正常登录数据库。可以使用sql语句创建数据库用户名,或者右键登录名—属性—用户映射中勾选数据库。创建和删除数据库用户名语句必须在该数据库下执行。用户名hy1默认权限public。

删除新建的登录名:

EXECUTE sp_droplogin ‘huyan1’

删除新建的用户名:

USE test;
EXECUTE sp_dropuser ‘hy1’

安装与使用mysql

安装mysql的方式很多,我们使用phpstudy这个软件来安装php + mysql的运行环境。安装之后,我们就拥有了mysql的服务。而且还有两款mysql客户端。

 

Mysql分为客户端与服务端。服务端仅仅用于对软件程序提供数据通信,普通用户无法直接看到其中的数据内容。

 

客户端用来将服务端的数据,以用户可以理解的方式展示在其界面上。

 

用户权限

phpMyAdmin

phpMyAdmin是最常用的mysql客户端,它是基于php语言的。

在桌面右下角的phpstudy图标上右击弹出菜单中选择phpMyAdmin就可以启动进入了。启动之后,需要输入用户与密码进行登陆,默认的用户名密码都是root。

 

主要语句

USE test;
GRANT SELECT,UPDATE,DELETE
ON Customers
TO huyan1

REVOKE SELECT,UPDATE,DELETE
ON Customers
TO huyan1

注: GRANT语句必须在目标数据库下执行。

新建数据库

1、 要指定数据库的名称。

2、 通常选择utf8_unicode_ci字符集,在只有中文的情况下,可以选择gbk_chinese_ic或gb2312_chinese_ci 。

 

授予用户权限的前提

(1)创建登录名huyan1,密码111111,默认数据库test

EXCUTE sp_addlogin ‘huyan1’,’111111’,’test’

(2)在huyan1登录名的目标数据库test下创建用户hy

USE test;
CREATE USER hy FOR LOGIN huyan1 WITH DEFAULT_SCHEMA=test;

或者右键点击huyan1登录名,在属性—用户映射中勾选test数据库,系统会自动在test数据库下添加huyan1的用户

新建数据表

1、 选中数据库之后,点击右侧的“新建数据表”,填写“数据表名”。

2、 指定数据表的字段,也就是列。在指定字段的过程中我们要对字段添加字段名、数据类型、数据长度、是否是主键、是否自增。(多数情况下,主键是名为id的整数类型,而且是自增的。)

3、 为字段指定这一列的数据类型。

授予权限

USE test;
GRANT SELECT,UPDATE,DELETE
ON Customers
TO hy

注:这里的hy指的是数据库用户名而非登录名。

自增auto_increment

自增字段中通常存放的是整数类型的数据,用于表示数据库中的记录的序号。

自增字段的值不需要手动输入,其中的编号是自动产生的,每当向这表里面添加一条新记录的时候,自增字段会自动取出上一行字段值,然后加一,作为新记录的主键值。(主键绝对不会发生重复,即使上一条记录被删除了)。

撤销权限

撤销用户hy在Customers表中的SELECT,UPDATE,DELETE权限

USE test;
REVOKE SELECT,UPDATE,DELETE
ON Customers
TO hy

如何设置一个字段为自增

1、在创建表或在修改字段结构的时候,设置字段的数据类型为int,然后选中auto_increment上的勾。

2、当插入一条数据时,不要手动填写主键字段。

3、主键生成的数字绝对不会重复,即使记录被删除。

拒绝权限

拒绝用户hy在Customers表中的DELETE权限

USE test;
DENY DELETE
ON Customers
TO hy

注:REVOKE和DENY的区别在于,用户权限被DENY后无法通过其组或角色成员身份继承该权限,而权限被REVOKE后还可以通过继承和授予得到。

如何设置主键

1、在创建表或在修改字段结构的时候,在主键字段的”索引”下拉菜单中选择“primary”。(一张表只允许设置一个主键,通常它是int自增的。)

 

创建角色并分配给用户

在test数据库下,用存储过程创建角色p_test,该角色的所有者为hy。为p_test角色授予Customers表的所有权限。用存储过程为角色p_test分配成员用户hy。

USE test
EXEC sp_addrole ‘p_test’,’hy’;
GRANT ALL ON Customers TO p_test;
EXEC sp_addrolemember ‘p_test’,’hy’;

注:角色所有者hy并不是角色成员。用EXEC sp_addrole ‘p_test’,’hy’语句创建角色p_test并设置所有者为hy,并不代表hy是p_test的成员,拥有p_test角色的权限。

为角色p_test移除成员hy

EXEC sp_droprolemember ‘p_test’,’hy’;

小练习:

1、 新建一个产品product数据库

2、 在数据库中添加产品product表,并建立字段pId (主键)、pName(产品名称)、pModel(产品型号)、pPrice(产品价格)

3、 添加5条产品信息

4、 在数据库中再添加一个产品入库表(saveInLib),并建立字段:sid(主键)、pName(产品名称)、saveInTime(入库时间)、saveInCount(入库数量)。

5、 添加5条产品入库记录。

 

服务器角色

bulkadmin:允许运行BULK INSERT语句,用于从文本中大量插入数据到数据库中
dbcreator:允许创建,修改,删除和还原任何数据库,适用于助理DBA和开发人员
diskadmin:允许管理磁盘文件,比如镜像数据库和添加备份设备,适用于助理DBA
processadmin:允许多任务化的管理,可以通过多个进程做多件事情,也可以删除进程
securityadmin:安全管理员,管理登录名及其属性
serveradmin:服务管理员,更改服务器的配置选项和关闭服务器
setupadmin:管理链接服务器,控制启动的存储过程
sysadmin:有权执行任何任务,仅适用于数据库管理员

mySQL中的数据类型

在mySQL中每个字段,都必须明确它存放的数据的类型,一旦指定了类型,该字段(列)中的数据都必须符合这个类型的范围,否则就会引起错误。

 

并且字符等类型需要指定内容的最大长度。

 

数据库角色

int整数类型

只能存放整数

标准角色

允许用户适用单一的权限来创建角色。如创建一个叫User的角色,允许用户INSERT,SELECT,UPDATE数据库中的指定表,不允许其他任务。

varchar字符类型

可以存储任一字符,包括符号、数字、字母,但是他们都会被当成字符为处理。

应用程序角色

允许用户为特定应用程序创建密码保护

Date日期

用于存储日期和时间

预定义数据库角色

这些角色是内置的,不能被更改权限
(1) db_owner:可以做其他角色能做的所有事情,还可以做一些管理性操作
(2) db_accessadmin:可以通过添加或删除用户指定谁可以访问数据库
(3) db_securityadmin:可以修改角色成员身份和管理权限
(4) db_dlladmin:可以在数据库中运行所有DLL命令,创建修改和删除数据库对象而不必浏览其数据
(5) db_backupoperator:备份数据库
(6) db_datareader:读取所有用户所有表中的数据
(7) db_datawriter:可以添加更改删除所有用户所有表中的数据
(8) db_denydatareader:不能读取任何用户任何表中的数据
(9) db_denydatawriter:不能对任何用户任何表中的数据做添加更改删除的操作
(10) public:每个数据库用户都属于public角色,未对用户授予权限时该用户将继承public权限。该角色不能被删除。

数值类型

TinyInt 非常小的整数存储格式,1字节(8位的二进制数),它的取值范围是:不带符号时0 ~ 255,带正负号时-128 ~ 127

SmallInt 小整数,2字节,不带符号时0~65535,带正负号时-32768~32767

MediumInt 中等整数,3字节,不带符号时0~16777215,带正负号时-8388608 ~ 8388607

Int 标准整数,4字节,不带符号时0 ~ 4294967295 ,带正负号时-2147483648 ~ 2147483647

BigInt 大整数,8字节,不带符号时0~18446744073709551615

 

Float 单精度浮点数,4字节,最小值,正负1.175494351E-58(1.175494351*10-58)最大值,正负3.102823466E+38(3.102823466E*1038)

Double 双精度浮点数,8字节,最小值,正负2.22507385072014E-308,最大值:正负1.17976931348623157E+308。

 

Decimal 以字符串形式存储浮点数。它的取值范围不是固定的,取决于用户指定的长度。

系统数据库

数值类型的字段可以设置的参数:

master数据库

存储了登录名和用户ID所属角色,系统配置,数据库名和初始化信息,是最重要的数据库。

unsigned属性

“整理”菜单中的unsigned选项,代表无符号,代表这一字段中的数值不能为负数,因为无符号就是代表没有正负号。如果“整理”菜单留空,什么都不选的话,就是代表有符号,可以为正数也可以为负数。

model数据库

存储了创建数据库时的一些预定义标准如数据库初始大小,特定信息集等,是tempdb数据库的基础。

unsigned zerofill属性

当数据的宽度小于类型的最大宽度时,则在数据前面自动填充0

tempdb数据库

临时性数据库,在sql server2008启动时新建,在sql server2008关闭时丢失。主要存储用户建立的临时表和临时存储过程。

auto_increment

自增

msdb数据库

给sql server2008提供必要的信息来运行作业。

默认à定义

这个选项代表,填充数据时如果该字段为空值时,所使用的默认值。

数据库文件和日志

字符串数据类型

字符类型可以存储任何值,甚至是二进制形式的图像、声音、视频。

CHAR[M] 代表M字节的字符。

varChar 常用的字符存储格式,使用时需要指定最大内容长度。

主数据文件

一个数据库对应一个主数据文件,扩展名为.mdf,包含数据库的启动信息并指其他文件,用户数据和对象可存储在主数据文件中,也可以存储在辅助数据文件中。

存储大容量的文本

TinyBlob ,blob,TinyText,text

前两个是代表二进制字符串,后两个是代表非二进制字符串,都可以存储大量的文本信息。

MediumBlob,MediumText

LongBlob,LongText

辅助数据文件

可选的,由用户定义存储用户数据的文件,扩展名为.ndf,当数据库超过单个Windows文件的最大限制,可使用辅助数据文件将数据分散到多个磁盘上,使数据库能够继续增长。

枚举类型

ENUM / SET 类型

设置其默认值为(‘值1’, ‘值2’, ‘值3’,…),由用户指定多个可选值,字段中的值必须是其中之一,最多只能有65535个可选值。

 

事务日志文件

保存了用于恢复数据库的日志信息,每个数据库必须至少有1个日志文件。扩展名为.ldf。

日期时间类型

Date 1000-01-01 ~ 9999-12-31

TIME -838:59:59~838:59:59

DateTime 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59

TimeStmp 1970-01-01 00:00:00 到2037年的之间的某一个时刻

Year 存储1901~2155年的一个年份。

 

数据库快照(database snapshot)

数据库快照是源数据库的静态只读视图,与源数据库位于同一服务器实例上,与源数据库在事务上一致,源数据库更新时,数据库快照也将更新。一个数据库可以存在多个数据库快照。

小练习:

1、 创建一张员工数据表,employee,包含字段:eId(标准整数,主键、自增)、姓名eName(varchar,50)、年龄eAge(tinyInt,无符号)、工号eNum(smallInt,不足的位数用0填充)、学历(枚举:初中~研究生)、性别(枚举:男,女)、出生日期(DateTime)、基本工资(Float,默认:1300)、自我介绍text。然后输入五名员工的信息。

 

 

T-SQL语言基础之增删改查

优点

(1) 用于报告目的。客户端可以查询数据库快照,以便利用创建快照时的数据编写报表。
(2) 用于保存历史数据以生成报表。数据库快照保留了某个时间点的历史数据,方便用户日后对该时间点的历史数据生成报表。
(3) 通过带有镜像数据库的数据库快照来访问镜像数据库,释放主体数据库上的资源。
(4) 使数据免受管理失误带来的影响
(5) 如果源数据库上出现用户错误,可以将数据库恢复到创建数据库快照时的状态。
(6) 管理测试数据库。在第一轮测试开始前,对测试数据库创建数据库快照。在测试结束后可利用数据库快照将数据库恢复到测试前的状态,以便重新开始测试。

注:数据库快照与源数据库相关,且无法对脱机和损坏的数据库进行恢复,不能替代备份和还原。所有恢复模式都支持数据库快照。

SQL语句

是一门专门用于数据库操作的语言,SQL语句的语法不仅仅适用于mysql数据库,同时也适用于几乎所有的主流数据库。当然不同公司出口的数据库在语法细节上还是有些差异。

 

数据库快照的限制

(1) 数据库快照存在期间,不能对源数据库进行分离,还原和删除操作,但可以备份。
(2) 每次更新源数据库时都会对快照进行“写入时复制”操作,源数据库的I/O性能受到影响。
(3) 不能从源数据库或任何快照中删除文件。
(4) 必须与源数据库在同一个服务器实例上创建和保存。
(5) 依赖于源数据库,但不是冗余存储,无法防止磁盘错误或其他类型的损坏。
(6) 快照更新时用尽磁盘空间或遇到其他错误,则成为可以快照,必须删除。
(7) 快照只读,无法升级,因此升级后不可用。
(8) 无法对model数据库,tempdb数据库,master数据库创建快照。
(9) 不能附加,分离,备份,还原数据库快照,不能对数据库快照中的文件进行删除。
创建数据库快照的唯一方式是使用Transact-SQL语句。且Microsoft SQL Server Management Studio不支持数据库快照。

select语句

查询语句,专门用于在数据表中按照用户指定的条件进行查询。

TAG标签:
版权声明:本文由澳门金莎娱乐网站发布于数据库,转载请注明出处:2008从入门到精通,NET学习笔记08MySQL基础知识