设为首页收藏本站
开启辅助访问 QQ强制聊天

安而遇随-随遇而安

 找回密码
 立即注册

QQ登录

只需一步，快速开始

搜索
安而遇随-随遇而安 › 首页 ›其他› 笔记 › 查看内容

Mysql相关笔记

2017-10-5 23:58| 发布者: 随便、先森。| 查看: 1387| 评论: 0|原作者: nMask|来自: nMask's Blog

摘要: Mysql相关笔记安装 Mysqlfor ubuntu12sudo apt-get install mysql-serversudo apt-get install phpmyadminUbuntu上安装mysql几乎都是自动安装的，在安装过程中可以选择额外安装php／apache2。for centos12345wget ht ...

Mysql相关笔记

安装 Mysql

for ubuntu

1
2
sudo apt-get install mysql-server
sudo apt-get install phpmyadmin

Ubuntu上安装mysql几乎都是自动安装的，在安装过程中可以选择额外安装php／apache2。

for centos

1
2
3
4
5
wget http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum install mysql-community-server
yum install phpmyadmin
yum install httpd

Mysql基础命令

想要mysql玩得6，mysql命令行必须会用，或者说sql必须会，一起复习一下吧。

数据库操作

查看数据库：

1
show databases;

使用数据库：

1
use  数据库名称;

新建数据库：

1
CREATE DATABASE mydb;

删除数据库：

1
DROP DATABASE mydb;

数据库表操作

查看当前数据库表：

1
show tables;

创建数据表：

1
2
3
4
5
6
7
8
CREATE TABLE teacher(
id int primary key auto_increment,
name varchar(20),
gender char(1),
age int(2),
birth date,
description varchar(100),
);

查看表结构：

1
desc  表名;

删除表（DROP TABLE语句）：

1
DROP TABLE teacher;

注：drop table 语句会删除该的所有记录及表结构

修改表结构（ALTER TABLE语句）：

  • alter table test add column job varchar(10); –添加表列
  • alter table test rename test1; –修改表名
  • alter table test drop column name; –删除表列
  • alter table test modify address char(10) –修改表列类型（改类型）
  • alter table test change address address1 char(40) –修改表列类型（改名字和类型，和下面的一行效果一样）
  • alter table test change column address address1 varchar(30)–修改表列名（改名字和类型）

数据操作

添加数据：

1
INSERT INTO 表名(字段1,字段2,字段3) values(值，值，值);

查询数据：

1
select * from 表名;

修改数据：

1
UPDATE 表名 SET 字段1名=值,字段2名=值,字段3名=值 where 字段名=值;

删除数据：

1
DELETE FROM 表名;

以上命令是最最基础的，但也是最常用的

常用Sql语句

获取固定数量的结果

1
select * from table limit m,n

说明：其中m是指记录开始的index，从0开始，表示第一条记录；n是指从第m+1条开始，取n条。

1
select * from table limit 0,n

说明：查询前n条结果。

1
select * from table limit m,-1

说明：查询m行以后的结果。

查询字符串

1
SELECT * FROM table WHERE name like '%PHP%'

说明：%表示模糊查询，%php表示以php结尾的所有结果，%php%表示包含php的所有结果。

非空查询

查询address字段不为空的结果。

1
SELECT * FROM table WHERE address <>''

判断查询

查询age在0-18之间的结果。

1
SELECT * FROM table WHERE age BETWEEN 0 AND 18

查询结果的数量

1
select count(*) from table

查询结果不显示重复记录

1
SELECT DISTINCT 字段名 FROM 表名 WHERE 查询条件

注:SQL语句中的DISTINCT必须与WHERE子句联合使用，否则输出的信息不会有变化 ,且字段不能用*代替。

查询排序

1
2
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 DESC 降序
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段 ASC  升序

注:对字段进行排序时若不指定排序方式，则默认为ASC升序。

多条件查询排序

1
SELECT 字段名 FROM tb_stu WHERE 条件 ORDER BY 字段1 ASC 字段2 DESC

多表联合查询

多表查询有三种方式：交叉查询、等值查询、外部查询（左连接、右连接）
参考：http://blog.csdn.net/hguisu/article/details/5731880

交叉连接查询

交叉查询可将2表中所有的数据都查出，比较耗时。

1
2
3
SELECT * FROM table1 CROSS JOIN table2   
SELECT * FROM table1 JOIN table2   
SELECT * FROM table1,table2

等值连接查询

1
SELECT * FROM table1 INNER JOIN table2

外部连接查询

这种查询是最常用的，查找出a与b表中公有的，另外查出只有a表或b表独有的。

1
2
select id, name from user left join techer on user.id = teacher.id
select id, name from user right join techer on user.id = teacher.id

三表查询

1
select id, name from user left join techer on user.id = teacher.id left join home on teacher.id=home.id

Mysql使用权限问题

只能本地连接mysql，远程机器连接不了？

当我在服务器上搭建好mysql，输入以下命令：

1
2
3
[root@ ~]# mysql -u root -p
Enter password:
mysql>

