大数据开发基础入门与项目实战(二)Java Web数据可视化之1.MySQL基础和SQL入门

18 篇文章 5 订阅

前言

本文主要介绍了MySQL的基础和SQL语法入门,主要包括数据库的概念、MySQL的安装及配置和SQL。

1.数据库的概念

(1)数据库的基本概念

当有很多数据时,可以通过IO流保存到文件中,但是要查询一条数据时,可能就需要读取整个文件再判断,显然当数据量很大时、效率会很低,此时就可以使用数据库代替。

数据库(DataBase)就是存储和管理数据的仓库,其本质还是一个文件系统,以文件的方式将数据保存在电脑上,但是组织形式与普通文件不同。

不同数据存储方式的比较如下:

存储方式优点缺点
内存速度快不能够永久保存,数据是临时状态的
文件数据是可以永久保存的使用IO流操作文件,不方便
数据库1.数据可以永久保存;
2.方便存储和管理数据;
3.使用统一的方式操作数据库(SQL)。
占用资源,有些数据库需要付费(比如Oracle数据库)

通过上面的比较,我们可以看出,使用数据库存储数据,用户可以非常方便地对数据库中的数据进行增加、删除、修改及查询操作。

(2)常见的数据库软件

数据库是一个抽象概念,数据库软件是对数据库的具体实现。

开发中常用的数据库如下:

数据库名介绍
MySQL数据库开源免费的数据库;
因为免费开源、运作简单的特点,常作为中小型项目的数据库首选;
MySQL1996年开始运作,目前已经被Oracle公司收购,MySQL6.x开始收费。
Oracle数据库收费的大型数据库,Oracle公司的核心产品;
安全性高。
DB2IBM公司的数据库产品,收费的超大型数据库;
常在银行系统中使用。
SQL ServerMicroSoft 微软公司收费的中型的数据库;
C#、.net等语言常使用;
只能运行在Windows机器上,扩展性、稳定性、安全性、性能都表现平平。

其中,MySQL的使用范围最广,选择MySQL的原因如下:

  • 功能强大,足以应付Web应用开发

  • 开源、免费

最新的数据库排行如下:

database rank

完整的数据库排行可查看https://db-engines.com/en/ranking

2.MySQL的安装及配置

(1)MySQL的安装

安装MySQL可以到MySQL官网下载安装包进行安装,可以直接点击https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-8.0.26.0.msi下载最新版或者https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-5.7.35.0.msi下载5.7版本,下载之后点击并选择下一步进行默认方式的安装即可。

除了直接通过下载安装包进行安装,也可以选择集成方式进行安装,即通过PHPStudy、xampp等集成工具来安装,这种方式在安装和使用时都更方便。

(2)MySQL的卸载

如果通过安装包安装MySQL,卸载时直接在控制面板\程序\程序和功能中选择MySQL安装程序(MySQL Installer Community)和MySQL服务器(MySQL Server)进行卸载即可。

同时还需要手动删除残余文件,一般为MySQL的安装路径,直接删除掉MySQL顶级目录即可,例如C:/ProgramData/MySQL

(3)MySQL配置环境变量

在配置环境变量前需要获取MySQL的安装路径,例如E:\PhpStudy\phpstudy_pro\Extensions\MySQL5.7.26\bin

MySQL配置环境变量有2种方式:

  • 直接添加安装路径到系统变量的Path中

    此时,配置路径应包含bin目录,如下:

sql path

  • 定义MYSQL_HMOE并添加到Path中

    此时MYSQL_HMOE变量的值不包含bin目录,如下:

mysql home

    再将MYSQL_PATH添加到Path中,如下:

mysql path

配置好之后,就可以在新开的命令行窗口中输入mysql -u username -p连接数据库和进行其他操作了。

(4)MySQL的启动和关闭

Windows中有3种方式启动MySQL:

  • Windows服务启动 MySQL

    之前安装的MySQL实质上是MySQL服务器,安装好之后就会在电脑(Windows)上注册到服务列表,所以可以在服务列表中开启服务。

    1. 打开服务列表

        可以先Win键 + R键打开Run窗口,然后输入services.msc打开服务列表窗口。

    2. 操作MySQL服务

mysql service

  • 命令行窗口操作MySQL

    以管理员身份发开命令行窗口:

    - 启动服务

        执行net start mysql命令启动MySQL服务,输出:

The MySQL service is starting.
The MySQL service was started successfully.

        即说明启动MySQL成功。

    - 停止服务

        执行net stop mysql命令停止MySQL服务,输出:

The MySQL service is stopping.             
The MySQL service was stopped successfully.

        即说明停止MySQL服务成功。

  • 集成工具操作MySQL

    前面两种方式的前提都是需要将MySQL安装到系统服务。

    使用集成工具也可以启动MySQL,如下:

mysql tool

    点击即可切换启动或者停止MySQL。

(5)命令行登录MySQL

MySQL是一个需要账户名密码登录的数据库,登录后使用,它提供了一个默认的root账号,使用安装时设置的密码即可登录。

命令行登录MySQL的方式如下:

