mysql-数据库-创建列表

一.创建列表

1..首先,进入mysql数据库  -->mysql -uroot -p

2. 其次,mysql默认的数据库类型为mydb,这时候,就得查看现在使用的类型

mysql> select database();

3. 如果创建的类型不同,则使用create database s_‘表名’来实现即可,创建完成后必须得使用该表名:

 完成这些操作后,就可以对该列表进行添加数据(增删改查)等操作。

二.创建列表和添加数据同时完成

mysql> create table stu_list(
    -> stu_vagetables char(20) comment'蔬菜',
    -> stu_number int comment'序列号',
    ->  stu_type text comment'类型',
    -> stu_produce BLOB comment'产地'
    -> )engine=InnoDb  character set gbk collate gbk_chinese_ci;

如果语法没有问题,就会显示已添加成功的提示:

 当列表创建完成后,就可以查看列表的各种信息了:

查看列表的数据结构(使用什么字段):

mysql> desc stu_list;
+----------------+----------+------+-----+---------+-------+
| Field          | Type     | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| stu_vagetables | char(20) | YES  |     | NULL    |       |
| stu_number     | int      | YES  |     | NULL    |       |
| stu_type       | text     | YES  |     | NULL    |       |
| stu_produce    | blob     | YES  |     | NULL    |       |
+----------------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)

三.数据的增删改查

        1.增加数据:

查看是否增加成功:

mysql> desc stu_list;
+----------------+----------+------+-----+---------+-------+
| Field          | Type     | Null | Key | Default | Extra |
+----------------+----------+------+-----+---------+-------+
| stu_vagetables | char(20) | YES  |     | NULL    |       |
| stu_number     | int      | YES  |     | NULL    |       |
| stu_type       | text     | YES  |     | NULL    |       |
| stu_produce    | blob     | YES  |     | NULL    |       |
| stu_color      | text     | YES  |     | NULL    |       |
+----------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)

         2.修改数据

 查看是否修改成功:

mysql> desc stu_list;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| stu_vagetables | char(20)      | YES  |     | NULL    |       |
| stu_number     | int           | YES  |     | NULL    |       |
| stu_type       | enum('M','F') | YES  |     | NULL    |       |
| stu_produce    | blob          | YES  |     | NULL    |       |
| stu_color      | text          | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

        3.删除数据

  查看是否删除成功:

mysql>  desc stu_list;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| stu_vagetables | char(20)      | YES  |     | NULL    |       |
| stu_number     | int           | YES  |     | NULL    |       |
| stu_type       | enum('M','F') | YES  |     | NULL    |       |
| stu_produce    | blob          | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

        4.更改表名

 查看是否更改成功:

mysql> desc stu_hobbits;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| stu_vagetables | char(20)      | YES  |     | NULL    |       |
| stu_number     | int           | YES  |     | NULL    |       |
| stu_type       | enum('M','F') | YES  |     | NULL    |       |
| stu_produce    | blob          | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

这时候,查看数据库就不能用之前的表名了,要使用更改之后的表名,并且之后要想查看列表,实用的命令都是desc  stu_hobbits;

        5.复制表的结构

意思就是想创建一个新表,但还是想要旧表里面的数据及其结构

(1).只复制数据

mysql> create table stu_info like stu_hobbits;
Query OK, 0 rows affected (0.02 sec)

mysql> desc stu_info;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| stu_vagetables | char(20)      | YES  |     | NULL    |       |
| stu_number     | int           | YES  |     | NULL    |       |
| stu_type       | enum('M','F') | YES  |     | NULL    |       |
| stu_produce    | blob          | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

(2).既有数据,又有结构

mysql> create table stu_book select *from stu_hobbits;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stu_book;
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| stu_vagetables | char(20)      | YES  |     | NULL    |       |
| stu_number     | int           | YES  |     | NULL    |       |
| stu_type       | enum('M','F') | YES  |     | NULL    |       |
| stu_produce    | blob          | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)