Linux云计算 |【第四阶段】RDBMS1-DAY1

时间:2024-09-30 19:19:29

主要内容:

数据库简介、构建MySQL服务(配置MySQL、配置phpMyadmin)、SQL语句基础(SQL语法规范、常用MYSQL命令)、SQL语句分类(DQL、DDL、DML、DCL)、基础查询、条件查询、排序;

一、数据库介绍

数据库是“按照数据结构来组织、存储和管理数据的仓库”。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。它们在各种应用中扮演着关键角色,从简单的数据存储到复杂的数据分析和处理。

主流数据库产品:

在数据库领域,DB、DBMS 和 DBS 是三个常见的术语,它们分别代表不同的概念。以下是对这三个术语的详细解释:

  • DB(数据库):存储数据的集合,以结构化的方式组织数据
  • DBMS(数据库管理系统):管理和操作数据库的软件系统,提供数据定义、操作和控制功能
  • DBS(数据库系统):包括数据库、数据库管理系统、应用程序和用户,形成一个完整的系统,用于管理和操作数据

DB(Database)

DB 是 Database 的缩写,中文翻译为“数据库”。数据库是一个有组织的数据集合,通常存储在计算机系统中,用于高效地存储、管理和检索数据。数据库可以包含各种类型的数据,如文本、数字、图像、音频、视频等。特点:

  • 数据组织:数据以结构化的方式存储,通常使用表格(表)来组织数据。
  • 数据独立性:数据与应用程序分离,应用程序通过数据库管理系统(DBMS)访问数据。
  • 数据共享:多个用户和应用程序可以同时访问和操作数据库中的数据。
  • 数据一致性:通过约束和事务机制,确保数据的一致性和完整性。

DBMS(Database Management System)

DBMS 是 Database Management System 的缩写,中文翻译为“数据库管理系统”。DBMS 是一个软件系统,用于创建、管理和操作数据库。它提供了用户与数据库之间的接口,使用户能够方便地存储、检索、更新和管理数据。特点:

  • 数据定义:提供数据定义语言(DDL),用于定义数据库的结构,如创建表、定义索引等。
  • 数据操作:提供数据操作语言(DML),用于查询、插入、更新和删除数据。
  • 数据控制:提供数据控制语言(DCL),用于控制数据的访问权限和安全性。
  • 事务管理:支持事务处理,确保数据的一致性和完整性。

DBS(Database System)

DBS 是 Database System 的缩写,中文翻译为“数据库系统”。数据库系统是一个更广泛的概念,它包括数据库(DB)、数据库管理系统(DBMS)以及相关的应用程序和用户。数据库系统是一个完整的系统,用于管理和操作数据。

组成:

  1. 数据库(DB):存储数据的集合。
  2. 数据库管理系统(DBMS):管理和操作数据库的软件系统。
  3. 应用程序:使用数据库的应用程序,如 Web 应用、桌面应用等。
  4. 用户:访问和操作数据库的用户,如管理员、开发人员、终端用户等。

1、MySQL简介

MySQL 是一种开源的关系型数据库管理系统(RDBMS),广泛用于各种规模的应用程序,从个人项目到大型企业级系统。MySQL 以其高性能、可靠性和易用性而闻名,是世界上最流行的数据库之一。最早隶属于瑞典的MySQL AB公司,2008年1月,被SUN公司收购,2009年4月,SUN被Oracle收购;其次拥有崭新的开源分支MariaDB,为应付MySQL可能会闭源的风险而诞生,由MySQL原作者Widenius主导开发,与MySQL保持最大程度兼容;

MySQL 的特点:

开源

  • 免费使用:MySQL 是开源软件,用户可以免费下载、使用和修改。
  • 社区支持:拥有庞大的用户和开发者社区,提供丰富的文档、教程和问题解答。

高性能

  • 快速读写:MySQL 优化了查询和数据操作,适用于高并发的读写操作。
  • 索引支持:支持多种索引类型(如 B-Tree 索引、哈希索引),提高查询性能。

可靠性

  • 事务支持:支持 ACID 事务,确保数据的一致性和完整性。
  • 备份和恢复:提供多种备份和恢复机制,确保数据的可靠性和安全性。

易用性

  • SQL 支持:完全支持 SQL 标准,用户可以使用熟悉的 SQL 语法进行数据操作。
  • 图形化工具:提供多种图形化管理工具(如 MySQL Workbench),方便用户管理和操作数据库。

可扩展性

  • 水平扩展:支持分片(Sharding)和复制(Replication),实现水平扩展。
  • 垂直扩展:支持多核处理器和多服务器配置,实现垂直扩展。

MySQL 的使用场景:

Web 应用

  • 电子商务:存储产品信息、订单数据、用户信息等。
  • 社交网络:存储用户信息、帖子、评论等。
  • 内容管理系统(CMS):存储文章、页面、用户数据等。

