MySQL基础知识总结

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

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

  1. 常见的SQL语句:
    1)DDL—数据定义语言(CREATE,ALTER,DROP)–>运维
    2)DML—数据操作语言(SELECT,INSTERT,UPDATE,DELETE) -->开发
    3)DCL—数据控制语言(GRANT,REVOKE,COMMT,ROLLBACK)–>运维

  2. 创建数据库:
    语法: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)
  1. 删除数据库:
    语法: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)
  1. 连接数据库:
    命令:use 数据库名 //相当于linux下的cd切换目录,use是切换数据库
  2. 查看各项信息:
    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)

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值