命令说明
mysql -u 用户名 -p 密码使用指定用户名和密码登录当前计算机中的MySQL数据库
mysql -u 用户名 -p使用指定用户名登录当前计算机中的MySQL数据库,回车后再输入密码,更安全
mysql -h 主机IP -u 用户名 -p 密码-h 指定IP 方式进行登录
exit/quit退出登录

例如:

mysql -uroot -p
mysql -uroot -p -h127.0.0.1
exit

(6)SQLYog的介绍和安装

如果没有一定的基础,使用命令行的方式登录和操作数据库可能会有一定的困难,此时可以选择使用可视化管理工具,例如SQLYog、Navicat等,以SQLYog为例,SQLyog是业界著名的Webyog公司出品的一款简洁高效、功能强大的图形化MySQL数据库管理工具。使用 SQLyog 可以快速直观地从世界的任何角落通过网络来维护远端的 MySQL 数据库。

直接下载安装即可使用,打开后,输入IP、端口、用户名和密码即可连接,界面如下:

mysql qlyog

可以看到,包含了所有的数据库、SQL语句执行区和执行结果区,点开数据库可以查询到数据库下的数据表和其他内容。

(7)MySQL的目录结构

以本机为例,MySQL的安装目录为E:\PhpStudy\phpstudy_pro\Extensions\MySQL5.7.26,目录下的文件及目录如下:

E:.
│   COPYING
│   my.ini
│   README
│
├───bin
├───data                    
│   ├───mysql               
│   ├───performance_schema  
│   └───sys                 
└───share                   
    ├───bulgarian           
    ├───charsets            
    ├───czech               
    ├───danish              
    ├───dutch               
    ├───english             
    ├───estonian            
    ├───french              
    ├───german              
    ├───greek               
    ├───hungarian           
    ├───italian             
    ├───japanese            
    ├───korean              
    ├───norwegian           
    ├───norwegian-ny        
    ├───polish              
    ├───portuguese          
    ├───romanian            
    ├───russian             
    ├───serbian             
    ├───slovak              
    ├───spanish             
    ├───swedish             
    └───ukrainian           

各目录功能如下:

目录目录内容
bin放置一些可执行文件
docs文档
include包含(头)文件
lib依赖库
share用于存放字符集、语言等信息
my.iniMySQL配置文件
data保存数据库和数据表信息,其中的一个子目录代表一个数据库,其内部的一个文件就是一个数据表,一条数据就是文件中的记录

需要注意,通过不同方式安装得到的安装目录可能会有所不同。

(8)数据库管理系统

数据库管理系统(DataBase Management System,DBMS)是指一种操作和管理维护数据库的大型软件。
MySQL就是一个数据库管理系统软件,安装了MySQL的电脑,称为数据库服务器。

数据库管理系统的作用:

用于建立、使用和维护数据库,对数据库进行统一的管理。

数据库管理系统、数据库和表之间的关系,如下:

mysql relation

其中:

  • MySQL中管理着很多数据库;

  • 在实际开发环境中一个数据库一般对应了一个应用,数据库中保存着多张表;

  • 每一张表对应着不同的业务,表中保存着对应业务的数据,一张表中包含着多条数据。

(9)数据库表

数据库中以表为组织单位存储数据,表是数据的矩阵,在一个数据库中的表看起来像一个简单的电子表格。

可以将Java程序与关系型数据表对比:

MySQL中的表类似Java中的类;

MySQL中的数据记录类似Java中的对象;

MySQL中的字段类似Java中的成员变量。

3.SQL

(1)SQL的概念

结构化查询语言(Structured Query Language)简称SQL,是一种有特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统

其中,关系型数据库按照行和列存储数据,类似于一个表格,一行(元组或记录)是一组相关的数据,一列(数据字段) 包含了相同类型和含义的数据。

SQL 的特点:

  • 是所有关系型数据库的统一查询规范,不同的关系型数据库都支持SQL;

  • 所有的关系型数据库都可以使用SQL;

  • 不同数据库之间的SQL有一些区别,可以称之为方言。

(2)SQL的通用语法

SQL的通用语法如下:

  1. SQL语句可以单行或者多行书写,以分号结尾(SQLYog中可以不用写分号) ;

  2. 可以使用空格和缩进来增加语句的可读性;

  3. MySQL中使用SQL不区分大小写,一般关键字大写,数据库名、表名列名小写;

  4. 注释方式:

注释语法说明
单行注释,需要在—后加空格,再加注释
#单行注释
/* */多行注释

    

    如下:

# 单行注释
show databases;
-- 单行注释
show databases;
/*
多行注释
*/
show databases;

    举例如下:

# 1.SQL语句可以单行或者多行书写,以;结尾
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| apicccp            |
| bank_info          |
| community_dating   |
| django_ecommerce   |
| django_fw          |
| flask_bbs          |
| fresh_ec           |
| mysql              |
| old_demo           |
| performance_schema |
| python_da          |
| sys                |
+--------------------+
13 rows in set (0.00 sec)

# 3.MySQL中使用SQL不区分大小写
mysql> SHOW Databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| apicccp            |
| bank_info          |
| community_dating   |
| django_ecommerce   |
| django_fw          |
| flask_bbs          |
| fresh_ec           |
| mysql              |
| old_demo           |
| performance_schema |
| python_da          |
| sys                |
+--------------------+
13 rows in set (0.00 sec)

