前言

首先对于数据库这块，我一直都是敬畏的，因为它是数据集中的地方，而数据又是现在网络应用的基础，无论是爬虫，还是web，还是数据挖掘，都需要依赖数据库，所以数据库中如果管理存储数据就是至关重要的问题.

数据库设计

我不是科班计算机系出生的，所以谈数据库设计我是惶恐的，所以我这里就只是稍微总结一下我的体悟吧.

数据库的设计现在大多数都是关乎与关系型数据库，关系型数据以Mysql为栗子吧，其他关系型数据库也差不多，它的结构的话我在 python爬虫（中）–保存也贴了张图，db的话现在我清楚了，一般来说就是一个项目刚开始就一个db(不涉及后面的性能调优)，当然你数据量大的话，一开始就可以做分库分表的设计，不过一些新项目，优先是先做出来上线，所以规模比较小，就没有必要一开始就做分表分库这种优化了

至于如果你想寻求系统专业一点的谈设计，我就不班门弄斧了，推荐这里有一系列文章：

① MySQL架构与概念 [先对MySQL有个认识]
②MySQL索引背后的数据结构及算法原理[可选]
③知行思新——数据库设计Step by Step[一共11篇的样子- -]
④MeteorSeed——写给开发者看的关系型数据库设计
⑤第④的重点 :-P

SQL语句基础

常用SQL语句

#查看当前mysql数据库提供什么引擎
SHOW ENGINES;
#查看当前mysql默认的存储引擎[一般默认的是InnoDB]
SHOW VARIABLES LIKE '%storage_engine%';

#查看编码
SHOW VARIABLES LIKE 'collation_%';
SHOW VARIABLES LIKE 'character_set_%';

#查看foo表使用的引擎
SHOW TABLE STATUS LIKE 'foo%'

#察看所有系统参数[静态的,可以通过set或者修改my.cnf配置文件修改]
SHOW VARIABLES; 
#察看所有系统运行的实时状态[动态的,不可认为修改,只能系统自动update]
SHOW STATUS;
#上面两个命令打印来的信息有点多，可以用像上面的LIKE一样做筛选

#创建支持中文字符集的db
CREATE DATABASE IF NOT EXISTS db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

#如果对COLLATE utf8_general_ci感到疑惑的，可以看到这里--
#http://stackoverflow.com/questions/341273/what-does-character-set-and-collation-mean-exactly
#或者中文http://www.360doc.com/content/11/0303/01/2588264_97631236.shtml

#察看所有db
SHOW DATABASES;

#删除某db
DROP DATABASE foo; #删除foo db

#选中某个db，每次登陆mysql都要选一次好麻烦
USE db_name;

#展示当前选中db的属性
SHOW VARIABLES WHERE variable_name LIKE 'character%'

#察看所选中的db下的table
SHOW TABLES;

#建立新的表
CREATE TABLE IF NOT EXISTS foo(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255));
#IF NOT EXISTS表示如果不存在表名为foo的表则创建该表，如果已经存在则不创建

#我们来察看一下我们刚才新建的这张表的属性
DESC foo;

#可以看到会显示Field，Type，Null，Key，Default，Extra这六个字段
#其中Field就是字段名字(比如刚才foo表中的id和name)，Type就是字段的格式(比如刚才foo表中的int和VARCHAR)
#至于后面的Null，Key，Default，Extra，具体哪个是约束我不是很分得清。
#但是打个比方如果你给刚才foo表的id加入主键约束（PRIMARY KEY），那么id的Null和Key的内容就是NO和PRI，意思是不允许为空[Null]和不允许重复id出现；
#而Default是默认值，一般没有特别需求都不用管它；
#最后是Extra，一般都是AUTO_INCREMENT给予后会在这一栏下面填入auto_increment，表示自增长

#自增长只能有一个，而且必须是主键，而主键只有一个，如何设置多个主键？

