【Mysql】学习笔记

橙色

基本操作

登录指令：

mysql -u root -p

启动、关闭、重启mysql指令（适用于centos7）：

sudo service mysqld start
sudo service mysqld stop
sudo service mysqld restart

查看mysql运行状态：

service mysqld status

删除和创建表

删除表

drop table [表名];

创建表比较复杂

修改密码（ubuntu18.04可行，其余版本行不行不知道）

参考了这篇文章

经验仅适用于ubuntu18.04

因为密码错误或者刚初始化不知道密码是多少，无法登入mysql服务器，所以要先找到/etc/mysql/mysql.conf.d/mysqld.cnf 这个文件中的[mysqld]这一段。这也是网上很多博文的一个坑，无法找到my.cnf配置文件。这是因为mysql5.7.17将原先的my.cnf改为mysqld.cnf，并放在/etc/mysql/mysql.conf.d/路径下。

进入mysqld.cnf 这个文件后，找到 [mysqld] 段，并加入一行“skip-grant-tables”,保存退出 。

重启mysql服务,用空密码进入mysql管理命令行，切换到mysql库,操作命令如下，

sudo service mysql restart

登陆进去mysql后

use mysqld
update mysql.user set authentication_string=password('123') where user='root’;
update user set plugin="mysql_native_password";
flush privileges; #立即生效

退出mysql登录：

quit

至此，密码修改完成，记得把上面加入的skip-grant-tables命令注释掉

该书附录b中提到的两个.sql脚本文件可在该网站下载。具体下载地方如下：

先创建一个数据库

选定该数据库

将上面所下载好的两个sql脚本文件传入虚拟机或者服务器中，在mysql命令行中可以通过如下指令执行这两个脚本：

至此，准备工作已就绪，下面开始学习

3 使用MYSQL

了解数据库和表

查看当前选择的数据库中所有表的名称

SHOW TABLES;

显示customers表的信息

//这两个语句有相同的效果
SHOW COLUMNS FROM customers;
DESCRIBE customers;

4 检索数据

检索单个列

从products表中检索一个名为prod_name的列，因为没有明确排序查询结果，所以返回的数据的顺序是随机的。

SELECT prod_name FROM products;

检索多个列

SELECT prod_id,prod_name,prod_price FROM products;

检索所有列

SELECT * FROM products;

检索不同的行

SELECT能返回对应列名的所有行，假如我想得到products表中产品的所有供应商ID：

SELECT语句返回14行（即使表中只有4个供应商），因为products表中列出了14个产品。那么，如何检索出有不同值的列表呢？
解决办法是使用DISTINCT关键字，顾名思义，此关键字指示MySQL只返回不同的值。

限制结果

此语句使用SELECT语句检索单个列。LIMIT 5指示MySQL返回不多于5行。

SELECT prod_name FROM products LIMIT 5;

为得出下一个5行，可指定要检索的开始行和行数，如下所示：
LIMIT 5, 5指示MySQL返回从行5开始的5行。第一个数为开始位置，第二个数为要检索的行数。

SELECT prod_name FROM products LIMIT 5,5;

行0：检索出来的第一行为行0而不是行1。因此，LIMIT 1, 1将检索出第二行而不是第一行。行0是第一行，行1是第二行

使用完全限定的表名

这里指定了一个完全限定的列名

SELECT products.prod_name FROM products;

当然表名也是可以完全限定的

SELECT products.prod_name FROM experience.products;

5 排序检索数据

排序数据

下面的SQL语句返回某个数据库表的单个列。但请看其输出，并没有特定的顺序。

为了明确地排序用SELECT语句检索出的数据，可使用ORDER BY子句。ORDER BY子句取一个或多个列的名字，据此对输出进行排序。请看下面的例子：

SELECT prod_name FROM products ORDER BY prod_name;

按多个排序

下面的代码检索3个列，并按其中两个列对结果进行排序——首先按价格，然后再按名称排序。注意仅在多个行具有相同的prod_price值时才对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序。

SELECT prod_id,prod_price,prod_name 
FROM products 
ORDER BY prod_price,prod_name;

指定排序方向

数据排序不限于升序排序（从A到Z）。这只是默认的排序顺序，还可以使用ORDER BY子句以降序（从Z到A）顺序排序。为了进行降序排序，必须指定DESC关键字。如下面的例子，最贵的排在前面

SELECT prod_id,prod_price,prod_name 
FROM products 
ORDER BY prod_pricE DESC;

DESC关键字只应用到直接位于其前面的列名。在上例中，只对prod_price列指定DESC，对prod_name列不指定。因此，prod_price列以降序排序，而prod_name列（在每个价格内）仍然按标准的升序排序。
所以，如果想在多个列上进行降序排序，必须对每个列指定DESC关键字。

使用ORDER BY和LIMIT的组合，能够找出一个列中最高或最低的值。下面例子展示了如何找出最昂贵物品的值：

mysql> SELECT prod_price
    -> FROM products
    -> ORDER BY prod_price DESC
    -> LIMIT 1;

ORDER BY子句的位置    在给出ORDER BY子句时，应该保证它位于FROM子句之后。如果使用LIMIT，它必须位于ORDER BY之后。使用子句的次序不对将产生错误消息

6 过滤数据

使用WHERE子句

数据库表一般包含大量的数据，很少需要检索表中所有行。通常只会根据特定操作或报告的需要提取表数据的子集。只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤。WHERE子句在表名（FROM子句）之后给出，如下所示：

mysql> SELECT prod_name,prod_price
    -> FROM products
    -> WHERE prod_price=2.5;

