YUM安装MySQL数据库

MySQL是目前使用最受信赖和广泛使用的开源数据库平台。全球十大最受欢迎和高流量的网站中有10个依赖于MySQL。MySQL 8.0通过提供全面的改进建立在这一势头上，旨在使创新的DBA和开发人员能够在最新一代的开发框架和硬件上创建和部署下一代Web，嵌入式，移动和云/ SaaS / PaaS / DBaaS应用程序平台。MySQL 8.0亮点包括：

  • MySQL文档存储
  • 交易数据字典
  • SQL角色
  • 默认为utf8mb4
  • 公用表表达式
  • 窗口功能
  • 以及更多

安装过程：

  1. 下载MySQL YUM仓库：
  1. wget https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm
  1. 安装MySQL YUM仓库：
  1. rpm -Uvh mysql80-community-release-el7-2.noarch.rpm
  1. 安装MySQL数据库
  1. #默认安装最新GA版MySQL
  2. #可以通过运行以下命令并检查其输出来验证是否已启用和禁用了正确的子存储库
  3. [root@python-test yum.repos.d]# yum repolist enabled | grep mysql
  4. mysql-connectors-community/x86_64 MySQL Connectors Community                  95
  5. mysql-tools-community/x86_64      MySQL Tools Community                       84
  6. mysql80-community/x86_64          MySQL 8.0 Community Server                  82
  7. #安装MySQL
  8. # yum install mysql-community-server
  1. 启动数据库
  1. #systemctl start mysqld.service
  2. #systemctl status mysqld.service

  1. MySQL服务器初始化（从MySQL 5.7开始）：在服务器初始启动时，如果服务器的数据目录为空，则会发生以下情况：

    • 服务器已初始化。
    • 在数据目录中生成SSL证书和密钥文件。
    • 该validate_password插件安装并启用。

    • 将’root’@‘localhost’ 创建一个超级用户帐户。设置超级用户的密码并将其存储在错误日志文件中。要显示它，请使用以下命令：

      1. grep 'temporary password' /var/log/mysqld.log

    • 通过使用生成的临时密码登录并为超级用户帐户设置自定义密码，尽快更改root密码：

      1. mysql -u root -p
      2. #进入数据库后更改密码：
      3. mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
      4. #注意：
      5. #MySQL的 validate_password 插件默认安装。这将要求密码包含至少一个大写字母，一个小写字母，一个数字和一个特殊字符，
      6. #并且密码总长度至少为8个字符。

二进制安装

前提：

  1. 如果您以前使用操作系统本机程序包管理系统（如Yum或APT）安装了MySQL，则使用本机二进制文件安装时可能会遇到问题。确保您之前的MySQL安装已完全删除（使用您的包管理系统），并且还删除了任何其他文件，例如旧版本的数据文件。您也应该检查配置文件，如/etc/my.cnf或/etc/mysql目录，并删除它们。
  2. MySQL依赖于libaio库。如果未在本地安装此库，则数据目录初始化和后续服务器启动步骤将失败
  1. # yum install libaio

要安装压缩的tar文件二进制分发版，请在您选择的安装位置（通常/usr/local/mysql）将其解压缩。这将创建下表中显示的目录。
通用Unix / Linux二进制包的MySQL安装布局：

目录 目录的内容
bin mysqld服务器，客户端和实用程序
docs 信息格式的MySQL手册
man Unix手册页
include 包含（标题）文件
lib 库文件
share 用于数据库安装的错误消息，字典和SQL
support-files 其他支持文件

