【MySQL练习】创建用户表

# 建表语句
CREATE TABLE keep_user(
	id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	email VARCHAR(30) NOT NULL,
	user_password VARCHAR(30) NOT NULL,
	uid BIGINT UNSIGNED NOT NULL,
	nickname VARCHAR(20) NOT NULL,
	gmt_create DATETIME DEFAULT CURRENT_TIMESTAMP,
	gmt_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	UNIQUE(email),
	UNIQUE(uid),
	UNIQUE(nickname)
);


# 编写关于此表常用的sql操作

# 检查邮箱是否已经被注册
SELECT 1 FROM keep_user
WHERE email = '3131015733@qq.com';

# 模拟注册
INSERT INTO keep_user(email,user_password,uid,nickname)
VALUES('3131015733@qq.com','123456',1,'urfread');

# 模拟登录
SELECT 1 FROM keep_user
WHERE email = '3131015733@qq.com' and user_password='123456';

SELECT id,uid,nickname FROM keep_user
WHERE email = '3131015733@qq.com';

# 改密码
UPDATE keep_user
SET user_password = '654321'
WHERE email = '3131015733@qq.com';
# 改用户昵称
UPDATE keep_user
SET nickname = '654321'
WHERE id = 1;