写文章

mysql---相关笔记总结

MySQL语句的关键词不区分大小写，语句以逗号为结束符，--为MySQL语句注释行。本节的所有命令操作均可以在终端命令实操后显示查看，建议初学者可以在图形化界面同步查看以便加深理解。笔者曾从事于传统IT行业，所以本章全程贯穿一个京东电子产品的数据库实例来进行演示各个语句以便我们更好的理解。

2.1 MySQL基本操作

1. 数据库操作

主要包括数据库的查看、创建、使用、删除操作。

  • **查看当前有哪些数据库 **

show databases;

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
可以看到，目前数据库里有默认的四个数据库，其中：mysql为本地服务器的配置、sys为系统配置信息。
  • **创建数据库 **create database 数据库名 [其他选项];
create database jing_dong;


-- create database jing_dong charset=utf8;
-- charset是指数据库所用的编码集
  • **使用数据库:**use 数据库名
use jing_dong;

mysql> use jing_dong;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

可以看到提示，数据库发生了改变。
  • 查看当前使用的数据库
select database();

mysql> select database();
+------------+
| database() |
+------------+
| jing_dong  |
+------------+

可以看到我们正在使用的数据库是jing_dong
  • **删除数据库 **drop database 数据库名；
drop database jing_dong;

mysql> drop database jing_dong;
Query OK, 0 rows affected (0.04 sec)
通过显示所有数据库的命令会发现jing_dong数据库已被删除。
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

2. 数据表操作

主要包括表的创建、删除、属性查看、显示当前数据库的表及属性、表头的增删改查。

  • 创建表
create table 表名称(列声明);
详解：
CREATE TABLE table_name(
    column1 datatype contrai,
    column2 datatype,
    column3 datatype,
    .....
    columnN datatype,
    PRIMARY KEY(one or more columns)
);

示例：创建商品种类表goods_cates

create table goods_cates(
    id int unsigned primary key auto_increment not null,
    name varchar(40) not null
);

列id，代表商品种类id，取值类型为int unsigned，primary key指id列为主键， 值为自增auto_increment ,且不能为空(not null)；
列name，代表商品种类名称，取值为最长40的字符，不能为空
注意：最后一列的后面不能有逗号

系统会提示：
Query OK, 0 rows affected (0.04 sec)
说明添加成功
  • 显示表
show tables;

可以看到goods_cates表已在数据库中。

mysql> show tables;
+----------------+
| Tables_in_jing |
+----------------+
| goods_cates    |
+----------------+
1 row in set (0.00 sec)
  • 查看表结构
desc 表名;

例：
desc goods_cates;

可以看到goods_cates表的表头信息：共有两个字段id和name，以及它们的详细信息。

mysql> desc goods_cates;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
  • 添加字段(列)
alter table 表名 add 列名 类型;

例：假设刚才我们所统计的商品种类名字太长，需要简称，那么向商品种类表中增加列abbreviation（种类简称）
alter table goods_cates add abbreviation varchar(5);

终端返回以下信息，说明添加成功。
mysql> alter table goods_cates add abbreviation varchar(5);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

通过查看表结构来看是否添加成功abbreviation。里面已经有了该列。

mysql> desc goods_cates;
+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name         | varchar(40)      | NO   |     | NULL    |                |
| abbreviation | varchar(5)       | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
  • 修改字段(列)
不重命名版：alter table 表名 modify 列名 类型及约束;   

例：假设我们现在认为简写还是太长了，仅仅需要3个长度的字符就可以。
alter table goods_cates modify abbreviation varchar(3);

运行后，通过查看表结构，可以看到这一列的数据类型修改为varchar(3)了。

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name         | varchar(40)      | NO   |     | NULL    |                |
| abbreviation | varchar(3)       | YES  |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

重命名版：alter table 表名 change 列原名 列新名 类型及约束;   

例：假设我们觉得表头里“abbreviation”这个单词太长了，想修改为“简称”。
alter table goods_cates change abbreviation 简称 varchar(3);

运行后，通过查看表结构，可以看到这一列的表头更改为“简称”了。

