mysql入门

引言

本文对mysql的基础部分进行讲解，包括软件的下载，sql语句的入门以及mysql中函数和约束的简要介绍。整体内容偏向实践，并无明显的理论介绍。在阅读本文前，最好掌握c+ +或者java中的面向对象思想。每种语言的语法都不一样，但编程的思想都是相通的。

一 mysql介绍

现阶段大型的软件或系统都需要一个专门管理数据的系统，mysql就是当下最流行的数据管理系统之一，广泛用于web等领域。mysql是一种关系型数据库管理系统，将数据保存在不同的表中，而不是将所有数据都存放在一个大仓库中，进而提高速度，增加灵活性。

二 mysql以及相关编辑环境的安装

2.1 mysql安装

mysql是一款开源的产品，因此在官网上下载即可。首先进入官网https://www.mysql.com/

进入官网后，发现如下界面，点击downloads

点击downloads后，将页面下拉到中间位置，点击下载mysql社区版，如下图所示。

进入后，观察到如下界面。根据电脑的系统选择下载入口。Windows系统和mac系统的下载入口分别如下图所示。

由于mac系统和windows系统的后续配置不太一样，之后如何配置和启动mysql，网络上讲的很详细了，自己查阅一下（我懒了）。

2.2 图形化界面DataGrip安装

DataGrip是由jetbrains公司出品的一款编写数据库的软件，正版收费但学生经过认证后是可以免费使用的（学生赶快入手）。具体安装过程就不啰嗦了，b站上有很详细的讲解教程。

三 mysql入门

mysql相关环境的介绍在上文中已经做出阐述，具体配置和启动过程需要自己摸索（也是提升自己最关键的一环），遇到困难时可以参考网络上的其他内容。下面对mysql的用法进行详细介绍。

mysql基于sql语言，在不考虑优化的前提下，基础功能分为如下几个方面。

然而，对于初学者而言，一时之间难以将以上内容完全掌握。多表查询和事务需要考虑的东西过多，因此本文将二者放在进阶篇进行讨论，不在基础篇内论述。

四 sql语句

对于数据库而言，增删改查的功能是必不可少的。mysql正是基于sql语言进行增删改查。

4.1 DDL

DDl是一种数据定义语言，用于定义管理数据的对象，也就是我们常说的数据表的定义，具体用法如下。

例如，我们要创建一个名为test的数据库：

create database if not exists test default charset utf8mb4;

查询当前数据库：

select database();

查看所有数据库：

show databases;

使用test数据库：

drop database if exists test;

删除test数据库：

use test;

在test数据库中，创建一张名为users的表：

`create table users(`

    `id int comment '序号',`

    `name varchar(50) comment '姓名',`

    `age int comment '年龄'`

`);`

查看当前数据库的所有表：

show tables ;

查看users表的结构：

desc users;

查询users表的建表语句：

show create table users;

向表中添加字段：

alter table users add nickname varchar(30) comment '昵称';

修改数据类型：

alter table users modify nickname varchar(40);

修改字段名和字段类型：

alter table users change nickname newnickname varchar(30) comment '新昵称';

删除字段：

alter table users drop newnickname;

修改表名：

alter table users rename to newname;

删除表：

drop table if exists users;

删除指定表并重新创建新表：

truncate table users;

4.2 DML

DML是一种操作语言，用于操作数据库中的数据。以上文创建的users表为例，表中包含id，name，age三个属性，则：

给指定字段添加数据：

insert into users (id, name, age) values (1, 'zhang', 18);

给全部字段添加数据：

insert into users values (2,'li',19);

批量添加数据：

insert into users (id, name, age) values (3,'liu',16),(4,'wang',20);

insert into users values (5,'zhao',12),(6,'wang',23);

修改数据：i

update users set id=7,name='meng' where age=18;

4.3 DQL

DQL是数据查询语言，对数据库的数据进行查询。在实际项目中，与增删改操作相比，查询操作具有非常高的使用频率。后端工程师往往需要根据不同的查询条件编写查询语句，因此对于后端工程师而言，必须掌握DQL的用法。查询语句的基础语法为select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条件列表 order by 排序字段列表 limit 分页参数，在实际应用中，往往根据不同的查询需求选择相应的部分。

对于DQL语句的基础语法，需要掌握各关键词的执行顺序，以便后续进行优化。执行顺序为：from > where > group by > select > order by > limit

DQL语句主要分为六大类，下文分别对其展开介绍。

4.3.1 基本查询

以上文提到的users表为例，查询多个字段：

select id,name,age from users; 或者

