mysql -a 参数_MySQL基础知识学习

MySQL基础知识学习

1.数据库的基本概念

1.1名称解释

实体：

现实世界中客观存在并可以被区别的事物。比如“一个学生”、“一本书”、“一门课”等等。值得强调的是这里所说的“事物”不仅仅是看得见摸得着的“东西”，它也可以是虚拟的，不如说“老师与学校的关系”。

属性：

教科书上解释为：“实体所具有的某一特性”，由此可见，属性一开始是个逻辑概念，比如说，“性别”是“人”的一个属性。在关系数据库中，属性又是个物理概念，属性可以看作是“表的一列”。

元组：

表中的一行就是一个元组。

分量：

元组的某个属性值。在一个关系数据库中，它是一个操作原子，即关系数据库在做任何操作的时候，属性是“不可分的”。否则就不是关系数据库了。

码：

表中可以唯一确定一个元组的某个属性(或者属性组)，如果这样的码有不止一个，那么大家都叫候选码，我们从候选码中挑一个出来做老大，它就叫主码。

全码：

如果一个码包含了所有的属性，这个码就是全码。

主属性，非主属性， 外码：

主属性：一个属性只要在任何一个候选码中出现过，这个属性就是主属性。

非主属性：与上面相反，没有在任何候选码中出现过，这个属性就是非主属性。

外码：一个属性(或属性组)，它不是码，但是它别的表的码，它就是外码。

1.2 范式

第一范式

定义：如果关系R 中所有属性的值域都是单纯域，那么关系模式R是第一范式的

那么符合第一模式的特点就有

1)有主关键字

2)主键不能为空，

3)主键不能重复

4)字段不可以再分

第二范式

存在非主属性对码的传递性依赖 R(A,B,C) A是码 A –>B ,B–>C

定义：如果关系模式R是第一范式的，而且关系中每一个非主属性不部分依赖于主键，称R是第二范式的(什么叫部分依赖：即某些非主属性只依赖与部分主键，如关系R(A,B,C,D)主键为(A,B),C只右B决定，则C部分依赖与主键)。

所以第二范式的主要任务就是

满足第一范式的前提下，消除部分函数依赖。

第三范式

不存在非主属性对码的传递性依赖以及部分性依赖

BC范式(BCNF)

符合3NF，并且，主属性不依赖于主属性

若关系模式属于第一范式，且每个属性都不传递依赖于键码，则R属于BC范式。

通常

BC范式的条件有多种等价的表述：每个非平凡依赖的左边必须包含键码；每个决定因素必须包含键码。

BC范式既检查非主属性，又检查主属性。当只检查非主属性时，就成了第三范式。满足BC范式的关系都必然满足第三范式。

还可以这么说：若一个关系达到了第三范式，并且它只有一个候选码，或者它的每个候选码都是单属性，则该关系自然达到BC范式。

一般，一个数据库设计符合3NF或BCNF就可以了。在BC范式以上还有第四范式、第五范式。

范式总结

通俗地理解三个范式

通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：

第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；

第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来，它要求字段没有冗余。

没有冗余的数据库设计可以做到。但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是：在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

2.数据库的一些基本操作

2.1 基本操作(创建，删除，增删改查)

数据库操作

创建数据库：Create DATABASE databasename

删除数据库：drop database databasename

表操作

创建新表：

create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)

根据已有的表创建新表：

select * into 目的数据库名.dbo.目的表名 from 原表名(使用旧表创建新表)

B：create table tab_new as select col1,col2… from tab_old definition only

删除表：drop table tabname

基本表操作(增删改查等)

增加一个列：

Alter table tabname add colname coltype

删除一个列：

Alter table tabname drop column colname

添加主键：

Alter table tabname add primary key(col)

说明：删除主键：Alter table tabname

修改字段类型：

ALTER TABLE 表名 ALTER COLUMN 字段名 varchar(30) NOT NULL

更改数据库表字段类型：

alter table page_shsjgrgl alter column s1 int

drop primary key(col)

select:

select [distinct ] * from 数据表 where 字段名=字段值 having ... order by 字段名 [desc]

查询去除重复值：select * from table1

select **top 10** * from 数据表 where 字段名=字段值 order by 字段名 [desc]

update:

sql="update 数据表 set 字段名=字段值 where 条件表达式"

(3) 删除数据记录：

sql=”delete from 数据表 where 条件表达式”

sql=”delete from 数据表” (将数据表所有记录删除)

(4) 添加数据记录：

sql=”insert into 数据表 (字段1,字段2,字段3 …) values (值1,值2,值3 …)”

sql=”insert into 目标数据表 select * from 源数据表” (把源数据表的记录添加到目标数据表)

高级的操作

创建索引

create [unique] index idxname on tabname(col…。)

删除索引

drop index idxname on tabname

注：索引是不可更改的，想更改必须删除重新建。

创建视图

create view viewname as select statement

删除视图

drop view viewname

数据记录统计函数：

AVG(字段名) 得出一个表格栏平均值

COUNT(*;字段名) 对数据行数的统计或对某一栏有值的数据行数统计

MAX(字段名)

MIN(字段名)

