MySQL必知必会1-12-基础知识

MySQL PV:

数据库基础

主键

主键每一行都应该可以唯一标识自己的一列。没有主键，更新或删除表中特定行很困难，因为没有安全的方法保证只涉及相关的行。

表中的任意行都可以作为主键，只要它满足以下条件：

  • 任意两行不具有相同的主键值。
  • 每个行都必须具有一个主键（主键不允许为NULL）。

主键通常是定义在表的一个列中，但并不是只能一列，也可以多个列一起使用作为主键。当使用多列作为主键时，上述条件必须应用到构成主键的所有列，所有列值的组合必须时唯一的（但单个列的值可以不唯一）。

主键的好习惯：

  • 不更新主键列中的值。
  • 不重用主键列中的值。
  • 不再主键列中使用可能会更改的值。

MySQL

MySQL是一种DBMS，即它是一种数据库软件。

数据库分类

DBMS可分为两类：

  • 基于共享文件系统的DBMS
  • 基于客户机-服务器的DBMS
    • 客户机是与用户打交道的软件。
    • 服务器部分是负责所有数据访问和处理的一个软件。

数据库命令

USE命令

USE可以用于选择数据库。

例如选择数据库HRDB

1
USE HRDB;

SHOW命令

SHOW命令可以用来展示数据，例如表、服务器状态，用户等。

例如：

  1. 展示表-applicant2022

    1
    SHOW applicant2022;

  2. 展示服务器状态

    1
    SHOW STATUS;

  3. 展示创建的数据库

    1
    2
    SHOW CREATE DATABASE;
    SHOW CREATE TABLE;

检索数据

SELECT from 语句可以用来检索数据。

其基本结构为

SELECT col1, col2, ..., coln from table

例如：

  1. 从表products中选择一列prod_name

    1
    SELECT prod_name FROM products;

  2. 从表products中选择两列prod_name, prod_price

    1
    SELECT prod_name, prod_price from products;

从表products中检索所有的列

可以通过通配符*来匹配所有列

1
SELECT * FROM products

检索不同的列

上面返回数据的列是包含重复元素的。而可以使用DISTINCT关键词来去掉列中的重复值。

例如：

1
SELECT DISTINCT vend_id FROM products;

注意：DISTINCT关键词会应用于所有列而不仅前置它的列。例如SELECT vend_id, prod_price from products，除非两个列都不同，否则所有的列都将被检索出来。

限制结果

SEELCT语句返回匹配的所有行，而LIMIT关键词可以指定返回的行数。

例如：从products中选择products的前5行。

1
SELECT prod_name FROM products LIMIT 5;

也可以指定返回行数的位置。其格式有以下两种：

  • LIMIT start, count:包含start，第一列为0。
  • LIMIT start OFFSET count:与上面代码意义一样。

例如：从products中选择products的从第3列开始的前5行。

1
2
SELECT prod_name FROM products LIMIT 3, 5;
SELECT prod_name FROM products LIMIT 3 OFFSET 5;

使用完全限定的表明

所谓的完全限定名，对于字段来说就是表名+列名，而对于表名来说就是数据库+表名。

例如：

1
SELECT products.prod_name FROM HRDB.products;

排序检索数据

可以使用ORDER BY子句，来对筛选出的数据进行排序。

单例排序

简单按照一列排序。

例：

1
SELECT prod_name FROM products ORDER BY prod_name;

多列排序

有时需要按照多列排序，即第一列相同，则按照第二列来排序。

例如：

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

指定排序方向

默认排序的方向为升序（关键词为ASC），而可以使用DESC来指定为降序。

例如：

  1. 升序

    1
    2
    SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price;
    SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price ASC;		//前后等价

  2. 降序

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

过滤数据

WHERE语句可以用来指定过滤的条件。WHERE后接条件相等，不等，大于或小于等。

普通筛选

从products表中选出prod_price等于2.5的列。

1
SELECT prod_name, prod_price FROM products WHERE prod_price = 2.50;

注意：在同hi使用ORDER BY语句和WHERE时，应该让ORDER BY语句位于WHERE之后。

下面是WHERE子句操作符

WHERE子句操作符

检查单个值

可以使用=来检查单个值：

1
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';

注意MySQL来执行匹配的时默认不区分大小写。

不匹配检查

可以使用<>来进行不等于匹配。

1
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003;

范围检查

为了检查某个列的范围可以用BETWEEN关键词。

例如检索价格在5-10美元之间的商品：

1
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

注意：两个值必须用AND关键词分隔。 BETWEEN匹配范围内的所有的值，包括开始值和结束值。

空值判断

对于NULL的判断，有一个特殊的子句-IS NULL。

例如：

