MySQL 数据库的使用

一、MySQL 简介

1什么是 MySQL

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于
Oracle 公司。MySQL 是一种关系型数据库管理系统，关系型数据库将数据保存在不同的表
中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

2MySQL 特点

MySQL 是开源的，所以你不需要支付额外的费用。
MySQL 支持大型系统的数据库。可以处理拥有上千万条记录的大型数据库。
MySQL 使用标准的 SQL（结构化查询语言） 数据语言形式。
MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、
Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
MySQL 存储数据量较大，32 位系统表文件最大可支持 4GB，64 位系统支持最大的
表文件为 8TB。

目前小型系统开发流行的架构(linux,apatche(服务器),php,mysql)

二、MySQL 的安装与卸载

1MySQL 版本说明

MySQL Community Server 社区版本，开源免费，但不提供官方技术支持。
MySQL Enterprise Edition 企业版本，需付费，可以试用 30 天。
MySQL Cluster 集群版，开源免费。可将几个 MySQL Server 封装成一个 Server。
MySQL Cluster CGE 高级集群版，需付费。

2安装 MySQL 8.0
下载地址：https://dev.mysql.com/downloads/installer/
安装依赖
Microsoft Visual C++ 2015下载地址：
https://www.updatestar.com/zh-cn/directdownload/microsoft-visual-c-2015-redistributable-package/2249800

三、MySQL 的客户端工具

1MySQL 自带的客户端端工具

四、MySQL 与 Oracle 的区别

1实例区别

MySQL 是轻量型数据库，开源免费。Oracle 是收费的而且价格非常高。
MySQL 安装完后 300M 而 Oracle 有 5G 左右。
MySql是一般可以直接在一个用户root下建多个数据库,再在数据库下建表
Oracle一般先创建一个用户,再给其授权,再在用户下创建数据库表
默认的编码方式是用Latin1（西欧文）

2操作区别

主键： MySQL 一般使用自动增长类型，而 Oracle 则需要使用序列对象。
单引号的处理： MySQL 里字符串可以用双引号包起字符串也可以用单引号，ORACLE 里只可以用单引号包
起字符串。
分页的 SQL 语句： MYSQL 用 limit，而 Oracle 需要使用 rownum 伪列。
事务处理：MySQL 默认是自动提交，而 Oracle 默认不自动提交，需要用户 CTL 语言
进行事务提交。

五、操作 MySQL

1创建与删除数据库

1.1创建数据库

1.1.1使用命令创建数据库

create database 数据库名 default character set 字符编码;

创建一个scott的数据库，并查看该数据库,及设置编码。
创建数据库：
create database scott default character set utf8;

查看 当前用户下的所有的库：
show databases;

1.2删除数据库

1.2.1使用命令删除数据库

Drop database 数据库名
drop database scott;
2选择数据库

需要在哪个库中创建表需要先选择该数据库。
use 需要选择的库的名称。
use scott;

3MySQL 中的数据类型

3.1数值类型(int)

MySQL 支持所有标准 SQL 数值数据类型。
作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT(类似于java中的bigdecimal)。

数值类型中的长度 m 是指显示长度，并不表示存储长度，只有字段指定 zerofill 时有用
例如：
int不需要指定长度.
int(3)，如果实际值是 1，如果列指定了 zerofill，查询结果就是 002，左边用 0 来
填充

3.2浮点型

3.3字符串型

3.3.1char 和 varchar

1)char(n) 若存入字符数小于 n，则以空格补于其后，查询之时再将空格去掉。所以 char
类型存储的字符串末尾不能有空格，varchar 不限于此。
2) char 类型的字符串检索速度要比 varchar 类型的快。

3.4日期类型(date)

3.5二进制数据(BLOB) 64位中最大单个文件可以存8G

1)BLOB
是以二进制方式存储，不分大小写。
2)BLOB 存储的数据只能整体读出。

4创建表与删除表

4.1创建表

创建一个表
create table student(
sid int primary key,
sname varchar(20) not null
);
insert into student values(1,‘张三‘);

4.2删除表

4.2.1通过 DDL 语句删除表
删除 一个 表
drop table 表名;
drop table student;

6.MySQL 中的约束

6.1约束类型

· 非空约束(not null)
· 唯一性约束(unique)
· 主键约束(primary key)
· 外键约束(foreign key) ,只支持表级别定义外键约束
· 检查约束(目前 MySQL 不支持、Oracle 支持)

