MySql
一、数据库的相关概念及术语:
- 数据库:相关联的数据表的集合
- 数据表:数据的矩阵,结构上像简单的电子表
- 列:具有同样属性类型的数据组成的一列
- 行:一组相关联的数据,如学生的成绩信息
- 冗余:就是多倍的数据,虽然会占用空间但是数据更安全
- 主键:一组数据或一行数据中的唯一标识
- 外键:用来联系其它表,与其它表主键对应
- 复合键:一张表中的多列组成的索引键,用来检索相应的数据
- 索引:用来提高检索速度,就像图书馆分了书籍的类别,以及能使用书名的音序进行了排序。
- 参照完整性:被参照的表必须是一个真实存在且基本关系完整的表。也就是说两张表之间有关系,表B参照表A,那么A必须存在,且有主键和相关数据。
二、常用配置操作
1、安装
wget https://repo.mysql.com//mysql80-community-release-el7-3.noarch.rpm
yum install mysql80-community-release-el7-3.noarch.rpm
vim /etc/yum.repos.d/mysql-community.repo
#修改配置文件的对应项
#[mysql57-community]
#name=MySQL 5.7 Community Server
#...
#enabled=1
#...
#
#[mysql80-community]
#name=MySQL 8.0 Community Server
#...
#enabled=0
#...
yum install mysql-community-server
2、启动和连接
#mysql的基本使用和配置:
#开启|停止|重启|查看状态数据库服务
systemctl start|stop|restart|status mysqld
#连接数据库:
mysql -hlocalhost -uroot -p123456 -P3306
#其他版本的Linux里,root用户的默认密码是空,可以不使用密码直接登录。但是在CentOS7里,mysql安装完成以后,会生成一个临时密码,我们需要通过命令查看到这个默认密码。
cat /var/log/mysqld.log |grep password
#修改root密码:
alter user root@localhost identified with mysql_native_password by 'new_password';
#添加新用户:
GRANT ALL PRIVILEGES on *.* to '用户名'@'localhost' IDENTIFIED BY "密码" WITHGRANT OPTION;
#*.* 代表该用户可以操作任何库、任何表
#主机名可以使用 '%', 代表允许该用户从任何机器登陆
flush privileges;
3、忘记密码的操作:
'''
1. 打开配置: vim /etc/my.cnf
2. 添加这么一段:
[mysqld]
skip-grant-tables
如果文件中已存在 [mysqld] , 则直接将 skip-grant-tables 写到其下方即可。
3. 修改完成后,保存退出,重启服务: sudo systemctl restart mysql.service
4. 使用命令sudo mysql -uroot 重新连接MySql服务器,此时可以不使用密码直接登录用户。
5. 执行update mysql.user set authentication_string=password('你的密码') where
user="root"; 修改root用户的密码。
6. 执行flush privileges 刷新策略,使策略立刻生效,并退出mysql客户端。
7. 修改 /etc/msyql/mysql.cnf 文件,注释掉第二步添加的两段内容。
8. 运行sudo systemctl restart mysql.service 重启mysql服务器。
9. 此时可以使用新密码登录mysql服务器。
'''
4、账户权限等
-
创建账户及授权:
GRANT ALL PRIVILEGES on *.* to '用户名'@'主机' IDENTIFIED BY "密码" WITH GRANT OPTION; flush privileges; -- 刷新使权限生效 ''' ALL PRIVILEGES : 授予全部权限, 也可以指定 select , insert , update , delete , create , drop , index , alter , grant , reference s , reload , shutdown , process , file 十四个权限。 *.* : 允许操作的数据库和表。 主机名:表示允许用户从哪个主机登录, % 表示允许从任意主机登录。 WITH GRANT OPTION : 带有该子句说明允许用户将自己拥有的权限授予别人。 '''
-
修改密码:
update user set authentication_string=password('new_password') where user='root' #也可以使用下面 alter user '用户名'@'主机' identified with mysql_native_password by 'new_password';
-
查看权限:
show grants; -- 查看当前用户的权限 show grants for 'abc'@'localhost'; -- 查看用户 abc 的权限
-
回收权限:
revoke all privileges on *.* from 'abc'@'localhost'; #回收用户 abc 的所有 权限 revoke grant option on *.* from 'abc'@'localhost'; #回收权限的传递
-
删除用户:
use mysql; select host, user from user; drop user 用户名@'%';
-
创建远程用户:
GRANT ALL PRIVILEGES on *.* to 'abc'@'%' IDENTIFIED BY "密码" WITH GRANT OPTION; flush privileges #修改mysql配置文件(不同操作系统,不同MySql版本,配置文件存储不同,ubuntu18.04配置文件保存在/etc/mysql/mysql.conf.d/mysqld.cnf; Centos7.7 配合文件保存在 /etc/my.cnf) sudo vim /etc/my.cnf #然后注释bind-address=127.0.0.1 #请注意服务器的安全组策略是否放开3306端口
三、数据库内容操作
1、数据库层操作
#创库
create database [if not exists] `数据库名` charset=字符编码(utf8mb4);
#1. 如果多次创建会报错
#2. 如果不指定字符编码,默认为 utf8mb4 (一个汉字占用 4 个字节)
#3. 给数据库命名一定要习惯性加上反引号, 防止和关键字冲突, 不加也可以
#查看库
show databases;
#选择库
use database_name;
#改库,仅能改字符集
alter database database_name charset=字符集;
#删库
drop database [if exists] database_name;
2、数据表层操作
#建表
create tbale [if not exists] table_name(
id int not null auto_increment primary key comment '主键',
account char(255) comment '用户名' default 'admin',
pwd text(16383) comment '密码' not null
)charset=utf8
#charset默认是继承库的字符集
#看表
show tables;
#删表
drop table if exists table_name;
#看表结构
desc[ribe] table_name;
#改表
-- 改表名
alter table tname rename new_name;
-- 移动表到指定库, 源库无,表到新库
alter table tname rename to database.table_name
#修改字段
-- 增加新字段
alter table tname add '字段名' 数据类型 [属性];
alter table tname add '字段名' 数据类型 [属性] first;
alter table tname add '字段名' 数据类型 [属性] after 指定字段;
-- 修改属性
alter table tname modify '字段名' 数类 [属性];
-- 修改字段名称
alter table tname change '旧字段名' '新字段名' 数类 [属性];
-- 修改字段位置
alter table tname change '旧字段名' '新字段名' 数类 [属性] after 指定字段;
-- 删除字段
alter table tname drop '字段名';
#复制表
create table '新的表名' like '源表名';
insert into '新表名' select * from '源表名';
-- 上述两条是复制的一模一样的表
create table paste_talbe_name select * from copy_table_name; -- 这种方式虽然有原表结构和数据,但会失去原表属性,再次操作就会出问题
3、CURD的使用
-
C:create,U:update,R:retrieve,D:delete
-
curd语句:
#Create: insert into tname ('字段1','字段2',...) values ('值1','值2',...) #插入数据语法 insert into tname set '字段名'='值', '字段名'='值'...; insert into tanme values (null,...) -- 一次插入一行,value、values均可 insert into tname (...) values (...),(...) -- 一次插入多行 insert into tname values (null,...),(null,...) -- 指定全部字段进行多行插入,这个必须指定id或null #Retieve: select * from tname; select '字段名1','字段名2',... from tname; #Update: update tname set '字1'=value, '字2'=value,...; update tname set '字1'=value,... where '指定字段'='指定值'; update tname set '字1'=value,... where '指定字段'='指定值' and '字2'='指定值'; #Delete: delete from tname; -- 删除表中所有数据,逐行 truncate tname; -- 一次删除所有数据 delete from tname where '字段'='指定value'; delete from tname where '字段' in (1,2,3,...);
四、数据
1、数据类型
-
整型,常用:
tinyint [unsigned] 1 byte -128127/0255 int [unsigned] 4 bytes -2147483648~2147483647 bigint [unsigned] 8 bytes 非常大有亿亿级 int(10) zerofill 不够10位就用0填充 -
浮点型,常用
float 4 bytes 非常大 double 8 bytes 非常大 dec(M,D) M+2 bytes m是总位数,d是小数后位数 支持float(M,D),double(M,D),decimal(M,D)写法
-
字符串,常用:
char(M) 0~255 bytes | M(255) | 0 |
varchar(M) 0~65535 bytes | M(65535) | |
text | M+2 | 65535 bytes |
longtext | M + 4 | 十亿级 bytes |
binary(M) | M +1 | M bytes |
varbinary(M) | M | M bytes |
-
枚举,enum(x, y, z) 。枚举计数从1开始,也可是字符。[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-NUghwZtA-1617161754734)(C:\Users\andy\AppData\Roaming\Typora\typora-user-images\image-20210331100259473.png)]
-
集合,set(x, y, z, …),最多64个成员,但是现代网站多选框有上千个,建议将复选框的值单独建表。[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4BdYl9m0-1617161754740)(C:\Users\andy\AppData\Roaming\Typora\typora-user-images\image-20210331100306612.png)]
-
时间类型
date 4 1000-01-01~9999-12-31 datetime 8 1000-01-01 00:00:00~9999-12-32 23:59:59 timestamp 4 1970-1-1 0:0:0 ~2038-1-19 11:14:07 time 3 -838:59:59~838:59:59 year 1 1901~2155 支持的格式“2019-4-2 16:54:00”,“2019/4/2 16:54:00”。时间戳支持"2019-4-2 16:54:00"和
now()函数是本刻值
时间戳显示同datetime一致,但存储不一样
- boolean,值为1和0,也可是True和False。
2、列属性
- null not null
- default
- auto_increment
- primary key
- unique
- comment
3、注释
- – 单行注释
- /* */多行注释
- mysql独有#
4、算术运算符,±*/%
5、比较运算符
= | |
<> != | |
< | |
<= | |
> | |
>= | |
between | select 123 between 100 and 200 |
in | select ‘b’ in (‘a’,‘b’,‘c’) |
is null | select 12 is null; select null is null; |
is not null | select 123 is not null; |
like | select ‘HelloWorld’ like ‘hello%’; 不区分大小写 |
rlike regexp | |
select 有输出的作用 | |
6、逻辑运算符
not ! | 逻辑非 |
and && | 逻辑与 |
or || | 逻辑或 |
xor | 逻辑异或 |