MariaDB 基础使用实验一 ——马哥教育

最新推荐文章于 2022-12-01 14:16:30 发布

KAIVI-Blog

最新推荐文章于 2022-12-01 14:16:30 发布

阅读量340

点赞数 1

分类专栏： mysql linux 文章标签： Mriadb基础实验数据库实验 centos7 马哥教育

kaivi's blog

本文链接：https://blog.csdn.net/weixin_45651006/article/details/103179804

版权

linux 同时被 2 个专栏收录

14 篇文章 0 订阅

订阅专栏

mysql

10 篇文章 0 订阅

订阅专栏

1.2.1 SQL基本概念

学习了数据库服务 MariaDB 的安装和基本配置，实现操作数据库。使用xshell客户端程序 mysql 连接了已经安装好的 MariaDB 服务。要想在数据库软件中存储和查找数据我们需要使
用 SQL (结构化查询语言) 来操作数据库，一般我们根据功能把 SQL 分为以下四类：

名称作用语法关键字

DQL 数据查询语言 selecte DDL
数据定义语言 create drop alter
DML 数据操作语言 insert delete update DCL
数据控制语言 grant revoke commit rollback

1.2.2 使用 DDL 管理数据库和表

数据库中曾经最高的数据存储单位为库 database，在库中我们可以存放各种表 table，具体的数据我们根据表结构表示为一条一条的数据记录。下面我们在 CentOS7 中使用 yum 安装一个 MariaDB10.2. 来做实验操作演示。

这里安装仓库有多种选择，一个是国外的官方，一个是国内的镜像网站，这里我们选择清华大学的镜像网站

国外官方
 清华大学镜像

1.快速安装一个 MariaDB ，然后启动服务，使用安全脚本给root设置密码，进制 root 用户远程登陆，删除 test 测试数据库

[root@centos7K ~]#vim /etc/yum.repos.d/mariadb.repo 

[root@centos7K ~]#cat /etc/yum.repos.d/mariadb.repo 
[mariadb]
name=MariaDB
baseurl=https://mirrors.tuna.tsinghua.edu.cn/mariadb/yum/10.2/centos7-amd64/
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
这里用的是清华大学镜像仓库

[root@centos7K yum.repos.d]#yum install -y MariaDB-server

[root@centos7K ~]#systemctl start mysqld

查看3306端口是否开启
[root@centos7K ~]#ss -ntl
State      Recv-Q Send-Q             Local Address:Port                            Peer Address:Port              
LISTEN     0      128                            *:22                                         *:*                  
LISTEN     0      100                    127.0.0.1:25                                         *:*                  
LISTEN     0      80                            :::3306                                      :::*                  
LISTEN     0      128                           :::22                                        :::*                  
LISTEN     0      100                          ::1:25                                        :::*                  

[root@centos7K ~]#mysql_secure_installation                #安全加固

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

2.使用 MariaDB 的 root 用户登陆，然后使用 show databases 命令查看当前数据库服务中都有哪些具体的库 database

[root@centos7K ~]#mysql -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.2.29-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [(none)]>

3.使用 use 命令可以指定当前要操作的默认数据库，之后可以使用 show tables 查看当前默认数据库下的所有表信息。 mysql 库是 MariaDB 的内部管理数据库，存放着很多和 MariaDB 相关的信息。该库中的 user 表存放正所有 MariaDB 的用户信息。

MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| column_stats              |
| columns_priv              |
| db                        |
| event                     |
| func                      |
| general_log               |
| gtid_slave_pos            |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| host                      |
| index_stats               |
| innodb_index_stats        |
| innodb_table_stats        |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| roles_mapping             |
| servers                   |
| slow_log                  |
| table_stats               |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
30 rows in set (0.00 sec)

MariaDB [mysql]> desc user;
+------------------------+-----------------------------------+------+-----+----------+-------+
| Field                  | Type                              | Null | Key | Default  | Extra |
+------------------------+-----------------------------------+------+-----+----------+-------+
| Host                   | char(60)                          | NO   | PRI |          |       |
| User                   | char(80)                          | NO   | PRI |          |       |
| Password               | char(41)                          | NO   |     |          |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N        |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N        |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N        |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N        |       |
| File_priv              | enum('N','Y')                     | NO   |     | N        |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N        |       |
| References_priv        | enum('N','Y')                     | NO   |     | N        |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N        |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N        |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N        |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N        |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N        |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N        |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N        |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N        |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N        |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |          |       |
| ssl_cipher             | blob                              | NO   |     | NULL     |       |
| x509_issuer            | blob                              | NO   |     | NULL     |       |
| x509_subject           | blob                              | NO   |     | NULL     |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0        |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0        |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0        |       |
| max_user_connections   | int(11)                           | NO   |     | 0        |       |
| plugin                 | char(64)                          | NO   |     |          |       |
| authentication_string  | text                              | NO   |     | NULL     |       |
| password_expired       | enum('N','Y')                     | NO   |     | N        |       |
| is_role                | enum('N','Y')                     | NO   |     | N        |       |
| default_role           | char(80)                          | NO   |     |          |       |
| max_statement_time     | decimal(12,6)                     | NO   |     | 0.000000 |       |
+------------------------+-----------------------------------+------+-----+----------+-------+
46 rows in set (0.00 sec)

MariaDB [mysql]>