6.2创建表

主键设置自动递增：
create table student(
sid int primary key auto_increment, # auto_increment表示自动递增+1
sname varchar(20) not null,
sex char(3),
email varchar(50) unique,
cid int,
constraint fk_clazz_cid foreign key(cid) references clazz(cid) #外键约束,constraint注意constraint不带s,oracle中是constraints

);

create table clazz(
cid int primary key auto_increment,
cname varchar(20) not null
);

insert into clazz values(null,‘高等数学‘);
insert into clazz values(null,‘国际英语‘);

select * from clazz;

insert into student values(null,‘张三‘,‘男‘,‘zhangsan@qq.com‘,1);
insert into student values(null,‘李四‘,‘男‘,‘lisi@qq.com‘,2);
select * from student;
select * from student s join clazz c on s.cid = c.cid;

查询表中的约束信息
show keys from 表名
show keys from student;

7.1.2自动增长(auto_increment)

MySQL 中的自动增长类型要求：
· 一个表中只能有一个列为自动增长。
· 自动增长的列的类型必须是整数类型。
· 自动增长只能添加到具备主键约束与唯一性约束的列上。

7.1.3默认值处理

在 MySQL 中可以使用 DEFAULT 为字段设定一个默认值。如果在插入数据时并未指

定该列的值，那么 MySQL 会将默认值添加到该列中。

7.1.3 插入数据时的默认值处理
如果在插入数据时并未指定该列的值，那么 MySQL 会将默认值添加到该列中。如果是
完全项插入需要使用 default 来占位。

create table student(
sid int primary key auto_increment, # auto_increment表示自动递增+1
sname varchar(20) not null,
sex char(3) default ‘男‘, #处理默认值
email varchar(50) unique,
cid int,
constraint fk_clazz_cid foreign key(cid) references clazz(cid) #外键约束

);
insert into student values(null,‘张三‘,default,‘zhangsan@qq.com‘,1);#默认值用default占位
insert into student values(null,‘李四‘,‘女‘,‘lisi@qq.com‘,2);

8MySQL 中的事务处理

在 MySQL 中，默认情况下，事务是自动提交的，也就是说，只要执行一条
DML (增insert、删delete、改update)语句就开启了事物，并且自动提交了事务

8.1关闭 MySQL 的事务自动提交
select @@autocommit; #autocommit的默认值是1代表事务是开启的,0带关闭
set @@autocommit = 0;#关闭自动提交事务
select @@autocommit;# 查询事务的是否自动提交参数,默认是1,代表默认自动提交
set @@autocommit = 0;

1.4MySQL 中定义空值
包含空值的算术表达式计算结果为空。

通用函数（了解）
coalesce(comm,0) 如果第一个参数为null就返回0
（类似 oracle 的 nvl()函数）

ifnull(expr1,expr2)判断 expr1 是否为 null，如果为 null，则用 expr2 来代替 null
（类似 oracle 的 nvl()函数）

IF(expr1,expr2,expr3)判断 expr1 是否（不为 null），如果为真，则使用 expr2
替代 expr1；如果为假，则使用 expr3 替代 expr1（类似 oracle 的 NVL2()函数）
实例：
从emp标准中查询员工的姓名,月总收入
ifnull(expr1,expr2)

IF(expr1,expr2,expr3)

查询每个员工的年总收入
select ename,(sal+coalesce(comm,0))*12 年薪 from emp;

select ename,(sal+ifnull(comm,0))*12 年薪 from emp;

select ename,(sal+if(comm,comm,0))*12 年薪 from emp;

2.1.2模糊查询

· like
· %表示任意个，任意字符
· _表示一个任意字符

查询 emp 中雇员名字第二个字母是 A的雇员信息。
select * from emp where ename like ‘_A%‘;

查询名字里不包含A的员工
select * from emp where ename not like ‘%A%‘;

2.1.3逻辑运算符

· and 并且,相当于 &&
· or 和
· not 不

查询 emp 表中雇员薪水是 3000 的并且名字中含有 s 的雇员信息
select * from emp where sal =3000 and ename like ‘%S%‘;

查询 emp 表中雇员名字中不包含 M 的雇员信息 NOT LIKE

查询emp表中,叫SCOTT和CLARK的员工
select * from emp where ename =‘scott‘ or ename=‘clark‘;#mysql中对大小写不敏感

3.4日期函数

