我的MySQL速查手册

发表于 2020-12-27 更新于 2024-07-31 分类于技术

查看已存在的数据库

MariaDB [carltest]> show databases;
+-------------------------+
| Database                |
+-------------------------+
| carltest                |
| test                    |
| test2                   |
| test3                   |
| test_insert             |
+-------------------------+
5 rows in set (0.00 sec)

MariaDB [carltest]> show databases like 'carl%';
+------------------+
| Database (carl%) |
+------------------+
| carltest         |
+------------------+
1 row in set (0.00 sec)

查询默认存储引擎

MariaDB [(none)]> show variables like 'storage_engine';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| storage_engine | InnoDB |
+----------------+--------+
1 row in set (0.00 sec)

MariaDB [(none)]>

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_specification] ...

create_specification:
    [DEFAULT] CHARACTER SET [=] charset_name
  | [DEFAULT] COLLATE [=] collation_name

# 创建一个utf8编码的database
MariaDB [(none)]> create database carltest
    -> default character set utf8
    -> default collate utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases like 'carl%';
+------------------+
| Database (carl%) |
+------------------+
| carltest         |
+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]>

删除数据库

MariaDB [(none)]> show databases like 'carl%';
+------------------+
| Database (carl%) |
+------------------+
| carltest         |
+------------------+
1 row in set (0.00 sec)

MariaDB [(none)]> drop database carltest;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show databases like 'carl%';
Empty set (0.00 sec)

MariaDB [(none)]>

设置表的主键

# 单字段主键, 可以直接指定, 如下示例
> create table if not exists test1(id int primary key,
>                                  name varchar(20),
>                                  sex boolean
>                                  );

# 多字段主键
> create table if not exists test1(stu_id int,
>                                  couse_id int,
>                                  grade float,
>                                  primary key(stu_id, course_id),
>                                  );

创建索引

MariaDB [carltest]> create table if not exists user(userid int(10) unique primary key auto_increment,
    -> username varchar(20) not null,
    -> passwd varchar(20) not null,
    -> info text,
    -> unique index index_uid(userid desc),
    -> index index_user(username, passwd),
    -> fulltext index index_info(info)
    -> ) engine=myisam;

Query OK, 0 rows affected (0.17 sec)

MariaDB [carltest]> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `userid` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `passwd` varchar(20) NOT NULL,
  `info` text,
  PRIMARY KEY (`userid`),
  UNIQUE KEY `index_uid` (`userid`),
  KEY `index_user` (`username`,`passwd`),
  FULLTEXT KEY `index_info` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 创建单列索引