+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name   | varchar(40)      | NO   |     | NULL    |                |
| 简称    | varchar(3)       | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
  • 删除字段(列)
alter table 表名 drop 列名;

例： 突然项目通知，我们的客户所需要的商品的名称一般都很容易记着，根本不需要简称，我们需要删除它。
alter table goods_cates drop 简称;

可以看到，已经删除：

+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(40)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
  • 查看创建表的语句
show create table 表名;

例：假如我们刚进入一个新环境拿到一个新账号需要操作，一般我们可以先看一下表的一些信息。
show create table goods_cates;

可以看到goods_cates表的数据引擎为InnoDB，字符集为latin1 ；主键时字头id等等。

注：一般建议在创建数据库时指定字符集类型，比如utf8 。 create database jing_dong charset=utf8;

  • 删除表
drop table 表名;

例：非常抱歉，项目通知设计中取消掉这一表。
drop table goods_cates;

通过查看表语句show tables;发现jing_dong数据库没有这个表了，由于我们只创建了这一个表，所以提示为空。

mysql> show tables;
Empty set (0.00 sec)

好了，我们可以重新删除掉jing_dong数据库，从一开始就定义数据库字符集为utf8来不断演练以上两节的操作了。

3. 数据增删改查

以上两节相信我们已经可以在数据库里拥有表了。本小节主要来说明在表内增加数据、删除数据、修改数据和简单查询数据。

本小节讲解时会用到以下数据，先列出。大家也可以去之前提到的源码网址获取。

id,name
1,台式机
2,平板电脑
3,服务器/工作站
4,游戏本
5,笔记本
6,笔记本配件
7,超级本
8,路由器
9,交换机
10,网卡
  • 添加数据行
insert [into] 表名 [(列名1, 列名2, 列名3, ...)] values (值1, 值2, 值3, ...);

例：
insert into goods_cates values (0, '台式机');

注：也可以一次insert命令添加多条数据行：insert into goods_cates values (0, '台式机'),(0, '平板电脑');

  • 查询数据行
select 列名称 from 表名称 [查询条件];

例：查询我们添加的所有数据
select * from goods_cates;

注： * 号代表全部.

可以查询看到我们添加的“台式机”。其中，id在台式机那一行为1，是因为我们之前在建立表格时设置其为自增的int数据，因此系统会默认为其赋值，我们一般用0进行占位。

+----+-----------+
| id | name      |
+----+-----------+
|  1 | 台式机    |
+----+-----------+

拓展：我们可以重复利用insert语句将以上提到的1条语句全部添加到数据库，但是很麻烦；所以我们把所有数据存入文件并通过文件导入。

load data local infile '/path/goods_cates.txt' into table goods_cates;

其中：path为文件存在路径。

通过查询所有来查看数据，已经成功导入。

mysql> select * from goods_cates;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | 台式机               |
|  2 | 平板电脑             |
|  3 | 服务器/工作站         |
|  4 | 游戏本               |
|  5 | 笔记本               |
|  6 | 笔记本配件            |
|  7 | 超级本               |
|  8 | 路由器               |
|  9 | 交换机               |
| 10 | 网卡                 |
+----+---------------------+
  • 修改数据行
update 表名称 set 列名称=新值 where 更新条件;

例：
update goods_cates set name='柔性手机' where id=10;

系统返回信息一行已更新，如下：

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

通过查询操作，会发现id=10的数据行中name列的值已修改。

mysql> select * from goods_cates;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | 台式机              |
|  2 | 平板电脑            |
|  3 | 服务器/工作站       |
|  4 | 游戏本              |
|  5 | 笔记本              |
|  6 | 笔记本配件          |
|  7 | 超级本              |
|  8 | 路由器              |
|  9 | 交换机              |
| 10 | 柔性手机            |
+----+---------------------+
  • 删除数据行
delete from 表名 where 条件

例：
delete from goods_cates where id=10;

系统会告诉我们，有一行数据有形象，如

Query OK, 1 row affected (0.00 sec)

通过查询语句来查看，我们发现柔性手机那一行已被删除。

