MySQL安装及常用命令

Mysql安装

CentOS安装Mysql

卸载maridb和mysql安装包

rpm -qa | grep maridb | xargs yum -y remove

rpm -qa | grep mysql | xargs yum -y remove

下载安装mysql.rpm

wget -P /etc/yum.repos.d/ https://repo.mysql.com/mysql57-community-release-el7-9.noarch.rpm

rpm -ivh /etc/yum.repos.d/mysql57-community-release-el7-9.noarch.rpm

rm -f /etc/yum.repos.d/mysql57-community-release-el7-9.noarch.rpm

查看mysql的yum源是否生效

yum list | grep mysql

安装mysql

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

yum install -y mysql-community-server

配置/etc/mysql/my.cnf

skip-grant-tables   # root默认不需要密码

character-set-server=utf8   # 数据库编码utf8

default-storage-engine=innodb   # 数据库引擎

启动mysql服务

systemctl start mysqld

设置开机启动

systemctl enable mysqld

systemctl deamon-reload

Ubuntu安装Mysql

卸载mysql

apt purge -y mysql*

删除遗留文件

rm -rf /etc/mysql /var/lib/mysql

查看apt库mysql版本

apt list | grep mysql-server

安装mysql

apt install -y mysql-server-8.0

启动mysql服务(两条命令等价，选择一个即可)

systemctl start mysql.service

service mysql start

设置开机启动

systemctl enable mysqld

systemctl deamon-reload

SQL分类

        DDL【data definition language】 数据定义语言，用来维护存储数据的结构;

        代表指令: create, drop, alter

        DML【data manipulation language】 数据操纵语言，用来对数据进行操作;

        代表指令： insert，delete，update

        DML中又单独分了一个DQL，数据查询语言，代表指令： select

        DCL【Data Control Language】 数据控制语言，主要负责权限管理和事务;

        代表指令： grant，revoke，commit

DDL

查看存储引擎

 show engines;

查看字符集和校验规则

查看系统默认字符集

show variables like 'character_set_database';

查看系统默认校验规则

show variables like 'collation_database';

查看数据库支持字符集

show charset;

查看数据库支持校验规则

show collation;

创建数据库

create database db1 charset=utf8 collate=utf8_general_ci

创建一个使用utf字符集，并带校对规则的 db1 数据库

collate:默认不区分大小写，utf8_bin区分大小写

操作数据库

创建数据库

create database db1;

查看数据库

show databases;

select database();

show create database db1;

修改数据库

alter database db1 charset=gbk;

删除数据库

drop database db1;

数据库备份和恢复

数据库备份

mysqldump -uroot -p -B db1 > db1.sql

数据库恢复

source db1.sql;

同时备份多个

# mysqldump -u root -p 数据库名 表名1 表名2 > D:/mytest.sql

# mysqldump -u root -p -B 数据库名1 数据库名2 ... > 数据库存放路径

查看数据库连接情况

show processlist;

表操作

创建表

create table t1 (

field1 datatype,

field2 datatype,

field3 datatype

) character set 字符集 collate 校验规则 engine 存储引擎;

查看创建表记录

show create table t1\G

查看表结构

desc t1;

操作表字段

alter table t1 add name varchar(20);

alter table t1 modify name varchar(30);

alter table t1 change name nm varchar(30);#新字段要加上属性

alter table t1 drop name;

修改表名

alter table t1 rename to tt1;#to可以省略

删除表

drop table t1,t2;

表数据类型

数值类型

        默认使用有符号，无符号需加上unsigned。

        int(11),11表示最少要显示的字符数宽度，和具体的存储字节无关，只有配合zerofill属性时有意义。

bit类型

bit[(M)] : 位字段类型。M表示每个值的位数，范围从1到64。如果M被忽略，默认为1；

bit类型是无符号整数，如果插入小数，会按照四舍五入转化成整数插入；

最大值为bit(64)，对应十进制同无符号bitint。

小数类型