MariaDB [carltest]> create index index_name on information(name(10));
Query OK, 0 rows affected (0.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 创建多列索引
MariaDB [carltest]> create index index_bir on information(birthday, address);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 使用alter语句创建索引
MariaDB [carltest]> alter table information add index index_id(id asc);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [carltest]> show create table information \G;
*************************** 1. row ***************************
       Table: information
Create Table: CREATE TABLE `information` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `sex` varchar(4) NOT NULL,
  `birthday` date DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  `tel` varchar(20) DEFAULT NULL,
  `pic` blob,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`(10)),
  KEY `index_bir` (`birthday`,`address`),
  KEY `index_id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 删除索引
MariaDB [carltest]> drop index index_user on user;
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [carltest]> show create table user \G
*************************** 1. row ***************************
       Table: user
Create Table: CREATE TABLE `user` (
  `userid` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,
  `passwd` varchar(20) NOT NULL,
  `info` text,
  PRIMARY KEY (`userid`),
  UNIQUE KEY `index_uid` (`userid`),
  FULLTEXT KEY `index_info` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

MariaDB [carltest]>

查询

基本查询语句

SELECT 属性列表
       FROM 表名
       [WHERE XXXX]
       [GROUP BY 属性名1, 属性名2 [, 属性名i ...] [HAVING 条件表达式]]
       [ORDER BY 属性名3 [ASC | DESC]]
       [LIMIT 初始位置, 记录数]

先准备点数据, 要不然下面的戏没法唱了.

MariaDB [carltest]> select * from score;
+----+--------+-----------+-------+
| id | stu_id | c_name    | grade |
+----+--------+-----------+-------+
|  1 |    901 | 计算机    |    98 |
|  2 |    901 | 英语      |    80 |
|  3 |    902 | 计算机    |    65 |
|  4 |    902 | 中文      |    88 |
|  5 |    903 | 中文      |    95 |
|  6 |    904 | 计算机    |    70 |
|  7 |    904 | 英语      |    92 |
|  8 |    905 | 英语      |    94 |
|  9 |    906 | 计算机    |    90 |
| 10 |    906 | 英语      |    85 |
+----+--------+-----------+-------+
10 rows in set (0.00 sec)

MariaDB [carltest]> select * from student;
+-----+-----------+------+-------+--------------+--------------------+
| id  | name      | sex  | birth | department   | address            |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
| 902 | 张老二    | 男   |  1986 | 中文系       | 北京市昌平区       |
| 903 | 张三      | 女   |  1990 | 中文系       | 湖南省永州市       |
| 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |
| 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |
| 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
+-----+-----------+------+-------+--------------+--------------------+
6 rows in set (0.00 sec)

LIMIT限定

MariaDB [carltest]> SELECT * FROM student LIMIT 1, 3;
+-----+-----------+------+-------+------------+--------------------+
| id  | name      | sex  | birth | department | address            |
+-----+-----------+------+-------+------------+--------------------+
| 902 | 张老二    | 男   |  1986 | 中文系     | 北京市昌平区       |
| 903 | 张三      | 女   |  1990 | 中文系     | 湖南省永州市       |
| 904 | 李四      | 男   |  1990 | 英语系     | 辽宁省阜新市       |
+-----+-----------+------+-------+------------+--------------------+
3 rows in set (0.00 sec)

IN关键字

MariaDB [carltest]> SELECT * FROM student WHERE department IN ('计算机系', '英语系');
+-----+-----------+------+-------+--------------+--------------------+
| id  | name      | sex  | birth | department   | address            |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
| 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |
| 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |
| 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
+-----+-----------+------+-------+--------------+--------------------+
4 rows in set (0.00 sec)

GROUP BY关键字

GROUP BY关键字通常与集合函数一起使用. 集合函数包括COUNT(), SUM(), AVG(), MAX()和MIN()等.

如果GROUP BY不与上述函数一起使用, 那么查询结果就是字段取值的分组情况. 字段中取值相同的记录为一组,
但只显示该组的第一条记录

MariaDB [carltest]> SELECT department, COUNT(id) as sum_of_department FROM student GROUP BY department;
+--------------+-------------------+
| department   | sum_of_department |
+--------------+-------------------+
| 中文系       |                 2 |
| 英语系       |                 2 |
| 计算机系     |                 2 |
+--------------+-------------------+
3 rows in set (0.00 sec)

# 首先按照c_name字段对score表中的记录进行分组. 然后使用MAX()函数计算每组的最大值
MariaDB [carltest]> SELECT c_name, MAX(grade) FROM score GROUP BY c_name;
+-----------+------------+
| c_name    | MAX(grade) |
+-----------+------------+
| 中文      |         95 |
| 英语      |         94 |
| 计算机    |         98 |
+-----------+------------+
3 rows in set (0.01 sec)

GROUP BY 后面多个属性的时候, 先按照属性1分组, 如果属性1相同, 再按照属性2分组

嵌套查询

MariaDB [carltest]> SELECT c_name, grade FROM score WHERE stu_id=(SELECT id FROM student WHERE name='李四');
+-----------+-------+
| c_name    | grade |
+-----------+-------+
| 计算机    |    70 |
| 英语      |    92 |
+-----------+-------+
2 rows in set (0.03 sec)

UNION, UNION ALL用法

使用UNION关键字时, 数据库系统会将所有的查询结果合并到一起, 然后去掉相同的记录. 而UNION ALL关键字则只是简单的合并到一起.

MariaDB [carltest]> SELECT id FROM student UNION SELECT stu_id FROM score;
+-----+
| id  |
+-----+
| 901 |
| 902 |
| 903 |
| 904 |
| 905 |
| 906 |
+-----+
6 rows in set (0.00 sec)

MariaDB [carltest]> SELECT id FROM student UNION ALL SELECT stu_id FROM score;
+-----+
| id  |
+-----+
| 901 |
| 902 |
| 903 |
| 904 |
| 905 |
| 906 |
| 901 |
| 901 |
| 902 |
| 902 |
| 903 |
| 904 |
| 904 |
| 905 |
| 906 |
| 906 |
+-----+
16 rows in set (0.00 sec)

ANY关键字

MariaDB [carltest]> SELECT * FROM student WHERE id=ANY (SELECT stu_id FROM score WHERE stu_id in (SELECT stu_id FROM score WHERE c_name='计算机') AND c_name='英语');
+-----+-----------+------+-------+--------------+--------------------+
| id  | name      | sex  | birth | department   | address            |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
| 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |
| 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
+-----+-----------+------+-------+--------------+--------------------+
3 rows in set (0.04 sec)

正则表达式匹配查询


SELECT * FROM table_name where xx REGEXP '匹配方式';

模式字符      含义

^             匹配字符串开始的部分
$             匹配字符串结束的部分
.             代表字符串中的任意一个字符, 包括回车和换行
[字符集合]    匹配"字符集合"中的任何一个字符
[^字符集合]   匹配除了"字符集合"以外的任何一个字符
S1|S2|S3      匹配S1, S2和S3中的任意一个字符串
*             代表多个该符号之前的字符, 包括0和1个
+             代表多个该符号之前的字符, 包括1个
字符串{N}     字符串出现N次
字符串{M,N}   字符串出现至少M次, 最多N次

MariaDB [carltest]> SELECT student.id name, sex, birth, department address, c_name, grade FROM student, score WHERE (name LIKE '张%' OR name LIKE '王%') AND student.id=score.stu_id;
+------+------+-------+--------------+-----------+-------+
| name | sex  | birth | address      | c_name    | grade |
+------+------+-------+--------------+-----------+-------+
|  901 | 男   |  1985 | 计算机系     | 计算机    |    98 |
|  901 | 男   |  1985 | 计算机系     | 英语      |    80 |
|  902 | 男   |  1986 | 中文系       | 计算机    |    65 |
|  902 | 男   |  1986 | 中文系       | 中文      |    88 |
|  903 | 女   |  1990 | 中文系       | 中文      |    95 |
|  905 | 女   |  1991 | 英语系       | 英语      |    94 |
|  906 | 男   |  1988 | 计算机系     | 计算机    |    90 |
|  906 | 男   |  1988 | 计算机系     | 英语      |    85 |
+------+------+-------+--------------+-----------+-------+
8 rows in set (0.00 sec)

连接查询


# 先从student表中删除id为902和903两行, 方便下面区分
MariaDB [carltest]> select * from student;
+-----+-----------+------+-------+--------------+--------------------+
| id  | name      | sex  | birth | department   | address            |
+-----+-----------+------+-------+--------------+--------------------+
| 901 | 张老大    | 男   |  1985 | 计算机系     | 北京市海淀区       |
| 904 | 李四      | 男   |  1990 | 英语系       | 辽宁省阜新市       |
| 905 | 王五      | 女   |  1991 | 英语系       | 福建省厦门市       |
| 906 | 王六      | 男   |  1988 | 计算机系     | 湖南省衡阳市       |
+-----+-----------+------+-------+--------------+--------------------+
4 rows in set (0.00 sec)

MariaDB [carltest]> select * from score;
+----+--------+-----------+-------+
| id | stu_id | c_name    | grade |
+----+--------+-----------+-------+
|  1 |    901 | 计算机    |    98 |
|  2 |    901 | 英语      |    80 |
|  3 |    902 | 计算机    |    65 |
|  4 |    902 | 中文      |    88 |
|  5 |    903 | 中文      |    95 |
|  6 |    904 | 计算机    |    70 |
|  7 |    904 | 英语      |    92 |
|  8 |    905 | 英语      |    94 |
|  9 |    906 | 计算机    |    90 |
| 10 |    906 | 英语      |    85 |
+----+--------+-----------+-------+
10 rows in set (0.00 sec)

# 内连接查询
MariaDB [carltest]> SELECT student.id name, sex, birth, department, address, c_name grade FROM student, score WHERE student.id=score.stu_id;
+------+------+-------+--------------+--------------------+-----------+
| name | sex  | birth | department   | address            | grade     |
+------+------+-------+--------------+--------------------+-----------+
|  901 | 男   |  1985 | 计算机系     | 北京市海淀区       | 计算机    |
|  901 | 男   |  1985 | 计算机系     | 北京市海淀区       | 英语      |
|  904 | 男   |  1990 | 英语系       | 辽宁省阜新市       | 计算机    |
|  904 | 男   |  1990 | 英语系       | 辽宁省阜新市       | 英语      |
|  905 | 女   |  1991 | 英语系       | 福建省厦门市       | 英语      |
|  906 | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 计算机    |
|  906 | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 英语      |
+------+------+-------+--------------+--------------------+-----------+
7 rows in set (0.00 sec)

# 给student和score起个别名
MariaDB [carltest]> SELECT s1.id name, sex, birth, department, address, c_name grade FROM student s1, score s2 WHERE s1.id=s2.stu_id;
+------+------+-------+--------------+--------------------+-----------+
| name | sex  | birth | department   | address            | grade     |
+------+------+-------+--------------+--------------------+-----------+
|  901 | 男   |  1985 | 计算机系     | 北京市海淀区       | 计算机    |
|  901 | 男   |  1985 | 计算机系     | 北京市海淀区       | 英语      |
|  904 | 男   |  1990 | 英语系       | 辽宁省阜新市       | 计算机    |
|  904 | 男   |  1990 | 英语系       | 辽宁省阜新市       | 英语      |
|  905 | 女   |  1991 | 英语系       | 福建省厦门市       | 英语      |
|  906 | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 计算机    |
|  906 | 男   |  1988 | 计算机系     | 湖南省衡阳市       | 英语      |
+------+------+-------+--------------+--------------------+-----------+
7 rows in set (0.00 sec)

# 外连接查询

# LEFT JOIN, 返回student所有的数据
MariaDB [carltest]> SELECT student.id, c_name FROM student LEFT JOIN  score on student.id=score.stu_id;
+-----+-----------+
| id  | c_name    |
+-----+-----------+
| 901 | 计算机    |
| 901 | 英语      |
| 904 | 计算机    |
| 904 | 英语      |
| 905 | 英语      |
| 906 | 计算机    |
| 906 | 英语      |
+-----+-----------+
7 rows in set (0.00 sec)

# RIGHT JOIN, 返回score所有的数据
MariaDB [carltest]> SELECT student.id, c_name FROM student RIGHT JOIN  score on student.id=score.stu_id;
+------+-----------+
| id   | c_name    |
+------+-----------+
|  901 | 计算机    |
|  901 | 英语      |
| NULL | 计算机    |
| NULL | 中文      |
| NULL | 中文      |
|  904 | 计算机    |
|  904 | 英语      |
|  905 | 英语      |
|  906 | 计算机    |
|  906 | 英语      |
+------+-----------+
10 rows in set (0.00 sec)

插入, 更新, 删除数据

先建一张表, 接下来用来操作

MariaDB [carltest]> CREATE TABLE food (id INT(10) NOT NULL UNIQUE PRIMARY KEY AUTO_INCREMENT,
    ->                    name VARCHAR(20) NOT NULL,
    ->                    company VARCHAR(30) NOT NULL,
    ->                    price FLOAT,
    ->                    produce_time YEAR,
    ->                    validity_time INT(4),
    ->                    address VARCHAR(50));
Query OK, 0 rows affected (0.31 sec)

MariaDB [carltest]> desc food;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int(10)     | NO   | PRI | NULL    | auto_increment |
| name          | varchar(20) | NO   |     | NULL    |                |
| company       | varchar(30) | NO   |     | NULL    |                |
| price         | float       | YES  |     | NULL    |                |
| produce_time  | year(4)     | YES  |     | NULL    |                |
| validity_time | int(4)      | YES  |     | NULL    |                |
| address       | varchar(50) | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
7 rows in set (0.09 sec)

插入数据

INSERT语句中不指定具体的字段名

# INSERT INTO tbl VALUES (值1, 值2, ..., 值n)

MariaDB [carltest]> INSERT INTO food VALUES (1, 'AA饼干', 'AA饼干厂', 2.5, '2008', 3, '北京');
Query OK, 1 row affected (0.05 sec)

MariaDB [carltest]> select * from food;
+----+----------+-------------+-------+--------------+---------------+---------+
| id | name     | company     | price | produce_time | validity_time | address |
+----+----------+-------------+-------+--------------+---------------+---------+
|  1 | AA饼干   | AA饼干厂    |   2.5 |         2008 |             3 | 北京    |
+----+----------+-------------+-------+--------------+---------------+---------+
1 row in set (0.00 sec)

插入指定列

# INSERT INTO tbl (属性1, 属性2, ..., 属性m)
#         VALUES (值1, 值2, ..., 值m)

MariaDB [carltest]> INSERT INTO food (id, name, company, price, produce_time, validity_time, address) VALUES (2, 'CC牛奶', 'CC牛奶厂', 3.5, '2009', 1, '河北');
Query OK, 1 row affected (0.00 sec)

MariaDB [carltest]> select * from food;
+----+----------+-------------+-------+--------------+---------------+---------+
| id | name     | company     | price | produce_time | validity_time | address |
+----+----------+-------------+-------+--------------+---------------+---------+
|  1 | AA饼干   | AA饼干厂    |   2.5 |         2008 |             3 | 北京    |
|  2 | CC牛奶   | CC牛奶厂    |   3.5 |         2009 |             1 | 河北    |
+----+----------+-------------+-------+--------------+---------------+---------+
2 rows in set (0.00 sec)

同时插入多行数据

# INSERT INTO tbl [(属性列表)]
#         VALUES (取值列表1), (取值列表2), ..., (取值列表n);

INSERT INTO food VALUES (NULL, 'EE果冻', 'EE果冻厂', 1.5, '2007', 2, '北京'),
                        (NULL, 'FF咖啡', 'FF咖啡厂', 20, '2002', 5, '天津'),
                        (NULL, 'GG奶糖', 'GG奶糖厂', 14, '2003', 3, '广东');

MariaDB [carltest]> INSERT INTO food VALUES (NULL, 'EE果冻', 'EE果冻厂', 1.5, '2007', 2, '北京'),
    ->                         (NULL, 'FF咖啡', 'FF咖啡厂', 20, '2002', 5, '天津'),
    ->                         (NULL, 'GG奶糖', 'GG奶糖厂', 14, '2003', 3, '广东');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [carltest]> select * from food;
+----+----------+-------------+-------+--------------+---------------+---------+
| id | name     | company     | price | produce_time | validity_time | address |
+----+----------+-------------+-------+--------------+---------------+---------+
|  1 | AA饼干   | AA饼干厂    |   2.5 |         2008 |             3 | 北京    |
|  2 | CC牛奶   | CC牛奶厂    |   3.5 |         2009 |             1 | 河北    |
|  3 | EE果冻   | EE果冻厂    |   1.5 |         2007 |             2 | 北京    |
|  4 | FF咖啡   | FF咖啡厂    |    20 |         2002 |             5 | 天津    |
|  5 | GG奶糖   | GG奶糖厂    |    14 |         2003 |             3 | 广东    |
+----+----------+-------------+-------+--------------+---------------+---------+
5 rows in set (0.00 sec)

将查询结果插入到表中

# INSERT INTO tbl1 (属性列表1)
#        SELECT 属性列表2 FROM tbl2 WHERE 条件表达式;

MariaDB [carltest]> desc food1;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| id            | int(10)     | NO   | PRI | NULL    | auto_increment |
| name          | varchar(20) | NO   |     | NULL    |                |
| company       | varchar(30) | NO   |     | NULL    |                |
| price         | float       | YES  |     | NULL    |                |
| produce_time  | year(4)     | YES  |     | NULL    |                |
| validity_time | int(4)      | YES  |     | NULL    |                |
| address       | varchar(50) | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
7 rows in set (0.12 sec)

MariaDB [carltest]> INSERT INTO food1 SELECT * FROM  food;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

MariaDB [carltest]> select * from food1;
+----+----------+-------------+-------+--------------+---------------+---------+
| id | name     | company     | price | produce_time | validity_time | address |
+----+----------+-------------+-------+--------------+---------------+---------+
|  1 | AA饼干   | AA饼干厂    |   2.5 |         2008 |             3 | 北京    |
|  2 | CC牛奶   | CC牛奶厂    |   3.5 |         2009 |             1 | 河北    |
|  3 | EE果冻   | EE果冻厂    |   1.5 |         2007 |             2 | 北京    |
|  4 | FF咖啡   | FF咖啡厂    |    20 |         2002 |             5 | 天津    |
|  5 | GG奶糖   | GG奶糖厂    |    14 |         2003 |             3 | 广东    |
+----+----------+-------------+-------+--------------+---------------+---------+
5 rows in set (0.00 sec)

更新数据

# UPDATE tbl SET 属性1=值1, 属性2=值2, ..., 属性n=值n WHERE 条件表达式

MariaDB [carltest]> select * from food where name='CC牛奶';
+----+----------+-------------+-------+--------------+---------------+---------+
| id | name     | company     | price | produce_time | validity_time | address |
+----+----------+-------------+-------+--------------+---------------+---------+
|  2 | CC牛奶   | CC牛奶厂    |   3.5 |         2009 |             1 | 河北    |
+----+----------+-------------+-------+--------------+---------------+---------+
1 row in set (0.00 sec)

MariaDB [carltest]> update food set address='内蒙古', price=3.2 where name='CC牛奶';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [carltest]> select * from food where name='CC牛奶';
+----+----------+-------------+-------+--------------+---------------+-----------+
| id | name     | company     | price | produce_time | validity_time | address   |
+----+----------+-------------+-------+--------------+---------------+-----------+
|  2 | CC牛奶   | CC牛奶厂    |   3.2 |         2009 |             1 | 内蒙古    |
+----+----------+-------------+-------+--------------+---------------+-----------+
1 row in set (0.00 sec)

删除数据

# DELETE FROM tbl [WHERE 条件表达式]

MariaDB [carltest]> select * from food where address='北京';
+----+----------+-------------+-------+--------------+---------------+---------+
| id | name     | company     | price | produce_time | validity_time | address |
+----+----------+-------------+-------+--------------+---------------+---------+
|  1 | AA饼干   | AA饼干厂    |   2.5 |         2008 |             3 | 北京    |
|  3 | EE果冻   | EE果冻厂    |   1.5 |         2007 |             2 | 北京    |
+----+----------+-------------+-------+--------------+---------------+---------+
2 rows in set (0.00 sec)

MariaDB [carltest]> delete from food where address='北京';
Query OK, 2 rows affected (0.00 sec)

MariaDB [carltest]> select * from food where address='北京';
Empty set (0.00 sec)

表结构修改

修改表名

1	alter table t_book rename to bbb;

添加列

1	alter table 表名 add column 列名 varchar(30);

修改列

1
2
3

alter table 表名 modify column 字段名 新数据类型 新类型长度 新默认值 新注释;

alter table 表名 change 旧字段名 新字段名 新数据类型;

删除列

1	alter table 表名 drop column 列名;

修改unique key

1 2	alter table table_name drop index `uk_name`; alter table table_name add unique key `new_uk_name` (`col1`,`col2`);

权限管理

MySQL中权限分配是按照user表, db表, tables_priv表和columns_priv表的顺序进行分配的. 数据库系统中, 先判断user表中的值为Y.
如果user表中的值为Y, 就不需要检查后面的表. 如果user表的为N, 则依次检查db表, tables_priv表和columns_priv表

mysql.user表

权限列中有很多权限字段需要特别注意:

字段名	说明
Grant_priv	表示是否拥有GRANT权限
Shutdown_priv	表示是否拥有停止MySQL的权限
Super_priv	表示是否拥有超级权限
Execute_priv	表示是否有EXECUTE权限, 该权限可以执行存储过程和函数

user表的资源控制列

字段名	说明
max_questions	每小时可以允许执行多少次查询
max_updates	每小时可以允许执行多少次更新
max_connections	每小时可以建立多少连接
max_user_connections	每个用户可以同时具有的连接数.

以上每个字段默认值为0, 表示没有限制

用户账号相关

新建普通用户

# CREATE USER user [IDENTIFIED BY [PASSWORD] 'passwd'] [, user [IDENTIFIED BY [PASSWORD] 'passwd']] ...
# GRANT priv_type ON databse.table TO user [IDENTIFIED BY [PASSWORD] 'passwd'] [, user [IDENTIFIED BY [PASSWORD] 'passwd']] ...

MariaDB [mysql]> create user 'test1'@'localhost' identified by 'test1_password';
Query OK, 0 rows affected (0.27 sec)

MariaDB [mysql]> GRANT SELECT ON carltest.* TO 'test3'@'localhost' IDENTIFIED BY 'test3';
Query OK, 0 rows affected (0.05 sec)

删除普通用户

# DROP USER user [, user] ...
# DELETE FROM mysql.user WHERE Host='localhost' AND User='username';

MariaDB [mysql]> DROP USER 'test1'@'localhost';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> DELETE FROM mysql.user WHERE Host='localhost' AND User='test3';
Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.11 sec)

root用户修改root用户的密码

1
2
3

# mysqladmin -u $username -p password "new_password";
# update mysql.user set Password=PASSWORD('new_password') where User='root' and Host='localhost';
# SET PASSWORD=PASSWORD('new_password');

root用户修改普通用户密码

# SET PASSWORD FOR 'username'@'hostname'=PASSWORD('new_password');
# UPDATE mysql.user SET Password=PASSWORD('mytest2') WHERE User='test3' AND Host='localhost';
# GRANT priv_type ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password'];
> GRANT SELECT ON *.* TO 'test3'@'localhost' IDENTIFIED BY 'mytest3';

普通用户修改密码

1	# SET PASSWORD=PASSWORD('new_password');

MySQL的各种权限

权限名称	对应user表中的列	权限的范围
CREATE	Create_priv	数据库, 表或索引
DROP	Drop_priv	数据库, 表
GRANT OPTION	Grant_priv	数据库, 表, 存储过程, 函数
REFERENCES	References_priv	数据库, 表
ALTER	Alter_priv	修改表
DELETE	Delete_priv	删除表
INDEX	Index_priv	用索引查询表
INSERT	Insert_priv	插入表
SELECT	Select_priv	查询表
UPDATE	Update_priv	更新表
CREATE VIEW	Create_view_priv	创建视图
SHOW VIEW	Show_view_priv	查看视图
ALTER ROUTINE	Alter_routine_priv	修改存储过程或存储函数
CREATE ROUTINE	Create_routine_priv	创建存储过程或存储函数
EXECUTE	Execute_priv	执行存储过程或存储函数
FILE	File_priv	加载服务器主机上的文件
CREATE TEMPORARY TABLES	Create_tmp_table_priv	创建临时表
LOCK TABLES	Lock_tables_priv	锁定表
CREATE USER	Create_user_priv	创建用户
PROCESS	Process_priv	服务器管理
RELOAD	Reload_priv	重新加载权限表
REPLICATION CLIENT	Repl_client_priv	服务器管理
REPLICATION SLAVE	Repl_slave_priv	服务器管理
SHOW DATABASES	Show_db_priv	查看数据库
SHUTDOWN	Shutdown_priv	关闭服务器
SUPER	Super_priv	超级权限

授权与收回权限

授权

# GRANT priv_type [(column_list)] ON database.table TO user [IDENTIFIED BY [PASSWORD] 'password']
#     [, user [IDENTIFIED BY [PASSWORD] 'password']] ... [WITH with_option [with_option] ...]

# with_option有几个权限值可选
#
# - GRANT OPTION: 被授权的用户可以将这些权限赋予给别的用户
# - MAX_QUERIES_PER_HOUR count: 设置每小时可以允许执行count次查询
# - MAX_UPDATES_PER_HOUR count: 设置每小时可以允许执行count次更新
# - MAX_CONNECTIONS_PER_HOUR count: 设置每小时可以建立count连接
# - MAX_USER_CONNECTIONS count: 设置单个用户可以同时具有的count个连接数

> GRANT SELECT, UPDATE ON *.* TO 'test5'@'localhost' IDENTIFIED BY 'test5' WITH GRANT OPTION;

收回权限

REVOKE priv_type [(column_list)] ... ON database.table FROM user [, user] ...

# 收回全部权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

查看权限

1 2	> SELECT * FROM mysql.user where User='xxxx' and Host='yyyy'; > SHOW GRANTS FOR 'username'@'hostname';

数据备份与还原

使用mysqldump命令备份

备份一个数据库

mysqldump -u username -p dbname table1 table2 ... > backup_name.sql

不指定table1, table2时, 表示备份整个db

备份多个数据库

mysqldump -u username -p --databases dbname1 dbname2 ... > backup_name.sql

备份所有数据库

mysqldump -u username -p --all-databases > backup_name.sql

直接复制整个数据库目录

这种方法最简单, 最快速, 但是使用这种方法最好将服务器先停止, 以保证在复制期间数据不发生变化.
这种方法对InnoDB存储引擎的表不适用, 只适用于MyISAM表
还原时最好是相同版本的MySQL数据库, 否则可能存在文件类型不同的情况

数据还原

使用mysql命令还原

mysql -u root -p [dbname] < backup_name.sql

指定dbname, 表示还原该数据库下的表

不指定dbname, 表示还原sql中的所有数据库

直接复制整个数据库目录

使用这个方式进行还原, 有上述使用该种方式备份时的种种限制, 请慎用

数据迁移

1	mysqldump -h hostname1 -u root --password=password1 --all-databases \| mysql -h hostname2 -u root --password=password2

表的导入导出

导出

# SELECT [列名] FROM table [WHERE 语句] INTO OUTFILE '目标文件' [OPTION]
#
# OPTION常用选项值
#
# - FIELDS TERMINATED BY '字符串': 设置字符串为字段的分隔符, 默认是'\t'
# - FIELDS ENCLOSED BY '字符': 设置字符来括上字段的值, 默认不使用任何符号
# - FIELDS OPTIONALLY ENCLOSED BY '字符': 设置字符来括上CHAR, VARCHAR和TEXT等字符型字段, 默认不使用任何符号
# - LINES STARTING BY '字符串': 设置每行开头的字符, 默认情况不使用任何字符
# - FIELDS ESCAPED BY '字符': 设置转移字符, 默认为'\'
# - LINES TERMINATED BY '字符串': 设置每行的结束符, 默认值是'\n'
#
# SELECT * FROM test.student INTO OUTFILE '/tmp/test.txt'
#     FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\"' LINES STARTING BY '\>'
#     TERMINATED BY '\r\n';

导入

# LOAD DATA [LOCAL] INFILE file INTO TABLE table [OPTION];
#
# OPTION常用选项值
#
# - 导出小节所列选项值
# - IGNORE n LINES: 忽略文件的前n行记录;
# - (字段列表): 根据字段列表中的字段和顺序来加载记录
# - SET column=expr: 将制定的列column进行相应地转换后再加载, 使用expr表达式来进行转换
#
# LODA DATA INFILE '/tmp/test.txt' INTO TABLE test.student FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "'";

日志

除了bin log, 其余日志都是文本文件

默认情况只启用错误日志err log, 其他3类日志都需要数据库管理员进行设置

如果MySQL数据库系统意外停止服务, 可以通过错误日志查看出现错误的原因. 并且, 可以通过bin log文件来查看用户执行了哪些操作
, 对数据库文件做了哪些修改. 然后, 可以根据bin log中的记录来修复数据库

但是, 启动log功能会降低MySQL数据库的执行速度. 例如, 一个查询操作比较频繁的MySQL中, 记录通用查询日志和慢查询日志要花费
很多的时间. 并且, log文件会占用大量的硬盘空间. 对于用户量非常大, 操作非常频繁的数据库, 日志文件需要的存储空间甚至比数
据库文件需要的存储空间还要大.

二进制日志(bin log)

也叫update log, 主要用于记录数据库的变化情况. 通过bin log可以查询MySQL数据库进行了哪些改变.

启动和设置bin log

# my.cnf
[mysqld]
log-bin [=DIR \ [filename]]

DIR: 指定bin log的存储路径
filename: 指定bin log的文件名, 其形式为filename.number, 例如filename.000001, filename.000002等. 每次重启MySQL服务, 都会
生成一个新的bin log文件. 这些bin log文件的'number'会不断递增. 除了生成上述文件外, 还会生成一个名为filename.index的文件.
这个文件中存储所有bin log文件的清单.

如果没有指定DIR, filename, bin log将默认存储在数据库的数据目录下, 默认的文件名为hostname-bin.number, 其中hostname为主机
名.

bin log与数据库的数据文件最好不要放在同一块硬盘上. 即使数据文件所在的硬盘被破坏, 也可以使用另一块硬盘上的bin log来回复数
据文件. 两块硬盘同时坏了的可能性要小得多. 这样可以保证数据库中数据的安全.

查看bin log

mysqlbinlog filename.number

使用bin log来还原数据库

mysqlbinlog filename.number | mysql -u root -p

注意, 编号(number)小的文件开始还原

mysqlbinlog mylog.00001 | mysql -u root -p
mysqlbinlog mylog.00002 | mysql -u root -p
mysqlbinlog mylog.00003 | mysql -u root -p
mysqlbinlog mylog.00004 | mysql -u root -p

暂停和重启bin log功能

1 2	SET SQL_LOG_BIN=0; # 暂停 SET SQL_LOG_BIN=1; # 重新启用

错误日志(error log)

err log是默认开启的, 并且无法被禁止. 默认存储路径为数据库的数据文件夹. 文件名通常为hostname.err.

存储路径可以通过 log-error选项来设置

1
2
3

# my.cnf
[mysqld]
log-error=DIR [/ filename]

通用查询日志

通用查询日志是用来记录用户的所有操作, 包括启动和关闭MySQL服务, 更新语句和查询语句等.

默认文件名为hostname.log, 默认存储路径为数据库的数据文件夹.

默认情况下, 通用查询日志功能是关闭的. 可以通过下面方式设置开启

1
2
3

# my.cnf
[mysqld]
log [=DIR \ [filename]]

慢查询日志

慢查询日志是用来记录执行时间超过指定时间的查询语句. 通过慢查询日志, 可以查找出哪些查询语句的执行效率很低, 以便进行优化.

默认该日志是关闭的. 默认存储路径为数据库的数据文件夹, 默认文件名为hostname-slow.log

# my.cnf
[mysqld]
log-slow-queries [=DIR \ [filename]]
long_query_time=n

# 执行时间超过n秒的语句会被记录下来, n不设置的话, 默认是10s

性能优化的有关概念

优化简介

db管理员可以使用SHOW STATUS语句查询MySQL的性能.

> SHOW STATUS LIKE 'value';

value是以下几个常用统计参数

- Connections: 连接MySQL服务器的次数
- Uptime: MySQL服务器的上线时间
- Slow_queries: 慢查询的次数
- Com_select: 查询操作的次数
- Com_insert: 插入操作的次数
- Com_update: 更新操作的次数
- Com_delete: 删除操作的次数

优化查询

分析查询语句

> EXPLAIN|DESCRIBE SELECT * FROM student \G;
MariaDB [carltest]> explain select * from student \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra:
1 row in set (0.24 sec)

ERROR: No query specified

MariaDB [carltest]> desc select * from student \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra:
1 row in set (0.00 sec)

ERROR: No query specified

id: 表示select语句的编号
select_type: 表示select语句的类型. SIMPLE表示简单查询, 其中不包括子查询和连接查询; PRIMARY表示主查询, 或者是最外层的
查询语句; UNION表示连接查询的第二个或后面的查询语句
table: 表示查询的表
type: 表示表的连接类型
- system: 表示表中只有一条记录
- const: 表示表中有多条记录, 但是只从表中查询一条记录
- ALL: 表示对表进行了完整的扫描;
- eq_ref: 表示多表连接时, 后面的表使用了UNIQUE或PRIMARY KEY
- ref: 表示多表查询时, 后面的表使用了普通索引;
- unique_subquery: 表示子查询中使用了UNIQUE或PRIMARY KEY
- index_subquery: 表示子查询中使用了普通索引
- range: 表示查询语句中给出了查询范围
- index: 表示对表中的索引进行了完整的扫描
possible_keys: 表示查询中可能使用的索引
key: 表示查询使用到的索引
key_len: 表示索引字段的长度
ref: 表示使用哪个列或常数与索引一起来查询记录
rows: 表示查询的行数
Extra: 表示查询过程的附件信息

使用索引查询

注意下面添加索引前后, 两次插叙 `rows`的结果, 一个为4, 一个为1.

MariaDB [carltest]> explain select * from student where name="李四" \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [carltest]> create index index_name on student(name);
Query OK, 0 rows affected (0.44 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [carltest]> explain select * from student where name="李四" \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: index_name
          key: index_name
      key_len: 62
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

ERROR: No query specified

LIKE关键字的使用方式. 如果匹配字符串的第一个字符为’%’, 索引不会被使用, 否则, 索引会被使用

MariaDB [carltest]> explain select * from student where name like "%四" \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [carltest]> explain select * from student where name like "李%" \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: range
possible_keys: index_name
          key: index_name
      key_len: 62
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.06 sec)

ERROR: No query specified

多列索引的用法. 只有查询条件中使用了这些字段中第一个字段时, 索引才会被使用.

MariaDB [carltest]> create index index_birth_department on student(birth, department);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [carltest]> explain select * from student where birth=1991 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ref
possible_keys: index_birth_department
          key: index_birth_department
      key_len: 2
          ref: const
         rows: 1
        Extra:
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [carltest]> explain select * from student where department="英语系" \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

ERROR: No query specified

OR关键字索引用法. OR前后两个条件的列都是索引时, 查询将使用索引. 只要有一列不是索引, 那么查询就不会使用索引

MariaDB [carltest]> explain select * from student where name='王六' or sex='女' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: index_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

ERROR: No query specified

# id列是索引
MariaDB [carltest]> explain select * from student where name='王六' or id=901 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: PRIMARY,index_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
        Extra: Using where
1 row in set (0.00 sec)

ERROR: No query specified

MySQL参数优化

参数	说明
key_buffer_size	索引缓存的大小, 该值越大, 使用索引查询速度越快
table_cache	表示同时打开的表的个数. 该值越大, 能同时打开的表的个数越多. 但不是越大越好, 因为同时打开的表太多会影响操作系统的性能
query_cache_size	查询缓存区大小.
query_cache_type	查询缓冲区的开启状态. 0-关闭, 1-开启, 2-按要求使用
max_connections	数据库的最大连接数. 也不是越大越好, 太多连接会浪费内存资源
sort_buffer_size	排序缓冲区大小. 该值越大, 排序速度越快
innodb_buffer_pool_size	InnoDB类型的表和索引的最大缓存. 该值越大, 查询速度越快. 但是太大了也会影响系统性能
innodb_flush_log_at_trx_commit	0-每隔1s将数据写入log, 并将log写入磁盘; 1-每次提交事务时写log写磁盘; 2-每次提交事务时写log, 每隔1s将log写入磁盘, 默认值为1

查看已存在的数据库

查询默认存储引擎

创建数据库

删除数据库

设置表的主键

创建索引

查询

基本查询语句

先准备点数据, 要不然下面的戏没法唱了.

LIMIT限定

IN关键字

GROUP BY关键字

嵌套查询

UNION, UNION ALL用法

ANY关键字

正则表达式匹配查询

连接查询

插入, 更新, 删除数据

先建一张表, 接下来用来操作

插入数据

INSERT语句中不指定具体的字段名

插入指定列

同时插入多行数据

将查询结果插入到表中

更新数据

删除数据

表结构修改

修改表名

添加列

修改列

删除列

修改unique key

权限管理

mysql.user表

权限列中有很多权限字段需要特别注意:

user表的资源控制列

用户账号相关

新建普通用户

删除普通用户

root用户修改root用户的密码

root用户修改普通用户密码

普通用户修改密码

MySQL的各种权限

授权与收回权限

授权

收回权限

查看权限

数据备份与还原

使用mysqldump命令备份

备份一个数据库

备份多个数据库

备份所有数据库

直接复制整个数据库目录

数据还原

使用mysql命令还原

直接复制整个数据库目录

数据迁移

表的导入导出

导出

导入

日志

二进制日志(bin log)

启动和设置bin log

查看bin log

使用bin log来还原数据库

暂停和重启bin log功能

错误日志(error log)

通用查询日志

慢查询日志

性能优化的有关概念

优化简介

优化查询

分析查询语句

使用索引查询

注意下面添加索引前后, 两次插叙 rows的结果, 一个为4, 一个为1.

LIKE关键字的使用方式. 如果匹配字符串的第一个字符为’%’, 索引不会被使用, 否则, 索引会被使用

多列索引的用法. 只有查询条件中使用了这些字段中第一个字段时, 索引才会被使用.

OR关键字索引用法. OR前后两个条件的列都是索引时, 查询将使用索引. 只要有一列不是索引, 那么查询就不会使用索引

MySQL参数优化

注意下面添加索引前后, 两次插叙 `rows`的结果, 一个为4, 一个为1.