MySQL基础操作详解-优快云博客

这篇博客总结了MySQL的基础知识，包括SQL语句分类、创建与删除数据库、用户权限管理、表的建立、数据类型、查看与修改数据、字符集设置、数据备份等。详细介绍了如DDL、DML和DCL的操作，以及如何授权、刷新权限和收回权限。同时讲解了如何避免更新数据时的误操作，以及数据备份的各种方法和关键参数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

常见的SQL语句：
1)DDL—数据定义语言（CREATE，ALTER，DROP）–>运维
2)DML—数据操作语言（SELECT，INSTERT，UPDATE，DELETE) -->开发
3)DCL—数据控制语言（GRANT，REVOKE，COMMT，ROLLBACK)–>运维
创建数据库：
语法：create database 库名（注意：库名不可以以数字开头）

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

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

查看创建库时的语句：

MariaDB [(none)]> show create database aaa\G;
*************************** 1. row ***************************
       Database: aaa
Create Database: CREATE DATABASE `aaa` /*!40100 DEFAULT CHARACTER SET latin1 */
1 row in set (0.00 sec)

创建一个名为jyy_gbk字符集为gbk的数据库；

MariaDB [(none)]> create database jyy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| jyy_gbk            |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

删除数据库：
语法：drop database 数据库名；

MariaDB [(none)]> drop database jyy_gbk;
Query OK, 0 rows affected (0.00 sec)

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

连接数据库：
命令：use 数据库名 //相当于linux下的cd切换目录，use是切换数据库
查看各项信息：
select database(); //显示当前所在的数据库；相当于linux下面的pwd
select version（）； //查看数据库版本；
select user（）； //查看当前数据库用户；
select now（）； //查看当前时间；
show tables； //查看数据库的表里的信息；
6.创建MySQL用户及赋予用户权限
GRANT ALL ON db_name.* TO ‘jyy’@’localhost’ IDENTIFIED BY ‘redhat’;
grant：授权命令；
all：对应关系；
on db_name.*：授权的库和表，*表示所有；
to ‘jyy’@‘localhost’：授权的用户和主机域；
identified by ‘redhat’：设置用户密码为redhat

MariaDB [(none)]> grant all on mysql.* to 'jyy'@'localhost' identified by 'redhat';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show grants for 'jyy'@'localhost';
+------------------------------------------------------------------------------------------------------------+
| Grants for jyy@localhost                                                                                   |
+------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'jyy'@'localhost' IDENTIFIED BY PASSWORD '*84BB5DF4823DA319BBF86C99624479A198E6EEE9' |
| GRANT ALL PRIVILEGES ON `mysql`.* TO 'jyy'@'localhost'                                                     |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

刷新授权表：

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

收回权限：
revoke all on mysql.* to ‘jyy’@‘localhost’ identified by ‘redhat’;
注意：收回的权限必须要与授予的权限相匹配

6.表的建立：
语法：
create table 表名（字段名1 类型1，字段名2 类型2，…）；

MariaDB [aaa]> create table student(id int(4) NOT NULL,name char(20) NOT NULL,age tinyint(2) NOT NULL DEFAULT 0,dept varchar(16) DEFAULT NULL);
Query OK, 0 rows affected (0.00 sec)

MariaDB [aaa]> show tables;
+---------------+
| Tables_in_aaa |
+---------------+
| student       |
+---------------+
1 row in set (0.00 sec)

注意：表如果没有指定字符集，则继承库的字符集；
查看创建的表的过程：

MariaDB [aaa]> show  create table student\G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `id` int(4) NOT NULL,
  `name` char(20) NOT NULL,
  `age` tinyint(2) NOT NULL DEFAULT '0',
  `dept` varchar(16) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

MySQL常用的数据类型：
1）INT[(M)]型：正常大小整数类型；
2）CHAR(M)型：定长字符串类型，当存储时，总是用空格填满右边到指定的长度；
3）VARCHAR型：变长字符串类型

查看表结构：
desc 表名;

MariaDB [aaa]> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(4)      | NO   |     | NULL    |       |
| name  | char(20)    | NO   |     | NULL    |       |
| age   | tinyint(2)  | NO   |     | 0       |       |
| dept  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

7.插入数据
语法：insert into 表名（字段名1，字段名2…） varlues（数值1，数值2…）

