mysql基础知识资料

Mysql基础知识分享

1用户及权限管理

1.1创建用户

createuserusername@hostidentifiedby'password';

1.2授权

全部权限：

grantallprivilegesondatabase.tableto'username^'host';

增删改查权限：

grantselect,update,insert,deleteondatabase.tableto'usemame^'host'

不同host的授权

本地授权:grantallprivilegeson*.*to'username^'localhost';

网络授权:grantselectprivilegesondbl.userinfoto'username'@'60.191.%.%';

权限列表：

ALTER修改表和索引。

CREATE:创建数据库和表。

DELETE:删除表中已有的记录。

DROP:抛弃（删除卜数据库和表。

INDEX:创建或抛弃索引。

INSERT:向表中插入新行。

SELECT:检索表中的记录。

UPDATE:修改现存表记录。

FILE:读或写服务器上的文件。

PROCESS:杳看服务器中执行的线程信息或杀死线程。

RELOAD:重载授权表或清空日志、主机缓存或表缓存。

SHUTDOWN:关闭服务器。

ALL:所有权限,ALLPRIVILEGES同义词。

USAGE:特殊的"无权限"权限。

1.3取消授权

revokeallprivilegesondbl.userjnfofrom'zhangsan'@'60.191.%.%,;

1.4删除用户

dropuser,zhangsan'@'60.191.%.%,;

1.5查看用户权限

showgrantsfor'zhangsan'@'%';

1.6刷新权限

flushprivileges;

1.7查看用户有设置过权限的host

selectuser,hostfrommysql.userwhereuser='usemame,;

1.8修改密码

root用户修改其他用户密码

setpasswordfor,zhangsan'@'60.191.%.%,=,new_pwd,;

修改自己的密码

setpassword='new_pwd'

shell命令行修改密码

mysqladmin-uroot-pl23456password123

修改user表

updateusersetpassword=passwordC123'）whereuser='zhangsan'andhost='localhost,;

破解密码

mysqld—skip-grant-tables模式启动mysql

1.9mysql权限分配规则

•一个用户在不同的host上可能有不同的权限

・慎用,username'©'%',最好根据需要开放对应IP的数据库访问权限，而且数据库的

端口最好也只针对指定ip开放

・用户权限是存在mysql.user表中的，一个用户如果对应有不同的host,在表中也是对

应的多条记录，所以说用户权限是根据'usemame'@'hosts'两个条件区分的（可以看作

两个用户，因为也可以设置不用的密码），同用户不同的host之间是没有权限关联

的。

•用户账户包括“username"和"host"两部分，后者表示该用户被允许从何地接入。

tom@'%'表示任何地址，默认可以省略。还可以是"tom@192.1681%”、

"tom@%."等。数据库格式为db.table,可以是"test.*"或前者表示

test数据库的所有表，后者表示所有数据库的所有表。

2常用操作命令

2.1查看当前登录用户

selectcurrent_user();

2.2查看当前选择数据库

selectdatabaseO;

2.3表操作

2.3.1创建表

createtableuser_info3(idxintprimarykeyauto_increment,namevarchar(3O)notnull,age

int,sexboolean);

2.3.2删除表

droptableuser_info2;

2.3.3修改表

•(修改表名altertableuser_info3renamenew_userinfo;

•修字段数据类型altertablenew_userinfomodifynamechar(20);

・修改字段名altertablenew_userinfochangesexgenderboolean;

•在最后追加新的字段altertablenew_userinfoaddaddressvarchar(200)notnull;

•在指定字段后插入新字段altertablenew_userinfoaddphonevarchar(20)notnull

afterage;

•在表的第一个位置插入新的字段altertablenew_userinfoadduuidtimestampdefault

now()first;

•删除一个字段altertablenew_userinfodropuuid;

•更改表的存储引擎altertablenew_userinfoengine=MyISAM;

•删除外键altertableforiegn_tabdropforeignkeyc_fk

2.4查看当前库下所有的表

showtables;

2.5查看表的字段信息

desctablename;

2.6查看表的创建信息

showcreatetabletablebname;

2.7数据操作

2.7.1增

insertintotest(id.email,ip,state)values(2/qq@770');

2.7.2删

•deletefrom

•deleteFROMusersWHEREname='n汰e'ORDERBYidDESCLIMIT6;

•truncatetable

・truncatetableusers;

清空数据库

•deletefromtablename;

•truncatetabletablename;

delete和truncate的区别

truncate删除表中的所有行，表的列、约束、索引保持不变，但标识计数器将被垂置。

而delete会保留标识计数器。有foreignkey约束的表和参与视图索引的表，都不能使用

truncate。所以truncate操作是无法在事务中回滚的，但delete在事务上卜文中在

commit前是可以回滚常见的DDL命令还有CREATE/ALTER/DROP,这些操作在事务上

下文中都是无法回滚的，你可认为它们自带提交TRUNCATE是DDL(数据定义语言)，

DELETE是DML(数据操作语言)

2.7.3改

UPDATErunoob_tblSETrunoob_title=，学习C++*WHERErunoob_id=3;

2.7.4查

SELECT*FROM表名WHERE条件

3数据类型

mysql数据类型大致分为整形，定点数，浮点型，日期时间型，字符串类型，二进制类型，

枚举，集合等几种大类型。由于过于庞杂，所以在此不多做介绍，有兴趣的同学请在网上查

阅。

4字段约束

关系型数据库有三大完整性

•实体完整性：实体属性中的标识属性不能为空、不能重复，该约束通过制定的主键实

现，其约束有系统强制实施

•参照完整性：实体中的外键可以为空，但不能是错的。

・用户定义完整性：某一具体的实际数据库的约束条件，由应用环境所决定，反映某一

具体应用所涉及的数据必须满足的要求。(例如定义某一行不能为空)

约束主要完成对数据的检验和限制，从而保证数据库的完整性.

4.1primarykey主键

定义：主键约束列不允许重复，即任意两行的主键值都不相同：每行都具有一个主键值，

也不允许出现空值；

单列主键

创建方式L

CREATETABLEstudent(idINTPRIMARYKEY,

nameVARCHAR(20)

)

创建方式2：

CREATETABLEstudent(idINTNOTNULL,

nameVARCHAR(20),

CONSTRAINTPRIMARYKEY(id)

//PK.STUD.ID为约束的名字

)

联合主键

有时候一个列的字段可能有重复，可以联合多个列设为主键。

CREATETABLEstudent(nameVARCHAR(20),

classVARCHAR(20)z

CONSTRAINTPRIMARYKEY(name,class)

)

其他操作

〃删除主键约束ALTERTABLE表名DROPPRIMARYKEY;

〃添加主键ALTERTABLE表名ADDPRIMARYKEY(歹U名)；

〃修改列为主犍ALTERTABLE表名MODIFY列名数据类型PRIMARYKEY;

4.2foreignkey外键约束

定义：外键约束是保证一个或两个表之间的参照完整性，保持数据一致性。表的外键可以是

另一表的主键，外键可以有重豆的，可以是空值。实现一对一或一对多关系。

创建外键约束

创建主表

CREATETABLEclasses(idINTAUTOJNCREMENTPRIMARYKEY,

nameVARCHAR(20)

)；

创建外键

〃添加关键字

CREATETABLEst