【无标题】

ehickdhwdjjsjwifici

于 2024-07-04 17:40:50 发布

阅读量468

点赞数 17

文章标签： adb

本文链接：https://blog.csdn.net/weixin_74819554/article/details/140182830

版权

一、数据库
1、登陆数据库
2、创建数据库zoo
3、修改数据库zoo字符集为gbk
4、选择当前数据库为zoo
5、查看创建数据库zoo信息
6、删除数据库zoo
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.37 MySQL Community Server - GPL

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> create database zoo;
Query OK, 1 row affected (0.01 sec)

mysql> create database if not exists zoo
-> create database if not exists zoo;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘create database if not exists zoo’ at line 2
mysql> create database if not exists zoo character set gbk;
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> use zoo
Database changed
mysql> select database();
±-----------+
| database() |
±-----------+
| zoo |
±-----------+
1 row in set (0.00 sec)

mysql> drop database zoo;
Query OK, 0 rows affected (0.01 sec)
二、创建表
1、创建一个名称为db_system的数据库
2、在该数据库下创建两张表，具体要求如下
员工表 user
字段类型约束备注
id 整形主键，自增长 id
NAME 字符型非空姓名
gender 字符非空性别
birthday 日期型生日
entry_date 日期型非空入职时间
job 字符型非空职位
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> create table salary(
-> id int primary key auto_increment comment ‘id’,
-> userid int not null comment ‘用户id’,
-> basesalary float not null comment ‘基本工资’,
-> month int not null comment ‘月份’,
-> allowances float not null default 0 comment ‘补贴’,
-> foreign key (userid) references user (id)
-> );
Query OK, 0 rows affected (0.01 sec)
三、修改表
1、在上面员工表的基本上增加一个image列，类型是blod，长度255。
2、修改job列，使其长度为60。
3、删除gender列。
4、表名salary改为usersalary。
5、修改表的字符集为utf8;
6、列名name修改为username
mysql> alter table user add image blob(255);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

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

mysql> alter table user convert to character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected, 2 warnings (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 2

mysql> alter table user change name username varchar(20) not null comment ‘用户名’;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0