SUM(字段名)

几个高级查询运算词

A：UNION 运算符

UNION 运算符通过组合其他两个结果表(例如TABLE1 和TABLE2)并消去表中任何重复行而派生出一个结果表。当 ALL 随UNION 一起使用时(即UNION ALL)，不消除重复行。两种情况下，派生表的每一行不是来自TABLE1 就是来自TABLE2。

B： EXCEPT 运算符

EXCEPT 运算符通过包括所有在TABLE1 中但不在TABLE2 中的行并消除所有重复行而派生出一个结果表。当ALL 随EXCEPT 一起使用时(EXCEPT ALL)，不消除重复行。

C：INTERSECT 运算符

INTERSECT 运算符通过只包括TABLE1 和TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当ALL 随INTERSECT 一起使用时(INTERSECT ALL)，不消除重复行。

注：使用运算词的几个查询结果行必须是一致的。

使用外连接

A、left outer join：

左外连接(左连接)：结果集既包括连接表的匹配行，也包括左连接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

B：right outer join:

右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。

C：full outer join：

全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录。

例子

查询数据库中含有同一这字段的表：

select name from sysobjects where xtype = 'u' and id in(select id from syscolumns where name = 's3')

不同数据库之间的复制：

复制结构：

select * into test.dbo.b from GCRT.dbo.page_shsjgrgl where 1<>1

复制内容：

insert into test.dbo.b(xm,ssdq) select xm,ssdq from GCRT.dbo.page_shsjgrgl

查看数据库中所有的数据表表名：

select name from SysObjects where type='u'

查询数据库时随机10条记录：

select top 10 * from td_areacode order by newid()

随机取出10条数据

select top 10 * from tablename order by newid()

随机选择记录

select newid()

删除重复记录

Delete from tablename where id not in (select max(id) from tablename group by col1,col2,...)

mysql总体认识">3.MySQL总体认识

3.1MySQL总体结构

Mysq架构图

上面是客户端，连接管理

中间主要是连接，线程管理，在是 查询缓存和分析器，优化器喎�"/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxwPs/Cw+bKx7TmtKLS/cfmo6zU2srHz7XNs7TmtcTOxLz+z7XNszwvcD4NCjxoMyBpZD0="mysql的物理文件组成">Mysql的物理文件组成

1.日志文件

错误日志文件，记录mysql server运行过程中遇到的严重错误信息以及启动关闭时的详细信息。

二进制日志文件：记录了Mysq中所有的修改数据库的操作，然后以二进制的形式存储在日志中，包括每条语句的执行时间和消耗的资源，和相关的事务信息。

查询日志：记录所有的查询语句。

慢查询日志：记录所有执行时间超过long_query_time变量的语句和达到min_examined_row_limit条距离的语句。

innoDB引擎在线redo日志：

2.数据文件

.frm文件：主要存放与表相关的数据，如表结构定义信息，当数据库崩溃时，可用它来恢复数据库表结构。

.myd文件：myisam存储引擎创建表时，都会有一个.myd文件用来存储数据表的数据文件。

.myi文件：主要用来存储数据文件中任何索引的数据树数。对于MyIsam类型的表，每一个表都会有一个.MYD文件和一个.MYI文件。

ibd文件和ibdata文件：用来存储InnoDB类型表的数据，包括索引数据。如果采用共享表空间，则采用ibdata文件(所有的表共享一个或多个ibdata文件)。如果是独享模式，则采用ibd文件。

其他文件：系统的配置文件。

Mysql存储引擎概述

Mysql存储引擎包括：MYISAM ,Innodb,BDB，memory,merge,ndb等

其中，Innodb为默认的存储引擎，支持外键和事务，支持行锁，存储限制为64TB

比较常用的存储引擎包括：myisam,innodb,memory,merge

Mysql工具

mysqladmin:

mysqldump:

mysql:

常用的参数：

-u: user

