MySQL数据库

基础

概述-数据模型

关系型数据库（RDBMS)

**概念：**建立在关系模型基础上，有多张相互连接的二维表组成的数据库。

特点：

​ 1、使用表存储数据，格式统一，便于维护。

​ 2、使用SQL语言操作，标准统一，使用方便。

id	name	job	dept_id
1	金庸	总裁	2
2	张无忌	项目经理	1
3	杨逍	开发	3

​ 员工表

id	name
1	研发部
2	总经办
3	财务部

​ 部门表

员工表和部门表通过员工表dept_id和部门表id 连接起来。

数据模型

SQL

通用语法

SQL通用语法

1、SQL语句可以单行或多行书写，以分号结尾。

2、SQL语句可以使用空格/缩进来增强语句的可读性。

3、MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。

4、注释：

• 单行注释：-- 注释内容 或者 # 注释内容（MySQL 特有）
• 多行注释：/* 注释内容 */

5、命令行登录

• win + R
• cmd
• mysql -u root -p
• 密码：****

语言分类

分类	全称	说明
DDL	Date Definition Language	数据定义语言，用来定义数据库对象**（数据库、表、字段）**
DML	Date Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Date Query Language	数据查询语言，用来查询数据库中的表的记录
DCL	Date Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

图形化界面工具-DataGrip

创建库

右键 MySQL-@localhost —— New —— Schema 点击—— 给数据库命名 —— 点击 Ok ;

创建表

右键 某库 —— New —— Table 点击 —— 给表命名 —— comment 给表添加注释信息 —— 点击 左上角 “+” —— columns —— 添加字段 ——设置字段名称、注释、数据类型 —— 多次点击columns ，添加多个字段。—— 字段添加完成后 —— 点击 Ok;

给表添加字段

右键 某表 —— modify Table —— 点击 左上角 “+” —— columns —— 添加字段 ——设置字段名称、注释、数据类型 —— 多次点击columns ，添加多个字段。—— 字段添加完成后 —— 点击 Ok;

切换成命令行形式

右键 某数据库 —— new —— Query console —— 输入命令行 —— 选中执行；

DDL-数据定义语言

DDL-数据定义语言 —— 数据定义语言，用来定义数据库对象（数据库、表、字段）

DDL-数据库操作

查询

查询所有数据库

show databases;  //小写关键字

查询当前数据库

select database();  //小写关键字

创建

创建数据库

create database [if not exists] 数据库名 [dafault charset 字符集] [collate 排序规则]；

[ ] 里面的内容可有可无，其表示“一定的意义”。

例如：

create database if not exists itcast;

解释：如果不存在名叫 itcast 的数据库就创建，若存在就不创建。

create datebase itheima default charset utf8mb4;

解释：创建数据库 itheima ; 字符集为utf8mb4，占4个字节。

删除

删除数据库

drop datebase [if exists] 数据库名;

使用

使用数据库

use 数据库名;

DDL-表操作

查询

查询当前数据库所有表

show tables;

查询表结构

desc 表名;

查询指定表的建表语句

show create table 表名;

创建

create table 表名(
     字段1 字段1类型[COMMENT 字段1注释],
     字段2 字段2类型[COMMENT 字段2注释],
     字段3 字段1类型[COMMENT 字段3注释],
     
     ......
     字段n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];

注意：

• […] 为可选参考，最后一个字段后面没有逗号。
• 字段——列名

举例：创建下面的表

id	name	age	gender
1	令狐冲	21	男
2	风清扬	58	男
3	东方不败	23	女

//进入自定义数据库
use itcast;

//建表--表名：tb_user
create table tb_user(

id int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄',
gender varchar(1) comment '性别'

)comment '用户表';

数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

数值类型

类型	大小	有符号（signed)范围	无符号（unsigned)范围	描述
tinyint	1 byte	( -128 , 127 )	( 0 , 255 )	小整数值
smallint	2 bytes	( -32768 , 32767 )	( 0 ,65535 )	大整数值
mediumint	3 bytes	( -8388608 , 8388607 )	( 0 , 16777215 )	大整数值
int / integer	4 bytes	( -2147483648 , 2147483647 )	( 0 , 4294967295 )	大整数值
bigint	8 bytes	( -2^63 , 2^63 - 1 )	( 0 , 2^64 - 1 )	极大整数值
float	4 bytes	( -3.402823466 E+38 , 3.402823466351 E+38 )	0 和 ( 1.175494351 E-38 , 3.402823466 E+38 )	单精度浮点数值
double	8 bytes	( -1.7976931348623157 E+308 , 1.7976931348623157 E+308 )	0 和 ( 2.2250738585072014 E-308 , 1.7976931348623157 E+308 )	双精度浮点数值
decimal		依赖于M（精度）和 D（标度）的值	依赖于M (精度) 和 D （标度）的值	小数值（精确定点数）

