数据库作业二

创建一个数据，db_classics

创建英雄表：t_hero

id name nickname age address job  weapon group

1 张百川 玉皇大帝 200000

2 孙悟空  齐天大圣

3 林妹妹

添加对应的约束条件

插入80条数据

通过alter添加两个新的字段

通过alter删除一个字段

重命名一张表

create database db_classics default charset=utf8mb4;

create table t_hero(

id int,

name varchar(100),

                nickname varchar(100),

        tel char(20),

                age int,

address text,

                job  varchar(100),

                weapon varchar(100),

                group1  varchar(100)            

) ;

insert into t_hero(id, name,nickname, tel, age, address, job,weapon,group1) values(1, "张百川", '玉皇大帝', '200000', '110','tting','god','wu','s');

insert into t_hero(id, name,nickname, tel, age, address, job,weapon,group1) values(2, "孙悟空", '齐天大圣', '200000', '111','hgs','monkey','steak','s');

insert into t_hero(id, name,nickname, tel, age, address, job,weapon,group1) values(3, "林妹妹 ", '林黛玉', '20', '112','hgs','human','wu','r');

查看：describe  t_hero;

select * from t_hero;

加约束条件

DROP DATABASE db_classics;

create database db_classics default charset=utf8mb4;

Use db_classics

create table if not exists  t_hero(

id int primary key auto_increment,

name varchar(100) not null unique,

        nickname varchar(100) not null unique,

    tel char(20),

        age int check(age >= 18) default 18,

address text ,

        job  varchar(100),

        weapon varchar(100),

        group1  varchar(100)            

) engine=innodb ;

insert into t_hero values(1, "张百川", '玉皇大帝', null, '110','tting','god','wu','s');

insert into t_hero values(null, "孙悟空", '齐天大圣', default, '111','hgs','monkey','steak','s') ;

insert into t_hero values(null,"林妹妹", '林黛玉', '20', '112','hgs','human','wu','r') ;

插入80条数据

这里我们用python随机80条数据name,nickname,ages随机生成

import random
import string

for i in range(2, 82):
    name = ''.join(random.choices(string.ascii_letters, k=5)) # 生成5位随机字符串作为姓名
    nickname = ''.join(random.choices(string.ascii_letters, k=5)) # 生成5位随机字符串作为昵称
    age = random.randint(1, 100) # 生成1到100之间的随机整数作为年龄
    sql = f"insert into t_hero(id, name, nickname, tel, age, address, job, weapon, group1) values({i}, '{name}', '{nickname}', '200000', '{age}', 'tting', 'god', 'wu', 's');"
    print(sql)

再复制执行：

通过alter添加两个新的字段:

Alter table t_hero add skin varchar(100);

Alter table t_hero add 属性 varchar(100);

通过alter删除一个字段:

通过alter删除一个字段:

Alter table t_hero drop 属性 ;

重命名一张表:

Alter table t_hero rename b_hero;