MySQL基础学习手册

前言：MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能轻松成为MySQL的代替品。由于CentOS7里软件库已经把mysql删除，使用MariaDB代替，所以我们在项目部署的时候也就直接使用了MariaDB。

用简单的例子来学习Mysql的用法，初学者可以用来快速学习，学过的也可以拿来复习，用了csdn的目录功能在PC端可以通过点击目录自动跳转到相应位置正，在持续更新！
制作不易，觉得可以的话可以留个赞谢谢啦！
话不多说直接上干货！

目录：

• ​​一、Mysql的基本操作​​

• ​​1.安装服务​​
• ​​2.登录Mysql​​
• ​​3.MySQL建，查，选，删库​​
• ​​4.MySQL 数据类型​​
• ​​5.建、查、删表​​
• ​​6.插入数据​​

• ​​二、Mysql的数据查询​​

• ​​1.查询表内数据​​
• ​​2.where子句​​
• ​​3.UPDATE 更新数据​​
• ​​4.DELETE 删除数据​​
• ​​5.LIKE 子句​​
• ​​6.排序​​
• ​​7.分组​​
• ​​8.多表查询​​
• ​​9.处理NULL值​​
• ​​10.正则表达式​​

• ​​三、Mysql高级操作​​

• ​​1.Mysql事务​​
• ​​2.alter用法​​

• ​​1.删除，添加或修改表字段​​
• ​​2.修改字段类型及名称​​
• ​​3.更改表名​​
• ​​4.修改字段默认值​​

• ​​3.查看mysql引擎​​

• ​​（1）看你的mysql现在已提供知什么存储引擎:​​
• ​​（2）看你的mysql当前默认的存储引擎​​
• ​​（3）看某个表用了什么引擎​​

• ​​4.数据库导出导入​​

• ​​1.导出​​
• ​​2.导入​​
• ​​3.备份所有数据库​​

• ​​5.mysql用户管理​​

• ​​1.添加用户​​
• ​​2.删除用户​​
• ​​3.给用户授权​​

一、Mysql的基本操作

1.安装服务

[root@a ~]# yum install mariadb-server mariadb 
[root@a ~]# systemctl start mariadb
[root@a ~]# systemctl enable mariadb
Created symlink from /etc/systemd/system/multi-user.target.wants/mariadb.service to /usr/lib/systemd/system/mariadb.service.
[root@a ~]# firewall-cmd --permanent --add-service
[root@a ~]# firewall-cmd --reload
success

2.登录Mysql

（1）查看服务是否开启

[root@a ~]# netstat -tulnp |grep mysql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      
2208/mysqld

（2）安全安装mysql

