MySQL 基本操作

MySQL 基本操作

MySQL5.7设置初始密码

在centos6.5安装教程不赘述。安装后第一次登录MySQL时，需要重新设置密码。MySQL 5.7 在初始安装后会生成随机初始密码，并在/etc/my.cnf配置文件中找到[mysqld]下的log.err配置的目录/var/log/mysqld.log 中有记录，可以通过 cat 命令查看，找 password 关键字。

# cat /var/log/mysqld.log | grep password
2017-10-20T07:58:04.065507Z 1 [Note] A temporary password is generated for root@localhost: boEsPgrCf1.X

找到密码后，在本机以初始密码登录，通过ALTER USER命令修改 root 用户的密码， 然后退出登录，重新以root用户和刚设置的密码进行登录即可。

#如果不设置初始密码则不允许操作
mysql> SHOW DATABASES;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

#MySQL改变了密码的强度，需要满足要求才可以通过密码设置
mysql> alter user 'root'@'localhost' identified by 'fefjay.2017';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

#正确的密码设置
mysql>  ALTER USER 'root'@'localhost' IDENTIFIED BY  'FEFJay@2017';
Query OK, 0 rows affected (0.00 sec)

查看MySQL官方文档，是这样解释的：

MySQL's validate_password plugin is installed by default. This will require that passwords contain at least one upper case letter, one lower case letter, one digit, and one special character, and that the total password length is at least 8 characters.

大概意思就是MySQL5.7以后默认安装密码校验器，新设置的密码必须满足这样的条件：包含至少一个大写字母、至少一个小写字母、至少一个数字、至少一个特殊字符（比如“@”、“_”之类的）、且总长度至少为8。
所以设置相应强度的密码才会通过校验，其实也可以通过禁用密码校验插件实现登录MySQL服务，然后可以进行更改密码、查询等操作，具体网上有很多不错的方法，此处不再赘述。

基本操作

1.建库/删库

数据库对名称是大小写敏感的。

#创建数据库
CREATE DATABAE 库名;

#删库
DROP DATABASE 库名;

2. 插入insert

2.1 仅插入一行

INSERT INTO 表名(列名1，列名2，列名3) VALUES(行1值1, 行1值2, 行1值3);

2.2 插入多行，比如三行

INSERT INTO 表名(列名1，列名2，列名3)
VALUES(行1值1,行1值2,行1值3),
(行2值1,行2值2,行2值3),
(行3值1,行3值2,行3值3);

3.更新update和删除delete

更新和删除操作一定要注意添加WHERE限制条件，否则很危险！！！
更新和删除操作只是对表的内容更改，并不会改变表的结构。

• 更新

UPDATE 表名字 SET 列1=值1,列2=值2 WHERE 条件;

• 删除
  这是删除内容，并不会释放存储空间。

DELETE FROM 表名字 WHERE 条件;

4.查询select

4.1 简单查询

SELECT 列1,列2,列3 FROM 表名 WHERE 限制条件;

4.2 子查询

最终结果只来自一个表的情况。条件里嵌套有查询，根据需要可多层嵌套。通过比较符连接。

SELECT ... FROM 表名 WHERE department IN (SELECT department FROM ...)

4.3 连接查询

最终结果来自多个表的情况，有某些列作为连接字段。

• 内连接
  得到两个表的公共集合，以下三个 查询语句都等价

方式一：SELECT ... FROM 表a,表b WHERE a.某列名 = b.某列名;
方式二：SELECT ... FROM 表a (INNER) JOIN 表b ON a.某列名 = b.某列名;
方式三：SELECT ... FROM 表a CROSS JOIN 表b WHERE a.某列名 = b.某列名;

特殊的内连接-自连接，自己和自己作为两个表处理。

SELECT ... FROM 表a AS A,表a AS B WHERE A.某列名 = B.某列名的计算操作;

• 左连接
  结果集是左表的全部内容，如果右表的某行和左表不匹配，则右表的相关列内容为空值NULL。

SELECT ... FROM 表a LEFT (OUTER) JOIN 表b ON a.某列名 = b.某列名;

• 右连接
  结果集是右表的全部内容，如果左表的某行和右表不匹配，则左表的相关列内容为空值NULL。