安装过程：

  1. 创建一个MySQL用户和组
  1. # groupadd mysql
  2. # useradd -r -g mysql -s /bin/flase mysql
  1. 解压下载的二进制安装包
  1. # cd /usr/local/
  2. # tar xvf /root/mysql/mysql-8.0.15-linux-glibc2.12-x86_64.tar.xz
  3. 创建一个链接目录mysql
  4. # ln -s mysql-8.0.15-linux-glibc2.12-x86_64 mysql
  5. 为了避免在使用MySQL时始终键入客户端程序的路径名，可以将/usr/local/mysql/bin目录添加到PATH变量中
  6. # export PATH=$PATH:/usr/local/mysql/bin
  7. 如何希望永远生效，可以写到/root/.bash_profile文件中
  1. 安装后设置与测试
  1. 为导入和导出操作创建安全目录,进入安装目录：
  2. # cd /usr/local/mysql
  3. 创建目录： 
  4. # mkdir mysql-files
  5. 设置属主属组为mysql，并设置权限
  6. # chown mysql:mysql mysql-files/
  7. # chmod 750 mysql-files/
  8. 初始化命令：
  9. # bin/mysqld --initialize --user=mysql
  11. 会生成一个密码，这个密码已经默认设置为过期，必须要更改才可以使用
  12. 也可以初始化的时候，指定一些选项：
  13. # bin/mysqld --initialize --user=mysql
  14.   --basedir=/opt/mysql/mysql       指定安装目录
  15.   --datadir=/opt/mysql/mysql/data      指定数据库根目录
  16. 也可以将选项放到配置文件中，假设选项文件名是 /opt/mysql/mysql/etc/my.cnf
  17. [mysqld]
  18. basedir=/opt/mysql/mysql
  19. datadir=/opt/mysql/mysql/data
  20. 然后初始化时候调用：
  21. # bin/mysqld --defaults-file=/opt/mysql/mysql/etc/my.cnf
  22.   --initialize --user=mysql
  1. 安全启动数据库：
  1. # bin/mysqld_safe --user=mysql &
  2. 要确保使用mysql用户启动
  3. 连接到数据库
  4. # mysql -uroot -p
  5. Enter password:   输入初始化的密码
  6. mysql> alter user 'root'@'localhost' identified by 'Com.123456';  修改为新密码
  1. 测试服务器

使用mysqladmin验证服务器是否正在运行。以下命令提供简单的测试，以检查服务器是否已启动并响应连接：

  1. [root@web01 mysql]# mysqladmin version -uroot -pCom.123456
  2. mysqladmin: [Warning] Using a password on the command line interface can be insecure.
  3. mysqladmin  Ver 8.0.16 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)
  4. Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
  6. Oracle is a registered trademark of Oracle Corporation and/or its
  7. affiliates. Other names may be trademarks of their respective
  8. owners.
  10. Server version        8.0.16
  11. Protocol version    10
  12. Connection        Localhost via UNIX socket
  13. UNIX socket        /tmp/mysql.sock
  14. Uptime:            2 min 32 sec
  16. Threads: 2  Questions: 8  Slow queries: 0  Opens: 144  Flush tables: 3  Open tables: 45  Queries per second avg: 0.052

1. MySQL安装及基本SQL语句 - 图1

  1. # mysqladmin variables -uroot -pCom.123456  显示可用变量
  2. 验证您是否可以关闭服务器：
  3. # mysqladmin -uroot -pCom.123456 shutdown
  4. 使用mysqlshow查看存在哪些数据库：
  5. # mysqlshow -p

1. MySQL安装及基本SQL语句 - 图2

  1. 如果指定数据库名称，mysqlshow将 显示数据库中的表列表：
  2. [root@python-test mysql]# mysqlshow mysql -p

1. MySQL安装及基本SQL语句 - 图3

  1. 使用mysql程序从mysql架构中的表中选择信息：
  2. # mysql -e "SELECT User, Host, plugin FROM mysql.user" mysql -p

