目录
1 数据库
1.1 相关概念
-
数据库
(DateBase-DB)
:存储数据的仓库,数据是有组织的进行存储 -
数据库管理系统
(DateBase Management System - DBMS)
:操纵和管理数据库的大型软件 -
SQL(Structured Query Language)
:操纵关系型数据库的编程语言,定义了一套操作关系型数据库的统一标准
1.2 主流的关系型数据库管理系统
我们所学是MySQL
数据库,尽管上面有这么多不同的数据库,但都有着统一的标准,都使用SQL
语言来操作数据库
2 安装与使用MySQL
2.1 安装与启动
一直点next
和execute
即可
安装好后来到服务这里可以看到MySQL
正在运行
-
①右击即可选择启动或关闭
-
②使用命令行启动或关闭
MySQL
net start mysql80 启动
net stop mysql80 关闭
不过要使用root
权限
2.2 操作MySQL(客户端连接)
有2种方式
2.2.1 MySQL自带的命令行终端
打开后输入密码即可
2.2.2 windows自带的终端命令行连接
首先,配置环境变量
找到MySQL
服务安装的这个目录,直接复制路径
找到环境变量path
,加入MySQL
的路径确定即可
mysql [-h 127.0.0.1] [-P 3306] -u root -p
连接的ip 连接的端口
mysql -u root -p 连接本机ip的3306端口
然后打开cmd
,输入 mysql -u root -p
命令即可连接(默认)
3 数据模型
3.1 关系型数据库(RDMBS)
概念:建立在关系模型基础上,由多张相互连接的二维表组成的数据库
特点:
-
使用表存储数据,格式统一,便于维护
-
使用
SQL
语言操作,标准统一,使用方便
二维表:
3.2 数据模型图
4 数据类型
4.1 数值类型
举例:
-
年龄,一般
0-100
岁,可以写age tinyint unsigned;
-
分数,一般
0-100.0
分,共4
位数,1
位小数,可以写score double(4,1);
4.2 字符串类型
对于char
和varchar
字符串,使用前都需要指定大小
如 char(10)
与varchar(10
)
代表当前字符串能够存储的最大长度是10
,如果超过10
将报错
-
char
类型,无论你存入什么字符,都会占用10
个字符的空间,如果字符不到10
个字符的空间,剩下空间用空格补全,因此性能较好。 -
而
varchar
类型,如果你存入1
个字符,那么就占1
个字符的空间,如果存入5
个字符,那么空间就是5
,而使用时会根据内容计算所占空间,导致性能低一些。
示例
-
用户名
username
一般用varchar(50);
-
性别
age
一般用char(1);
4.3 日期时间类型
示例
我的生日 birthday
应该用date
类型
4.4 示例
综上,我们创建一个员工表:(要先学下面的创建表)
要求:
-
编号(纯数字)
-
员工工号(字符串类型,长度不超过
10
字节) -
员工姓名(字符串类型,长度不超过
10
字节 -
性别(男 / 女,存储一个汉字)
-
年龄(正常年龄,正数)
-
身份证号(二代身份证号均为
18
位,最后可能有X
字符) -
入职时间(年月日即可)
create table worker
(
id int comment'编号',
Workernumber varchar(10) comment'工号',
name varchar(10) comment'姓名',
gender char(1) comment'性别',
age tinyint unsigned comment'年龄',
IdCard char(18) comment'身份证',
Joindate date comment'入职时间'
)comment'员工表';
查看表
5 SQL
5.1 通用语法
-
SQL
语句可以单行或多行书写,以分号结尾 -
SQL
语句可以使用空格、缩进来增强语句的可读性 -
MySQL
数据库的SQL
语句不区分大小写,关键字建议大写 -
注释:
-
单行注释:
--cmd
或#cmd
(`MySQL特有) -
多行注释:
/ * cmd*/
-
5.2 分类
分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,定义数据库对象(数据库、表、字段) |
DML | Data Manipulation Language | 数据操作语言,对数据库表中的数据进行增删改操作 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Contral Language | 数据控制语言,来创建数据库用户,以及控制数据库的访问权限 |
5.3 DDL
Data Definition Language
数据定义语言
作用:数据库、数据表、数据字段的定义
常用语句:[ ]
中的为可选的,写了执行,不写也没事
5.3.1 常用 库 语句
show databases; 查询所有数据库
select database(); 查询当前数据库
create database[if not exist] 数据库名 [default charset 字符集] [collate排序规则];
创建数据库 (如果存在则创建) (如果写了就指定字符集) (如果写了就制定规则)
drop database[if exists] 数据库名; 删除数据库
use 数据库名; 使用数据库
查询所有数据库
创建一个名为itcast
的数据库,再次查询
如果我们再次创建这个名为itcast
数据库会怎么样?
因此,我们创建时加上if not exist
,当他不存在时则创建itcast
数据库
创建utf8mb4
字符集的数据库itjoyce
删除名为itcast
的数据库
如果我们再次删除itcast
数据库,就一样报错
如果不想让他报错,加上关键字if exists
使用数据库:
如果我们使用了很多指令 ,想查看我们当前属于哪个数据库
5.3.2 常用 表 查询 语句
show tables; 查询当前数据库所有表(先进入数据库)
desc 表名; 查询表结构
show create table 表名; 查询指定的表的建表语句
我们先查询一下itjoyce
这个数据库下的表
表为空,没有表
那我们切换到sys
系统数据库,再次查看表
可以看到有很多表
接下来我们先了解表的创建,再使用第二和第三条语句
5.3.3 常用 表 创建 语句
create table 表名
(
字段1 type [ comment 字段1注释],
字段2 type [ comment 字段2注释],
字段3 type [ comment 字段3注释],
...
字段n type [ comment 字段n注释]
) [ comment 表注释];
// [ ]为可选参数,可写可不写,最后一个字段后面没有逗号,
如果我要制定这么一个数据表的表头第一行:
使用desc name;
来查看当前数据库下的表
但是没有我写的comment
注释
因此使用show create table name;
来查看表创建信息
5.3.4 常用 表 修改 语句
5.3.4.1 添加字段
alter table 表名 add 字段名 type/length [comment 注释] [约束]; 添加字段
如我在上面创建的表worker
中添加名为nickname
的字段 ,varchar=20
5.3.4.2 修改字段
alter table 表名 modify 字段名 newtype/length; 修改数据类型或长度
alter table 表名 change 旧字段名 新字段名 type/length [comment 注释] [约束]; 修改字段名和字段类型
如我将上面创建的nickname
字段修改为username
,类型为varchar(3);
使用modify
只能修改数据类型或长度,字段名无法修改
使用change
可以同时修改字段名和类型/长度
5.3.4.3 删除字段
alter table 表名 drop 字段名; 删除字段
如我将上面的username
字段删除
5.3.4.4 修改表名
alter table 表名 rename to 新表名;
如我将上面的worker
表修改为employee
show tables;
5.3.5 常用 表 删除 语句
drop table[if exist] 表名; 删除表
truncate table 表名; 删除指定表,并重新创建该表
首先,我们删除t1
表
然后使用truncate table name
删除并重新创建employee
表
会清空数据,留下表结构。
5.4 DML
Data Manipulation Language
数据操作语言
作用:对数据库中的数据记录进行增删改操作
5.4.1 添加数据
insert into 表名(字段名1,字段名2...) values(值1,值2...); 给指定字段添加数据
insert into 表名 values(值1,值2...); 给全部字段添加数据
批量添加数据:
insert into 表名(字段名1,字段名2...) values(值1,值2...),(值1,值2...),(值1,值2...);
insert into 表名 values(值1,值2...),(值1,值2...),(值1,值2...);
接下来,我们使用DataGrip
工具对itcast
数据库的employee
表添加数据
-
首先是给指定字段添加数据
添加成功
接下来我们查看刚才插入的数据
可以 双击左边的employee
表
或者使用语句 select *from name;
来查看名为name
的表
-
现在,假设我再次添加一段数据,年龄这里改为负数
-20
运行
直接报错,提示超出了范围
-
接下来,我们使用给全部字段添加数据
然后,我们批量向表中添加数据,一次添加2个数据,中间用逗号,
分开
insert into employee values(3,'3','Nna','女',11,'410185222222220499','2023-03-13'),
(4,'4','knad','男',41,'410185222244220499','2022-07-13');
添加成功
注意
-
插入数据时,指定的字段顺序要与值的顺序是对应的
-
字符串和日期型数据应使用引号
' '
包含 -
插入的数据大小,应在字段的规定范围内
5.4.2 修改数据
update 表名 set 字段名1=值1,字段名2=值2...[where条件];
where
后条件可写可不写,不写代表修改所有字段的数据
示例
Ⅰ 我们将字段id
为1
的姓名name
由Joyce
修改为itjoyce
Ⅱ 我们将字段id
为2的姓名修改为ittatina
,性别改为男
Ⅲ 将所有员工的入职日期修改为2008-08-08
注意看,当我们修改所有字段的数据时,IDE
会再次提醒我们是否要执行
全部的入职时间已经修改为2008-08-08
5.4.3 删除数据
delete from 表名 [where 条件];
where
后条件可写可不写,不写代表删除所有字段的数据
示例
Ⅰ 删除employee
表里性别为女的员工
可以看到性别只有男了
Ⅱ 删除表中所有员工
同样会提示,我们直接点执行
表中没有数据了
注意
-
delete
语句的条件可以写或不写,不写则删除表的所有数据 -
delete
语句不可删除某一个字段的值(用update
可以,设置为none
)
5.5 DQL
Data Query Language
数据查询语言
功能:查询数据表中的记录
5.5.1 基本查询
查询多个字段:
select 字段1,字段2... from 表名;
select *from 表名;
设置别名:
select 字段1[as 别名],字段2[as 别名]... from 表名;
去除重复记录:
select distinct 字段列表 from 表名;
示例
-
首先删除原来的
employee
表
然后我们创建新的表
接下来,我们插入16
条数据
insert into emp (id, number, name, gender, age, idcard,workaddress, entrydate)
values
(1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),
(2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),
(3, '3', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01'),
(4, '4', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01'),
(5, '5', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),
(6, '6', '杨道', '男', 28, '12345678931234567X', '北京', '2006-01-01'),
(7, '7', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01'),
(8, '8', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01'),
(9, '9', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01'),
(10,'10', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01'),
(11,'11', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01'),
(12,'12', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),
(13,'13', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01'),
(14,'14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01'),
(15,'15', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'),
(16,'16', '周芷若', '女', 18, 'nutl', '北京', '2012-06-01');
双击emp
表,可以看到我们插入的数据
-
Ⅰ 查询指定字段,
name number age
返回
-
Ⅱ 查询所有字段返回
2种方法:
1(不推荐)
或
2
-
Ⅲ 查询所有员工的工作地址并返回
当我们直接输入workadress
时,返回的信息并不直观
因此,我们给工作地址加上别名
当然,as
可以省略,直接写别名
-
Ⅳ 查询员工的上班地址(不要重复)
5.5.2 条件查询
select 字段列表 from 表名 where 条件列表;
下面是各种条件
示例
-
Ⅰ 查询年龄等于 88 的员工
-
Ⅱ 查询年龄小于 20 的员工信息
-
Ⅲ 查询年龄小于等于 20 的员工信息
-
Ⅳ 查询没有身份证号的员工信息
-
Ⅴ 查询有身份证号的员工信息
直接在is null
中间加个关键字not
就行
-
Ⅵ 查询年龄不等于
88
的员工信息
或者用<>
-
Ⅶ 查询年龄在15岁(包含) 到 20 岁(包含)之间的员工信息
也可以使用这两个
注意:between
后跟最小值,and
后跟最大值
-
Ⅷ 查询性别为 女 且年龄小于 25 岁的员工信息
-
Ⅸ 查询年龄等于 18 或 20 或 40 的员工信息
也可以使用in()
;in
中满足其一即可
-
Ⅹ 查询姓名为两个字的员工信息
使用like
与2
个下划线判断名为2
个字的员工
select *from emp where name like '__' ;
同理,如果我们想查找名字为3
个字的员工,输入3
个下划线即可
-
ⅩⅠ 查询身份证后为最后一位是
X
的员工
可以使用&
或者_
来指定查询条件,下划线需要写17
个
select *from emp where emp.idcard like '%X' ;
select *from emp where emp.idcard like '_________________X' ;
5.5.3 聚合函数
由于下面会用到,所以先讲
作用:将一列数据视为一个整体,进行纵向计算
常见聚合函数:
语法:
select 聚合函数(字段列表) from 表名;
示例
-
Ⅰ 统计该企业员工数量
这两条语句都行
-
Ⅱ 统计该企业员工的平均年龄
-
Ⅲ 统计该企业员工的最大年龄
-
Ⅳ 统计该企业员工的最小年龄
-
Ⅴ 统计西安地区员工的年龄之和
求和使用sum
,字段where
选择工作地点等于西安的
select sum(age) from emp where workaddress = '西安';
注意:所有null
值不参与聚合函数的计算
5.5.4 分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后的过滤条件];
而对于where
和having
,有如下不同
-
执行时机不同:
where
是在分组之前进行过滤,不满足where
条件,不参与分组;而having
是在分组之后对结果进行过滤 -
判断条件不同:
where
不能对聚合函数进行判断,having
可以
示例
-
Ⅰ 根据性别分组,统计 男性员工 和 女性员工 的数量
select gender,count(*) from emp group by gender ;
不加gender
会不显示男女
-
Ⅱ 根据性别分组,统计 男性员工 和 女性员工 的平均年龄
select gender,avg(age) from emp group by gender ;
-
Ⅲ 查询年龄小于 45 的员工,并根据工作地址分组,获取员工数量大于等于 3 的工作地址
select workaddress,count(*) from emp where age < 45
group by workaddress having count(*) >= 3 ;
先是 年龄小于45
,where
;然后是根据工作地址分组,group by
;最后是工作地址里员工数大于3
的输出,having
注意
-
执行顺序:
where
> 聚合函数 >having
-
分组后,查询的字段一般为聚合函数或分组字段,查询其他字段无意义
5.5.5 排序查询
select 字段列表 from 表名 order by 字段1 排序方式,字段2 排序方式;
排序方法
asc :升序(默认)
desc:降序
示例
-
Ⅰ 根据 年龄 对员工进行 升序 排序
可以加esc
,也可以不加
-
Ⅱ 根据 入职时间 对员工进行 降序 排序
-
Ⅲ 根据 年龄 对公司的员工进行 升序 排序,如果年龄相同,再按照 入职时间 进行 降序 排序
注意:
-
如果是多字段排序,只有当第一个字段值相同时,才会对相同的值进行第二个排序
5.5.6 分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数; //记住二者之间有逗号,
示例
-
Ⅰ 查询第1页员工数据,每页展示10条记录
-
Ⅱ 查询第2页员工数据,每页展示10条记录
注意
-
起始索引从
0
开始,起始索引 = (查询页码 -1)*每页显示的记录数
-
在不同的数据库中,分页查询有不同的方式,
MySQL
中是limit
-
如果查询的是第一页数据,起始索引可以省略,直接简写为
limit 10
5.5.7 执行顺序
我们平时的编写顺序
而实际上的执行顺序
5.5.7.1 证明
完成这样一条指令
查询年龄大于
15
的员工的姓名、年龄,并根据年龄进行升序排序
select name ,age from emp where age > 15 order by age;
-
① 因为
select
是在from
之后执行,所以我在from
后对emp
起别名e
,而where
在from
后,我在where
后就可以使用别名e
执行:结果相同,则证明select
在from
之后,where
在from
之后
-
②接着①后,对
select
后的字段选择使用别名的方式访问name
和age
执行:结果相同
-
③在②后,我对
select
后的name
和age
起别名为ename
与eage
,并尝试在where
后直接使用
执行:没找到,因为where
执行时还没有select
,因此没有别名
-
④而
select
是在order by
之前,因此,在select
起别名后,我们在order by
后使用别名
执行:结果相同
5.5.7.2 示例
-
Ⅰ 查询年龄为20,21,22,23岁的女性员工信息
select *from emp where gender = '女' and age in(20,21,22,23);
-
Ⅱ 查询性别为 男,并且年龄在20-40 岁(含)以内的姓名为三个字的员工。
select *from emp where gender = '男' && age between 20 and 40 && name like '___';
-
Ⅲ 统计员工表中年龄小于60岁的,男性员工和女性员工的人数。
select gender 性别,count(age) 年龄 from emp where age < 60 group by gender;
-
Ⅳ 查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序
select name,age from emp where age <= 35 order by age asc,entryDate desc;
-
Ⅴ 查询性别为男,且年龄在20-40 岁(含)以内的前5个员工信息,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序。
select *from emp where gender = '男' and (age between 20 and 40)
order by age asc,entryDate desc limit 5;
以上就是DQL
的所有内容
5.6 DCL
Data Contral Language
,数据控制语言
功能:管理可以访问数据库的用户,以及用户的数据库访问权限
分类:管理用户
5.6.1 管理用户
①查询用户
use mysql;
select *from user;
②创建用户
create user '用户名'@'主机名' identified by '密码';
③修改用户密码
create user '用户名'@'主机名' identified with mysql_native_password by '新密码';
④删除用户
drop user '用户名'@'主机名';
示例
-
Ⅰ 创建用户
itcast
, 只能够在当前主机Localhost
访问,密码123456
如果再次执行,会提示 创建失败
我们点开mysql
的用户表查看用户
已经有了itcast
用户,但没有分配任何权限
我们使用cmd
连接itcast
用户并尝试使用一些命令如show databases
可以发现,只显示了2条记录,而我们使用root
用户会看到所有的数据库
-
Ⅱ 创建用户
babe
,可以在任意主机访问该数据库,密码110420
要使用户可在任意主机访问该数据库,只需将主机名改为%
即可
成功
-
Ⅲ 修改用户
bab
e的访问密码为123456
打开cmd
用旧密码尝试连接babe
拒绝访问
输入新密码
-
Ⅳ 删除
itcast@localhost
用户
打开用户表
注意
-
主机名使用
%
可以进行通配 -
这类
SQL
开发人员一般不操作,主要由DBA(Database Adminstrator 数据库管理员)
使用
5.6.2 权限控制
常用的权限:
而对于权限本身的命令,还有如下几个:
①查询权限
show grants for '用户名'@'主机名';
②授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名’;
③撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
5.6.2.1 查询权限
接下来我查看一下babe
用户的权限
可以看到,是usage
,是只有登录MySQL
的权限,没有其他权限
接下来我登录babe
用户,尝试查看当前的所有数据库
可以看到只有两个schema
数据库
5.6.2.2 授予权限
给babe
用户授予itcast
数据库的所有权限
再次查看babe
的权限
可以看到有了对itcast
的all privileges
,即所有权限
然后再次使用babe
用户查看数据库
切换到itcast
数据库,查看当下的所有表
5.6.2.3 撤销权限
撤销babe
用户对itcast
的所有权限
没了权限
注意
-
多个权限之间,使用逗号
,
分隔 -
授权时,数据库名和表名可以使用
*
号进行通配,代表所有数据库或所有表
以上就是DCL的全部内容
6 MySQL图形化管理工具
常用的图形化管理工具有:
我这里使用DataGrip
工具来进行我接下来的学习
安装好后,直接连接我们的数据库
然后输入用户名和密码后,点击下面安装驱动,然后再点击测试,即可成功
接下来点击展示我们所有的表
接下来,我们创建一个新的数据库
鼠标右键后点击new->schema
输入数据库名称
注意,这里使用了create schema test;
和create database test;
效果相同
左边就有了一个数据库
然后我们右击创建一个新的表
然后就可以在这里添加新的表
这里添加表名与注释,下方会自动生成
columns
右击可以添加新的字段,输入字段名类型和注释,下方会自动生成
同理,我们加上name
字段
看一下我们生成的字段:
点击ok
如果我们想修改字段,右击选择修改表
点击columns
后点击+
号即可填写新的字段信息
点击ok
,查看我们的表
同时,我们还可以使用SQL
语句操纵我们的test
数据库
首先,右击我们的tes
t库,选择new->Query Console
即可在弹出的界面中输入我们的语句
比如输入show schemas;
显示我们所有的表
输入后选中我们输入的语句,然后点击左上角的运行标志
即可看到我们的数据库
同理,查看我们所有的表
查看user
表