Mysql数据库操作总结

ゝ一纸荒年。 2022-12-02 00:51 9468阅读 0赞

### Mysql数据库操作 ###

*  一、Mysql基本操作
 *   *   *  连接数据库
         *   *   *  (1)连接到本机上的Mysql
                 *  (2)连接远程主机上的Mysql
         *  Mysql基本操作
         *   *   *  1.显示数据库列表
                 *  2. 更换数据库
                 *  3.显示数据表的结构
                 *  4.建库：
                 *  5.建表：
                 *  6.删库和删表:
                 *  7.将表中记录清空：
                 *  8.退出数据库
         *  查询操作
         *   *   *  1.查询数据库中所有表名
                 *  2.查询指定数据库中指定表的所有字段名column\_name
                 *  3.mysql查询数据库大小
                 *  4.查询时间操作
         *  更新表
         *   *   *  更新某个字段
         *  增加表的字段
         *   *   *  1) 加索引
                 *  2) 加主关键字的索引
                 *  3) 加唯一限制条件的索引
                 *  4) 删除某个索引
                 *  5) 增加字段
                 *  6) 修改原字段名称及类型
                 *  7) 删除字段
 *  二、数据备份与恢复
 *   *   *  （1）备份：从数据库导出数据：
         *  （2）备份导出示例：
         *  （3）恢复导入数据库数据：
         *  （4）具体恢复示例：
 *  三、执行外部sql脚本文件的命令
 *   *   *  创建sql脚本
         *  执行文件

# 一、Mysql基本操作 #

### 连接数据库 ###

##### (1)连接到本机上的Mysql #####

mysql-uroot -p，回车后提示你输密码

##### (2)连接远程主机上的Mysql #####

*  mysql -h主机地址 -u用户名 －p用户密码

mysql -h25.56.62.12 -uroot -p #回车后提示你输密码

注意：u与root可以不用加空格

### Mysql基本操作 ###

##### 1.显示数据库列表 #####

show databases;

##### 2. 更换数据库 #####

use mysql；\#（mysql为数据库名）  
show tables; \#显示表

##### 3.显示数据表的结构 #####

describe 表名;

简写：desc 表名

##### 4.建库： #####

create database 库名;  
或者：create database IF NOT EXISTS 库名;  
IF NOT EXISTS是判断是否存在，存在则创建，不存在则不创建。

##### 5.建表： #####

use 库名；

