MySql常用命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
mysql -u用户名 -p密码		#登录数据库
quit		#退出数据库

show databases;		        #查看数据库
use 库名;					  #进入数据库，切换数据库cd
Show tables;				#查看当前所在数据库中有哪些表
desc 表名;				  #查看表的字段结构数据类型
select * from 表名;		  #查看表里的信息数据内容
select * from 表名 where 字段名(id)=数据信息(3);	#等值查询，where指定查看表里id=3的信息

create database 库名;		  #创建数据库
show create database 库名;  #查看创建数据库时的语句
create database 库名 character set utf8;		#创建数据库时指定字符集为utf8
alter database 库名 character set utf8mb4;	#修改数据库字符集为utf8mb4
create user 新用户名@'主机域.%' identified by '密码';	#创建数据库的新用户

select user();		#查看当前登录用户
select user,host from mysql.user;	#查看数据库中的用户
drop user 'root'@'用户名';			  #删除数据库中的用户
show variables like "%character%";		#查看数据库字符集

create user 新用户名@'主机域.%' identified by '密码';	#创建新用户
mysql -u新用户名 -p密码 -h10.0.0.100(主机域)		#新用户登录-h

alter user 用户名@'主机域' identified by '新密码';		#修改用户密码方
update mysql.user set password=PASSWORD('密码') where user='用户名' and host='IP.%';	#另一种修改用户密码

flush privileges;		#刷新落盘，刷新的意思
mysqladmin password '123456'	#给管理员root设置密码，注意不是在数据库里

drop database 库名;		  		  #删除数据库
drop table 表名;			  		  #删除表
alter table 表名 drop 字段名;		#删除表里某个字段、类型结构,不带column也能删除
alter table 表名 drop column 字段名;	#删除表里某个字段、类型结构，

rename table 表名 to 新表名;			#修改表名

alter table 表名 change 字段名 新字段名 类型[int(),varchar(),tinyint()]; 		#修改字段名和字段的结构
alter table 表名 change 字段名 字段名 int(3); 			#修改字段类型为int(3)

select database();               #查看当前路径
show databases like "%库名%";	   	#筛选查看指定某个库
show tables like "%表名%";		#筛选查看指定某个表

------

新增字段haha哈哈并设置数据类型
alter table 表名 add haha int(20);   		 	 		     #不设置null，默认是yes
alter table 表名 add haha int(20) not null; 	 		     #null设置为NO，默认是yes
alter table 表名 add haha int(20) after xuhao;	  		     #创建haha字段在xuhao字段的后面，就是指定插入的位置
alter table 表名 add haha int(20) not null first;		 	 #first 添加到第一列，不带first默认都是最后一列；
alter table 表名 add haha int(20) not null comment'说明';    #commen''添加说明
alter table 表名 add haha int(20) not null default '9' comment'你好' after id;	#添加字段haha，设置null为no，default为9，并指定插入在id字段下面

#alter table 表名 add column 字段名 数据类型		#add后面带column也能创建字段，还没搞明白删除添加带column啥意思
-------

select count(*) from 表名;	#查询表里一共有多少条数据，count(*) 
select * from 表名 where 字段名='数据信息';	#等值查询，where只查看这个表里字段='xxx'的全部条数据
select * from 表名 where 字段名='xxx' limit 10;   #(limit 10)限制查询，where只查看这个表里字段='xxx'的10条数据
select 字段名 from 表名 where 字段名='xxx' limit 5;		#只查看xxx表里xxx字段等于xxx数据的5条全部信息
select 字段名1,字段名2 from 表名 where 字段名='xxx' limit 5;	#只查看表里字段1和字段2，where字段里=xxx的5条数据
select * from 表名 where 字段名 >=7000;	#区间查询，只查看这个表字段大于等于7000的全部数据
#>(大于号)、 >=(大于等于)、 <(小于)、 <=(小于等于)、 !=(不等于)
select * from 表名 where 字段名1 > 7000 and 字段名2='xxx';	#逻辑查询，只查看这个表字段1里大于7000，且字段2=xxx的数据，
select * from 表名 where 字段名1 > 7000 or 字段名2='xxx' limit 10;	#逻辑查询，只查看这个表字段1里大于7000和字段2里=xxx的十条数据
select * from 表名 where 字段名1 > 1000 and 字段名1 < 2000;	  #多条件查询，查看这个表的字段名1里大于1000小于2000的全部数据
select * from 表名 where 字段名1 like "k%";	#模糊查询，只查看这个表的字段1里，k开头的全部数据
select * from 表名 where 字段名1 like "%h";	#模糊查询，只查看这个表的字段1里，h结尾的全部数据
select * from 表名 where 字段名1 like "k%h";	#模糊查询，只查看这个表的字段1里，以k开头h结尾的全部数据
select * from 表名 where 字段名1 like "%p%";	#模糊查询，只查看这个表的字段1里，带p的全部数据

