文章目录
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密码 > 保存的路径 - 还原
- 登录数据库
- 创建数据库
- 使用数据库
- 执行文件:source 文件路径
事务
- 概念:如果一个包含多个步骤的业务操作,被事务管理,那么这些操作要么同时成功,要么同时失败
- 操作:
- 开启事物:start transaction;
- 回滚:rollback;
- 提交:commit;
事务的四大特征
- 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败
- 持久性:当事务提交或回滚后,数据库会持久化地保护数据
- 隔离性:多个事务之间相互独立
- 一致性:事务操作前后,数据总量不变
事务的隔离级别
- 概念:多个事务之间隔离的,相互独立的。但是如果多个事务操作同一批数据,则会引发一些问题,设置不同的隔离级别就可以解决这些问题
- 存在问题:
- 脏读:一个事务,读取到另一个事务没有提交的数据
- 不可重复读:在同一个事务中,两次读取的数据不一样
- 幻读:一个事务操作(DML)数据表中所有记录,另一个事务添加了一条数据,则第一个事务查询不到自己的修改
- 结合例子理解幻读:
两个并发事务A、B。在T这个时刻,事务A通过筛选条件获取到的记录是10条。此时并发事务B往数据库插入一条满足事务A查询条件的记录。这时A还没有结束,在事务内再次获取数据时以相同的条件筛选,结果筛选出了11条记录。这就是幻读
- 隔离登记:
- read uncommitted:读未提交
产生问题:脏读,不可重复读,幻读 - read committed:读已提交(oracle)
产生的问题:不可重复读,幻读 - repeatable read:可重复读(Mysql默认)
产生的问题:幻读 - serializable:串行化
可以解决所有问题
注意:隔离级别从小到大安全性越来越高,但是效率越来越低
- read uncommitted:读未提交
- 数据库查询隔离级别
select @@tx_isolation; - 数据库设置隔离级别
set global transaction isolation level 级别字符串;
管理用户DCL
添加用户
create user ‘用户名’@‘主机名’ inentified by ‘密码’;
删除用户
drop user ‘用户名’@‘主机名’;
修改用户密码
ALTER USER ‘root’@‘localhost’ IDENTIFIED BY ‘1234’;
mysql中忘记了root用户的密码
- cmd(使用管理员权限) --> net stop mysql 停止mysql服务
- 使用无验证方式启动mysql服务:mysqld --skip-grant-tables
- 打开新的cmd窗口,直接输入mysql命令,敲回车,就可以登录成功
- use mysql;
- update user set password = password(‘你的新密码’) where user = 'root;
- 关闭两个窗口
- 打开任务管理器,手动结束mysqld.exe的进程
- 启动mysql服务
- 使用新密码登录
权限管理
- 查询权限
show grants for '用户名'@'主机名';
范例:
show grants for 'lisi'@'%';
- 授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
通配符:%表示可以在任意主机使用用户登录数据库