1. MySQL安装及基本SQL语句 - 图4

  1. 使用systemd启动服务器
  1. # 构建主配置文件
  2. [root@python-test mysql]# touch /etc/my.cnf
  3. [root@python-test mysql]# chmod 644 /etc/my.cnf 
  4. [root@python-test data]# vim /etc/my.cnf 
  5. [mysqld]
  6. datadir=/usr/local/mysql/data
  7. basedir=/usr/local/mysql
  8. socket=/tmp/mysql.sock
  9. port=3306
  10. log-error=/usr/local/mysql/data/python-test.err
  11. user=mysql
  12. secure_file_priv=/usr/local/mysql/mysql-files
  13. local_infile=OFF
  15. # 构建systemd服务单元配置文件
  16. [root@python-test data]# cat /usr/lib/systemd/system/mysqld.service 
  17. [Unit]
  18. Description=MySQL Server
  19. Documentation=man:mysqld(8)
  20. Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
  21. After=network.target
  22. After=syslog.target
  24. [Install]
  25. WantedBy=multi-user.target
  27. [Service]
  28. User=mysql
  29. Group=mysql
  31. Type=notify
  33. # Disable service start and stop timeout logic of systemd for mysqld service.
  34. TimeoutSec=0
  36. # Start main service
  37. ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf $MYSQLD_OPTS 
  39. # Use this to switch malloc implementation
  40. EnvironmentFile=-/etc/sysconfig/mysql
  42. # Sets open_files_limit
  43. LimitNOFILE = 10000
  45. Restart=on-failure
  47. RestartPreventExitStatus=1
  49. # Set environment variable MYSQLD_PARENT_PID. This is required for restart.
  50. Environment=MYSQLD_PARENT_PID=1
  52. PrivateTmp=false
  54. # 配置MySQL开机启动：
  55. [root@python-test data]# systemctl daemon-reload 
  56. [root@python-test data]# systemctl enable mysqld.service
  57. [root@python-test data]# systemctl start mysqld.service
  58. [root@python-test data]# systemctl status mysqld.service

1. MySQL安装及基本SQL语句 - 图5

1. 连接MySQL

使用命令行工具连接数据库

命令：mysql

  • –help 查看帮助
  • -h 指定主机
  • -p 指定密码
  • -P 指定端口 默认3306
  • -u 指定登陆用户

例如：

  1. [root@db-server ~]# mysql --host=localhost --port=3306 --user=root --password=Com.123456
  2. [root@db-server ~]# mysql -h 127.0.0.1 -P 3306 -u root -pCom.123456

登陆成功界面如下：

1. MySQL安装及基本SQL语句 - 图6

在数据库中可以输入sql语句，默认使用结束符“；”或“\g”，都是输出水平显示；如果使用“\G”,则是输出垂直显示；

1. MySQL安装及基本SQL语句 - 图7

使用\G

1. MySQL安装及基本SQL语句 - 图8

如果想退出数据库，输入exit或者Ctrl+D

如果想撤销命令，使用Ctrl+C

基本数据库操作sql语句

数据库服务器可以容纳很多个数据库，数据库是许多表的组合，逻辑关系如下：

数据库服务器->数据库->表（由列定义）->行

表是由行和列组成。

数据库和表称为数据库对象。任何操作（如创建、修改或删除数据库对象）都称为数据定义语言（DDL）操作。

数据按某种蓝图组织构建数据库（分为数据库和表），这种数据的组织形式被称为schema。

  1. 创建数据库：
  1. mysql> create database company;
  2. mysql> create database `my.contacts`;

