①创建库db_06默认字符集utf8；5分②创建表user表字段：id用户编号username用户名password用户密码birthyear出生年birthmonth出生月birthday出生日gender性别isonline是否在线hometown家乡location居住地15分字段名 数据类型 约束id Int Primary keynot nullusername Varchar20 No

创建库db_06,默认字符集utf8： CREATE DATABASE db_06 DEFAULT CHARACTER SET utf8;

创建表user表： CREATE TABLE user ( id INT PRIMARY KEY NOT NULL, username VARCHAR(20) NOT NULL, password VARCHAR(20) NOT NULL, birthyear INT, birthmonth INT, birthday INT, gender VARCHAR(10), isonline VARCHAR(10), hometown VARCHAR(10), location VARCHAR(10) );

插入10条数据： INSERT INTO user (id, username, password, birthyear, birthmonth, birthday, gender, isonline, hometown, location) VALUES (1, 'liujie', '123456', 1991, 7, 12, '女', '是', '湖南', '长沙'), (2, 'zhangsan', 'zs123', 1994, 8, 18, '男', '是', '河北', '长沙'), (3, 'qinhao', 'qinhao', 1989, 6, 7, '男', '是', '河南', '长沙'), (4, 'zhangshuang', 'zs123', 1988, 6, 5, '女', '否', '云南', '岳阳'), (5, 'lisi', '123678', 1992, 8, 1, '女', '是', '内蒙', '赤峰'), (6, 'wangwu', '111333', 1992, 12, 3, '男', '是', '西藏', '拉萨'), (7, 'maliu', '123456', 1993, 8, 1, '男', '否', '湖南', '长沙'), (8, 'lilei', '123456', 1994, 4, 9, '男', '是', '湖南', '长沙'), (9, 'hanbin', '123456', 1994, 3, 27, '男', '是', '湖北', '武昌'), (10, 'zhaohao', '987651', 1995, 9, 21, '女', '是', '湖南', '长沙');

实现以下sql： a.全表查询user表 SELECT * FROM user;

b.查询男性用户信息 SELECT * FROM user WHERE gender = '男';

c.查询女性用户信息 SELECT * FROM user WHERE gender = '女';

d.查询在线人数 SELECT COUNT(*) FROM user WHERE isonline = '是';

e.查询1990年后出生的用户 SELECT * FROM user WHERE birthyear >= 1990;

f.查询6月份出生的用户 SELECT * FROM user WHERE birthmonth = 6;

g.查询8月份出生的用户 SELECT * FROM user WHERE birthmonth = 8;

h.查询家乡是湖南的用户 SELECT * FROM user WHERE hometown = '湖南';

i.修改“张三”的状态为离线 UPDATE user SET isonline = '否' WHERE username = 'zhangsan';

j.添加字段registerdate注册日期 ALTER TABLE user ADD registerdate DATE