当输入mysql密码，出现mysql>提示后，说明已经成功登陆mysql。

我满心欢喜地打开自己的mac，准备远程连接服务器上的mysql，结果如下：

1
2
3
[Mac~]mysql -u root -p -h 192.168.2.2
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'192.168.2.2' (using password: YES)

显示登陆失败，原因是mysql默认只支持本地登陆，不支持远程登陆。

解决方案

第一步，登陆mysql（服务器本地登陆，因为远程登陆不了），查看user表（内置表）

1
2
3
4
5
6
7
8
9
10
11
mysql> use mysql;  #选择mysql数据库（mysql是数据库名称）
mysql> select host,user,password from user; #(查看user表中的内容)
+-----------+------------+-------------------------------------------+
| host      | user       | password                                  |
+-----------+------------+-------------------------------------------+
| localhost | root       | *21D8392A6B4CA12B9D194ED3E245258C4BE56DBA |
| 127.0.0.1 | root       | *930D8392A6B4CA12B9D194ED3E245258C4BE56DB |
+-----------+------------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql>

可以看到，user表中目前只有一个root用户，并且host为127.0.0.1/localhost，也就是说root用户目前只支持本地ip访问连接。

第二步，修改表内容

增加一个用户，将host设置为%

1
mysql>CREATE USER 'nmask'@'%' IDENTIFIED BY '123456';

或者更改root用户的host字段内容

1
mysql>update user set host = '%' where user = 'root';

更改root用户的密码：

1
UPDATE user SET Password=PASSWORD('nmask') where USER='root';

注意：初始化安装mysql时，默认可能只能用root用户登录，但默认root没有设置密码，因此一开始可以先给root用户添加一个密码进行登录。

flush(必须要flush，使之生效)：

1
mysql>flush privileges;

查看用户

1
mysql> select host,user from mysql.user;

再看下user表内容：

1
2
3
4
5
6
7
8
9
mysql> select host,user,password from user;
+-----------+------------+-------------------------------------------+
| host      | user       | password                                  |
+-----------+------------+-------------------------------------------+
| localhost | root       | *21D8392A6B4CA12B9D194ED3E245258C4BE56DBA |
| 127.0.0.1 | root       | *930D8392A6B4CA12B9D194ED3E245258C4BE56DB |
| %         | nmask      | *435A8F39F0791250895CA1DE2068FDC2CB477122 |
+-----------+------------+-------------------------------------------+
5 rows in set (0.00 sec)

可以看到user表中增加了一个用户nmask，host为%。

重启Mysql：

1
sudo /etc/init.d/mysqld restart

此时再用nmask用户远程连接下Mysql：

1
2
3
mysql -u nmask -p -h 192.168.2.2
Enter password: 
mysql>

连接成功，因为此用户host内容为%，表示允许任何主机访问此mysql服务。

用户权限很低

当我用nmask账号登陆后，发现权限很低，具体表现为只能看到information_schema数据库。

解决方案

在添加此用户时，就赋予其权限