WHERE子句的位置 在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后，否则将会产生错误

6.2 WHERE子句操作符

MySQL支持下表所列出的所有操作符

来看几个使用其他操作符的例子。

第一个例子是列出价格小于10美元的所有产品：

mysql> SELECT prod_name,prod_price
    -> FROM products
    -> WHERE prod_price<10;

范围值检查
为了检查某个范围的值，可使用BETWEEN操作符。它需要两个值，即范围的开始值和结束值。例如，BETWEEN操作符可用来检索价格在5美元和10美元之间或日期在指定的开始日期和结束日期之间的所有产品。

mysql> SELECT prod_name,prod_price
    -> FROM products
    -> WHERE prod_price BETWEEN 5 AND 10;

从这个例子中可以看到，在使用BETWEEN时，必须指定两个值——所需范围的低端值和高端值。这两个值必须用AND关键字分隔。BETWEEN匹配范围中所有的值，包括指定的开始值和结束值。

空值检查

NULL 无值（no value），它与字段包含0、空字符串或仅仅包含空格不同。

mysql> SELECT cust_id
    -> FROM customers
    -> WHERE cust_email IS NULL;

7 数据过滤

组合WHERE子句

第6章中介绍的所有WHERE子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制，MySQL允许给出多个WHERE子句。这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用。

AND操作符

如下的例子，通过WHERE组合子句，检索由供应商1003制造且价格小于等于10美元的所有产品的id、名称和价格。

OR操作符

应该比较好理解，检索由供应商id为1002或1003的供应商所制造的所有产品的产品名和价格

mysql> SELECT prod_name,prod_price
    -> FROM products
    -> WHERE vend_id=1002 OR vend_id=1003;

AND和OR计算次序

如下的例子，明明设置了prod_price应该大于等于10，但筛选出来的仍旧有两条数据的price小于10，这是因为AND的优先级比OR更高，所以SQL理解为由供应商1003制造的任何价格为10美元（含）以上的产品，或者由供应商1002制造的任何产品，而不管其价格如何。

解决的方法就是通过圆括号，因为圆括号具有较AND或OR操作符高的计算次序，如下：

IN操作符

圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。下面的例子说明了这个操作符：

NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。这里的NOT否定跟在它之后的条件，因此，MySQL不是匹配1002和1003的 vend_id ，而是匹配 1002 和 1003 之外供应商的vend_id。

8 用通配符进行过滤

LIKE操作符

通配符：用来匹配值的一部分的特殊字符。
搜索模式：由字面值、通配符或两者组合构成的搜索条件。

为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

百分号(%)通配符

最常使用的通配符是百分号（%）。在搜索串中，%表示任何字符出现任意次数。例如，为了找出所有以词jet起头的产品，可使用以下SELECT语句：

分析：此例子使用了搜索模式’jet%'。在执行这条子句时，将检索任意以jet起头的词。%告诉MySQL接受jet之后的任意字符，不管它有多少字符。

通配符可在搜索模式中任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它们位于模式的两端：

分析：搜索模式’%anvil%'表示匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符。

下划线(_)通配符

下划线的用途与%一样，但下划线只匹配单个字符而不是多个字符。

使用通配符的技巧

• 通配符搜索的处理一般要比前面讨论的其他搜索所花时间更长。所以不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
• 在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。

9 用正则表达式进行搜索

使用MySQL正则表达式

基本字符匹配

关键字REGEXP，告诉MySQL：REGEXP后所跟的东西作为正则表达式使用了正则表达式’.000’。'.'是正则表达式语言中一个特殊的字符。它表示匹配任意一个字符，因此，1000和2000都匹配且返回。

MySQL中的正则表达式匹配（自版本3.23.4后）不区分大小写（即，大写和小写都匹配）。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY ‘JetPack .000’。

进行OR匹配

为搜索两个串之一（或者为这个串，或者为另一个串），使用 | ，如下所示：

分析：语句中使用了正则表达式1000|2000。|为正则表达式的OR操作符。它表示匹配其中之一，因此1000和2000都匹配并返回。

匹配几个字符之一

这里，使用了正则表达式[123] Ton。[123]定义一组字符，它的意思是匹配1或2或3，因此，1 ton和2 ton都匹配且返回（没有3 ton）。

字符集合也可以被否定，即，它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处放置一个^即可。因此，尽管[123]匹配字符1、2或3，但 [^123] 却匹配除这些字符外的任何东西。

匹配范围

集合可用来定义要匹配的一个或多个字符。例如，下面的集合将匹配数字0到9：

为简化这种类型的集合，可使用-来定义一个范围。下面的式子功能上等同于上述数字列表：

此外，范围不一定只是数值的，[a-z]匹配任意字母字符。

举个例子：

分析：这里使用正则表达式[1-5] Ton。[1-5]定义了一个范围，这个表达式意思是匹配1到5，因此返回3个匹配行。由于5 ton匹配，所以返回.5 ton。

匹配特殊字符

正则表达式语言由具有特定含义的特殊字符构成。我们已经看到.、[]、|和-等，还有其他一些字符。请问，如果你需要匹配这些字符，应该怎么办呢？
如下：

这并不是期望的输出，.匹配任意字符，因此每个行都被检索出来。

为了匹配特殊字符，必须用\为前导。\-表示查找-，\.表示查找.。

分析：\.匹配.，所以只检索出一行。这种处理就是所谓的转义（escaping），正则表达式内具有特殊意义的所有字符都必须以这种方式转义。这包括.、|、[]以及迄今为止使用过的其他特殊字符。