-p :password (直接写password时， 中间不用加空格，如-proot,表示密码为root

-P : port

3.2 权限管理与安全

3.2.1 权限表

权限表存放在mysql数据库中，由mysql_install_db脚本初始化，包括表：

user，db,host,tables_priv,columns_priv,procs_priv

user:

存储的是全局的性的权限，即表示对所有的数据库所有的权限。分为4种：用户基本信息类，权限类，安全类，资源控制类。基本的权限、资源型如下：

主键为：host,user,password，登录时，需要这三项全部匹配

db:

存储的表示用户对某个数据库的权限，主键为：host,user,db.

host:

存储的那个主机对那个db有什么权限，不区分用户，主键为：host,db

tables_priv:

存储的用户对那个表具有什么权限，主键为：host,db,user,table_name

columns_priv:

存储的 存储的用户对那个表的那个列具有什么权限

procs_priv:

存储的对存储过程和存储函数的设置操作权限。

由于用户权限信息量比较小，访问比较频繁，mysql在启动后会将权限表信息缓存起来，所以手工的修改权限表后，都需要执行flush privileges指令来重新刷新缓冲区。

用户使用grant,revoke,drop user,create user 命令修改用户权限信息时，会自动刷新权限信息。

这里执行flush命令，需要reload权限

3.2.2.创建用户

方式一：直接使用create user语句。使用create user语句会直接在mysql.user中插入一条新记录，但是创建的用户没有任何权限。另外，使用create user语句需要有全局的create user权限或是mysql数据库的insert权限。

方式二：使用grant语句创建用户并授权

grant select,insert on . to ‘test3’@’localhost’ identified by ‘test3’;

如果需要给用户test3授予能够在所有主机上的权限，则可用通配符%，如：

grant select,insert on . to ‘test3’@’%’ identified by ‘test3’;

如果需要让用户也可以授予，则在授予权限时，可加上 with grant option,这种命令需要由root帐号执行。

grant all on test.* to ‘test’@’localhost’ with grant option;

注意：好像有时候在授权时，使用%给所有主机都授权，但是使用localhost主机登录时，还是不行，需要单独给localhost授权。

好像有时候在授权并创建用户时，创建了针对所用主机登录时的密码，但是使用localhost主机登录时，提示密码不对，还是不行，需要单独给localhost登录设置密码。

这两是在phpadmin5.6.2版本中进行测试的结果。

方式三：直接在mysql.user中插入表记录。

3.2.3.删除用户

删除普通用户：drop user ‘test1’@’localhost’

直接操作mysql.user表

3.2.4 修改密码

root用户修改别人的密码

set password for ‘user’@’localhost’ =password(‘userpassword’);

自己修改自己的密码：

set password=password(‘newpassword’);

3.2.5 权限管理

授权：

grant priv_Type on database.table,database.table To user [with grant option]

PS：使用with grant option可让被授权用户拥有授权权限

回收权限：

revoke all from ‘user’@’host’ , ‘user’@’host’

revoke priv_type on database.table ,database.table2 from ‘user’@’host’

PS：在回收权限后，会在相应的表中删除相应的记录，但是user表中的记录还在，要删除user表中记录，需要使用drop user 语句

3.2.6 访问控制

访问控制分为两个阶段：连接核实阶段和请求核实阶段。

连接核实阶段：验证请求的host,user,password，与user表中的记录是否相符，如果不同，则拒绝连接。否则服务器接受连接，进入第二阶段。

请求核实：检查用户是否有足够的权限来执行请求。权限检查包括user表，db表，host表，tables_priv,columns_priv表。在确认时，首先检查user表，如果指定的权限没有在user表中授权，则到db表中找，依次类推，直到找到或是没有找到，请求没有足够的权限。

PS：对于用户登录时，需要结合IP，对于相同的用户，不同的IP,Mysql 也会认为是不同的用户。

PS2：mysql权限检查是逐级向下的，但是有的权限不需要检查那么多，如，用户登录后，执行的管理操作，则只检查user表。

3.2.7 Mysql中的安全问题

a:避免使用root用户运行MYSQL服务器:

主要是为了具有file权限的用户访问root用户创建的文件。

关于file权限：具有file权限的用户，能够执行从文件中装载数据的命令，如，load data infile ‘D:/txt.txt’ into table t5;

b:关闭不需要的服务:减轻服务器的负担。

c:修改root用户口令，删除匿名用户:mysql安装的时候，默认是无root用户密码的。

另外，mysql安装完后，mysql会初始化自动生成匿名帐号和test库(匿名账户对test数据库拥有大部分的权限)。如果存在匿名账户，则普通用户使用匿名帐号登录，执行大量的针对test库的耗时操作，则会导致服务器的性能下降。

d:设置安全密码:

e:不要把file,process,super权限授予管理员以外的帐号:

file权限：从文件中装载数据到数据库中

process权限：可以执行show processlist命令，查看当前用户正在执行的命令

super权限：能够指向kill命令，杀死用户线程(指向用户请求的线程)

关于：show processlist;(需要有process权限)

注意：只有有process权限，也能看到root用户正在执行的命令。关于正在二字的意义是：当你查询时，用户已经提交的，还没执行完的命令(一般的命令都是很快就能结束，一般显示不了，除非命令被阻塞了)。

如：test6用户锁住了user表，然后root用户登录，修改密码，执行set password=password(‘root’)命令，因为A锁住了user表，所以root用户修改密码的密码会被阻塞。这时，test用户登录执行show processlist就能看到root用户提交的命令：

当test6释放锁了之后，root用户的的命令能里面完成，test在使用show processlist则就看不到什么东西了

关于super权限：kill id ;需要有super权限

如test，test6用户同时登录到mysql.

test执行kill命令，杀死test6用户线程

之后，test6用户执行sql操作：

就能看到已经连不上了，需要退出重新登录。

f:限制用户的连接次数:使用max_user_connection参数限制用户的最大连接数

其他的资源权限还有：

g:记住Revoke all的缺陷:

revoke all privileges on . from ‘test’@’localhost’;

mysql在这里是存在漏洞：如果使用了多条语句给用户授权了，在revoke all的时候，只会撤销一条授权语句，如：

使用了三次授权语句为用户授权：

revoke all之后：还是会存在其他的权限：

这时test还是具有mysql，test数据的权限，因此，在使用revoke all时一定要确保此时该用户没有其他授权条目了！