select * from users;

在实际应用中，往往选择第一种方式，可以通过语句观察表中字段。但为了方便演示，下文统一采用第二种方法。

为表中字段设置别名：

select id as newid,name as newname from users;

去除重复记录：

select distinct id from users;

为了更好地理解基本查询，我们新建一个员工表，通过案例加深了解。

`create table emp(`

    `id int comment '编号',`

    `workno varchar(10) comment '工号',`

    `name varchar(10) comment '姓名',`

    `gender char comment '性别',`

    `age int comment '年龄',`

    `idcard char comment '身份证号',`

    `workaddress varchar(50) comment '工作地址',`

    `entrydate date comment '入职时间'`

`)comment '员工表';`

导入数据：

`insert into emp (id, workno, name, gender, age, idcard, workaddress, entrydate)`

`values (1,'1','name1','女',20,'12345678','北京','2000-01-01'),`

       `(2,'2','name2','男',18,'123456789','北京','2005-09-01'),`

       `(3,'3','name3','女',19,'1234567890','上海','2004-02-01'),`

       `(4,'4','name4','男',22,'1234567891','沈阳','2007-09-03'),`

       `(5,'5','name5','女',23,'1234567892','大连','2005-09-01'),`

       `(6,'6','name6','男',16,'1234567893','深圳','2001-08-04'),`

       `(7,'7','name7','女',17,'1234567894','广州','2006-11-11');`

(1)查询指定字段name，workno，age返回

select name,workno,age from emp;

(2)查询所有字段返回

select * from emp;

或者select id, workno, name, gender, age, idcard, workaddress, entrydate from emp;

(3)查询所有员工的工作地址，起别名

select workaddress as '工作地址' from emp;

(4)查询员工的上班地址（去除重复）

select distinct workaddress as '工作地址' from emp;

4.3.2 条件查询

在实际查询中，很少有将表中所有字段进行查询的需求，往往需要按条件查找数据。因此，如果说查询语句重要，那么条件查询语句就是重中之重。

基础语法：select 字段列表 from 表名 where 条件列表;

以上文建立的员工表为例，根据以下案例，理解条件查询。

(1)查询年龄等于18的员工

select * from emp where age=18;

(2)查询年龄小于20的员工信息

select * from emp where age<20;

(3)查询年龄小于等于20岁的员工

select * from emp where age<=20;

(4)查询没有身份证号的员工

select * from emp where idcard is null;

(5)查询有身份证号的员工

select * from emp where idcard is not null;

(6)查询年龄不等于18的员工

select * from emp where age != 18;

或select * from emp where age <> 18;

(7)查询年龄在15和20之间的员工（包括15和20）

select * from emp where age >=15 && age <=20;

select * from emp where age >=15 and age <=20;

select * from emp where age between 15 and 20;

(8)查询性别为女，且年龄小于20的员工

select * from emp where gender='女' && age<20;

(9)查询年龄等于18或20或23的员工信息

select * from emp where age = 18 or age = 20 or age = 23;

select * from emp where age in(18,20,23);

(10)查询姓名为五个字的员工信息(下划线代表占位符)

select * from emp where name like '_____';

(11)查询身份证号最后一位是1的员工信息(百分号代表任意)

select * from emp where idcard like '%1';

4.3.3 聚合函数查询

在实际应用中，后端工程师往往需要统计满足xx条件的有多少人，或者求满足xx条件的平均数、最大值等条件。mysql是一款非常方便的数据管理系统，自然也提供了相应的聚合函数，使后端工程师能够更加简便地实现类似功能。

基础语法：select 聚合函数（字段列表） from 表名

注意：聚合函数是不计算null值的！！！

还是以员工表为例，进行如下操作，通过案例理解聚合函数。

(1)统计该企业员工数量

select count(*) from emp;

(2)统计该企业员工的平均年龄

select avg(age) from emp;

(3)统计该企业员工的最大年龄

select max(age) from emp;

(4)统计该企业员工的最小年龄

select min(age) from emp;

(5)统计北京地区员工的年龄之和

select sum(age) from emp where workaddress = '北京';

4.3.4 分组查询

在实际应用中，后端工程师经常需要实现统计各类别信息的需求，那么就需要按照类别进行分组，之后进行适当的查询操作，因此引入了分组查询用法。

基础语法：select 字段列表 from 表名 where 条件 group by 分组字段名 having 分组后的过滤条件;