float(4,2),4表示长度，2表示小数位数，取值范围-99.99~99.99，unsigned取值返回0~99.99，精度大约是7位；

decimal(4,2),同float，精度不同，长度最大为60，小数位数最大为30。

小数部分如果超过有效位数，会按四舍五入进行进位。

 字符串类型

char(L): 固定长度字符串，L是可以存储的长度，单位为字符，最大长度值可以为255.

varchar(L): 可变长度字符串， L 表示字符长度，最大长度 65535 个字节。

varchar长度可以指定为0到65535之间的值，但是varchar字段需要有1 - 3 个字节用于记录数据大小，所以说一个varchar最大有效字节数是65532。

当我们的表的编码是utf8时，varchar(n)的参数n最大值是65532/3=21844[因为utf中，一个字符占用3个字节]，如果编码是gbk，varchar(n)的参数n最大是65532/2=32766（因为gbk中，一个字符占用2字节)。需要注意65535个字节是对于一行数据来说，因此定义varchar大小时还要去掉一行中其他列已占用的字节数。

text: 用于存储较长的字符串，最长可以存储65535个字符。

如何选择定长或变长字符串？

        如果数据确定长度都一样，就使用定长（char），比如：身份证，手机号，md5

        如果数据长度有变化,就使用变长(varchar), 比如：名字，地址，但是你要保证最长的能存的进去。

        定长的磁盘空间比较浪费，但是效率高。

        变长的磁盘空间比较节省，但是效率低。

        定长的意义是，直接开辟好对应的空间。

        变长的意义是，在不超过自定义范围的情况下，用多少，开辟多少。

日期类型

date :日期 'yyyy-mm-dd' ，占用三字节

time: 时间'HH:ii:ss'，占用三字节

datetime 时间日期格式 'yyyy-mm-dd HH:ii:ss' 表示范围从 1000 到 9999 ，占用八字节

timestamp ：时间戳，从1970年开始的 yyyy-mm-dd HH:ii:ss 格式和 datetime 完全一致，占用四字节，显示输入NULL或者不输入，会自动更新当前时间戳。

enum和set类型

enum：枚举，“单选”类型；

enum('选项1','选项2','选项3',...);

该设定只是提供了若干个选项的值，最终一个单元格中，实际只存储了其中一个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,3,....最多65535个；当我们添加枚举值时，也可以添加对应的数字编号。

set：集合，“多选”类型；

set('选项值1','选项值2','选项值3', ...);

该设定只是提供了若干个选项的值，最终一个单元格中，设计可存储了其中任意多个值；而且出于效率考虑，这些值实际存储的是“数字”，因为这些选项的每个选项值依次对应如下数字：1,2,4,8,16,32，.... 最多64个。

集合查询使用find_ in_ set函数：

find_in_set(sub,str_list) ：如果 sub 在 str_list 中，则返回下标；如果不在，返回0； str_list 用逗号分隔的字符串。

表的约束

comment

        列描述：comment，没有实际含义，专门用来描述字段，会根据表创建语句保存，用来给程序员或DBA来进行了解。

desc

        查看表结构，但是看不到注释，使用show create table t1 \G命令可以看到表详细信息。

zerofill

        如果宽度小于设定的宽度，会在前面填充0字符。

primary key主键

增加和删除主键

alter table 表名 add primary key(字段列表)

alter table 表名 drop primary key

主键不能为NULL，创建时默认就是不为NULL的。

主键可以在创建表的时候跟在字段后面，也可以单独作为一个列属性，如果是多个字段就是复合主键，可以把主键看成是一个表中的特殊列属性进行管理。

auto_increment自增长

        任何一个字段要做自增长，前提是本身是一个索引（key一栏有值）；

        自增长字段必须是整数；

        一张表最多只能有一个自增长；

        通常与主键搭配使用作为逻辑主键。

unique key唯一键

        unique可以为NULL，但是不会出现重复的数据。

外键

foreign key (字段名) references 主表(列)

alter table t9 add constraint myfon foreign key(classid) references t10(id);

if exists

