mysql数据库备份和查询优化语句命令

红太狼 2022-05-23 07:57 254阅读 0赞

数据库小积累

## 查看数据库执行sql语句优化 ##

mysql> EXPLAIN EXTENDED
        -> -> SELECT dept_name from course NATURAL JOIN section where `year` = 2009 GROUP BY dept_name HAVING COUNT(course_id) <= 1;
    
    ---- 结果----
    +----+-------------+---------+------------+--------+-------------------+-------------+---------+------------------------------+------+----------+-----------------------------------------------------------+
    | id | select_type | table   | partitions | type   | possible_keys     | key         | key_len | ref                          | rows | filtered | Extra                                                     |
    +----+-------------+---------+------------+--------+-------------------+-------------+---------+------------------------------+------+----------+-----------------------------------------------------------+
    |  1 | SIMPLE      | section | NULL       | index  | PRIMARY           | section_fk2 | 28      | NULL                         |  100 |    10.00 | Using where; Using index; Using temporary; Using filesort |
    |  1 | SIMPLE      | course  | NULL       | eq_ref | PRIMARY,course_fk | PRIMARY     | 10      | university.section.course_id |    1 |   100.00 | NULL                                                      |
    +----+-------------+---------+------------+--------+-------------------+-------------+---------+------------------------------+------+----------+-----------------------------------------------------------+
    2 rows in set, 2 warnings (0.00 sec)
    
    mysql> show warnings ;
    +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Level   | Code | Message                                                                                                                                                                                                                                                                                                                                                     |
    +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                                                                                                                                                                                                                                                                           |
    | Note    | 1003 | /* select#1 */ select `university`.`course`.`dept_name` AS `dept_name` from `university`.`course` join `university`.`section` where ((`university`.`course`.`course_id` = `university`.`section`.`course_id`) and (`university`.`section`.`year` = 2009)) group by `university`.`course`.`dept_name` having (count(`university`.`course`.`course_id`) <= 1) |
    +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    2 rows in set (0.00 sec)

## 数据库备份数据 ##

# 只导出表结构
    mysqldump -u root -p -d university > abc.sql
    # 导出表结构以及数据
    mysqldump -u root -p xx > abc.sql
    # 导入数据
    1、创建数据库
    2、mysql -u root -p xx < abc.sql