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 


 

相关内容

热门资讯

一分钟内幕!科乐吉林麻将系统发... 一分钟内幕!科乐吉林麻将系统发牌规律,福建大玩家确实真的是有挂,技巧教程(有挂ai代打);所有人都在...
一分钟揭秘!微扑克辅助软件(透... 一分钟揭秘!微扑克辅助软件(透视辅助)确实是有挂(2024已更新)(哔哩哔哩);1、用户打开应用后不...
五分钟发现!广东雀神麻雀怎么赢... 五分钟发现!广东雀神麻雀怎么赢,朋朋棋牌都是是真的有挂,高科技教程(有挂方法)1、广东雀神麻雀怎么赢...
每日必看!人皇大厅吗(透明挂)... 每日必看!人皇大厅吗(透明挂)好像存在有挂(2026已更新)(哔哩哔哩);人皇大厅吗辅助器中分为三种...
重大科普!新华棋牌有挂吗(透视... 重大科普!新华棋牌有挂吗(透视)一直是有挂(2021已更新)(哔哩哔哩)1、完成新华棋牌有挂吗的残局...
二分钟内幕!微信小程序途游辅助... 二分钟内幕!微信小程序途游辅助器,掌中乐游戏中心其实存在有挂,微扑克教程(有挂规律)二分钟内幕!微信...
科技揭秘!jj斗地主系统控牌吗... 科技揭秘!jj斗地主系统控牌吗(透视)本来真的是有挂(2025已更新)(哔哩哔哩)1、科技揭秘!jj...
1分钟普及!哈灵麻将攻略小,微... 1分钟普及!哈灵麻将攻略小,微信小程序十三张好像存在有挂,规律教程(有挂技巧)哈灵麻将攻略小是一种具...
9分钟教程!科乐麻将有挂吗,传... 9分钟教程!科乐麻将有挂吗,传送屋高防版辅助(总是存在有挂)1、完成传送屋高防版辅助透视辅助安装,帮...
每日必看教程!兴动游戏辅助器下... 每日必看教程!兴动游戏辅助器下载(辅助)真是真的有挂(2025已更新)(哔哩哔哩)1、打开软件启动之...