注意：

• 精度M，指的是数字的位数。

  标度D，指的是数字的小数位数。

​ 例如：123.45；

​ 精度M ：5

​ 标度D ：2

• 举例：

  年龄 ：age tinyint unsigned //表示 年龄是无符号整型；

  分数： score double(4 , 1) // 4:表示 精度M , 1:表示标度D

字符串类型

类型	大小	描述
char	0 - 255 bytes	定长字符串
varchar	0 - 65535 bytes	变长字符串
tinyblob	0 - 255 bytes	不超过255个字符的二进制数据
tinytext	0 - 255 bytes	短文本字符串
blob	o - 65535 bytes	二进制形式的长文本数据
text	0 - 65535 bytes	长文本数据
mediumblob	0 - 16777215 bytes	二进制形式的中等长度文本数据
mediumtext	0 - 16777215 bytes	中等长度文本数据
longblob	0 - 4294967295 bytes	二进制形式的极大文本数据
longtext	0 - 4294967295 bytes	极大文本数据

注意：

• 当我们使用 char 或者 varchar 时，后面都跟一个参数，这表示，字符串的最大长度。

• char(10) ：即使使用1个字符，也占用10个字符的空间，其他9个字符的空间，会用 空格 进行 补位；

• varchar(10) ：在最大存储字符个数内，存储多少个字符，就占多少空间；

• char 与 varchar 相比较，char 的 性能较好；

• 举例：

  用户名 username varchar(50)

  性别 gender char(1)

日期类型

类型	大小	范围	格式	描述
date	3	1000-01-01 至 9999-12-31	YYYY-MM-DD	日期值
time	3	-838 ：59 ：59 至 838 ：59 ：59	HH : MM : SS	时间值或持续时间
year	1	1901 至 2155	YYYY	年份值
datetime	8	1000-01-01 00 ：00 ：00 至 9999-12-31 23 ：59 ：59	YYYY-MM-DD HH : MM : SS	混合日期和时间值
timestamp	4	1970-01-01 00 ：00 ：01 至 2038-01-19 03 ：14 ：07	YYYY-MM-DD HH : MM : SS	混合日期和时间值

注意：

• 举例：

  生日 birthday data

案例：根据需求创建表（设计合理的数据类型、长度）

设计一张员工信息表，要求如下：

1、编号（ 纯数字 ）

2、员工工号（ 字符串类型，长度不超过10位 ）

3、员工姓名 （ 字符串类型，长度不超过10位 ）

4、性别（ 男/女，存储一个汉字 ）

5、年龄（ 正常人的年龄，不可能存储负数 ）

6、身份证号（二代身份证号均为18位，身份证中有X这样的字符 ）

7、入职时间（取值年月日即可 ）

修改

添加字段

alter table 表名 add 字段名 字段类型(长度) [comment 注释][约束];

案例：

为 emp 表增加一个新的字段 “昵称” 为nickname，类型为 varchar(20);

alter table emp add nickname varchar(20) comment '昵称'；

修改数据类型

alter table 表名 modify 字段名 新数据类型(长度);

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 新数据类型(长度) [comment 注释][约束];

案例：

将 emp 表的 nickname 字段修改为 username , 类型为 varchar(30)；

alter table emp change nickname username varchar(30) comment '用户名';

删除字段

alter table 表名 drop 字段名;

案例:

将 emp 表的字段 username 删除

alter table emp drop username;

修改表名

alter table 表名 rename to 新表名;

案例：

将 emp 表的表名修改为 employee

alter table emp rename to employee;

删除表

drop table [if exists] 表名;

删除指定表，并重新创建该表

truncate table 表名;

案例：

删除表 tb_user;

DML-数据操作语言