# 4.注释方式
mysql> show databases; -- 查询数据库列表       
+--------------------+                  
| Database           |                  
+--------------------+                  
| information_schema |                  
| apicccp            |                  
| bank_info          |                  
| community_dating   |                  
| django_ecommerce   |                  
| django_fw          |                  
| flask_bbs          |                  
| fresh_ec           |                  
| mysql              |                  
| old_demo           |                  
| performance_schema |                  
| python_da          |                  
| sys                |                  
+--------------------+                  
13 rows in set (0.00 sec)               


mysql> show databases; /*多行注释*/
+--------------------+
| Database           |
+--------------------+
| information_schema |
| apicccp            |
| bank_info          |
| community_dating   |
| django_ecommerce   |
| django_fw          |
| flask_bbs          |
| fresh_ec           |
| mysql              |
| old_demo           |
| performance_schema |
| python_da          |
| sys                |
+--------------------+
13 rows in set (0.00 sec)

mysql> show databases; # 单行注释
+--------------------+
| Database           |
+--------------------+
| information_schema |
| apicccp            |
| bank_info          |
| community_dating   |
| django_ecommerce   |
| django_fw          |
| flask_bbs          |
| fresh_ec           |
| mysql              |
| old_demo           |
| performance_schema |
| python_da          |
| sys                |
+--------------------+
13 rows in set (0.00 sec)                                        

(3)SQL的分类

SQL的分类如下:

分类说明
数据定义语言简称DDL(Data Definition Language),用来定义和操作数据库对象,包括数据库、表、列等
数据操作语言简称DML(Data Manipulation Language),用来对数据库中表的记录进行更新,即对表中的数据进行增删改
数据查询语言简称DQL(Data Query Language),用来查询数据库中表的记录
数据控制语言简称DCL(Data Control Language),用来定义数据库的访问权限和安全级别,及创建用户

其中重点在DML和DQL。

(4)DDL操作数据库之创建和查询

对数据库的操作分为以下几类:

  • C

    create,创建数据库

  • R

    retrieve,查询数据库

  • U

    update,修改数据库

  • D

    delete,删除数据库

  • 使用数据库

创建数据库有以下几种方式:

命令说明
create database 数据库名;创建指定名称的数据库
create database 数据库名 character set 字符集;创建指定名称的数据库,并且指定字符集(一般都指定utf-8)

指定名称创建数据库如下:

CREATE DATABASE java_demo;

这会使用配置文件中指定的默认字符集方式创建数据库,执行后刷新图形化工具就会发现新键的数据库

默认字符集可以查看配置文件,[mysqld]下的character-set-server配置项即为字符集,一般默认为latin1,可以修改为utf8或utf8mb4,注意修改后需要重启MySQL

指定字符集方式创建数据库如下:

CREATE DATABASE java_demo CHARACTER SET utf8;

字符集一般都指定为 utf8,与Java中的编码保持一致。

查看和切换数据库的命令如下:

命令说明
use 数据库;使用(切换)数据库
select database();查看当前正在使用的数据库
show databases;查看MySQL中都有哪些数据库
show create database 数据库名;查看一个数据库的定义信息

切换数据库使用USE 数据库名;,例如USE java_demo;就将使用的数据库切换为java_demo。

查询当前正在使用的数据库使用SELECT DATABASE();,输出:

+------------+
| DATABASE() |
+------------+
| java_demo  |
+------------+
1 row in set (0.00 sec)

查询当前平台MySQL所有的数据库使用show DATABASES;,输出:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| apicccp            |
| bank_info          |
| community_dating   |
| django_ecommerce   |
| django_fw          |
| flask_bbs          |
| fresh_ec           |
| java_demo          |
| mysql              |
| old_demo           |
| performance_schema |
| python_da          |
| sys                |
+--------------------+
14 rows in set (0.00 sec)

查看数据库的定义信息,例如SHOW CREATE DATABASE java_demo;,输出:

+-----------+--------------------------------------------------------------------+
| Database  | Create Database                                                    |
+-----------+--------------------------------------------------------------------+
| java_demo | CREATE DATABASE `java_demo` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+-----------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

除了上面通过命令的方式外,MySQL的很多操作都可以通过图形化工具如SQLYog来实现,只需要点击和输入即可,而不需要执行命令。

(5)MySQL自带数据库的介绍

通过show DATABASES;可以看到,MySQL有4个自带的数据库,分别如下:

  • information_schema

    信息数据库,主要保存了其他数据库的信息,例如数据库名称、表名称和表字段名称。

  • mysql

    MySQL核心数据库,保存与用户和权限相关的信息。

  • performance_schema

    主要保存性能相关的数据,监控MySQL性能。

  • sys

    记录了DBA(数据库管理员)所需要的信息,比如哪个表被访问得最多、哪条SQL语句执行最慢,更加方便地让DBA了解数据库的运行情况。

(6)DDL操作数据库之修改和删除

修改数据库主要是修改数据库的字符集,语法格式为alter database 数据库名 character set 字符集;

示意如下:

