Mysql第八次作业
创始人
2024-12-29 11:35:02
0


一、备份与恢复作业:
创库,建表:
  

 CREATE DATABASE booksDB;     use booksDB;      CREATE TABLE books     (     bk_id  INT NOT NULL PRIMARY KEY,     bk_title VARCHAR(50) NOT NULL,     copyright YEAR NOT NULL     );      CREATE TABLE authors     (     auth_id     INT NOT NULL PRIMARY KEY,     auth_name  VARCHAR(20),     auth_gender CHAR(1)     );      CREATE TABLE authorbook     (     auth_id  INT NOT NULL,     bk_id   INT NOT NULL     );

插入数据:
  

 INSERT INTO books         VALUES (11078, 'Learning MySQL', 2010),         (11033, 'Study Html', 2011),         (11035, 'How to use php', 2003),         (11072, 'Teach youself javascript', 2005),         (11028, 'Learing C++', 2005),         (11069, 'MySQL professional', 2009),         (11026, 'Guide to MySQL 5.5', 2008),         (11041, 'Inside VC++', 2011);          INSERT INTO authors           VALUES (1001, 'WriterX' ,'f'),         (1002, 'WriterA' ,'f'),         (1003, 'WriterB' ,'m'),         (1004, 'WriterC' ,'f'),         (1011, 'WriterD' ,'f'),         (1012, 'WriterE' ,'m'),         (1013, 'WriterF' ,'m'),         (1014, 'WriterG' ,'f'),         (1015, 'WriterH' ,'f');          INSERT INTO authorbook         VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),         (1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);


1、使用mysqldump命令备份数据库中的所有表

[root@node01 ~]# mysqldump -u root -p123456 booksDB > /root/booksDB.sql   [root@node01 ~]# ll total 33560 -rw-r--r--  1 root root      3909 Jul 12 16:31 booksDB.sql


2、备份booksDB数据库中的books表

[root@node01 ~]# mysqldump -u root -p123456 booksDB books > /root/books.sql mysqldump: [Warning] Using a password on the command line interface can be insecure.   [root@node01 ~]# ll total 570976 -rw-r--r--  1 root root      3909 Jul 12 16:31 booksDB.sql -rw-r--r--  1 root root      2121 Jul 12 16:44 books.sql


3、使用mysqldump备份booksDB和test数据库(test数据库自行准备)

[root@node01 ~]# mysqldump -u root -p123456 --database booksDB test > /root/booksDB.sql test.sql


4、使用mysql命令还原第二题导出的book表

[root@node1 ~]# mysql -u root -p booksDB select * from books; +-------+--------------------------+-----------+ | bk_id | bk_title                 | copyright | +-------+--------------------------+-----------+ | 11026 | Guide to MySQL 5.5       |      2008 | | 11028 | Learing C++              |      2005 | | 11033 | Study Html               |      2011 | | 11035 | How to use php           |      2003 | | 11041 | Inside VC++              |      2011 | | 11069 | MySQL professional       |      2009 | | 11072 | Teach youself javascript |      2005 | | 11078 | Learning MySQL           |      2010 | +-------+--------------------------+-----------+ 


5、进入数据库使用source命令还原第二题导出的book表

mysql> drop table books; mysql> source /root/mysql/table_books.sql mysql> select * from books; +-------+--------------------------+-----------+ | bk_id | bk_title                 | copyright | +-------+--------------------------+-----------+ | 11026 | Guide to MySQL 5.5       |      2008 | | 11028 | Learing C++              |      2005 | | 11033 | Study Html               |      2011 | | 11035 | How to use php           |      2003 | | 11041 | Inside VC++              |      2011 | | 11069 | MySQL professional       |      2009 | | 11072 | Teach youself javascript |      2005 | | 11078 | Learning MySQL           |      2010 | +-------+--------------------------+-----------+ 

二、索引作业:
创库,建表:
    

mysql> create table goods(     goods_id int primary key auto_increment,     goods_name varchar(20) not null,     cat_id int not null default 0,     brand_id int not null default 0,     goods_sn char(12) not null,     shop_price float(6,2) not null default 0.00,     goods_desc text      );           mysql> create table category(      cat_id int primary key auto_increment,      cate_name varchar(20),      parent_id int default 0 );


1、删除 goods 表中的 goods_desc 字段及货号字段,并增加 click_count 字段

mysql> alter table goods drop column goods_desc; mysql> alter table goods drop column goods_sn; +-------------+-------------+------+-----+---------+----------------+ | Field       | Type        | Null | Key | Default | Extra          | +-------------+-------------+------+-----+---------+----------------+ | goods_id    | int(11)     | NO   | PRI | NULL    | auto_increment | | goods_name  | varchar(20) | NO   | UNI | NULL    |                | | cat_id      | int(11)     | NO   |     | 0       |                | | brand_id    | int(11)     | NO   |     | 0       |                | | shop_price  | float(6,2)  | NO   |     | 0.00    |                | +-------------+-------------+------+-----+---------+----------------+   mysql> alter table goods add click_count int; +-------------+-------------+------+-----+---------+----------------+ | Field       | Type        | Null | Key | Default | Extra          | +-------------+-------------+------+-----+---------+----------------+ | goods_id    | int(11)     | NO   | PRI | NULL    | auto_increment | | goods_name  | varchar(20) | NO   |     | NULL    |                | | cat_id      | int(11)     | NO   |     | 0       |                | | brand_id    | int(11)     | NO   |     | 0       |                | | goods_sn    | char(12)    | NO   |     | NULL    |                | | shop_price  | float(6,2)  | NO   |     | 0.00    |                | | click_count | int(11)     | YES  |     | NULL    |                | +-------------+-------------+------+-----+---------+----------------+ 


