mysql 常用操作

说明:

我在ubuntu14.04上开发爬虫, 经常用到mysql数据库, 这里将一些常用操作做一些记录,方便以后查阅,还会不断更新…..
资料：

• 常用的一个tutorial
• 了解一下mysql体系结构, 其中的存储引擎也要好好学习，不同引擎对于锁的支持不同，这对并发访问mysql效率有直接的决定作用。

一、mysql的安装：

• 环境： ubuntu14.04

• 安装mysql server:

      sudo apt-get install mysql-server

• 安装MySqldb模块:

      sudo apt-get install python-mysqldb

二、 用户管理

1. 创建用户:

   • 命令:

     CREATE USER 'username'@'host' IDENTIFIED BY'pwd';     

   • 说明:

     • username - 你将创建的用户名,
     • host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%.
     • password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.

   • 例子:

     CREATE USER 'dog'@'localhost' IDENTIFIED BY '123456';CREATE USER 'pig'@'192.168.1.101_' IDENDIFIED BY '123456';CREATE USER 'pig'@'%' IDENTIFIED BY '123456';CREATE USER 'pig'@'%' IDENTIFIED BY '';CREATE USER 'pig'@'%';

2. 授权

   • 命令

     GRANT privileges ON databasename.tablename TO 'username'@'host'

   • 说明:

     • privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL.;
     • databasename - 数据库名,
     • tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用表示, 如.*.

   • 例子

     GRANT SELECT, INSERT ON test.user TO 'pig'@'%';GRANT ALL ON *.* TO 'pig'@'%';

   • 注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:

     GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

3. 设置与更改用户密码

   • 命令:

     SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');# 如果是当前登陆用户用SET PASSWORD = PASSWORD("newpassword");

   • 例子:

     SET PASSWORD FOR 'pig'@'%' = PASSWORD("123456");

4. 撤销用户权限

   • 命令:

     REVOKE privilege ON databasename.tablename FROM 'username'@'host';

   • 说明: privilege, databasename, tablename - 同授权部分.

   • 例子:

     REVOKE SELECT ON *.* FROM 'pig'@'%';

   • 注意:
     假如你在给用户’pig’@’%’授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO ‘pig’@’%’, 则在使用REVOKE SELECT ON . FROM ‘pig’@’%’;命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是GRANT SELECT ON . TO ‘pig’@’%’;则REVOKE SELECT ON test.user FROM ‘pig’@’%’;命令也不能撤销该用户对test数据库中user表的Select 权限.
     具体信息可以用命令SHOW GRANTS FOR ‘pig’@’%’; 查看.

5. 删除用户

   • 命令:

     DROP USER 'username'@'host'; 

三、关于mysql的远程连接的设置

1. mysql-server 端:
   
   • 创建用户(如果对现有用户操作,则跳过此步骤):
     CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;
     (说明:username - 你将创建的用户名, host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%. password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器. )
   • 赋予允许访问权限:
     grant all privileges on . to 创建的用户名 @”%” identified by “密码”;
     flush privileges; # 刷新权限信息
2. 设置配置文件/etc/mysql/my.cnf
   将 “bind-address = 127.0.0.1” 注释掉, 使server监听外部网络的请求
3. 重启mysql 服务:
   sudo service mysql restart

4. 曾经出现的问题:ERROR 2003 (HY000): Can’t connect to MySQL server (111)
   解决方案参考链接：点这里

5. 关于mysql数据库字符编码的设置

6. mysql防止重复插入（sql）：触发器解决

四、编程使用随记

1. 中文字符插入失败问题：将数据库和其中的表的编码默认设置为utf-8
   (1) SHOW VARIABLES LIKE ‘CHAR%’;
   (2)

2. 创建数据库：mysqladmin -u root -p create databaseName

3. 自增
   (1) 设置自增字段的初始值：alter table users AUTO_INCREMENT=10000;
   (2) 创建表格时设置自增：
   create table PaperInfo(
   PID int primary key auto_increment,
   ChineseText text,
   EnglishText text,
   originText text,
   purl varchar(200),
   pType int
   )auto_increment = 1;

4. 外键
   create table PaperInfo(
   PID int primary key auto_increment,
   ChineseText text,
   EnglishText text,
   originText text,
   purl varchar(200),
   pType int,
   CONSTRAINT FK_ID FOREIGN KEY (pType) REFERENCES MenuItems(MID)
   )auto_increment = 1;