-- 将数据库java_demo的字符集修改为gbk
mysql> ALTER DATABASE java_demo CHARACTER SET gbk;
Query OK, 1 row affected (0.00 sec)
-- 查看当前数据库的基本信息,发现编码已更改
mysql> SHOW CREATE DATABASE java_demo;
+-----------+-------------------------------------------------------------------+
| Database  | Create Database                                                   |
+-----------+-------------------------------------------------------------------+
| java_demo | CREATE DATABASE `java_demo` /*!40100 DEFAULT CHARACTER SET gbk */ |
+-----------+-------------------------------------------------------------------+
1 row in set (0.00 sec)

使用drop database 数据库名;从MySQL中永久删除 某个数据库,删除后不能恢复,需要慎用

使用如下:

-- 删除某个数据库
mysql> DROP DATABASE java_demo;
Query OK, 0 rows affected (0.01 sec)

(7)MySQL常见的数据类型

MySQL中常见的数据类型如下:

类型描述
int整型
double浮点型
varchar字符串型,长度可变
char字符串类型,长度固定
date日期类型,格式是yyyy-MM-dd,只有年月日,没有时分秒
datetime日期时间类型,包含年月日和时分秒

其中,varchar和char都表示字符串类型:

varchar是可变长度的字符串,存储字符串时只使用所需的空间;

char是固定长度的字符串,根据定义的长度分配对应的存储空间,可能存在内存空间的浪费。

例如,保存字符串"abc"时,x char(10)会占用10个字节,而y varchar(10)占用3个字节。

使用场景:

char类型适合存储固定长度的字符串,比如银行卡密码、性别等;

varchar类型适合存储长度在一定范围内、并且可能变化的字符串。

完整的数据类型如下:

mysql data type

(8)DDL操作数据表之创建和查看

创建表的语法格式如下:

CREATE TABLE 表名(
    字段名称1 字段类型(长度),
    字段名称2 字段类型,
    字段名称3 字段类型,
    ...
);

其中,最后一列不要加逗号。

例如:

-- 1.创建商品分类表
mysql> CREATE TABLE category(
    cid INT,
    cname VARCHAR(20)
);
Query OK, 0 rows affected (0.01 sec)
-- 2.创建测试表
mysql> CREATE TABLE test1 (
    tid INT,
    tdate DATE
);
Query OK, 0 rows affected (0.02 sec)

快速创建一个表结构相同的表(复制表结构)的语法格式为create table 新表名 like 旧表名

查看表的语句如下:

命令说明
show tables;查看当前数据库中的所有表名
desc 表名;查看数据表的结构
show create table 表名;查看创建表的SQL语句

例如:

