数据库（表的创建以及修改）

最新推荐文章于 2024-07-12 16:34:24 发布

炭烧小肥杨

最新推荐文章于 2024-07-12 16:34:24 发布

阅读量285

点赞数 8

文章标签：数据库

本文链接：https://blog.csdn.net/2302_76732016/article/details/140181021

版权

一、数据库

1、登陆数据库

 mysql -uadmin -p123456 -h192.168.1.199

2、创建数据库zoo

mysql> create database zoo;
Query OK, 1 row affected (0.00 sec)

3、修改数据库zoo字符集为gbk

#查看字符集类型
mysql> show variables like 'character%';
+--------------------------+----------------------------------------------------
-----+
| Variable_name            | Value
     |
+--------------------------+----------------------------------------------------
-----+
| character_set_client     | gbk
     |
| character_set_connection | gbk
     |
| character_set_database   | utf8mb4
     |
| character_set_filesystem | binary
     |
| character_set_results    | gbk
     |
| character_set_server     | utf8mb4
     |
| character_set_system     | utf8mb3                                                 |
| character_sets_dir       | D:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)

#修改数据库zoo字符集为gbk
mysql> alter database zoo character set gbk;
Query OK, 1 row affected (0.00 sec)

#查看修改后的是否准确
mysql> show create database zoo;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                |
+----------+------------------------------------------------------------------------------------------------+
| zoo      | CREATE DATABASE `zoo` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4、选择当前数据库为zoo

mysql> use zoo;
Database changed

5、查看创建数据库zoo信息

#查看所数据库有
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
| zoo                |
+--------------------+
6 rows in set (0.01 sec)

#查找数据库zoo信息
mysql> show create database zoo;
+----------+------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                |
+----------+------------------------------------------------------------------------------------------------+
| zoo      | CREATE DATABASE `zoo` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

6、删除数据库zoo

#删除数据库zoo
mysql> drop database zoo;
Query OK, 0 rows affected (0.01 sec)

#查看是否删除
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

二、创建表

1、创建一个名称为db_system的数据库

#创建数据库
mysql> create database db_system;
Query OK, 1 row affected (0.00 sec)

#查看是否创建成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db_system          |
| information_schema |
| mysql              |
| performance_schema |
| student            |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

2、在该数据库下创建两张表，具体要求如下

创建user表

mysql> create table user(
    -> id int primary key auto_increment comment'id',
    -> NAME char(20) not null comment'姓名',
    -> gender char(4) not null comment'性别',
    -> birthday date comment'生日',
    -> entry_date date not null comment'入职时间',
    -> job char(30) not null comment'职位');
Query OK, 0 rows affected (0.02 sec)

查看表信息

mysql> desc user;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int      | NO   | PRI | NULL    | auto_increment |
| NAME       | char(20) | NO   |     | NULL    |                |
| gender     | char(4)  | NO   |     | NULL    |                |
| birthday   | date     | YES  |     | NULL    |                |
| entry_date | date     | NO   |     | NULL    |                |
| job        | char(30) | NO   |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

创建salary表

mysql> create table salary(
    -> id int primary key auto_increment comment'id',
    -> userID int not null comment'用户id',
    -> baseSalary decimal(10,2) not null comment'基本工资',
    -> month int not null comment'月份',
    -> allowances decimal(10,2) not null default 0 comment'补贴',
    -> foreign key (userID) references user(id) on delete cascade);
Query OK, 0 rows affected (0.04 sec)

查看salary表

mysql> desc salary;
+------------+---------------+------+-----+---------+----------------+
| Field      | Type          | Null | Key | Default | Extra          |
+------------+---------------+------+-----+---------+----------------+
| id         | int           | NO   | PRI | NULL    | auto_increment |
| userID     | int           | NO   | MUL | NULL    |                |
| baseSalary | decimal(10,2) | NO   |     | NULL    |                |
| month      | int           | NO   |     | NULL    |                |
| allowances | decimal(10,2) | NO   |     | 0.00    |                |
+------------+---------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

三、修改表

1、在上面员工表的基本上增加一个image列，类型是blob，长度255。

mysql> alter table user add image blob(255);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0



结果

mysql> desc user;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| id         | int      | NO   | PRI | NULL    | auto_increment |
| NAME       | char(20) | NO   |     | NULL    |                |
| gender     | char(4)  | NO   |     | NULL    |                |
| birthday   | date     | YES  |     | NULL    |                |
| entry_date | date     | NO   |     | NULL    |                |
| job        | char(30) | NO   |     | NULL    |                |
| image      | tinyblob | YES  |     | NULL    |                |
+------------+----------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

2、修改job列，使其长度为60。

mysql> alter table user modify job varchar(60);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#结果

mysql> desc user;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| NAME       | char(20)    | NO   |     | NULL    |                |
| gender     | char(4)     | NO   |     | NULL    |                |
| birthday   | date        | YES  |     | NULL    |                |
| entry_date | date        | NO   |     | NULL    |                |
| job        | varchar(60) | YES  |     | NULL    |                |
| image      | tinyblob    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

3、删除gender列。

mysql> alter table user drop gender;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

#结果

mysql> desc user;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| NAME       | char(20)    | NO   |     | NULL    |                |
| birthday   | date        | YES  |     | NULL    |                |
| entry_date | date        | NO   |     | NULL    |                |
| job        | varchar(60) | YES  |     | NULL    |                |
| image      | tinyblob    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

4、表名salary改为usersalary。

mysql> alter table salary rename usersalary;
Query OK, 0 rows affected (0.01 sec)

也可以使用rename table salary to usersalary;命令来修改表名。

5、修改表的字符集为utf8;

mysql> alter table user default character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

6、列名NAME修改为username

mysql> alter table user change NAME  username char(20) not null;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| id         | int         | NO   | PRI | NULL    | auto_increment |
| username   | char(20)    | NO   |     | NULL    |                |
| birthday   | date        | YES  |     | NULL    |                |
| entry_date | date        | NO   |     | NULL    |                |
| job        | varchar(60) | YES  |     | NULL    |                |
| image      | tinyblob    | YES  |     | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)