企业应用

  • 客户关系管理(CRM):存储客户信息、销售数据、市场活动等。
  • 企业资源规划(ERP):存储财务数据、供应链数据、人力资源数据等。

数据分析

  • 数据仓库:存储大量历史数据,用于数据分析和报表生成。
  • 实时分析:支持实时数据查询和分析,如实时监控、实时报表等。

MySQL 的架构主要包括以下几个组件:

① 客户端

  • 命令行工具:如 mysql 命令行工具,用于执行 SQL 命令和管理数据库。
  • 图形化工具:如 MySQL Workbench,提供图形化界面进行数据库管理。
  • 应用程序:各种编程语言的 MySQL 驱动程序,如 PHP、Python、Java 等。

② 服务器

  • MySQL 服务器:负责处理客户端请求,执行 SQL 查询和数据操作。
  • 存储引擎:MySQL 支持多种存储引擎,如 InnoDB、MyISAM、Memory 等,不同的存储引擎提供不同的功能和性能。

③ 存储

  • 数据文件:存储实际的数据,如表数据、索引数据等。
  • 日志文件:存储事务日志、错误日志等,用于数据恢复和故障排查。

2、MySQL 的安装和配置

1)Linux:可以使用包管理器(如 apt、yum)安装 MySQL。

sudo apt-get install mysql-server

2)Windows:可以从 MySQL 官方网站下载安装包,按照向导进行安装。

配置文件:MySQL 的配置文件通常位于 /etc/mysql/my.cnf(Linux)或 C:\ProgramData\MySQL\MySQL Server X.X\my.ini(Windows)。
常用配置项:
- bind-address:指定 MySQL 服务器监听的 IP 地址。
- port:指定 MySQL 服务器监听的端口(默认 3306)。
- datadir:指定数据文件的存储路径。
- log_error:指定错误日志文件的路径。

二、部署MySQL服务

1)MySQL安装准备环境

  • 关闭防火墙和SELinux
  • 下载MySQL社区软件(教学提供mysql-5.7.17.tar)

官网下载:MySQL :: Download MySQL Community Server

mysql-community-client    //客户端应用程序

mysql-community-common    //数据库和客户端库共享文件

mysql-community-devel    //客户端应用程序的库和头文件

mysql-community-embedded   //嵌入式函数库

mysql-community-embedded-compat   //嵌入式兼容函数库

mysql-community-embedded-devel   //头文件和库文件作为MySQL的嵌入式库文件

mysql-community-libs     //mysql数据库客户端应用程序的共享库

mysql-community-libs-compat    //客户端应用程序的共享兼容库

mysql-community-minimal-debuginfo

mysql-community-server

mysql-community-test

  • 配置网络yum源(为后续实验方便软件下载)
  • 启动mysqld服务,首次启动服务,会执行数据初始化

2)相关参数

  • 软件安装后自动创建相关目录与文件

3)MySQL初始配置

① 初始密码登录

  • 数据库管理员名为root,默认仅允许root本机连接
  • 首次登录密码在安装软件是随机生成,随机密码存储在/var/log/mysqld.log日志文件

② 修改root密码

  • 使用mysqladmin -u用户名 -p随机密码 password '新密码'
  • 使用alter user命令修改登录密码,新密码必须满足密码策略;

4)连接方式

客户端连接MySQL服务的方法

  • ① 命令行
  • ② web页面
  • ③ 安装图形软件(phpMyAdmin)
  • ④ 编写脚本(PHP、JAVA、Python)

5)数据存储流程

客户端把数据存储到数据库服务器上的步骤:

  • ① 连接数据库服务器
  • ② 建库 //类似于文件夹
  • ③ 建表 //类似于文件
  • ④ 插入记录 //类似于文件内容
  • ⑤ 断开连接

附加:修改密码策略

策略名称

验证方式

0 or LOW

长度

1 or MEDIUM(默认)

长度;数字,小写/大写,和特殊字符

2 os STRONG

长度;数字,小写/大写和特殊字符;

mysql> show variables like "%password%";       //查看变量
mysql> set global validate_password_policy=0;     //修改密码策略
mysql> set global validate_password_length=6;     //修改密码长度
[root@localhost ~]# vim /etc/my.cnf    //永久配置
[mysqld]
validate_password_policy=0
validate_password_length=6

案例:构建MySQL服务器

步骤1:环境准备

1)关闭防火墙与SELinux限制

2)准备网络Yum源

# 安装相关依赖包并启动httpd服务

