基础SQL示例

-- ----------------------------
-- 新建数据表
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `uid` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '用户UID',
  `username` varchar(64) NOT NULL DEFAULT '' COMMENT '登录账号',
  `password` varchar(128) NOT NULL DEFAULT '' COMMENT '登录密码',
  `realname` varchar(64) NOT NULL DEFAULT '' COMMENT '姓名',
  `gender` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '性别：0=未知|1=男|2=女',
  `age` smallint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
  `profile` text NOT NULL COMMENT '个人简介',
  PRIMARY KEY (`uid`),
  UNIQUE KEY `unique_username` (`username`) USING BTREE COMMENT '唯一索引：username',
  KEY `index_realname` (`realname`) USING BTREE COMMENT '普通索引：realname'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';



-- ----------------------------
-- 插入记录·方式一（执行效率低，但能一次插入多条记录）
-- ----------------------------
INSERT INTO `user` (`username`, `password`, `realname`, `gender`, `age` , `profile`) VALUES ('zhangsan', '张三的登录密码', '张三', '1', '18' , '张三的个人简介'), ('lisi', '李四的登录密码', '李四', '2', '18' , '李四的个人简介'), ('wangwu', '王五的登录密码', '王五', '1', '19' , '王五的个人简介');



-- ----------------------------
-- 插入记录·方式二（执行效率高，但每次只能插入一条记录）
-- ----------------------------
INSERT INTO `user` SET `username` = 'zhangsan', `password` = '张三的登录密码', `realname` = '张三', `gender` = '1', `age` = '18', `profile` = '张三的个人简介';
INSERT INTO `user` SET `username` = 'lisi', `password` = '李四的登录密码', `realname` = '李四', `gender` = '2', `age` = '18', `profile` = '李四的个人简介';
INSERT INTO `user` SET `username` = 'wangwu', `password` = '王五的登录密码', `realname` = '王五', `gender` = '1', `age` = '19', `profile` = '王五的个人简介';



-- ----------------------------
-- 查询记录
-- ----------------------------
SELECT * FROM `user` WHERE `username` = 'zhangsan' LIMIT 1;



-- ----------------------------
-- 更新记录
-- ----------------------------
UPDATE `user` SET `password` = '张三的登录密码（新）', `profile` = '张三的个人简介（新）' WHERE `username` = 'zhangsan' LIMIT 1;



-- ----------------------------
-- 删除记录
-- ----------------------------
DELETE FROM `user` WHERE `username` = 'zhangsan' LIMIT 1;