数据库MYSQL学习总结9—建立完整用户信息表

建立完整用户信息表

-- 用户名信息表
CREATE TABLE IF NOT EXISTS test_msg(
    id INT UNSIGNED AUTO_INCREMENT KEY COMMENT '用户编号int无符号自动主键',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名50字符不为空唯一',
    password CHAR(32) NOT NULL COMMENT '密码32字符不为空唯一',
    email VARCHAR(50) NOT NULL UNIQUE COMMENT '邮箱50字符不为空唯一',
    age TINYINT NOT NULL COMMENT '年龄TINYINT不为空',
    sex ENUM('男','女','保密') NOT NULL COMMENT '性别ENUM枚举不为空',
    tel CHAR(11) NOT NULL UNIQUE COMMENT '电话字符11不为空唯一',
    addr VARCHAR(50) NOT NULL COMMENT '地址字符50可变不为空',
    card CHAR(18) NOT NULL UNIQUE COMMENT '身份证定长18字符不为空唯一',
    married TINYINT(1) NOT NULL COMMENT '是否结婚1代表已结婚 0代表未结婚',
    salary FLOAT(8,2) NOT NULL COMMENT '薪水float占8位'
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

添加三条数据

INSERT test_msg(username,password,email,age,sex,tel,addr,card,married,salary) VALUES('jack','123456','[email protected]',18,1,'12345678901','北京','123456789123456789',1,3700.362);

INSERT test_msg(username,password,email,age,sex,tel,addr,card,married,salary) VALUES('tom','121156','[email protected]',25,1,'12345670001','天津','123456700003456789',1,4500.132);

INSERT test_msg(username,password,email,age,sex,tel,addr,card,married,salary) VALUES('Mary','113456','[email protected]',27,2,'12300678901','上海','123456789123400089',2,9000.430);