上图中我们使用简单的 SQL 查询语句查看的 mysql 库中 user 表保存的 MariaDB 登陆用户信息，通过表信息我们发现MariaDB 的用户登陆连接是通过用户名，密码，登陆者的 ip 地址来进行验证的，之前执行的安装脚本之所以可以禁止 root 用户远程登陆，就是将 Host 信息设置成了本地 IP 地址实现的，处理之前修改 user 表外，我们可以使用 MariaDB 的用户管理命令来修改用户信息，后面我们讲解 DCL 语句时再详细给大家介绍。通过上面的演示，大家掌握到库，表，数据记录的包含关系即可。下面我们介绍一些 DDL 的基本使用。

3.在数据库定义语言中，我们可以使用 create database 库名称， drop database 库名称的方式创建和删除数据库

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
+--------------------+
4 rows in set (0.00 sec)

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

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [mysql]>

4.有了数据库后，我们可以使用 use 库名称的方式选中默认要操作的库，然后使用 show tables 除了数据库自带的表文件之后发现没有任何自己创建表文件

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

MariaDB [mysql]> create database school;
Query OK, 1 row affected (0.00 sec)

MariaDB [mysql]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [mysql]> use school;
Database changed
MariaDB [school]> show tables;
Empty set (0.00 sec)

MariaDB [school]>

5.下面我们在刚创建的 school 库中创建一个 student 表，创建表时需要设计表结构和字段类型，具体的字段类型大家可以查看一下官方文档，这里我们只是演示 DDL 的创建表语句，创建成功后我们使用 drop table 表名将其删除

MariaDB [school]> 
MariaDB [school]> create table student(
    -> StuID int(10) unsigned not null auto_increment primary key,
    -> Name varchar(50) not null,
    -> Gender enum ('F','M') not null
    -> )engine=InnoDB default charset=utf8;
ERROR 1286 (42000): Unknown storage engine 'InoDB'
Query OK, 0 rows affected (0.01 sec)

MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.01 sec)

MariaDB [school]> 
MariaDB [school]> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| StuID  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name   | varchar(50)      | NO   |     | NULL    |                |
| Gender | enum('F','M')    | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [school]> drop table student;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> show tables;
Empty set (0.00 sec)

MariaDB [school]>

6.对已创建好的表结构进行修改，使用 alter table 表名 add 和 drop 添加和删除字段，使用 alter table 表名 change 和 modify
修改表结构，其中 change 可以改字段名和属性，而 modify 只能修改字段属性

MariaDB [school]> create table student( 
	->StuID int(10) unsigned not null auto_increment primary key, 
	->Name varchar(50) not null , 
	->Gender enum ('F','M') not null,
	->)engine=InnoDB default charset=utf8;
Query OK, 0 rows affected (0.01 sec)

MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

MariaDB [school]> 
MariaDB [school]> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| StuID  | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name   | varchar(50)      | NO   |     | NULL    |                |
| Gender | enum('F','M')    | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

MariaDB [school]> alter table student drop Gender;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [school]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(50)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

MariaDB [school]> alter table student add Phone varchar(50) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [school]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(50)      | NO   |     | NULL    |                |
| Phone | varchar(50)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [school]> 
MariaDB [school]> alter table student change Phone Phone varchar(20) not null;
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [school]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(50)      | NO   |     | NULL    |                |
| Phone | varchar(20)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [school]> alter table student modify Name varchar(30);
Query OK, 0 rows affected (0.01 sec)               
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [school]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(30)      | YES  |     | NULL    |                |
| Phone | varchar(20)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [school]>

1.2.3 使用 DML 管理数据库中的表数据

1.下面我们使用 insert 语句在 school 库的 student 表中添加数据记录

MariaDB [school]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(30)      | YES  |     | NULL    |                |
| Phone | varchar(20)      | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [school]> insert into student (Name,Phone) values ("likai",15600005220);
Query OK, 1 row affected (0.01 sec)

MariaDB [school]> select * from student;
+-------+-------+-------------+
| StuID | Name  | Phone       |
+-------+-------+-------------+
|     1 | likai | 15600005220 |
+-------+-------+-------------+
1 row in set (0.00 sec)

MariaDB [school]> insert into student (Name,Phone) values ("duanxin",15600000946);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> select * from student;
+-------+---------+-------------+
| StuID | Name    | Phone       |
+-------+---------+-------------+
|     1 | likai   | 15600005220 |
|     2 | duanxin | 15600000946 |
+-------+---------+-------------+
2 rows in set (0.00 sec)

MariaDB [school]>

如上图: 在表中添加数据格式为 insert into 表名 [(对应的字段列表，如果不写代表默认所有字段)] values ( 对应字段中每一个字段的具体值 ) ;
查看表中的数据可以使用 select * from 表名，一般不建议写 * 号，他代表所有字段，表数据内容比较少时可以测试使用，具体的下一节讲解 DQL 语句时再细说。

2.使用 delete 删除表中的指定数据记录，delete 后不加 where 条件代表删除所有数据，一般我们操作时肯定要指明过滤条件的,这里用到过滤where语法

MariaDB [school]> 
MariaDB [school]> select * from student;
+-------+---------+-------------+
| StuID | Name    | Phone       |
+-------+---------+-------------+
|     1 | likai   | 15600005220 |
|     2 | duanxin | 15600000946 |
+-------+---------+-------------+
2 rows in set (0.00 sec)

MariaDB [school]> delect from student;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'delect from student' at line 1
MariaDB [school]> delete from student;
Query OK, 2 rows affected (0.00 sec)

MariaDB [school]> select * from student;
Empty set (0.00 sec)