划重点！！！where和having的区别：在执行时机方面，where在分组之前过滤，不满足where限定的条件则不参与分组，having则是分组之后对结果进行过滤（前端工程师可以类比v-if和v-show的区别）；在判断条件方面，where不能对聚合函数进行判断，而having可以。

下面还是根据案例理解：

(1)根据性别分组，统计男性员工和女性员工数量

select gender,count(*) from emp group by gender;

(2)根据性别分组，统计男性员工和女性员工的平均年龄

select gender,avg(age) from emp group by gender;

(3)查询年龄小于23岁的员工，并根据工作地址分组，获取员工数量大于等于2的工作地址

select workaddress,count(*) from emp where age < 23 group by workaddress having count(*) >= 2;

4.3.5 排序查询

当遇见按照某一顺序显示信息的需求时，需要对表中某一字段进行排序，并按排序后的结果进行显示，这就是排序查询的作用。

基础语法：select 字段列表 from 表名 order by 字段1 排序方式,字段2 排序方式2;

(1)根据年龄对员工升序排序(升序用asc，可不写，默认升序)

select * from emp order by age asc;

(2)根据入职时间，对员工进行降序排序

select * from emp order by entrydate desc;

(3)根据年龄对公司的员工升序排序，年龄相同则按入职时间降序排序

select * from emp order by age asc, entrydate desc ;

4.3.6 分页查询

在使用某些系统中，自己可以定义在一页中显示多少条数据，这个功能就是通过分页查询实现的。分页查询需要掌握的东西不多，还是根据案例进行演示。

(1)查询第一页员工数据，每页展示四条数据

select * from emp limit 0,4;

(2)查询第二页员工数据，每页展示四条记录

select * from emp limit 4,4;

4.4 DCL

DCL是数据控制语言，主要功能是对数据的权限进行控制。对于后端工程师而言，对DCL的要求不高。DCL主要对接的是DBA岗位。

创建用户itcast，只能够在当前主机localhost访问，密码123456

create user 'itcast'@'localhost' identified by '123456';

创建用户mycase，可以在任意主机访问数据库，密码123456

create user 'mycase'@'%' identified by '123456';

修改mycase用户密码

alter user 'mycase'@'%' identified with mysql_native_password by '1234';

删除itcast用户

drop user 'itcast'@'localhost';

查询权限

show grants for 'mycase'@'%';

授予权限

grant all on root.* to 'mycase'@'%';

撤销权限

revoke all on root.* from 'mycase'@'%';

五 mysql基础

sql语句是mysql数据库的基础，可以说如果没有sql，mysql也就无法使用。在介绍完sql的基本用法后，下文对mysql的基础部分进行介绍。

5.1 函数

对于任意语言，内置函数往往会对程序的编写过程带来极大便利。下文列举常见的四种mysql内置函数，用来解决常见需求。

5.1.1 字符串函数

字符串拼接：

select concat('hello',' mysql');

全转为小写:

select lower('HELLO');

全转为大写:

select upper('hello');

用-将字符串填充到五位（左侧填充）:

select lpad('01',5,'-');

用-将字符串填充到五位（右侧填充）:

select rpad('01',5,'-');

去除头部和尾部的空格:

select trim(' hello mysql ');

从第一个位置起，截取五个位置:

select substring('hello mysql',1,5);

5.1.2 数值函数

向上取整：（结果为2）

select ceil(1.2);

向下取整：(结果为1)

select floor(1.8);

取余：（6%4==2）

select mod(6,4);

求随机数（0到1）

select rand();

四舍五入(保留两位小数，结果为2.35)

select round(2.348,2);

5.1.3 日期函数

当前日期：

select curdate();

当前时间：

select curtime();

当前日期+时间：

select now();

当前日期所属年份：

select year(now());

当前日期所属月份：

select month(now());

当前日期是几号：

select day(now());

从当前日期往后推70天：

select date_add(now(),interval 70 day );

从当前日期往后推70个月：

select date_add(now(),interval 70 month );

从当前日期往后推70年：

select date_add(now(),interval 70 year );

两个日期相差天数(第一个时间减去第二个时间)：

select datediff('2023-01-01',now());

5.1.4 流程控制函数

if用法：若第一个值为true，则返回第二个参数，否则返回第三个参数：

select if(true,'right','error');

ifnull用法：判断第一个值是否为空？若空，则返回第二个值，否则返回第一个值：

select ifnull('ok','error');

select ifnull(null,'default');

注：只有null为空，‘‘null’’，0，‘0’均不为空。上述语句1返回‘ok’，语句2返回‘default’。

case when then else and用法：

`select name as '名字',`

       `case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end as '工作地址'`

`from emp;`

