mysql

MySQL 内部原理面试常考题

  • InnoDB 有行级别的锁,而 MyISAM 只能锁定到表级别。
  • InnoDB 有更好的故障恢复机制。
  • InnoDB 实现了事务、外键和关系限制,MyISAM 没有。

总的来说,引用完整性和事物才是数据库的本质,所以说:“MyISAM is a file system that understands SQL. There’s no comparison. If you want a database engine with MySQL, use InnoDB.”

聚簇索引

MyISAM 没有使用聚簇索引,InnoDB 使用了聚簇索引。

四种隔离界别

  1. 读未提交 Read Uncommitted(在本次事务中可以读到其他事务中没有提交的数据 – 脏数据)
  2. 读已提交 Read Committed (只能读到其他事务提交过的数据。如果在当前事务中,其他事务有提交,则两次读取结果不同)
  3. 可重复读 Repeatable Read(MySQL 默认,保证了事务中每次读取结果都相同,而不管其他事物是否已经提交。会出现幻读)
  4. 序列化 Serializable(隔离级别中最严格的,开启一个 serializable 事务,那么其他事务对数据表的写操作都会被挂起)

  5. 读未提交:别人修改数据的事务尚未提交,在我的事务中也能读到。

  6. 读已提交:别人修改数据的事务已经提交,在我的事务中才能读到。
  7. 可重复读:别人修改数据的事务已经提交,在我的事务中也读不到。
  8. 串行:我的事务尚未提交,别人就别想改数据。

聚簇索引

InnoDB 使用聚簇索引,聚簇索引按照主键的顺序在磁盘上。MyISAM 不使用聚簇索引,行按照插入顺序在磁盘上。

聚簇索引的优势在于按照主键范围读取,而劣势在于主键中插入可能造成性能问题。

参考文献

  1. https://learnku.com/articles/13849/understanding-four-isolation-levels-in-mysql#2000d4
  2. https://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam
  3. https://jeremystein.com/journal/innodb-versus-myisam-no-comparison/

MySQL 性能小技巧和在 Django 中的应用

对于 delete update insert 等语句一定要使用 limit 子句限制影响的行数,避免一次更改特别多的行,造成数据库假死

while (1) {
    // 每次只做 1000 
    mysql_query("DELETE FROM logs WHERE log_date <= "2009-11-01" LIMIT 1000");
    if (mysql_affected_rows() == 0) {
        // 没得可删了退出
        break;
    }
    // 每次都要休息一会儿
    usleep(50000);
}

2. 垂直分割

把不会用作索引的,或者是过长的字段可以考虑使用其他存储引擎,比如 rocksdb 等。

3. IPv4 地址可以存为 uint32

使用 uint32 存储 IP 地址不光可以节省空间,而且可以按区间查询。

4. 避免 select *

从数据库里读出越多的数据,那么查询就会变得越慢。并且,如果你的数据库服务器和应用服务器是两台独立的服务器的话,这还会增加网络传输的负载。

所以,你应该养成一个需要什么就取什么的好的习惯。

不要使用:

SELECT * FROM user WHERE user_id = 1

使用:

SELECT username FROM user WHERE user_id = 1

在 django 中,可以使用 only

books = Book.objects.filter(author="Jim").only("book_name")

5. 当只要一行数据时使用 LIMIT 1

当你查询表的有些时候,你已经知道结果只会有一条结果,但因为你可能需要去 fetch 游标,或是你也许会去检查返回的记录数。

在这种情况下,加上 LIMIT 1 可以增加性能。这样一样,MySQL 数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。

下面的示例,只是为了找一下是否有“中国”的用户,很明显,后面的会比前面的更有效率。(请注意,第一条中是 Select *,第二条是 Select 1)

SELECT * FROM user WHERE country = "China"
SELECT 1 FROM user WHERE country = "China" LIMIT 1

在 Django 中可以使用 [:1] 来添加 limit 1

