JavaWeb系列笔记五——Mysql

MYSQL

安装的前期准备

• 安装并解压MYSQL文件

• 初始化文件 my.ini

在mysql目录下,新建my.ini文件,填入下面初始化字段

需将mysql的安装目录和数据库的数据存放目录(data目录自动生成–不需要用户新建)修改为自己电脑的路径.

[mysqld]
# 设置3306端口
port=3306
# 设置mysql的安装目录
basedir=G:\mysql
# 设置mysql数据库的数据的存放目录
datadir=G:\mysql\data  
# 允许最大连接数
max_connections=200
#允许连接失败的次数。防止有人从该主机试图攻击数据库系统
max_connect_errors=10
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
default-character-set=utf8

安装 MySQL 服务

• 搜索 cmd，右键以管理员身份运行 cmd ,进入 bin 目录，进入 bin 目录命令:

>cd /d bin目录所在的绝对路径

• 初始化

>mysqld --initialize --console #初始化MySQL环境

在命令行的最后一行local的后面有一段字符串 ,该字符串为初始密码,需记录下来,为后面修改密码做准备

输入安装指令:

>mysqld install

关闭和启动 MySQL

>net start mysql # 启动命令
>net stop mysql # 启动命令

卸载 MySQL

命令窗口进入bin目录,输入指令

>mysqld remove

然后删除源码包文件夹.

与数据库建立连接

登录 MySQL

打开CMD窗口,输入下面指令登录mysql.该账号为root账号,具有较高权限

>mysql -uroot -p -P3306 -hlocalhost

然后输入初始密码,即可成功登录.

“-u” 后面表示登录用户名，“-p” 表示密码单独输入，“-P” 表示端口号，“-h” 表示主机地址

修改初始密码

输入指令:

>alter user 'root'@'localhost' identified with mysql_native_password by '新密码';

第一次用初始密码登录成功后须修改初始密码，如果不修改初始密码，则会提示错误信息：

创建子账号

使用root账号登录后,输入新建子账号命令:

>create user 'test'@'localhost' identified by '密码';

退出root账号登录,输入指令:

>quit;

使用子账号登录mysql,输入指令:

>mysql -utest -p -P3306 -hlocalhost

注:创建子账号的目的,给不同管理人员分配不同的权限.

## 创建数据库

### 指令规范

> 注意:从现在开始将会频繁使用mysql指令,需要遵守一些指令规范

- 除use database指令外,其他指令都需要在结尾加上分号';'
- 使用英文的标点符号
- 数据库命名规则一般以小写英语字母和_组成
- 数据表命名是小写字母和下划线 `_` 组成，用来分割不同单词之间的含义



### 开始操作

- 登录MYSQl操作界面