[root@a ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
...此处省略安装内容...

(3)登录mysql

[root@a ~]# mysql -uroot -pa
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

3.MySQL建，查，选，删库

1.mysql命令规则：要在最后加上;(英文输入法分号)表示命令结束
2.在linux中用mysql可以用tab键补全
3.mysql内命令不区分大小写，比如CREATE=create
4.delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。
(1)创建数据库create

[root@a ~]# mysqladmin -uroot -pa create test2

MariaDB [(none)]> create database test1;
Query OK, 1 row affected (0.00 sec)

(2)查询数据库show

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test1              |
| test2              |
+--------------------+
5 rows in set (0.00 sec)

(3)选择数据库use

MariaDB [(none)]> use test1;
Database changed
MariaDB [test1]> use test2;
Database changed
MariaDB [test2]> use  mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

(4)删除数据库
在操作中会有不少的错误，学会看报错信息是个解决问题的好方法，如下根据提示（for the right syntax to use near ‘test2’ at line 1）发现test2附近语法错误，检查后发现没有写databases，加上后便可以解决！

[root@a ~]# mysqladmin -uroot -pa drop test1
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.

Do you really want to drop the 'test1' database [y/N] y
Database "test1" dropped

MariaDB [mysql]> drop test2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'test2' at line 1
MariaDB [mysql]> drop database test2;
Query OK, 0 rows affected (0.01 sec)

4.MySQL 数据类型

1.经常变化的字段用 varchar
2.知道固定长度的用 char
3.尽量用 varchar
4.超过 255 字符的只能用 varchar 或者 text
5.varchar 的地方不用 text
6.使用varchar必须带范围！

（1）数值类型放图了，可在菜鸟教程找到

（2）日期和时间类型

（3）字符串类型

5.建、查、删表

1.建立、查询、删除表要在进入数据库的前提下进行操作
2.数据类型后面不填的话默认是最高位
3.如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。

4.delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。
5.使用varchar必须带范围！
6.表名不能是mysql中数据类型等关键字。
(1)建表

MariaDB [test1]> create table book(name varchar(255),price int,date date);
Query OK, 0 rows affected (0.01 sec)

两种方式都可以建表，看个人习惯！

MariaDB [test1]> create table book(
    -> name varchar(255),
    -> price int(11),
    -> date date)
    -> ;
ERROR 1050 (42S01): Table 'book' already exists

（2）查表
1.在数据库查看表

MariaDB [test1]> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| book            |
+-----------------+
1 row in set (0.00 sec)

2.查看表结构

MariaDB [test1]> desc book;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| name  | char(1) | YES  |     | NULL    |       |
| price | int(11) | YES  |     | NULL    |       |
| date  | date    | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

（3）删除表

MariaDB [test1]> drop table book;
Query OK, 0 rows affected (0.00 sec)

6.插入数据

1.添加数据的时候可以规定列进行添加。
如果所有的列都要添加数据可以不规定列进行添加数据：

MariaDB [test1]> insert into book(name,price,date) values ("a","8","2020-04-14");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO runoob_tbl
    -> VALUES
    -> (0, "JAVA 教程", "RUNOOB.COM", '2016-05-06');

2.INSERT 插入多条数据

INSERT INTO table_name  (field1, field2,...fieldN)  VALUES  (valueA1,valueA2,...valueAN),(valueB1,valueB2,...valueBN),(valueC1,valueC2,...valueCN)......;

MariaDB [test1]> insert into book values("水浒","88","2020-04-13"),("红楼梦","99","2020-04-13"),("西游记","100","2020-04-13");
Query OK, 3 rows affected, 3 warnings (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 3

二、Mysql的数据查询

（1）.不显示中文内容是因为字符编码搞的鬼，设置下就可以了

•vim /etc/my.cnf.d/client.cnf 文件，添加如下内容

[client]

default-character-set=utf8

•vim /etc/my.cnf.d/mysql-clients.cnf文件，添加如下内容

[mysql]
default-character-set=utf8

•vim /etc/my.cnf 文件，添加如下内容

[mysqld]
character-set-server=utf8
default-storage-engine=INNODB

•重启服务

# systemctl restart mariadb

1.查询表内数据

1.SQL中用select来查看数据，select后面跟是要查看表格的列头，查几个跟几个列头，如果都要查可以用通配符，代表所有的意思。
2.as可以对查询的内容进行重命名

MariaDB [test1]> select * from book;
+------+-------+------------+
| name | price | date       |
+------+-------+------------+
| ?    |    88 | 2020-04-13 |
| ?    |    99 | 2020-04-13 |
| ?    |   100 | 2020-04-13 |
+------+-------+------------+
6 rows in set (0.00 sec)

更改后

MariaDB [test1]> select * from book;
+-----------+-------+------------+
| name      | price | date       |
+-----------+-------+------------+
| 水浒      |    88 | 2020-04-13 |
| 红楼梦    |    99 | 2020-04-13 |
| 西游记    |   100 | 2020-04-13 |
+-----------+-------+------------+
3 rows in set (0.00 sec)

2.where子句

1.如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

2.你可以使用 AND 或者 OR 指定一个或多个条件。

3.查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。

MariaDB [test1]> select * from book where name="水浒";
+--------+-------+------------+
| name   | price | date       |
+--------+-------+------------+
| 水浒   |    88 | 2020-04-13 |
+--------+-------+------------+
1 row in set (0.00 sec)

MariaDB [test1]> select * from book where price=100 and date="2020-04-13";
+-----------+-------+------------+
| name      | price | date       |
+-----------+-------+------------+
| 西游记    |   100 | 2020-04-13 |
+-----------+-------+------------+
1 row in set (0.00 sec)


MariaDB [test1]> select * from book where price=100 or date="2020-04-13";
+-----------+-------+------------+
| name      | price | date       |
+-----------+-------+------------+
| 水浒      |    88 | 2020-04-13 |
| 红楼梦    |    99 | 2020-04-13 |
| 西游记    |   100 | 2020-04-13 |
+-----------+-------+------------+
3 rows in set (0.00 sec)

3.UPDATE 更新数据

1.你可以同时更新一个或多个字段。
2.你可以在一个单独表中同时更新数据。
3.你可以在 WHERE 子句中指定任何条件。

MariaDB [test1]> update book set name="水浒传" where price=88;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0


MariaDB [test1]> select * from book;
+-----------+-------+------------+
| name      | price | date       |
+-----------+-------+------------+
| 水浒传    |    88 | 2020-04-13 |
| 红楼梦    |    99 | 2020-04-13 |
| 西游记    |   100 | 2020-04-13 |
+-----------+-------+------------+
3 rows in set (0.00 sec)

4.DELETE 删除数据

1.如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
2.你可以在 WHERE 子句中指定任何条件
3.您可以在单个表中一次性删除记录。
4.delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。

MariaDB [a]> select * from book;
+------+-------+------------+
| name | price | date       |
+------+-------+------------+
| ?    |    66 | 2020-04-13 |
| ?    |    88 | 2020-04-13 |
| ?    |    99 | 2020-04-13 |
| ?    |   100 | 2020-04-13 |
| ?    |    88 | 2020-04-13 |
| a    |     8 | 2020-04-14 |
+------+-------+------------+
6 rows in set (0.00 sec)

MariaDB [a]> delete from book where price=8;
Query OK, 1 row affected (0.00 sec)

MariaDB [a]> select * from book;
+------+-------+------------+
| name | price | date       |
+------+-------+------------+
| ?    |    66 | 2020-04-13 |
| ?    |    88 | 2020-04-13 |
| ?    |    99 | 2020-04-13 |
| ?    |   100 | 2020-04-13 |
| ?    |    88 | 2020-04-13 |
+------+-------+------------+
5 rows in set (0.00 sec)

MariaDB [a]> delete from book;
Query OK, 5 rows affected (0.00 sec)

MariaDB [a]> select * from book;
Empty set (0.00 sec)

5.LIKE 子句

1.like 匹配/模糊匹配，会与 % 和 _ 结合使用。
2.在 where like 的条件查询中，SQL 提供了四种匹配方式。
%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
[]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
[^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询。

MariaDB [test1]> select * from book where name like '水%';
+-----------+-------+------------+
| name      | price | date       |
+-----------+-------+------------+
| 水浒传    |    88 | 2020-04-13 |
+-----------+-------+------------+
1 row in set (0.00 sec)

MariaDB [test1]> select * from book where name like '%%';
+-----------+-------+------------+
| name      | price | date       |
+-----------+-------+------------+
| 水浒传    |    88 | 2020-04-13 |
| 红楼梦    |    99 | 2020-04-13 |
| 西游记    |   100 | 2020-04-13 |
+-----------+-------+------------+
3 rows in set (0.00 sec)

MariaDB [test1]> select * from book where name like '_楼_';
+-----------+-------+------------+
| name      | price | date       |
+-----------+-------+------------+
| 红楼梦    |    99 | 2020-04-13 |
+-----------+-------+------------+
1 row in set (0.00 sec)

6.排序

MariaDB [test1]> select * from book order by price desc;
+-----------+-------+------------+
| name      | price | date       |
+-----------+-------+------------+
| 西游记    |   100 | 2020-04-13 |
| 红楼梦    |    99 | 2020-04-13 |
| 水浒传    |    88 | 2020-04-13 |
+-----------+-------+------------+
3 rows in set (0.00 sec)

MariaDB [test1]> select * from book order by price asc;
+-----------+-------+------------+
| name      | price | date       |
+-----------+-------+------------+
| 水浒传    |    88 | 2020-04-13 |
| 红楼梦    |    99 | 2020-04-13 |
| 西游记    |   100 | 2020-04-13 |
+-----------+-------+------------+
3 rows in set (0.00 sec)

7.分组

1.GROUP BY 语句根据一个或多个列对结果集进行分组。
常和 COUNT, SUM, AVG,等函数一起使用。
2.GROUP by 的用法很多要多加练习！
3.as可以对查询的内容进行重命名
4.WITH ROLLUP（rollup的意思就是归纳！） 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。其中记录 NULL 表示所有人的登录次数。我们可以使用 coalesce 来设置一个可以取代 NUll 的名称select coalesce(a,b,c);

MariaDB [test1]> select * from book;
+-----------+-------+------------+
| name      | price | date       |
+-----------+-------+------------+
| 水浒传    |    88 | 2020-04-13 |
| 红楼梦    |    99 | 2020-04-13 |
| 西游记    |   100 | 2020-04-13 |
| 水浒传    |    90 | 2020-01-01 |
+-----------+-------+------------+
4 rows in set (0.00 sec)


MariaDB [test1]> select name,count(*) from book group by name;
+-----------+----------+
| name      | count(*) |
+-----------+----------+
| 水浒传    |        2 |
| 红楼梦    |        1 |
| 西游记    |        1 |
+-----------+----------+
3 rows in set (0.00 sec)

MariaDB [test1]> select name,sum(price) as total from book group  by name with rollup;
+-----------+-------+
| name      | total |
+-----------+-------+
| 水浒传    |   178 |
| 红楼梦    |    99 |
| 西游记    |   100 |
| NULL      |   377 |
+-----------+-------+
4 rows in set (0.00 sec)

MariaDB [test1]> select coalesce(name,'total') as name,sum(price) as total from book group  by name with rollup;
+-----------+-------+
| name      | total |
+-----------+-------+
| 水浒传    |   178 |
| 红楼梦    |    99 |
| 西游记    |   100 |
| total     |   377 |
+-----------+-------+
4 rows in set, 1 warning (0.00 sec)

8.多表查询

1. 以上内容比较简单容易理解，但是在真正的应用中经常需要从多个数据表中读取数据。
2. INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

3.

4.

5.

MariaDB [test1]> select * from book;
+-----------+-------+------------+
| name      | price | date       |
+-----------+-------+------------+
| 水浒传    |    88 | 2020-04-13 |
| 红楼梦    |    99 | 2020-04-13 |
| 西游记    |   100 | 2020-04-13 |
| 水浒传    |    90 | 2020-01-01 |
+-----------+-------+------------+
4 rows in set (0.00 sec)

MariaDB [test1]> select * from num;
+-----------+----------+
| name      | ordernum |
+-----------+----------+
| 水浒传    |        1 |
| 红楼梦    |        3 |
| 西游记    |        2 |
+-----------+----------+
3 rows in set (0.00 sec)

#内连接
MariaDB [test1]> select book.name,book.price,book.date,num.ordernum from book inner join num where book.name=num.name;
+-----------+-------+------------+----------+
| name      | price | date       | ordernum |
+-----------+-------+------------+----------+
| 水浒传    |    88 | 2020-04-13 |        1 |
| 红楼梦    |    99 | 2020-04-13 |        3 |
| 西游记    |   100 | 2020-04-13 |        2 |
| 水浒传    |    90 | 2020-01-01 |        1 |
+-----------+-------+------------+----------+
4 rows in set (0.00 sec)

#左连接，左表没有对应的项的情况，会显示null
MariaDB [test1]> select book.name,num.ordernum from book left join num on book.name=num.name;
+--------------+----------+
| name         | ordernum |
+--------------+----------+
| 水浒传       |        1 |
| 红楼梦       |        3 |
| 西游记       |        2 |
| 三国演艺     |     NULL |
+--------------+----------+
4 rows in set (0.00 sec)

#右连接，以右表为主，右表多出的将不显示
MariaDB [test1]> select book.name,num.ordernum from book right join num on book.name=num.name;
+-----------+----------+
| name      | ordernum |
+-----------+----------+
| 水浒传    |        1 |
| 红楼梦    |        3 |
| 西游记    |        2 |
+-----------+----------+
3 rows in set (0.00 sec)

9.处理NULL值

1.我们已经知道 MySQL 使用 SQL SELECT 命令及 WHERE 子句来读取数据表中的数据,但是当提供的查询条件字段为 NULL 时，该命令可能就无法正常工作。
2. IS NULL: 当列的值是 NULL,此运算符返回 true。
IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
<=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

MariaDB [test1]> create table nulll(name varchar(255) not null, price int);
Query OK, 0 rows affected (0.00 sec)

MariaDB [test1]> insert into nulll values("吴承恩","max"),("罗贯中","maxx"),("雪芹",null),("施耐庵",null);
Query OK, 4 rows affected, 2 warnings (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 2

MariaDB [test1]> select * from nulll;
+-----------+-------+
| name      | price |
+-----------+-------+
| 吴承恩    |     0 |
| 罗贯中    |     0 |
| 曹雪芹    |  NULL |
| 施耐庵    |  NULL |
+-----------+-------+
4 rows in set (0.00 sec)

#原先的方式无法查看null值
MariaDB [test1]> select * from nulll where price=null;
Empty set (0.00 sec)

#采用is null查看空值对应数据
MariaDB [test1]> select * from nulll where price is null;
+-----------+-------+
| name      | price |
+-----------+-------+
| 曹雪芹    |  NULL |
| 施耐庵    |  NULL |
+-----------+-------+
2 rows in set (0.00 sec)

#采用is not null查看非空值对应数据
MariaDB [test1]> select * from nulll where price is not null;
+-----------+-------+
| name      | price |
+-----------+-------+
| 吴承恩    |     0 |
| 罗贯中    |     0 |
+-----------+-------+
2 rows in set (0.00 sec)

10.正则表达式

1.MySQL可以通过 LIKE …% 来进行模糊匹配。
2.MySQL 同样也支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

#查看name字段以水开头的所有数据
MariaDB [test1]> SELECT name FROM book WHERE name REGEXP '^水';
+-----------+
| name      |
+-----------+
| 水浒传    |
+-----------+
1 row in set (0.00 sec)

#查看name字段所有以记结尾的数据
MariaDB [test1]> SELECT name FROM book WHERE name REGEXP '记$';
+-----------+
| name      |
+-----------+
| 西游记    |
+-----------+
1 row in set (0.00 sec)

#查看name字段以水开头或者结尾以记结尾的所有数据
MariaDB [test1]> SELECT name FROM book WHERE name REGEXP '^水|记$';
+-----------+
| name      |
+-----------+
| 水浒传    |
| 西游记    |
+-----------+
2 rows in set (0.00 sec)

三、Mysql高级操作

1.Mysql事务

1.MySQL 事务主要用于处理操作量大，复杂度高的数据。
2.一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

3.MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

2.alter用法

1.删除，添加或修改表字段

MariaDB [test1]> alter table book add a int;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

#查看字段信息
MariaDB [test1]> show columns from book;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
| date  | date         | YES  |     | NULL    |       |
| a     | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

#用add添加字段
MariaDB [test1]> alter table book add b int;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test1]> show columns from book;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
| date  | date         | YES  |     | NULL    |       |
| b     | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#使用drop删除字段
MariaDB [test1]> alter table book drop b;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test1]> show columns from book;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
| date  | date         | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

2.修改字段类型及名称

（1）.如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。
（2）.使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型

#查看字段信息
MariaDB [test1]> show columns from book;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
| date  | date         | YES  |     | NULL    |       |
| a     | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

#使用change命令修改字段和字段类型
MariaDB [test1]> alter table book change a b varchar(255);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test1]> show columns from book;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
| date  | date         | YES  |     | NULL    |       |
| b     | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

#使用modify命令修改字段和字段类型
MariaDB [test1]> alter table book modify b int;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test1]> show columns from book;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
| date  | date         | YES  |     | NULL    |       |
| b     | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