创建删除表时用作判断可以避免报错

drop table if exists t11;

create table if not exists t11(id int);

DML

插入insert(duplicate重复的)

插入主键或者唯一键冲突

INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')

ON DUPLICATE KEY UPDATE sn = 10010, name = '唐大师';

Query OK, 2 rows affected (0.47 sec)

-- 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等

-- 1 row affected: 表中没有冲突数据，数据被插入

-- 2 row affected: 表中有冲突数据，并且数据已经被更新

-- 通过 MySQL 函数获取受到影响的数据行数

SELECT ROW_COUNT();

替换replace

-- 主键 或者 唯一键 没有冲突，则直接插入；

-- 主键 或者 唯一键 如果冲突，则删除后再插入

REPLACE INTO students (sn, name) VALUES (20001, '曹阿瞒');

Query OK, 2 rows affected (0.00 sec)

-- 1 row affected: 表中没有冲突数据，数据被插入

-- 2 row affected: 表中有冲突数据，删除后重新插入

        duplicate和replace执行的结果都是如果出现唯一键重复，则新数据在原重复数据上进行更新。

运算符<=>

        等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)

分页limit

-- 起始下标为 0

-- 从 0 开始，筛选 n 条结果

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;

-- 从 s 开始，筛选 n 条结果

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n;

-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用

SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

截断表truncate

TRUNCATE [TABLE] table_name

只能对整表操作，不能像 DELETE 一样针对部分数据操作；

实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚；

会重置 AUTO_INCREMENT 项

创建复制表结构

创建复制表，只是复制表结构，数据不复制

create table no_duplicate_table like duplicate_table;

插入查询结果(复制表数据) 

insert into no_duplicate_table select * from duplicate_table;

重命名表名rename

重命名表名，两种方式

alter table duplicate_table rename to old_duplicate_table;

rename table duplicate_table to old_duplicate_table,no_duplicate_table to duplicate_table;

日期函数

字符串函数

数字函数

其他函数

user() 查询当前用户;

database()显示当前正在使用的数据库;

md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串;

password()函数，MySQL数据库使用该函数对用户加密，41位字符串;

ifnull（val1， val2） 如果val1为null，返回val2，否则返回val1的值.

索引

        索引分为主键索引，唯一索引，普通索引，全文索引。

主键索引

-- 在创建表的时候，直接在字段名后指定 primary key

create table user1(id int primary key, name varchar(30));

-- 在创建表的最后，指定某列或某几列为主键索引

create table user2(id int, name varchar(30), primary key(id));

-- 创建表以后再添加主键

create table user3(id int, name varchar(30));

alter table user3 add primary key(id);

alter table user3 modify id int primary key;

--删除主键

alter table user3 drop primary key;

主键索引的特点：

        一个表中，最多有一个主键索引，当然可以使符合主键；

        主键索引的效率高（主键不可重复）；

        创建主键索引的列，它的值不能为null，且不能重复；

        主键索引的列基本上是int。

唯一索引 

-- 在表定义时，在某列后直接指定unique唯一属性。

create table user4(id int primary key, name varchar(30) unique);

-- 创建表时，在表的后面指定某列或某几列为unique

create table user5(id int primary key, name varchar(30), unique(name));

-- 创建表以后再添加索引

create table user6(id int primary key, name varchar(30)）；

alter table user6 add unique(name);

alter table user6 modify name varchar(30) unique;

--删除唯一索引

alter table user6 drop index name;

drop index name on user6;

唯一索引的特点：

        一个表中，可以有多个唯一索引；

        查询效率高；

        如果在某一列建立唯一索引，必须保证这列不能有重复数据；

        如果一个唯一索引上指定not null，等价于主键索引。

普通索引

--在表的定义最后，指定某列为索引

create table user8(id int primary key,

name varchar(20),

email varchar(30),

index(name)

);

--创建完表以后指定某列为普通索引

create table user9(id int primary key, name varchar(20), email varchar(30));

alter table user9 add index(name);

-- 创建一个索引名为 idx_name 的索引

create table user10(id int primary key, name varchar(20), email varchar(30));

create index idx_name on user10(name);

--删除索引

alter table user9 drop index name;

drop index idx_name on user10;

普通索引的特点：

        一个表中可以有多个普通索引，普通索引在实际开发中用的比较多；

        如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引。

全文索引

        当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。MySQL提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版(coreseek)。

--在表的定义最后，指定某列为索引

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

)engine=MyISAM;

