MySql数据库基础

一、MySql简介

Mysql是目前最流行的关系型数据库管理软件之一，是一款开源的、免费的web应用软件。它由库和表组成，所有的操作都是围绕着表中数据展开，表中的数据都是有着严格的规则和关系的。

二、MySql库级和表级操作

1、库级操作

1.显示所有的库：show databases;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |

2、创建库：create database db_name;create database if not exists db_name;

create database TanZhou;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| TanZhou            |
| mysql              |
| performance_schema |
| sys                |

3、删除库：drop database db_name;drop database if exists db_name;

drop database if exists TanZhou;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |

4、进入数据库：use db_name;

use TanZhou;
Database changed

2、表级操作

1、显示所有表格：show tables;

show tables;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| db                        |
| engine_cost               |
| event                     |
| func                      |
| general_log               |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| ndb_binlog_index          |
| plugin                    |
| proc                      |
| procs_priv                |
| proxies_priv              |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| students                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |

2、创建表：create table db_name (value);

create table student (id int,name varchar(64),age int,gender enum("男","女"));
Query OK, 0 rows affected (0.12 sec)

mysql> show tables;
+-------------------+
| Tables_in_TanZhou |
+-------------------+
| student           |

3、显示创建信息：show create table tb_name;

show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                          |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` enum('男','女') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8  

4、删除表：drop table if exists tb_name;

drop table if exists student;
Query OK, 0 rows affected (0.01 sec)
mysql> show tables;
Empty set (0.00 sec)