select * from 表名 where 字段名 in ('xxx1','xxx2');			#in 在...里
select * from 表名 where 字段名 not in ('xxx1','xxx2');		#not in 不在...里
select * from 表名 where 字段名 between 1000 and 2000;		#在...之间（数值区间）

 mysql -uroot -p123456 < /home/world.sql	#将world.sql文件导入数据库里，注意不是在数据库里操作
 source /home/world.sql;					#将world.sql文件导入数据库里，在数据库里操作

1.MySql的基本操作

  • 环境Centos7
  • 安装mariadb
1
2
3
4
5
#优化yum，为阿里源
curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
curl -o /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-7.repo
#优化过省略此步骤，直接yum -y安装
yum -y install mariadb-server mariadb
  • 启动数据库
1
2
3
4
5
6
systemctl start mariadb.service			#启动数据库
systemctl enable mariadb.service		#设置开机自启动
systemctl stop mariadb.service			#关闭数据库
systemctl disable mariadb.service		#禁止开机自动启动
systemctl status mariadb.service		#查看数据库状态
rpm -qa | grep mariadb-server			#查看数据库版本
  • 介绍
1
2
3
4
5
6
7
8
9
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |    #【默认】：系统运行的状态信息，性能信息的存储库
| mysql              |    #【默认】：授权权限，用户管理的数据库
| performance_schema |    #【默认】：系统运行的状态信息，性能信息的存储库
| test               |    #测试库，让用户先测试使用的
+--------------------+

1.2.数据库的相关操作设置

  • 修改数据库密码（知道密码）
1
[root@Centos7 ~]# mysqladmin -uroot -p123456 password '654321'
  • 修改数据库密码（不知道密码）
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#1.关闭数据库。如果关闭数据库查看进程仍然存在 pkill mysqld
systemctl stop mariadb.service
#2.关闭授权表，跳过网络启动数据库
mysqld_safe --skip-grant-table --skip-network &
#3.查看是否启动
ps -ef | grep mysqld
#4.启动了此时直接mysql登录数据库
mysql
#5.查看用户表信息
MariaDB [(none)]> select user,host,password from mysql.user;
#6.修改用户表中密码字的信息
MariaDB [(none)]> update mysql.user set password=PASSWORD("123456789") where user='root' and host='localhost';
#7.刷新落盘
MariaDB [(none)]> flush privileges;
#8.退出数据库
quit
#9.杀死mysqld进程
pkill mysqld
#10.正常启动数据库
systemctl start mariadb.service
#11.登录数据库
mysql -uroot -p123456789
  • 查看用户权限

语法：【show grants for 用户名@’主机域’】

1
MariaDB [(none)]> show grants for root@'10.0.0.%';
  • 给用户授权

语法：【grant 权限 on 库.表 to 用户名@’主机域’;】

1
2
3
4
5
6
7
MariaDB [(none)]> grant all on *.* to root@'10.0.0.%';

all    #所有的权限
*.*    #所有的库、所有的表

#刷新落盘；
MariaDB [(none)]> flush privileges;
  • 权限设置案例