mysql> select * from goods_cates;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | 台式机              |
|  2 | 平板电脑            |
|  3 | 服务器/工作站       |
|  4 | 游戏本              |
|  5 | 笔记本              |
|  6 | 笔记本配件          |
|  7 | 超级本              |
|  8 | 路由器              |
|  9 | 交换机              |
+----+---------------------+

4. 数据备份与恢复命令

和平常生活一样，我们都喜欢备份和恢复，所以在基本操作最后一小节里列出备份和恢复命令。

  • 备份

运行mysqldump命令

示例：
mysqldump –uroot –p 数据库名 > python.sql;

# 按提示输入mysql的密码
  • 恢复

连接mysql，创建新的数据库

退出连接，执行如下命令：

mysql -uroot –p 新数据库名 < python.sql

# 根据提示输入mysql密码

2.2 MySQL查询

上一节中演示了基本的select * from goods_cates查询，可以获取到goods__cates里的所有数据。但是当数据量大时，就需要各种合适的查询方式来获取指定的信息并呈现给我们。本节就来讲述并演示这些操作。在开始之间我们先介绍几个会用到的命令。

  • 查询指定列(指定字段)
select 列1,列2,... from 表名;
例:
select name from goods_cates;

+---------------------+
| name                |
+---------------------+
| 台式机              |
| 平板电脑            |
| 服务器/工作站       |
| 游戏本              |
| 笔记本              |
| 笔记本配件          |
| 超级本              |
| 路由器              |
| 交换机              |
| 网卡                |
+---------------------+
  • 查询指定列，并在显示中为它指定别名
select id as 序号, name as 名字 from goods_cates;

+--------+---------------------+
| 序号   | 名字                |
+--------+---------------------+
|      1 | 台式机              |
|      2 | 平板电脑            |
|      3 | 服务器/工作站       |
|      4 | 游戏本              |
|      5 | 笔记本              |
|      6 | 笔记本配件          |
|      7 | 超级本              |
|      8 | 路由器              |
|      9 | 交换机              |
|     10 | 网卡                |
+--------+---------------------+

注： as也可以为表等起别名以便简化语句等等。

  • 查询时，显示时消除重复列

在select后面列前使用distinct可以消除重复的行

select distinct 列1,... from 表名;

我们在goods_cates表中先添加一列id=15，name=网卡的数据。

insert into goods_cates values (15, '网卡');



+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | 台式机              |
|  2 | 平板电脑            |
|  3 | 服务器/工作站       |
|  4 | 游戏本              |
|  5 | 笔记本              |
|  6 | 笔记本配件          |
|  7 | 超级本              |
|  8 | 路由器              |
|  9 | 交换机              |
| 10 | 网卡                |
| 15 | 网卡                |
+----+---------------------+

接下来，我们获取name列，但不显示id。

mysql> select name from goods_cates;
+---------------------+
| name                |
+---------------------+
| 台式机              |
| 平板电脑            |
| 服务器/工作站       |
| 游戏本              |
| 笔记本              |
| 笔记本配件          |
| 超级本              |
| 路由器              |
| 交换机              |
| 网卡                |
| 网卡                |
+---------------------+

可以看到，显示了两行网卡。我们通过distinct来消除重复并显示，就剩一个网卡了。哇，针实用，就像EXCEL里面去重选项那样来识别本列有多少种选项。

mysql> select distinct name from goods_cates;
+---------------------+
| name                |
+---------------------+
| 台式机              |
| 平板电脑            |
| 服务器/工作站       |
| 游戏本              |
| 笔记本              |
| 笔记本配件          |
| 超级本              |
| 路由器              |
| 交换机              |
| 网卡                |
+---------------------+

1. 条件查询

细心的朋友会发现我们上一节删除数据行时已经实用过where关键词，没错，它就是用来条件查询的一个重要关键词。一般使用where子句来筛选获取其后语句为True的数据行。其查询的语句格式为：

select * from 表名 where 条件;
例：
select * from grands_goods where id=1;

where后面支持多种运算符，进行条件的处理

  • 比较运算符
  • 逻辑运算符
  • 模糊查询
  • 范围查询
  • 空判断

接下来我们一一演示说明。

  • 比较运算符