MariaDB [school]> 
MariaDB [school]> 
MariaDB [school]> insert into student (Name,Phone) values("marry",13100003163);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student (Name,Phone) values("alice",13111113163);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student (Name,Phone) values("natasha",13222232163);
Query OK, 1 row affected (0.01 sec)

MariaDB [school]> insert into student (Name,Phone) values("kaivi",13245671163);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student (Name,Phone) values("tom",15645671163),("duanxin",15675455555);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [school]> select * from student;
+-------+---------+-------------+
| StuID | Name    | Phone       |
+-------+---------+-------------+
|     3 | marry   | 13100003163 |
|     4 | alice   | 13111113163 |
|     5 | natasha | 13222232163 |
|     6 | kaivi   | 13245671163 |
|     7 | tom     | 15645671163 |
|     8 | duanxin | 15675455555 |
+-------+---------+-------------+
6 rows in set (0.00 sec)

MariaDB [school]> delete from student where Name="natasha";
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> select * from student;
+-------+---------+-------------+
| StuID | Name    | Phone       |
+-------+---------+-------------+
|     3 | marry   | 13100003163 |
|     4 | alice   | 13111113163 |
|     6 | kaivi   | 13245671163 |
|     7 | tom     | 15645671163 |
|     8 | duanxin | 15675455555 |
+-------+---------+-------------+
5 rows in set (0.00 sec)

MariaDB [school]> delete from student where Phone=15645671163;
Query OK, 1 row affected (0.01 sec)

MariaDB [school]> select * from student;
+-------+---------+-------------+
| StuID | Name    | Phone       |
+-------+---------+-------------+
|     3 | marry   | 13100003163 |
|     4 | alice   | 13111113163 |
|     6 | kaivi   | 13245671163 |
|     8 | duanxin | 15675455555 |
+-------+---------+-------------+
4 rows in set (0.00 sec)

MariaDB [school]> 
MariaDB [school]>

删除表中的数据时，对应的 where 判断条件可以根据实际需要写的很复杂，这里具体的细节我就不再演示了，大家查看
官方文档即可。
3.使用 update 语句修改表中的数据记录

MariaDB [school]> select * from student;
+-------+---------+-------------+
| StuID | Name    | Phone       |
+-------+---------+-------------+
|     3 | marry   | 13100003163 |
|     4 | alice   | 13111113163 |
|     6 | kaivi   | 13245671163 |
|     8 | duanxin | 15675455555 |
+-------+---------+-------------+
4 rows in set (0.00 sec)

MariaDB [school]> update student set Name="likai" where Phone=13245671163;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [school]> select * from student;
+-------+---------+-------------+
| StuID | Name    | Phone       |
+-------+---------+-------------+
|     3 | marry   | 13100003163 |
|     4 | alice   | 13111113163 |
|     6 | likai   | 13245671163 |
|     8 | duanxin | 15675455555 |
+-------+---------+-------------+
4 rows in set (0.00 sec)

MariaDB [school]>

和删除语句类似，修改语句也要指明被修改的数据的过滤条件，然后指明要修改的字段内容即可，格式为 update 表名 set 字段=值 where 过滤条件

1.2.4 使用 DQL 单表数据查询

数据库查询可以使用 select 完成，格式为 select 字段列表 from 表名 where 过滤条件，其中字段列表可以使用 * 号代表所有字段

1.下面我们演示一下简单数据查询:

MariaDB [school]> 
MariaDB [school]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
+--------------------+
4 rows in set (0.00 sec)

MariaDB [school]> use school
Database changed
MariaDB [school]> 
MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

MariaDB [school]> select * from student;
+-------+---------+-------------+
| StuID | Name    | Phone       |
+-------+---------+-------------+
|     3 | marry   | 13100003163 |
|     4 | alice   | 13111113163 |
|     6 | likai   | 13245671163 |
|     8 | duanxin | 15675455555 |
+-------+---------+-------------+
4 rows in set (0.00 sec)

MariaDB [school]> select StuID,Phone from student;
+-------+-------------+
| StuID | Phone       |
+-------+-------------+
|     3 | 13100003163 |
|     4 | 13111113163 |
|     6 | 13245671163 |
|     8 | 15675455555 |
+-------+-------------+
4 rows in set (0.00 sec)

MariaDB [school]> select StuID,Phone from student where StuID>5;
+-------+-------------+
| StuID | Phone       |
+-------+-------------+
|     6 | 13245671163 |
|     8 | 15675455555 |
+-------+-------------+
2 rows in set (0.00 sec)

MariaDB [school]>

2.除了简单的数据查询，我们还可以对结果进行排序，使用 order by 排序字段名即可，默认顺序，使用 desc 实现逆序

MariaDB [school]> select * from student;
+-------+---------+-------------+
| StuID | Name    | Phone       |
+-------+---------+-------------+
|     3 | marry   | 13100003163 |
|     4 | alice   | 13111113163 |
|     6 | likai   | 13245671163 |
|     8 | duanxin | 15675455555 |
+-------+---------+-------------+
4 rows in set (0.00 sec)

MariaDB [school]> alter table student add Age tinyint not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [school]> select * from student;
+-------+---------+-------------+-----+
| StuID | Name    | Phone       | Age |
+-------+---------+-------------+-----+
|     3 | marry   | 13100003163 |   0 |
|     4 | alice   | 13111113163 |   0 |
|     6 | likai   | 13245671163 |   0 |
|     8 | duanxin | 15675455555 |   0 |
+-------+---------+-------------+-----+
4 rows in set (0.00 sec)

