MySQL（1）：数据类型、库的操作、表的操作

数据类型

mysql的数据类型主要分为数值类型、日期/时间类型、字符串类型三种

数值类型

日期/时间类型

字符串类型

库的操作

显示当前数据库

语法

show databases;

示例

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| study              |
+--------------------+
4 rows in set (0.00 sec)

创建数据库

语法

create database [库名];

示例

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| study              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

使用数据库

语法

use [库名];

示例

MariaDB [(none)]> use test;
Database changed

删除数据库

语法

drop database [库名];

示例

MariaDB [(none)]> drop database test;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| study              |
+--------------------+
4 rows in set (0.00 sec)

表的操作

创建表

语法

create table [表名]([数据名] [数据类型], [数据名] [数据类型], [数据名] [数据类型] ......);

示例

MariaDB [study]> create table student( id int, name varchar(8), age int, birth datetime, chinese decimal(4,2), math decimal(4,2), english decimal(4,2));
Query OK, 0 rows affected (0.03 sec)

MariaDB [study]> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)

显示当前库中所有表

语法

show tables;

示例

MariaDB [study]> show tables;
+-----------------+
| Tables_in_study |
+-----------------+
| student         |
+-----------------+
1 row in set (0.00 sec)

显示当前所使用的库

语法

select database();

示例

MariaDB [study]> select database();
+------------+
| database() |
+------------+
| study      |
+------------+
1 row in set (0.00 sec)

查看表结构

语法

desc [表名];

示例

MariaDB [study]> desc student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id      | int(11)      | YES  |     | NULL    |       |
| name    | varchar(8)   | YES  |     | NULL    |       |
| age     | int(11)      | YES  |     | NULL    |       |
| birth   | datetime     | YES  |     | NULL    |       |
| chinese | decimal(4,2) | YES  |     | NULL    |       |
| math    | decimal(4,2) | YES  |     | NULL    |       |
| english | decimal(4,2) | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)

删除表

语法

drop database [表名];

示例

MariaDB [(none)]> drop database test;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| study              |
+--------------------+
4 rows in set (0.00 sec)