等于: =
大于: >
大于等于: >=
小于: <
小于等于: <=
不等于: !=

示例：查询id大于7的商品种类

select * from goods_cates where id > 7;

+----+-----------+
| id | name      |
+----+-----------+
|  8 | 路由器    |
|  9 | 交换机    |
| 10 | 网卡      |
| 15 | 网卡      |
+----+-----------+
  • 逻辑运算符
and   与
or    或
not   非
建议为了程序逻辑易读性，可以结合括号操作

示例：查询name=网卡，并且id=10的商品种类

select * from goods_cates where name="网卡" and id = 10;

+----+--------+
| id | name   |
+----+--------+
| 10 | 网卡   |
+----+--------+
  • 模糊查询
关键字： like

% 表示任意多个任意字符
_ 表示一个任意字符

示例：查询商品种类name中包含“本”的数据

select * from goods_cates where name like '%本';

+----+-----------+
| id | name      |
+----+-----------+
|  4 | 游戏本    |
|  5 | 笔记本    |
|  7 | 超级本    |
+----+-----------+
  • 范围查询
in表示在一个非连续的范围内

between ... and ...表示在一个连续的范围内

示例：查询商品种类id为1、3、5的数据

select * from goods_cates where id in(1,3,5);

+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | 台式机              |
|  3 | 服务器/工作站       |
|  5 | 笔记本              |
+----+---------------------+

示例：查询商品种类id为1到5的数据

select * from goods_cates where id between 1 and 5;

+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | 台式机              |
|  2 | 平板电脑            |
|  3 | 服务器/工作站       |
|  4 | 游戏本              |
|  5 | 笔记本              |
+----+---------------------+
  • 空判断
判空is null
注意：null与''是不同的

为了方便后续操作演示，除了上一节的表之外，我们新增商品表goods,并向其插入数据。

create table goods(
    id int unsigned primary key auto_increment not null,
    name varchar(40) default '',
    price decimal(5,2),
    cate_id int unsigned,
    brand_id int unsigned,
    is_show bit default 1,
    is_saleoff bit default 0,
);

insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default); 
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default); 
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default); 
insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default); 
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default); 
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default); 
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default); 
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default); 
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default); 
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default); 
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default); 
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default); 
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default); 
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default); 
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default); 
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default); 
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default); 
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);

商品表的信息为：

2. 排序查询

生活中经常会遇到将商品的价格进行排序，数据也一样。在行业分析数据库中，经常要将竞争对手的产品统计起来并和自己的产品一起排序。