[root@localhost ~]# yum install -y httpd createrepo
[root@localhost ~]# systemctl start httpd
[root@localhost ~]# systemctl enable httpd
[root@localhost ~]# mkdir /var/www/html/mysql
[root@localhost ~]# tar -xf mysql-5.7.17.tar -C /var/www/html/mysql/    //解压mysql软件包,并指定存放目录
[root@localhost ~]# cd /var/www/html/mysql/
[root@localhost mysql]# createrepo -d .    //生成仓库数据文件(-d 选项指定使用SQLite来存储生成的元数据)
[root@localhost mysql]# ls /var/www/html/mysql/    //repodata Yum
mysql-community-client-5.7.17-1.el7.x86_64.rpm           mysql-community-libs-5.7.17-1.el7.x86_64.rpm
mysql-community-common-5.7.17-1.el7.x86_64.rpm           mysql-community-libs-compat-5.7.17-1.el7.x86_64.rpm
mysql-community-devel-5.7.17-1.el7.x86_64.rpm            mysql-community-minimal-debuginfo-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-5.7.17-1.el7.x86_64.rpm         mysql-community-server-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.17-1.el7.x86_64.rpm  mysql-community-test-5.7.17-1.el7.x86_64.rpm
mysql-community-embedded-devel-5.7.17-1.el7.x86_64.rpm   repodata

3)配置YUM仓库,安装并启动mysql服务

[root@localhost ~]# vim /etc/yum.repos.d/mysql.repo   //配置网络yum源
[mysql]
name=mysql5.7
baseurl=http://192.168.2.5/mysql     //指定网路Yum源地址(httpd服务器192.168.2.5)
enabled=1
gpgcheck=0

[root@localhost ~]# yum -y install mysql-community-*     //安装mysql软件
[root@localhost ~]# systemctl start mysqld      //启动服务
[root@localhost ~]# systemctl enable mysqld    //设置开机自启
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since 二 2021-06-01 11:21:45 CST; 57min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 51701 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─51701 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
 
6月 01 11:21:42 localhost systemd[1]: Starting MySQL Server...
6月 01 11:21:45 localhost systemd[1]: Started MySQL Server.
[root@localhost ~]# id mysql     //服务启动后会生成一个mysql用户
uid=27(mysql) gid=27(mysql) 组=27(mysql)

提示:第一次启动mysql服务需要初始化数据,会比较慢

补充:忘记mysql密码可进行重新初始化配置,再配置密码

[root@localhost ~]# systemctl stop mysqld   //停止服务
[root@localhost ~]# rm -rf /var/lib/mysql/*      //删除配置与数据
[root@localhost ~]# systemctl restart mysqld     //重启mysql服务

步骤2:修改mysql密码,导入案例数据库

1)启动Mysqld服务时,自动生成随机密码并存放在/var/log/mysqld.log日志文件中

[root@localhost ~]# grep -i password /var/log/mysqld.log   //查看随机密码
2021-06-01T03:21:43.121483Z 1 [Note] A temporary password is generated for root@localhost: J>o>kzAOT7(_
[root@localhost ~]# mysqladmin -uroot -p'J>o>kzAOT7(_' password NSD2021@tedu.cn
[root@localhost ~]# mysql -uroot -p'NSD2021@tedu.cn'   //验证是否可以登录

2)导入nsd2021_data.sql案例数据库(参考:tedu_nsd/dbs/mysql_scripts)

补充:# git clone https://gitee.com/mrzhangzhg/tedu_nsd.git

[root@localhost ~]# cd /var/lib/mygit/tedu_nsd/dbs/mysql_scripts
[root@localhost mysql_scripts]# ls
dbsetup.sh  gen_data.py  nsd2021_data.sql  online_setup.sh  pypkgs  setup  tedu_db.sql
[root@localhost mysql_scripts]# mysql -uroot -p'NSD2021@tedu.cn' < nsd2021_data.sql

3)验证导入的数据

[root@localhost mysql_scripts]# mysql -uroot -p'NSD2021@tedu.cn'
mysql> show databases;     //查看所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nsd2021            |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
 
mysql> use nsd2021;      //切换数据库
mysql> show tables;     //查看库中所有的表
+-------------------+
| Tables_in_nsd2021 |
+-------------------+
| departments       |
| employees         |
| salary            |
+-------------------+
3 rows in set (0.00 sec)
 
mysql> select count(*) from departments;     //查看并统计表记录的数量
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)
 
mysql> select count(*) from employees;
+----------+
| count(*) |
+----------+
|      133 |
+----------+
1 row in set (0.00 sec)
 
mysql> select count(*) from salary;
+----------+
| count(*) |
+----------+
|     8055 |
+----------+
1 row in set (0.00 sec)

步骤3:部署phpMyAdmin,通过web页面管理mysql数据库

1)安装准备

[root@localhost ~]# yum -y install php php-mysqlnd php-xml php-json   //安装依赖包
[root@localhost ~]# systemctl staus httpd    //确认httpd服务已开启
[root@localhost mysqladmin]# ls -ld /var/www/html/     //确认目录权限为755
drwxr-xr-x. 4 root root 37 6月   1 14:40 /var/www/html/

2)解压压缩包并修改配置文件