1
2
3
4
5
#回收用户（123）的drop删除权限，10.0.0.%是主机域
MariaDB [(none)]> revoke drop on *.* from 123@'10.0.0.%';

#查看mysql系统中都有哪些权限
show privileges;
  • 永久设置字符集
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#1.服务端配置文件配置字符集
vim /etc/my.cnf
......
[mysqld]
character-set-server=utf8mb4
......
#2.客户端配置文件配置字符集
vim /etc/my.cnf.d/client.cnf 
......
[client]
default-character-set=utf8mb4
......
[client-mariadb]
default-character-set=utf8mb4
......
#3.重启数据库服务
systemctl restart mariadb.service 
#4.验证是否配置成功
MariaDB [(none)]> show create database 库名;
  • DML语句
1
2
3
4
5
6
7
8
#定义：主要针对数据库当中的数据表进行操作，用来操作数据的语句；
	增删改查数据；
	- insert  #表中插入数据
	- delete  #删除表中的数据
	- update  #修改表中的数据
	- select  #查询数据表中的数据
#查询DML语句帮助
MariaDB [(none)]> ? data manipulation;

2.MySql表的基操

2.1数据类型和语法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
#整型
tinyint  #整型（小）  1byte==8bit==256  【-128~127】
int      #整型（大）   4byte=32bit==（40亿+）
bigint   #整型（超大） 8byte=64bit==（老大了）

#字符类型
varchar  #变长，varchar（3），
			= abc存储abc
			= a  存储 a
			= abcd 存储 abc （把最后一个丢掉）
char     #定长，char(3)
            = abc 存储 abc
            = a  存储 a + 空格 + 空格
            = abcd 存储 abc （把最后一个丢掉）
1
2
3
4
5
6
7
语法
create table 表名(
    -> 字段 数据类型
    -> ......
    -> 字段 数据类型
    -> primary key()
    -> );

2.2创建一个表名为：shujubiao

xuhao、minghzi、nianling……都是每个字段的名字，后面跟的int()、varchar()、not null……都是设置这个字段数据类型的参数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#创建shujubiao,字段内容有序号、名字、年龄、性别、身高、体重、结婚
MariaDB [test]> create table shujubiao(
    -> xuhao int(10) not null  primary key auto_increment comment '这是序号',	# comment''：添加说明
    -> mingzi varchar(20) not null comment '这是名字',
    -> nianling tinyint(3) not null default '0',		#default '0'：设置default为0
    -> xingbie varchar(3) default null,					#default null：设置null为yes
	-> shengao int(3) not null,
	-> tizhong int(3) not null,
	-> jiehun varchar(3) default 'no'
    -> ) engine=innodb charset=utf8mb4;					#engine=innodb charset=utf8mb4：设置字符集为utf8mb4
#查看表的结构  
MariaDB [test]> desc shujubiao;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| xuhao    | int(10)     | NO   | PRI | NULL    | auto_increment |	#auto_increment：自增
| mingzi   | varchar(20) | NO   |     | NULL    |                |
| nianling | tinyint(3)  | NO   |     | 0       |                |
| xingbie  | varchar(3)  | YES  |     | NULL    |                |
| shengao  | int(3)      | NO   |     | NULL    |                |
| tizhong  | int(3)      | NO   |     | NULL    |                |
| jiehun   | varchar(3)  | NO   |     | no      |                |
+----------+-------------+------+-----+---------+----------------+

2.3.表中新增字段

语法：【alter table 表名 add 新增字段名 新增字段类型】