反引号（`）用于引用标识符,如果数据库名称或者表名称中包含特殊字符，需要使用。

查看你有权访问的都有哪些数据库：

  1. mysql> show databases;
  1. 切换数据库：
  1. mysql> use company;
  2. Database changed

查看当前使用了哪个数据库：

  1. mysql> select database();
  2. +------------+
  3. | database() |
  4. +------------+
  5. | company    |
  6. +------------+
  7. 1 row in set (0.00 sec)

数据库被创建为数据目录中的一个目录，如果是yum安装的默认目录是/var/lib/mysql，如果是二进制安装的，数据目录是/usr/local/mysql/data/。可以通过以下命令来查看数据目录位置。

  1. mysql> show variables like 'datadir';
  2. +---------------+------------------------+
  3. | Variable_name | Value                  |
  4. +---------------+------------------------+
  5. | datadir       | /usr/local/mysql/data/ |
  6. +---------------+------------------------+
  7. 1 row in set (0.01 sec)
  1. 创建表

更难的部分是决定数据库的结构应该是什么：您需要哪些表以及每个表中应该包含哪些列。

在表中定义列时，应该指定列的名称、数据类型和默认值（如果有的话）。

MySQL支持多种类型的SQL数据类型：数字类型，日期和时间类型，字符串（字符和字节）类型，spatial 类型和 JSON数据类型

例如：

  1. mysql> create table if not exists company.customers (
  2.     -> id int unsigned auto_increment primary key,
  3.     -> first_name varchar(20),
  4.     -> last_name varchar(20),
  5.     -> country varchar(20)
  6.     -> ) engine=InnoDB;

1. MySQL安装及基本SQL语句 - 图9

  • IF NOT EXISTS:如果存在一个具有相同名字的表，并且你指定了这个字句，MySQL只会抛出一个警告，告知表已经存在。否则，MySQL将抛出一个错误。
  • company.customers :前面是数据库名字，后面是表名
  • id：这个是列名
  • int：代表是整数型
  • AUTO_INCREMENT:自动增长序列值
  • PRIMARY KEY: 指定主键
  • ENGINE:指定存储引擎，默认就是InnoDB

再来一个例子：

  1. mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
  2.        species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);
  • sex：‘male’和 ‘female’。最简单的是使用单个字符’m’和’f’

1. MySQL安装及基本SQL语句 - 图10

再来一个例子：

  1. mysql> create table company.payments (
  2.     -> customer_name varchar(20) primary key,
  3.     -> payment  float
  4.     -> );
  1. 查看创建的表：
  1. mysql> show tables ;
  2. +-------------------+
  3. | Tables_in_company |
  4. +-------------------+
  5. | customers         |
  6. | new_customers     |
  7. | payments          |
  8. | pet               |
  9. +-------------------+
  10. 4 rows in set (0.00 sec)
  1. 查看表中的列结构：
  1. mysql> describe pet;
  2. +---------+-------------+------+-----+---------+-------+
  3. | Field   | Type        | Null | Key | Default | Extra |
  4. +---------+-------------+------+-----+---------+-------+
  5. | name    | varchar(20) | YES  |     | NULL    |       |
  6. | owner   | varchar(20) | YES  |     | NULL    |       |
  7. | species | varchar(20) | YES  |     | NULL    |       |
  8. | sex     | char(1)     | YES  |     | NULL    |       |
  9. | birth   | date        | YES  |     | NULL    |       |
  10. | death   | date        | YES  |     | NULL    |       |
  11. +---------+-------------+------+-----+---------+-------+
  12. 6 rows in set (0.00 sec)

mysql> show create table customers\G

  1. mysql> show create table customers\G
  2. *************************** 1. row ***************************
  3.        Table: customers
  4. Create Table: CREATE TABLE `customers` (
  5.   `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  6.   `first_name` varchar(20) DEFAULT NULL,
  7.   `last_name` varchar(20) DEFAULT NULL,
  8.   `country` varchar(20) DEFAULT NULL,
  9.   PRIMARY KEY (`id`)
  10. ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
  11. 1 row in set (0.00 sec)