SELECT ... FROM 表a RIGHT (OUTER)  JOIN 表b ON a.某列名 = b.某列名;

• 完整外部连接/全连接
  返回两个表的所有行，即返回左、右连接的和。若一个表的行在另一个表不匹配时则显示为空值NULL。

SELECT ... FROM 表a FULL (OUTER)  JOIN 表b ON a.某列名 = b.某列名;

• 交叉连接
  笛卡尔连接，返回左表每一行和右表每一行相互组合的结果，可借助笛卡尔积n*m理解。条件限制只能使用WHERE不能用ON。

SELECT ... FROM 表a CROSS JOIN 表b WHERE a.某列名 = b.某列名;

如果没有WHERE语句，那么交叉连接和没有限制的内连接等价，都是得到两个表的笛卡尔积。
即SELECT ... FROM 表a CROSS JOIN 表b;结果集等同于 SELECT ... FROM 表a,表b;。

参考：
http://www.cnblogs.com/youzhangjin/archive/2009/05/22/1486982.html
http://www.cnblogs.com/Ewin/archive/2009/10/05/1578322.html

5 操作表

对表格的操作，可以理解为更改表的属性或者结构。

5.1 建表

表的名字是大小写敏感的。

CREATE TABLE 表名(
`列名1` INT(长度) UNSIGNED DEFAULT NOT NULL AUTO_INCREMENT，
`列名2` 数据类型(长度) DEFAULT 默认值，
`列名3` 数据类型(长度) DEFAULT NULL,
 PRIMARY KEY(列名)
)ENGINE=InnoDB default-charset=utf8;

添加主键也可以在括号里的最后一行加上CONSTRAINT 主键名 PRIMARY KRY(列名)。
主键也可以是多列的复合主键CONSTRAINT 主键名 PRIMARY KEY(列1,列2,列3)。

查看表的结构

#方式一：使用关键字`DESCRIBE`
DESCRIBE 表名；

#方式二：查看建表语句
SHOW CREATE TABLE 表名;

5.2 重命名表格

对一张已经建立的表重命名

方式一：RENAME TABLE 原名 TO 新名;
方式二：ALTER TABLE 原名 RENAME 新名;
方式三：ALTER TABLE 原名 RENAME TO 新名;

5.3 删除表

DROP TABLE 表名字;

5.4 表增加一列

以下两种方式是等效的

方式一：ALTER TABLE 表名字 ADD 列名 数据类型 约束;
方式二：ALTER TABLE 表名字 ADD COLUMN 列名 数据类型 约束;

如果没有指定添加的新列的位置，那么就会默认添加在表的最后一列，如果需要指定在第一列则用关键字FIRST，如果需要在某列后则用关键字AFTER 某列名。

• 新增列放在第一列

ALTER TABLE 表名字 ADD 列名 数据类型 约束 FIRST;

• 新增列放在列A之后

ALTER TABLE 表名字 ADD 列名 数据类型 约束 AFTER A;

5.5 表删除一列

方式一：ALTER TABLE 表名字 DROP 列名;
方式二：ALTER TABLE 表名字 DROP COLUMN 列名;

5.6 重命名表的一列/修改该列的名字

本质上是修改一个列的属性，用了关键字CHANGE。

ALTER TABLE 表名字 CHANGE 原列名 新列名 新列的数据类型 约束;

注意到如果新列名和原列名相同的话，就可以修改这个列的数据类型和约束了。
但是修改数据类型可能会导致数据丢失！！

5.7 修改表一列的数据类型

方式一：ALTER TABLE 表名字 CHANGE 原列名 原列名 新数据类型 约束;
方式二：ALTER TABLE 表名字 MODIFY 列名字 新数据类型;

注意，修改数据类型可能会导致数据丢失！！

6.索引

数据库默认使用全表扫描，当一张表数据量较少的时候检索速度也会很快，当数据量很大的时候（比如达到百万级）如果使用全表扫描就会很慢。这个时候就需要添加索引来提高查询速度，往往会有很大的改善。添加索引时会在磁盘上创建索引文件，并且写入索引信息，所以创建索引会增加插入速度和消耗一部分存储空间。