1
SELECT prod_name FROM products WHERE prod_price IS NULL;

数据过滤

组合WHERE子句

MySQL允许给出多个WHERE子句。这些子句有两种组合方式：

  • AND关键词：并，都要满足，优先级最高。
  • OR关键词：或，只满足一个即可。

AND子句

例如：

1
SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;

该语句表示，既要vend_id等于1003，又要prod_price小于等于10.

OR子句

例如：

1
SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id = 1003 OR prod_price <= 10;

计算次序

WHERE可以包含任意数目的AND和OR操作符。并允许两者以任意的顺序结合来进行组合为更加复杂的过滤句子。但是注意AND有更高的优先级。如果想要改变，则需要括号()。

例如：

1
2
SELECT prod_name，prod_price FROM products
WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

所实际的顺序是先计算后面的AND，再计算前面的OR。

如果要改变顺序，可以通过一下格式：

1
2
SELECT prod_name，prod_price FROM products
WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

IN操作符

圆括号在WHERE子句中还有另一种用法。IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN合法值为由逗号分隔的多个值。

例如：取vend_id的值在1002,1003,1004中的任意一个：

1
2
SELECT prod_name，prod_price FROM products
WHERE vend_id IN (1002,1003,1004) ORDER BY prod_name;

使用IN操作符的优点为：

  • 在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
  • 在使用IN时，计算的次序更容易管理。
  • IN操作符一般比OR操作符清单执行更快。
  • IN操作符的最大优点是可以包含其他SELECT语句，使得能够动态地建立WHERE语句。

NOT操作符

WHERE子句中地NOT操作符有且只有一个功能，那就是否定它之后所跟地任意条件。

例如vend_id不在1002,1003,1004之中：

1
2
3
SELECT prod_name，prod_priceFROM products
WHERE vend_id NOT IN (1002,1003,1004)
ORDER BY prod_name;

注意：

MySQL中地NOT支持对IN、BETWEEN和EXISTS子句进行取反。

用通配符进行过滤

LIKE操作符

LIKE操作符用于与后面的搜索模式组合进行过滤。

百分号（%）通配符

%表示任何字符出现任意次数（0-∞）。

例如：匹配prod_name为以jet开头地所有内容：

1
2
SELECT prod_id，prod_nameFROM products
WHERE prod_name LIKE 'jet%';

注意：根据MySQL地配置方式，搜索可以是区分大小写的。如果区分大小写jet%与Jetpack 1000将不匹配。

注意通配符可以出现在字段的任意位置，例如：

1
2
SELECT prod_id，prod_nameFROM products
WHERE prod_name LIKE '%anvil%';

