数据库有关表的创建-CSDN博客

1、创建 student 数据库，该数据库的默认字符集为 gbk，默认的校对规则为 gbk_chinese_ci

mysql> create database if not exists student character set gbk collate gbk_chinese_ci;
Query OK, 1 row affected (0.01 sec)

mysql> show create database student;
+----------+----------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                    |
+----------+----------------------------------------------------------------------------------------------------+
| student  | CREATE DATABASE `student` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2、在 student 数据库中创建 user_list 数据表，该表使用 MyISAM 引擎，该表定义了三个字段，要求分别如下：
id 字段，该字段数据类型为 int(3)
username 字段，该字段数据类型为 varchar(12)

mysql> show databases;//查看所有创建的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| student            | //我们创建的student数据库
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)

mysql> select database();//查看当前所使用的数据库
+------------+
| database() |
+------------+
| NULL       |  //NULL表示当前没有使用数据库
+------------+
1 row in set (0.00 sec)

mysql> use student; //使用student数据库
Database changed
mysql> select database(); //再次查看当前使用的数据库验证是否使用正确
+------------+
| database() |
+------------+
| student    | //可以看到成功使用student数据库
+------------+
1 row in set (0.00 sec)

mysql> show tables;  //查看当前数据库中创建的表
Empty set (0.00 sec) //可以看到现在student数据库中还没有创建表

mysql> create table user_list(id int(3),username varchar(12),sex varchar(12))engine = MyISAM;  //创建user_list表，且使用MySAM引擎
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show tables; //再次查看数据库中创建的数据表
+-------------------+
| Tables_in_student |
+-------------------+
| user_list         |  //可以看到我们创建的user_list表
+-------------------+
1 row in set (0.00 sec)

mysql> show create table user_list; 查看表的详细信息
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                    |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_list | CREATE TABLE `user_list` (
  `id` int DEFAULT NULL,
  `username` varchar(12) DEFAULT NULL,
  `sex` varchar(12) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=gbk |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

3、复制 user_list 数据表为 user_list_new 表,但是要求不需要复制数据。

mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| user_list         |
+-------------------+
1 row in set (0.00 sec)

mysql> create table user_list_new like user_list;  //复制user_list表但不复制数据
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| user_list         |
| user_list_new     |
+-------------------+
2 rows in set (0.00 sec)

mysql> desc user_list_new;//查看表的结构
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(12) | YES  |     | NULL    |       |
| sex      | varchar(12) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

4、在 user_list_new 表中增加新字段，该字段位于 username 字段和 sex 字段中间，该字段数据类型要求为 varchar(10)。

mysql> desc user_list_new;  //查看表的结构
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int         | YES  |     | NULL    |       |
| username | varchar(12) | YES  |     | NULL    |       |
| sex      | varchar(12) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table user_list_new add new_field varchar(10) after username;//增加新的字段
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user_list_new;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| id        | int         | YES  |     | NULL    |       |
| username  | varchar(12) | YES  |     | NULL    |       |
| new_field | varchar(10) | YES  |     | NULL    |       |
| sex       | varchar(12) | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)