这条语句的含义是：如果查询到一个员工的工作地点为背景则返回一线城市，为上海则返回一线城市，否则返回二线城市，并将workaddress字段起别名为“工作地址”。

注意：流程控制函数的用法与常用编程语言的三运表达式类似。在实际项目中，后端工程师也会选择一种编程语言对数据库进行操作。因此，熟悉一门编程语言是实践mysql的前提。

5.2 约束

约束就是在创建表的过程中，对表内元素的性质进行限制。比如，在实际应用中，用户id是自动增长的状态，name不能为空等。与其后续编写程序对其进行清洗，不如在创建表时对其进行约束，从而使脏数据被拦截在外，避免后续数据清洗时的麻烦。

在mysql中，约束大致分为以下几项：

各种约束对应的关键词已经在括号内标出，下面通过案例进行介绍。

首先，创建一张名为user的用户表：

`create table user(`

    `id int primary key auto_increment comment 'id主键（自动增长）',`

    `name varchar(10) not null unique comment '姓名（非空，唯一）',`

    `age int check ( age > 0 && age <= 120 ) comment '年龄（检查约束）',`

    `status char(1) default '1' comment '状态（默认为1）',`

    `gender char(1) comment '性别'`

`)comment '用户表';`

插入数据：（验证id自增长）

insert into user(name,age,status,gender) values ('tom1',19,'1','男'),('tom2',25,'0','男');

验证name非空：（空则报错）

insert into user(name,age,status,gender) values (null,20,'1','男');

验证name唯一：（重复则报错）

insert into user(name,age,status,gender) values ('tom2',20,'1','男');

验证status默认：

insert into user(name,age,gender) values ('tom3',15,'女');

此条语句并未给status赋予任何值，运行后可观察到status的值为1。

注意：即使上述语句中有错误，出现报错情况，添加数据失败，但id也是自动增长的。

建立一张部门表dept和一张员工表employee，进而探究外键约束：

`create table dept(`

    `id int primary key auto_increment comment '部门ID',`

    `name varchar(30) not null comment '部门名称'`

`)comment '部门表';`

insert into dept (id, name) values (1,'研发部'),(2,'市场部'),(3,'财务部'),(4,'销售部'),(5,'总经办');

`create table employee(`

    `id int primary key  auto_increment comment '员工ID',`

    `name varchar(30) not null comment '员工姓名',`

    `age int comment '年龄',`

    `job varchar(30) comment '职位',`

    `salary int comment '薪资',`

    `entrydate date comment '入职时间',`

    `managerID int comment '直属领导ID',`

    `deptID int comment '部门ID'`

`)comment '员工表';`

`insert into employee (id, name, age, job, salary, entrydate, managerID, deptID)`

`values (1,'name1',50,'总裁',20000,'2000-01-01',null,5),(2,'name2',20,'项目经理',12500,'2003-11-02',1,1),`

       `(3,'name3',34,'开发',8600,'2001-10-11',2,1),(4,'name4',45,'开发',11000,'2003-08-08',2,1),`

       `(5,'name5',44,'开发',10600,'2004-07-03',3,1),(6,'name6',18,'设计',6000,'2008-05-04',2,1);`

已成功创建部门表和员工表，并导入数据。观察上述结构，可以发现两张表是有联系的。如果觉得在代码中看的不清晰，可以利用DataGrip软件进行表结构的查看，如下图所示。

试想，如果直接删除dept表的某个部门，相当于不对部门人员进行任何安排就直接解散该部门，这种处理方式无疑是有问题的。因此，通常采用外键约束的方式，建立两表之间的联系，避免出现不合实际的操作。

在本案例中，部门表中的部门id和员工表中部门id存在对应关系，因此需要建立外键关联，代码如下：

alter table employee add constraint fk_employee_deptID foreign key (deptID) references dept(id);

当这种联系被取消时，需要删除外键关联，代码如下：

alter table employee drop foreign key fk_employee_deptID;

cascade关键字：当对部门表（父表）进行记录时，检查是否有对应外键，如果有则修改外键在子表中的记录。也就是说，在变动部门信息时，也要对相应员工的信息进行更新。（fkemployeedeptID为外键命名，自行决定）：

alter table employee add constraint fk_employee_deptID foreign key (deptID) references dept(id) on update cascade on delete cascade ;

同理，使用set null关键字，父表删除对应记录，检查是否有对应外键，如果有，子表中该外键值设为null：

alter table employee add constraint new_fk_employee_deptID foreign key (deptID) references dept(id) on update set null on delete set null ;