MySQL数据库1-CSDN博客

本文链接：https://blog.csdn.net/flowerxxxxx/article/details/112638470

本文详细记录了从创建数据库到删除表的过程，包括如何使用`CREATE DATABASE`、`SHOW DATABASES`、`DROP DATABASE`等命令，以及表的创建、修改、查看结构和删除。重点展示了如何使用UTF-8字符集和字段约束。

摘要由CSDN通过智能技术生成

#查看
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| person             |
| test               |
+--------------------+
4 rows in set (0.00 sec)
#创建数据库
mysql> create database goods charset=utf8;
Query OK, 1 row affected (0.00 sec)
mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| goods              |
| mysql              |
| person             |
| test               |
+--------------------+
5 rows in set (0.00 sec)

#使用数据库
mysql> use goods
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| goods      |
+------------+
1 row in set (0.00 sec)
#删除数据库
mysql> drop database goods
    -> ;
Query OK, 0 rows affected (0.00 sec)

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| person             |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql>  use goods;
Database changed
mysql> --查看当前数据库所有的表
mysql> show tables;
Empty set (0.00 sec)
#新建info表
mysql> --新建一张info表id，name，age;
mysql> create table info(
    -> --字段名字 数据类型 可选约束 无符号 主键 不为空 自增;
    -> id int unsigned primary key not null auto_increment,
    -> name char(10) not null ,
    -> age int
    -> );
Query OK, 0 rows affected (0.01 sec)



mysql> show tables
    -> ;
+-----------------+
| Tables_in_goods |
+-----------------+
| info            |
+-----------------+
1 row in set (0.00 sec)
#查看表结构
mysql> desc info;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(10)         | NO   |     | NULL    |                |
| age   | int(11)          | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
#删除表
mysql> drop table info;
Query OK, 0 rows affected (0.00 sec)
#查看当前库中的所有表
mysql> show tables;
Empty set (0.00 sec)
#修改表
mysql> --新建一张info表id，name，age;
mysql> create table info(
    -> --字段名字 数据类型 可选约束 无符号 主键 不为空 自增;
    -> id int unsigned primary key not null auto_increment,
    -> name char(10) not null ,
    -> age int
    -> );
Query OK, 0 rows affected (0.01 sec)	

#修改前后对比
mysql> desc info;#前
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | char(10)         | NO   |     | NULL    |                |
| age   | int(11)          | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

mysql> desc info;#后
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name  | varchar(200)     | NO   |     | NULL    |                |
| age   | int(11)          | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+

在这里插入图片描述