MySQL中使用order by来进行排序查询。

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
说明 {#说明}
  • 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
  • 默认按照列值从小到大排列（asc）
  • asc从小到大排列，即升序
  • desc从大到小排序，即降序

示例： 将所有在库商品安装价格从小到大来排序显示

select * from goods order by price;

3. 集合(统计)函数

几乎所有的数据处理软件都会有简单的数据统计功能，mysql也不例外。本小节介绍总数、最大、最小、总和和平均值五个简单的统计函数。

  • 总数

count(*)表示计算总行数，括号中写星与列名，结果是相同的

示例：显示仓库当前在库商品数量

select count(*) from goods;

  • 最大

max(列)表示求此列的最大值

示例：显示笔记本类商品中最大的id号

select max(id) from goods where cate_name="笔记本";

  • 最小

min(列)表示求此列的最小值

示例：显示台式机类商品中最小的id号

select min(id) from goods where cate_name="台式机";

  • 总和

sum(列)表示求此列的和

示例：显示目前在库台式机类商品总价

select sum(price) from goods where cate_name="台式机";

  • 平均值

avg(列)表示求此列的平均值

示例1：显示目前在库台式机类商品平均价格

select avg(price) from goods where cate_name="台式机";

示例2：显示目前在库台式机类商品平均价格，格式为小数点后两位

select round(avg(price), 2) as "平均价" from goods where cate_name="台式机";

4. 分组

生活中经常需要分组统计众多的商品名目，MySQL采用group by来进行分组查询。

  • group by

将查询结果按照指定的字段(列)进行分组，内容相同的为一组

示例：按照产品种类来查看目前的商品都有哪些种类的产品

select cate_name from goods group by cate_name;

  • group by + group_concat(name)

将分组后的name字段信息按照分组结果打印出来

示例：按照产品种类来分组显示商品名称

select cate_name, group_concat(name) from goods group by cate_name;

  • group by + 集合函数

示例：按照产品种类来分组，并显示各自的在库商品数量

select cate_name, count(*) from goods group by cate_name;

  • group by + having

分组之后的条件查询

示例：按照产品按照产品种类来分组，并显示各自的在库商品数量大于3的商品id，

select cate_name, group_concat(id) from goods group by cate_name having count(*) > 3;

having与where类似，可以筛选数据，where后的表达式怎么写，having后就怎么写
where针对表中的列发挥作用，查询数据
having对查询结果中的列发挥作用，筛选数据
select id,name,price as s from goods having s>2000 ;
这里不能用where因为s是查询结果，而where只能对表中的字段名筛选

  • group by + with rollup

在最后新增一行，来记录当前列记录的总和

示例：按照产品按照产品种类来分组，并显示各自的在库商品数量大于3的商品id，且在最后一行列出所有商品id

select cate_name, group_concat(id) from goods group by cate_name with rollup having count(*) > 3;

5.部分行查询

如上图，我们的goods表仅仅21行数据却已经不能全部显示了。因此我们常常需要去显示部分行数据。MySQL里用limit来实现此功能。

elect * from 表名 limit start,count;

说明：从start开始，获取count条数据

示例：显示第1行到第10行的数据

select * from goods limit 1, 10;

6. 连接查询

本小节数据已之前采用goods表和下面那个只有一行数据的goods_cates表来进行演示说明。

  • 左连接 left join

以左表为准，去右表找数据，如果没有匹配的数据，则以null补空位，所以输出结果数>=左表原数据数

示例：

select * from goods left join goods_cates on goods.cate_name= goods_cates.name;

  • 右连接 right join

    a left join b 等价于 b right join a 推荐使用左连接代替右连接。

  • 内连接 inner join

查询结果是左右连接的交集，即左右连接的结果去除null项后的集。

示例：

select * from goods inner join goods_cates on goods.cate_name= goods_cates.name;

7. 自关联查询

自连接查询其实等同于连接查询，需要两张表，只不过它的左表（父表）和右表（子表）都是自己。请记住这个。

在之前的例子里我们使用了goods表和goods_cates表来表明仓库中的商品信息和商品种类。假如我们现在需要来做一个产品层级分类：一级分类为商品种类，二级分类为商品名；商品信息仅仅展示id和名字。那么按照之前那样，我们建立两张表goods_1和goods_cates_1，每张表表头均是id、 name、 cate_name 。由于商品种类为一级分类，所以我们记其cate_name=null。

create table goods_1(  
    id smallint primary key auto_increment,  
    name varchar(20) not null,  
    cate_name varchar(20)
);  

create table goods_cates_1(  
    id smallint primary key auto_increment,  
    name varchar(20) not null,  
    cate_name varchar(20) 
);  

insert into goods_1 values(1,'r510vc 15.6英寸笔记本','笔记本');
insert into goods_1 values(0,'y400n 14.0英寸笔记本电脑','笔记本');
insert into goods_1 values(0,'x550cc 15.6英寸笔记本','笔记本');
insert into goods_1 values(0,'x240 超极本','超级本');
insert into goods_1 values(0,'u330p 13.3英寸超极本','超级本');
insert into goods_1 values(0,'vp13226scb 触控超极本','超级本');

insert into goods_cates_1 values(1, '笔记本', null);
insert into goods_cates_1 values(0, '超极本', null);

两张表信息如下：

goods_1表仅能看到产品的上一级分类，并不能看见最终分类，最终分类信息在goods_cates_1表里。现在我们需要显示每一个产品完整的两层层级信息，怎么做呢？用上一节的连接命令将两张表信息连接起来就可以啦：

select goods_cates_1.cate_name,goods_1.cate_name,goods_1.name
from goods_1 left join goods_cates_1 on goods_1.cate_name = goods_cates_1.name;

假如产品分类层级有10层，我们又需要建立剩下的八张表。通过观察发现，这两张表的结构基本相同，那么以上功能能否用一张表实现呢？答案是肯定的。

我们现在来创建一张表goods_cates_2用于产品层级信息存储，它的字段有：编号——id、名字——name、所属种类id——cate_id。其中默认顶级分类的所属种类为null。

create table goods_cates_2(  
    id smallint primary key auto_increment,  
    name varchar(20) not null,  
    cate_id smallint
); 

insert into goods_cates_2 values(1, '笔记本', null);
insert into goods_cates_2 values(2, '超极本', null);
insert into goods_cates_2 values(0,'r510vc 15.6英寸笔记本',1);
insert into goods_cates_2 values(0,'y400n 14.0英寸笔记本电脑',1);
insert into goods_cates_2 values(0,'x550cc 15.6英寸笔记本',1);
insert into goods_cates_2 values(0,'x240 超极本',2);
insert into goods_cates_2 values(0,'u330p 13.3英寸超极本',2);
insert into goods_cates_2 values(0,'vp13226scb 触控超极本',2);

现在我们假设有两张表gc2为goods_cates_2代表第二级分类的信息，等价于上面两张表例子的goods_1表，gc1为goods_cates_2代表第一级分类的信息，等价于上面两张表例子中的goods_cates_1表。使用上面例子中的方法，通过左连接来显示完整的两层层级信息。如下图所示，语句中的元素实现了一一对应。也就是说我们给goods_cates_2表起了两个别名gc1和gc2，让它们来连接实现完整的两层层级信息。功能和我们上面两张表的方案一样，且如果有10层分类，我们并不需要新建表。

这和本节开始时说的完全一样，对，这就是自连接。如下为操作命令示例。其中gc1 和gc2 为我们为自连接表启的别名，语法中要求select的显示列需要用别名来区分。

select gc1.cate_id,gc1.name,gc2.name 
from goods_cates_2 gc2 left join goods_cates_2 gc1 on gc2.cate_id=gc1.id;

8. 子查询

之前我们所看到的所有查询语句均是单一命令的原始查询，并没有基于某一个查询结果的二次查询语句。所以这一小节我们引入子查询这一概念，来做基于某一个查询结果的二次查询操作。

我们知道查询基本语句为：select A from B where C;

对应的子查询语句分别为

  • where型子查询

把内层查询结果当作外层查询的比较条件

select A from B where (查询语句);

示例：显示商品表内的一组商品名称和商品种类数据，这些数据满足以下条件：商品种类的id号在商品种类表中小于2

select name, cate_name from goods where cate_name in (select name from goods_cates where id < 3);

  • from型子查询

把内层的查询结果供外层再次查询

select A from (查询语句) as D where C;

示例： 显示一组数据中商品种类为笔记本的数据，这批数据来自于id小于5的商品表数据集合。

select name, cate_name from (select * from goods where id < 5) as t where t.cate_name = "笔记本";

  • exists型子查询

把外层查询获得的行数据逐条传递到内层查询中被调用的地方，若内层查询非空则保留该条行数据，若内层查询为空则舍弃该条行数据。

select A from B where exists(查询语句)；

示例： 从goods_cates表中显示种类姓名，但是这些种类姓名必须满足一个条件，就是在goods表中存在此种类的商品。

select name from goods_cates where exists(select * from goods where goods.cate_name = goods_cates.name);

9. 小结

查询的综合完整语句：

  SELECT select_expr [,select_expr,...] [
  FROM tb\_name

  \[WHERE 条件判断\]

  \[GROUP BY {col\_name \| postion} \[ASC \| DESC\], ...\] 

  \[HAVING WHERE 条件判断\]

  \[ORDER BY {col\_name\|expr\|postion} \[ASC \| DESC\], ...\]

  \[ LIMIT {\[offset,\]rowcount \| row\_count OFFSET offset}\]
  ]

 

 

 

 

Install 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

全文检索

使用全文检索前，先要建立字段索引，比如我要这样查询：

1
match(`name`,`name2`) aganst("abc" IN BOOLEAN MODE)

 

即查询name或者name2字段中存在字符串abc的记录，则需要事先将name与name2字段做联合的索引，类似于这样：

多表全文检索

1
select * from a left join b on a.pid = b.pid WHERE MATCH(`id`,`ip`) AGAINST("abc" IN BOOLEAN MODE) OR MATCH(`name`,`port`) AGAINST("123" IN BOOLEAN MODE)

说明：其中字符串abc也可以用%s代替，参数化构造sql语句。值得注意的是，默认情况下mysql只支持4个字符以上的全文索引。即搜索”nginx”是可以的，但是搜索”tcp”就不行。解决方案是通过修改/etc/my.conf配置文件，增加一行ft_min_word_len = 2，然后重启mysql，重新建索引（但是我测试失败了）。

全文检索模糊匹配与精确匹配

当我们搜索:thief.one，若不用双引号包括，则会匹配出存在thief、one的结果，因为默认会使用.来分割字符串，搜索就变成了存在thief或者one的字符串，因此精确搜索为:”thief.one”就可以解决，因为把其当成了一个完整的字符串。

多表联合查询

多表查询有三种方式：交叉查询、等值查询、外部查询（左连接、右连接）
参考：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

导出数据库为sql文件

1
mysqldump -uusername -ppassword db_name > file_name.sql

Mysql使用权限问题

mysql初始化设置密码

当我们刚在服务器上安装完mysql，默认是可以无密码登陆的

1
mysql -u root

 

当然，我们肯定要为mysql设置密码，那么怎么设置最方便呢？

1
2
3
4
>>use mysql;
>>update user set host = '%' where user = 'root';
>>UPDATE user SET Password=PASSWORD('nmask') where USER='root';
>>flush privileges;

 

注意：这里需要注意一点，以上命令输入成功之后，仍然无法用root账号密码登陆，这是为什么呢？因为默认root账户有好几个，会影响设置密码的这个root账户，需要将其余几个root账户都删除。

1
2
>>delete from user where user='root' and host!='%';
>>flush privileges;

 

重启mysql，应该可以使用设置了密码的root账户登陆了。

忘记密码？安全模式

如果忘记了mysql密码怎么办？没事，可以进入安全模式，重设密码。

首先，我们停掉MySQL服务：

1
sudo service mysqld stop

 

以安全模式启动MySQL：

1
sudo mysqld_safe --skip-grant-tables --skip-networking &

 

注意我们加了–skip-networking，避免远程无密码登录 MySQL。这样我们就可以直接用root登录，无需密码：

1
mysql -u root

 

接着重设密码：

1
2
3
mysql> use mysql;
mysql> update user set password=PASSWORD("nmask") where User='root';
mysql> flush privileges;

 

重设完毕后，我们退出，然后启动 MySQL 服务：

1
sudo service mysql restart

 

参考：http://www.ghostchina.com/how-to-reset-mysqls-root-password/

只能本地连接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配置项，超过这个值会日志会使用磁盘数据，这时，效率会有所下降。所以比较好的做法是，在事务大小达到配置项数据级前进行事务提交。

Mysql特殊字符编码问题

存储报错

有时会碰到在往mysql中存储一些特殊字符时（微信、qq表情等），会提示编码报错，类似如下：

1
(1366, "Incorrect string value: '\\xF0\\x9F\\x9A\\x80 D...’ for

 

存储特殊编码解决方案

首先得明确，需要将mysql的utf8编码改成utf8mb4，utf8mb4是对utf8的补充，让其可以保存一些特殊字符。

修改数据库编码

可以选择数据库，然后点击操作，排序规则改为：utf8mb4_general_ci

修改数据表编码

选中数据表，点击操作，修改排序规则为：utf8mb4_general_ci

修改数据字段编码

选择字段，点击修改，排序规则改为：utf8mb4_general_ci

修改代码中连接mysql的编码：

这里以python为例子：

1
mysql_charset="utf8mb4"

 

说明：必须要以上4个编码都改成utf8mb4，才不会报错。

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，插入多条数据

posted @ 2019-08-03 15:55  AnthonyWang  阅读(185)  评论(0)    收藏  举报