-- 创建一个与test1表结构相同的test2表
mysql> create table test2 like test1;
Query OK, 0 rows affected (0.02 sec)
-- 查看test2的表结构
mysql> desc test2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| tid   | int(11) | YES  |     | NULL    |       |
| tdate | date    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.01 sec)
-- 查看test1的表结构
mysql> desc test1;                                 
+-------+---------+------+-----+---------+-------+ 
| Field | Type    | Null | Key | Default | Extra | 
+-------+---------+------+-----+---------+-------+ 
| tid   | int(11) | YES  |     | NULL    |       | 
| tdate | date    | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+ 
2 rows in set (0.00 sec)   
-- 查看当前数据库中的所有表
mysql> show tables;
+---------------------+
| Tables_in_java_demo |
+---------------------+
| category            |
| test1               |
| test2               |
+---------------------+
3 rows in set (0.00 sec)
-- 查看创建category表的SQL语句
mysql> show create table category;
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                    |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
| category | CREATE TABLE `category` (
  `cid` int(11) DEFAULT NULL,
  `cname` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+----------+---------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
                        

(9)DDL操作数据表之删除

删除表的语法格式如下:

命令说明
drop table 表名;删除表(从数据库中永久删除 某一张表)
drop table if exists 表名;判断表是否存在,存在则删除,不存在就不执行删除

例如:

-- 删除表
mysql> drop table test1;
Query OK, 0 rows affected (0.01 sec)
-- 再删除,会报错
mysql> drop table test1;
ERROR 1051 (42S02): Unknown table 'java_demo.test1'
-- 判断并删除
mysql> drop table if exists test1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

可以看到,在第一次删除表之后,当前数据库中已经不存在test1,所以再删除时会报错,此时可以先判断再删除,就不会再报错、而是只有警告了。

(10)DDL操作数表之修改

修改表的相关情景和语法格式如下:

修改目标语法格式
修改表名rename table 旧表名 to 新表名;
修改表的字符集alter table 表名 character set 字符集;
向表中添加列,关键字ADDalert table 表名 add 字段名称 字段类型;
修改表中列的数据类型或长度,关键字MODIFYalter table 表名 modify 字段名称 字段类型;
修改列名称,关键字CHANGEalter table 表名 change 旧列名 新列名 类型(长度);
删除列,关键字DROPalter table 表名 drop 列名;

举例如下:

-- 将test2表改名为test1
mysql> rename table test2 to test1;
Query OK, 0 rows affected (0.02 sec)
-- 修改test1表的字符集为gbk
mysql> alter table test1 character set gbk;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
-- 给category表增加字段cdesc,类型为varchar、长度为50
mysql> alter table category add cdesc varchar(50);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc category;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cid   | int(11)     | YES  |     | NULL    |       |
| cname | varchar(20) | YES  |     | NULL    |       |
| cdesc | varchar(50) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
-- 修改字段长度
mysql> alter table category modify cdesc varchar(100);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc category;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| cid   | int(11)      | YES  |     | NULL    |       |
| cname | varchar(20)  | YES  |     | NULL    |       |
| cdesc | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
-- 修改字段类型
mysql> alter table category modify cdesc char(100);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc category;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cid   | int(11)     | YES  |     | NULL    |       |
| cname | varchar(20) | YES  |     | NULL    |       |
| cdesc | char(100)   | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
-- 修改category表cdesc字段为description,类型为varchar(30)
mysql> alter table category change cdesc description varchar(50);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc category;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| cid         | int(11)     | YES  |     | NULL    |       |
| cname       | varchar(20) | YES  |     | NULL    |       |
| description | varchar(50) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
-- 删除description列
mysql> alter table category drop description;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc category;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cid   | int(11)     | YES  |     | NULL    |       |
| cname | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


(11)DML之插入数据

SQL中的DML 用于对表中的数据进行增删改操作。

插入数据的语法格式为:

insert into 表名 (字段名1, 字段名2, ...) values(字段值1, 字段值2, ...);

测试如下:

-- 创建student表
mysql> CREATE TABLE student(
    sid int,
    sname VARCHAR(20),
    age INT,
    sex CHAR(1),
    address VARCHAR(50)
);
Query OK, 0 rows affected (0.02 sec)
-- 插入全部字段,写出全部字段
mysql> insert into student (sid, sname, age, sex, address) values(1, "Corley", 18, '男', 'Beijing');
Query OK, 1 row affected (0.00 sec)
-- 插入全部字段,省略字段名
mysql> insert into student values(2, 'Jack', 20, '男', "Shanghai");
Query OK, 1 row affected (0.01 sec)
-- 插入部分指定字段
mysql> insert into student (sid, sname) values(3, 'Bob');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+--------+------+------+----------+
| sid  | sname  | age  | sex  | address  |
+------+--------+------+------+----------+
|    1 | Corley |   18 || Beijing  |
|    2 | Jack   |   20 || Shanghai |
|    3 | Bob    | NULL | NULL | NULL     |
+------+--------+------+------+----------+
3 rows in set (0.00 sec)


可以看到,在插入部分字段时,在其他字段允许为空的情况下,保存到数据表中的值为NULL。

在插入数据时,需要注意以下几点:

  1. 值与字段必须要对应,需要保证个数相同、数据类型相同

    例如insert into student (sid, sname, age, sex, address) values(1, 'Corley', 18, '男');不能正常插入。

  1. 值的数据大小,必须在字段指定的长度范围内

    例如,insert into student (sid, sname, age, sex, address) values(4, 'Corley', 18, 'male', 'Beijing');不能正常插入。

  1. varchar、char、date类型的值必须使用单引号或者双引号包裹

    例如insert into student (sid, sname, age, sex, address) values(4, Corley, 18, 'male', 'Beijing');不能正常插入,推荐使用单引号 包括。

  1. 如果要插入空值,可以忽略不写,或者插入null

    例如insert into student (sid, sname, age) values(4, 'Corley', 18);insert into student (sid, sname, age, sex, address) values(4, 'Corley', 18, null, null);的效果相同,插入的数据中sex和address字段的值都为null。

  1. 如果插入指定字段的值,必须要写对应的列名。

(12)DML之修改数据

修改数据的语法格式如下:

场景语法格式
不带条件的修改update 表名 set 列名 = 值;
带条件的修改update 表名 set 列名 = 值 [where 条件表达式(字段名 = 值)];

举例如下:

-- 修改表中所有学生的性别为女
mysql> update student set sex = '女';
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0
-- 带条件修改,将address为空的修改为earth
mysql> update student set address = 'earth' where address is null;
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0
-- 一次修改多列,将性别为女的修改为性别为男、年龄为18
mysql> update student set sex = '男', age = 18 where sex = '女';
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0

mysql> select * from student;
+------+--------+------+------+----------+
| sid  | sname  | age  | sex  | address  |
+------+--------+------+------+----------+
|    1 | Corley |   18 || Beijing  |
|    2 | Jack   |   18 || Shanghai |
|    3 | Bob    |   18 || earth    |
|    4 | Corley |   18 || Beijing  |
|    1 | Corley |   18 || Beijing  |
|    4 | Corley |   18 || earth    |
|    4 | Corley |   18 || earth    |
+------+--------+------+------+----------+
7 rows in set (0.00 sec)


其中,第一种方式慎用,因为操作会影响到表中的所有数据;

当修改多个字段时,字段之间需要用逗号,隔开。

(13)DML之删除数据

删除数据的语法格式如下:

场景语法格式
删除所有数据delete from 表名;
指定条件删除数据delete from 表名 [where 字段名 = 值];

举例如下:

-- 删除表中address为earth的数据
mysql> delete from student where address = 'earth';
Query OK, 3 rows affected (0.00 sec)

mysql> select * from student;
+------+--------+------+------+----------+
| sid  | sname  | age  | sex  | address  |
+------+--------+------+------+----------+
|    1 | Corley |   18 || Beijing  |
|    2 | Jack   |   18 || Shanghai |
|    4 | Corley |   18 || Beijing  |
|    1 | Corley |   18 || Beijing  |
+------+--------+------+------+----------+
4 rows in set (0.00 sec)
-- 删除表中的所有数据
mysql> delete from student;
Query OK, 4 rows affected (0.01 sec)

mysql> select * from student;
Empty set (0.00 sec)

可以看到,删除表中所有的数据有2种方式:

  • delete from 表名;

    是对表中的数据逐条删除,即有多少条记录,就执行多少次删除操作,效率较低、不推荐。

  • truncate table 表名;

    是先删除整张表,然后再重新创建一张一模一样的新表,效率高、推荐。

但是一般不建议删除表中所有的数据,需慎用

(14)DQL之简单查询

查询使用SELECT关键字,语法格式为:

select 列名 from 表名;

测试如下,先准备一些数据:

-- 创建员工表
mysql> CREATE TABLE emp(
eid INT,
ename VARCHAR(20),
sex CHAR(1),
salary DOUBLE,
hire_date DATE,
dept_name VARCHAR(20)
);
Query OK, 0 rows affected (0.02 sec)
-- 插入数据
mysql> INSERT INTO emp VALUES(1,'孙悟空','男',7200,'2013-02-04','教学部');
INSERT INTO emp VALUES(2,'猪八戒','男',3600,'2010-12-02','教学部');
INSERT INTO emp VALUES(3,'唐僧','男',9000,'2008-08-08','教学部');
INSERT INTO emp VALUES(4,'白骨精','女',5000,'2015-10-07','市场部');
INSERT INTO emp VALUES(5,'蜘蛛精','女',5000,'2011-03-14','市场部');
INSERT INTO emp VALUES(6,'玉兔精','女',200,'2000-03-14','市场部');
INSERT INTO emp VALUES(7,'林黛玉','女',10000,'2019-10-07','财务部');
INSERT INTO emp VALUES(8,'黄蓉','女',3500,'2011-09-14','财务部');
INSERT INTO emp VALUES(9,'吴承恩','男',20000,'2000-03-14',NULL);
INSERT INTO emp VALUES(10,'孙悟饭','男', 10,'2020-03-14','财务部');
INSERT INTO emp VALUES(11,'兔八哥','女', 300,'2010-03-14','财务部');
Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

再进行查询,如下:

-- 查询emp表中所有的数据
mysql> select * from emp;
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    1 | 孙悟空    ||   7200 | 2013-02-04 | 教学部    |
|    2 | 猪八戒    ||   3600 | 2010-12-02 | 教学部    |
|    3 | 唐僧      ||   9000 | 2008-08-08 | 教学部    |
|    4 | 白骨精    ||   5000 | 2015-10-07 | 市场部    |
|    5 | 蜘蛛精    ||   5000 | 2011-03-14 | 市场部    |
|    6 | 玉兔精    ||    200 | 2000-03-14 | 市场部    |
|    7 | 林黛玉    ||  10000 | 2019-10-07 | 财务部    |
|    8 | 黄蓉      ||   3500 | 2011-09-14 | 财务部    |
|    9 | 吴承恩    ||  20000 | 2000-03-14 | NULL      |
|   10 | 孙悟饭    ||     10 | 2020-03-14 | 财务部    |
|   11 | 兔八哥    ||    300 | 2010-03-14 | 财务部    |
+------+-----------+------+--------+------------+-----------+
11 rows in set (0.00 sec)
-- 查询所有数据,只显示id和name
mysql> select eid, ename from emp;
+------+-----------+
| eid  | ename     |
+------+-----------+
|    1 | 孙悟空    |
|    2 | 猪八戒    |
|    3 | 唐僧      |
|    4 | 白骨精    |
|    5 | 蜘蛛精    |
|    6 | 玉兔精    |
|    7 | 林黛玉    |
|    8 | 黄蓉      |
|    9 | 吴承恩    |
|   10 | 孙悟饭    |
|   11 | 兔八哥    |
+------+-----------+
11 rows in set (0.01 sec)
-- 查询所有数据,同时将列名改为中文
mysql> select 
    eid as '编号',
    ename as '姓名',
    sex as '性别',
    salary as '薪资',
    hire_date as '入职时间',
    dept_name '部门名'
from emp;
+--------+-----------+--------+--------+--------------+-----------+
| 编号   | 姓名      | 性别   | 薪资   | 入职时间     | 部门名    |
+--------+-----------+--------+--------+--------------+-----------+
|      1 | 孙悟空    ||   7200 | 2013-02-04   | 教学部    |
|      2 | 猪八戒    ||   3600 | 2010-12-02   | 教学部    |
|      3 | 唐僧      ||   9000 | 2008-08-08   | 教学部    |
|      4 | 白骨精    ||   5000 | 2015-10-07   | 市场部    |
|      5 | 蜘蛛精    ||   5000 | 2011-03-14   | 市场部    |
|      6 | 玉兔精    ||    200 | 2000-03-14   | 市场部    |
|      7 | 林黛玉    ||  10000 | 2019-10-07   | 财务部    |
|      8 | 黄蓉      ||   3500 | 2011-09-14   | 财务部    |
|      9 | 吴承恩    ||  20000 | 2000-03-14   | NULL      |
|     10 | 孙悟饭    ||     10 | 2020-03-14   | 财务部    |
|     11 | 兔八哥    ||    300 | 2010-03-14   | 财务部    |
+--------+-----------+--------+--------+--------------+-----------+
11 rows in set (0.00 sec)
-- 查询一共有几个部门
mysql> select distinct dept_name from emp;
+-----------+
| dept_name |
+-----------+
| 教学部    |
| 市场部    |
| 财务部    |
| NULL      |
+-----------+
4 rows in set (0.01 sec)
-- 将员工薪资数据加1000进行展示
mysql> select ename, salary+1000 as salary from emp;
+-----------+--------+
| ename     | salary |
+-----------+--------+
| 孙悟空    |   8200 |
| 猪八戒    |   4600 |
| 唐僧      |  10000 |
| 白骨精    |   6000 |
| 蜘蛛精    |   6000 |
| 玉兔精    |   1200 |
| 林黛玉    |  11000 |
| 黄蓉      |   4500 |
| 吴承恩    |  21000 |
| 孙悟饭    |   1010 |
| 兔八哥    |   1300 |
+-----------+--------+
11 rows in set (0.00 sec)

mysql> select * from emp;
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    1 | 孙悟空    ||   7200 | 2013-02-04 | 教学部    |
|    2 | 猪八戒    ||   3600 | 2010-12-02 | 教学部    |
|    3 | 唐僧      ||   9000 | 2008-08-08 | 教学部    |
|    4 | 白骨精    ||   5000 | 2015-10-07 | 市场部    |
|    5 | 蜘蛛精    ||   5000 | 2011-03-14 | 市场部    |
|    6 | 玉兔精    ||    200 | 2000-03-14 | 市场部    |
|    7 | 林黛玉    ||  10000 | 2019-10-07 | 财务部    |
|    8 | 黄蓉      ||   3500 | 2011-09-14 | 财务部    |
|    9 | 吴承恩    ||  20000 | 2000-03-14 | NULL      |
|   10 | 孙悟饭    ||     10 | 2020-03-14 | 财务部    |
|   11 | 兔八哥    ||    300 | 2010-03-14 | 财务部    |
+------+-----------+------+--------+------------+-----------+
11 rows in set (0.00 sec)


其中,*代表所有的列;

别名查询使用as关键字,其中as可以省略;

去重使用关键字distinct

查询不会对数据库中的数据进行修改,只是一种显示数据的方式。

(15)DQL之条件查询

如果查询语句中没有设置条件,就会查询所有的行信息,在实际应用中,一般要指定查询条件,对记录进行过滤。

语法格式如下:

select 列名 from 表名 where 条件表达式;

即先取出表中的每条数据,满足条件的数据就返回,不满足的就过滤掉。

其中条件表达式中一般会用到运算符。

比较运算符如下:

运算符说明
>、<、>=、<=、 <>/!=大于、小于、大于等于、小于等于、不等于
BETWEEN … AND …显示在某一区间的值;例如1000-5000之间为between 1000 and 5000
IN(集合)集合表示多个值,使用逗号分隔,例如name in (悟空, 八戒)
in中的每个数据都会作为一次条件,只要满足条件就会显示
LIKE ‘%张%’模糊查询
IS NULL查询某一列为NULL的值,不能写= NULL

逻辑运算符如下:

运算符说明
and &&多个条件同时成立
or
not不成立,取反

在进行条件查询时需要确定以下几点:

  • 查哪张表

  • 查哪些字段

  • 查询条件是什么

使用如下:

# 查询员工姓名为黄蓉的员工信息
mysql> select * from emp where ename = '黄蓉';
+------+--------+------+--------+------------+-----------+
| eid  | ename  | sex  | salary | hire_date  | dept_name |
+------+--------+------+--------+------------+-----------+
|    8 | 黄蓉   ||   3500 | 2011-09-14 | 财务部    |
+------+--------+------+--------+------------+-----------+
1 row in set (0.01 sec)
# 查询薪水价格为5000的员工信息
mysql> select * from emp where salary = 5000;
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    4 | 白骨精    ||   5000 | 2015-10-07 | 市场部    |
|    5 | 蜘蛛精    ||   5000 | 2011-03-14 | 市场部    |
+------+-----------+------+--------+------------+-----------+
2 rows in set (0.00 sec)
# 查询薪水价格不是5000的所有员工信息
mysql> select * from emp where salary != 5000;
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    1 | 孙悟空    ||   7200 | 2013-02-04 | 教学部    |
|    2 | 猪八戒    ||   3600 | 2010-12-02 | 教学部    |
|    3 | 唐僧      ||   9000 | 2008-08-08 | 教学部    |
|    6 | 玉兔精    ||    200 | 2000-03-14 | 市场部    |
|    7 | 林黛玉    ||  10000 | 2019-10-07 | 财务部    |
|    8 | 黄蓉      ||   3500 | 2011-09-14 | 财务部    |
|    9 | 吴承恩    ||  20000 | 2000-03-14 | NULL      |
|   10 | 孙悟饭    ||     10 | 2020-03-14 | 财务部    |
|   11 | 兔八哥    ||    300 | 2010-03-14 | 财务部    |
+------+-----------+------+--------+------------+-----------+
9 rows in set (0.00 sec)
# 查询薪水价格大于6000元的所有员工信息
mysql> select * from emp where salary > 6000;
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    1 | 孙悟空    ||   7200 | 2013-02-04 | 教学部    |
|    3 | 唐僧      ||   9000 | 2008-08-08 | 教学部    |
|    7 | 林黛玉    ||  10000 | 2019-10-07 | 财务部    |
|    9 | 吴承恩    ||  20000 | 2000-03-14 | NULL      |
+------+-----------+------+--------+------------+-----------+
4 rows in set (0.00 sec)
# 查询薪水价格在5000到10000之间所有员工信息
mysql> select * from emp where salary between 5000 and 10000;
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    1 | 孙悟空    ||   7200 | 2013-02-04 | 教学部    |
|    3 | 唐僧      ||   9000 | 2008-08-08 | 教学部    |
|    4 | 白骨精    ||   5000 | 2015-10-07 | 市场部    |
|    5 | 蜘蛛精    ||   5000 | 2011-03-14 | 市场部    |
|    7 | 林黛玉    ||  10000 | 2019-10-07 | 财务部    |
+------+-----------+------+--------+------------+-----------+
5 rows in set (0.00 sec)
# 查询薪水价格是3600或7200或者20000的所有员工信息
mysql> select * from emp where salary = 3600 or salary = 7200 or salary = 20000;
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    1 | 孙悟空    ||   7200 | 2013-02-04 | 教学部    |
|    2 | 猪八戒    ||   3600 | 2010-12-02 | 教学部    |
|    9 | 吴承恩    ||  20000 | 2000-03-14 | NULL      |
+------+-----------+------+--------+------------+-----------+
3 rows in set (0.00 sec)


其中,select * from emp where salary != 5000;等价于select * from emp where salary <> 5000;

select * from emp where salary between 5000 and 10000;等价于select * from emp where salary >= 5000 and salary <= 10000;

select * from emp where salary = 3600 or salary = 7200 or salary = 20000;等价于select * from emp where salary in (3600, 7200, 20000);,即使用in()函数匹配括号中的参数。

模糊查询使用like关键字,常用通配符如下:

通配符说明
%表示匹配任意多个字符串
_表示匹配 一个字符

再使用如下:

# 查询含有'精'字的所有员工信息
mysql> select * from emp where ename like '%精%';
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    4 | 白骨精    ||   5000 | 2015-10-07 | 市场部    |
|    5 | 蜘蛛精    ||   5000 | 2011-03-14 | 市场部    |
|    6 | 玉兔精    ||    200 | 2000-03-14 | 市场部    |
+------+-----------+------+--------+------------+-----------+
3 rows in set (0.00 sec)
# 查询以'孙'开头的所有员工信息
mysql> select * from emp where ename like '孙%';
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    1 | 孙悟空    ||   7200 | 2013-02-04 | 教学部    |
|   10 | 孙悟饭    ||     10 | 2020-03-14 | 财务部    |
+------+-----------+------+--------+------------+-----------+
2 rows in set (0.00 sec)
# 查询第二个字为'兔'的所有员工信息
mysql> select * from emp where ename like '_兔%';
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    6 | 玉兔精    ||    200 | 2000-03-14 | 市场部    |
+------+-----------+------+--------+------------+-----------+
1 row in set (0.00 sec)
# 查询没有部门的员工信息
mysql> select * from emp where dept_name is null;
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    9 | 吴承恩    ||  20000 | 2000-03-14 | NULL      |
+------+-----------+------+--------+------------+-----------+
1 row in set (0.00 sec)
# 查询有部门的员工信息
mysql> select * from emp where dept_name is not null;
+------+-----------+------+--------+------------+-----------+
| eid  | ename     | sex  | salary | hire_date  | dept_name |
+------+-----------+------+--------+------------+-----------+
|    1 | 孙悟空    ||   7200 | 2013-02-04 | 教学部    |
|    2 | 猪八戒    ||   3600 | 2010-12-02 | 教学部    |
|    3 | 唐僧      ||   9000 | 2008-08-08 | 教学部    |
|    4 | 白骨精    ||   5000 | 2015-10-07 | 市场部    |
|    5 | 蜘蛛精    ||   5000 | 2011-03-14 | 市场部    |
|    6 | 玉兔精    ||    200 | 2000-03-14 | 市场部    |
|    7 | 林黛玉    ||  10000 | 2019-10-07 | 财务部    |
|    8 | 黄蓉      ||   3500 | 2011-09-14 | 财务部    |
|   10 | 孙悟饭    ||     10 | 2020-03-14 | 财务部    |
|   11 | 兔八哥    ||    300 | 2010-03-14 | 财务部    |
+------+-----------+------+--------+------------+-----------+
10 rows in set (0.00 sec)


可以看到,判断为空使用is null,而不是= null,判断不为空使用is not null

总结

MySQL是一个关系型数据库管理系统,因为其开源的特点和丰富强大的功能,成为最受欢迎的(关系型)数据库,因此掌握MySQL极为必要。

  • 1
    点赞
  • 1
    评论
  • 4
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

©️2022 CSDN 皮肤主题:博客之星2020 设计师:CSDN官方博客 返回首页

打赏作者

cutercorley

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值