1
2
3
4
mysql>INSERT INTO user
    ->     VALUES('%','nmask',PASSWORD('123456'),
    ->     'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
mysql>flush privileges;

或者

1
2
3
mysql>CREATE USER 'nmask'@'%' IDENTIFIED BY '123456';
mysql>GRANT ALL PRIVILEGES ON *.* TO 'nmask'@'%' WITH GRANT OPTION;
mysql>flush privileges;

如果是phpmyadmin，可以通过root用户登陆后，进入user表进行修改。

最后重启Mysql：

1
sudo /etc/init.d/mysqld restart

自定义授权问题

如果想nmask使用123456密码从任何主机连接到mysql服务器，其他密码不行，则可以：

1
2
mysql>GRANT ALL PRIVILEGES ON *.* TO 'nmask'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql>flush privileges;

如果想允许用户nmask只能从ip为10.0.0.1的主机连接到mysql服务器，并只能使用123456作为密码。

1
2
mysql>GRANT ALL PRIVILEGES ON *.* TO 'nmask'@'10.0.0.1' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql>flush privileges;

最后重启Mysql：

1
sudo /etc/init.d/mysqld restart

只能连接localhost？

连接报错信息：

1
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.10.2' (111) 不能用192.168.10.2去连接。

解决方案

修改/etc/my.cnf内容：

1
bind_address=127.0.0.1 改成 bind_address=192.168.10.2

重启mysql服务：

1
sudo /etc/init.d/mysqld restart

脱坑秘籍：通过mysql命令行修改内容后，要记得plush；如果还不生效，尝试restart mysql服务

报错：too many connections

一般mysql默认最大连接数是100，当mysql连接数超过这个时，会报错此错；解决方案可以更改/etc/my.cof文件，更改最大连接上限。
在[mysqld]中新增max_connections=N，如果你没有这个文件请从编译源码中的support-files文件夹中复制你所需要的*.cnf文件为到 /etc/my.cnf

1
2
3
4
5
6
7
8
9
10
11
12
13
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 160M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections=1000

mysql服务重启出错

mysql重启如果出错，可以先查看日志，在/var/log/mysql.log中查看具体的错误。
一般来说，可能是权限问题，如果mysql是mysql用户权限，则需要切换到sudo su mysql用户下去启动mysql服务。

phpmyadmin 403问题

安装完phpmyadmin与apache以后，访问http://localhost/phpmyadmin路径显示403。

1
sudo vim /etc/httpd/conf.d/phpMyAdmin.conf

编辑phpmyadmin.conf文件：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<Directory /usr/share/phpMyAdmin/>
   AddDefaultCharset UTF-8
   <IfModule mod_authz_core.c>
     # Apache 2.4
     <RequireAny>
       #Require ip 127.0.0.1
       #Require ip ::1
       Require all granted
     </RequireAny>
   </IfModule>
   <IfModule !mod_authz_core.c>
     # Apache 2.2
     Order Deny,Allow
     Deny from All
     Allow from 127.0.0.1
     Allow from ::1
   </IfModule>
</Directory>

另外补充一下，如果要更改apache的端口，则可更改/etc/httpd/conf/httpd.conf文件。

Mysql性能优化

mysql insert加速

　　insert是操作数据库最常用的动作，当有大量数据需要插入数据库时，性能至关重要，即插入数据的速度。mysql insert性能优化参考：http://blog.jobbole.com/29432/

方案：一条SQL语句插入多条数据（亲测有效）

将sql语句修改成以下类型，即一条sql语句插入多条数据，可大大提高插入效率。

1
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因有两个，一是减少SQL语句解析的操作， 只需要解析一次就能进行数据的插入操作，二是SQL语句较短，可以减少网络传输的IO。

方案：在事务中进行插入处理

插入改成以下内容：

1
2
3
4
5
START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VALUES ('1', 'userid_1', 'content_1', 1);
...
COMMIT;

　　使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内进行真正插入处理。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。

注意事项：

  1. SQL语句是有长度限制，在进行数据合并在同一SQL中务必不能超过SQL长度限制，通过max_allowed_packet配置可以修改，默认是1M。
  2. 事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会日志会使用磁盘数据，这时，效率会有所下降。所以比较好的做法是，在事务大小达到配置项数据级前进行事务提交。

Python操作Mysql

利用python开发时，经常会用到跟mysql相关的操作，这时候需要利用第三方库，MySQLdb。

MySQLdb安装

1
sudo pip install mysql-python

或者

1
sudo apt-get install python-mysqldb

Usage

导入模块

1
import MySQLdb

连接mysql数据库

1
2
conn=MySQLdb.connect(host="localhost",user="root",passwd="root",db="test",charset="utf8",connect_timeout=10)  #connec_timeout连接超时时间  
cursor = conn.cursor()

创建表结构

1
2
sql = "create table if not exists user(name varchar(128) primary key, created int(10))"  
cursor.execute(sql)

往表中写入数据

1
2
3
4
5
sql = "insert into user(name,created) values(%s,%s)"     
param = ("aaa",int(time.time()))      
n = cursor.execute(sql,param)      
cursor.close()
conn.commit()    #必须要commit，不然数据只会缓存在本地，而不会真正的插入数据库

往表中写入多行数据

1
2
3
sql = "insert into user(name,created) values(%s,%s)"     
param = (("bbb",int(time.time())), ("ccc",33), ("ddd",44) )  
n = cursor.executemany(sql,param)

更新表中数据

1
2
3
sql = "update user set name=%s where name='aaa'"     
param = ("zzz")      
n = cursor.execute(sql,param)

查询表中数据

1
2
3
4
5
n = cursor.execute("select * from user")      
for row in cursor.fetchall():      
    print row  
    for r in row:      
        print r

删除表中数据

1
2
3
sql = "delete from user where name=%s"     
param =("bbb")      
n = cursor.execute(sql,param)

删除表

1
2
sql = "drop table if exists user"  
cursor.execute(sql)

提交commit

1
conn.commit()

关闭连接

1
conn.close()

Python操作mysql优化问题

1、commit操作放在最后，或者循环外面
2、使用executemany，插入多条数据


鲜花
握手
雷人
路过
鸡蛋
收藏 分享 邀请
上一篇：SpringMVC与Struts2区别与比较总结下一篇：Maven笔记

相关阅读

最新评论

相关分类

  • 解决 Wn10无法连接 蓝牙耳
  • Python之Selenium知识总结
  • 2万字带你了解Selenium全攻
  • 解决网站网页不能复制，不
  • 我对ddos攻击的见解以及ufo
论坛精选
NTP放大反射DDOS攻击扫描教程,放大列表扫描教程
NTP放大反射DDOS攻
NTP放大反射DDOS攻击扫描教程,放大列表扫描教程 图上是国外测的 本文实测 能有二三
DDOS：NTP、SSDP、DNS、SNMP、RIP 放大反射攻击、扫描脚本
DDOS：NTP、SSDP、D
NTP、SSDP、DNS、SNMP、RIP 放大反射攻击扫描脚本 不懂的 请自行谷歌 声明：本文转
返回顶部