mysql会在数据目录内创建.ibd文件

  1. [root@db-server company]# ls -lhtr /usr/local/mysql/data/company/
  2. total 240K
  3. -rw-r-----. 1 mysql mysql 112K Apr  5 18:17 customers.ibd
  4. -rw-r-----. 1 mysql mysql 112K Apr  5 18:30 pet.ibd
  5. -rw-r-----. 1 mysql mysql 112K Apr  5 22:00 payments.ibd
  1. 克隆表结构
  1. mysql> create table new_customers like customers;
  2. Query OK, 0 rows affected (0.03 sec)
  4. mysql> describe new_customers;
  5. +------------+------------------+------+-----+---------+----------------+
  6. | Field      | Type             | Null | Key | Default | Extra          |
  7. +------------+------------------+------+-----+---------+----------------+
  8. | id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
  9. | first_name | varchar(20)      | YES  |     | NULL    |                |
  10. | last_name  | varchar(20)      | YES  |     | NULL    |                |
  11. | country    | varchar(20)      | YES  |     | NULL    |                |
  12. +------------+------------------+------+-----+---------+----------------+
  13. 4 rows in set (0.00 sec)

insert、update、delete和select操作称为数据操作语言（DML）语句。insert、update和delete也称为写操作，或者简称为写（write）。select是一个读操作，简称为读（read）。

  1. 插入数据（insert）
  1. mysql> insert ignore into company.customers (first_name,
  2.     -> last_name,country)
  3.     -> values
  4.     -> ('mike','christensen','USA'),
  5.     -> ('andy','Hollands','Australia'),
  6.     -> ('ravi','vedantam','India'),
  7.     -> ('rajiv','perera','Sri lanka');

或者明确写出id列，如果你想插入特定的id

  1. mysql> insert ignore into company.customers (id,first_name,
  2.     -> last_name,country)
  3.     -> values
  4.     -> (1,'mike','christensen','USA'),
  5.     -> (2,'andy','Hollands','Australia'),
  6.     -> (3,'ravi','vedantam','India'),
  7.     -> (4,'rajiv','perera','Sri lanka');
  8. Query OK, 0 rows affected, 4 warnings (0.00 sec)
  9. Records: 4  Duplicates: 4  Warnings: 4

ignore：如果该行已经存在，并给出了ignore字句，则新数据将被忽略，insert语句仍然会执行成功，同时生成一个警告和重复数据的数目。反之，如果未给出ignore字句，则insert语句会生成一条错误信息。行的唯一性由主键标识。

  1. mysql> show warnings;
  2. +---------+------+---------------------------------------+
  3. | Level   | Code | Message                               |
  4. +---------+------+---------------------------------------+
  5. | Warning | 1062 | Duplicate entry '1' for key 'PRIMARY' |
  6. | Warning | 1062 | Duplicate entry '2' for key 'PRIMARY' |
  7. | Warning | 1062 | Duplicate entry '3' for key 'PRIMARY' |
  8. | Warning | 1062 | Duplicate entry '4' for key 'PRIMARY' |
  9. +---------+------+---------------------------------------+
  10. 4 rows in set (0.00 sec)
  1. 更新语句（update）

update用于修改表中现有数据

  1. mysql> update customers set first_name='rajiv',country='UK' where id=4;

where：这是用于过滤的字句。在where字句后指定的任何条件都会用于过滤，被筛选出来的行都会被更新。where字句是强制性的。如果没有给出它，update会更新整个表。

  1. 删除语句（delete）
  1. mysql> delete from customers where id=4 and first_name='rajiv';
  2. Query OK, 1 row affected (0.01 sec)

where字句是强制性的。如果没有给出它，delete将删除表中的所有行。

  1. replace、insert、on duplicate key update

在很多情况下，我们需要处理重复项。行的唯一性由主键标识。如果行已经存在，则replace会简单的删除行并插入新行；如果行不存在，则replace等同于insert。

如果你想在行已经存在的情况下处理重复项，则需要使用on duplicate key update。如果指定了on duplicate key update选项，并且insert语句在primary key中引发了重复值，则MySQL会用新值更新已有行。

假设你希望每次从同一客户那里收到付款后更新之前的金额，并且在客户首次付款时插入新记录，那么你需要定义一个金额栏，并在每次收到新付款时进行更新：

  1. mysql> replace into customers values (1,'Mike','Christensen','America');
  2. Query OK, 2 rows affected (0.00 sec)