执行查询语句时，数据库会自动判断是否有可用的索引，如果没有才会进行全表扫描。

• 创建单列索引的命令：

方式一：ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
方式二：CREATE INDEX 索引名 ON 表名字 (列名);

• 创建多列索引的命令：
  多列索引必须符合最左匹配原则。

方式一：ALTER TABLE 表名字 ADD INDEX 索引名 (列名1,列名2,列名3);
方式二：CREATE INDEX 索引名 ON 表名字 (列名1,列名2,列名3);

• 查询一张表的索引命令:

SHOW INDEX FROM 表名字;

6.视图

视图是一张虚拟表，基于数据库中已有的一张表或者多张表的真实数据组织而成，是一张逻辑表而不是实际存在的物理表,数据库中存放的是视图的定义。查询的时候可以把视图当做一张普通的表操作。

• 创建视图命令：

CREATE VIEW 视图名(视图列1,视图列2,视图列3) AS SELECT 列1,列2,列3 FROM 表;

可以看到视图中用到关键字AS，而且在AS后面是一个查询语句，所以这个查询语句可以是子查询和连接查询，视图依赖的数据可以是多个表的数据。

• 查看视图表的数据命令（和查询普通表一样，只不过把表名字换成了视图名字）：

SELECT * FROM 视图名;

• 删除视图（和删除表操作一样）:

DROP VIEW 视图名;

7.导入和导出

• 导入
  导入本地文件数据到数据库中的某张表去。需要注意指定文本中行、列数据的分隔符，默认使用制表符Tab作为列分隔符、使用换行符（windows是“\r\n”，OS X是“\r”，Linux是“\n”）作为行分隔符。

#使用默认的分隔符
LOAD DATA INFILE '文件路径' INTO TABLE 表名;

注意导入的文件路径需要加上单引号。如果需要指定分隔符，则为：

LOAD DATA INFILE '文件路径' 
INTO TABLE 表名 
FIELDS TERMINATED BY '列分隔符'
LINES TERMINATED BY '换行符';

• 导出
  导出操作和导入相反，是把数据库的数据导出到本地文件中去。需要设置MySQL服务器允许导出权限。

SELECT 列1,列2,列3 INTO OUTFILE '文件路径' FROM 表名;

注意导出文件路径需要加上单引号。同样，上面是使用默认的分隔符导出数据，如果想要设定分隔符，则可以在上面的命令后添加分隔操作。

SELECT 列1,列2,列3 
INTO OUTFILE '文件路径' 
FROM 表名
FIELDS TERMINATED BY '列分隔符'
[OPTIONALLY] ENCLOSED BY '指定把字段包括起来的字符（若用了OPTIONALLY则只会包括CHAR和VARCHAR类型）'
ESCAPED BY '指定当需要转义时用什么作为转义字符（通常为\\）'
LINES TERMINATED BY '换行符';

对于用LOAD DATA INFILE或SELECT ...INTO OUTFILE执行的文本文件导入或导出操作，NULL用序列\N表示。

8.数据备份和恢复

• 导出和备份的区别
  导出仅仅是导出需要的数据，而备份则是把数据库的结构，包括表结构、所有数据、索引、视图等全部另存为一个文件。

• 备份
  数据备份使用关键词mysqldump，mysqldump是一个备份程序，产生一个备份脚本文件*.sql。

备份命令不需要在MySQL服务里面敲命令，需要退出MySQL的命令行操作界面，在系统命令行操作界面进行。

#备份整个数据库
mysqldump -u 用户名 数据库名 > 备份文件路径;

#备份某个数据表
mysqldump -u 用户名 数据库名 表名 > 备份文件路径;

• 恢复
  方式一是在MySQL命令行操作，使用关键词source。会重新创建需要的库和表。
  source命令直接执行sql脚本文件。

source 备份文件路径;

方式二先在数据库建一个空库，然后在系统命令行操作恢复命令。使用Linux输入符号“<”完成恢复。

mysql -u 用户名 < 备份文件路径;

不交互执行SQL

在shell脚本执行SQL语句，不需要登录MySQL服务执行操作。