**DML 数据操作语言：**用来对数据库中表的数据记录进行增删改操作。

• **添加/插入**数据 —— insert ( INSERT )

• **修改**数据 —— **update ** ( UPDATE )

• **删除**数据 —— delete ( DELETE )

DML-数据添加

1、给指定字段添加数据

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

查看表数据

select * from 表名;

举例：

insert into employee(id,workno,workname,gender,age,idcard,entrydata) values (1,'1','Itcast','男',10,123456789012345678,'2000-01-01');

2、给全部字段添加数据

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

举例：

insert into employee values (2,'2','张无忌','男',18,123456789012345670,'2005-01-01');

3、批量添加数据

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

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

举例：

insert into employee values (3,'3','韦一笑','男',38,123456789712345670,'2005-01-01'),(4,'4','赵敏','女',18,123456785712345670,'2005-01-01');

注意：

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段规定的范围内。

DML-修改数据

修改数据

update 表名 set 字段名1 = 值1,字段名2 = 值2,...[where 条件];

举例：

• 修改id为1的数据，将workname修改为itheima；

update employee set workname = 'itheima' where id = 1;

• 修改id为1的数据，将workname修改为 小昭，gender 修改为 女；

update employee set workname = '小昭',gender = '女' where id = 1;

• 将所有员工入职日期entrydata修改为 2008-01-01

update employee set entrydata = '2008-01-01';

注意：

• 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

DML-删除数据

删除数据

delete from 表名 [where 条件];

举例：

• 删除 gender 为女的员工

delete from employee where gender = '女';

• 删除所有员工

delete from employee;

注意：

• delete 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• delete 语句不能删除某一个字段的值(可以使用 update)

DQL-数据查询语言

DQL-数据查询语言：用来查询数据库中的表的记录。

• **查询关键字：**select ( SELECT )

• DQL-语法

  select
         字段列表
  from
         表名列表
  where
         条件列表
  group by
         分组字段列表
  having
         分组后条件列表
  order by
         排序字段列表
  limit
         分页参数
  

DQL-基础查询

​ 表emp

1、查询多个字段

select 字段1,字段2,字段3,... from 表名;

举例：

• 查询指定字段 name , workno , age 返回

  select name,workno,age from emp;
  

查询整张表/查询所有字段

select * from 表名;

举例：

• 查询所有字段返回

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

select * from emp;

2、查询字段并设置别名

select 字段1[as 别名1],字段2[as 别名2],... from 表名;

注意：as 可以省略

举例：

• 查询所有员工的工作地址，起别名

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

select workaddress '工作地址' from emp;

3、去除重复记录

select distinct 字段列表 from 表名;

举例：

• 查询公司员工的上班地址(不要重复)

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

DQL-条件查询

1、语法

select 字段列表 from 表名 where 条件查询;

2、条件

比较运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<> 或 ！=	不等于
between … and …	在某个范围之内**（含最小、最大值）**
in(…)	在 in 之后的列表中的值，多选一
like 占位符 ( _ % ）	模糊匹配（_ 匹配单个字符，%匹配任意个字符）
is null	是 null

逻辑运算符	功能
and 或 &&	并且（多个条件同时成立）
or 或 ||	或者（多个条件任意一个成立）
not 或 !	非，不是

3、举例：

• 1、查询年龄等于 88 的员工信息

  select * from emp where age = 88;
  

• 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、查询年龄不等于 88 的员工信息

  select * from emp where age <> 88;
  
  select * from emp where age != 88;
  

• 7、查询年龄在 15 岁（包含）到 20岁（包含）之间的员工信息

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

• 8、查询性别为 女 且年龄小于 25岁的员工信息

  select * from emp where gender = '女' and age < 25;
  

• 9、查询年龄等于 18 或 20 或 40 的员工信息

  select * from emp where age = 18 or age = 20 or age = 40;
  
  select * from emp where age in(18,20,40);
  

• 10、查询姓名为两个字的员工信息

  select * from emp where name like '_ _';
  

• 11、查询身份证号最后一位是 X 的员工信息

  select * from emp where idcard like '%X';
  
  select * from emp where idcard like '_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ X';
  

  解释：%X 表示，身份证的最后一位是 X, 其他位，取值随意。

DQL-聚合函数

1、聚合函数介绍

​ 将一列数据作为一个整体，进行纵向计算。

2、常见聚合函数

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

3、语法

select 聚合函数 (字段列表) from 表名;

select 聚合函数 (字段列表) from 表名 where 查询条件;

**注意：**null 值不参与所有聚合函数运算。

4、举例

• 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 = '西安';
  

DQL-分组查询

1、语法

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

2、where 和 having 区别

• 执行时机不同：where 是分组之前进行过滤，不满足 where 条件，不参与分组；而 having 是分组之后对结果进行过滤。
• 判断条件不同：where 不能对聚合函数进行判断，而 having 可以。

注意：

• 执行顺序：where > 聚合函数 > having
• 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

3、举例

• 1、根据性别分组，统计男性员工 和 女性员工的数量

  select * from emp group by gender;   //根据性别分组
  
  select count(*) from emp group by gender;   //根据性别分组，分组完毕统计各分组员工数量
  
  select gender count(*) from emp group by gender;   //根据性别分组，分组完毕根据性别统计对应分组员工数量
  

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

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

• 3、查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址

  select * from emp where age < 45;    //查询年龄小于45的员工
  
  select * from emp where age < 45 group by workaddress;      //查询年龄小于45的员工，并分组
  
  select workaddress,count(*) from emp where age < 45 group by workaddress having count(*) >= 3;   //获取员工数量大于等于3的工作地址
  

DQL-排序查询

1、语法

select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;

2、排序方式

• ASC : 升序 （默认值0)
• DESC : 降序

