# 语法# Update 表名 # Set 列1 = 新值 1,列2 = 新值2,列n = 新值n.....# Where expr# =============================================# 修改zhangsan的公司为google
update user set com = 'google' where name = 'zhangsan';
1.3 DML-delete
# 语法
# Deletefrom 表名 where expr
# =============================================
deletefromuserwhere name = 'lisi';
1.1:主键为32的商品
select goods_id,goods_name,shop_price from goods where goods_id=32;
1.2:不属第3栏目的所有商品
select goods_id,cat_id,goods_name,shop_price from goods where cat_id!=3;
1.3:本店价格高于3000元的商品
select goods_id,cat_id,goods_name,shop_price from goods where shop_price >3000;
1.4:本店价格低于或等于100元的商品
select goods_id,cat_id,goods_name,shop_price from goods where shop_price <=100;
1.5:取出第4栏目或第11栏目的商品(不许用or)
select goods_id,cat_id,goods_name,shop_price from goods
where cat_id in (4,11);
1.6:取出100<=价格<=500的商品(不许用and)
select goods_id,cat_id,goods_name,shop_price from goods
where shop_price between 100and500;
1.7:取出不属于第3栏目且不属于第11栏目的商品(and,或notin分别实现)
select goods_id,cat_id,goods_name,shop_price from goods
where cat_id!=3and cat_id!=11;
select goods_id,cat_id,goods_name,shop_price from goods
where cat_id notin (3,11);
1.8:取出价格大于100且小于300,或者大于4000且小于5000的商品()
select goods_id,cat_id,goods_name,shop_price from goods
where shop_price>100and shop_price <300or shop_price >4000and shop_price <5000;
1.9:取出第3个栏目下面价格<1000或>3000,并且点击量>5的系列商品
select goods_id,cat_id,goods_name,shop_price,click_count from goods
where cat_id=3and (shop_price <1000or shop_price>3000)
and click_count>5;
1.10:取出第1个栏目下面的商品(注意:1栏目下面没商品,但其子栏目下有)
select goods_id,cat_id,goods_name,shop_price,click_count from goods
where cat_id in (2,3,4,5);
1.11:取出名字以"诺基亚"开头的商品
select goods_id,cat_id,goods_name,shop_price from goods
where goods_name like'诺基亚%';1.12:取出名字为"诺基亚Nxx"的手机
select goods_id,cat_id,goods_name,shop_price from goods
where goods_name like'诺基亚N__';1.13:取出名字不以"诺基亚"开头的商品
select goods_id,cat_id,goods_name,shop_price from goods
where goods_name notlike'诺基亚%';1.14:取出第3个栏目下面价格在1000到3000之间,并且点击量>5"诺基亚"开头的系列商品
select goods_id,cat_id,goods_name,shop_price from goods
where cat_id=3and shop_price>1000and shop_price <3000and click_count>5and goods_name like'诺基亚%';select goods_id,cat_id,goods_name,shop_price from goods
where shop_price between 1000and3000and cat_id=3and click_count>5and goods_name like'诺基亚%';
3.2 group/having/order by/limit
分组函数中常用聚合函数
2.1:查出最贵的商品的价格
selectmax(shop_price) from goods;
2.2:查出最大(最新)的商品编号
selectmax(goods_id) from goods;
2.3:查出最便宜的商品的价格
selectmin(shop_price) from goods;
2.4:查出最旧(最小)的商品编号
selectmin(goods_id) from goods;
2.5:查询该店所有商品的库存总量
selectsum(goods_number) from goods;
2.6:查询所有商品的平均价
selectavg(shop_price) from goods;
2.7:查询该店一共有多少种商品
selectcount(*) from goods;
having与group by综合查询
3.1:查询该店的商品比市场价所节省的价格
select goods_id,goods_name,market_price-shop_price as j from goods ;
3.2:查询每个商品所积压的货款(提示:库存*单价)
select goods_id,goods_name,goods_number*shop_price from goods
3.3:查询该店积压的总货款
selectsum(goods_number*shop_price) from goods;
3.4:查询该店每个栏目下面积压的货款.
select cat_id,sum(goods_number*shop_price) as k from goods groupby cat_id;
3.5:查询比市场价省钱200元以上的商品及该商品所省的钱(where和having分别实现)
select goods_id,goods_name,market_price-shop_price as k from goods
where market_price-shop_price >200;select goods_id,goods_name,market_price-shop_price as k from goods
having k >200;
3.6:查询积压货款超过2W元的栏目,以及该栏目积压的货款
select cat_id,sum(goods_number*shop_price) as k from goods
groupby cat_id having k>200003.7:where-having-group综合练习题
# 有如下表及数据
# +------+---------+-------+
# | name | subject | score |
# +------+---------+-------+
# | 张三 | 数学 | 90 |
# | 张三 | 语文 | 50 |
# | 张三 | 地理 | 40 |
# | 李四 | 语文 | 55 |
# | 李四 | 政治 | 45 |
# | 王五 | 政治 | 30 |
# +------+---------+-------+
# 要求:查询出2门及2门以上不及格者的平均成绩
# 这里使用sum而不是countselect name,sum(score<60) as k,avg(score) from result groupby name having k>=2;
order by 与 limit查询
4.1:按价格由高到低排序
select goods_id,goods_name,shop_price from goods orderby shop_price desc;
4.2:按发布时间由早到晚排序
select goods_id,goods_name,add_time from goods orderby add_time;
4.3:接栏目由低到高排序,栏目内部按价格由高到低排序
select goods_id,cat_id,goods_name,shop_price from goods
orderby cat_id ,shop_price desc;
4.4:取出价格最高的前三名商品
select goods_id,goods_name,shop_price from goods
orderby shop_price desc limit 3;
4.5:取出点击量前三名到前5名的商品
select goods_id,goods_name,click_count from goods
orderby click_count desc limit 2,3;
3.3 子查询
where子查询
# 查询每个栏目下最新商品的信息(goods_id最大为最新)
select goods_id,cat_id,goods_name from goods
where goods_id in (selectmax(goods_id) from goods groupby cat_id);
from子查询
select cat_id,goods_id,goods_name from
(select * from goods orderby cat_id asc,goods_id desc) as tmp
groupby cat_id;
exists子查询
# 查出所有有商品的栏目
select * from category
whereexists (select * from goods where goods.cat_id=category.cat_id);
3.4 连接查询
左连接/右连接
# 取出所有商品的商品名,栏目名,价格select goods_name,cat_name,shop_price from
goods left join category
on goods.cat_id = category.cat_id;
# 右连接与左连接类似# inner join或者join为等值连接
面试题
# 根据给出的表结构按要求写出SQL语句。# Match 赛程表# 字段名称 字段类型 描述# matchID int 主键# hostTeamID int 主队的ID# guestTeamID int 客队的ID# matchResult varchar(20) 比赛结果,如(2:0)# matchTime date 比赛开始时间# =============================================# Team 参赛队伍表# 字段名称 字段类型 描述# teamID int 主键# teamName varchar(20) 队伍名称# =============================================# Match的hostTeamID与guestTeamID都与Team中的teamID关联# 查出 2006-6-1 到2006-7-1之间举行的所有比赛,并且用以下形式列出:# 拜仁 2:0 不来梅 2006-6-21# select * from m; select * from t;# +-----+------+------+------+------------+ +------+----------+# | mid | hid | gid | mres | matime | | tid | tname |# +-----+------+------+------+------------+ +------+----------+# | 1 | 1 | 2 | 2:0 | 2006-05-21 | | 1 | 国安 |# | 2 | 2 | 3 | 1:2 | 2006-06-21 | | 2 | 申花 |# | 3 | 3 | 1 | 2:5 | 2006-06-25 | | 3 | 公益联队 |# | 4 | 2 | 1 | 3:2 | 2006-07-21 | +------+----------+# +-----+------+------+------+------------+
select hid,t1.tname as hname,mres,gid,t2.tname as gname,matime
from m left join t as t1
on m.hid = t1.tid
left join t as t2
on m.gid = t2.tid;
# 建表时直接声明索引:
create table tableName (
列1 列类型 列属性, .... 列N 列类型 列属性, primary key(列名), index [索引名](列名), unique[索引名](列名), # 中文环境下全文索引无效,一般用第三方解决方案 fulltext[索引名](列名)
)engine=InnoDB ,charset=utf8;
# ===============================================# 通过修改表建立索引
alter table add index [索引名](列名);
alter table add unique [索引名](列名);
alter table add primary key(列名);
alter table add fulltext [索引名](列名);
索引优化
# 1、索引长度,N为建索引的长度
index [索引名](列名(N))
# 2、多列索引,把多列看成一个整体建立索引
index [索引名](列名1,列名2,...,列名N)
# 3、冗余索引,在某列上可能存在多个索引
索引操作
# 注:索引名一般是列名,如果不是,可通过show index from tableName查看索引
show index from 表名 \G
# ===============================================
# 删除主键:
altertabledropprimarykey
# 删除其他索引:
altertabledrop index 索引名
drop index 索引名 on 表名
# 为了进行数据类型转化,mysql提供了cast()函数,它可以把一个值转化为指定的数据类型。类型有:binary,char,date,time,datetime,signed,unsigned
# 示例:
selectcast(now() as signed integer),curdate()+0;select'f'=binary 'f','f'=cast('f'as binary);