SQL基础笔记

1、创建一个表shohin 

CREATE TABLE `shohin` (
  `good_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `good_name` varchar(100) NOT NULL,
  `category` varchar(32) NOT NULL,
  `sale` int(11) DEFAULT NULL,
  `cost` int(11) DEFAULT NULL,<pre name="code" class="sql">SELECT DISTINCT category,createtime FROM shohin;

`createtime` date DEFAULT NULL, PRIMARY KEY (`good_id`)) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

2、插入数据

INSERT INTO `shohin` VALUES ('1', 'T恤衫', '衣服', '1000', '500', '2009-09-20');
INSERT INTO `shohin` VALUES ('2', '打孔器', '办公用品', '500', '320', '2009-09-11');
INSERT INTO `shohin` VALUES ('3', '运动T恤', '衣服', '4000', '2800', null);
INSERT INTO `shohin` VALUES ('4', '菜刀', '厨房用具', '3000', '2800', '2009-09-20');
INSERT INTO `shohin` VALUES ('5', '高压锅', '厨房用具', '6800', '5000', '2009-01-15');
INSERT INTO `shohin` VALUES ('6', '叉子', '厨房用具', '500', null, '2009-09-20');
INSERT INTO `shohin` VALUES ('7', '擦菜板', '厨房用具', '880', '790', '2008-04-28');
INSERT INTO `shohin` VALUES ('8', '圆珠笔', '办公用品', '100', null, '2009-11-11');

3、查询表中指定字段

SELECT good_id,good_name,sale FROM shohin;

4、更新表中good_id为3的记录

UPDATE shohin SET createtime=NULL WHERE good_id=3;

5、将category与creatime都重复的数据结合为一条输出

SELECT DISTINCT category,createtime FROM shohin;