3.更改表名

MariaDB [test1]> alter table book rename to books;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test1]> select * from books;
+--------------+-------+------------+------+
| name         | price | date       | b    |
+--------------+-------+------------+------+
| 水浒传       |    88 | 2020-04-13 | NULL |
| 红楼梦       |    99 | 2020-04-13 | NULL |
| 西游记       |   100 | 2020-04-13 | NULL |
| 三国演艺     |    91 | 2020-04-14 | NULL |
+--------------+-------+------------+------+
4 rows in set (0.00 sec)

4.修改字段默认值

MariaDB [test1]> desc nulll;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | NO   |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#修改默认值
MariaDB [test1]> alter table nulll alter price set default 1000;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0


MariaDB [test1]> desc nulll;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | NO   |     | NULL    |       |
| price | int(11)      | YES  |     | 1000    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

#删除默认值
MariaDB [test1]> alter table nulll alter price drop default;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test1]> desc nulll;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | NO   |     | NULL    |       |
| price | int(11)      | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3.查看mysql引擎

（1）看你的mysql现在已提供知什么存储引擎:

MariaDB [test1]> show engines;
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                          | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys       | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                            | NO           | NO   | NO         |
| MyISAM             | YES     | Non-transactional engine with good performance and small data footprint          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)                   | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                               | NO           | NO   | NO         |
| CSV                | YES     | Stores tables as CSV files                                                       | NO           | NO   | NO         |
| ARCHIVE            | YES     | gzip-compresses tables for a low storage footprint                               | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                        | NO           | NO   | NO         |
| FEDERATED          | YES     | Allows to access tables on other MariaDB servers, supports transactions and more | YES          | NO   | YES        |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                           | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

