一、数据库
1、登陆数据库
C:\Users\ASUS>mysql -uroot -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.37 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2、创建数据库zoo
mysql> create database zoo;
Query OK, 1 row affected (0.00 sec)
3、修改数据库zoo字符集为gbk
mysql> alter database zoo
-> character set gbk;
Query OK, 1 row affected (0.01 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| yes |
| zoo |
+--------------------+
7 rows in set (0.00 sec)
mysql> use zoo;
Database changed
5、查看创建数据库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
mysql> drop database zoo;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| sys |
| yes |
+--------------------+
6 rows in set (0.00 sec)
二、创建表
1、创建一个名称为db_system的数据库
mysql> create database db_system;
Query OK, 1 row affected (0.00 sec)
2、在该数据库下创建两张表,具体要求如下
员工表 user
字段 类型 约束 备注
id 整形 主键,自增长 id
NAME 字符型 非空 姓名
gender 字符 非空 性别
birthday 日期型 生日
entry_date 日期型 非空 入职时间
job 字符型 非空 职位
mysql> create table user(
-> id int primary key,
-> NAME char(20) not null,
-> gender char(2) not null,
-> birthday DATE not null,
-> entry_date DATE not null,
-> job char(20) not null
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc user;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| NAME | char(20) | NO | | NULL | |
| gender | char(2) | NO | | NULL | |
| birthday | date | NO | | NULL | |
| entry_date | date | NO | | NULL | |
| job | char(20) | NO | | NULL | |
+------------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
员工绩效表 salary
字段 类型 约束 备注
id 整形 主键,自增长 id
userId 整型 非空,外键,关联的是user表的id字段 用户id
baseSalary 小数 非空 基本工资
month 整数 非空 月份
allowances 小数 非空,默认为0 补贴
mysql> create table salary(
-> id int auto_increment primary key,
-> userld int not null,
-> foreign key (userld) references user(id),
-> baseSalary decimal(10,2) not null,
-> month int not null,
-> allowances decimal(10,2) not null default 0
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc salary;
+------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| userld | 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.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
2、修改job列,使其长度为60。
mysql> alter table user
-> modify job char(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 | |
| NAME | char(20) | NO | | NULL | |
| gender | char(2) | NO | | NULL | |
| birthday | date | NO | | NULL | |
| entry_date | date | NO | | NULL | |
| job | char(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 | |
| NAME | char(20) | NO | | NULL | |
| birthday | date | NO | | NULL | |
| entry_date | date | NO | | NULL | |
| job | char(60) | YES | | NULL | |
| image | tinyblob | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
4、表名salary改为usersalary。
mysql> rename table user to usersalary;
Query OK, 0 rows affected (0.01 sec)
mysql> desc user;
ERROR 1146 (42S02): Table 'db_system.user' doesn't exist
mysql> desc usersalary;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| NAME | char(20) | NO | | NULL | |
| birthday | date | NO | | NULL | |
| entry_date | date | NO | | NULL | |
| job | char(60) | YES | | NULL | |
| image | tinyblob | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
5、修改表的字符集为utf8;
mysql> alter table usersalary convert to character set utf8;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
6、列名name修改为username
mysql> alter table usersalary
-> change name username char(20) not null;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc usersalary;
+------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| id | int | NO | PRI | NULL | |
| username | char(20) | NO | | NULL | |
| birthday | date | NO | | NULL | |
| entry_date | date | NO | | NULL | |
| job | char(60) | YES | | NULL | |
| image | tinyblob | YES | | NULL | |
+------------+----------+------+-----+---------+-------+
6 rows in set (0.00 sec)