[root@localhost ~]# tar -xf phpMyAdmin-2.11.11-all-languages.tar.gz
[root@localhost ~]# mv phpMyAdmin-2.11.11-all-languages /var/www/html/mysqladmin
[root@localhost ~]# cd /var/www/html/mysqladmin/
[root@localhost mysqladmin]# cp config.sample.inc.php config.inc.php  //复制模板,创建配置文件
[root@localhost mysqladmin]# vim config.inc.php
$cfg['blowfish_secret'] = 'tedu.cn';   //随便加一些字符
...

3)访问http://192.168.2.5/mysqladmin,用户名和密码是登陆mysql的root及密码

- 用户名:root,密码:NSD2021@tedu.cn

- 登录web页面管理mysql数据库


案例数据库说明:

数据库名为nsd2021,共有三张表

① departments表:部门表,共有8个部门

② employees表:员工表,共有133位员工,属于不同部门

③ salary表:工资表,记录自2015年以来的工资

三张表的关系:

  • 部门表departments与员工表employees之间有外键约束关系,employees表的department_id字段必须出现在departments表中
  • 员工表employees和工资表salary表之间有外键约束关系,salary表的employee_id必须出现在employees表中

三、SQL语句基础

SQL(Structured Query Language)是一种用于管理和操作关系型数据库的标准语言。SQL 语法规范定义了如何编写 SQL 语句,以确保语句的正确性和可读性。

1、SQL语法规范

① 不区分大小写
  • 关键字:SQL 关键字通常使用大写,以提高可读性。例如:SELECT、FROM、WHERE。
  • 标识符:表名、列名等标识符可以使用小写或混合大小写,但应保持一致性。例如:user_table、order_details。
② 分号【 ; 】结尾
  • 语句结束:每条 SQL 语句通常以分号(;)结尾,表示语句的结束。(或者使用`\g`结尾)
③ 注释
  • 单行注释:使用 -- 或 # 表示单行注释。
    -- 这是一个单行注释
    # 这也是一个单行注释
    
  • 多行注释:使用 /* ... */ 表示多行注释。
    /*
    这是一个多行注释
    可以跨越多行
    */
④ 空格和缩进
  • 空格:在关键字、标识符和表达式之间使用空格,以提高可读性。
    SELECT column1, column2
    FROM table_name
    WHERE condition;
    
  • 缩进:在复杂的 SQL 语句中,使用缩进提高可读性。
    SELECT column1, column2
    FROM table_name
    WHERE condition
      AND another_condition
    ORDER BY column_name ASC;