#如果写成 CREATE TABLE IF NOT EXISTS foo(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(255) PRIMARY KEY); 如何？？
#那么你会得到一个错误( there can be only one auto column and it must be defined as a key
),其实用联合主键写法就可以避免这种错误了，下面会说

#P.s. DESC是DESCRIBE的简写，所以你也可以写成DESCRIBE foo

#察看表内容
SELECT id，name FROM foo; # id,name可以换成其他字段，这里表示只察看Field为id，name的内容
SELECT * FROM foo; # *可以代表所有的Field，即这里就是察看所有Field字段的内容

#联合主键
CREATE TABLE foofoo(id INT  AUTO_INCREMENT,name VARCHAR(255),PRIMARY KEY(id,name));
#DESC foofoo发现它和刚才foo表比较下，id还是一样，但是name的Null，Key，Default都不一样了，Null从YES变成NO，Key填入了PRI，而Default则去掉Null
#PRIMARY KEY(id,name)这句是同时给id，name加上主键约束(Primary Key Constrain),此时foofoo表内不能同时存在相同id和name的行，但是可以存在相同id或name的行，这一点请注意

#把刚才我们的foo表删除
DROP TABLE foo;

#往foofoo表内插入内容，e.g. id=1  name='foo'
INSERT INTO foofoo(id,name) values(1,'foo');#可以用SELECT * FROM foofoo检查插入内容
INSERT INTO foofoo(name) values('foo');#也可以只是插入name，你会发现id那项会自己填入2，这就是自增长(AUTO_INCREMENT)的作用
INSERT INTO foofoo(id,name) value(3,'foo');#我发现插入多值也可以用VALUE取代VALUES也可以的，这样看来好像VALUE和VALUES结果上没有什么区别

#倒空却不删除表
TRUNCATE foofoo;#包括让自增长的id重置重新从1开始

#更改表结构,e.g. 在id后面加入/删除一个gender
ALTER TABLE foofoo ADD gender VARCHAR(255) AFTER id;
ALTER TABLE foofoo DROP gender;

#更新表的某行的某字段
UPDATE foofoo SET name='blabla' WHERE id=3;#把原来'foo'值更新成了'blabla'，其中WHERE是子句，用来确定究竟某行究竟是哪行

#删除表的某行
DELETE FROM foofoo WHERE id = 3;#删除id=3的那一行
#注意，你可以尝试用上面的DELETE把foofoo表所有的行删除完，然后INSERT新的行进去，你会发现主键自增长的字段id并不会从头开始算.


#模糊匹配
#首先先生成几行数据
INSERT INTO foofoo(name) VALUES('anya');
INSERT INTO foofoo(name) VALUES('joe');
INSERT INTO foofoo(name) VALUES('andre');

#问题来了：如果我仅仅只拿出三条记录里面name字段是以a开头，或者name是以e结尾的该怎么写SQL？

#仅仅只拿出三条记录里面name字段是以a开头
SELECT * FROM foofoo WHERE name LIKE 'a%';
#仅仅只拿出三条记录里面name字段是以e结尾
SELECT * FROM foofoo WHERE name LIKE '%e';
#定位anya
SELECT * FROM foofoo WHERE name LIKE 'a%a';

#%你可以把它当成*通配符,用来代表1或任意多的字符
#拙见：其实感觉有点像linux的管道的用法，首先SELECT * FROM foofoo打印全表，然后由WHERE子句(相当与 | 管道的作用)和LIKE子句过滤出符合条件行

#两表联查
  SELECT A.* B.* FROM A,B WHERE A.id=B.id

  上面只是一些基础，其他的一些例如group by和max(id)可以取得id字段最大值这些用法待以后补充

上面只是一些独立的SQL操作，但是往往一些操作需要一些组合拳，所以光是有上面这些相对独立的SQL还是不够的，我这里只是应了标题所说，写了一些基础而已，还有一些要用来解决实际问题的SQL操作(比如查询中的关联查询,过滤排序搜索等查询)都是不太容易掌握的