**注意：**如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

3、举例

• 1、根据年龄对公司的员工进行升序排序

  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;
  

DQL-分页查询

1、语法

select 字段列表 from 表名 limit 起始索引,查询记录数;

注意：

• 起始索引从0开始，起始索引 = （查询页码 - 1） 每页显示记录数*。

• 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL 中是 LIMIT。

• 如果查询的是第一页数据，起始索引可以省略，直接简写为limit + 查询记录数。

2、举例

• 1、查询第1页员工数据，每页展示10条记录。

  select * from emp limit 0,10;
  

• 2、查询第2页员工数据，每页展示10条记录。

  select * from emp limit 10,10;
  

DQL-案例练习

• 1、查询年龄为20，21，22，23岁的女性员工的信息。

  select * from emp where gender = '女'and age in(20,21,22,23);
  

• 2、查询性别为 男，并且年龄在 20-40 岁（含）以内的姓名为三个字的员工。

  select * from emp where gender = '男' and age between 20 and 40 and name like '_ _ _';
  

• 3、统计员工表中，年龄小于60岁的，男性员工和女性员工数。

  select gender , count(*) from emp where age < 60 group by gender;
  

• 4、查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按照年龄升序排序，如果年龄相同按入职时间降序排序。

  select name,age from emp where age <= 35 order by age asc,entrydate desc;
  

• 5、查询性别为男，且年龄在20-40岁（含)以内的5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序。

  select * from emp where gender = '男' and age between 20 and 40 order by age asc,entrydate dasc limit 0,5;
  

DQL-执行顺序

DCL-数据控制语言

**Data Control Language DCL-数据控制语言：**用来创建数据库用户、控制数据库的访问权限。

DCL-用户管理

1、查询用户

use mysql;
select * from user;

2、创建用户

create user '用户名'@'主机名' identified by 密码;

3、修改用户密码

alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

4、删除用户

drop user '用户名'@'主机名';

注意：

• 主机名可以使用 % 通配

举例：

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

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

• 2、创建用户 heima , 可以在任意主机访问该数据库，密码123456

  create user 'heima'@'%' identifide by '123456';
  

• 3、修改用户 heima 的访问密码为 1234

  alter user 'heima'@'%' identified with myaql_native_password by '1234';
  

• 4、删除 itcast@localhost 用户

  drop user 'itcast'@'localhost';
  

DCL-权限控制

MySQL中定义了很多种权限，但是常用的就以下几种：

权限	说明
all , all privileges	所有权限
select	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter	修改表
drop	删除数据库/表/视图
create	创建数据库/表

1、查询权限

show grants for '用户名'@'主机名';

2、授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

3、撤销权限

revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';

注意：

• 多个权限之间，使用逗号分隔；所有权限用 all 表示
• 授权时，数据库名和表名可以使用 * 进行通配，代表所有。