--创建完表以后指定某列为全文索引

alter table articles add fulltext(title,body);

--删除索引

alter table articles drop index title;

drop index title on articles;

        全文索引需要特定查询格式才可以用到索引，否则不会走索引

 select * from articles where match(title,body) against ('database');

explain

        可以查看sql的执行计划。

查询索引

show keys form 表名\G

show index from 表名\G

desc 表名; 

预编译

创建预编译语句

prepare stmt1 from 'select * from stu where id = ?';

设置会话变量（必须加@）

set @a = '101';

执行预编译命令

execute stmt1 using @a;

删除服务端预编译缓存

deallocate prepare stmt1;

查看服务端已设置的预编译信息

SELECT * FROM performance_schema.prepared_statements_instances \G;

事务

事务提交方式

查看事务提交方式

show variables like 'autocommit';

禁止自动提交

set autocommit = 0;

开启自动提交

set autocommit = 1;

设置隔离级别

查看全局隔离级别

select @@global.tx_isolation;

查看当前会话隔离级别

select @@session.tx_isolation;

select @@tx_isolation;

设置全局隔离级别，设置后重启客户端生效

set global transaction isolation level read committed;

read uncommitted读未提交，read committed读提交，repeatable read可重复读，serializable串行化。

事务开启/回滚/提交

开启事务

start transaction;

begin;

执行回滚或提交结束事务

rollback;

commit;

设置保存点和回退保存点

savepoint a;

rollback to a;

当前读(行级锁)

如果获取行级锁进入阻塞，当前读需等待其他事务释放锁，或者等待获取锁超时才可继续操作

select * from 表名 lock in share mode;

select * from 表名 for update;  

视图

创建视图

create view 视图名 as select语句；

删除视图

drop view 视图名;

用户管理

查看用户

use mysql;

select host,user,authentication_string from user;

密码强度修改

在/etc/my.cnf配置文件中添加密码配置插件，重启服务

plugin-load-add=validate_password.so #插件的加载方法，每次服务器启动时都必须给出该选项
validate-password=FORCE_PLUS_PERMANENT #validate-password在服务器启动时使用该选项来控制插件的激活

查看密码强度配置

show variables like 'validate_password%';

设置密码策略最低级别

set global validate_password_policy=0;

设置密码有效长度 4 位及以上

set global validate_password_length=4;

刷新mysql权限

flush privileges;

创建删除用户

create user '用户名'@'登陆主机/ip' identified by '密码';

drop user '用户名'@'登陆主机/ip';

修改用户密码

修改自己密码

set password=password('新的密码');

root用户修改指定用户密码

set password for '用户名'@'主机名'=password('新的密码');

alter user '用户名'@'登陆主机/ip' identified by '密码';

mysql8.0以上

修改密码已不再支持password函数。通过查看user表可以看到默认密码插件。

select user,plugin,authentication_string from mysql.user;

root密码默认为空，修改root密码必须按下列语句修改，否则不生效

alter user 'root'@'localhost' identified with caching_sha2_password by 'root';

普通用户创建和修改跟之前版本一致

create user 'test'@'localhost' identified by 'root';

alter user 'test'@'localhost' identified by 'rootroot';

查看结果

用户授权

授予权限

grant 权限列表 on 库.对象名 to '用户名'@'登陆主机';

撤销权限

revoke 权限列表 on 库.对象名 from '用户名'@'登陆主机';

权限列表如果是all表示所有权限。

权限列表查看

select * from mysql.user \G

查看用户权限

show grants;

show grants for 'whb'@'%';