MySql基本操作，建库删库，建表，查看

Microsoft Windows [版本 10.0.18363.1016] (c) 2019 Microsoft Corporation。保留所有权利。 C:\Users\woodd>mysql -u root -p //登录默认root账户 Enter password: ********** //输入密码 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 62 Server version: 8.0.21 MySQL Community Server - GPL Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; //显示数据库列表 +----------------------------+ | Database | +----------------------------+ | db_beego | | douban_crawl_movie_info_01 | | information_schema | | mysql | | performance_schema | | sakila | | sys | | world | +----------------------------+ 8 rows in set (0.00 sec) mysql> drop database douban_crawl_movie_info_01 //删除douban_crawl_movie_info_01数据库 -> ; Query OK, 1 row affected (0.07 sec) mysql> show databases; //显示数据库删除成功 +--------------------+ | Database | +--------------------+ | db_beego | | information_schema | | mysql | | performance_schema | | sakila | | sys | | world | +--------------------+ 7 rows in set (0.01 sec) mysql> create database crawl_test; //创建数据库crawl_test Query OK, 1 row affected (0.01 sec) mysql> use crawl_test; //使用crawl_test数据库 Database changed mysql> CREATE TABLE `doubancrawl_movieinfo` ( //在crawl_test数据库中创建表格 -> `id` int(10) unsigned NOT NULL AUTO_INCREMENT, -> `movie_id` int(11) unsigned NOT NULL COMMENT '电影id', -> `movie_name` varchar(100) COMMENT '电影名称', -> `movie_directer` varchar(50) COMMENT '电影导演', -> `movie_writer` varchar(50) COMMENT '电影编剧', -> `movie_country` varchar(50) COMMENT '电影产地', -> `movie_language` varchar(50) COMMENT '电影语言', -> `movie_main_character` varchar(50) COMMENT '电影主演', -> `movie_type` varchar(50) COMMENT '电影类型', -> `movie_on_time` timestamp DEFAULT '0000-00-00 00:00:00' COMMENT '电影上映时间', -> `movie_span` varchar(20) COMMENT '电影时长', -> `movie_grade` varchar(5) COMMENT '电影评分', -> `remark` varchar(500) COMMENT '备注', -> `_create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间', -> `_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间', -> `_status` tinyint(1) DEFAULT '1', -> PRIMARY KEY (`id`), -> KEY `idx_movie_id` (`movie_id`), -> KEY `idx_create_time` (`_create_time`), -> KEY `idx_modify_time` (`_modify_time`) -> ) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COMMENT='电影信息表'; Query OK, 0 rows affected, 4 warnings (0.07 sec) mysql> show tables; //显示crawl_test数据库里的表格 +-----------------------+ | Tables_in_crawl_test | +-----------------------+ | doubancrawl_movieinfo | +-----------------------+ 1 row in set (0.01 sec) mysql> desc doubancrawl_movieinfo; //查看doubancrawl_movieinfo表格的描述 +----------------------+--------------+------+-----+---------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+--------------+------+-----+---------------------+-----------------------------------------------+ | id | int unsigned | NO | PRI | NULL | auto_increment | | movie_id | int unsigned | NO | MUL | NULL | | | movie_name | varchar(100) | YES | | NULL | | | movie_directer | varchar(50) | YES | | NULL | | | movie_writer | varchar(50) | YES | | NULL | | | movie_country | varchar(50) | YES | | NULL | | | movie_language | varchar(50) | YES | | NULL | | | movie_main_character | varchar(50) | YES | | NULL | | | movie_type | varchar(50) | YES | | NULL | | | movie_on_time | timestamp | YES | | 0000-00-00 00:00:00 | | | movie_span | varchar(20) | YES | | NULL | | | movie_grade | varchar(5) | YES | | NULL | | | remark | varchar(500) | YES | | NULL | | | _create_time | timestamp | NO | MUL | 0000-00-00 00:00:00 | | | _modify_time | timestamp | NO | MUL | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | | _status | tinyint(1) | YES | | 1 | | +----------------------+--------------+------+-----+---------------------+-----------------------------------------------+ 16 rows in set (0.01 sec) mysql> show tables; //显示表格 +-----------------------+ | Tables_in_crawl_test | +-----------------------+ | doubancrawl_movieinfo | +-----------------------+ 1 row in set (0.00 sec) mysql> select * from doubancrawl_movieinfo; //查询doubancrawl_movieinfo表中内容 +----+----------+------------+----------------+--------------+---------------+----------------+--------------------------------------------------+------------+---------------------+------------+-------------+--------+---------------------+---------------------+---------+ | id | movie_id | movie_name | movie_directer | movie_writer | movie_country | movie_language | movie_main_character | movie_type | movie_on_time | movie_span | movie_grade | remark | _create_time | _modify_time | _status | +----+----------+------------+----------------+--------------+---------------+----------------+--------------------------------------------------+------------+---------------------+------------+-------------+--------+---------------------+---------------------+---------+ | 20 | 0 | 羞羞的铁拳 | 宋阳/张吃鱼 | | 中国大陆 | 汉语普通话 | 艾伦/马丽/沈腾/田雨/宋阳/薛皓文/常远/黄才伦/王智 | 喜剧/奇幻 | 0000-00-00 00:00:00 | 100分钟 | 6.8 | NULL | 0000-00-00 00:00:00 | 2020-10-20 16:19:05 | 1 | +----+----------+------------+----------------+--------------+---------------+----------------+--------------------------------------------------+------------+---------------------+------------+-------------+--------+---------------------+---------------------+---------+ 1 row in set (0.00 sec) mysql> select * from Doubancrawl_movieinfo\G; //查询表中内容\G纵向 *************************** 1. row *************************** id: 20 movie_id: 0 movie_name: 羞羞的铁拳 movie_directer: 宋阳/张吃鱼 movie_writer: movie_country: 中国大陆 movie_language: 汉语普通话 movie_main_character: 艾伦/马丽/沈腾/田雨/宋阳/薛皓文/常远/黄才伦/王智 movie_type: 喜剧/奇幻 movie_on_time: 0000-00-00 00:00:00 movie_span: 100分钟 movie_grade: 6.8 remark: NULL _create_time: 0000-00-00 00:00:00 _modify_time: 2020-10-20 16:19:05 _status: 1 1 row in set (0.00 sec) ERROR: No query specified mysql> select * from Doubancrawl_movieinfo\G; *************************** 1. row *************************** id: 20 movie_id: 0 movie_name: 羞羞的铁拳 movie_directer: 宋阳/张吃鱼 movie_writer: movie_country: 中国大陆 movie_language: 汉语普通话 movie_main_character: 艾伦/马丽/沈腾/田雨/宋阳/薛皓文/常远/黄才伦/王智 movie_type: 喜剧/奇幻 movie_on_time: 0000-00-00 00:00:00 movie_span: 100分钟 movie_grade: 6.8 remark: NULL _create_time: 0000-00-00 00:00:00 _modify_time: 2020-10-20 16:19:05 _status: 1 *************************** 2. row *************************** id: 21 movie_id: 0 movie_name: 羞羞的铁拳 movie_directer: 宋阳/张吃鱼 movie_writer: movie_country: 中国大陆 movie_language: 汉语普通话 movie_main_character: 艾伦/马丽/沈腾/田雨/宋阳/薛皓文/常远/黄才伦/王智 movie_type: 喜剧/奇幻 movie_on_time: 2017-09-30 00:00:00 movie_span: 100分钟 movie_grade: 6.8 remark: NULL _create_time: 0000-00-00 00:00:00 _modify_time: 2020-10-20 16:35:26 _status: 1 2 rows in set (0.00 sec) ERROR: No query specified mysql> delete from Doubancrawl_movieinfo //删除表中内容 -> ; Query OK, 2 rows affected (0.01 sec) mysql> select * from Doubancrawl_movieinfo\G; //此时查询无内容 Empty set (0.00 sec) ERROR: No query specified mysql> drop table Doubancrawl_movieinfo; //删除表格 Query OK, 0 rows affected (0.06 sec) mysql> show tables; //此时查询无表格 Empty set (0.00 sec) mysql> mysql>