2、在 goods_name 列上加唯一性索引(用alter table方式):

mysql> alter table goods add unique index goods_name_index (goods_name(20)); mysql> show create table goods\G; *************************** 1. row ***************************        Table: goods Create Table: CREATE TABLE `goods` (   `goods_id` int(11) NOT NULL AUTO_INCREMENT,   `goods_name` varchar(20) NOT NULL,   `cat_id` int(11) NOT NULL DEFAULT '0',   `brand_id` int(11) NOT NULL DEFAULT '0',   `shop_price` float(6,2) NOT NULL DEFAULT '0.00',   `click_count` int(11) DEFAULT NULL,   PRIMARY KEY (`goods_id`),   UNIQUE KEY `goods_name_index` (`goods_name`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 


2、在 shop_price 列上加普通索引(用create index方式)

mysql> create index g_price_index on goods(shop_price); mysql> show create table goods\G; *************************** 1. row ***************************        Table: goods Create Table: CREATE TABLE `goods` (   `goods_id` int(11) NOT NULL AUTO_INCREMENT,   `goods_name` varchar(20) NOT NULL,   `cat_id` int(11) NOT NULL DEFAULT '0',   `brand_id` int(11) NOT NULL DEFAULT '0',   `shop_price` float(6,2) NOT NULL DEFAULT '0.00',   `click_count` int(11) DEFAULT NULL,   PRIMARY KEY (`goods_id`),   UNIQUE KEY `goods_name_index` (`goods_name`),   KEY `g_price_index` (`shop_price`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 


4、在 click_count 上增加普通索引,然后再删除 (分别使用drop index和alter table删除)

mysql> alter table goods add index c_l_index (click_count); mysql> show create table goods\G; *************************** 1. row ***************************        Table: goods Create Table: CREATE TABLE `goods` (   `goods_id` int(11) NOT NULL AUTO_INCREMENT,   `goods_name` varchar(20) NOT NULL,   `cat_id` int(11) NOT NULL DEFAULT '0',   `brand_id` int(11) NOT NULL DEFAULT '0',   `shop_price` float(6,2) NOT NULL DEFAULT '0.00',   `click_count` int(11) DEFAULT NULL,   PRIMARY KEY (`goods_id`),   UNIQUE KEY `goods_name_index` (`goods_name`),   KEY `g_price_index` (`shop_price`),   KEY `c_l_index` (`click_count`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4  mysql> alter table goods drop index c_l_index; mysql> drop index c_l_index on goods;  mysql> show create table goods\G; *************************** 1. row ***************************        Table: goods Create Table: CREATE TABLE `goods` (   `goods_id` int(11) NOT NULL AUTO_INCREMENT,   `goods_name` varchar(20) NOT NULL,   `cat_id` int(11) NOT NULL DEFAULT '0',   `brand_id` int(11) NOT NULL DEFAULT '0',   `shop_price` float(6,2) NOT NULL DEFAULT '0.00',   `click_count` int(11) DEFAULT NULL,   PRIMARY KEY (`goods_id`),   UNIQUE KEY `goods_name_index` (`goods_name`),   KEY `g_price_index` (`shop_price`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 


 

相关内容

热门资讯

热点推荐!决战十三水ios版辅... 热点推荐!决战十三水ios版辅助器(辅助挂)总是是真的有挂(2020已更新)(哔哩哔哩)亲,关键说明...
重大通报!!牛郎棋牌助手黑科技... 重大通报!!牛郎棋牌助手黑科技(透明挂)透视辅助挂(2020已更新)(哔哩哔哩)1、完成牛郎棋牌助手...
两分钟攻略!碧海麻将是不是有挂... 两分钟攻略!碧海麻将是不是有挂的,wepoKE都是是有挂,教你攻略(有挂神器);1、碧海麻将是不是有...
重大通报!兴动辅助器免费版(辅... 重大通报!兴动辅助器免费版(辅助)确实是真的有挂(2024已更新)(哔哩哔哩)1、完成兴动辅助器免费...
每日必看!牌乐门如何拿到好牌(... 每日必看!牌乐门如何拿到好牌(透视)透视脚本辅助插件(2025已更新)(哔哩哔哩)一、牌乐门如何拿到...
6分钟科普!决战血流麻将湖北里... 6分钟科普!决战血流麻将湖北里面是不是有挂,德扑助手好像真的是有挂,黑科技教程(有挂科普)小薇(透视...
推荐十款!!网易棋牌挂怎么开(... 推荐十款!!网易棋牌挂怎么开(透视辅助)外挂透明挂辅助器(2020已更新)(哔哩哔哩);1、网易棋牌...
每日必看教程!星悦手机麻将是不... 每日必看教程!星悦手机麻将是不是挂(辅助)果然是真的有挂(2023已更新)(哔哩哔哩)1、每一步都需...
五分钟辅助!中至宜春麻将助赢神... 五分钟辅助!中至宜春麻将助赢神器,pokermaster总是是真的有挂,AI教程(有挂神器)1、让任...
盘点一款!!微乐第三方辅助工具... 盘点一款!!微乐第三方辅助工具下载(透明挂)透视辅助神器(2024已更新)(哔哩哔哩)1、微乐第三方...