⑤ 引号
  • 字符串:字符串常量使用单引号(')括起来。
    SELECT * FROM users WHERE name = 'John';
    
  • 标识符:如果标识符包含特殊字符或与关键字冲突,可以使用双引号(")或方括号([])括起来。
    SELECT * FROM "user table" WHERE "user id" = 1;
⑥【\c】终止SQL命令

例如:查询命令

mysql> SELECT name,email FROM emplouees;
mysql> SELECT
    -> name,email
    -> FROM
    -> employees;

注意:该方式需要先切换到nsd2021库中才能进行查询操作,或使用nsd2021.employees;

例如:注释说明

- 单行注释(#、--)

mysql> # select * from departments
mysql> -- select * from departments

- 多行注释(/* 注释内容 */)

mysql> /*
   /*> SELECT
   /*> *
   /*> FROM
   /*> departments;
   /*> */

2、常用MySQL命令:

命令

含义

mysql> SHOW DATABASES;

查看所有数据库

mysql> USE ;

切换指定数据库

mysql> SHOW TABLES;

查看当前库中所有的表

mysql> DESC ;

查看表结构

mysql> SELECT DATABASE();

查看当前所处的数据库

mysql> SELECT USER();

查看当前登陆用户

mysql> SELECT VERSION();

查看数据库版本

例如:

mysql> show databases;    //查看所有数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| nsd2021            |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use nsd2021;         //切换指定数据库
mysql> select database();   //查看当前所处的数据库
+------------+
| database() |
+------------+
| nsd2021    |
+------------+
1 row in set (0.00 sec)
mysql> show tables;        //查看当前库中所有的表
+-------------------+
| Tables_in_nsd2021 |
+-------------------+
| departments       |
| employees         |
| salary            |
+-------------------+
3 rows in set (0.00 sec)
mysql> desc departments;     //查看表结构
+-----------+-------------+------+-----+---------+----------------+
| Field     | Type        | Null | Key | Default | Extra          |
+-----------+-------------+------+-----+---------+----------------+
| dept_id   | int(4)      | NO   | PRI | NULL    | auto_increment |
| dept_name | varchar(10) | YES  |     | NULL    |                |
+-----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> select user();     //查看当前登录用户
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> select version();    //查看数据库版本
+-----------+
| version() |
+-----------+
| 5.7.17    |
+-----------+
1 row in set (0.00 sec)

3、SQL语句分类

3.1 数据定义语言(DDL)

用于定义数据库结构,如创建、修改和删除数据库对象(如表、视图、索引等)。

  • 创建表
    CREATE TABLE table_name (
        column1 datatype constraints,
        column2 datatype constraints,
        ...
    );
    
  • 修改表
    ALTER TABLE table_name ADD column_name datatype;
    
  • 删除表
    DROP TABLE table_name;
    

3.2 数据操作语言(DML)

用于操作数据库中的数据,如插入、更新、删除和查询数据。

  • 插入数据
    INSERT INTO table_name (column1, column2, ...)
    VALUES (value1, value2, ...);
    
  • 更新数据
    UPDATE table_name
    SET column1 = value1, column2 = value2, ...
    WHERE condition;
    
  • 删除数据
    DELETE FROM table_name
    WHERE condition;
    
  • 查询数据
    SELECT column1, column2, ...
    FROM table_name
    WHERE condition
    ORDER BY column_name ASC|DESC;
    

3.3 数据控制语言(DCL)

用于控制数据库的访问权限和安全性。

  • 授权
    GRANT privileges ON object TO user;
    
  • 撤销权限
    REVOKE privileges ON object FROM user;
    

3.4 事务控制语言(TCL)

用于管理数据库事务,确保数据的一致性和完整性。

  • 开始事务
    BEGIN TRANSACTION;
    
  • 提交事务
    COMMIT;
    
  • 回滚事务
    ROLLBACK;

案例:数据查询语言DQL

— 基础查询

  • 语法:SELECT 查询的字段列表 FROM 数据表; //查询字段可使用【*】表示所有字段

补充:

① 查询多个字段,使用逗号【 , 】分隔

② 查看当前库的表记录时,库名可以省略

③ 字段列表决定显示列个数;

④ 条件决定显示行的个数;

1)查询的字段列表可以是字段、常量、表达式、函数

例如:查询单个字段

mysql> select dept_name from departments;

例如:查询多个字段

mysql> select name, email from employees;

例如:查询所有字段

mysql> select * from departments;

例如:使用表达式查询

mysql> select date, employee_id, basic+bonus from salary;   //basic工资,bonus奖金
+------------+-------------+-------------+
| date       | employee_id | basic+bonus |
+------------+-------------+-------------+
| 2015-01-10 |           2 |       27000 |
…
| 2020-12-10 |         133 |       10697 |
+------------+-------------+-------------+
8055 rows in set (0.00 sec)

例如:查询常量

mysql> select 100;
+-----+
| 100 |
+-----+
| 100 |
+-----+
1 row in set (0.01 sec)

例如:查询表达式

mysql> select 10+5;
+------+
| 10+5 |
+------+
|   15 |
+------+
1 row in set (0.00 sec)

例如:查询函数

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.17    |
+-----------+
1 row in set (0.00 sec)

例如:查询函数,统计salary表共有多少行记录

mysql> select count(*) from salary;
+----------+
| count(*) |
+----------+
|     8055 |
+----------+
1 row in set (0.00 sec)

2)查询时,给表字段添加别名并显示,字段名和别名之间可用空格或关键字AS

mysql> select dept_id 部门编号,dept_name AS 部门名 from departments;
+--------------+-----------+
| 部门编号     | 部门名    |
+--------------+-----------+
|            1 | 人事部    |
|            2 | 财务部    |
|            3 | 运维部    |
|            4 | 开发部    |
|            5 | 测试部    |
|            6 | 市场部    |
|            7 | 销售部    |
|            8 | 法务部    |
+--------------+-----------+
8 rows in set (0.00 sec)

3)查询时,将指定字段的重复记录去重显示,使用固定语法distinct

mysql> select dept_id from employees;
+---------+
| dept_id |
+---------+
|       1 |
|       1 |
|       2 |
|       3 |
|       3 |
|       4 |
|       5 |
|       5 |
|       6 |
|       7 |
|       8 |
|       8 |
|       8 |
|       8 |
+---------+
133 rows in set (0.00 sec)
 
mysql> select distinct dept_id from employees;
+---------+
| dept_id |
+---------+
|       1 |
|       2 |
|       3 |
|       4 |
|       5 |
|       6 |
|       7 |
|       8 |
+---------+
8 rows in set (0.01 sec)

4)查询时,使用concat函数进行字符串拼接

mysql> select concat(name,'-',phone_number) from employees;
+-------------------------------+
| concat(name,'-',phone_number) |
+-------------------------------+
| 梁伟-13591491431              |
| 郭岩-13845285867              |
| 李玉英-15628557234            |
| 杨金凤-18831784879            |
+-------------------------------+
133 rows in set (0.00 sec)

 

 — 条件查询

  • 语法:SELECT 查询的字段列表 FROM 表 WHERE 条件;

1)根据条件运算符,进行条件查询

- 条件运算符(与python类似)

[ > ]  //大于

[ < ]  //小于

[ = ]  //等于

[ >= ]  //大于等于

[ ]  //小于等于