mysql -h主机IP地址 -u 登录用户名 -p '密码' -S mysql.sock的路径 -e "SQL命令;" >> 保存本地文件路径

如果没有-h参数，则表示是访问本地的MySQL服务。举例：

mysql -uroot -p'fefjay@2017' -S /tmp/mysql.sock -e "show databases;" >> /home/a.txt 
echo "show databases;" | mysql -uroot -p'fefjay@2017' -S /tmp/mysql.sock > /home/a.txt 

上述命令都是先要登录MySQL服务，然后执行命令，得到结果，至于把结果是导出到本地的文件a.txt，或者结合管道符|和grep的操作，看具体的业务需要了。但是这里需要写出登录的密码，不安全，需要找到一种更加安全的可以写到脚本中的方法。
参考 http://oldboy.blog.51cto.com/2561410/632608/

约束

序号	约束类型	关键字	说明	添加方式
1	主键	PRIMARY KRY	该行数据的唯一标识，非空，通常为自增长的int	CONSTRAINT 主键名 PRIMARY KEY(列名)
2	外键	FOREIGE KRY	作为外键的列必定是其他表的主键，否则插入失败。体现数据完整性和表之间的关系	CONSTRAINT 外键名 FOREIGEN KEY(外键列) REFERENCE 外键作为主键所在的表名T(T的主键名)
3	设置默认值	DEFAULT	当插入数据为空时，自动设置为默认值	在列后面直接设置“DEFAULT 值”
4	非空	NOT NULL	被插入的列不允许空	在列后面直接设置“NOT NULL”
5	唯一	UNIQE	指定表的这一列数据不能有重复，即每个值都是唯一的。否则插入失败	UNIQUE(列)

查询条件

用在WHERE之后，条件：使用“=”、“<”、“>”、“<=”、“=>”组成的不等式。
MySQL中0或者NULL表示条件为假，其他值都表示条件为真。

序号	关键字	说明	使用方式举例
1	AND	逻辑与，遵循与短路(第一个条件为假则直接判断最终结果是假，不会再去执行判断后面的条件)	条件1 AND 条件2
2	OR	逻辑或，遵循或短路(第一个条件为真则直接判断最终结果是真，不会再去执行判断后面的条件)	条件1 OR 条件2
3	IN	在某范围内，可以是字符串也可以是数字。如果是连续数值，可以用“BETWEEN...AND...”代替。	IN (值1，值2，值3)
4	NOT IN	不在某范围内，可以是字符串也可以是数字	NOT IN (值1，值2，值3)
5	LIKE	模糊匹配，大小写不敏感，使用BINARY对大小写敏感。常与“_”（代表一个字符）和“%”（代表任意个>=0字符）搭配。与“NOT LIKE”相反	“LIKE 'fef_'”或“LIKE 'jay”
6	RLIKE或REGEXP	扩展正则表达式，使用BINARY可对大小写敏感。与“NOT RLIKE”或“NOT RREGEXP”相反	找长度为5的串“RLIKE '^.{5}&'”或“RLIKE '%jay%'”
7	ORDER BY	对查询结果集排序，默认是大小写不敏感，如果要大小写敏感则用关键词BINARY。升序用ASC（默认，ascending缩写），降序用DESC（descending缩写）	“ORDER BY 列名 DESC” 或 “ORDER BY 列名” 或“ORDER BY BINARY 列名”（大小写敏感）
8	GROUP BY	分组，根据某一列进行分组	GROUP BY 列名
9	AS	AS进行重命名，可以对结果的值列重命名。也用于视图指定数据集	SELECT SUM(money) AS 标识名 FROM salary;

简单的计算函数

序号	函数名	作用	使用
1	COUNT	统计行数	COUNT(1)
2	SUM	求和，仅适用于定义为数值的列	SUM(列名)
3	AVG	求均值，仅适用于定义为数值的列	AVG(列名)
4	MAX	求最大值，适用于定义为数值、字符串、日期、时间类型的列	MAX(列名)
5	MIN	求最小值，用于定义为数值、字符串、日期、时间类型的列	MIN(列名)

待探讨

COUNT(*)、COUNT(1)、COUNT(0)、COUNT(列名)的区别函数

索引及最左匹配原则

数据类型