MariaDB [aaa]> insert into student(id,name,age) values(1,'jyy',19),(2,'aaa',2),(3,'bbb',3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

8.查询表的数据：
语法：select 字段1，字段2… from 表名 where 判断条件;

MariaDB [aaa]> select id,name,age  from  student where id > 1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | aaa  |   2 |
|  3 | bbb  |   3 |
+----+------+-----+
2 rows in set (0.00 sec)

升序查看数据：

MariaDB [aaa]> select id,name,age  from  student order by age asc;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  2 | aaa  |   2 |
|  3 | bbb  |   3 |
|  1 | jyy  |  19 |
+----+------+-----+
3 rows in set (0.00 sec)

倒序查看数据：

MariaDB [aaa]> select id,name,age  from student  order by age desc;+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | jyy  |  19 |
|  3 | bbb  |   3 |
|  2 | aaa  |   2 |
+----+------+-----+
3 rows in set (0.00 sec)

8.修改表的数据
语法：update 表名 set 字段=新值，… where 条件（一定要注意条件）

MariaDB [aaa]> update student set age=30 where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [aaa]> select  id,name,age from studnet;
ERROR 1146 (42S02): Table 'aaa.studnet' doesn't exist
MariaDB [aaa]> select  id,name,age from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | jyy  |  19 |
|  2 | aaa  |  30 |
|  3 | bbb  |   3 |
+----+------+-----+
3 rows in set (0.00 sec)

！！！防止不加where条件误操作的方法：
在mysql命令加上选项-U后，当发出没有WHERE或LIMIT关键字的UPDATE或DELETE时，mysql程序就会拒绝执行
登录数据库时：
mysql -uroot -predhat -U
还可以做命令别名操作：
alias ‘mysql’=‘mysql -U’
“echo alias mysql=’mysql -U’” >>/etc/profile

9.删除表中的数据：
语法：delete from 表名 where 匹配条件；

MariaDB [aaa]> delete from student where id=3;
Query OK, 1 row affected (0.00 sec)

MariaDB [aaa]> select id,name,age from student;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | jyy  |  19 |
|  2 | aaa  |  30 |
+----+------+-----+
2 rows in set (0.00 sec)

10.更改字符集乱码：
插入中文乱码时：set names latin1;
show variables\G; //查看变量
show global status; //查看数据库状态
set global key_buffer_size= //不重启修改MySQL
show variables like ‘key_buffer%’; //查询库表的字符集
show character set; //查看字符集
show variables like ‘character_set%’; //查看mysql的当前字符集设置情况
不乱码总结：统一MySQL数据库客户端以及服务器端字符集；

11.数据备份：
语法：mysqldump -u 用户名 -p 数据库名 > 备份的文件名
-B备份：作用是增加创建数据库和连接数据库的命令，表示连接多个库，并且·增加use db，和create database db的信息；要加上。

[root@yang ~]# mysqldump -uroot -p  -B aaa > aaa_bak.sql
Enter password: 
[root@yang ~]# ll aaa_bak.sql 
-rw-r--r-- 1 root root 2081 Jan 21 12:34 aaa_bak.sql
[root@yang ~]#

备份单个表：
语法：mysqldump -u用户名 -p密码数据库名表名 > 备份的文件名

[root@yang ~]# mysqldump -uroot -p  aaa student > aaa_stud_bak.sql
Enter password: 
[root@yang ~]# ll aaa_stud_bak.sql 
-rw-r--r-- 1 root root 1945 Jan 21 12:35 aaa_stud_bak.sql
[root@yang ~]#

备份多个表：
语法：mysqldump -u用户名 -p密码数据库表名1 表名2 > 备份的文件名

备份所有的库和表：
语法：mysqldump -u用户名 -p密码 -A -B --events | gzip > 备份的文件名

[root@yang ~]# mysqldump -uroot -p  -A -B --events|gzip  > all_bak.sql.gzEnter password: 
[root@yang ~]# ll
total 140
-rw-r--r-- 1 root root 139816 Jan 21 12:38 all_bak.sql.gz

mysqldump的关键参数说明：
关键参数：mysqldump –help
1、-B指定多个库，增加建库语句和use语句。
2、–compact去掉注释，适合调试输出，生产不用。
3、-A备份所有库。
4、-F刷新binlog日志。
5、–master-data增加binlong日志文件名以及对应的位置点。
6、-x，锁表。
7、-l，只读锁表。
8、-d，只备份表结构。
9、-t，只备份数据。
10、–single-transaction，适合innodb事务数据库备份。

12.恢复数据

MariaDB [(none)]> drop database aaa;
Query OK, 1 row affected (0.01 sec)

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

MariaDB [(none)]> source aaa_bak.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)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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 [aaa]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [aaa]> 
MariaDB [aaa]> select * from aaa.student;
+----+------+-----+------+
| id | name | age | dept |
+----+------+-----+------+
|  1 | jyy  |  19 | NULL |
|  2 | aaa  |  30 | NULL |
+----+------+-----+------+
2 rows in set (0.00 sec)