1
2
3
4
5
6
7
8
#新增字段
alter table shujubiao add haha int(20);   		 	  #不设置null，默认是yes
alter table shujubiao add haha int(20) not null; 	  #null设置为NO，默认是yes
alter table shujubiao add haha int(20) after xuhao;	  #创建haha字段在xuhao字段的后面，就是指定插入的位置
alter table shujubiao add haha int(20) not null first;		#first 添加到第一列，不带first默认都是最后一列；
alter table shujubiao add haha int(20) not null comment'你好';	#commen''添加说明内容你好
#添加字段haha，设置null为no，default为9，并指定插入在xuhao字段下面
alter table shujubiao add haha int(20) not null default '9' comment'你好' after xuhao;
1
2
3
4
5
6
7
8
9
10
11
#新增字段哈哈haha
MariaDB [test]> alter table shujubiao add haha int(3) not null default '9' comment '窗前明月光';
#查看表的结构
MariaDB [test]> desc shujubiao;
+----------+-------------+------+-----+---------+----------------+
| Field    | Type        | Null | Key | Default | Extra          |
+----------+-------------+------+-----+---------+----------------+
| xuhao    | int(10)     | NO   | PRI | NULL    | auto_increment |	#auto_increment：自增
				............................
| haha     | int(3)      | NO   |     |   9     |                |
+-------+------------+------+-----+---------+---------------------+

2.4往【shujubiao】表里插入数据信息

语法1：insert into 表名 value(第1列内容，第二列内容，第三列内容……);

语法2：insert into 表名(序号,名字,年龄……) value(1,’小花’,25…….); #指定插入位置

#凡是字符型的数据都要加【‘ ’】单引号，如汉字，英文字母，数字可以不加

  • 第1种方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
MariaDB [test]> insert into shujubiao value(1,'xiaohua',22,'nan',175,120,'yes');
#查看插入内容
MariaDB [test]> select * from shujubiao;
+-------+---------+----------+---------+---------+---------+--------+
| xuhao | mingzi  | nianling | xingbie | shengao | tizhong | jiehun |
+-------+---------+----------+---------+---------+---------+--------+
|     1 | xiaohua |       22 | nan     |     175 |     120 | yes    |
+-------+---------+----------+---------+---------+---------+--------+
MariaDB [test]> insert into shujubiao value(2,'xiaohua1',23,'nan',180,130,'no');
MariaDB [test]> select * from shujubiao;
+-------+----------+----------+---------+---------+---------+--------+
| xuhao | mingzi   | nianling | xingbie | shengao | tizhong | jiehun |
+-------+----------+----------+---------+---------+---------+--------+
|     1 | xiaohua  |       22 | nan     |     175 |     120 | yes    |
|     2 | xiaohua1 |       23 | nan     |     180 |     130 | no     |
+-------+----------+----------+---------+---------+---------+--------+

  • 第2种方法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
MariaDB [test]> insert into shujubiao(xuhao,mingzi,nianling,xingbie,shengao,tizhong,jiehun) value(3,'xiaohua2',25,'nv',165,125,'yes');
#查看插入内容
MariaDB [test]> select * from shujubiao;
+-------+----------+----------+---------+---------+---------+--------+
| xuhao | mingzi   | nianling | xingbie | shengao | tizhong | jiehun |
+-------+----------+----------+---------+---------+---------+--------+
|     1 | xiaohua  |       22 | nan     |     175 |     120 | yes    |
|     2 | xiaohua1 |       23 | nan     |     180 |     130 | no     |
|     3 | xiaohua2 |       25 | nv      |     165 |     125 | yes    |
+-------+----------+----------+---------+---------+---------+--------+
#创建表的时候给xuhao的字段类型设置了auto_increment[自增的意思]就是不写序号它也会往下排
MariaDB [test]> insert into shujubiao(mingzi,nianling,xingbie,shengao,tizhong,jiehun) value('xiaohua3',27,'nv',168,129,'no');
MariaDB [test]> select * from shujubiao;
+-------+----------+----------+---------+---------+---------+--------+
| xuhao | mingzi   | nianling | xingbie | shengao | tizhong | jiehun |
+-------+----------+----------+---------+---------+---------+--------+
|     1 | xiaohua  |       22 | nan     |     175 |     120 | yes    |
|     2 | xiaohua1 |       23 | nan     |     180 |     130 | no     |
|     3 | xiaohua2 |       25 | nv      |     165 |     125 | yes    |
|     4 | xiaohua3 |       27 | nv      |     168 |     129 | no     |
+-------+----------+----------+---------+---------+---------+--------+