[ != ]   //不等于

例如:

mysql> select * from departments where dept_id>5;   //查询dept_id字段中大于5的记录
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
3 rows in set (0.00 sec)
mysql> select * from departments where dept_id<3;   //查询dept_id字段中小于3的记录
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
+---------+-----------+
2 rows in set (0.00 sec)
mysql> select * from departments where dept_id=3;   //查询dept_id字段中等于3的记录
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       3 | 运维部    |
+---------+-----------+
1 row in set (0.00 sec)
mysql> select * from departments where dept_id!=3;  //查询dept_id字段中不等于3的记录
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
7 rows in set (0.00 sec)
mysql> select * from departments where dept_id>=7;   //查询dept_id字段中大于或等于7的记录
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
2 rows in set (0.00 sec)
mysql> select * from departments where dept_id<=2;   //查询dept_id字段中小于或等于2的记录
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
+---------+-----------+
2 rows in set (0.00 sec)

2)根据逻辑运算符,进行条件查询

逻辑运算符:and(&&)与、or(||)且、not(!)取反

例如:

mysql> select * from departments where dept_id>1 and dept_id<4;  //查询dept_id字段大于1且小于4的记录
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       2 | 财务部    |
|       3 | 运维部    |
+---------+-----------+
2 rows in set (0.00 sec)
mysql> select * from departments where dept_id<2 or dept_id>7;  //查询dept_id字段小于2或大于7的记录
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       8 | 法务部    |
+---------+-----------+
2 rows in set (0.00 sec)
mysql> select * from departments where not dept_id<=6;  //查询dept_id字段非小于等于6的记录
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
2 rows in set (0.00 sec)

— 模糊查询

[ like ]   //包含

[ between x and y ]   //查询x和y之间

[ in ]   //在列表中的

[ is null ]   //为空,相当于python的None

[ is not null ]   //非空

例如:通过like模糊查询,查找name字段包含姓张的人,注意“张“是严格匹配,无法找到

【%】匹配0到多个任意字符(类似shell的【*】)

mysql> select name,email from employees where name like '张%';
+-----------+--------------------------+
| name      | email                    |
+-----------+--------------------------+
| 张健      | zhangjian@tarena.com     |
| 张建平    | zhangjianping@tarena.com |
| 张倩      | zhangqian@tedu.cn        |
| 张冬梅    | zhangdongmei@tedu.cn     |
| 张伟      | zhangwei@tarena.com      |
| 张淑英    | zhangshuying@tarena.com  |
| 张娜      | zhangna@tedu.cn          |
| 张宇      | zhangyu@tedu.cn          |
| 张亮      | zhangliang@tedu.cn       |
| 张梅      | zhangmei@tedu.cn         |
+-----------+--------------------------+
10 rows in set (0.00 sec)

例如:通过like模糊查询,查找name字段包含姓张,且名只有一位的人

【_】匹配一个字符(类似shell的【?】)

mysql> select name,email from employees where name like '张_';
+--------+----------------------+
| name   | email                |
+--------+----------------------+
| 张健   | zhangjian@tarena.com |
| 张倩   | zhangqian@tedu.cn    |
| 张伟   | zhangwei@tarena.com  |
| 张娜   | zhangna@tedu.cn      |
| 张宇   | zhangyu@tedu.cn      |
| 张亮   | zhangliang@tedu.cn   |
| 张梅   | zhangmei@tedu.cn     |
+--------+----------------------+
7 rows in set (0.00 sec)

例如:通过like模糊查询,查找name字段包含姓张,且名有两位的人

mysql> select name,email from employees where name like '张__';
+-----------+--------------------------+
| name      | email                    |
+-----------+--------------------------+
| 张建平    | zhangjianping@tarena.com |
| 张冬梅    | zhangdongmei@tedu.cn     |
| 张淑英    | zhangshuying@tarena.com  |
+-----------+--------------------------+
3 rows in set (0.00 sec)

例如:通过between模糊查询,找到dept_id字段在3和5之间的记录

mysql> select * from departments where dept_id between 3 and 5;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       3 | 运维部    |
|       4 | 开发部    |
|       5 | 测试部    |
+---------+-----------+
3 rows in set (0.00 sec)

例如:通过in模糊查询,找到dept_id字段在列表(1,3,5)中的记录

mysql> select * from departments where dept_id in (1,3,5);
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       3 | 运维部    |
|       5 | 测试部    |
+---------+-----------+
3 rows in set (0.00 sec)

例如:is null 为空(相当于python的None)

在phpMyAdmin的WEB页面中新增一条记录,dept_name字段不填