P.s.
①所谓的CRUD，就是增加，查询，更新和删除，CRUD=Create,Retrieve,Update,Delete(我倒是觉得是Drop比较合适);
②一般来说一张表第一个是主键自增长的int id，跑不掉的;
③谨记无论NoSQL有多流行，无论MapReduce的Hadoop有多火，SQL始终是数据库领域里面基础中的基础，如果你是个Newbie，你有熟练它的义务
④MySQL的视图(View)只是取出目标表中的一部分组成一张图，有点像缓存，而且一些业务人员没有必要接触的字段，通过视图取出部分的方式屏蔽了，减少了多余信息leak的风险，但是数据库操作效率却低了。但是说到底，对于我们而言还是得关注原表的操作就好了，没有必要太好奇视图这东西;

※有时候会就算你命令和用户密码都没错，而且确认service也打开了，却会出现下面这种情况
Error 1698(28000):Access denied for user 'root'@ 'localhost'

  解决办法：
  ①sudo service mysql stop
  ②sudo mysqld-safe --skip-grant-tables &  #启动安全模式
  ③mysql -uroot #安全模式下不用密码登陆
  ④SELECT user,plugin FROM mysql.user;
  ⑤UPDATE mysql.user SET authetication_string=PASSWORD('yournewpassword')，plugin='mysql_native_password' WHERE user='root';
  #就是user表这个plugin字段导致的问题，设置为'mysql_native_password'就OK了
  ⑥flush privileges;

mysql部署问题总结[17.2.20更新]

1.远程连接数据库报错 ERROR 1130 (HY000): Host ‘xxxxxxxx’ is not allowed to connect to this MySQL server，原因是mysql默认是以localhost/127.0.0.1打开的

  方法一：[直接改表--允许任何主机ip以root用户身份登录]
use mysql;
select user,host from user;  #发现除了localhost就是127.0.0.1，根本没有允许远程访问
UPDATE user SET host = '%' WHERE user = 'root';
FLUSH PRIVILEGES;
select host, user from user;  #查看修改情况 

  方法二：[GRANT命令放权--你想foo用户使用foopassword从任何(%)主机连接到mysql服务器的话]
GRANT ALL PRIVILEGES ON *.* TO 'foo'@'%' IDENTIFIED BY 'foopassword' WITH GRANT OPTION;
FLUSH PRIVILEGES;

2.mysql数据库改密码

平时我都是习惯改.cnf/.conf/.config这些配置文件，然后重启服务，但是mysql改密码有点不太一样

  方法一：[直接改表mysql数据库下的user表]
 use mysql;
 UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
 FLUSH PRIVILEGES;

  方法二：[组合拳改密码，貌似其实还是在改动mysql.user表]
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');
FLUSH PRIVILEGES;

3.修改数据库的存储引擎

我以为现在的mysql都已经是默认的InnoDB了，结果还是MyISAM，可以通过修改my.cnf改变数据库默认引擎

打开my.cnf，在[mysqld]最后添加为上default-storage-engine=InnoDB，重启数据库服务，数据库默认的引擎修改为InnoDB

4.mysql字符编码问题

把数据录入mysql发现，中文数据的位置都是一堆的问号，这个和默认是latin1编码有关，之前在python爬虫（中）–保存最后稍微说了一下，是用set names utf8解决的，这个并不太好，查了一下发现有配置.cnf文件的办法，这个是极好的.


vim 打开/etc/my.cnf或者/etc/mysql/my.cnf再或者其他位置的mysql配置文件

在[client]下添加
default-character-set=utf8

在[mysqld]下添加
default-character-set=utf8

重启mysql服务：sudo service mysqld restart

查看编码，基本都是utf8
SHOW VARIABLES LIKE 'collation_%';
SHOW VARIABLES LIKE 'character_set_%';

再查看刚才问号的地方都正确显示出中文