可以看到有两行受到影响，一个重复行被删除，一个新行被插入；

  1. mysql> insert into payments values ('Mike Christensen',200) on duplicate key
  2.     -> update payment=payment+values(payment);
  3. Query OK, 1 row affected (0.00 sec)
  5. mysql> insert into payments values ('Ravi Vedantam',500) on duplicate key
  6.     -> update payment=payment+values(payment);
  7. Query OK, 1 row affected (0.00 sec)

当Mike Christensen 下次支付300美元时，将更新该行并将此付款金额添加到以前的金额中：

  1. mysql> insert into payments values ('Mike Christensen',300) on duplicate key
  2.     -> update payment=payment+values(payment);
  3. Query OK, 2 rows affected (0.00 sec)

values(payment)：指的insert语句中给出的值，payment指的是表中的列。

  1. mysql> select * from payments;
  2. +------------------+---------+
  3. | customer_name    | payment |
  4. +------------------+---------+
  5. | Mike Christensen |     500 |
  6. | Ravi Vedantam    |     500 |
  7. +------------------+---------+
  8. 2 rows in set (0.00 sec)
  1. truncating table

删除整个表需要很长时间，因为mysql需要逐行执行操作。删除表的所有行（保留表结构）的最快方法是使用truncate table语句。truncating table 是mysql中的DDL操作，也就是说一旦数据被清空，就不能被回滚：

  1. mysql> truncate table info_tables;
  2. Query OK, 0 rows affected (0.03 sec)

sql查询

为了更好的练习操作，我们需要更多的数据。MySQL提供了一个示例employee数据库和大量数据提供给我们学习使用。

加载样例库步骤：

  1. 下载压缩文件： 

    1. # wget https://github.com/datacharmer/test_db/archive/master.zip

  2. 解压缩文件： 

    1. # unzip master.zip

  3. 加载数据： 

    1. # cd test_db-master/
    2. # mysql -u root -p < employees.sql

  4. 验证数据： 

    1. # mysql -uroot -p  employees   登录employees库
    2. mysql> show tables;
    3. +----------------------+
    4. | Tables_in_employees  |
    5. +----------------------+
    6. | current_dept_emp     |
    7. | departments          |
    8. | dept_emp             |
    9. | dept_emp_latest_date |
    10. | dept_manager         |
    11. | employees            |
    12. | salaries             |
    13. | titles               |
    14. +----------------------+
    15. 8 rows in set (0.00 sec)

从数据库中检索数据是经常的操作，使用select可以做很多事情，这里先看最经常的用法。

  1. 从数据库的表中查询所有的数据： 

    1. mysql> select * from departments;

  2. 查询特定列的信息： 

    1. mysql> select emp_no,dept_no from dept_manager;

  3. 从employees表中查找员工的数量： 

    1. mysql> select count(*) from employees;

  4. 条件过滤：使用where字句。
    所有的过滤条件都是通过where字句给出的，除整数型和浮点数外，其他所有内容都应放在引号内。
    找到first_name为Georgi且last_name为Facello的员工的emp_no： 

    1. mysql> select emp_no from employees where first_name='Georgi' and
    2. -> last_name='Facello';

  5. 操作符
    MySQL支持使用许多操作符来筛选结果。

    • equality：使用=进行过滤

    • IN：检查一个值是否在一组值中,例如，找出姓氏为Christ、Lamba或者Baba的所有员工的人数： 

      1. mysql> select count(*) from employees where last_name in 
      2. -> ('Christ','Lamba','Baba');

    • BETWEEN…AND：检查一个值是否在一个范围内。例如：找出1986年12月入职的员工人数： 

      1. mysql> select count(*) from employees where hire_date between 
      2. -> '1986-12-01' and '1986-12-31';

    • NOT：简单地用NOT运算符来否定结果。例如：找出不是在1986年12月入职的员工的人数： 

      1. mysql> select count(*) from employees where hire_date not between
      2. ->  '1986-12-1' and '1986-12-31';

  6. 简单模式匹配
    可以使用like运算符来实现简单模式匹配。使用下划线（_）来精准匹配一个字符，使用（%）来匹配任意数量的字符。
    找出名字以Christ开头的所有员工的人数：
    找出名字以Christ开头并以ed结尾的所有员工的人数：
    找出名字中包含sri的所有员工的人数：
    找出名字以er结尾的所有员工的人数：
    找出名字以任意两个字符开头、后面跟随ka、再后面跟随任意数量字符的所有员工人数： 

    1. mysql> select count(*) from employees where first_name like 'Christ%';
    1. mysql> select count(*) from employees where first_name like 'Christ%ed';
    1. mysql> select count(*) from employees where first_name like '%sri%';
    1. mysql> select count(*) from employees where first_name like '%er';
    1. mysql> select count(*) from employees where first_name like '__ka%';

