当前位置:   article > 正文

mysql数据库数据的心得_mysql数据库总结

mysql数据库完整性约束心得体会

数据库定义语句言DDL(Data Definition Language)

创建数据库:create Database database_name 执行成功显示(Query Ok).

查看数据库:show databases;

选择数据库:use databases_name; 出现Database changed

删除数据库:drop database database_name 所有数据库对象也会删除

查看数据库所支持的存储引擎:

show engines G

show variables like 'have%';

查询默认存储引擎:show variables like 'storage_engine%'

修改默认存储引擎:在my.ini配置文件中该掉default-storage-engine=? 修改后需要重启MySQL服务

查看表的定义:describe table_name

查看表的详细定义:show create table table_name G

删除表:drop table table_name

该表名:alter table old_name rename new_name;

增加列在最后一个位置:alter table table_name add 属性名 属性类型;

第一个位置增加列:alter table table_name add 属性名 属性类型 first;

在某个列之后增加字段:alter table table_name add 属性名 属性类型 after 属性名

删除字段:alter table table_name drop 属性名;

修改属性的数据类型:alter table table_name modify 属性名 属性类型;

修改属性名:alter table table_name change 旧属性名 新属性名 旧属性类型;

同时修改属性名和属性:alter table table_name change 旧属性名 新属性名 新属性名;

自连接外连接?

单表查询sql语句

distinct避免重复数据:select distinct 列名 from 表名

四则运算操作:加减乘除取余。+ - * / %

例:select 属性*12 from 表名;

设置显示格式:

concat()来连接字符串

例:select concat(ename,‘雇员的年薪为:’,sal*12) as yearsalary from table_name;

条件查询

where + 条件

AND与 OR或 XOR异或 NOT非

is null/not null 是否空的条件

between a and b :a到b之间

in(a,b,c,d....)判断字段是否是在集合里面,可以代替or

不在集合里就是 not in(a,b,c......)

not in集合中如果有null,则不会出现任何的查询结果,而in里面不会

like来模糊查询

filed like value:来判断字段和value字符串是否相匹配

like关键字支持的通配符:

“_”匹配一个字符

“%”任意长度的字符,可以是0个,1个等多个字符。

列:查第二个字符为A的:字段 like '_A%'

第二个字符不为A的: not 字段 like '_A%'

like '%%'表示查询所有的记录

排序数据记录

排序在查询语句的最后默认是asc的也就是升序

单字段排序:

oder by 字段 desc/asc

多字段排序:

oder by 字段 desc/asc,字段 desc/asc,......

限制数据记录查询数量

limit(起始下标,查询结果数量);起始下标默认从0开始

统计函数

avg()平均值 sum()总和 max()最大值 min()最小值

count(*):对表中记录进行统计,不管字段是否包含null

count(field):对指定字段的记录进行统计,将忽略null值

所操作的表中没有任何记录的话Count()函数返回0,而其他统计函数显示null

分组查询

group by 字段

通过分组查询得到的每个组中随机的一条记录,具有很大的不确定性,所以可以通过函数group_concat(字段)来实现

多个字段分组查询:group by field1,field2,......filedn;首先按照field1进行分组再按照field2进行分组以此类推

对分组实现条件限制用having不用where

多表数据记录查询

内连接查询

from table_name1 inner join table_name2 on 匹配条件

自连接查询

内连接查询中一种特殊的等值连接,表与自身进行连接

等值连接

三个以上的表:from table_name1 inner join table_name2 on 相等条件 inner join table_name3 on 相等条件

不等连接

除了等于关系的运算符

外连接查询

from table_name1 left|right|full table_name2 on 条件

合并查询

select field1 field2....fieldn from tablename1

union | union all

select field1 field2....fieldn from tablename1

union会去掉重复数据,union all不会

连接查询性能差,因此出现了连接查询的替代者子查询

存储过程和函数

简单的可以理解为一条或多条sql语句的集合。存储过程和函数就是事先经过编译并存储在数据库中的一段SQL语句集合。

存储过程和函数的区别