SYSDATE() 或者 NOW()返回当前系统时间，格式为 YYYY-MM-DD hh-mm-ss
LAST_DAY(date)返回 date 日期当月的最后一天

查询系统的当前时间：
select sysdate() from dual; #from dual可以省略不写
select now();

返回本月的最后一天
select last_day(now());

3.5转换函数

date_format(date,format)将日期转换成字符串（类似 oracle 中的 to_char()）
把当前系统时间按指定格式输出为字符串
select date_format(now(),‘%Y-%m-%d‘);
select date_format(now(),‘%Y年%m月%d日 %H:%i:%s‘);

把字符串的时间转换为date格式
str_to_date(str,format)将字符串转换成日期（类似 oracle 中的 to_date())
select str_to_date(‘1997-7-1‘,‘%Y-%m-%d‘);

往emp表中插入一条记录
insert into emp values(4444,‘张三‘,‘总经理‘,7902,str_to_date(‘2020-3-10‘,‘%Y-%m-%d‘),8000,1000,20);

五.多表连接查询

等值连接

查询雇员 King 所在的部门名称
select * from emp e join dept d on e.deptno = d.deptno where ename=‘king‘;

自连接
查询每个雇员的经理的名字以及雇员的名字。
select e1.ename,e2.ename 上司 from emp e1 join emp e2 on e1.mgr = e2.empno;

查询所有雇员的薪水级别。
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal >= s.losal and e.sal <= s.hisal;

外连接(OUTER JOIN)

左外连接(LEFT [OUTER] JOIN)

查询所有雇员的名字以及他们的部门名称，包含那些没有部门的雇员。
select * from emp e leftjoin dept d on e.deptno = d.deptno;

右外连接(RIGHT [OUTER] JOIN)

查询所有雇员的名字以及他们的部门名称，包含那些没有雇员的部门。
select * from emp e right join dept d on e.deptno = d.deptno;

92sql在mysql支持不完善

全外链接

注意：MySQL 中不支持 FULL OUTER JOIN 连接

六、MySQL 中的其他对象

1索引

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL
的检索速度。

MySQL 中的索引
主键索引

主键索引是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建
表的时候同时创建主键索引。

创建索引(显示创建隐式使用,主键和唯一键会自动创建index)
用法:
当某一列频繁的使用查询时,此列又不是主键或唯一约束,可以考虑给此列创建一个索引,可以大大提高效率
create index index_name on 表名 (列名)
create index index_emp_ename on emp(ename);

删除索引

drop index idx_name on 表名
drop index index_emp_ename on emp; #on emp oracle中不用加on 表名

七、MySQL 分页查询

MySQL 分页查询原则
· 在 MySQL 数据库中使用 limit 子句进行分页查询。
· MySQL 分页中开始位置为 0。
· 分页子句在查询语句的最后侧。

1LIMIT 子句

语法格式

SELECT 列 FROM 表名 WHERE 条件 LIMIT 开始位置，查询数量。

示例

查询emp表中第1行~5行

查询emp表中第6行~10行

查询emp表中第11行~15行

查询分页的公式m代表每页显示m行,n代表第n页

显示emp表的第二页,每页三行数据

select * from emp limit 0,5; #从0(第一行),5表示显示5行
select * from emp limit 5,5;

select * from emp limit 10,5;

m代表每页显示多少行,n代码第n页

select * from emp limit m*n-n,m;

2LIMIT OFFSET 子句分页查询（了解）

语法格式

SELECT 列 FROM 表名 WHERE 条件 [ORDER BY ] LIMIT 查询数量 OFFSET
开始位置。

查询emp表中第1行~5行

查询emp表中第6行~10行

查询分页的公式m代表每页显示m行,n代表第n页

查询emp表中第1行~5行

查询emp表中第6行~10行

推导公式

offset的方式分页

查询emp表中第1行~5行

查询emp表中第6行~10行

推导公式

select * from emp limit 5 offset 0; #limit后跟显示的行数, offset表偏移量0表示第一行

select * from emp limit 5 offset 5;

推导的分页查询公式:m代表每页显示多少行,n代码第n页

select * from emp limit m offset m*n-m;

mysql的远程访问

切换到mysql库下 use mysql;
select * from user;

默认只能有本地ip来访问mysql数据库

update user set host = ‘%‘ where user=‘root‘;

flush privileges;#刷新访问权限

接下来就可以远程访问mysql数据库了

mysql入门以及远程访问