mysql> select * from departments;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       3 | 运维部    |
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
|       9 | NULL      |
+---------+-----------+
9 rows in set (0.00 sec)
mysql> select * from departments where dept_name is null;    //为空
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       9 | NULL      |
+---------+-----------+
1 row in set (0.00 sec)
mysql> select * from departments where dept_name is not null;    //不为空
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 人事部    |
|       2 | 财务部    |
|       3 | 运维部    |
|       4 | 开发部    |
|       5 | 测试部    |
|       6 | 市场部    |
|       7 | 销售部    |
|       8 | 法务部    |
+---------+-----------+
8 rows in set (0.00 sec)

— 排序

  • 格式:SELECT 查询的字段列表 FROM 表 ORDER BY 排序列表 [asc升序|desc降序];

例如:

mysql> select name,birth_date from employees where birth_date>'19980101';
+-----------+------------+
| name      | birth_date |
+-----------+------------+
| 聂想      | 1999-06-05 |
| 陈斌      | 2000-01-22 |
| 胡秀云    | 2000-05-14 |
| 张倩      | 2000-04-27 |
| 李平      | 1998-07-24 |
| 张伟      | 1999-04-30 |
| 王璐      | 2000-02-01 |
| 符燕      | 1999-12-12 |
| 韩丹      | 1999-06-08 |
| 蒋秀芳    | 2000-04-27 |
| 张宇      | 2000-07-16 |
| 陶红      | 2000-02-21 |
| 崔志强    | 1998-07-27 |
| 苏波      | 1999-12-08 |
| 游静      | 2000-02-14 |
| 王荣      | 1999-11-22 |
+-----------+------------+
16 rows in set (0.00 sec)
 
mysql> select name,birth_date from employees where birth_date>'19980101' order by birth_date;       //默认升序排序
+-----------+------------+
| name      | birth_date |
+-----------+------------+
| 李平      | 1998-07-24 |
| 崔志强    | 1998-07-27 |
| 张伟      | 1999-04-30 |
| 聂想      | 1999-06-05 |
| 韩丹      | 1999-06-08 |
| 王荣      | 1999-11-22 |
| 苏波      | 1999-12-08 |
| 符燕      | 1999-12-12 |
| 陈斌      | 2000-01-22 |
| 王璐      | 2000-02-01 |
| 游静      | 2000-02-14 |
| 陶红      | 2000-02-21 |
| 张倩      | 2000-04-27 |
| 蒋秀芳    | 2000-04-27 |
| 胡秀云    | 2000-05-14 |
| 张宇      | 2000-07-16 |
+-----------+------------+
16 rows in set (0.01 sec)
 
mysql> select name,birth_date from employees where birth_date>'19980101' order by birth_date desc;    //desc降序排序
+-----------+------------+
| name      | birth_date |
+-----------+------------+
| 张宇      | 2000-07-16 |
| 胡秀云    | 2000-05-14 |
| 张倩      | 2000-04-27 |
| 蒋秀芳    | 2000-04-27 |
| 陶红      | 2000-02-21 |
| 游静      | 2000-02-14 |
| 王璐      | 2000-02-01 |
| 陈斌      | 2000-01-22 |
| 符燕      | 1999-12-12 |
| 苏波      | 1999-12-08 |
| 王荣      | 1999-11-22 |
| 韩丹      | 1999-06-08 |
| 聂想      | 1999-06-05 |
| 张伟      | 1999-04-30 |
| 崔志强    | 1998-07-27 |
| 李平      | 1998-07-24 |
+-----------+------------+
16 rows in set (0.00 sec)

例如:查询2015年1月10号员工工资情况

mysql> select date, employee_id, basic, bonus from salary where date='20150110';    //无序
+------------+-------------+-------+-------+
| date       | employee_id | basic | bonus |
+------------+-------------+-------+-------+
| 2015-01-10 |           2 | 17000 | 10000 |
| 2015-01-10 |           3 |  8000 |  2000 |
| 2015-01-10 |           4 | 14000 |  9000 |
| 2015-01-10 |           6 | 14000 | 10000 |
| 2015-01-10 |           7 | 19000 | 10000 |
| 2015-01-10 |         129 | 12000 | 10000 |
| 2015-01-10 |         130 |  8000 |  2000 |
| 2015-01-10 |         132 |  9000 |  8000 |
| 2015-01-10 |         133 |  5000 |  7000 |
+------------+-------------+-------+-------+
95 rows in set (0.00 sec) 

例如:查询2015年1月10号员工工资情况,以基本工资进行降序排列;如果基本工资相同,再以奖金升序排列

mysql> select date, employee_id, basic, bonus from salary where date='20150110' order by basic desc, bonus;
+------------+-------------+-------+-------+
| date       | employee_id | basic | bonus |
+------------+-------------+-------+-------+
| 2015-01-10 |         117 | 21000 |  6000 |
| 2015-01-10 |          68 | 21000 |  8000 |
| 2015-01-10 |          27 | 20000 |  5000 |
| 2015-01-10 |          61 | 20000 | 11000 |
| 2015-01-10 |          29 | 19000 |  1000 |
| 2015-01-10 |          56 | 19000 |  2000 |
| 2015-01-10 |          82 |  7000 |  1000 |
| 2015-01-10 |          26 |  7000 | 11000 |
| 2015-01-10 |          89 |  6000 |  1000 |
| 2015-01-10 |          18 |  6000 | 10000 |
| 2015-01-10 |         133 |  5000 |  7000 |
| 2015-01-10 |         111 |  5000 |  7000 |
+------------+-------------+-------+-------+
95 rows in set (0.00 sec)