函数必须有返回值,而存储过程没有。

存储过程的参数类型远远多于函数参数类型。

存储过程的优点:

存储过程只在创造时进行编译,以后每次执行存储过程都不需要再重新编译,而一般SQL语句每次执行就需要编译一次,所以存储过程可以提高数据库执行速度。

对数据库进行复杂操作时,可以将此复杂过程封装起来与数据库提供的事务处理结合一起使用。这些操作如果用程序来完成的话,就变成了一条条的sql语句,可能需要多次连接数据库。而换成存储过程只需要连接一次数据库就可以了。

存储过程可以重复使用,减少开发人员的一个工作量。

安全性高,可设定用户对存储过程的使用权限。

存储过程缺点:

调试麻烦。

比单句SQL语句复杂。

在编写存储过程和函数时,需要创建这些数据库对象的权限。

数据库事务

主要是为了保证数据的完整性

事务具有以下4个特性:

原子性:数据修改操作只能是完全提交或者完全回滚。

一致性:完成事务时必须使所有的数据从一种一致性变更为另一种一致性,以确保数据的完整性。

隔离性:及当前事务不会查看有另一个并发事务正在修改的数据。通过锁机制实现。

持久性:事务完成后,所做的修改对数据的影响是永久的。及重启或者系统故障数据仍然可以恢复。

REDO日志和UNDO日志

REDO日志:

​ 事务执行时需要将执行的事务日志写入REDO日志。在mysql崩溃恢复时会重新执行REDO日志中的记录。

UNDO日志:

​ 主要用于事务异常的回滚。复制事务前的数据库内容到UNDO缓冲区,然后在适当的时间将内容刷新到磁盘。

​ 所有的UNDO日志均存放在表空间对应的.ibd数据文件中。UNDO日志又被称为回滚段。

MySQL事务控制语句

使用begin开始事务、commit结束事务、rollback回滚事务。

事务隔离级别

SQL标准定义了4种隔离级别:

未提交读:所有事务可以看到其他未提交事务的执行结果。

set global transaction isolation level read uncommitted;

提交读:大多数数据库系统默认的隔离级别,但不是mysql默认的隔离级别。事务只能看见已经提交事务所做的改变。

set global transaction isolation level read committed;

可重复读:MySQL的默认事务隔离级别。能确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。会导致幻读(在一个事务的两次查询中数据笔数不一致)的问题。InnoDB存储引擎MVCC机制:通过为每个数据行增加2个隐含值得方式来实现。

set global transaction isolation level repeatable read;

可串行化:在每个读的数据行上加上共享锁。在这个级别,可能会导致大量的超时现象和锁竞争,一般不推荐使用。

set global transaction isolation level serializable;

InnoDB锁机制

共享锁S(Share):粒度是行或元组(多个行)。一个事务获取了共享锁之后,可以对锁范围内的数据进行读操作。

排他锁X(eXclusive):粒度是行或元组。一个事务获得排它锁后,可对锁范围内的数据进行写操作。

意向锁:

意向锁是一种表锁,锁的粒度是整张表。分为意向共享锁IS和意向排它锁IX两类。

为了尽可能提高数据库的并发量,每次锁定的数据范围越小越好,越大的锁其消耗的系统资源越多,系统性能下降。

锁粒度

锁的粒度主要分为表锁和行锁。

表锁管理锁的开销最小,同时允许的并发量也是最小的锁机制。MyISAM存储引擎使用该锁机制。

行锁可以支持最大的并发。InnoDB存储引擎使用该锁机制。

索引

索引与图书目录类似,主要是为了提高从表中检索数据的速度。索引是创建在数据库表对象上的,由表中的一个字段或多个字段生成的键组成,这些键存储在数据结构(B-树或哈希表)中。根据索引的存储类型,可以将索引分为B型树索引(BTREE)和哈希索(HASH)引。

InnoDB和MyISAM存储引擎支持BTREE类型索引,MEMORY存储引擎支持HASH类型索引,默认为前者索引。

适用索引的情况

