11月21号 SQL语句

1、创建表

2、SQL语句

2.1、插入数据

INSERT INTO `gdtype` (`code`,`name`,`gdtypeid`,`unit`,`sprice`,`price`)
VALUES('K003','中学作品集',1,'本',50,30),
            ('K004','小学生百强作文集',1,'本',50,30),
            ('K005','七彩语文',1,'本',100,80),
            ('K006','DVD',2,'套',1500,1000),
            ('K007','家庭影院',2,'套',2000,1500),
            ('K008','功放机',2,'套',1000,800),
            ('K009','彩电',2,'台',4500,3000),
            ('K010','麦克风',2,'个',150,100),
            ('K011','AV线',3,'条',15,10),
            ('K012','电源线',3,'条',15,10),
            ('K013','视频线',3,'条',15,10),
            ('K014','显示器',4,'台',1500,1000),
            ('K015','鼠标键盘套装',4,'套',150,100),
            ('K016','联想电脑',4,'台',5500,3000),
            ('K017','戴尔电脑',4,'台',6500,4000),
            ('K018','小米笔记本',4,'台',5500,3000)

2.2、更新数据

-- 更新goods表中name为DVD的值修改DVD套装
UPDATE `goods` SET `name` ='DVD套装' WHERE `name`='DVD'

2.3、查询数据

-- 查询出gdtypeid为2的数据
SELECT    * FROM    `goods` WHERE    `gdtypeid` = 2

2.4、多表查询（内联）

-- 查询出分类名为“家电”的商品
SELECT
    a.*, b.`name` AS fl_name
FROM
    `goods` AS a
INNER JOIN `gdtype` AS b ON a.`gdtypeid` = b.`gdtypeid`
WHERE
    b.`name` = '家电'

2.5、删除数据

-- 删除goodsid=1的商品
DELETE FROM `goods` WHERE `goodsid`=1

---

手写代码