- 基本操作
创建数据库
create database exam;
use exam;
创建表
#“BRANDS”
create table BRANDS
(
id int(11) primary key not null auto_increment,
bna varchar(10),
bnu int(4),
price decimal(10,1) not null;
#CATES
create table CATES
(
cna varchar(10),
cnu int(4));
#CATE-BRANDS
create table CATE_BRANDS
(
bnu int(4),
cnu int(4));
·下附插入的数据
三个表关系
品牌(品牌名称,品牌编号,价格,类型名称)
类型(类型名称,类型编号)
类型-品牌(品牌编号,类型编号)
- 设计题
(a)找到价格在3000以上的超级本有哪些品牌。
select DISTINCT brand_name from goods where price > 3000 AND cate_name = "超级本";
(b)找到类型编号为2的品牌名。
#where
select distinct BRANDS.bna from BRANDS,CATES where BRANDS.cate_name = CATES.cna AND CATES.cnu = 2;
#内连接 inner join ... on
select distinct BRANDS.bna from BRANDS inner join CATES on BRANDS.cate_name = CATES.cna where CATES.cnu = 2;
#左连接 left join ... on
select distinct BRANDS.bna from BRANDS left join CATES on BRANDS.cate_name = CATES.cna where CATES.cnu = 2;
(c)在goods表中找到name含有“键盘”的数据。
select * from goods where name like '%键盘%';
#%表示任何字符出现任意次数
#‘%键盘’ 表示以“键盘”结尾;‘键盘%’ 表示以“键盘”开头
~补充知识
- update
#同一个表
update table1 set field1 = field2;
#不同表
update table1,table2 set table1.field1=table2.field2 where table1.id=table2.id
#对表格中的内容进行操作 : insert、update、delete
- alter table
#增加一列-add
alter table BRANDS add column name varchar(10);
#删除一列-drop
alter table BRANDS drop column name varchar(10);
#修改数据类型、字段位置-modify
alter table BRANDS modify column id int(11) auto_increment first;
#修改名字-change (change修改的范围和力度要比modify 大)
alter table BRANDS modify column id int(11) auto_increment first;
- 删除表
#删除原表中的数据记录
DELETE FROM table;
#删除数据表
DROP TABLE table;
附表
“BRANDS”表
insert into BRANDS
VALUES
(1,"华硕",1,3399),
(2,"华硕",1,2799),
(3,"联想",2,4880),
(4,"联想",2,4299),
(5,"索尼",3,7999),
(6,"苹果",4,1999),
(7,"苹果",4,3388),
(8,"苹果",4,2788),
(9,"联想",2,29),
(10,"戴尔",5,3499),
(11,"爱戴尔",6,2899),
(12,"雷蛇",7,29),
(13,"索尼",3,299),
(14,"唯爱",8,1899);
“CATES”表
#插入数据
insert into CATES
VALUES
("笔记本",1),
("超级本",2),
("平板电脑",3),
("台式机",4),
("电脑配件",5)
;
“CATE-BRANDS”表
insert into CATE_BRANDS
VALUES
(1,1),
(1,1),
(2,2),
(2,2),
(3,2),
(4,3),
(4,3),
(4,3),
(2,4),
(5,4),
(6,5),
(7,5),
(3,5),
(8,5)
;
京东数据—“goods”表
create table goods( id int unsigned primary key auto_increment not null,
name varchar(150) not null,
cate_name varchar(40) not null,
brand_name varchar(40) not null,
price decimal(10.3) not null default 0,
is_show bit not null default 1,
is_saleoff bit not null default 0
);
insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default);
insert into goods values(0,'x240 超级本','超级本','联想','4880',default,default);
insert into goods values(0,'u330p 13.3英寸超级本','超级本','联想','4299',default,default);
insert into goods values(0,'svp13226scb 触控超级本','超级本','索尼','7999',default,default);
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1999',default,default);
insert into goods values(0,'iPad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
insert into goods values(0,'iPad mini 配置 retine 显示屏','平板电脑','苹果','2788',default,default);
insert into goods values(0,'ideacentre c3340 20英寸一体电脑','台式机','联想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
insert into goods values(0,'15.6 寸电脑屏保护膜','电脑配件','爱戴尔','29',default,default);
insert into goods values(0,'优雅 复古 无线鼠标键盘','电脑配件','雷蛇','299',default,default);
insert into goods values(0,'15寸 4K 液晶显示屏','电脑配件','索尼','1899',default,default);
insert into goods values(0,'限量款 LOL 鼠标垫','电脑配件','唯爱','29',default,default);