create table 表名 (字段设定列表)；  
示例：create table IF NOT EXISTS TABLENAME ( name varchar(20), id int(11) default 0；

##### 6.删库和删表: #####

drop database 库名;

drop table 表名；

##### 7.将表中记录清空： #####

delete from 表名;

##### 8.退出数据库 #####

exit; （回车）或者quit;

### 查询操作 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkyNDUwMA_size_16_color_FFFFFF_t_70_pic_center]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkyNDUwMA_size_16_color_FFFFFF_t_70_pic_center 1]

information\_schema数据库

*  information\_schema这这个数据库中保存了MySQL服务器所有数据库的信息。  
    如数据库名，数据库的表，表栏的数据类型与访问权限等。  
    再简单点，这台MySQL服务器上，到底有哪些数据库、各个数据库有哪些表，  
    每张表的字段类型是什么，各个数据库要什么权限才能访问，等等信息都保存在information\_schema里面。
 *  information\_schema的表**schemata**中的列schema\_name记录了所有数据库的名字
 *  information\_schema的表**tables**中的列table\_schema记录了所有数据库的名字
 *  information\_schema的表**tables**中的列table\_name记录了所有数据库的表的名字
 *  information\_schema的表**columns**中的列table\_schema记录了所有数据库的名字
 *  information\_schema的表**columns**中的列table\_name记录了所有数据库的表的名字
 *  information\_schema的表**columns**中的列column\_name记录了所有数据库的表的列的名字

##### 1.查询数据库中所有表名 #####

select table_name from information_schema.tables where table_schema='数据库名称' and table_type='base table';

##### 2.查询指定数据库中指定表的所有字段名column\_name #####

select column_name from information_schema.columns where table_schema='数据库名称' and table_name='users'

##### 3.mysql查询数据库大小 #####

需要进入information\_schema 数据库，或者直接写出数据库名。本示例为直接写数据库名，如果不想写数据库名则进入information\_schema 数据库，去除每个语句的information\_schema 。

*  查询整个库中各个数据库的大小

select table_schema ,round(sum(data_length/1024/1024),2) as size from information_schema.tables group by table_schema order by size desc ;

*  查询整个库数据的大小

select round(sum(data_length/1024/1024),2) as size from  information_schema.tables;

*  查询单个数据库大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from  information_schema.tables WHERE table_schema = '数据库名';

*  查询某个表大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables where table_schema='数据库名' and table_name='表名';

##### 4.查询时间操作 #####

表为：t\_user  
字段为：fullName,addedTime

-- 今天  
    select fullName,addedTime from t_user where to_days(addedTime) <= to_days(now()); 
    -- 昨天  
    select fullName,addedTime from t_user where to_days(NOW()) - TO_DAYS(addedTime) <= 1;  
    -- 近7天  
    select fullName,addedTime from t_user where date_sub(CURDATE(),INTERVAL 7 DAY) <= DATE(addedTime);  
    -- 近30天  
    SELECT fullName,addedTime FROM t_user where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(addedTime);
    -- 本月  
    SELECT fullName,addedTime FROM t_user WHERE DATE_FORMAT( addedTime, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' );
    -- 上一月  
    SELECT fullName,addedTime FROM t_user WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( addedTime, '%Y%m' ) ) =1; 
    -- 查询本季度数据  
    select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(now()); 
    -- 查询上季度数据  
    select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));  
    -- 查询本年数据  
    select fullName,addedTime FROM t_user where YEAR(addedTime)=YEAR(NOW());  
    -- 查询上年数据  
    select fullName,addedTime FROM t_user where year(addedTime)=year(date_sub(now(),interval 1 year));  
    -- 查询距离当前现在6个月的数据  
    select fullName,addedTime FROM t_user where addedTime between date_sub(now(),interval 6 month) and now();  
     
    -- 查询当前这周的数据  
    SELECT fullName,addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d')) = YEARWEEK(now());  
    -- 查询上周的数据  
    SELECT fullName,addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d')) = YEARWEEK(now())-1;  
    -- 查询上个月的数据   
    select fullName,addedTime FROM t_user where date_format(addedTime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m'); 
    -- 查询当前月份的数据
    select fullName,addedTime FROM t_user where DATE_FORMAT(addedTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');
    select fullName,addedTime FROM t_user where date_format(addedTime,'%Y-%m')=date_format(now(),'%Y-%m'); 
     
    -- 查询指定时间段的数据
    select fullName,addedTime FROM t_user where addedTime between  '2017-1-1 00:00:00'  and '2018-1-1 00:00:00';   
    select fullName,addedTime FROM t_user where addedTime >='2017-1-1 00:00:00'  and addedTime < '2018-1-1 00:00:00';

1、查询时间段内的数据，一般可以用between and 或 <> 来指定时间段。

2、mysql的时间字段类型有：datetime，timestamp，date，time，year。

3、 获取系统当前时间的函数：  
select CURDATE();  
select NOW();  
4、获取时间差的函数：  
period\_diff() datediff(date1,date2) timediff(time1,time2)

5、日期加减函数：  
date\_sub()  
date\_add() adddate() addtime()  
period\_add(P,N)

6、时间格式转化函数：  
date\_format(date, format) ，MySQL日期格式化函数date\_format()  
unix\_timestamp()  
str\_to\_date(str, format)  
from\_unixtime(unix\_timestamp, format) ，MySQL时间戳格式化函数from\_unixtime

### 更新表 ###

##### 更新某个字段 #####

更新apts表中timestamp字段

update apts set timestamp = NOW();

### 增加表的字段 ###

alter add命令用来增加表的字段。

alter add命令格式：alter table 表名 add字段 类型 其他;

例如，在表MyClass中添加了一个字段passtest，类型为int(4)，默认值为0：  
mysql> alter table MyClass add passtest int(4) default ‘0’;

##### 1) 加索引 #####

mysql> alter table 表名 add index 索引名 (字段名1\[，字段名2 …\]);

例子： mysql> alter table employee add index emp\_name (name);

##### 2) 加主关键字的索引 #####

mysql> alter table 表名 add primary key (字段名);

例子： mysql> alter table employee add primary key(id);

##### 3) 加唯一限制条件的索引 #####

mysql> alter table 表名 add unique 索引名 (字段名);

例子： mysql> alter table employee add unique emp\_name2(cardnumber);

##### 4) 删除某个索引 #####

mysql> alter table 表名 drop index 索引名;

例子： mysql>alter table employee drop index emp\_name;

##### 5) 增加字段 #####

mysql> ALTER TABLE table\_name ADD field\_name field\_type;

##### 6) 修改原字段名称及类型 #####

mysql> ALTER TABLE table\_name CHANGE old\_field\_name new\_field\_name field\_type;

##### 7) 删除字段 #####

MySQL ALTER TABLE table\_name DROP field\_name;

# 二、数据备份与恢复 #

mysqldump -uusername -ppassword --default-character-set=gb2312 db1 table1 > tb1.sql

mysqldump客户端可用来转储数据库或搜集数据库进行备份或将数据转移到另一个sql服务器(不一定是一个mysql服务器)。转储包含创建表和/或装载表的sql语句。  
如果在服务器上进行备份，并且表均为myisam表，应考虑使用mysqlhotcopy，因为可以更快地进行备份和恢复。  
[mysqldump参数说明][mysqldump]

### （1）备份：从数据库导出数据： ###

格式：mysqldump -h链接ip -P(大写)端口 -u用户名 -p密码数据库名>d:XX.sql(路劲)

示例：mysqldump -h132.72.192.432 -P3307 -uroot -p8888 htgl > bak.sql;

### （2）备份导出示例： ###

1、导出数据和表结构——将特定数据库特定表中的数据和表格结构和数据全部返回

mysqldump --u b\_user -h 101.3.20.33 -p’H\_password’ -P3306 database\_di up\_subjects > 0101\_0630\_up\_subjects.sql

2、导出表结构却不导出表数据——只返回特定数据库特定表格的表格结构，不返回数据,添加“-d”命令参数

mysqldump --u b\_user -h 101.3.20.33 -p’H\_password’ -P3306 -d database\_di up\_subjects > 0101\_0630\_up\_subjects.sql

3、导出表结构和满足挑顶条件的表数据——只返回特定数据库中特定表的表格结构和满足特定条件的数据

mysqldump --u b\_user -h 101.3.20.33 -p’H\_password’ -P3306 database\_di up\_subjects --where=" ctime>‘2017-01-01’ and ctime<‘2017-06-30’" > 0101\_0630\_up\_subjects.sql

4、导出数据却不导出表结构——只返回特定数据库中特定表格的数据，不返回表格结构，添加“-t”命令参数

mysqldump --u b\_user -h 101.3.20.33 -p’H\_password’ -t -P3306 database\_di up\_subjects >0101\_0630\_up\_subjects.sql

5、导出特定数据库的所有表格的表结构及其数据，添加“–databases ”命令参数

mysqldump --u b\_user -h 101.3.20.33 -p’H\_password’ -P3306 --databases test > all\_database.sql

### （3）恢复导入数据库数据： ###

将导出的本地文件导入到指定数据库

1、系统命令行

格式：mysql -h链接ip -P(大写)端口 -u用户名 -p密码 数据库名 < d:XX.sql(路劲)

mysql -uusername -ppassword db1 <tb1tb2.sql

2、或mysql命令行

mysql>

user db1;

source tb1\_tb2.sql;

3、恢复整个数据库的方法：

mysql -u b\_user -h 101.3.20.33 -p’H\_password’ -P3306 < all\_database.sql

### （4）具体恢复示例： ###

1、先登录该MySQL服务器，切换要导入数据的数据

具体命令如下：

mysql> use test;

mysql> source /home/test/0101\_0630\_up\_subjects.sql

Query OK, 0 rows affected (0.01 sec)

……

Query OK, 0 rows affected (0.00 sec)

Query OK, 9 rows affected (0.00 sec)

Records: 9 Duplicates: 0 Warnings: 0

注释：表示影响的记录为9行，重复的记录有0行，警告的记录有0个

2、直接使用系统命令行

mysql -u b\_user -h 101.3.20.33 -p’H\_password’ -P3306 test </home/test/0101\_0630\_up\_subjects.sql

# 三、执行外部sql脚本文件的命令 #

### 创建sql脚本 ###

use ttp;  #选择数据库
    
    --创建表，使用“--”进行注释
    create table 表名称
    (                   
      Guid Varchar(38) not null primary key,  
      Title Varchar(255),       
                  
    ) TYPE=InnoDB;
    
    --在表A中增加字段Status
    alter table A add Status TinyInt default '0';
    
    --在表A中更新操作
    update A set timestamp = NOW(); 
    
    --在表A上创建索引
    create index XX_TaskId_1 on A(Id_);
    
    --在A表中添加一条记录
    Insert into A (Id,ParentId, Name) values(1,0,'名称');
    
    --添加、修改、删除数据后，有可能需要提交事务
    Commit;

### 执行文件 ###

*  方法一 mysql –u用户名 –p密码 –D数据库<【sql脚本文件路径全名】  
    示例：  
    mysql –uroot –p123456 -Dtest<d:\\test\\ss.sql  
    注意：  
    A、如果在sql脚本文件中使用了use 数据库，则-D数据库选项可以忽略
 *  方法二 进入mysql的控制台后，使用source命令执行  
    Mysql>source 【sql脚本文件的路径全名】 或 Mysql>. 【sql脚本文件的路径全名】  
    示例：  
    source d:\\test\\ss.sql 或者 . d:\\test\\ss.sql

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkyNDUwMA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/d8246f1967fb4574bc939e25f1bb9c21.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl8zODkyNDUwMA_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/185253f5c4f54ac398ba83a40f984b65.png
[mysqldump]: https://blog.csdn.net/weixin_38924500/article/details/108327876