（2）看你的mysql当前默认的存储引擎

MariaDB [test1]> show variables like '%storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)

（3）看某个表用了什么引擎

MariaDB [test1]> show variables like '%storage_engine%';
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
| storage_engine         | InnoDB |
+------------------------+--------+
2 rows in set (0.00 sec)

MariaDB [test1]> show create table books;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| books | CREATE TABLE `books` (
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4.数据库导出导入

1.导出

[root@a ~]# mysqldump -uroot -p test1 > test1.sql
Enter password:
[root@a ~]# ll
total 8
-rw-------. 1 root root 1260 Jan 30 17:12 anaconda-ks.cfg
-rw-r--r--. 1 root root 4062 Apr 14 00:17 test1.sql

2.导入

MariaDB [(none)]> create database b;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use b
Database changed
MariaDB [b]> source /root/test1.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
....此处省略...

MariaDB [b]> show tables;
+-------------+
| Tables_in_b |
+-------------+
| books       |
| nulll       |
| num         |
| xs          |
+-------------+
4 rows in set (0.00 sec)

3.备份所有数据库

[root@a ~]# mysqldump -uroot -p --all-databases > a.sql
Enter password:
[root@a ~]# ll
total 520
-rw-------. 1 root root   1260 Jan 30 17:12 anaconda-ks.cfg
-rw-r--r--. 1 root root 520847 Apr 14 00:25 a.sql
-rw-r--r--. 1 root root   4062 Apr 14 00:17 test1.sql

5.mysql用户管理

1.添加用户

1、本地环境
[root@a shell]# cat /etc/redhat-release 
CentOS Linux release 7.7.1908 (Core)
2、以root用户登录Mysql
[root@a ~]# mysql -uroot -pa
3、切换到mysql数据库
MariaDB [(none)]> use mysql
4、添加用户
//只允许指定ip连接
create user '新用户名'@'localhost' identified by '密码';
//允许所有ip连接（用通配符%表示）
create user '新用户名'@'%' identified by '密码';

实例：

MariaDB [mysql]> create user 'Jack'@'localhost' identified by 'a';
Query OK, 0 rows affected (0.00 sec)

2.删除用户

删除用户

DROP USER username@localhost;

3.给用户授权

为新用户授权

//基本格式如下
grant all privileges on 数据库名.表名 to '新用户名'@'指定ip' identified by '新用户密码' ;
//示例
//允许访问所有数据库下的所有表
grant all privileges on *.* to '新用户名'@'指定ip' identified by '新用户密码' ;
//指定数据库下的指定表
grant all privileges on test.test to '新用户名'@'指定ip' identified by '新用户密码' ;
6、设置用户操作权限

//设置用户拥有所有权限也就是管理员
grant all privileges on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;
//拥有查询权限
grant select on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;
//其它操作权限说明,select查询 insert插入 delete删除 update修改
//设置用户拥有查询插入的权限
grant select,insert on *.* to '新用户名'@'指定ip' identified by '新用户密码' WITH GRANT OPTION;
//取消用户查询的查询权限
REVOKE select ON what FROM '新用户名';

实例：

mysql> grant all privileges on *.* to 'yangxin'@'%' identified by 'yangxin123456' with grant option;
   
   all privileges：表示将所有权限授予给用户。也可指定具体的权限，如：SELECT、CREATE、DROP等。
    on：表示这些权限对哪些数据库和表生效，格式：数据库名.表名，这里写“*”表示所有数据库，所有表。如果我要指定将权限应用到test库的user表中，可以这么写：test.user
    to：将权限授予哪个用户。格式：”用户名”@”登录IP或域名”。%表示没有限制，在任何主机都可以登录。比如：”yangxin”@”192.168.0.%”，表示yangxin这个用户只能在192.168.0IP段登录
    identified by：指定用户的登录密码
    with grant option：表示允许用户将自己的权限授权给其它用户

    可以使用GRANT给用户添加权限，权限会自动叠加，不会覆盖之前授予的权限，比如你先给用户添加一个SELECT权限，后来又给用户添加了一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。

修改后刷新权限
FLUSH PRIVILEGES;

未完待续…