大题dati

CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键',
NAME VARCHAR(50) NOT NULL COMMENT '用户名',
phone VARCHAR(11) NOT NULL COMMENT '手机号',
email VARCHAR(100) COMMENT '邮箱',
profession VARCHAR(11) COMMENT '专业',
age TINYINT UNSIGNED COMMENT '年龄',
gender CHAR(1) COMMENT '性别 , 1: 男, 2: 女',
STATUS CHAR(1) COMMENT '状态',
createtime DATETIME COMMENT '创建时间'
) COMMENT '系统用户表';

INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('吕布', '17799990000', 'lvbu666@163.com', '软件工程', 23, '1',
'6', '2001-02-02 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('曹操', '17799990001', 'caocao666@qq.com', '通讯工程', 33,
'1', '0', '2001-03-05 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('赵云', '17799990002', '17799990@139.com', '英语', 34, '1',
'2', '2002-03-02 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('孙悟空', '17799990003', '17799990@sina.com', '工程造价', 54,
'1', '0', '2001-07-02 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('花木兰', '17799990004', '19980729@sina.com', '软件工程', 23,
'2', '1', '2001-04-22 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('大乔', '17799990005', 'daqiao666@sina.com', '舞蹈', 22, '2',
'0', '2001-02-07 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('露娜', '17799990006', 'luna_love@sina.com', '应用数学', 24,
'2', '0', '2001-02-08 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('程咬金', '17799990007', 'chengyaojin@163.com', '化工', 38,
'1', '5', '2001-05-23 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('项羽', '17799990008', 'xiaoyu666@qq.com', '金属材料', 43,
'1', '0', '2001-09-18 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('白起', '17799990009', 'baiqi666@sina.com', '机械工程及其自动
化', 27, '1', '2', '2001-08-16 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('韩信', '17799990010', 'hanxin520@163.com', '无机非金属材料工
程', 27, '1', '0', '2001-06-12 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('荆轲', '17799990011', 'jingke123@163.com', '会计', 29, '1',
'0', '2001-05-11 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('兰陵王', '17799990012', 'lanlinwang666@126.com', '工程造价',
44, '1', '1', '2001-04-09 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('狂铁', '17799990013', 'kuangtie@sina.com', '应用数学', 43,
'1', '2', '2001-04-10 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('貂蝉', '17799990014', '84958948374@qq.com', '软件工程', 40,
'2', '3', '2001-02-12 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('妲己', '17799990015', '2783238293@qq.com', '软件工程', 31,
'2', '0', '2001-01-30 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('芈月', '17799990016', 'xiaomin2001@sina.com', '工业经济', 35,
'2', '0', '2000-05-03 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('嬴政', '17799990017', '8839434342@qq.com', '化工', 38, '1',
'1', '2001-08-08 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('狄仁杰', '17799990018', 'jujiamlm8166@163.com', '国际贸易',
30, '1', '0', '2007-03-12 00:00:00');

# 表结构中插入的数据如下：
# 数据准备好了之后，接下来，我们就来完成如下需求：
# A. name字段为姓名字段，该字段的值可能会重复，为该字段创建索引。
# B. phone手机号字段的值，是非空，且唯一的，为该字段创建唯一索引。
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('安琪拉', '17799990019', 'jdodm1h@126.com', '城市规划', 51,
'2', '0', '2001-08-15 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('典韦', '17799990020', 'ycaunanjian@163.com', '城市规划', 52,
'1', '2', '2000-04-12 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('廉颇', '17799990021', 'lianpo321@126.com', '土木工程', 19,
'1', '3', '2002-07-18 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('后羿', '17799990022', 'altycj2000@139.com', '城市园林', 20,
'1', '0', '2002-03-10 00:00:00');
INSERT INTO tb_user (NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES ('姜子牙', '17799990023', '37483844@qq.com', '工程造价', 29,
'1', '4', '2003-05-26 00:00:00');

-- 准备工作 : 日志表 user_logs
CREATE TABLE user_logs(
id INT(11) NOT NULL AUTO_INCREMENT,
operation VARCHAR(20) NOT NULL COMMENT '操作类型, insert/update/delete',
operate_time DATETIME NOT NULL COMMENT '操作时间',
operate_id INT(11) NOT NULL COMMENT '操作的ID',
operate_params VARCHAR(500) COMMENT '操作参数',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TRIGGER tb_user_insert_trigger
    AFTER INSERT ON tb_user FOR EACH ROW
BEGIN
    INSERT INTO user_logs(id, operation, operate_time, operate_id, operate_params)
VALUES
    (NULL, 'insert', NOW(), new.id, CONCAT('插入的数据内容为:
    id=',new.id,',name=',new.name, ', phone=', NEW.phone, ', email=', NEW.email, ',
    profession=', NEW.profession));
END;

-- 查看
SHOW TRIGGERS ;
-- 插入数据到tb_user
INSERT INTO tb_user(id, NAME, phone, email, profession, age, gender, STATUS,
createtime) VALUES (26,'三皇子','18809091212','erhuangzi@163.com','软件工
程',23,'1','1',NOW());