```mysql
>mysql -utest -p;

• 查看数据库

>show databases

系统会显示当前已经存在四个MySQL系统数据库，这些数据库用来存储和管理 MySQL 服务相关的一些配置。

• 新建数据库

>create database item_name;

• 也可以利用客户端工具新建数据库

此处略.

删除数据库

在登录状态下,输入指令:

>drop database item_name;

同理,客户端工具也可以删除数据库,此处略.

选中数据库

• 输入选择数据库指令:

>use item_name;

选中数据后才能对数据库进行操作,当想选中另一个数据库时,直接输入use database_name即可

数据类型

整数类型

类型	大小	说明
tinyint	1字节	小整型
smallint	2字节	小整型
mediumint	3字节	中整型
int	4字节	整型
bigint	8字节	大整型

浮点类型

类型	大小	说明
float	4字节	单精度浮点型
double	8字节	双精度浮点型
decimal	每4个字节存储9个数字,小数点占1字节	精确类型,常用来表示金额

日期与时间类型

类型	大小	格式	范围
date	3字节	YYYY-MM-DD	1000-01-01~9999-12-31
time	3-6字节	HH::MM::SS[.微秒]	-838:59:59~838:59:59
year	1字节	YYYY	1901~2155
datetime	5-8字节	YYYY-MM-DD HH::MM::SS[.微秒]	1000-01-01 00:00:00~2038-01-19 03:14:07 UTC
timestamp	4-7字节	YYYY-MM-DD HH::MM::SS[.微秒]	1974-01-01 00:00:00~2038-01-19 03:14:07 UTC

字符串类型

类型	范围	说明
char	1~255个字节	固定长度
varchar	字段存储所占字节数不能超过65535字节	可变长度
tinytest	最大长度255字节	可变长度
text	最大长度65535字节	可变字节
mediumtext	最大字节16777215字节	可变字节
longtext	最大长度4294967295字节	可变字节

char 类型是一种固定长度的类型，varchar 则是一种可变长度的类型，它们的区别是：char(M) 类型的数据列里，每个值都占用 M 个字节，如果某个长度小于 M，MySQL 就会在它的右边用空格字符补足.。

枚举值类型

类型	范围	说明
enum	几何数最大65535	枚举值类型,只能插入列表中指定的值

二进制类型

类型	范围	说明
tinyblob	最大长度255字节	可变长度
blob	最大长度65535字节	可变长度
mediumblob	最大长度16777215字节	可变长度
longblob	最大长度4294967295字节	可变字节

设计数据表

• 一个数据表主要包含信息有 : 表名、主键、字段、数据类型、索引

• 数据表是在数据中进行操作的,所以进行数据表操作时,需要先进入相对应的数据库

字段

以数据表student为例:

字段名称	数据类型	含义
id	无符号整型 (unsigned int)	自增子健
name	varchar(50)	学生姓名
age	unsigned int	学生年龄
id_number	varchar(18)	身份证号

主键

每一张表都必须有一个主键，一般建议选定为无符号整型 id 作为主键，并且 id 一般作为主键一般设置为自增的（特殊情况可使用其他非自增 id 作为主键）

新建数据表

• 学生数据表

create table `student`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '无名',
  `age` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `id_number` varchar(18) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

注:其中 “student” 为表名称，“id”、“name”、“age”、“id_number” 为字段名称，跟在字段名称后面的是字段的数据类型，“UNSIGNED” 表示无符号，“AUTO_INCREMENT” 表示自增，"PRIMARY KEY (id)"表示设置 “id” 为业务主键,，"NOT NULL DEFAULT ‘无名’ " 表示默认不为空，且默认值为 “无名” 。

同理:

• 教师数据表

create table `teacher`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '教师名',
  `age` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `id_number` varchar(18) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
);

• 课程表

create table `course`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `course_name` varchar(50) NOT NULL DEFAULT '',
  `teacher_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);

• 学生选课关联表