表示任意内容，只要包含anvil就可以匹配到。如1anvil、anval1q、qanval0`。

下划线（_）通配符

另一个通配符（_）只匹配单个字符。

如：

1
2
SELECT prod_id，prod_nameFROM products
WHERE prod_name LIKE '_ ton anvi7';

只匹配第一开头包含一个单词，后面为 ton anvi7的单词。如1 ton anvi7，q ton anvi7等。

使用通配符的技巧

使用通配符应当记住以下技巧：

  • 不要过度使用通配符。其它操作符能够达到相同效果的目的，应该使用其他操作符。
  • 在确定需要使用通配符时，除非绝对必要，否则不要把它们放在搜索模式的开始处。因为这会使检索速度慢很多。

用正则表达式进行搜索

由于前面的通配符能实现的通配模式有限，所以引入了正则表达式来进行匹配。

其基本语句是REGEXP关键词。其后接的字符串即为正则表达式。

例如表示name为1000的列：

1
SELECT * FROM table1 WHERE name REGEXP '1000';

进行OR匹配

可以通过|来间隔两个串，并且匹配这来给你个串中的一个。

例如：匹配prod_name为1000或2000：

1
2
SELECT prod_nameFROM products
WHERE prod_name REGEXP '1000 | 2000'ORDER BY prod_name;

可以给出两个以上的OR条件，用于匹配多个可能的情况。

匹配多字符中的一个

可以通过[xyz]来匹配z, y, z中的任意一个字母。

例如：

1
2
SELECT prod_nameFROM products
WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name;

匹配1tom、2tom或者3tom。

匹配范围

可以结合[]和-来表示范围。如，[a-z]来匹配a到z的任意一个字母；[1-10]来匹配1到10的任意一个数字。

例如：匹配1 Ton到5 Ton：

1
2
SELECT prod_nameFROM products
WHERE prod_name REGEXP '[1-5] Ton' ORDER BY prod_name ;

而为了方便，MySQL定义了特定范围的串。具体如下表：

正则表达式-字符类

匹配特殊字符

对于MySQL定义的正则表达式特殊字符，如%, _等，可以通过转义来进行。

如：\\.匹配.，\\%匹配%。具体如下表。

正则表达式-转义字符

匹配出现次数

有时候需要匹配的模式出现多次，则需要量词。两次直接跟在模式的后面。如a{5}匹配aaaaa，a出现5次。而所有的两次如下：

正则表达式-量词

例如：匹配id为一个或多个1的值：

1
SELECT * FROM table1 WHERE id REGEX '1+';

定位符

没有定位词时，正则表达式总是匹配所有的内容，而通过定位符可以匹配开头或者结尾。具体所有的定位符如下：

正则表达式-定位符

例如匹配数字开头的id：

1
SELECT * FROM table1 WHERE id REGEX '^[0-9]';

创建计算字段

计算字段可以重新组合、计算原有表中的数据。

拼接字段

利用Concat(..args)函数可以用来拼接字段。这其中参数不仅可以是表中字段，也可以是自定义字符，其间用逗号,分割。

例如：其中有表的字段vend_name、vend_country，也有(、)：

1
2
SELECT Concat(vend_name，' ( ', vend_country，')')FROM vendors
ORDER BY vend_name ;

别名

刚才创建的新字段实际上是没有名字的，而需要给他给一个名字，即别名（不止新字段可以指定别名，存在于表中的字段也可以指定别名）。其采用关键字AS来指定。

如：给新字段指定了别名vend_title

1
2
3
SELECT Concat(RTrim(vend_name)，'('，RTrim(vend_country)，')') AS vend_title
FROM vendors
ORDER BY vend_name;

执行算术计算

不仅可以用Concat(...)来拼接字段，还可以使用算数运算符来获得新的数字字段。

例如：利用乘法计算新字段

1
2
3
4
5
SELECT prod_id，
quantity,item_price，
quantity*item_price As expanded_price
FROM orderitems
WHERE order_num = 20005;

下面是具体所有的操作符：

正则表达式-算术操作符

使用数据处理函数

MySQL包含了很多处理函数。

文本处理函数

字符处理函数包含以下：

正则表达式-文本处理函数1

正则表达式-文本处理函数2

其中Soundex()是读音类似的一个处理函数。这个一般是用于英语。

日期和时间处理函数

对于日期和时间，MySQL也内置了一些函数。所有的函数如下：

正则表达式-常用日期和时间处理函数

值得注意的是：无论什么时候指定一个日期，不管插入或更新表值还是用WHERE语句进行过滤，日期必须为格式yyyy-mm-dd。

因此，基本的日期比较简单：

1
2
SELECT cust_id，order_numFROM orders
WHERE order_date = '2005-09-01';

利用Datr()函数可以创建一个日期时间的日期部分。如一个值dt在表中为一个DateTime类型，假设其为2022-02-02 08:08:08。则Date(dt)就为2022-02-02

而日期之间可以用BETWEEN AND来筛选确定日期范围内的列。

如：筛选生日在2001年2月1日到2002年2月4日之间的列：

1
SELECT * FROM table1 WHERE birthday BETWEEN '2001-2-1' AND '2002-2-4'

数字处理函数

下面是所有的处理函数：

正则表达式-数值处理函数

汇总数据

又是仅仅需要获得数据的一些汇总，而并不是数据本身，如数量等。

MySQL也涉及相关的函数，具体如下：

正则表达式-聚集函数

汇聚不同值

对于所有的聚集函数，可以指定两个参数来表示计算的值：

  • ALL（默认）：所有的值都将被计算。
  • DISTINCT：只计算不同的值。

例如：表示统计不同name的所有列：

1
SELECT COUNT(DISTINCT name) FROM table1;

多个汇集函数

汇集函数就可以多个同时使用。

如：同时使用了4个汇聚函数：

1
2
3
SELECT COUNT(*) AS num_items,
MIN(prod_price) As price_min,MAX(prod_price) AS price_max,AVG(prod_price) As price_avg
FROM products;

组合汇聚函数

还可以嵌套组合汇聚函数：

1
SELECT AVG(COUNT(car1), COUNT(car2), COUNT(car3), COUNT(car4)) FROM sales;

汇聚函数可以嵌套组合，如上，先统计每种车类型的数量，然后统计其平均数（虽然一般不会这些设计表，只是示例用法）。

使用别名可以更清晰一些。

如：

1
SELECT COUNT(car1) AS c1, COUNT(car2) AS c2, COUNT(car3) AS c3, COUNT(car4) AS c4, AVG(c1, c2, c3, c4) AS carAvg FROM sales;

Powered by Hexo and Hexo-theme-hiker

Copyright © 2019 - 2024 My Wonderland All Rights Reserved.

UV : | PV :