MariaDB [school]> desc student;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| StuID | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(30)      | YES  |     | NULL    |                |
| Phone | varchar(20)      | NO   |     | NULL    |                |
| Age   | tinyint(4)       | NO   |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [school]> insert into student (Name,Phone,Age) values ("laowang",13487513163,35);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student (Name,Phone,Age) values ("mage",13486347777,30);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student (Name,Phone,Age) values ("kaivi",13433347777,18);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> select * from student;
+-------+---------+-------------+-----+
| StuID | Name    | Phone       | Age |
+-------+---------+-------------+-----+
|     3 | marry   | 13100003163 |   0 |
|     4 | alice   | 13111113163 |   0 |
|     6 | likai   | 13245671163 |   0 |
|     8 | duanxin | 15675455555 |   0 |
|     9 | laowang | 13487513163 |  35 |
|    10 | mage    | 13486347777 |  30 |
|    11 | kaivi   | 13433347777 |  18 |
+-------+---------+-------------+-----+
7 rows in set (0.00 sec)

MariaDB [school]> select * from student order by Age;
+-------+---------+-------------+-----+
| StuID | Name    | Phone       | Age |
+-------+---------+-------------+-----+
|     3 | marry   | 13100003163 |   0 |
|     4 | alice   | 13111113163 |   0 |
|     6 | likai   | 13245671163 |   0 |
|     8 | duanxin | 15675455555 |   0 |
|    11 | kaivi   | 13433347777 |  18 |
|    10 | mage    | 13486347777 |  30 |
|     9 | laowang | 13487513163 |  35 |
+-------+---------+-------------+-----+
7 rows in set (0.00 sec)

MariaDB [school]> select * from student order by Age desc;
+-------+---------+-------------+-----+
| StuID | Name    | Phone       | Age |
+-------+---------+-------------+-----+
|     9 | laowang | 13487513163 |  35 |
|    10 | mage    | 13486347777 |  30 |
|    11 | kaivi   | 13433347777 |  18 |
|     3 | marry   | 13100003163 |   0 |
|     4 | alice   | 13111113163 |   0 |
|     6 | likai   | 13245671163 |   0 |
|     8 | duanxin | 15675455555 |   0 |
+-------+---------+-------------+-----+
7 rows in set (0.00 sec)

MariaDB [school]>

询时可以限定只需要返回结果的前几条记录，使用 limit 记录调试即可

MariaDB [school]> 
MariaDB [school]> select * from student;
+-------+---------+-------------+-----+
| StuID | Name    | Phone       | Age |
+-------+---------+-------------+-----+
|     3 | marry   | 13100003163 |   0 |
|     4 | alice   | 13111113163 |   0 |
|     6 | likai   | 13245671163 |   0 |
|     8 | duanxin | 15675455555 |   0 |
|     9 | laowang | 13487513163 |  35 |
|    10 | mage    | 13486347777 |  30 |
|    11 | kaivi   | 13433347777 |  18 |
+-------+---------+-------------+-----+
7 rows in set (0.00 sec)

MariaDB [school]> select * from student limit 2;
+-------+-------+-------------+-----+
| StuID | Name  | Phone       | Age |
+-------+-------+-------------+-----+
|     3 | marry | 13100003163 |   0 |
|     4 | alice | 13111113163 |   0 |
+-------+-------+-------------+-----+
2 rows in set (0.00 sec)

MariaDB [school]>

查询时可以根据业务的需要对指定重复的字段进行去重, 使用 select distinct 去重的字段 from 表名 where 判断条件即可

MariaDB [school]> select * from student;
+-------+---------+-------------+-----+
| StuID | Name    | Phone       | Age |
+-------+---------+-------------+-----+
|     3 | marry   | 13100003163 |   0 |
|     4 | alice   | 13111113163 |   0 |
|     6 | likai   | 13245671163 |   0 |
|     8 | duanxin | 15675455555 |   0 |
|     9 | laowang | 13487513163 |  35 |
|    10 | mage    | 13486347777 |  30 |
|    11 | kaivi   | 13433347777 |  18 |
+-------+---------+-------------+-----+
7 rows in set (0.00 sec)

MariaDB [school]> insert into student (Name,Phone,Age) values("kaivi",13433347777,18);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student (Name,Phone,Age) values("mage",13486367777,30);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student (Name,Phone,Age) values("laowang",13487513163,35);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> select * from student;
+-------+---------+-------------+-----+
| StuID | Name    | Phone       | Age |
+-------+---------+-------------+-----+
|     3 | marry   | 13100003163 |   0 |
|     4 | alice   | 13111113163 |   0 |
|     6 | likai   | 13245671163 |   0 |
|     8 | duanxin | 15675455555 |   0 |
|     9 | laowang | 13487513163 |  35 |
|    10 | mage    | 13486347777 |  30 |
|    11 | kaivi   | 13433347777 |  18 |
|    12 | kaivi   | 13433347777 |  18 |
|    13 | mage    | 13486367777 |  30 |
|    14 | laowang | 13487513163 |  35 |
+-------+---------+-------------+-----+
10 rows in set (0.00 sec)

MariaDB [school]> 
MariaDB [school]> select distinct Name from student;
+---------+
| Name    |
+---------+
| marry   |
| alice   |
| likai   |
| duanxin |
| laowang |
| mage    |
| kaivi   |
+---------+
7 rows in set (0.00 sec)

MariaDB [school]> select distinct Phone from student;
+-------------+
| Phone       |
+-------------+
| 13100003163 |
| 13111113163 |
| 13245671163 |
| 15675455555 |
| 13487513163 |
| 13486347777 |
| 13433347777 |
| 13486367777 |
+-------------+
8 rows in set (0.00 sec)