2.5删除插入的信息

语法：delete from 表名 where xxx=xxx;

  • 删除前一定先查看信息，防止有相同的信息，例如同名，删除一个同名的信息，就要where参照其他信息删除【身高、体重…】
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
#查看
MariaDB [test]> select * from shujubiao where xuhao=1;
#或者查看名字叫小花的
MariaDB [test]> select * from shujubiao where mingzi='xiaohua';
+-------+---------+----------+---------+---------+---------+--------+
| xuhao | mingzi  | nianling | xingbie | shengao | tizhong | jiehun |
+-------+---------+----------+---------+---------+---------+--------+
|     1 | xiaohua |       22 | nan     |     175 |     120 | yes    |
+-------+---------+----------+---------+---------+---------+--------+

#删除
MariaDB [test]> delete from shujubiao where xuhao='4';
或者选一个xxx=xxx对应的信息
MariaDB [test]> delete from shujubiao where mingzi='xiaohua3';
#查看
MariaDB [test]> select * from shujubiao;
+-------+----------+----------+---------+---------+---------+--------+
| xuhao | mingzi   | nianling | xingbie | shengao | tizhong | jiehun |
+-------+----------+----------+---------+---------+---------+--------+
|     1 | xiaohua  |       22 | nan     |     175 |     120 | yes    |
|     2 | xiaohua1 |       23 | nan     |     180 |     130 | no     |
|     3 | xiaohua2 |       25 | nv      |     165 |     125 | yes    |
+-------+----------+----------+---------+---------+---------+--------+

2.6修改插入的信息

语法：update 表名 set 列名=新信息 where 列名=’信息’;

  • 修改前先查看：select * from 表名 where 列名=信息;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
#修改
MariaDB [test]> update shujubiao set shengao=190 where mingzi='xiaohua2';
#查看
MariaDB [test]> select * from shujubiao2;
+-------+----------+----------+---------+---------+---------+--------+
| xuhao | mingzi   | nianling | xingbie | shengao | tizhong | jiehun |
+-------+----------+----------+---------+---------+---------+--------+
|     1 | xiaohua  |       22 | nan     |     175 |     120 | yes    |
|     2 | xiaohua1 |       23 | nan     |     180 |     130 | no     |
|     3 | xiaohua2 |       25 | nv      |     190 |     125 | yes    |
+-------+----------+----------+---------+---------+---------+--------+

MariaDB [test]> update shujubiao2 set shengao=190;
#若要是这样写，不加where mingzi='xiaohua2'; 则所有身高哪一列都将改为190。where xxx=xxx;随便选一个对应的，防止有同名或者同身高,这就是删除修改前先查看的原因

3.数据库数据备份与恢复

3.1.查看数据库的数据目录

1
ls /var/lib/mysql

3.2.数据库备份

3.2.1.物理备份

需要先关闭数据库服务

1
tar zcvf sql-`data +%F`.tar.gz /var/lib/mysql

3.2.2.逻辑备份

mysqldump命令；

语法：【mysqldump -uroot -p1 [备份参数] > /路径/文件名.sql】

  • 参数
    • -A 全备，备份所有数据库的数据信息
    • -B 数据库名 #备份指定数据库的数据信息
    • -F 备份启动之前，自动刷新日志文件（落盘）；
1
2
3
4
5
6
7
8
#【-A】全备，/home/：是备份到根目录下的home文件夹里
mysqldump -uroot -p123456 -A > /home/back.sql

#【-B】指定库备份
mysqldump -uroot -p123456 -B 库名1 库名2 > /home/back.sql

#指定数据库中的部分数据表进行备份
mysqldump -uroot -p123456 库名 表名 > /home/back.sql

3.3.数据恢复

就是往数据库里导入文件，在数据库里操作

1
MariaDB [test]> source ~/back.sql

登录数据库前导入，不在数据库里操作

1
2
3
mysql -uroot -p123456 < ~/back.sql
#查看
mysql -uroot -p123456 -e "show databases"