MySQL知识学期整理

一 .  认识数据库

数据库按照特定的规律，组织和存储数据，是信息技术的重要组成部分。

二 .数据库设计

（一）.E-R图 

（二）.模式转换

（三）.范式理论

三 .数据定义

（一）.创建与管理数据库

create { database | schema  } [ if not exitsts ] 数据库名

[ [ default ] character set 字符集名

| [ default ] collate 校对规则名 ]

（二）.创建与管理数据库表

(1) .创建表

create table 表名

(2) .修改表

alter [ ignore ] table 表名

add [ column ] 列名 [ first | after 列名 ]

| alter [ column ] 列名

{ set default 默认值 | drop default }

| change [ column ] 旧表名 列定义

[ first | after 列名 ]

| modify [ column ] 列定义 [ first | after 列名 ]

| drop [ column ] 列名

| rename [ to ]  新表名

（三）.数据完整性约束

(1) .域完整性 default | check

(2) .实体完整性 primary by | unique

(3) .参照物完整性 foreign key

四 .数据操作

（一）.数据插入

insert [ ignore ] [ into 表名 ( 列名 ，...) ]

values( { 表达式 | default } ，... ) , (...) , ...

| set 列名 = { 表达式 | default } ，...

（二）.数据修改

update [ ignore ] 表名

set 列名1 = 表达式1 [ , 列名2 = 表达式2 ... ]

[ where 条件 ]

（三）.数据删除

truncate table 表名

五 .数据查询

（一）.单表查询

select [ all | distinct ] 输出列表达式 ，... [ from 表名1 [ , 表名2 ] ... ]

[ where 条件 ]

[group by  { 列名 | 表达式 | 列编号 }

[ asc | desc ] , ...

[ having 条件 ]

[ order by { 列名 | 表达式 | 列编号 } [  asc | desc ] , ...]

[ limit { [ 偏移量，] 行数 | 行数 offset 偏移量 } ]

（二）.多表查询

from 表名1 [ as ] 别名1 ] [ , 表名2 [ as ] 别名2 ] ] ... | join 子句

（三）.分类汇总与排序

(1) . count (求组中项数，返回 int 类型的整数 )

(2) .max ( 求最大值 ）

(3) .min ( 求最小值 )

(4) .sum ( 返回表达式中所有值得和 )

(5) .avg ( 求组中值得平均值 )  

六 .数据视图

（一）.创建与查询视图

create [  or replace ] view 视图名 [ ( 列表名 ) ] as select 语句 [ with [ cascaded | local ] check option ]

（二）.操作视图

(1) .聚合函数

(2) .distinct 关键字

(3) .group by 子句

(4) .order by 子句

(5) .having 子句

(6) .union 运算符

(7) .位于选择列表中的子查询

(8) .from 子句中包含多个表

(9) .select 语句中引用了不可更新视图

(10) .where 子句中的子查询，引用 from 子句中的表

七 .索引与分区

（一）.索引的分类

(1) .普通索引（ index )

(2) .唯一索引性（ unique )

(3) .主键（ primary key )

(4) .全文索引（ fulltext )                                                                                                                     

（二）.创建索引

create [ unique | fulltext ] index 索引名 on 表名 ( 列名 ）[ ( 长度) ] [ asc | desc ] , ...)

（三）.删除索引

(1) .drop index 索引名 on 表名

(2) .alter [ ignore ] table 表名 | drop primary key | drop index 索引名

（四）.索引对查询的影响

索引可以提高检索数据的速度。

（五）.数据库分区

数据库分区就是在物理层面将表分割成许多小块进行存储的块号。

八 . 数据库编程

（一）.编程基础知识

(1) .常量与变量

(2) .系统内置函数

(3) .表达式

(4) .流程控制语句

（二）.存储过程

(1) .创建存储过程

create procedure 存储过程名 （ [ 参数 [ , ... ] ] ）存储过程体   

(a) .存储过程体

- declare 变量 [ , ... ] 类型 [ default 值 ]

(2) .返回一行结果

select 列名 [ , ... ] into 变量名 [ , ... ] 数据来源表达式     

(3) .显示存储过程

show procedure status ;

show create procedure 存储过程名 ；   

(4) .调用存储过程 

call 存储过程名（ [ 参数 ] [ , ... ] ] )

(5) .删除存储过程

drop procedure [ if exists ] 存储过程名                                                             

（三）.创建和调用存储函数

(1) .创建存储函数

create function 存储函数名 （[ 参数 [ , ...] ] ） returns 类型  deterministic 函数体

(2) .调用存储函数

select 存储函数名 （ [  参数 [ , ... ] ] ）

（四）.设置触发器

create trigger 触发器名 触发时间 触发事件 on 表名 for each row 触发器动作

（五）.事件

create event 事件名 on schedule 时间调度 do 触发事件

九 .数据安全

（一）.用户和数据权限管理

1.添加和删除用户

(1) .添加用户

create user 用户名[identified by'密码'] [密码选项]

(a) .密码选项：

password expire [default | never | interval n day]

| password historry { default | n }

| password reuse interval { default | n day }

| password require current [default | optional ]

| falled_lock_time { n | unbounded }

(2) .修改用户登录密码

set password [for 用户名] = '新密码'

(3) .删除用户

drop user 用户名 [ , 用户名2 ]...

(4) .修改用户名

rename user 旧用户名 to 新用户名 [ , ... ]

（二）.数据的备份与恢复

(1) .使用SQL语句备份和恢复数据

select * from 表名 into outfile '文件名'  输出选项 | dumpfile

(a) .输出选项

[ fields [ terminated by 'string' ]

           [ [ optionally ] enclosed by 'char' ]

           [escaped by 'char' ]

]

[ lines terminater by 'string' ]

（三）.事务和多用户管理

(1) .事务

(a) .事务

事务由作为一个单独单元的一个或多个SQL语句组成。如果事务中的任何一个语句执行失败，整个事务就会回滚（撤销），所有被影响的数据将返回到事务开始以前的状态。

(b) .事务与ACID属性

- 原子性

- 一致性

- 隔离性

- 持久性

(2) .事务处理

- 关闭自动提交功能

set @@autocommit = 0 ;

- 开始事务

start transaction

- 结束事务

commit 

- 撤销事务

rollback

(3) .多用户与锁定机制

(a) .表级（ table-level )锁定

(b) .行级（ row-level )锁定

(c) .页级（ page-level )锁定