MariaDB [school]> select distinct Name,Phone from student;
+---------+-------------+
| Name    | Phone       |
+---------+-------------+
| marry   | 13100003163 |
| alice   | 13111113163 |
| likai   | 13245671163 |
| duanxin | 15675455555 |
| laowang | 13487513163 |
| mage    | 13486347777 |
| kaivi   | 13433347777 |
| mage    | 13486367777 |
+---------+-------------+
8 rows in set (0.00 sec)

MariaDB [school]> select distinct * from student;
+-------+---------+-------------+-----+
| StuID | Name    | Phone       | Age |
+-------+---------+-------------+-----+
|     3 | marry   | 13100003163 |   0 |
|     4 | alice   | 13111113163 |   0 |
|     6 | likai   | 13245671163 |   0 |
|     8 | duanxin | 15675455555 |   0 |
|     9 | laowang | 13487513163 |  35 |
|    10 | mage    | 13486347777 |  30 |
|    11 | kaivi   | 13433347777 |  18 |
|    12 | kaivi   | 13433347777 |  18 |
|    13 | mage    | 13486367777 |  30 |
|    14 | laowang | 13487513163 |  35 |
+-------+---------+-------------+-----+
10 rows in set (0.00 sec)
这里全部都没有去重留下是因为前面的StuID都不一样
MariaDB [school]>

上图中 distinct 后可以是一个字段，也可以是多个字段，当为多个字段时代表他们的组合不能重复， * 号代表所有字段的组合不能重复

5.查询时我们还可以对数据进行分组，比如我们在查询时以 student 表中的姓名进行分组，然后处理改组里的数据，在 where 后使用 group by 分组的字段名即可实现

MariaDB [school]> select * from student;
+-------+---------+-------------+-----+
| StuID | Name    | Phone       | Age |
+-------+---------+-------------+-----+
|     3 | marry   | 13100003163 |   0 |
|     4 | alice   | 13111113163 |   0 |
|     6 | likai   | 13245671163 |   0 |
|     8 | duanxin | 15675455555 |   0 |
|     9 | laowang | 13487513163 |  35 |
|    10 | mage    | 13486347777 |  30 |
|    11 | kaivi   | 13433347777 |  18 |
|    12 | kaivi   | 13433347777 |  18 |
|    13 | mage    | 13486367777 |  30 |
|    14 | laowang | 13487513163 |  35 |
+-------+---------+-------------+-----+
10 rows in set (0.00 sec)

MariaDB [school]> select * from student group by Name;
+-------+---------+-------------+-----+
| StuID | Name    | Phone       | Age |
+-------+---------+-------------+-----+
|     4 | alice   | 13111113163 |   0 |
|     8 | duanxin | 15675455555 |   0 |
|    11 | kaivi   | 13433347777 |  18 |
|     9 | laowang | 13487513163 |  35 |
|     6 | likai   | 13245671163 |   0 |
|    10 | mage    | 13486347777 |  30 |
|     3 | marry   | 13100003163 |   0 |
+-------+---------+-------------+-----+
7 rows in set (0.00 sec)

MariaDB [school]> select count(StuID) AS count_num, Name,Phone,Age from student group by Name;
+-----------+---------+-------------+-----+
| count_num | Name    | Phone       | Age |
+-----------+---------+-------------+-----+
|         1 | alice   | 13111113163 |   0 |
|         1 | duanxin | 15675455555 |   0 |
|         2 | kaivi   | 13433347777 |  18 |
|         2 | laowang | 13487513163 |  35 |
|         1 | likai   | 13245671163 |   0 |
|         2 | mage    | 13486347777 |  30 |
|         1 | marry   | 13100003163 |   0 |
+-----------+---------+-------------+-----+
7 rows in set (0.00 sec)

MariaDB [school]>

从上图我们了解到分组后显示的字段值默认为分组中第一次出现的记录信息值，因此大多数要显示的字段都需要进行运算

之前我们使用的 where 可以对单行数据进行过滤，下面我们介绍一些 having ，他可以对分组处理后的数据再次进行过滤

MariaDB [school]> select count(StuID) AS count_num, Name,Phone,Age from student group by Name;
+-----------+---------+-------------+-----+
| count_num | Name    | Phone       | Age |
+-----------+---------+-------------+-----+
|         1 | alice   | 13111113163 |   0 |
|         1 | duanxin | 15675455555 |   0 |
|         2 | kaivi   | 13433347777 |  18 |
|         2 | laowang | 13487513163 |  35 |
|         1 | likai   | 13245671163 |   0 |
|         2 | mage    | 13486347777 |  30 |
|         1 | marry   | 13100003163 |   0 |
+-----------+---------+-------------+-----+
7 rows in set (0.00 sec)

MariaDB [school]> select count(StuID) AS count_num, Name,Phone,Age from student group by Name having Age=0;
+-----------+---------+-------------+-----+
| count_num | Name    | Phone       | Age |
+-----------+---------+-------------+-----+
|         1 | alice   | 13111113163 |   0 |
|         1 | duanxin | 15675455555 |   0 |
|         1 | likai   | 13245671163 |   0 |
|         1 | marry   | 13100003163 |   0 |
+-----------+---------+-------------+-----+
4 rows in set (0.00 sec)