create table `student_course`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `student_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
  `course_id` int(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
);

查看数据表

输入查看指令:

show tables;

可查看到新建了四个数据表.

数据表设计规范

第一设计范式

第一设计范式要求表中字段都是不可再分的，如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息.

第二设计范式

第二设计范式要求表中必须存在业务主键，并且全部非主键依赖于业务主键。

第三设计范式

一个数据库表中不包含已在其它表中已包含的非主键字段。就是说表的信息如果能够被推导出来，就不应该单独的设计一个字段来存放（能尽量外键 join 就用外键 join）。

反范式设计

没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，提高读性能，就必须降低范式标准，适当保留冗余数据。具体做法是： 在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，减少了查询时的关联，提高查询效率，因为在数据库的操作中查询的比例要远远大于 DML 的比例。

alter 命令修改表

修改数据表名称

alter table 旧的表名 rename to 新的表名;

查看建表sql语句

show create table 需要查看的表名;

修改建表sql语句

alter table `表名`
modify column `字段1` 数据类型
新字段类型的字符集和编排方式
not NULL default '无名' after `字段2`;

新增表字段

alter table `表名`
add column `新字段` 数据类型 
not NULL default 1 comment '字段名 : 1:数据一 2:数据二' alter `字段2`;

其中comment '字段名 : 1:数据一 2:数据二’表示该字段的注释说明

删除表字段

alter table `表名`
drop column `字段`;

修改表字段名称

alter table `表名`
change column `旧字段名` `新字段名` 数据类型 编码方式

删除数据表

先选中表所在的数据库,然后输入删除指令:

use 数据库;
drop table 表名;

insert 插入数据

insert into 表名
(字段_1,字段_2,字段_3...)
values
(数据,数据,数据...),
(数据,数据,数据...),
...
(数据,数据,数据...);

通过使用真实数据替换上面指令,可进行一条至多条数据的插入.

delete删除数据

删除部分数据

delete from 表名 where 字段=1(假设为1);

选中数据库,然后输入上面指令表明:删除所选表中,所选字段对应数据为1的数据.

删除表全部数据

• delete

delete from 表名;

• truncate

truncate table 表名;

注:

TRUNCATE 清空表数据的实际过程是先删除数据表，然后新建一张和原来表结构一模一样的表来替代清空。

DELETE 删除表数据不会改变自增主键的增长值

select查询数据

查询表中所有的数据

select * from 表名; 

查询指定条数的结果集

select * from 表名 limit 10(假设为10);

输入上述指令将呈现10条结果的结果集.

查询指定其实条数的结果集

select * from 表名 limit 10,10;(假设为10,10);

将输出11-20的十条结果的结果集.

查询指定字段列的结果集

输入以下指令:

select 字段1,字段2 from 表名 limit 6,5;

给指定字段重命名

select 字段1 as 字段3,字段2 from 表名 limlt 6,5;

注:该重命名仅仅使用呈现结果,而不会保存到原数据表

update更新数据

更新某一列字段的值

输入下列指令:

update 表名 set 字段1=数据1 limit 3;

即可将前三条数据中字段1的数据修改为 数据1.

更新多段字段的值

update 表名 set 字段1=数据1,字段2=数据2 where 某字段=某一数据;

即可进行修改.

like模糊查询

模糊查询表达式

% 表示指代任意内容,例如 '%小%' 表示包含 小 的表达式,且 小 前后都有内容, '%小' 表示以 小 结尾的表达式,王 前面有内容,后面没有内容,'小%' 表示以 小 开头的表达式,小 前面没有内容,后面有内容。

使用like模糊查询

输入下面指令:

select * from 表名 where 某字段 like '小%';

即可查看该字段对应的字符串最右边的字符为’小’的结果.

where条件查询

where条件

符号	说明
<	小于
=	等于
>	大于
<>或!=	不等于
<=	小于等于
>=	大于等于
like	模糊条件
not like	不符合模糊条件
between and	在两个值之间(包含两端值)
not between and	不在在两个值之间(不包含两端值)
is null	空
is not null	不为空

单条件查询

select * from 表名 where 条件1;

and多条件查询

select * from 表名 where 条件1 and 条件2;

and表示同时满足两个条件才为真.

or多条件查询

select * from 表名 where 条件1 or 条件2;

只需要满足至少一个条件即为真

union联合查询

union all查询

输入以下指令即可把满足两种查询条件的结果集并到一起：

select * from 表名 where 条件1
union all
select * from 表名 where 条件2;

注: UNION ALL 联合查询的结果集没有去掉重复的数据.

union查询

select * from 表名 where 条件1
union
select * from 表名 where 条件2;

注:UNION 将两种查询结果并到一起，可以看到结果集中已经去掉重复的数据

order by排序

ASC 从小到大排序

输入以下指令:

select * from 表名 order by 某字段 ASC;

即可根据该字段数据实现从小到大排序

DESC 从大到小排序

select * from 表名 order by 某字段 DESC;

多字段混合排序

select * from 表名 order by 字段1 DESC,字段2 ASC;

根据优先级是:先进行根据字段1的降序,在此基础上再进行根据字段2的升序.

对字符串排序

对字符串排序和对数值进行排序的区别在于:英文字符排序在前，中文在后，其中排序规则是按照字符 ASCII码 对应值的大小排序.

join表连接

left左连接

输入以下指令:

select a.原字段名 as 新字段名,a.*,b.* 
from 表一  a 
left join 表二 b
on a.字段1=b.字段2;

• a.* 表示 表一所有字段数据；

• t.* 表示 表二 表字段所有数据；

• ON 后面跟着的条件是连接表的条件；

• 表一 a 表示将 表一 简写为 a， 表二 b 表示将 表二 简写为 b；

• left join 为左连接，是以左边的表为’基准’，若右表没有对应的值，用 NULL 来填补。

同理:

• right join 为右连接，是以右边的表为’基准’，若左表没有对应的值，用 NULL 来填补。

当然,内连接有些许不同:

• INNER JOIN 为内连接，展示的是左右两表都有对应的数据。

多表混合连接

即一或多种连接方式的多次使用:

select a.原字段名 as 新字段名,a.*,b.* 
from 表一  a 
left join 表二 b
on a.字段1=b.字段2
right join 表三 c
on b.字段2=c.字段3
inner join 表四 d
on b.字段3=d.字段4;

该多表连接以表二的字段2为基准.

distinct去重

输入下列指令:

select
distinct 字段1,字段2,...字段n
from 
表一 a
inner join
表二 b
on a.字段1=b.字段2;

group by分组

单字段分组

输入以下指令:

select a.原字段名 as 新字段名,a.*,b.* 
from 表一  a 
left join 表二 b
on a.字段1=b.字段2
right join 表三 c
on b.字段2=c.字段3
inner join 表四 d
on b.字段3=d.字段4
group by 某字段;

即可完成单字段分组.

多字段分组

输入以下指令:

select a.原字段名 as 新字段名,a.*,b.* 
from 表一  a 
left join 表二 b
on a.字段1=b.字段2
right join 表三 c
on b.字段2=c.字段3
inner join 表四 d
on b.字段3=d.字段4
group by a.字段1,b.字段2;

即可完成所需分组.

单字段分组和多字段分组的区别在于，单字段是以一个字段来判断数据是否重复分组出来的结果，多字段分组是以多个字段同时来判断是否重复分组出来的结果。

聚合函数

函数	作用
avg()	计算平均值
sum()	计算总和
count()	计算总条数
min()	取最小值
max()	取最大值

Mysql进阶

数据库的备份和还原

• 备份
  mysqldump -u用户名 -p密码 > 保存的路径
• 还原
  1. 登录数据库
  2. 创建数据库
  3. 使用数据库
  4. 执行文件：source 文件路径

事务

• 概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败
• 操作：
  1. 开启事物：start transaction;
  2. 回滚：rollback;
  3. 提交：commit;

事务的四大特征

• 原子性：是不可分割的最小操作单位，要么同时成功，要么同时失败
• 持久性：当事务提交或回滚后，数据库会持久化地保护数据
• 隔离性：多个事务之间相互独立
• 一致性：事务操作前后，数据总量不变

事务的隔离级别

• 概念：多个事务之间隔离的，相互独立的。但是如果多个事务操作同一批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题
• 存在问题：
  • 脏读：一个事务，读取到另一个事务没有提交的数据
  • 不可重复读：在同一个事务中，两次读取的数据不一样
  • 幻读：一个事务操作（DML）数据表中所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改
  • 结合例子理解幻读：
    两个并发事务A、B。在T这个时刻，事务A通过筛选条件获取到的记录是10条。此时并发事务B往数据库插入一条满足事务A查询条件的记录。这时A还没有结束，在事务内再次获取数据时以相同的条件筛选，结果筛选出了11条记录。这就是幻读
• 隔离登记：
  1. read uncommitted：读未提交
     产生问题：脏读，不可重复读，幻读
  2. read committed：读已提交（oracle）
     产生的问题：不可重复读，幻读
  3. repeatable read：可重复读（Mysql默认）
     产生的问题：幻读
  4. serializable：串行化
     可以解决所有问题

  注意：隔离级别从小到大安全性越来越高，但是效率越来越低

• 数据库查询隔离级别
  select @@tx_isolation;
• 数据库设置隔离级别
  set global transaction isolation level 级别字符串;

管理用户DCL

添加用户

create user ‘用户名’@‘主机名’ inentified by ‘密码’;

删除用户

drop user ‘用户名’@‘主机名’;

修改用户密码

ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘1234’;

mysql中忘记了root用户的密码

1. cmd（使用管理员权限） --> net stop mysql 停止mysql服务
2. 使用无验证方式启动mysql服务：mysqld --skip-grant-tables
3. 打开新的cmd窗口，直接输入mysql命令，敲回车，就可以登录成功
4. use mysql;
5. update user set password = password(‘你的新密码’) where user = 'root;
6. 关闭两个窗口
7. 打开任务管理器，手动结束mysqld.exe的进程
8. 启动mysql服务
9. 使用新密码登录

权限管理

• 查询权限

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

范例：
show grants for 'lisi'@'%';

• 授予权限

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

• 撤销权限

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

通配符：%表示可以在任意主机使用用户登录数据库