6. EXPLAIN 你的 SELECT 查询

使用 EXPLAIN 关键字可以让你知道 MySQL 是如何处理你的 SQL 语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。

7. 尽量让查询能 fit 进内存中

参考:

  1. https://coolshell.cn/articles/1846.html

在 Ubuntu 上安装 MySQL

安装

sudo apt -y install mysql-server

默认账号密码:

cat /etc/mysql/debian.cnf

或者直接通过 sudo mysql 就可以进去。这是因为默认情况下 root 用户是通过 sudo 来校验的,而不需要密码,我们可以改成通过密码校验的方式:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '';
FLUSH PRIVILEGES;

需要把 ubuntu 绑定地址改为 0.0.0.0。在 /etc/mysql/mysql.conf.d/mysqld.cnf 中注释掉 bind-address=127.0.0.1 行

开启 root 的远程登录:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password';

在 8.0 以上需要显示创建 root 用户,见参考资料。

参考

  1. https://stackoverflow.com/questions/11223235/mysql-root-access-from-all-hosts

mysql 基础知识 (7) – JSON 字段

在前公司的时候,大家习惯在每个表加一个 extra 字段来表示一些额外的字段,然后在 ORM 中使用的时候再解析出来,方便了扩展字段,但是缺点也很明显,extra 字段只能读取而无法进行查询。MySQL 5.7 终于支持了 json 字段,相当于加入了一些 NoSQL 的特性,这样就可以很方便得查询了。

json 字段的使用

建表:

CREATE TABLE `book` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `tags` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

直接使用 json 类型就可以了。注意 json 字段不可以作为主键,不可以作为外键,不过既然是 json 字段了,谁会这么做呢。

插入:

INSERT INTO `book` (`title`, `tags`)
VALUES (
  "ECMAScript 2015: A SitePoint Anthology",
  "["JavaScript", "ES2015", "JSON"]"
);

使用一个 json 字符串作为值插入即可。或者你也可以使用 json 相关的函数来表示 json。

json 相关函数

json path

-- returns "SitePoint":
SELECT JSON_EXTRACT(
  "{"id": 1, "website": "SitePoint"}",
  "$.website"
);

json path 的语法,用 $ 开头,然后跟着下面的选择器:

  • . 点后面跟着跟着一个字典里的名字,比如 $.website
  • [N] 表示数组里的第 N 个元素
  • .[*] 表示选择字典里的所有元素
  • [*] 表示选择数组里的所有元素
  • prefix**suffix 表示以 prefix 开头,suffix 结尾的所有路径

举个栗子

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}
the following paths:

$.a returns 1
$.c returns [3, 4]
$.c[1] returns 4
$.d.e returns 5
$**.e returns [5]

构造、修改 json 的函数

函数都比较简单,看注释就明白了。

-- returns [1, 2, "abc"]:
SELECT JSON_ARRAY(1, 2, "abc");

-- returns {"a": 1, "b": 2}:
SELECT JSON_OBJECT("a", 1, "b", 2);

-- returns ["a", 1, {"key": "value"}]:
SELECT JSON_MERGE("["a", 1]", "{"key": "value"}");

-- returns ARRAY:
SELECT JSON_TYPE("[1, 2, "abc"]");

-- returns OBJECT:
SELECT JSON_TYPE("{"a": 1, "b": 2}");

-- returns an error:
SELECT JSON_TYPE("{"a": 1, "b": 2");

-- returns 1:
SELECT JSON_VALID("[1, 2, "abc"]");

还有其他一些函数,可以查看文档:

  • JSON_SET(doc, path, val[, path, val]…) —
    inserts or updates data in the document
  • JSON_INSERT(doc, path, val[, path, val]…) —
    inserts data into the document
  • JSON_REPLACE(doc, path, val[, path, val]…) —
    replaces data in the document
  • JSON_MERGE(doc, doc[, doc]…) —
    merges two or more documents
  • JSONARRAYAPPEND(doc, path, val[, path, val]…) —
    appends values to the end of an array
  • JSONARRAYINSERT(doc, path, val[, path, val]…) —
    inserts an array within the document
  • JSON_REMOVE(doc, path[, path]…) —
    removes data from the document.