MariaDB [school]> select count(StuID) AS count_num, Name,Phone,Age from student group by Name having Name="kaivi";
+-----------+-------+-------------+-----+
| count_num | Name  | Phone       | Age |
+-----------+-------+-------------+-----+
|         2 | kaivi | 13433347777 |  18 |
+-----------+-------+-------------+-----+
1 row in set (0.00 sec)

MariaDB [school]>

1.2.5 使用 DQL 进行多表查询数据

1.多表查询可以将多张表中的数据按照一定的组合关系拼接成一章表后进行查询操作，下面我们先创建2张表并插入对应的数据

MariaDB [school]> show tables;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

MariaDB [school]> create table student2( 
	->stuID int(10) unsigned not null auto_increment primary key, 
	->name varchar(20) not null, 
	->gender enum("f","m") not null,
	->classid int(10) not null);
 
Query OK, 0 rows affected (0.01 sec)

MariaDB [school]> create table class( 
	->classID int(10) unsigned not null auto_increment primary key, 
	->className varchar(20) not null, 
	->courseInfo varchar(200) not null);

Query OK, 0 rows affected (0.01 sec)

MariaDB [school]> desc student2;
+---------+------------------+------+-----+---------+----------------+
| Field   | Type             | Null | Key | Default | Extra          |
+---------+------------------+------+-----+---------+----------------+
| stuID   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name    | varchar(20)      | NO   |     | NULL    |                |
| gender  | enum('f','m')    | NO   |     | NULL    |                |
| classid | int(10)          | NO   |     | NULL    |                |
+---------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

MariaDB [school]> desc class
    -> ;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| classID    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| className  | varchar(20)      | NO   |     | NULL    |                |
| courseInfo | varchar(200)     | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

MariaDB [school]> 

MariaDB [school]> insert into student2 values(1,"likai","m",1);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student2 values(2,"kaivi","m",2);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student2 values(3,"xinxin","f",1);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student2 values(4,"duanxin","f",3);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student2 values(5,"laowang","m",3);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into student2 values(6,"LBJ","f",5);
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> select * from student2;
+-------+---------+--------+---------+
| stuID | name    | gender | classid |
+-------+---------+--------+---------+
|     1 | likai   | m      |       1 |
|     2 | kaivi   | m      |       2 |
|     3 | xinxin  | f      |       1 |
|     4 | duanxin | f      |       3 |
|     5 | laowang | m      |       3 |
|     6 | LBJ     | f      |       5 |
+-------+---------+--------+---------+
6 rows in set (0.00 sec)

MariaDB [school]> 
MariaDB [school]> insert into class values(1,"linux","R&D");
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into class values(2,"python","R&D");
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> insert into class values(3,"java","linux and R&D");
Query OK, 1 row affected (0.01 sec)

MariaDB [school]> insert into class values(4,"C++","ARCH");
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> select * from class;
+---------+-----------+---------------+
| classID | className | courseInfo    |
+---------+-----------+---------------+
|       1 | linux     | R&D           |
|       2 | python    | R&D           |
|       3 | java      | linux and R&D |
|       4 | C++       | ARCH          |
+---------+-----------+---------------+
4 rows in set (0.00 sec)

MariaDB [school]>

2.多表连接查询中的内连接查询,也叫等值连接使用 select 字段名 from 表名1 inner join 表名2 on 表名1.字段=表名2.字段

MariaDB [school]> select * from student2;
+-------+---------+--------+---------+
| stuID | name    | gender | classid |
+-------+---------+--------+---------+
|     1 | likai   | m      |       1 |
|     2 | kaivi   | m      |       2 |
|     3 | xinxin  | f      |       1 |
|     4 | duanxin | f      |       3 |
|     5 | laowang | m      |       3 |
|     6 | LBJ     | f      |       5 |
+-------+---------+--------+---------+
6 rows in set (0.00 sec)

MariaDB [school]> select * from class;
+---------+-----------+---------------+
| classID | className | courseInfo    |
+---------+-----------+---------------+
|       1 | linux     | R&D           |
|       2 | python    | R&D           |
|       3 | java      | linux and R&D |
|       4 | C++       | ARCH          |
+---------+-----------+---------------+
4 rows in set (0.00 sec)

MariaDB [school]> select * from student2 inner join class on student2.classid = class.classID;
+-------+---------+--------+---------+---------+-----------+---------------+
| stuID | name    | gender | classid | classID | className | courseInfo    |
+-------+---------+--------+---------+---------+-----------+---------------+
|     1 | likai   | m      |       1 |       1 | linux     | R&D           |
|     3 | xinxin  | f      |       1 |       1 | linux     | R&D           |
|     2 | kaivi   | m      |       2 |       2 | python    | R&D           |
|     4 | duanxin | f      |       3 |       3 | java      | linux and R&D |
|     5 | laowang | m      |       3 |       3 | java      | linux and R&D |
+-------+---------+--------+---------+---------+-----------+---------------+
5 rows in set (0.00 sec)

MariaDB [school]> 
MariaDB [school]> select * from class inner join student2 on student2.classid = class.classID;
+---------+-----------+---------------+-------+---------+--------+---------+
| classID | className | courseInfo    | stuID | name    | gender | classid |
+---------+-----------+---------------+-------+---------+--------+---------+
|       1 | linux     | R&D           |     1 | likai   | m      |       1 |
|       1 | linux     | R&D           |     3 | xinxin  | f      |       1 |
|       2 | python    | R&D           |     2 | kaivi   | m      |       2 |
|       3 | java      | linux and R&D |     4 | duanxin | f      |       3 |
|       3 | java      | linux and R&D |     5 | laowang | m      |       3 |
+---------+-----------+---------------+-------+---------+--------+---------+
5 rows in set (0.00 sec)