正则表达式：

可以使用RLIKE或者REGEXP运算符在where字句中使用正则表达式。
常用正则：

字符 含义
* 0次或者多次重复
+ 一个或多个重复
？ 可选字符
. 任何字符
. 区间
^ 以。。。。开始
$ 以…结束
[abc] 只有a、b或c
[^abc] 非a、非b、非c
[a-z] 字符a到z
[0-9] 数字0到9
^…$ 开始和结束
\d 任何数字
\D 任何非数字字符
\s 任何空格
\S 任何非空白字符
\w 任何字母数字字符
\W 任何非字母数字字符
{m} 重复m次
{m,n} 重复m到n次

找出名字以Christ开头的所有员工的人数：

  1. mysql> select count(*) from employees where first_name rlike '^Christ';

找出姓氏以ba结尾的所有员工的人数：

  1. mysql> select count(*) from employees where last_name regexp 'ba$';

查找姓氏不包含元音（a、e、i、o和u）的所有员工的人数：

  1. mysql> select count(*) from employees where last_name  not regexp '[aeiou]';

限定结果：limit

可以在查询语句末尾使用limit字句来实现限定

查询hire_date在1986年之前的任何10名员工的姓名：

  1. mysql> select first_name,last_name from employees where hire_date < '1986-01-01' limit 10;

使用表别名:as

可以使用as语句来做别名显示

将conut(*)显示列改为numbers列输出

  1. mysql> select count(*) as numbers from employees where hire_date < '1986-01-01';

排序和使用聚合函数

对结果排序

可以根据列或者别名列队结果进行排序，也可以使用DESC指定按降序或用ASC指定按升序来排序。默认情况下，排序安装升序来进行。

可以将LIMIT字句与ORDER BY结合使用以限定结果集。

查找薪水最高的前5名员工的员工编号

  1. mysql> select emp_no,salary from salaries order by salary desc limit 5;
  2. +--------+--------+
  3. | emp_no | salary |
  4. +--------+--------+
  5. |  43624 | 158220 |
  6. |  43624 | 157821 |
  7. | 254466 | 156286 |
  8. |  47978 | 155709 |
  9. | 253939 | 155513 |
  10. +--------+--------+
  11. 5 rows in set (1.36 sec)

也可以在select语句中提及列的位置，而不是指定列名称。例如，想对位于第二列的工资进行排序，那么可以指定order by 2；

  1. mysql> select emp_no,salary from salaries order by 2 desc limit 5;
  2. +--------+--------+
  3. | emp_no | salary |
  4. +--------+--------+
  5. |  43624 | 158220 |
  6. |  43624 | 157821 |
  7. | 254466 | 156286 |
  8. |  47978 | 155709 |
  9. | 253939 | 155513 |
  10. +--------+--------+
  11. 5 rows in set (1.41 sec)

结果也是一样的

对结果分组（聚合函数）

可以在列上使用GROUP BY字句对结果进行分组，然后使用聚合函数（aggregate）,例如COUNT、MAX、MIN和AVERAGE。还可以在group by字句中的列上使用函数。