例如:查询2015年1月10号员工工资情况,以工资总额为排序条件

mysql> select date,employee_id,basic,bonus,basic+bonus as total from salary where date='20150110' order by total;
+------------+-------------+-------+-------+-------+
| date       | employee_id | basic | bonus | total |
+------------+-------------+-------+-------+-------+
| 2015-01-10 |          89 |  6000 |  1000 |  7000 |
| 2015-01-10 |          30 |  6000 |  1000 |  7000 |
| 2015-01-10 |         114 |  5000 |  3000 |  8000 |
| 2015-01-10 |          93 |  7000 |  1000 |  8000 |
| 2015-01-10 |          82 |  7000 |  1000 |  8000 |
| 2015-01-10 |          90 |  5000 |  4000 |  9000 |
| 2015-01-10 |           3 |  8000 |  2000 | 10000 |
| 2015-01-10 |         109 |  7000 |  3000 | 10000 |
| 2015-01-10 |          70 | 10000 |  1000 | 11000 |
| 2015-01-10 |         133 |  5000 |  7000 | 12000 |
| 2015-01-10 |         111 |  5000 |  7000 | 12000 |
| 2015-01-10 |         117 | 21000 |  6000 | 27000 |
| 2015-01-10 |          52 | 16000 | 11000 | 27000 |
| 2015-01-10 |         122 | 18000 | 10000 | 28000 |
| 2015-01-10 |         105 | 18000 | 10000 | 28000 |
| 2015-01-10 |           7 | 19000 | 10000 | 29000 |
| 2015-01-10 |         106 | 19000 | 10000 | 29000 |
| 2015-01-10 |          61 | 20000 | 11000 | 31000 |
+------------+-------------+-------+-------+-------+
95 rows in set (0.00 sec)

附加:创建练习数据库

- 在确保主机联网、yum可用、的前提下,运行脚本cent7_setup.sh

[root@mysql ~]# yum -y install git wget
[root@mysql ~]# mkdir /var/lib/mygit/
[root@mysql ~]# cd /var/lib/mygit/
[root@mysql mygit]# git clone https://gitee.com/mrzhangzhg/tedu_nsd.git  //克隆下载git
[root@mysql mygit]# ls
tedu_nsd
[root@mysql mygit]# cd tedu_nsd/dbs/mysql_scripts/setup/
[root@mysql setup]# ls
cent7_setup.sh  cent8_setup.sh  gen_data.py  readme.md  tedu_db.sql
[root@mysql setup]# bash cent7_setup.sh     //运行脚本

- 访问数据库,并查看数据库和表信息

[root@mysql setup]# mysql -uroot -pNSD2021@tedu.cn
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| tedu_db            |
| test               |
+--------------------+
5 rows in set (0.00 sec)
MariaDB [(none)]> use tedu_db;
MariaDB [tedu_db]> show tables;
MariaDB [tedu_db]> select count(*) from departments;
+----------+
| count(*) |
+----------+
|        8 |
+----------+
1 row in set (0.00 sec)
MariaDB [tedu_db]> select count(*) from employees;
+----------+
| count(*) |
+----------+
|      133 |
+----------+
1 row in set (0.00 sec)
MariaDB [tedu_db]> select count(*) from salary;
+----------+
| count(*) |
+----------+
|     8069 |
+----------+
1 row in set (0.00 sec)

扩展:数据导入导出

1)数据导入

  • 默认只有root用户有数据导入权限;
  • 命令格式:

注意事项:

- 字段分隔符要与文件一致;

- 表字段类型和字段个数要与文件匹配;

- 导入数据时指定文件的绝对路径;

2)数据导出

  • 命令格式:

① 格式1:

② 格式2:

③ 格式3:

注意事项:

- 导出数据行数由SQL查询决定;

- 导出的是表记录,不包括字段名;

- 自动创建存储数据的文件;

- 存储数据文件,具有唯一性;

 小结:

本篇章节为【第四阶段】RDBMS1-DAY1 的学习笔记,这篇笔记可以初步了解到 数据库简介、构建MySQL服务(配置MySQL、配置phpMyadmin)、SQL语句基础(SQL语法规范、常用MYSQL命令)、SQL语句分类(DQL、DDL、DML、DCL)、基础查询、条件查询、排序。


Tip:毕竟两个人的智慧大于一个人的智慧,如果你不理解本章节的内容或需要相关笔记、视频,可私信小安,请不要害羞和回避,可以向他人请教,花点时间直到你真正的理解。