MariaDB [school]>

上图中注意数字的显示格式，内连接查询两个表，inner join 前面的表为参照表，按照参照表的顺序匹配生成
查询结果，参照表中如果有不匹配 on 条件的记录，则跳过该记录。

3.外连接查询，左外链接查询和内连接查询的差异在于，如果参照表中有不匹配 on 条件的记录，则也会生成对应的数据，结果为参照表信息和空信息组成查询记录。使用表1 left join 表2 on 条件即可

MariaDB [school]> select * from student2;
+-------+---------+--------+---------+
| stuID | name    | gender | classid |
+-------+---------+--------+---------+
|     1 | likai   | m      |       1 |
|     2 | kaivi   | m      |       2 |
|     3 | xinxin  | f      |       1 |
|     4 | duanxin | f      |       3 |
|     5 | laowang | m      |       3 |
|     6 | LBJ     | f      |       5 |
+-------+---------+--------+---------+
6 rows in set (0.00 sec)

MariaDB [school]> select * from class;
+---------+-----------+---------------+
| classID | className | courseInfo    |
+---------+-----------+---------------+
|       1 | linux     | R&D           |
|       2 | python    | R&D           |
|       3 | java      | linux and R&D |
|       4 | C++       | ARCH          |
+---------+-----------+---------------+
4 rows in set (0.00 sec)

MariaDB [school]> select * from student2 left join class on student2.classid = class.classID;
+-------+---------+--------+---------+---------+-----------+---------------+
| stuID | name    | gender | classid | classID | className | courseInfo    |
+-------+---------+--------+---------+---------+-----------+---------------+
|     1 | likai   | m      |       1 |       1 | linux     | R&D           |
|     2 | kaivi   | m      |       2 |       2 | python    | R&D           |
|     3 | xinxin  | f      |       1 |       1 | linux     | R&D           |
|     4 | duanxin | f      |       3 |       3 | java      | linux and R&D |
|     5 | laowang | m      |       3 |       3 | java      | linux and R&D |
|     6 | LBJ     | f      |       5 |    NULL | NULL      | NULL          |
+-------+---------+--------+---------+---------+-----------+---------------+
6 rows in set (0.00 sec)

MariaDB [school]> select * from class left join student2 on student2.classid = class.classID;
+---------+-----------+---------------+-------+---------+--------+---------+
| classID | className | courseInfo    | stuID | name    | gender | classid |
+---------+-----------+---------------+-------+---------+--------+---------+
|       1 | linux     | R&D           |     1 | likai   | m      |       1 |
|       2 | python    | R&D           |     2 | kaivi   | m      |       2 |
|       1 | linux     | R&D           |     3 | xinxin  | f      |       1 |
|       3 | java      | linux and R&D |     4 | duanxin | f      |       3 |
|       3 | java      | linux and R&D |     5 | laowang | m      |       3 |
|       4 | C++       | ARCH          |  NULL | NULL    | NULL   |    NULL |
+---------+-----------+---------------+-------+---------+--------+---------+
6 rows in set (0.00 sec)

MariaDB [school]>

4.右外连接查询和上面的左外链接查询参照表不一样，左外链接以左边的表为参照，右外连接以右面的表为参照
使用表1 right join 表2 on 条件即可

MariaDB [school]> select * from student2;
+-------+---------+--------+---------+
| stuID | name    | gender | classid |
+-------+---------+--------+---------+
|     1 | likai   | m      |       1 |
|     2 | kaivi   | m      |       2 |
|     3 | xinxin  | f      |       1 |
|     4 | duanxin | f      |       3 |
|     5 | laowang | m      |       3 |
|     6 | LBJ     | f      |       5 |
+-------+---------+--------+---------+
6 rows in set (0.00 sec)

MariaDB [school]> select * from class;
+---------+-----------+---------------+
| classID | className | courseInfo    |
+---------+-----------+---------------+
|       1 | linux     | R&D           |
|       2 | python    | R&D           |
|       3 | java      | linux and R&D |
|       4 | C++       | ARCH          |
+---------+-----------+---------------+
4 rows in set (0.00 sec)

MariaDB [school]> select * from student2 right join class on student2.classid = class.classID;
+-------+---------+--------+---------+---------+-----------+---------------+
| stuID | name    | gender | classid | classID | className | courseInfo    |
+-------+---------+--------+---------+---------+-----------+---------------+
|     1 | likai   | m      |       1 |       1 | linux     | R&D           |
|     2 | kaivi   | m      |       2 |       2 | python    | R&D           |
|     3 | xinxin  | f      |       1 |       1 | linux     | R&D           |
|     4 | duanxin | f      |       3 |       3 | java      | linux and R&D |
|     5 | laowang | m      |       3 |       3 | java      | linux and R&D |
|  NULL | NULL    | NULL   |    NULL |       4 | C++       | ARCH          |
+-------+---------+--------+---------+---------+-----------+---------------+
6 rows in set (0.00 sec)

MariaDB [school]> select * from class right join student2 on student2.classid = class.classID;
+---------+-----------+---------------+-------+---------+--------+---------+
| classID | className | courseInfo    | stuID | name    | gender | classid |
+---------+-----------+---------------+-------+---------+--------+---------+
|       1 | linux     | R&D           |     1 | likai   | m      |       1 |
|       2 | python    | R&D           |     2 | kaivi   | m      |       2 |
|       1 | linux     | R&D           |     3 | xinxin  | f      |       1 |
|       3 | java      | linux and R&D |     4 | duanxin | f      |       3 |
|       3 | java      | linux and R&D |     5 | laowang | m      |       3 |
|    NULL | NULL      | NULL          |     6 | LBJ     | f      |       5 |
+---------+-----------+---------------+-------+---------+--------+---------+
6 rows in set (0.00 sec)

