mysql 精粹_MySQL精粹

关于Mysql整理的需要记忆和熟练掌握的内容1.查询数据表的信息(比如有多少行数据)：

showtable status like 'tab_User' --数据表中的数量

2. 使用 explain查看sql运行效率时1)通过 'row'来查看扫描的行数2)通过 'key'来查看使用的索引1. /*查看操作*/ ------------------------------------------------------------------------------------------------------- 1. /* 查看操作 */

SHOW PROCESSLIST --显示哪些线程正在运行

SHOW VARIABLES --查看变量

2. /*数据库操作*/ ------------------------------------------------------------------------------------------------------ 2. /* 数据库操作 */--查看当前数据库

select database();--显示当前时间、用户名、数据库版本

select now(), user(), version();--复制表结构

CREATE TABLE 表名 LIKE要复制的表名--复制表结构和数据

CREATE TABLE 表名 [AS] SELECT * FROM要复制的表名3. /*字符集编码*/ --------------------------------------------------------------------------------------------------------- 3. /* 字符集编码 */

字符编码--MySQL、数据库、表、字段均可设置编码--数据编码与客户端编码不需一致

SHOW VARIABLES LIKE 'character_set_%' --查看所有字符集编码项

character_set_client 客户端向服务器发送数据时使用的编码

character_set_results 服务器端将结果返回给客户端所使用的编码

character_set_connection 连接层编码SET 变量名 =变量值set character_set_client =gbk;set character_set_results =gbk;set character_set_connection =gbk;SET NAMES GBK; --相当于完成以上三个设置

4./*数据类型(列类型)*/ ---------------------------------------------------------------------------------------------------4. /* 数据类型(列类型) */

1) 数值类型int4字节bigint8字节int(M) M表示总位数-默认存在符号位，unsigned 属性修改-显示宽度，如果某个数不够定义字段时设置的位数，则前面以0补填，zerofill 属性修改

例：int(5) 插入一个数'123'，补填后为'00123'

-在满足要求的情况下，越小越好。- 1表示bool值真，0表示bool值假。MySQL没有布尔类型，通过整型0和1表示。常用tinyint(1)表示布尔型。2) 字符串类型--a. char, varchar ----------

char定长字符串，速度快，但浪费空间varchar变长字符串，速度慢，但节省空间

M表示能存储的最大长度，此长度是字符数，非字节数。

不同的编码，所占用的空间不同。char,最多255个字符，与编码无关。varchar,最多65535字符，与编码有关。

一条有效记录最大不能超过65535个字节。

utf8 最大为21844个字符，gbk 最大为32766个字符，latin1 最大为65532个字符varchar 是变长的，需要利用存储空间保存 varchar的长度，如果数据小于255个字节，则采用一个字节来保存长度，反之需要两个字节来保存。varchar的最大有效长度由最大行大小和使用的字符集确定。

最大有效长度是65532字节，因为在varchar存字符串时，第一个字节是空的，不存在任何数据，然后还需两个字节来存放字符串的长度，所以有效长度是64432-1-2=65532字节。

例：若一个表定义为CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最大值是多少？ 答：(65535-1-2-4-30*3)/3

5./*建表规范*/ ------------------------------------------------------------------------------------------------------------------ 5./* 建表规范 */--Normal Format, NF

-每个表保存一个实体信息-每个具有一个ID字段作为主键- ID主键 +原子表--1NF, 第一范式

字段不能再分，就满足第一范式。--2NF, 第二范式

满足第一范式的前提下，不能出现部分依赖。

消除符合主键就可以避免部分依赖。增加单列关键字。--3NF, 第三范式

满足第二范式的前提下，不能出现传递依赖。

某个字段依赖于主键，而有其他字段依赖于该字段。这就是传递依赖。

将一个实体信息的数据放在一个表内实现。6./*select 查询语句*/ -------------------------------------------------------------------------------------------------------- 6./* select 查询语句*/

1) having子句，条件子句

与where功能、用法相同，执行时机不同。where在开始时执行检测数据，对原数据进行过滤。having对筛选出的结果再次进行过滤。having 字段必须是查询出来的，where字段必须是数据表存在的。where 不可以使用字段的别名，having可以。因为执行WHERE代码时，可能尚未确定列值。where 不可以使用合计函数。一般需用合计函数才会用 havingSQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。7./*备份与还原*/ ------------------------------------------------------------------------------------------------------------- 7./* 备份与还原 */

备份，将数据的结构与表内数据保存起来。

利用 mysqldump 指令完成。--导出

1) 导出一张表

mysqldump-u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)2)导出多张表

mysqldump-u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)3)导出所有表

mysqldump-u用户名 -p密码 库名 > 文件名(D:/a.sql)4)导出一个库

mysqldump-u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)

可以-w携带备份条件--导入

1)在登录mysql的情况下：

source 备份文件2)在不登录的情况下

mysql-u用户名 -p密码 库名

表锁定只用于防止其它客户端进行不正当地读取和写入

MyISAM 支持表锁，InnoDB 支持行锁--锁定

LOCK TABLES tbl_name [AS alias]

--解锁

UNLOCK TABLES9./*用户和权限管理*/ -------------------------------------------------------------------------------------------------------- 9./* 用户和权限管理 */

用户信息表：mysql.user

--刷新权限

FLUSH PRIVILEGES

--增加用户

CREATE USER 用户名 IDENTIFIED BY [PASSWORD]密码(字符串)-必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。-只能创建用户，不能赋予权限。- 用户名，注意引号：如 'user_name'@'192.168.1.1'

-密码也需引号，纯数字密码也要加引号-要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD--重命名用户

RENAME USER old_user TOnew_user--设置密码

SET PASSWORD = PASSWORD('密码') --为当前用户设置密码

SET PASSWORD FOR 用户名 = PASSWORD('密码') --为指定用户设置密码--删除用户

DROP USER用户名--分配权限/添加用户

GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']- all privileges表示所有权限- *.*表示所有库的所有表-库名.表名 表示某库下面的某表--查看权限

SHOW GRANTS FOR用户名--查看当前用户权限

SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();--撤消权限

REVOKE 权限列表 ON 表名 FROM用户名REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 --撤销所有权限--权限层级--要使用GRANT或REVOKE，您必须拥有GRANT OPTION权限，并且您必须用于您正在授予或撤销的权限。

全局层级：全局权限适用于一个给定服务器中的所有数据库，mysql.user

GRANT ALL ON *.*和 REVOKE ALL ON *.*只授予和撤销全局权限。

数据库层级：数据库权限适用于一个给定数据库中的所有目标，mysql.db, mysql.hostGRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。

表层级：表权限适用于一个给定表中的所有列，mysql.talbes_privGRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。

列层级：列权限适用于一个给定表中的单一列，mysql.columns_priv

当使用REVOKE时，您必须指定与被授权列相同的列。