查询 json 函数

用于 where 子句中的函数

json_contains 用于选取数组中包含某个元素的行

-- all books with the "JavaScript" tag:
SELECT * FROM `book`
WHERE JSON_CONTAINS(tags, "["JavaScript"]");

json_search 用于选取字典中包含某个值的行

-- all books with tags starting "Java":
SELECT * FROM `book`
WHERE JSON_SEARCH(tags, "one", "Java%") IS NOT NULL;

用于 select 子句中的 json 函数

可以使用 json path 语法从得到的 json 文档中抽取出某个值。

select 语句

要想在 select 语句中使用 json path 抽取元素可以使用下面的语法,也就是 column->path

SELECT
  name,
  tags->"$[0]" AS `tag1`
FROM `book`;

一个更复杂一点的例子:

| id | name | profile |
| — | ——— | ———————————————————————————— |
| 1 | Craig | {“twitter”: “@craigbuckler”,“facebook”: “craigbuckler”,“googleplus”: “craigbuckler”} |
| 2 | SitePoint | {“twitter”: “@sitepointdotcom”} |

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;
SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

参考

  1. https://www.sitepoint.com/use-json-data-fields-mysql-databases/

mysql 基础知识 (6) – Join 和 Subquery

看到网上 [有篇文章][1] 用韦恩图来讲解了一下 SQL 的 join 操作,但是感觉举的例子似乎不太实际,遂自己写了一篇,图是从那篇文章里面盗的(逃

假设我们有下面两张表,上边的是表 user,下边的是 package,表示每个用户对应的包裹

| id | name |
| — | —— |
| 1 | Luke |
| 2 | Leia |
| 3 | Anakin |
| 4 | Padem |

| id | content | user_id |
| — | ———- | ——- |
| 1 | droid | 3 |
| 2 | lightsaber | 2 |
| 3 | blaster | 1 |
| 4 | R2D2 | 5 |

创建这两个表的语句分别是:

create table user (id integer, name string);
create table package (id integer, content string, user_id integer);
insert into user (id, name) values (1, "Luke");
insert into user (id, name) values (2, "Leia");
insert into user (id, name) values (3, "Anakin");
insert into user (id, name) values (4, "Padme");
insert into package (id, content, user_id) values (1, "droid", 3);
insert into package (id, content, user_id) values (2, "lightsaber", 2);
insert into package (id, content, user_id) values (3, "blaster", 1);
insert into package (id, content, user_id) values (4, "R2D2", 5);

Veen diagram(韦恩图)是一种表示集合的图形语言。SQL 的 join 本质上也是从集合论里面来的,可以从集合论的角度来学习和记忆 Join 的语法。

Inner Join

如果我们要选出每个有包裹的人,以及对应的包裹,可以使用 inner join。内连接(inner join)计算的是两个表的交集,也就是 A ∩ B

select
    user.id, user.name, package.id, package.content
from
    user inner join package
on user.id == package.user_id;

结果一共有 3 列,每个表中的第四列都因为在对方表中没有而没有出现在结果里。

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        3           blaster
2           Leia        2           lightsaber
3           Anakin      1           droid

如果需要使用过滤条件,那么可以使用 where 语句或者 on 中的语句,不过在 inner join 中,这两者几乎是等价的。

Left Outer Join

如果我们要取出每个人的包裹情况,没有包裹的也写上 null,那么这用情况下应该使用 left outer join。

select
    user.id, user.name, package.id, package.content
from
    user left outer join package
on user.id == package.user_id;

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        3           blaster
2           Leia        2           lightsaber
3           Anakin      1           droid
4           Padme       NULL        NULL

Where 和 on 的区别

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

我们还可以使用 left join 来过滤不包含某些对应关系的行。

Full Outer Join

如果我们想要选出所有的任何包裹的对应关系,哪怕是对应得人或者包裹不存在的话,可以使用 full outer join。全连接计算的是两个表的并集,也就是 A ∪ B

select
    user.id, user.name, package.id, package.content
from
    user full outer join package
on user.id == package.user_id;

结果一共有 6 列,注意其中缺字段的地方被补上了 null。另外 SQLite 不支持 full outer join。感觉这个 Join 似乎用的不是太多,因为实际情况中,往往 package.userid 是 user.id 的外键,所以不会出现 userid 不存在的情况。

// 结果省略

Cross Join

要获得 A 表和 B 表左右可能的交叉组合的话,可以使用 cross join,也就是笛卡尔乘积。

select
    user.id, user.name, package.id, package.content
from
    user cross join package;

结果如下

id          name        id          content
----------  ----------  ----------  ----------
1           Luke        1           droid
1           Luke        2           lightsaber
1           Luke        3           blaster
1           Luke        4           R2D2
2           Leia        1           droid
2           Leia        2           lightsaber
2           Leia        3           blaster
2           Leia        4           R2D2
3           Anakin      1           droid
3           Anakin      2           lightsaber
3           Anakin      3           blaster
3           Anakin      4           R2D2
4           Padme       1           droid
4           Padme       2           lightsaber
4           Padme       3           blaster
4           Padme       4           R2D2

Subquery

子查询是可以和 join 等价的一种查询方式。在以往(2010 年左右),子查询被认为是比较低效的查询方式,但是随着 SQL 引擎的进步,子查询应该在性能上问题不大了。

子查询其实很简单,就是一个查询的结果是另一个查询的数据基础。和函数调用有一些相似。

第一种常见用法,把子查询的结果用在 IN 中。

select * from students where id in (select sudent_id from student_awards);

第二种使用子查询来作为结果列的数值。

select id, (select count(*) from student_awards where students.id = student_awards.student_id) from students;

参考

  1. https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
  2. https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause
  3. https://stackoverflow.com/questions/25890534/mysql-select-rows-where-left-join-is-null
  4. https://stackoverflow.com/questions/2577174/join-vs-sub-query

mysql 基础知识 (5) – 聚合语句 (group by)

Group by 用来按照某一列或者某几列的值聚合数据。group by x 按照 x 相同的值聚合,group by x, y 按照 x 和 y 都相同的值聚合。而查询的列要么是聚合的列,要么应该通过聚合函数来选取一列。而且所有的 null 会被聚合成一行。

在 SQL 规范中,只能查询 group_by 的字段,如果查询其他字段,这种行为是未定义的。

比如说下面的数据表中

-- How many countries are in each continent?
select
  continent
  , count(*)
from
  countries
group by
  continent

执行查询可以得到每个洲的国家的数量。

过滤

在 SQL 中,Where 子句是在 group 子句之前运行的,所以我们无法通过 where 来过滤 group 之后的结果,而应该使用 having 子句来过滤。

select
 continent
  , max(area)
from
  countries
group by
  1
having
  max(area) >= 1e7

隐式聚合

当你没有使用 group by,而使用了 max、min、count 等聚合函数的时候已经在聚合了

-- What is the largest and average country size in Europe?
select
  max(area) as largest_country
  , avg(area) as avg_country_area
from
  countries
where
  continent = "Europe"

MySQL 的特殊处理

如果在查询中有没有聚合的列,那么 MySQL 就会随机选取一个列,比如下面就会随机选取一个州。总之,这个行为是未定义的,不要这么查询。

select
  country
  , state
  , count(*)
from
  countries
group by
  country

参考

  1. https://www.periscopedata.com/blog/everything-about-group-by

mysql 基础知识 (4) – 用户和权限

创建用户

CREATE USER "newuser"@"%" IDENTIFIED BY "password";

授权所有权限

GRANT ALL PRIVILEGES ON *.* TO "newuser"@"%";

其中的 % 代表这个用户可以在任意主机登录。

如果只需要在本机登录,使用 localhost

显示一个用户的当前授权:

SHOW GRANTS FOR newuser

更改用户密码:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';
flush privileges;

其他问题

EXPLAIN, slow-log

mysql 基础知识 (3) – 创建修改表和权限

重置密码

首先尝试能不能用 sudo mysql -u root 登录,因为系统的 root 用户默认是可以使用 mysql 的 root 用户免密码登录的。

如果不行,再尝试:

  1. sudo service mysql stop
  2. sudo mysqld_safe –skip-grant-tables –skip-networking
  3. mysql
    1. mysql> use mysql;
      ​ 2. mysql> update user set authentication_string=password(‘NEWPASSWORD’) where user=’root’;
      ​ 3. mysql> flush privileges;
    2. ​mysql> quit

创建数据库

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

ALTER DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

推荐使用 unicode 对应的编码模式:

create database blog DEFAULT CHARSET = utf8mb4 DEFAULT COLLATE = utf8mb4_unicode_ci;

查询当前 MySQL 中的表以及字段的信息

可以使用 information_schema 库中的表来查询。

SELECT column_name, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'xxx';
select table_name from information_schema.tables where table_schema = "xxx";

创建表

请注意,一定要是用 utf8mb4 对应的编码模式:

CREATE TABLE table_name (
    field_name type is_null default options,
    ...
    PRIMAR KEY (id),
    INDEX/KEY index_name (field_name),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;
 
// 注意:KEY is normally a synonym for INDEX

设定 auto_increment

注意 mysql 的关键字是 auto_increment, 而 sqlite 的是 autoincrement

CREATE TABLE(...) AUTO_INCREMENT=xxx;

更改已经存在的表

ALTER TABLE SET AUTO_INCREMENT=xxx;

 

数据类型

字符串

字符串分两种,定长和变长,MySQL 处理定长数据比变长数据快得多。CHAR 属于定长类型,VARCHAR 和 TEXT 属于变长类型。

  • CHAR 的长度为 1-255,默认为 1,使用 CHAR(n) 指定长度
  • VARCHAR 为 0-255,使用 VARCHAR(n) 指定长度
  • TEXT 为 65536,MEDIUMTEXT 为 16k,LONGTEXT 为 4GB

数字

注意数字后面跟的数字,例如 INT(5),并不是限制数字的存储长度,而是限制数字的展示长度(显示时填充 0)!可以使用 UNSIGNED 指定为非负值,默认为 signed

日期

使用 DATETIME,不要使用 TIMESTAMP,防止 2038 年溢出

tips

创建 modifytime/updatetime 字段时使用自动更新时间

modify_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

设计表要注意每个字段的正交性,不要出现一个字段表示“xx 且 xx”的状态。

 

更新表

rename table "old_name" to "new_name"

添加一列,其中的 COLUMN 关键字是 optional 的。

ALTER TABLE table_name ADD COLUMN field_name type;

更新某个字段的数据类型

Alter TABLE &#x60;tableName&#x60; MODIFY COLUMN &#x60;ColumnName&#x60; datatype(length);

比如说:

Alter TABLE &#x60;tbl_users&#x60; MODIFY COLUMN &#x60;dup&#x60; VARCHAR(120);

重命名一列

需要注意的是数据类型也需要带上

alter table user change name first_name varchar(128) default null;

添加不同类型的索引

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE index_name (column_list)

ALTER TABLE table_name ADD PRIMARY KEY index_name (column_list)

需要注意的是 mysql 索引的最大长度是 255,也就是在 VARCHAR(255) 以上的列是不能添加索引的,一个改进方法就是另外添加一列存储这一列的 hash 值。

删除字段

删除索引

alter table TABLENAME drop index xxxx

—————— 之前笔记的分割线 ————————
 

组合索引

 
如果有一个组合索引 (cola,colb,col_c)
 
下面的情况都会用到这个索引:

col_a = "some value";
col_a = "some value" and col_b = "some value";
col_a = "some value" and col_b = "some value" and col_c = "some value";
col_b = "some value" and col_a = "some value" and col_c = "some value";

对于最后一条语句,mysql 会自动优化成第三条的样子
 
下面的情况就不会用到索引:

col_b = "aaaaaa";
col_b = "aaaa" and col_c = "cccccc";

参考

  1. https://www.mysqltutorial.org/mysql-views/mysql-show-view/
  2. https://www.techrepublic.com/article/how-to-set-change-and-recover-a-mysql-root-password/

mysql 基础知识 (2) – 增删改查

我们还是使用上篇文章定义的例子来说明问题

插入数据

使用 INSERT 语句。

insert into students(**field_names) values(**VALUES), values(**VALUES);

批量插入

如果一次要插入所有数据的话,可以直接省略前面的字段名。MySQL 可以一次插入多行数据或者一行数据,但是这并不是 SQL 标准规定的。使用批量插入可以大幅度提高性能。

在批量插入的语句中,如果有一行是错的,那么就会导致整个插入失败,可以使用 insert ignore 语句。

如果要在批量插入不同的表,可以一次执行多个语句,而不是只执行一个。

如果在有 unique 索引的表中,如果插入重复数据可能会引起错误,这时候有两个解决方法:

  1. insert ignore, 忽略插入的新数据
  2. insert on duplicate update, 遇到重复数据则更新。

insert ignore 的语法是:

INSERT IGNORE INTO table(column_list)
VALUES( value_list),
      ( value_list),
      ...

也就是说和普通的插入语句相比就是多了 ignore 关键字,除此之外,再无区别。如果数据中有重复的,有不重复的,那么不重复的会插入成功。

但是 insert ignore 的问题在于它会忽略所有的错误,比如说不能为 null 的值提供了 null.

insert on duplicate update 的语法是:

INSERT INTO table (column_list)
VALUES (value_list)
ON DUPLICATE KEY UPDATE
   c1 = v1, 
   c2 = v2,
   ...;

insert on duplicate update 的问题在于它会消耗掉一个 id, 虽然并没有实际插入,导致 id 不是连续的,而是有好多空洞。而且会执行实际的写操作。另外在语法上也非常傻屌,竟然需要把插入的值在后面在写一遍。

更新数据

使用 UPDATE 语句。

update students set math_score = 100 where first_name = "luke";

删除数据

使用 DELETE 语句。

delete from students where name = "luke";

参考

  1. http://www.mysqltutorial.org/mysql-insert-ignore/
  2. https://www.mysqltutorial.org/mysql-insert-or-update-on-duplicate-key-update/
  3. https://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update

mysql 基础知识 (1) – 查询数据

显示操作

show databases;
show create database DB;
show tables;
show columns from TABLE;
show create table TABLE;
show status;
shou grants;

连接数据库

mysql -u root -p -h HOST -P 3306, 其中的参数代表了用户,机器地址和密码等,注意其中的密码必须之后输入,而不能直接带上,这样是为了避免把密码记录在 bash 历史中。
另一个更好的工具是 Python 写的 mycli, 谁用谁知道~

使用 use DATABASE来更改指定的数据库。

查询数据库

下面的部分都使用这个表,方便讨论。
| id | firstname | lastname | mathscore | englishscore | grade |
| — | ———- | ——— | ———- | ————- | —– |
| 1 | master | yoda | 100 | 100 | 9 |
| 2 | obiwan | kenobi | 70 | 100 | 8 |
| 3 | luke | skywalker | 100 | 70 | 7 |
| 4 | leia | skywalker | 90 | 90 | 7 |

select field from table, 或者 select * from table. 好多时候往往犯懒直接写星号,但是检索无关的列会降低数据库的效率。
有一个比较有趣的关键字,叫做DISTINCT, 顾名思义,就是不同的意思,如果使用这个关键字作为前缀的话,MYSQL 会做一个去重的操作,有点类似于uniq命令。

select distinct vendor_id from products

如果需要限定查询的结果,使用 LIMIT 和 OFFSET 两个关键字。LIMIT 限定了返回的结果数量,OFFSET 指定了从那一条开始返回。

select name from students limit 5 offset 3;  -- 从所有学生中选出从第 3 个开始的 5 个学生

小技巧

如果确定数据库只应该返回一个结果,那么一定要加上 limit 1,这样有助于性能提升。

select * from users where id=1 limit 1;

排序

一般来说,mysql 返回的结果是按照主键排序的,如果要让结果按照某个键排序,使用 ORDER BY 关键字来给他们排序

select name from students order by score

默认情况下,mysql 是按照升序排列的,也就是小的在前面,如果需要大的在前面,使用 DESC 关键字

select name from students order by score desc;

如果排序的字段相同怎么办呢,还可以按多个字段排序,这时候需要注意的是 desc 只对一个列生效。

select name from students order by score desc, name;

过滤结果

可以使用 WHERE 子句过滤结果,比如

select * from students where score > 100;

值得注意的是,如果要比较的是 null, 那么不能使用 = 来比较,需要使用 IS

select * from students where name is null;

还可以使用 AND, OR, NOT 来计算复合表达式。
使用 IN 来表达在某些值之间,就像在 Python 中一样。

select * from students where name in ("yoda", "obiwan", "luke");

使用 LIKE 来匹配结果,% 表示任意字符出现任意次数,_ 表示任意一个字符出现一次。

select * from students where name like "%walker";

函数与计算字段

MySQL 中有一些常见的字符串处理函数,concat, trim 等等,这些函数在常见的编程语言中都有,在 MySQL 中作用大概也是相同的。还可以使用 AS 来个计算出的字段来起一个别名,这样方便输出。如果是数字的话,还可以做数学运算。

select concat(first_name, last_name) as name from students;

select math_score + english_score as score from students;

select * from students where year(enroll_time) = 2009;

聚合与分组数据

之前说的一些函数都是把一个值变成了另一个值,也就是使用或者不使用这些函数得到的都是相同行数的数据。而使用聚合或者分组函数之后,会改变得到的数据的大小。
聚集函数常用的一共有五个:AVG(), COUNT(), MAX(), MIN(), SUM(). 这几个函数的意思看名字也应该知道了。.. 直接上例子了。

select avg(math_score) as average_math_score from students;  -- 90, null 会被忽略
select count(*) from students; -- 3, null 会被忽略

分组函数

使用 group by 关键字可以让所有的行按照某个值,聚合成相应的行,比如在我们的数据中有 3 个年纪 (grade), 那么 group by(grade) 之后就会变成 3 行。

select grade, count(*) as num_students from students group by grade;

需要注意的是 SQL 的计算顺序是先计算 WHERE 子句,然后才回去使用 GROUP BY 聚合,那么如果想要过滤 GROUP BY 分组之后的数据呢,这时候可以使用 HAVING 子句。

select grade, count(*) as num_students from students group by grade having count(*) > 1;

数据类型

TINYBLOB, TINYTEXT       L + 1 bytes, where L < 2^8    (255 Bytes)
BLOB, TEXT               L + 2 bytes, where L < 2^16   (64 Kibibytes)
MEDIUMBLOB, MEDIUMTEXT   L + 3 bytes, where L < 2^24   (16 Mebibytes)
LONGBLOB, LONGTEXT       L + 4 bytes, where L < 2^32   (4 Gibibytes)

参考

  1. 史上最全的 mysql 总结