MariaDB [school]>

5.嵌套查询，用一次查询的结果作为查询匹配条件的范围或者作为一个新表来被再次查询

MariaDB [school]> select * from student2;
+-------+---------+--------+---------+
| stuID | name    | gender | classid |
+-------+---------+--------+---------+
|     1 | likai   | m      |       1 |
|     2 | kaivi   | m      |       2 |
|     3 | xinxin  | f      |       1 |
|     4 | duanxin | f      |       3 |
|     5 | laowang | m      |       3 |
|     6 | LBJ     | f      |       5 |
+-------+---------+--------+---------+
6 rows in set (0.00 sec)

MariaDB [school]> select * from class;
+---------+-----------+---------------+
| classID | className | courseInfo    |
+---------+-----------+---------------+
|       1 | linux     | R&D           |
|       2 | python    | R&D           |
|       3 | java      | linux and R&D |
|       4 | C++       | ARCH          |
+---------+-----------+---------------+
4 rows in set (0.00 sec)

MariaDB [school]> select * from student2 where student2.classid in (select classID from class where classID < 3);
+-------+--------+--------+---------+
| stuID | name   | gender | classid |
+-------+--------+--------+---------+
|     1 | likai  | m      |       1 |
|     2 | kaivi  | m      |       2 |
|     3 | xinxin | f      |       1 |
+-------+--------+--------+---------+
3 rows in set (0.00 sec)

MariaDB [school]> 


MariaDB [school]> select * from student2 where student2.classid in (select classID from class where classID < 3);
+-------+--------+--------+---------+
| stuID | name   | gender | classid |
+-------+--------+--------+---------+
|     1 | likai  | m      |       1 |
|     2 | kaivi  | m      |       2 |
|     3 | xinxin | f      |       1 |
+-------+--------+--------+---------+
3 rows in set (0.00 sec)

MariaDB [school]> 


MariaDB [school]> 
MariaDB [school]> select fullinfo.stuID,fullinfo.name,fullinfo.className from (
    -> select student2.stuID,student2.name,student2.gender,class.className from student2 inner join class on student2.classid = class.classID) as fullinfo;
+-------+---------+-----------+
| stuID | name    | className |
+-------+---------+-----------+
|     1 | likai   | linux     |
|     3 | xinxin  | linux     |
|     2 | kaivi   | python    |
|     4 | duanxin | java      |
|     5 | laowang | java      |
+-------+---------+-----------+
5 rows in set (0.00 sec)

6.合并查询结果，当多个查询的结果又相同个数和内容的字段时，使用 union 会将这些查询结果去重合并

MariaDB [school]> select * from student2;
+-------+---------+--------+---------+
| stuID | name    | gender | classid |
+-------+---------+--------+---------+
|     1 | likai   | m      |       1 |
|     2 | kaivi   | m      |       2 |
|     3 | xinxin  | f      |       1 |
|     4 | duanxin | f      |       3 |
|     5 | laowang | m      |       3 |
|     6 | LBJ     | f      |       5 |
+-------+---------+--------+---------+
6 rows in set (0.00 sec)

MariaDB [school]> select * from student2 where stuID <=3;
+-------+--------+--------+---------+
| stuID | name   | gender | classid |
+-------+--------+--------+---------+
|     1 | likai  | m      |       1 |
|     2 | kaivi  | m      |       2 |
|     3 | xinxin | f      |       1 |
+-------+--------+--------+---------+
3 rows in set (0.00 sec)

MariaDB [school]> select * from student2 where stuID >=3;
+-------+---------+--------+---------+
| stuID | name    | gender | classid |
+-------+---------+--------+---------+
|     3 | xinxin  | f      |       1 |
|     4 | duanxin | f      |       3 |
|     5 | laowang | m      |       3 |
|     6 | LBJ     | f      |       5 |
+-------+---------+--------+---------+
4 rows in set (0.00 sec)

MariaDB [school]> select * from student2 where stuID <=3 union select * from student2 where stuID >=3;
+-------+---------+--------+---------+
| stuID | name    | gender | classid |
+-------+---------+--------+---------+
|     1 | likai   | m      |       1 |
|     2 | kaivi   | m      |       2 |
|     3 | xinxin  | f      |       1 |
|     4 | duanxin | f      |       3 |
|     5 | laowang | m      |       3 |
|     6 | LBJ     | f      |       5 |
+-------+---------+--------+---------+
6 rows in set (0.00 sec)

MariaDB [school]>

上图中两次的单独查询结果中都包含 stuID 为 3 的记录，使用 union 后实现了去重合并的效果

KAIVI-Blog

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MariaDB 基础使用实验一 ——马哥教育

1.2.1 SQL基本概念学习了数据库服务 MariaDB 的安装和基本配置，实现操作数据库。使用xshell客户端程序 mysql 连接了已经安装好的 MariaDB 服务。要想在数据库软件中存储和查找数据我们需要使用 SQL (结构化查询语言) 来操作数据库，一般我们根据功能把 SQL 分为以下四类：名称作用语法关键字DQL 数据查询语言 selecte DDL数据定义语言 c...
复制链接

扫一扫