COUNT

分别找出男性和女性员工的人数：

  1. mysql> select gender ,count(*) as count from employees group by gender;
  2. +--------+--------+
  3. | gender | count  |
  4. +--------+--------+
  5. | M      | 179973 |
  6. | F      | 120051 |
  7. +--------+--------+
  8. 2 rows in set (0.40 sec)

找出员工名字中最常见的10个名字

  1. mysql> select first_name,count(first_name) as count from employees  group by first_name order by count desc limit 10;
  2. +-------------+-------+
  3. | first_name  | count |
  4. +-------------+-------+
  5. | Shahab      |   295 |
  6. | Tetsushi    |   291 |
  7. | Elgin       |   279 |
  8. | Anyuan      |   278 |
  9. | Huican      |   276 |
  10. | Make        |   275 |
  11. | Sreekrishna |   272 |
  12. | Panayotis   |   272 |
  13. | Hatem       |   271 |
  14. | Giri        |   270 |
  15. +-------------+-------+
  16. 10 rows in set (0.34 sec)

SUM

查找每年给予员工的薪水总额，并按薪水高低对结果进行排序。YEAR()函数返回给定日期所在的年份：

  1. mysql> select '2017-05-13',year('2017-6-13');
  2. +------------+-------------------+
  3. | 2017-05-13 | year('2017-6-13') |
  4. +------------+-------------------+
  5. | 2017-05-13 |              2017 |
  6. +------------+-------------------+
  7. 1 row in set (0.00 sec)
  9. mysql> select year(from_date),sum(salary) as sum from salaries
  10.     -> group by year(from_date) order by sum desc limit 5;
  11. +-----------------+-------------+
  12. | year(from_date) | sum         |
  13. +-----------------+-------------+
  14. |            2000 | 17535667603 |
  15. |            2001 | 17507737308 |
  16. |            1999 | 17360258862 |
  17. |            1998 | 16220495471 |
  18. |            1997 | 15056011781 |
  19. +-----------------+-------------+
  20. 5 rows in set (2.99 sec)

AVERAGE

查找平均工资最高的10名员工：

  1. mysql> select emp_no,avg(salary) as avg from salaries
  2.     -> group by emp_no order by avg desc limit 10;
  3. +--------+-------------+
  4. | emp_no | avg         |
  5. +--------+-------------+
  6. | 109334 | 141835.3333 |
  7. | 205000 | 141064.6364 |
  8. |  43624 | 138492.9444 |
  9. | 493158 | 138312.8750 |
  10. |  37558 | 138215.8571 |
  11. | 276633 | 136711.7333 |
  12. | 238117 | 136026.2000 |
  13. |  46439 | 135747.7333 |
  14. | 254466 | 135541.0625 |
  15. | 253939 | 135042.2500 |
  16. +--------+-------------+
  17. 10 rows in set (2.45 sec)

DISTINCT

可以使用DISTINCT字句过滤出表中的不同条目：

  1. mysql> select distinct title from titles;
  2. +--------------------+
  3. | title              |
  4. +--------------------+
  5. | Senior Engineer    |
  6. | Staff              |
  7. | Engineer           |
  8. | Senior Staff       |
  9. | Assistant Engineer |
  10. | Technique Leader   |
  11. | Manager            |
  12. +--------------------+
  13. 7 rows in set (0.31 sec)

HAVING过滤

可以通过添加HAVING字句来过滤GROUP BY字句的结果

例如：找出平均工资超过140000美元的员工：

  1. mysql> select emp_no, avg(salary) as avg from salaries
  2.     -> group by emp_no having avg > 140000 order by avg desc;
  3. +--------+-------------+
  4. | emp_no | avg         |
  5. +--------+-------------+
  6. | 109334 | 141835.3333 |
  7. | 205000 | 141064.6364 |
  8. +--------+-------------+
  9. 2 rows in set (1.40 sec)