经常被查询的字段,即where子句中出现的字段。

在分组的字段,即在group by子句中出现的字段。

存在依赖关系的子表和父表之间的联合查询,即主键和外键字段。

设置唯一完整性约束的字段。

不适合创建索引:

在查询中很少被使用的字段。

拥有许多重复值得字段。

索引的基本操作:

创建索引:

创建表时创建普通索引:

create table table_name(

属性名 数据类型(),

属性名 数据类型(),

属性名 数据类型(),

属性名 数据类型(),

.....

index|key 索引名(属性名(长度) asc|desc)

);

为了校验数据表中索引是否创建成功可以:

show create table table_name \G;

为了校验数据库表中索引是否被使用:

explain

select * from table_name where 有索引的属性名=1 \G;

在已经存在的表上创建普通索引:

create index 索引名

on 表名(属性名(长度) as|desc);

alter table table_name add index|key 索引名 (属性名(长度) asc|desc);

创建和查看唯一索引:

创建唯一索引时,限制索引的值必须是唯一的。

create table table_name(

属性名 数据类型(),

属性名 数据类型(),

....

unique index|key 索引名 (属性名(长度) asc|desc)

);

create unique index 索引名

on 表名(属性名(长度) asc|desc);

alter table table_name

add unique index|key 索引名 (属性名(长度) asc|desc);

创建全文索引:

将上述唯一索引的unique换成fulltext就行了

全文索引主要关联在数据类型为char、varchar和text的字段上,以便能够更快地查询数据量较大的字符串类型的字段。

只能在存储引擎为MyISM的数据库表上创建全文索引。

创建多列索引:

create table table_name(

属性名 数据类型(),

属性名 数据类型(),

....

index|key 索引名 (属性名1(长度) asc|desc,

属性名2(长度) asc|desc,

属性名3(长度) asc|desc,

.....

)

);

create index 索引名

on 表名(属性名1(长度) asc|desc,

属性名2(长度) asc|desc,

属性名3(长度) asc|desc,

.....

)

alter table table_name

add index|key 属性名1(长度) asc|desc,

属性名2(长度) asc|desc,

属性名3(长度) asc|desc,

.....;

删除索引:

drop index index_name on table_name

视图

为什么使用视图?

mysql从5.0.1版本开始提供。

​ 数据库中关于数据的查询有时非常复杂,例如表连接、子查询等,因为他们的逻辑复杂,编写语句比较多。当这种查询需要重复使用时,则不会每次都能编写正确,从而降低了数据库的实用性。

​ 为了提高复杂SQL语句的复用性和表操作的安全性,数据库提供了视图特性。它在本质上是一种虚拟表,行和列数据来自定义视图的查询索引用的基本表,并在具体引用时动态生成。这样程序员只能看到视图中定义的数据,而不是视图所引用表中的数据,从而提高数据库中数据的安全性。

​ 视图的功能实际是封装查询语句。

视图的特点:

视图的列可以来自于不同的表。

视图是由基本表产生的表(虚表)。

视图的建立和删除不影响基本表。

对视图内容的更新直接影响基本表。

当视图来自多个基本表时,不允许添加和删除数据。

创建视图:

视图命名规范一般为view_xxx或v_xxx。

create view view_name

as 查询SQL语句

查看视图:

use view;

show tables;//显示表中所有视图名

show table status from view like "view_name"\G;//查看视图对象详细信息

show create view viewname \g;查看视图定义信息

describe|desc view_name 查看视图设计信息

select * from views where table_name='view_name' \G;

删除视图:

drop view view_name;

修改视图:

create or replace view view_name as 查询语句

//alter语句修改

alter view view_name as 查询语句

通过视图操作基本表:

通过视图查询数据,与通过表进行查询完全相同,只不过通过视图查询比表更安全、更简单、实用。

select *from view_name;

insert into view_name (field1,field2...) values(xx,xx,xx..);

delete from view_name where ...;

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/你好赵伟/article/detail/680232
推荐阅读
相关标签
  

闽ICP备14008679号